MySQL学习笔记（二） 数据表的增删改操作

MySQL 数据表的增删改操作

Column Attributes 列属性

INT：储存数字

CHAR：储存字符串

VARCHAR：动态储存字符串（占用空间随着字符串的大小变化）

PK (Primary Key)：主键

NN (Not Null)：非空（选中后，该列必须存在数据）

AI (Auto Increament)：自动增加（常用主键的序列自增）

Default/Expression：默认值（若无数据，自动填充设定好的内容）

插入数据

插入单行

不选定特定列插入：

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

选定特定列插入：

INSERT INTO customers(
	first_name,
	last_name,
	birth_date,
	address,
	city,
	state
)
VALUE (
	'John',
	'Smith',
	'1990-01-01',
	'address',
	'city',
	'CA',
)

插入多行数据

INSERT INTO clients
VALUE (
	6,
    'Fancy',
    'Shanxi Taiyuan',
	'Taiyuan',
    'SX',
    '666'
),
(
	7,
    'Shawn',
    'Hainan Chengmai',
	'Chengmai',
    'HN',
    '999'
)

复制表格

直接复制一张表格（数据复制过去了，但是列属性的主键等信息还需自己设置）

CREATE TABLE orders_archived AS SELECT * FROM orders

复制部分表格

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

更新数据

更新单行

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

更新多行

UPDATE invoices
SET payment_total = invoice_total*0.5,
payment_date = due_date
WHERE client_id > 2

删除数据

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

汇总数据

Aggregate Function 聚合函数

SELECT
	MAX(invoice_total) AS highest,
	MIN(invoice_total) AS lowest,
	AVG(invoice_total) AS average,
	MAX(payment_date) AS latest,
	MIN(payment_date) AS earliest,
	COUNT(invoice_total) AS number_of_invoices,
	-- 下面的COUNT(列名)代表计算非空行
	COUNT(payment_date) AS count_of_payments,
	-- 下面的COUNT(*)代表了计数所有行
	COUNT(*) AS total_records
FROM invoices

GROUP BY 语句

SELECT
	client_id,
	SUM(invoice_total)AS total_sales
FROM invoices
WHERE invoice_date >= '2019-07-01'
GROUP BY client_id 
ORDER BY total_sales DESC

HAVING 语句

HAVING语句和WHERE语句的区别：WHERE语句是对没有分组之前的数据进行筛选，而HAVING语句是对分组后的数据进一步筛选

SELECT
	client_id,
	SUM(invoice_total) AS total_sales
FROM invoices
GROUP BY client_id
HAVING total_sales>500

WITH ROLLUP 语句 (Only MySQL)

ROLLUP的作用就在每个分组下方增加一行计算数据合，最后对总体也进行求和

SELECT
	state,
	city,
	SUM(invoice_total) AS total_sales
FROM invoices i
JOIN clients c USING (client_id)
GROUP BY client_id, city WITH ROLLUP