比较详细的mysql的DDL、DML、DQL操作笔记总结

介绍：这是我们自己学习做的笔记，共以后自己及各位朋友同行使用，做的不好的地方请大家见谅！

mysql的数据类型

mysql	Java	length	Example
TINYINT(tinyint)	byte	1字节有符号整数	2
SMALINT(smalint)	short	2字节有符号整数	20
INT(int)	int	4字节有符号整数	20
BIGINT(bigint)	long	8字节有符号整数	20
null	boolean	布尔类型：true or flase	TRUE FALSE
FLOAT(float)	float	单精度浮点型	3.1415926
DOUBLE(double)	double	双精度浮点数	3.14592678
VARCHAR(varchar)	String	字符系列。可以指定字符集。可以使用单引号或者双引号	‘test mysql’ or “test mysql”
TIMESTAMP(timestamp)		时间类型
BINARY(binary)		字节数组

数据库的DDL操作

---

DDL(Data Definition Language)数据定义语言
用来定义数据库对象：数据库，表，列等。关键字：create, drop,alter 等

---

创建数据库

创建数据库：
	create database 数据库名称;
创建数据库，判断不存在，再创建：
	create database if not exists 数据库名称;(增强代码的健壮性)
创建数据库，并指定字符集
	create database 数据库名称 character set 字符集名;

#示例
#创建数据库stu，并判断是否存在，并制定字符集为gbk
create database if not exists stu character set gbk;

查询：

查询所有数据库的名称:
	show databases;
查询某个数据库的字符集:查询某个数据库的创建语句
	show create database 数据库名称;

#示例
#查询所有数据库的名称
show databases;

#查询某个数据库的字符集(查询某个数据库的创建语句)
show create database stu;

修改库

注意：不能修改数据库名

修改数据库的字符集
	alter database 数据库名称 character set 字符集名称;

#示例
#修改数据库stu字符集改成utf8
alter database stu character set utf8;

删除库

删除数据库
	drop database 数据库名称;
判断数据库存在，存在再删除
    drop database if exists 数据库名称;

#示例
#删除数据库stu
drop database stu;

#删除数据库之前先判断数据库stu是否存在
drop database if exists stu;

使用库

查询当前正在使用的数据库名称
	select database();
使用数据库
	use 数据库名称;

#示例
#查询当前正在使用的数据库名称
select database();

#使用数据库stu
use stu;

数据库的表的DDL操作

---

DDL(Data Definition Language)数据定义语言
用来定义数据库对象：数据库，表，列等。关键字：create, drop,alter 等

---

表创建

语法：
	create table 表名(
		列名1 数据类型1,
		列名2 数据类型2,
		....
		列名n 数据类型n
		);
	注意：最后一列，不需要加逗号（,）
	数据类型：
		a. int：整数类型
				age int,
		b. double:小数类型
				score double(7,2)
		c. date:日期，只包含年月日，yyyy-MM-dd
		d. datetime:日期，包含年月日时分秒	 yyyy-MM-dd HH:mm:ss
		e. timestamp:时间戳 类型	包含年月日时分秒	 yyyy-MM-dd HH:mm:ss	
		如果将来不给这个字段赋值，或赋值为null，则默认使用当前的系统时间，来自动赋值
		f. varchar：字符串
			name varchar(20):姓名最大20个字符
			zhangsan 8个字符  张三 2个字符

#示例
#创建stu表
create table stu(
	id int,
    name varchar(22),
    age int,
    gender char,
    birthday date,
    insert_time timestamp
);

#创建stu表，如果不存在才创建
create table if not exists stu1(
	id int,
    name varchar(22),
    age int,
    gender char,
    birthday date,
);

插入数据

语法
	insert into 表名 (列名1，列名2，...，列名n) values (值1，值2，...，值n);

#示例
#向stu表中插入数据
insert into stu(id,name,age,gender,birthday) values(1,'lqs',18,'男','2020-02-20');

#向stu表中插入数据2
insert into stu1 values(1,'lqs',18,'男','2020-02-20');

#向stu表中插入数据3
insert into stu(id,name,age) values(1,'lqs',18);

查询表

语法:
	查询某个数据库中所有的表名称
		show tables;
	查询表结构
		desc 表名;
	查看表的字符集
	    show create table 表名;

#示例
#在数据库stu中查询所有表名称
show tables;

#没有在数据库stu中查询所有表名称
show tables from stu;

#查询数据库stu中stu表的结构
desc stu;

#查询数据库stu中stu表的字符集
show create table stu;

修改表

语法：
	修改表名
		alter table 表名 rename to 新的表名;
	修改表的字符集
		alter table 表名 character set 字符集名称;
	添加一列
		alter table 表名 add 列名 数据类型;
	修改列名称 类型
		alter table 表名 change 列名 新列名 新数据类型;  -- 修改列名和数据类型
		alter table 表名 modify 列名 新数据类型;   -- 只修改数据类型
	删除列
		alter table 表名 drop 列名;

#示例
#把stu表的名字修改为student
alter table stu rename to student;

#把stu表的字符集改成utf8
alter table stu character set utf8;

#在stu表中添加一列
alter table stu add t1 int;

#修改stu表列名test改成id
alter table stu change test id int;

#修改stu表test的数据类型
alter table stu modify test varchar(12);

#删除stu表的id列
alter table stu drop id;

删除表

语法：
	drop table 表名;
	drop table  if exists 表名 ;

#示例：
#不判断表是否存在直接删除
drop table stu;

#判断表是否存在直接删除
drop table if exists stu;

数据库表的 DML增删改

---

DML(Data Manipulation Language)数据操作语言
用来对数据库中表的数据进行增删改。关键字：insert, delete, update 等

---

添加数据

语法：
	insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n);
注意：
	a. 列名和值要一一对应。
	b. 如果表名后，不定义列名，则默认给所有列添加值
		insert into 表名 values(值1,值2,...值n);
	c. 除了数字类型，其他类型需要使用引号(单双都可以)引起来

#示例
#在stu表中插入数据1
insert into stu values(3,'lqs',23),(4,'法外狂徒张',18);

#在stu表中插入数据2
insert into stu(id,name,age) values(3,'lqs',23),(4,'法外狂徒张',18);

#在stu表中插入数据3
insert into stu(name,age) values('lqs',23),('法外狂徒张',18);

删除数据

d语法：
	delete from 表名 [where 条件]
注意：
	如果不加条件，则删除表中所有记录。
	如果要删除所有记录:
		不推荐使用。有多少条记录就会执行多少次删除操作
			delete from 表名;  
		推荐使用，效率更高 先删除表，然后再创建一张一样的表
			truncate table 表名; 

#示例
#删除stu表中id为1的行信息
delete from stu where id=1;

#删除stu表的全部数据
delete from stu;

#采用效率更高 先删除表，然后再创建一张一样的表
truncate table stu;

修改数据

语法：
	 update 表名 set 列名1 = 值1, 列名2 = 值2,... [where 条件];

注意：
如果不加任何条件，则会将表中所有记录全部修改

#示例
#修改stu表id为1的信息
update stu set id=2,name='student',age=18 where id=1;

#修改整张表数据
update stu set id=2,name='student',age=18;

数据库DQL查询表操作

---

DQL(Data Query Language)数据查询语言
用来查询数据库中表的记录(数据)。关键字：select, where 等

---

查询语句的语法

查询某张表中所有内容的时候：
	select * from 表名;
语法：
select
	字段列表
from
	表名列表
where
	条件列表
group by
	分组字段
having
	分组之后的条件
order by
	排序
limit
	分页限定

基础查询

语法
	select 查询列表 from 表名;  （类似于 System.out.println(打印的内容);）	
特点:
	查询列表可以是:表中的字段，常量值，表达式，函数
	查询的结果是一个虚拟的表格
	注意：
		没有将结果进行存储，而是打印后就结束

#单个字段查询
#select 列名 from 表明
#查询来自表users字段为name的信息
select name from users;

#多字段查询
#select 字段名1，字段名2，...字段名n from 表名
#查询来自表user字段为id和age的信息
select id,age from users;

#查询所有的字段
select * from users;

注意: 在mysql里面可以使用 `` 将字段特殊含义去掉,如说 name 在sql里面是一个关键字，如果name是一个字段你得需要用 `name` 引起来select `name` from users;

#查询常量值
select 91821;
select lqs;

#查询表达式
select 9182/9070;

#查询函数
select version();

#起别名
#第一种
select 9128/9070 as fruit;
#第二种
select 9128/9070 fruit;

#去重复（distinct）
#select distinct 字段名1，字段名2，...，字段名n from 表名;
#查询user表中员工涉及到的所有地址
select distinct address from users;

注意：+号的作用
在java +有两种含义
a.运算符，两个操作数都是数值型
b.连接符。只要有一个操作数为字符串
在mysql中的l+号
仅仅只有一个功能:运算符
select 100+90; 两个操作数都为数值型,则做加法运算
select ‘123’+90; 213 其中一方为字符型，试图将字符型数值转换成数值型，如果转换成功则做加法运算
select ‘user’ +90; 90如果转换失败，则将字符型的值变成0
select null+‘0’; 只要有一方值为null,结果必定为null

注意:如果想拼接字符串 可以使用函数 concat
select concat(name,address) from user;

条件查询

语法
 	select 
    	查询列表
	from 
    	表名
	where 
		筛选条件;

**按条件表达式查询 **

	按条件表达式分类筛选
	条件运算符 : > < = != <> <= >=

#查询年龄大于18岁的用户信息
select * from users where age >18;

#查询不住在美国的用户信息的姓名、地址、id
select id,name,address from users where address != '美国';
select id,name,address from users where address <> '美国';#不推荐使用

按逻辑表达式筛选

按逻辑表达式筛选
	怎么用？
		用于连接条件表达式
	逻辑运算符： 
		&&  ||  ！  and  or  not
	解释
		&&和and ：只要有两个条件为true,结果为true,反之为false
		||和or  : 两个条件只要有一个true,结果为true,反之为false
		！和 not : 将条件取反

#查询用户月薪大于6666并且小于16666RMB的id,年龄,月薪
select id,name,salary from users where salary > 6666 adn salary < 16666

#查询用户年龄不在18~28岁之间的用户，或者他们的工资小于6666的
select * from users where age <18 or age >28 or salary < 6666
select * from users where age <18 || age >28 || salary < 6666
select * from users where (age >18 and age <28) or salary < 6666
select * from users where (age >18 && age <28) || salary < 6666
#注意：以上四句都可以满足：查询用户年龄不在18~28岁之间的用户，或者他们的工资小于6666的

模糊查询

模糊查询
	like
	between and
  	in
  	is null

注意：like 一般和通配符一起使用

占位符（通配符）：
  	 %:表示任意匹配字符（个数不限）
   	_:代表一个任意字符(只占一个位置)

#查询用户信息中包含字符'青'的学生信息
select * from users where name like '%青%';

#查询用户信息中第二字为'青'的用户信息
select * from users where name like '_青%';

#查询用户信息中第二个字为‘青’，且名字长度为2的用户信息
select * from users where name like '_青';

#查询用户信息中名字长度为四的用户信息
select * from users where name like '____';

特殊情况

查询用户信息中姓名包含特殊字符（列如：‘@’，‘_’…）的用户,代码如下

#查询特殊字符需要使用转义符（\）进行转义
select * from users where name like '\@%\__';

between and （在xxx之间 (从小到大)）关键字

#查询年在18~28岁的用户信息
select * from users where age >=18 and age <= 30;
select * from users where age >=18 && age <= 30;
select * from users where age between 18 and 30;
-- 以上三句达到的效果一样

in

判断某字段的值是否属于in列表中的某一项(为了简洁,但不支持通配符)

#查询用户住在香港和澳门、北京的用户信息
select * from users where address = '香港' or address = '澳门' or address = '北京';
select * from users where address = '香港' || address = '澳门' || address = '北京';
select * from users whers address in('香港','澳门','深圳');
--以上三句得到的效果是一样的

is null

#查询用户月薪为null的姓名和年龄
select age,salary from users where salary = null; #错误使用
select age,salaru from users where salary <=> null;#建议少用或者不用
select age,salary from users where salary is null;

#查询用户工资不为null的姓名和年龄
select name,age from users where salery is not null;

排序查询

语法:
select 
	查询列表
from
	表
[where 筛选条件]
order by 
	排序列表 [asc|desc]
排序方式：
	ASC：升序，默认的。
	 DESC：降序。

注意：
如果有多个排序条件，则当前边的条件值一样时，才会判断第二条件。

#查询用户信息，按照月薪从高到低排序
select * from users order by salary desc;

#查询用户信息，按照月薪从高到低排序，如果月薪相同就按照年薪进行升序排序
select * from users order by salary desc,yearSalary asc;
select * from users order by salary desc,yearSalary;
-- 以上两句话得到的结果是一样的

#查询用户为男性的信息，并且按照年薪进行从高到低进行排序
select * from users where sex = '男' order by yearSalary desc;

#查询用户信息，并按照年龄和月薪从高到低排序
select *,age + salary from users order by age+salary desc;
select *,age + salary as asl from users order by asl desc;

解决null值问题

ifnull() 函数
语法
	 ifnull(字段,值) 当字段中结果为null是，用值去替代,不为null的值就是原来的值
 select ifnull(salary,0) from users;

#查询用户信息，并按照年龄和月薪从高到低排序，同时排除salary为null的值
select *,age + ifnull(salary,0) ain from users order by ain desc;

常见函数

函数的概念

概念：
	与Java中的方法类似，将一个操作封装成公共的方法供外界使用
好处：
	a、隐藏了操作实现的细节
	b、提高了代码的复用性
调用方式：
	select 函数名(实参列表) [from 表]
	注意：
		[]的东西表示可选操作
分类：
	a、单行函数
	b、分组函数（聚合函数）：做统计使用的

单行函数

字符函数、数字函数、日期函数、流程控制函数、其它函数

字符函数

#length 获取参数值的字节个数（需要考虑字符集）
select length('lqs');

#concat 拼接字符串
select concat (name,'_',age) from users;

#upper（转大写） ,lower（转小写）
select upper('lqs');
select lower('LQS');

#将姓大写名小写 然后进行拼接
select  concat(upper('lqs'),lower('ADH'));

#substr 截取字符串 在sql中索引从1开始
select substr('2020-02-20',1,6);
select substr('小蠢货爱上了道士哥哥',6,6);

#instr (子串在大字符串里的第一次的出现起始索引，如果没有返回0)
select instr('杨过爱上了小龙女','小龙女');

#trim 前后去字符
select trim('       狐妖小红娘      ');
select trim('a' from 'aaaaaa狐妖aaaaaa小aaa红娘aaaaaa');

#lpad，rpad（向左或者向右补全）
select lpad('lqs',10,'a');
select rpad('lqs',10,'a');

#replace 替换
select replace('小蠢货爱上了道士哥哥','小蠢货','道士哥哥');

数学函数

#round 四舍五入
select round(1.5);-- 2
select round(1.4);-- 1
select round(-1.5);-- -2
select round(-1.4);-- -1
select round(1.56787,3);-- 1.568

 #ceil 向上取整，返回>=该参数的最小整数
select ceil(1.5);-- 2
select ceil(1.4);-- 2
select ceil(-1.5);-- -1
select ceil(-1.4);-- -1

#floor 向下取整， 返回<=该参数的最大整数
select floor(1.9);-- 1
select floor(1.1);-- 1
select floor(-1.9);-- -2
select floor(-1.1);-- -2

#truncate 截断
select truncate(1.91289128,2);-- 1.91
select truncate(1.91289128,4);-- 1.9128

#mod 取余   a-a/b*b
select mod(10,9);-- 1
select 10%9;-- 1

日期函数

#now 返回当前系统日期+时间
select now();# 2021-12-02 09:42:26

#curdate 返回当前系统日期
select curdate();# 2021-12-02
 #获取指定的部分：年(year),月(month),天(day),小时(hour),分钟(minute),秒(second)
select year(now());# 2021
select month('1999-09-09');#2
select day('2020-02-20');# 20
select hour('20:22:28');# 20
select minute('20:22:28');# 22
select second('20:22:28');# 28

#str_to_date:将日期格式的字符转换成指定格式的日期
select str_to_date('9-13-2004','%m-%d-%Y');# 2020-02-20
select str_to_date('02-20-2020','%m月%d日%年');# null
   
date_format:将日期转化成字符串
select date_format('2020-02-20','%Y年/%m月/%d日 ');# 2020年/02月/20日

其他函数

select version();# 5.7.28-log
select database();# jdbc_test
select user();# root@localhost

流程控制函数

if 函数 类似于if else
if(条件表达式,true 执行这个, false 执行这个)
if(10>5,'大于','小于')
类似于三目运算符 语句？ （true）：（false）

if(salary is null ,'我没有工资了','我有18888的工资') from users;# 我有18888的工资

case 函数 类似于 switch case
case 要判断的字段或表达式
when 常量1 then 显示的值1
when 常量2 then 显示的值2
else 要显示的值n
end

#根据不同的地区，将月薪乘以不同的系数(日本 0.1 美国 0.1 北京 1.3 其他不变)
SELECT
	address,
	CASE address
WHEN '日本' THEN
	salary * 0.1
WHEN '美国' THEN
	salary * 1.2
WHEN '北京' THEN
	salary * 1.3
ELSE
	salary
END
FROM
	users;

分组函数（聚合函数）

#count：计算个(行数)数
#一般选择非空的列：主键
#count(*)
select count(salary) from users;
	
#max：计算最大值
select max(salary) from users;

#min：计算最小值
select min(salary) from users;

#sum：计算和
select max(salary) from users;

#avg：计算平均值
select avg(salary) from users;

/*
参数支持哪些类型
sum(),avg() 数值型
min(),max(),count() 数值和字符都可以
 注意：所有聚合函数的计算，排除null值。
	解决方案：
		选择不包含非空的列进行计算
		IFNULL函数
*/

#可以和distinct 搭配使用
select sum(distinct salary),sum(salary) from users;
select count(distinct salary),count(salary) from users;
   
#和分组函数一同查询的字段有限制,一般来说是group by之后的字段
select sum(salary),name from users; -- 没有意义

分组查询

语法
select 分组函数,列(要求出现在group by 后面)
from 表
[where 筛选条件]
group by 分组列表
[having 筛选条件]
[order by 排序列表]
注意：
 	分组之后查询的字段：分组字段、聚合函数
	where 和 having 的区别如下：
		where 在分组之前进行限定，如果不满足条件，则不参与分组。having在分组之后进行限定，如果不满足结果，则不会被查询出来。
		where 后不可以跟聚合函数，having可以进行聚合函数的判断。

#查询不同性别的月薪最高和年薪最高的用户信息
select sex,max(salary),max(yearSalary) from users group by sex;

#统计不同地区的用户个数
select address,count(*) from users group by address;

#统计来自北京的用户并按照性别求出月薪平均数
select sex,avg(salary) from users where address='北京' group by sex;

#统计不同性别,不同地区用户的平均工资
select sex,address,avg(salary) from user group by sex,address;

#统计不同地区用户的月薪平均工资并要求平均分大于80
select address,avg(salary) as avg_salary from users group by address having avg;

#统计来自香港的用户按照性别求月薪平均工资，并要月薪平均工资大于8888
#方式1
select sex,avg(salary) as avg_salary from user where address = '香港' group by sex having avg>8888;
#方式二
select address,sex,avg(salary) avg from users group by address,sex having avg>8888 and address='香港';

#按用户姓名的长度分组，统计每一组用户个数（group by 后面可以接函数）
select length(name),count(*) from users group by length(name);

#统计不同地区，不同性别用户的月薪平均工资，并且要求按照月薪平均工资从大到小排序
select address,sex,avg(salary) as avg_salary from users group by address,sex order by avg_salary desc;

分页查询

语法：
select
	字段列表
from
	表名列表
[where
	条件列表]
[group by
	分组字段]
[having
	分组之后的条件]
[order by
	排序]
limit
	分页限定

#显示用户前3条信息
select * from users limit 0,3;
#显示月薪在前3的用户信息
select * from users order by salary limit 0,3;

注意：
分页限定公式：
开始的索引 = （当前的页码 - 1） * 每页显示的条数
limit 是一个MySQL"方言"，也就是说这个limit在MySQL数据库里面才能正确使用

多表查询

查询语法

select
	列名列表
from
	表名列表
where....

准备数据

# 创建部门表
create table dept(
	id int,
	name varchar(20),
    location_id int
);
	
insert into dept (id,name,location_id) VALUES (1,'开发部',1),(2,'市场部',2),(3,'财务部',3);

# 创建位置表
create table location(
    id int,
    location_name varchar(25)
);
insert into location(id,location_name) values(1,'北京'),(2,'深圳'),(3,'上海');

# 创建员工表
create table emp (
	id int,
	name varchar(10),
	gender char(1), -- 性别
	salary double, -- 工资
	join_date date, -- 入职日期
	dept_id int
);

insert into emp(id,name ,gender,salary,join_date,dept_id) values(1,'孙悟空','男',7200,'2013-02-24',1);
insert into emp(id,name ,gender,salary,join_date,dept_id) values(2,'猪八戒','男',3600,'2010-12-02',2);
insert into emp(id,name ,gender,salary,join_date,dept_id) values(3,'唐僧','男',9000,'2008-08-08',2);
insert into emp(id,name ,gender,salary,join_date,dept_id) values(4,'白骨精','女',5000,'2015-10-07',3);
insert into emp(id,name ,gender,salary,join_date,dept_id) values(5,'蜘蛛精','女',4500,'2011-03-14',1);

#创建薪水等级表	
create table salary_level(
  salary_level varchar(3),
  lowest_sal int,
  highest_sal int
);
   
insert into salary_level values('A',2000,4000);
insert into salary_level values('B',4000,6000);
insert into salary_level values('C',6000,8000);
insert into salary_level values('D',8000,10000);

隐式内连接：使用where条件消除无用数据(sql92)

#查询员工表的名称，性别,部门名称
select emp.name,emp.gender,dept.name from emp,dept where emp.`dept_id` = dept.`id`;

#标准格式		
select 
	t1.name, -- 员工表的姓名
	t1.gender,-- 员工表的性别
	t2.name -- 部门表的名称
from
	emp t1,
	dept t2
where
	t1.`dept_id` = t2.`id`;

#查询所有男性员工的名称，性别,部门名称
select e.name,e.gender,d.name from emp e ,dept d where e.dept_id=d.id and e.gender='男';

显式内连接(sql99)：
语法： select 字段列表 from 表名1 [inner] join 表名2 on 条件
注意：[]里面的东西表示可选操作

#查询所有男性员工的名称，性别,部门名称
select e.name,e.gender,d.name from emp e join dept d on e.dept_id=d.id where e.gender='男';

#统计员工的平均工资，按照部门名称分组，并且按平均工资从高到低排序
select d.name ,avg(salary) salary_avg from emp e join dept d on e.dept_id=d.id  group by d.name order by salary_avg desc;

#注意：上述所有的都是等值连接，然后还有非等值连接 

#求每个人的薪资级别，非等值连接 
select e.name,e.salary,s.salary_level from emp e join salary_level s on e.salary>=s.lowest_sal and e.salary<=s.highest_sal

连接查询的重点：
a、从哪些表中查询数据
b、条件是什么
c、查询哪些字段

外连接

左外连接： 	
	语法：
		select 字段列表 from 表1 left [outer] join 表2 on 条件；
查询的是左表所有数据以及其交集部分。

-- 查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称
select t1.*,t2.`name` from emp t1 left jion dept t2 on t1.`dept_id` = t2.`id`;

右外连接：
	语法：
		select 字段列表 from 表1 right [outer] join 表2 on 条件；

#查询的是右表所有数据以及其交集部分。
select * from dept t2 right join emp t1 on t1.`dept_id` = t2.`id`;

子查询

查询中嵌套查询，称嵌套查询为子查询

#查询工资最高的员工信息
#查询最高的工资是多少 9000
select max(salary) from emp;
#查询员工信息，并且工资等于9000的
select * from emp where emp.`salary` = 9000;
			
#一条sql就可以完成上述的两步操作,这个就叫做子查询(嵌套查询)
select * from emp where emp.`salary` = (select max(salary) froom emp);

根据子查询的结果不同可以进行以下分类

子查询可以作为条件，使用运算符去判断。 运算符： > >= < <= =

#查询员工工资小于平均工资的人
select * from emp where emp.salary < (select avg(salary) from emp);

子查询的结果是多行单列的：
子查询可以作为条件，使用运算符in来判断

#查询'财务部'和'市场部'所有的员工信息
select id from dept where name = '财务部' OR name = '市场部';
select * from emp where dept_id = 3 OR dept_id = 2;
#子查询
select * from emp where dept_id IN (select id from dept where name = '财务部' or name = '市场部');

子查询的结果是多行多列的：
子查询可以作为一张虚拟表参与查询

#查询员工入职日期是2011-11-11日之后的员工信息和部门信息
#子查询
select  t1.id,.t1.name,t1.join_date,d.name from dept d join (select * from emp where join_date < '2020-02-20') t1 on d.id=t1.dept_id;
  				
#普通内连接
select * from emp t1,dept t2 where t1.`dept_id` = t2.`id` and t1.`join_date` >  '2020-02-20'
select * from emp e join dept d on e.dept_id = d.id where e.join_date > '2020-02-20'

约束

定义：
对表中的数据进行限定，保证数据的正确性、有效性和完整性

约束分类：
	主键约束：primary key
	非空约束：not null
	唯一约束：unique
	外键约束：foreign key

非空约束

概念:not null,某一列的值不能出现null值

#创建表时添加约束
create table users (
	id int,
	name varchar(20) not null -- name为非空
);

#创建表后，添加非空约束
alter table users modify varchar(20) not null;

#删除非空约束
alter table users modify name varchar(20);

唯一约束

概念:unique,某一列的值不能重复
注意:唯一约束可以有null值，null值可以重复

#在创建表时，添加唯一约束
create table users (
	id int,
	name varchar(20) unique -- 手机号
);
#在表创建完后，添加唯一约束
#第一种方式
alter table users modify phone_number varchar(20) unique;
#第二种方式
alter table users add constraint <unique_name> unique(id);

#查看表的约束名
show create table users;

#删除唯一键
alter table users drop index unique_name;

主键约束

概念:
	primary key 非空且唯一
注意:
	一张表只能有一个主键,主键可以由一个或多个字段构成
  	主键就是表中每行数据的唯一标识

#在创建表时,添加主键约束
create table users(
	id int primary key,-- 给id添加主键约束
	name varchar(20)
);

#删除主键
#错误 alter table stu modify id int ;
alter table users drop primary key;

#创建完表后,添加主键
alter table users stu modify id int primary key;

自动增长
概念：
如果某一列是数值类型的，使用 auto_increment 可以来完成值得自动增长

#在创建表时，添加主键约束，并且完成主键自增长
create table stu(
	id int primary key auto_increment,-- 给id添加主键约束
	name varchar(20)
);

#删除自动增长
alter table users stu modify id int;
	
#添加自动增长
alter table users stu modify id int auto_increment;

外键约束

概念:
foreign key,让表于表产生关系，从而保证数据的正确性

create table emp1(
    id int primary key auto_increment,
    name varchar(22),
    age int,
    dep_name varchar(22),
    dep_location varchar(22)
);

-- 添加数据
insert into emp1(name,age,dep_name,dep_location) values ('法外狂徒张三', 20, '研发部', '广州');
insert into emp1(name,age,dep_name,dep_location) values ('轻功盐水飘李四', 21, '研发部', '广州');
insert into emp1(name,age,dep_name,dep_location) values ('老板王老五王五', 20, '研发部', '广州');
insert into emp1(name,age,dep_name,dep_location) values ('隔壁老王老王', 20, '销售部', '深圳');

#解决方案：分成 2 张表
#创建部门表(id,dep_name,dep_location)
#一方，主表
create table department(
 id int primary key auto_increment,
 dep_name varchar(20),
 dep_location varchar(20)
);

#创建员工表(id,name,age,dep_id)
#多方，从表
create table employee(
 id int primary key auto_increment,
 name varchar(20),
 age int,
 dep_id int  -- 外键对应主表的主键
);

#添加 2 个部门
insert into department values(null, '研发部','广州'),(null, '销售部', '深圳');
select * from department;

#添加员工,dep_id 表示员工所在的部门
insert into employee (name,age,dep_id) values ('法外狂徒张三', 20, '研发部');
insert into employee (name,age,dep_id) values ('轻功盐水飘李四', 21, '研发部');
insert into employee (name,age,dep_id) values ('老板王老五王五', 20, '研发部');
insert into employee (name,age,dep_id) values ('隔壁老王老王', 20, '销售部');

select * from employee;

在创建表时,可以添加外键
create table 表名(
	....
	外键列
	constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)

);

create table employee1( 
 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) 
) 
#正常添加数据 
insert into employee (name,age,dep_id) values ('法外狂徒张三', 20, '研发部');
insert into employee (name,age,dep_id) values ('轻功盐水飘李四', 21, '研发部');
insert into employee (name,age,dep_id) values ('老板王老五王五', 20, '研发部');
insert into employee (name,age,dep_id) values ('隔壁老王老王', 20, '销售部');

select * from employee;

/*
部门错误的数据添加失败 
插入不存在的部门 
Cannot add or update a child row: a foreign key constraint fails 
*/ 
INSERT INTO employee (NAME, age, dep_id) VALUES ('老张', 18, 6);
 
#删除外键
alter table users drop froeign key users_id;
#创建表后添加外键
alter table users add constraint users_id froeign key (id) references use(usertest);

删除外键
alter table 表名 drop froeign key 外键名称;
创建表后添加外键
alter table 表名 add constraint 外键名称froeign key (外键字段名称) references 主表名称(主表列名称);

结束了，操作的部分，理论的部分我就不写了，还是加一个重要的知识点吧：

事务的四大特征：
	原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败。
	持久性：当事务提交或回滚后，数据库会持久化的 保存数据。
	隔离性：多个事务之间。相互独立。
	一致性：事务操作前后，数据总量不变.

---

脏读：
	一个事务，读取到另一个事务中没有提交的数据
	不可重复读：在同一个事务中，两次读取到的数据不一样。
	幻读(虚读)：一个事务操作(DML)数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改。

---

补充

DQL(Data Query Language)数据查询语言

用来查询数据库中表的记录(数据)。关键字：select, where 等

---