CRUD
CRUD:就是增加(Create)、查询(Retrieve)、更新(Update)、删除(Delete)四个单词的首字母缩写
注释:这个就是在数据库SQL语句里做一些注释说明,可以使用“--空格+描述”来表示注释说明
1、新增(Create)
语法:
INSERT [INTO] table_name
[(column [,column]...)]
VALUES (value_list) [,(value_list)]...
value_list: value,[,value]...
--先创建一张学生表
--先创建一张学生表
DROP TABLE IF EXISTS student;
CREATE TABLE student(
id INT,
sn INT comment '学号',
name VARCHAR(20) comment '姓名',
qq_mail VARCHAR(20) comment '邮箱'
) ;
1.1 单行数据+全列插入
--插入两条记录
--插入两条记录,value_list 数量必须与定义表的列的数量以及顺序一致
INSERT INTO student VALUES (1,001,'唐三藏',NULL);
INSERT INTO student VALUES (2,002,'孙悟空','11111');
2.2多行数据+指定列插入
--插入两条记录,value_list 数量必须与定义表的列的数量以及顺序一致
INSERT INTO studnet(id,sn,name) VALUES
(102,003,'曹孟德'),
(103,004,'关羽');
2、查询(Retrieve)
语法:
SELECT
[DISTINCT] {*| {column [,column]...}
[FROM table_name]
[WHERE ..]
[ORDER BY column [ASC| DESC],...]
LIMIT..
//ASC是增序,DESC是降序
案例:
--创建考试成绩表
--创建考试成绩表
DROP TABLE IF EXISTS exam_result;
CREATE TABLE exam_result(
id INT,
name VARCHAR(20),
chinese DECIMAL(3,1),
math DECIMAL(3,1),
english DECIMAL(3,1)
);
--插入测试数据
INSERT INTO exam_result (id,name,chinese,math,english)VALUES
(1,'唐三藏',67,55,55),
(2,'孙悟空',66,77,88),
(3,'猪悟能',77,66,66),
(4,'沙和尚',88,66,56),
(5,' 天津饭',78,44,55),
(6,'贝吉塔',65,56,77);
2.1全列查询
--通常情况不建议使用 *进行全列查询,耗费资源大
1.查询的列越多,意味着需要传输的数据量越大 。2.可能会影响索引的使用。
SELECT * FROM exam_result;
2.2指定列查询
--指定列的顺序不需要按定义
SELECT id,name,english FROM exam_result;
3.3查询字段为表达式
--表达式不包括字段
SELECT id,name,10 FROM exam_result;
--表达式包含一个字段
SELECT id,name,english+10 FROM exam_result;
--表达式包含多个字段
SELECT id,name,chinese+math+english FROM exam_result;
为查询结果列指定别名,表示返回的结果集中,以别名作为该列的名称
语法:
SELECT column [AS] alias_name[...] FROM table_name;
--结果集中,表头的列名=别名
SELECT id,name,chinese+math+english [AS] 总分 FRMO exam_result;
AS可以忽略,最好写上
3.4去重:DISTINCT
使用DISTINCT 关键字对某列数据进行去重:
例如 math 成绩有3个人都是99分,那么我查询的时候可以去重结果
SELECT DISTINCT math FROM exam_result;
返回结果结果没有重复数据了
3.5 排序 ORDER BY
语法:
--ASC 为升序
--DESC 为降序
默认为ASC
SELECT ... FROM table_name [WHERE..]
ORDER BY column [ASC|DESC],[..];
注意:
1.没有ORDER BY字句的查询,返回的顺序是未定义的,永远不要依赖这个顺序
2.NULL数据排序,视为比任何值都小,升序出现在最上面,降序出现在最下面
--查询同学姓名和qq邮箱,按qq_mail排序显示
SELECT name,qq_email FROM student ORDER BY qq_email DESC;
3.使用表达式以及别名排序
--查询同学以及总分,由高到低
SELECT name,chinese+english+math AS total FROM exam_result ORDER BY total DESC;
4.可以对多个字段进行排序,排序优先级随书写顺序
-- 查询同学各门成绩,依次按数学降序,英语升序,语文升序的方式显示
SELECT name,math,english,chinese
FROM exam_result ORDER BY math DESC,english,chinese;
排序优先级是:先按数学降序优先度最高,如果数学分数一样的, 按照英语升序排序,如果数学,英语成绩一样的,然后再按照语文升序排序
总结起来,排序优先级根据查询语句中排序条件的书写顺序来确定,第一个排序条件具有最高的优先级,以此类推。这样可以确保按照期望的顺序对查询结果进行排序。
3.7条件查询 WHERE
这里只说一些特殊运算符
比较运算符
预算符号 | 说明 |
<=> | 等于,NULL 安全,例如NULLNULL结果是TRUE(1) |
= | 等于,NULL不 安全,例如NULLNULL结果是NULL |
BETWEEN a0 AND a1 | 范围匹配,[a0,a1],如果a0 |
IN(option,...) | 如果option中的任意一个,返回TRUE(1) |
LIKE | 模糊匹配,&表示任意多个(包括0个),任意字符, _表示任意一个字符 |
例子:
LIKE的案例
寻找“Smith”开头的所有姓氏记录
SELECT * FROM users WHERE last_name LIKE 'Smith%';
寻找'J'开头,然后任意字符,最后'son'结尾的用户名
SELECT * FROM users WHERE username LIKE 'J_son';
查找包含字母 "e" 的任何位置的名称:
SELECT * FROM users WHERE name LIKE '%e%';
查找以字母 "A" 或 "B" 开头的用户名:
SELECT * FROM users WHERE username LIKE '[AB]%';
IN的案例
IN的案例
查询将返回 age 列的值为 18、25 或 30 的用户记录。
SELECT * FROM users WHERE age IN (18, 25, 30);
使用子查询:
返回所属国家为美国的部门的用户记录。
SELECT * FROM users WHERE department_id IN
(SELECT id FROM departments WHERE country = 'USA');
id 列的值不是 4 或 5 的用户记录。
SELECT * FROM users WHERE id NOT IN (4, 5);
--需要注意的是,使用 IN 操作符时,给定的列表中的值应该是逗号分隔的,
并且小括号用于将列表括起来。
此外,当使用 IN 操作符进行查询时,如果列表中的值很多,可能会对性能产生负面影响。
因此,在使用 IN 操作符时,请谨慎选择列表的大小以及优化查询和索引的使用,以提高查询性能。
3.8分页查询 :LIMIT
语法:
从0开始,筛选N条结果
SELECT ...FROM table_name [WHERE ...] [ORDER BY...] LIMIT n;
从s开始,筛选n条结果
SELECT ...FROM table_name [WHERE ...][ ORDER BY ] LIMIT s,n;
更明确:
SELECT ...FROM table_name [WHERE ...][ ORDER BY ] LIMIT n OFFSET s;
案例:按id分页,每页3条记录,分别显示 第1,2,3页
第一页
SELECT id,name,math, english,chinese FROM exam_result ORDER BY id LIMIT 3
OFFSET 0;
第二页
SELECT id,name,math, english,chinese FROM exam_result ORDER BY id LIMIT 3
OFFSET 3;
第三页,结果不够3个无影响
SELECT id,name,math, english,chinese FROM exam_result ORDER BY id LIMIT 3
OFFSET 6;
LIMIT 用于限制返回的记录数量 LIMIT 3表示每页返回3条记录
OFFSET 用于指定起始位置偏移量 OFFSET 0 表示从结果集中的第一条记录开始
3、修改(UPDATE)
语法:
UPDATE table_name SET column=expr [,column=expr...]
[WHERE ...] [ORDER BY ...] [LIMIT ....]
案例:
-- 将孙悟空同学的数学成绩变更为 80 分
UPDATE exam_result SET math = 80 WHERE name = '孙悟空';
-- 将曹孟德同学的数学成绩变更为 60 分,语文成绩变更为 70 分
UPDATE exam_result SET math = 60, chinese = 70 WHERE name = '曹孟德';
-- 将总成绩倒数前三的 3 位同学的数学成绩加上 30 分
UPDATE exam_result SET math = math + 30 ORDER BY chinese + math + english LIMIT3;
-- 将所有同学的语文成绩更新为原来的 2 倍
UPDATE exam_result SET chinese = chinese * 2;
4、删除(DELETE)
语法:
DELETE FROM table_name [WHERE ...] [ORDER BY..] [LIMIT....]
案例:
-- 删除孙悟空同学的考试成绩
DELETE FROM exam_result WHERE name = '孙悟空';
-- 删除整张表数据-- 准备测试表
DROP TABLE IF EXISTS for_delete;CREATE TABLE for_delete (id INT,name VARCHAR(20));
-- 插入测试数据
INSERT INTO for_delete (name) VALUES ('A'), ('B'), ('C');
-- 删除整表数据
DELETE FROM for_delete;
5、重点总结
-- 单行插入
insert into 表(字段1, ..., 字段N) values (value1, ..., value N);
-- 多行插入
insert into 表(字段1, ..., 字段N) values
(value1, ...),(value2, ...),(value3, ...);
-- 全列查询
select * from 表
-- 指定列查询
select 字段1,字段2... from 表
-- 查询表达式字段
select 字段1+100,字段2+字段3 from 表
-- 别名
select 字段1 别名1, 字段2 别名2 from 表
-- 去重DISTINCT
select distinct 字段 from 表
-- 排序ORDER BY
select * from 表 order by 排序字段