1.使用SQL語言創建滿足以下要求的數據庫。

（1）創建數據庫名稱為jwgl，字符集選擇utf8，排序規則選擇utf8_general_ci。

create database jwgl character set utf8 collate utf8_general_ci;

（2）查看數據庫。

show databases;

（3）將數據庫jwgl的指定字符集修改為gb2312。

mysql> alter database jwgl

-> default character set gb2312;

（4）刪除數據庫jwgl。

drop database jwgl;

3.使用SQL語言查看數據庫存儲引擎。

mysql> show engines;

(6).創建kc表

mysql> CREATE TABLE kc

-> (

-> kch char(4) NOT NULL PRIMARY KEY,

-> kcm char(20),

-> xss int,

-> xf int);

(7).①增加“成績”一列cj，類型是int，允許空值。默認為0。

mysql> alter table kc add column cj int default 0;

②修改cj列的類型為char。

mysql> alter table kc change column cj cj char(4);

③修改cj列名為mark。

mysql> alter table kc change column cj mark char(4) default 0;

④刪除mark列。

mysql> alter table kc drop column mark;

(8).刪除表kc。

mysql> drop table kc;

(9).將表xs重命名為Student。

mysql> rename table xsgl.xs to xsgl.Student;

1.根據學生基本信息表（xsjbxxb）和學生選課表（xsxkb）創建學生表（xsb）和課表（xkb）。

use jwgl;

create table xsb as select * from xsjbxxb;

create table xkb as select * from xsxkb;

2. 根據xsb表和xbk表，完成以下各題。

（1）對xsb表的xh字段添加單一主鍵，對xkb表的xh和kcdm字段創建復合主鍵。

alter table xsb modify xh varchar(20) primary key;

alter table xkb add primary key(xh,kcdm);

（2）對xkb表的xh字段創建外鍵約束，建立xsb表和xkb表的關聯關系。

alter table xkb add foreign key(xh) references xsb(xh);

（3）針對xsb表的xm，bjbh，xy字段添加非空約束。

alter table xsb modify xm varchar(50) not null;

alter table xsb modify bjbh varchar(20) not null;

alter table xsb modify xy varchar(10) not null;

（4）針對xsb表的xm字段添加唯一性約束。

alter table xsb modify xm varchar(50) unique;

（5）針對xsb表的xb字段添加檢查約束，要求只能取值男或女。

alter table xsb add check(xb in ("男","女"));

（6）針對xsb表的xb字段添加默認值：男。

alter table xsb modify xb char(2) default "男";

（7）刪除創建的外鍵約束。

show create table xkb;

alter table xkb drop foreign key xkb_ibfk_1;

（8）刪除創建的主鍵約束。

alter table xsb drop primary key;

（9）刪除創建的唯一性約束。

show index from xsb;

alter table xsb drop index xm;

（10）刪除創建的非空約束。

alter table xsb modify xm varchar(50);

alter table xsb modify bjbh varchar(20);

alter table xsb modify xy varchar(10);

（11）刪除創建的默認值約束。

alter table xsb modify xb char(2);

（12）刪除創建的檢查約束。

show create table xsb;

alter table xsb drop check xsb_chk_1;

1.無條件查詢：

（1）查詢課程代碼表的數據。（5分）

mysql>select * from kcdmb;

（2）在學生基本信息表中查詢每個學生的姓名及專業信息。（5分）

mysql> select xm,zymc from xsjbxxb;

2. 條件查詢

（1）查詢學生基本信息表中男生的基本信息。（5分）

mysql> select * from xsjbxxb where xb ='男';

（2）查詢2000年出生的女生的信息。（5分）

mysql> select * from xsjbxxb where year(csrq) =2000 and xb ='女';

（3）查詢考試成績在80~90分之間的學生學號、課程號和成績。（5分）

mysql> select xh,kcdm,cj from xsxkb where cj between 80 and 90;

（4）查詢選修了課程代碼00202117或00202118學生選課信息。（5分）

mysql> select * from xsxkb where kcdm in('00202117','00202118');

（5）查詢選修了課程名中包含了“數據庫”的選課信息。（5分）

mysql> select xh,xk.kcdm,cj

-> from xsxkb as xk join kcdmb

-> on xk.kcdm=kcdmb.kcdm

-> where kcmc like '%數據庫%';

（6）查詢學號為201820107101的學生的課程選課信息，輸出學號和平均成績。（5分）

mysql> select xh ,avg(cj) as 平均成績

-> from xsxkb

-> where xh='201820107101';

（7）查詢考試成績前3名的選課信息。（5分）

mysql> select * from xsxkb order by cj desc limit 3;

（8）查詢教師基本信息表的第3至6條記錄。（5分）

mysql> select * from jsjbxxb limit 2,4;

（9）查詢每門課程的平均成績，并顯示平均成績最高的前3門課程的課程代碼，課程名稱，平均成績。（10分）

mysql> select xsxkb.kcdm,kcdmb.kcmc,avg(xsxkb.cj) as '平均成績'

-> from xsxkb,kcdmb

-> where xsxkb.kcdm=kcdmb.kcdm

-> group by xsxkb.kcdm

-> order by avg(xsxkb.cj) desc

-> limit 3;

或者：

mysql> select xsxkb.kcdm,kcmc, avg(cj) as平均成績

-> from xsxkb join kcdmb

-> on xsxkb.kcdm=kcdmb.kcdm

-> group by xsxkb.kcdm

-> order by 3 desc

-> limit 3;

（10）統計每個學生的成績平均分，并按平均成績降序排序。（10分）

mysql> select xh,avg(cj) as 平均成績

-> from xsxkb

-> group by xh

-> order by 2 desc;

（11）統計學生基本信息表中女生的人數。（10分）

mysql> select xb as 性別,count(*) as 人數

-> from xsjbxxb

-> where xb='女';

（12）按性別分組，求每組學生的平均年齡。（10分）

mysql> select xb as 性別,avg(year(now())-year(csrq)) as 平均年齡

-> from xsjbxxb

-> group by xb;

（13）查找選修課程超過2門且每門成績都在80分以上的學生的學號。（10分）

mysql> select xh as 學號, count(*) as 選課人數

-> from xsxkb

-> group by xh

-> having count(*)>2 and min(cj)>80;

1、按照要求使用連接查詢進行以下操作。

（1）查找學生基本信息表中年齡最大的男生的所有信息。

mysql> select * from xsjbxxb

-> where xb = '男'

-> order by age desc

-> limit 1;

查詢學生的選課信息，輸出學號，姓名，課程名稱，成績。

mysql> select xs.xh,xm,kcmc,cj

-> from xsjbxxb as xs join xsxkb on xs.xh=xsxkb.xh

-> join kcdmb on xsxkb.kcdm=kcdmb.kcdm

-> ;

統計每個學生的成績平均分，輸出學號，姓名，平均成績。

mysql> select xs.xh,xm,avg(cj) as 平均成績

-> from xsjbxxb as xs,xsxkb

-> where xs.xh=xsxkb.xh

-> group by xs.xh;

統計每門課程的平均成績，輸出課程編碼，課程名稱，平均成績。

mysql> select kcdmb.kcdm, kcmc, avg(cj) as 平均成績

-> from kcdmb join xsxkb

-> on kcdmb.kcdm=xsxkb.kcdm

-> group by kcdm;

查詢每個學生選修的課程成績信息，要求顯示學號，姓名，課程代碼，成績。

mysql> select xs.xh,xm,kcdm,cj

-> from xsjbxxb as xs join xsxkb

->on xs.xh=xsxkb.xh;

查詢每個學生選修的課程成績信息，要求只顯示成績在90分（包含90）以上的學生的學號，姓名，課程名稱，成績。

mysql> select xs.xh,xm,kcmc,cj

-> from xsjbxxb as xs join xsxkb on xs.xh=xsxkb.xh

-> join kcdmb on xsxkb.kcdm=kcdmb.kcdm

-> where cj >=90;

查詢每個學生選修的課程成績信息，要求只顯示平均成績在90分（包含90）以上的學生的學號，姓名，課程名稱，成績。

select xs.xh,xm,kcmc,cj

from xsjbxxb as xs join xsxkb on xs.xh=xsxkb.xh

join kcdmb on xsxkb.kcdm=kcdmb.kcdm

group by xsxkb.xh

having avg(cj)>=90;

或者：使用子查詢

select xs.xh,xm,kcmc,cj

from xsjbxxb as xs ,xsxkb,kcdmb

where xs.xh=xsxkb.xh

and xsxkb.kcdm=kcdmb.kcdm

and xs.xh in(select xh from xsxkb group by xh having avg(cj)>=90)

;

查看每個同學的選課信息，包括未選課的學生信息，要求顯示學號，姓名，課程代碼，成績。

mysql> select xs.xh,xm,kcdm,cj

-> from xsjbxxb as xs left join xsxkb

-> on xs.xh = xsxkb.xh； -> ;

查看每門課程的選課信息，包括沒有人選修的課程，要求只顯示課程代碼，課程名，成績。

mysql> select xsxkb.kcdm, kcmc,cj

-> from kcdmb left join xsxkb

-> on kcdmb.kcdm = xsxkb.kcdm；

2、按照要求使用union進行查詢。

（10）查找學號為201820919102和學號為201820511102的兩位同學的信息。

mysql> select *

-> from xsjbxxb

-> where xh='201820919102'

-> union

-> select *

-> from xsjbxxb

-> where xh='201820511102'

-> ;

3、按照要求使用子查詢進行以下操作。

（11）查找電子信息工程平均成績在70上的學生的學號和平均成績。

mysql> select xh,avg(cj) as '平均成績'

-> from xsxkb

-> where xh in

-> (select xh from xsjbxxb where zymc='電子信息工程')

-> group by xh

-> having avg(cj)>=70;

（12）查找與學號為201820917101學生性別相同、專業相同的學生學號和姓名。

select xh,xm

from xsjbxxb as xs1

where exists(select * from xsjbxxb as xs2

where xs2.xb=xs1.xb and xs2.zymc=xs1.zymc

and xs2.xh='201820917101')

and xs1.xh!='201820917101';

（13）從xsjbxxb表中查找所有女學生的姓名、學號，以及與學號為201820917101的學生的年齡差距。

mysql> select xm,xh,

-> year(csrq)-(select year(csrq) from xsjbxxb where xh='201820917101') as 年齡差

-> from xsjbxxb

-> where xb='女';

（14）查找選修了全部課程的同學的姓名。

mysql> select xm

-> from xsjbxxb

-> where not exists

-> (

-> select *

-> from kcdmb

-> where not exists

-> ( select *

-> from xsxkb

-> where xh=xsjbxxb.xh and kcdm=kcdmb.kcdm

-> )

-> );

或者

mysql> select xm from xsjbxxb

-> where xh in

-> (select xh from xsxkb where kcdm=all

-> (select kcdm from kcdmb));

（15）查找選修了課程代碼為00202118的課程的學生姓名。

mysql> select xm

-> from xsjbxxb

-> where xh in(

-> select xh from xsxkb where kcdm='00202118');

（16）查找選修了計算機接口技術的學生學號，姓名。

mysql> select xh,xm

-> from xsjbxxb

-> where xh in(select xh from xsxkb

-> where kcdm in

-> (select kcdm from kcdmb where kcmc='計算機接口技術'));

（17）查找xsjbxxb表中，比所有土木工程專業的學生年齡都大的學生學號、姓名、專業名稱、出生日期字段。

mysql> select xh,xm,zymc,csrq

-> from xsjbxxb

-> where csrq<all(select csrq from xsjbxxb where zymc='土木工程');

4、按照要求使用正則表達式進行以下操作。

（18）查詢姓李的學生的學號、姓名和專業名稱。

mysql> select xh, xm, zymc

-> from xsjbxxb

-> where xm regexp '^李' ;

查詢學號里包含4、5、6的學生學號、姓名和專業名。

mysql> select xh, xm, zymc

-> from xsjbxxb

-> where xh regexp '4|5|6' ;

或者mysql> select xh,xm,zymc

-> from xsjbxxb

-> where xh regexp '[4-6]';

查詢學號以2018開頭，以101結尾的學生學號、姓名和專業名稱。

mysql> select xh, xm, zymc

-> from xsjbxxb

-> where xh regexp '^2018' and xh regexp '101$' ;

或者mysql> select xh,xm,zymc

-> from xsjbxxb

-> where xh regexp '^2018.*101$';

1、索引練習

（1）對學生基本信息表xsjbxxb的籍貫jg字段，建立全文索引index_jg，并驗證索引的建立。

Create fulltext index index_jg on xsjbxxb(jg);

Show index from xsjbxxb;

對學生基本信息表xsjbxxb的xm和bjbh字段，建立復合索引index_xm_bjbh，并驗證索引的建立。

Create index index_xm_bjbh on xsjbxxb(xm,bjbh);

Show index from xsjbxxb;

對部門代碼表bmdmb的部門名稱bmmc字段，建立唯一降序索引index_bmmc，并驗證索引的建立。

Create unique index index_bmmc on bmdmb(bmmc desc);

Show index from bmdmb;

刪除索引index_bmmc。

Drop index index_bmmc on bmdmb;