mysql快速入门

mysql是关系型数据库

登录数据库服务器

mysql -uroot -p密码

查询数据库服务器中所有的数据库

show databases;

选中某一个数据库

use 数据库名字;

退出数据库服务器

exit;

在数据库服务器中创建数据库

create database 创建的数据库名;

展示当前所用数据库的所有数据表

show tables;
由于我们刚创建test1所以是空的

创建数据表

create table 数据表名(
键名1 数据类型,
键名2 数据类型,
);

查看创建好的数据表的结构

describe 数据表名;

NULL：是否允许为空
Key：约束相关
Extra：额外的

常用数据类型

mysql支持多种类型，大致分为三类： 数值，日期/时间 ，字符串

添加数据记录

insert into 数据表名 (指定我们下面的值对应的键名，可以不写，括号也是)
values (各个键的值 ，有些可以为NULL),(各个键的值),(各个键的值);

查看数据表中的数据记录

select * from 数据表名;

删除数据记录

delete from 数据表名 where 键名=键值;

修改数据记录

update 数据表名 set 键名=键值 where 键名=键值;

建表约束（建表的时候做的事）

主键约束

通过给某个字段添加约束（修饰某个键），就可以使得该字段不可被重复且非空，且只有一个键可以被主键约束（唯一）

primary key

当我们尝试插入含有相同id值时报错了，就是因为主键约束不允许重复嘛！

当然也不可以为空了

联合主键

create table user2(
id int,
name varchar(20),
password varchar(20),
primary key(id,name));
上面这一行就是联合主键了，就是id和name拼起来不能被重复和为NULL

添加约束

下面两种方式都可以

add

alter table 数据表名 add primary key(待约束的键名);

modify

alter table 数据表名 modify 待约束的键名 待约束键名的type primary key;

删除约束

alter table 数据表名 drop primary key;

自增约束

插入数据时，键值自动加1

外键约束

涉及到父表和子表，子表的值必须在父表中对应存在

父表
create table classes(
id int primary key,
name varchar(20)
);

子表
create table students(
id int primary key,
name varchar(20),
class_id int,
foreign key(class_id) references classes(id)
);
我们创建了父表和子表后，为父表插入数据
这时候因为子表的class_id键指向了父表的id键，因此当我们尝试在子表插入class_id为6的数据时失败了，因为父表中没有，只有1到4

唯一约束

被修饰的键的值不可重复
create table user5(
id int,
name varchar(20) unique);

不一定写在数据类型后面，还可以另起一行unique(键名)
create table user6(
id int,
name varchar(20),
unique(name)
);

id和name拼起来的结果是唯一的，在desc 表;的时候在Key那里会发现有个MUL
create table user7(
id int,
name varchar(20),
unique(id,name)
);

添加约束

add

alter table 数据表名 add unique(待约束的键名);

modify

alter table 数据表名 modify 待约束的键名 待约束的键名的数据类型（字符串要带长度）unique;

删除约束

alter table 数据表名 drop indx 键名;

非空约束

默认是可以为空的，但被not null修饰的字段不可为NULL，
create table user9(
id int,
name varchar(20) not null);

默认约束

被修饰了的键如果没有被传值，就使用默认值，但这时候就一定要在括号里指定传入的键名
create table user10(
id int,
name varchar(20),
age int default 10
);

数据库的三大设计范式

第一范式 1NF

数据表中的所有字段都应当是不可分割的原子值
比如一个地址 中国广东省佛山市顺德区××街道××号
我们就不要这样建表，然后一次性把一大串地址怼进去address字段
create table user10(
id int,
name varchar(20),
address varchar(20)
);

要这样子，尽量地详细，拆分
create table user10(
id int,
name varchar(20),
country varchar(20)，/*中国*/
province varchar(20)， /*广东省*/
city varchar(20)， /*佛山市*/
district varchar(20)， /*顺德区*/
details varchar(20)， /*××街道××号*/
);

设计得越详细，对某些操作更好，但不是全部

第二范式 2NF

必须在满足第一范式的前提下，除主键外的每一列都必须完全依赖主键
如下图，学号是主键，
姓名，系名，系主任完全依赖于学号，（ 只根据学号就能被唯一确认的信息）
分数完全依赖于学号和课名（学号和课名为联合主键）

改造后：

第三范式 3NF

必须先满足第二范式，除开主键列的其他列之间不能有传递依赖关系
我们发现上面改造后的第二张表之间有传递依赖关系，也就是系主任依赖于系名，而系名又依赖于学号，于是还需要把第二张表拆分，最终表如下：

删除数据表

drop table 数据表名;

查询数据表

查询所有字段

select * from 数据表名;
数据表中现有的所有记录都返回

查询部分字段

select 键1,键2,键3 from 数据表名;
数据表中现有的所有记录的被指定的部分字段都返回

查询时去重

select distinct 键 from 数据表名;

查询区间

select * from 数据表名 where 键 between 数字 and 数字;

select * from 数据表名 where 键 > 数字 and 键 < 数字;
两句等价

比如选出成绩单中分数在20到80的数据
select * from score where degree >20 and degree <80;

键值的或关系的查询

select * from 数据表名 where 键 in(值1,值2,…)

比如选择成绩单中分数为85,89,10的数据
select * from score where degree in(85,89,10)

键的或关系的查询

select * from 数据表名 where 键1=值1 or 键2=值2;

比如从年级学生花名册中选出5班的女生
select * from student where class=‘5’ or ssex=‘f’;

升序降序查询

只升或者只降

select * from 数据表名 order by 键 desc/asc;

比如让年级学生花名册按学号升序
select * from student order by studenId asc;

既要升也要降

select * from 数据表名 order by 键1 desc/asc,键2 asc/desc;

比如让年级学生花名册按年龄降序，学号升序
select * from student order by age desc,studentId asc;
这样就会先按年龄降序排好，然后如果年龄相同就按学号升序排

统计等于指定键值的个数

select count(*) from 数据表名 where 键=值;

比如统计年级学生花名册中所有年龄为17的学生个数
select count(*) from student where age=17;

获取最大最小值者的其他键值

select 键1,键2 from 数据表名 where 键3=(select max(键3) from 数据表名);

比如想知道这次考试最高分同学的名字和学号
第一步就是找到最高分的分数
第二步就是根据最高分找到他
合起来如下：
select name,studentId from score where degree=(select max(degree) from score);

limit

另一个做法就是直接排序获得
select name,studentId from score order by degree desc limit 0,1
这里的limit的0,1两个参数，第一个是要取的数据的下标（from 0），第二个是取多少个（大于1就自动从后面拿）

分组条件与模糊查询

求均值

select avg(键1) from 数据表名 where 键2=值2;

比如要求某一门课程的平均成绩
select avg(degree) from score where course=‘语文’;

group分组

如果我的需求变成全部课程分别的平均成绩呢?
select course,avg(degree) from score group by course;

第一个course可以不写
我目前是把group后的结果想象成一个列表，列表里每个元素就是键值相同的列表

模糊查询like和having

like用于模糊查询，感觉和正则表达式差不多

首先要知道having和where的区别就是，having是对前面语句选出来的东西进行过滤且having语句内可使用聚合函数，而where是直接对表的数据进行筛选，且执行顺序是where>group>having

比如我想根据成绩表score知道至少有2名学生选修且以3开头的课程的平均分数
select course avg(degree) from score group by course
having count(studentId)>=2 and course like ‘3%’;

这里3%的%就是通配符，是什么都行，意思是只要头为3即可

多表查询

假设花名册只有学生名字和对应学号，而成绩单只有学号，课程名，成绩

如果我想得到学生名字，课程名，成绩，（差不多相当于成绩单的学号列换成学生名字）
select name,course,degree from student,score
where student.studentId=score.studentId;

假设花名册只有学生名字和对应学号，而现在成绩单只有学号，课程号，成绩，然后课程号与课程名的对应在课程表中
如果我想得到学号，课程名，分数
select studentId,course,degree from score,courseTable
where courseTable.courseId=score.courseId;

三表关联查询

假设花名册只有学生名字和对应学号，而现在成绩单只有学号，课程号，成绩，然后课程号与课程名的对应在课程表中

要求得到学生名字，课程名，成绩
select name,course,degree from student,courseTable,score
where student.studentId=score.studentId and courseTable.courseId=score.courseId;

别名 as

甚至可以直接原名加空格加新名，直接省略掉as
select name as ne,course as ce,degree as dg,
from student,courseTable,score
where student.studentId=score.studentId and courseTable.courseId=score.courseId;

子查询（嵌套select）

查询分组求平均

假如现在花名册student只有班别和学号，成绩单score有学号，课程名，成绩
查询5班同学每门课平均分

select course,avg(degree)
from score
where studentId in (select studentId from student where class=‘5’)
group by course;

查询高于某一个数据的键值的数据

请找到 在选修‘语文’课程的所有同学中，语文分数高于学号为09的同学
select * from score where course='语文‘
having degree>(select degree where course=‘语文’ and studentId=‘09’);

请找到 成绩高于学号09且课程号为2的所有记录
select * from score
where degree>(select degree from score where studentId=‘09’)
and course=(select course from courseTable where courseId=2);

year函数与带in关键字的子查询

查询与学号为09,10的同学同年出生的所有学生的学号，学生名字，生日列
假设生日列的格式如下“1978-09-22”，也就是年-月-日
花名册student有学号，学生姓名，性别，生日时间，班级

select studentId,name,birthday from student
where year(birthday) in (select year(birthday) from student where studentId in (‘09’,‘10’) );

多层嵌套子查询

根据教师名字查询其任课的学生成绩
假设教师花名册teacher有教师工号，教师名字，所在系
课程表class有课程号，课程名，教师工号
成绩单score有课程号，学生号，成绩
select name,degree from score where courseId in (select courseId from class where teacherId=(select teacherId from teacher where teacherName));

查询选修某门课程的同学数大于5的教师姓名

条件同上
select teacherName from teacher where teacherId in(
select teacherId from class
where teacherId in (
select courseId from score group by courseId having count(*)>5)
);

in表示或者关系

student是学生花名册，有学生名字，性别，班别
这里用class表示班别
查询5班和6班全体学生的记录
select * from student where class in(‘5’,‘6’);

where条件查询

成绩单score有课程号，学生号，成绩

查询存在有85分以上成绩的课程号
select distinct courseId from score where degree>85;

子查询

假设教师花名册teacher有教师工号，教师名字，所在系department
课程表class有课程号，课程名，教师工号
成绩单score有课程号，学生号，成绩

查询出计算机系教师任教课程的成绩表
select * from score where courseId in (
select courseId from class where teacherId in (
select teacherId from teacher where department=‘computer’)
);

union(并集)和not in的使用

假设教师花名册teacher有教师工号，教师名字，所在系department,职称professionalTitle(教授，讲师)

查询计算机系和电子工程系不同职称的教师记录，也就是两个系中各自独有的职称的对应的教师
select * from teacher where deparment=‘CS’ and professionalTitle not in (select professionalTitle from teacher where department=‘EE’)
union
select * from teacher where deparment=‘EE’ and professionalTitle not in (select professionalTitle from teacher where department=‘CS’);

any某一个

查询语文课的成绩中大于某个人的数学课成绩的学生记录
select * from score where class=‘语文’ and degree>any(select degree from score where class=‘数学’ );

all

查询语文课的成绩中大于选修了数学课的所有同学的成绩的记录
select * from score where class=‘语文’ and degree>all(select degree from score where class=‘数学’ );

复制表数据做条件查询（疑惑！！！！！）

新创建了一个虚拟的但又相同的表

求各门课程低于平均成绩的学生记录
select * from score a
where degree<(select avg(degree) from score b where b.course=a.course);

把a数据表迭代遍历，比如一开始，取a表的第一条记录，然后在b表根据a表当前的这条记录的课程名，找到相同的几个就求平均值，再与a比较，再到a表的下一条记录

子查询

假设教师花名册teacher有教师工号，教师名字，所在系department
课程表class有课程号，课程名，教师工号

查询所有任课老师的名字和院系（因为有的老师不用任教！）
select teacherName,department from teacher where teacherId in (select distinct teacherId from class);

条件加分组筛选

花名册student有学生姓名，班别，性别

查询至少有2个男生的班号classId
select classId from student where sex=‘m’ group by classId havng count(*)>2;
这里classId前面不用distinct，因为好像group byclassId之后返回来的自动去重了

not like模糊查询取反

查询上述花名册中不姓王的同学
select * from student where name not like ‘王%’;

year函数和now函数

year(now())获取当前年份

花名册student有学生姓名，班别，性别，生日(年月日)
查询上述花名册中每个学生的姓名和年龄
select name,year(now())-year(birthday) as ‘age’ from student;

max和min函数

查询花名册中最大最小的生日日期
select max(birthday) maxbirth,min(birthday) minbirth from student;

按等级查询

假设现在按等级来算分，比如A等就是90-100分,要求是把sco表的分数列全部变成等级
那么我们首先要再创建一个表
create table grade(
low int(3),
up(3),
grade char(1)
);
insert into grade
values(
90,100,‘A’
);
insert into grade
values(
0,89,‘B’
);

select name,course,grade from score,grade where degree between low and up;

连接查询

内连接 inner join查询（内联查询）

select * from 表1 join 表2 on 条件;

假设有一个表card，一列为id，一列为卡的种类type
我们插入数据，
(1，‘饭卡’)
(3，‘工行卡’)
(5，‘农行卡’)

又有一个表person，一列为姓名name，一列为cardId
我们插入数据，
(林我，2)
(公分，1)
(高新新，3)

这里相当于有一个隐含的关系，即cardId就是对应上面那个表的
这时候我们可以使用
select * from person join card on person.cardId=card.id;
这一句相当于，如果person表中任意一条数据的cardId的值等于card表中的id的值，那么就取出并且拼接起来，因此上述结果就是
name cardId id type
(公分，1，1，’饭卡‘)
(高新新，3，3，’工行卡‘)

左连接 left join

select * from person left join card on person.cardId=card.id;
不管三七二十一，先把左边表的数据全放进来，右边表的符合条件就照常内连接那样，不符合就是NULL
name cardId id type
(林我，2，NULL，NULL)
(公分,1，1，’饭卡‘)
(高新新，3，3，’工行卡‘)

右连接 full join

select * from person left join card on person.cardId=card.id;
不管三七二十一，先把右边表的数据全放进来，左边表的符合条件就照常内连接那样，不符合就是NULL
name cardId id type
(NULL，NULL，1，‘饭卡’)
(公分，1，1，’饭卡‘)
(高新新，3，3，’工行卡‘)

全连接 full join

mysql不支持直接full join
实现方法：
select * from person left join card on person.cardId=card.id
union
select * from person left join card on person.cardId=card.id;

name cardId id type
(林我，2，NULL,NULL)
(公分，1，1，’饭卡‘)
(高新新，3，3，’工行卡‘)
(NULL，NULL，5，‘农行卡’)

连接总结，重要的图，自行放大来看

事务

事务是一个最小的不可分割的工作单元，事务能够保证一个业务的完整性（原子性操作）

用我的话来说，事务就是一批的sql操作必须一次性一起被执行完，比如转账，我转给你，你收到钱，这两个动作不可以是我转给你，然后执行别的操作，最后你才收到钱

控制事务

开启事务

mysql默认开启自动提交

set autocommit=1;

默认接下来每一条语句都会立刻提交，持久化到磁盘，

如果autocommit=0，也就是不自动提交，那么输入的语句就只是在内存上操作，我关个机就无了，除非我们手动把当前语句提交，也就是输入commit;

查看自动提交是否开启
select @@autocommit;

想要手动开启一个事务，也就是让接下来的一批sql操作一起被执行

set autocommit=0;
start transaction; /*用begin也行，这种写法属于显式事务，DML语句隐式*/
…sql语句1
…sql语句2
…
/*在commit之前可以rollback;撤回到上一步，一旦rollback或commit事务结束*/
commit;

rollback生成断点：
savepoint 断点名;

rollback到指定断点：
rollback to 断点名;

事务四大特征ACID

原子性（Atomicity）

事务的整个过程如原子操作一样，最终要么全部成功，或者全部失败，这个原子性是从最终结果来看的，从最终结果来看这个过程是不可分割的

一致性（Consistency）

事务开始之前、执行中、执行完毕，这些时间点，多个人去观察事务操作的数据的时候，看到的数据都是一致的；

持久性（Durability）

一个事务一旦提交，他对数据库中数据的改变就应该是永久性的。当事务提交之后，数据会持久化到硬盘；

隔离性（Isolation）

一个事务的执行不能被其他事务干扰。每个事务的执行过程是相对独立的；

隔离级别分为4种：

读未提交：READ-UNCOMMITTED
读已提交：READ-COMMITTED
可重复读：REPEATABLE-READ
串行：SERIALIZABLE

读未提交：READ-UNCOMMITTED

如果有事务a和b，当a对数据进行操作的过程中，事务还没被提交，但是b可以看见a操作的结果，
可以想象，假设我转账给你但还没commit，这时候我让你去确认，你却说钱收到了，那么我这时候rollback就把你给骗了

读已提交：READ-COMMITTED

如果有事务a和b，当a对数据进行操作的过程中，事务只有被提交，b才可以看见a操作的结果，
假设一名会计看账单的时候算得今天收入为1000，然后去了厕所，这时有人又买了一些东西并且commit，于是刚回到来的会计就会发现账单前后不一致，这就是不可重复现象，难以读到同一份表，表一会儿就改变一次

可重复读：REPEATABLE-READ

如果有事务a和b，即使a事务提交了，b也不能看见a操作的结果，
就会出现幻读现象，比如我玩一个新游戏填写用户名uu，而你早玩，游戏名本来就是uu，假设有一个展示板显示游戏当前所有的用户名，在这个隔离级别下，我这边明明看到展示板上没有uu，却说用户名已被注册

串行：SERIALIZABLE

如果有事务a和b，a事务提交了，b可以看见a操作的结果，但是在a事务数据的时候，b这时候也想操作的话（比如输入了一个sql语句），那么b这边就卡住了，进入了队列排队，因为a先来，只有a搞定了（commit）b这边才有ok字眼
显然，性能很差

总结

隔离级别越高，性能越差！
mysql默认隔离级别是 可重复读：REPEATABLE-READ