Mysql入门+sql练习

启动Mysql

PATH="$PATH":/usr/local/mysql/bin
mysql -u root -p

基本操作语句以及概念

增删改查：
INSERT INTO ** VALUES ();
DELETE FROM *** where ;
DROP TABLE *;
SELECT * FROM *** where ;
Update ** set =‘’ where ;

Mysql数据类型参考：
https://www.runoob.com/mysql/mysql-data-types.html

Mysql建表约束条件：

主键约束

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

	create table user2(
	id int,
	name varchar(20),
	password varchar(20),
	primary key (id,name)); %联合主键约束，只要加起来不一样就行，但是不能为空。

Field	Type	Null	Key	Default	Extra
id	int(11)	NO	PRI	NULL
name	varchar(20)	NO	PRI	NULL
password	varchar(20)	YES		NULL

#后续添加主键约束
alter table user4 add primary key(id);
#删除
alter table user4 drop primary key;
#修改主键约束
Alter table user4 modify id int primary key;

自增约束：在插入数据时，未指定变量自动进行计数。

create table user3(
id int primary key auto_increment,
name varchar(20)
);
#操作
insert into user3 (name) values (’zhang’);

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

create table user5 (
id int,
name varchar(20)
);
#
alter table user5 add unique(name);

Or
create table user5 (
id int,
name varchar(20)，
unique(name)
);

Field	Type	Null	Key	Default	Extra
id	int(11)	YES		NULL
name	varchar(20)	YES	UNI	NULL

若unique(id, name)
则不能同时重复（可以单独重复）

#删除唯一约束
Alter table user5 drop index name;

Field	Type	Null	Key	Default	Extra
id	int(11)	YES		NULL
name	varchar(20)	YES		NULL

#使用modify添加唯一约束：（modify无法删除唯一约束,也无法删除key，可以删除NULL）
Alter table user5 modify name varchar(20) unique;

非空约束

create table user6 (
id int,
name varchar(20) not null
);

Field	Type	Null	Key	Default	Extra
id	int(11)	YES		NULL
name	varchar(20)	NO		NULL

默认约束：设定默认值

create table user7 (
id int,
name varchar(20),
age int default 10
);

Insert into user7 (id, name) value (1, 'Lucas');

id	name	age
1	Lucas	10

外键约束：涉及到主表&附表

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

mysql> desc students;

Field	Type	Null	Key	Default	Extra
id	int(11)	NO	PRI	NULL
name	varchar(20)	YES		NULL
class_id	int(11)	YES	MUL	NULL

insert into classes values (1,'class one')
insert into classes values (2,’class two')
insert into classes values (3,’class three');

insert into students values (1001,zhangsan,1)
insert into students values (1002,lisi,2)
insert into students values (1003,wanger,2)

#主表中没有的值是无法被附表引用的

insert into students values (1004,wanger,4)
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`))

#被引用过后无法删除

Delete from classes where id =2;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`))

数据库的三个范式

1.第一范式：都是不可分的原子值
#以下地址是可再分的，不满足第一范式

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

insert into student2 values(1,'zhangsan','shanghai minhang distrit dongchuan 800#');
insert into student2 values(2,'zhangsi','shanghai minhang distrit dongchuan 900#');

2。第二范式：（在第一范式前提下）除了主键外每一列，都要完全依赖主键
若发生不完全依赖，则只可能发生在联合主键的情况下。

create table myorder (
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(
    product_id int primary key,
    product_name varchar(20)
    );

create table customer(
    customer_id int primary key,
    customer_name varchar(20)
    );

3.第三范式：除了主键列的其他列，不能有传递依赖。
#存在传递依赖的例子：
create table myorder (
order_id int primary key,
product_id int,
customer_id int,
customer_phone varchar(20)
);

查询练习

数据准备

1.学生表
create table Student (
student_id varchar(20) primary key,
student_name varchar(20) not null,
sex char(1) not null,
birth date,
class_id varchar(20)
);

2.课程表
create table Course (
course_id varchar(20) primary key,
course_name varchar(20) not null,
teacher_id varchar(20) not null,
foreign key (teacher_id) references Teacher(teacher_id)
);

3.成绩表
create table Score (
student_id varchar(20),
course_id varchar(20),
score decimal,
primary key(student_id,course_id),
foreign key (student_id) references Student (student_id),
foreign key (course_id) references Course(course_id)
);

4.教师表
create table Teacher (
teacher_id varchar(20) primary key,
teacher_name varchar(20) not null,
teacher_sex char(1) not null,
tbirth date,
prof varchar(20),
depart varchar(20)
);

mysql> show tables;

Tables_in_selecttest
Course
Score
Student
Teacher

–向表中添加数据

insert into Student values('1110','Peter','m','1994-11-12','11701');
insert into Student values('1111','Steven','m','1995-11-19','11703');
insert into Student values('1113','Lucas','m','1994-01-19','11703');
insert into Student values('1114','Amy','f','1995-12-10','11702');
insert into Student values('1115','Ceres','f','1995-03-23','11702');
insert into Student values('1116','Leon','m','1996-05-02','11703');
insert into Student values('1117','Theodor','m','1997-08-10','11701');
insert into Student values('1118','Dea Won','f','1995-01-12','11701');
insert into Student values('1119','Magnus','m','1995-03-19','11703');

insert into Teacher values('0099','Kyubin','m','1964-03-19','ViceProf','CS');
insert into Teacher values('0087','Mengjo','f','1994-09-09','TA','EE');
insert into Teacher values('0066','Joe','m','1974-08-02','Prof','EE');

insert into Course values ('3-101','MathAdv.','0099');
insert into Course values ('3-103','OS','0087');
insert into Course values ('3-104','DataStructure','0066');

insert into score values('1110','3-101','98');
insert into score values('1110','3-103','56');
insert into score values('1111','3-101','55');
insert into score values('1111','3-104','78');
insert into score values('1113','3-101','85');
insert into score values('1113','3-103','98');
insert into score values('1113','3-104','93');
insert into score values('1114','3-101','90');
insert into score values('1114','3-103','83');
insert into score values('1114','3-104','85');
insert into score values('1115','3-101','87');
insert into score values('1115','3-103','88');
insert into score values('1115','3-104','89');
insert into score values('1116','3-101','83');
insert into score values('1116','3-104','78');
insert into score values('1117','3-101','76');
insert into score values('1117','3-104','89');
insert into score values('1118','3-101','83');
insert into score values('1119','3-101','78');
insert into score values('1119','3-103','76');

mysql> select * from Score;

student_id	course_id	score
1110	3-101	98
1110	3-103	56
1111	3-101	55
1111	3-104	78
1113	3-101	85
1113	3-103	98
1113	3-104	93
1114	3-101	90
1114	3-103	83
1114	3-104	85
1115	3-101	87
1115	3-103	88
1115	3-104	89
1116	3-101	83
1116	3-104	78
1117	3-101	76
1117	3-104	89
1118	3-101	83
1119	3-101	78
1119	3-103	76

查询练习

1。查询部分列：
mysql> desc Student;

Field	Type	Null	Key	Default	Extra
student_id	varchar(20)	NO	PRI	NULL
student_name	varchar(20)	NO		NULL
sex	char(1)	NO		NULL
birth	date	YES		NULL
class_id	varchar(20)	YES		NULL

mysql> select student_name,sex,class_id from Student;

student_name	sex	class_id
Peter	m	11701
Steven	m	11703
Lucas	m	11703
Amy	f	11702
Ceres	f	11702
Leon	m	11703
Theodor	m	11701
Dea Won	f	11701
Magnus	m	11703

2.查询不重复的班级列

mysql> select distinct class_id from Student;

class_id
11701
11703
11702

3.查询成绩中60-80的记录

mysql> select student_id,score from Score where score between 60 and 80;
mysql> select student_id,score from Score where score > 60 and score < 80;

student_id	score
1111	78
1116	78
1117	76
1119	78
1119	76

4.查询成绩为85，86，88的记录

mysql> select * from Score where score in (85, 86, 88);

student_id	course_id	score
1113	3-101	85
1114	3-104	85
1115	3-103	88

5.查询3，2班或者性别为女的学生。
mysql> select * from Student where sex = 'f' or class_id in ('11703','11702');

student_id	student_name	sex	birth	class_id
1111	Steven	m	1995-11-19	11703
1113	Lucas	m	1994-01-19	11703
1114	Amy	f	1995-12-10	11702
1115	Ceres	f	1995-03-23	11702
1116	Leon	m	1996-05-02	11703
1118	Dea Won	f	1995-01-12	11701
1119	Magnus	m	1995-03-19	11703

6.以class降序查询student记录。

mysql> select * from Student order by class_id  desc;

student_id	student_name	sex	birth	class_id
1111	Steven	m	1995-11-19	11703
1113	Lucas	m	1994-01-19	11703
1116	Leon	m	1996-05-02	11703
1119	Magnus	m	1995-03-19	11703
1114	Amy	f	1995-12-10	11702
1115	Ceres	f	1995-03-23	11702
1110	Peter	m	1994-11-12	11701
1117	Theodor	m	1997-08-10	11701
1118	Dea Won	f	1995-01-12	11701

#升序：mysql> select * from Student order by class_id asc;

7.以class_id升序，以score降序

mysql> select * from Score order by course_id asc,score desc;

student_id	course_id	score
1110	3-101	98
1114	3-101	90
1115	3-101	87
1113	3-101	85
1116	3-101	83
1118	3-101	83
1119	3-101	78
1117	3-101	76
1111	3-101	55
1113	3-103	98
1115	3-103	88
1114	3-103	83
1119	3-103	76
1110	3-103	56

8.查询coures 3-101人数

mysql> select count(*) from Score where course_id = '3-101';

count(*)
9

9.查询score中分数最高的学生的学号和课程号

mysql> select student_id,course_id from score where score =(select max(score) from Score);

student_id	course_id
1110	3-101
1113	3-103

mysql> select max(score) from Score;

max(score)
98

10.查询每门课的平均成绩

mysql> select course_id,avg(score) from Score group by course_id;

course_id	avg(score)
3-101	81.6667
3-103	80.2000
3-104	85.3333

11.查询表中至少有四名学生选修的并且以3开头的平均分数，

mysql> select course_id,avg(score) from Score group by course_id having count(course_id)>=6 and course_id like '3%';

course_id	avg(score)
3-101	81.6667
3-104	85.3333

12.查询学生姓名，课程，分数（多表查询）

mysql> select student_name,course_name,score from Student,Score,Course
-> where Student.student_id=Score.student_id and Course.course_id=Score.course_id ;

student_name	course_name	score
Peter	MathAdv.	98
Steven	MathAdv.	55
Lucas	MathAdv.	85
Amy	MathAdv.	90
Ceres	MathAdv.	87
Leon	MathAdv.	83
Theodor	MathAdv.	76
Dea Won	MathAdv.	83
Magnus	MathAdv.	78
Peter	OS	56
Lucas	OS	98
Amy	OS	83

13.查询一班学生每门课程的平均分

select course_id,avg(score) from Score where student_id in (select student_id from Student where class_id='11701')
group by course_id;

course_id	avg(score)
3-101	85.6667
3-103	56.0000
3-104	89.0000

14.查询3-101课程中成绩高于1115同学的所有同学成绩

mysql> select student_id,course_id,score from Score where course_id ='3-101' and score>(select score from Score where student_id ='1115' and course_id ='3-101');

student_id	course_id	score
1110	3-101	98
1114	3-101	90

15.查询和1114同学童年出生的

mysql> select * from Student where year(birth)= (select year (birth) from Student where student_id ='1114');

student_id	student_name	sex	birth	class_id
1111	Steven	m	1995-11-19	11703
1114	Amy	f	1995-12-10	11702
1115	Ceres	f	1995-03-23	11702
1118	Dea Won	f	1995-01-12	11701
1119	Magnus	m	1995-03-19	11703

16.查询计算机系教师所教学生的成绩表

mysql> select student_id,score from Score where course_id =(select course_id from Course where teacher_id=
-> (select teacher_id from Teacher where depart='CS'));

student_id	score
1110	98
1111	55
1113	85
1114	90
1115	87
1116	83
1117	76
1118	83
1119	78

17.取不同

select teacher_name, prof from Teacher where depart='CS' and prof not in (select prof from Teacher where depart ='EE')
union
select teacher_name, prof from Teacher where depart='EE' and prof not in (select prof from Teacher where depart ='CS');

teacher_name	prof
Kyubin	ViceProf
Joe	Prof
Mengjo	TA

18.any() all()

19.别名：as

select student_name ,sex, birth from Student
union
select teacher_name ,teacher_sex, tbirth from Teacher;

student_name	sex	birth
Peter	m	1994-11-12
Steven	m	1995-11-19
Lucas	m	1994-01-19
Amy	f	1995-12-10
Ceres	f	1995-03-23
Leon	m	1996-05-02
Theodor	m	1997-08-10
Dea Won	f	1995-01-12
Magnus	m	1995-03-19
Joe	m	1974-08-02
Mengjo	f	1994-09-09

select student_name as name ,sex, birth from Student
union
select teacher_name ,teacher_sex, tbirth from Teacher;

name	sex	birth
Peter	m	1994-11-12
Steven	m	1995-11-19
Lucas	m	1994-01-19
Amy	f	1995-12-10

20.查询成绩大于平均成绩的学生

select student_id,score from Score a where score<(select avg(score) from Score b where  a.course_id=b.course_id)

student_id	score
1110	56
1111	55
1111	78
1114	85
1116	78
1117	76
1119	78
1119	76

21.查询男生大于1的班号

select class_id,count(*) as num from Student where sex='m' group by class_id having count(*)>1;

class_id	num
11701	2
11703	4

22.查询学生年龄

select student_name, (year(now())-year(birth)) as Age from Student;

student_name	Age
Peter	25
Steven	24
Lucas	25
Amy	24
Ceres	24
Leon	23
Theodor	22

SQL 的四种连接

1、内连接： （inner） join
2、左连接；left (outer) join
3、有连接；right (outer) jion
4、完全外连接：full (outer) jion

-建表

create database jiontest;

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

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 card values (6,'IC卡');

insert into person values(1,'Zed',1 );
insert into person values(2,'John',3 );
insert into person values(3,'Tony',6 );
insert into person values(4,'Chery',7 );

inner join 查询:两张表中的数据通过某个字段相等查询出数据

select * from person inner join card on person.cardID =card.id;

id	name	cardID	id	name
1	Zed	1	1	饭卡
2	John	3	3	建行卡
3	Tony	6	6	IC卡

mysql> select * from person,card where person.cardID= card.id ;

id	name	cardID	id	name
1	Zed	1	1	饭卡
2	John	3	3	建行卡
3	Tony	6	6	IC卡

左外连接查询

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

id	name	cardID	id	name
1	Zed	1	1	饭卡
2	John	3	3	建行卡
3	Tony	6	6	IC卡
4	Chery	7	NULL	NULL

右外键查询

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

id	name	cardID	id	name
1	Zed	1	1	饭卡
2	John	3	3	建行卡
3	Tony	6	6	IC卡
NULL	NULL	NULL	2	中行卡
NULL	NULL	NULL	4	农行卡
NULL	NULL	NULL	5	招行卡

全外连接

select * from person left join card on person.cardID =card.id
union
select * from person right join card on person.cardID =card.id;

id	name	cardID	id	name
1	Zed	1	1	饭卡
2	John	3	3	建行卡
3	Tony	6	6	IC卡
4	Chery	7	NULL	NULL
NULL	NULL	NULL	2	中行卡
NULL	NULL	NULL	4	农行卡
NULL	NULL	NULL	5	招行卡

事务

数据库事务（简称：事务）是数据库管理系统执行过程中的一个逻辑单位，由一个有限的数据库操作序列构成。

为什么需要事务？

一个数据库事务通常包含了一个序列的对数据库的读/写操作。它的存在包含有以下两个目的：
1、为数据库操作序列提供了一个从失败中恢复到正常状态的方法，同时提供了数据库即使在异常状态下仍能保持一致性的方法。
2、当多个应用程序在并发访问数据库时，可以在这些应用程序之间提供一个隔离方法，以防止彼此的操作互相干扰。

当事务被提交给了DBMS（数据库管理系统），则DBMS（数据库管理系统）需要确保该事务中的所有操作都成功完成且其结果被永久保存在数据库中，如果事务中有的操作没有成功完成，则事务中的所有操作都需要被回滚，回到事务执行前的状态;同时，该事务对数据库或者其他事务的执行无影响，所有的事务都好像在独立的运行。

但在现实情况下，失败的风险很高。在一个数据库事务的执行过程中，有可能会遇上事务操作失败、数据库系统/操作系统失败，甚至是存储介质失败等情况。这便需要DBMS对一个执行失败的事务执行恢复操作，将其数据库状态恢复到一致状态（数据的一致性得到保证的状态）。为了实现将数据库状态恢复到一致状态的功能，DBMS通常需要维护事务日志以追踪事务中所有影响数据库数据的操作。

mysql如何控制事务

默认是开启事务的（自动提交），效果立即提现且不可回滚
mysql> select @@autocommit;

@@autocommit
1

update person set money=1000 where id =2;

id	name	cardID	money
1	Zed	1	NULL
2	piggy	3	1000

#关闭自动提交
set autocommit= 0;

update person set money=2000 where id =1;
#现在显示为虚拟表
mysql> select * from person;

id	name	cardID	money
1	Zed	1	2000
2	piggy	3	1000

#回滚
mysql> rollback;

id	name	cardID	money
1	Zed	1	199
2	piggy	3	1000

#手动提交
mysql> commit;

–手动开启事务 begin/ start transaction
begin ;
update person set money=money+100 where id =1;
update person set money=money-100 where id =2;

id	name	cardID	money
1	Zed	1	2100
2	piggy	3	900

#仍然能够回滚,回滚之后事务结束
mysql> rollback;select * from person;
Query OK, 0 rows affected (0.00 sec)

id	name	cardID	money
1	Zed	1	2000
2	piggy	3	1000

#事务在手动提交的时候结束

事务的四大特性
A 原子性：事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行；
C 一致性：事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态的含义是数据库中的数据应满足完整性约束。
I 隔离性：多个事务并发执行时，一个事务的执行不应影响其他事务的执行；
D 持久性：已被提交的事务对数据库的修改应该永久保存在数据库中。

事务开启：
1、修改默提交 set autocommit=0；
2、begin;
3、start transaction;
事务结束（2、3）：
1、提交后结束
2、回滚后结束

事务提交
commit；
事务回滚
rollback；

事务的隔离性：

1、read uncommitted; 读未提交的事务

如果有两个事务a, b。 若操作过程中，事务没有被提交，但是b可以看见a操作的结果

#查看数据库的隔离级别
mysql> select @@global.transaction_isolation;

@@global.transaction_isolation
REPEATABLE-READ

#修改隔离级别
mysql> set global transaction isolation level read uncommitted;

@@global.transaction_isolation
READ-UNCOMMITTED

start transaction ;
update person set money =money -500 where id =1;
update person set money =money +500 where id =2;
mysql> select * from person;

id	name	cardID	money
1	Zed	1	1500
2	piggy	3	1500

mysql> rollback;
mysql> select * from person;

id	name	cardID	money
1	Zed	1	2000
2	piggy	3	1000

如果两个不同的地方，都在进行操作，事务a开启之后，其他事务可以读到未提交的数据。

2、read committed 读已提交的

#修改隔离级别
mysql> set global transaction isolation level read committed;
mysql> select @@global.transaction_isolation;

@@global.transaction_isolation
READ-COMMITTED

#被人恶意插入记录
start transaction;
insert into person values(5,‘dad’,2,200);
commit;

#发现不正确了
mysql> select avg(money) from person;

avg(money)
1066.6667

#对于同一个事务，两次读出来的数据不一样，此为不可重复度问题
#该隔离级别同样会出现问题

3、repeatable read 可重复读

mysql> set global transaction isolation level repeatable read;
mysql> select @@global.transaction_isolation;

@@global.transaction_isolation
REPEATABLE-READ

#甲开事务
start transaction;

#乙开事务
start transaction;

#甲插入
insert into person values(7,‘Wa’,4,10);
commit;
#甲查询
mysql> select * from person;

id	name	cardID	money
1	Zed	1	2000
2	piggy	3	1000
3	Tony	6	NULL
4	Chery	7	NULL
5	dad	2	200
6	Li	4	800
7	Wa	4	10

#乙在甲提交前和提交后都差一次，都查不到（若之前不查，之后查，则可以查到）
mysql> select * from person;

id	name	cardID	money
1	Zed	1	2000
2	piggy	3	1000
3	Tony	6	NULL
4	Chery	7	NULL
5	dad	2	200
6	Li	4	800

#可以解决不可重复度问题，但是有幻读现象，即使提交了也读不到

4、 串行化

mysql> set global transaction isolation level serializable;
mysql> select @@global.transaction_isolation;

@@global.transaction_isolation
SERIALIZABLE

mysql> select * from person;

id	name	cardID	money
1	Zed	1	2000
2	piggy	3	1000
3	Tony	6	NULL
4	Chery	7	NULL
5	dad	2	200

#甲开事务
start transaction;

#乙开事务
start transaction;

#甲
insert into person values(7,‘Wa’,4,10);
commit;
mysql> select * from person;

id	name	cardID	money
1	Zed	1	2000
2	piggy	3	1000
3	Tony	6	NULL
4	Chery	7	NULL
5	dad	2	200
7	Wa	4	10

#乙查询
mysql> select * from person;

id	name	cardID	money
1	Zed	1	2000
2	piggy	3	1000
3	Tony	6	NULL
4	Chery	7	NULL
5	dad	2	200
7	Wa	4	10

#甲
mysql> start transaction;
mysql> insert into person values(9,‘lili’,0,100);
Query OK, 1 row affected (0.00 sec)

#乙插入冲突数据会进入等待，到甲执行完毕才会返回结果，若数据不冲突，则插入成功。
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into person values(9,‘lili’,0,100);

#并行化也会出现幻读情况
直到退出transaction

#串行化的问题：性能变差

注：可能是Mysql版本问题，导致串行化和重复读下特性相同，有待进一步探究。