经验首页 前端设计 程序设计 Java相关 移动开发 数据库/运维 软件/图像 大数据/云计算 其他经验
当前位置:技术经验 » 数据库/运维 » MySQL » 查看文章
Mysql基础知识
来源:cnblogs  作者:Venusoooooo  时间:2021/1/4 9:19:27  对本文有异议

Mysql基础总结(部分参照燕十八老师的教学)

1. 什么是SQL(Structured Query Language)结构查询语句

SQL语句是一种what型语言【想要什么,给你】,语法相对简单易懂

  • SQL语言的划分

    1. DDL(Data Definition Language)-数据库定义语言;用来定义数据库对象、数据表和列;使用DDL创建、删除、修改数据库的表和结构
    2. DML(Data Manipulation Language)-数据库操作语言;操作数据库的相关数据,比如增加、删除、修改表中的数据
    3. DCL(Data Control Language)-数据控制语言;用它来定义访问权限和安全等级
    4. DQL(Data Query Language)-数据查询语言;数据查询语言,用来查询数据
  • SQL语句的执行顺序

    
    select distinct player_id , player_name , count(*) as num     # 顺序5
    from player join team on player.team_id = team.team_id        # 顺序1
    where height > 1.80            # 顺序2
    group by player.team_id        # 顺序3
    having num >2                  # 顺序4
    order by num desc              # 顺序6
    limit 2;                       # 顺序7          
    
    
    • 完整的顺序是
      1. from子句组装数据(包括join
      2. where子句进行条件筛选
      3. group by分组
      4. 使用聚集函数进行计算、数据映射
      5. having筛选分组
      6. 计算所有的表达式
      7. select的字段
      8. order by排序
      9. limit筛选
  • SQL语句的执行流程

    ? Mysql中的流程:SQL语句 → 缓存查询 → 解析器 → 优化器 → 执行器

    1. 查询缓存:Server如何在查询缓存总发现了这条SQL语句,就会直接将结果返回给客户端;如果没有,就进入到解析器阶段。需要说明的是,因为查询缓存往往效率不高,所以在Mysql 8.0 之后就抛弃了这个功能。

    2. 解析器:在解析器中队SQL语句进行语法分析、语义分析

    3. 优化器:在优化器中会确定SQL语句的执行路径,比如是根据全表检索,还是根据索引来检验等

    4. 执行器:在执行之前需要判断改用户是否具备权限,如果具备权限就执行SQL查询并返回结果。在Mysql 8.0以下的版本,如果设置了查询缓存,这时会将查询结果进行缓存。

2. Mysql 架构

  1. Mysql是Client/Server架构,体系架构图如下

  • 由以下几个部分组成
    1. 连接池组件(Connection Pool)
    2. 管理服务和工具组件(Enterprise Management Services & Utilities)
    3. SQL 接口组件(SQL Interface)
    4. 查询分析器(Parser)
    5. 优化器组件(Optimizer)
    6. 缓冲组件(Cache & Buffer)
    7. 插件式储存引擎(Pluggable Storage Engines)
    8. 物理文件(File System , Files & Logs)
  1. 关于储存引擎

    1. InnoDB储存引擎

      Mysql 5.5 版本后默认的储存引擎,优点是支持事务、行级锁、外键约束、支持崩溃后的安全恢复

    2. Myisam储存引擎

      不支持事务和外键,支持全文索引(只对英文有效),特点是查询速度快

    3. Memory储存引擎

      数据放在内存当中(类似memcache)以便得到更快的响应速度,但是崩掉的话数据会丢失

    4. NDB储存引擎

      主要用于Mysql Cluster 分布式集群

    5. Archive储存引擎

      有很好的压缩机制,用于文件文档,写入时会进行压缩

3. 常用库/表操作语句

 1.  mysql -h localhost -u root -p      # 以root用户连接本地数据库
 2.  show databases;                    # 查看Mysql服务中的所有数据库
 3.  create database database_name;     # 创建数据库
 4.  use databases;                     # 更改操作的数据库对象
 5.  \c                                 # 取消执行当前未输入mysql语句
 6.  show tables;                       # 查看该操作数据库对象中所有的数据表名和视图名
 7.  desc table_name/view_name;         # 查看表/视图结构
 8.  truncate table_name;               # 清空表数据【表结构不变】
 9.  delete from table_name;            # 删除表
 10.  show create table table_name;     # 查看建表/视图过程
 11. show table status \G;              # 查看数据库中的所有表信息;\G 竖行显示
 12. show table status where name = table_name \G;     # 指定表
 13. rename table_name;                 # 改表名
 14. drop table/view table_name/view_name;             # 删除表/视图

4. 增删改

1.insert

insert into table_name (column1,column2 ……) values (value1,value2 ……);

2.delete

delete from table_name where …… ;          # where表示指定条件,不用where将针对表整表操作

3.update

update table_name set column1 = new_value , column2 = new_value …… where ……;

4. select

select cloumn1,cloumn2…… from table_name where …… group by …… having …… order by …… limit …… ;        
# where|group by|having|order by|limit 可以没有其中某些项,若有必须按照先后顺序

5.深入理解select

select 是增删改查的重点,也是难点,能否写出高性能的sql语句,select是否熟练占很大一部分

  • 变量,变量就可以进行各种运算,包括算术运算逻辑运算

  • where后面的语句是表达式,表达式的值为 eg:where 1 则恒为真 查询整张表,反之 where 0 恒位假 查询结果位Empty

  • select 语句还可以配合算术运算符逻辑运算符位运算符以及相关函数写出更高性能的查询语句

    • 常用的select用法以goods表为例

      1. 数字筛选

        select goods_id,goods_name,shop_price from goods where shop_price > 300;
        
      2. 字符筛选

        select goods_id,goods_name,shop_price from goods where goods_name = 'kd876';
        
      3. 区间筛选

        select goods_id,goods_name,shop_price from goods where shop_price between 300 and 3000;
        
      4. 多条件筛选

        select goods_id,goods_name,shop_price from goods where shop_price between 300 and 3000 and goods_id > 10 ;
        
      5. 模糊条件筛选

        select goods_id,goods_name,shop_price from goods where goods_name like '诺基亚%';         
        # 通配符% 表示任意多个字符 _ 表示任意单个字符
        
      6. 在字符串组里筛选

        select goods_id,goods_name,shop_price from goods where goods_id in (3,10);      
        # goods_id in(3,10)等价于goods_id = 3 or goods_id = 10 
        
      7. 借助函数优化筛选

         select goods_id,goods_name,shop_price from goods where left(goods_name,2)='kd';  
        # 函数left(a,n)表示在a字符串中从左到右取n个字符
        
      8. 全字符段筛选

        select * from goods;
        
      9. 不重复筛选

        select distinct goods_id,goods_name,shop_price from goods; # distinct 不重复的意思
        
      10. 排序筛选

        select goods_id,goods_name,shop_price from goods where shop_price >300 order by shop_price desc;    
        # asc升序(默认)/ desc 降序   
        
  • 补上后续练习所需要的表格代码

    create table goods (
      goods_id mediumint(8) unsigned primary key auto_increment,
      goods_name varchar(120) not null default '',
      cat_id smallint(5) unsigned not null default '0',
      brand_id smallint(5) unsigned not null default '0',
      goods_sn char(15) not null default '',
      goods_number smallint(5) unsigned not null default '0',
      shop_price decimal(10,2) unsigned not null default '0.00',
      market_price decimal(10,2) unsigned not null default '0.00',
      click_count int(10) unsigned not null default '0'
    ) engine=InnoDB default charset=utf8;
    insert into `goods` values (1,'kd876',4,8,'ecs000000',1,1388.00,1665.60,9),
    (4,'诺基亚n85原装充电器',8,1,'ecs000004',17,58.00,69.60,0),
    (3,'诺基亚原装5800耳机',8,1,'ecs000002',24,68.00,81.60,3),
    (5,'索爱原装m2卡读卡器',11,7,'ecs000005',8,20.00,24.00,3),
    (6,'胜创kingmax内存卡',11,0,'ecs000006',15,42.00,50.40,0),
    (7,'诺基亚n85原装立体声耳机hs-82',8,1,'ecs000007',20,100.00,120.00,0),
    (8,'飞利浦9@9v',3,4,'ecs000008',1,399.00,478.79,10),
    (9,'诺基亚e66',3,1,'ecs000009',4,2298.00,2757.60,20),
    (10,'索爱c702c',3,7,'ecs000010',7,1328.00,1593.60,11),
    (11,'索爱c702c',3,7,'ecs000011',1,1300.00,0.00,0),
    (12,'摩托罗拉a810',3,2,'ecs000012',8,983.00,1179.60,13),
    (13,'诺基亚5320 xpressmusic',3,1,'ecs000013',8,1311.00,1573.20,13),
    (14,'诺基亚5800xm',4,1,'ecs000014',1,2625.00,3150.00,6),
    (15,'摩托罗拉a810',3,2,'ecs000015',3,788.00,945.60,8),
    (16,'恒基伟业g101',2,11,'ecs000016',0,823.33,988.00,3),
    (17,'夏新n7',3,5,'ecs000017',1,2300.00,2760.00,2),
    (18,'夏新t5',4,5,'ecs000018',1,2878.00,3453.60,0),
    (19,'三星sgh-f258',3,6,'ecs000019',12,858.00,1029.60,7),
    (20,'三星bc01',3,6,'ecs000020',12,280.00,336.00,14),
    (21,'金立 a30',3,10,'ecs000021',40,2000.00,2400.00,4),
    (22,'多普达touch hd',3,3,'ecs000022',1,5999.00,7198.80,16),
    (23,'诺基亚n96',5,1,'ecs000023',8,3700.00,4440.00,17),
    (24,'p806',3,9,'ecs000024',100,2000.00,2400.00,35),
    (25,'小灵通/固话50元充值卡',13,0,'ecs000025',2,48.00,57.59,0),
    (26,'小灵通/固话20元充值卡',13,0,'ecs000026',2,19.00,22.80,0),
    (27,'联通100元充值卡',15,0,'ecs000027',2,95.00,100.00,0),
    (28,'联通50元充值卡',15,0,'ecs000028',0,45.00,50.00,0),
    (29,'移动100元充值卡',14,0,'ecs000029',0,90.00,0.00,0),
    (30,'移动20元充值卡',14,0,'ecs000030',9,18.00,21.00,1),
    (31,'摩托罗拉e8 ',3,2,'ecs000031',1,1337.00,1604.39,5),
    (32,'诺基亚n85',3,1,'ecs000032',4,3010.00,3612.00,9);
    
    create table category (
    cat_id smallint unsigned auto_increment primary key,
    cat_name varchar(90) not null default '',
    parent_id smallint unsigned
    )engine=InnoDB charset utf8;
    INSERT INTO `category` VALUES
    (1,'手机类型',0),
    (2,'CDMA手机',1),
    (3,'GSM手机',1),
    (4,'3G手机',1),
    (5,'双模手机',1),
    (6,'手机配件',0),
    (7,'充电器',6),
    (8,'耳机',6),
    (9,'电池',6),
    (11,'读卡器和内存卡',6),
    (12,'充值卡',0),
    (13,'小灵通/固话充值卡',12),
    (14,'移动手机充值卡',12),
    (15,'联通手机充值卡',12);
    
    CREATE TABLE `result` (
      `name` varchar(20) DEFAULT NULL,
      `subject` varchar(20) DEFAULT NULL,
      `score` tinyint(4) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    insert into result
    values
    ('张三','数学',90),
    ('张三','语文',50),
    ('张三','地理',40),
    ('李四','语文',55),
    ('李四','政治',45),
    ('王五','政治',30);
    
    create table a (
    id char(1),
    num int
    )engine=InnoDB charset utf8;
    insert into a values ('a',5),('b',10),('c',15),('d',10);
    
    create table b (
    id char(1),
    num int
    )engine=InnoDB charset utf8;
    insert into b values ('b',5),('c',15),('d',20),('e',99);
    
    create table m(
         mid int,
         hid int,
         gid int,
         mres varchar(10),
         matime date
    )engine=InnoDB charset utf8;
    insert into m
         values
         (1,1,2,'2:0','2006-05-21'),
         (2,2,3,'1:2','2006-06-21'),
         (3,3,1,'2:5','2006-06-25'),
         (4,2,1,'3:2','2006-07-21');
    
    create table t (
         tid int,
         tname varchar(20)
    )engine=InnoDB charset utf8;
    insert into t
         values
         (1,'国安'),
         (2,'申花'),
         (3,'布尔联队');
    
    create table mian ( num int) engine=InnoDB;
    insert into mian values
    (3),
    (12),
    (15),
    (25),
    (23),
    (29),
    (34),
    (37),
    (32);
    
    create table user (
    uid int primary key auto_increment,
    name varchar(20) not null default '',
    age smallint unsigned not null default 0
    ) engine=InnoDB charset utf8;
    
    create table boy (
        hid char(1),
         bname varchar(20)
    )engine=InnoDB charset utf8;
    insert into boy (bname,hid)
         values
         ('屌丝','A'),
         ('杨过','B'),
         ('陈冠希','C');
    
    create table girl (
        hid char(1),
         gname varchar(20)
         )engine=InnoDB charset utf8;
    insert into girl(gname,hid)
         values
         ('小龙女','B'),
         ('张柏芝','C'),
         ('死宅女','D');
    

    ? 注:上述包含的表格有goodscategoryresultabmtmianuserboygirl

?

6.查询练习以goods表为例

  1. 查询出名字为’诺基亚NXX’的手机

    select * from goods where goods_id in(4,11); 
    
  2. 查询出名字不以’诺基亚’开头的商品

    select * from goods where goods_name not like '诺基亚%';
    
  3. 查询出第4和第11行的所有信息

    
    
  4. goods表中商品名为’诺基亚xxxx’改为’HTCxxxx’

    select goods_id, concat('HTC',substring(goods_name,4)) from goods where goods_name like '诺基亚%';     
    # 1. 函数concat(a,b) 将ab两个字符串连接成一个字符串     
    # 2. 函数substring(string,position)从特定位置开始的字符串返回一个给定长度的子字符串
    											
    
  • 小结:当涉及到多重条件查询需要用到运算符、and、or、not……之类来修饰条件时
    * 一定要先弄清楚条件之间的分类
    * 使用( )将其分类,避免优先级之类的问题
  1. 面试题mian表

mian表中处于[20,29]之间的num值改成20,[30,39]之间的num值改成30,一句sql完成。

update mian set num = (floor(num/10)*10) where num between 20 an 39;  # 函数floor(x) 取不大于x的最大整数值

7. group by 分组与统计函数

  1. 常用统计函数

    max()					# 获取最大值
    min()					# 获取最小值
    avg()					# 求取平均值
    sum()					# 求和
    count()				# 计算行数/条数          *特别注意count()返回的是一个总行数
    distinct()		# 求有多少种不同解
    
  2. 另外注意:当出现group by分组种不能配对的情况,该字段取查询时候第一次出现的值

8. having 筛选结果集

having 并不一定 与 where共存(这种情况可以看做类似where 1这种语句可以忽略),但一定在where之后;可以存在只有having而没有where的情况

  1. 查询goods表中商品比市场价低出多少?

    select goods_id ,goods_name,market_price - shop_price from goods ;
    
  2. 查询goods表中商品比市场价低出至少200元的商品

    select goods_id ,goods_name,(market_price - shop_price) as discount from goods where market_price - shop_price ;    
    # 注意为什么where后面不能用 discount
    
    select goods_id,goods_name,(market_price-shop_price) as discount from goods having discount>200;
    

9. where __ group by __ having 综合练习result表

  • 有以上result表,要求查询出2门及2门以上不及格的平均成绩 ※※※经典题目※※※
    • 难点分析:如何找出2门及2门以上不及格的同学
  1. 一种典型错误 ? 【错误点:对count和比较运算两者结合的理解错误

    msyql> select name , count(score<60) as gks , avg(score) as pjf from result group by name having gks >= 2;
    
    +--------+-----+---------+
    | name   | gks | pjf     |
    +--------+-----+---------+
    | 张三   |   3 | 60.0000 |
    | 李四   |   2 | 50.0000 |
    +--------+-----+---------+                
    2 rows in set (0.00 sec)                # 貌似是正确的,但只针对此种情况
    

    验证:假如增加1行数据 values('赵六','语文',88),('赵六','数学',99),('赵六','物理',100)

    再次执行上面的sql语句,将会得到如下结果

    +--------+-----+---------+
    | name   | gks | pjf     |
    +--------+-----+---------+
    | 张三   |   3 | 60.0000 |
    | 李四   |   2 | 50.0000 |
    | 赵六   |   3 | 95.6667 |
    +--------+-----+---------+
    3 rows in set (0.00 sec)       					
    # 很明显有语义上的错误!实际上count(score)和count(score<60)查询出的结果是一样的,函数count()返回的是总行数!
    
  2. 正确解题思路?(逆向思维)

    1. select name ,avg(score),as pjf from result group by name;
    
    +--------+---------+
    | name   | pjf     |
    +--------+---------+
    | 张三   | 60.0000 |
    | 李四   | 50.0000 |
    | 王五   | 30.0000 |
    | 赵六   | 95.6667 |
    +--------+---------+
    4 rows in set (0.00 sec)						# 1. 查询出所有同学的平均分,并分组
    
    2. select name, score<60 from result;
    
    +--------+----------+
    | name   | score<60 |
    +--------+----------+
    | 张三   |        0 |
    | 张三   |        1 |
    | 张三   |        1 |
    | 李四   |        1 |
    | 李四   |        1 |
    | 王五   |        1 |
    | 赵六   |        0 |
    | 赵六   |        0 |
    | 赵六   |        0 |
    +--------+----------+
    9 rows in set (0.00 sec)						
    # 2. 查看每个同学的挂科情况;这里运用了逻辑运算,这个点也很重要!score<60 若真则返回0 若假则返回1
    
    3. select name , sum(score<60) as gks from result group by name;
    
    +--------+------+
    | name   | gks  |
    +--------+------+
    | 张三   |    2 |
    | 李四   |    2 |
    | 王五   |    1 |
    | 赵六   |    0 |
    +--------+------+
    4 rows in set (0.00 sec)						# 3. 计算每位同学的总挂科数
    
    4. select name ,sum(score<60) as gks ,avg(score) as pjf from result group by name having gks >=2;
    
    +--------+------+------------+
    | name   | gks  |     pjf   |
    +--------+------+------------+
    | 张三   |    2 |    60.0000 |
    | 李四   |    2 |    50.0000 |
    +--------+------+------------+
    2 rows in set (0.00 sec)						# 4. 整合1.3步,得到结果集,并筛选出gks大于等于2的同学
    

10. order by 排序(在内存中排序)与limit(限制范围)综合查询goods表

  1. 按栏目由低到高排序,栏目内部按价格由高到低排序

    select goods_id ,goods_name ,shop_price ,cat_id from goods order by cat_id desc,shop_price asc;
    
  2. 取出价格最高的前3名商品

    select goods_id ,goods_name,shop_price from goods order by shop_price desc limit 0,3;    
    # limit x,y 其中x代表起始位置也就是偏移量,y代表返回最大行数;x初始值为0
    
  3. 取出商品市场价前10到25的商品信息

    select goods_id ,goods_name,market_price from goods order by market_price limit 11,15;
    

11. 子查询

mysql子查询是嵌套在另一个查询(如selectinsertupdate或者delete)中的查询。这里重点总结了嵌套在select中的子查询

  1. where 子查询【以内层查询结果通常为变量作为外层查询的比较条件
  • 如何查询每个栏目下面最新的那件产品?
    • 语义解析:栏目列 :cat_id ;最新的那件产品?goods_id 为最大值时所对应的那一件产品
# 1. 陷阱演示 ?
# 思路:最新的商品 max(goods_id);每个栏目 group by cat_id

select max(goods_id) ,goods_name ,cat_id ,shop_price from goods group by cat_id ; 

# 报错:ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated 
# column 'zion.goods.goods_name' which is not functionally dependent on columns in GROUP BY clause;
# this is incompatible with sql_mode=only_full_group_by    大概意思是 语义缺陷,不兼容

#分析:”先查询再排序“  group by cat_id  但是goods_name,shop_price应该取谁的呢?
# 2. 正确方法 ?
# 思路:”先排序再查询“  需要用到子查询/连接查询

# ”先排序“
select max(goods_id),cat_id from goods group by cat_id;

+---------------+--------+
| max(goods_id) | cat_id |
+---------------+--------+
|            16 |      2 |
|            32 |      3 |
|            18 |      4 |
|            23 |      5 |
|             7 |      8 |
|             6 |     11 |
|            26 |     13 |
|            30 |     14 |
|            28 |     15 |
+---------------+--------+
9 rows in set (0.00 sec)

# ”再查询“
select cat_id ,goods_id, goods_name, shop_price from goods where goods_id in (select max(goods_id) from goods group by cat_id);

+--------+----------+----------------------------------------+------------+
| cat_id | goods_id | goods_name                             | shop_price |
+--------+----------+----------------------------------------+------------+
|     11 |        6 | 胜创kingmax内存卡                      |      42.00 |
|      8 |        7 | 诺基亚n85原装立体声耳机hs-82           |     100.00 |
|      2 |       16 | 恒基伟业g101                           |     823.33 |
|      4 |       18 | 夏新t5                                 |    2878.00 |
|      5 |       23 | 诺基亚n96                              |    3700.00 |
|     13 |       26 | 小灵通/固话20元充值卡                  |      19.00 |
|     15 |       28 | 联通50元充值卡                         |      45.00 |
|     14 |       30 | 移动20元充值卡                         |      18.00 |
|      3 |       32 | 诺基亚n85                              |    3010.00 |
+--------+----------+----------------------------------------+------------+
9 rows in set (0.00 sec)


# 分析:1.由此可见 select max(goods_id) ,goods_name ,shop_price from goods  除了goods_id符合题意,其它的在语义上就是存在缺陷的;
# 这是一个有缺陷的语句。  
#      2.列就是变量;把查询这个变量(列)的sql语句作为外层sql语句的比较条件,这么做的目的是为了我们每次更新商品后,都能取得最新的那个商品。 
# 这样也不会出现 列与列不匹配错乱的情况
  • 查询出编号位19的商品的栏目名称[栏目名称放在category表中]

    select cat_id,cat_name from category where cat_id = ( select cat_id from goods where goods_id = 19 );
    
    +--------+-----------+
    | cat_id | cat_name  |
    +--------+-----------+
    |      3 | GSM手机   |
    +--------+-----------+
    1 row in set (0.00 sec)
    
  1. from 子查询 【将查询出来的结果当成一个新”表“来操作

==============未完待续……

原文链接:http://www.cnblogs.com/newcheer/p/14221356.html

 友情链接: NPS