2020-2-23

我们发现对于 NULL 的记录不会统计，建议如果统计个数则不要使用有可能为 null 的列，但如果需要把 NULL 也统计进去呢？

我们可以利用 IFNULL()函数，如果记录为 NULL，给个默认值，这样统计的数据就不会遗漏

select count(ifnull(id,0)) from student;

GROUP BY 将分组字段结果中相同内容作为一组，并且返回每组的第一条数据，所以单独分组没什么用处。 分组的目的就是为了统计，一般分组会跟聚合函数一起使用。

-- 按性别进行分组，求男生和女生数学的平均分
select sex, avg(math) from student3 group by sex;

注意：当我们使用某个字段分组,在查询的时候也需要将这个字段查询出来,否则看不到数据属于哪组的

查询男女各多少人 1) 查询所有数据,按性别分组。 2) 统计每组人数

select sex, count(*) from student3 group by sex;

查询年龄大于 25 岁的人，按性别分组，统计每组的人数，并只显示性别人数大于 2 的数据

-- 对分组查询的结果再进行过滤
SELECT sex, COUNT(*) FROM student3 WHERE age > 25 GROUP BY sex having COUNT(*) >2;

LIMIT 语法格式：LIMIT offset,length; offset：起始行数，从 0 开始计数，如果省略，默认就是 0, length： 返回的行数 -- 查询学生表中数据，从第 3 条开始显示，显示 6 条。

select * from student3 limit 2,6;

数据库备份和还原

备份操作：

-- 备份 day21 数据库中的数据到 d:\day21.sql 文件中
mysqldump -uroot -proot day21 > d:/day21.sql

还原操作

还原 day21 数据库中的数据，注意：还原的时候需要先登录 MySQL,并选中对应的数据库。

1. 删除 day21 数据库中的所有表

2. 登录 MySQL

3. 选中数据库

4. 使用 SOURCE 命令还原数据

5. 查看还原结果

   use day21; source d:/day21.sql;

删除增加主键

-- 删除 st5 表的主键 
alter table st5 drop primary key; 
-- 添加主键 
alter table st5 add primary key(id);

修改自增长的默认值起始值

默认地 AUTO_INCREMENT 的开始值是 1，如果希望修改起始值,请使用下列 SQL 语法

-- 指定起始值为 1000
create table st4 (
 id int primary key auto_increment,
 name varchar(20)
) auto_increment = 1000;
insert into st4 values (null, '孔明');
select * from st4;

创建好以后修改起始值
alter table st4 auto_increment = 2000;

DELETE 和 TRUNCATE 对自增长的影响

• DELETE：删除所有的记录之后，自增长没有影响。

• TRUNCATE：删除以后，自增长又重新开始。

唯一约束

null 没有数据，不存在重复的问题

数据库约束的概述

约束名 约束关键字
主键 primary key
唯一 unique
非空 not null
外键 foreign key
检查约束 check 注：mysql 不支持

默认值

-- 创建一个学生表 st9，包含字段(id,name,address)， 地址默认值是广州
create table st9 (
 id int,
 name varchar(20),
 address varchar(20) default '广州'
)

疑问:如果一个字段设置了非空与唯一约束，该字段与主键的区别?

1. 主键数在一个表中，只能有一个。不能出现多个主键。主键可以单列，也可以是多列。

2. 自增长只能用在主键上

创建外键约束的语法

[CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)
ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主
键字段名);

删除外键

-- 删除 employee 表的 emp_depid_fk 外键
alter table employee drop foreign key emp_depid_fk;
-- 在 employee 表情存在的情况下添加外键
alter table employee add constraint emp_depid_fk
foreign key (dep_id) references department(id);

级联操作

在修改和删除主表的主键时，同时更新或删除副表的外键值，称为级联操作

级联操作语法 描述
ON UPDATE CASCADE 级联更新，只能是创建表的时候创建级联关系。更新主表中的主键，从表中的外键 列也自动同步更新
ON DELETE CASCADE 级联删除

数据库设计

范式 特点
1NF 原子性：表中每列不可再拆分。
2NF 不产生局部依赖，一张表只描述一件事情
3NF 不产生传递依赖，表中每一列都直接依赖于主键。而不是通过其它列间接依赖于主键。

 -- 创建复合主键 
 primary key(rid,uid),

笛卡尔积现象

-- 需求：查询所有的员工和所有的部门
select * from emp,dept;

什么是子查询

子查询的概念：

• 1. 一个查询的结果做为另一个查询的条件
• 2. 有查询的嵌套，内部的查询称为子查询
• 3. 子查询要使用括号

子查询的结果是一个值的时候

• 子查询结果只要是单行单列，肯定在 WHERE 后面作为条件，父查询使用：比较运算符，如：> 、<、<>、= 等

  -- 1) 查询最高工资是多少
  select max(salary) from emp;
  -- 2) 根据最高工资到员工表查询到对应的员工信息
  select * from emp where salary = (select max(salary) from emp);

子查询结果是多行单列的时候

子查询结果是单例多行，结果集类似于一个数组，父查询使用 IN 运算符

-- 先查询大于 5000 的员工所在的部门 id
select dept_id from emp where salary > 5000;
-- 再查询在这些部门 id 中部门的名字 Subquery returns more than 1 row
select name from dept where id = (select dept_id from emp where salary > 5000);
select name from dept where id in (select dept_id from emp where salary > 5000);

子查询的结果是多行多列

子查询作为表需要取别名，否则这张表没有名称则无法访问表中的字段

-- 查询所有的部门信息，与上面的虚拟表中的信息组合，找出所有部门 id 等于的 dept_id
select * from dept d, (select * from emp where join_date >='2011-1-1') e where
d.`id`= e.dept_id ;

-- 也可以使用表连接：
select * from emp inner join dept on emp.`dept_id` = dept.`id` where
join_date >='2011-1-1';
select * from emp inner join dept on emp.`dept_id` = dept.`id` and
join_date >='2011-1-1';

子查询小结

• 子查询结果只要是单列，则在 WHERE 后面作为条件
• 子查询结果只要是多列，则在 FROM 后面作为表进行二次查询

手动提交事务

MYSQL 中可以有两种方式进行事务的操作：

• 1. 手动提交事务
• 2. 自动提交事务

手动提交事务的 SQL 语句

功能 SQL 语句
开启事务 start transaction;
提交事务 commit;
回滚事务 rollback

MySQL 自动提交事务

-- @@表示全局变量，1 表示开启，0 表示关闭
select @@autocommit;

set @@autocommit=0;

事务的步骤：

1. 客户端连接数据库服务器，创建连接时创建此用户临时日志文件

2. 开启事务以后，所有的操作都会先写入到临时日志文件中

3. 所有的查询操作从表中查询，但会经过日志文件加工后才返回

4. 如果事务提交则将日志文件中的数据写到表中，否则清空日志文件。

回滚点

在某些成功的操作完成之后，后续的操作有可能成功有可能失败，但是不管成功还是失败，前面操作都已经成 功，可以在当前成功的位置设置一个回滚点。可以供后续失败操作返回到该位置，而不是返回所有操作，这个点称 之为回滚点。

回滚点的操作语句

回滚点的操作语句 语句
设置回滚点 savepoint 名字
回到回滚点 rollback to 名字

事务的隔离级别

事务在操作时的理想状态： 所有的事务之间保持隔离，互不影响。因为并发操作，多个用户同时访问同一个 数据。可能引发并发访问的问题：

并发访问的问题 含义
脏读 一个事务读取到了另一个事务中尚未提交的数据
不可重复读 一个事务中两次读取的数据内容不一致，要求的是一个事务中多次读取时数据是一致的，这 是事务 update 时引发的问题
幻读 一个事务中两次读取的数据的数量不一致，要求在一个事务多次读取的数据的数量是一致 的，这是 insert 或 delete 时引发的问题

MySQL 数据库有四种隔离级别

名字 隔离级别 脏读 不可重复读 幻读 数据库默认隔离级别
读未提交 read uncommitted 是 是 是
读已提交 read committed 否 是 是 Oracle 和 SQL Server
可重复读 repeatable read 否 否 是 MySQL
串行化 serializable 否 否 否

隔离级别越高，性能越差，安全性越高。

查询全局事务隔离级别

-- 查询隔离级别 
select @@tx_isolation;
-- 设置隔离级别
set global transaction isolation level 级别字符串;

创建用户

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

关键字 说明
用户名 将创建的用户名
主机名 指定该用户在哪个主机上可以登陆，如果是本地用户可用 localhost，如果想让该用户可以 从任意远程主机登陆，可以使用通配符%
密码 该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器

create user 'user1'@'localhost' identified by '123';

create user 'user2'@'%' identified by '123

给用户授权 用户创建之后，没什么权限！需要给用户授权

GRANT 权限 1, 权限 2... ON 数据库名.表名 TO '用户名'@'主机名';

关键字 说明
GRANT…ON…TO 授权关键字
权限 授予用户的权限，如 CREATE、ALTER、SELECT、INSERT、UPDATE 等。如果要授 予所有的权限则使用 ALL
数据库名.表名 该用户可以操作哪个数据库的哪些表。如果要授予该用户对所有数据库和表的相应操作 权限则可用表示，如.*
'用户名'@'主机名' 给哪个用户授权，注：有 2 对单引号

-- 给 user1 用户分配对 test 这个数据库操作的权限：创建表，修改表，插入记录，更新记录，查询
grant create,alter,insert,update,select on test.* to 'user1'@'localhost';

-- 给 user2 用户分配所有权限，对所有数据库的所有表
grant all on *.* to 'user2'@'%';

撤销授权

revoke all on test.* from 'user1'@'localhost'; 

关键字 说明
REVOKE…ON…FROM 撤销授权的关键字
权限 用户的权限，如 CREATE、ALTER、SELECT、INSERT、UPDATE 等，所有的权 限则使用 ALL
数据库名.表名 对哪些数据库的哪些表，如果要取消该用户对所有数据库和表的操作权限则可用表 示，如.*
'用户名'@'主机名' 给哪个用户撤销

-- 撤销 user1 用户对 test 数据库所有表的操作的权限
revoke all on test.* from 'user1'@'localhost';

查看权限

SHOW GRANTS FOR '用户名'@'主机名';

删除用户

DROP USER '用户名'@'主机名';

修改管理员密码

mysqladmin -uroot -p password 新密码

修改普通用户密码

set password for '用户名'@'主机名' = password('新密码');