第11章-第1节-SQL语句（基于mysql社区版8.0系列）_mysql8.0之后的sql语句写法-CSDN博客

本文链接：https://blog.csdn.net/jnbbwyth/article/details/135734125

1、先看看以前写过的几篇数据库基础文章：

2、SQL的基础应用感觉没有太多可以讲的东西，直接上学习笔记，可以看的很直接：

-- DDL 库，表 .. 创建和删除 ， 修改

USE testdb;

-- 创建数据库 
CREATE DATABASE IF NOT EXISTS testdb;
-- 删除数据库 
DROP DATABASE IF EXISTS testdb;

-- 创建表 
-- 主键 primary key  数据不能重复   所以我们设置主键的字段要选择一个没有重复数据的字段才可以！！！
-- 数据短，连续性强的  推荐作为主键！
CREATE TABLE studentinfo(
	-- INT 数值类型  not null 不能为空
	stuId INT NOT NULL PRIMARY KEY COMMENT '学号',
	-- comment 注释 
	stuName VARCHAR(10) NOT NULL COMMENT '学生姓名',
	-- decimal(总位数，小数点后位数）
	stuHeight DECIMAL(5,2) COMMENT '学生身高',
	-- 出生年月 timestamp
	birthday DATETIME COMMENT '学生出生日',
	phoneNumber VARCHAR(11) COMMENT '学生电话',
	-- 默认值  default 数据
	stuAddress VARCHAR(50) DEFAULT '西安' COMMENT '学生地址'
);

-- 删除表 
DROP TABLE IF EXISTS studentinfo;

-- 员工表
CREATE TABLE empinfo(
	eid INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
	emp_name VARCHAR(20) NOT NULL,
	emp_age INT NOT NULL,
	emp_time DATE NOT NULL,
	dept_id INT COMMENT '部门id',
	sid VARCHAR(10) COMMENT '工资编号',
	remark VARCHAR(255) 
);
DROP TABLE empinfo 
-- 部门表
CREATE TABLE deptinfo(
	dept_id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
	dept_name VARCHAR(20) NOT NULL 
);

-- 工资表 
CREATE TABLE salaryinfo(
	sid VARCHAR(10) NOT NULL PRIMARY KEY ,
	salary_amount DECIMAL(10,2) NOT NULL
);

INSERT INTO deptinfo VALUES(DEFAULT,'开发部'),(DEFAULT,'测试部'),(DEFAULT,'宣传部');

INSERT INTO empinfo VALUES(DEFAULT,'张三丰',30,'2000-10-1',1,'1001',NULL),
(DEFAULT,'张无忌',20,'2000-10-1',1,'1002',NULL),
(DEFAULT,'郭靖',18,'2000-10-2',2,'1003',NULL),
(DEFAULT,'洪七公',50,'2000-10-2',3,'1004',NULL),
(DEFAULT,'欧阳锋',55,'2000-2-1',3,'1005',NULL),
(DEFAULT,'段誉',25,'2000-11-1',2,NULL,NULL),
(DEFAULT,'乔峰',30,'2000-12-1',2,NULL,NULL),
(DEFAULT,'虚竹',33,'2000-10-10',1,NULL,NULL);


-- 连接查询   多表联查  empinfo 联合 deptinfo  一起查询
-- 连接查询： 1） 内连接  2）外连接  2.1）左外连接  2.2）右外连接
-- 内连接   表1 inner join  表2  on  表1.关联字段 = 表2.关联字段 ......

SELECT eid,emp_name,emp_age,emp_time,dept_name FROM empinfo 
INNER JOIN deptinfo 
ON empinfo.dept_id = deptinfo.dept_id

-- 外连接  表1 left/right join 表2 on 表1.关联字段 = 表2.关联字段 ......
SELECT eid,emp_name,emp_age,emp_time,dept_name FROM empinfo 
LEFT JOIN deptinfo 
ON empinfo.`dept_id` = deptinfo.`dept_id`

-- 两种连接的区别 
-- 1. 内连接 和 外连接的区别？
-- 内连接的查询结果 是必须 在连接的所有表中 都有映射的数据 ， 那么这些结果才会出现在inner join的结果集中
-- 外连接 ， 在查询的结果中，是以 主表的数据作为主体，主表的数据必须全部查询出来，如果在其他表中
-- 没有相应的映射数据，那么以null来填充

-- 2. 内连接的效率要高于外连接

-- 内连接写法2 ：等同于 inner join
SELECT eid,emp_name,emp_age,emp_time,dept_name
FROM empinfo,deptinfo
WHERE empinfo.`dept_id` = deptinfo.`dept_id`

-- 三表连接查询   员工信息 + 部门信息 + 工资数目 
SELECT eid,emp_name,emp_age,emp_time,dept_name,salary_amount
FROM empinfo 
INNER JOIN deptinfo ON empinfo.`dept_id`=deptinfo.`dept_id`
INNER JOIN salaryinfo ON empinfo.`sid` = salaryinfo.`sid`

SELECT eid,emp_name,emp_age,emp_time,dept_name,salary_amount
FROM empinfo,deptinfo,salaryinfo 
WHERE empinfo.`dept_id`=deptinfo.`dept_id` 
AND empinfo.`sid` = salaryinfo.`sid`

CREATE TABLE IF NOT EXISTS studentinfo(
sid VARCHAR(10) NOT NULL ,
sname VARCHAR(10) NOT NULL ,
birthday DATE,
ssex CHAR(1),
schinese DECIMAL(10,2),
smath DECIMAL(10,2),
senglish DECIMAL(10,2)
);

INSERT INTO studentinfo VALUES
('01', '赵雷', '1990-01-01 00:00:00', '男',80.5,91,76),
('02', '钱电', '1990-12-21 00:00:00', '男',84,97,67),
('03', '孙风', '1990-05-20 00:00:00', '男',70.5,92,85.5),
('04', '李云', '1990-08-06 00:00:00', '男',60,99,89),
('05', '周梅', '1991-12-01 00:00:00', '女',44.5,69,76),
('06', '吴兰', '1992-03-01 00:00:00', '女',46.5,55,90),
('07', '郑竹', '1989-07-01 00:00:00', '女',91.5,78,91),
('09', '张三', '2017-12-20 00:00:00', '女',98.5,88,99),
('10', '李四', '2017-12-25 00:00:00', '女',100,47,100),
('11', '李四', '2017-12-30 00:00:00', '女',NULL,78,98),
('12', '赵六', '2017-01-01 00:00:00', '女',67,NULL,65),
('13', '孙七', '2018-01-01 00:00:00', '女',89,NULL,44);

INSERT INTO studentinfo VALUES
('10', '李四', '2017-12-25 00:00:00', '女',100,47,100),
('11', '李四', '2017-12-30 00:00:00', '女',NULL,78,98)

# 1、查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
SELECT sid,sname,((schinese+smath+senglish)/3) AS '平均成绩'
FROM studentinfo 
WHERE ((schinese+smath+senglish)/3) >= 60;

# 2、查询所有同学的学生编号、学生姓名、所有课程的总成绩(没成绩的显示为 0 )
SELECT sid,sname,
(IFNULL(schinese,0)+IFNULL(smath,0)+IFNULL(senglish,0)) AS '总成绩'
FROM studentinfo 

# 3、查询「李」姓学生的数量
SELECT COUNT(*) FROM studentinfo WHERE sname LIKE '李%'

# 4、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT sid,sname,IFNULL(((schinese+smath+senglish)/3),0) AS '平均成绩'
FROM studentinfo
WHERE (IFNULL(schinese,0)<60 AND IFNULL(smath,0)<60)
OR (IFNULL(smath,0)<60 AND IFNULL(senglish,0)<60)
OR (IFNULL(schinese,0)<60 AND IFNULL(senglish,0)<60)
OR (IFNULL(schinese,0)<60 AND IFNULL(smath,0)<60 AND IFNULL(senglish,0)<60)

-- 写法2  运用case when 
SELECT sid,sname,IFNULL(((schinese+smath+senglish)/3),0) AS '平均成绩'
FROM studentinfo
WHERE   
	(CASE WHEN schinese < 60 THEN 1 ELSE 0 END
	+
	CASE WHEN smath < 60 THEN 1 ELSE 0 END
	+
	CASE WHEN senglish < 60 THEN 1 ELSE 0 END) >= 2 


# 5、按平均成绩从高到低显示所有学生的所有课程的成绩
SELECT sname,schinese,smath,senglish FROM studentinfo 
ORDER BY (schinese+smath+senglish)/3 DESC

# 6、修改张三的性别为 男 ，语文成绩为 100
UPDATE studentinfo SET ssex='男' ,schinese=100 WHERE sname='张三'

# 7、修改钱电的出生日期为 2000-10-01
UPDATE studentinfo SET birthday='2000-10-01' WHERE sname='钱电'

# 8、查询男生、女生人数
SELECT ssex,COUNT(*) FROM studentinfo GROUP BY ssex

# 9、查询名字中含有「风」字的学生信息
SELECT * FROM studentinfo WHERE sname LIKE '%风%'

# 10、查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
SELECT sname,smath FROM studentinfo WHERE smath < 60

# 11、查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
SELECT sname,schinese,smath,senglish FROM studentinfo 
WHERE schinese > 70 OR smath>70 OR senglish>70

# 12、查询出出生日期在2010-2012年的人员信息
SELECT * FROM studentinfo 
WHERE birthday BETWEEN "2015-01-01" AND "2018-12-31"

# 13、任意删除 一条李四 的重复数据
-- 统计 一个名字的数量 >= 2 代表 这个人名肯定重复了 
DELETE FROM studentinfo 
WHERE sname IN (SELECT sname FROM 
-- 子查询的功能就是去查询  名字统计出来 >1  代表重复数据
(SELECT sname FROM studentinfo GROUP BY sname HAVING COUNT(sname)>1)s)
-- 挑一个李四  sid 
AND sid IN (SELECT `Min(sid)` FROM 
(SELECT MIN(sid) FROM studentinfo GROUP BY sname HAVING COUNT(sname)>1)A);

-- 方法2 ：
DELETE FROM studentinfo WHERE sid = 
(SELECT sid FROM (SELECT sid FROM studentinfo WHERE sname='李四' LIMIT 1) s )

# 14、修改 所有为null 的成绩 为 0
UPDATE studentinfo 
SET schinese = IFNULL(schinese,0),
smath = IFNULL(smath,0),
senglish = IFNULL(senglish,0);

# 15、查询各科成绩最高分、最低分和平均分   
-- Union All 将不同sql执行的结果拼接成一份（注意拼接的子结果中，列数必须相同，否则报错）

-- union 将不同的子结果拼接成一个总结果
-- union 有去重和排序的效果

-- 如果对去重和排序没有要求，那么union all 效率更高
SELECT 
'语文' AS '科目',
MAX(schinese) AS '最高分',
MIN(schinese) AS '最低分',
AVG(schinese) AS '平均分'
FROM studentinfo

UNION ALL

SELECT 
'数学' AS '科目',
MAX(smath) AS '最高分',
MIN(smath) AS '最低分',
AVG(smath) AS '平均分'
FROM studentinfo

UNION ALL

SELECT 
'英语' AS '科目',
MAX(senglish) AS '最高分',
MIN(senglish) AS '最低分',
AVG(senglish) AS '平均分'
FROM studentinfo

# 16、将所有不及格的成绩 统一+10分
-- case when  相当于  java中 swith 结构
-- 类似与  java中 if结构 
UPDATE studentinfo 
SET schinese = CASE WHEN schinese < 60 THEN schinese+10 ELSE schinese END,
 smath = CASE WHEN smath < 60 THEN smath+10 ELSE smath END,
 senglish = CASE WHEN senglish < 60 THEN senglish+10 ELSE senglish END;

3、写的几个简单案例：

CREATE DATABASE my_db;

CREATE TABLE t_student ( 
  id INT PRIMARY KEY AUTO_INCREMENT,  		-- 编号 
  NAME VARCHAR(30), 				-- 姓名 
  age INT, 					-- 年龄 
  sex VARCHAR(1),  				-- 性别 
  address VARCHAR(255),  			-- 地址 
  chinese INT, 					-- 语文 
  math INT 					-- 数学 
); 

INSERT INTO t_student(id, NAME, age, sex, address, chinese, math) 
VALUES(1, '马云', 55, '男', '杭州', 66, 78),
		(2, '马化腾', 45, '女', '深圳', 98, 87),
		(3, '马景涛', 55, '男', '香港', 56, 77),
		(4, '柳岩', 20, '女', '湖南', 76, 65),
		(5, '柳青', 20, '男', '湖南', 86, NULL),
		(6, '刘德华', 57, '男', '香港', 99, 99),
		(7, '马德', 22, '女', '香港', 99, 99),
		(8, '德玛西亚', 18, '男', '南京', 56, 65);
		
-- 需求1：查询年龄大于22岁
SELECT * FROM t_student WHERE age > 22;

-- 需求2：查询年龄等于22岁
SELECT * FROM t_student WHERE age = 22;

-- 需求3：查询年龄不等于20岁
SELECT * FROM t_student WHERE age != 20;

-- 需求4： 查询年龄大于等于20 小于等于30
SELECT * FROM t_student WHERE age >= 20 OR age <=30;
SELECT * FROM t_student WHERE age BETWEEN 20 AND 30;

-- 需求5：查询年龄22岁，18岁，25岁的信息
SELECT * FROM t_student WHERE age =22 OR age = 18 OR age = 25;
SELECT * FROM t_student WHERE age IN(22,18,25);

-- 需求6：查询数学成绩为null ?
SELECT * FROM t_student WHERE math IS NULL;

-- 需求7：查询数学成绩不为null ?
SELECT * FROM t_student WHERE math IS NOT NULL;

-- 需求8：查询姓马的有哪些
SELECT * FROM t_student WHERE NAME LIKE '马%';

-- 需求9：查询姓名是3个字的人
SELECT * FROM t_student WHERE NAME LIKE '___';

-- 需求10：查询姓名中包含德的人
SELECT * FROM t_student WHERE NAME LIKE '%德%';

-- 需求11：查询姓马的，名字长度为2个的人
SELECT * FROM t_student WHERE NAME LIKE '马_';

4、关于外键的补充：

-- 员工表
CREATE TABLE empinfo(
	eid INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
	emp_name VARCHAR(20) NOT NULL,
	emp_age INT NOT NULL,
	emp_time DATE NOT NULL,
	dept_id INT ,
	sid VARCHAR(10) COMMENT '工资编号',
	remark VARCHAR(255) 
	foreign key(dept_id) references deptinfo(dept_id)	
);



-- 外键  foreign key 
-- 创建方式1 ： 直接在建表脚本中写入外键  注意：主表必须优先创建出来
-- 创建方式2： alter语句
ALTER TABLE empinfo ADD FOREIGN KEY(dept_id) REFERENCES deptinfo(dept_id)
ALTER TABLE empinfo DROP FOREIGN KEY empinfo_ibfk_1

ALTER TABLE empinfo ADD COLUMN height DECIMAL(5,2)
ALTER TABLE empinfo DROP COLUMN heigh

5、关于常见的函数及数据库设计原则：

-- 常见的单行处理函数：   多行处理函数： SUM  AVG MIN MAX COUNT ....
   SELECT SUM(SCHINESE) FROM studentinfo
--     lower转换小写
	SELECT UPPER(emp_name) FROM empinfo
--     upper转换大写
--     substr取子串：substr(被截取的字符串，起始下标，截取的长度) ，下标从1开始。
       SELECT SUBSTR(emp_name,1,1) FROM empinfo
--     length取长度 --- 字节数  一个汉字 = 3字节
	SELECT LENGTH(emp_name)/3 FROM empinfo
--     concat字符串拼接：concat（字段1，字段2）
	SELECT CONCAT(SUBSTR(emp_name,1,1),'*',SUBSTR(emp_name,3,1)) FROM empinfo
--     trim去空格
--     str_to_date将字特串转换成日期
	SELECT STR_TO_DATE('20240122','%Y%m%d') FROM empinfo
--     date_ format 格式化日期
	SELECT DATE_FORMAT(emp_time,'%Y年%m月%d日') FROM empinfo
--     round四舍五入
--     rand()生成随机数
	SELECT ROUND(RAND(1)*10) FROM empinfo
--     ifnull：将null转换成-一个具体值
	
-- 数据库设计原则：
-- 第一范式（1NF）：原子性（存储的数据应该具有“不可再分性”）
-- 
-- 第二范式（2NF）：唯一性 (消除非主键部分依赖联合主键中的部分字段)（一定要在第一范式已经满足的情况下）
-- 
-- 第三范式（3NF）：独立性，消除传递依赖(非主键值不依赖于另一个非主键值)

本电子书目录：《Java基础的重点知识点全集》