MySQL（从零到亿）

支蛛网

已于 2024-07-23 13:49:01 修改

阅读量361

点赞数 7

文章标签： mysql 数据库

于 2024-07-16 16:31:41 首次发布

本文链接：https://blog.csdn.net/a585555/article/details/140470165

版权

一、MySQL概述

数据库相关概念

主流的关系型数据库管理系统

Oracle·············收费

Mysql··············开源

Microsoft SQL Server·············收费

PostgreSQL·············开源免费的中小型数据库

1.数据库

数据存储的仓库

2.数据库管理系统

操纵和管理数据库的大型软件

3.SQL

操作关系熊数据库的编程语言，是一套标准

MySQL数据库安装及启动

安装

1、进入官网点击download

2、有个MySQL Community(GPL) Downloads点进去

3、然后找到Mysql Install for windows

4、下载第二个大的

5、打开安装包

6、

7、

8、

9、

10、

11、

12、

设置root密码后点击下一步

13、

14、

15、安装完后点击finish再点击Cancel就安装完了

启动与停止

方法一、win+r输入services.msc 然后找到MySQL80右键启动或停止

方法二、在命令提示符输入net start mysql80可以启动服务 net stop mysql80可以关闭

默认mysql是开机自启动的

客户端连接

方法一、在开始菜单里使用MySQL提供的客户端命令行工具MySQL 8.0 Command Line Client

方法二、在命令提示符中输入命令 mysql [-h 127.0.0.1] [-P 3306] -u root -p

-h指定的是连接的ip

-P指定的是连接的端口

-u表示的是连接的用户

-p表示的是连接用户的密码

使用这种方式时要配置PATH环境变量

1、右键电脑，点击属性，找到高级系统设置然后点击环境变量

2、在系统变量中找到Path点击编辑，然后新建变量，输入MySQL server8.0里的bin目录将路径复制粘贴过去，默认是在C:\Program Files\MySQL\MySQL Server 8.0\bin

3、然后点击确定就配置完成了

关系型数据库（RDBMS）

概念：建立在关系模型的基础上，由多张互相连接的二维表组成的数据库

特点：

1、使用表储存数据，格式统一，便于维护

2、使用SQL语言操作，标准统一，使用方便

数据模型

二、SQL

SQL通用语法

1.SQL语句可以单行或多行书写，以分号结尾。

2.SQL语句可以使用空格/缩进来增强语句的可读性。

3.MySQL数据库的SQL语句不区分大小写，关键字建议用大写。

4.注释：

单行注释：--注释内容或 # 注释内容（MySQL特有）

多行注释：/* 注释内容 */

SQL分类

分类全称说明

DDL Data Definition Language 数据定义语言，用来定义数据库对象（数据库，表，字段）

DML Data Manipulation Language 数据操作语言，用于对表中数据进行增删改

DQL Data Query Language 数据查询语言，用来查询数据库中表的记录

DCL Data Control Language 数据控制语言，用来创建数据库用户、控制数据库的访问权限

DDL

DDL-数据库操作

查询

查询所有数据库

SHOW DATABASES;

查询当前数据库

SELECT DATABASE();

当你执行了很多SQL语句忘记现在在哪的时候就可以用这条语句

注意！！！！！！！后面的()不能少

创建

CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];

方括号里面的内容是可选的

create database spider; 表示创建一个叫spider的数据库

这个时候再想创建一个spider数据库会报错，因为已经存在

如果不想让他报错可以用这个语句

create database if not exists spider;

这句话的意思是，如果不存在则创建，如果存在则不创建

create database hello default charset utf8mb4;

表示创建一个hello数据库，他的字符集为utf8mb4

不用utf8的原因是他的存储长度是三个字节，但是数据库中有些特殊字符是占四个字节的

utf8mb4是四个字节的

删除

DROP DATABASE [IF EXISTS] 数据库名;

drop database test;

表示删除test数据库

这个时候如果再删除不存在的test将会报错

如果不想报错可以用这个语法：
drop database if exists test;

表示如果存在就删除，如果不存在就不删除

使用

USE 数据库名;

创建完一个数据库后如果使用就用use语句

use spider;

这个时候弹出Database changed就代表切换成功

DDL-表操作

查询

查询当前数据库所有表

SHOW TABLES;

前提是要用use先进入到这个数据库

查询表结构

DESC 表名;

查询指定表的建表语句

SHOW CREATE TABLE 表名;

创建

 CREATE TABLE 表名(
    字段1 字段1类型 [COMMENT 字段1注释],
    字段2 字段2类型 [COMMENT 字段2注释],
    字段3 字段3类型 [COMMENT 字段3注释],
    ....
    字段n 字段n类型 [COMMENT 字段n注释]
)[COMMENT 表注释];

创建一个tb_user的表

create table tb_user(
id int comment '编号',
name varchar(50) comment '姓名',
age int comment '年龄',
gender int comment '性别'
)comment '用户表';

数据类型

MySQL中的数据类型有很多，主要分为三类：数值类型，字符串类型，日期时间类型

数值类型

字符串类型

日期类型

示例：

创建一张员工表，要求如下

1.编号（纯数字）
2.员工工号（字符串类型，长度不超过10位）
3.员工姓名（字符串类型，长度不超过10位）
4.性别（男/女，存储一个汉字）
5.年龄（正常人年龄，不可能储存负数）
6.身份证号（二代身份证均为18位，身份证中有X这样的字符）
7.入职时间（取年月日即可）
create table emp(
    id int comment '编号',
    workno varchar(10) comment '员工工号',
    name varchar(10) comment '员工姓名',
    gender char(1) comment '性别',
    age tinyint unsigned comment '年龄',
    idcard char(18) comment '身份证',
    entrydate date comment '入职时间'

)comment '员工表';

修改

添加字段

ALTER TABLE 表名 ADD 字段名 类型(长度) [comment 注释] [约束];

示例：
为emp表添加一个新的字段"昵称"为nickname,类型为varchar(20)

alter table emp add nickname varchar(20) comment '昵称'

修改字段

修改数据类型

ALTER TABLE 表名 MODIFY 字段名 心数据类型(长度) [comment 注释];

修改字段名和字段类型

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [comment 注释] [约束];

示例：

将emp表的nickname字段修改为username,类型为varchar(30)

ALTER TABLE emp CHANGE nickname username varchar(30) comment '用户名';

删除字段

ALTER TABLE 表名 DROP 字段名;

示例：

将emp表的字段username删除

alter table emp drop username;

修改表名

ALTER TABLE 表名 RENAME TO 新表名;

示例：

将emp表的表名修改为employee

alter table emp rename to employee

删除

删除表

DROP TABLE [IF EXISTS] 表名;

删除指定表，并重新创建该表（格式化）

TRUNCATE TABLE 表名;

DML

添加数据(INSERT)

1、给指定的字段添加数据

INSERT INTO 表名 (字段名1,字段名2,......) VALUES(值1,值2,.....);

2、给全部字段添加数据

INSERT INTO 表名 VALUES (值1,值2,.....);

3、批量添加数据

INSERT INTO 表名 (字段名1,字段名2,......) VALUES (值1,值2，......),(值1,值,......),(值1,值2,......);

INSERT INTO 表名 VALUES (值1,值2,...),(值1,值2,...),(值1,值2,...)；

注意：

插入数据时，指定的字段顺序需要和值的顺序是一一对应的。

字符串和日期型数据应该包含在引号中。

插入的数据大小，应该在字段的规定范围内。

示例：

1、往tb_user中添加指定的数据

insert into tb_user(id,name,age,gender) values (1,'令狐冲',26,'男');

2、往tb_user全部字段添加数据

insert into tb_user values(2,'张无忌',25,'男');

3、往tb_user批量添加字段数据

insert into tb_user values(3,'张三丰',33,'男'),(4,'赵敏',27,'女');

修改数据(UPDATE)

UPDATE 表名 SET 字段名1=值1,字段名2=值2,.....[WHERE 条件];

注意：

修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。

示例：

1、将id为1的数据name修改为spider

update tb_user set name = 'spider' where id=1;

2、将id为1的数据name修改为小貂，gender修改为女

update tb_user set name = '小貂',gender = '女' where id = 1;

3、将所有人的年龄都修改为24岁

update tb_user set age = 24;

不填where条件时就代表修改整张表的数据

删除数据(DELETE)

DELETE FROM 表名 [WHERE 条件];

注意：DELETE语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据

DELETE语句不能删除某一个字段的值(可以使用UPDATE)

示例：

1、删除gender为女的人物

delete from tb_user where gender = '女';

2、删除所有人物

delete from tb_user;

DQL

查询关键字：SELECT

语法：

SELECT

字段列表

FROM

表名列表

WHERE

条件列表

GROUP BY

分组字段列表

HAVING

分组后条件列表

ORDER BY

排序字段列表

LIMIT

分页参数

基本查询

1、查询多个字段

SELECT 字段1,字段2,字段3... FROM 表名;
SELECT * FROM 表名;

2、设置别名

SELECT 字段1 [AS 别名1],字段2 [AS 别名 2] ....FROM 表名

3去除重复记录

SELECT DISTINCT 字段列表 FROM 表名;

示例：

insert into emp values(1,'1','柳岩','女',20,'123456789012345678','北京','2000-01-01'),
(2,'2','张无忌','男',18,'123456789012345670','北京','2005-09-01'),
(3,'3','韦一笑','男',38,'123456789712345670','上海','2005-08-01'),
(4,'4','赵敏','女',18,'123456757123845670','北京','2009-12-01'),
(5,'5','小昭','女',16,'123456769012345678','上海','2007-07-01'),
(6,'6','杨道','男',28,'12345678931234567X','北京','2006-01-01'),
(7,'7','范瑶','男',40,'123456789212345670','北京','2005-05-01'),
(8,'8','黛绮丝','女',38,'123456157123645670','天津','2015-05-01'),
(9,'9','范凉凉','女',45,'123156789012345678','北京','2010-04-01'),
(10,'10','陈友谅','男',53,'123456789012345670','上海','2011-01-01'),
(11,'11','张士诚','男',55,'123567897123465670','江苏','2015-05-01'),
(12,'12','常遇春','男',32,'123446757152345670','北京','2004-02-01'),
(13,'13','张三丰','男',88,'123656789012345678','江苏','2020-11-01'),
(14,'14','灭绝','女',65,'123456719012345670','西安','2019-05-01'),
(15,'15','胡青牛','男',70,'12345674971234567X','西安','2018-04-01'),
(16,'16','周芷若','女',18,null,'北京','2012-06-01');

1、查询指定字段 name，workno，age 返回

select name,workno,age from emp;

2、查询所有字段返回

select * from emp;

3、查询所有员工的工作地址，起别名

select workaddress as '工作地址' from emp;

as 可以省略

select workaddress '工作地址' from emp;

4、查询公司员工的上班地址（不要重复）

select distinct workaddress from emp;

条件查询（WHERE）

1、语法

SELECT 字段列表 FROM 表名 WHERE 条件列表;

2、条件

示例：

1、查询年龄等于88的员工

select * from emp where age = 88;

2、查询年龄小于20的员工信息

select * from emp where age < 20;

3、查询年龄小于等于20的员工信息

select * from emp where age <= 20;

4、查询没有身份证号的员工信息

select * from emp where idcard is NULL;

5、查询有身份证号的员工信息

select * from emp where idcard is not NULL;

6、查询年龄不等于88的员工信息

select * from emp where age != 88;

7、查询年龄在15岁(包含)到20岁(包含)之间的员工信息

select * from emp where age >= 15 and age <= 20;

8、查询性别为女且年龄小于25的员工信息

select * from emp where gender = '女' and age < 25;

9、查询年龄等于 18 或 20 或 40 的员工信息

select * from emp where age = 18 or age = 20 or age = 40;

select * from emp where age in (18,20,40);

10、查询姓名为两个字的员工信息

select * from emp where name like '__';

11、查询身份证号最后一位是X的员工信息

select * from emp where idcard like '%X';

也可以用_来完成

select * from emp where idcard like '_________________X';

聚合函数（count、max、min、avg、sum）

1、什么是聚合函数

将一列数据作为一个整体，进行纵向计算

2、常见聚合函数

3、语法

SELECT 聚合函数(字段列表) FROM 表名;

所有的null值不参与所有聚合函数运算

示例：

1、统计该企业员工数量

select count(*) from emp;

2、统计该企业员工的平均年龄

select avg(age) from emp;

3、统计该企业员工的最大年龄

select max(age) from emp;

4、统计该企业员工的最小年龄

select min(age) from emp;

5、统计西安地区员工的年龄之和

select sum(age) from emp where workaddress = '西安';

分组查询（GROUP BY）

1、语法

SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件]

2、WHERE和HAVING区别

执行时机不同：WHERE是分组之前进行过滤，不满足WHERE条件，不参与分组；而HAVING是分组之后对结果进行过滤

判断条件不同：WHERE不能对聚合函数进行判断，而HAVING可以

注意：

执行顺序：where > 聚合函数 > having

分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

示例：

1、根据性别分组，统计男性员工和女性员工的数量

select gender,count(*) from emp group by gender;

2、根据性别分组，统计男性员工和女性员工的平均年龄

select gender,avg(age) from emp group by gender;

3、查询年龄小于45的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址

select workaddress,count(*) from emp where age<45 group by workaddress having count(*)>=3


select workaddress,count(*) as address_count from emp where age<45 group by workaddress having address_count >= 3;

排序查询（ORDER BY）

1、语法

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1 ，字段2 排序方式2;

2、排序方式

ASC：升序(默认值)

DESC：降序

注意：

如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序

示例：

1、根据年龄对公司员工进行升序排序

select * from emp order by age asc;

2、根据入职时间，对员工进行降序排序

select * from emp order by desc;

3、根据年龄对公司员工进行升序排序，年龄相同，再按照入职时间进行降序排序。

select * from emp order by age asc , entrydate desc

分页查询（LIMIT）

1、语法

SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;

注意

起始索引从0开始，起始索引=(索引页码-1) * 每页显示记录数;

分页查询是数据库的方言，不同数据可以有不同的实现，MySQL中是LIMIT。

如果查询的是第一页数据，起始索引可以省略，直接简写为LIMIT 10。

示例：

1、查询第1页员工数据，每页展示10条记录。

select * from emp limit 0,10;

也可以简写

select * from emp limit 10;

2、查询第2页员工数据，每页展示10条记录。

select * from emp limit 10,10;

DQL案例需求：

1、查询年龄为20,21,22,23岁的员工信息。

select * from emp where gender = '女' and age in(20,21,22,23);

2、查询性别为男，并且年龄在20-40岁(含)以内的姓名为三个字的员工。

select * from emp where gender = '男' and age between 20 and 40 and name like '___';

select * from emp where gender = '男' and (age>=20 and age <=40) and name like '___';

3、统计员工表中，年龄小于60岁的，男性员工和女性员工的人数。

select gender,count(*) from emp where < 60 group by gender;

4、查询所有年龄小于等于35岁的员工的姓名和年龄，并对查询结果按年龄升序排序，如果年龄相同按入职时间降序排序。

select name,age from emp where age <= 35 order by age,entrydate desc;

5、查询性别为男，且年龄为20-40岁(含)以内的前5个员工信息，对查询的结果按年龄升序排序，年龄相同按入职时间升序排序。

select * from emp where gender = '男' and (age>=20 adn age <= 40) order by age asc,entrydate acs limit 5;

DQL-执行顺序

查询年龄大于15的员工姓名、年龄，并根据年龄进行升序排序

一行代码自己领悟

select e.name ename,e.age eage from emp e where e.age > 15 order by eage;

1、from

2、where

3、select

4、order by

DCL

管理用户

1、查询用户

USE mysql;

SELECT * FROM user;

2、创建用户

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

3、修改用户密码

ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码'

注意：

主机名可以使用%通配。

这类SQL开发人员操作的比较少，主要是DBA(Database Administrator 数据库管理员)使用。

示例：

1、创建用户itcast，只能够在当前主机localhost访问，密码123456;

create user 'itcast'@'localhost' identified by '123456';

2、创建用户spider，可以在任意主机访问数据库，密码123456;

create user 'spider'@'%' identified '123456'

3、修改用户spider的访问密码为1234;

alter user 'spider'@'%' identified with mysql_native_password by '1234';

4、删除itcast@localhost用户;

drop user 'itcast'@'localhost';

权限控制

1、查询权限

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

2、授予权限

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

3、撤销权限

REVOKE 权限列表 ON 数据库.表名 FROM '用户名'@'主机名'

注意：

多个权限之间，使用逗号分割。

授权时，数据库和表名可以使用*进行统配，代表所有。

示例：

1、查询spider权限

show grants for 'spider'@'%';

2、授予spider所有权限

grant all on spider.* to 'spider'@'%';

3、撤销spider所有权限

revoke all on spider.* from 'spider'@'%';

4、给spider超级管理权限

grant all on *.* to 'spider'@'%';

三、函数

字符串函数

语法：

SELECT 函数(参数);

示例：

1、concat 返回字符串Hello拼接字符串mysql

selsect concat('Hello','mysql');

2、lower 将全部字符转为小写

select lower('HellO');

3、upper 将全部字符串转为大写

select upper('hello');

4、lpad 将字符串'01'向左填充五位 '-'

select lpad('01',5,'-');

5、rpad 将字符串'01'向右填充五位 '-'

select rpad('01',5,'-');

6、trim 去除字符串两边的空格

select trim(' hello mysql ');

7、substring 在hello world中从起始位置截取五个字符

select substring('hello world',1,5);

8、由于业务需求变更，企业员工的工号，统一为5位数，目前不足五位数的全部在前面补0。比如：1号员工的工号应该为00001。

update emp set workno = lpad(workno,5,'0');

数值函数

1、CEIL 1.5向上取整

select ceil(1.5);

2、FLOOR 1.5向下取整

select floor(1.5);

3、MOD 求6模于4 意思是6÷4他的余数是几

select mod(6,4);

4、RAND 求0到1之间的随机数

select rand();

5、ROUND 求2.345四舍五入后保留两位小数的值

select round(2.345,2);

6、通过数据库的函数，生成一个轮流位数的随机验证码

select lpad(round(rand()*1000000,0),6,'0')

日期函数

1、curdate 查看当前日期

select curdate();

2、curtime 查看当前时间

select curtime();

3、now 查看当前日期和时间

select nwo();

4、year 查看当前年

select year(now());

5、month

select month(now());

6、day

select day(now());

7、date_add 查看50天后的日期

select date_add(now(),interval 70 day);

year代表年  month代表月 day代表天 hour代表时 minute 代表分 second 代表秒

8、datediff 查看2021年12月1号和2021年11月1号相差多少天

select datediff('2021-12-01','2021-11-01');
                   起始时间 -结束时间

9、查询所有员工的入职天数，并根据入职天数倒序排序

select name,datediff(now(),entrydate) as '入职天数' from emp order by '入职天数' desc;

流程函数

1、IF(value,t,f) 判断如果为true返回ok 如果为false则返回Error

select if(true,'ok','Error');

2、IFNULL(value1,value2) 判断第一个值如果不为NULL返回ok如果为NULL返回Default

select ifnull('ok','Default');

3、CASE WHEN [val1] THEN [res1] ... ELSE [default] END

统计班级各个学员的成绩，展示规则如下

>=85，展示优秀，>=60，展示及格，否则展示不及格

create table score(
id int,
name varchar(10),
math int,
english int,
chinese int
);

insert into score values(1,'Tom',67,88,95),(2,'Rose',23,66,90),(3,'Jack',56,98,76);

成绩表生成完毕
开始进行规则展示
select id,
name,
(case when math >= 85 then '优秀' when math>=60 then '及格' else '不及格' end) as '数学',

(case when english >= 85 then '优秀' when english>=60 then '及格' else '不及格' end) as '英语',
(case when chinese >= 85 then '优秀' when chinese>=60 then '及格' else '不及格' end) as '语文' from score;

4、CASE [expr] WHEN [val1] THEN [res1] ...ELSE [default] END

查询emp表的员工工作地址

（如果员工的地址是上海或北京则展示一线城市，如果是其他则展示二线城市）

select name,(case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end) as '工作地址' from emp;

5、数据库表中，储存的是入职时间，如2000-11-12，如何快速计算入职天数？

用DATEDIFF函数

6、数据库表中，储存的是学生的分值，如98、75，如何快速拍段分数的等级呢？

用CASE....WHEN....函数

四、约束

概述

1、概念：约束是作用于表中字段上的规则，用于限制储存在表中的数据。

2、目的：保证数据库中数据的正确性、有效性、完整性和一致性。

3、分类：

注意：约束是作用在表中字段上的，可以在创建表/修改表的时候添加约束。

约束演示

创建表

create table user(
id int primary key auto_increment comment '主键',
name varchar(10) not null unique comment '姓名',
age int check(age >0 and age<=120) comment '年龄',
status char(1) default '1' comment '当前状态',
gender char(1) comment '性别'
)comment '用户表';

加入数据

insert into user(name,age,status,gender) values('Tom1',19,'1','男'),('Tom2',25,'0','男');

insert into user(name,age,status,gender) values('Tom3',19,'1','男');

insert into user(name,age,gender) values('Tom5',120,'男');

外键约束

外链用来让两张表数据之间建立连接，从而保证数据的一致性和完整性。

添加外键

语法：

建立外键关联创建表的时候添加

CREATE TABLE 表名(
        字段名 数据类型
        ...
        [CONSTRANT] [外链名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名)
);

创建表之后添加

ALTER TABLE 表名 ADD CONSTRANT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名);

注意：目前上述的两张表，在数据库层面，并未建立外键关联，所以是无法保证数据的一致性和完整性的。

步骤：

1、创建dept表

 create table dept(
id int primary key auto_increment comment 'ID',
name varchar(50) not null comment '部门名称'
)comment '部门表';

2、添加数据到dept表里

insert into dept(id,name) values(1,'研发部'),(2,'市场部'),(3,'财务部'),(4,'销售部'),(5,'总经办');

3、创建emp1表

create table emp1(
id int primary key auto_increment comment 'ID',
name varchar(50) not null comment '性别',
age int comment '年龄',
job varchar(20) comment '职位',
salary int comment '薪资',
entrydate date comment '入职时间',
managerid int comment '直属领导ID',
dept_id int comment '部门ID'
)comment '员工表';

4、添加数据到emp1表里

insert into emp1(id,name,age,job,salary,entrydate,managerid,dept_id) values
(1,'金庸',66,'总裁',20000,'2000-01-01',null,5),
(2,'张无忌',20,'项目经理',12500,'2005-12-05',1,1),
(3,'杨逍',33,'开发',8400,'2000-11-03',2,1),
(4,'韦一笑',48,'开发',11000,'2002-02-05',2,1),
(5,'常遇春',43,'开发',10500,'2004-09-07',3,1),
(6,'小昭',19,'程序员鼓励师',6600,'2004-10-12',2,1);

5、进行绑定外链

alter table emp1 add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);

删除外键

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

示例：

alter table emp1 drop foreign key fk_emp_dept_id;

删除/更新行为

语法：

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名(主表字段名) ON UPDATE (外键名);

NO ACTION

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名 (主表字段名) ON UPDATE NO ACTION ON DELETE NO ACTION;

RESTRICT

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名 (主表字段名) ON UPDATE RESTRICT ON DELETE RESTRICT;

CASCADE

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名(主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;

SET NULL

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名(主表字段) ON UPDATE SET NULL ON DELETE SET NULL

SET DEFAULT

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名(主表字段) ON UPDATE SET DEFAULT ON DELETE SET DEFAULT;

五、多表查询

多表关系

项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间互相关联，所以各个表结构之间也存在各种联系，基本上分为三种：

一对多(多对一)

案例：部门与员工的关系

关系：一个部门对应多个员工，一个员工对应一个部门

实现：在多的一方建立外键，指向一的一方的主键

多对多

案例：学生与课程的关系

关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择

实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

实现案例：

1、创建学生表并加入学生数据

create table student(
      id int auto_increment primary key comment '主键ID',
      name varchar(10) comment '姓名',
      no varchar(10) comment '学号'
      )comment '学生表';

insert into student values(null,'黛绮丝','2000100101'),(null,'谢逊','2000100102'),(null,'殷天正','2000100103'),(null,'韦一笑','2000100104');

2、创建课程表并加入课程

create table course(
        id int auto_increment primary key comment '主键ID',
        name varchar(10) comment '课程名称'
        )comment '课程表';

insert into course values(null,'JAVA'),(null,'PHP'),(null,'MySQL'),(null,'Hadoop');

3、创建学生和课程的中间表并插入对应数据

create table student_course(
    id int auto_increment comment '主键' primary key,
    studentid int not null comment '学生ID',
    courseid  int not null comment '课程ID',
    constraint fk_courseid foreign key (courseid) references course (id),
    constraint fk_studentid foreign key (studentid) references student (id)
)comment '学生课程中间表';

insert into student_course values(null,1,1),(null,1,2),(null,1,3,),(null,2,2),(null,2,3),(null,3,4);

一对一

案例：用户与用户详细的关系

关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详细字段放在另一张表中，以提升操作效率

实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)

实现案例：

1、创建tb_user表并储存用户基本信息

create table tb_user(
    id int auto_increment primary key comment '主键ID',
    name varchar(10) comment '姓名',
    age int comment '年龄',
    gender char(1) comment '1:男,2:女',
    phone char(11) comment '手机号'
)comment '用户基本信息表';

insert into tb_user(id,name,age,gender,phone) values
    (null,'黄渤',45,'1','18800001111'),
    (null,'冰冰',35,'2','18800002222'),
    (null,'码云',55,'1','18800008888'),
    (null,'李彦宏',50,'1','18800009999');

2、创建tb_user_edu 并存储用户教育信息

create table tb_user_edu(
    id int auto_increment primary key comment '主键ID',
    degree varchar(20) comment '学历',
    major varchar(50) comment '专业',
    primaryschool varchar(50) comment '小学',
    middleschool varchar(50) comment '中学',
    university varchar(50) comment '大学',
    userid int unique comment '用户ID',
    constraint fk_userid foreign key (userid) references tb_user(id)
)comment '用户教育信息表';

insert into tb_user_edu(id,degree,major,primaryschool,middleschool,university,userid) values
(null,'本科','舞蹈','静安区第一小学','静安区第一中学','北京舞蹈学院',1),
(null,'硕士','表演','朝阳区第一小学','朝阳区第一中学','北京电影学院',2),
(null,'本科','英语','杭州市第一小学','杭州市第一中学','杭州师范大学',3),
(null,'本科','应用数学','阳泉第一小学','阳泉区第一中学','清华大学',4);

多表查询概述

概述：指从多张表中查询数据

笛卡尔积：笛卡尔成绩是指在数学中，两个集合 A集合和 B集合的所有组合情况（在多表查询时，需要消除无效笛卡尔积）

select * from emp1,dept;
！！直接用这一行的话会很乱 而且不符合标准

解决办法

select * from emp1,dept where emp1.dept_id = dept.id;

多表查询分类：

连接查询

内连接：相当于查询A、B交集部分数据

外连接：

左外连接：查询左表所有数据，以及两张表交际部分数据

右外连接：查询右表所有数据，以及两张表交际部分数据

自连接：当前表与自身的连接查询，自连接必须使用表别名

子查询

内连接

内连接查询的是两张表的交集部分

内连接查询语法：

隐式内连接

SELECT 字段列表 FROM 表1,表2 WHERE 条件....;

显式内连接

SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件....;

示例：

1、查询每一个员工的姓名，及关联的部门的名称(隐式内连接实现)

select emp1.name,dept.name from emp1 where emp1.dept_id = dept.id;

可以给他起别名

select e.name ,d.name from emp1 e,dept d where e.dept_id = d.id;

2、查询每一个员工的姓名，及关联的部门的名称(显式内连接实现)

select e.name,d.name from emp1 e inner join dept d on e.dept_id = d.id;

外连接

外连接查询语法：

左外连接

SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件....;

右外连接

SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件....;

示例：
1、查询emp1表的所有数据，和对应的部门信息(左外连接)

select e.*,d.name from emp1 e left outer join dept d on e.dept_id = d.id;

2、查询dept表的所有数据,和对应的员工信息(右外连接)

select d.*,e.* from emp1 e right outer join dept d on e.dept_id = d.id;

如果是用左外可以这样写：

select d.*,e.* from dept d left outer join emp1 e on d.id = e.dept_id;

自连接

自连接查询，可以是内连接查询，也可以是外连接查询

自连接查询语法：

SELECT 字段名称 FROM 表A 别名A JOIN 表A 别名B ON 条件....;

示例：

1、查询员工及其所属领导的名字

select a.name,b.name from emp1 a , emp b where a.managerid = b.id;

2、查询所有员工emp1及其领导的名字emp1，如果员工没有领导，也要查询出来

select a.name,b.name from emp1 a left outer join emp1 b on a.managerid = b.id;

联合查询-union,union all

对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。

SELECT 字段列表 FROM 表A ....
UNION [ALL]
SELECT 字段列表 FROM 表B ....;

注意：

对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。

union后面加all会直接将查询结果合并而直接用union连接会进行去重操作

示例：

1、将薪资低于 5000 的员工，和年龄大于50岁的员工全部查出来

select * from emp1 where salary < 5000 union all select * from emp1 where age > 50;

子查询

子查询概念

概念：SQL语句中嵌套SELETE语句，称为嵌套查询，又称为子查询。

SELETE * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

子查询外部的语句可以是INSERT、UPDATE、DELETE、SELECT任意一个

根据子查询查询结果不同，分为：

标量子查询(子查询结果为单个值)

列子查询(子查询结果为一列)

行子查询(子查询结果为一行)

表子查询(子查询结果为多行多列)

根据子查询位置，分为：WHERE之后、FROM之后、SELECT之后。

标量子查询

子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询称为表象子查询。

常用的操作符：=、 <>、 >、 >=、 <、 <=

示例：

1、查询"销售部"的所有员工信息

查询"销售部"部门的ID
select id from dept where name = '销售部';
根据"销售部"部门ID查询，查询员工信息
select * from emp1 where dept_id = 4;

把他们结合起来
select * from emp1 where dept_id = (select id from dept where name = '销售部');

2、查询在"杨逍"入职之后的员工信息

查询杨逍入职日期
select entrydate from emp1 where name = '杨逍';
查询制定入职日期后入职的员工信息
select * from emp1 where entrydate > '2000-11-03';

把他们连起来
select * from emp1 where entrydate > (select entrydate from emp1 where name = '杨逍');

列子查询

子查询返回的结果是一列（可以是多行），这种子查询被称为列子查询。

常用的操作符：IN、NOT IN、ANY、SOME、ALL

示例：

1、查询"销售部" 和 "市场部"的所有员工信息

查询"销售部"和"市场部"的部门ID
select id from dept where name = "销售部" or name = "市场部"；
根据部门ID，查询员工信息
select * from emp1 where dept_id in (2,4)

把他们连起来
select * from emp1 where dept_id in (select id from dept where name = "销售部" or name = "市场部");

2、查询比财务部所有工人工资都高的员工信息

查询财务部所有人的工资
select salary from emp1 where dept_id = (select id from dept where name = '财务部');
查询比财务部员工工资都高的信息
select * from emp1 where salary > all(select salary from emp1 where dept_id = (select id from dept where name = '财务部'));

3、查询比研发部任意一人的工资高的员工信息

查询研发部所有人的工资
select salary from emp1 where dept_id = (select id from dept where name = '研发部');

查询比研发部其中任意一人工资高的员工信息
select * from emp1 where salary>any(select salary from emp1 where dept_id = (select id from dept where name = '研发部'));

行子查询

子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。

常用的操作符：=、<>、IN、NOT IN

示例：

1、查询与"张无忌"的薪资及直属领导相同的员工信息

查询"张无忌"的薪资及直属领导
select salary,managerid from emp1 where name = '张无忌';

查询与"张无忌"的薪资及直属领导相同的员工信息
select * from emp1 where (salary,managerid) = (select salary,managerid from emp1 where name = '张无忌');

表子查询

子查询返回的结果是多行多列，这种子查询称为表子查询。

常用操作符：IN

示例：

1、查询与"小昭","杨逍"的职位和薪资相同的员工信息

查询小昭和杨逍的工资和职位
select job,salary from emp1 where name = '小昭' or name = '杨逍';

查询小昭和杨逍的工资和职位相同的员工信息
select * from emp1 where (job,salary) in (select job,salary from emp1 where name = '小昭' or name = '杨逍');

2、查询入职日期是"2006-01-01"之后的员工信息，及其部门信息

查询入职时间在2006-01-01之后的员工信息
select * from emp1 where entrydate > '2006-01-01';
查询员工对应的部门信息
select e.*,d.* from (select * from emp1 where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id;

多表查询案例

1、查询员工的姓名、年龄、职位、部门信息

select e.name,e.age,e.job,d.name from emp as e,dept as d where e.dept_id = d.id;

2、查询年龄小于30岁的员工姓名、年龄、职位、部门信息

第一种、select e.name,e.age,e.job,d.name from emp1 e join dept d on e.age<30 and e.dept_id = d.id;
第二种、select e.name,e.age,e.job,d.name from emp1 e join dept d on e.dept_id = d.id where age<30;

3、查询拥有员工的部门ID、部门名称

select distinct * from emp e, dept d where e.dept_id = d.id;

4、查询所有年龄大于40岁的员工,及其归属的部门名称;如果员工没有分配部门,也需要展示出来

select e.*,d.name from emp e left join dept d on e.dept_id = d.id where e.age>40;

5、查询所有员工的工资等级

select e.* ,s.grade,s.losal from emp e ,salgrade s where e.salary >= s.losal and e.salary <= s.hisal;

简写后：
select e.* ,s.grade,s.losal from emp e ,salgrade s where e.salary between s.losal and s.hisal;

6、查询"研发部"所有员工的信息及工资等级

select e.*,s.grade
from emp e,
        dept d,
        salarade s
where e.dept_id = d.id
    and (e.salary between s.losal and s.hisal)
    and d.name = '研发部';

7、查询"研发部"员工的平均工资

select avg(e.salary) from emp e,dept d where e.dept_id = d.id and d.name = '研发部';

8、查询工资比"灭绝"高的员工信息

select * from emp where salary > (select salary from emp where name = '灭绝');

9、查询比平均薪资高的员工信息

select * from emp1 e where e.salary>(select avg(e.salary) from emp1 e);

10、查询低于本部门平均工资的员工信息

select * from emp e2 where e2.salary < (select avg(e1.salary) from emp e1 where e1.dept_id=e2.dept_id);

11、查询所有的部门信息,并统计部门的员工人数

select d.id,d.name ,(select count(*) from emp e where dept_id = d.id) '人数' from dept d;

12、查询所有学生的选课情况,展示出学生名称,学号,课程名称

select s.name , 
        s.no , 
        c.name from student s , 
        student_course sc,
        course c where s.id = sc.studentid and sc.courseid = c.id;

六、事务

事务简介

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

默认MySQL的事务是自动提交的 , 也就是说当执行一条DML语句 , MySQL会立即隐式的提交事务

事务操作

示例：

创建表
create table account(
    id int auto_increment primary key comment '主键ID',
    name varchar(10) comment '姓名',
    money int comment '余额'
)comment '账户表';

插入数据
insert into account(id,name,money) values(null,'张三',2000),(null,'李四',2000);

恢复数据
update account set money = 2000 where name = '张三' or '李四';

1、查询张三账户余额

select money from account where name = '张三';

2、将张三账户的余额扣除1000

update account set money = money - 1000 where name = '张三';

3、将李四的余额加1000

update account set money = money + 1000 where name = '李四';

查看/设置事务提交方式

查看事务提交方式     #如果为0就是手动提交 为1则是自动提交
SELECT @@autocommit;


设置事务提交方式
SET @@autocommit = 0;

开启事务

SELECT TRANSACTION 或 BEGIN;

提交事务

COMMIT;

回滚事务

ROLLBACK;

进行修改后：

start transaction;

select * from account where name = '张三';

update account set money = money - 1000 where name = '张三';

#程序执行报错 ....

update account set money = money + 1000 where name = '李四';

如果没有报错则手动执行下面这行

提交事务
commit;


如果报错了则手动执行回滚 保证数据的准确性
回滚事务
roolback;

事务四大特性(ACID)

原子性(Atomicity)：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。

一致性(Consistency)：事务完成时，必须使所有的数据都保持一致状态。

隔离性(Isolation)：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下执行

持久性(Durability)：事务一旦提交成功或回滚，它对数据库中的数据改变就是永久的

并发事务问题

脏读：

不可重复读：

幻读：

事务隔离级别

隔离级别从上到下越来越高 Read uncommitted安全性最低

性能从上到下越来越低 Read uncommitted性能最高

查看事务隔离级别

SELECT @@TRANSACTION_ISOLATION;

设置事务隔离级别

SET [SESSION | GLOBAL] TRANSATION ISOATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE};

SESSION代表对当前客户端有效

GLOABL代表对所有客户端有效

示例：

查看事务隔离级别
select @@transaction_isolation;

设置事务隔离级别
set session transaction isolation level read uncommitted;

set session transaction isolation level read repeatable read;