便于快速 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_detailLIMIT 3\G

 ▲ 修改表，插入一个新的字段/属性/列：

ALTER TABLE chucangADD enterprise_phone CHAR(20);

▲ 检查是否修改成功：

mysql> DESCRIBE chucang;

 ▲ 更新数据：

UPDATE chucang_detailSET total = quantity * price;

UPDATE chucang x, chucang_detail ySET x.chucang_sum = y.totalWHERE x.chucang_no = y.chucang_no;-- 多表示例 ↑ ↓UPDATE chucang xSET x.chucang_sum = ( SELECT SUM(total) FROM chucang_detail y GROUP BY y.chucang_no HAVING y.chucang_no = x.chucang_no)

▲ 最简单的查询：

SELECT chucang_sumFROM chucangWHERE chucang_type = ‘Y‘

▲ 排序：

SELECT chucang_no, enterprise_nameFROM chucangORDER BY chucang_no

▲ 子查询：

SELECT chucang_noFROM chucang_detailWHERE chucang_no NOT IN ( SELECT chucang_no FROM chucang)

▲ 分组过滤 & 聚集函数：

SELECT chucang_type, SUM(chucang_sum)FROM chucangGROUP BY chucang_typeHAVING chucang_type = ‘Y‘

▲ 本题无关，联表查询示例：

SELECT employee_no, employee_name, product_name, quantity, priceFROM employee xLEFT 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) >= 5ORDER BY employee_no