数据库基础-复习-SQL

一. 前言

在数据库中，SQL 语句分为四大类

• DML(Data Manipulation Language)：select, insert, update, delete
• DDL(Data Definition Language)：create, alter, drop, truncate
• DCL(Data Control Language)：grant, revoke
• TCL(Transaction Control Language)：commit, rollback, savepoint

SQL的三种注释方法

/* 注释1
   注释2 */
SELECT *
FROM mytable; -- 注释
# 注释

数据库的创建与使用

CREATE DATABASE mydbs; --创建数据库
USE mydbs; --使用数据库
SHOW CREATE DATABASE mydbs; --显示指定的已创建数据库信息

二. 创建表

DROP TABLE IF EXISTS student; --如果已存在同名表，则删除原表重新创建
CREATE TABLE student (
	id INT NOT NULL AUTO_INCREMENT, --字段名id ，int类型，不可为空，自增默认初始为1步长为1
	name VARCHAR(45) NOT NULL, 
	email VARCHAR(45) NULL, --字段名email，变长字符串类型，可为空
	address VARCHAR(45) NOT NULL,
	birthday DATE NOT NULL, --字段名birthday，日期类型，不可为空
	PRIMARY KEY (id) --主键id
) ENGINE=InnoDB; --默认的引擎已设置为InnoDB可省略

三. 修改表

添加列

ALTER TABLE student
ADD phone CHAR(11);

删除列

ALTER TABLE student
DROP COLUMN phone;

删除表

DROP TABLE student;

重命名表

RENAME TABLE student TO mystudent;

四. 插入数据

插入完整的行

#方法一，对应字段名插入值
INSERT INTO student(id, name, email, address, birthday)
VALUES('1','张三','zhangsan@qq.com','西安市','1992-01-01');

#方法二，省略字段名插入
 INSERT INTO student
 VALUES('2','李四','lisi@qq.com','重庆市','1994-01-01'),
 ('3','王二','wanger@qq.com','北京市','1996-01-01');

插入检索出的数据

INSERT INTO mystudent(name, address)
SELECT name, address
FROM student;

复制一个表的内容到新表

CREATE TABLE newstudent AS
SELECT * FROM student;

五. 更新和删除数据

更新数据

UPDATE student
SET email = "zhangsan@163.com", address = "咸阳市"
WHERE id = 1;`在这里插入代码片`

删除数据

DELETE FROM student
WHERE id = 3;

清空表

TRUNCATE TABLE student; --效率高，相当于删除原表并重新创建

更新和删除数据一定要带 WHERE 语句，否则容易破坏表中数据

六. 检索数据

SELECT … FROM

SELECT name,address
FROM student;

DISTINCT 相同值只会出现一次，多列情况时，所有列的值都相同才算相同

SELECT DISTINCT name,address
FROM student;

LIMIT 限制检索行数，第一个参数+1为起始行，第二个参数为检索行数
如果存在 ORDER BY ，LIMIT必须放其后面

SELECT *
FROM student
LIMIT 3,5 --检索从第4行开始，共检索5行

七. 排序检索到的数据

ASC：升序（默认）；DESC：降序

SELECT *
FROM student
ORDER BY address DESC, name; --先按地址降序，如果有地址相同的行，再按名字升序

八. 过滤数据

BETWEEN…AND… 指定一个由低到高的范围

SELECT name
FROM student
WHERE id BETWEEN 1 AND 3;

IS NULL

SELECT name
FROM student
WHERE email IS NULL;

AND 和 OR 用于连接多个过滤条件。优先处理 AND，也可以使用 () 来决定优先级

IN 操作符用于匹配一组值，其后也可以接一个 SELECT 子句，从而匹配子查询得到的一组值

NOT 操作符用于否定一个条件，有时候对复杂子句取反可以使过滤条件变简单

九. 使用通配符过滤

% 匹配多个字符；_ 匹配单个字符；正则表达式
过滤条件用 LIKE 连接

SELECT name
FROM student
WHERE email LIKE '%@qq%' --过滤出用QQ邮箱的学生名

通配符会降低检索效率，不能滥用通配符

十. 计算字段

计算字段通常需要使用 AS 来取别名，否则输出的时候字段名为计算表达式

SELECT col1 * col2 AS col3
FROM mytable;

连接字段使用 CONCAT() ，许多数据库会使用空格把一个值填充为列宽，因此连接的结果会出现一些不必要的空格，使用 TRIM() 可以去除首尾空格

SELECT CONTCAT(TRIM(col1),' (',TRIM(col2),')') AS concat_col
FROM mytable;

十一. 函数

聚集函数

函数	说明
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列值之和

文本处理

函数	说明
LEFT()	左边的字符
RIGHT()	右边的字符
LOWER()	转换为小写字符
UPPER()	转换为大写字符
LTRIM()	去除左边的空格
RTRIM()	去除右边的空格
LENGTH()	长度
SOUNDEX()	转换为语音值

日期和时间处理

• 日期格式：YYYY-MM-DD
• 时间格式：HH:MM:SS

函数	说明
ADDDATE()	增加一个日期（天、周等）
ADDTIME()	增加一个时间（时、分等）
CURDATE()	返回当前日期
CURTIME()	返回当前时间
DATE()	返回日期时间的日期部分
DATADIFF()	计算两个日期之差
DATE_ADD()	高度灵活的日期运算函数
DATE_FORMAT()	返回一个格式化的日期或时间串
DAY()	返回一个日期的天数部分
DAYOFWEEK()	对于一个日期，返回对应的星期几
HOUR()	返回一个时间的小时部分
MINUTE()	返回一个时间的分钟部分
MONTH()	返回一个时间的月份部分
NOW()	返回当前日期和时间
SECOND()	返回一个时间的秒部分
TIME()	返回一个日期时间的时间部分
YEAR()	返回一个日期的年份部分

数值处理

函数	说明
SIN()	正弦
COS()	余弦
TAN()	正切
ABS()	绝对值
SQRT()	平方根
MOD()	余数
EXP()	指数
PI()	圆周率
RAND()	随机数

十二. 分组数据

GROUP BY 可以把具有相同的数据值的行放在同一组中，从而进行下一步操作：
对同一分组的数据使用聚集函数进行处理，如 COUNT(*) 计算分组后各类的数量
可以在分组前使用 WHERE 条件过滤数据，分组后使用 HAVING 条件过滤数据
最后可以对结果进行 ORDER BY 排序

SELECT col, COUNT(*) AS num
FROM mytable
WHERE col>3
GROUP BY col
HAVING num>10
ORDER BY num;

分组规定

• GROUP BY 子句出现在 WHERE 子句之后，ORDER BY 子句之前
• 除了汇总字段外，SELECT 语句中的每一字段都必须在 GROUP BY 子句中给出
• NULL 的行会单独分为一组
• 大多数 SQL 实现不支持 GROUP BY 列具有可变长度的数据类型

十三. 子查询

子查询只能返回一个字段的数据

方式一：
利用子查询过滤数据，即将子查询的结果作为 WHERE 语句的过滤条件

SELECT *
FROM mytable2
WHERE col2 IN (SELECT col1
			   FROM mytable1);

方式二：
利用子查询创建计算字段，即将子查询的结果作为 SELECT 语句的一个计算字段

#这条语句可以检索出客户的订单数量，子查询语句会对第一个查询检索出的每个客户执行一次
SELECT cust_name, (SELECT COUNT(*)
                   FROM Orders
                   WHERE Orders.cust_id = Customers.cust_id)
                   AS orders_num
FROM Customers
ORDER BY cust_name;

十四. 联结表

内联结（INNER JOIN）

SELECT A.value, B.value
FROM table_a AS A INNER JOIN table_b AS B
ON A.key = B.key;

自联结（内联结的一种）

SELECT e1.name
FROM employee AS e1 INNER JOIN employee AS e2
ON e1.department = e2.department
AND e2.name = "Jim";

外联结（LEFT JOIN 和 RIGHT JOIN）
左外联结就是以左表为主表，保留左表没有关联的行，右外同理

SELECT Customers.cust_id, Customer.cust_name, Orders.order_id
FROM Customers LEFT JOIN Orders
ON Customers.cust_id = Orders.cust_id;

MySQL不支持 FULL JOIN，所以直接使用 UNION 连接 左外表和右外表 就可以达到FULL JOIN效果

十五. 组合查询

使用 UNION 来组合两个查询的行，两个查询的字段和聚集函数要相同
默认会去除相同行，要保留则使用 UNION ALL
只能使用一次 ORDER BY 语句，且位于语句最后

SELECT col
FROM mytable
WHERE col = 1
UNION
SELECT col
FROM mytable
WHERE col =2;

十六. 视图

视图是虚拟的表，本质是一段写好的 SQL 语句，如把一个复杂的联结表包装成视图，就可以直接对视图进行操作，操作方式与普通表一样，一般用来检索数据

创建视图

CREATE VIEW myview AS
SELECT Concat(col1, col2) AS concat_col, col3*col4 AS compute_col
FROM mytable
WHERE col5 = val;

视图具有以下好处

• 可以方便的重用 SQL 语句，简化复杂的 SQL 操作
• 可以使用表的一部分而非整个表
• 给用户访问视图的权限，保护数据安全
• 更改数据表示格式

十七. 存储过程

存储过程就是为以后的使用而保存的多条 SQL 语句的集合，可以看作为一系列 SQL 操作的批处理，存储过程的优点是简单、安全、高性能

创建存储过程

/*命令行中创建存储过程需要自定义分隔符，因为命令行也是以 ; 为结束符，
而存储过程中也包含了分号，因此会错误把这部分分号当成是结束符，造成语法错误。*/
DELIMITER $ --声明新的结束符号$

CREATE PROCEDURE mypercedure(out ret int) --包含 in(传入)、out(传出) 和 inout(传入传出) 三种参数
BEGIN
	DECLARE x int;
	SELECT sum(col1) --给变量赋值都需要用 select into 语句
	FROM mytable
	INTO x;

	SELECT x*x INTO ret;
END$

DELIMITER ; --恢复原来的分号为结束符号
	

调用存储过程

CALL myprocedure(@ret);
SELECT @ret;

十八. 游标

游标在存储过程中使用，可以对一个结果集进行移动遍历。

使用游标的四个步骤：

1. 声明游标，这个过程没有实际检索出数据；
2. 打开游标；
3. 取出数据；
4. 关闭游标；

delimiter $
create procedure myprocedure(out ret int)
    begin
        declare done boolean default 0;

        declare mycursor cursor for
        select col1 from mytable;
        # 定义了一个 continue handler，当 sqlstate '02000' 这个条件出现时，会执行 set done = 1
        declare continue handler for sqlstate '02000' set done = 1;

        open mycursor;

        repeat
            fetch mycursor into ret;
            select ret;
        until done end repeat;

        close mycursor;
    end$
 delimiter ;

十九. 触发器

触发器会在某个表执行以下语句时而自动执行：DELETE、INSERT、UPDATE。

触发器必须指定在语句执行之前还是之后自动执行，之前执行使用 BEFORE 关键字，之后执行使用 AFTER 关键字。BEFORE 用于数据验证和净化，AFTER 用于审计跟踪，将修改记录到另外一张表中。

INSERT 触发器包含一个名为 NEW 的虚拟表。

CREATE TRIGGER mytrigger AFTER INSERT ON mytable
FOR EACH ROW SELECT NEW.col into @result;

SELECT @result; -- 获取结果

DELETE 触发器包含一个名为 OLD 的虚拟表，并且是只读的。

UPDATE 触发器包含一个名为 NEW 和一个名为 OLD 的虚拟表，其中 NEW 是可以被修改的，而 OLD 是只读的。

MySQL 不允许在触发器中使用 CALL 语句，也就是不能调用存储过程。

二十. 事务管理

基本术语

• 事务（transaction）指一组 SQL 语句
• 回退（rollback）指撤销指定 SQL 语句的过程
• 提交（commit）指将未存储的 SQL 语句结果写入数据库表
• 保留点（savepoint）指事务处理中设置的临时占位符（placeholder），你可以对它发布回退（与回退整个事务处理不同）

MySQL 的事务提交默认是隐式提交，每执行一条语句就把这条语句当成一个事务进行提交。当出现 START TRANSACTION 语句时，会关闭隐式提交；当 COMMIT 或 ROLLBACK 语句执行后，事务会自动关闭，重新恢复隐式提交。

如果没有设置保留点，ROLLBACK 会回退到 START TRANSACTION 语句处；如果设置了保留点，并且在 ROLLBACK 中指定该保留点，则会回退到该保留点。

START TRANSACTION
// ...
SAVEPOINT delete1
// ...
ROLLBACK TO delete1
// ...
COMMIT