便于快速 COPY ，以备不时之需。
▲ 在所有操作之前：
SET character_set_database=utf8;
确保 ↓
mysql> SHOW VARIABLES LIKE "%char%";
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
▲ 创建数据库：
CREATE DATABASE sql_lab;
▲ 然后创建表：
CREATE TABLE chucang ( chucang_no VARCHAR(12), enterprise_name VARCHAR(20), chucang_time DATE, chucang_type CHAR(1) COMMENT ‘企业性质，Y 公有，N 私有‘, enterprise_address VARCHAR(50), delivery_car_no VARCHAR(12), chucang_sum DECIMAL(8,2), PRIMARY KEY(chucang_no) ) ENGINE=INNODB DEFAULT CHARSET=utf8; CREATE TABLE chucang_detail ( chucang_no VARCHAR(12), product_no VARCHAR(12), price DECIMAL(8,2), quantity INT, total DECIMAL(8,2), FOREIGN KEY (chucang_no) REFERENCES chucang(chucang_no) ) ENGINE=INNODB DEFAULT CHARSET=utf8;
PS. 别漏掉 DEFAULT CHARSET=utf8
▲ 确认表是否创建：
mysql> SHOW TABLES; +-------------------+ | Tables_in_sql_lab | +-------------------+ | chucang | | chucang_detail | +-------------------+ 2 rows in set (0.01 sec)
▲ 插入数据：
INSERT INTO chucang (chucang_no, enterprise_name, chucang_time, chucang_type, enterprise_address, delivery_car_no, chucang_sum) VALUES (‘10001‘, ‘enterprise_a‘, ‘1997-10-11‘, ‘Y‘, ‘????‘, ‘30005‘, 0.00), (‘10002‘, ‘enterprise_b‘, ‘1997-10-21‘, ‘Y‘, ‘shanghai‘, ‘30001‘, 0.00), (‘10003‘, ‘enterprise_c‘, ‘1997-10-22‘, ‘N‘, ‘beijin‘, ‘30003‘, 0.00), (‘10004‘, ‘enterprise_d‘, ‘1997-10-23‘, ‘Y‘, ‘shanghai‘, ‘30004‘, 0.00), (‘10005‘, ‘enterprise_e‘, ‘1997-10-24‘, ‘N‘, ‘guangdong‘, ‘30007‘, 0.00); INSERT INTO chucang_detail (chucang_no, product_no, price, quantity, total) VALUES (‘10002‘, ‘221‘, 30.5, 20, 0.00), (‘10003‘, ‘222‘, 31.5, 22, 0.00), (‘10004‘, ‘223‘, 40.5, 20, 0.00), (‘10001‘, ‘224‘, 41.5, 20, 0.00), (‘10005‘, ‘225‘, 43.5, 20, 0.00);
▲ 检查数据：
SELECT * FROM chucang_detail LIMIT 3\G
▲ 修改表，插入一个新的字段/属性/列：
ALTER TABLE chucang ADD enterprise_phone CHAR(20);
▲ 检查是否修改成功：
mysql> DESCRIBE chucang;
▲ 更新数据：
UPDATE chucang_detail SET total = quantity * price;
UPDATE chucang x, chucang_detail y SET x.chucang_sum = y.total WHERE x.chucang_no = y.chucang_no; -- 多表示例 ↑ ↓ UPDATE chucang x SET x.chucang_sum = ( SELECT SUM(total) FROM chucang_detail y GROUP BY y.chucang_no HAVING y.chucang_no = x.chucang_no )
▲ 最简单的查询：
SELECT chucang_sum FROM chucang WHERE chucang_type = ‘Y‘
▲ 排序：
SELECT chucang_no, enterprise_name FROM chucang ORDER BY chucang_no
▲ 子查询：
SELECT chucang_no FROM chucang_detail WHERE chucang_no NOT IN ( SELECT chucang_no FROM chucang )
▲ 分组过滤 & 聚集函数：
SELECT chucang_type, SUM(chucang_sum) FROM chucang GROUP BY chucang_type HAVING chucang_type = ‘Y‘
▲ 本题无关，联表查询示例：
SELECT employee_no, employee_name, product_name, quantity, price FROM employee x LEFT OUTER JOIN order_master y ON x.employee_no = y.saler_no LEFT OUTER JOIN order_detail z ON z.order_no = y.order_no LEFT OUTER JOIN product m ON m.product_no = z.product_no WHERE ( SELECT COUNT(DISTINCT product_no) FROM order_master w LEFT OUTER JOIN order_detail e ON w.order_no = e.order_no WHERE w.saler_no = x.employee_no) >= 5 ORDER BY employee_no
原文：https://www.cnblogs.com/xkxf/p/8966393.html