MySQL 学习笔记（4）— 组合查询 子查询 插入数据 更新/删除表数据 增加/删除表

1. 组合查询

1、表的加减法

表的加法，即求product和product2的并集，UNION运算会除去重复的记录

SELECT product_id, product_name FROM product UNION SELECT product_id, product_name FROM product2

要保留重复的行时，使用UNION ALL

SELECT product_id, product_name FROM product UNION ALLSELECT product_id, product_name FROM product2

注意事项：

1、作为运算对象的记录的列数必须相同

2、作为运算对象的记录中列的数据类型必须一致

3、可以使用任何SELECT语句，但是ORDER BY子句只能在最后使用一次

2. 插入数据

SQL主要提供了两种增加数据的方法：

INSERT INTO … VALUES … ;INSERT INTO … SELECT … ;

首先，第一种形式的语法如下：

INSERT INTO t(col1, col2, ...)VALUES (value1, value2, ...);

其中，t是表名；VALUES子句中提供的值与INSERT INTO中的字段数量必须相同，并且数据类型能够兼容。如下示例：

INSERT INTO product (product_id, product_name, product_type, sale_price, purchase_price, regist_date) VALUES ('009', '打印机', '办公用品', 5000, 2000, '-03-11');

如果VALUES值列表与表中的字段顺序完全一致，可以省略字段列表。

表中插入数据，是按行插入的。

START TRANSACTION; -- 事物开始的标志INSERT INTO product VALUES ('001', 'T恤衫', '衣服', 1000, 500, NULL);或者连续插入两行INSERT INTO product (product_id, product_name, product_type, sale_price, purchase_price, regist_date) VALUES ('010', '打印机', '办公用品', 5000, 2000, '-03-11'),('011', '篮球', '体育用品', 200, 50, '-08-23');COMMIT;-- 事物结束的标志 COMMIT提交 或者 ROLLBACK 取消提交

3. 更新和删除数据

3.1 更新数据

SQL使用UPDATE语句更新表中的数据，基本语法如下：

UPDATE tSET col1 = expr1,col2 = expr2,...[WHERE condition];

其中，t是表名；SET子句指定了需要更新的列和更新后的值，多个字段使用逗号进行分隔；只有满足WHERE条件的数据行才会被更新；如果没有指定条件，将会更新表中所有行。

更新数据库示例：

UPDATE addressbook_new SET country = '美国' WHERE regist_no = 2;UPDATE addressbook_new SET country = '德国';UPDATE addressbook_new SET country = NULL;# 多列更新UPDATE addressbook_new SET country = '中国', address='北京', tel_no = '110';

3.2 删除数据

SQL中使用DELETE语句删除数据：

DELETE FROM t[WHERE conditions];

只有满足WHERE条件的数据才会被删除；如果省略WHERE条件，将会删除表中所有的数据。

数据的删除（只删除表中的数据，表仍然存在）

DELETE FROM addressbook_new WHERE regist_no = 1;DELETE FROM addressbook_new;

DELETE与TRUNCATE区别：

DELETE语句通过WHERE条件从表中删除指定的数据；如果不指定条件将会删除所有的数据。DELETE属于数据操作语言（DML），删除数据后，可以选择提交或者回滚。如果删除的数据较多时速度比较慢。TRUNCATE语句用于快速删除表中的所有数据，并且释放表的存储空间。TRUNCATE属于数据定义语言（DDL），操作默认提交无法回滚。TRUNCATE相当于删除表并重建，通常执行速度很快。

4. 删除表结构和数据

删除表结构和数据使用DROP TABLE语句。

DROP TABLE product;

5. 添加删除表中的列

添加表中列的语句

ALTER TABLE addressbook ADD COLUMN （列定义）postal_code CHAR(8) NOT NULL;

删除表中列的语句

ALTER TABLE addressbook DROP COLUMN （列名）postal_code

6. 重命名表

RENAME TABLE product TO new_product;

7. 子查询

子查询就是将用来定义视图的SELECT语句直接作用于FROM子句, 子查询作为内层查询会首先执行。

SELECT product_type, cnt_product FROM (SELECT product_type, COUNT(*) AS cnt_product FROM product GROUP BY product_type ) AS product_sum;

MySQL 学习笔记（4）— 组合查询 子查询 插入数据 更新/删除表数据 增加/删除表中的列以及重命名表