mosh数据库——第四章

1.列属性|Column Attributes

打开设计模式

Column:列名称                                     

Datatype:数据类型

1. INT：整数型（没有小数点）
2. VARCHAR：可变字符variable                                                                                         [VARCHAR（50）就是说这列最多有50个字符（即使这列字符最长可以有50，也不会因为顾客名没到50字符浪费空间）]
3. CHAR:character                                                                                                                      [CHAR(50)  如果顾客名只有5个字符，MySQL会再插入45个空格符填满这列，这样就很浪费空间]，所以最好使用VARCHAR来储存字符串或值文本值

PK:主键 会有黄钥匙标识

NN: NOT NULL非空值（决定了该列是否可以写空值）

AI: 自动递增

（通常被用于主键列：每次在表中加入一条新数据，我们让MySQL或者数据库引擎在列中插入一个值，其实就是在最后一行加入一个顾客id {就会在加入新记录的同时，让顾客id加1}）

Default/Expression:标注了每列的默认值

2.插入单行|Inserting a Single Row

会使用到INSERT INTO 语句

默认值：default

INSERT INTO customers
VALUES(
DEFAULT,
'John',
'Smith',
'1990-01-01',
NULL,
'address',
'city',
'CA',
DEFAULT)

INSERT INTO customers(
last_name,
first_name,
birth_date,
address,
city,
state) -- 可以选择必须要添加值的列，其他的默认
VALUES (
'Smith',
'John',
'1990-01-01',
'address',
'city',
'CA'
)

3.插入多行

INSERT INTO shippers (name)
VALUES ('Shipper1'),
       ('Shipper2'),
       ('Shipper3')

使用（），来连接

注意MySQL会自动为发货人id列生成值

INSERT INTO products (name, quantity_in_stock, unit_price)
VALUES ('Product1', 10, 1.95),
	   ('Product2', 11, 1.95),
	   ('Product3', 12, 1.95)

4.插入分层行

怎么往多张表中插入数据

 LAST_INSERT_ID()   最近生成插入的id

INSERT INTO orders (customer_id, order_date, status)
VALUES (1, '2019-01-02', 1);-- y要使用；终止第一句
-- SELECT LAST_INSERT_ID()
INSERT INTO order_items
VALUE (LAST_INSERT_ID(),1,1,2.95),
	(LAST_INSERT_ID(),2,1,3.95)

在这串代码中都是order_id

5.创建表复制

从一张表复制数据到另一张表

CREATE TABLE orders_archived AS
SELECT * FROM orders  -- 子查询

首先要创造这张新表|CREATE TABLE .... AS(创建表时，MySQL会忽略一些属性[主键，递增，等])

我们把选择语句称为子查询，子查询是部分属于另外一段SQL语句的选择语句

Truncate Table:清空表中的内容

INSERT INTO orders_archived
SELECT *
FROM orders
WHERE order_date < '2019-01-01'

只复制表的内容： 可以把选择语句作为INSERT语句的子语句

CREATE TABLE invoices_archive AS -- 最后再写这一句
select invoice_id, 
number,
name as client,
invoice_total,
payment_total,
invoice_date,
due_date,
payment_date

from invoices i
join clients c
on i.client_id = c.client_id  -- USING(client_id)
where payment_date IS NOT NULL

6.更新单行

UPDATE invoices
SET payment_total = 10, payment_date = '2019-03-01'  -- DEFAULT填充默认值
WHERE invoice_id = 1

UPDATE invoices
SET
payment_total = invoice_total * 0.5,
payment_date = due_date
WHERE invoice_id = 3

 set后面更新的数据可以是具体的值或者是别的数据的值

UPDATE + 表名

SET +列名和值 （DEFAULT是默认值，外面可不加引号）

WHERE + 条件

7.更新多行

UPDATE invoices
SET
payment_total = invoice_total * 0.5,
payment_date = due_date
WHERE client_id = 3 -- client_id有多条时

只更新第一个client_id值为3的数据,如何全部更新：

去到上面的MySQL Workbentch菜单 然后 ..

点击Preferences(偏好)

在这个对话框的左侧,点击SqL Editor

然后在底部，取消勾选 Safe Update(安全更新【来防止意外更新和删除一些表记录】)

然后重新连接 My Sql instance

• 复制所有的代码 关掉这个Local Instance窗口
• 在主页面 双击这个Connection来重新连接
• 贴贴代码，运行

UPDATE invoices
SET

payment_total = invoice_total * 0.5,
payment_date = due_date
WHERE client_id IN (3, 4) --3和4
-- 所以你们学过的所有可以用在WHERE子句中的运算符 也可以用在这里

USE sql_store;
UPDATE customers
SET points = points + 50
WHERE birth_date < '1990-01-01'

 8.在UpDate中用子查询

一个时,

UPDATE invoices
SET
payment_total = invoice_total * 0.5,
payment_date = due_date
WHERE client_id =
        (SELECT client_id
        FROM clients
        WHERE name = 'Myworks')

多于一个时，用IN

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'))

有空值时

UPDATE invoices
SET
payment_total = invoice_total * 0.5,
payment_date = due_date
WHERE payment_date IS NULL

9.删除行

我们用DELETE FROM语句 用来删除表记录

用WHERE来筛查条件（不用WHERE的话，会被全部删除）

DELETE FROM invoices
WHERE client_id = (
					SELECT client_id
					FROM clients
					WHERE name = 'Myworks'
					)

10.恢复数据库

找到file

打开sql脚本

删除之前的，重新运行SQL文件