MySQL基础复习
-
SQL语句分类
1) Data Definition Language (DDL 数据定义语言) 如：建库，建表
2) Data Manipulation Language(DML 数据操纵语言)，如：对表中的记录操作增删改
3) Data Query Language(DQL 数据查询语言)，如：对表中的查询操作
4) Data Control Language(DCL 数据控制语言)，如：对用户权限的设置
-
1.1 DDL操作数据库
创建数据库
-- 直接创建数据库db1
create database db1; -- 判断是否存在，如果不存在则创建数据库db2
create database if not exists db2; -- 创建数据库并指定字符集为gbk
create database db3 default character set gbk;
查看数据库
-- 查看所有的数据库 show databases; -- 查看某个数据库的定义信息 show create database db3; show create database db1;
修改数据库
-- 将 db3 数据库的字符集改成 utf8 alter database db3 character set utf8;
删除数据库
-- 删除 db2 数据库 drop database db2;
使用数据库
-- 查看正在使用的数据库 select database(); -- 改变要使用的数据库 use db4;
-
1.2 DDL操作表结构
前提先使用某个数据库
amount DECIMAL(19,4); 这是保留4位小数的意思
create table student ( id int, -- 整数 name varchar(20), -- 字符串amountDECIMAL(19,4),birthday date -- 生日，最后没有逗号 );
查看表
desc 查看表结构
快速创建一个表结构相同的表 （这不熟）
语法 CREATE TABLE 新表名 LIKE 旧表名;
-- 创建一个s1的表与student结构相同 create table s1 like student; desc s1;
修改表结构
添加表列add
为学生表添加一个新的字段 remark,类型为 varchar(20)
alter table student add remark varchar(20);
修改列类型MODIFY
将 student 表中的 remark 字段的改成 varchar(100)
alter table student modify remark varchar(100);
修改列名 CHANGE
将 student 表中的 remark 字段名改成 intro，类型 varchar(30)
alter table student change remark intro varchar(30);
删除列 DROP
删除 student 表中的字段 intro
alter table student drop intro;
修改字符集character set
将 student2 表的编码修改成 gbk
alter table student2 character set gbk;
-
2.DML操作表中的数据
用于对表中的记录进行增删改操作
复制表的数据
什么是蠕虫复制： 将一张已经存在的表中的数据复制到另一张表中
将表名 2 中的所有的列复制到表名 1 中
INSERT INTO 表名 1 SELECT * FROM 表名 2;
只复制部分列
INSERT INTO 表名 1(列 1, 列 2) SELECT 列 1, 列 2 FROM student;
更新表记录 （用update不能用alert,alert是操作表的，表中数据修改只能用update）
-- 不带条件修改数据，将所有的性别改成女 update student set sex = '女'; -- 带条件修改数据，将id号为2的学生性别改成男 update student set sex='男' where id=2; -- 一次修改多个列，把id为3的学生，年龄改成26岁，address改成北京 update student set age=26, address='北京' where id=3;
删除表记录
-- 带条件删除数据，删除id为1的记录 delete from student where id=1; -- 不带条件删除数据,删除表中的所有数据 delete from student;
-
3.DQL查询表中的数据
查询不会对数据库中的数据进行修改.只是一种显示数据的方式
SELECT 列名 FROM 表名 [WHERE 条件表达式]
1) SELECT 命令可以读取一行或者多行记录。
2) 你可以使用星号（*）来代替其他字段，SELECT 语句会返回表的所有字段数据
3) 你可以使用 WHERE 语句来包含任何条件。
使用别名AS的好处： 显示的时候使用新的名字，并不修改表的结构。
-- 列使用别名 select name as 姓名,age as 年龄 from student; -- 表和列同时使用别名 select st.name as 姓名,age as 年龄 from student as st；-- st.name换成name也可以
清除重复值 （distinct 不熟）
-- 查询学生来至于哪些地方 select address from student; -- 去掉重复的记录 select distinct address from student;
查询结果参与运算
select * from student; -- 给所有的数学加 5 分 select math+5 from student; -- 查询 math + english 的和 select *,(math+english) as 总成绩 from student; -- as 可以省略
???这样是结果显示增加了一列叫总成绩的吗 是的
条件查询 (WHERE 条件)
为什么要条件查询 如果没有查询条件，则每次查询所有的行。实际应用中，一般要指定查询的条件。对记录进行过滤
重要！！！
-- 查询 age 大于 35 且性别为男的学生(两个条件同时满足) select * from student3 where age>35 and sex='男'; -- 查询 age 大于 35 或性别为男的学生(两个条件其中一个满足) select * from student3 where age>35 or sex='男'; -- 查询 id 是 1 或 3 或 5 的学生 select * from student3 where id=1 or id=3 or id=5;
in 关键字
SELECT 字段名 FROM 表名 WHERE 字段 in (数据 1, 数据 2...);
in 里面的每个数据都会作为一次条件，只要满足条件的就会显示
-- 查询id是1或3或5的学生 select * from student3 where id in(1,3,5); -- 查询id不是1或3或5的学生 select * from student3 where id not in(1,3,5);
范围查询
BETWEEN 值 1 AND 值 2
表示从值 1 到值 2 范围，包头又包尾
比如：age BETWEEN 80 AND 100 相当于： age>=80 && age<=100
--查询 english 成绩大于等于 75，且小于等于 90 的学生 select * from student3 where english between 75 and 90;
-- 查询姓马的学生 select * from student3 where name like '马%'; select * from student3 where name like '马'; -- 这个不行 这个只有名字叫马才能查出来 -- 查询姓名中包含'德'字的学生 select * from student3 where name like '%德%'; -- 这个不管开头结尾 包含就行 就叫德都行 -- 查询姓马，且姓名有两个字的学生 select * from student3 where name like '马_';
MySQL约束与设计
继续DQL查询语句
1.排序
通过 ORDER BY 子句，可以将查询出的结果进行排序(排序只是显示方式，不会影响数据库中数据的顺序)
SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名 [ASC|DESC];
ASC: 升序，默认值 DESC: 降序
-- 查询所有数据,使用年龄降序排序 select * from student order by age desc;
组合排序
什么是组合排序?
同时对多个字段进行排序，如果第 1 个字段相等，则按第 2 个字段排序，依次类推
-- 查询所有数据,在年龄降序排序的基础上，如果年龄相同再以数学成绩升序排序 select * from student order by age desc, math asc;
2.聚合函数
之前我们做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用聚合函数查询是纵向查询， 它是对一列的值进行计算，然后返回一个结果值。聚合函数会忽略空值 NULL。
语法： SELECT 聚合函数(列名) FROM 表名; （select后面使用）
-- 查询学生总数 select count(id) as 总人数 from student; select count(*) as 总人数 from student;
IFNULL(列名，默认值) 如果列名不为空，返回这列的值。如果为 NULL，则返回默认值
-- 查询id字段，如果为null，则使用0代替 select ifnull(id,0) from student; --我们可以利用 IFNULL()函数，如果记录为 NULL，给个默认值，这样统计的数据就不会遗漏 select count(ifnull(id,0)) from student;
3.分组 （查询结果分组展示）
分组查询是指使用 GROUP BY 语句对查询信息进行分组，相同数据作为一组
SELECT 字段 1,字段 2... FROM 表名 GROUP BY 分组字段 [HAVING 条件];
GROUP BY 将分组字段结果中相同内容作为一组，并且返回每组的第一条数据，所以单独分组没什么用处。 分组的目的就是为了统计，一般分组会跟聚合函数一起使用
-- 按性别进行分组，求男生和女生数学的平均分 select sex, avg(math) from student3 group by sex;
注意：当我们使用某个字段分组,在查询的时候也需要将这个字段查询出来,否则看不到数据属于哪组的
--查询年龄大于 25 岁的人,按性别分组,统计每组的人数 select sex, count(*) from student3 where age > 25 group by sex ;
只显示符合having条件的分组
-- 对分组查询的结果再进行过滤 --查询年龄大于 25 岁的人，按性别分组，统计每组的人数，并只显示性别人数大于 2 的数 SELECT sex, COUNT(*) FROM student3 WHERE age > 25 GROUP BY sex having COUNT(*) >2;
group by后面不能出现where，要使用having
4.limit语句 （不熟悉）超级有用啊啊啊啊
有用！！！！！！！！！！！！！！！这把offset设为变量 i++ 不就可以实现自动序列遍历嘛！！！！！！！！！！！！！！！！！！！
-- 查询学生表中数据，从第3条开始显示，显示6条。 select * from student3 limit 2,6;
LIMIT的使用场景： 分页：
比如我们登录京东，淘宝，返回的商品信息可能有几万条，不是一次全部显示出来。是一页显示固定的 条数。 假设我们每页显示 5 条记录的方式来分页
-- 如果第一个参数是0可以省略写： select * from student3 limit 5; -- 最后如果不够5条，有多少显示多少 select * from student3 limit 10,5;
数据库备份和还原
-- 备份day21数据库中的数据到d:\day21.sql文件中 mysqldump -uroot -proot day21 > d:/day21.sql
导出结果：数据库中的所有表和数据都会导出成 SQL 语句
数据库表的约束
通常不用业务字段作为主键，单独给每张表设计一个 id 的字段，把 id 作为主键。主键是给数据库和程序使用的，不是给最终的客户使用的。所以主键有没有含义没有关系，只要不重复，非空就行
疑问:如果一个字段设置了非空与唯一约束，该字段与主键的区别?
1) 主键数在一个表中，只能有一个。不能出现多个主键。主键可以单列，也可以是多列。
2) 自增长只能用在主键上
那我比较喜欢truncate!!!但不会用啊。。
默认值
设置了非空的话必须要有默认值！！！！这里出错好难发现。。。
-- 创建一个学生表 st9，包含字段(id,name,address)， 地址默认值是广州 create table st9 ( id int, name varchar(20), address varchar(20) default '广州'
)； -- 添加一条记录,使用默认地址 insert into st9 values (1, '李四', default);
select * from st9; insert into st9 (id,name) values (2, '李白'); -- 添加一条记录,不使用默认地址 insert into st9 values (3, '李四光', '深圳');
外键约束
什么是外键：在从表中与主表主键对应的那一列，
如：员工表中的 dep_id
主表： 一方，用来约束别人的表
从表： 多方，被别人约束的表
是给从表增加外键！！！emplotee是从表我晕。
新建表时增加外键： [CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)
已有表增加外键： ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主 键字段名);
-- 2) 创建从表 employee 并添加外键约束emp_depid_fk -- 多方，从表 create table employee( id int primary key auto_increment, name varchar(20), age int, dep_id int, -- 外键对应主表的主键 -- 创建外键约束 constraint emp_depid_fk foreign key (dep_id) references department(id) )
-- 删除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);
-- 创建外键约束 constraint emp_depid_fk foreign key (dep_id) references department(id) on update cascade on delete cascade )
什么是复合主键
主键是唯一的索引，通常会用ID做为主键，但是并不是每个表都存在ID字段，例如学生表（姓名，生日，性别，班级），这里面每一个值都可能重复，无法使用单一字段作为主键，这时我们可以将多个字段设置为复合主键，由复合主键标识唯一性。只要不是复合主键每个值都重复，就不算重复。（主键依然只有这一个）
--创建收藏表tab_favorite -- rid 旅游线路id，外键 --date 收藏时间 --uid用户id，外键 --rid和uid不能重复，设置复合主键，同一个用户不能收藏同一个线路两次 create table tab_favorite ( rid int, date datetime, uid int, -- 创建复合主键 primary key(rid,uid), foreign key (rid) references tab_route(rid), foreign key(uid) references tab_user(uid) )
数据库设计
数据规范化
什么是范式：
好的数据库设计对数据的存储性能和后期的程序开发，都会产生重要的影响。建立科学的，规范的数据库就需 要满足一些规则来优化数据的设计和存储，这些规则就称为范式。
三大范式：
简而言之，第一范式（1NF）每一列不可再拆分，称为原子性
第二范式（2NF）就是在第一范式的基础上所有列完全依赖于主键列。
第三范式就是所有列不依赖于其它非主键列，也就是在满足 2NF 的基础上，任何非主列不得传递依赖于主键。
------------------------------------------------------------------------------------------------------------------------------------
MySQL多表查询与事务的操作
表连接查询
内连接
用左边表的记录去匹配右边表的记录，如果符合条件的则显示。如：从表.外键=主表.主键
隐式内连接
隐式内连接：看不到 JOIN 关键字，条件使用 WHERE 指定：SELECT 字段名 FROM 左表, 右表 WHERE 条件
select * from emp,dept where emp.dept_id = dept.id;
显式内连接
显示内连接：使用 INNER JOIN ... ON 语句, 可以省略 INNER SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 条件
select * from emp e inner join dept d on e.dept_id = d.id;
--确定查询字段，查询唐僧的信息，显示员工 id，姓名，性别，工资和所在的部门名称 select e.`id`,e.`name`,e.`gender`,e.`salary`,d.`name` from emp e inner join dept d on e.`dept_id` = d.`id` where e.`name`='唐僧';
总结内连接查询步骤：
1) 确定查询哪些表
2) 确定表连接的条件
3) 确定查询的条件
4) 确定查询的字段
左外连接
左外连接：使用 LEFT OUTER JOIN ... ON，OUTER 可以省略
SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件
用左边表的记录去匹配右边表的记录，如果符合条件的则显示；
否则，显示 NULL
可以理解为：在内连接的基础上保证左表的数据全部显示(左表是部门，右表员工)
-- 使用内连接查询 select * from dept d inner join emp e on d.`id` = e.`dept_id`; -- 使用左外连接查询 select * from dept d left join emp e on d.`id` = e.`dept_id`;
对比结果：（内连接是红线部分，只显示了id与dept_id相等的。左外连接是全部部分，保证了dept这个左表全部显示了。）
右外连接
右外连接：使用 RIGHT OUTER JOIN ... ON，OUTER 可以省略
SELECT 字段名 FROM 左表 RIGHT [OUTER ]JOIN 右表 ON 条件
用右边表的记录去匹配左边表的记录，如果符合条件的则显示；
否则，显示 NULL
可以理解为：在内连接的基础上保证右表的数据全部显示
-- 使用内连接查询 select * from dept inner join emp on dept.`id` = emp.`dept_id`; -- 使用右外连接查询 select * from dept right join emp on dept.`id` = emp.`dept_id`;
子查询（两张表！有点难了）
-- 需求：查询开发部中有哪些员工 select * from emp; -- 通过两条语句查询 select id from dept where name='开发部' ; select * from emp where dept_id = 1; -- 使用子查询 select * from emp where dept_id = (select id from dept where name='开发部');
子查询的概念：
1) 一个查询的结果做为另一个查询的条件
2) 有查询的嵌套，内部的查询称为子查询
3) 子查询要使用括号
子查询的结果是一个值的时候
子查询结果只要是单行单列，肯定在 WHERE 后面作为条件，父查询使用：比较运算符，如：> 、<、<>、 = 等
SELECT 查询字段 FROM 表 WHERE 字段 =（子查询）;
-- 根据最高工资到员工表查询到对应的员工信息 select * from emp where salary = (select max(salary) from emp);
-- 到员工表查询薪水小于平均的员工信息 select * from emp where salary < (select avg(salary) from emp);
子查询结果是多行单列的时候
子查询结果是单例多行，结果集类似于一个数组，父查询使用 IN 运算符
SELECT 查询字段 FROM 表 WHERE 字段 IN （子查询）;
--查询工资大于5000的员工，来自于哪些部门的名字
-- 先查询大于5000的员工所在的部门id
-- 再查询在这些部门id中部门的名字
select name from dept where id in (select dept_id from emp where salary > 5000);
--查询开发部与财务部所有的员工信息
-- 先查询开发部与财务部的id
-- 再查询在这些部门id中有哪些员工
select * from emp where dept_id in (select id from dept where name in('开发部','财务部'));
子查询的结果是多行多列
子查询结果只要是多列，肯定在 FROM 后面作为表
SELECT 查询字段 FROM （子查询） 表别名 WHERE 条件;
子查询作为表需要取别名，否则这张表没有名称则无法访问表中的字段
-- 查询出2011年以后入职的员工信息，包括部门名称
-- 在员工表中查询2011-1-1以后入职的员工 select * from emp where join_date >='2011-1-1'; -- 查询所有的部门信息，与上面的虚拟表中的信息组合，找出所有部门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';
事务
事务执行是一个整体，所有的 SQL 语句都必须执行成功。如果其中有 1 条 SQL 语句出现异常，则所有的 SQL 语句都要回滚，整个业务执行失败。（所以事务是使sql语句为整体执行结果的）
MYSQL 中可以有两种方式进行事务的操作：
1) 手动提交事务
2) 自动提交事务
自动提交事务
MySQL 默认每一条 DML(增删改)语句都是一个单独的事务，每条语句都会自动开启一个事务，语句执行完毕 自动提交事务，MySQL 默认开始自动提交事务
事务原理
事务开启之后, 所有的操作都会临时保存到事务日志中, 事务日志只有在得到 commit 命令才会同步到数据表中，其他任何情况都会清空事务日志(rollback，断开连接)
回滚点
5.5.1 什么是回滚点
在某些成功的操作完成之后，后续的操作有可能成功有可能失败，但是不管成功还是失败，前面操作都已经成功，可以在当前成功的位置设置一个回滚点。可以供后续失败操作返回到该位置，而不是返回所有操作，这个点称之为回滚点（就像是玩游戏的cheakpoint检查点）
事务的四大特性ACID
事务的隔离级别
事务在操作时的理想状态： 所有的事务之间保持隔离，互不影响。
但因为并发操作，多个用户同时访问同一个数据。可能引发并发访问的问题
MySQL数据库有四种隔离级别（重要的！！考到了！！！原来是这里。。。）
上面的级别最低，下面的级别最高。“是”表示会出现这种问题，“否”表示不会出现这种问题。
MySQL事务隔离级别相关的命令
查询全局事务隔离级别 查询隔离级别
select @@tx_isolation;
设置事务隔离级别，需要退出 MySQL 再重新登录才能看到隔离级别的变化
设置隔离级别
set global transaction isolation level 级别字符串（就那4个 read uncommitted,read committed,repeatable read,serializable）;
脏读非常危险的，比如张三向李四购买商品，张三开启事务，向李四账号转入 500 块，然后打电话给李四说钱 已经转了。李四一查询钱到账了，发货给张三。张三收到货后回滚事务，李四的再查看钱没了。
解决脏读的问题：将全局的隔离级别进行提升
两次查询输出的结果不同，到底哪次是对的？不知道以哪次为准。 很多人认为这种情况就对了，无须困惑， 当然是后面的为准。我们可以考虑这样一种情况，比如银行程序需要将查询结果分别输出到电脑屏幕和发短信给客户，结果在一个事务中针对不同的输出目的地进行的两次查询不一致，导致文件和屏幕中的结果不一致，银行工作人员就不知道以哪个为准了。
解决不可重复读的问题： 将全局的隔离级别进行提升为：repeatable read
同一个事务中为了保证多次查询数据一致，必须使用 repeatable read 隔离级别
在 MySQL 中无法看到幻读的效果。 但我们可以将事务隔离级别设置到最高，以挡住幻读的发生
使用serializable隔离级别，一个事务没有执行完，其他事务的SQL执行不了，可以挡住幻
关于使用什么关键字的总结：
DDL 库和表： create / alter / drop
DML 增删改表中数据：insert /update/delete
DQL 查询数据：select /show
DCL ：grant /revoke
关于DCL 数据库控制：
1.创建用户
--创建 user1 用户，只能在 localhost 这个服务器登录 mysql 服务器，密码为 123 create user 'user1'@'localhost' identified by '123';
--创建 user2 用户可以在任何电脑上登录 mysql 服务器，密码为 123 create user 'user2'@'%' identified by '123';
2.给用户授权
--给 user1 用户分配对 test 这个数据库操作的权限：创建表，修改表，插入记录，更新记录，查询 grant create,alter,insert,update,select on test.* to 'user1'@'localhost'; -- 注：用户名和主机名要与上面创建的相同，要加单引号。 -- 给 user2 用户分配所有权限，对所有数据库的所有表 grant all on *.* to 'user2'@'%';
3.撤销授权
--撤销 user1 用户对 test 数据库所有表的操作的权限 revoke all on test.* from 'user1'@'localhost';
4.查看权限
语法： SHOW GRANTS FOR '用户名'@'主机名';
5.删除用户
--删除 user2 drop user 'user2'@'%';
6.修改管理员密码
语法： mysqladmin -uroot -p password 新密码
注意：需要在未登陆 MySQL 的情况下操作，新密码不需要加上引号。
7.修改普通用户密码
语法： set password for '用户名'@'主机名' = password('新密码');
注意：需要在登陆 MySQL 的情况下操作，新密码要加单引号。
