SQL进阶语法

插入数据一

INSERT INTO tab_name (col_name) VALUES (要插入的数据,这里是第一行数据), (要插入的数据,这里是第二行数据)…(要插入的数据，第n行数据)

例子：插入两个用户的试卷作答情况到exam_record表中，
（1）用户1001在2021年9月1日晚上10点11分12秒开始作答试卷9001，并在50分钟后提交，得了90分；
（2）用户1002在2021年9月4日上午7点1分2秒开始作答试卷9002，并在10分钟后退出了平台。（注意，用户1002没有完成试卷，所以 submit_time和score两列的值为null）

方法一：指定插入列名
INSERT INTO exam_record (uid, exam_id, start_time, submit_time, score) VALUES
(1001, 9001, '2021-09-01 22:11:12', '2021-09-01 23:01:12', 90),
(1002, 9002, '2021-09-04 07:01:02', NULL, NULL)
方法二：把id的值设置为null或0，让mysql自增
INSERT INTO exam_record VALUES
(NULL, 1001, 9001, '2021-09-01 22:11:12', '2021-09-01 23:01:12', 90),
(NULL, 1002, 9002, '2021-09-04 07:01:02', NULL, NULL)
方法三：直接填入id值
INSERT INTO exam_record VALUES
(1, 1001, 9001, '2021-09-01 22:11:12', '2021-09-01 23:01:12', 90),
(2, 1002, 9002, '2021-09-04 07:01:02', NULL, NULL)
1
2
3
4
5
6
7
8
9
10
11
12

插入数据二

插入方式汇总：

1、普通插入（全字段）：INSERT INTO table_name VALUES (value1, value2, …)
2、普通插入（限定字段）：INSERT INTO table_name (column1, column2, …) VALUES (value1, value2, …)
3、多条一次性插入：INSERT INTO table_name (column1, column2, …) VALUES (value1_1, value1_2, …), (value2_1, value2_2, …), …
4、从另一个表导入：INSERT INTO table_name(column1, column2, …) SELECT (column1, column2, …) FROM table_name2 [WHERE key=value]

题目一：做数据备份，将exam_record表中2021年之前的内容备份到exam_record_before_2021表中。

insert into exam_record_before_2021(uid, exam_id, start_time, submit_time, score)
select uid, exam_id, start_time, submit_time, score from exam_record
where year(submit_time)<'2021'
1
2
3

题目二：有一套ID为9003的高难度SQL试卷，时长一个半小时，需要将2021-01-01 00:00:00作为发布时间插入到试题信息表中，不管ID是否存在都要插入成功。

方法一：使用replace into。replace into首先尝试插入数据列表，如果发现表中已经有了这行数据，先删除此行数据，然后插入新的数据，否则直接插入新数据。

replace into examination_info values
(null,9003,'SQL','hard',90,'2021-01-01 00:00:00')
1
2

方法二：使用insert into，但是需要（delete）优先删除用户名为9003的数据。

delete from examination_info 
where exam_id=9003;
insert into examination_info 
values(null,9003,'SQL','hard',90,'2021-01-01 00:00:00')
1
2
3
4

更新数据一

修改方式汇总：

1、设置为新值：

UPDATE table_name 
SET column_name=new_value
WHERE column_name3=value3

例如，将tag字段的PYTHON转为Python：
update examination_info
set tag='Python'
where tag='PYTHON'
1
2
3
4
5
6
7
8

2、根据已有值替换：(可整串替换也可以子串替换！)

UPDATE table_name 
SET key1=replace(key1, '查找内容', '替换成内容') 
WHERE column_name3=value3

例如，将tag字段的PYTHON转为Python：（整串替换）
update examination_info
set tag=replace(tag,"PYTHON","Python")
where tag='PYTHON'

子串替换，将APYTHON替换为APython：
update examination_info
set tag=replace(tag,"PYTHON","Python")
where tag like "%PYTHON%"
1
2
3
4
5
6
7
8
9
10
11
12
13

删除记录

删除方式汇总：

1、根据条件删除：

DELETE FROM tb_name 
WHERE options
ORDER BY fields LIMIT n 
1
2
3

2、全部删除（表清空，包含自增计数器重置）：

TRUNCATE tb_name
1

牛客. SQL8 删除记录三

注意，使用delete from删除表需要手动自增ID，从效率角度考虑，还是全部删除（TRUNCATE tb_name）效率更高。上次用delete from删除的解法是：

delete from exam_record;
alter table exam_record auto_increment=1;
1
2

创建一张新表

表的操作：创建、修改、删除。

1、直接创建表
牛客. SQL9 创建一张新表
2、从另一张表复制表结构创建表
3、从另一张表的查询结果创建表
4、修改表

{ ADD COLUMN <列名> <类型>  -- 增加列
 | CHANGE COLUMN <旧列名> <新列名> <新列类型> -- 修改列名或类型
 | ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT } -- 修改/删除 列的默认值
 | MODIFY COLUMN <列名> <类型> -- 修改列类型
 | DROP COLUMN <列名> -- 删除列
 | RENAME TO <新表名> -- 修改表名
 | CHARACTER SET <字符集名> -- 修改字符集
 | COLLATE <校对规则名> } -- 修改校对规则（比较和排序时用到）
1
2
3
4
5
6
7
8

牛客. SQL10 修改表
5、删除表

drop table if exists 表名1，表名2....
1

牛客. SQL11 删除表

索引的创建和删除

索引的创建、删除、使用：

1、create方式创建索引

CREATE INDEX 索引名 ON 表名(列名);  --不指定唯一或全文时默认为普通索引
CREATE UNIQUE INDEX 索引名 ON 表名(列名);  --唯一索引
CREATE FULLTEXT INDEX 索引名 ON 表名(列名);  --全文索引
1
2
3

牛客. SQL12 创建索引

2、alter方式创建索引

alter方式创建索引基本格式：

alter table 表名 add unique index 索引名(列名);  --唯一索引
alter table 表名 add fulltext 索引名(列名);    --全文索引
alter table 表名 add index 索引名(列名);   --不指定唯一或全文时默认为普通索引
1
2
3

3、drop方式删除索引

DROP INDEX <索引名> ON <表名>
1

牛客. SQL13 删除索引

牛客. SQL13 删除索引:
DROP INDEX uniq_idx_exam_id ON examination_info;
DROP INDEX full_idx_tag ON examination_info;
1
2
3

4、alter方式删除索引

ALTER TABLE <表名> DROP INDEX <索引名>

牛客. SQL13 删除索引:
alter table examination_info drop index uniq_idx_exam_id;
alter table examination_info drop index full_idx_tag;
1
2
3
4
5

聚合分组查询

牛客. SQL16不小于平均分的最低分

牛客. SQL17 平均活跃天数和月活人数

SUBSTR(str,pos,len): 从pos开始的位置，截取len个字符。
SUBSTR(str,pos): pos开始的位置，一直截取到最后。
1
2

多表查询

牛客. SQL45 对过长的昵称截取处理

length函数：计算值的长度，但1个中文会算作长度3,1个数字或者字母算作长度1;
char_length函数：计算值的长度，但1个中文或者1个数字或者字母都算作长度1;