[JavaWeb玩耍日记] 数据库

mysql版本：5.7.24

使用Navicat for MySQL辅助学习(2015年版)，这个在粘贴本博客的块引用内容时会有额外的不可见内容导致sql运行出问题，不过有影响的地方笔者已排除

目录

一.数据库创建

二.使用数据库与创建表

三.表内列的数据类型

四.修改表列名与数据类型（alter table：修改表）

五.增删改数据

六.简单查询

七.分页查询与分组查询

八.sql查询语句的执行顺序案例

九.约束案例：给表格的列增加约束

十.外键约束

十一.数据库设计

十二.连接查询

十三.多表查询练习

十四.事务

---

一.数据库创建

1.命令行的简单登录数据库命令？

mysql -uroot -p123456 

 2.mysql数据库存放地址在哪里？如创建了数据库db1的文件夹存放位置

data文件夹

3.特殊数据库(别删了就行)：

1.Information_schema：暂无文件夹，记录有哪一些库和表，是视图逻辑表

2.mysql：存权限安全等信息

3.performance：存储性能相关信息

4.sys：系统相关信息

4.单行注释和多行注释如何表示？

-- 两杠以空格（很方便）

#一个井号

/*左斜杠和Java一样*/

5.查询数据库语句？

show databases;

6.安全创建数据库语句？

create database if not exists db2;

7.安全删除数据库语句？

Drop database  if exists db2;

8.查看自己正在用那个数据库语句？

select database(); 

---

二.使用数据库与创建表

1.使用数据库的语句？

use 数据库名;  如：

use mysql;

2.查询所有的表语句？

show tables;

3.查询表的结构语句（不是表内数据信息）？

desc 表名;   (description)  如：

desc func;

4.创建表的简单语句？

create table 表名(

名字 数据类型，

名字 数据类型
);(表名最好全小写) 如：

create table tb1(
username varchar(10),
password varchar(20)
) ;

5.删除表语句？

drop table 表名; 如：

drop table tb1;

---

三.表内列的数据类型

1.小数类型 的列如何创建，括号中的数字代表的是？

字段名 double（总长度，小数的位数）

2.日期类型 的列如何创建？

字段名 date

3.char()与varchar()有什么区别？

char定长，varchar根据数据自动限定长度。

char优势：存储快，空间小（最多255比特）

4.byte类型的数据在数据库中的列的表达？

tinyint

5.建表案例：

Create table student(

Id int,

name varchar(10),

sex char(1),

birthday date,

score double(5,2),

status tinyint

);

---

四.修改表列名与数据类型（alter table：修改表）

1.修改表名语句？

alter table 表名 rename to 新表名;

alter table student rename to stud;

2.添加列语句？

alter table 表名 add 列名 类型;

alter table stud add qq int;

3.修改列数据类型语句？

alter table 表名 modify 列名 类型

alter table stud modify qq varchar(11);

4.修改列名和其数据类型

alter table 表名 change 旧列名 新列名 新类型

alter table stud change qq Email varchar(20);

5.删除列

alter table 表名 drop 列名;

alter table stud drop Email;

---

五.增删改数据

1.添加一行数据语句

insert into 表名 values（值1，值2....）;

Insert into stud values(1,'zhangsan','b','2003-06-27','100',1);

2.添加单行数据（指定列）语句

insert into 表名（列名1,列名2...） values（值1，值2..)；

Insert into stud(id,name) values (1,"zhangsan");

3.对于字符串，单双引号的区别

都一样

4.添加多行语句

在上面两种添加方式后面添加逗号，续写数据就行

Insert into stud(id,name) values (1,"zhangsan"),(2,"lisi"),(3,"wangwu");

5.修改数据语句

update 表名 set 列=属性，列=属性... where 条件；（不是alter了，是update）

update stud set name='张三',sex='男' where id=1;

6.删除数据语句

delete from 表名 where 条件;（不是drop了，是delete）

delete from stud where sex='男';

不加条件删除所有数据

六.简单查询

 数据准备：（已有表stud）

delete from stud;

Insert into stud(id,name) values (1,"zhangsan"),(2,"lisi"),(3,"wangwu");

Insert into stud(id,name) values (1,"wangwu"),(2,"zhaoliu"),(3,"qianqi");

Insert into stud(id,name) values (1,"zhangsan"),(2,"lisi"),(3,"wangwu");

select * from stud;

1.对查询的结果删除重复数据（和分组不一样）

select distinct * from 表名;

select distinct * from stud;

2.查询某属性有多种可能的值的数据

select * from 表名 where  列 in (值1，值2...);

select * from stud where name in ('zhangsan','lisi');

3.查询某个范围内的数据（数字与日期）

select * from 表名 where 列 between a and b;(在这里ab都可取)

select * from stud where id between 1 and 3;

4.对查询的结果排序使用什么关键字？

排序默认是升序还是降序排序？降序排序需要使用什么关键字？

能根据多个列排序吗？

select * from 表名 order by 列1，列2...；

默认升序，列后加desc表示降序；

order by 后面接多个列名表示在值相同时以后面的列排序

如数学成绩相同，则按英语成绩排名

select * from stud order by id desc,name;

6.聚合函数：sum，count，max，min，avg

使用聚合函数能够对单列进行求和操作吗？

聚合函数遇到值为null的列中数据会怎么办？

对多列数据（如select *）能够使用上面五个聚合函数的哪一个？

聚合函数主要结合分组使用，在此处默认分成一组

可作为查询的对象，对单列进行操作。

聚合函数不统计为null的数据（案例中不包括此内容）。

select count(*) from stud;

select sum(id) from stud;

查询出来的数据只有一个。

---

七.分页查询与分组查询

1.分页查询语句用到的关键字是？关键字后面数字的意思是？

分页查询需要得到想要的页面数应该如何规定关键字后面的数字？

select * from 表 limit 起始索引,查询条数;

select * from stud limit 0,3;

可以看作查询第一页数据（每页三条数据）

只要改其实索引就能改变页数：（页数-1）*3

2.分组查询一般结合聚合函数（分组函数）使用

求id（id相同是一组）总和语句：

select id,sum(id) from stud group by id;

3.分组查询的执行顺序如何进行？

其顺序为：1.查询表中所有数据（from） 2.以group by 分组，分完组之后，相当于一个表变成多个表 3.再到每一个组中去进行sum函数求和

4.分组查询的数据为每一组的第一个吗？

分完组之后查询的数据为那个组中随机一个数据

5.group by后能接多个属性吗？

group by和order by一样后面能接多个数据来分组。

select id,name,sum(id) from stud group by id,name;

6.分组查询显示的数据条数和什么有关？

对于查询结果来说，分成多少组，就只有多少条数据。

7.查找id大于id平均值的数据

错误：select * from stud where id> avg(id)

正确：select * from stud where id>(select avg(id) from stud)

使用聚合函数的时候需要有数据来源，也就是第一步的from

由于where的执行已经在from后，故第一个命令失败

8.having的使用：查找分组之后id总和，去除分组之后的总和小于等于3的部分

select sum(id) from stud group by id having sum(id)>3;

---

八.sql查询语句的执行顺序案例

select 字段  from 表名

where ……

group by ……

having ……(为了过滤分组后（group by）的数据而存在，不可以单独出现)

order by ……

以上语句的执行顺序如下

1. 首先执行 from 从某种表中查询数据；

2. 执行 where 语句过滤原始数据；

3. 执行 group by 进行分组；

4. 执行 having 对分组数据进行操作；

5. 执行 select 选出数据；

6. 执行 order by 排序。

---

九.约束案例：给表格的列增加约束

注意建表时列名与属性之间的空格不要多打，插入数据指定列名的时候就会出错！

drop table if EXISTS emp;#安全删除emp表格
create TABLE emp(
id int primary key auto_increment,#自动增长和主键
ename varchar(50) not null unique,#非空且唯一，连这写！
joindate date not NULL,#非空
salary double(7,2) not null,
bonus double(7,2) default 0 #默认为0
);

1.约束的位置在哪里？能够增加多个约束吗？用什么分隔？

约束的位置写在定义列与数据类型后，可以连续写多个约束，空格分隔即可。

主键约束：primary key

自动增长约束：auto_increment，一般和主键约束一起写

非空约束：not null

唯一约束：unique

默认约束：default 数据值

案例1：插入数据，主键为空的情况

当添加数据为空时，且数据约束为自动增长，该数据添加后变为多少？

会按照当前列最大数据往后增长。

INSERT INTO emp (id,ename,joindate,salary)VALUES (2,'aaa','2020-12-25',20000);

INSERT INTO emp (id,ename,joindate,salary)VALUES (NULL,'bbb','2020-12-25',20000);

select * from emp;

案例2：插入主键小于该主键的这一列中最大值的合法数据

在主键且自动增长的列中，有值为2的数据与值为3的数据，能够直接插入值为1的数据吗？

在有主键和自动增长的约束的情况下也可以可以指定合法的主键放入数据，不是只能将数据继续变大。

INSERT INTO emp (id,ename,joindate,salary)VALUES (1,'ccc','2020-12-25',20000);

select * from emp;

案例3：插入数据，数据中某项数据有默认为0的约束，该项数据插入值为null

有默认约束的时候，填写数据为null，会插入默认值吗？

有默认约束的时候，填null不会插入 默认值，还是null

INSERT INTO emp VALUES (null,'ddd','2020-12-25',20000,null);

select * from emp;

案例4：让主键自动增长的第二种插入数据方式（第一种为指定主键为null）

插入数据，指定列，不指定id主键

insert into emp(ename,joindate,salary) values('eee','2020-12-25',20000);

select * from emp;

---

十.外键约束

在一对多的关系中，要先删除多，再删除一

直接删除部门——不行，要先删除员工，最后再删除这个部门。

1.如果要增加外键，应该在部门表里面加还是在员工表里面加呢？

要在员工表里面设置外键，也就是从表-如果以后还需要增加表关联主表：就不需要再修改主表了，把琐碎的复杂的留给从表即可

外键约束案例：

drop table if EXISTS dept;

drop table if EXISTS emp;

-- 部门表（主表）

CREATE TABLE dept(

id int primary key auto_increment,

dep_name varchar(20),

addr varchar(20)

);

-- 员工表 （从表）

CREATE TABLE emp(

id int primary key auto_increment,

name varchar(20),

age int,

-- 外键

dep_id int,

-- 添加约束,指定是外键约束,关联 从表的外键 到 主表的主键

constraint fk_emp_dept foreign key (dep_id) references dept(id)

);

2.外键约束取名一般为？

fk_从表_主表

3.在创建表的时候创建外键的方法？

在定义列后加入另一句：constraint 约束名 foreign key (外键) references 主表(主键)

3.创建表，添加数据，删除数据的顺序有什么要求？

先创建主表，再创建从表。

先添加主表数据（部门数据），再添加员工数据。

先删除从表数据，再删除主表数据。外键在哪里就先删除哪一个

4.在创建表之后删除约束与添加约束，其实也就是对表进行修改

alter table emp drop FOREIGN KEY fk_emp_dept;

alter table emp add constraint fk_emp_dept foreign key (dep_id) references dept(id);

---

十一.数据库设计

一对多，多对多，一对一

1.一对多的实现：

如部门员工管理，从表建立外键即可

2.对于一对一的实现：

通常把表的信息分为常用信息和不常用信息,两张表的关联也可以使用外键来保证数据的完整性。不常用信息设置为外键即可。

3.多对多的实现

如用户与商品，一个用户可以买多个商品，一个商品也能够被多个用户买，这个时候我们的订单里面记录了用户与商品，此时应该在订单这个表中创建两个外键。

drop table if exists db_order;

drop table if exists goods;

drop table if exists user;

create table goods(id int primary key);

create table user(id int primary key);

create table db_order(

user_id int,

goods_id int,

cost int

);

alter table db_order add constraint fk_order_user foreign key (user_id) references user(id);

alter table db_order add constraint fk_order_goods foreign key (goods_id) references goods(id);

---

十二.连接查询

准备数据

drop table if exists emp;

drop table if exists dept;

create table dept(

did int primary key auto_increment,

dname char(3)

);

create table emp(

id int primary key auto_increment,

name varchar(10),

gender char(1),

salary int,

join_date date,

dep_id int

);

-- 添加部门数据

INSERT INTO dept (dNAME) VALUES ('研发部'),('市场部'),('财务部'),('销售部');

select * from dept;

-- 添加员工数据

INSERT INTO emp(NAME,gender,salary,join_date,dep_id) VALUES

('孙悟空','男',7200,'2013-02-24',1),

('猪八戒','男',3600,'2010-12-02',2),

('唐僧','男',9000,'2008-08-08',2),

('白骨精','女',5000,'2015-10-07',3),

('蜘蛛精','女',4500,'2011-03-14',1),

('小白龙','男',2500,'2011-02-14',null);

select * from emp;

连接查询

1.连接查询简单案例（笛卡尔连接），从结果来看笛卡尔连接该如何描述？

select * from emp,dept;

两个表连接查询，相当于其中一个表每一项数据都连接了另一个表中的每一项数据

2.内连接简单案例（查看人员职务对照关系，没有则不显示），从结果来看内连接该如何描述？

select * from emp , dept where emp.dep_id = dept.did;

从两个不同的结果来看，内连接就是在连接查询的基础上去掉了没有对应条件的数据（取交集）

3.这时发现，缺少了白龙马的数据（emp）和销售部的数据（dept），为什么缺少了呢？

因为在职员表中，白龙马没有职位，而且也没有职员在销售部。

从表的一项数据没有对应主表的一项数据，主表中的数据在从表中也找不到

这个时候就需要用到外连接

4.如何给查询所用到的表起别名？如何给查询的结果中的列起别名？

在from 后面操作：表1 别名1 ，表2 别名2   能给给查询所用到的表起别名

在查询的数据项后面操作，空格加别名   能给查询的结果中的列起别名

select t1.name 姓名,t1.gender 年龄,t2.dname 部门

from emp t1 , dept  t2

where t1.dep_id =t2.did;

5.显式连接简单，案例相较于隐式连接，有如何处不同？

select * from emp join dept on emp.dep_id=dept.did

使用join on代替  逗号when

左外连接，右外连接案例：

6.查询人员与职务关系，要求保留所有人员，没有对应人员的职务不显示

select t1.name,t1.gender,t2.dname from emp t1 LEFT JOIN dept t2

ON t1.dep_id = t2.did;

显示白龙马

select t1.name,t1.gender,t2.dname from emp t1 RIGHT JOIN dept t2

ON t1.dep_id = t2.did;

显示销售部

7.左外连接与右外连接分别使用什么关键字连接表？分别于内连接产生的数据有什么区别？

左外连接中，使用left join

查询的消息在内连接的基础上多了左边表中匹配不到右边的表中的数据

右外连接中，使用right join

查询的消息在内连接的基础上多了右边表中匹配不到左边的表中的数据

子查询

（子条件为单个数据）

1.查询工资高于猪八戒的员工信息

select * FROM emp where salary>=(select salary from emp where name='猪八戒');

（子条件为一列）

2.查询财务部和市场部所有员工信息

select * FROM emp where dep_id in (select did from dept where dname='财务部'or dname='市场部');

（子条件多行多列)

3.查询入职日期是2011-11-11之后的员工信息和部门信息

Select * from (select * FROM emp where join_date>'2011-11-11') t,dept where t.dep_id=dept.did;

先找出员工表中的员工与部门表，再与部门信息表连接即可

在这个案例中子查询作为多表查询的一部分

案例2和3都能够先连接再筛选查询 

---

十三.多表查询练习

准备数据

drop table if exists emp;
drop table if exists dept;
drop table if exists job;
drop table if exists salarygrade;
-- 部门表
CREATE TABLE dept (id INT PRIMARY KEY PRIMARY KEY, -- 部门iddname VARCHAR(50), -- 部门名称loc VARCHAR(50) -- 部门所在地
);
-- 职务表，职务名称，职务描述
CREATE TABLE job (id INT PRIMARY KEY,jname VARCHAR(20),description VARCHAR(50)
);
-- 员工表
CREATE TABLE emp (id INT PRIMARY KEY, -- 员工idename VARCHAR(50), -- 员工姓名job_id INT, -- 职务idmgr INT , -- 上级领导joindate DATE, -- 入职日期salary DECIMAL(7,2), -- 工资bonus DECIMAL(7,2), -- 奖金dept_id INT, -- 所在部门编号CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
);
-- 工资等级表
CREATE TABLE salarygrade (grade INT PRIMARY KEY,   -- 级别losalary INT,  -- 最低工资hisalary INT -- 最高工资
);	-- 添加4个部门
INSERT INTO dept(id,dname,loc) VALUES 
(10,'教研部','北京'),
(20,'学工部','上海'),
(30,'销售部','广州'),
(40,'财务部','深圳');
-- 添加4个职务
INSERT INTO job (id, jname, description) VALUES
(1, '董事长', '管理整个公司，接单'),
(2, '经理', '管理部门员工'),
(3, '销售员', '向客人推销产品'),
(4, '文员', '使用办公软件');
-- 添加员工
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES 
(1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20),
(1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),
(1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30),
(1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),
(1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
(1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),
(1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10),
(1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20),
(1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10),
(1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30),
(1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20),
(1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30),
(1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20),
(1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10);
-- 添加5个工资等级
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES 
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);

1.查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置（显式连接两种方式）

方法一：

select emp.id,emp.ename,emp.salary,job.jname,job.description,dept.dname,dept.loc from emp

join job on emp.job_id=job.id

join dept on emp.dept_id=dept.id;

方法二：

select emp.id,emp.ename,emp.salary,job.jname,job.description,dept.dname,dept.loc from emp join job join dept on emp.job_id=job.id and emp.dept_id=dept.id;                                                                                                                              

总结：当需要连接的表有三个时，显式连接有两种连接方式

a join b join c on 条件1 and 条件2

a join b on 条件1 join c on 条件2

-- 3.查询员工姓名，工资，工资等级

这个表不能直接有什么条件连接

方式一：

select emp.ename,emp.salary,salarygrade.grade from emp,salarygrade

where emp.salary BETWEEN salarygrade.losalary and salarygrade.hisalary;

首先要知道，如果只有前面的条件，那么，这两个表会作笛卡尔链接

后面的内容只是删除数据而已，不一定要两个表一定要有物理上的关联，别定死了 

方式二：根据工资进行子查询，子查询会对应外部查询中每一条数据的salary进行查询

select ename,salary,(select grade from salarygrade where salary BETWEEN losalary and hisalary ) grade from emp

-- 4.查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级

select emp.id,emp.ename,emp.salary,job.jname,job.description,

dept.dname,dept.loc,salarygrade.grade from emp

join job on emp.job_id=job.id 

join dept on emp.dept_id=dept.id

join salarygrade on emp.salary BETWEEN salarygrade.losalary and salarygrade.hisalary;

用这种显式连接更加清晰

-- 5.查询出部门编号、部门名称、部门位置、部门人数

方法一：先内连接，再根据部门分组，同时也能查找人数

SELECT dept.id,dept.dname,dept.loc,count(emp.id) from emp,dept

where dept.id=emp.dept_id group by dept.id;

方法二：先查询员工表找出部门id与人数的对应关系（子查询），而后连接

Select a.id,a.dname,a.loc,b.count

From dept a ,(Select dept_id,count(*) count from emp group by dept_id) b

Where a.id=b.dept_id;

这里count（*）需要起别名才可以，不然会报错

方法三：直接根据部门查询出单个sum的数据作为表中的元素

select id,dname,loc,(select count(*) from emp where dept_id=dept.id) 人数 from dept

 

---

十四.事务

要么同时成功，要么同时失败

比如转账，钱转出去了，但是收钱却没完成

1.sql语句：

开启事务：begin;或者Start transaction;之后，所有语句都是临时操作

撤回操作：rollback;

提交操作：commit;

数据准备：

DROP TABLE IF EXISTS `account`;
CREATE TABLE `account` (`id` int NOT NULL AUTO_INCREMENT,`name` varchar(10) DEFAULT NULL,`money` double(10,2) DEFAULT NULL,PRIMARY KEY (`id`)
);
INSERT INTO `account` VALUES ('1', '张三', '3000.00');
INSERT INTO `account` VALUES ('2', '李四', '1000.00');
INSERT INTO `account` VALUES ('3', '王五', '3000.00');
UPDATE account set money = 1000;
select * from account;

 实验sql：（出现异常备注处可删掉中间的空格制造异常实验）

-- 转账操作

-- 1. 查询李四账户金额是否大于500

-- 2. 李四账户 -500

begin;

UPDATE account set money = money - 500 where name = '李四';

-- 出现异常了...

-- 3. 张三账户 +500

UPDATE account set money = money + 500 where name = '张三';

-- 回滚事务

ROLLBACK;

-- 提交事务

COMMIT;

不手动制造错误的话数据库中数据不会变化（因为都rollback了，如果需要变动可以把rollback删除，从而使用后面的commit提交事务）

4.如果出现异常备注处出现异常，不断运行上面的sql，会发生什么？为什么？

李四账户不断减少钱（从第二次事务开始），张三钱不增加。

第一次不减少是因为事务没有处理（回滚或提交）

第二次开始减少是因为上一个未处理的事务被提交了

在同一个会话中，重新开启事务会commit未提交的事务

如果这个时候恰好这个事务里面出错了，那么这条错误的事务就会记录到数据库中