mysql 学习 一

一、mysql设计

1、第一范式1NF：

数据表中的所有字段都是不可分割的原子值

create table student2(
	id int primary key,
	name varchar(20),
	address varchar(30)
);

insert into student2 values(1,'张三','河南四川成都市武侯区武侯大道110号');
insert into student2 values(2,'李四','河南四川成都市武侯区武侯大道100号');
insert into student2 values(3,'王五','河南四川成都市武侯区武侯大道90号');

字段值还可以继续拆分的，就不满足第一范式

create table student3(
	id int primary key,
	name varchar(20),
	cuntry varchar(20),
	privence varchar(20),
	city varchar(20)
);

insert into student3 values(1,'张三','中国','四川','成都');
insert into student3 values(2,'李四','中国','河南','信阳');
insert into student3 values(3,'王五','中国','湖北','武汉');

字段值不可以继续拆分，满足第一范式

范式，设计的越详细，对于某些操作可能更好，但是不一定都是好的

2、第二范式：

满足第一个范式的前提下，第二范式要求，除主键外的每一列都必须完全依赖与主键，如果要出现不完全依赖，只可能发生在联合主键的情况下。

订单表：
create table moorder(
	product_id int,
	customer_id int,
	product_name varchar(20),
	customer_name varchar(20),
	primary key(product_id,customer_id)
);

问题：除主键以外的其他列，只依赖于主键的部分字段。//需要拆分

create table myorder (
	order_id int primary key,
	product_id int,
	customer_id int
);

create table product(
	id int primary key,
	name varchar(20)
);

create table customer(
	id int primary key,
	name varchar(20)
);

在设计中可以将第一范式，分成多个第二范式

3、第三范式

必须先满足第二范式，除开主键列其他列不能有传递依赖

二、mysql前期学习准备

1、mysql库前常见命令

1、登录mysql数据库服务器

win + r 输入cmd ，回车

mysql -uroot -p密码

2、常看mysql数据

show databases;

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

create database 数据库名;

例如: create database test;

4、选中数据库

use 数据库名;

5、显示数据库中数据表

show tables;

6、创建一个数据表

CREATE TABLE pet1 (
name VARCHAR(20),
owner VARCHAR(20),
species VARCHAR(20),
sex CHAR(1),
brith DATE,
death DATE

);

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

describe 数据表名;

或

desc 数据表明;

8、查看数据表中的记录

select * from 数据表名;

select * from pet1;

9、往数据表中添加数据记录

insert into 表名 values();

insert into pet1 values(‘Puffball’,‘Diane’,‘hamset’,‘f’,‘1999-03-30’,NULL);

insert into pet1 values(‘张三’,‘Diane’,‘hamset’,‘f’,‘1999-08-25’,NULL);

insert into pet1 values(‘李四’,‘Diane’,‘hamset’,‘f’,‘1999-09-09’,NULL);

10、删除数据表中的数据

delete from 数据表名 where 关键字值=key

delete from pet1 where name=‘张三’;

11、修改数据表中的数据

update 数据表名 set 关键字值=key where 修改的关键字名=新键值;

update pet1 set name=‘张三’ where date=2000-00-00’;

12、总结数据表操作：

增加 INSERT
删除 DELETE
修改 UPDATE
查询 SELECT

2、mysql常见数据类型

​ 1、数值

​ 2、日期

​ 3、字符串

3、mysql建表约束

1、主键约束
2、自增约束
3、外键约束
4、唯一约束
5、非空约束
6、默认约束

1、主键约束

​ 它能够唯一确定一张表中的记录，我们通过给某个字段添加约束，就可以使得字段不重复，且不为空。

create table uer(
id int primary key,
name varchar(20) ,
);

insert into user values(1,‘张三’);
错误insert into user valuse(1,‘张三’);
错误insert into user valuse(NULL,‘张三’);
主键约束-联合约束:联合主键（主键值加起来不重复就可以,且主键中任何字段都不为空）

create table User1(
id int,
name varchar(20),
password varchar(20),
primary key(id,name)
);
insert into User1 values(1,‘张三’,‘123’);
insert into User1 values(2,‘张三’,‘123’);

如果创建表的时候忘记主键约束了
alter table 表名 add primary key(id);
修改表结构，添加主键约束

删除主键
alter table 表名 drop primary key;
使用modify 修改字段，添加约束：
alter table 表名 modify id int primary key;

2、自增约束

让相关的值自动增长

create table user3(
id int primary key auto_increment,
name varchar(20)
);
insert into user3 (name) values (‘张三’);

3、唯一约束

约束修饰字段的值不可以重复,但是可以为空

create table user5(
id int,
name varchar(20)
);
alter table user5 add unique(name);
insert into user5 values(1,‘张三’);
创建的时候直接添加约束
create table user6(
id int ,
name varchar(20),
unique(name)
); //适合添加多个约束，组合约束，组合不重复就可以
或者是
create table user7(
id int ,
name varchar(20) unique
);

删除唯一约束：
alter table user7 drop index name;
添加唯一约束modify添加
alter table user7 modify name varchar(20) unique;

总结：

1. 建表的时候添加约束
2. 可以使用alter … add …方式添加
3. alter … modify … 方式添加
   删除： alter … drop…

4、非空约束

修饰的字段不能为空NULL

create table user9(
id int,
name varchar(20) not NULL
);
insert into user9 (name) values(‘李四’);
insert into user9 values(1,‘张三’);

5、默认约束

就是当我们插入字段值的时候，如果没有传值，就会使用默认值

create table user10(
id int ,
name varchar(20),
age int default 10
);
insert into user10 (id,name) values(1,‘张三’);
insert into user10 (id,name) values(1,‘张三’，19);

6、外键约束

设计到两个表：主表、副表（父表，子表）

–班级
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)
);

insert into classes values(1,‘一班’);
insert into classes values(2,‘二班’);
insert into classes values(3,‘三班’);
insert into classes values(4,‘四班’);

insert into students values(1001,‘张三’,1);
insert into students values(1002,‘张三’,2);
insert into students values(1003,‘张三’,3);
insert into students values(1004,‘张三’,4);

insert into students values(1005,‘张三’,5);-- 就会报错，因为子表中添加了，主表中没有的数据

–主表中没有的数据，在副表找那个，是不可以使用的
–主表中的记录被副表引用，是不可以被删除的。

三、mysql学习–数据准备

-- 学生表
create table student(
	sno varchar(20) primary key,
	sname varchar(20) not null,
	ssex varchar(10) not null,
	sbirthday datetime not null,
	class varchar(20)
);
-- 课程表
create table course(
	cno varchar(20) primary key,
	cname varchar(20) not null,
	tno varchar(20) not null,
	foreign key(tno) references teacher(tno)
);

-- 教师表: 教师姓名、教师性别、出生年月日、职称、所在部门
create table teacher(
	tno varchar(20) primary key,
	tname varchar(20) not null,
	tsex varchar(10) not null ,
	tbirthday datetime,
	procf varchar(20) not null,
	depart varchar(20) not null
);

create table score(
	sno varchar(20) not null,
	cno varchar(20) not null,
	degree decimal,
	foreign key(sno) references student(sno),
	foreign key(cno) references course(cno),
	primary key(sno,cno)
);




-- 往数据表找那个添加数据:
-- 学生表数据
INSERT INTO student VALUES('101','曾华','男','1977-09-01','95033');
INSERT INTO student VALUES('102','匡明','男','1975-10-02','95031');
INSERT INTO student VALUES('103','王丽','女','1976-01-23','95033');
INSERT INTO student VALUES('104','李军','男','1976-02-20','95033');
INSERT INTO student VALUES('105','王芳','女','1975-02-10','95031');
INSERT INTO student VALUES('106','陆军','男','1974-06-03','95031');
INSERT INTO student VALUES('107','王尼玛','男','1976-02-20','95033');
INSERT INTO student VALUES('108','张全蛋','男','1975-02-10','95031');
INSERT INTO student VALUES('109','赵铁柱','男','1974-06-03','95031');

-- 教师表数据
INSERT INTO teacher VALUES('804','李诚','男','1958-12-02','副教授','计算机系');
INSERT INTO teacher VALUES('856','张旭','男','1969-03-12','讲师','电子工程系');
INSERT INTO teacher VALUES('825','王萍','女','1972-05-05','助教','计算机系');
INSERT INTO teacher VALUES('831','刘冰','女','1977-08-14','助教','电子工程系');

-- 添加课程表
INSERT INTO course VALUES('3-105','计算机导论','825');
INSERT INTO course VALUES('3-245','操作系统','804');
INSERT INTO course VALUES('6-166','数字电路','856');
INSERT INTO course VALUES('9-888','高等数学','831');

-- 添加成绩表
INSERT INTO score VALUES('103','3-245','86');
INSERT INTO score VALUES('105','3-245','75');
INSERT INTO score VALUES('109','3-245','68');
INSERT INTO score VALUES('103','3-105','92');

INSERT INTO score VALUES('105','3-105','88');
INSERT INTO score VALUES('109','3-105','76');
INSERT INTO score VALUES('103','6-166','85');

INSERT INTO score VALUES('105','6-166','79');
INSERT INTO score VALUES('109','6-166','81');

四、mysql基本操作

1、常见的查询

1、查询student表所有记录

select * from student;

2、查询student表中所有记录的sname,ssex,classlie

select sname,ssex,class from student;

3、查询教室所有单位不重复的depart列

​ distinct 排除重复

select depart from teacher;

select distinct depart from teacher;

4、查询score表中成绩再60到80之间所有记录

select * from score where degree between 60 and 80;

select * from score where degree >= 60 and degree <=80;

5、查询score表中成绩为85，86或88的成绩

表示或者关系的查询 in

select * from score where degree in (85,86,88);

select * from score where degree==

6、查询student表中"95031"班或性别为"女"的同学记录

or 表示或则

select * from student where class=“95031” or ssex=“女”;

7、以class降序查询student表的所有记录

–升序asc，降序desc

select * from student order by class desc;

select * from student order by class asc;

select * from student order by class;

8、以cno升序、degree降序查询score表的所有记录

select * from score order by cno asc,degree desc;

9、查询"95031"班的学生人数

select count(*) from student where class=“95031”;

10、查询score表中的最高分的学生学号和课程号（子查询或者排序）

select sno,cno from score

​ where degree=(select max(degree) from score);

1、找到最高分

select max(degree) from score;

2、找最高分的sno 和cno

select sno,cno from score

​ where degree=(select max(degree) from score);

3、有缺陷查询方式

select sno,cno from score order by degree desc limit 0,1;

2、其他常用查询

11、查询每门课程的平均成绩

–avg()

select cno,avg(degree) from score group by cno;

12、查询score表中至少有两名学生选修的并以3开头的课程的平均成绩

select cno from score group by cno having count(*) >= 2 and cno like ‘3%’;

select cno,avg(degree),count() from score group by cno having count() >= 2 and cno like ‘3%’;

范围查询的两种类查询方式

13、查询分数大于70，小于90的列

select sno,degree from score where degree>70 and degree<90;

select sno,degree from score where degree between 70 and 90;

3、多表查询

14、查询所有学生的sname,cno和degree列

select sname,cno,degree from student,score where student.sno=score.sno;

15、查询所有学生的sno,cname,degree列

select sno,cname,degree from course,score where course.cno=score.cno;

16、查询所有学生的sname,cname和degree

–sname -> student

–cname -> course

–degree -> score

三表关联查询

select sname,cname,degree from student,course,score where student.sno=score.sno and course.cno=score.cno;

select sname,cname,degree,student.sno,course.cno from student,course,score where student.sno=score.sno and course.cno=score.cno;

子查询+分组+求平均分

17、查询"95031"班学生每门课的平均分

select sno from student where class=“95031”;

select cno,avg(degree) from score where sno in (select sno from student where class=“95031”) group by cno;

18、查询选修“3-105”课程的成绩高于"109"号同学"3-105"成绩的所有同学的记录

select degree from score where sno=“109” and cno=“3-105”;

select * from score where degree > (select degree from score where sno=“109” and cno=“3-105”) and cno=“3-105”;

19、查询成绩高于学号高于109 ，课程号为3-105的成绩的所有记录

select * from score where degree > (select degree from score where sno=‘109’ and cno=‘3-105’);

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

20、查询学号为108，101，的铜须同年出生的所有学生的sno,sname,sbirthday列

select year(sbirthday) from student where sno in (108,101);

select * from student where year(sbirthday) in (select year(sbirthday) from student where sno in (108,101));

多层嵌套子查询

21、查询张旭教师任课的学生成绩

select tno from teacher where tname=‘张旭’;

select cno from course where tno=(select tno from teacher where tname=‘张旭’);

select * from score where cno in (select cno from course where tno=(select tno from teacher where tname=‘张旭’));

多表查询

22、查询选修某课程的同学人数多余5人的教师姓名

select cno from score group by cno having count(*) > 5;

select * from teacher;

select tno from course where cno=(select cno from score group by cno having count(*) > 5);

select * from teacher where tno=(select tno from course where cno=(select cno from score group by cno having count(*) > 5));

in表示或者关系

23、查询95033班和95031班全体学生的记录

select * from student where class in (‘95031’,‘95038’);

where条件查询

24、查询存在85分以上成绩的课程cno

select * from score where degree > 85;

子查询

25、查询出计算机系教师所教课程的成绩表

select tno from teacher where depart=‘计算机系’;

select cno from course where tno in (select tno from teacher where depart=‘计算机系’);

select * from score where cno in (select cno from course where tno in (select tno from teacher where depart=‘计算机系’));

union和notin的使用

26、查询计算机系与电子工程系不同职称的教师的tname和procf

–union求并集

select procf from teacher where depart=‘电子工程系’;

select * from teacher where depart =‘计算机系’ and procf not in (select procf from teacher where depart=‘电子工程系’)

union

select * from teacher where depart =‘电子工程系’ and procf not in (select procf from teacher where depart=‘计算机系’);

any表示至少一个desc降序

27、查询选修编号为3-105课程且成绩至少高于选修编号为3-245的同学的cno,sno,degree,并按degree从高到低次序降序

select * from score where cno=‘3-245’;

select * from score where cno=‘3-105’;

至少？大于其中一个,any

select * from score where cno=‘3-105’ and degree>any(select degree from score where cno=‘3-245’);

select * from score where cno=‘3-105’ and degree>any(select degree from score where cno=‘3-245’) order by degree desc;

all表示所有

28、查询选修编号为3-105且成绩高于选修编号为3-245课程的同学的cno,sno,degree

–且？all表示所有关系

select * from score where cno=‘3-105’ and degree>all(select degree from score where cno=‘3-245’) order by degree desc;

as取别名，union求并集

29、查询所有教师和同学的name,sex,birthday

select tname name,tsex sex,tbirthday birthday from teacher

union

select sname,ssex,sbirthday from student;

union求并集

30、查询所有女教师和女同学的name,sex,birthday

select tname name,tsex sex,tbirthday birthday from teacher where tsex=‘女’

union

select sname,ssex,sbirthday from student where ssex=‘女’;

复制表数据做条件查询

31、查询成绩比该课程平均成绩第的同学的成绩表

select cno,avg(degree) from score group by cno;

select * from score;

select * from score a where degree < (select avg(degree) from

score b where a.cno=cno

);

子查询

32、查询所有任课教师的tname,depart(有课程教学的老师)

select * from teacher where tno in(select tno from course);

条件+分组筛选

33、查询自少有2名男生的班号

select * from student;

select class from student where ssex=‘男’ group by class having count(*) >=2;

not like模糊查询取反

33、查询student表中不姓 “王”的同学记录

select * from student where sname not like ‘王%’;

yaer函数和now函数

34、查询student表中每个学生的姓名和年龄

select * from student;

select sname,year(now())-year(sbirthday) from student;

max函数和min函数

35、查询student表中最大和最小的sbirthday日期值

select max(sbirthday),min(sbirthday) from student;

多字段排序

36、以班号和年龄从大到小的顺序查询student表中的全部记录

select * from student order by class desc,sbirthday;

子查询

37、查询 男 教师及其所上课程

select * from teacher where tsex=‘男’;

select * from course where tno in (select tno from teacher where tsex=‘男’);

max函数与子查询

38、查询最高分同学的sno,cno,degree列

select max(degree) from score;

select * from score where degree = (select max(degree) from score);

子查询

39、查询和李军同性别的所有同学的sname

select ssex from student where sname=‘李军’;

select sname from student where ssex=(select ssex from student where sname=‘李军’);

40、查询和李军同性别并且同班的同学的sname

select sname from student

where ssex=(select ssex from student where sname=‘李军’)

and class=(select class from student where sname=‘李军’);

41、查询所有选修 计算机导论 课程的男同学的成绩表

select * from student student where ssex=‘男’;

select * from course where cname =‘计算机导论’;

select * from score

where cno = (select cno from course where cname =‘计算机导论’)

and sno in (select sno from student where ssex=‘男’);

按等级查询

42、假设使用如下命令建立要给grade表

create table grade(

low int(3),

upp int(3),

grade char(1)

);

insert into grade value(90,100,‘A’);

insert into grade value(80,89,‘B’);

insert into grade value(70,79,‘C’);

insert into grade value(60,69,‘D’);

insert into grade value(0,59,‘E’);

查询所有同学的sno,cno,rank列

select sno,cno,grade from score,grade where

degree between low and upp;

五、mysql连接查询

sql四种连接查询

内连接inner join 或者join

外连接

​ 左连接left join 或者left outer join

​ 有连接right join或者right outer join

​ 完全外连接 full join或者full outer join

创建两个表

create database testjoin;

person表

create table person(id int, name varchar(20), cardId int);

card表

create table card(id int,name varchar(20));

insert into card values(1,‘饭卡’);

insert into card values(2,‘建行卡’);

insert into card values(3,‘农行卡’);

insert into card values(4,‘工商卡’);

insert into card values(5,‘邮政卡’);

insert into person values(1,‘张三’,1);

insert into person values(2,‘李四’,3);

insert into person values(3,‘王五’,6);

—不创建外键

1、inner join 查询（内连接）

select * from person inner join card on person.cardId = card.Id;

–内联查询，其实就是两张表中的数据，通过某个字段相等查询出相关记录数据

2、left join(左外连接)

select * from person left join card on person.cardId = card.id;

select * from person left outer join card on person.cardId = card.id;

–左外连接，会把左边表里面所有数据取出来，而右边表中数据如果有相等，就显示出来，如果没有就补NULL

3、right join(右外连接)

select * from person right join card on person.cardId = card.id;

select * from person right outer join card on person.cardId = card.id;

–右外连接，会把右边边表里面所有数据取出来，而左边表中数据如果有相等，就显示出来，如果没有就补NULL

4、full join(全外连接)

select * from person full join card on person.cardId=card.id;

mysql不支持全外连接

select * from person left join card on person.cardId = card.id

union

select * from person right join card on person.cardId = card.id;

左外连接+右外连接取并集，便是全外连接

五、mysql事务

mysql事务，事务其实是一个最小的不可分割的工作单元，事务能够保证 一个业务的完整性。

例如银行转账。A账户减少100元，B账户增加100元

多条sql语句，可能会有同时完成的要求。要么同时失败，要么同时成功。

mysql中如何控制事务？

mysql默认开启事务的(自动提交)。

select @@autocommit; --查看事务提交状态

–默认事务开启的作用是，当我们去执行一个sql语句的时候，效果会立即体现出来，且不能回滚。

create database bank;

create table user(id int primary key,name varchar(20),money int);

insert into user values(1,‘a’,1000);

–事务回滚:撤销sql语句执行效果

rollback;

–设置mysql自动提交为false

set autocommit=0;

insert into user values(2,‘b’,1000);

rollback;

insert into user values(2,‘b’,1000);

commit;

–commit手动提交数据，回滚无效,持久性；

–事务

自动提交autocommit=1

手动提交autocommit=0

事务回滚rollback

事务给我们提供了一个返回(反悔)的机会。

set autocommit=1

select @@autocommit=1;

手动开启事务

begin;或者start transaction都可以让我们手动开启一个事务

insert into user values(2,‘b’,1000);

update user set money=money-100 where name=‘a’;

update user set money=money+100 where name=‘b’;

rollback;

–事务回滚，但是无法撤销，因为autommit=1

手动开启事务(1)

begin;

update user set money=money-100 where name=‘a’;

update user set money=money+100 where name=‘b’;

select * from user;

rollback;

select * from user;

可以看出，事务可以回滚；

手动开启事务(2)

start transaction;

update user set money=money-100 where name=‘a’;

update user set money=money+100 where name=‘b’;

select * from user;

rollback;

select * from user;

可以看出，事务可以回滚

commit;

事务开启之后，一旦手动提交commit,再使用rollback可以看出回滚无效。也就是说当前的这个事务在提交的时候就结束了

ACID特征与使用

事务四大特征

A 原子性:事务是最小的单位，不可再分割

C 一致性:事务要求，同一事务中的sql语句，必须保证同时成功，或者同时失败。

I 隔离性:事务 1 和事务 b 之间是具有隔离性的。

D 持久性:事务一旦结束commit，就不可返回(反悔rollback)。

事务开启方式:

1.修改默认提交

​ set autocommit=0;

​ begin;

​ start transaction;

事务手动提交

​ commit;

事务手动回滚

​ rollback;

事务的隔离性

1、read uncommitted;读未提交的

​ 如果有事务 a 和事务 b

​ a 事务对数据进行操作，在操作的过程中，事务没有被提交，但是 b 可以看见 a 操作的结果。

​ insert into user values(3,‘小明’,1000);

​ insert into user values(4,‘淘宝店’,1000);

–查看数据的隔离级别

系统级别的

​ select @@global.transaction_isolation;

会话级别的

select @@transaction_isolation;

–修改隔离级别

set global.transaction_isolation level read uncommitted;

–转账:小明在淘宝店买东西，800元

set global.transaction_isolation level read uncommitted;

start transaction;

update user set money=money-800 where name=‘小明’;

update user set money=money+800 where name=‘淘宝店’;

select * from user;

–淘宝店吃饭结账

select * from user

如果两个不同地方，都在进行操作，如果事务 a 开启之后，他的数据可以被其他事务读取到，这样就会出现(脏读)

脏读:一个事务读取到了另外一个事务没有提交的数据，就叫做脏读。实际开发是不允许脏读出现的。

2、read committed;读已提交的

修改隔离级别

set global.transaction_isolation level read committed;

select @@global.transaction_isolation;

虽然提交了一个事务提交的数据，但是还是会出现读取同一个表的数据，发现前后不一致–不可重复读现象，read committed

3、repeatable read;可以重复读

修改隔离级别

set global.transaction_isolation level repeatable read;

select @@global.transaction_isolation;

–在repeatable read隔离级别下

幻读

事务 a 和事务 b 同时操作一个表，事务 a 提交的数据，也不能被事务 b 读到，就会造成幻读

4、serializable;串行化

set global.transaction_isolation level serializable;

select @@global.transaction_isolation;

当一个表被另外一个事务操作的时候，其他事务的写操作是不可以进行的。直到进入排队状态(串行化状态)

–串行化问题是,性能特差！！！

隔离级别越高，性能越差。mysql默认隔离级别是repeatable read