SQL2022-8-10

+# SQL
提示：sql

---

---

前言

提示：这里可以添加本文要记录的大概内容：

SQL语言，是结构化查询语言(Structured Query Language)的简称。SQL语言是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名。

---

提示：以下是本篇文章正文内容，下面案例可供参考

一、理论

1.SQL编写顺序：

FROM、ON 、JOIN、WHERE、GROUP BY、AGG_FUNC、WITH、HAVING、SELECT、UNION、DISTINCT 、ORDER BY、LIMIT。
在实际执行过程中，每个步骤都会为下一个步骤生成一个虚拟表，这个虚拟表将作为下一个执行步骤的数据。
1、FROM：选择FROM后面跟的表，产生虚拟表1。
2、ON：ON是JOIN的连接条件，符合连接条件的行会被记录在虚拟表2中。
3、JOIN：如果指定了LEFT JOIN，那么保留表中未匹配的行就会作为外部行添加到虚拟表2中，产生虚拟表3。如果有多个JOIN链接，会重复执行步骤1~3，直到处理完所有表。
4、WHERE：对虚拟表3进行WHERE条件过滤，符合条件的记录会被插入到虚拟表4中。
5、GROUP BY：根据GROUP BY子句中的列，对虚拟表4中的记录进行分组操作，产生虚拟表5。
6、AGG_FUNC：常用的 Aggregate 函数包涵以下几种：（AVG：返回平均值）、（COUNT：返回行数）、（FIRST：返回第一个记录的值）、（LAST：返回最后一个记录的值）、（MAX： 返回最大值）、（MIN：返回最小值）、（SUM： 返回总和）。
7、WITH对虚拟表5应用ROLLUP或CUBE选项，生成虚拟表 6。
8、HAVING：对虚拟表6进行HAVING过滤，符合条件的记录会被插入到虚拟表7中。
9、SELECT：SELECT到一步才执行，选择指定的列，插入到虚拟表8中。
10、UNION：UNION连接的两个SELECT查询语句，会重复执行步骤1~9，产生两个虚拟表9，UNION会将这些记录合并到虚拟表10中。
11、DISTINCT将重复的行从虚拟表10中移除，产生虚拟表 11。DISTINCT用来删除重复行，只保留唯一的。
12、ORDER BY: 将虚拟表11中的记录进行排序，虚拟表12。
13、LIMIT：取出指定行的记录，返回结果集。

2.SQL中的锁

3.面试题

1. 牛客数据库面试题
2. mysql面试题

二、数据的增删改

1. 插入

1）insert

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

#字段id auto_increment,uid,exam_id,start_time, submit_time, score
#id为自动增长
insert into  exam_record(uid,exam_id,start_time, submit_time, score) values (1001,9001,"2021-09-01 10:11:12",'2021-09-01 23:01:12', 90) # id不填
insert into  exam_record values (NULL, 1001,9001,"2021-09-01 10:11:12",'2021-09-01 23:01:12', 90) #id=NULL自动增长
#关键字NULL可以用DEFAULT替代。

2）replace

replace into 跟 insert into功能类似，不同点在于：replace into 首先尝试插入数据到表中，如果发现表中已经有此行数据（根据主键或者唯一索引判断）则先删除此行数据，然后插入新的数据；否则，直接插入新数据。
要注意的是：插入数据的表必须有主键或者是唯一索引！否则的话，replace into 会直接插入数据，这将导致表中出现重复的数据。

2. 更新：

1）设置为新值

UPDATE table_name SET column_name=new_value [, column_name2=new_value2] [WHERE column_name3=value3]

2）根据已有值替换

UPDATE table_name SET key1=replace(key1, ‘查找内容’, ‘替换成内容’) [WHERE column_name3=value3]

UPDATE examination_info
SET tag = "Python"
WHERE tag = "PYTHON";
#当tag为PYTHON时，修改tag为Python

UPDATE examination_info
SET tag = REPLACE(tag, "PYTHON", "Python")
WHERE tag = "PYTHON";
#不仅可用于整体替换，还能做子串替换，例如要实现将tag中所有的PYTHON替换为Python（如CPYTHON=>CPython）

update exam_record
set submit_time='2099-01-01 00:00:00',score=0 
where start_time<"2021-09-01" and score is null

3.删除:delete from和drop table

1）根据条件删除

DELETE FROM tb_name
 [WHERE options]
  [ [ ORDER BY fields ] LIMIT n ]

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

TRUNCATE tb_name

三、表与索引操作

1. 表的创建、修改与删除

表的创建和修改可以通过如下代码查看表的结构进行确认

SHOW FULL FIELDS FROM user_info 

1）创建表

1.1 直接创建表：

CREATE TABLE
[IF NOT EXISTS] tb_name -- 不存在才创建，存在就跳过
(column_name1 data_type1 -- 列名和类型必选
  [ PRIMARY KEY -- 可选的约束，主键
   | FOREIGN KEY -- 外键，引用其他表的键值
   | AUTO_INCREMENT -- 自增ID
   | COMMENT comment -- 列注释（评论）
   | DEFAULT default_value -- 默认值（可用当前时间current_timestamp）
   | UNIQUE -- 唯一性约束，不允许两条记录该列值相同
   | NOT NULL -- 该列非空
  ], ...
) [CHARACTER SET charset] -- 字符集编码
[COLLATE collate_value] -- 列排序和比较时的规则（是否区分大小写等）

举个栗子

create table user_info_vip(
id int(11) not null  primary key auto_increment comment '自增ID',
uid int(11) not null unique  key  comment "用户ID" ,
nick_name varchar(64) comment "昵称",
achievement int(11) default 0 comment "成就值"
    ,level int(11) comment "用户等级"
    ,job varchar(32) comment "职业方向"
    ,register_time datetime default current_timestamp comment "注册时间"
)CHARACTER SET utf8 COLLATE utf8_general_ci;
# 或者 ")default charset=utf8"

1.2 从另一张表复制表结构创建表：

CREATE TABLE tb_name LIKE tb_name_old

1.3 从另一张表的查询结果创建表：

CREATE TABLE tb_name AS SELECT * FROM tb_name_old WHERE options

2）修改表

ALTER TABLE 表名 修改选项 。选项集合：

   { ADD COLUMN <列名> <类型> [after  col2] -- 在col2后增加列
     | CHANGE COLUMN <旧列名> <新列名> <新列类型> [default default_value] -- 修改列名或类型、默认值
     | ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT } -- 修改/删除 列的默认值