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

便于 COPY

▲ 在所有操作之前：

SET character_set_database=utf8;

确保 ↓

▲ 创建数据库：

CREATE DATABASE sql_lab;

▲ 然后创建表：

1CREATE TABLE chucang (
2    chucang_no VARCHAR(12),
3    enterprise_name VARCHAR(20),
4    chucang_time DATE,
5    chucang_type CHAR(1) COMMENT '企业性质，Y 公有，N 私有',
6    enterprise_address VARCHAR(50),
7    delivery_car_no VARCHAR(12),
8    chucang_sum DECIMAL(8,2),
9    
10    PRIMARY KEY(chucang_no)
11    
12)    ENGINE=INNODB DEFAULT CHARSET=utf8;
13
14CREATE TABLE chucang_detail (
15    chucang_no VARCHAR(12),
16    product_no VARCHAR(12),
17    price DECIMAL(8,2),
18    quantity INT,
19    total DECIMAL(8,2),
20    
21    FOREIGN KEY (chucang_no) 
22        REFERENCES chucang(chucang_no)
23        
24)    ENGINE=INNODB DEFAULT CHARSET=utf8;

PS. 别漏掉 DEFAULT CHARSET=utf8

▲ 确认表是否创建：

1mysql> SHOW TABLES;
2+-------------------+
3| Tables_in_sql_lab |
4+-------------------+
5| chucang           |
6| chucang_detail    |
7+-------------------+
82 rows in set (0.01 sec)

▲ 插入数据：

1INSERT INTO chucang
2    (chucang_no, 
3    enterprise_name, 
4    chucang_time, 
5    chucang_type,
6    enterprise_address,
7    delivery_car_no,
8    chucang_sum)
9VALUES
10    ('10001',
11    'enterprise_a',
12    '1997-10-11',
13    'Y',
14    '????',
15    '30005',
16    0.00),
17    ('10002',
18    'enterprise_b',
19    '1997-10-21',
20    'Y',
21    'shanghai',
22    '30001',
23    0.00),
24    ('10003',
25    'enterprise_c',
26    '1997-10-22',
27    'N',
28    'beijin',
29    '30003',
30    0.00),
31    ('10004',
32    'enterprise_d',
33    '1997-10-23',
34    'Y',
35    'shanghai',
36    '30004',
37    0.00),
38    ('10005',
39    'enterprise_e',
40    '1997-10-24',
41    'N',
42    'guangdong',
43    '30007',
44    0.00);
45    
46INSERT INTO chucang_detail
47    (chucang_no, 
48    product_no, 
49    price, 
50    quantity,
51    total)
52VALUES
53    ('10002',
54    '221',
55    30.5,
56    20,
57    0.00),
58    ('10003',
59    '222',
60    31.5,
61    22,
62    0.00),
63    ('10004',
64    '223',
65    40.5,
66    20,
67    0.00),
68    ('10001',
69    '224',
70    41.5,
71    20,
72    0.00),
73    ('10005',
74    '225',
75    43.5,
76    20,
77    0.00);

▲ 检查数据：

1SELECT *
2FROM chucang_detail
3LIMIT 3\G

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

1ALTER TABLE chucang
2ADD enterprise_phone CHAR(20);

▲ 检查是否修改成功：

mysql> DESCRIBE chucang;

▲ 更新数据：

1UPDATE chucang_detail
2SET total = quantity * price;
3
4UPDATE chucang x, chucang_detail y
5SET x.chucang_sum = y.total
6WHERE x.chucang_no = y.chucang_no;
7-- 多表示例 ↑ ↓
8UPDATE chucang x
9SET x.chucang_sum = (
10    SELECT SUM(total)
11    FROM chucang_detail y
12    GROUP BY y.chucang_no
13    HAVING y.chucang_no = x.chucang_no
14)

▲ 最简单的查询：

1SELECT chucang_sum
2FROM chucang
3WHERE chucang_type = 'Y'

▲ 排序：

1SELECT chucang_no, enterprise_name
2FROM chucang
3ORDER BY chucang_no

▲ 子查询：

1SELECT chucang_no
2FROM chucang_detail
3WHERE chucang_no NOT IN (
4    SELECT chucang_no
5    FROM chucang
6)

▲ 分组过滤 & 聚集函数：

1SELECT chucang_type, SUM(chucang_sum)
2FROM chucang
3GROUP BY chucang_type
4HAVING chucang_type = 'Y'

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

1SELECT employee_no, employee_name, product_name, quantity, price
2FROM employee x
3LEFT OUTER JOIN order_master y ON x.employee_no = y.saler_no
4LEFT OUTER JOIN order_detail z ON z.order_no = y.order_no
5LEFT OUTER JOIN product m ON m.product_no = z.product_no 
6WHERE (
7    SELECT COUNT(DISTINCT product_no)
8    FROM order_master w
9    LEFT OUTER JOIN order_detail e ON w.order_no = e.order_no
10    WHERE w.saler_no = x.employee_no) >= 5
11ORDER BY employee_no

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

评论区

相关推荐