欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

Mysql复习资料整理

程序员文章站 2022-06-16 21:44:28
...

基础知识:

1.数据库的连接

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

MySQL 默认端口号 3306 编码方式utf8 超级用户root 客户端localhost

Myql规范
关键字和函数名称全部大写 
数据库名称、表名称、字段名称全部小写 
SQL语句必须以“;”结尾,也可以通过’ DELIMITER 符号‘语句来自已定义结尾符号 

SQL标准语法“{ }表示必选项,“[ ]”表示可选项,“|”表示从中选择其一 

开启(版本号5.7)
c:\Windows\system32>net start mysql56
停止

c:\Windows\system32>net stop mysql56

WIN+R快捷键打开运行命令,并输入services.msc,手动启动mysql56服务

登录
c:\Windows\system32>mysql -uroot -p
输入密码登录
or
c:\Windows\system32>mysql -u用户名 -p密码 -P端口名称 -h服务器地址
(-uroot, -p密码, -P默认端口3306,-h本地地址127.0.0.1)

更改密码
c:\Windows\system32>mysqladmin -u用户名 -p旧密码 password 新密码

版本号
c:\Windows\system32>mysql -V
or
mysql> SELECT VERSION();

更改提示符PROMPT
mysql> PROMPT \u @ \h \d>
root @ localhost (none) >

退出
mysql> exit;
mysql> quit;
mysql> \q;

显示日期
mysql> SELECT NOW();
显示当前用户
mysql> SELECT USER();

2.库级知识

  1. 显示数据库: show databases;
  2. 选择数据库: use dbname;
  3. 创建数据库: create database dbname charset utf8;
  4. 删除数据库: drop database dbname;
  5. 显示当前数据库: select database();
  6. 打开数据库 test: use test;
  7. 查看警告:SHOW WARNINGS;  
  8. 查看当前服务器的数据库列表:SHOW DATABASES; 
  9. 显示数据库创建时的指令:SHOW CREATE DATABASE t1;

【创建数据库】create database dbname charset utf8;
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [DEFAULT]CHARACTER SET [=] charset_name;

mysql> CREATE DATABASE t1; //默认编码方式utf8
mysql> CREATE DATABASE IF NOT EXISTS t1; //若存在t1则警告
mysql> CREATE DATABASE IF NOT EXISTS t2 CHARACTER SET gbk;// t2为gbk编码方式

【修改数据库】alter database;
ALTER {DATABASE|SCHEMA} [db_name] [DEFAULT] CHARACTER SET [=]charset_name;

mysql> ALTER DATABASE t2 CHARACTER SET = utf8; //修改编码方式

【删除数据库】drop database dbname;
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name;

mysql> DROP DATABASE IF EXISTS t2;

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;

"id int unsigned not null auto_increment primarykey" 行进行介绍:

·"id" 为列的名称;

·"int" 指定该列的类型为 int(取值范围为 -83886088388607), 在后面我们又用 "unsigned" 加以修饰, 表示该类型为无符号型, 此时该列的取值范围为 016777215;

·"not null" 说明该列的值不能为空, 必须要填, 如果不指定该属性, 默认可为空;

·"auto_increment" 需在整数列中使用, 其作用是在插入数据时若该列为 NULL, MySQL将自动产生一个比现存值更大的唯一标识符值。在每张表中仅能有一个这样的值且所在列必须为索引列。

·"primary key" 表示该列是表的主键, 本列的值必须唯一, MySQL将自动索引该列。

5. 修改表

    这里关于MySQL中ALTER ,CHANGE , MODIFY做一下总结

    (1)既更改列名也更改类型,用CHANGE 

    (2)只修改类型,用MODIFY

  1. 修改表之增加列:

         alter table tbName 
         add 列名称1 列类型 [列参数] [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. 修改表约束为空

    ALTER TABLE `tableName` MODIFY COLUMN `columnName` char(10) default NULL;
  9. 清空表的数据

         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
实存字符i 实占空间 利用率
char(M) 0<=i<=M M i/m<=100%
varchar(M) 0<=i<=M i+1,2 i/i+1/2<100%
  • 日期时间类型

    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 特性:不用赋值,该列会为自己赋当前的具体时间 

详细数据类型资料可以查考 https://blog.csdn.net/Frank_Hsu_/article/details/79248022

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 :注having 子句在分组聚合函数后对组的记录进行筛选条件的添加
  4. 排序 order by :对条件进行排序进行排序时使用 asc 升序/从小到大 ,desc降序 ,对多个条件排序是逗号隔开即可,可以参考下面的例子。
  5. 限制 limit,起始位置,取多少条数 ,主要用来实现分页功能  注:索引是从0开始。
    SELECT * FROM TableName LIMIT startRow,pageSize;
  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) 

http://book.ecmoban.com/

DROP TABLE IF EXISTS `ecs_goods`;
CREATE TABLE `ecs_goods` (
  `goods_id` mediumint(8) NOT NULL COMMENT '商品id',
  `cat_id` smallint(5) NOT NULL COMMENT '商品所属商品分类id,取值category的cat_id',
  `goods_name` varchar(120) NOT NULL COMMENT '商品的名称',
  `goods_number` smallint(5) NOT NULL COMMENT '商品库存数量',
  `market_price` decimal(10,2) NOT NULL COMMENT '市场售价',
  `shop_price` decimal(10,2) NOT NULL COMMENT '本店售价'
);


在练习时可以只取部分列,方便查看.

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()

17.多字段排序 

按商品类目和店铺价格排序;

select * from ecs_goods order by cat_id,shop_price;

按商品类目递增和店铺价格递减;

select * from ecs_goods order by cat_id asc,shop_price desc;

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

https://blog.csdn.net/znyyjk/article/details/52717373