MySQL数据库（基础)——期末复习总结

一、 数据定义

1、库的管理

1.1、库的创建

语法：createdatabase 库名；
     或create databdse ifnotexists 库名；

1.2、库的修改

语法：alterdatabase 库名

例：修改数据库pet的默认字符集为latin1,效对规则为latin1_swedish_ci。

Createdatabase pet
           default(默认)character(字符)set(集) latin1
           default(默认)collate(校对) latin1_swedish_ci;

1.3、库的删除

语法：dropdatabase(if esists) 库名;

1.4、库的显示

语法：showdatabases;

2、表的管理：

2.1、表的创建

语法：createtable 表名（
        列名 数据类型(char,varchar,date,float,int,tinyint) 约束(notnull,null), 
        列名 数据类型 约束，
        …………
       列名 数据类型 约束(最后一个不需要逗号))engine=innodb;(engine=存储引擎，可以省略)

例：假设已经创建了数据库bookstore,在该数据库中创建图书目录表book。

Use bookstore;createtable book (
      图书编号 char(10)notnullprimarykey,
      图书类别 varchar(20)notnulldefault(列默认值) ‘计算机’,
      书名     varchar(40)notnull,
      作者     char(10)notnull,
      出版社   varchar(20)notnull,
      出版时间 date(时间)notnull,
      单价     float(5)(小数)notnull,
      数量     int(5),(整数)
      折扣     float(3),(小数)
      封面图片 blob(较长的文本))engine=innodb;

注：blob和text列不能被赋予默认值

2.2、表的修改

(1)添加新列

altertable 表名 addcolumn(列) 新列名 数据类型 约束；

例：在表t1中增加新的一列a。
Altertable t1 addcolumn a tinyint(表一个字节)null

(2)删除列

Altertable 表名 dropcolumn 列名 数据类型 约束；

(3)修改列名

Altertable 表名 change 旧列名 新列名 新列名的数据类型；

(4)修改列的类型

Altertable 表名 modifycolumn 列名 列的新类型 约束；

例：在表t1中把a列的数据类型改为bigint。
Altertable t1 modify a bigint(表整型的八个字节)notnull;

(5)修改表名

Altertable 表名 renameto 新表名；

1)添加新列，删除列:

use bookstore;altertable book
    add 浏览次数 tinyintnull,dropcolumn 书名;

2)修改表名:
方法一：

use bookstore;altertable book renameto mybook;

方法二：

use bookstore;renametable mybook to booklist,member to memberlist;

2.3、表的复制

(1)仅仅复制表的结构

语法：createtable(ifnotexists) 新表名 like 参照表名；

例：假设数据库中有一个表book，创建book表的一个名为book_copy1的副本。

createtable book_copy1 like book;

(2)复制表的结构+内容：

语法：createtable(ifexists) 新表名 as(select语句)；

例：创建表book的一个名为book_copy2的副本，并且复制内容。

createtable book_copy2
   as(select*from book);

注：当在select语句指定列的位置上使用*号时，表示选择表的所有列

(3)只复制部分数据

语法：createtable 新表名 as(select*from 旧表名)where nation='中国';(筛选语句)

2.4、表的删除

语法：droptable(ifexists) 要被删除的表名；

例：删除表test。

Droptableifexists test；

2.5、表的显示

(1)显示数据表文件名

语法：Showtables

例：显示bookstore数据库建立的数据表文件。

use bookstore;showtables;

(2)显示数据表结构：

语法：describe/desc 表名 列名；

例：查看book表“图书编号”列的信息。

Desc book 图书编号；

3、 主键约束

3.1、定义主键（primary key）

(1)创建表book_copy,将书名定义为主键。

createtable book_copy
( 
    图书编号 varchar(6)null,
        书名 varchar(20)notnullprimarykey,
        出版日期 date);

(2)创建course表来记录每门课程的学生学号，姓名，课程号，学分和毕业日期。其中学号，课程号和毕业日期构成复合主键。

createtable course
(
    学号       varchar(6)notnull,
        姓名       varchar(8)notnull,
        毕业日期   datenotnull,
        课程号     varchar(3),
        学分       tinyint,primarykey(学号,课程号,毕业日期));

3.2、定义主键的原则

mysql 自动地为主键创建一个索引，通常这个索引(index)名为primary,用户可以重新给这个索引起名

(1)创建course表，把索引命名为index_course。

createtable course
(
    学号       varchar(6)notnull,
        姓名       varchar(8)notnull,
        毕业日期   datenotnull,
        课程号     varchar(3),
        学分       tinyint,primarykey index_course(学号,课程号,毕业日期));

4、替代键约束

定义替代键的关键字为：nuique

例：在表book_copy1中，将图书编号作为主键，将书名列定义为一个替代键：
方法一：

createtable book_copy1
(
   图书编号 varchar(20)notnull,
     书名     varchar(20)notnull nuique,
     出版日期 datenull,primarykey(图书编号));

方法二：

createtable book_copy1
(
   图书编号 varchar(20)notnull,
     书名     varchar(20)notnull,
     出版日期 datenull,primarykey(图书编号)unique(书名));

5、添加约束

语法：altertable 表名
        Addprimarykey 索引方式 列名     添加主键
        /addunique 索引名 列名           添加代替键约束
        /dropprimarykey                  删除主键
        /dropindex(索引) 索引名            删除索引

例：1，假设book表中主键未设定，为book表建立主键约束为图书编号，书名为替代键约束。

altertable book 
addprimarykey(图书编号),addunique u_idx(书名);

2,删除book表上的主键和替代键约束(索引)

altertable book
    dropprimarykey,dropindex u_idx;

6、参照完整性约束

6.1、参照完整性约束语法定义

定义外键的语法格式：

Foreignkey(外键)References(参照) 父表表名（父表列名）
         Ondelete(删除)restrict/cascade/setnull/noactionOnupdate(更新)restrict/cascade/setnull/noaction

语法说明：

• 外键：参照表的列名。 父表表名：外键所参照的表名。这个表叫做被参照表，外键所在的表叫做参照表(子表)。
• 父表列名：被参照的列名。外键可以引用一个或多个列。
• On delete/On update：可以为每个外键定义参照动作。参照动作包含两部分，第一部分指定这个参照动作应用哪一条语句，有update和delete语句。第二部分指定采取哪个动作，可能采取的动作是restrict,cascade,set null,no action和set default。
• Restrict(约束):当要删除或更新父表中被参照列上在外键中出现的值时，拒绝对父表的删除或更新操作。
• Cascade(级联):从父表删除或更新时，自动删除或更新子表中匹配的行。
• Set null(设置为空):当从父表删除或更新时，设置子表中与之对应的外键列为null。 No action(行动):意味着不采取动作。
• Set default(默认):作用和set null一样，只不过set default是指定子表中的外键列为默认值。

6.2、修改表的引擎

语法：altertable 表名 存储引擎；

例：修改book表的存储引擎为innodb.

Altertable book engine=innodb;

6.3、外键的创建

6.3.1、创建表的同时创建外键

语法格式：createtable 表名 列定义 外键定义

例：1)创建book_ref表，所有book_ref表中的图书编号都必须出现在book表中。假设已经使用“图书编号”列作为book表主键。

createtable book_copy1
(
   图书编号 varchar(20)notnull,
     书名     varchar(20)notnull,
     出版日期 datenull,primarykey(书名),foreignkey(图书编号)references book(图书编号)ondeleterestrictonupdaterestrict)engine=innodb;

2)创建带有参照动作cascade的book_ref1表

createtable book_copy1
(
   图书编号 varchar(20)notnull,
     书名     varchar(20)notnull,
     出版日期 datenull,primarykey(书名),foreignkey(图书编号)references book(图书编号)onupdatecascade)engine=innodb;

6.3.2、对已有表创建外键

语法：altertable 表名
           Add 外键定义

例：在网络图书销售系统中，只有会员才能下订单。因此sell表中的所有用户号也必须出现在members表的用户号列中。定义参照完整性约束来实现这种约束。

altertable sell
    addforeignkey(用户号)references members(用户号)ondeletecascadeonupdatecascade;

6.4、外键的删除

使用alter table语句

例：删除表book的主键，删除sell表的外键sell_ibfk_1。

Altertable book dropprimarykey,Altertable sell dropforeignkey sell_ibfk _1;

6.5、查看表的所有信息（包括一些字段类型，字段的约束，外键，主键，索引及字符编码等）

语法：showcreatetable 表名 

7、check完整性约束

语法：check(表达式)

语法说明：

• 表达式：指定需要检查的条件，在更新数据的时候，mysql会检查更新后的数据行是否满足check的条件。

例：1)创建表student,只考虑学号和性别两列，性别只能包含男或女两项。

createtable student 
(
     学号 char(6)notnull,
     性别 char(2)notnullcheck(性别 in('男','女')));

2)创建student1，只考虑“学号“，”出生日期“，”学分“列，出生日期必须大于1980年1月1日

createtable student 
(
     学号 char(6)notnull,
         出生日期 datenotnull,
         学分 intnull,check(出生日期>'1980-01-01'));

3)使用子查询：
创建表student2,只考虑学号和性别两列，并确认学号列中的所有值来源于student表的“性别”列中。

createtable student 
(
     学号 char(6)notnull,
性别 char(2)notnull,check(学号 in(select 学号 from student)));

4)创建表student3，有“学号”，“最好成绩”和“平均成绩”3列，要求最好成绩必须大于平均成绩。

createtable student 
(
        学号 char(6)notnull,
         最好成绩 int(1)notnull,
         平均成绩 int(1)notnull,check(最好成绩>平均成绩));

完整性约束同时定义多个，中间用逗号隔开。

5)删除book表的主键，删除sell表的外键sell_idfk_1。

altertable book dropprimarykey;altertable sell dropforeignkey sell_idfk_1;

二、数据更新

Insert(插入),update(更新),delete(删除)

1，数据插入

语法：（1）insertinto 表名(列名……)Values(值……需要插入的数据);

例：1）向bookstore数据库中的表book(表中列包括图书编号，图书类别，书名，作者，出版社，出版时间，单价，数量，折扣及封面图片)中插入如下一行数据(‘tp9/5006-t’,‘计算机’……0.8,null)

use bookstore;insertinto book
 values('tp9/5006-t','计算机','dreamwearer 8 网站制作','鮑嘉','高等教育出版社','2010-08-16',33.25,50,0.8,null);

2）若表book中图书类别的默认值为“计算机“，封面图片默认值为null，插入1)那行数据。

insertinto book（
         图书编号，书名，作者，出版社，出版时间，单价，数量，折扣）
 values('tp9/5006-t','计算机','dreamwearer 8 网站制作','鮑嘉','高等教育出版社’,’2010-08-16',33.25,50,0.8,null);

语法：（2）insertinto 表名（列名……）
          Set 列名=对应的值……

例：上述命令执行结果与1)效果相同，还可以使用set子句来实现。

Insertinto book
Set 图书编号=' tp9/5006-t',书名='dreamwearer 8 网站制作',图书类别=default,作者='鮑嘉',出版时间=,'2010-08-16',单价=33.25,数量=50,折扣=0.8;

1. 2，使用replace语句，可以用第二条记录替换第一条记录

Replaceinto book
  Values('tp9/5006-t','计算机','php网站制作','林小红','高等教育出版社','2011-10-16',23.5,30,0.8,null);

1.3，插入多行数据

向members表中插入如下两行数据：co138，李华，123456，1382255555，2013-18-13 Co139,李明，123456，13822666666，2012-9-23。

Insertinto members values('co138','李华','123456','1382255555','2013-8-23'),('co139','张明','123456','13922666 66','2012-9-23');

2、数据修改（使用update语句）

2.1、单表数据修改

语法：update(更新) 表名
      Set 列名1=表达式1，列名2=表达式2……
      Where 筛选条件

例：将bookstore数据库中book表的所有书籍数量都增加10。将members表中姓名为“张三”的员工联系电话为“13802551234”，密码改为“111111”。

Update book
  Set 数量=数量+10;Update members
  Set 联系电话='13802551234',密码='111111'Where 姓名='张三';（只更改了员工张三的数据）

2.2、多表数据修改

语法：uodate 表名列表（包含多个表的联合，各表之间用逗号隔开）
      Set 列名1=表达式1，列名2=表达式2Where 条件

例：订单号为16的客户因某种原因退回4本图书，请在sell表中修改订购册数，同时书退回后，book表中该图书的数量增加4。

Update sell,book
   Set sell.订购册数=订购册数-4, book.数量=数量+4Where sell.图书编号=book.图书编号 and sell.订单号='16'

  当用update修改多个表时，要修改的表名之间用逗号分开，字段名因为涉及多个表，用“表名.字段名“表示，如上题中的sell.图书编号和book.图书编号，多表连接条件须在where子句中指定。

3、数据删除

3.1、从单个表中删除行

Deletefrom 表名
Where 条件；

语法说明：

• From子句：用于说明从何处删除，表名为要删除数据的表名。
• Where子句：条件中的内容为指定的删除条件。如果省略where子句则删除数据该表的所有行。

例：1）将bookstore数据库members表中姓名为“张三“的员工的记录删除。

Use bookstore;Deletefrom members
      Where 姓名='张三';

2）将book表中数量小于5的所有行删除。

Use bookstore;Deletefrom book
     Where 数量<5;

3.2、从多个表中删除行

语法：delete 表名1，表名2……
      From 表名列表（包含了多个表的联合，各表之间用逗号隔开）
      Where 条件
或
      DeleteFrom 表名1，表名2……
      Using 表名列表（包含了多个表的联合，各表之间用逗号隔开）
Where 条件

例：用户号为D1963的客户注销了，请在member表中将该客户记录删除，同时将其在sell表中的记录也删除。

Delete sell,members
   From sell,members
      Where sell.用户号=members.用户号
         And members.用户号='D1963';

或

DeleteFrom sell,members
     Using sell,members
        Where sell.用户号=members.用户号
              And members.用户号='D1963';

4、使用truncate(截取) table 语句删除表数据（无法恢复）

语法：truncatetable 表名

• 语法说明：truncate(截取) table 语句后，auto_increment(自动增加)计数器被重新设置为该列的初始值。对于参与了索引和视图的表，不能使用truncate table 删除数据，而应使用delete(删除)语句。

三、数据查询

1、单表查询

1.1、selete 语句定义

语法：
Select 【all/distinct(不同的)】 输出表达式,……
   【From 表名1,表名2……】                       from子句
   【where 条件】                                  where子句
   【groupby(分组依据) {列名/表达式/列编号}
         【asc(自动稳定控制)/desc(降序排列)】,……      groupby子句
   【having(具有) 条件                               having 子句
   【orderby(排序语句) {列名/表达式/列编号}
        【asc(自动稳定控制)/desc(降序排列)】,……】     orderby 子句
   【limit(限制) {【偏移量,】 行数/行数offset(偏移量) 偏移量}】limit子句

1.2、选择列

(1)选择指定的列

语法：select 列名1，列名2，……
         From 表名；

例：1)查询bookstore数据据库的members表中各会员的会员姓名，联系电话和注册时间。

Use bookstore;Select 会员姓名，联系电话，注册时间
   From members;

2)当在select语句指定列的为位置上使用*号时，表示选择表的所有列，如要显示member表中所有列，不必将所有字段名一一列出，可使用以下命令：

Select*from members;

(2)定义列别名
  当希望查询结果中的列使用自己选择的列标题时，可以在列名之后使用as子句来更改查询结果的列名，其格式为：

Select 列名 as 别名
From 表名
  Where 条件；

例：1)查询book表中图书类别为“计算机”的图书书名、作者和出版社，结果中列的标题分别指定为name、auther和publisher。

Select 书名 as name , 作者 as auther , 出版社 as publisher
   From book
     Where 图书类别='计算机';

2)当自定义的列标题中含有空格时，引号将标题括起来。

Select 书名 as'name of book', 作者 as' name of auther',  
出版社 as publisher
   From book   Where 图书类别='计算机';

注意：不允许在where子句中使用列别名。这是因为，执行where代码时，可能尚未确定列值。例如，下列查询是犯法的： Select 性别 as set from members where set=’男‘；

(3)替换查询结果中的数据

语法：caseWhere 条件1then 表达式1Where 条件2then 表达式2
……
Else 表达式n
           End

例：查询book表中图书编号、书名和数量，对其库存数量按以下规则进行替换：若数量为空值，替换为“尚未进货”；若数量小于5，替换为“需进货”若数量在5-50之间，替换为“库存正常”若数量大于50，替换为“库存积压”。将标题更改为“库存”。Select 图书编号，书名，（对数量进行替换所以不用写在这了）

CaseWhere 数量 isnullthen'尚未进货'Where 数量 <5then'需进货'Where 数量 >=5and 数量<=50then'库存正常'Endas 库存
From book;

(4)计算列值
例：对sell表已发货的记录计算订购金额(订购金额=订购册数*订购单价),并显示图书编号和订购金额。

Select 图书编号,round(订购册数*订购单价,2)as 订购金额
     From sell
        Where 是否发货='已发货';

其中round()函数用于获得一个四舍五入后的整数值，逗号后面的数字指定小数的位数

(5)消除结果集中的重复行

格式：selectdistinct 列名1，列名2，……

复习题

一、填空题

1.DBMS的含义是:

数据库管理系统

。
2.用户名是root，密码是123456登录到数据服务器的命令是：

mysql-uroot-p123456

。
3.数据模型有层次模型、网状模型、

和关系模型

。
4.创建数据库的命令是

create database

。
5.显示数据库中有哪些表的命令是

show tables

。

二、写sql命令

1、创建yggl

createdatabase yggl;

2.将book表中所有书籍都增加10

update book
    set 数量=数量+10;

3.将members表中姓名为“张三”的员工的记录删除

deletefrom members
    where 姓名='张三';

4.现有employees，departments，salary三个代表，计算员工总数

selectcount(*)from employees;

5.显示收入高于2000元的员工的员工号

select 员工编号 from salary
    where 收入>2000;

6.查询1970年以后出生的员工的姓名和地址

select 姓名,地址 from employees
    where 出生日期>'1970-12-31';

7.查询“王琳”的基本情况和所工作的部门名称

select 员工编号,姓名,学历,出生日期,性别,工作年限,地址,电话号码,部门名称
from employees,departments
    where 员工部门号=部门编号 and 姓名='王琳';

8.按部门引出该部门工作的员工人数

select 员工部门号,count(*)as 员工人数
from employees groupby 员工部门号;

9.将员工信息按出生日期从大到小排序

select*from employees
orderby 出生日期 desc;

10.使用create index 语句创建索引：对employees表中的名字和地址列创建复合索引

createindex Ad_ind
on employees(姓名,地址);

三、创建一个存储过程，输入月份数字1~12，返回月份所在的季度

delimiter $$
createprocedure q_quarter
    (in mon int,out q_name uarchar(8))begincasewhen mon in(1,2,3)thenset q_name='一季度';when mon in(4,5,6)thenset q_name='二季度';when mon in(7,8,9)thenset q_name='三季度';when mon in(10,11,12)thenset q_name='四季度';elseset q_name='输入错误';endcase;end$$
delimiter;