文章目录
- SQL开发
- DML(Data Manipulation Language)
- 向表中写入数据
- 查询表中的数据
- ALL语句
- DISTINCT语句
- WHERE子句
- 编写一条简单的SELECT语句的思路
- MySQL的比较运算符
- MySQL的逻辑运算符
- 多表查询
- 使用JOIN关联多个表
- 内关联
- 外关联
- 分组查询GROUP BY...HAVING子句
- 聚合函数
- 使用ORDER BY 子句进行排序
- 使用LIMIT分页查询
- 创建视图
- 按条件删除表中的数据
- 数据更新语句UPDATE
- 系统函数
- 时间函数
- 字符串函数
- 其他函数
- MYSQL8.0新特性
- 公共表表达式
- 窗口函数
- SQL开发规范
SQL开发
DML(Data Manipulation Language)
关键字 | 作用 |
INSERT | 向表中插入数据 |
DELETE | 删除表中数据 |
SELECT | 查询表中数据 |
UPDATE | 更新表中数据 |
向表中写入数据
ON DUPLICATE KEY UPDATE :在出现主键冲突或索引冲突的时候,加上这条语句,更新除了唯一索引主键列外的其他列的值.
编写INSERT语句的思路:
- 确认要把数据插入到哪个表中
- 确认表的数据库结构,哪些列不能为NULL,不能为null列是否有默认值
- 确认插入列的插入值清单
举个例子,向imc_class分类表中的分类名称列插入数据:
INSERT INTO imc_class(class_name) VALUES ('MySQL'),('Redis'),('MongoDB');
接着将该列的值设置为唯一索引,让该列的值不重复,
CREATE UNIQUE INDEX uqx_classname ON imc_class(class_name);
若要再次插入相同的值,则需加上ON DUPLICATE KEY UPDATE
INSERT INTO imc_class(class_name) VALUES ('MySQL'),('Redis'),('MongoDB') ON DUPLICATE KEY UPDATE add_time=CURRENT TIME;
这样就可以插入成功,同时发现加入时间这一列的值更新为当前的时间。
查询表中的数据
ALL语句
从课程表中选出学习人数这一列所有的值
SELECT ALL study_cnt FROM imc_course;
DISTINCT语句
对于每一列中可能会有重复的值,DISTINCT可以滤除重复的值
有如下一张表CESHIDEMO
执行以下语句得到如下结果:
select distinct name from CESHIDEMO
但如果查询的是多个字段,则只会滤除所有字段值相同的记录,如
SELECT DISTINCT name, age FROM CESHIDEMO;
得到的结果依然是:
WHERE子句
1.使用LIKE关键字过滤字符串
我们这有这有一个需求,查询所有课程名中包含MySQL的课程名称
SELECT title
FROM imc_course
WHERE title LIKE '%MySQL%';
这个查询语句中LIKE起到过滤作用,MySQL中前面%代表任意个字符,后面一个%也代表任何数量的字符,这句查询语句就是过滤出所有含有MySQL的课程名称;
编写一条简单的SELECT语句的思路
- 确认要查询的数据在哪些表中
- 确认要查询的字段
- 确认是否需要对数据过滤
MySQL的比较运算符
比较运算符 | 说明 |
= > < >= <= <> != | <>和!=都表示不等于 |
BETWEEN min AND max | 列的值大于等于最小值,小于等于最大值 |
IS NULL,IS NOT NULL | 判断列的值是否为空 |
LIKE, NOT LIKE | %代表任何数量的字符,下划线代表任意一个字符 |
IN , NOT IN | 判断列的值是否在指定范围内 |
1.查找学习人数大于1000的课程,列出课程标题和学习人数
SELECT title,study_cnt
FROM imc_course
WHERE study_cnt>1000
;
2.查找学习人数大于等于1000小于等于2000的课程,列出课程标题和学习人数
SELECT title,study_cnt
FROM imc_course
WHERE study_cnt BETWEEN 1000 AND 2000
;
3.建一张表,插入数据,查找值为空的列
CREATE TABLE test_id(id INT,c VARCHAR(10),PRIMARY KEY(id));
INSERT INTO test_id VALUES(1,'aa'),(2,NULL),(3,'cc');
SELECT * FROM test_id WHERE c IS NULL;
4.查找一段字符串是否包含某一段字符:
SELECT 'this is mysql course' LIKE '%mysql%';
SELECT 'mysql' LIKE '_ysql';
SELECT 'xxxxxxxmysql' LIKE '_ysql';
5.查找课程ID为1,3,5,7,9的课程名称
SELECT title
FROM imc_course
WHERE course_id IN (1,3,5,7,9);
MySQL的逻辑运算符
任何值与NULL进行逻辑运算都为NULL。
1.查询出课程标题含有MySQL关键字且学习人数大于5000的课程中的课程标题和学习人数
SELECT title,study_cnt
FROM imc_course
WHERE title LIKE '%MySQL%' AND study_cnt>5000;
2.查询出课程标题含有MySQL关键字或者学习人数大于5000的课程中的课程标题和学习人数
SELECT title,study_cnt
FROM imc_course
WHERE title LIKE '%MySQL%' OR study_cnt>5000;
3.查询出课程标题不含有MySQL关键字但学习人数大于5000的课程中的课程标题和学习人数
和查询出课程标题含有MySQL关键字但学习人数不大于5000的课程中的课程标题和学习人数
法一:
SELECT title,study_cnt
FROM imc_course
WHERE title LIKE '%MySQL%' XOR study_cnt>5000;
法二:
SELECT title,study_cnt
FROM imc_course
WHERE title LIKE '%MySQL%' AND study_cnt<=5000
UNION ALL
SELECT title,study_cnt
FROM imc_course
WHERE title NOT LIKE '%MySQL%' AND study_cnt>5000;
多表查询
- 表与表之间要存在一定的关联关系
使用JOIN关联多个表
内关联
JOIN分为内关联和外关联,其中内关联就是找出两个表中关联之后都存在的数据,也就是找出两个表的交集。
我们来通过一个例子来理解一下,这里有个需求,就是查询出每一门课程的课程ID,课程名称和章节名称,这里就要关联课程表和章节表,通过课程ID将他们关联起来,
SELECT a.course_id,a.title,b.chapter_name
FROM imc_course a
JOIN imc_chapter b ON b.course_id=a.course_id;
假如我们在课程表中插入一条新数据,这时候再内关联查询,是否可以查询出这条新数据呢?
会发现无法查询出该数据,所以说,内关联只能查询出同时存在于两个表中的数据,那么怎样查询出这条数据呢,这就需要外关联
外关联
左外关联:
左外关联查询到的数据就是一定存在于左表中的,若右表的关联列为空,则只查询到左表且不存在于右表的数据(即用于查询不存在右表的数据的情况);所以上文中提到的在左表新加一条记录如何查询到,就用左外关联LEFT JOIN;
SELECT a.course_id,a.title,b.chapter_name
FROM imc_course a
LEFT JOIN imc_chapter b ON b.course_id=a.course_id;
WHERE title='MySQL关联测试';
如我们要查询只存在于课程表,但是不存在于章节表中的课程ID和课程名称
SELECT a.course_id,a.title,b.chapter_name
FROM imc_course a
LEFT JOIN imc_chapter b ON b.course_id=a.course_id
WHERE b.course_id IS NULL;
右外关联:
与左外关联一样,可以查询存在于右表或只存在于右表的数据,其他用法参照左外关联
分组查询GROUP BY…HAVING子句
- 把结果集按某些列分成不同的组,并对分组后的数据进行聚合操作
如下,我们想要把课程表中按难度计算每一种难度的课程数目为多少
在这里,level_name为分组键,所有非聚合函数列都应作为分组键
SELECT b.level_name,count(*)
FROM imc_course a
JOIN imc_level b ON b.level_id=a.level_id
GROUP BY level_name
- 可以通过选择合适的HAVING子句对聚合后的数据进行过滤
需求:统计每个分类下课程大于3门的难度有哪些
SELECT level_name,class_name,count(*)
FROM imc_course a
JOIN imc_level b ON b.level_id=a.level_id
JOIN imc_class c ON c.class_id=a.class_id
GROUP BY level_name,class_name HAVING count(*)>3
;
聚合函数
1.统计不同难度下的课程学习人数
SELECT level_name,SUM(study_cnt)
FROM imc_course a
JOIN imc_level b ON b.level_id=a.level_id
GROUP BY level_id
;
2.统计每门课程的平均学习人数
SELECT AVG(study_cnt)
FROM imc_course
3.查询出课程人数最多的课程
要先查询出最多的人数是多少,再将这作为条件再查找人数为这个的课程
SELECT title,study_cnt
FROM imc_course
WHERE study_cnt=(SELECT MAX(study_cnt) FROM imc_course)
;
使用ORDER BY 子句进行排序
- 列名后加ASC是按该列的升序排序,加DESC指定按该列的降序排序
- 也可以使用SELECT中未出现的列或函数
1.查询出所有课程的学习人数并按从高到低排列(降序)
SELECT title,study_cnt
FROM imc_course
ORDER BY study_cnt DESC
使用LIMIT分页查询
- 常用于数据列表分页
- 一定要配合ORDER BY子句使用
- LIMIT 起始偏移量,结果集的行数
1.分页返回课程ID和课程名称,每页返回10行数据
SELECT course_id,title
FROM imc_course
ORDER BY study_cnt DESC
LIMIT 0,10
;
创建视图
1.定义一个包括课程ID,课程名称,分类名称,方向名称,难度名称的视图
CREATE VIEW vm_view
AS
SELECT a.course_id,a.title,b.class_name,c.type_name,d.level_name
FROM imc_course
JOIN imc_class b ON b.class_id=a.class_id
JOIN imc_type c ON c.type_id=a.type_id
JOIN imc_level d ON d.level_id=a.level_id
;
按条件删除表中的数据
1.删除课程方向表中重复的方向,并且保留ID最小的一条,并在方向名称上增加唯一索引
DELETE a
FROM imc_type a
JOIN
(SELECT type_name,MIN(type_id),COUNT(*)
FROM imc_type
GROUP BY type_name HAVING COUNT(*)>1) b
ON b.type_name=a.type_name AND a.type_id>b.type_id;
//建立唯一索引
CREATE UNIQUE INDEX uqx_typename ON imc_type(type_name);
数据更新语句UPDATE
- 确定要更新的数据存在哪个表中
- 确定要更新的列及列的值
- 确定过滤条件
1.随机推荐10门课程
首先先给课程表中添加一列,是否推荐,
ALTER TABLE imc_course
ADD is_recommend TINYINT DEFAULT 0 COMMENT '是否推荐,0不推荐,1推荐';
UPDATE imc_course
SET is_recommend=1
ORDER BY RAND()
LIMIT 10
;
SELECT title,course_id
FROM imc_course
WHERE is_recommend=1
;
系统函数
时间函数
1.
SELECT CURDATE(),CURTIME(),NOW();
2.
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s');
3.
SELECT SEC_TO_TIME(60),TIME_TO_SEC('01:00:00');
4.
SELECT NOW(),
DATE_ADD(NOW(),INTERVAL 1 DAY),--当前日期加1天
DATE_ADD(NOW(),INTERVAL 1 YEAR),--当前日期加1年
DATE_ADD(NOW(),INTERVAL -1 DAY),--当前日期减1天
DATE_ADD(NOW(),INTERVAL '-1:30' HOUR_MINUTE);--当前日期减1个小时30分钟
5.
SELECT NOW(),
EXTRACT(YEAR FROM NOW()),--提取年
EXTRACT(MONTH FROM NOW()),--提取月
EXTRACT(DAY FROM NOW()),--提取日期
EXTRACT(HOUR FROM NOW()),--提取小时
EXTRACT(MINUTE FROM NOW()),--提取分钟
EXTRACT(SECOND FROM NOW());--提取秒
字符串函数
1.
SELECT CONCAT_WS('||',class_name,title)
FROM imc_course a
JOIN imc_class b ON b.class_id=a.class_id
;
2.
SELECT class_name,LENGTH(class_name),CHAR_LENGTH(class_name)
FROM imc_class;
3.
SELECT FORMAT('123456.789',4)
1.LEFT / RIGHT函数
SELECT LEFT('www.baidu.com',6),
RIGHT('www.baidu.com',6)
2.SUBSTRING函数
SELECT SUBSTRING('www.baidu.com',6);
3.SUBSTRING_INDEX函数
SELECT SUBSTRING_INDEX('www.baidu.com','.',-1),
SUBSTRING_INDEX('www.baidu.com','.',1);
4.LOCATE函数
SELECT title
,LOCATE('-',title)
,SUBSTRING(title,1,LOCATE('-',title)-1)
FROM imc_course
5.TRIM函数
SELECT TRIM( ' imooc '),TRIM('x' FROM 'xxximoocxxx')
其他函数
MYSQL8.0新特性
公共表表达式
//公共表定义
WITH cte AS (
SELECT title,study_cnt,class_id
FROM imc_course
WHERE study_cnt>2000
)
//查询
SELECT *
FROM cte
UNION ALL--连接
SELECT *
FROM cte
ORDER BY title;
公共表表达式可以多次引用,与子查询不同,子查询只可引用一次
还可用公共表表达式生成递归序列:
WITH RECURSIVE test AS (
SELECT 1 AS n
UNION ALL
SELECT 1+n FROM test WHERE n<10
)
SELECT * FROM test;
窗口函数
1.
然后执行以下查询语句
SELECT study_name,class_name,score
,ROW_NUMBER() OVER (PARTITION BY class_name ORDER BY score DESC) AS rw
,RANK() OVER (PARTITION BY class_name ORDER BY score DESC) AS rk
,DENSE_RANK() OVER (PARTITION BY class_name ORDER BY score DESC) AS drk
FROM test
ORDER BY class_name,rw;
2.计算每门课程学习人数占总学习人数的百分比
WITH tmp AS(
SELECT class_name,title,study_cnt
,SUM(study_cnt) OVER(PARTITION BY class_name ) AS class_total
FROM imc_course a
JOIN imc_class b ON b.class_id=a.class_id)
SELECT class_name,title,CONCAT(study_cnt/class_total*100,'%')
FROM tmp
ORDER BY class_name
SQL开发规范
- 使用SELECT…LIMIT 1来判断是否存在符合条件的数据,不要使用COUNT(*)
- 在执行一条更新语句后,使用ROW_COUNT()判断是否更新成功
- 使用JOIN关联代替子查询,由于子查询的性能差,子查询的结果集无法使用索引,通常子查询的结果集会被存储到临时表中,不论是内存临时表还是磁盘临时表都不会存在索引,所以查询性能会受到一定的影响。特别是对于返回结果集比较大的子查询,其对查询性能的影响也就越大。
- 在明显没有重复值的时候使用UNION ALL对结果进行连接
- 尽量减少使用SELECT *进行查询,应使用SELECT 【字段名】