MySQL基础复习

2022-09-15 20:58:11

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),  -- 字符串 
amount DECIMAL(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 的情况下操作，新密码要加单引号。

MySQL基础复习

码农公寓

SQL语句分类

1.1 DDL操作数据库

1.2 DDL操作表结构

2.DML操作表中的数据

3.DQL查询表中的数据