【mysql必知必会】知识点部分汇总

1 数据检索

1.1 检索

检索单个列：SELECT 字段 FROM 表名；
检索多个列：SELECT 字段1，字段2，…，字段n FROM 表名；
检索所有列：SELECT * FROM 表名；
检索不同的值：SELECT DISTINCT 字段 FROM 表名；
① SELECT 字段 FROM 表名 LIMIT 行数；
② SELECT 字段 FROM 表名 LIMIT 行数1 OFFSET 行数2；
③ SELECT 字段 FROM 表名 LIMIT 行数2，行数1；

1.2 排序

按单个列排序：SELECT 字段 FROM 表 ORDER BY 字段；
按多个列排序：SELECT 字段1，字段2，…，字段n FROM 表 ORDER BY 字段1，字段2，…，字段n；
按列位置排序：SELECT 字段1，字段2，…，字段n FROM 表 ORDER BY 2，3；
①升序（A到Z）：SELECT 字段 FROM 表 ORDER BY 字段 ASC；
②降序（Z到A）：SELECT 字段 FROM 表 ORDER BY 字段 DESC；
③多个列排序：SELECT 字段1，字段2，…，字段n FROM 表 ORDER BY 字段1 DESC，字段2；

1.3 过滤

检查单个值：SELECT 字段 FROM 表 WHERE 字段 = 值；
不匹配检查：SELECT 字段 FROM 表 WHERE 字段 <> 值；
‘不等于’操作符不同的DBMS可能不同：<>或！=
范围值检查：SELECT 字段 FROM 表 WHERE 字段 BETWEEN 值1 AND 值2；
空值检查：SELECT 字段 FROM 表 WHERE 字段 IS NULL；

1.4 高级过滤

AND 操作符：SELECT 字段 FROM 表 WHERE 过滤子句1 AND 过滤子句2 AND …；
OR 操作符：SELECT 字段 FROM 表 WHERE 过滤子句1 OR 过滤子句2 OR …；
IN 操作符：①SELECT 字段 FROM 表 WHERE 字段 IN （值1，值2，…，值n）；
② SELECT 字段 FROM 表 WHERE 字段 IN （SELECT语句）；
NOT 操作符：SELECT 字段 FROM 表 WHERE NOT 过滤子句 ；

1.5 通配符过滤

① 百分号（%）通配符：SELECT 字段 FROM 表 WHERE 字段 LIKE ‘xxxx%’ ；
② 下划线（）通配符：SELECT 字段 FROM 表 WHERE 字段 LIKE 'xxxx’ ；
③ 方括号（[ ]）通配符：SELECT 字段 FROM 表 WHERE 字段 LIKE ‘[xy]%’ ；

1.6 计算字段

SELECT 字段1 + 字段2 +‘字符’ FROM 表名；
删除空格：SELECT RTRIM(字段1) + RTRIM(字段2) +‘字符’ FROM 表名；
RTRIM( )函数：去掉值右边所有空格
LTRIM( )函数：去掉值左边所有空格
TRIM( )函数：去掉值左右两边的空格
使用别名：SELECT RTRIM(字段1) + RTRIM(字段2) +‘字符’ AS 别名 FROM 表名；
SELECT 字段1 *字段2 FROM 表名；

1.7 函数

几个常用函数
① 提取字符串的组成部分SUBSTRING( )
②数据类型转换CONVERT( )
③取当前日期CURDATE（ ）
常用的文本处理函数
LEFT( )（或使用子字符串函数）：返回字符串左边的字符
RIGHT( )（或使用子字符串函数）：返回字符串右边的字符
LENGTH( )（或使DATALENGTH( )\LEN( )）：返回字符串的长度
LOWER( )（Access使用LCASE( )）：将字符串转换为小写
UPPER( )（Access使用UCASE( )）：将字符串转换为大写
LTRIM( ) ：去掉字符串左边的空格
RTRIM( ) ：去掉字符串右边的空格
SOUNDEX( ) ： 返回字符串的SOUNDEX值（返回读音相似的字符）
数值处理函数
ABS( )：返回一个数的绝对值
EXP( )：返回一个数的指数值
SQRT( )：返回一个数的平方根
PI( ) ：返回圆周率
COS( )：返回一个角度的余弦
SIN( ) ：返回一个角度的正弦
TAN( ) ： 返回一个角度的正切

1.8 汇总

聚集函数
AVG( )：返回某列的平均值
COUNT( ) ：返回某列的行数
MAX( ) ：返回某列的最大值
MIN( ) ：返回某列的最小值
SUM( ) ：返回某列值之和
AVG（）：函数只能用来计算单个列的平均值，为计算多个列必须使用多个AVG()函数
COUNT() ：对表中行的数目进行计数，不管表列中是NALL还是非空，COUNT(column)对特定列进行计数，忽略NULL值
MAX() ：用于文本时返回该列排序的最后一行
MIN() ：用于文本时返回该列排序的第一行
聚集不同值
①ALL：对所有行执行计算（ALL为默认可以不指定）
②DISTINCT：只包含不同的值，不能用于COUNT()*
SELECT AVG(DISTINCT 字段） AS 别名 FROM 表 ；
组合聚集函数
SELECT AVG(字段） AS 别名1，MIN(字段) AS 别名2，COUNT(*) AS 别名3 FROM 表 ；

1.9 分组

创建分组：SELECT 字段，COUNT() AS num FROM 表 GROUP BY 字段；
过滤分组：SELECT 字段1，COUNT() AS num FROM 表 WHERE 字段2 > 值1 GROUP BY 字段1 HAVING COUNT(*)>值2；

SELECT子句顺序：SELECT，FROM ，WHERE，HAVING ，GROUP BY，ORDER BY

1.10 子查询

SELECT 字段1 FROM 表1 WHERE 字段2 IN （SELECT 字段3 FROM 表2 WHERE 字段4=‘字符串’）；
SELECT 字段1，（SELECT 字段3 FROM 表2 WHERE 表1.字段4=表2.字段4） AS 字段2 FROM 表；
完全限定列名：表明.字段

1.11 联结表

创建联结（内联结\等值联结）：
①SELECT 字段1，字段2，字段3 FROM 表1，表2 WHERE 表1.字段4=表2.字段4；
②SELECT 字段1，字段2，字段3 FROM 表1 INNER JOIN 表2 ON 表1.字段4=表2.字段4；

1.12 高级联结

使用别名：
SELECT 字段1，字段2，字段3 FROM 表1 AS 别名1 ，表2 AS 别名2，表3 AS 别名3 WHERE 别名1.段4= 别名2.字段4 AND 别名2.字段5=别名3.字段5 ；
自联结：SELECT 别名1.字段1，别1,.字段2，别名1.字段3 FROM 表1 AS 别名1，表1 AS 别名2 WHERE 别名1.字段4=别名2.字段4 AND 别名2.字段5=‘值’；
外联结：SELECT 表1.字段1，表2.字段2 FROM 表1 LEFT OUTER JOIN 表2 ON 表1.字段3=表2.字段3;
全外联结：SELECT 表1.字段1，表2.字段2 FROM 表1 FULL OUTER JOIN 表2 ON 表1.字段3=表2.字段3;
带聚集函数的联结：SELECT 表1.字段1, COUNT(表2.字段2) AS 别名 FROM 表1 INNER JOIN 表2 ON 表1.字段1 = 表2.字段2 GROUP BY 表1.字段1;

1.13 组合查询

使用UNION

SELECT ~~~ NUION SELECT~~~

2 插入数据

插入完整的行
INSERT INTO 表名 VALUES(‘值’，‘值’，…，‘值’，null，null)；
INSERT INTO 表名(字段1，字段2，…，字段n) VALUES(‘值’，‘值’，…，‘值’，null，null)；
插入检索出的数据
INSERT INTO 表1（字段1，字段2，…，字段n） SELECT 字段1，字段2，…，字段n FROM 表2；
从一个表复制到另一个表
CREATE TABLE 表1 AS SELECT * FROM 表2；

3 更新和删除数据

更新数据：UPDATE 表名 SET 字段1=‘值’，字段2=‘值2’ WHERE ~；
删除数据：DELETE FROM 表名 WHERE 字段=‘值’；
删除所有行使用 TRUNCATE TABLE 语句，速度更快

4 创建和操纵表

创建表（GREATE TABLE）
CREATE TABLE 表名

(

列名 数据类型 NOT NULL,

…

列名 数据类型 NULL

);

指定默认值
CREATE TABLE 表名

(

列名 数据类型 NOT NULL DEFAULT 1,

…

列名 数据类型 NULL

);

更新表（ALTER TABLE）
增加列：ALTER TABLE 表名 ADD 字段 数据类型 ;
删除列：ALTER TABLE 表名 DROP COLUMN 字段 ;
删除表（DROP TABLE）
DROP TABLE 表名；
重命名表
RENAME

5 使用视图

创建视图：GREATE VIEW 视图名 AS SELECT ~~~~
利用视图简化复杂的联结：
CREATE VIEW ProductCustomers AS
SELECT cust_name, cust_contact, prod_id
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
AND OrderItems.order_num = Orders.order_num;
利用视图重新格式化检索出的数据：
CREATE VIEW VendorLocations AS
SELECT RTRIM(vend_name) + ’ (’ + RTRIM(vend_country) + ‘)’
AS vend_title
FROM Vendors;
利用视图过滤不想要的数据：
CREATE VIEW CustomerEMailList AS
SELECT cust_id, cust_name, cust_email
FROM Customers
WHERE cust_email IS NOT NULL;
使用视图与计算字段：
CREATE VIEW OrderItemsExpanded AS
SELECT order_num,
prod_id,
quantity,
item_price,
quantity*item_price AS expanded_price
FROM OrderItems;

6 使用存储过程

创建存储过程
CREATE PROCEDURE MailingListCount (
ListCount OUT INTEGER
)
IS
v_rows INTEGER;
BEGIN
SELECT COUNT(*) INTO v_rows
FROM Customers
WHERE NOT cust_email IS NULL;
ListCount := v_rows;
END;
LISTCOUNT:参数

7 管理事务处理

事务处理用来管理INSERT\UPDATE和DELETE语句
控制事务处理
START TRANSACTION
…

使用ROLLBACK
DELETE FROM Orders;
ROLLBACK;
ROLLBACK命令用来回退（撤销）SQL语句
使用COMMIT
一般的SQL语句都是针对数据库表直接执行和编写—隐式提交
在事务处理块中，提交不会隐式进行，需使用COMMIT语句
创建占位符
SAVEPOINT delete1;
回退到保留点
ROLLBACK TO delete1;

8 使用游标

创建游标
DECLARE 游标名 CURSOR FOR SELECT 字段 FROM 表名；
使用游标
OPEN CURSOR 游标名
关闭游标
CLOSE 游标名

9 高级SQL特性

创建主键
CREATE TABLE 表名 （字段名 数据类型 NOT NULL PRIMARY KEY）;
ALTER TABLE 表名 ADD CONSTRAINT PRIMARY KEY （字段名）；
创建外键
CREATE TABLE 表1 （字段1 数据类型 NOT NULL REFERENCES 表2（字段2））；
ALTER TABLE 表1 ADD CONSTRAINT FOREIGN KEY (字段1) REFERENCES 表2 (字段2)；
③唯一约束
④检查约束
CREATE TABLE 表1 （字段1 数据类型 NOT NULL CHECK(字段1>0)）；
索引：
用来排序数据以加快搜索和排序操作的速度
创建索引：CREATE INDEX 索引名 ON 表名 (字段名);
触发器：
是特殊的存储过程，在特定的数据库活动发生时自动执行