MySQL课堂查询

一.简单查询语句

1.查表内数据

SELECT * FROM emp; #员工表
SELECT * FROM dept; #部门表
SELECT * FROM student; #学生表
SELECT * FROM course; #课程表
SELECT * FROM score; #成绩表
SELECT * FROM salgrade;#工资等级表

2.查询员工工号，姓名，职位

SELECT empno,ename,job FROM emp;

3.查询工号，姓名，工资以及工资加500之后工资；

SELECT empno,ename,sal,sal+500 FROM emp;

4.查询员工的工资，奖金和工资与奖金和；

SELECT ename,sal,com,sal+comm form emp;

5.AS改列名

#查询员工姓名与年薪
SELECT ename AS 姓名,sal*12 AS 年薪 FROM emp;

#修改为含有空格的列名
SELECT ename AS 姓名,sal*12 AS '年  薪' FROM emp;

6.简单算术运算

SELECT 25*40;
SELECT "hello",'1999-12-12';

7.Distinct去重

#部门编号和工作去重
SELECT DISTINCT deptno,job FROM emp;

8.where语句

#部门号为10 的员工
SELECT * FROM emp WHERE deptno=10;

#职位是salesman的员工
SELECT * FROM emp WHERE job='SALESMAN';

#入职日期是1980-12-17的员工
SELECT * FROM emp WHERE hiredate='1980-12-17';

#查询工资大于等于1500
SELECT * FROM emp WHERE sal>=1500;

#查询工资小于等于3000
SELECT * FROM emp WHERE sal<=3000;

#查询职位不是cleck的员工
SELECT * FROM emp WHERE job<>'cleck'; #不等于<>

#查询奖金比工资高的员工
SELECT * FROM emp WHERE comm>sal;

9.Between and

#查询工资在1500和3000之间的员工
SELECT * FROM emp WHERE sal BETWEEN 1500 AND 3000;

#查询1981年入职的员工
SELECT * FROM emp WHERE hiredate BETWEEN '1981-01-01' AND '1981-12-31';
#知道日期格式的情况下
SELECT * FROM emp WHERE hiredate LIKE '1981%';

10.In用法

#查询工资是1500或者3000的员工
SELECT * FROM emp WHERE sal IN (1500,3000);

11.通配符%和_

#0个或者任意多个字符
#_一个字符
#查询姓张的同学
SELECT * FROM student WHERE sname LIKE'张%';
#查询名字第二个字是小的同学
SELECT * FROM student WHERE sname LIKE'_小%';
#查询名字最后一个字是文的同学
SELECT * FROM student WHERE sname LIKE'%文';

12.转义符

#查询名字最后是%的同学,放在想转换的字符前面
SELECT * FROM student WHERE sname LIKE'%\%';

13.Null

#查询奖金为null的员工
SELECT * FROM emp WHERE comm IS NULL;
#查询奖金不为null的员工
SELECT * FROM emp WHERE comm IS NOT NULL;

14.And 和 Or

#查询部门10工资大于1500的员工
SELECT * FROM emp WHERE sal>1500 AND deptno=10;
#查询部门10或者工资大于1500的员工
SELECT * FROM emp WHERE sal>1500 OR deptno=10;

#查询部门10和部门20的员工
SELECT * FROM emp WHERE deptno=10 OR deptno=20;
SELECT * FROM emp WHERE deptno IN(10,20);

15.Not的用法

#直接将判定条件反转
SELECT * FROM emp WHERE NOT 1=1;

#查询工资不在1500和3000之间的员工
SELECT * FROM emp WHERE NOT sal BETWEEN 1500 AND 3000;
#查询不信张的同学
SELECT * FROM student WHERE sname NOT LIKE '张%';
#查询部门号不是10也不是20的员工
SELECT * FROM emp WHERE NOT deptno IN(10,20);

16.优先级问题

#查询部门号是10，和部门号为20且工资大于1500的
SELECT * FROM emp WHERE deptno=10 OR deptno=20 AND sal>1500;
#查询部门号是10和部门号为20且工资都大于1500的
SELECT * FROM emp WHERE (deptno=10 OR deptno=20) AND sal>1500;

17.Order by 排序

#desc降序，asc升序(不写默认升序)
#查询emp表，显示奖金不为空或者部门号为20的雇员信息，要求按照薪水降序排列
SELECT * FROM emp WHERE comm IS NOT NULL OR deptno=20 ORDER BY sal DESC;

#查询emp表，显示年薪大于30000，工作类别不是MANAGER，且部门号不是10或40(既不是10，也不是40)的雇员信息
#要求按照雇员姓名进行排列
SELECT * FROM emp WHERE sal*12>30000 
AND job<>'Manager' 
AND deptno NOT IN(10,40) 
ORDER BY ename;

#入职日期最早到最晚排序
SELECT * FROM emp ORDER BY hiredate;

双列排序注意

#查询员工信息，按照部门号升序，工资降序
SELECT * FROM emp ORDER BY deptno,sal DESC;
#第一列没有重复的数据，第二列数据将毫无意义
#所以先部门号，再工资

18.别名排序

#年薪倒序查询
SELECT ename,sal*12 FROM emp ORDER BY sal12 DESC;
#年薪倒序查询
SELECT ename,sal*12 年薪 FROM emp ORDER BY 年薪 DESC;

不能使用列的别名在where语句

执行顺序

fromwhereselect 定义别名order by

19.Limit

SELECT * FROM emp LIMIT 0,3;
SELECT * FROM emp LIMIT 3;# 0可以省略
SELECT * FROM emp LIMIT 2,3;#从第三行显示三行

#查询公司最高工资
SELECT * FROM emp 
ORDER BY sal DESC
LIMIT 1;

#写零默认整数
SELECT ROUND(123.45,1), ROUND(123.454);

20.Round函数

#查询员工姓名，日薪，保留两位，
SELECT ename ,ROUND (sal/22,2) FROM emp;

21.大小写转换

SELECT UPPER('adcd'),LOWER('abcd')

#查询员工姓名，显示小写字母
SELECT LOWER(ename) FROM emp;

22.拼接字符串(后面会用)

SELECT CONCAT('test','mysql');
SELECT ename, job FROM emp;
SELECT CONCAT(ename,' is a ',job) FROM emp;

23.Length长度函数

#1个英文字符占1个字节
#1个中文字符占3个字节
SELECT LENGTH('test'),CHAR_LENGTH('test');
SELECT LENGTH('汉字'),CHAR_LENGTH('三个');

24.Left，Right函数

#从abcde左取三个，abcde右取两个
SELECT LEFT('adcde',3),RIGHT('adcde',2);

查找姓张的
SELECT * FROM student WHERE LEFT(sname,1)='张';
SELECT * FROM student WHERE RIGHT(sname,1)='文';

25.Mid,Subtr,Subtring函数

SELECT MID('abcdefg',2,4),
       SUBSTR('abcdefg',2,4),
       SUBSTRING('abcdefg',2);
#从第二个开始，只取一个
SELECT * FROM student WHERE MID(sname,2,1)='小';

26.IFNull

#不为空输出NULL，为空输出10
SELECT IFNULL(NULL,10);
SELECT ename,sal,comm, IFNULL(comm,0),sal+comm FROM emp;

27.IF函数

SELECT ename,sal,IF(sal>5000,'富','穷') FROM emp;

28.日期函数Month（）

#查询2月入职的员工
SELECT * FROM emp WHERE MONTH(hiredate)=02;
#格式必须提前知道
SELECT * FROM emp WHERE hiredate LIKE '____-02%';

29.Datediff()

#获取天数差值
SELECT DATEDIFF('2025-10-18','2025-10-01');

#查询每个员工的工作年限
SELECT ename,DATEDIFF(CURDATE(),hiredate)/365,
YEAR(CURDATE())-YEAR(hiredate)
FROM emp;

30.当前日期函数

#获取当前日期
SELECT 
CURDATE(),
CURTIME(),
NOW();
#获取当前年月日
SELECT 
YEAR('1997-7-1'),
MONTH('1997-7-1'),
DAY('1997-7-1');

二.多表查询

#查询员工工号，姓名，部门号，部门地址；
/*  思路
1.生成笛卡尔乘积
select * from emp,dept; 
2.添加连接条件，
目的是从笛卡尔乘积中过滤有效数据
select * from
*/
SELECT empno,ename,emp.deptno,loc 
FROM emp,dept 
WHERE 
emp.deptno
=dept.deptno;

1.等值连接：连接条件使用等号

2.不等连接：连接条件使用处理等号以外的比较符号

#查询名字为King的员工编号，员工名，部门编号和部门地址
SELECT empno,ename,e.deptno,loc 
FROM emp e,dept d
WHERE 
e.deptno=d.deptno 
AND ename='king';

#查询学生的课程成绩
SELECT s.sname,c.cname,sc.grade
FROM student s,score sc,course c
WHERE 
s.sid=sc.sid 
AND sc.cid=c.cid;

#找出选修了“LoadRunner”课程但成绩低于80分的学生姓名。
SELECT s.sname,c.cname,sc.grade
FROM student s,score sc,course c
WHERE 
s.sid=sc.sid AND sc.cid=c.cid
AND c.cname='LoadRunner'
AND sc.grade<80;

3.不等连接

#员工的姓名,工资和工资等级
SELECT e.ename,e.sal,sal.grade
FROM emp e,salgrade sal
WHERE e.sal 
BETWEEN sal.losal AND sal.hisal;

按照连接的表是否是一个表自连接：将一个表看成多个表进行连接非自连接：不同表进行连接

4.自连接

#查询员工的姓名及其主管的姓名
SELECT w.ename 员工,m.ename 主管
FROM emp w,emp m
WHERE w.mgr=m.empno;

#查询入职日期比自己的主管早的员工
SELECT *
FROM emp w,emp m
WHERE 
w.mgr=m.empno 
#要先判断主管和员工,再比较
AND w.hiredate<m.hiredate;

5.内连接（inner join）

–满足连接条件的记录

#查询员工的姓名及其主管的姓名
#ISO99标准
SELECT w.ename 员工,m.ename 主管
FROM emp w JOIN emp m
ON w.mgr=m.empno;

6.外连接（outer join）

–将不满足连接条件的记录也显示出来。ps没有主管也显示

7.左外连接（left join）

#查询员工的姓名及其主管的姓名,将没有主管的也显示出来
SELECT w.ename 员工,m.ename 主管
FROM emp w LEFT JOIN emp m
ON w.mgr=m.empno;

8.右外连接（right join）

9.全外连接（left join）

#插入一名员工
INSERT INTO emp(empno,ename,job,sal)
VALUES(7901,'JACK','CLERK',3500);

#查询员工员工的工号，姓名，部门，部门地址
SELECT e.empno,e.ename,e.deptno,d.loc
FROM emp e JOIN dept d
ON e.deptno=d.deptno;

#查询员工员工的工号，姓名，部门，部门地址
#没有员工的部门也查询出来
SELECT e.empno,e.ename,d.deptno,d.loc
FROM emp e RIGHT JOIN dept d
ON e.deptno=d.deptno;

#查询员工员工的工号，姓名，部门，部门地址
#没有部门的员工也查询出来
SELECT e.empno,e.ename,e.deptno,d.loc
FROM emp e LEFT JOIN dept d
ON e.deptno=d.deptno;

#删除刚插入的一行
DELETE FROM emp WHERE empno=7901;

#查询工资等级为3/4/5级的员工姓名
#工资，工资等级,工资等级升序
SELECT e.ename,e.sal,sal.grade
FROM emp e,salgrade sal
WHERE e.sal 
BETWEEN sal.losal AND sal.hisal 
AND sal.grade IN (3,4,5)
ORDER BY sal.grade;

#列出主管的名字。
SELECT DISTINCT m.ename  #去重
FROM emp w RIGHT JOIN  emp m
ON w.mgr=m.empno;

#列出部门名称和这些部门的员工信息，
#同时列出那些没有员工的部门。
SELECT d.dname,e.* 
FROM emp e RIGHT JOIN dept d
ON e.deptno=d.deptno;

#列出成绩高于80分的学生姓名、课程名称和成绩。
SELECT st.sname,c.cname,s.grade
FROM student st,score s,course c
WHERE
st.sid=s.sid 
AND s.cid=c.cid
AND s.grade>80;

#查询公司的平均工资，最高工资，最低工资
SELECT 
AVG(sal),MAX(sal),MIN(sal),SUM(sal)
FROM emp;

#查询manager的平均工资，最高工资，最低工资
SELECT 
job,AVG(sal),MAX(sal),MIN(sal),SUM(sal)
FROM emp
WHERE job='manager';

#最早晚的入职日期
SELECT MIN(hiredate),MAX(hiredate)
FROM emp;

10.COUNT函数

#count不统计空值
#count统计重复值
SELECT
COUNT(empno),COUNT(comm),
COUNT(DISTINCT deptno),
COUNT(*) 
FROM emp;

11.AVG函数

#avg不统计空值
#查询平均佣金
SELECT 
AVG(comm),#非null/非NULL
SUM(comm)/14,#非null/14
AVG(IFNULL(comm,0))#（非null+0*null员工）/14
FROM emp;

12.分组函数

——出现在select后面的列，必须是group by的列或者聚合函数

#查询每个部门的平均工资
#逻辑
#1. FROM子句：首先从 emp 表中获取所有数据行，这是查询的数据源。
#2. GROUP BY子句：将 emp 表中的数据按照 deptno （部门编号）进行分组，相同 deptno 的行被归为一组。
#3. 聚合计算（AVG(sal)）：对每个分组内的 sal （工资）字段执行 AVG() 函数，计算每组的平均工资。
#4. SELECT子句：从分组后的结果中选取 deptno （分组依据字段）和计算得到的平均工资 AVG(sal) ，作为最终结果集返回。
SELECT deptno,AVG(sal) 
FROM emp 
GROUP BY deptno;

#查询每个职位的平均工资
SELECT job,AVG(sal) 
FROM emp
GROUP BY job;

#查询学生表中每个年龄的人数
SELECT sage,COUNT(sage) FROM student 
GROUP BY sage;

#查询学生表中男生和女生的人数
SELECT ssex,COUNT(*) FROM student 
GROUP BY ssex;

#查询学生表中每个年龄的人数男生和女生的人数
SELECT sage,ssex,COUNT(*)
FROM student
GROUP BY sage,ssex;

#查询每个部门的人数
SELECT deptno,COUNT(empno)
FROM emp
GROUP BY deptno;

#查询每个部门的人数
#没有员工的部门也输出
SELECT d.deptno,COUNT(empno)
FROM emp e RIGHT JOIN dept d
ON e.deptno=d.deptno
GROUP BY d.deptno;

#查询每个职位的人数
SELECT job,COUNT(empno)
FROM emp
GROUP BY job;

#查询每个部门中每种职位的人数
SELECT deptno,job,COUNT(empno)
FROM emp
GROUP BY deptno,job;

#查询每年入职人数
SELECT DISTINCT YEAR(hiredate),
COUNT(empno)
FROM emp
GROUP BY YEAR(hiredate);

#出现在select后面的列，必须是groupby的列或者聚合函数
SELECT deptno,MAX(sal) FROM emp 
GROUP BY deptno,job;
SELECT ename,SUM(sal) FROM emp;

13.分组过滤Having

#查询平均工资大于1500的职位
SELECT job,AVG(sal) FROM emp
GROUP BY job
HAVING AVG(sal)>1500;

#where 和 having的区别
/*
1.where对行进行过滤，having对分组进行过滤
2.where不能使用分组函数，
having可以使用分组函数
*/

三.子查询

#查询工资高于jones的工资
SELECT * 
FROM emp
WHERE sal>(
SELECT sal FROM emp 
WHERE ename='JONES');

#查询工资最高的员工
SELECT *
FROM emp
WHERE  sal=
(SELECT MAX(sal) FROM emp);

#查询工资高于公司平均工资的员工
SELECT *
FROM emp
WHERE  sal>
(SELECT AVG(sal) FROM emp);

#查询平均工资高于salsman的平均工资的职位
SELECT job,AVG(sal)
FROM emp
GROUP BY job
having AVG(sal)>
(SELECT AVG(sal) FROM emp 
WHERE job='salesman');

#查询有员工的部门号，部门名和部门地址
SELECT *FROM dept
WHERE deptno 
IN(SELECT deptno FROM emp);

#查询没有员工的部门号，部门名，和部门地址
SELECT *FROM dept
WHERE deptno 
NOT IN(SELECT deptno FROM emp);

#查询工资比部门20所有人都高的员工
#单行子查询
SELECT * FROM emp WHERE sal>
(SELECT MAX(sal) 
FROM emp 
WHERE deptno=20);
#多行子查询
SELECT * FROM emp WHERE sal>
ALL(SELECT sal
FROM emp 
WHERE deptno=20);

#查询工资比部门20工资最低的人都高的员工
SELECT * FROM emp WHERE sal>
(SELECT MIN(sal) 
FROM emp 
WHERE deptno=20);


SELECT * FROM emp WHERE sal>
ALL(SELECT MIN(sal)
FROM emp 
WHERE deptno=20);

SELECT * FROM emp WHERE sal>
(SELECT sal
FROM emp 
WHERE deptno=20);

#查询部门号和职位都跟simth相同的员工
SELECT * FROM emp WHERE deptno=（
SELECT deptno FROM emp WHERE ename='smith')
AND 
job=(
SELECT job 
FROM emp WHERE ename='smith');

练习

#列出薪金比“SMITH”多的所有员工。
SELECT * FROM emp
WHERE sal>(
SELECT sal FROM emp
WHERE ename='SMITH');

#列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。
SELECT ename FROM emp 
WHERE deptno=(
SELECT deptno FROM dept 
WHERE dname='SALES');

#列出与“SCOTT”从事相同工作的所有员工。
SELECT * FROM emp 
WHERE job=(
SELECT job FROM emp 
WHERE ename='scott');

#查询在NEW YORK工作并且工资比'SCOTT'高的职员的名字、职业、工资
SELECT ename,job,sal FROM emp
WHERE deptno=(SELECT deptno FROM dept WHERE loc='New york')
AND sal>(SELECT sal FROM emp 
WHERE ename='scott');

#查询叶玉娟的测试基础这门课成绩
SELECT s.grade FROM score s,course c
WHERE s.cid=c.cid
AND c.cname='测试基础'
AND s.sid =(SELECT sid FROM student WHERE sname='叶玉娟');

SELECT grade FROM score 
WHERE sid=(SELECT sid FROM student WHERE 
sname='叶玉娟')
AND cid=(SELECT cid FROM course WHERE cname
='测试基础')；

#查询最高的部门平均工资
SELECT MAX(avgsal) FROM( 
SELECT deptno,AVG(sal) avgsal FROM emp 
GROUP BY deptno) t;

UNION连接

SELECT* FROM emp WHERE deptno=10
UNION#去重 显示3+5=8个结果
SELECT * FROM emp WHERE deptno<=20;

SELECT* FROM emp WHERE deptno=10
UNION ALL# 不去重 显示3+3+5=11个结果
SELECT * FROM emp WHERE deptno<=20;

CASE WHEN ELSE END

#根据员工不同的 
#第一种用法
SELECT ename,job,CASE job
WHEN 'clerk' THEN'办事员'
WHEN 'salesman'THEN'销售员'
WHEN'manager'THEN'经理'
ELSE '其他岗'
END '岗 位'
FROM emp;

#第二种用法
SELECT sid,cid grade,CASE
WHEN grade >=90 THEN 'A'
WHEN grade >=80 THEN 'B'
WHEN grade >=70 THEN 'C'
ELSE 'D'
END 等级 
FROM score
ORDER BY 等级;

#查询学生姓名，性别为M显示男，F的显示女
#年龄，年龄大于30，显示中年
#年龄大于25，显示壮年
#大于18，显示青年
#小于18 显示未成年
#多注意Case前的符号
SELECT sname,ssex, CASE 
WHEN ssex='M' THEN'男'
ELSE '女'
END 性别,
sage,CASE 
WHEN sage >=30 THEN '中年'
WHEN sage >=25 THEN '壮年'
WHEN sage >=18 THEN '青年'
ELSE '未成年'
END 年纪
FROM student;

四.数据操作语句

DML语句

insert语句

INSERT INTO dept(deptno,dname,loc)
VALUES (50,'test50','NJ');
#重复就会报错，因为deptno是主键，不能重复
INSERT INTO dept(deptno,dname)
VALUES (60,'test60');
#正确写法

INSERT INTO dept VALUES(70,'test70');
#报错，不指定列名，每个字段必须填数据
INSERT INTO dept VALUES (70,'test70',NULL);
#正确写法

#插入日期
INSERT INTO emp(empno,ename,hiredate)
VALUES(7777,'zhangsan','1997-7-1');

#插入员工李四，入职日期为现在日期
INSERT INTO emp(empno,ename,hiredate)
VALUES(9999,'李四',NOW());
SELECT * FROM emp;

#一次插入多条记录
INSERT INTO dept(deptno,dname,loc)
VALUES 
(71,'test71','BJ'),
(72,'test72','SH'),
(73,'test73','GZ');

修改

#将部门号为50的地址改为苏州
UPDATE dept 
SET loc='苏州'
WHERE deptno=50;

#一次可以修改多列
#部门60改为测试部
#部门搬到深圳
UPDATE dept SET dname='测试部',loc='深圳'
WHERE deptno=60;

使用子查询修改

#将Smith的工作地修改到深圳
UPDATE emp
SET deptno=(
SELECT deptno 
FROM dept 
WHERE loc='深圳')
WHERE ename='smith';

#将Smith的部门号修改的和King一样
#mysql规定
#修改的表和子查询的表不能是同一个
UPDATE emp
SET deptno=(
SELECT deptno
FROM (SELECT *FROM emp) t
WHERE ename='king')
WHERE ename='smith';

删除

DELETE FROM dept WHERE deptno=50;
DELETE FROM dept WHERE deptno>70;

#先删除dept表，才能删emp表，因为外键需要参考
DELETE FROM dept;
DELETE FROM emp;#可以删

事务

#默认情况下，MySQL是自动提交事务的

#所有用户都能看到添加的记录

BEGIN-COMMIT

SELECT * FROM dept;
INSERT INTO dept(deptno,dname,loc)
VALUES (80,'test80','NJ');

#开启手动提交，只有执行commit才会提交事务
BEGIN#先begin
INSERT INTO dept(deptno,dname,loc)
VALUES (90,'test90','YC');
COMMIT#最后提交才能看到

ROLLBACK

BEGIN
DELETE FROM emp;
ROLLBACK;#回滚

练习

#在EMP表中，增加一名员工，员工信息参照现有员工构造.
SELECT * FROM emp;
INSERT INTO emp(empno,ename,job,hiredate,sal,deptno)
VALUES (8888,'XCP','TESTER','2025-05-20',20000,10);

#员工SMITH部门调动到SALES部门，请编写SQL语句更新员工信息.
UPDATE emp SET deptno=(
SELECT deptno FROM dept WHERE dname='sales')
WHERE ename='smith';

#员工JAMES已经离职，请编写SQL语句更新数据库.
DELETE FROM emp WHERE ename='JAMES';

#将叶玉娟的测试基础这门课成绩修改为60分
UPDATE score SET grade=60
WHERE cid=(SELECT cid FROM course 
WHERE cname='测试基础')
AND sid=(SELECT sid FROM student 
WHERE sname='叶玉娟');

#夏晓燕转学，将夏晓燕的全部信息删除
DELETE FROM score
WHERE sid=(
SELECT sid FROM student
WHERE sname='夏晓燕');

DELETE FROM student WHERE 
sname='夏晓燕';

创建数据库

CREATE DATABASE school;

删数据库

DROP DATABASE school;

CREATE TABLE teacher
(
    tid INT PRIMARY KEY,
    tname VARCHAR(30),
    tage TINYINT UNSIGNED,
    tsex CHAR(1) DEFAULT '男',
    tsal DECIMAL(7,2),
    hiredate DATE
)

DESC查看表结构

DESC teacher;

SHOW查看建表语句

#查看建表语句
SHOW CREATE TABLE teacher;

测试浮点数和定点数

CREATE TABLE test1
(
         id1 FLOAT(7,2),
         id2 DOUBLE(7,2),
         id3 DECIMAL(7,2)
 )
 
INSERT INTO test1
VALUES (3.14,3.14,3.14);
#均显示3.14

SELECT id1+3.14,id2+3.14,id3+3.14
FROM test1;
#均显示6.28

SELECT id1*3.14,id2*3.14,id3*3.14
FROM test1;
#9.86,9.86,9.8596
/*
原因：
1. 类型的存储特性
- FLOAT（单精度浮点型）/DOUBLE（双精度浮点型）：
采用“近似值存储”，通过二进制浮点表示法存储数据，无法精准表示所有十进制小数（比如 3.14 这类小数转二进制是无限循环的），计算后会自动对结果进行近似舍入，所以显示为 9.86 。
- DECIMAL（定点型）：
采用“精确值存储”，以字符串形式存储十进制数字，能精准表示小数，计算时不会丢失精度，因此保留了更详细的 9.8596 。
2. 显示规则的影响
MySQL客户端默认会对浮点型（FLOAT/DOUBLE）的结果进行自动格式化显示（通常与字段定义的精度一致，比如 (7,2) 会显示两位小数），而DECIMAL会保留计算后的实际精度。
*/

#插入一名教师不指定性别
INSERT INTO teacher(tid,tname)
VALUES(1000,'王晓红');
SELECT * FROM teacher;

INSERT INTO teacher(tid,tname,tsal)
VALUES(1001,'王红',12345.11);

INSERT INTO teacher(tid,tname,tsal)
VALUES(1002,'王晓',123.231231);
#警告，四舍五入

自增AUTO_INCREMENT

#创建学生表school
CREATE TABLE student
(
     sid INT AUTO_INCREMENT PRIMARY KEY,
     sname VARCHAR(30),
     ssex CHAR(1) DEFAULT '男',
     sage TINYINT UNSIGNED
)
#查看表结构
DESC student;

#自增的作用,先插入，再插入变成9001
INSERT INTO student(sid,sname,sage)
VALUES(9000,'张三',19);
INSERT INTO student(sname,sage)
VALUES('张三',19);

表数据 alter

#表数据 insert update delete
#alter直接提交，不能回滚

#增加一列 手机号
ALTER TABLE student ADD 
sphone CHAR(11)
AFTER ssex;#放在性别后面

#删除手机号列
ALTER TABLE student
DROP COLUMN sphone;

MODIFY

#修改数据类型和长度
#有数据的情况下有可能不能改，引发错误
ALTER TABLE student
MODIFY sphone VARCHAR(13);

CHANGE

#修改列名和数据类型
#将手机改为mobile 改为固定11个字符
ALTER TABLE student 
CHANGE sphone mobile CHAR(11);

Set修改默认值

ALTER TABLE student 
ALTER ssex
SET DEFAULT '女';

Drop删除默认值

ALTER TABLE student 
ALTER ssex
DROP DEFAULT;

Rename修改表名

ALTER TABLE student
RENAME TO stu;
DESC stu;

练习

#将表名改回student
ALTER TABLE stu
RENAME TO student;
DESC student;

#添加一列国籍，默认值中国，不为空
ALTER TABLE student
ADD country VARCHAR(30) 
DEFAULT '中国' NOT NULL;

#删除sage这一列
ALTER TABLE student
DROP COLUMN sage;

#增加出生日期
ALTER TABLE student
ADD birthday DATE;

order_status 表

#创建表
CREATE TABLE order_status 
(
    id INTEGER PRIMARY KEY ,
    STATUS CHAR(10),
    last_modified DATE,
    modified_by INTEGER,
    initially_created DATE NOT NULL,
    test VARCHAR(15),
    testb VARCHAR(10) DEFAULT 'testb'
)

#修改test字段
#使其不允许为空且赋予其一个默认值testing
ALTER TABLE order_status 
MODIFY test 
VARCHAR(15)#类型得写，不改也写
DEFAULT 'testing'
NOT NULL ; 

#ddl语句是定义语句，直接写到数据库，

#无法回滚

TRUNCATE删除全部记录

TRUNCATE TABLE student;
# 快速删除 炸单包赔

DROP TABLE student;

#drop truncate delete语句
/*
drop删除表记录和表结构
truncate和delete只删除表数据
drop和truncate是DDL语句，不能回滚
delete是DML语句，可以回滚
truncate只能删除全部记录，delete可以删除全部或者部分记录
truncate的效率比delete高
*/

约束

CREATE TABLE student
(
     sid INT ,
     sname VARCHAR(30) NOT NULL,
     ssex CHAR(1) DEFAULT '男',
     sage TINYINT UNSIGNED
     #check(sage between 18 and 28)
     ,
     sphone CHAR(11) UNIQUE,
     PRIMARY KEY(sid)
)#不适合有外键

查看约束的作用

主键约束

#主键约束 唯一且不为空
INSERT INTO student(sid,sname)
VALUES(NULL,'张三');
INSERT INTO student(sid,sname)
VALUES(1000,'张三');
#二次运行报Duplicate重复

非空约束

#非空约束，不为空但是可以重复
INSERT INTO student(sid,sname)
VALUES(1000,NULL);
INSERT INTO student(sid,sname)
VALUES(1001,'张三');

唯一约束

#唯一约束，不重复但是为空
INSERT INTO student(sid,sname,sphone)
VALUES(1001,'张三',13113131313);
INSERT INTO student(sid,sname,sphone)
VALUES(1003,'王五',NULL);

外键约束

#创建一个成绩表
CREATE TABLE score
(
     scid INT AUTO_INCREMENT PRIMARY KEY,
     ssid INT,
     cid INT,
     grade TINYINT UNSIGNED ,
     FOREIGN KEY(ssid)
     REFERENCES student(sid)#外键
)

#外键作用
INSERT INTO score(ssid,cid,grade)
VALUES(1234,10,98);
#报错，学生表没有1234学号
INSERT INTO score(ssid,cid,grade)
VALUES(1000,10,98);

查看约束名

SHOW CREATE TABLE score;
#结果如下
CREATE TABLE `score` (
  `scid` INT(11) NOT NULL AUTO_INCREMENT,
  `ssid` INT(11) DEFAULT NULL,
  `cid` INT(11) DEFAULT NULL,
  `grade` TINYINT(3) UNSIGNED DEFAULT NULL,
  PRIMARY KEY (`scid`),
  KEY `ssid` (`ssid`),
  CONSTRAINT `score_ibfk_1` FOREIGN KEY (`ssid`) REFERENCES `student` (`sid`)
) ENGINE=INNODB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

删掉主键约束

ALTER TABLE student DROP PRIMARY KEY;

删除外键

ALTER TABLE score 
DROP FOREIGN KEY score_ibfk_1;
ALTER TABLE student
MODIFY sid INT NULL;

删除非空约束

ALTER TABLE student
MODIFY sname VARCHAR(30) NULL;

删除唯一约束

ALTER TABLE student
DROP INDEX sphone;

建表后增加约束

ALTER TABLE student 
ADD CONSTRAINT PRIMARY KEY(sid);

#给order_status表的id列添加一个外键约束, 
#外键约束的表为employees, 
#对应的列为employee_id;
CREATE TABLE employees
(
      employee_id INT PRIMARY KEY 
)
ALTER TABLE order_status 
ADD CONSTRAINT FOREIGN KEY (id)
REFERENCES employees(employee_id);

VIEW视图

创建视图

#创建视图empvu10
CREATE VIEW empvu10(e_no,e_name,e_job)
AS SELECT empno 工号,ename 姓名,job 职位 FROM 
emp WHERE deptno=10;

#创建视图empvu20
CREATE VIEW empvu20
AS SELECT empno 工号,ename 姓名,job 职位 FROM 
emp WHERE deptno=20;

#创建视图empvu30
CREATE VIEW empvu30(e_no,e_name,e_job)
AS SELECT empno 工号,ename 姓名,job 职位 FROM 
emp WHERE deptno=30;

修改视图

CREATE OR REPLACE VIEW empvu10
AS SELECT empno,ename,job,hiredate FROM 
emp WHERE deptno=10;

#简单视图
#复杂的视图：多表连接，分组，函数

/*
创建视图dept_sum_vu
显示部门名称，部门平均工资，
最高工资，最低工资，工资和
*/
CREATE VIEW dept_sum_vu AS 
SELECT 
d.dname 部门名称,
AVG(e.sal) 平均工资,
MAX(e.sal) 最高工资,
MIN(e.sal) 最低工资 ,
SUM(e.sal) 工资和
FROM emp e, dept d
WHERE e.deptno=d.deptno
GROUP BY d.dname

#简单视图可以操作表数据，如简单增删改查
DELETE FROM empvu10
WHERE ename='clark';

#复杂视图不可以操作表数据
UPDATE dept_sum_vu 
SET 最高工资=8000
WHERE 部门名称='SALES';

删除视图

DROP VIEW empvu20;

#在EMP表上创建一个视图emp_vu20，要求包括RESEARCH部门所有员工的信息
CREATE VIEW emp_vu20 AS 
SELECT * FROM emp 
WHERE deptno=(
SELECT deptno FROM dept
WHERE dname='RESEARCH')

#分别创建两个视图emp_deptview、emp_jobview
#按部门、职位分别统计员工的年薪和.
CREATE OR REPLACE VIEW emp_deptview AS
SELECT deptno,SUM(sal*12)
FROM emp GROUP BY deptno;

CREATE VIEW emp_jobview AS
SELECT job,SUM(sal*12) 年薪和
FROM emp
GROUP BY job;

#通过对视图emp_vu20来更新员工的工资为原来的1.1倍
UPDATE emp_vu20 SET sal=sal*1.1;

#将MySQL默认的结束符修改成$$

#创建一个存储过程名为adddept
#有多个参数n,为传入类型，数据类型是整数
#将MySQL默认的结束符修改成$$
DELIMITER $$
CREATE PROCEDURE adddept(IN n INT)
BEGIN
#声明变量i,类型为int,赋值为1 java int=1
DECLARE i INT DEFAULT 1; 
#循环（重复）n 次 
WHILE  i<=n DO
INSERT INTO dept(dname,loc) 
VALUES(CONCAT('testdept',i),'testlocation');
SET i = i+1;
END WHILE;
END$$
DELIMITER ;
#将结束符修改为默认的分号
DELIMITER ;

#调用存储过程
CALL adddept(1000)

SELECT * FROM dept

#在school数据库中学生表插入1万学生
#要求名字不同，
#学号自增，性别5000个男，5000个女
DESC student

CREATE TABLE student
(
     sid INT AUTO_INCREMENT,
     sname VARCHAR(30) NOT NULL UNIQUE,
     ssex CHAR(1) DEFAULT '男',
     sage TINYINT UNSIGNED
     #check(sage between 18 and 28)
     ,
     sphone CHAR(11) UNIQUE,
     PRIMARY KEY(sid)
)

DELIMITER $$
CREATE PROCEDURE addstu(IN n INT)
BEGIN
DECLARE i INT DEFAULT 1; 
WHILE  i<=n DO
INSERT INTO student(sname,ssex,sage) 
VALUES(CONCAT('张三',i),IF(i<n/2,'男','女'),20);
SET i = i+1;
END WHILE;
END$$
DELIMITER ;

CALL addstu(1000);

TRUNCATE TABLE student 

INDEX索引

解释计划
EXPLAIN SELECT * FROM student WHERE sname='张三1000'

索引的意义

无索引/查询条件未命中索引：MySQL只能“逐行扫描全表”，逐一判断是否符合条件（即全表扫描）；有索引且查询条件命中索引：MySQL先通过索引（类似目录）快速定位到目标数据的物理位置，再直接去表中取数据，无需扫描无关记录。

优缺点

索引的核心优点：大幅加速查询（精准/前缀匹配等场景）、减少全表扫描、提升数据检索效率。

索引的核心缺点：占额外存储空间、拖慢增删改（写操作）、可能失效、维护成本高。

创建一个索引

CREATE INDEX student_sname_idx ON 
student(sname);

删除索引

DROP INDEX student_sname_idx ON student;

权限分配

#创建用户
CREATE USER 'test1'@'%' 
IDENTIFIED BY '123456';
/*
@  是 分隔符， %  是 主机匹配通配符，核心含义如下：
 
-  @ ：分隔「用户名」和「允许登录的主机」，格式为  '用户名'@'主机名' ，明确该用户能从哪些主机登录MySQL。
-  % ：匹配「任意主机」，包括本地服务器、远程电脑等所有网络可达的设备，即  'test1'@'%'  表示  test1  可从任何地方登录。
 
常见主机匹配场景（补充理解）：
 
-  'test1'@'localhost' ：仅允许从MySQL所在的本地服务器登录（不能远程）；
-  'test1'@'192.168.1.100' ：仅允许从IP为  192.168.1.100  的特定主机登录；
-  'test1'@'192.168.1.%' ：允许从  192.168.1  网段的所有主机登录（通配符%匹配网段后缀）。
*/

#授予全部权限
GRANT ALL PRIVILEGES ON bwf.*TO test1; 
#让权限立即生效
FLUSH PRIVILEGES ;
#修改密码
ALTER USER 'test1'@'%'
IDENTIFIED BY '121212';
#删除用户
DROP USER 'test1'@'%';