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
    
posted @ 2020-09-25 10:20  鹏懿如斯  阅读(213)  评论(0编辑  收藏  举报