1、 数据库和SQL
1.1、基本概念：
数据库管理系统 DBMS
关系数据库 RDB
关系数据库管理系统 RDBMS
结构化查询语言 SQL
1.2、创建数据库
CREATE DATABASE
1.3、创建表
CREATE TABLE
(，
，
。
。
。
PRIMARY KEY (列名))；
1.4、删除表
DROP TABLE
1.5、表定义的更新
添加列ALTER TABLE ADD COLUMN
删除列ALTER TABLE DROP COLUMN
1.6、向表中插入数据
START TRANSACTION；
INSERT INTO
VALUES(VALUE1，VALUE2，VALUE3)；
INSERT INTO
VALUES(VALUE1，VALUE2，VALUE3)；
COMMIT；
1.7、变更表名
RENAME TABLE
to
2、查询基础
2.1、在select 语句中使用DISTINCT 可以删除重复行
DISTINCT 关键字智能用在第一个列名之前
2.2、根据where语句来选择记录
Where 子句中可以指定“某一列的值和这个字符串相等”或者“某一列的值大于这个数字”等条件。
Select
FROM
WHERE
通过WHERE子句查询出符合条件的记录，然后再选出SELECT语句指定的列
WHERE子句紧跟在FROM子句之后
2.3、算术运算符和比较运算符
2.3.1算术运算符 + - * /
包含null值的计算结果都是null
2.3.2比较运算符
=
<>
>=
>
<=
<
不能对NULL使用比较运算符，要用IS NULL 运算符
2.4、逻辑运算符
NOT
AND
OR
真值
TRUE
FALSE
UNKNOWN
3、 聚合与排序
3.1、
对表进行聚合查询
聚合函数
COUNT 计算个数
SUM 数值求和
AVG 数值平均值
MAX 数值最大值
MIN 数值最小值
聚合函数(除了COUNT外)会把NULL值排除在外
COUNT(*)会得到包含NULL的数据行数
COUNT()会得到不含NULL的数据行数
想要计算种类时，可以在COUNT函数中使用DISTINCT
COUNT(DISTINCT )
3.2、
对表进行分组
GROUP BY
GROUP BY子句中指定的列称为聚合键或者分组列，可以通过逗号指定多列。
书写顺序
SELECT →FROM →WHERE →GROUP BY
常见错误1
把聚合键之外的列名书写在select 子句之中
常见错误2
在where子句中使用聚合函数
只有 select 子句、HAVING子句以及ORDER BY 子句能使用聚合函数
3.3、为聚合结果指定条件
HAVING
HAVING子句写在GROUP BY子句之后
SELECT →FROM →WHERE →GROUP BY →HAVING
3.4、
对查询结果进行排序
ORDER BY,
ORDER BY子句写在SELECT语句的末尾
ORDER BY 子句中的列名称为排序键
书写顺序
SELECT →FROM →WHERE →GROUP
BY →HAVING →ORDER BY
指定升序或降序
DESC 降序 ASC 升序
默认升序
排序键中包含null时，会在开头或末尾进行汇总
执行顺序
FROM →WHERE →GROUP BY →HAVING
→SELECT→ORDER BY
SELECT子句执行顺序在GROUP BY 之后ORDER BY 之前因此 GROUP BY子句中不能识别SELECT子句中定义的别名，而ORDER BY 子句可以识别别名
4、数据更新
4.1、数据的插入(INSERT)
INSERT INTO (列1，列2，列3)VALUES(值1，值2，值3)；
插入默认值
在创建表的时候在列的约束部分设置默认值
DEFAULT<0>
CREATE TABLE Product
(sale_price INTEGER DEFAULT 0，
Primary KEY())；
INSERT INTO Product values (‘0001’,’擦菜板’，‘厨房用具’，default，790，‘2009-04-28’)；
从其他表中复制数据
INSERT…SELECT 语句
INSERT INTO Productcopy SELECTproduct_id,
product_name, regist_date
FROM product;
4.2、数据删除
DROP TABLE 删除表
DELETE FROM TABLE
WHERE ；
删除表中的数据/行
4.3、数据的更新
UPDATE
SET =
;
WHERE ；
4.4、
事务
事务就是需要在同一个处理单元中执行的一系列更新处理的集合
事务的语法：
START TRANSACTION；
DML语句1
DML语句2
。
。
。
COMMIT(ROLLBACK)；
5、 复杂查询
5.1、视图
视图其实就是一张表但是这张表并不会保存到储存设备中，实际上保存的是SELECT语句，我们从视图中读取数据时，视图会在内部执行该SELECT语句并创建出一张临时表。
表中存储的是数据，而视图中保存的是从表中取出数据所使用的SELECT语句。
应该将经常使用的SELECT语句做成视图。
创建视图
CREAT VIEW 视图名称(列1，列2)
AS
使用视图
SELECT 列1，列2
FROM 视图名称;
删除视图
DROP VIEW 视图名称；
5.2、子查询
子查询实际就是一张一次性的视图。
子查询是将用来定义视图的SELECT语句直接用于FROM子句当中。
为子查询设定名称时需要使用AS 关键字，也可以省略
5.2.1 标量子查询
标量子查询就是返回单一值的子查询
标量子查询几乎所有地方都可以使用。
标量子查询的返回值可以用在=或者>
5.2.2 关联子查询
在细分的组内进行比较时，需要使用关联子查询。
关联子查询是在子查询中添加where子句的条件，将数据限制在同一个组内(然后进行比较)。
关联子查询和GROUP BY子句一样可以对集合进行切分。
结合条件一定要写在子查询中。
SELECT product_type, product_name,
sale_price
FROM Product AS P1
WHERE sale_price >(SELECT
AVG(sale_price)
FROM Product AS P2
WHERE P1.product_type=P2.product_type
GROUP BY product_type);
关联子查询的关联名称(P2)仅在子查询中有效。
子查询结束时只会留下执行结果，P2表已经不存在了。在子查询以外使用P2会返回“不存在使用该名称的表”这样的错误。
6、 函数、谓词、CASE表达式
6.1、函数
6.1.1 算术函数
+-*/
ABS(数值)
MOD(被除数，除数)
MOD(7，3)返回1
ROUND(对象数值，保留小数位数)
6.1.2 字符串函数
LENGTH(字符串)
LOWER(字符串)
UPPER(字符串)
REPLACE(对象字符串，替换前的字符串，替换后的字符串)
SUBSTRING(对象字符串 FROM 起始位置 FOR 截取长度)
6.1.3 日期函数
CURRENT_DATE 返回当前日期
CURRENT_TIME 返回当前时间
CURRENT_TIMESTAMP 返回当前时间和日期
EXTRACT(日期元素 FROM 日期)
EXTRACT(YEAR FROM CURRENT_TIMESTAMP)AS year；
DATE_FORMAT (DATE, FORMAT)
DATE_FORMAT(NOW(), ‘%m-%d-%Y’)
DATE_SUB()函数从日期减去指定的时间间隔
CURDATE()返回当前日期
DAY() 返回日期值INTEGER
MONTH() 返回月份值INTEGER
YEAR()返回年值INTEGER
昨天
Date_format(pay_time,’%Y-%m-%d’)>=date_sub(curdate(),interval
1day)
30天前
Date_format(pay_time,’%Y-%m-%d’)>=date_sub(curdate(),interval
30 day)
上一个月
Month(DATE _format(pay_time,’%Y-%m-%d’))=month(now())-1
6.1.4 转换函数
CAST(转换前的值 AS 想要转换的数据类型)
CAST(‘0001’ AS
INTEGER)
COALESCE(数据1，数据2，数据3)返回左侧开始第1个不是null的值。
COALESCE(null，1)
返回1
IFNULL(null，1)返回1
IFNULL(A，B)如果A是null时返回B，如果A不是null返回A
6.2 谓词
LIKE ‘%ddd%’ 模糊查询
BETWEEN AND 范围查询
BETWEEN 100 AND 1000
IS NULL 判断是否为NULL
IN(值1，值2)
IN (子查询)
EXIST(关联子查询)
作为EXIST参数的子查询中经常会使用SELECT *
6.3 CASE表达式
CASE WHEN THEN
WHEN THEN
WHEN THEN
---------
ELSE
END
CASE 表达式可以用在任意位置，常用在SELECT语句中。
7、 集合运算
7.1、表的加减法
集合运算包括：表的加法UNION(并集)，选取公共部分INTERSECT(交集)，表的减法EXCEPT(差集)等。
集合运算是行方向上的运算，可以去除重复行，如果希望保留重复行就需要使用ALL关键字。
注意事项：
①
列数必须相同
②
列的类型必须一致
③
可以使用任何SELECT语但OEDER BY子句只能在最后使用一次
7.2、联结(以列为单位对表进行联结)
联结是列方向上的运算。
INNER JOIN(等值联结)只返回两个表中联结字段相等的行
LEFT JOIN(左联结)返回包括左表中的所有记录和右表中联结字段相等的字段。
RIGHT JOIN(右联结)返回包括右表中的所有记录和左表中联结字段相等的字段。
使用联结时SELECT子句中的列需要按照“.”的格式来书写全部列。
8、 练习
分组求最值
例1：求每类商品中最贵的商品
select *
FROM product P1
WHERE
sale_price =(SELECT MAX(sale_price)FROM product P2
where
P1.product_type=P2.product_type)
ORDER BY product_type, sale_price DESC;
例2：求每类商品中价格最贵的2种商品
select *
FROM product
P1
WHERE
(SELECT count(*) FROM product P2
where
P1.product_type=P2.product_type AND P2.sale_price>P1.sale_price)<2
ORDER BY product_type, sale_price DESC;
参考书
SQL基础教程(第2版)MICK
171
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
