先圣博客
0%

Mosh教程SQL：重学SQL3

发表于 更新于 分类于
数据库学习笔记

插入单行：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# 新增 INSERT INTO  表名 (列名) values (列值)
# 如果表名后面没有列名则默认全部列内容
INSERT INTO `customers` (
	`first_name`,
	`last_name`,
	`birth_date`,
	`address`,
	`city`,
	`state`)
VALUES (
	'John',
	'Smith',
	'1990-01-01',
	'address',
	'city',
	'CA');
	
# 查询结果
SELECT * 
FROM `customers`;

插入多行：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 插入多行
INSERT INTO `shippers` (`name`)
VALUES  ('Shipper1'),
	('Shipper2'),
	('Shipper3');
	
# 查询结果
SELECT * FROM `shippers`;

# 练习：
INSERT INTO `products` (`name`,`quantity_in_stock`,`unit_price`)
VALUES  ('Product1',10,1.95),
	('Product2',11,1.95),
	('Product3',12,1.95);
	
SELECT * FROM `products`;

插入分层行：

1
2
3
4
5
6
7
8
# 我们需要往两个关联的表插入数据
# LAST_INSERT_ID() 可以获取最新的插入ID
INSERT INTO `orders` (`customer_id`,`order_date`,`status`)
VALUES (1,'2019-01-02',1);

INSERT INTO `order_items`
VALUES  (LAST_INSERT_ID(), 1, 1, 2.95),
	(LAST_INSERT_ID(), 2, 1, 3.95);

创建表复制：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
# CREATE TABLE 可以快速的复制表，但是会缺少主键和自增等属性
CREATE TABLE orders_archived AS 
SELECT * FROM `orders`;

# 也可以使用insert新增
INSERT INTO `orders_archived`
SELECT * FROM `orders` 
WHERE `order_date` < '2019-01-01';

# 练习：
USE `sql_invoicing`;
CREATE TABLE invoices_archived AS
SELECT 
	i.`invoice_id`,
	i.`number`,
	c.`name`,
	i.`invoice_total`,
	i.`payment_total`,
	i.`invoice_date`,
	i.`due_date`,
	i.`payment_date`
FROM `invoices` i
LEFT JOIN `clients` c
	ON i.`client_id` = c.`client_id`
WHERE `payment_date` IS NOT NULL
ORDER BY `invoice_id`;

更新单行：

1
2
3
4
# 更新单行
UPDATE `invoices`
SET `payment_total` = 10, `payment_date` = '2019-03-01'
WHERE `invoice_id` =1;