运行环境: postgreSQL。

第一章:数据库和 SQL

第二章:查询基础

SQL 语句分类

  1. DDL (Data Definition Language,数据定义语言) 用来创建或者删除存储数据用的数据库以及数据库中的表等对象。

    • CREATE :创建数据库和表等对象 DROP;
    • 删除数据库和表等对象 ALTER;
    • 修改数据库和表等对象的结构;
  2. DML(Data Manipulation Language,数据操纵语言) 用来查询或者变更表中的记录。

    • SELECT :查询表中的数据 INSERT :向表中插入新数据;

    • UPDATE :更新表中的数据 DELETE :删除表中的数据;

  3. DCL(Data Control Language,数据控制语言) 用来确认或者取消对数据库中的数据进行的变更。除此之外,还可以对 RDBMS 的用户是否有权限 操作数据库中的对象(数据库表等)进行设定。

    • COMMIT : 确认对数据库中的数据进行的变更;
    • ROLLBACK :取消对数据库中的数据进行的变更;
    • GRANT : 赋予用户操作权限;
    • REVOKE:取消用户的操作权限;

数据库操作

数据库创建

1
CREATE DATABASE <数据库名称>;

表操作

表的创建

1
2
3
4
5
6
7
8
9
CREATE TABLE <表名>
(< 列名 1> < 数据类型 > < 该列所需约束 > 
< 列名 2> < 数据类型 > < 该列所需约束 >  
< 列名 3> < 数据类型 > < 该列所需约束 > 
< 列名 4> < 数据类型 > < 该列所需约束 > 
						.
 						.
 						.
< 该表的约束 1>  < 该表的约束 2> ,……)

示例

1
2
3
4
5
6
7
CREATE TABLE Product 
(product_id 	CHAR(4) NOT NULL, 
 product_name VARCHAR(100) NOT NULL, 
 product_type VARCHAR(32) NOT NULL, 
 sale_price INTEGER , 
 purchase_price INTEGER , 
 regist_date DATE , PRIMARY KEY(product_id));

CHAR 是定长字符串,即存储字符串长度不足时以半角空格补足空余字符,而相对的 VARCHAR 是指可变长字符串。

PRIMARY KEY(product_id) 指将 product_id 设置为主键。

表的删除

1
DROP TABLE <表名>;

表的更新

添加列

1
ALTER TABLE <表名> ADD COLUMN <列的定义>;

示例:表 Product 添加列 product_name_pinyin

1
ALTER TABLE Product ADD COLUMN product_name_pinyin VARCHAR(100);

删除列

1
ALTER TABLE <表名> DROP COLUMN <列名> 

示例:表 Product 删除列 product_name_pinyin

1
ALTER TABLE Product DROP COLUMN product_name_pinyin;

向表中插入数据

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
BEGIN TRANSACTION;
/* Mysql */
/* START TRANSACTION; */
/* 在Oracle和DB2 不需要 BEGIN */

INSERT INTO Product VALUES ('0001', 'T 恤衫', '衣服', 1000, 500, '2009-09-20'); 
INSERT INTO Product VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11'); 
INSERT INTO Product VALUES ('0003', '运动 T 恤', '衣服', 4000, 2800, NULL); 
INSERT INTO Product VALUES ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20'); 
INSERT INTO Product VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15'); 
INSERT INTO Product VALUES ('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20'); 
INSERT INTO Product VALUES ('0007', '擦菜板', '厨房用具', 880, 790, '2008-04-28'); 
INSERT INTO Product VALUES ('0008', '圆珠笔', '办公用品', 100, NULL,'2009-11-11');

COMMIT;

表的更名

不同数据库提供的 RENAME 指令用法各异

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
/* Oracle PostgreSQL */
ALTER TABLE Poduct RENAME TO Product;

/* DB2 */
RENAME TABLE Poduct TO Product;

/* SQL Server */
sp_rename 'Poduct', 'Product';

/* MySQL */
RENAME TABLE Poduct to Product;

查询基础

SELECT

基本 SELECT 语句

1
SELECT <列名>, ...... FROM <表名>

示例:从 Product 表查询 product_id,product_name,purchase_price 三列, 查询全部列可用 * 替代

1
2
SELECT product_id,product_name,purchase_price FROM Product;
SELECT * FROM Product;

使用 AS 关键字为列设置输出别名, 别名支持使用中文,使用时需要双引号(”“)括起来

1
2
3
4
5
6
7
8
9
SELECT product_id 		AS id,
			 product_name 	AS name,
			 purchase_price AS price
	FROM Product;
	
SELECT product_id 		AS "商品编号",
			 product_name 	AS "商品名称",
			 purchase_price AS "进货单价"
	FROM Product;

常数查询

1
2
3
SELECT '商品' AS 	string, 38 AS "商品名称", '2019-02-24' AS date,
			 product_id, product_name
	FROM Product;

使用 DISTINCT 删除重复行, NULL 也被视为一类数据

1
2
3
SELECT DISTINCT product_type FROM Product;
/* 多列进行组合,将重复数据删除 */
SELECT DISTINCT product_type, regist_date FROM Product;

WHERE

SELECT 语句中使用 WHERE 进行条件匹配

首先通过 WHERE 子句查询出符合指定条件的记录,然后再选取出 SELECT 语句指定的列

1
SELECT < 列名 >, ...... FROM < 表名 > WHERE < 条件表达式 >;

示例:选取 product_type 为”衣服”的记录

1
2
SELECT product_name, product_type FROM Product 
	WHERE product_type = '衣服';

算数运算符

SELECT 语句支持算数表达式

示例:将各个商品单价的 2倍 以 “sale_price_x2” 列展示出来

注意:所有包含 NULL 的计算,结果肯定是 NULL

1
2
SELECT product_name, sale_price, sale_price * 2 AS "sale_price_x2"
	FROM Product;

比较运算符

WHERE 语句通过比较运算符组合条件表达式

示例:选出 sale_price 为 500 的记录, 使用 <> 选出不为 500 的记录, 使用 < 选出日期在 2009-09-27 之前的记录

1
2
3
SELECT product_name, product_type FROM Product WHERE sale_price = 500;
SELECT product_name, product_type FROM Product WHERE sale_price <> 500;
SELECT product_name, product_type, regist_date FROM Product WHERE regist_date < '2009-09-27';

结合运算表达式对结果进行比较匹配

1
2
SELECT product_name, sale_price, purchase_price FROM Product 
	WHERE sale_price - purchase_price >= 500;

注意:字符串比较是按照字典顺序进行比较,以相同字符开头的单词比不同字符开头 的单词更相近

NULL 不能使用比较运算符,标准SQL 提供 IS NULL 判断是否为 NULL,与其相对的,使用 IS NOT NULL 表示非 NULL 的记录

1
2
3
4
SELECT product_name, purchase_price FROM Product 
	WHERE purchase_price IS NULL;
SELECT product_name, purchase_price FROM Product 
	WHERE purchase_price IS NOT NULL;

逻辑运算符

NOT 运算符 表示否定条件,示例:选出.sale_price 不大于等于 1000 的记录

1
2
3
SELECT product_name, product_type, sale_price
	FROM Product
WHERE NOT sale_price >= 1000;

AND(逻辑积) OR(逻辑和) 运算符 表示对多个条件进行与或组合

示例:

1
2
3
4
5
6
--AND 在其两侧的查询条件都成立时整个查询条件才成立
SELECT product_name, purchase_price FROM Product 
	WHERE product_type = '厨房用具' AND sale_price >= 3000;
--OR 在其两侧的查询条件有一个成立时整个查询条件都成立
SELECT product_name, purchase_price FROM Product 
	WHERE product_type = '厨房用具' OR sale_price >= 3000;

SQL 同样支持使用 () 提高运算的优先级

1
2
3
SELECT product_name, product_type, regist_date FROM Product 
	WHERE product_type = '办公用品' 
	AND ( regist_date = '2009-09-11' OR regist_date = '2009-09-20');

NULL 不能使用逻辑运算符,标准SQL 提供 不确定(UNKNOWN) ,即 SQL 的逻辑运算为三值逻辑