Mysql基礎(chǔ)練習(xí)

1、MySQL根底練習(xí)Sutdent表的定義字段名字段描述數(shù)據(jù)類型主鍵外鍵非空自增id學(xué)號(hào)INT(10)是否是是是name姓名VARCHAR(20)否否是否否sex性別VARCHAR(4)否否否否否birth出生年份YEAR否否否否否department院系VARCHAR(20)否否是否否address家庭住址VARCHAR(50)否否否否否score表的定義字段名字段描述數(shù)據(jù)類型主鍵外鍵非空自增id編號(hào)INT(10)是否是是是stu_id學(xué)號(hào)INT(10)否否是否否c_name課程名VARCHAR(20)否否否否否grade分?jǐn)?shù)INT(10)否否否否否需要插入的數(shù)據(jù)：student表需要插入的數(shù)

2、據(jù)：901,張老大,男,1985,計(jì)算機(jī)系,北京市海淀區(qū)902,張老二,男,1986,中文系,北京市昌平區(qū)903,張三,女,1990,中文系,湖南省永州市904,李四,男,1990,英語系,遼寧省阜新市 905,王五,女,1991,英語系,福建省廈門市906,王六,男,1988,計(jì)算機(jī)系,湖南省衡陽市score表需要插入的數(shù)據(jù)：901,計(jì)算機(jī),98901,英語,80902,計(jì)算機(jī),65902,中文,88903,中文,95904,計(jì)算機(jī),70904,英語,92905,英語,94906,計(jì)算機(jī),90906,英語,851根據(jù)表格所提供的需求創(chuàng)立兩張表執(zhí)行語句create table mxx_stud

3、ent(id int(10) primary key not null unique auto_increment,name varchar(20) not null,sex varchar(4), birth year,department varchar(20) not null,address varchar(50)；結(jié)果圖：iysql> create table mxx_£tud&nt (- > id jnt(JO) primary key not null uniqup auto incremeritt- > name varchar(20) no

4、x null,- > sex varchar(4),- > birth year,- > dejjartment varchar (20) not null,一> address varchar(50)I3)：uerv OK, 0 rows affected tO. 04 sec)ysql dose mxx_student;Field+-Type+ Default+Extra+NDNOPF?TYESYES NO YESvarcharOO varchar14) year (4)varchar(20) vaKdiai'loO)L L- L L L L L L L L

5、- L L xuXLxlNxl-NLauto incrementidname HP工 birth department addressrows in set. (Or 01 sec)執(zhí)行語句2create table mxx_score(id int(10) primary key not null unique auto_increment,stu_id int(10) not null,c_name varchar(20),);結(jié)果圖:niysql) creaTe table mxx_£core(- > id int(L0) primary key not null uni

6、que autc_increineQtT一stu_id int (10) not nulL- 'L, c_ ianie varchr ,- > grade int(10)!-> )；Query OK. 0 rows affected (0.04 sec)hiysql? desc mxx_student;1Fild|11Type 11Null1-lKeyDefaultirExtraiiidname SUK birth department addies s11int (10)varchar (20) varchar (4) year (4) varchar(20) varch

7、ar(50)1NONOYESYES NOYES1PRINULL NULL XLLL XL1L MIL NULLiiauto_increment6 rows in set (0. 00 sec)2.將相應(yīng)的數(shù)據(jù)插入到對(duì)應(yīng)的表中執(zhí)行語句1insert into mxx_student(id,name,sex,birth,department,address) values (901,'張老大','男',1985,'計(jì)算機(jī)系','北京市海淀區(qū),);insert into mxx_student(id,name,sex,birth,departm

8、ent,address) values(null,'張老二','男',1986,'中文系','北京市昌平區(qū)');insert into mxx_student(id,name,sex,birth,department,address) values(null,張三','女',1990,'中文系','湖南省永州市');insert into mxx_student(id,name,sex,birth,department,address) values (null,'李

9、四','男',1990,英語系','遼寧省阜新市');insert into mxx_student(id,name,sex,birth,department,address) values null,'王六','男',1988,'英語系','湖南省衡陽市,;結(jié)果圖：iysql ? select+from inAX_student;id namesex birth department address12 3 4 5 6 o o o o o O 9 9 9 9 9 9大二名名三四五六張張張李

10、工E男出女尋女珥5 6 0 0 188 8 9 9 9 89 q 9 9 9 9 lx lx T-1一 T . 11 11系 系機(jī)一系一系系系機(jī)克文文語厝克計(jì)中中英英計(jì)京京南宇建南北北湖遼福湖區(qū)區(qū)IIJ市市市 淀平州新門陽 海昌永阜M衡 -I6 rows in set (0.Q0 sec)執(zhí)行語句2insert into mxx_score(id,stu_id,c_name,grade) values (null,901,計(jì)算機(jī)',98);insert into mxx_score(id,stu_id,c_name,grade) values (null,901 英語',80)

11、;insert into mxx_score(id,stu_id,c_name,grade) values (null,902,計(jì)算機(jī)',65);insert into mxx_score(id,stu_id,c_name,grade) values (null,902,中文',88);insert into mxx_score(id,stu_id,c_name,grade) values (null,903,中文',95);insert into mxx_score(id,stu_id,c_name,grade) values (null,904,計(jì)算機(jī)',7

12、0);insert into mxx_score(id,stu_id,c_name,grade) values (null,904英語',92);insert into mxx_score(id,stu_id,c_name,grade) values (null,905英語',94);insert into mxx_score(id,stu_id,c_name,grade) values (null,906,計(jì)算機(jī)',90);insert into mxx_score(id,stu_id,c_name,grade) values (null,906英語',85)

13、;結(jié)果圖：一一一ji.ysql> select * from niKX_score;qstupid+c_nampgrad?1234367890IX112 2 3 4-4566 OOOOOOOOOO 9gg 99 9 9999W®算文文算語語算語計(jì)英計(jì)中中計(jì)英英計(jì)英機(jī)機(jī)0 rows in set (0.00 sec)3,查詢所有學(xué)生的信息和測試信息select * from mxx_student st,mxx_score sc where sc.stu_id = st.id ;iysQl> select * fron tiKX_5tudent striKJC_score

14、sc where sc. 5tu_id - st. idid 口跑脾depart Reni蔡蔡 系系_ 一 系系系系系系機(jī)機(jī) 一 算掉充立克語暗語» 一計(jì)CI-中中中婁蛉英“汁一大大二二 謖gg抬二四四五六六一 張張張強(qiáng)強(qiáng)李李±±E .H區(qū)區(qū)區(qū)市市rtiItf市市一 一 淀施平平的新孺門閨陽 一 3海樨昌昌水晝艇衙所一 出一,京京京南寧寧善南一 曲一北北北北湖江江招湖湖一id I stu_id c_naiwgrade1234567890機(jī)機(jī) 機(jī) 機(jī) 克語MLT三n倡語n嗚 計(jì)英H中中計(jì)旅英證9S8063戰(zhàn)70929490E510 in set (0.02 mec)

15、4.從student表中查詢每個(gè)院系有多少人 select department,count(department) from mxx_student group by department;nysql? select department, count'cepartnent) from mAX_studen.t group by depaitment;卜T+department countdepartraent)卜T+中文系2貞諾索2計(jì)算機(jī)系2II-1 hi tows in set (0h 00 see)5,計(jì)算每個(gè)學(xué)生的總成績select ,sum(mc.grade)

16、 from mxx_student mt ,mxx_score mc where mt.id=mc.stu_id group by mc.stu_id;name sumtmc- grade)ecS00O大二老老三四五六 張張張李王王16 .查詢李四的測試科目和測試成績select ,mc.c_name,mc.grade from mxx_student mt ,mxx_scoremc where mt.id=mc.stu_id and ="李四"；一i I1"-十name c_nanie grade1_i+李四 計(jì)算機(jī) |70李四 英語

17、92 1j2 rows in set (0. 00 sac)7 .查詢計(jì)算機(jī)成績低于95的學(xué)生信息selectmt.id,,mt.sex,mt.birth,mt.address,mt.department,mc.c_name, mc.grade from mxx_student mt ,mxx_score mc where mt.id=mc.stu_id and mc.c_name='計(jì)算機(jī)"and mc.grade<95;id name I sex birth addressdepartment c_jame I grade902 張者二904 手同M6

18、 壬六男易更一6 0 8 _8 9 S - 9 9 9 _-11 IX- -北京市昌平區(qū)遼寧省阜新市湖南省衡陽擊系系機(jī) 文譜霓 +英計(jì)機(jī)機(jī)機(jī)一算算算一H ros in set (0,W sec'8 .查詢同時(shí)參加計(jì)算機(jī)和英語測試的學(xué)生的信息select *from mxx_student where id in (select stu_id from mxx_score where stu_id in (select stu_id from mxx_score where c_name=#MM ")and c_name='英語");+-id namesex birlh department address北京市海淀區(qū)遼寧省中新市湖南省衡陽市系系機(jī)系機(jī)算語n計(jì)英計(jì)5 o B8 9 89 9 9VI -1L 1-大老IN六 張李王14 6 o o O9 9 9row3 in set (0.00 sec)9 .查詢姓張或者姓王的同學(xué)的姓名、院系和測試科目及成績select ,mt.depart