mysql4614_MySQL笔记（四）DDL与DML风格参考

便于 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 TABLEchucang (

chucang_noVARCHAR(12),

enterprise_nameVARCHAR(20),

chucang_time DATE,

chucang_typeCHAR(1) COMMENT '企业性质，Y 公有，N 私有',

enterprise_addressVARCHAR(50),

delivery_car_noVARCHAR(12),

chucang_sumDECIMAL(8,2),PRIMARY KEY(chucang_no)

) ENGINE=INNODB DEFAULT CHARSET=utf8;CREATE TABLEchucang_detail (

chucang_noVARCHAR(12),

product_noVARCHAR(12),

priceDECIMAL(8,2),

quantityINT,

totalDECIMAL(8,2),FOREIGN KEY(chucang_no)REFERENCESchucang(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 INTOchucang

(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 INTOchucang_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 *

FROMchucang_detailLIMIT 3\G

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

ALTER TABLEchucangADD enterprise_phone CHAR(20);

▲ 检查是否修改成功：

mysql> DESCRIBE chucang;

▲ 更新数据：

UPDATEchucang_detailSET total = quantity * price;

UPDATEchucang x, chucang_detail ySET x.chucang_sum =y.totalWHERE x.chucang_no =y.chucang_no;--多表示例 ↑ ↓

UPDATEchucang xSET x.chucang_sum =(SELECT SUM(total)FROMchucang_detail yGROUP BYy.chucang_noHAVING y.chucang_no =x.chucang_no

)

▲ 最简单的查询：

SELECTchucang_sumFROMchucangWHERE chucang_type = 'Y'

▲ 排序：

SELECTchucang_no, enterprise_nameFROMchucangORDER BY chucang_no

▲ 子查询：

SELECTchucang_noFROMchucang_detailWHERE chucang_no NOT IN(SELECTchucang_noFROMchucang

)

▲ 分组过滤 & 聚集函数：

SELECT chucang_type, SUM(chucang_sum)FROMchucangGROUP BYchucang_typeHAVING chucang_type = 'Y'

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

SELECTemployee_no, employee_name, product_name, quantity, priceFROMemployee xLEFT OUTER JOIN order_master y ON x.employee_no =y.saler_noLEFT OUTER JOIN order_detail z ON z.order_no =y.order_noLEFT OUTER JOIN product m ON m.product_no =z.product_noWHERE(SELECT COUNT(DISTINCTproduct_no)FROMorder_master wLEFT OUTER JOIN order_detail e ON w.order_no =e.order_noWHERE w.saler_no = x.employee_no) >= 5

ORDER BY employee_no