史上最全的mysql基础教程

作者：智障二百五

转载链接：https://blog.csdn.net/weixin_45108087/article/details/102766281

进入mysql命令shell:

mysql -h 主机 -P 端口号 -u 用户名 -p

回车后输入密码即可。

[root@localhost ~]# mysql -h 127.0.0.1 -P 3306 -u root -p

如果-h参数没有默认本机，-P(大写的)没有，默认是3306:

mysql -u root -p

进入到shell后。

一、数据库相关操作：

查询所有数据库:
mysql> show databases;

创建数据库（tests）并编码（utf-8）：
mysql> create database tests character set utf8 collate utf8_general_ci;

选择(使用)数据库:
mysql> use tests;

查询当前正在使用的数据库名称
mysql> select database();

创建数据库:
mysql> create database 数据库名称;

判断数据库不存在再创建: 
mysql> create database if not exists 数据库名;

删除数据库:
mysql> drop database 数据库名称;

判断数据库存在再删除:
mysql> drop database if exists 数据库名称;

二、数据表相关操作：

查看所有数据表：
mysql> show tables;

创建数据表：
mysql> create table 表名 (
	ID int not null primary key, # 不为空，设为主键
	name varchar(20));

复制数据表：
mysql> create table 表名 like 来源数据表名;

查看表结构：
mysql> desc 数据表;
或
mysql> describe 数据表;

修改表名:
mysql> alter table 表名 rename to 新表名;

修改表的字符集(编码):
mysql> alter table 表名 character set 字符集名称;

添加字段：
mysql> alter table 表名 add 字段 数据类型;

删除列:
mysql> alter table 表名 drop 列名;

删除表
mysql> drop table 表名;
或
mysql> drop table if exists 表名 ;

添加数据:
mysql> insert into 表名(列名1,列名2,...) values(值1,值2,...);

添加多条数据
mysql> insert into 表名(列名1,列名2,...) values(值1,值2,...),(值1,值2,...),(值1,值2,...)...;

删除数据:
mysql> delete from 表名 where 条件

如果有用到自增ID，又想删除全表的话，用delete删除的话有个弊端就是ID还是会从原有的基础上往上类。建议最好使用：
mysql> truncate 表名;
因为该方式效率较高，把原表直接drop掉然后再新建一张一模一样的。所以ID还是从1开始自增。

修改数据：
mysql> update 表名 set 字段1 = 设置值1, 字段2 = 设置值2,... where 条件;
如果不加任何条件，则全表修改。

部分查询数据条件：
SELECT * FROM test WHERE id >= 100;
SELECT * FROM test WHERE id >= 100 AND  id <=100;
SELECT * FROM test WHERE id BETWEEN 100 AND 100;
SELECT * FROM test WHERE id IN (1,3,4);
// 关于NULL
SELECT * FROM test WHERE name = NULL; 错误,因为null值不能使用=或（!=) 判断
SELECT * FROM test WHERE name IS NULL;(正确)
SELECT * FROM test WHERE name  IS NOT NULL;(正确)
// 查询姓林的有哪些？< like>
SELECT * FROM test WHERE NAME LIKE '林%';
// 查询姓名第二个字是中是的人
SELECT * FROM test WHERE NAME LIKE "_中%";
// 查询姓名是三个字的人（注：为三个下划线_）
SELECT * FROM test WHERE NAME LIKE '___';
// 查询姓名中包含中的人
SELECT * FROM test WHERE NAME LIKE '%中%';

三、约束相关

1、主键约束 (primary key)
能够唯一确定一张表中的的一条记录,我们通过给某个字段添加约束, 可以使得这个字段不重复且不为空.

create table user (
	id int primary key auto_increment, // 在创建表时，添加主键约束，并且完成主键自增	
	name varchar(20)
 );
-- 联合主键: 由多个字段联合组成的主键, 只要联合的主键加起来不重复就可以.联合主键中的任何一个字段都不能为空.
create table user2 (
 	id int,
 	name varchar(20),
 	password varchar(20),
 	primary key(id, name)
);

表创建完成后:

添加主键:
alter table user add primary key(id);
或
alter table user modify id int primary key;
删除主键:
alter table user drop primary key;

2、唯一约束：unique 约束修饰的字段的值不可以重复。

create table user1 (
 	id int primary key auto_increment,
  	phone_num varchar(20) unique
  	 );
 create table user2 (
 	id int primary key auto_increment,
  	name varchar(20),
  	unique(id, name) // 表示两个字段在一起不重复就可以
  	 );

也可以在表创建完成后, 通过

添加unique约束:
alter table user3 add unique(phone_num);
或
alter table user3 modify phone_num varchar(20) unique;
删除unique约束:
alter table user3 drop index phone_num;

3、非空约束：not null 修饰的字段不能为空NULL

create table user3 (
	id int primary key auto_increment,
	name varchar(20) not null
	);
删除非空约束:
alter table user3 modify name varchar(20);

4、默认约束
当我们插入字段值时候,如果对应的字段没有插入值,则会使用默认值.如果传入了值,则不会使用默认值.

create table user4(
	id int primary key auto_increment,
	age int default 18,
	name varchar(20) not null
	);

5、外键约束：foreign key

create table 表名(
....
外键列
constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
);
// 班级
create table classes(
	id int primary key,
	name varchar(20)
	);	
// 学生表
create table student (
		id	int primary key,
		name varchar(20),
		class_id int,
		foreign key(class_id) references classes(id)
		);

四、进阶查询

查询所有记录
例如:查询student表中的所有记录.
select * from student;

查询指定字段
例如:查询student中的sname,ssex,class.
select sname,ssex,class from student;

查询教师表中所有的单位即不重复的depart列. <排除重复distinct>
select distinct depart from teacher;

查询score表中成绩在60到80之间的所有记录 <查询区间 between…and…>
select * from score where degree between 60 and 80;
select * from score where degree > 60 and degree < 80;

查询score表中成绩为85,86或88的记录
select * from score where degree in(85, 86, 88);

查询student表中’95031’班或性别为’女’的同学记录. <or 表示或者>
select *from student where class = '95031' or sex = '女';

以class降序查询student表的所有记录 <降序:desc, 升序asc,默认升序(省略)>.
select * from student order by class desc;

以cno升序,degree降序查询score表的所有记录
select * from score order by cno asc,degree desc;

查询"95031’班的学生人数 <统计 count>
select count(*) from student where class = '95031';

查询score表中最高分的学生学号和课程号(子查询)
select sno, cno from score where degree = (select max(degree) from score );其中:select max(degree) from score 先查出最高分.
select sno,cno degree from score order by degree desc limit 0,1;其中:limit第一个数字表示从多少开始,第二个表示多少条.当有多个相同最高分时,容易出bug,不推荐使用这种方式查询.

查询每门课的平均成绩
select cno, avg(degree) from score group by cno;

查询score表中至少有2名学生选修的并以3开头的课程的平均分数.
select cno, avg(degree) from score group by cno having count(cno) >= 2 and cno like '3%';

查询分数大于70, 小于90的sno列.
select sno, degree from score where degree between 70 and 90;

查询所有学生的sname, cno和degree列.
select sname, cno, degree from student, score where student.sno = score.sno;

查询所有学生的sno,cname和degree列
select sno,cname,degree from course ,score where course.cno = score.cno;

查询"95031"班学生每门课的平均分.
select cno, avg(degree) from score where sno in (select sno from student where class = '95031') group by cno;

查询选修"3-105"课程的成绩高于"109"号同学"3-105"成绩的所有同学的记录.
select * from score where cno = '3-105' and degree > (select degree from score where sno = '109' and cno = '3-105');

查询成绩高于学号为"109", 课程号为"3-105"的成绩的所有记录
select * from score where degree > (select degree from score where sno = '109' and cno = '3-105');

查询和学号为108,101的同学同年出生的所有的sno, sname, sbirthday
select *from student where year(sbirthday) in (select year(sbirthday) from student where sno in(108, 101));

查询"张旭"教师任课的学生成绩
select * from score where cno = ( select cno from course where tno = (select tno from teacher where tname = "张旭"));

查询选修某课程的同学人数多于5人的教师姓名.
select tname from teacher where tno = (select tno from course where cno = (select cno from score group by cno having count(*) > 5));

查询存在有85分以上的成绩的课程的cno:
select cno, degree from score where degree > 85;

查询出"计算机系"教师所教课程的成绩表:
select * from score where cno in (select cno from course where tno in (select tno from teacher where depart = "计算机系"));

查询选修编号为"3-105"课程且成绩至少高于选休息编号为"3-245"的同学的cno,sno和degree,并按degree从高到低次序排序.
any 至少一个
select * from score where cno = '3-105' and degree > any(select degree from score where cno = '3-245') order by degree desc;

查询选修编号为"3-105"课程且成绩高于选休息编号为"3-245"的同学的cno,sno和degree,并按degree从高到低次序排序.
all 表示所有
select * from score where cno = '3-105' and degree > all(select degree from score where cno = '3-245') order by degree desc;

查询所有教师和同学的name, sex和birthday
select tname as name, tsex as sex, tbirthday as birthday from teacher union select sname, ssex, sbirthday from student;

查询所有"女"教师和"女"同学的name,sex和birthday
select tname as name, tsex as sex, tbirthday as birthday from teacher where tsex = '女' union select sname, ssex, sbirthday from student where ssex = '女';

查询成绩比该课程成绩低的同学的成绩表
思路: 从a表查出对应的分数跟b表筛选出来的平均分作比较.
select * from score a where degree < (select avg(degree) from score b where a.cno = b.cno);
表a

查询所有任课教师的tname和depart
select tname, depart from teacher where tno in (select tno from course);

查询至少有两名男生的班号
select class from student where ssex= '男' group by class having count(*) > 1

查询student表中不姓"王"的同学记录
select * from student where sname not like '王%';

查询student表中每个学生的姓名和年龄
select sname, year(now()) - year(sbirthday)  as '年龄' from student;

查询student表中最大和最小的sbirthday日期值
select max(sbirthday) as '最大', min(sbirthday) as '最小' from student;

以班号和年龄从大到小的顺序查询student表中的全部记录
select * from student order by class desc, sbirthday;

查询"男"教师及其所上的课程
select * from course where tno in (select tno from teacher where tsex = '男');

查询最高分同学的sno, cno和degree列
select * from score where degree = (select max(degree) from score);

查询和李军同性别的所有同学的sname
select sname from student where ssex = (select ssex from student where sname = '李军');

查询和李军同性别并同班 同学sname
select sname from student where ssex = (select ssex from student where sname = "李军") and class = (select class from student where sname = '李军');

查询所有选修"计算机导论"课程的"男"的成绩表
select * from score where cno = (select cno from course where cname = '计算机导论') and sno in(select sno from student where ssex = '男');

五、SQL的四种连接查询

1、内连接
inner join 或者 join, 后面通常跟对一个on表示条件
---- 内联查询: 就是两张表中的数据, 通过某个字段相等,查询出相关记录数据.
<当前表中的cardid与id相同.>
2、外连接
左外连接:左连接 left join 或者 left outer join
---- 左外连接, 会把左边表里面的所有数据取出来, 而右边表中的数据,如果有相等的,就显示出来, 如果没有, 则会补NULL.
3、右外连接:右连接 right join 或者right outer join
----右外连接, 会把右边表里面的所有数据取出来, 而左边表中的数据,如果有相等的,就显示出来, 如果没有, 则会补NULL.
4、全外连接:完全外连接 full join 或者full outer join<mysql不支持full join>

六、要点梳理

where 和 having 的区别？
(1) having通常用在聚合函数前面，对聚合函数进行过滤,（MAX、MIN、COUNT、SUM).having通常和group by 一起连用，因为where不能加在group by的后面.
(2) where 在分组之前进行限定，如果不满足条件，则不参与分组。having在分组之后进行限定，如果不满足结果，则不会被查询出来. where 后不可以跟聚合函数，having可以进行聚合函数的判断。

MYSQL执行语句顺序，严格遵循次顺序，不能改变
select
from
where
group by
having
order by

七、mysql的事务

1、关于事务
mysql中, 事务其实是一个最小的不可分割的工作单元. 事务能够保证一个业务的完整性.
分析:

例如:
a --> -100
update user set money = money - 100 where name = 'a';
b --> +100
update user set money = money + 100 where name = 'b';
-- 实际程序中, 如果只有一条sql语句执行成功了,而另外一条没有执行成功?则会出现前后数据不一致的情况.
update user set money = money - 100 where name = 'a';
update user set money = money + 100 where name = 'b';
在多条sql语句,可能会有同时成功的要求,要么就同时失败.

2、事务控制
(1)事务主要包含自动提交@@autocommit=1;,手动提交commit;和事务回滚rollback;.
(2) mysql默认是开启事务的(自动提交).
----当我们去执行一个sql语句的时候,效果会立即提现出来,且不能回滚.
set autocommit = 0;设置mysql是否自动提交,<0为否, 1为是.>
select @@autocommit;查看mysql的自动提交方式.
commit; 手动提交.
具体事务控制相关参照下面代码分析:
3、手动开启事务
begin和start transaction都可以手动开启一个事务. 也就是说,当我们当前的mysql如果默认的是自动提交模式,则执行rollback进行事务回滚则是无效的. 但是可以通过begin和start transaction手动开启事务.
4、事务的四大特征
A 原子性: 事务是最小的单元, 不可以在分割.
C 一致性: 事务要求, 同一事务中的sql语句必须保证同时成功,同时失败.
I 隔离性: 事务1 和事务2之间是具有隔离性的.
D 持久性: 事务一旦结束(commit,rollback),就不可以返回.
5、事务的隔离性
多个事务之间隔离的，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题.
存在问题：
(1) 脏读：一个事务，读取到另一个事务中没有提交的数据.
(2)不可重复读(虚读)：在同一个事务中，两次读取到的数据不一样.
(3)幻读：一个事务操作(DML)数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改.
read uncommitted; 读未提交的–>产生的问题：脏读、不可重复读、幻读.
read committed; 读已经提交的–>产生的问题：不可重复读、幻读repeatable read; 可以重复读–>产生的问题：幻读
serializable; 串行化<性能特差>
通常是隔离级别越高,性能越差.
(1)查看数据库的隔离级别
mysql默认的隔离级别: REPEATABLE-READ
mysql8.0:
系统级别的:select @@global.transaction_isolation;
会话级别的:select @@transaction_isolation;
mysql5.x:
系统级别的:select @@global.tx_isolation;
会话级别的:select @@tx_isolation;
(2)修改隔离级别
set global tansaction isolation level read uncomitted;

八、数据库的三大范式

1、第一范式
数据表中的所有字段都是不可分割的原子项.初步可以理解为:字段值还可以继续拆分的,就不满足第一范式.
比如某表中有一个address的字段,插入值为"中国陕西省西安市碑林区柏树林11号".该字段值是可以继续拆分的,原则上就不满足第一范式.可以依次拆分为:国家/省/市/区/街道等等.
当然,范式设计的越详细,对某些实际操作可能会更好.但不一定都是好处.<比如对address字段来说,可能拆分开来永远都用不到这么详细的信息,可能就没有拆分的必要.>
2、第二范式
必须是满足第一范式的前提下,第二范式要求,除主键外的每一列都必须完全依赖主键.如果要出现不完全依赖,只可能发生在联合主键的情况下.
3、第三范式
必须先满足第二范式.除开主键列的其他列之间不能有传递依赖关系.