SQL语句种类
- DDL——数据定义语言
CREATE
:创建数据库和表等对象DROP
:删除数据库和表等对象ALTER
:修改数据库和表的结构
- DML ——数据操纵语言
SELECT
:查询INSERTL
:插入UPDATE
:更新DELETE
:删除
- DCL——数据控制语言
COMMIT
:确认对数据库中的数据进行的变更ROLLBACK
:取消数据库中的数据进行的变更GRANT
:赋予用户操作权限REVOKE
:取消用户操作权限
表的创建、删除与修改
-
数据库的创建 CREATE DATABASE
-
CREATE DATABASE <数据库名称>;
-
-
创建表 CREATE TABLE
-
CREATE TABLE <表名> (<列名1> <数据类型> <字段约束>, <列名2> <数据类型> <字段约束>, <列名3> <数据类型> <字段约束>, ... <该表的约束1>, <该表的约束2>, ... );
-
-
表的删除 DROP TABLE
-
DROP TABLE <表名>;
不可恢复!!!
-
-
表定义的更新 ALTER TABLE
-
添加列的ALTER TABLE语句
ALTER TABLE <表名> ADD COLUMN <列的定义>; -- EXAMPLE FOR DB2, MySQL, PostgreSQL ALTER TABLE Product ADD COLUMN product_name_pinyin VARCHAR(100) NOT NULL;
-
删除列的ALTER TABLE语句
ALTER TABLE <表名> DROP COLUMN <列名>; -- EXAMPLE FOR DB2, MySQL, PostgreSQL ALTER TABLE Product DROP COLUMN product_name_pinyin
-
向表中插入数据INSERT INTO语句
INSERT INTO <表名> VALUES (值1, 值2,....);
-
SELECT语句基础
列的查询
-
基本的
SELECT
语句-
查询表中部分列
-- EXAMPLE SELECT product_id, product_name, purchase_price FROM Product;
-
-
查询表中所有列
SELECT * FROM <列名>;
-
为列设定别名,
AS
关键字可以为列设置别名SELECT product_id AS id, product_name as "商品名称", product_price as "商品价格" FROM Product; -- 执行结果的列名会变为替代的列名
-
常数的查询
SELECT '商品' AS type, 38 AS number, '2020-02-02' AS date, proudct_id, product_name FROM Product; /* RESULT string | number | date |product_id| product_name| ----------------------------------------------------------- 商品 |38 |2020-02-02 |0001 |牙刷 商品 |38 |2020-02-02 |0002 |剃须刀 商品 |38 |2020-02-02 |0003 |纸巾 商品 |38 |2020-02-02 |0004 |洗脸盆 商品 |38 |2020-02-02 |0005 |拖把 商品 |38 |2020-02-02 |0006 |洗发水 */
-
查询去重 SELECT DISTINCT
SELECT DISTINCT product_type, date -- 可以对多列数据查询 FROM Product;
- 在使用DISTINCT关键字时,NULL也被视为一类数据,存在多条NULL时会被合并成一条
-
根据WHERE语句来选择记录
-
基本格式
SELECT <列名>,... FROM <表名> WHERE <条件表达式>; -- EXAMPLE SELECT product_name, product_type FROM Product WHERE product_type = '衣物';
-
SQL的运算符
算术运算符
-
SELECT product_name, sale_price, sale_price * 2 AS "sale_price_x2", -- 将sale_price以2倍的形式展现出来 FROM Product;
其它运算符与Python大体相同
-
特别注意
NULL
-
5 + NULL 3 * NULL NULL / 0 -- 以上结果均为NULL
-
所有包含NULL的运算,结果必为NULL
-
比较运算符
- 常规运算符
运算符 | 含义 |
---|---|
= | 和 / 相等 |
<> | 和 / 不相等 |
>= | 大于等于 |
<= | 小于等于 |
-
对字符串使用不等号时,使用的是和数字不同的比较规则,其按照字典顺序比较,如果同一位相同则向后延
-
不能对NULL使用比较运算符
-- 查找purchase_price字段为NULL的数据 SELECT product_name, purchase_price FROM Product WHERE purchase_price = NULL; -- 以上是错误❌作法,查询结果为空!!! SELECT product_name, purchase_price FROM Product WHERE purchase_price is NULL; -- 正确做法✔做法 -- SQL提供了专门判断是否为NULL的is NULL运算符
逻辑运算符
-
NOT运算符
-- 查找价格小于等于1000的商品 SELECT name, type, price FROM Product WHERE NOT price > 1000; -- 等价于 price <= 1000
-
AND运算符和OR运算符
AND
运算符在其两侧的查询条件都成立时整个查询条件才成立OR
运算符符合任意一个条件即整个查询条件成立
-- 查询商品类别为服装且价格大于500的商品 SELECT name, type, price FROM Product WHERE type = '服装' AND price > 500;
- 注意:
AND
运算符优先级高于OR
运算符
-- 查询商品类别为办公用品且时间为2020-08-28 或 2020-09-25的商品
SELECT name, type, price, dt
WHERE type = '办公用品'
AND dt = '2020-08-28'
OR dt = '2020-09-25';
-- 错误示例❌,AND优先于OR
SELECT name, type, price, dt
WHERE type = '办公用品'
AND (dt = '2020-08-28'
OR dt = '2020-09-25');
--正确示例✔,合理利用括号
聚合查询
聚合函数
函数名 | 功能 |
---|---|
COUNT() | 计算表中的行数 |
SUM() | 计算表中某一列的合计值 |
AVG() | 计算表中某一列的平均值 |
MAX() | 计算表中某一列的最大值 |
MIN() | 计算表中某一列的最小值 |
ROUND() | 保留xx位小数 |
注:只有在
SELECT
子句和HAVING
子句中才能使用聚合函数
-
计算表中数据的行数
SELECT COUNT(*) FROM Product; -- *为COUNT()聚合函数的参数
-
计算某一列的非空行数
SELECT COUNT(price) FROM Product; -- COUNT()函数不会把某一列的NULL所在行计算进行数里
-
计算合计值
SELECT SUM(sale_price), SUM(purchase_price) FROM Product; /* sum | sum ----------- 12345|23456 */
-
计算平均值
SELECT AVG(sale_price) FROM Product;
注意:
AVG()
函数对含NULL
值的列计算方式要视具体情况而定 -
计算最大最小值
SELECT MAX(regist_data), MIN(regist_data) FROM Product;
SUM
和AVG
函数只能对数值类型列使用,而MAX
和MIN
函数可以对任何类型的列使用 -
计算某一列的非重复行数
SELECT COUNT(DISTINCT type) FROM Product;
注意:
DISTINCT
关键字必须写在括号内,否则会先计算行数,再删除重复列
GROUP BY对表进行分组
基本语法:
使用
GROUP BY
子句进行汇总SELECT <列名1>, <列名2>, <列名3>, ... FROM <表名> GROUP BY <列名1>, <列名2>, <列名3>, ...;
注:
GROUP BY
语句一定要写在WHERE
语句之后
书写顺序:SELECT
–>FROM
–>WHERE
–>GROUP BY
在GROUP BY
子句中指定的列成为聚合键或者分组列
-
聚合键中包含NULL的情况
SELECT purchase_price, COUNT(*) FROM Product GROUP BY purchase_price; -- 聚合键包含NULL时,会单独列出NULL的数量
-
使用
WHERE
子句时GROUP BY
的执行结果SELECT purchase_price, COUNT(*) FROM Product WHERE product_type = '衣服' GROUP BY purchase -- 执行顺序:FROM-->WHERE-->GROUP BY-->SELECT-->HAVING
HAVING
子句——为聚合结果指定条件
SELECT <列名1>, <列名2>, <列名3>, ... FROM <表名> GROUP BY <列名1>, <列名2>, <列名3>, ... HAVING <分组结果对应条件>; -- 执行顺序:FROM-->WHERE-->GROUP BY-->SELECT-->HAVING
HAVING
子句中能包含的元素只有三种:
- 常数
- 聚合函数
GROUP BY
指定列名
相对于
HAVING
子句,某些条件更适合写在WHERE
子句中
HAVING
子句是用来指定"组"的条件WHERE
子句是用来指定“行”的条件
ORDER BY
子句——对查询结果进行排序
SELECT <列名1>, <列名2>, <列名3>, ... FROM <表名> ORDER BY <基准列1>, <基准列2>, ...
书写顺序 SELECT子句 FROM子句 WHERE子句 GROUP BY子句 HAVING子句 ORDER BY子句
INSERT
语句——数据的插入
基本格式:
INSERT INTO <表名> (列1, 列2, 列3...) VALUES (值1, 值2, 值3...);
-
插入默认值
在
CREATE TABLE
语句中设置**DEFAULT
约束**来设定默认值CREATE TABLE Product( product_id CHAR(4) NOT NULL , sale_price INTEGER DEFAULT 0, PRIMARY KEY (product_id));
通过显示方法插入默认值
INSERT INTO Product ('product_id', 'sale_price') VALUES ('qwer', DEFAULT);
通过隐式方法插入默认值(更推荐显示方法,一目了然)
-- 省略sale_price列 INSERT INTO Product ('product_id') VALUES ('qwer');
-
INSERT
…SELECT
语句——从其他表中复制数据INSERT INTO ProductCopy( product_id, product_name, product_type, sale_price) SELECT (product_id, product_name, product_type, sale_price) FROM Product;
DELETE
语句——数据的删除
DROP TABLE
语句会将表完全删除
DELETE
语句会保留表(容器),而删除表中所有数据