1.列属性
关于数据类型:
- INT(只接受整数值)
- VARCHAR(括号里标注了50,也就是说这列最多可以有50个字符)
- CHAR(如果括号里标注了50,而顾客名只有5个字符,MySQL会再插入45个空格符填满这列,这样很浪费空间)所以一般最好用VARCHAR来存储字符串和文本值
- PK:主键的缩写
- NN:非空值,决定了该列是否可以写空值,✔代表不可以有空值
- AI:自动递增,通常被用在主列键,加一条记录,主键列的id自动加一
- Default/Expression:表明了每列的默认值
主键列不需要赋值
2.插入单行
INSERT INTO customers
VALUES (
DEFAULT,
'Join',
'Smith',
'1990-01-01',
NULL,
'address',
'city',
'CA',
DEFAULT)
----------------------------------------------------------------------------------------------------------------------------
注意名称和赋值对应,顺序不重要
INSERT INTO customers
(
first_name,
last_name,
birth_date,
address,
city,
state)
VALUES (
'Join',
'Smith',
'1990-01-01',
'address',
'city',
'CA',)
3.插入多行
INSERT INTO shippers(name)
VALUES ('Shippers1'),
('Shippers2'),
('Shippers3')
----------------------------------------------------------------------------------------------------------------------------
作业:
INSERT INTO products(name, quantity_in_stock, unit_price)
VALUES ('Product1', 10, 1.95),
('Product2', 11, 1.95),
('Product3', 12, 1.95)
4.插入分层行
一个订单可以买很多东西,第一个表查这个订单是谁下的,第二个表看订单有什么,两个表之间是亲子关系。在这段关系中订单表是母方,订单项目表是子方,订单表的一行可以在订单向目标里有一子或多子。
所谓插入分层行,在这节课就是插入一笔订单以及它所对应的所有项目
INSERT INTO orders(customer_id, order_date, status)
VALUES(1, '2019-01-02', 1);
-- 插入了一个订单,接下来要插入订单项目表as
INSERT INTO order_items
VALUES
(LAST_INSERT_ID(), 1, 1, 2.95),
(LAST_INSERT_ID(), 2, 1, 3.95)
-- SELECT LAST_INSERT_ID()
-- 如何获取新加记录的ID
5.创建表复制:从一张表复制数据到另外一张表
6.更新单行
UPDATE invoices
SET payment_total = 10, payment_date = '2019-03-01'
WHERE invoice_id = 1
UPDATE invoices
SET payment_total = invoice_total * 0.5,
payment_date = due_date
WHERE invoice_id = 3
7.更新多行
USE sql_invoicing;
UPDATE invoices
SET payment_total = invoice_total * 0.5,
payment_date = due_date
WHERE client_id = 3
-- 默认情况下,MySQL工作台会在安全更新模式下运行,它只让你更新一条记录,用别的软件不会出现问题and:edit——preferrences——SQL edit最下面safe update,然后退出重进local instance
UPDATE invoices
SET payment_total = invoice_total * 0.5,
payment_date = due_date
WHERE client_id = 3 IN (3,4)
UPDATE invoices
SET payment_total = invoice_total * 0.5,
payment_date = due_date
-- 更新表里的所有记录,就直接把where子句去掉
-----------------------------------------------------------------------------------------------------------------------------
作业:
USE sql_store;
UPDATE customers
SET points = points + 50
WHERE birth_date < '1990-01-01'
8.在updates中用子查询
最好在执行update语句前,先执行查询,看看会更新什么记录,这样就不会意外更新了不该更新的记录。
USE sql_invoicing;
UPDATE invoices
SET payment_total = invoice_total * 0.5,
payment_date = due_date
WHERE client_id =
(SELECT client_id
FROM clients
WHERE name = 'Myworks')
-- 先执行括号里的这一段
USE sql_invoicing;
UPDATE invoices
SET payment_total = invoice_total * 0.5,
payment_date = due_date
WHERE client_id IN
(SELECT client_id
FROM clients
WHERE state IN ('CA','NY'))
-- 括号里返回多条记录,所以不能用 = ,要用IN
----------------------------------------------------------------------------------------------------------------------------
作业:
USE sql_store;
UPDATE orders
SET comments = 'Gold customer'
WHERE customer_id IN
(SELECT customer_id
FROM customers
WHERE points > 3000)
9.删除行
如果不写WHERE子句的话,这个语句会删除表里的全部记录
USE sql_invoicing;
DELETE FROM invoices
WHERE invoice_id =
(SELECT client_id
FROM clients
WHERE name = 'Myworks')