MySQL基础

MySQL的三层结构

数据库-表的本质任然是文件

数据在数据库中的存储方式

SQL语句的分类

• DDL：数据定义语句（create表、库…）
• DML：数据操作语句（增加insert、修改update、删除delete）
• DQL：数据查询语句（select）
• DCL：数据控制语句（管理数据库，比如用户权限grant revoke）

创建数据库

基本语法：

CREATE DATABASE [IF NOT EXISTS] db_name
                  [create_specification[,create_specification]...]
 create_specification:
     [DEFAULT] CHARACTER SET charset_name
     [DEFAULT] COLLATE collation_name                 

1. CHARACTER SET : 指定数据库采用的字符集，默认为utf8mb4。
2. COLLATE : 指定数据库字符集的校对规则（常用的utf8mb4_bin[区分大小写]、utf8mb4_general_ci[不区分大小写]），默认为utf8mb4_general_ci。

查看、删除数据库

显示数据库语句：

SHOW DATABASE

显示数据库创建语句：

SHOW CREATE DATABASE db_name

数据库删除语句(慎用)：

DROP DATABASE [IF EXISTS] db_name

在创建数据库表的时候为了规避规避关键字，可以用反引号`解决

CREATE DATABASE `CREATE`
DROP DATABASE `CREATE`

备份恢复数据库

• 备份数据库（注意：在DOS执行）cmd

mysqldump -u 用户名 -p -B 数据库1 数据库2 表n > 文件名.sql

• 回复数据库（注意：进入SQLyog再执行）cmd

Source 文件名.sql

创建表

基本语法：

CREATE TABLE table_name
(
				field1 datatype,
				field2 datatype,
				field3 datatype,
)character set 字符集  collate 校对规则 engine 引擎

field：指定列名
datatype：指定列类型（字段类型）
character set：如不指定则为所在数据库字符集
collate：如不指定则为所在数据库校对规则
engine：引擎（后续单独讲解）
注意：创建表时要根据所需保存的数据创建相应的列，并根据数据的类型定义相应的列类型。
比如：创建一个user表，要求：id 整形；name 字符串；password 字符串；birthday 日期。

CREATE TABLE `user`
(
	id INT,
	`name` VARCHAR(255),
	`password` VARCHAR(255),
	`birthday` DATE 
)
CHARACTER SET utf8mb4 COLLATE utf8mb4_bin ENGINE INNODB

常用数据类型

CREATE TABLE t2
(
	birthday DATE,
	jobtime DATETIME,
	logintime TIMESTAMP 
		NOT NULL DEFAULT CURRENT_TIMESTAMP 
		ON UPDATE CURRENT_TIMESTAMP -- 如果希望timestamp自动更新，则需要配置
);
INSERT INTO t2(birthday, jobtime) VALUES('2022-11-11','2022-11-11 10:10:10')
SELECT * FROM t2
-- 如果我们跟新表的某条记录，logintiem会自动以当前时间记录

修改表

基本语法：

#添加
ALTER TABLE table_name
ADD                 (column datatype [DEFAULT expr]
						 [,column datatype]...);

#修改
ALTER TABLE table_name
MODIFY                 (column datatype [DEFAULT expr]
						 [,column datatype]...);

#删除
ALTER TABLE table_name
DROP                 (column)

查看表的结构： desc 表名; -- 可以查看所有的列
修改表名：Rename table 表名 to 表名；
修改表字符集：alter table 表名 character set 字符集； 

操作：

#修改表
-- 给t2表增加一个image列，varchar类型，要求在birthday后面
ALTER TABLE t2
	ADD image VARCHAR(32) 
	NOT NULL 
	DEFAULT ' '
	AFTER birthday
	
DESC t2

#修改image列，使其长度为60
ALTER TABLE t2
	MODIFY image VARCHAR(32) 
	NOT NULL 
	DEFAULT ' '
	
#删除image列
 ALTER TABLE t2
	DROP image
	
#将列名image改为sb
ALTER TABLE t2 
	CHANGE image sb VARCHAR(64)
	NOT NULL
	DEFAULT ' '

数据库CRUD语句（create创建、retrieve检索、update更新、delete删除）

insert语句

基本语法

INSERT INTO table_name[(column[,,column...])]
VALUES          (value [,value...]);

操作

创建一张商品表goods(id int , goods_name varchar(10) , price double);
CREATE TABLE 'goods'(
	id INT,
	goods_name VARCHAR(10),
	price DOUBLE);

添加数据
INSERT INTO `goods` (id, goods_name, price)
	VALUES(10, '苹果手机', 2000);
INSERT INTO `goods` (id, goods_name, price)
	VALUES(20, '华为手机', 3000);

insert语句基本细节

1、插入的数据应与字段的数据类型相同。比如把abc添加到int类型会报错
2、数据的长度一个在列的规定范围内。比如：不能将长度为80的字符串加入到长度为40的列中。
3、在values中列出的数据位置必须与被加入的列的排列位置相对应。
4、字符和日期型数据应包含在单引号‘’中。
5、列可以插入空值，前提是该字段允许为空。

insert into table_name （）
				value(null)

6、可以同时添加多段数据

insert into table_name(1,2,3)
				values(),(),()

7、如果是给表中的所有字段添加数据，可以不写前面的字段名称。
8、默认值的使用，当不给某个字段值时，如果有默认值就是默认值，否则报错。比如：没有指定not null ,没有填写时默认为null。

CREATE TABLE 'goods'(
	...
	price DOUBLE NOT NULL DEFAULT 10);

update语句

基本语法

UPDATE table_name
				SET col_name1=expr1 [, col_name2 = expr2 ...]
				[WHERE where_definition]

操作

--创建员工表employee
CREATE TABLE employee (
	id INT,
	user_name VARCHAR(10),
	brithday DATETIME,
	entey_date DATETIME,
	job VARCHAR(10),
	salary INT,
	`resume` VARCHAR(10)
	);
INSERT INTO employee (id, user_name, salary)
	VALUES(100,'小妖怪',2000),(101,'老妖怪',3200),(201,'老不死',2300),(203,'吸血鬼',2340);
	
--将所有员工的薪水修改为5000
UPDATE	employee SET salary = 5000

--将姓名小妖怪的员工薪水修改为3000
UPDATE	employee 
	SET salary = 3000
	WHERE user_name = '小妖怪'
	
--将姓名老妖怪的薪水在原有的基础上增加1000
UPDATE	employee 
	SET salary = salary + 1000
	WHERE user_name = '老妖怪'

update语句基本细节

1、update语法可以用新值更新原有表行中的各列。
2、set子句表示要修改哪些列和要赋予哪些值。
3、where子句指定更新哪些行。如果没有where子句，则更新所有的行。
4、如果需要修改多个字段，可以通过 set字段1=值1，字段2=值2…

delete语句

基本语法

delete from table_name
			where where_definition

操作

-- 删除表中名称为吸血鬼的记录
DELETE FROM employee
	WHERE user_name = '吸血鬼';

-- 删除表中所有记录
DELETE FROM employee;

delete语句使用系列

1、没法删除具体某一列的值，最多用update设置为null。
2、delete不能删除表本身，只能用drop删除表本身。

select语句（单表）

基本语句

select [distinct] *| {column1, column2...}
			from table_name;
1、select指定查询哪些列的数据。
2、column指定列名。
3、*号代表查询所有列。
4、from指定查询哪张表。
5、distinct可选，指显示结果时，是否去掉重复数据。

在select语句中可使用as语句

select column_name as 别名 from 表名;

-- 统计每个学生的总分
SELECT `name`,(chinese+english+math) FROM student;

-- 给所有学生的总分加10分
SELECT `name`,(chinese+english+math+10) FROM student;

-- 使用别名表示学生分数
SELECT `name` AS '名字',(chinese+english+math+10) AS total_score FROM student;

在where语句中经常使用的运算符

-- 查询数学大于60并且英语大于90的学生姓名和成绩
SELECT `name`,english,math FROM student
	WHERE math > 60 AND english > 90;

-- 查询英语成绩大于语文成绩的同学
SELECT `name`,chinese,english FROM student
	WHERE	chinese < english;

-- 查询总分大于200分并且数学成绩小于语文成绩的姓张的学生
SELECT `name`,chinese,math,(chinese+english+math) AS total_score FROM student
	WHERE (chinese+english+math) > 200 AND chinese > math AND `name` LIKE '张%';

在select语句中用order by 子句排序查询结果

select [distinct] *| {column1, column2...}
			from table_name
			order by column asc|desc, ...
1、order by 指定排序的列，排序的列既可以是表中的列名，也可以是select语句后面指定的别名。
2、asc升序（默认），desc降序
3、order by 子句应位于select语句的结尾

操作

-- 对数学成绩排序后输出（升序）
SELECT `name`,math FROM student
	ORDER BY math;

-- 对总分从高到低的顺序输出（降序）
SELECT `name`,(chinese+english+math) AS total_score FROM student
	ORDER BY total_score DESC;

在select语句中用group by 子句对列进行分组

基本语句

select column1,column2... from table_name
			group by column

操作

-- 显示每个部门的平均工资和最低工资
SELECT AVG(salary),MIN(salary),deptno FROM emp
	GROUP BY deptno
	ORDER BY deptno;
	
-- 显示每个部门的各种岗位的平均工资和最低工资
SELECT AVG(salary),MIN(salary),deptno,job FROM emp
	GROUP BY deptno,job
	ORDER BY deptno;

在select语句中用having子句对分组后的结果进行过滤

一般与group by 搭配使用
操作

-- 显示平均工资低于2000的部门号和他的平均工资
SELECT deptno,AVG(salary) AS ms FROM emp
	GROUP BY deptno
	HAVING ms < 2000;

统计函数-count

基本语句

select count(*) |count(列名) from table_name
   		where where_definition

操作

-- 统计一个班共有多少名学生
SELECT COUNT(*) FROM student

-- 统计数学成绩大于60的学生有多少个
SELECT COUNT(*) FROM student
	WHERE math > 60
	
-- 统计总分大于250的人数
SELECT COUNT(*) FROM student
	WHERE (chinese+english+math) > 250

-- count(*)和count(列)的区别
count(*)返还满足条件的记录的行数
count(列)是统计满足条件的某列有多少个，但是会排除null
SELECT COUNT(`name`) FROM student
	WHERE math > 60

合计函数-sum、-avg、-max/min

sum函数返回满足where条件的行的和，一般使用在数值列
基本语法

select sum(列名) from table_name
	where where_definition

操作

-- 统计一个班数学总成绩
SELECT SUM(math) FROM student;

-- 统计一个班语文，数学，英语各科的总成绩
SELECT SUM(math),SUM(chinese),SUM(english) FROM student;

-- 统计一个班语文，数学，英语的成绩综合
SELECT SUM(math+chinese+english) AS total_score FROM student;

-- 统计一个班语文平均分
SELECT AVG(math) FROM student;

-- 统计一个班数学最高分
SELECT MAX(math) FROM student;

字符串相关函数

操作

-- charset（str） 返回字串字符集
SELECT CHARSET(ename) FROM emp

-- concat(string2[,...]) 连接字符串
SELECT CONCAT(ename ,' job is ', job) FROM emp

-- instr(string,substring) 返回substring在string中出现的位置，如果没有就返回0
SELECT INSTR('zmx' , 'x') FROM DUAL  # dual 是系统的亚元表，没有表可用时用来做测试表

-- ucase(string2) 转换成大写
SELECT UCASE(ename) FROM emp

-- lcase(string2) 转换成小写
SELECT LCASE(ename) FROM emp 

-- left(string2,length) 从string2中的左边起取length个字符
SELECT LEFT(ename,2) FROM emp
SELECT RIGHT(ename,2) FROM emp

-- length（string） 返回string长度【按照字节】
SELECT LENGTH(ename) FROM emp

-- replace（str,search_str,replace_str） 在str中用replace_str替换search_str
SELECT REPLACE(job,'经理','manager') FROM emp

-- strcmp(string1,string2) 逐字符比较俩字符串大小
SELECT STRCMP('zmx','zmx1') FROM DUAL

-- substring（str,position[,length]） 从str的position开始[从1开始计算]，取length个字符
SELECT SUBSTRING(ename,1,2) FROM emp

-- ltrim(string2) 去除前端空格 rtrim(string2) 去除后端空格 trim 去除两端空格
SELECT LTRIM(' zmx ') FROM DUAL
SELECT RTRIM(' zmx ') FROM DUAL
SELECT TRIM(' zmx ') FROM DUAL

-- 以首字母大写的方式显示所有员工的姓名
SELECT REPLACE(ename,SUBSTRING(ename,1,1),UCASE(SUBSTRING(ename,1,1))) AS new_name FROM FROM emp
SELECT REPLACE(ename, LEFT(ename,1),UCASE(LEFT(ename,1))) AS new_name FROM FROM emp
SELECT CONCAT(UCASE(SUBSTRING(ename,1,1)),SUBSTRING(ename,2)) AS new_name FROM emp

数学相关函数

操作

-- abs(num) 绝对值
SELECT ABS(-10) FROM DUAL

-- bin(decimal_number) 十进制转二进制
SELECT BIN(10) FROM DUAL

-- ceiling(number2) 向上取整
SELECT CEILING(10.1) FROM DUAL

-- conv(number2,from_base,to_base) 进制转换
SELECT CONV(8,10,2) #8是十进制，转成二进制

-- floor(number2) 向下取整
SELECT FLOOR(10.1) FROM DUAL

-- format(number,decimal_plaaces) 保留小数位
SELECT FORMAT(10.234142,2) FROM DUAL

-- hex(DecimalNumber)  转十六进制
SELECT HEX(100) FROM DUAL

-- least(number,number2....) 求最小值
SELECT LEAST(1,3) FROM DUAL

-- mod(numerator,denomniator) 求余
SELECT MOD(5,3) FROM DUAL

-- rand([seed]) 其范围为0<= v <=1.0,返回一个随机数
SELECT RAND() FROM DUAL
SELECT RAND(1) FROM DUAL # 固定rand(1)的值

时间日期相关函数

操作

-- current_date() 当前日期
SELECT CURRENT_DATE FROM DUAL

-- current_time 当前时间
SELECT CURRENT_TIME FROM DUAL

-- current_timestamp 当前时间戳
SELECT CURRENT_TIMESTAMP FROM DUAL

CREATE TABLE mes(
id INT,
content VARCHAR(30),
sendtime DATETIME
);

INSERT INTO mes VALUES(1,'北京新闻',CURRENT_TIMESTAMP)
INSERT INTO mes VALUES(2,'上海新闻',NOW())
INSERT INTO mes VALUES(3,'江苏新闻',NOW())

SELECT * FROM mes

-- 显示所有留言信息，发布日期只显示日期不显示时间
SELECT id,content,DATE(sendtime) FROM mes;

-- 查询在十分钟内发布的新闻
SELECT * FROM mes
	WHERE DATE_ADD(sendtime,INTERVAL 10 MINUTE) >= NOW()
	
-- 求出2011-11-11 和1990-1-1相差多少天
SELECT DATEDIFF('2011-11-11','1990-1-1') FROM DUAL

-- 求出你活了多少天
SELECT DATEDIFF(NOW(),'2002-02-03') FROM DUAL

-- 如果你能活到80岁，求你还能活多少天
SELECT DATEDIFF(DATE_ADD('2002-02-03',INTERVAL 80 YEAR),NOW()) FROM DUAL

-- date(datetime) 返回datetime的日期部分

-- date_add(date2,interval d_value d_type) 在date2中加上日期或时间

-- date_sub(date2,interval d_value d_type) 带date2中减去一个时间

-- datediff(date1.date2) 两个日期差（结果是天）

-- timediff(date1,date2) 两个时间差（结果是小时，分，秒）

-- now() 当前时间

-- year|month|date(datetime)  年月日
SELECT YEAR(NOW()) FROM DUAL
SELECT MONTH(NOW()) FROM DUAL
SELECT DAY(NOW()) FROM DUAL

-- unix_timestamp() 返还从开始到现在的秒数
SELECT UNIX_TIMESTAMP() FROM DUAL

-- from_unixtime() 把一个unix_timestamp()秒数转成指定格式的日期
SELECT FROM_UNIXTIME(1618483484,'%Y-%m-%d %H:%i:%s') FROM DUAL
#在开发中可以存放一个整数来存放时间。

加密和系统函数

操作

-- user() 查询用户
SELECT USER() FROM DUAL  #可以查看登录mysql的用户以及IP

-- database() 数据库名称
SELECT DATABASE() FROM DUAL

-- md5(str) 为字符串算出一个MD5 32的字符串，（用户密码）加密
SELECT MD5('123456') FROM DUAL 
SELECT LENGTH(MD5('123456')) FROM DUAL # md5是32位

-- password(str) 也是加密函数,mysql默认的加密函数
SELECT PASSWORD('123456') FROM DUAL;

-- select * from mysql.user \G 从原文密码str计算并返回密码字符串，通常用于对mysql
SELECT * FROM mysql.user

-- 创建用户表，并新建一个用户
CREATE TABLE users(
id INT,
`name` VARCHAR(32) NOT NULL DEFAULT '',
pwd CHAR(32) NOT NULL DEFAULT ''
);

INSERT INTO users VALUES(100,'zmx',MD5('zmx'))

SELECT * FROM users
	WHERE `name` = 'zmx' AND pwd = 'zmx' #查不到
SELECT * FROM users
	WHERE `name` = 'zmx' AND pwd = MD5('zmx')	

流程控制函数

操作

-- if(e1，e2，e3） 如果e1为True，则返回e2,否则返回e3
SELECT IF(TRUE,'北京','上海') FROM DUAL;
SELECT IF(FALSE,'北京','上海') FROM DUAL;

-- ifnull(e1,e2) 如果e1不为null，则返回e1,否则返回e2
SELECT IFNULL(NULL,'北京') FROM DUAL;
SELECT IFNULL('北京','上海') FROM DUAL;

-- select case when e1 then e2 when e3 then e4 else e5 end 如果e1为Ture,则返还e2,如果e3为True，则返还e4,否则返还e5
SELECT CASE
	WHEN TRUE THEN 'jack'
	WHEN FALSE THEN 'ben'
	ELSE 'pig'
	END

-- 查询emp表，如果comm是null，则显示0.0
SELECT ename,IF(comm IS NULL , 0.0 , comm) AS com FROM emp
SELECT ename,IFNULL(comm , 0.0) AS com FROM emp

-- 如果emp表的job是CLERK则显示职员，manager显示经理，salesman显示销售，其他则正常显示
SELECT ename , (SELECT CASE
		WHEN job = 'clerk' THEN '职员'
		WHEN job = 'manager' THEN '经理'
		WHEN job = 'salesman' THEN '销售'
		ELSE job
		END) AS 'job'
	FROM emp

select查询-加强

where、like

-- 使用where子句查找1992.1.1后入职的员工
SELECT * FROM emp
	WHERE hiredate > '1991-1-1';
	
-- 使用like(模糊）
-- 		%：表示0到多个容易字符
--		_: 表示单个任意字符
-- 显示首字符为s的员工姓名和工资
SELECT ename,salary FROM emp
	WHERE ename LIKE 's%';

-- 显示名字第三个字母为o的员工的姓名和工资
SELECT ename,salary FROM emp
	WHERE ename LIKE '__o%';

-- 显示没有上级的员工的信息
SELECT * FROM emp
	WHERE mgr IS NULL;
	
-- 查询表的结构
DESC emp

order by

-- 使用order by 子句
-- 按照工资的高低（升序）来显示雇员的信息
SELECT * FROM emp
	ORDER BY salary ASC;
	
-- 按照部门号升序，而工资降序的方式来显示雇员的信息
SELECT * FROM emp
	ORDER BY deptno ASC , salary DESC;

分页查询

基本语法

select ... limit start, rows
表示从start + 1行开始取，取出rows行 ,start 从0开始计算

操作

-- 按雇员的id号升序取出，每页显示3条记录,分别显示前两页。
SELECT * FROM emp
	ORDER BY empno ASC
	LIMIT 0 , 3;#第一页
SELECT * FROM emp
	ORDER BY empno ASC
	LIMIT 1 , 3;#第二页
	-- limit公式  limit 每页显示的记录数*（第几页-1） , 每页显示记录数

group by

-- 显示每种岗位的雇员总数、平均工资
SELECT job,COUNT(*),AVG(salary) FROM emp
	GROUP BY job;

-- 显示雇员总数，以及获得补助的雇员数
SELECT COUNT(*),COUNT(comm) FROM emp   # count不会统计空值
-- 没有获得补助的员工数
SELECT COUNT(*),COUNT(IF(comm IS NULL , 1 ,NULL)) FROM emp 

-- 显示管理者的总人数
SELECT COUNT(mgr) FROM emp # 有重复
SELECT COUNT(DISTINCT mgr) FROM emp

-- 显示雇员工资的最大差额
SELECT (MAX(salary)-MIN(salary)) AS sal_diff FROM emp

顺序

操作

-- 统计每个部门的平均工资，并且是大于1000的，并且按照平均工资从高到低排序,取出前两行数据
SELECT deptno , AVG(salary) AS avgsal FROM emp
	GROUP BY deptno
	HAVING avgsal > 1000
	ORDER BY avgsal DESC
	LIMIT 0 , 2;

多表查询

以emp、dept、salgrade三表为例

-- 显示雇员名，雇员工资以及所在部门的名字[笛卡尔集]
SELECT * FROM emp , dept #emp表的每一行都与dept表的每行相乘，列相加，行相乘，默认处理方式成为笛卡尔集
SELECT ename , salary , dname , emp.deptno FROM emp , dept
	WHERE emp.deptno = dept.deptno
-- tips:多表查询的条件不能少于表的个数-1，否则会出现笛卡尔集

-- 显示部门号为10的部门名、员工名和工资
SELECT ename , emp.deptno , salary FROM emp , dept
	WHERE emp.`deptno` = dept.`deptno`  AND emp.`deptno` = 10

-- 显示各个员工的姓名，工资，以及其工资的级别
SELECT ename , salary , salgrade.`grade` FROM emp , salgrade
	WHERE emp.`salary` BETWEEN salgrade.`losal` AND salgrade.`hisal`

自连接

自连接是指将同一张表的连接查询看作两张表来使用。
操作

-- 显示公司员工和他的上级的名字
SELECT worker.ename , boss.ename FROM emp AS worker , emp AS boss
	WHERE worker.`mgr` = boss.`empno`

自连接特点：
1、把同一张表当作两张表来使用
2、需要给表取别名
3、列名不明确，要指定列的别名

子查询

引例

-- 显示与smith同一个部门的所有员工
-- select deptno from emp
--	where ename = 'smith'  第一步

SELECT * FROM emp
	WHERE deptno = (
	SELECT deptno FROM emp
		WHERE ename = 'smith' 
	);


-- 多行子查询
-- 查询和10号部门的工作相同的员工的名字、岗位、工资、部门号，但是不含10号部门自己的
SELECT ename , job , salary , deptno FROM emp
	WHERE job IN (
		SELECT DISTINCT job FROM emp
			WHERE deptno = 10
	)
	AND deptno != 10

子查询当做临时表使用

-- 得到各个类别中价格最高的商品
SELECT cat_id , MAX(shop_price) FROM scshop 
	GROUP BY cat_id；

SELECT temp.cat_id , goods_id , goods_name , shop_price FROM(
	SELECT cat_id , MAX(shop_price) AS max_price FROM scshop 
		GROUP BY cat_id；
	) AS temp , ecs_goods
	WHERE temp.cat_id = ecs_goods.cat_id
	AND tep.max_price = ecs_goods.shop_price

all操作符

全部

-- 显示工资比部门30的所有员工高的员工的姓名、工资和部门号
SELECT ename , salary , deptno FROM emp
	WHERE salary > ALL(
		SELECT salary FROM emp
			WHERE deptno = 30
		);

any操作符

其中一个 = min()

-- 显示工资比部门30的其中一个员工高的员工的姓名、工资和部门号
SELECT ename , salary , deptno FROM emp
	WHERE salary > ANY(
		SELECT salary FROM emp
			WHERE deptno = 30
		);
也可以写成
SELECT ename , salary , deptno FROM emp
	WHERE salary > (
		SELECT MIN(salary) FROM emp
			WHERE deptno = 30
		);

多列子查询

-- 查询与smith的部门和岗位完全相同的所有雇员
#第一步，查询allen的部门和岗位
SELECT depno , job FROM emp
	WHERE ename = 'allen'
#第二布，把上面的查询当作子查询来使用，并且使用多列子查询的语法进行匹配
SELECT * FROM emp
	WHERE (deptno , job) = (
			SELECT deptno , job FROM emp
				WHERE ename = 'allen'
			)
	AND ename != 'allen'

-- 查询与宋江数学、英语、语文成绩完全相同的学生
SELECT math , chinese , english FROM student
	WHERE `name` = '张飞'

SELECT * FROM student
	WHERE (math , chinese , english) = (
		SELECT math , chinese , english FROM student
			WHERE `name` = '张飞'
		)
	AND `name` != '张飞'

子查询练习

-- 查找每个部门工资高于本部门平均工资的人的资料
# 1、得到每个部门的部门号和平均工资
SELECT deptno , AVG(salary) AS avg_sal FROM emp
	GROUP BY deptno;
# 2、把上面的结果当作子查询，和emp进行多表查询	
SELECT * FROM emp , (SELECT deptno , AVG(salary) AS avg_sal FROM emp
				GROUP BY deptno
	) AS temp
	WHERE emp.`deptno` = temp.deptno
	AND emp.`salary` > temp.avg_sal
	
	
-- 查找每个部门工资最高的人的详细资料
SELECT deptno , MAX(salary) AS max_sal FROM emp
	GROUP BY deptno
	
SELECT * FROM emp , (
	SELECT deptno , MAX(salary) AS max_sal FROM emp
		GROUP BY deptno
	) AS temp 
	WHERE emp.`deptno` = temp.deptno
	AND emp.`salary` = temp.max_sal


-- 查询每个部门的信息（包括：部门名，编号，地址和人员数量）
# 分析： 部门名、地址来自dept表，编号dept、emp,人员数量-临时表
SELECT deptno , COUNT(*) AS num_peop FROM emp
	GROUP BY deptno;
SELECT dname, loc , temp.* 
	FROM dept , (
		SELECT deptno , COUNT(*) AS num_peop 
			FROM emp
			GROUP BY deptno
	) AS temp
	WHERE dept.`deptno` = temp.deptno

表复制、删除表的重复记录

自我复制数据（蠕虫复制）

-- 有时，为了对某个sql语句进行效率测试，我们需要海量数据时，可以使用此法为表创建海量数据
CREATE TABLE my_tab01(
	id INT,
	`name` VARCHAR(32),
	sal DOUBLE,
	job VARCHAR(32),
	deptno INT
	);
DESC my_tab01;
SELECT * FROM my_tab01;

-- 演示如何自我复制
-- 1、先把emp表的记录复制到my_tab01
INSERT INTO my_tab01
	(id,`name`,sal,job,deptno)
	SELECT empno,ename,salary,job,deptno FROM emp;

-- 2、自我复制
INSERT INTO my_tab01
	SELECT * FROM my_tab01;


-- 思考题：如何删除一张表的重复记录
-- 1、先创建一张表 my_tab02
-- 2、让 my_tab02 有重复的记录
CREATE TABLE my_tab02 LIKE emp; # 这个语句， 把emp表的结构（列），复制到my_tab02
DESC my_tab02;
INSERT INTO my_tab02
	SELECT * FROM emp;
SELECT * FROM my_tab02;
-- 3、考虑去重
/*
思路：	1、创建一张临时表temp，该表的结构与my_tab02一样
	2、把temp的记录通过distinct关键字把处理后的记录复制到tenp
	3、清楚掉my_tab02 记录
	4、把temp表记录复制到my_tab02
	5、drop掉临时表temp
*/
CREATE TABLE temp LIKE my_tab02;
INSERT INTO temp
	SELECT DISTINCT * FROM my_tab02;
DELETE FROM my_tab02;
INSERT INTO my_tab02
	SELECT * FROM temp;
DROP TABLE temp;
SELECT * FROM my_tab02;

合并查询union、union all

有时在实际应用中，为了合并多个select语句的结果，可以使用合集操作符号union,union all
union all 不会去重； union会去重

-- 合并查询
SELECT ename , salary , job FROM emp
	WHERE salary > 2500

SELECT ename , salary , job FROM emp
	WHERE job = 'manager'

-- union all 就是将两个查询结果合并，不会去重
SELECT ename , salary , job FROM emp
	WHERE salary > 2500
UNION ALL
SELECT ename , salary , job FROM emp
	WHERE job = 'manager'

-- union 与union all 相同，但是会自动去重
SELECT ename , salary , job FROM emp
	WHERE salary > 2500
UNION
SELECT ename , salary , job FROM emp
	WHERE job = 'manager'

表外连接

引例

-- 用多表查询列出部门名称和这些部门的员工名称和工作，同时要求显示那些没有员工的部门。
SELECT 	dname , ename , job
	FROM emp , dept
	WHERE emp.`deptno` = dept.`deptno`
	ORDER BY dname;
-- 多表查询中，只能显示匹配上的内容，匹配不上的不显示 比如40号部门就不显示出来

这时就需要用到外连接
外连接分为左外连接跟右外连接:
1、左外连接：如果左侧的表完全显示我们就说时左外连接
2、右外连接：如果右侧的表完全显示我们就说时右外连接

基本语法

左外连接
select ... from tab_01 left join tab_02  
			on 条件
右外连接
select ... from tab_01 right join tab_02  
			on 条件

操作

-- 创建stu
CREATE TABLE stu(
	id INT,
	`name` VARCHAR(32)
	);
INSERT INTO stu VALUES(1,'jack'),(2,'tom'),(3,'kity'),(4,'nono');
SELECT * FROM stu;
-- 创建 exam
CREATE TABLE exam(
	id INT,
	grade INT
	);
INSERT INTO exam VALUES(1,56),(2,76),(11,8);
SELECT * FROM exam;
-- 使用左外连接，显示所有人的成绩，如果没有成绩，也要显示该人的姓名和id,成绩显示为空
SELECT `name`,stu.`id`,grade
	FROM stu LEFT JOIN exam
	ON stu.`id` = exam.`id`;
-- 使用右外连接，显示所有人的成绩，如果没有名字示为空
-- 右边的表和左表没有匹配的记录，也会把右表的记录显示出来
SELECT `name`,stu.`id`,grade
	FROM stu RIGHT JOIN exam
	ON stu.`id` = exam.`id`;
	
-- 练习：用多表查询列出部门名称和这些部门的员工名称和工作，同时要求显示那些没有员工的部门。
-- 左外连接
SELECT dname,ename,job 
	FROM dept LEFT JOIN emp
	ON dept.`deptno` = emp.`deptno`
-- 右外连接
SELECT dname,ename,job 
	FROM emp RIGHT JOIN dept
	ON dept.`deptno` = emp.`deptno`

mysql 约束

约束主要用于确保数据库的数据满足特定的商业规则。
约束主要包括：not null 、unique 、primary key 、foreign key和check五种

primary key

主要用于唯一的标示表行的数据，当定义主键约束后，该列不能重复

CREATE TABLE t17(
	id INT PRIMARY KEY,  -- 表示id列是主键
	`name` VARCHAR(32),
	email VARCHAR(32)
	);

-- 主键列的值是不可以重复的
INSERT INTO t17
	VALUES(1,'jack','jack@souhu.com'),(2,'tom','tom@souhu.com');

INSERT INTO t17 VALUES(1,'zmx','zmx@souhu.com'); -- 报错 id 1 不可重复

SELECT * FROM t17;

细节：
1、主键不能重复且不可以为空
2、一张表最多有一个主键，但可以是复合主键

-- 报错，一张表不能有两个主键
CREATE TABLE t18(
	id INT PRIMARY KEY,
	`name` VARCHAR(32) PRIMARY KEY,
	email VARCHAR(32)
	);
-- 但可以设置复合主键 , 只有同时满足id,name相同才不能生成
CREATE TABLE t18(
	id INT,
	`name` VARCHAR(32),
	email VARCHAR(32),
	PRIMARY KEY (id , `name`)
	);

3、主键的指定方式有两种

第一钟
CREATE TABLE t17(
	id INT PRIMARY KEY,  -- 表示id列是主键
	`name` VARCHAR(32),
	email VARCHAR(32)
	);
第二种
CREATE TABLE t18(
	id INT,
	`name` VARCHAR(32),
	email VARCHAR(32),
	PRIMARY KEY (id)
	);

4、使用desc可以查看主键（表的结构）

unique(唯一)

-- unique 的使用
CREATE TABLE t21(
	id INT UNIQUE,  -- 表示id列是不可重复的
	`name` VARCHAR(32),
	email VARCHAR(32)
	);

INSERT INTO t21
	VALUES(1,'jack','jack@souhu.com'),(2,'tom','tom@souhu.com');

INSERT INTO t21 VALUES(1,'tom','111') -- 报错，不可重复

细节
1、如果没有指定 not null ，则unique字段可以有多个null

INSERT INTO t21 VALUES(NULL,'tom','111'),(NULL,'pig','1112222')

2、一张表可以有多个unique字段

foreign key(外键)

用于定义主表和从表之间的关系：外键约束要定义在从表上，主表必须具有主键约束或是unique约束。当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null(学生/班级 图示)

基本语句

foreign key （本表字段名）references 主表名（主键名或unique字段名）

操作

-- 先创建主表class
CREATE TABLE class(
	id INT PRIMARY KEY,
	`name` VARCHAR(30) NOT NULL DEFAULT '',
	address VARCHAR(30)
	);
-- 创建从表cla_stu
CREATE TABLE cla_stu(
	id INT PRIMARY KEY,
	`name` VARCHAR(32) NOT NULL DEFAULT '',
	class_id INT,
	FOREIGN KEY (class_id) REFERENCES class(id) -- 创建外键
	);

INSERT INTO class VALUES(100,'java','北京'),(200,'mysql','上海');
INSERT INTO cla_stu VALUES(1,'tom',100),(2,'jack',200);
INSERT INTO cla_stu VALUES(3,'pig',300); -- 报错，在class中300 号班级不存在

细节
1、外键指向的表的字段，要求是primary key 或者是unique
2、表达类型是inodb，这样的表才支持外键
3、外键字段的类型必须要和主键字段的类型一致（长度可以不一致）
4、外键字段的值，必须在主键字段中出现过，或者为null(前提是主键字段允许为null)
5、一但建立主外键的关系，数据就不能随意删除了。删除时要先删除从表才能删除主表。

check

用于强制行数据必须满足的条件，假定在sal列上定义了check约束，并且要求sal的值在1000~2000之间如果不在就会提示出错。

CREATE TABLE t23(
	id INT PRIMARY KEY,
	`name` VARCHAR(32),
	sex VARCHAR(6) CHECK (sex IN('man','woman')),
	sal DOUBLE CHECK (sal BETWEEN 1000 AND 2000)
	);
INSERT INTO t23 VALUES(1,'jack','mid',1); -- 报错

练习

现有一个商店的数据库shop_db，记录客户及其购物情况，由下面三个表组成：商品表goods(商品id goods_id,商品名goods_name,单价unitprice,类别category,供应商probider)，客户customer(客户号cus_id,姓名name,住址address,邮箱email,性别sex,身份证card_id)，购买purchase(订单号order_id, 客户号cus_id, 商品号goods_id, 购买数量nums)
要求：建表，在定义中要求声明
1、每个表的主外键
2、客户的姓名不能为空值
3、电邮不能够重复
4、客户的性别【男/女】
5、单价unitprice在1.0-9999.99之间

CREATE DATABASE shop_db;
CREATE TABLE goods(
	goods_id INT PRIMARY KEY NOT NULL,
	goods_name VARCHAR(32) NOT NULL,
	unitprice DOUBLE CHECK (unitprice BETWEEN 1.0 AND 9999.99),
	category INT NOT NULL DEFAULT 0,
	provider VARCHAR(32)
	);
CREATE TABLE customer(
	cus_id INT PRIMARY KEY NOT NULL,
	address VARCHAR(32),
	email VARCHAR(32) UNIQUE,
	sex VARCHAR(6) CHECK (sex IN('man','woman')),
	card_id INT
	);
CREATE TABLE purchase(
	order_id INT PRIMARY KEY NOT NULL,
	cus_id INT NOT NULL,
	goods_id INT NOT NULL,
	nums INT NOT NULL DEFAULT 0,
	FOREIGN KEY (cus_id) REFERENCES customer(cus_id),
	FOREIGN KEY (goods_id) REFERENCES goods(goods_id)
	);

自增长

基本语句

字段名 整型 primary key auto_increment

-- 添加自增长的字段方式
insert into xxx(字段1，字段2.,,,) values (null , '值' ...) -- 从1开始自增长
-- 方法 2
insert into xxx(字段2.,,,) values ( '值1','值2' ...)
 --方法3
insert into xxx  values (null , '值1' ...)

操作

INSERT INTO t24
	VALUES(NULL , 'jack@qq.com' , 'jack');

INSERT INTO t24
	VALUES(NULL , 'tom@qq.com' , 'tom');

细节：
1、自增长一般与primary key配合使用
2、自增长也可以单独使用，但需要配合一个unique
3、自增长修饰的字段为整数型的（极少情况用在小数上）
4、自增长默认从1开始，也可以通过以下命令修改

alter table table_name auto_increment = x

5、如果添加数据时，给自增长字段（列）指定的值，则以指定的值为准（可以不用自增长，但是之后自增长的值从你指定的值开始。）

INSERT INTO t24
	VALUES(666 , 'tom@qq.com' , 'tom');
INSERT INTO t24
	VALUES(NULL , 'tom@qq.com' , 'tom');

索引