MySQL数据库（基础)——期末复习总结_创建student2_1129 复合数据表 数据表来源格式 查看/root/test_data/et

一、 数据定义

1、库的管理

1.1、库的创建

语法：create database 库名；
     或create databdse if not exists 库名；
1
2

1.2、库的修改

语法：alter database 库名
1

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

Create database pet
           default(默认) character(字符) set(集) latin1
           default(默认) collate(校对) latin1_swedish_ci;
1
2
3

1.3、库的删除

语法：drop database (if esists) 库名;
1

1.4、库的显示

语法：show databases;
1

2、表的管理：

2.1、表的创建

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

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

Use bookstore;
create table book (
      图书编号 char(10)    not null  primary key,
      图书类别 varchar(20)  not null  default(列默认值) ‘计算机’,
      书名     varchar(40)  not null,
      作者     char(10)     not null,
      出版社   varchar(20)  not null,
      出版时间 date(时间)       not null,
      单价     float(5)(小数)      not null,
      数量     int(5),(整数)
      折扣     float(3),(小数)
      封面图片 blob(较长的文本)
) engine=innodb; 
1
2
3
4
5
6
7
8
9
10
11
12
13

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

2.2、表的修改

(1)添加新列

alter table 表名 add column(列) 新列名 数据类型 约束；
1

例：在表t1中增加新的一列a。
Alter table t1 add column a tinyint(表一个字节)  null
1
2

(2)删除列

Alter table 表名 drop column 列名 数据类型 约束；
1

(3)修改列名

Alter table 表名 change 旧列名 新列名 新列名的数据类型；
1

(4)修改列的类型

Alter table 表名 modify column 列名 列的新类型 约束；
1

例：在表t1中把a列的数据类型改为bigint。
Alter table t1 modify a bigint(表整型的八个字节) not null;
1
2

(5)修改表名

Alter table 表名 rename to 新表名；
1

1)添加新列，删除列:

use bookstore;
alter table book
    add 浏览次数 tinyint null,
		drop column 书名;
1
2
3
4

2)修改表名:
方法一：

use bookstore;
alter table book rename to mybook;
1
2

方法二：

use bookstore;
rename table mybook to booklist,member to memberlist;
1
2

2.3、表的复制

(1)仅仅复制表的结构

语法：create table (if not exists) 新表名 like 参照表名；
1

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

create table book_copy1 like book;
1

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

语法：create table (if exists) 新表名 as(select语句)；
1

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

create table book_copy2
   as
 (select * from book);
1
2
3

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

(3)只复制部分数据

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

2.4、表的删除

语法：drop table (if exists) 要被删除的表名；
1

例：删除表test。

Drop table if exists test；
1

2.5、表的显示

(1)显示数据表文件名

语法：Show tables
1

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

use bookstore;
show tables;
1
2

(2)显示数据表结构：

语法：describe / desc 表名 列名；
1

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

Desc book 图书编号；
1

3、 主键约束

3.1、定义主键（primary key）

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

create table book_copy
( 
    图书编号 varchar(6) null,
		书名 varchar(20) not null  primary key,
		出版日期 date
);
1
2
3
4
5
6

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

create table course
(
    学号       varchar(6) not null,
		姓名       varchar(8) not null,
		毕业日期   date       not null,
		课程号     varchar(3),
		学分       tinyint,
		primary key(学号,课程号,毕业日期)
);
1
2
3
4
5
6
7
8
9

3.2、定义主键的原则

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

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

create table course
(
    学号       varchar(6) not null,
		姓名       varchar(8) not null,
		毕业日期   date       not null,
		课程号     varchar(3),
		学分       tinyint,
		primary key index_course(学号,课程号,毕业日期)
);
1
2
3
4
5
6
7
8
9

4、替代键约束

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

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

create table book_copy1
(
   图书编号 varchar(20) not null,
	 书名     varchar(20) not null nuique,
	 出版日期 date null,
	 primary key(图书编号)
);
1
2
3
4
5
6
7

方法二：

create table book_copy1
(
   图书编号 varchar(20) not null,
	 书名     varchar(20) not null ,
	 出版日期 date null,
	 primary key(图书编号)
	 unique(书名)
);
1
2
3
4
5
6
7
8

5、添加约束

语法：alter table 表名
        Add primary key 索引方式 列名     添加主键
        / add unique 索引名 列名           添加代替键约束
        / drop primary key                  删除主键
        / drop index(索引) 索引名            删除索引
1
2
3
4
5

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

alter table book 
add primary key(图书编号),
   add unique u_idx(书名);
1
2
3

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

alter table book
    drop primary key,
		drop index u_idx;
1
2
3

6、参照完整性约束

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

定义外键的语法格式：

Foreign key(外键)
References(参照) 父表表名（父表列名）
         On delete(删除) restrict/cascade/set null/no action
         On update(更新) restrict/cascade/set null/no action
1
2
3
4

语法说明：

• 外键：参照表的列名。 父表表名：外键所参照的表名。这个表叫做被参照表，外键所在的表叫做参照表(子表)。
• 父表列名：被参照的列名。外键可以引用一个或多个列。
• 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、修改表的引擎

语法：alter table 表名 存储引擎；
1

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

Alter table book engine=innodb;
1

6.3、外键的创建

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

语法格式：create table 表名 列定义 外键定义
1

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

create table book_copy1
(
   图书编号 varchar(20) not null,
	 书名     varchar(20) not null,
	 出版日期 date null,
	 primary key(书名),
	 foreign key(图书编号)
	 references book(图书编号)
	    on delete restrict
			on update restrict
)engine=innodb;
1
2
3
4
5
6
7
8
9
10
11

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

create table book_copy1
(
   图书编号 varchar(20) not null,
	 书名     varchar(20) not null,
	 出版日期 date null,
	 primary key(书名),
	 foreign key(图书编号)
	    references book(图书编号)
	    on update cascade
)engine=innodb;
1
2
3
4
5
6
7
8
9
10

6.3.2、对已有表创建外键

语法：alter table 表名
           Add 外键定义
1
2

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

alter table sell
    add foreign key(用户号)
		 references members(用户号)
		    on delete cascade
				    on update cascade;
   
1
2
3
4
5
6

6.4、外键的删除

使用alter table语句

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

Alter table book drop primary key,
Alter table sell drop foreign key sell_ibfk _1;
1
2

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

语法：show create table 表名 
1

7、check完整性约束

语法：check(表达式)
1

语法说明：

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

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

create table student 
(
     学号 char(6) not null,
	 性别 char(2) not null check(性别 in ('男','女'))
);
1
2
3
4
5

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

create table student 
(
     学号 char(6) not null,
		 出生日期 date not null,
		 学分 int null,
		 check(出生日期>'1980-01-01')
);
1
2
3
4
5
6
7

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

	create table student 
(
     学号 char(6) not null,
性别 char(2) not null,
		 check(学号 in (select 学号 from student))
);
1
2
3
4
5
6

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

create table student 
(
        学号 char(6) not null,
		 最好成绩 int(1) not null,
		 平均成绩 int(1) not null,
		 check(最好成绩>平均成绩)
);
1
2
3
4
5
6
7

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

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

alter table book drop primary key;
alter table sell drop foreign key sell_idfk_1;
1
2

二、数据更新

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

1，数据插入

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

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

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

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

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

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

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

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

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

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

1.3，插入多行数据

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

Insert into members values
      ('co138','李华','123456','1382255555','2013-8-23'),
	('co139','张明','123456','13922666 66','2012-9-23');
1
2
3

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

2.1、单表数据修改

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

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

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

2.2、多表数据修改

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

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

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

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

3、数据删除

3.1、从单个表中删除行

Delete from 表名
Where 条件；
1
2

语法说明：

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

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

Use bookstore;
Delete from members
      Where 姓名='张三';
1
2
3

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

Use bookstore;
Delete from book
     Where 数量<5;
1
2
3

3.2、从多个表中删除行

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

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

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

或

Delete
     From sell,members
     Using sell,members
        Where sell.用户号=members.用户号
              And members.用户号='D1963';
1
2
3
4
5
6
7
8
9
10
11
12

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

语法：truncate table 表名
1

• 语法说明：truncate(截取) table 语句后，auto_increment(自动增加)计数器被重新设置为该列的初始值。

  对于参与了索引和视图的表，不能使用truncate table 删除数据，而应使用delete(删除)语句。
  
  
  
  

三、数据查询

1、单表查询

1.1、selete 语句定义

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

1.2、选择列

(1)选择指定的列

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

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

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

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

Select * from members; 
1

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

Select 列名 as 别名
From 表名
  Where 条件；
1
2
3

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

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

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

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

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

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

语法：case
         Where 条件1 then 表达式1
Where 条件2 then 表达式2
……
Else 表达式n
           End
1
2
3
4
5
6

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

Case
     Where 数量 is null then '尚未进货'
     Where 数量 < 5 then '需进货'
     Where 数量 >=5 and 数量<=50 then '库存正常'
  End as 库存
From book;
1
2
3
4
5
6

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

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

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

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

格式：select distinct 列名1，列名2，……
1

复习题

一、填空题

1.DBMS的含义是:数据库管理系统。
2.用户名是root，密码是123456登录到数据服务器的命令是：mysql-uroot-p123456。
3.数据模型有层次模型、网状模型、和关系模型。
4.创建数据库的命令是create database。
5.显示数据库中有哪些表的命令是show tables。

二、写sql命令

1、创建yggl

create database yggl;
1

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

update book
	set 数量=数量+10;
1
2

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

delete from members
	where 姓名='张三';
1
2

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

select count(*) from employees;
1

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

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

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

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

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

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

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

select 员工部门号,count(*) as 员工人数
from employees group by 员工部门号;
1
2

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

select * from employees
order by 出生日期 desc;
1
2

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

create index Ad_ind
on employees(姓名,地址);
1
2

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

delimiter $$
create procedure q_quarter
	(in mon int,out q_name uarchar(8))
begin
	case
		when mon in (1,2,3) then set q_name='一季度';
		when mon in (4,5,6) then set q_name='二季度';
		when mon in (7,8,9) then set q_name='三季度';
		when mon in (10,11,12) then set q_name='四季度';
	else set q_name='输入错误';
	end case;
end$$
delimiter;
1
2
3
4
5
6
7
8
9
10
11
12
13