数据库

终端操作数据库基本命令：

进入数据库：mysql -uroot -p
（输入密码：生日）
显示数据库服务器中所有的数据库：show database;（一定要加；）
选择数据库a进行操作：use a；
显示数据库a中虽所有的表：show tables；
SQL语句查询语句—显示表admin中所有数据：select * from admin；
查询语句—显示表admin中所有的username=“hanwenshuo”的数据：select * from admin where username=“hanwenshuo”；
退出数据库服务器：exit
在数据库中创建我们的数据库：create database test；
查看某个数据库中所有的数据表：show tables;
创建一个数据表：
CREATE TABLE pet(
name VARCHAR(20),
owner VARCHAR(20),
species VARCHAR(20),
sex CHAR(20),
birth DATE,
death DATE);

查看创建好的数据表的结构：describe pet；
在数据表中添加数据记录：INSERT INTO pet
VALUES (‘puffball’,‘Dave’,‘hanster’,‘f’,‘2019-02-30’,NULL);
mysql常用数据类型：（菜鸟教程）
三类：
数值：
日期：
字符串：

数据类型的选择：日期选择按照格式
数值和字符串按照大小！

删除数据：delete from pet where name=‘puffball’；
修改数据：update pet set name=‘wangwangcai’ where owner=‘dave’；

mysql建表约束

主键约束
他能够唯一确定一张表中的一条记录，也就是我们同股派给某个字段添加约束，就可以使得该字段不重复且不为空。
联合主键：由多个字段组成的主键。
只要联合的主键的值加起来不重复就行。联合主键的任何一个字段都不能为空。
create table user2(id int,name varchar(20),password varchar(20),primary key(id,name));
primary（id，name）；将id和name 设置成了联合主键约束。
create table user（）；
–自增约束
create table user3(id int primary key auto_increment,name varchar(20));
将id设置成为自增约束。insert into user3 (name) values(‘zhangsan’);
自增约束和主键约束结合起来使用。
创建表的时候忘记创建主键约束怎么办？
添加主键约束：
add方式：alter table user4 add primary key(id);
使用modify修改字段添加约束：alter table user4 modify id int primary key;
如何删除：alter table user4 drop primary key;

**–外键约束 foreign key…regerence classes …
–到两个表：父表，子表
–主表，副表。
-班级
create table classes(id int primary key,name varchar(20));
-学生
create table students(id int primary key,name vatchar(20),class_id int,foreign key(class_id) references classes(id));
将class_id设置为外键约束。
1.主表 （父表）没有的数据，在副表中不可以使用的。
2.主表中的记录被副表引用，是不可以被删除的。

–唯一约束unique
约束修饰的字段的值不可以重复
alter table user5 add unique(name);
那么字段不可重复。
创建表的时候声明唯一约束：
create table user7( id int, name varchar(20) unique);
create table user6( id int, name varchar(20), unique(name));
对两个字段声明唯一约束：
create table user8( id int, name varchar(20), unique(id,name));
unique(id,name)两个组合起来不重复就ok。类似于联合主键。
删除唯一约束：
alter table user7 drop index name;
modify添加：alter table user7 modify name varchar(20) unique;

–非空约束 not full
它所修饰的字段不可为空
create table user9(id int,name varchar(20) not NULL);
可以使用alter add，modify来更改字段加上非空约束。
alter …drop…删除非空约束。

–默认约束 default
当我们插入字段值的时候，如果没有传值，就会使用默认值。
create table user10(id int,name varchar(20),age int default 10);
insert into user10 (id,name) values(1,‘zhangsan’)；使用默认值，注意加字段。
insert into user10 values(1,‘zhangsan’,20)；传了值就不会使用默认值了。

数据库设计范式

第一范式：1NF
表中的所有字段都是不可分割的原子值–列不可拆分
字段值还可以拆分的就不满足第一范式：
例如:
create table syudent3(
id int prinmary key,
name varchar(20),
address varchar(100))；
address：中国辽宁省大连市甘井子区海事大学；
应该拆分为：
create table syudent3(
id int prinmary key,
name varchar(20),
country varchar(20),
privence varchar(30),
city varchar(30),
details varchar(30))；
范式，设计的越详细，对某些实际操作可能更好，但是不一定都是好处。
address-》country | privence | city | detailes

第二范式：2NF
–必须是满足第一范式的前提下，第二范式要求，除主键外的每一列都必须完全依赖于主键。
–如果要出现不完全依赖，只可能发生在联合主键的情况下。
craete table myorder(
product_id int,
cusomer_id int,
product_name varchhar(20),
customer_name varchar(20),
primary key(product_id,customer_id)
);
craete table myorser(
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)
);
分成三个表后就完成了第二范式。

第三范式
–3NF
-必须先满足第二范式，除开主键列的其他列之间不能有传递依赖关系。

练习

create table student(
sno varchar(20) primary key,
sname varchar(20) not null,
ssex varchar(10) not null,
sbrithday datetime,
class varchar(20));

create table teacher(
tno varchar(20) primary key,
tname varchar(20) not null,
tsex varchar(10),
tbirthday datetime,
prof varchar(20) not null,
depart varchar(20) not null);

create table course(
cno varchar(20) prinmary key,
cname varchar(20) not null,
tno varchar(20) not null,
foreign key(tno) reference teacher(tno));

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

1.查询表student中所有的数据
select * from student；
2.查询student表中所有记录的sname，sex和class列。
select sname,ssex,class from student;
3.查询teacher所有单位及不重复的depart列。
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;
and–表示并且
5.查询score表中成绩为85，86或88的记录。
select * from score where degree in(85,86,88);
6.查询student表中“95031”班或者性别为“female”的同学的记录。
select * from student where class=‘95031’ or sex=‘female’;
or–表示或者
7.以class降序查询student 表的所有记录
***降序desc
select * from student order by class desc;
升序asc或者默认
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”班的学生人数
–统计count
select count() from student where class=‘95031’;
10.查询score表中最高分的学生号和课程号。（子查询或者排名）
–条件查询
（1）找到最高分（2）找最高分的sno和cno；
select sno,cno from score where degree=(select max(degree) from score);
–排序做法：
select sno,cno,degree from score order by degree desc limit 0,1;
将score中的degree倒序排列后将第一行（limit 0，1）取出其sno，cno。
limit n，m：表示从第n个数据开始查询m条数据。
11.查询每门课的平均成绩
计算课程105的平均成绩
select avg(degree) from score where cno=‘3-105’;
avg()计算平均
计算每个课程的平均成绩：
–group by 分组
select cno,avg(degree) from score group by cno;
12.查询score表中至少有两名学生选修的并且以3开头的课程名称。
分段查询：（1）分组group by cn
（2）group by cno 分组后面加查询条件 having并不是where
（3） like 3%：模糊查询；
select cno from score group by cno having count(cno)>=2 and cno like ‘3%’;

13，查询分数大于70，小于90的sno列。
select sno,degree from score where degree>70 and degree<90;
select sno,degree from score where degree between 70 and 90;

多表查询
14 查询所有学生的sname，cno和degree列。
sname，cno，degree不在一个表中
分别查：
select cno,dgree from score;
select sno,sname from student;
组合查：
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;
起别名
select sname,cname,degree,student.sno as stu_sno,course.cno from student,course,score where student.sno=score.sno and course.cno=score.cno;
24查询除‘计算机系’教师所教课程的成绩表。
select * from score where cno in (select cno from course where tno in (select tno from teacher where depart=“jisuanjixi”));

26 查询“计算机系”与“电子工程系” 不同职称的教师的tname
select * from teacher where depart=“jisuanjixi” and prof not in (select prof from teacher where depart=‘dianzigongchengxi’)
union
select * from teacher where depart=“dianzigongchengxi” and prof not in (select prof from teacher where depart=‘jisuanjixi’);

求并集：union

27.查询选修编号为“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;

28 查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的cno，sno和degree。
大于245中所有用all
select * from score where cno=“3-105” and degree>**all(**select degree from score where cno=‘3-245’);

29.查询所有教师和同学的name，sex和birthday
联合（求并）
select tname,tsex,tbirthday from teacher
union
select sname,ssex,sbrithday from student;
取别名 as
select tname as name,tsex as sex,tbirthday as birthday from teacher union select sname,ssex,sbrithday from student;
30 查询所有女教师和女学生的name，sex和birthday。
select tname as name,tsex as sex,tbirthday as birthday from teacher where tsex=“female” union select sname,ssex,sbrithday from student where ssex=“female”;

31.查询成绩该课程比平均成绩低的同学的成绩表。
建立了表a和b应该是给score起了别名
select * from score a where degree<(select avg(degree) from score b where a.cno=b.cno);

32.查询所有任课教师的Tname和Depart。

select tname,depart from teacher where tno in (select tno from course);

33查询至少有两名男生的班号
先把男生选出，再进行分组，分组后筛选数量大于1的。
select class from student where ssex=“male” group by class having count(*)>1;

34查询student表中不姓王的同学的记录
模糊查询
select * from student where sname not like ‘wang%’;
35 查询student表中每个学生的姓名和年龄
年龄=当前年份-出生年份
year（）函数和now（）函数
select sname,**year(now())-**year(sbrithday) as ‘age’ from student;

36 .查询student表中最大和最小的sbrithday日起值
max（） min（）
select max(sbrithday) as max,min(sbrithday) as min from student;

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

38.查询“male”教师及其锁上的课程
select * from course where tno in (select tno from teacher where tsex=“male”);

39.查询最高分同学的sno，cno和dgree列。
select * from score where degree=(select max(degree) from score);

40.查询和李俊同性别的所有同学的sname
select sname from student where ssex=(select ssex from student where sname=“lijun”);

41.查询和李俊同性别同班的所有同学的sname
select sname from student where ssex=(select ssex from student where sname=“lijun”) and class=(select class from student where sname=“lijun”);

42.查询所有选修“计算导论”课程的“male”的成绩表。
select cno from course where cname=“daolun”；
select sno from student where ssex=“male”；
select * from score；
select * from score where cno in (select cno from course where cname=“daolun”) and sno in(select sno from student where ssex=“male”);

43.假设使用如下命令建立了一个grade表
create table grade(low int(3),upp int(3),grade char(1));
insert into grade values(90,100,‘A’);
insert into grade values(80,89,‘B’);
insert into grade values(70,79,‘C’);
insert into grade values(60,69,‘D’);
insert into grade values(0,59,‘E’);
按等级划分
select sno,cno,grade from score,grade where degree between low and upp;

SQL四种连接查询-内连接，左连接，右连接

内连接
inner join 或者 join

外连接
1.左连接 left join 或者 left outer join
2.右连接 right join 或者 right outer join
3. 完全外连接 full join 或者 full outer join

创建两个表
person表{
id，
name，
cardId
}
card表{
id，
name
}
–1.inner join 查询内联查询=其实就是两张表中的数据，通过某个字段相对，查询出相关记录数据。
select * from person inner join card on person.cardId=card.id;
select * from person join card on person.cardId=card.id;

–2.left join（左外连接）==左外连接，会把左边表里面的所有数据取出来。而右边表中的数据，如果与相等的，就显示出来，如果没有，就补NULL。
select * from person left join card on person.cardId=card.id;
select * from person left outer join card on person.cardId=card.id;

–3.right join（右外连接）==会把右边表里的数据取出来，而左边表中的数据，如果有相等的，就显示出来。如果没有，就会补NULL
select * from person right join card on person.cardId=card.id;
select * from person right outer join card on person.cardId=card.id;

–4.full join （全外连接）
select * from person full join card on person.cardId=card.id;
mysql不支持全连接full join，可以通过union 左连接和右连接
连接的好处就是不用去创建外键就是通过他们某个字段值之间的相等来求交集和并集。

**

mysql事务

**
事务就是一个最小的不可分割单元，事务能够保证一个业务的完整性。
比如我们的银行转账：
a->-100
update user set money=money-100 where name=‘a’;
b->+100
update user set money=money+100 where name=‘b’;

–实际程序中，如果只有一条语句执行成功了，而另外一条没有成功？–出现数据前后不一致

–多条sql语句，可能会有同时成功的要求，要么就同时失败。
事务出现的目的就是解决这种问题：
–mysql中如何控制事务？
1.mysql默认开启事务（自动提交）。
默认事务开启的作用是什么？
–当我们去执行一个sql语句的时候，效果会立即体现出来，且不能回滚。
create database bank；
事务回滚rooback： 撤销SQL语句执行效果。
–设置MySQL自动提交为false
set autocommit=0；
select @@autocommit；
设置自动提交为false后可以回滚。
手动提交-commit后不可以回滚了，持久性。
事务可以自动提交，@@autocommit=1；
也可以手动提交，commit；
也可进行回滚。rollback；
事务给我们提供一个返回机会。

begin；或者 start transaction；都可以手动开启一个事务。
手动开启事务
begin;/start transaction；
在没有提交的时候还可以rollback；
一旦commit提交就不能rollback了。

事务的特征

事务的四大特征ACID
A：原子性：事务是最小的单位，不可以再分割
C：一致性：事务要求，同一事务中的SQL语句，必须保证同时成功或者同时失败。
I：隔离性：事务1和事务2之间是具有隔离性的。
D：持久性：事务一旦结束（commit，rollback），就不可以返回。

事务开启：
1.修改默认提交 set autocommit=0；
2.begin；
3.start transaction;

事务提交：
commit；

事务手动回滚：
rollback；

事务的隔离性：
–四种隔离性：
1.read uncommitted；读未提交的
2.read committed； 读已经提交的
3.repeatable read; 可以重复读（数据库默认隔离级别）
4.serializable; 串行化

1.read uncommited
如果有事务a和事务b，a事务对数进行操作，在操作过程中，事务没有被提交，但是b可以看见a操作的结果。
如何查看数据的隔离级别：
mysql 8.0
系统级别：
select @@global.transaction_isolation;
会话级别：
select @@transaction_isolation；
mysql 5.X
系统级别：
select @@global.tx_isolation;
会话级别：
select @@global.tx_isolation

如何修改隔离级别：
set global transaction isolation level read uncommitted;
查看数据隔离级别：
select @@global.transaction_isolation;
如果两个不同的地方，都在进行操作，如果事务a开启之后，他的数据可以被其他事务读取到，这样就会出现脏读；
脏读：一个事务读到了另外一个事务没有提交的数据，就叫做脏读。
实际开发是不允许脏读出现的。
2.read uncommited；读已提交的
修改数据隔离级别为read committed
set global transaction isolation level read committed;
–虽然只能读到提交的数据，但是还会出现问题，就是读取同一个表的数据，发现前后不一致。
–出现不可重复读取：read committed；
3.repeatable read； 可以重复读
事务隔离性-幻读
在REPEARTED-READ隔离界别下出现问题
–两个事务，事务A操作和事务B同时操作一张表，事务a提交的数据也不能被事务b读到，就可以造成幻读。

隔离界别–串行化
–当user表被另外一个事务操作的时候，其他事务里面的写操作，是不可以进行的。
–进入排队状态（串行化），直到a那边的事务提交完成，b这边的写入操作才会执行。
–在没有等待超时的情况下。

串行化带来的问题就是：性能特差。

性能比较：read-uncommited>read-committed>repeatable-read>serializable;
隔离界别越高，性能越差。

使用可视化工具操作数据库：

使用编程语言中操作数据库：