前言

一切從創(chuàng)建數(shù)據(jù)庫開始
數(shù)據(jù)庫→基本表（創(chuàng)建-增刪改查）→視圖（創(chuàng)建-增刪改查）

一、數(shù)據(jù)類型

1.1 數(shù)值型

int #4個字節(jié)
smallint #2個字節(jié)
tinyint #1個字節(jié)
float   #浮點(diǎn)型
numeric/decimal #小數(shù)

1.2 字符型

char   #固定長度
varchar    #可變長度
text     #文本

1.3 日期型

datetime   #8個字節(jié)
year       #年
date       #天
time       #時間

二、數(shù)據(jù)庫

2.1 創(chuàng)建數(shù)據(jù)庫

create database 數(shù)據(jù)庫名
default character set utf8;

2.2 修改數(shù)據(jù)庫

alter database 數(shù)據(jù)庫名;

2.3 刪除數(shù)據(jù)庫

drop database 數(shù)據(jù)庫名;

三、基本表

3.1 表結(jié)構(gòu)

3.1.1 創(chuàng)建表結(jié)構(gòu)

定義

create table 表名
(
列名  數(shù)據(jù)類型  約束條件,
列名  數(shù)據(jù)類型  約束條件,
……
);

創(chuàng)建學(xué)生表student
唯一性標(biāo)識 id:整型，自動增長列，主鍵
學(xué)號 sno：9個字符，非空，不允許重復(fù)
姓名 sname：20個字符,非空
性別 ssex：2個字符，男或女
年齡 sage：tinyint，15到45之間
系別 sdept：20個字符，默認(rèn)值為計算機(jī)系

create table student
(
id int auto_increment primary key,
sno char(9) not null unique,
sname char(20) not null,
ssex char(2) check(ssex in ('男','女'))，
sage smallint check(sage>=15 and sage<=45),
sdept char(20) default '計算機(jī)系'
);

創(chuàng)建課程表course
課程號 cno：4個字符 主鍵
課程名 cname： 50個可變長字符，不允許為空
先修課程號 cpno： 4個字符
學(xué)分 ccredit： smallint
先修課程號 cpno 參照 課程號cno 取值

create table course
(
cno char(4) primary key,
cname varchar(50) not null,
cpno char(4),
ccredit smallint,
foreign key(cpno) references course(cno)
);

創(chuàng)建成績表sc
學(xué)號 sno：9個字符
課程號 cno：4個字符
成績 grade： smallint
sno,cno：組合主鍵
sno參照student表的sno取值
cno參照course表的cno取值

create table sc
(
sno char(9) not null,
cno char(4) not null,
grade smallint,
primary key(sno,cno),
foreign key(sno) references student(sno),
foreign key(cno) references course(cno)
);

3.1.2 修改表結(jié)構(gòu)

定義

alter table 表名 modify 列名 新數(shù)據(jù)類型 約束;

例：修改student的sdept為40個字符寬度

alter table student modify sdept char(40);

3.1.3 增加列

定義

alter table 表名 add 列名 數(shù)據(jù)類型 約束;

例：在student中增加address列

alter table student add address varchar(50) not null;

3.1.4 刪除列

定義

alter table 表名 drop column 列名;

例：刪除student的sage列

alter table student drop column sage;

3.1.5 增加約束

定義

alter table 表名 add constraint 約束名 約束類型(列名);

例：在student中增加sno為主關(guān)鍵字

alter table student add primary key(sno);

例：添加sc的sno列的外碼約束為student的sno

alter table sc add foreign key(sno) references studnet(sno);

3.1.6 刪除約束

定義

alter table 表名 drop 約束類型 約束名

例：刪除student的主鍵約束

alter table student drop primary key

3.1.7 刪除表

定義

drop table 表名

3.2 數(shù)據(jù)查詢

定義

select 查詢目標(biāo)
from 目標(biāo)所在表
where 條件
group by 分組依據(jù)
having 分組條件
order by 排序依據(jù)
limit 限制;

3.2.1 簡單查詢

1. 查詢?nèi)w學(xué)生的學(xué)號、姓名

select sno,sname from student;

2. 查詢?nèi)苛?/li>

select * from student;
select * from course;
select * from sc;

3. 查詢?nèi)w學(xué)生的姓名和出生年份

select sname as 'student name',2023-sage 'birthday' from student;

3.2.2 條件（where）

4. 查詢計算機(jī)系全體學(xué)生的姓名

select sname 
from student 
where sdept='計算機(jī)系';

5. 查詢年齡小于20歲的學(xué)生的姓名

select sname 
from student 
where sage<20;

6. 查詢計算機(jī)系年齡小于20歲的學(xué)生的姓名

select sname 
from student 
where sage<20 and sdept='計算機(jī)系';

7. 查詢年齡介于22歲到24歲的學(xué)生姓名和年齡

select sname,sage 
from student 
where sage>=22 and sage<=24;

select sname,sage 
from student 
where sage between 22 and 24;

8. 查詢信息系、數(shù)學(xué)系和計算機(jī)系學(xué)生的姓名和性別

select sname,ssex 
from student 
where sdept='信息系' 
or sdept='數(shù)學(xué)系' 
or sdept='計算機(jī)系';

select sname,ssex 
from student 
where sdept in ('信息系','數(shù)學(xué)系','計算機(jī)系');

9. 查詢未確定系學(xué)生的基本信息

select * 
from student 
where sdept is null;

10. 查詢無考試成績的學(xué)生的學(xué)號

select sno 
from sc 
where grade is null;

11. 查詢有考試成績的學(xué)生的學(xué)號和成績

select sno,grade 
from sc 
where grade is not null;

3.2.3 消除（distinct）

12. 查詢由哪些學(xué)生選修了課程，只需給出學(xué)號（消除重復(fù)值）

select distinct sno 
from sc;

3.2.4 模糊（like）

13. 查詢姓王的同學(xué)的基本信息

select * 
from student 
where sname like "王%";

14. 查詢姓名中有王字的同學(xué)的基本信息

select * 
from student
where sname like "%王%";

3.2.5 排序（order by）

15. 將學(xué)生按年齡由小到大排序

select * 
from student 
order by sage asc;

16. 輸出選修c02課程學(xué)生的學(xué)號和成績，按成績降序排序

select sno,grade 
from sc 
where sno='c02' 
order by grade desc;

3.2.6 分組（group by）

17. 統(tǒng)計學(xué)生的總?cè)藬?shù)

select count(*) 
from student 
group by sno;

18. 統(tǒng)計選修了課程的學(xué)生的人數(shù)

select count(distinct sno) 
from sc 
group by sno;

19. 計算學(xué)號為200215121學(xué)生的考試成績總和

select sum(grade) 
from sc 
where sno='200215121' 
group by sno;

20. 計算c01課程的平均成績

select avg(grade) 
from sc 
where cno='c01' 
group by cno ;

21. 查詢最大的學(xué)生年齡

select max(sage) 
from student 
group by sno;

22. 統(tǒng)計每門課的平均成績，輸出課程號和平均成績

select cno,avg(grade) 
from sc 
group by cno;

23. 統(tǒng)計每門課的選修人數(shù)，輸出課程號和課程選修人數(shù)

select cno,count(*) 
from sc 
group by cno;

24. 查詢每名學(xué)生的選課門數(shù)和平均成績

select sno,count(*),avg(grade) 
from sc 
group by sno;

25. 查詢選修了3門以上課程的學(xué)生的學(xué)號

select sno 
from sc 
group by sno 
having count(*)>3;

26. 查詢選課門數(shù)大于等于4門的學(xué)生的平均成績和選課門數(shù)

select sno,avg(grade),count(*) 
from sc
group by sno 
having count(*)>=4;

27. 查詢選修門數(shù)超過3門，且平均分在70以上的學(xué)生的學(xué)號，選修門數(shù)，平均成績，并按成績從高到低排序

select sno,count(*),avg(grade) 
from sc
where grade>=60
group by sno
having count(*)>3 and avg(grade)>70
order by avg(grade) desc;

3.2.7 限制（limit）

28. 查詢平均成績前三名同學(xué)的學(xué)號，姓名，平均成績

select student.sno,student.sname,avg(sc.grade) from student,sc
where student.sno=sc.sno
group by student.sno
order by avg(sc.grade)
limit 3;

3.2.8 單行輸出（group_concat）

29. 查詢200215121學(xué)生的選課情況，顯示：學(xué)號，課程號（橫向排列成一行，用’,'分隔）

select sno,group_concat(cno) 
from sc 
where sno='200215121';

30. 查詢每個學(xué)生的選課情況，顯示：學(xué)號，課程號（橫向排列成一行，用’,'分隔）

select sno,group_concat(cno) 
from sc 
group by sno;

31. 查詢學(xué)生的選課情況，要求輸出學(xué)號，姓名，課程門數(shù)，課程名列表（用逗號分隔），按照學(xué)號升序排序

select student.sno,student.sname,count(sc.cno),
group_concat(cname order by cname separator ',') 
from sc,student,course 
where student.sno=sc.sno 
and course.cno=sc.cno
group by student.sno
order by sno asc;

3.2.9 多表查詢（連接）

32. 查詢每個學(xué)生的基本信息及其選課情況

select student.sno,sname,ssex,sage,sdept,cno,grade
from student left join sc on student.sno=sc.sno;

33. 查詢計算機(jī)系學(xué)生的選課情況，要求輸出姓名，課程號，成績

select student.sname,sc.cno,sc.grade 
from student,sc
where student.sno=sc.sno and sdept='計算機(jī)系';

34. 查詢計算機(jī)系學(xué)生的選課情況，要求輸出姓名，課程名，成績

select student.sname,course.cname,sc.grade 
from student,course,sc
where student.sno=sc.sno 
and course.cno=sc.cno 
and sdept='計算機(jī)系';

35. 查詢計算機(jī)系學(xué)生的選課情況，要求輸出姓名，課程名

select student.sname,course.cname 
from student,course,sc
where student.sno=sc.sno 
and course.cno=sc.cno 
and sdept='計算機(jī)系';

36. 查詢學(xué)生的選課情況，要求輸出學(xué)號，姓名，課程代號，成績

select student.sno,student.sname,sc.cno,sc.grade from student,sc
where student.sno=sc.sno;

37. 查詢學(xué)生的選課情況，要求輸出學(xué)號，姓名，課程代號，成績（包括未選課學(xué)生的信息）

select student.sno,student.sname,sc.cno,sc.grade
from student 
left join sc 
on student.sno=sc.sno;

38. 查詢各門課程的先修課名稱情況

select a.cno,a.cname,b.cname 
from course a,course b
where a.cpno=b.cno;

3.2.10 子查詢（嵌套）

39. 查詢與劉晨在同一個系的學(xué)生

select * 
from student 
where sdept=
(select sdept from student where sname='劉晨')
and sname!='劉晨';

40. 查詢年齡最小的同學(xué)信息

select * 
from student 
where sage=
(select min(sage) from student);

select * 
from student 
order by sage asc 
limit 1;

41. 查詢有成績大于90分的學(xué)生的學(xué)號和姓名

select student.sno,student.sname 
from student,sc
where student.sno=sc.sno and grade>90;

select sno,sname 
from student 
where sno in
(select sno from sc where grade>90);

42. 查詢有成績大于90分的學(xué)生的學(xué)號和姓名和成績

select student.sno,student.sname,sc.grade 
from student,sc
where student.sno=sc.sno and grade>90;

43. 查詢選修了c02課程且成績高于此課程的平均成績的學(xué)生的學(xué)號和成績

select sno,grade 
from sc 
where grade>
(select avg(grade) from sc where cno='c02' group by cno);

44. 查詢每門課課程且成績高于此課程的平均成績的學(xué)生的學(xué)號和成績

select a.sno,a.grade 
from sc a 
where grade>
(select avg(grade) from sc b 
where a.cno=b.cno 
group by b.cno);

45. 查詢每個學(xué)生成績高于他選修課的平均成績的學(xué)生的學(xué)號和課程號及成績

select sno,cno,grade 
from sc a 
where grade>
(select avg(grade) 
from sc b 
where b.sno=a.sno);

46. 查詢其他系中比信息系某一學(xué)生年齡小的學(xué)生姓名和年齡

select sname,sage 
from student 
where sdept!='信息系'and 
sage<
(select max(sage) 
from student 
where sdept='信息系');

select sname,sage 
from student 
where sdept!='信息系' and 
sage<
any(select age 
from student 
where sdept='信息系');

47. 查詢所有選修了c02的學(xué)生的姓名

select sname 
from student 
where exists
(select * from sc where cno='c02' and sno=student.sno);

select sname from 
student where sno in 
(select sno from sc where cno='c02');

select sname from sc,student 
where sc.sno=student.sno and sno='c02';

48. 查詢所有未選修c02課程的學(xué)生的姓名

select sname from student 
where not exists
(select * from sc where cno='c02' and sno=student.sno);

select sname 
from student,sc
where student.sno=sc.sno and cno!='c02';

49. 查詢所有選修了’數(shù)據(jù)庫’課程的學(xué)生的姓名

select sname 
from student,sc,course
where student.sno=sc.sno and course.cno=sc.cno and cname='數(shù)據(jù)庫';

select sname 
from student 
where exists
(select * from sc where cno=(select cno from course where cname='數(shù)據(jù)庫'));

3.2.11 復(fù)雜查詢（exists）

50. 查詢同時選修了c01和c02課程的學(xué)生的學(xué)號

select sno from sc where cno='c01' and cno='c02';

select distinct sno 
from sc where 
sno in (select sno from sc where sno='c01') and 
sno in (select sno from sc where sno='c02');

select distinct sno 
from sc a 
where exists
(select * from sc b where b.sno=a.sno 
and cno='c01') and exists
(select * from sc c where c.sno=a.sno 
and cno='c02');

51. 查詢同時選修了’數(shù)據(jù)庫’和’數(shù)據(jù)結(jié)構(gòu)’課程的學(xué)生的學(xué)號

select distinct sno from sc a where
exists(select * from sc b where b.sno=a.sno and 
cno=(select cno from course where cname='數(shù)據(jù)庫'))
and exists(select * from sc c where c.sno=a.sno and cno=(select cno from course where cname='數(shù)據(jù)結(jié)構(gòu)'));

52. 查詢選修了全部課程的學(xué)生的姓名（即查詢這樣的學(xué)生，沒有一門課是他不選修的）

select sname from student 
where not exists(select * from course
where not exists(select * from sc 
where sno=student.sno and cno=course.cno);

53. 查詢選修了學(xué)號為’200215122’學(xué)生選修的全部課程的學(xué)號

select distinct sno from sc a
where not exists(select * from sc b 
where sno='200215122' and 
not exists(select * from sc c where
c.sno=a.sno and c.cno=b.cno);

3.2.12 組合查詢（union）

54. 查詢所有課程名與學(xué)生名并將查詢列命名為name

(select sname as name from student) 
union
(select cname from course);

3.3 數(shù)據(jù)更新

3.3.1 插入（insert）

定義

insert into 表名 (列名) values (值列表);

1. 將新生記錄為(200821105,陳冬,男,18,信息系)插入到student表中

insert into student (sno,sname,ssex,sage,sdept)
values('200821105','陳冬','男',18,'信息系');

insert student
values('200821105','陳冬','男',18,'信息系');

2. 將與劉晨同一個系的新生記錄(200821105,陳冬,男,18)插入到student表中

insert into student set sno='200821105',sname='陳冬',ssex='男',sage=18,
sdept=(select sdept from student where sname='劉晨');

3. 將新生記錄(200821107,陳冬,男,18,信息系),(200821118,劉晨,男,18,信息系)一起插入到student表中

insert into student
values('200821107','陳冬','男',18,'信息系'),('200821108','劉晨','男',18,'信息系');

4. 在sc表中插入一新記錄學(xué)號為200821105,課程為c01

insert into sc (sno,cno) values('200821105','c01',null);

insert into sc values('200821105','c01',null);

5. 備份c01的成績到c01_cj新表中

create table c01_cj like sc;
insert into c01_cj
select * from sc where cno='c01';

3.3.2 修改（update）

定義

update 表名 set 列名=表達(dá)式 where 條件;

1. 將計算機(jī)系全體學(xué)生的成績加5分

update sc set grade=grade+5
where sno in (select sno from student where sdept='計算機(jī)系');

2. 將平均成績80分以上的學(xué)生成績加3分

update sc set grade=grade+3 
where sno in
(select sno from sc group by sno having avg(grade)>80);

3. 將數(shù)據(jù)結(jié)構(gòu)3-5名學(xué)生的成績加5分

update sc set grade=grade+5 where sno=
(select sno from course where course.cno=sc.cno and cname='數(shù)據(jù)結(jié)構(gòu)'
order by grade desc limit 2,4);

3.3.3 刪除（delete）

定義

delete from 表名 where 條件;

1. 刪除學(xué)號為200215121的學(xué)生的記錄

delete from sc where sno='200215121';
delete from student where sno='200215121';

2. 刪除所有學(xué)生的記錄

delete from student;

3. 刪除計算機(jī)系所有學(xué)生的選課記錄

delete from sc where sno in(select sno from student where sdept='計算機(jī)系');

四、視圖

4.1 創(chuàng)建視圖

定義

create view 視圖名 as 子查詢;

1. 建立系名為計算機(jī)系的學(xué)生的視圖

create view student_view as
select sno,sname,ssex,sdept from student 
where sdept='計算機(jī)系';

2. 建立系名為計算機(jī)系的學(xué)生的視圖，要求對視圖的更新進(jìn)行檢查

create view student_view as
select sno,sname,ssex,sdept 
from student 
where sdept='計算機(jī)系'
with check option;

3. 建立信息系選修了c01課程的學(xué)生的視圖

create view student_view as
select student.sno,student.sname,sc.grade
from student,sc 
where student.sno=sc.sno 
and sdept='信息系' 
and cno='c01';

4.2 其他

同基本表操作一樣

總結(jié)

數(shù)據(jù)庫→基本表（創(chuàng)建-增刪改查）→視圖（創(chuàng)建-增刪改查）文章來源地址http://www.zghlxwxcb.cn/news/detail-406524.html

附：更多內(nèi)容

到了這里，關(guān)于零基礎(chǔ)入門MySQL數(shù)據(jù)庫（全）的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！