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

便于 COPY

▲ 在所有操作之前：

SET character_set_database=utf8;

确保 ↓

▲ 创建数据库：

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

    ('',

    'enterprise_a',

    '1997-10-11',

    'Y',

    '????',

    '',

    0.00),

    ('',

    'enterprise_b',

    '1997-10-21',

    'Y',

    'shanghai',

    '',

    0.00),

    ('',

    'enterprise_c',

    '1997-10-22',

    'N',

    'beijin',

    '',

    0.00),

    ('',

    'enterprise_d',

    '1997-10-23',

    'Y',

    'shanghai',

    '',

    0.00),

    ('',

    'enterprise_e',

    '1997-10-24',

    'N',

    'guangdong',

    '',

    0.00);

INSERT INTO chucang_detail

    (chucang_no,

    product_no,

    price,

    quantity,

    total)

VALUES

    ('',

    '',

    30.5,

    20,

    0.00),

    ('',

    '',

    31.5,

    22,

    0.00),

    ('',

    '',

    40.5,

    20,

    0.00),

    ('',

    '',

    41.5,

    20,

    0.00),

    ('',

    '',

    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

巴特西

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

最新文章

热门文章