《SQL必知必会》学习总结

总结

以整个DBMS操作流程为主线，总结全部已经学习的知识。

创建表格（17课）

创建表：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
USE mustknown;

-- ----------------------
-- Create Customers table(注释)
-- ----------------------
CREATE TABLE Customers
(
  cust_id      char(10)  NOT NULL ,
  cust_name    char(50)  NOT NULL ,
  cust_address char(50)  NULL ,
  cust_city    char(50)  NULL ,
  cust_state   char(5)   NULL ,
  cust_zip     char(10)  NULL ,
  cust_country char(50)  NULL ,
  cust_contact char(50)  NULL ,
  cust_email   char(255) NULL 
);

定义主键:

1
2
3
4
5
6
7
8
-- -------------------
-- Define primary keys
-- -------------------
ALTER TABLE Customers ADD PRIMARY KEY (cust_id);
ALTER TABLE OrderItems ADD PRIMARY KEY (order_num, order_item);
ALTER TABLE Orders ADD PRIMARY KEY (order_num);
ALTER TABLE Products ADD PRIMARY KEY (prod_id);
ALTER TABLE Vendors ADD PRIMARY KEY (vend_id);

定义外键：

1
2
3
4
5
6
7
-- -------------------
-- Define foreign keys
-- -------------------
ALTER TABLE OrderItems ADD CONSTRAINT FK_OrderItems_Orders FOREIGN KEY (order_num) REFERENCES Orders (order_num);
ALTER TABLE OrderItems ADD CONSTRAINT FK_OrderItems_Products FOREIGN KEY (prod_id) REFERENCES Products (prod_id);
ALTER TABLE Orders ADD CONSTRAINT FK_Orders_Customers FOREIGN KEY (cust_id) REFERENCES Customers (cust_id);
ALTER TABLE Products ADD CONSTRAINT FK_Products_Vendors FOREIGN KEY (vend_id) REFERENCES Vendors (vend_id);

删除表：

1
DROP table customers:

插入、更新和删除数据（15、16课）

插入数据：

1
2
3
4
5
6
7
8
9
10
11
12
13
-- ---------------------
-- Populate Orders table
-- ---------------------
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20005, '2020-05-01', '1000000001');
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20006, '2020-01-12', '1000000003');
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20007, '2020-01-30', '1000000004');
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20008, '2020-02-03', '1000000005');
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20009, '2020-02-08', '1000000001');

更新数据：

1
2
3
4
update customers set cust_email = 'johnkou@github.io'
where cust_id = '1000000005';
update customers set cust_email = 'johnkou@github.io', cust_state = 'CH'
where cust_id = '1000000005';

删除数据：

1
2
3
DELETE FROM customers where cust_id = '1000000005';
DELETE FROM customers where cust_id = '1000000006';
DELETE FROM customers where cust_name = 'Jon';

初级检索数据（2、3课）

完全检索：

1
2
3
SELECT * from customers;
SELECT * from products;
SELECT * from vendors;

限制检索：

1
SELECT cust_id, cust_name, cust_state from customers;

限定检索出的数据不可重复：

1
2
SELECT DISTINCT cust_id
customers;

限制检索出的数据的数量：

1
2
SELECT cust_id from customers limit 3 offset 2;
SELECT cust_id from customers limit 2, 3;

添加注释：

1
2
-- 注释
/*注释*/

对检索出的数据进行排序：

1
2
3
SELECT cust_id, cust_name from customers order by cust_id;
SELECT cust_id, cust_name from customers order by cust_id, cust_name;
SELECT cust_id, cust_name from customers order by cust_id desc, cust_name;

过滤数据（4、5、6课）

初级数据过滤：

1
2
3
4
5
6
7
8
9
10
11
SELECT cust_name from customers where cust_id = '1000000003';
SELECT cust_id from customers where cust_name <> 'Jon';
SELECT cust_id from customers where cust_name != 'Jon';
SELECT vend_name from vendors where vend_price < 10;
SELECT vend_name from vendors where vend_price <= 10;
SELECT vend_name from vendors where vend_price !> 10;
SELECT cust_id from orders where order_price > 10;
SELECT cust_id from orders where order_price >= 10;
SELECT cust_id from orders where order_price !< 10;
SELECT cust_id from orders where order_price BETWEEN 55 AND 10;
SELECT cust_id from orders where order_price IS NULL;

高级数据过滤：

1
2
3
4
5
6
7
SELECT cust_id from orders where order_price > 10 AND cust_id <> 'Jon';
SELECT cust_name from customers where cust_id = '10000003' OR cust_id = '100000004';
SELECT cust_name from customers where cust_id IN ('10000003' ,'100000004');
SELECT cust_name from customers where NOT cust_id = '100000003';
------------------------
-- 计算顺序：AND 优先于 OR
-------------------------

特殊数据过滤（通配符）：

1
2
3
4
5
6
SELECT prod_id, prod_name from products where prod_name LIKE 'FISH%';--以fish开头的任意数据
SELECT prod_id, prod_name from products where prod_name LIKE '%FISH';--以fish结尾的任意数据
SELECT prod_id, prod_name from products where prod_name like '%fish%';--任意位置包含fish的数据
SELECT prod_id, prod_name from products where prod_name LIKE 'F%Y';--以F开头，以Y结尾的数据
SELECT prod_id, prod_name from products where prod_name LIKE '_ inch teddy bear'--只代表一个字符串
SELECT prod_id, prod_name from products where prod_name like '[JM]%';--任意以J或者M开头的数据

高级检索数据（7、8、9、10课）

跨表操作数据（11、12、13、14课）

今天先写这些，明天继续7-14课。