MySQL 必知必会

• 登录

C:\Program Files\MySQL\MySQL Server 5.7\bin>mysql -u admin -p
Enter password: ****

Server version: 5.7.29

• distinct去重

  select distinct vend_id from products;
  

  select distinct vend_id, prod_price from products;
  

  distinct 后面跟两项时，必须都不相同，才算不同

• limit

  select prod_name from products limit 5;
  

  返回行数不超过 5

• offset

  select prod_name from products limit 5 offset 5;
  -- 简写
  select prod_name from products limit 5 , 5;
  

  从第 5 行开始（下标从 0 开始算），往后检索 5 行

• order by

  后跟多个参数时，以前面的为主

  mysql> select prod_id, prod_name, prod_price
      -> from products
      -> order by prod_price, prod_name;
  +---------+---------------------+------------+
  | prod_id | prod_name           | prod_price |
  +---------+---------------------+------------+
  | BNBG02  | Bird bean bag toy   |       3.49 |
  | BNBG01  | Fish bean bag toy   |       3.49 |
  | BNBG03  | Rabbit bean bag toy |       3.49 |
  | RGAN01  | Raggedy Ann         |       4.99 |
  | BR01    | 8 inch teddy bear   |       5.99 |
  | BR02    | 12 inch teddy bear  |       8.99 |
  | RYL01   | King doll           |       9.49 |
  | RYL02   | Queen doll          |       9.49 |
  | BR03    | 18 inch teddy bear  |      11.99 |
  +---------+---------------------+------------+
  

  可按 select 字段的顺序简写（排序的字段必须在 select 中）

  select prod_id, prod_name, prod_price from products order by 3, 2;
  

  order by 必须在 where 之后

• desc

  select prod_id, prod_name, prod_price
      -> from products
      -> order by 3 desc, 2;
  

  desc 应分别放在要排序的列的后边

  默认是 asc 升序

  排序中默认不区分大小写

• not 写哪都行

  mysql> select prod_name, prod_price from products
      -> where vend_id not in ('DLL01','BRS01')
      -> order by prod_name;
  mysql> select prod_name, prod_price from products
      -> where not vend_id in ('DLL01','BRS01')
      -> order by prod_name;
  +------------+------------+
  | prod_name  | prod_price |
  +------------+------------+
  | King doll  |       9.49 |
  | Queen doll |       9.49 |
  +------------+------------+
  

  mysql> select prod_name, prod_price from products
      -> where not prod_price = 9.49;
  

• 通配符

  %，0~多个任意字符

  mysql> select prod_id, prod_name from products
      -> where prod_name like 'fish%';
  mysql> select prod_id, prod_name from products
      -> where prod_name like '%bean bag%';
  mysql> select prod_id, prod_name from products
      -> where prod_name like 'f%y';
  mysql> select prod_id, prod_name from products
      -> where prod_name like '%'; -- 只有null不被匹配
  

  _，1个任意字符

  mysql> select prod_id, prod_name from products
      -> where prod_name like '__ inch teddy bear';
  

• 拼接字段

  select concat(vend_name, ' (', vend_country, ')') from vendors;
  

  去空格

  select trim(concat(vend_name, ' (', vend_country, ') ')) from vendors;
  

  ltrim，去左空格

  rtrim，去右空格

• 算术运算符

  + - * /

  mysql> select prod_id, quantity, item_price, quantity*item_price as expanded_price
      -> from orderitems
      -> where order_num = 20008;
  +---------+----------+------------+----------------+
  | prod_id | quantity | item_price | expanded_price |
  +---------+----------+------------+----------------+
  | RGAN01  |        5 |       4.99 |          24.95 |
  | BR03    |        5 |      11.99 |          59.95 |
  | BNBG01  |       10 |       3.49 |          34.90 |
  | BNBG02  |       10 |       3.49 |          34.90 |
  | BNBG03  |       10 |       3.49 |          34.90 |
  +---------+----------+------------+----------------+
  

• 文本处理函数

  upper，转大写，lower，转小写

  mysql> select vend_name, upper(vend_name) as vend_name_upcase
      -> from vendors
      -> order by vend_name;
  

  length，字符串长度

  soundex，返回 soundex 值

  mysql> select vend_name, soundex(vend_name) as soundex from vendors;
  +-----------------+---------+
  | vend_name       | soundex |
  +-----------------+---------+
  | Bear Emporium   | B65165  |
  | Bears R Us      | B6262   |
  | Doll House Inc. | D4252   |
  | Fun and Games   | F53252  |
  | Furball Inc.    | F61452  |
  | Jouets et ours  | J32362  |
  +-----------------+---------+
  mysql> select cust_name, cust_contact from customers
      -> where soundex(cust_contact) = soundex('michael green');
  +------------+----------------+
  | cust_name  | cust_contact   |
  +------------+----------------+
  | Kids Place | Michelle Green |
  +------------+----------------+
  

  SOUNDEX 是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。

  substring(s, start, n)，start 是起始坐标（从 1 开始算），n 是返回最大字符数

• 日期时间处理函数

  year，哪一年

  mysql> select order_date from orders where year(order_date) = 2012;
  +---------------------+
  | order_date          |
  +---------------------+
  | 2012-05-01 00:00:00 |
  | 2012-01-12 00:00:00 |
  | 2012-01-30 00:00:00 |
  | 2012-02-03 00:00:00 |
  | 2012-02-08 00:00:00 |
  +---------------------+
  

  month，day，hour，minute，second

  select order_date from orders where month(order_date) = 01;
  select order_date from orders where day(order_date) = 01;
  

• 数值处理函数

  函数	说明
  abs	返回一个数的绝对值
  cos	返回一个角度的余弦，使用弧度制
  exp	返回一个数的指数值，即自然对数 e 的几次方
  pi	返回圆周率
  sin	返回一个角度的正弦，select sin(pi()/2);输出 1
  sqrt	返回一个数的平方根
  tan	返回一个角度的正切

• 聚集函数

  函数	说明
  avg	返回某列的平均值，忽略列中的 null
  count	返回某列的行数，count(*) null 值也算，count(字段) null 值不算
  max	返回某列的最大值
  min	返回某列的最小值
  sum	返回某列值之和

  select avg(prod_price) as avg_price from products;
  select max(prod_price) as max_price from products;
  

  mysql> select count(*) as num_cust from customers;
  +----------+
  | num_cust |
  +----------+
  |        5 |
  +----------+
  mysql> select count(cust_email) as num_cust from customers;
  +----------+
  | num_cust |
  +----------+
  |        3 |
  +----------+
  

  select sum(quantity * item_price) from orderitems;
  

  去重放在字段前面

  mysql> select sum(prod_price) from products;
  +-----------------+
  | sum(prod_price) |
  +-----------------+
  |           61.41 |
  +-----------------+
  
  mysql> select sum(distinct prod_price) from products;
  +--------------------------+
  | sum(distinct prod_price) |
  +--------------------------+
  |                    44.94 |
  +--------------------------+
  

  组合聚集函数

  mysql> select count(*) as num_items,
      -> min(prod_price) as price_num,
      -> max(prod_price) as price_max,
      -> avg(prod_price) as price_avg
      -> from products;
  +-----------+-----------+-----------+-----------+
  | num_items | price_num | price_max | price_avg |
  +-----------+-----------+-----------+-----------+
  |         9 |      3.49 |     11.99 |  6.823333 |
  +-----------+-----------+-----------+-----------+
  

• 数据分组 group by

  mysql> select vend_id, count(*) as num_prods
      -> from products
      -> group by vend_id;
  +---------+-----------+
  | vend_id | num_prods |
  +---------+-----------+
  | BRS01   |         3 |
  | DLL01   |         4 |
  | FNG01   |         2 |
  +---------+-----------+
  

• 过滤分组

  having，过滤分组，而 where 过滤行，在分组前使用

  mysql> select vend_id, count(*) as num_prods
      -> from products
      -> group by vend_id
      -> having count(*) > 2;
  +---------+-----------+
  | vend_id | num_prods |
  +---------+-----------+
  | BRS01   |         3 |
  | DLL01   |         4 |
  +---------+-----------+
  
  mysql> select vend_id, count(*) as num_prods
      -> from products
      -> where prod_price >= 4
      -> group by vend_id
      -> having count(*) >= 2;
  +---------+-----------+
  | vend_id | num_prods |
  +---------+-----------+
  | BRS01   |         3 |
  | FNG01   |         2 |
  +---------+-----------+
  

  order by 排序放在最最后边

  mysql> select order_num, count(*) as items
      -> from orderitems
      -> group by order_num
      -> having count(*) >= 3
      -> order by items, order_num;
  +-----------+-------+
  | order_num | items |
  +-----------+-------+
  |     20006 |     3 |
  |     20009 |     3 |
  |     20007 |     5 |
  |     20008 |     5 |
  +-----------+-------+
  

• select 字句顺序

  子句	说明	是否必须使用
  select	要返回的列或表达式	✔
  from	从中检索数据的表	从表中选择数据时使用
  where	行级过滤	✖
  group by	分组说明	按组计算聚集时使用
  having	组级过滤	✖
  order by	输出排序顺序	✖

• 子查询

  • 列出订购物品 RGAN01 的所有顾客，分开查询，需要三步

  mysql> select order_num from orderitems where prod_id = 'rgan01';
  +-----------+
  | order_num |
  +-----------+
  |     20007 |
  |     20008 |
  +-----------+
  2 rows in set (0.00 sec)
  
  mysql> select cust_id from orders where order_num in ('20007','20008');
  +------------+
  | cust_id    |
  +------------+
  | 1000000004 |
  | 1000000005 |
  +------------+
  2 rows in set (0.00 sec)
  
  mysql> select cust_id, cust_name from customers where cust_id in ('1000000004','1000000005');
  +------------+---------------+
  | cust_id    | cust_name     |
  +------------+---------------+
  | 1000000004 | Fun4All       |
  | 1000000005 | The Toy Store |
  +------------+---------------+
  

  嵌套，合起来写

  select cust_id, cust_name from customers where cust_id in (select cust_id from orders where order_num in (select order_num from orderitems where prod_id = 'rgan01'));
  

  作为子查询的 SELECT 语句只能查询单个列，企图检索多个列将返回错误

  • 要显示 Customers 表中每个顾客的订单总数

  mysql> select cust_name, (select count(*) from orders where orders.cust_id = customers.cust_id) as orders from customers order by cust_name;
  +---------------+--------+
  | cust_name     | orders |
  +---------------+--------+
  | Fun4All       |      1 |
  | Fun4All       |      1 |
  | Kids Place    |      0 |
  | The Toy Store |      1 |
  | Village Toys  |      2 |
  +---------------+--------+
  

  表名.列名 区分混淆的列

• 联结表

  一个联结的例子

  mysql> select vend_name, prod_name, prod_price from vendors, products
      -> where vendors.vend_id = products.vend_id;
  +-----------------+---------------------+------------+
  | vend_name       | prod_name           | prod_price |
  +-----------------+---------------------+------------+
  | Doll House Inc. | Fish bean bag toy   |       3.49 |
  | Doll House Inc. | Bird bean bag toy   |       3.49 |
  | Doll House Inc. | Rabbit bean bag toy |       3.49 |
  | Bears R Us      | 8 inch teddy bear   |       5.99 |
  | Bears R Us      | 12 inch teddy bear  |       8.99 |
  | Bears R Us      | 18 inch teddy bear  |      11.99 |
  | Doll House Inc. | Raggedy Ann         |       4.99 |
  | Fun and Games   | King doll           |       9.49 |
  | Fun and Games   | Queen doll          |       9.49 |
  +-----------------+---------------------+------------+
  

  若没有条件，检索出的行数为第一个表的行数乘以第二个表中的行数（笛卡尔积），要使用 where 筛选出正确的

  mysql> select count(*) from vendors;
  +----------+
  | count(*) |
  +----------+
  |        6 |
  +----------+
  
  mysql> select count(*) from products;
  +----------+
  | count(*) |
  +----------+
  |        9 |
  +----------+
  
  mysql> select count(*) from (select vend_name, prod_name, prod_price from vendors, products) as new_table;
  +----------+
  | count(*) |
  +----------+
  |       54 |
  +----------+
  

  返回笛卡儿积的联结，也称叉联结（cross join）

• 内联结

  mysql> select vend_name, prod_name, prod_price
      -> from vendors as v inner join products as p
      -> on v.vend_id = p.vend_id; -- where结果一样
  

• 联结多个表

  mysql> select prod_name, vend_name, prod_price, quantity
      -> from orderitems as o, products as p, vendors as v
      -> where p.vend_id = v.vend_id and o.prod_id = p.prod_id and order_num = 20007;
  +---------------------+-----------------+------------+----------+
  | prod_name           | vend_name       | prod_price | quantity |
  +---------------------+-----------------+------------+----------+
  | 18 inch teddy bear  | Bears R Us      |      11.99 |       50 |
  | Fish bean bag toy   | Doll House Inc. |       3.49 |      100 |
  | Bird bean bag toy   | Doll House Inc. |       3.49 |      100 |
  | Rabbit bean bag toy | Doll House Inc. |       3.49 |      100 |
  | Raggedy Ann         | Doll House Inc. |       4.99 |       50 |
  +---------------------+-----------------+------------+----------+
  

  mysql> select cust_name, cust_contact
      -> from customers as c, orders as o, orderitems as oi
      -> where c.cust_id = o.cust_id and oi.order_num = o.order_num and prod_id = 'rgan01';
  +---------------+--------------------+
  | cust_name     | cust_contact       |
  +---------------+--------------------+
  | Fun4All       | Denise L. Stephens |
  | The Toy Store | Kim Howard         |
  +---------------+--------------------+
  

• 自联结（重复用自己）

  找到与 Jim Jones 同一公司的所有顾客

  用子查询

  mysql> select cust_id, cust_name, cust_contact from customers
      -> where cust_name = (select cust_name from customers where cust_contact = 'jim jones');
  +------------+-----------+--------------------+
  | cust_id    | cust_name | cust_contact       |
  +------------+-----------+--------------------+
  | 1000000003 | Fun4All   | Jim Jones          |
  | 1000000004 | Fun4All   | Denise L. Stephens |
  +------------+-----------+--------------------+
  

  表联结

  mysql> select c1.cust_id, c1.cust_name, c1.cust_contact
      -> from customers as c1, customers as c2
      -> where c1.cust_name = c2.cust_name and c2.cust_contact = 'jim jones';
  

• 自然联结

  mysql> select c.*, o.order_num, o.order_date, oi.prod_id, oi.quantity, oi.item_price
      -> from customers as c, orders as o, orderitems as oi
      -> where c.cust_id = o.cust_id and oi.order_num = o.order_num and prod_id = 'rgan01';
  

• 外联结

  必须有 left 或 right

  检索所有顾客及每个顾客所下的订单有哪些

  mysql> select customers.cust_id, orders.order_num
      -> from customers inner join orders
      -> on customers.cust_id = orders.cust_id;
  +------------+-----------+
  | cust_id    | order_num |
  +------------+-----------+
  | 1000000001 |     20005 |
  | 1000000001 |     20009 |
  | 1000000003 |     20006 |
  | 1000000004 |     20007 |
  | 1000000005 |     20008 |
  +------------+-----------+
  
  mysql> select c.cust_id, o.order_num
      -> from customers as c left outer join orders as o
      -> on c.cust_id = o.cust_id;
  +------------+-----------+
  | cust_id    | order_num |
  +------------+-----------+
  | 1000000001 |     20005 |
  | 1000000001 |     20009 |
  | 1000000002 |      NULL |
  | 1000000003 |     20006 |
  | 1000000004 |     20007 |
  | 1000000005 |     20008 |
  +------------+-----------+
  
  mysql> select c.cust_id, o.order_num
      -> from customers as c right outer join orders as o
      -> on c.cust_id = o.cust_id;
  +------------+-----------+
  | cust_id    | order_num |
  +------------+-----------+
  | 1000000001 |     20005 |
  | 1000000001 |     20009 |
  | 1000000003 |     20006 |
  | 1000000004 |     20007 |
  | 1000000005 |     20008 |
  +------------+-----------+
  

• 聚集函数 + 联结

  检索所有顾客及每个顾客所下的订单数

  mysql> select c.cust_id, count(o.order_num) as num_order
      -> from customers as c inner join orders as o
      -> on c.cust_id = o.cust_id
      -> group by c.cust_id;
  +------------+-----------+
  | cust_id    | num_order |
  +------------+-----------+
  | 1000000001 |         2 |
  | 1000000003 |         1 |
  | 1000000004 |         1 |
  | 1000000005 |         1 |
  +------------+-----------+
  
  mysql> select c.cust_id, count(o.order_num) as num_order
      -> from customers as c left outer join orders as o
      -> on c.cust_id = o.cust_id
      -> group by c.cust_id;
  +------------+-----------+
  | cust_id    | num_order |
  +------------+-----------+
  | 1000000001 |         2 |
  | 1000000002 |         0 |
  | 1000000003 |         1 |
  | 1000000004 |         1 |
  | 1000000005 |         1 |
  +------------+-----------+
  

• 什么时候使用联结

  • 注意所使用的联结类型。一般我们使用内联结，但使用外联结也有效
  • 关于确切的联结语法，应该查看具体的文档，看相应的 DBMS 支持何种语法
  • 保证使用正确的联结条件（不管采用哪种语法），否则会返回不正确的数据。
  • 应该总是提供联结条件，否则会得出笛卡儿积
  • 在一个联结中可以包含多个表，甚至可以对每个联结采用不同的联结类型。虽然这样做是合法的，一般也很有用，但应该在一起测试它们前分别测试每个联结。这会使故障排除更为简单

• 组合查询

  同一个表

  mysql> select cust_name, cust_contact, cust_email
      -> from customers
      -> where cust_state in ('il','in','mi')
      -> union
      -> select cust_name, cust_contact, cust_email
      -> from customers
      -> where cust_name = 'fun4all';
      -> order by cust_name, cust_contact;
  

  相当于

  mysql> select cust_name, cust_contact, cust_email from customers
      -> where cust_state in ('il','in','mi') or cust_name = 'fun4all';
  

  union 可自动去重，不想去重可用 union all

  union 规则

  • UNION 必须由两条或两条以上的 SELECT 语句组成，语句之间用关键字UNION 分隔（因此，如果组合四条SELECT语句，将要使用三个UNION关键字）
  • UNION 中的每个查询必须包含相同的列、表达式或聚集函数（不过，各个列不需要以相同的次序列出）
  • 列数据类型必须兼容：类型不必完全相同，但必须是 DBMS 可以隐含转换的类型（例如，不同的数值类型或不同的日期类型）

• 数据插入

  直接按字段顺序，不建议使用

  mysql> insert into customers
      -> values('1000000006','toy land','123 street','Beijing','BJ','102249','China',null,null);
  

  把字段写上，前后对应插入

  mysql> insert into customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country)
      -> values('1000000006','toy land','123 street','Beijing','BJ','102249','China');
  

  insert 还存在另一种形式，可以利用它将 select 语句的结果插入表中

  如，把另一表中的顾客列合并到 customers 表中

  mysql> insert into customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country)
      -> select cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country
      -> from custnew;
  

• 表复制

  mysql> create table custcopy as
      -> select * from customers;
  

• 更新数据

  mysql> update customers
      -> set cust_email = 'kim@thetoystore.com'
      -> where cust_id = '1000000005';
  

  set 中若有多个字段，用逗号隔开

  删除某行的某个值，可以把它 set 成 null，表示空，不用 where 即可删除一列

• 删除数据

  删除一行

  mysql> delete from customers
      -> where cust_id = '1000000006';
  

  若没有 where 将删除所有行

  mysql> delete from custcopy;
  Query OK, 6 rows affected (0.01 sec)
  

  或者使用 truncate，速度更快

  mysql> truncate table custcopy;
  

  联通表结构一块删除

  mysql> drop table custcopy;
  

• 创建表

  mysql> create table t1(
      -> id	char(10)	not null,
      -> name	char(10),
      -> age	integer		default 18
      -> );
  

• 更新表

  增加一列

  mysql> alter table t1
      -> add tel char(20);
  

  删除一列

  mysql> alter table t1
      -> drop column tel;
  

  删除表

  mysql> drop table t1;
  

  重命名表

  mysql> rename table os to orders;
  

• 创建视图

  create view 接 select

  本质就是存下一个 select 的查询结果，方便以后使用

  mysql> create view productcustomers as
      -> select cust_name, cust_contact, prod_id
      -> from customers as c, orders as o, orderitems as oi
      -> where c.cust_id = o.cust_id and oi.order_num = o.order_num;
  

  mysql> select cust_name, cust_contact
      -> from productcustomers
      -> where prod_id = 'rgan01'
      -> order by cust_name; -- 不建议在这排序
  +---------------+--------------------+
  | cust_name     | cust_contact       |
  +---------------+--------------------+
  | Fun4All       | Denise L. Stephens |
  | The Toy Store | Kim Howard         |
  +---------------+--------------------+
  

  mysql> create view vendorlocations as
      -> select concat(vend_name, ' (', vend_country, ')') as vend_title
      -> from vendors;
  

  mysql> select * from vendorlocations;
  +-------------------------+
  | vend_title              |
  +-------------------------+
  | Bear Emporium (USA)     |
  | Bears R Us (USA)        |
  | Doll House Inc. (USA)   |
  | Fun and Games (England) |
  | Furball Inc. (USA)      |
  | Jouets et ours (France) |
  +-------------------------+
  

• 视图过滤

  只看有 email 的用户

  mysql> create view customeremaillist as
      -> select cust_id, cust_name, cust_email from customers
      -> where cust_email is not null;
  
  mysql> select * from customeremaillist;
  +------------+---------------+-----------------------+
  | cust_id    | cust_name     | cust_email            |
  +------------+---------------+-----------------------+
  | 1000000001 | Village Toys  | sales@villagetoys.com |
  | 1000000003 | Fun4All       | jjones@fun4all.com    |
  | 1000000004 | Fun4All       | dstephens@fun4all.com |
  | 1000000005 | The Toy Store | kim@thetoystore.com   |
  +------------+---------------+-----------------------+
  

• 事务

  开启事务首先要关闭自动提交，set autocommit = 0;

  mysql> start transaction;
  mysql> update acount
      -> set money = money - 500
      -> where name = 'a';
  mysql> update acount
      -> set money = money + 500
      -> where name = 'b';
  mysql> commit;
  

• 回滚

  mysql> delete from acount where name = 'a';
  mysql> select * from acount;
  +------+-------+
  | name | money |
  +------+-------+
  | b    |  1500 |
  +------+-------+
  mysql> rollback;
  mysql> select * from acount;
  +------+-------+
  | name | money |
  +------+-------+
  | a    |   500 |
  | b    |  1500 |
  +------+-------+
  

• 保留点

  savepoint，返回的点

  mysql> savepoint delete1;
  
  mysql> delete from acount where name = 'a';
  mysql> delete from acount where name = 'b';
  
  mysql> select * from acount;
  Empty set (0.00 sec)
  
  mysql> rollback; -- 或者使用 rollback to delete1;
  
  mysql> select * from acount;
  +------+-------+
  | name | money |
  +------+-------+
  | a    |   500 |
  | b    |  1500 |
  +------+-------+
  

  在事务中也一样使用

• 添加主键

  • 创建时添加 primary key

  • 后来添加

  mysql> alter table acount
      -> add primary key(name);
  

• 添加索引

  mysql> create index name_idx
      -> on acount(name);
  

• Bye

  mysql> quit;
  Bye