mysql笔记
本帖最后由 广州39期陈浩涛 于 2022-3-1 22:26 编辑一、mysql的特性
体积小，安装简单，维护成本低
开源、免费
使用C++编写
支持多系统部署
二、常见的数据类型、约束
1、常见的数据类型
mysql> create table test ===》 结合创建表，讲解mysql中常见的数据类型
-> (id int(10) primary key auto_increment,
-> name varchar(20) not null,
-> score float(20,2),
-> phone bigint(20) default 13411111111,
-> time date);
auto_increment：只能跟着主键一起使用，需要删除主键
varchar(20):表示数据的类型为可变长字符串类型，一个·英文字母占1个字节，utf-8汉字占3个字节，gbk汉字占两个字节
char(10)：定长字符串，固定占用255字节，不管括号中写多少个字节数，保存时都是固定占255个字节，但是显示时，是按照括号中的字节数显示；
char和varchar的区别：两者都是保存字符串的，char是定长的(255个字节)，varchar是可变长的(0~2^16个字节)，
char的查询性能会更好些，但是不节约磁盘的资源，varchar性能差点，但是节省磁盘资源；
not null：非空约束，不管是否填写内容，它都会占用对应的资源，不会显示为null(啥都没有)
float(20,2)：浮点数，保存小数，20表示可以保存数据长度为20个字节，2表示输入的数字可以保留小数位后两位，本质上占4个字节
double（xx,x）：也是浮点数，但是可以保存更多的浮点数，显示更多的小数位，占8个字节空间
int(10)：只能保存数字4个字节，保存数字的范围 -2^31~2^31 （+-2147483648）
bigint(20) ：表示整数类型为大整数型，占8个字节空间，当保存的数据大于2147483648
default：默认值约束，插入数据不填值会默认填充默认值
date：用来保存日期的，固定格式YYYY-MM-DD
1.
[*]整型：
1.
[*]int：整型，整数数字，只能保存4个字节的数字 -2^31~2^31
1.
[*]当需要保存超过10位数的整数需要使用bigint
1.
[*]bigint：大整型，整数数字，可以保存最大8个字节的数字
1.
[*]浮点型：
1.
[*]float：保存小数4个字节
1.
[*]double：保存小数8个字节
1.
[*]字符串型：
1.
[*]char：定长字符串，固定占255个字节，性能会更好些，但是不节省资源
1.
[*]varchar：可变长字符串，占0~2^16个字节，性能稍微差些，但是节省磁盘资源
1.
[*]日期型：
1.
[*]date：保存日期，固定格式 YYYY-DD-MM
2、常见的约束
1.
[*]primary key ：主键约束，特点：表中主键字段的值不能为空，主键字段的点值不能重复(例如上学时，
班上的同学姓名可以重复，但是学号是唯一而且是必要的)；
1.
[*]not null ：非空约束（必须要填的）；
1.
[*]default：默认值约束()；
1.
[*]auto_increment：自增长约束，只能在主键字段使用，而且只能是数据类型为整数型的去使用（例
如不输入id时，直接跟着原来最大的id加1；
1.
[*]NULL和" " ：NULL是完全不占系统空间，“ ” 虽然没有，但是会占据一字节的空间；
三、常见的sql语句
1、数据库操作：
1.1、初始化设置mysql的密码：mysqladmin -uroot password '密码'
1.2、查看有多少数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema | ==》mysql的基础信息库，定义mysql中其他表的信息，不能删
| mysql | ==》 mysql的管理库，管理mysql数据库系统中的用户和权限，不能删
| test | ==》 mysql的测试库，可以删，但是自己创建的数据库不能以test开头，不能程序识别不了
+--------------------+
1.3、创建数据库：create database 数据库名;
1.4、查询当前连接的是哪个库：select database();
2、通配符
mysql中的模糊匹配去符：
%匹配0个或多个字符
_匹配单个字符
linux中的模糊匹配符
*匹配0个或多个字符
？匹配单个字符
3、表-修改操作
3.1、修改表结构：
1.
[*]altertable 表名 rename 新表名 ==》修改表的名称
1.
[*]alter table 表名change 字段名 新字段名 数据类型、约束等==》修改表的字段名、字段属性
1.
[*]例：alter table test change id sid int(4);
1.
[*]alter table 表名 modify 字段名 数据类型、约束等 after 字段名==》 修改字段属性，并放在某个字段后面
1.
[*]alter table 表名 add新字段名 数据类型、约束等 ==》新增表字段
1.
[*]例：alter table test change class class int(5);
1.
[*]alter table +表名 add +字段名 数据类型 约束 first ==》新增表字段，并放到第一个字段前
1.
[*]alter table +表名 add +字段名 数据类型 约束 after +字段名==》 新增字段，并放到某个字段后面
1.
[*]alter table +表名 add(字段1 数据类型，字段2 数据类型) ==》 新增多个表字段
1.
[*]alter table +表名 drop + 字段==》删除字段
1.
[*]alter table +表名 drop 字段1,drop 字段2==》删除多个字段
change 和modify的区别：
两者都是可以改变表结构，但是使用change时，字段名是要写两次的，modify则只要写一次就好，然后change
可以对字段名直接重命名，modify则不行
删除主键：
因为一张表只有一个主键，所有可以直接通过drop primary key的方式删除，
mysql> alter table test01 drop primary key;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
直接执行删除主键报错，因为这个主键字段还有自增长约束，自增长约束需要有主键约束，所有需要先删除自增长约束，再删除主键约束；
添加主键：
alter table 表名 add primary key(字段名)
alter table 表名 change id id int(4) primary key;
alter table 表名 modify id int(4) primary key;
3.2、修改表数据
update
语法组成：update 表名 set 字段=“值”，字段2=‘值’where条件
例子：update test01 set name = '柳岩',score = 18 where id = 1;
4、表-查询操作
1、select * from 表;==》查询这张表所有字段的值
2、分页查询：limit，要放在sql语句的最后面，结合order by一起使用
select * from 表名 limit 开始行数,要显示几行；
例：select * from test01 limit 4,3; ==》查询第5行开始3行数据
注：计算机中的排序是从0开始算起的，0代表第一行；
3、排序：order by
规则：
1.
[*]不写关键字做默认升序操作；
1.
[*]order by可以对多个字段进行排序，哪个字段排前面，哪个字段的优先级就最高；
order by字段名desc/asc
desc：降序
asc：升序
例：select * from emp order by incoming;
https://note.youdao.com/yws/res/2481/WEBRESOURCE15c7ff0fab8624dab9b6641f745c6e0d
例：select * from emp where incoming is not null order by incoming desc,age desc; ==》 按incoming降序、age字段降 序查看表中的数据
https://note.youdao.com/yws/res/2500/WEBRESOURCE77f73ca8f77279b6ed4d893f720e7a29
4、分组：group by
注意：
(1)、分组查询要查询指定的字段，对什么进行分组
就查询什么字段。因为分组之后，只有字段可以正常匹配
，其他非分组字段(普通字段)是随机出现(一般是表中第一条)；
(2)、使用group by分组之后，进行条件过滤，是不能使用where，
where条件要写在group by之前；
(3)、如果希望在group by分组之后进行条件过滤，则使用having(作
用类似where，但是只能用在group by之后)；
(4)、group by分组一般是结合聚合函数使用，where条件是不能接聚合函数
，having才可以接聚合函数；
例：求每个部门的平均收入，并过滤平均收入大于6000的部门。
解法如下图：
https://note.youdao.com/yws/res/2594/WEBRESOURCE8a133e38f3f26e3d951b76024a55f1c8
面试题：为什么要优先使用解法2的？
解法1：select dept2,avg(incoming) from emp group by dept2 having dept2 <> 103; 先分组再过滤条件
解法2：select dept2,avg(incoming) from emp where dept2<>103 group by dept2; 先过滤条件再分组
答：因为解法2的执行效率更高，执行时间更少。
https://note.youdao.com/yws/res/2597/WEBRESOURCE7398df93466443d681c3aa1b1a386151
面试题：现在有一个科目表，里面只有两个字段：姓名、科目。求表中选择科目大于两个科目的学生姓名。
答：select name from 科目表 group by name having count(科目)>2
4.1、聚合函数：
1.
[*]count：统计函数
select count(*) from 表 ==》统计表中的行数
select count(1) from 表 ==》统计表中的行数，这个查询效率更高
count(科目)
特点：count不会统计NULL值；
https://note.youdao.com/yws/res/2561/WEBRESOURCEf61467364c89f13a20b1326c7a4b7c87
1.
[*]sum：求和函数
1.
[*]avg：平均值函数
1.
[*]max：最大值函数
1.
[*]min：最小值函数
4.2、题目：求每个部门年龄最小的人是谁
4.2.1、错误解法如下图：
https://note.youdao.com/yws/res/2515/WEBRESOURCEae4a958b1add660430a06c6eb75b80cf
错误原因：
因为使用了group by分组查询，dept2供group by 进行分组，是分组字段，可以正确匹配，
min(age)是一个求最小值的聚合函数，是一个聚合函数字段，可以正确匹配，但是name在这条
sql语句中属于普通字段，普通字段一旦和分组或者聚合函数使用，就会出现随机匹配的情况，一般
是随机匹配第一条。（遇到这种要使用嵌套查询）
4.2.2、正确解法:
select * from emp,(select dept2,min(age) as mi from emp group by dept2) a1 where emp.dept2=a1.dept2 and emp.age = a1.mi;
https://note.youdao.com/yws/res/2533/WEBRESOURCEe224f8c8f224b2501bd5e0e32c82cb42
https://note.youdao.com/yws/res/2535/WEBRESOURCE8c0fb0104a408412a056a91fe53c8bb9
5、去重：distinct
例：select distinct(phone) from test01;
5、表-删除操作
面试题：mysql中删除数据有多少种方式？分别有什么特性？
答：
1、delete：可以根据条件删除指定数据，如果不接条件删除时，
可以清空表中数据，但是重新插入数据时，自增长字段还是会在原有
最大值基础上进行累计递增。(只删除表中数据，保留表结构，没有
释放空间)
2、truncate：truncate+表名 ==》直接清空表中的数据，不能接条件删除指定数据。保留了
表结构，重新插入数据时，自增长字段会重新从1开始(清空表中的数据并释放空间，保留
表结构的)
3、drop table +表名 ==》删除整张表的结构和数据，释放空间
6、表-备份操作
备份表 ==》直接在数据库操作的
1、备份表结构
create table 新表 like 旧表
2、备份表数据
备份整个表的数据：
insert into 新表名select * from 旧表名
例：insert into test02 select * from test01;
备份某几个字段的表数据：
insert into 新表名(字段1,字段2)select 字段1,字段2 from 旧表名
四、数据库备份/还原 ==》在linux命令界面执行
备份
mysqldump -u用户 -p密码 要备份的数据库名 > 备份路径/文件名
还原
例：mysqldump -uroot -p123456 dcs30 > ./dcs39.sql
mysql -u用户 -p密码 数据库名 < 文件名
例：mysql -uroot -p123456 dcs40 < dcs39.sql
页:
[1]