MySQL基础

写在前面

本文根据燕十八视频整理

燕十八视频地址：https://www.bilibili.com/video/av19538278?from=search&seid=17562872333520185143

MySQL安装

安装教程参见MySQL 8.0.16安装教程(windows 64位)

最基本语句

1. 连接数据库

mysql -uusename -ppaseword;

2. 库级知识

• 查看、选择数据库

show databases;

use 库名;

• 创建、删除一个数据库

create databases 数据库名 charset utf8

drop database 库名;

注意： MySQL中库名不能修改

3. 表级操作

• 显示库下面的表

show tables;

• 创建简单表

create table stu(
snum int,
sname varchar(10)
)engine myisam charset utf8;

• 删除表

drop table 表名;

• 给表改名

rename table 旧表名 to 新表名;

• 往表里加case

Insert into stu 
values 
(1,'zhangsan'), 
(2,'lisi'),
(3,'wangwu');

• 往表里添加列

alter table stu add score;

• 清空表数据

truncate 表名;

truncate与delect的区别：

• truncate相当于删表再重建一张同样结构的表
• 而delect是从删除所有的层面（结构和数据都删除了）来操作的
• 如果决定全清空，truncate快一些

---

4. 可能出现的问题：

• 中文乱码：

set names gbk;

set names utf8;

• 语句码到一半出错,退出该语句：

\c;

建表

建表基础

建表的过程，就是一个声明列类型的过程

列类型的重要性：
存储同样的是数据，不同的类型，所占据的空间和效率是不一样的

整型

类型	范围
tinyint	(-128，127) or (0,255)
smallint	(-32768,32767)
mediuintint	(-8388608,8388607)
int	亿
bigint	*

声明参数	含义
unsigned	无符号
(M)	位数
zerofill	0填充

注意

• unsigend表示无符号类型
• zerofill只能跟(M)配合
• 意为不够M的位数用0填充

示例：

alter table class2 
add snum 
smallint(5) zerofill 
not null default 0; 

select * from class2;

结果：

insert into class2
 (sname,snum) 
 values 
 ('吕布',1), ('廖化',12);

select * from class2;

结果：

注意： zerofill同时必是unsigned类型

浮点型

• float(M,D):浮点型
• decimal(M,D)：定点型

参数含义：

• M 精度 总位数
• D 标度 保留几位小数

注意：
float有时会损失精度，像账户这样的字段，建议用decimal

示例：

日期型

year的范围：1901-2055 特别的 0000年也可以

字符型

• char
• varchar
• text
• blob

char(M)： 定长字符串 0<=M<=255
varchar(M): 边长字符串 0<=M<=65535

定长优势： 查找行记录时，如果都是定长，完全可以通过行数与行的长度计算出来文件指针的偏移量

定长劣势：

• 对于定长N，无论够不够指定长度，实际都占N个长度
• 如果不到N个长度，用空格在末尾补齐N个长度，浪费空间

其他特点：

• 对于char型,如果不够N个字符,内部用空格补齐，取出时再把右侧空格删掉，这意味着如果右侧本身有空格，将会丢失
• 而对于varchar型，不用空格补齐，但列内容前，有1-2个字节来标志该列的内容长度

注意：
char(M),varchar(M)中的M限定的是字符，不是字节 即char(2)charset utf8能存两格字符 比如"中国"

text:文本类型
可以存比较大的文本段，如文章内容、新闻内容等

特点：
搜索速度稍慢 如果不是特别大的内容，建议用char ,varchar来代替
声明text列时，不必给默认值(也就是没有not null default语句)

Blob：二进制类型
用来存储音频等二进制信息

意义：
2进制意味着0-255都有可能出现，blob在于防止因为字符集的问题导致信息丢失

建表前分析

现在我们要建一个会员信息表mermber
首先对各列的类型进行设计：

优化方法： 时间与空间互换

分析： 这张表除了usename和intro列之外，每一列都是定长的，我们不妨让其所有列都定长，可以极大提高查询速度

• Usename char(20)会造成空间的浪费，但是提高了速度
• Intro char（1500）却浪费的太多了，另一方面，人的简介，一旦注册改的频率不高 我们可以把intro列单独拿出来，另放一张表里
  
• 在开发中，会员的信息往往把频繁使用的信息优先考虑效率，存储到一张表
• 不常用的信息和占据空间信息，优先考虑空间占用，存储到辅表中

建表语法

所谓建表就是一个声明列的过程

Create table 表名( 
列名1 列类型1 列1参数, 
列名2 列类型2 列2参数, 
… 
… 
列名n 列类型n 列n参数
 )engine myisam/innodb/bdb charset utf8/gbk

查看表结构：

desc 表名;

增删改查

增（insert）

要素：

• 哪张表
• 哪几列
• 分别是什么值

插入所有列：

Insert into class
(id,sname,gender,company,salary,fanbu) 
Values
(1,'张三','男','百度',8888.67,234);

插入部分列：

Insert into class 
(sname,gender,salary) 
Values 
('小红','女',9999.00);

插入多行：

Insert into class 
(sname,company,salary)
 values 
 ('刘备','皇室成员',13.56), 
 ('孙策','江东集团',28.56), 
 ('曹操','宦官集团',99.56);

注意：

• 后面两个例子没有插入ID，但插入的行也会有ID，这是因为ID是自增型
• 如果插入所有列，则可以不声明插入的列

提醒：
当没有声明插入列时，自增型的列也需要赋值，列与值需要一一对应

删（delete）

要素：

• 哪张表
• 哪几行

语句：

delete from 表名 where 表达式;

注意：

delete from 表; # 会把表中所有行删除

改（updata）

要素：

• 哪张表
• 哪几列
• 分别改为什么值
• 对哪些行生效

示例；

update class 
set 
gender = '女', 
company = '千度' 
Where salary >9000;

注意：

• where 后面接表达式
• 对某行表达式为真，则将改行发挥作用

注意： 这里所有的行都要改

Update class Set fanbu =99 Where 1;

查（select）

要素:

• 表
• 列
• 行

部分行，部分列

select sname ,company,salary from class where id =6;

部分行，所有列

select * from class where id >=3;

所有行，部分列

select sname, company ,salary from class ;

暴力查询：

select * from 表名;

解释：

• *表示所有列，表名后面不加where条件,则选所有行，因此，取出所有行所有列
• 可以观察到，where来筛选行

表的修改

• 增加列
• 删除列
• 修改列

增加列：

Alter table 表名 add 列名称 列类型 列参数 
# 加的列会在表的最后

把新列加到某一列后面：

Alter table 表名 add 列名称 列类型 列参数 after 某一列 # 把新列加在指定列后面

把新列放在第一列：

alter table m1 add pid int not null default 0 first;

删除列：

Alter table 表名 drop 列名;

修改列：

1. 修改列类型：modify

Alter table 表名 modify 列名 新类型 新参数
# 注意这里的列名没改，只是改了类型和参数

2. 修改列名及列类型：change

Alter table 表名 change 旧列名 新列名 新类型 新参数

查询

查询准备工作

首先创建一个goods表：

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 myisam 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);

该数据来自这里

条件查询

比较运算符

1. 查询商品主键为32的商品

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

2. 查出不属于第三个栏目的所有商品

Select goods_id,goods_name,cat_id from goods where cat_id !=3;

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

Select*from goods where shop_price >3000;

4. 取出价格在100-500之间的商品

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

5. 取出第4栏目和第11栏目的商品

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

总结：

• between A and B 是A到B的闭区间
• In后面接的是集合，即离散的取值

逻辑运算符

1. 取出不属于第3栏目且不属于第11栏目的商品

select goods_id,goods_name,shop_price from goods where cat_id not in (3,11);

模糊查询

%匹配任意字符

1. 取出名字以“诺基亚”开头的商品：

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

“_”匹配单个字符

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

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

查询的理解

1. where条件是表达式，在哪一行上表达式为真，哪一行就取出来
   比如：

• where 1;会把所有行取出来
• where 1<2声明都取不出来

2. 把列看成变量，可以运算

示例：

1. 用市场价减去本店价格得到折扣dischout
   
2. 查出低于市场价200以上的商品：
   注意： where后面不能直接对dischout使用表达式，因为where只能对原表中的列进行筛选，而idschout列可以看成是temp列
   
3. 有一列num为下列值，如何把
   num处于[20,29]之间的，改为20
   num处于[30，30]之间的,改为30

3
12
25
23
29
34
37
32
45
48
52

做法如下：

Update face1 
set 
num=floor(num/10)*10 
where num>=20 and num <=39;

分组查询：group by与统计函数

• max
• min
• sum
• avg
• count
  这些函数单独使用意义不大，要和group by结合起来使用

示例:

1. 查出最贵的商品的价格：

select max(shop_price) from ecs_goods;

2. 查询该店共有多少种商品:

select count(*) from ecs_goods;

注意：

• Count(*)就数绝对行数，哪怕某一行所有字段都为null，也计算在内
• Count(列名)查询的是该列不为null的所有行的行数

3. 查询该店每个栏目下积压的货款：
   

筛选：having

前面我们提到where只能对原表中的列进行操作，而having可以对temp列进行操作

示例：
查询比市场价省200元以上的商品及该商品所省的钱:

# where实现
select goods_id,goods_name,market_price-shop_price as k from ecs_goods 
where market_price-shop_price >200; 

# having实现
select goods_id,goods_name,market_price-shop_price as k from ecs_goods 
having k >200;

综合练习题：
有如下表及数据，
要求：查询出两门及两门以上不及格者的平均成绩
第一步： 查询所有人的平均分

select name,avg(score) from result group by name;

第二步： 计算出每个人挂科的情况

Select name ,subject,score,score<60 as g from result;

第三步： 挂科数目就是sum(g)

Select name ,avg(score),sum(score<60) as gks from result group by name;

第四步： 综合以上三步

Select name ,avg(score),sum(score<60) as gks from result group by name having gks>=2;

排序和限制：order by&limit

排序要针对最终结果集，即order by 要放在where / group by / having 后面

多字段排序：

order by 列一,列二;

limit放在语句最后，起到限制条目的作用：

limit [offset] N

# offset:偏移量
# N:取出条目(不包括偏移量)
# offset 如果不写，则相当于limit 0,N;

示例：

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

select goods_id,cat_id,goods_name,shop_price from ecs_goods order by cat_id ,shop_price desc;

2. 取出点击量第三到第五名的商品：

select goods_id,goods_name,click_count from ecs_goods order by click_count desc limit 2,3;

子查询

where型

where 型子查询:
内层sql的返回值在where后作为条件表达式的一部分

示例:

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

注意：

• 若where列 = (内层sql) 则内层sql返回的必是单行单列，单个值
• 若where列 in (内层sql) 则内层sql返回单列,可以多行

from型

新版本好像有点问题，待处理

exists型

exists型子查询：
把外层sql的结果，拿到内层sql去测试如果内层sql成立，则将该行取出

数据准备：

create table category ( 
cat_id smallint unsigned auto_increment primary key, cat_name varchar(90) not null default '', 
parent_id smallint unsigned 
)engine myisam 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);

数据来自 https://www.cnblogs.com/lms520/p/5427685.html

从category表中可以看到cat_id从1-15（少了10）：
但从goods表中我们看到没有cat_id不是那么全
如何从category表中把goods表中存在的cat_id取出来呢？

左连接

左连接可以看成列合并

1. 取出所有商品的商品名,栏目名,价格

 select goods_name,cat_name,shop_price 
 from goods
  left join category on goods.cat_id=category.cat_id;

2. 取出第4个栏目下的商品的商品名,栏目名,价格

select goods_name,cat_name,shop_price 
from goods
 left join category 
 on goods.cat_id=category.cat_id 
 where goods.cat_id = 4; 

3. 取出第4个栏目下的商品的商品名,栏目名,与品牌名

select goods_name,cat_name,brand_name 
from goods
 left join category on goods.cat_id=category.cat_id 
 left join brand on goods.brand_id=brand.brand_id where goods.cat_id = 4;

union

合并两条或多条语句的结果（可以看成结果集的行合并）

示例：
常见疑问：

• 两次的列名称不一样，还能否union？
  答：能，以第一个sql的列名为准

• union什么时候能用？
  答：只要结果集中的列数一致就可以，列的类型不一致时合并的意义要看具体情况

• union后的行能不能排序呢？
  答：可以，order by是针对合并后的结果集进行排序
  示例：
  - 如果union后的结果有重复(即某两行或N行，所有的列，值都一样)怎么办？
  答：这种情况比较常见，默认去重，不想去重使用 union all

使用order by 的注意事项:

1. 内层的order by 语句单独使用时，不会影响结果集，在执行期间会被mysql的代码分析器优化掉，内层的order by 必须能够影响结果集时，才有意义，比如，配合limt使用
2. Sql1 和sql2有时候要用括号括起来

union例子

A表:
±-----±-----+
| id | num |
±-----±-----+
| a | 5 |
| b | 10 |
| c | 15 |
| d | 10 |
±-----±-----+

B表:
±-----±-----+
| id | num |
±-----±-----+
| b | 5 |
| c | 15 |
| d | 20 |
| e | 99 |
±-----±-----+

要求查询出以下效果:
±-----±---------+
| id | sum(num) |
±-----±---------+
| a | 5 |
| b | 15 |
| c | 30 |
| d | 30 |
| e | 99 |
±-----±---------+

第一步：
行合并两张表 ,注意all的作用

select * from ta 
union all
select * from tb;

±-----±-----+
| id | num |
±-----±-----+
| a | 5 |
| b | 10 |
| c | 15 |
| d | 10 |
| b | 5 |
| c | 15 |
| d | 20 |
| e | 99 |
±-----±-----+

第二步：
对第一步生成的表进行操作，按id对num进行求和即可

合起来写:

select id,sum(num) 
from (select * from ta union all select * from tb) as tmp 
group by id; 

±-----±---------+
| id | sum(num) |
±-----±---------+
| a | 5 |
| b | 15 |
| c | 25 |
| d | 30 |
| e | 99 |
±-----±---------+
5 rows in set (0.00 sec)

函数

未完待续