高端IT就業培訓專家第三十五章數據庫本章目標安裝介紹創建數據庫表、主鍵、處鍵概念范式表與表之間關系字段約束字段類型數據庫與表的創建函數查詢語句select

from

where查詢語句group

by,having,order

by,函數多表查詢子查詢MySql數據庫介紹與安裝數據庫:數據的集合目前流行的數據庫存儲形式為關系型數庫流行數據庫軟件有MySql、SqlServer、Oracle、DB2下載官網址：表名：學生(Stu)學號(StudyNo)、身份證號(IdCardNo)、性名(name)、性別(sex)、選修課(elective)StudyNoIdCardNoNameSexElective1019800715小明男數學1119800815小華女Android1219800816小強女JSP1319800817張三男Android1419800818張三女Android1519800819小王女Spring表字段記錄主鍵關鍵字如果某個字段或多個字段的值可以唯一地標識一條記錄，則該字段或字段組就稱為關鍵字。主鍵如果一個關鍵字是用以標識每條記錄的唯一性，并作為該表與其他表實現關聯之用候選關鍵字除主關鍵字以外的其他關鍵字表的范式三大范式–第一范式：字段不能再分；

(必須符合)第二范式：必須有主鍵；第三范式：除主鍵之外不能再有候選關鍵字。表與表的關系表與表的關聯關系有三種一對一一對多多對一一對一國家:country總統：President主鍵外鍵,必須約束值唯一。是否為空可以根據業務任意指定id

主鍵Namesexf_country_id

外鍵1奧吧馬男12習近平男23安倍晉三男3id

主鍵NameLanguage1美國英語2中國漢語3日本日語一對多(多對一)一個班有多個學生，每個學生只有一個班主鍵idclassNameHeadTeacher1外鍵火箭班張文2沖刺班小王外鍵id

主鍵nameagef_class_id1張三3012李四3213王五2924小四3415小五3326小六282多對多id

主鍵name1張三2李四3王五4小四5小五6小六表：Teacherid

主鍵name1張小華2陳建明id主鍵f_Teacher_id外f_Stu_id

外鍵11鍵1212313414521622723表：Stu表：teacher_stu_middle字段約束主鍵字約束（primary

key）值不能為空，并且值唯一外鍵字約束（foreign

key）值必須為指向的表的主鍵值惟一性約束（unique）值唯一非空約束

(not

null)

值不能為空檢查約束（check）值為符合指定條件的值缺省約束（default）相當于值沒有約束最常用的字段類型數值類型:int、float、double字符串(字符)類型:char:

只能存儲一個字符(一個漢字也行）char(M):能存儲M個字符varchar(M):能存儲M個字符，但是實計存儲的字符小于M個時，存儲空間會被自動縮小SQL——創建、刪除數據庫create

database

數據庫名;drop

database

數據庫名;SQL——創建表刪除表創建表：?create

table

表名?（字段名類型<約束>,字段名類型<約束>,......字段名類型<約束>）；刪除表：?drop

table

表名;SQL——創建表刪除表示例創建表：create

table

stu(id

int

primary

key

auto_increment,idcardno

char(20)

unique

not

null,name

char(16)

not

null,sexchar(2)notnullcheck(sex='男人'orsex='女生'),elective

char(20));id:

主鍵，從1自增長；idcardno:非空,值唯一

name:非空；sex:非空；elective:默認約束

刪除表：drop

table

stu;idIdCardNoNameSexElective1019800715小明男數學1119800815小華女Android1219800816小強女JSP1319800817張三男Android1419800818張三女Android1519800819小王女SpringSQL——一對一外鍵示例創建country表create

table

country(id

int

primary

key

auto_increment,name

char(10)

,

language

char(10));創建president表create

table

president(id

int

primary

key

auto_increment,name

char(10)

not

null,sex

char,

f_country_id

int

unique);指定president表中的字段f_country_id為外鍵，指向country表的主鍵country(id)；alter

table

president

add

constraint

foreign

key(f_country_id)references

country(id) on

delete

cascade;on

delete

cascade:刪除country時刪除相應的presidenton

delete

set

null:刪除country時設置相應的president為nullon

delete

no

action:如果country中某條記錄被president指向，那么刪除country報錯，即不能刪除。主鍵外鍵,必須約束值唯一id

主鍵NameLanguage1美國英語2中國漢語3日本日語id

主鍵Namesexf_country_id1奧吧馬男12習近平男23安倍三男3外鍵SQL——一對多示例創建class表:create

table

class(classname

char(10)

primary

key,headteacher

char(10));創建stu表:create

table

stu(no

int

primary

key

auto_increment,name

char(10),age

int,

f_classname

char(10));增加外鍵約束：alter

table

stu

add

constraint

foreign

key(f_classname)references

class(classname)on

delete

cascade;classNameHeadTeacher火箭班張文沖刺班小王nonameageF_className1張三30火箭班2李四32火箭班3王五29沖刺班4小四34火箭班5小五33沖刺班6小六28沖刺班主鍵外鍵主鍵班級:class學生:stuSQL——多對多示例創建Teacher表create

table

Teacher(id

int

primary

key

auto_increment,name

char(10));創建Stu表create

table

stu(id

int

primary

key

auto_increment,name

char(10));創建中間表middle

create

table

middle(id

int

primary

key

auto_increment,f_teacher_id

int,

f_stu_id

int);增加外鍵約束alter

table

middle

add

constraint

foreignkey(f_teacher_id)

referencesteacher(id);增加外鍵約束alter

table

middle

add

constraint

foreignkey(f_stu_id)

references

stu(id);id

主鍵name1張小華2陳建明id

主鍵f_Teacher_id

外鍵f_Stu_id

外鍵111212313414521622723id

主鍵name1張三2李四3王五4小四5小五6小六TeacherstuSQL——檢查約束示例create

table

mytest(id

int

primary

key,no1

int

check(no1<20),no2

int

check(no2>20

and

no2

<30),no3

int

check(no3>20

or

no3<0),sex

char(2)

check(sex='男',sex='女'));上面寫了check檢查約束，但是實際插入值時會發現check沒有生效，原因是mysql數據庫對check檢查約束不支持，保留可以這樣寫是為了以后板本等級有可能支持。SQL——增、刪、改idNameSexage1小一女202小二男213小三男214小四女235小一女286小六女237小七男208小八男239小九女2110小十女2111小十一女2312小十二男2013小十三男20stu增加：insert

into

表(字段)values(值);insert

into

stu(id,

name,

sex,

age)

values

(13,'小十三','男',

20);insert

into

stu

values

(14,

'小十四', '男',

20);insert

into

stu(age)values

(20);修改：update

表set

屬性1=值1,......,屬性n=值n

<where

條件>update

stu

set

sex='G'where

sex='女';update

stu

set

sex='男'where

id>5

and

id<10;update

stu

set

name='小花',sex='G'where

sex='女';刪除：delete

from

表<where

條件>delete

from

stu

where

id=2;delete

from

stu

where

name='小一'and

sex='女'and

age=28;SQL——單表查詢介紹查詢語句要使用到的關鍵字有select

字段from

表where

條件過濾表中的行group

by

分組having

條件過濾組order

by

排序<desc>SQL——單表查詢select

*

from

stu;|

29

|

小花|

男|

20||

30

|

小花|

男|

23||

31

|

小花|

男|

23||

32

|

小花|

男|

32||

33

|

小花|

女|

23||

34

|

小花|

女|

23||

38

|

小花|

女|

21||

39

|

小花|

女|

21|idNameSexage29小花男2030小花男2331小花男2332小花男3233小花女2334小花女2338小花女2139小花女21stuSQL——單表查詢select

id,

name,

sex,

age

from

stu;+----+------+-----+-----+|

id

|

name

|

sex

|

age

|+----+------+-----+-----+|

29

|

小花|

男|

20||

30

|

小花|

男|

23||

31

|

小花|

男|

23||

32

|

小花|

男|

32||

33

|

小花|

女|

23||

34

|

小花|

女|

23||

38

|

小花|

女|

21||

39

|

小花|

女|

21|+----+------+-----+-----+idNameSexage29小花男2030小花男2331小花男2332小花男3233小花女2334小花女2338小花女2139小花女21stuSQL——單表查詢select

name,

id,

age

from

stu;+------+----+-----+|

name

|

id

|

age

|+------+----+-----+|

小花|

29

|

20||

小花|

30

|

23||

小花|

31

|

23||

小花|

32

|

32||

小花|

33

|

23||

小花|

34

|

23||

小花|

38

|

21||

小花|

39

|

21|+------+----+-----+stuidNameSexage29小花男2030小花男2331小花男2332小花男3233小花女2334小花女2338小花女2139小花女21SQL——單表查詢select

*

from

stu

where

id=29;+----+------+-----+-----+|

id

|

name

|

sex

|

age

|+----+------+-----+-----+|

29

|

小花

|

男

|

20

|+----+------+-----+-----+stuidNameSexage29小花男2030小花男2331小花男2332小花男3233小花女2334小花女2338小花女2139小花女21SQL——單表查詢select

*

from

stuwhere

idin(29,30,31,32);+----+------+-----+-----+|

id

|

name

|

sex

|

age

|+----+------+-----+-----+|

29

|

小花|

男|

20||

30

|

小花|

男|

23||

31

|

小花|

男|

23||

32

|

小花|

男|

32|+----+------+-----+-----+stuidNameSexage29小花男2030小花男2331小花男2332小花男3233小花女2334小花女2338小花女2139小花女21SQL——單表查詢select

*

fromstuwhere

id

not

in(29,30,31,32);+----+------+-----+-----+|

id

|

name

|

sex

|

age

|+----+------+-----+-----+|

33

|

小花|

女|

23||

34

|

小花|

女|

23||

38

|

小花|

女|

21||

39

|

小花|

女|

21|+----+------+-----+-----+stuidNameSexage29小花男2030小花男2331小花男2332小花男3233小花女2334小花女2338小花女2139小花女21SQL——單表查詢select

*

fromstuwhere

id>29

and

id<34;+----+------+-----+-----+|

id

|

name

|

sex

|

age

|+----+------+-----+-----+|

30

|

小花|

男|

23||

31

|

小花|

男|

23||

32

|

小花|

男|

32||

33

|

小花|

女|

23|+----+------+-----+-----+stuidNameSexage29小花男2030小花男2331小花男2332小花男3233小花女2334小花女2338小花女2139小花女21SQL——單表查詢stuidNameSexage29小花男2030小花男2331小花男2332小花男3233小花女2334小花女2338小花女2139小花女21select

*

from

stu

where

id>31

and

age=23;+----+------+-----+-----+|

id

|

name

|

sex

|

age

|+----+------+-----+-----+|

33

|

小花

|

女

|

23

||

34

|

小花

|

女

|

23

|+----+------+-----+-----+SQL——單表查詢stuidNameSexage29小花男2030小花男2331小花男2332小花男3233小花女2334小花女2338小花女2139小花女21select

*

from

stuwhere d=29

or

age=23

or

age=31;+----+------+-----+-----+|

id

|

name

|

sex

|

age

|+----+------+-----+-----+|

29

|

小花|

男|

20||

30

|

小花|

男|

23||

31

|

小花|

男|

23||

33

|

小花|

女|

23||

34

|

小花|

女|

23|+-

--+------+-----+-----+SQL——單表查詢stu1i;dNameSexage29小花男2030小花男2331小花男2332小花男3233小花女2334小花女2338小花女2139小花女21select

*

from

stu

where

id=29

or

id=23

or

id=3+----+------+-----+-----+|

id

|

name

|

sex

|

age

|+----+------+-----+-----+|

29

|

小花

|

男

|

20

||

31

|

小花

|

男

|

23

|+----+------+-----+-----+SQL——單表查詢stuidsex;NameSexage29小花男2030小花男2331小花男2332小花男3233小花女2334小花女2338小花女2139小花女21select

count(*)as

性別人數from

stu

group

by+----------+|

性別人數|+----------+||4|4|+----------+SQL——單表查詢stuidNameSexage29小花男2030小花男2331小花男2332小花男3233小花女2334小花女2338小花女2139小花女21select

sex,count(*)as

性別人數from

stu

group

by

sex;+-----+----------+|

sex

|

性別人數|+-----+----------+|

女||

男|4

|4

|+-----+----------+SQL——單表查詢stuidNameSexage29小花男2030小花男2331小花男2332小花男3233小花女2334小花女2338小花女2139小花女21select

sex,count(*)as

性別人數from

stuwhere

id

not

in(29,30)group

by

sex;+-----+----------+|

sex

|

性別人數|+-----+----------+|

女||

男|4

|2

|+-----+----------+SQL——單表查詢stuidNameSexage29小花男2030小花男2331小花男2332小花男3233小花女2334小花女2338小花女2139小花女21select

sex,count(*)as性別人數from

stuwhere

id

not

in(29,30)group

by

sex

desc;+-----+------------+|

sex

|

as性別人數|+-----+------------+|

男|2

||

女|4

|SQL——單表查詢stuidNameSexage29小花男2030小花男2331小花男2332小花男3233小花女2334小花女2338小花女2139小花女21select

sex,count(*)as性別人數

from

stuwhere

id

not

in(29,30)group

by

sexhaving

sex='女'desc;SQL——多表查詢21

二班S_IDS_NAMES_SEXS_AGES_MOENYC_IDt_stu表:s_id、s_name、s_sex、s_age、s_money、c_idt_class表：c_id、c_name查詢班級名稱、學生姓名、性別、繳費、相同班級的要放在一起,姓名根據字典順序排列。select

c_name,

s_name,

s_sex,s_moneyfrom

t_stu,

t_classwhere

t_stu.c_id=t_class.c_idorder

by

S_NAME1張三男204000202李四男215000203王五女226000214小六女23700021C_ID5C_name小明女2480002120一班S_IDS_NAMES_SEXS_AGES_MOENYC_IDC_IDC_NAME1張三男2040002020一班2李四男2150002020一班3王五女2260002120一班4小六女2370002120一班5小明女2480002120一班1張三男2040002021二班2李四男2150002021二班3王五女2260002121二班4小六女2370002121二班5小明女2480002121二班SQL——多表查詢S_IDS_NAMES_SEXS_AGES_MOENYC_ID1張三男204000202李四男215000203王五女226000214小六女237000215小明女24800021T_STU表:S_ID、S_NAME、S_SEX、S_AGE、S_MOENY、C_IDT_CLASS表：C_ID、C_NAME★、學生表：學號、姓名、性別、入學年齡、繳費★、班級表：班級編號、班級名稱查詢各班名稱和人數select

c.C_NAME,count(*)

as

rsfrom

T_STU

s,T_CLASS

Cwhere

s.C_ID=c.c_id

group

by

c.C_NAMEC_IDC_name20一班21二班S_IDS_NAMES_SEXS_AGES_MOENYC_IDC_IDC_NAME1張三男2040002020一班2李四男2150002020一班3王五女2260002120一班4小六女2370002120一班5小明女2480002120一班1張三男2040002021二班2李四男2150002021二班3王五女2260002121二班4小六女2370002121二班5小明女2480002121二班SQL——多表查詢T_STU表:S_ID、S_NAME、S_SEX、S_AGE、S_MOENY、C_IDT_CLASS表：C_ID、C_NAME★、學生表：學號、姓名、性別、入學年齡、繳費★、班級表：班級編號、班級名稱★★查詢各班名稱和人數,但人數必須不少于2,人數多的放在前面select

c.C_NAME,count(*)

as

rsfrom

T_STU

s,T_CLASS

Cwhere

s.C_ID=c.c_idgroup

by

c.C_NAMEhaving

count(*)>=2order

by

count(*)

descC_IDC_name20一班S_IDS_NAMES_SEXS_AGES_MOENYC_ID1張三男204000202李四男215000203王五女226000214小六女237000215小明女2480002121S_ID二班S_NAMES_SEXS_AGES_MOENYC_IDC_IDC_NAME1張三男2040002020一班2李四男2150002020一班3王五女2260002120一班4小六女2370002120一班5小明女2480002120一班1張三男2040002021二班2李四男2150002021二班3王五女2260002121二班4小六女2370002121二班5小明女2480002121二班SQL——多表查詢T_STU表:S_ID、S_NAME、S_SEX、S_AGE、S_MOENY、C_IDT_CLASS表：C_ID、C_NAME★、學生表：學號、姓名、性別、入學年齡、繳費★、班級表：班級編號、班級名稱查詢沒有人員的班級效率低：s