本文编写于 273 天前,最后修改于 269 天前,其中某些信息可能已经过时。

登录数据库mysql -uroot -p密码
查询数据库中所有数据库show databases;
use xxx
select * from xxx;
查看所有数据表
mysql> show tables;
Empty set (0.00 sec)
创建
mysql> create table pet (

-> owner varchar(20),
-> pecies varchar(20),
-> species varchar(20),
-> sex char(1),
-> birth date,
-> death date);

Query OK, 0 rows affected (0.60 sec)

show tables;
Tables_in_quanwu
pet

1 row in set (0.00 sec)

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

describe pet;
FieldTypeNullKeyDefaultExtra
ownervarchar(20)YES NULL
peciesvarchar(20)YES NULL
speciesvarchar(20)YES NULL
sexchar(1)YES NULL
birthdateYES NULL
deathdateYES NULL

6 rows in set (0.04 sec)

查看数据表中数据
mysql>select * from pet;
Empty set (0.00 sec)

如何往数据表中添加记录呢
insert into pet
values ('Puffball','Diane','hamster','f','1999-03-30',null);

mysql> select * from pet;
ownerpeciesspeciessexbirthdeath
PuffballDianehamsterf1999-03-30NULL

1 row in set (0.00 sec)

create table testType ( number TINYINT );

insert into testType values(127);

insert into pet values('wangcai','Harold','cat','f','1993-02-04',null);
insert into pet values('Claws','Gwen','cat','m','1994-03-07',null);

删除数据

delete from pet where name='xxx';

mysql> delete from pet where owner='wangcai';
Query OK, 1 row affected (0.00 sec)

mysql> select * from pet;
ownerpeciesspeciessexbirthdeath
PuffballDianehamsterf1999-03-30NULL
FluffyHaroldcatf1993-02-04NULL
ClawsGwencatm1994-03-07NULL

3 rows in set (0.00 sec)

如何修改数据

update pet set name='wangwangcai' where owner='xxx';

mysql> update pet set sex='m' where owner='wangcai';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

总结数据记录常见操作

---增加
INSETRT
---删除
DELETE
---修改
UPDATE
---查询
SELECT

建表约束。sql

--主键约束

它能够唯一确定一张表中的记录,也就是我们通过给某个字段添加约束,就可以使得字段不重复且不为空。
mysql> create table user(

->     id int primary key,
->     name varchar(20)
-> );

Query OK, 0 rows affected (0.03 sec)

insert into user values(1,"sansan");

mysql> insert into user values(1,"sansan");
Query OK, 1 row affected (0.13 sec)

mysql> insert into user values(1,"sansan");
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

mysql> insert into user values(2,"sansan");
Query OK, 1 row affected (0.00 sec)

mysql> insert into user values(null,"sansan");
ERROR 1048 (23000): Column 'id' cannot be null

--联合主键
--只要联合的主键值加起来不重复就可以
create table user2(

id int,
name varchar(20),
password varchar(20),
primary key(id,name)

);

insert into user2 values(1,'sansan','123');
insert into user2 values(2,'sansan','123');

--自增约束
--auto_increment
create table uuser3(

id int primary key auto_increment,
name varchar(20)    

);

insert into uuser3 (name) values('zhangsan');

mysql> insert into uuser3 (name) values('zhangsan');
Query OK, 1 row affected (0.02 sec)

mysql> select * from uuser3;
idname
1zhangsan
2zhangsan

2 rows in set (0.00 sec)

--如果我们创建表 的时候忘记 创建主键约束

create table user4(

id int,
name varchar(20)    

);

mysql> desc user4;
FieldTypeNullKeyDefaultExtra
idint(11)YES NULL
namevarchar(20)YES NULL

2 rows in set (0.00 sec)

--修改表结构添加主键

mysql> alter table user4 add primary key(id);
Query OK, 0 rows affected (0.11 sec)

Records: 0 Duplicates: 0 Warnings: 0
FieldTypeNullKeyDefaultExtra
idint(11)NOPRINULL
namevarchar(20)YES NULL

2 rows in set (0.00 sec)

--修改表结构删除主键
alter table user4 drop primary key;
mysql> alter table user4 drop primary key;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc user4;
FieldTypeNullKeyDefaultExtra
idint(11)NO NULL
namevarchar(20)YES NULL

2 rows in set (0.00 sec)

--使用 modify修改字段 添加约束

alter table user4 modify id int primary key;
FieldTypeNullKeyDefaultExtra
idint(11)NOPRINULL
namevarchar(20)YES NULL

--唯一约束
--约束修饰的字段的值不可以重复
create table user5(

id int,
name varchar(20)

);

mysql> alter table user5 add unique(name);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc user5;
FieldTypeNullKeyDefaultExtra
idint(11)YES NULL
namevarchar(20)YESUNINULL

2 rows in set (0.00 sec)

mysql> insert into user5 values(1,'zhangsan');
Query OK, 1 row affected (0.01 sec)

mysql> insert into user5 values(1,'zhangsan');
ERROR 1062 (23000): Duplicate entry 'zhangsan' for key 'name'

create table user6(

id int,
name varchar(20),
unique(name)
);
FieldTypeNullKeyDefaultExtra
idint(11)YES NULL
namevarchar(20)YESUNINULL

2 rows in set (0.00 sec)

--如何删除唯一约束
mysql> alter table user5 drop index name;
Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0
FieldTypeNullKeyDefaultExtra
idint(11)YES NULL
namevarchar(20)YES NULL

2 rows in set (0.00 sec)

--modify 添加
alter table user5 modify name varchar(20) unique;

-- 总结:
-- 1.建表的时候就添加约束
-- 2.可以使用alter..add...
-- 3.alter...modify...
-- 4.删除alter...drop

--非空约束not null、
--修饰 的字段不能为空NULL
create table user9(

id int,
name varchar(20) not null

);

mysql> desc user9;
FieldTypeNullKeyDefaultExtra
idint(11)YES NULL
namevarchar(20)NO NULL

2 rows in set (0.00 sec)

insert into user9 values(1,'张三');

insert into user9 (name) values('lisi');

mysql> select * from user9;
idname
1张三
NULLlisi

2 rows in set (0.00 sec)

--默认约束
--当我们插入字段值当时候没有传值,就会使用默认值 default
mysql> create table user10(

->     id int,
->     name varchar(20),
->     age int default 10
-> );

Query OK, 0 rows affected (0.10 sec)

mysql> desc user10;
FieldTypeNullKeyDefaultExtra
idint(11)YES NULL
namevarchar(20)YES NULL
ageint(11)YES 10

3 rows in set (0.00 sec)
mysql> insert into user10(id,name) values(1,'zhangsan');
Query OK, 1 row affected (0.01 sec)

mysql> select * from user10;
idnameage
1zhangsan10

1 row in set (0.00 sec)

--传了值就不会使用默认值
mysql> insert into user10 values(1,'zhangsan',19);
Query OK, 1 row affected (0.02 sec)

mysql> select * from user10;
idnameage
1zhangsan10
1zhangsan19

2 rows in set (0.00 sec)

--外键约束
--涉及到两个表:父表,字表
--主表,副表

--班级
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,'1st');
insert into classes values(2,'2st');
insert into classes values(3,'3rd');
insert into classes values(4,'4th');

mysql> select * from classes;
idname
11st
22st
33rd
44th

4 rows in set (0.00 sec)

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> insert into students values(1005,'里斯',5);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (quanwu.students, CONSTRAINT students_ibfk_1 FOREIGN KEY (class_id) REFERENCES classes (id))

--1.主表(父表) classes中没有的数据值,在副表中(子表)是不可以使用的;
-- 2 主表中的记录被副表引用是不可以被删除的

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

数据库的三大设计范式

-- 1.第一范式
--1NF

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

create table student(

id int primary key,
name varchar(20),
address varchar(30)

);

insert into student values(1,'zhang','zh_CN');
insert into student values(2,'li','zh-HK');
insert into student values(3,'zhang','zh_TW');

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

--范式越详细对于某些世纪操作可能更好,但是不一定都是好处

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

--订单表

create table myorder(

product_id int,
customer_id int,
produuct_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.第三范式
--3NF
--必须先满足第二范式,抛开主键列的其他列之间不能有传递依赖关系

mysql查询练习

学生
create table student(

sno varchar(20) primary key,
sname varchar(20) not null,
ssex varchar(10) not null,
sbirthday datetime,
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 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)

);

教师
create table teacher(

tno varchar(20) primary key,
tname varchar(20) not null,
tsex varchar(10) not null,
tbirtthday datetime,
prof varchar(20) not null,
depart varchar(20) not null

);

-- 往数据表中添加数据

insert into student values('101',"zhao",'m','1977-09-01','95033');
insert into student values('102',"qian",'w','1975-09-05','95031');
insert into student values('103',"sun",'m','1957-08-01','95033');
insert into student values('104',"li",'m','1997-07-01','95033');
insert into student values('105',"zhou",'w','1987-05-01','95031');
insert into student values('106',"wu",'m','1979-09-01','95031');

insert into teacher values('804',"zheng",'m','1958-12-01','副教授','computer');
insert into teacher values('856',"wang",'m','1969-03-21','讲师','scientist');
insert into teacher values('825',"ling",'w','1972-02-01','助教','xianyu');
insert into teacher values('831',"yu",'w','1977-12-11','院长','music');

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('101','3-245','86');
insert into score values('105','3-245','75');
insert into score values('103','3-245','68');
insert into score values('101','3-105','92');
insert into score values('103','3-105','88');
insert into score values('101','6-166','81');

--查询练习

  • 1.查询stucent表的所有信息
    mysql> select * from student;
snosnamessexsbirthdayclass
101zhaom1977-09-01 00:00:0095033
102qianw1975-09-05 00:00:0095031
103sunm1957-08-01 00:00:0095033
104lim1997-07-01 00:00:0095033
105zhouw1987-05-01 00:00:0095031
106wum1979-09-01 00:00:0095031

6 rows in set (0.00 sec)

  • 2.查询student表中记录的sname , ssex ,cless 列(上中*表示所有)
  1. sname,ssex,class from student;
snamessexclass
zhaom95033
qianw95031
sunm95033
lim95033
zhouw95031
wum95031

6 rows in set (0.00 sec)

  • 3.查询教师所有的单位不重复的depart列
    --distinct 排除重复

select distinct depart from teacher;

  • 4.查询score表中成绩在60-80之间的所有记录
    --查询区间between ...and...
mysql> select * from score where degree between 60 and 80;
snocnodegree
1033-24568
1053-24575

2 rows in set (0.00 sec)
-- 直接使用运算符比较

mysql> select * from score where degree >60 and degree < 80;
snocnodegree
1033-24568
1053-24575

2 rows in set (0.00 sec)

  • 5.查询score表中成绩为86,68,81的记录
    -- 表示或者关系的查询 in
mysql> select * from score where degree in(86,68,81);
snocnodegree
1013-24586
1016-16681
1033-24568

3 rows in set (0.00 sec)

  • 6.查询student表中“95031”班或者性别为女的同学记录
    mysql> select * from student where class ="95031" or ssex="w";
snosnamessexsbirthdayclass
102qianw1975-09-05 00:00:0095031
105zhouw1987-05-01 00:00:0095031
106wum1979-09-01 00:00:0095031

3 rows in set (0.00 sec)

  • 7.以class降序查询student表中的记录
    -- 升序降序
mysql> select * from student order by class desc;
snosnamessexsbirthdayclass
101zhaom1977-09-01 00:00:0095033
103sunm1957-08-01 00:00:0095033
104lim1997-07-01 00:00:0095033
102qianw1975-09-05 00:00:0095031
105zhouw1987-05-01 00:00:0095031
106wum1979-09-01 00:00:0095031

6 rows in set (0.00 sec)

--默认是升序的,asc

  • 8.以con升序,degree降序查询score表的所有记录
    mysql> select * from score order by cno asc,degree desc;
snocnodegree
1013-10592
1033-10588
1013-24586
1053-24575
1033-24568
1016-16681

6 rows in set (0.00 sec)

  • 9.查询“95031”班学生人数
    --统计 count
mysql> select count(*) from student where class='95031';
count(*)
3

1 row in set (0.00 sec)

  • 10.查询score表中的最高分的学生学号和课程号。(子查询,或者 排序
    mysql> select sno,cno from score where degree=(select max(degree) from score);
snocno
1013-105

1 row in set (0.00 sec)

--1.找到最高分
select max(degree) from score;
--2.找最高分的sno 和 cno
select sno,cno from score where degree=(select max(degree) from score);

--排序的做法

select sno,cno,degree from score order by degree;

mysql> select sno,cno,degree from score order by degree;
snocnodegree
1033-24568
1053-24575
1016-16681
1013-24586
1033-10588
1013-10592

6 rows in set (0.00 sec)

--limit第一个数字表示从多少开始
--第二个数字表示查多少条

mysql> select sno,cno,degree from score order by degree limit 0,1;
snocnodegree
1033-24568

1 row in set (0.00 sec)

mysql> select sno,cno,degree from score order by degree desc limit 0,1;
snocnodegree
1013-10592

1 row in set (0.00 sec)

  • 11.查询每门课的平均成绩
    • from course;

--avg()
select avg(degree) from score where cno='3-105';

mysql> select cno,avg(degree) from score group by cno;
cnoavg(degree)
3-10590.0000
3-24576.3333
6-16681.0000

3 rows in set (0.01 sec)

  • 12.查询score表中至少2名学生选修的并以三开头的课程平均分数

select cno from score group by cno

having count(cno)>=2;
cno
3-105
3-245

2 rows in set (0.00 sec)

--like模糊查询,以3开头
select cno from score group by cno
having count(cno)>=2 and cno like '3%';

--group by分组
--having带条件

select cno,avg(degree) from score
group by cno
having count(cno)>=2

and cno like '3%';
cnoavg(degree)
3-10590.0000
3-24576.3333

2 rows in set (0.00 sec)

  • 13.查询分数

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

-> where degree>70 and degree<90;
snodegree
10186
10181
10388
10575

4 rows in set (0.00 sec)

  • 14。查询所有学生的sname,cno和degree列
    mysql> select sname from student;
sname
zhao
qian
sun
li
zhou
wu

6 rows in set (0.00 sec)

mysql> select cno,degree from score;
cnodegree
3-10592
3-24586
6-16681
3-10588
3-24568
3-24575

6 rows in set (0.00 sec)

mysql> select sno.sname from student;
ERROR 1054 (42S22): Unknown column 'sno.sname' in 'field list'

mysql> select sno,sname from student;
snosname
101zhao
102qian
103sun
104li
105zhou
106wu

6 rows in set (0.00 sec)

mysql> select sno,cno,degree from score;
snocnodegree
1013-10592
1013-24586
1016-16681
1033-10588
1033-24568
1053-24575

6 rows in set (0.00 sec)

mysql> select sname,cno,degree from student,score

-> where student.sno=score.sno;
snamecnodegree
zhao3-10592
zhao3-24586
zhao6-16681
sun3-10588
sun3-24568
zhou3-24575

6 rows in set (0.00 sec)

  • 15.查询所有学生的sno,cname和degree列
    mysql> select cno,cname from course;
cnocname
3-105计算机导论
3-245操作系统
6-166数字电路
9-888音乐

4 rows in set (0.00 sec)

mysql> select cno,sno,degree from score;
cnosnodegree
3-10510192
3-24510186
6-16610181
3-10510388
3-24510368
3-24510575

6 rows in set (0.00 sec)

mysql> select cno,sno,degree from score;
cnosnodegree
3-10510192
3-24510186
6-16610181
3-10510388
3-24510368
3-24510575

6 rows in set (0.01 sec)

mysql> select sno,cname,degree from course,score

-> where course.cno = score.cno;
snocnamedegree
101计算机导论92
101操作系统86
101数字电路81
103计算机导论88
103操作系统68
105操作系统75

6 rows in set (0.00 sec)

  • 16.查询所有学生的sname,cname和degree
  1. -> student

cname -> course
degree -> score

mysql> select sname,cname,degree from student,course,score

-> where student.sno=score.sno and course.cno=score.cno;
snamecnamedegree
zhao计算机导论92
zhao操作系统86
zhao数字电路81
sun计算机导论88
sun操作系统68
zhou操作系统75

6 rows in set (0.00 sec)

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

  • 17.查询“95031”班学生每门课平均分

select * from student where class='95031';

select * from score where sno in (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;
cnoavg(degree)
3-24575.0000

1 row in set (0.00 sec)

  • 18.查询选修'3-245'课程到成绩高于‘103’号同学“3-245”成绩的所有同学的记录。

select degree from score where sno='103' and cno='3-245';

select * from score where cno='3-245' and degree>(select degree from score where sno='103' and cno='3-245');
snocnodegree
1013-24586
1053-24575
  • 19.查询成绩高于学好卫109,课程为3-245的成绩的所有记录
    • from score where and degree>(select degree from score where sno='103' and cno='3-245');
  • 20.查询学号为108,101的同学同年出生的所有学生 的sno,sname和sbirthday列。

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

mysql> select year(sbirthday) from student where sno in (101,105);
year(sbirthday)
1977
1987

2 rows in set (0.00 sec)

--多个不用等于用in
select * from student where year(sbirthday)in(select year(sbirthday) from student where sno in (101,105));

mysql> select * from student where year(sbirthday)in(select year(sbirthday) from student where sno in (101,105));
snosnamessexsbirthdayclass
101zhaom1977-09-01 00:00:0095033
105zhouw1987-05-01 00:00:0095031

2 rows in set (0.00 sec)

。。。。没有

  • 查询“zheng”教师任课 的学生成绩

select tno from teacher where tname='zheng';
select cno from course where tno=(select tno from teacher where tname='zheng');

select * from score where cno=(select cno from course where tno=(select tno from teacher where tname='zheng'));

snocnodegree
1013-24586
1033-24568
1053-24575

3 rows in set (0.07 sec)

  • 22.查询选修某课程的同学多于2人 的教师姓名

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

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

  • 23.查询95033和95031班全体学生的记录
    mysql> insert into student values('110','nc','m','1974-01-02','95038');

Query OK, 1 row affected (0.19 sec)

mysql> select * from student where class in ('95031','95033');
snosnamessexsbirthdayclass
101zhaom1977-09-01 00:00:0095033
102qianw1975-09-05 00:00:0095031
103sunm1957-08-01 00:00:0095033
104lim1997-07-01 00:00:0095033
105zhouw1987-05-01 00:00:0095031
106wum1979-09-01 00:00:0095031

6 rows in set (0.00 sec)

  • 24.查询存在有85分以上课程成绩的课程cno
    mysql> select cno,degree from score where degree>85;
cnodegree
3-10592
3-24586
3-10588

3 rows in set (0.00 sec)

  • 25.查询computer教师课成绩表
mysql> select * from course where tno in (select tno from teacher where depart='computer');
cnocnametno
3-245操作系统804

1 row in set (0.00 sec)

select * from score where cno in (select cno from course where tno in (select tno from teacher where depart='computer'));
snocnodegree
1013-24586
1033-24568
1053-24575

3 rows in set (0.00 sec)

  • 26.查询computer与music不同职称教师的tname和prof
    --union求并集

select prof from teacher where depart='computer';

select * from teacher where depart='music' and prof not in(select prof from teacher where depart='computer');
tnotnametsextbirtthdayprofdepart
831yuw1977-12-11 00:00:00院长music

1 row in set (0.03 sec)

select * from teacher where depart='computer' and prof not in(select prof from teacher where depart='music');
tnotnametsextbirtthdayprofdepart
804zhengm1958-12-01 00:00:00副教授computer

select * from teacher where depart='music' and prof not in(select prof from teacher where depart='computer')
union

select * from teacher where depart='computer' and prof not in(select prof from teacher where depart='music');
tnotnametsextbirtthdayprofdepart
831yuw1977-12-11 00:00:00院长music
804zhengm1958-12-01 00:00:00副教授computer

2 rows in set (0.04 sec)
1 row in set (0.00 sec)

  • 27.查询选修编号为“3-105”课程且成绩至少高于选修编号为“3-245”的同学Cno,Sno和Degree。
    -- 并按照degree从高到低次序排序
select * from score where cno='3-245';
snocnodegree
1013-24586
1033-24568
1053-24575

3 rows in set (0.00 sec)

mysql> select * from score where cno='3-105';
snocnodegree
1013-10592
1033-10588

2 rows in set (0.00 sec)
--至少?大于其中至少一个,any

select * from score where cno='3-105' and degree>any(select degree from score where cno='3-245');
snocnodegree
1013-10592
1033-10588

2 rows in set (0.06 sec)
--order by是排序

  • 28.查询选修编号为3-105且成绩 高于选修编号为‘3-245’课程的同学的Cno,Sno和Degree。
select * from score where cno='3-105' and degree>all(select degree from score where cno='3-245') order by degree desc;
snocnodegree
1013-10592
1033-10588

2 rows in set (0.00 sec)

  • 29.查询所有教师和同学的name,sex和birthday
    -- 别名 as

select tname as name,tsex as sex,tbirtthday as birthday from teacher
union

select sname,ssex,sbirthday from student;
namesexbirthday
zhengm1958-12-01 00:00:00
lingw1972-02-01 00:00:00
yuw1977-12-11 00:00:00
wangm1969-03-21 00:00:00
xianw1966-12-02 00:00:00
zhaom1977-09-01 00:00:00
qianw1975-09-05 00:00:00
sunm1957-08-01 00:00:00
lim1997-07-01 00:00:00
zhouw1987-05-01 00:00:00
wum1979-09-01 00:00:00
ncm1974-01-02 00:00:00

12 rows in set (0.00 sec)

  • 30.查询所有女教师 和女同学 的name,sex和birthday
  1. tname as name,tsex as sex,tbirtthday as birthday from teacher where tsex='w'

union

select sname,ssex,sbirthday from student where ssex='w';
namesexbirthday
lingw1972-02-01 00:00:00
yuw1977-12-11 00:00:00
xianw1966-12-02 00:00:00
qianw1975-09-05 00:00:00
zhouw1987-05-01 00:00:00

5 rows in set (0.00 sec)

  • 31.查询成绩比该课程平均成绩低的同学的成绩表
  1. cno,avg(degree) from score group by cno;
cnoavg(degree)
3-10590.0000
3-24576.3333
6-16681.0000

3 rows in set (0.00 sec)

select * from score;
a b
+-----+-------+--------+ +-----+-------+--------+
| sno | cno | degree | | sno | cno | degree |
+-----+-------+--------+ +-----+-------+--------+
| 101 | 3-105 | 92 | | 101 | 3-105 | 92 |
| 101 | 3-245 | 86 | | 101 | 3-245 | 86 |
| 101 | 6-166 | 81 | | 101 | 6-166 | 81 |
| 103 | 3-105 | 88 | | 103 | 3-105 | 88 |
| 103 | 3-245 | 68 | | 103 | 3-245 | 68 |
| 105 | 3-245 | 75 | | 105 | 3-245 | 75 |
+-----+-------+--------+ +-----+-------+--------+
6 rows in set (0.00 sec) 6 rows in set (0.00 sec)

select * from score a where degree < (select avg(degree) from score b where a.cno=b.cno);

  • 32.查询所有任课教师的Tname和Deoart

select * from teacher;

--课程表中安排了课程

select * from course;
cnocnametno
3-105计算机导论825
3-245操作系统804
6-166数字电路856
9-888音乐831
select * from teacher where tno in (select tno from course);
tnotnametsextbirtthdayprofdepart
804zhengm1958-12-01 00:00:00副教授computer
825lingw1972-02-01 00:00:00助教xianyu
831yuw1977-12-11 00:00:00院长music
856wangm1969-03-21 00:00:00讲师scientist

4 rows in set (0.08 sec)

  • 33.查询至少有2名男生的班号

select * from student;

select class from student where ssex='m' group by class having count(*)>1;
class
95033

1 row in set (0.00 sec)

  • 34.查询student表中定义字段开头没有的同学记录
select * from student;
snosnamessexsbirthdayclass
101zhaom1977-09-01 00:00:0095033
102qianw1975-09-05 00:00:0095031
103sunm1957-08-01 00:00:0095033
104lim1997-07-01 00:00:0095033
105zhouw1987-05-01 00:00:0095031
106wum1979-09-01 00:00:0095031
110ncm1974-01-02 00:00:0095038

7 rows in set (0.00 sec)

select * from student where sname not like 'n%';
snosnamessexsbirthdayclass
101zhaom1977-09-01 00:00:0095033
102qianw1975-09-05 00:00:0095031
103sunm1957-08-01 00:00:0095033
104lim1997-07-01 00:00:0095033
105zhouw1987-05-01 00:00:0095031
106wum1979-09-01 00:00:0095031

6 rows in set (0.00 sec)

  • 35.查询student表中每个学生的姓名和年龄

--年龄=当前年份-出生年份

select year(now());
year(now())
2020

1 row in set (0.00 sec)

select year(sbirthday) from student;
year(sbirthday)
1977
1975
1957
1997
1987
1979
1974

7 rows in set (0.00 sec)

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

  • 36.查询student表中最大和最小的sbirthday日期 值
select max(sbirthday) as max,min(sbirthday) as min from student;
maxmin
1997-07-01 00:00:001957-08-01 00:00:00

1 row in set (0.01 sec)

  • 37.以班号和年龄从大到小的顺序查询student表中的全部记录
mysql> select * from student order by class desc,sbirthday desc;
snosnamessexsbirthdayclass
110ncm1974-01-02 00:00:0095038
104lim1997-07-01 00:00:0095033
101zhaom1977-09-01 00:00:0095033
103sunm1957-08-01 00:00:0095033
105zhouw1987-05-01 00:00:0095031
106wum1979-09-01 00:00:0095031
102qianw1975-09-05 00:00:0095031

7 rows in set (0.00 sec)

  • 38.查询男教师及其所上课程
    • from course where tno in (select * from teacher where tsex='m');
  • 39.查询最高分同学的sno,cno和degree列

select max(degree) from score;

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

  • 40.查询和zhou同性别的所有学生的name
    mysql> select sname from student where ssex = (select ssex from student where sname='zhou');
sname
qian
zhou

2 rows in set (0.00 sec)

  • 41.查询和li同性别且同班的所有学生的sname
  1. sname from student

where ssex = (select ssex from student where sname='li')

and class=(select class from student where sname='li');
sname
zhao
sun
li

3 rows in set (0.00 sec)

  • 42.查询所有选修操作系统的男同学成绩表
    • from student where ssex='m';

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='m');
snocnodegree
1013-24586
1033-24568

2 rows in set (0.00 sec)

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

-- 现查询所有同学的sno,cno和grade列

select sno,cno,grade from score,grade where degree between low and upp;
snocnograde
1013-105A
1013-245B
1016-166B
1033-105B
1033-245D
1053-245C

6 rows in set (0.00 sec)

SQl 的四种链接查询

内链接
inner join 或者 join

外链接

  1. 左链接 left join 或者 left outer join

2.右链接 right join 或者 right outer join

3.完全外链接 full join 或者 full outer join

  • 创建两个表

create database testJoin;

--person表
id,
name,
cardId

create table person(

id int,
name varchar(20),
cardId int

);

--card表
id,
name
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查询(内连接)
    • from person inner join card on person.cardId=card.id;
idnamecardIdidname
1张三11饭卡
2李四33农行卡

2 rows in set (0.00 sec)

--内联查询,其实就是两张表中的数据,通过某个字段相对,查询出相关记录数据
select * from person join card on person.cardId=card.id;

  • 2.left join(左外连接)
    • from person left join card on person.cardId=card.id;
idnamecardIdidname
1张三11饭卡
2李四33农行卡
3王五6NULLNULL

3 rows in set (0.00 sec)
--左外连接,会把左边表里面的所有数据取出来,而右边表中的数据,如果有相等的,就显示出来
--如果没有就补null

select * from person left outer join card on person.cardId=card.id;
idnamecardIdidname
1张三11饭卡
2李四33农行卡
3王五6NULLNULL

3 rows in set (0.00 sec)

  • 3.right join(右外连接)
    • from person right join card on person.cardId=card.id;
idnamecardIdidname
1张三11饭卡
2李四33农行卡
NULLNULLNULL2建行卡
NULLNULLNULL4工商卡
NULLNULLNULL5邮政卡

5 rows in set (0.00 sec)

--左外连接,会把右边表里面的所有数据取出来,而左边表中的数据,如果有相等的,就显示出来
--如果没有就补null
select * from person right outer join card on person.cardId=card.id;

  • 4.full join(全外连接)
    • from person full join card on person.cardId=card.id;

mysql不支持full join
可以用union并上两个

mysql事务

mysql中,事务其实是一个最小 的不可分割的工作单元。事务能够保证一个业务的完整性
多条sql语句,可能会有同时成功的要求,要么就同时失败
mysql中如何控制事务

1.mysql 默认开启事务(自动提交)

mysql> select @@autocommit;
@@autocommit
1

1 row in set (0.00 sec)

--默认事务开启的作用
--当我们去执行一个sql语句,效果会立即体现出来,且无法撤回

create database bank;

create table user(

id int primary key,
name varchar(20),
money int

);
insert into user values(1,'a',1000);

--事务回滚:撤销sql语句执行效果
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user;
idnamemoney
1a1000

1 row in set (0.00 sec)

-- 设置mysql自动提交为false
set autocommit=0;

--自动提交?@@autocommit=1
--手动提交?commit;
--事务回滚?rollback;

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

begin;
update xxxx;

rollback;
可以回滚

start transaction同理

事务的四大特征:
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 uncommitted
如果有事务a,和事务b
a 事务对数据进行操作,在操作过程中,事务没有被提交,但是b可以看见a的操作结果

bank数据库 user表

insert into user values(3,'小明',1000);
insert into user values(4,'淘宝店',1000);

--如何查看数据库的隔离级别

mysql 8.0
--系统级别的
select @@global.transaction_isolation;
--会话级别的
select @@transaction_isolation;

mysql> select @@global.transaction_isolation;
@@global.transaction_isolation
REPEATABLE-READ

1 row in set (0.00 sec)

--如何修改数据库的隔离级别
set global transaction isolation level read uncommitted;

select @@global.transaction_isolation;
@@global.transaction_isolation
READ-UNCOMMITTED

1 row in set (0.00 sec)

--转账:小明在淘宝店买鞋子:800
start transaction;
update set money=money-800 where name='小明';
update set money=money+800 where name='淘宝店';

rollback;

没了