sqlite给列添加备注

转载

数码精灵abc 2024-10-29 06:36:33

文章标签 sqlite给列添加备注数据 MySQL 子查询 文章分类 计算机视觉人工智能

文章目录

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	更新表中数据

向表中写入数据

sqlite给列添加备注_sqlite给列添加备注

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;

这样就可以插入成功，同时发现加入时间这一列的值更新为当前的时间。

查询表中的数据

sqlite给列添加备注_sqlite给列添加备注_02

ALL语句

从课程表中选出学习人数这一列所有的值

SELECT ALL study_cnt FROM imc_course;

DISTINCT语句

对于每一列中可能会有重复的值，DISTINCT可以滤除重复的值

有如下一张表CESHIDEMO

sqlite给列添加备注_数据_03

执行以下语句得到如下结果：

select distinct name from CESHIDEMO

sqlite给列添加备注_数据_04

但如果查询的是多个字段，则只会滤除所有字段值相同的记录，如

SELECT DISTINCT  name, age FROM CESHIDEMO；

得到的结果依然是：

sqlite给列添加备注_数据_05

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;

sqlite给列添加备注_MySQL_06

4.查找一段字符串是否包含某一段字符：

SELECT 'this is mysql course' LIKE '%mysql%';

sqlite给列添加备注_MySQL_07

SELECT 'mysql' LIKE '_ysql';

sqlite给列添加备注_MySQL_08

SELECT 'xxxxxxxmysql' LIKE '_ysql';

sqlite给列添加备注_数据_09

5.查找课程ID为1,3，5,7，9的课程名称

SELECT title
FROM imc_course
WHERE course_id IN (1,3,5,7,9);

MySQL的逻辑运算符

sqlite给列添加备注_子查询_10

任何值与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关联多个表

sqlite给列添加备注_子查询_11

内关联

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;

sqlite给列添加备注_sqlite给列添加备注_12

假如我们在课程表中插入一条新数据，这时候再内关联查询，是否可以查询出这条新数据呢？

sqlite给列添加备注_MySQL_13

会发现无法查询出该数据，所以说，内关联只能查询出同时存在于两个表中的数据，那么怎样查询出这条数据呢，这就需要外关联

外关联

左外关联：

sqlite给列添加备注_子查询_14

左外关联查询到的数据就是一定存在于左表中的，若右表的关联列为空，则只查询到左表且不存在于右表的数据（即用于查询不存在右表的数据的情况）；所以上文中提到的在左表新加一条记录如何查询到，就用左外关联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;

右外关联：

sqlite给列添加备注_MySQL_15

与左外关联一样，可以查询存在于右表或只存在于右表的数据，其他用法参照左外关联

分组查询GROUP BY…HAVING子句

把结果集按某些列分成不同的组，并对分组后的数据进行聚合操作

如下，我们想要把课程表中按难度计算每一种难度的课程数目为多少

sqlite给列添加备注_sqlite给列添加备注_16

在这里，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

sqlite给列添加备注_MySQL_17

可以通过选择合适的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
;

聚合函数

sqlite给列添加备注_子查询_18

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
;

sqlite给列添加备注_sqlite给列添加备注_19

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
;

创建视图

sqlite给列添加备注_数据_20

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
;

按条件删除表中的数据

sqlite给列添加备注_MySQL_21

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

sqlite给列添加备注_sqlite给列添加备注_22

确定要更新的数据存在哪个表中
确定要更新的列及列的值
确定过滤条件

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
;

系统函数

时间函数

sqlite给列添加备注_数据_23

SELECT CURDATE(),CURTIME(),NOW()；

sqlite给列添加备注_sqlite给列添加备注_24

sqlite给列添加备注_子查询_25

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s')；

sqlite给列添加备注_sqlite给列添加备注_26

SELECT SEC_TO_TIME(60),TIME_TO_SEC('01:00:00')；

sqlite给列添加备注_数据_27

sqlite给列添加备注_sqlite给列添加备注_28

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分钟

sqlite给列添加备注_sqlite给列添加备注_29

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());--提取秒

sqlite给列添加备注_MySQL_30

字符串函数

sqlite给列添加备注_MySQL_31

SELECT CONCAT_WS('||',class_name,title)
FROM imc_course a
JOIN imc_class b ON b.class_id=a.class_id
;

sqlite给列添加备注_sqlite给列添加备注_32

SELECT class_name,LENGTH(class_name),CHAR_LENGTH(class_name)
FROM imc_class；

sqlite给列添加备注_sqlite给列添加备注_33

SELECT  FORMAT('123456.789',4)

sqlite给列添加备注_数据_34

sqlite给列添加备注_子查询_35

1.LEFT / RIGHT函数

SELECT LEFT('www.baidu.com',6),
       RIGHT('www.baidu.com',6)

sqlite给列添加备注_sqlite给列添加备注_36

2.SUBSTRING函数

SELECT SUBSTRING('www.baidu.com',6);

sqlite给列添加备注_子查询_37

3.SUBSTRING_INDEX函数

SELECT SUBSTRING_INDEX('www.baidu.com','.',-1),
       SUBSTRING_INDEX('www.baidu.com','.',1);

sqlite给列添加备注_MySQL_38

4.LOCATE函数

SELECT title
       ,LOCATE('-',title)
       ,SUBSTRING(title,1,LOCATE('-',title)-1)
FROM imc_course

sqlite给列添加备注_数据_39

5.TRIM函数

SELECT TRIM( '  imooc '),TRIM('x' FROM 'xxximoocxxx')

sqlite给列添加备注_数据_40

其他函数

sqlite给列添加备注_数据_41

MYSQL8.0新特性

公共表表达式

sqlite给列添加备注_sqlite给列添加备注_42

//公共表定义
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;

公共表表达式可以多次引用，与子查询不同，子查询只可引用一次

sqlite给列添加备注_sqlite给列添加备注_43

还可用公共表表达式生成递归序列：

WITH RECURSIVE test AS (
SELECT 1 AS n
UNION ALL
SELECT 1+n FROM test WHERE n<10
)
SELECT * FROM test;

sqlite给列添加备注_sqlite给列添加备注_44

窗口函数

sqlite给列添加备注_子查询_45

sqlite给列添加备注_子查询_46

sqlite给列添加备注_子查询_47

sqlite给列添加备注_sqlite给列添加备注_48

然后执行以下查询语句

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;

sqlite给列添加备注_MySQL_49

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

sqlite给列添加备注_数据_50

SQL开发规范

使用SELECT…LIMIT 1来判断是否存在符合条件的数据，不要使用COUNT(*)
在执行一条更新语句后，使用ROW_COUNT()判断是否更新成功
使用JOIN关联代替子查询，由于子查询的性能差，子查询的结果集无法使用索引，通常子查询的结果集会被存储到临时表中，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响。特别是对于返回结果集比较大的子查询，其对查询性能的影响也就越大。
在明显没有重复值的时候使用UNION ALL对结果进行连接
尽量减少使用SELECT *进行查询，应使用SELECT 【字段名】

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：fivem管理员指令大全

下一篇：Facets 不能添加 spring

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯