MySQL复习秘籍

谁践踏了优雅 2022-07-16 03:28 179阅读 0赞

# 基础知识: #

## 1.数据库的连接 ##

mysql -u -p -h
    -u 用户名
    -p 密码
    -h host主机

## 2.库级知识 ##

1.  显示数据库: show databases;
2.  选择数据库: use dbname;
3.  创建数据库: create database dbname charset utf8;
4.  删除数据库: drop database dbname;

## 3. 表级操作 ##

### 1. 显示库下面的表 ###

show tables;

### 2. 查看表的结构: ###

desc tableName;

### 3. 查看表的创建过程: ###

show create table  tableName;

### 4. 创建表: ###

create table tbName (
            列名称1　列类型　[列参数]　[not null default ],
            ....列2...
            ....
            列名称N　列类型　[列参数]　[not null default ]
        )engine myisam/innodb charset utf8/gbk
    
        例子:
        create table user (
            id int auto_increment,
            name varchar(20) not null default '',
            age tinyint unsigned not null default 0,
           index id (id)
        )engine=innodb charset=utf8;
    
    
        注:innodb是表引擎,也可以是myisam或其他,但最常用的是myisam和innodb,charset 常用的有utf8,gbk;

### 5. 修改表 ###

1.  修改表之增加列:
    
        alter table tbName 
             add 列名称１　列类型　[列参数]　[not null default ]　#(add之后的旧列名之后的语法和创建表时的列声明一样)
2.  修改表之修改列
    
        alter table tbName
            change 旧列名  新列名  列类型　[列参数]　[not null default ]
    
    (注:旧列名之后的语法和创建表时的列声明一样)
3.  修改表之减少列:
    
        alter table tbName 
            drop 列名称;
4.  修改表之增加主键
    
        alter table tbName add primary key(主键所在列名);
            例:alter table goods add primary key(id)
            该例是把主键建立在id列上
5.  修改表之删除主键
    
        alter table tbName　drop primary key;
6.  修改表之增加索引
    
        alter table tbName add [unique|fulltext] index 索引名(列名);
7.  修改表之删除索引
    
        alter table tbName drop index 索引名;
8.  清空表的数据
    
        truncate tableName;

## 4. 列类型讲解 ##

*  整型:
    
        tinyint (0~255/-128~127) 
        smallint (0~65535/-32768~32767) 
        mediumint 
        int 
        bigint (参考手册11.2)
        
        参数解释:
            unsigned 无符号(不能为负)  zerofill 0填充  M 填充后的宽度
        举例:tinyint unsigned;
            tinyint(6) zerofill;
 *  数值型
    
        浮点型:float double
        格式:float(M,D)  unsigned\zerofill;
 *  字符型
    
        char(m) 定长
        varchar(m)变长
        text

<table> 
 <thead> 
  <tr> 
   <th>列</th> 
   <th>实存字符i</th> 
   <th>实占空间</th> 
   <th>利用率</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>char(M)</td> 
   <td>0&lt;=i&lt;=M</td> 
   <td>M</td> 
   <td>i/m&lt;=100%</td> 
  </tr> 
  <tr> 
   <td>varchar(M)</td> 
   <td>0&lt;=i&lt;=M</td> 
   <td>i+1,2</td> 
   <td>i/i+1/2&lt;100%</td> 
  </tr> 
 </tbody> 
</table>

*  日期时间类型
    
        year       YYYY 范围:1901~2155. 可输入值2位和4位(如98,2012)
        date       YYYY-MM-DD 如:2010-03-14
        time       HH:MM:SS 如:19:26:32
        datetime   YYYY-MM-DD  HH:MM:SS 如:2010-03-14 19:26:32
        timestamp  YYYY-MM-DD  HH:MM:SS 特性:不用赋值,该列会为自己赋当前的具体时间

## 5. 增删改查基本操作 ##

### 1. 插入数据 ###

-- 插入指定列
        insert into 表名(col1,col2,……) values(val1,val2……); 
    
    -- 插入所有列
        insert into 表名 values (,,,,); 
    
    -- 一次插入多行
        insert into 表名 values    
        (val1,val2……),
        (val1,val2……),

### 2. 修改数据 ###

update tablename 
    set 
    col1=newval1,  
    col2=newval2,
    ...
    ...
    colN=newvalN
    where 条件;

### 3. 删除数据 ###

delete from tablenaeme where 条件;

### 4. select 查询 ###

1.  条件查询 where
    
        a. 条件表达式的意义，表达式为真，则该行取出 
        b. 比较运算符  = ，!=，< > <=  >=
        c. like , not like ('%'匹配任意多个字符,'_'匹配任意单个字符) 
            in , not in , between and
        d. is null , is not null
2.  分组 group by
    
        一般要配合5个聚合函数使用:max,min,sum,avg,count
3.  筛选 having
4.  排序 order by
5.  限制 limit
6.  连接查询
    
    1.  左连接 .. left join .. on
        
            table A left join table B on tableA.col1 = tableB.col2 ;
             例句:
              select 列名 from table A left join table B on tableA.col1 = tableB.col2
    2.  右链接: right join
    3.  内连接: inner join

左右连接都是以在左边的表的数据为准,沿着左表查右表.  
内连接是以两张表都有的共同部分数据为准,也就是左右连接的数据之交集.  
7. 子查询  
where 型子查询:  
内层sql的返回值在where后作为条件表达式的一部分  
例句: select \* from tableA where colA = (select colB from tableB where …);

from 型子查询:  
内层sql查询结果,作为一张表,供外层的sql语句再次查询  
例句:select \* from (select \* from …) as tableName where ….  
8. 字符集

客服端sql编码 character_set_client
    服务器转化后的sql编码 character_set_connection
    服务器返回给客户端的结果集编码     character_set_results
    快速把以上3个变量设为相同值: set names 字符集
    
    
    存储引擎 engine=1\2
    1 Myisam  速度快 不支持事务 回滚    
    2 Innodb  速度慢 支持事务,回滚
    
        ①开启事务          start transaction
        ②运行sql;          
        ③提交,同时生效\回滚 commit\rollback
    
    触发器 trigger
        监视地点:表
        监视行为:增 删 改
        触发时间:after\before
        触发事件:增 删 改
    创建触发器语法
        create trigger tgName
        after/before insert/delete/update 
        on tableName
        for each row
        sql; -- 触发语句
    
      删除触发器:drop trigger tgName;
    
     索引
     提高查询速度,但是降低了增删改的速度,所以使用索引时,要综合考虑.
     索引不是越多越好,一般我们在常出现于条件表达式中的列加索引.
     值越分散的列，索引的效果越好
    
     索引类型
     primary key主键索引
     index 普通索引
     unique index 唯一性索引
     fulltext index 全文索引

# 查询知识 #

注:以下查询基于ecshop网站的商品表(ecs\_goods)  
在练习时可以只取部分列,方便查看.

## 1. 基础查询 where的练习: ##

查出满足以下条件的商品  
1. 主键为32的商品

select goods_id,goods_name,shop_price 
     from ecs_goods
     where goods_id=32;

2. 不属第3栏目的所有商品

select goods_id,cat_id,goods_name,shop_price  from ecs_goods
     where cat_id!=3;

3. 本店价格高于3000元的商品

select goods_id,cat_id,goods_name,shop_price  from ecs_goods
     where shop_price >3000;

4. 本店价格低于或等于100元的商品

select goods_id,cat_id,goods_name,shop_price  from ecs_goods where shop_price <=100;

5. 取出第4栏目或第11栏目的商品(不许用or)

select goods_id,cat_id,goods_name,shop_price  from ecs_goods
     where cat_id in (4,11);

6. 取出100<=价格<=500的商品(不许用and)

select goods_id,cat_id,goods_name,shop_price  from ecs_goods
     where shop_price between 100 and 500;

7. 取出不属于第3栏目且不属于第11栏目的商品(and,或not in分别实现)

select goods_id,cat_id,goods_name,shop_price  from ecs_goods where cat_id!=3 and cat_id!=11;
    
    select goods_id,cat_id,goods_name,shop_price  from ecs_goods where cat_id not in (3,11);

8. 取出价格大于100且小于300,或者大于4000且小于5000的商品

select goods_id,cat_id,goods_name,shop_price  from ecs_goods where shop_price>100 and shop_price <300 or shop_price >4000 and shop_price <5000;

9. 取出第3个栏目下面价格<1000或>3000,并且点击量>5的系列商品

select goods_id,cat_id,goods_name,shop_price,click_count from ecs_goods where cat_id=3 and (shop_price <1000 or shop_price>3000) and click_count>5;

10. 取出第1个栏目下面的商品(注意:1栏目下面没商品,但其子栏目下有)

select goods_id,cat_id,goods_name,shop_price,click_count from ecs_goods
     where cat_id in (2,3,4,5);

11. 取出名字以”诺基亚”开头的商品

select goods_id,cat_id,goods_name,shop_price  from ecs_goods     where goods_name like '诺基亚%';

12. 取出名字为”诺基亚Nxx”的手机

select goods_id,cat_id,goods_name,shop_price  from ecs_goods where goods_name like '诺基亚N__';

13. 取出名字不以”诺基亚”开头的商品

select goods_id,cat_id,goods_name,shop_price from ecs_goos
     where goods_name not like '诺基亚%';

14. 取出第3个栏目下面价格在1000到3000之间,并且点击量>5 “诺基亚”开头的系列商品

select goods_id,cat_id,goods_name,shop_price  from ecs_goods where cat_id=3 and shop_price>1000 and shop_price <3000 and click_count>5 and goods_name like '诺基亚%';

15. 一道面试题

有如下表和数组
    把num值处于[20,29]之间,改为20
    num值处于[30,39]之间的,改为30
    
    mian表
    +------+
    | num  |
    +------+
    |    3 |
    |   12 |
    |   15 |
    |   25 |
    |   23 |
    |   29 |
    |   34 |
    |   37 |
    |   32 |
    |   45 |
    |   48 |
    |   52 |
    +------+

16. 练习题:  
把good表中商品名为’诺基亚xxxx’的商品,改为’HTCxxxx’,  
提示:大胆的把列看成变量,参与运算,甚至调用函数来处理 .  
substring(),concat()

## 2.分组查询group: ##

1.  查出最贵的商品的价格
    
        select max(shop_price) from ecs_goods;
2.  查出最大(最新)的商品编号
    
        select max(goods_id) from ecs_goods;
3.  查出最便宜的商品的价格
    
        select min(shop_price) from ecs_goods;
4.  查出最旧(最小)的商品编号
    
        select min(goods_id) from ecs_goods;
5.  查询该店所有商品的库存总量
    
        select sum(goods_number) from ecs_goods;
6.  查询所有商品的平均价
    
        select avg(shop_price) from ecs_goods;
7.  查询该店一共有多少种商品
    
        select count(*) from ecs_goods;
8.  查询每个栏目下面
    
        最贵商品价格
        最低商品价格
        商品平均价格
        商品库存量
        商品种类
        提示:(5个聚合函数,sum,avg,max,min,count与group综合运用)
        select cat_id,max(shop_price) from ecs_goods  group by cat_id;

## 3. having与group综合运用查询: ##

1.  查询该店的商品比市场价所节省的价格
    
        select goods_id,goods_name,market_price-shop_price as j  from ecs_goods ;
2.  查询每个商品所积压的货款(提示:库存\*单价)
    
        select goods_id,goods_name,goods_number*shop_price  from ecs_goods
3.  查询该店积压的总货款
    
        select sum(goods_number*shop_price) from ecs_goods;
4.  查询该店每个栏目下面积压的货款.
    
        select cat_id,sum(goods_number*shop_price) as k from ecs_goods group by cat_id;
5.  查询比市场价省钱200元以上的商品及该商品所省的钱(where和having分别实现)
    
        select goods_id,goods_name,market_price-shop_price  as k from ecs_goods where market_price-shop_price >200;
        
        select goods_id,goods_name,market_price-shop_price  as k from ecs_goods having k >200;
6.  查询积压货款超过2W元的栏目,以及该栏目积压的货款
    
        select cat_id,sum(goods_number*shop_price) as k from ecs_goods group by cat_id having k>20000
7.  where-having-group综合练习题
    
        有如下表及数据
        +------+---------+-------+
        | name | subject | score |
        +------+---------+-------+
        | 张三 | 数学    |    90 |
        | 张三 | 语文    |    50 |
        | 张三 | 地理    |    40 |
        | 李四 | 语文    |    55 |
        | 李四 | 政治    |    45 |
        | 王五 | 政治    |    30 |
        +------+---------+-------+
        
        要求:查询出2门及2门以上不及格者的平均成绩
        
        ## 一种错误做法
        mysql> select name,count(score<60) as k,avg(score) from stu group by name having k>=2;
        +------+---+------------+
        | name | k | avg(score) |
        +------+---+------------+
        | 张三     | 3 |    60.0000 |
        | 李四     | 2 |    50.0000 |
        +------+---+------------+
        2 rows in set (0.00 sec)
        
        mysql> select name,count(score<60) as k,avg(score) from stu group by name;
        +------+---+------------+
        | name | k | avg(score) |
        +------+---+------------+
        | 张三     | 3 |    60.0000 |
        | 李四     | 2 |    50.0000 |
        | 王五     | 1 |    30.0000 |
        +------+---+------------+
        3 rows in set (0.00 sec)
        
        mysql> select name,count(score<60) as k,avg(score) from stu group by name having k>=2;
        +------+---+------------+
        | name | k | avg(score) |
        +------+---+------------+
        | 张三     | 3 |    60.0000 |
        | 李四     | 2 |    50.0000 |
        +------+---+------------+
        2 rows in set (0.00 sec)
        
        #加上赵六后错误暴露
        mysql> insert into stu 
            -> values 
            -> ('赵六','A',100),
            -> ('赵六','B',99),
            -> ('赵六','C',98);
        Query OK, 3 rows affected (0.05 sec)
        Records: 3  Duplicates: 0  Warnings: 0
        
        #错误显现
        mysql> select name,count(score<60) as k,avg(score) from stu group by name having k>=2;
        +------+---+------------+
        | name | k | avg(score) |
        +------+---+------------+
        | 张三 | 3 |    60.0000 |
        | 李四 | 2 |    50.0000 |
        | 赵六 | 3 |    99.0000 |
        +------+---+------------+
        3 rows in set (0.00 sec)
        
        #正确思路,先查看每个人的平均成绩
        mysql> select name,avg(score) from stu group by name;
        +------+------------+
        | name | avg(score) |
        +------+------------+
        | 张三 |    60.0000 |
        | 李四 |    50.0000 |
        | 王五 |    30.0000 |
        | 赵六 |    99.0000 |
        +------+------------+
        4 rows in set (0.00 sec)
        
        mysql> # 看每个人挂科情况
        mysql> select name,score < 60 from stu;
        +------+------------+
        | name | score < 60 |
        +------+------------+
        | 张三 |          0 |
        | 张三 |          1 |
        | 张三 |          1 |
        | 李四 |          1 |
        | 李四 |          1 |
        | 王五 |          1 |
        | 赵六 |          0 |
        | 赵六 |          0 |
        | 赵六 |          0 |
        +------+------------+
        9 rows in set (0.00 sec)
        
        mysql> #计算每个人的挂科科目
        mysql> select name,sum(score < 60) from stu group by name;
        +------+-----------------+
        | name | sum(score < 60) |
        +------+-----------------+
        | 张三 |               2 |
        | 李四 |               2 |
        | 王五 |               1 |
        | 赵六 |               0 |
        +------+-----------------+
        4 rows in set (0.00 sec)
        
        #同时计算每人的平均分
        mysql> select name,sum(score < 60),avg(score) as pj from stu group by name;
        +------+-----------------+---------+
        | name | sum(score < 60) | pj      |
        +------+-----------------+---------+
        | 张三 |               2 | 60.0000 |
        | 李四 |               2 | 50.0000 |
        | 王五 |               1 | 30.0000 |
        | 赵六 |               0 | 99.0000 |
        +------+-----------------+---------+
        4 rows in set (0.00 sec)
        
        #利用having筛选挂科2门以上的.
        mysql> select name,sum(score < 60) as gk ,avg(score) as pj from stu group by name having gk >=2; 
        +------+------+---------+
        | name | gk   | pj      |
        +------+------+---------+
        | 张三 |    2 | 60.0000 |
        | 李四 |    2 | 50.0000 |
        +------+------+---------+
        2 rows in set (0.00 sec)

## 4. order by 与 limit查询 ##

1.  按价格由高到低排序
    
        select goods_id,goods_name,shop_price from ecs_goods order by shop_price desc;
2.  按发布时间由早到晚排序
    
        select goods_id,goods_name,add_time from ecs_goods order by add_time;
3.  接栏目由低到高排序,栏目内部按价格由高到低排序
    
        select goods_id,cat_id,goods_name,shop_price from ecs_goods order by cat_id ,shop_price desc;
4.  取出价格最高的前三名商品
    
        select goods_id,goods_name,shop_price from ecs_goods order by shop_price desc limit 3;
5.  取出点击量前三名到前5名的商品
    
        select goods_id,goods_name,click_count from ecs_goods order by click_count desc limit 2,3;

## 5. 连接查询 ##

1.  取出所有商品的商品名,栏目名,价格
    
        select goods_name,cat_name,shop_price from ecs_goods left join ecs_category on ecs_goods.cat_id=ecs_category.cat_id;
2.  取出第4个栏目下的商品的商品名,栏目名,价格
    
        select goods_name,cat_name,shop_price from ecs_goods left join ecs_category on ecs_goods.cat_id=ecs_category.cat_id where ecs_goods.cat_id = 4;
3.  取出第4个栏目下的商品的商品名,栏目名,与品牌名
    
        select goods_name,cat_name,brand_name from ecs_goods left join ecs_category on ecs_goods.cat_id=ecs_category.cat_id left join ecs_brand on ecs_goods.brand_id=ecs_brand.brand_id where ecs_goods.cat_id = 4;
4.  用友面试题

## 6. union查询 ##

1.  把ecs\_comment,ecs\_feedback两个表中的数据,各取出4列,并把结果集union成一个结果集.
2.  3期学员碰到的一道面试题

## 7. 子查询: ##

1.  查询出最新一行商品(以商品编号最大为最新,用子查询实现)
    
        select goods_id,goods_name from ecs_goods where goods_id =(select max(goods_id) from ecs_goods);
2.  查询出编号为19的商品的栏目名称(用左连接查询和子查询分别)
3.  用where型子查询把ecs\_goods表中的每个栏目下面最新的商品取出来
    
        select goods_id,goods_name,cat_id from ecs_goods where goods_id in (select max(goods_id) from ecs_goods group by cat_id);
4.  用from型子查询把ecs\_goods表中的每个栏目下面最新的商品取出来
    
        select * from (select goods_id,cat_id,goods_name from ecs_goods order by goods_id desc) as t group by cat_id;
5.  用exists型子查询,查出所有有商品的栏目
    
        select * from category where exists (select * from goods where goods.cat_id=category.cat_id);

创建触发器:

CREATE  trigger tg2
    after insert on ord
    for each row
    update goods set goods_number=goods_number-new.num where id=new.gid
    
    CREATE trigger tg3
    after delete on ord
    for each row
    update goods set goods_number=good_number+old.num where id=old.gid
    
    
    CREATE  trigger tg4
    after update on ord
    for each row
    update goods set goods_number=goods_number+old.num-new.num where id=old.gid