2.1数据库基础知识

1.Dos登录

命令:mysql -h hostname -u username -p

例如:mysql -h 123 -uroot -p

简写:mysql -u root -p

复合主键 就是指你表的主键含有一个以上的字段组成

> create table test 
> ( 
>   name varchar(19), 
>   id number, 
>   value varchar(10), 
>   primary key (name,id) 
> ) 

上面的name和id字段组合起来就是你test表的复合主键
它的出现是因为你的name字段可能会出现重名，所以要加上ID字段这样就可以保证你记录的唯一性
一般情况下，主键的字段长度和字段数目要越少越好

---

2.1.1创建和查看数据库

• 创建数据库

语法:create database 数据库名称;
create database itcast;/*创建数据库itcast:*/

• 查看数据库

语法:show databases/*查看数据库*/

• 查看某个数据库

语法:show create database 数据库名称;
例如:show create database itcase;/*查看数据库itcase信息*/

• 使用数据库

语法:use 数据库名称;
例如:use itcast;/*使用数据库itcast*/

---

2.1.2修改数据库

• alter 数据库名称;修改数据库

语法:alter database 数据库名称 default character set 编码方式 collate 编码方式_bin
例如:修改数据库,将数据库itcast的编码修改为gbk
> alter database itcast default character set gbk collate gbk_bin;

---

2.1.3删除数据库

语法:drop database 数据库名称;
例如:drop database itcast;/*删除名称为itcast的数据库*/

---

2.2数据类型

2.2.1 整数类型

1.在MySQL数据库中，经常需要存储整数数值。根据数值取值范围的不同，MySQL中的整数类型可分为5种，分别是
TINYINTtinyint、SMALLINT、 MEDIUMINT、INT和BIGINT。不同整数类型的取值范围可以根据字节数计算出来，例如，
TINYINT类型的整数占用1个字节，1个字节是8位，那么，TINYINT类型无符号数的最大值就是28-1，即255。 同理可以算出其它不同整数类型的取值范围。

**注意:**在足够使用的情况下选择选择尽量小的数据类型

---

2.2.2浮点数类型与定点数类型

2.在MySQL数据库中，存储的小数都是使用浮点数和定点数来表示的。浮点数的类型有两种，分别是单精度浮点数类型(float)和双精度浮点类型(double)。而定点数类型只有decimal，类型。

浮点型:非精准数据类型,有误差

定点型:精准类型

---

2.2.3日期与时间类型

3.为了方便在数据库中存储日期和时间，MySQL提供了表示日期和时间的数据类型,分别是YEAR、DATE、TIME、DATETIME和timestamp

year:1字节

例如:year 2014 ='2014' p35

'00'~'69' 20?? 58= 2058

'70'~'99' 19?? 89 =1989

1.YEAR类型:(字符串类型与数字类型)'2020' 2020 '00-99' 1-99

2.DATE类型: '2020-06-21' '20200621'

current_data或now()/表示当前日期/

3.TIME类型:表示时间值

格式:HH:MM:SS

current_data或now()表示当前系统时间

4.DATATIME类型

now()表示当前日期和时间

5.TIMESTAMP类型

与DATATIME类型显示形式相似,但取值范围比DATATIME类型小

---

2.2.4 字符串和二进制类型

为了存储字符串、图片和声音等数据，MySQL提供了字符串和二进制类型。包括CHAR和VARCHAR类型、BINARY和VARBINARY类型、TEXT类型、BLOB类型ENUM类型、SET类型和BIT类型

1.CHAR和VARCHAR类型:

​ CHAR和VARCHAR类型都用来表示字符串数据,VARCHAR可以储存可变字符串

语法:CHAR(M)或VARVCHAR(M) M指字符串的最大长度;

VARCHAR(4)所对应的数据所占用的字节数为实际长度加1;

char:浪费时间,但查询速度快;

varchar:节约空间,但查询速度慢

''	''	4个字节	''	1个字节
'ab'	'ab'	4个字节	'ab'	2个字节
'abc'	'abc'	4个字节	'abc'	3个字节
'abcd'	'abcd'	4个字节	'abcd'	4个字节
'abcdef'	'abcd'	4个字节	'abcd'	5个字节

---

2.BINARY和VARBINARY类型:

​ BINARY和VARBINARY类型类似CHAR和VARCHAR类型,不同的是,它们表示二进制数据

语法:BINARY(M)或VARBINARY(M) M指二进制数据的最大字节长度;

注意:BINARY类型的长度是固定的,如果数据的长度不足最大长度,将在数据的后面用"\0"补齐,最终达到指定长度.

例如,指定数据的类型是BINARY(3),当插入a时,实际储存的数据为"a\0\0",当插入ab时,实际储存的数据是"ab\0";

---

3.TEXT类型:

TEXT类型用来表示大文本数据,例如,文章内容、评论等,它的类型分为4种;

数据类型	储存范围	数据类型	储存范围
TINYTEXT	0-255字节	MEDIUMTEXT	0-16777215字节
TEXT	0-65535字节	LONGTEXT	0-4294967295字节

---

4.BLOB类型:

​ BLOB类型是一种特殊的二进制类型,它用于表示数据量大很大的二进制数据,例如 图片、PDF文档等

数据类型	储存范围	数据类型	储存范围
TINYBLOB	0-255字节	MEDIUMBLOB	0-16777215
BIOB	0-65535字节	LONGBILOB	4294967295

注意:BLOB类型数据是根据二进制编码进行比较和排序,而TEXT类型数据是根据文本模式进行比较和排序

---

5.ENUM类型:

ENUM类型又称为枚举类型

ENUM('值1','值2','值3',...'值n')

注意:MYSQL中存入的是这个顺序编号,而不是列表中的值

---

6.SET类型:

SET类型用于表示字符串对象,它的值可以有零个或多个,SET类型数据的定义格式与ENUM类型相似

语法:SET('值1','值2','值3',...'值n')

注意:MYSQL中存入的是这个顺序编号,而不是列表中的值

---

7.BIT类型:

​ 用于表示二进制数据

语法:BIT(M) M用于表示每个值的位数,范围是1-64;

注意:如果分配的BIT(M)类型的数据的长度小于M,将在数据的左边用0补齐

例如,为BIT(6)分配值b'101'与分配b'000101'相同

---

2.3创建数据表

create database student;/*创建名为student*/
use itcase;/*使用数据库*/
create TABLE tb_grade/*创建名为tb_gradea的数据表*/
(
	id INT(11),/*举例*/
	name VARCHER(20),
	grade FLOAT
);
show tables;/*验证数据库是否创建成功*/ 
show create table tb_greade;/*查看数据表创建时的定义语句,查看表的字符编码*/
describe tb_greade;/*查看表的字段信息,包括字段名、字段类型等信息*/

修改数据表

​ 1.修改表名;

alter table 表名 rename 新表名;
alter table tb_grade rename(to) grade;

​ 2.修改字段名;

alter table 表名 change 旧字段名 新字段名 新数据类型;
alter table grade change name username verchar(20);/*name字段改为username,数据类型保持不变*/
desc greade;/*验证*/

​ 3.修改字段的数据类型;

alter table 表名 modify 字段名 数据类型;
alter table grade modify id int(20);/**/

​ 4.添加字段;

alter table 表名 add 新字段名 数据类型
	[约束条件][first|after 已存在的字段名]
alter table grade add age int(10);
alter table grade add age int(10) first;/*添加到第一位*/
alter table grade add age int(10) after id;/*添加到id后面*/
alter table grade add age int(10) not null first;/*不允许为空*/

​ 5.删除字段;

alter table 表名 drop 字段名;
alter table grade drop age;

​ 6.修改字段的排列位置;

alter table 表名 modify 字段名1 数据类型 first|after 字段名2;
字段名1 指要修改位置的字段;
first是可选参数,指的是将字段1修改为表的第一个字段;
after 字段名2 是将字段1插入到字段2后面;
alter table grade modify id int(10) after grade;/*将id字段插入到grade字段后面*/

---

删除数据表

drop table 表名;
drop table grade;
desc table grade;/*验证*/

---

2.4表的约束

• 防止数据表中插入错误的数值,而定义的维护数据库完整性的规则

约束条件	说明
PRIMARY KEY	主键约束，用于唯一标识对应的记录(主键唯一表示,值不能为空null );
FOREIGN KEY	外键约束
NOT NULL	非空约束
UNIQUE	唯一性约束
DEFAULT	默认值约束，用于设置字段的默认值

2.4.1 主键约束;

注意:每个数据表中最多只能有一个主键约束,定义为primary key的字段不能有重复值且不能为null值

• 1.单字段约束

语法: 字段名 数据类型 PRIMARY KEY
例如:创建一个数据表ex1,并设置id作为主键,SQL语句如下:
CREATE TABLE ex1(
    id INT PRIMARY KEY,
	name VARCHAR(20),
	grade FLOAT
);

• 2.多字段主键

语法:PRIMARY KAY(字段名1,字段名2,***字段名n)
例如:创建一个数据表ex2,在表中将stu_id和course_id两个字段共同作为主键,SQL语句如下:
CREATE TABLE ex2(
    stu_id INT,
	course_id FLOAT,
	PRIMARY KEY(stu_id,course_id)
);

2.4.2非空约束

• 非空约束指的是字段的值不能为null,在MySQL中,非空约束是通过NOT NULL定义的;

语法: 字段名 数据类型 NOT NULL;
例如:CREATE TABLE ex1(
    id INT PRIMARY KEY,
	name VARCHAR(20) NOT NULL,
	grade FLOAT not null
	);

2.4.3唯一约束

• 保证数据表中字段的唯一性,即表中字段的值不能重复出现

语法: 字段名 数据类型 UNIQUE;
CREATE TABLE ex1(
    id INT PRIMARY KEY,
	name VARCHAR(20) NOT NULL,
	grade FLOAT UNIQUE
	);
ID为主键,name的字段不能为空值,grade字段为唯一值,该值不能重复

2.4.4默认约束

• 用于给数据表中的字段添加指定默认值

语法:字段名 数据类型 DEFAULT 默认值;
例如:CREATE TABLE ex1(
    id INT PRIMARY KEY,
	name VARCHAR(20) NOT NULL,
	grade FLOAT default 0/*默认值为0*/
	);

---

2.5设置表的字段值自动增加

• 在数据表中，若想为表中插入的新记录自动生成唯一的ID，可以使用AUTO_INCREMENT约束来实现。
  AUTO_INCREMENT约束的字段可以是任何整数类型，默认情况下，该字段的值是从1开始自增的

语法: 字段名 数据类型 AUTO_INTCREMENT;
例如:创建一个数据表ex5,将表中的id字段设置为自动增加,SQL语句如下:
CREATE TABLE ex1(
    id INT PRIMARY KEY AUTO_INCREMENT, 
	name VARCHAR(20) NOT NULL,
	grade FLOAT UNIQUE
	);

---

2.6索引

2.6.1索引的概念

数据库的索引好比新华字典的音序表，它是对数据库表中一列或多列的值进行排序后的一种结构，其作用就是提高表中数据的查询速度

• 1.普通索引

  普遍索引是由KEY或INDEX定义的索引，它是MySQL中的基本索引类型,可以创建在任何数据类型中，其值是否唯一和非空 由字段本身的约束条件所决定。

• 2.唯一性索引

  唯一性索引是由UNIQUE定义的索引,该索引所在字段的值必须是唯一的。

• 3.全文索引/文本/

  全文索引是由FULLTEXT定义的索引，它只能创建在CHAR、VARCHAR或TEXT类型的字段上,而且,现在只有MyISAM存储引擎支持全文索引。

• 4.单列索引

  单列索引指的是在表中单个字段上创建索引，它可以是普通索引、唯一索引或者全文索引,只要保证该索引只对应表中一个字段即可。

• 5.多列索引

  多列索引指的是在表中多个字段.上创建索引，只有在查询条件中使用了这些字段中的第一个字段时,该索引才会被使用。

• 6.空间索引

  空间索引是由SPATIAL定义的索引，它只能创建在空间数据类型的字段上。创建时必须声明为not null,并且只有MyISAM存储引擎支持

---

2.6.2 创建索引

> 1.创建表的时候创建索引

语法:CREATE TABLE 表名(字段名 数据类型 [完整性约束条件],
  					  字段名 数据类型 [完整性约束条件],
  					  ...
  					  		字段名 数据类型
  					  		[UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY
  					  			[别名] (字段名1 [(长度)] [ASC|DESC])
  					  			);

• UNIQUE:可选参数，表示唯一索引
• FULLTEXT:可选参数，表示全文索引
• SPATIAL:可选参数，表示空间索引
• INDEX和KEY:用来表示字段的索引，二者选一即可
• ASC和DESC可选参数，ASC表示升序排列，DESC表示降序排列
• 别名:可选参数，表示创建的索引的名称
• 字段名1:指定索引对应字段的名称，
• 长度:可选参数，用于表示索引的长度

>1.创建普通索引

mysql> create table t2(
    -> id int(11),
    -> name varchar(20),
    -> grade float,
    -> index index(id asc)/*创建名为index的索引,升序排列*/
    -> );
建立后,使用show create table查看表的结构
	

==show create table t1\G; t1\G 使排列有序
为了查看索引是否被使用可以使用explain语句进行查看,SQL代码如下:

explain select * from t1 while id=1 \G

>2.创建唯一性索引

mysql> create table t2(
    -> id int(11),
    -> name varchar(20),
    -> grade float,
    -> unique index unique_id(id asc)/*在表中id字段上建立索引名为unique_id的唯一性索引,并且按照升序排序*/
    -> );

>3.创建全文索引

mysql> create table t1(
    -> id int(11),
    -> name varchar(20),
    -> grade float,
    -> fulltext index fulltext_name(name)
    -> )engine=myisam;/*因为目前只有MyISAM存储引擎支持全文索引*/

>4.创建单列索引

mysql> create table t4(
    ->name varchar(20) not null,
    -> score float,
    -> index single_name(name(20))/*在name字段上创建一个名叫single_name的单列索引索引长度为20*/
    -> );

>5.创建多列索引

mysql> create table t5(
	->id int not null,
    ->name varchar(20) not null,
    -> score float,
    -> index multi(id,name(20))/*在id和name字段上创建一个名叫multi的多列索引*/
    -> );

注意:在多列索引中,只有查询条件中使用了这些字段的第一个字段时多列索引才会被使用

>6.创建空间索引

mysql> create table t6(
	->id int,
    ->space geometry not null,
    ->spatial index sp(space)/*在space字段上建立一个名为sp的空间索引*/
    ->)engine=myisam;

注意:创建空间索引时,所在字段不能为空值,并且表的储存引擎为myisam

---

2.使用CREATE INDEX语句在已经存在的表上创建索引

语法CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名 
	ON 表名 (字段名 [(长度)] [ASC|DESC]);

mysql> create table book(
    -> bookid int not null,
    -> bookname varchar(255) not null,
    -> authors varchar(255) not null
    -> info varchar(255) null,
    -> comment varchar(255) null,
    -> publicyear year not null
    -> );

>1.创建普通索引

mysql> create index index_id on book(bookid);/*在book表中的bookid字段上建立一个名为index_id的普通索引*/
mysql> show create table book\g;/*查看数据表结构*/

>2.创建唯一性索引

create unique index uniqueidx on book(bookid);
/*在bookid字段上创建一个名为uniqueidx的唯一性索引*/

>3.创建单列索引

create index singleidx on book(comment);
/*在comment字段上创建一个名称为singleidx的单列索引*/

>4.创建多列索引

create index mulitidx on book(authors(20)),info(20));
/*在authors和info字段上创建一个名称为mulitidx的多列索引*/

>5.创建全文索引

create fulltext index fulltextidx on book(info);
/*在info字段上创建名为fulltextidx的全文索引*/

>6.创建空间索引

create table t7(
				g geometry not null
				)engine=myisam;
create spatail index spatidx on t7(g);/*在g字段上创建名称为spatidx的空间索引*/

---

3.使用ALTER TABLE 语句在已经存在的表上创建索引

语法:ALTER TABLE 表名 ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX  
					索引名 (字段名[(长度)] [ASC|DESC] )

create table book(
				  bookid int not null,
				  bookname varchar(255) not null,
				  authors varchar(255) null,
				  comment varchar(255) null,
				  pubblicyear year not null
				  );

>1.创建普通索引

alter table book add index id(bookid);

>2.创建唯一性索引

alter table book add unique uniqueidx(bookid);

>3.创建单列索引

alter table book add index singleidx(comment(50));

>4.创建多列索引

alter table book add index multidx(authors(20),info(50));

>5.创建全文索引

alter table book add fulltext index fulltextidx(info);

>6.创建空间索引

create table t8(
				space geometry not null
				)engine=myisam;
alter table t8 add spatial index spatidx(space);

---

2.6.3删除索引

> 1.使用alter table删除索引

语法:alter table 表名 drop index 索引名;

例如: 删除表book中名叫fulltextidx的全文索引
alter table book drop index fulltextidx;

> 2.使用 drop index删除索引

语法:drop index 索引名 on 表名;

---

---

3.1添加数据

> 3.1.1为表中所有字段添加数据;

> 1.insert语句中指定所有字段名

向表中添加新记录时,可以在insert语句中列出表中的所以字段名;

注意:必须列出表中所以的字段名称,"值1,值2,.."的顺序,类型必须与对应的字段相匹配

语法:insert into 表名(字段名1,字段名2,...)
	values(值1,值2,...);

• 验证数据是否成功使用select语句查看student表中的数据

select * from 表名;

> 2.insert语句中不指定字段名

注意:由于insert语句中没有指定字段名,添加的值的顺序必须和字段在定义的顺序相同

语法: insert into 表名 values(值1,值2,...);

>3.1.2为表中指定的字段添加数据

语法: insert into 表名(字段1,字段2,...)
			values(值1,值2)

>3.1.3同时添加多条记录

语法:insert into 表名 [(字段名1,字段名2,...)]
			values(值1,值2,...),
			(值1,值2,...),
			(值1,值2,...);

例如:
	向student表中添加三条新纪录,insert语句如下:
mysql> insert into student values
	-> (6,'lilei',99),
	-> (7,'hanmeimei',100),
	-> (8,'poly',40.5);

---

3.2更新数据

语法:

update 表名
	set字段名1=值1[,字段2=值2,...]
	[where条件表达式]

> 1.update 更新部分数据

例如:更新student表中id字段值为1的记录,将记录中的name字段的值更改为'caocao',grade字段的值更改为50;
mysql> update student
	> set name='caocao',grade=50
	> where id=1

> 2.update 更新全部数据

例如:将student表中全部记录,将grade字段值都变为100:
>update student
>set grade=100

---

3.3 删除数据

delete from 表名[where 条件表达式]

>1.删除部分数据

>delete from 表名
>where 条件表达式

例如:删除表student上id字段上小于4的记录
>delete from student
>where id<4;

>2.删除全部数据

>1.delete

delete from 表名;

>2.truncate

truncate from 表名;

区别:

1. delete语句后跟where子句,可以删除部分记录,truncate只能删除表中全部记录
2. delete语句删除数据后,再次向表中增加记录时,自动增加字段的值为删除时该字段的最大值加1,truncate语句删除数据后,自动字段的默认初始值重新由1开始;

---

---

4.1 简单查询

>4.1.1 select 语句

语法 //顺序不能颠倒

mysql> > select [distinct] * |字段名1,字段名2,字段名3,...
	>	from 表名
	>	[where 条件表达式1]
	>	[group by 字段名 [having条件表达式2]]
	>	[order by 字段名 [asc|desc]]
	>	[limit [offset] 记录数]

>4.1.2 查询所有字段

> 1.在select语句中指定所有字段

语法:select 字段名1,字段名2,...from 表名

> 2.在select语句中使用星号(*)通配符代替所有字段

select * from 表名

>4.1.3 查询指定字段

语法: select 字段名1,字段名2,...from 表名;

---

4.2 按条件查询

>4.2.1带关系运算符查询

where 子句中指定查询条件对数据进行过滤

语法:select 字段名1,字段名2,... from 表名 where 条件表达式

>4.2.2 带in关键字的查询

in关键字用于判断某个字段的值是否在指定集合中

语法:select * |字段名1,字段名2,... from 表名 where 字段名[not] in (元素1,元素2)
	"元素1,元素2"表示集合中的元素,即指定的条件范围
	not 为可选参数,使用not表示查询不在in关键字指定集合范围的记录

例如:查询student表中ID值为1,2,3的记录
mysql> select id,grade,name,grade from student where id in(1,2,3);

>4.2.3带between and 关键字查询

between and 用于判断某个字符的值是否在指定的范围之内,如果字段的值在指定的范围内,则满足条件

select * |{字段名1,字段名2,...} from 表名 where 字段名 [not] between 值1 and 值2

例如: 查询student表中id值在2~5之间的学生姓名
mysql> select id,name from student where id between 2 and 5;

>4.2.4 空值查询

在数据表中,某些列的值可能为空值(null),空值不同于0,也不同于空字符串

在mysql中,使用is null关键字来判断是否为空值

语法:select * |字符段1,字符段2,... from 表名 where 字段名 is [not] null;

例如:查询Student表中gender为空值的记录
mysql> select id,name,grade,gender from student where gender is null;

>4.2.5 带distinct关键词的查询

很多表中某些字段的数据存在重复的值,使用distinct过滤重复记录的字段

例如:在student表中查询gender(性别)字段的值,查询记录不能重复;
mysql> select distinct gender from student;

distinct关键字作用于多个字段,如果字段名1字段名2里的值都相同才被认为是重复记录

语法:select distinct 字段名1,字段名2,...from 表名;

>4.2.6 带like关键字的查询

使用关系运算符"="可以判断两个字符串是否相等,但有些需要是对字符串进行模糊查询

select * | {字符段1,字符段2,...}
from 表名
where 字段名 [not] like '匹配字符串';

1. 百分号(%) 通配符

作用 : 匹配任意长度的字符串 ,包括空字符串

例1: 查询student表中name字段值以字符"s"开头的学生 id
mysql> select id, name from student where name like "s%" ;

例2:查询student表中name字段值以字符"w"开头,以字符"g"结束的学生 id
mysql> select id, name from student where name like "w%g" ;

例3:查询student表中name字段值包括字符"y"的学生 id;
mysql> select id, name from student where name like "%y%" ;

2. 下划线(_) 通配符

作用 : 下划线通配符只能匹配单个字符,如果需要匹配多个则需要使用多个下划线

例1: 查询student表中name字段值以字符串"wu"开始, 以字符串"ong"结尾,并且两个字符串之间只有一个字符的记录
mysql> select * from student where name like "wu_ong";

例2: 查询student表中name字符值包括7个字符, 并且以字符串"ing"结尾的记录
mysql> select * from student where name like "_______ing";

3.使用百分号和下划线通配符进行查询操作

作用 : 通过右斜线( " \ " )对百分号和下划线进行转义

例如 : "%"匹配百分号字符值 , " \ _ "匹配下划线字符值

例如: 查询student表中name字段值包括"%"的记录;
mysql> insert into student (name,grade,gender)
	-> values ('sun%er',95,'男');
	
mysql> select * from student where name like "%\%%";

>4.2.7 带and关键字的多条件查询

and关键字连接两个或以上查询条件

例如:select * from 表名 where 条件表达式1 and 条件表达式2 [...and 条件表达式n]
查询student表中id字段值在1.2.3.4之中,name字符值以字符串'ng'结尾,并且grade字段值小于80的记录
mysql> select id,name,grader,gender
	-> from student
	-> where id in(1,2,3,4) and name like '%ng' and grade <80;

>4.2.8 带or关键字的多条件查询

or关键字满足一个,可以连接两个或多个查询条件

例如:查询student表中满足条件name字段值以字符'h'结束,或者gender字符值为'女',或者grade字段值为100的记录
mysql> select id,name,grade,gender
	-> from student
	-> where name like '%h' or gender='女' or grade=100;

注意:or和and可以一起使用, and优先级高于or

例如:查询student表中student字符值为'女'或者gender字符值为'男',并且gender字段值为100的学生姓名
mysql> select name,grade,gender
	-> from student
	-> where gender='女' or gender ='男' and grade=100;

---

4.3 高级查询

>4.3.1 聚合函数

>1.count()函数

COUNT()函数用来统计记录的条数，其语法格式如下所示:
SELECT COUNT(*) FROM 表名;

例如: 查询student表中一共有多少条记录
mysql> select count(*) from student;

>2.sum()函数

SUM()是求和函数，用于求出表中某个字段所有值的总和，其语法格式如下:
SELECT SUM(字段名) FROM 表名

例如:求出student表中grade字段的总和
mysql> select sum(grade) from student7

>3.avg()函数

AVG()函数用于求出某个字段所有值的平均值其语法格式如下所示:
SELECT AVG(字段名) FROM student;
例如:求出student表中grade字段的平均值
>select avg(grade) from student

>4.max()函数

MAX()函数是求最大值的函数，用于求出某个字段的最大值，其语法格式如下所示:
SELECT MAX(grade) FROM student;
例如:求出student表中grade字段的最大值
>select max(grade) from student

>5.min()函数

MIN()函数是求最小值的函数，用于求出某个字段的最小值，其语法格式如下所示:
SELECT MIN(grade) FROM student;
例如:求出student表中grade字段的最小值
>select min(grade) from student

>4.3.2 对查询结果排序

​ 从表中查询出来的数据可能是无序的,或者排列顺序不是用户期望的,为了使查询结果满足用户的要求,可以使用order by

​ 对查询结果进行排序,语法格式如下:

使用 ORDER BY对查询结果进行排序其语法格式如下所示:
SELECT字段名1, 字段名2, FROM 表名
ORDER BY字段名1 [ASC|DESC]，字段名2 [ASC|DESC] /*asc:升序,从小到大,desc:倒叙,从大到小*/

例2:查询student表中的所有记录,按照gender字段的升序和grade字段的降序进行排序:
mysql> select * from student
	-> order by gender asc, grade desc;

==注意:==在按照指定字段进行升序排列时，如果某条记录的字段值为NULL，则这条记录会在第一条显示，这是因为NULL值可以被认为是最小值

> 4.3.3 分组查询

>1.单独使用group by 分组

单独使用group by 关键词,查询的是每个分组的一条记录

例如:查询student表中的记录,按照gender字段值进行分组
select * from student group by gender;

查询结果是按照gender字段中的不同值进行分类的,查询结果只显示每个分组

>2.group by和聚合函数一起使用

gruop by 和聚合函数一起使用可以统计出某个或者某些字段在一个分组中的最大值,最小值,平均值等

例如:将student表中gender字段值进行分组查询,计算出每个分组中各有多少名学生
select count(*),gender from student group by gender; 
例如:将student表中gender字段值进行分组查询,计算出每个分组的总成绩,平均成绩,最高分,最低分
select sum(grade),avg(grade),max(grade),min(grade),genter from student3 group by genter;

>3. group by 和 having关键词一起使用

having关键字和 where关键字的作用相同,都设置条件表达式对查询结果进行过滤,两者区别在于,

having关键字后可以跟聚合函数,而where 关键字不能

例如:将student表中按照gender字段进行分组查询,查找grade字段值之和小于300的分组
select student sum(grade),gender from student group by gender having sum(grade)<300 

>4.3.4 使用limit限制查询结果的数量

查询数据时,可能会返回很多条记录,而用户需要的记录可能只是其中的一条或者几条,比如实现分页功能,每页显示10行信息,每次查询都需要查出10条记录

格式:select 字段名1,字段名2,...
	from 表名
	limit [offset,] 记录数  //limit后可以跟两个参数,第一个参数offset表示偏移量,如果为0则从查询结果的第一条开始记录,偏移量为1则从查询结果中的第二条记录开始,以此类推;offset为可选值,如果不指定其默认值为0.第二个参数"记录数"表示返回查询记录的条数;

例(1):查询Student表中的前四条记录
select * from student limit 4;	//没有指定返回记录的偏移量,只指定了查询记录的条数4,因此返回结果从第一条开始,一共返回4条记录;

例(2):查询student表中grade字段值从第五位到第八位的学生(从高到低)
select * from student group desc limit 4,4

limit后面第一个参数表示偏移量位4,即从第五条开始查询,第二个参数表示一共返回4条记录,即从第五位到第8为学生

>4.3.5 函数(列表)

函数名称	作用
ABS(x)	返回x的绝对值
SQRT(x)	返回x的非负2次方根
MOD(xy)	返回x被y除后的余数
CEILING(x)	返回x的绝对值
FLOOR(z)	返回x的绝对值
ROUND(x,y)	返回x的绝对值
TRUNCATE(x)	返回x的绝对值
SIGN(x)2	返回x的绝对值

---

4.4 为表和字段取别名

> 4.4.1 为表取别名

在查询操作时,如果表名很长使用起来就不太方便,这时可以为表取一个别名,用这个别名来代替表的名称;

语法:	select * from 表名 [as] 别名;	as关键字用于指定表名的别名,它可以省略不写;
例如: 为student表起一个别名 s,并查询student表中gender字段值为'女'的记录
select * from student as s where s.gender='女';	
student as s表示student表的别名为s
s.gender 表示student表中的gender字段;

> 4.4.2 为字段取别名

为了让显示查询结果更加直观,可以为字段取一个别名

select 字段名 [as] 别名 [,字段名 [as] 别名,...] from 表名;

例如:查询student表中的所有记录的name和grade字段值,并为这两个字段名起别名stu_name和stu_genter;
select * from name as stu_name, grader as stu_genter from student;

---

---

5.1 外键

> 5.1.1 什么是外键

外键是指引用另一个表中的一列或多列,==被引用的列应该具有主键约束或唯一性约束.==外键用来建立和加强两个表数据之间的连接

引入外键后,外键列只能插入参照列存在的值,参照列被参照的值不能被删除,这就确保了数据的参照完整性

创建两个表,一个班级表(grade),一个学生表(student)

create table grade(
	id int(4) not null primary key,
	name varchar(36)
);

create table student(
	sid int(4) not null primary key,
	sname varchar(36),
	gid int(4) not null
);

学生表(student)中的gid 是学生所在的班级id,是引入了班级表(grade) 中的主键id,那么gid就可以作为表student的主键..被引用的表,即表grade是主表;引用外键的表,即表student是从表,两个表是主从关系.

表student用gid可以连接表grade中的信息

>5.1.2 为表中添加外键约束

语法: alter table 表名 add constraint FK_ID foreign key(外键字段名) references 主表表名(主键字段名);

语句: alter table student add constraint FK_ID foreign key(gid) references grade(id);

>5.1.3 删除外键约束

在实际开发中,根据业务逻辑的需求,需要解除两个表的关联关系时, 就需要删除外键约束

语法: alter table 表名 drop foreign key 外键名;

接下来,将表中student表的外键约束删除

alter table student drop foreign key FK_ID; 

---

5.2 操作关联表

>5.2.1 关联关系

1.多对一 (外键定义在多端)

一个部门可以有多个员工，而一个员工不能属于多个部门。
在多对一的表关系中，应该将外键建在多的一方

2.多对多 (需定义中间表)

一个老师可以教多个学生，同时一个学生可以上多个老师的课程。
为了实现数据表多对多的关系，需要定义一张第三方中间表，该表保存两个关系表的外键。

3.一对一 (从表依赖主表)

一对一的对应关系中，需要分清主从关系，通常在从表中建立外键

>5.2.2 添加数据

在开发中，最常见的关联关系就是多对一关系

为表student和表grade中添加外键约束来建立两个表的关联关系
alter table student add constraint FK_ID foreign key(gid) references grade (id);

>5.2.3 删除数据

由于grade表与student表之间具有关联关系 , 参照列被参照的值不能被删除的,再删除软件二班时需要删除软件二班的所有学生

delete from student where sname='王红';
delete from student where sname='李强';
delete * from grade where id=2;

---

5.3 连接查询

> 5.3.1交叉连接

交叉连接返回的结果是被连接的两个表中所有数据行的笛卡尔积即行数的乘积

例如:department表中有4个部门,employee表中有4个员工,那么交叉连接的结果就有4X4=16条数据

语法: select *from 表1,表2;
	 或select * from 表1 cross join 表2;

> 5.3.2 内连接

内连接（INNER JOIN）又称简单连接或自然连接，内连接使用比较运算符对两个表中的数据进行比较，列出与连接条件匹配的数据行，组合成新的记录

简单连接或自然连接,返回的结果是两个表中一一对应的数据,只有满足条件的数据才显示

语法:select 查询字段 from 表1[inner] join 表2 on 表1.关系字段= 表2.关系字段
例如: 在department表和employee表之间使用内连接查询;
select employee.name,department.dname from department inner join employee on department.did=employee.did;	//只有department.did和employee.did相等的才输出

使用where条件语句来实现同样的功能;
例如:在department表和employee表之间使用where:
select employee.name, department.dname from department,employee where department,did=employee.did;

注意:where子句的查询结果与使用inner join的查询结果是一致的,虽然结果相同,但inner join 是内连接语句,where是条件判断语句,在where语句后可以添加其他条件,而inner join语句不可以;

如果在一个连接查询中,涉及的两个表是同一个表,这种查询称为自连接查询,指的是相互连接的表在物理上为同一个表,但逻辑上分为两个表

查询部门为2的员工的姓名和年龄;
select department.did,employee.name,employee.age from department join employee on department.did=employee.did where department.did=2;
或
select department.did,employee.name,employee.age from department,employee where department.did=employee.did and department.did=2;

查询姓名为王红所在的部门编号和部门名称
select employee.name,department.did,department.dname from department,employee where department.did=employee.did and employee.name='王红';

自连接:

例如:在department表和employee表之间使用自连接查询王红所在的部门有哪些员工;
select p1.* from employee p1 inner join employee p2 on p1.did=p2.did where p2.name='王红';	/*p1 p2为别名*/
或select p1.* from employee p1,employee p2 where p1.did=p2.did and p2.name='王红';/*where方法*/

> 5.3.3 外连接

外连接分为左连接和有连接

左连接的结果包括LEFT JOIN子句中指定的左表的所有记录，和所有满足连接条件的记录。如果左表的某条记录在右表中不存在，则在右表中显示为空 ;

右连接与左连接正好相反，返回右表中所有指定的记录和所有满足连接条件的记录。如果右表的某条记录在左表中没有匹配，则左表将返回空值 ;

做外链接:左表中的所有记录以及内连接的记录(放在前面的是左表)

右外连接:右表中的所有记录以及内连接的记录

语法: SELECT 所查字段 FROM 表1 LEFT|RIGHT [OUTER] JOIN 表2 
	ON 表1.关系字段 = 表2.关系字段 WHERE 条件
	
	select * from 表1,表2 where表1.关系字段=表2.关系字段 and 其他条件

>全外连接查询

在外连接的基础上,增加左边表有,右边表没有的记录,以及右边表有左边表没有的记录

语法: select * from 表1 full jion 表二 on 表一.关系字段=表二.关系字段;
	select * from dep full jion emp on dep.did=em.did;

>5.3.4 复合条件查询

---

5.4 子查询

>5.4.1 带IN关键字的子查询

使用in关键字进行子查询时,内层查询语句仅返回一个数列值,这个数列值中的值将供外层查询语句进行比较操作

例如:查询存在年龄为20岁的员工的部门
select * from department where did in(select did from employee where age=20);
或											           //子查询方法
select department.did,department.dname from department join employee on department.did=employee.did where employee.age=20;		//内连接方法
或
select department.did,department.dname from department,employee where department.did=employee.did and employee.age=20;	 	  //where方法

>5.4.2 带exists关键字的子查询

exists关键字后面的参数可以是任意一个子查询,这个子查询的作用相当于测试,他不产生任何数据,只返回true或false,当返回值为true时,外层查询才会执行

例如:查询employee表中是否存在年龄大于21岁的员工,如果存在,则查询department表中的所有记录
select * from department where exists(select did from employee where age>21);

> 5.4.3 带any关键字的子查询

any关键字表示满足其中任意一个条件,它允许创建一个表达式对子查询的返回值列表进行比较

例如:
select * from department where did>any(select did from employee);//列出只要大于employee.did任意一个数

>5.4.4 带all关键字的子查询

例如:
select * from department where did>all(select did from employee);/列出大于employee.did所有的数

>5.4.5 带比较运算符的子查询

比较运算符 > , < , = , >= , <= , ! =

例如:使用比较运算符的子查询,查询赵四是哪个部门的员工
select * from department where did=(select did from employee where name ="赵四");

---

---

6.1 事务管理

> 6.1.1 事务的概念

所谓的事务就是针对数据库的一组操作，它可以由一条或多条SQL语句组成，同一个事务的操作具备同步的特点，即事务中的语句要么都执行，要么都不执行

开启事务的语法: start transaction

上述语句用于开启事务,事务开启之后就可以执行SQL语句,SQL语句执行成功后,需使用相对应语句提交事务,语法如下:

提交事务的语法: commit

在操作一个事务时,如果发现当前事务中的操作是不合理的,此时只要还没有提交事务,就可以通过回滚来取消当前事务

事务回滚语句: rollback

---

6.2 存储过程的创建

> 6.2.1 创建存储过程

存储过程就是一条或多条SQL语句的集合，当对数据库进行一系列复杂操作时，存储过程可以将这些复杂操作封装成一个代码块，以便重复使用，大大减少数据库开发人员的工作量

语法格式: 
CREATE PROCEDURE sp_name([proc_parameter])
[characteristics…]routine_body

CREATE PROCEDURE：为用来创建存储过程的关键字。
sp_name: 为存储过程的名称。
proc_parameter: 为指定存储过程的参数列表。
characteristics : 用于指定存储过程的特性。

演示存储过程的创建

1.首先创建一个student表:
mysql> create table student(
    -> id int(3) primary key auto_increment,
    -> name varchar(20) not null,
    -> grade float,
    -> gender char(2)
    -> );

2.插入数据
mysql> insert into student(name,grade,gender)
    -> values('tom',60,'男'),
    -> ('jack',70,'男'),
    -> ('rose',90,'女'),
    -> ('lucy',100,'女');

3.创建一个查看student表的存储过程
创建一个存储过程proc,每次调用这个存储过程的时候都会执行select语句查看表的内容
mysql> delimiter //		--------> 作用: 将MySQL的结束符设置为 //
mysql> create procedure proc()
    -> begin
    -> select * from student;
    -> end //

> 6.2.2 变量的使用

在MySQL中, 变量可以在子程序中声明, 用于保存数据处理过程中的值, 这些变量的作用范围在BEGIN…END程序中

语法格式: DECLARE var_name[,varname]…date_type[DEFAULT value];

例如: 定义一个名称为v1的变量，类型为INT类型，默认值为100,查询这个变量的值
mysql> delimiter //
mysql> create procedure proc2()
    -> begin
    -> declare v1 int default 100;
    -> select v1;
    -> end //
mysql> delimiter ;
mysql> call proc2();
+------+
| v1   |
+------+
|  100 |
+------+    

---

例如: 声明三个变量,使用set为变量赋值
mysql> delimiter //
mysql> create procedure proc2()
    -> begin
    -> declare v1,v2,v3 int;
    -> set v1=10,v2=20;
    -> set v3=v1+v2;
    -> select v1,v2,v3;
    -> end //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call proc2();
+------+------+------+
| v1   | v2   | v3   |
+------+------+------+
|   10 |   20 |   30 |
+------+------+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

除了使用set语句为变量赋值外,MySQL中还可以通过select...into为一个或多个变量赋值,
该语句可以把选定的列直接储存到对应的位置的变量
语法格式如下:

select col_name[...] into var_name [...] table_expr

上述语法格式中,col_name表示字段名称;var_name表示定义的变量名称,table_expr 表示查询条件表达式,包括表名称和where子句.

例如:

select grade, gender into s_grade, s_gender from student where name= 'rose' ; 

例:声明变量s_grade 和 s_gender,通过select ...into 语句 查询指定记录并为变量赋值

注意: 查询时如果不执行 select s_grade,s_gender; 则无返回结果

mysql> delimiter //
mysql> create procedure proc4()
    -> begin
    -> declare s_grade float;
    -> declare s_gender char(4);
    -> select grade,gender into s_grade,s_gender from student where name='rose';
    -> select s_grade,s_gender;
    -> end //
Query OK, 0 rows affected (0.00 sec)

mysql> call proc4()//
+---------+----------+
| s_grade | s_gender |
+---------+----------+ 
|      90 | 女       |
+---------+----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

---

> 6.2.3 定义条件和处理程序

> 1.定义条件

定义条件是指事先定义程序执行过程中遇到的问题。定义条件使用DECLARE语句，语法格式如下：

DECLARE condition_name CONDITION FOR [condition_type];
// condition_type的两种形式：
[condition_type]:
SQLSTATE[VALUE] sqlstate_value|mysql_error_code

condition_name：表示所定义的条件的名称；
condition_type：表示条件的类型；
sqlstate_value和mysql_error_code：表示MySQL的错误，sqlstate_value是长度为5的字符串类型错误代码，mysql_error_code为数值类型的错误代码。例如：ERROR1142（42000）中，sqlstate_value的值是42000，mysql_error_code的值是1142

例如

// 方法一: 使用sqlstate_value
declare commad_not_allowed condition for sqlstate '42000';
// 方法二: 使用 mysql_error_code
declare commad_not_allowed condition for 1064;

> 2. 定义处理程序

处理程序定义了在程序执行过程中遇到问题时应当采取的处理方式，并且保证存储过程在遇到警告或错误时能继续执行处理过程使用DECLARE语句定义，语法格式如下 :

DECLARE handler_type HANDLER FOR condition_value[,…] sp_statement
handler_type:
CONTINUE|EXIT|UNDO
condition_value:	//包括5个字符的字符串错误值 ;
|condition_name		//表示declare condition 定义的错误条件名称
|SQLWARNING			//匹配所有以01开头的sqlstate错误代码
|NOT FOUND			//匹配所有以02开头的sqlstate错误代码
|SQLEXCEPTION		//匹配所有没有被sqlwarning 或 not found捕获的sqlstate错误代码
|mysql_error_code	//匹配数值类型错误代码

handler_type：为错误处理方式，参数取值
有3个：CONTINUE、EXIT和UNDO。CONTINUE：表示遇到错误不处理，继续执行；
EXIT：遇到错误马上退出。
UNDO：表示遇到错误后撤回之前的操作，MySQL中暂时不支持这样的操作。sp_statement：参数为程序语句段，表示在遇到定义的错误时，需要执行的存储过程。
condition_value：表示错误类型

例如 :

// 方法一:捕获sqlstate_value
declare continue handler for sqlstate '42S02' set @info = 'no_such_table';
// 方法二:捕获mysql_error_code
declare continue handler for 1146 set @info = 'no_such_table'
// 方法三:先定义条件,然后调用
declare continue handler for no_such_table set @info='error';
// 方法四:使用sqlwarning
declare exit handler for sqlwarning set @info='error';
// 方法五:使用not found
declare exit handler for not found set @info='no_such_table';
// 方法六:使用sqlexception
declare exit handler for sqlexception set @info='error';

---

> 6.2.4 光标的使用

在编写存储过程时，查询语句可能会返回多条记录，如果数据量非常大，则需要使用光标来逐条读取查询结果集中的记录。光标是一种用于轻松处理多行数据的机制

> 1. 光标的声明

想要使用光标处理结果集中的数据, 需要先声明光标 。光标必须声明在声明变量, 条件之后

语法: DECLARE cursor_name CURSOR FOR select_statement
cursor_name表示光标的名称;
select_statement表示select语句的内容, 返回一个用于创建光标的结果集

例如: 声明一个名为cursor_student的光标
DECLARE cursor_student CURSOR FOR select s_name,s_gender FROM student;

> 2. 光标的使用

首先要打开光标

语法: open cursor_name;
select cursor_name into var_name[,var_name]...

cursor_name 表示参数的名称;
var_name 表示将光标中的select语句查询出来的信息存入该参数中, 需要注意的是, var_name必须在声明光标之前就定义好

例如: 使用名称为cursor_student的光标, 将查询出来的信息存入s_name和s_gender中
fetch cursor_student into s_name, s_gender;

> 3. 光标的关闭

使用完光标后要将光标关闭, 关闭光标的语法格式为

close cursor_name;

---

> 6.2.5 流程控制的使用

流程控制语句用于将多个sql语句划分或组合成符合业务逻辑的代码块

>1. if语句

是指如果条件满足

mysql> delimiter //
mysql> create procedure proc6()			//定义一个存储过程;
    -> begin							//开始;
    -> declare var int;					//定义一个变量 var;
    -> set var=10;						//设置var的值,可不设置;
    -> if var is null					//if语句开始;
    -> then select 'val is null';
    -> else select 'val is not null';
    -> end if;							//if语句结束;
    -> end //							//结束;
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call proc6();					//查看
+-----------------+
| val is not null |
+-----------------+
| val is not null |
+-----------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

> 2. case 语句

mysql> delimiter //
mysql> create procedure proc7()
    -> begin
    -> declare var int;
    -> set var=1;
    -> case var
    -> when 1 then select 'val is 1';
    -> when 2 then select 'val is 2';
    -> else select 'val is not 1 or 2';
    -> end case;
    -> end //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call proc7();
+----------+
| val is 1 |
+----------+
| val is 1 |
+----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

> 3.loop 语句

loop循环语句用来重复执行某些语句,并不会进行条件判断。

loop语句内的语句会一直重复执行直到跳出循环语句

基本语法如下 :

[loop_label:]LOOP		// loop_label 表示loop语句的标记名称,该参数可以省略
statement_list			// statement_list 表示需要循环执行的语句
END LOOP [loop_label]

> 4. leave语句

当不满足循环条件时, 需要用leave语句退出循环

语法: leave label

例如:

mysql> create procedure proc8()
    -> begin
    -> declare id int default 0;
    -> add_loop:LOOP
    -> set id=id+1;
    -> if id>=10 then leave add_loop;
    -> end if;
    -> end LOOP add_loop;
    -> select id;
    -> end //
Query OK, 0 rows affected (0.00 sec)

mysql> call proc8() //
+------+
| id   |
+------+
|   10 |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

> 5. iterate 语句

mysql> delimiter //
mysql> create procedure proc9()
    -> begin
    -> declare p1 int default 0;			//p1初始值为0;
    -> my_loop:loop
    -> set p1=p1+1;
    -> if p1<10 then iterate my_loop;		//如果p1的值小于10是,重复执行p+1
    -> elesif p1>20 then leave my_loop;		//当p1的值大于或等于10并且小于20时
    -> end if;
    -> select 'p1 is between 10 and 20';	//当p1的值大于或等于10并且小于20时,打印"p1 is bet..."
    -> end loop my_loop;
    -> end //

> 6. repeat 语句

作用: 创建一个带有条件判断的循环过程, 每次语句执行完毕后, 会对条件表达式进行判断, 如果表达式为真, 则循环结束; 否则重复执行循环中的语句

语法: [repeat_lable:] repeat		//repeat_label为repeat语句的标注名称,该参数可选
	statement_list
	utill expr_condition		 //reapeat语句内的语句或语句群被重复,直至expr_condition为真
	end repeat [repeat_lable]

例如

mysql> create procedure proc40()
    -> begin
    -> declare id int default 0;
    -> repeat		//
    -> set id=id+1;
    -> select id;	//遍历 1-10;
    -> until id>=10 --------->> 不带分号
    -> end repeat;	//
    -> end //
Query OK, 0 rows affected (0.00 sec)

> 7. where 语句

作用: 创建一个带条件判断的循环过程, 与repeat 不同的是, where在语句执行时, 先对指定的表达式进行判断, 如果为真, 则执行循环内的语句, 否则退出循环

语法: [where_lable:] where expr_condition DO
	Statement_list
	end where [where_lable]

mysql> create procedure proc42()
    -> begin
    -> declare i int default 0;
    -> while i<10 do
    -> set i=i+1;
    -> select i;		//遍历1-10;
    -> end while;
    -> end //
Query OK, 0 rows affected (0.01 sec)

---

6.3 存储过程的使用

> 6.3.1 调用存储过程

1.定义存储过程

mysql> create procedure test2(in s_gender varchar(50),out num int )
    -> begin
    -> select count(*) into num from student where gender=s_gender;
    -> end //
Query OK, 0 rows affected (0.00 sec)

2调用存储过程

mysql> delimiter ;
mysql> call test2("女",@num);
Query OK, 1 row affected (0.01 sec)

3.查看返回结果

mysql> select @num;
+------+
| @num |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

---

>6.3.2 查看存储过程

> 1. show status 语句中查看存储过程的状态

mysql> show procedure status like 'proc%' \G
*************************** 1. row ***************************
                  Db: chapter06
                Name: proc
                Type: PROCEDURE
             Definer: root@localhost
            Modified: 2020-11-25 08:32:11
             Created: 2020-11-25 08:32:11
       Security_type: DEFINER
             Comment:
character_set_client: gbk
collation_connection: gbk_chinese_ci
  Database Collation: utf8_general_ci
*************************** 2. row ***************************
                  Db: chapter06
                Name: proc1
                Type: PROCEDURE
             Definer: root@localhost
            Modified: 2020-11-25 09:15:20
             Created: 2020-11-25 09:15:20
       Security_type: DEFINER
             Comment:
character_set_client: gbk
collation_connection: gbk_chinese_ci
  Database Collation: utf8_general_ci

> 2. show create 语句中查看存储过程的状态&创建语句

mysql> show create procedure chapter06.proc1 \G
*************************** 1. row ***************************
           Procedure: proc1
            sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `proc1`()
begin
select * from student;
end
character_set_client: gbk
collation_connection: gbk_chinese_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

> 3.从information_schema.Routine 表中查看存储过程的信息

mysql> select * from information_schema.Routines
    -> where routine_name='proc1'
    -> and routine_type='procedure' \G
*************************** 1. row ***************************
           SPECIFIC_NAME: proc1
         ROUTINE_CATALOG: def
          ROUTINE_SCHEMA: chapter06
            ROUTINE_NAME: proc1
            ROUTINE_TYPE: PROCEDURE
               DATA_TYPE:
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
          DTD_IDENTIFIER: NULL
            ROUTINE_BODY: SQL
      ROUTINE_DEFINITION: begin
select * from student;
end
           EXTERNAL_NAME: NULL
       EXTERNAL_LANGUAGE: NULL
         PARAMETER_STYLE: SQL
        IS_DETERMINISTIC: NO
         SQL_DATA_ACCESS: CONTAINS SQL
                SQL_PATH: NULL
           SECURITY_TYPE: DEFINER
                 CREATED: 2020-11-25 09:15:20
            LAST_ALTERED: 2020-11-25 09:15:20
                SQL_MODE: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
         ROUTINE_COMMENT:
                 DEFINER: root@localhost
    CHARACTER_SET_CLIENT: gbk
    COLLATION_CONNECTION: gbk_chinese_ci
      DATABASE_COLLATION: utf8_general_ci
1 row in set (0.01 sec)

根据学号查询

mysql> delimiter //
mysql> create procedure proc43(in myid int)
    -> begin
    -> select name from student where id=myid;
    -> end //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call proc43(1) ;
+------+
| name |
+------+
| tom  |
+------+

根据输入的ID从学生表中查询该ID对应的姓名,并输出姓名

mysql> delimiter //
mysql> create procedure test1(in myid int,out myname varchar(36))
    -> begin
    -> select name into myname from student where id=myid;
    -> end //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call test1(3,@myname) ;	//调用存储过程
Query OK, 1 row affected (0.00 sec)

mysql> select @myname ;			//查看返回结果
+---------+
| @myname |
+---------+
| rose    |
+---------+
1 row in set (0.00 sec)

---

> 6.3.3 修改存储过程

在MySQL中可以使用alter语句修改存储过程

语法格式如下:

ALTER {PROCEDURE|FUNCTION} sp_name[characteristic…]

sp_name：表示存储过程或函数的名称。characteristic：表示要修改存储过程的哪个部分

characteristic 的取值如下:

1.contains sql 表示子程序中包括sql语句, 但不包含读或写数据的语句;
2.no sql 表示子程序中不包括sql语句;
3.reads sql data 表示表示子程序中包括读数据的语句;
4.modifies sql data	表示子程序中包括写数据的语句;
5.sql security{definer|invoker}	指明谁有权限来执行;
6.definer 表示只有定义者自己才能执行;
7.invoker	表示调用者可以执行;
8.comment 'string' 表示注释信息;

注意: 目前此版本Mysql还不提供对已存在的存储过程代码的修改, 如果一定要修改存储过程代码, 必须先将存储过程删除之后, 再重新编写代码, 或创建一个新的存储过程

---

> 6.3.4 删除存储过程

在MySQL中使用DROP语句修改存储过程，语法如下:

DROP{ procedure|function }[if exists] sp_name

if exists 表示如果程序不存在, 它可以避免发生错误, 产生一个警告

---

---

7.1 视图概述

​ 视图是从基本表中导出来的表，是一种虚拟存在的表, 并且表的结构和数据都依赖于基本表

通过视图不仅可以看到放在基本表中的数据, 并且还可以像操作基本表一样, 对视图中存放的数据进行查询

删除,修改 .与直接操作表相比视图具有以下优点:

1.简化查询语句
日常开发中我们可以将经常使用的查询定义为视图，从而使用户避免大量重复的操作。
2安全性
通过视图用户只能查询和修改他们所能见到的数据，数据库中的其他数据则既看不到也取不到。
3逻辑数据独立性
视图可以帮助用户屏蔽真实表结构变化带来的影响。

---

7.2 视图管理

> 7.2.1 创建视图的语法格式:

create [or replace] [algorithm={undefined | merge | temptable]
view view_name [(column_list)]
as select_statement
[where [cascaded|local] check option]

CREATE：表示创建视图的关键字。
OR REPLACE：表示该语句能够替换已有视图。
ALGORITHM：可选，表示视图选择的算法。
UNDEFINED：表示MySQL将自动选择所要使用的算法。
MERGE：使得视图定义的某一部分取代语句的对应部分。
TEMPTABLE：表示将视图的结果存入临时表，然后使用临时表执行语句。
view_name：表示要创建的视图名称。
column_list：可选，表示属性清单。
AS：表示指定视图要执行的操作。
SELECT_statement：表示从某个表或视图中查出满足条件的记录，并导入视图中。
WITH CHECK OPTION：可选，表示创建视图时要保证在该视图的权限范围之内。
CASCADED：需要满足跟该视图有关的所有相关视图和表的条件，该参数为默认值。
LOCAL：可选。表示创建视图时，只要满足该视图本身定义的条件即可。

---

>7.2.2 在单标上创建视图

在student表上创建view_stu视图和view_stu2视图(自定义字段名)

1.创建数据库,表,插入数据

mysql> create database chapter07;
Query OK, 1 row affected (0.04 sec)

mysql> use chapter07
Database changed
mysql> create table student(
    -> s_id int(3),
    -> name varchar(20),
    -> math float,
    -> chinese float
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> insert into student(s_id,name,math,chinese) values
    -> (1,'Tom',80,78),
    -> (2,'Jack',70,80),
    -> (3,'Lucy',97,95);
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from student;
+------+------+------+---------+
| s_id | name | math | chinese |
+------+------+------+---------+
|    1 | Tom  |   80 |      78 |
|    2 | Jack |   70 |      80 |
|    3 | Lucy |   97 |      95 |
+------+------+------+---------+
3 rows in set (0.00 sec)

2.创建视图并查询

mysql> create view view_stu as select math,chinese,math+chinese from student;
mysql> select * from view_stu;
+------+---------+--------------+
| math | chinese | math+chinese |
+------+---------+--------------+
|   80 |      78 |          158 |
|   70 |      80 |          150 |
|   97 |      95 |          192 |
+------+---------+--------------+
3 rows in set (0.00 sec)

mysql> create view view_stu2(math,chinese,sum) as select math,chinese,math+chinese from student;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from view_stu2; 
+------+---------+------+
| math | chinese | sum  |
+------+---------+------+
|   80 |      78 |  158 |
|   70 |      80 |  150 |
|   97 |      95 |  192 |
+------+---------+------+
3 rows in set (0.00 sec)

---

> 7.2.3 在多表上创建视图

在student表和stu_info表中创建stu_class视图, 查询s_id号,姓名和班级

1.创建数据库,表,插入数据

mysql> create table stu_info(
    -> s_id int(3),
    -> class varchar(50),
    -> addr varchar(100)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into stu_info(s_id,class,addr) values
    -> (1,'erban','anhui'),
    -> (2,'sanban','chongqing'),
    -> (3,'yiban','shandong');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from stu_info;
+------+--------+-----------+
| s_id | class  | addr      |
+------+--------+-----------+
|    1 | erban  | anhui     |
|    2 | sanban | chongqing |
|    3 | yiban  | shandong  |
+------+--------+-----------+
3 rows in set (0.00 sec)

2.创建视图并查询

mysql> create view stu_class(id,name,class) as select student.s_id,student.name,stu_info.class from student,stu_info where student.s_id=stu_info.s_id;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from stu_class;
+------+------+--------+
| id   | name | class  |
+------+------+--------+
|    1 | Tom  | erban  |
|    2 | Jack | sanban |
|    3 | Lucy | yiban  |
+------+------+--------+
3 rows in set (0.00 sec)

---

> 7.2.4 查看视图

> 1. 使用describe 语句查看视图

作用: 使用DESCRIBE语句可以查看视图的字段信息

语法: DESCRIBE 视图名; 或 DESC 视图名;

例如: desc[describe] stu_class;

> 2. 使用 show table status

作用: 可以查看视图的基本信息

语法: SHOW TABLE STATUS LIKE '视图名'; ----> 注意like 引号

例如: show table status like 'stu_class' \G;

> 3. 使用 show create view

作用: 不仅可以查看创建视图时的定义语句，还可以查看视图的字符编码

语法: SHOW CREATE VIEW 视图名;

例如: show create view stu_class \G;

---

> 7.2.5 修改视图

> 1. 使用 create or replace view 语句修改视图;

语法 : create or replace view 视图名 as select 表中字段 from 表名

create or replace view view_stu as select * from student;

> 2. 使用 alter 语句修改视图;

语法 : alter view 视图名 as select 表中字段 from 表名

alter view view_stu as select chinese from student;

---

> 7.2.6 更新视图

1. 使用update 语句更新视图

update view_stu set chinese=100;

2. 使用insert 语句更新视图

insert into student values(4,'Lily',100,100);

3. 使用delete语句更新视图

delete from view_stu2 where math=10;

---

7.2.7 删除视图

视图不在需要时,可以将其删除,删除视图只会删除视图的定义,不会删除数据.

删除一个或多个视图可以使用dorp view语句, 删除视图的基本语法格式如下所示:

drop view [if exists]
view_name [,view_name1] ...
[restrict|cascade]

view_name 是指要删除的视图的名称,视图名称可以添加多个,各个名称用逗号隔开,删除视图必须有root权限

例如: 删除view_stu2视图
drop view if exists view_stu2;

---

---

8.1 数据备份与还原

8.1.1 数据的备份

通过MySQL的mysqldump命令实现数据的备份

1. 备份单个数据库

mysqldump 命令备份数据库的语法格式如下 :

mysqldump -u username -p password dbname [tbname] [tbname2...] > filename.sql

上述语法格式中, -u后面的参数username 表示用户名, -p 后面的参数 password表示登录密码, dbname 表示需要备份的数据库的名称, tbname表示数据库中的表名, 可以指定一个表或多个表, 多个表之间用空格隔开, 如果不指定则备份整个数据库, filename.sql 表示备份文件的名称, 文件名前可以加上绝对路径

注意: 在使用mysqldump命令备份数据库时, 直接在dos命令行窗口中执行该命令即可, 不需要登录到mysql数据库

例如:

例如: 创建一个名称为chapter08的数据库, 并在数据库中创建表student, 插入相应数据
mysql> create database chapter08
mysql> use chapter08
Database changed
mysql> create table student(
    -> id int primary key auto_increment,
    -> name varchar(20),
    -> age int
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into student(name,age) values
    -> ('Tom',20),
    -> ('Jack',16),
    -> ('Lucy',18);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | Tom  |   20 |
|  2 | Jack |   16 |
|  3 | Lucy |   18 |
+----+------+------+
3 rows in set (0.00 sec)

首先在C盘创建一个名为backupdb的 文件夹用来存备份好的文件夹, 然后重新开启一个dos命令行窗口( 不需要登录到MySQL数据库), 使用mysqldump命令登录备份chapter08

语法: 
C:\Users\ASUS>mysqldump -u root -p chapter08 > C:/backupdb/chapter08.sql
Enter password: ****

查看备份:

-- MySQL dump 10.13  Distrib 5.5.28, for Win32 (x86)
--
-- Host: localhost    Database: chapter08
-- ------------------------------------------------------
-- Server version	5.5.28

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `student`
--

DROP TABLE IF EXISTS `student`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `student` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(20) DEFAULT NULL,
  `age` INT(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `student`
--

LOCK TABLES `student` WRITE;
/*!40000 ALTER TABLE `student` DISABLE KEYS */;
INSERT INTO `student` VALUES (1,'Tom',20),(2,'Jack',16),(3,'Lucy',18);
/*!40000 ALTER TABLE `student` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2020-12-16  8:30:50

2. 备份多个数据库

mysqldump –u username –p password -–database dbname1 [dbname2 dbname3…]
>filename.sql

3. 备份所以数据库

mysqldump –uusername –ppassword -–all-databases>filename.sql

8.1.2 数据的还原

语法: mysql –uusername –ppassword [dbname] <filename.sql

1. 删除数据库

mysql> drop database chapter08;

2. 创建数据库

mysql> create database chapter08;
Query OK, 1 row affected (0.01 sec)

3. 还原数据库

C:\Users\ASUS>mysql -u root -p chapter08 < C:/backupdb/chapter08-1.sql
Enter password: ****

4. 查看数据

mysql> use chapter08
Database changed
mysql> select * from student;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | Tom  |   20 |
|  2 | Jack |   16 |
|  3 | Lucy |   18 |
+----+------+------+
3 rows in set (0.00 sec)

---

8.2 用户管理

8.2.1 user表

在安装数据库的时候会自动安装一个名为mysql的数据库, 该数据库中的表都是权限表, 如user, db, host ,table_priv

其中user表是最重要的一个权限表, 它记录了允许链接到服务器的账号信息以及一些全局级的权限信息, 通过操作表就可以对这些信息进行修改

mysql> use mysql
mysql> desc user;
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field  字段名 			| Type  数据类型					| Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host                   | char(60)                          | NO   | PRI |         |       |
| User                   | char(16)                          | NO   | PRI |         |       |
| Password               | char(41)                          | NO   |     |         |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Update_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Delete_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Create_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Drop_priv              | enum('N','Y')                     | NO   |     | N       |       |
| Reload_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Shutdown_priv          | enum('N','Y')                     | NO   |     | N       |       |
| Process_priv           | enum('N','Y')                     | NO   |     | N       |       |
| File_priv              | enum('N','Y')                     | NO   |     | N       |       |
| Grant_priv             | enum('N','Y')                     | NO   |     | N       |       |
| References_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Index_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Alter_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Show_db_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Super_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N       |       |
| Lock_tables_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Execute_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Repl_slave_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Repl_client_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Create_view_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Show_view_priv         | enum('N','Y')                     | NO   |     | N       |       |
| Create_routine_priv    | enum('N','Y')                     | NO   |     | N       |       |
| Alter_routine_priv     | enum('N','Y')                     | NO   |     | N       |       |
| Create_user_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Event_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Trigger_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Create_tablespace_priv | enum('N','Y')                     | NO   |     | N       |       |
| ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |         |       |
| ssl_cipher             | blob                              | NO   |     | NULL    |       |
| x509_issuer            | blob                              | NO   |     | NULL    |       |
| x509_subject           | blob                              | NO   |     | NULL    |       |
| max_questions          | int(11) unsigned                  | NO   |     | 0       |       |
| max_updates            | int(11) unsigned                  | NO   |     | 0       |       |
| max_connections        | int(11) unsigned                  | NO   |     | 0       |       |
| max_user_connections   | int(11) unsigned                  | NO   |     | 0       |       |
| plugin                 | char(64)                          | YES  |     |         |       |
| authentication_string  | text                              | YES  |     | NULL    |       |
+------------------------+-----------------------------------+------+-----+---------+-------

1. 用户列

用户列包括Host、User、Password,分别代表主机名、用户名和密码

其中Host和User列为user表的联合主键

当修改密码时，只需要修改,user表中Password字段的值即可

2. 权限列

user表的权限列包括Select_priv、Insert_priv、Update_priv等以priv结尾的字段，这些字段决定了用户的权限，其中包括查询权限、修改权限、关闭服务等权限。

3.安全列

user表的安全列用于管理用户的安全信息，其中包括6个字段，具体如下：
ssl_type和ssl_cipher：用于加密
x509_issuer和x509_subject标准：可以用来标识用户
plugin和authentication_string：用于存储与授权相关的插件

4. 资源控制列

资源控制列是用于限制用户使用的资源，包括4个字段，具体如下：
max_questions：每小时允许用户执行查询操作的次数
max_updates：每小时允许用户执行更新操作的次数
max_connections：每小时允许用户建立连接的次数
max_user_connections：允许单个用户同时建立连接的次数

8.2.2 创建普通用户

1. 使用 grant 语句创建用户

GRANT privileges ON database.table
      TO 'username'@'hostname' [IDENTIFIED BY [PASSWORD]'password']
      [,'username'@'hostname [IDENTIFIED BY [PASSWORD]'password']] …

privileges 参数表示该用户具有的权限信息, database.table 表示新用户的权限范围表, 可以在指定的数据库, 表上使用自己的权限, username参数是新用户的名称, hostname参数是主机名, password参数是新用户的密码

例如: 使用grant 语句创建一个新用户, 用户名是user1, 密码是 123, 并授予该用户对chapter08.student 表具有查询权限

grant select on chapter08.student to 'user1'@'localhost' identified by '123';
mysql> select host,user,password from user;
+-----------+-------+-------------------------------------------+
| host      | user  | password                                  |
+-----------+-------+-------------------------------------------+
| localhost | root  | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| localhost | user1 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| %         | root  | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
+-----------+-------+-------------------------------------------+
3 rows in set (0.00 sec)

2. 使用 create user 语句创建用户

CREATE USER 'username'@'hostname'[IDENTIFIED BY [PASSWORD]'password']
       [,'username'@'hostname'[IDENTIFIED BY [PASSWORD]'password']]…

username参数是新用户的名称, hostname参数是主机名,identified by关键字用于设置用户的密码, password 表示用户的密码, [PASSWORD] 关键字表示使用哈希值设置的密码, 该参数是可选的, 如果密码是一个普通的字符串, 就不需要使用PASSWORD 关键字

例如: 使用create user语句创建一个新用户, 用户名为user2, 密码是123, create user语法如下:
create user 'user2'@'localhost' identified by '123';

3. 使用 insert 语句创建用户

INSERT INTO mysql.user(Host,User,Password,ssl_cipher、x509_issuer、x509_subject)
VALUES('hostname','username',PASSWORD('password'),'','','');

上述语法格式中, mysql.user表示操作的表, Host,User,Password,ssl_cipher、x509_issuer、x509_subject 为相应字段

PASSWORD( ) 是一个加密函数, 用于给密码加密

注意: 使用insert添加用户时, 通常只需要添加host, user和password这三个字段即可, 其他字段取默认值,但由于

Host,User,Password,ssl_cipher、x509_issuer、x509_subject 字段是没有默认值的, 因此insert语句创建用户时, 还需要为这几个字段设置初始值

例如: 使用insert 语句直接在mysql.user表中创建一个新用户, 用户名为user3, 密码为123 ,insert语句如下: 
insert into mysql.user(Host,User,Password,scl_cipher,x509_issuer,x509_subject)
values('localhost','user3',password('123'),'',''); 

注意: insert语句没有刷新权限表的功能, 因此,user3用户暂时不能使用,为了让当前用户生效, 还需要手动刷新当前的权限表或重新启动mysql服务, 刷新权限表的语句如下:

flush privileges;

8.2.3 删除普通用户

1. 使用drop user 语句删除用户

drop user语句与drop database 语句有些相似, 如果删除某个用户, 只需在 drop user 后面指定要删除的用户信息即可

语法:

drop user 'username'@'hostname'[,'username'@'hostname'];

username 表示要删除的用户, hostname表示主机名, drop user语句可以同时删除一个或多个用户, 多个用户之间可以使用逗号隔开

注意: 使用drop user语句来删除用户时, 必须拥有drop user 权限

使用drop user语句删除用户 user1

drop user 'user'@'localhost';

2. 使用 delete 语句删除用户

delete语句不仅可以删除普通表中的数据, 还可以删除 user表中的数据, 使用该语句删除 user表中的数据时, 只需要指定表名为mysql.user, 以及要删除的用户信息即可, 同样的, 在使用delete语句时必须拥有对mysql.user表的delete权限

语法格式如下: delete from mysql.user where host='hostname' and user='username';

上述语法格式中, mysql.user 参数指定要操作的表, where 指定条件语句, host和user 都是mysql.user 表的字段,

这两个字段可以确定唯一的一条记录

例如: 使用delete语句删除用户 user2
delete from mysql.user where host='localhost' and user='user2';

注意: 由于直接对user表进行操作, 执行完命令后需要使用'flush privileges' 语句重新加载用户权限

8.2.4 修改用户密码

1. 修改 root用户密码

一. 通过mysqladmin命令修改root用户的密码

mysqladmin命令通常用于执行一些管理性工作

语法格式如下:

mysqladmin -u username [-h hostname] -p password new_password;

username为要修改的用户名, 这里指的是root用户, 参数 -h 用于指定对应的主机, 可以省略不写, 默认为 localhost

-p 后面的password为关键字, 而不是修改后的密码, new_password 为新设置的密码

注意: 在使用mysqladmin命令修改root用户密码时, 需要在 C: \document and setting\administrator>目录下进行修改

例如: 在命令行窗口中使用mysqladmin命令, 将root用户的密码修改为mypwd1,sql语句如下: 
mysqladmin -u root -p password mypwd1

二. 通过update语句修改root用户的密码

由于所以的用户信息都存放在mysql.user表中, 因此,只有root用户登录到MySQL服务器, 使用update语句就可以直接修改自己的密码

语法格式如下

update mysql.user set password=password('mypwd2') where user='root' and host='localhost';

上述语法执行成功后, 还需要使用 flush privileges 重新加载权限表, 然后就可以使用新密码登录 mysql数据库

结果如下 :

三. 使用set 语句修改root用户的密码

root用户登录到mysql数据库后, 还可以通过set语句修改root用户的密码

set语句修改密码的语法格式如下:

set password=password('new_password');

注意: 由于set语句没有对密码加密的功能, 因此, 新密码必须使用password() 函数加密, 并且新密码需要使用引号括起

例如: root用户登录到mysql数据库, 使用set 语句将root用户的密码修改为mypwd3, set语句如下:
set password=password('mypwd3');

2. root 用户修改普通用户的密码

1.使用grant语句修改普通用户密码

grant语句的作用比较多, 不仅可以创建用户授权, 还可以修改用户的密码, 通常情况下, 为了不影响当前账户的权限, 可以使用grant usage语句修改指定的账户的密码

grant语句修改密码语法格式如下:

grant usage on * . * to 'username'@'localhost' identified by [password] 'new_password';

2.使用update语句修改普通用户的密码

root用户具有操作数据库的所有权限, 因此, 它不仅可以使用update语句修改自己的密码, 还可以使用update语句修改普通用户的密码

update的语法格式为与root修改用户密码的语法格式相同

update mysql.user set password=password('new_password')
where user='username' and host='hostname';

注意: 通过上述语句修改完普通用户的密码后, 还需要使用flush privileges语句重新加载权限表

3.使用set语句修改普通用户的密码

set 不仅可以修改root用户的密码, 而且还可以修改普通用户密码, 在修改普通用户密码时, 还需要增加一个for子句, 指定要修改哪一个用户即可

set语句修改密码的语法格式如下

set password for 'username'@'hostname'=password('new_password');

3. 普通用户修改密码

普通用户也可以修改自己的密码, 这样普通用户就不需要每次修改密码时都通知管理员, 普通用户登录到MySQL服务器后, 可以通过set语句来设置自己的密码

set语句的基本格式如下:

set password=password('new_password');

set语句修改普通用户密码时, 和修改root用户是一样的, 都需要使用password() 函数进行加密

---

8.3 权限管理

8.3.1 MySQL的权限

Mysql中的权限信息被储存在mysql 数据库的 user,db,host,table_priv,column_priv和procs_priv表中, 当MySQL启动时会自动加载这些权限信息, 并将这些权限信息读取到内存中

权限解释:

1. create和drop的权限, 可以创建数据库,表,索引,或者删除已有的数据库, 表, 索引
2. insert, delete,update,select 权限,可以对数据库中的表进行增删改查操作
3. index权限,可以创建或删除索引, 适用于所以的表
4. alter权限.可以用于修改表的结构或重命名表
5. grant权限, 允许为其他用户授权, 可用于数据库和表
6. file权限, 被赋予该权限的用户能读写MySQL服务器上的任何文件

8.3.2 授予权限

我们之所以可以对数据进行增删改查的操作，是因为数据库中的用户拥有不同的权限，合理的授权可以保证数据库的安全

MySQL中的GRANT语句可以为用户授权，语法格式如下:

GRANT privileges [(columns)][,privileges[(columns)]] ON database.table
TO 'username'@'hostname' [IDENTIFIED BY [PASSWORD]'password']
[,'username'@'hostname' [IDENTIFIED BY [PASSWORD]'password']] …
[WITH with_option [with_option]…]

privileges：表示权限类型。
columns：参数表示权限作用于某一列，该参数可以省略不写，此时权限作用于整个表，username：表示用户名。
hostname表示主机名。
IDENTIFIED BY：参数为用户设置密码。
PASSWORD参数为关键字.
password为用户的新密码。

WITH关键字后面可以带有多个参数with_option，这个参数有五个取值

具体如下:

GRANT OPTION：将自己的权限授予其他用户MAX_QUERIES_PER_HOUR count：设置每小时最多可以执行多少次（count）查询。
MAX_UPDATES_PER_HOUR count：设置每小时最多可以执行多少次更新。
MAX_CONNECTIONS_PER_HOUR count：设置每小时最大的连接数量。
MAX_USER_CONNECTIONS：设置每个用户最多可以同时建立连接的数量。

8.3.3 查看权限

虽然使用SELECT语句可以查询user表中的权限信息，但是该语句需要指定用户和查询的权限，比较麻烦，MySQL还提供了一个查看权限的简单语句-SHOW GRANTS语句

SHOW GRANTS的语法格式如下:

SHOW GRANTS FOR 'username'@'hostname';

语法格式相对简单, 只需要指定查询的用户名和主机名即可

例1: 使用show grants语句查询root用户的权限信息

show grants for 'root'@'hostname';

例2: 使用show grants语句查询user4 用户的权限信息

show grants for 'user4'@'hostname';

8.3.4 收回权限

为了保证数据库的安全性，需要将用户不必要的权限收回，收回用户权限使用REVOKE语句

1. 收回用户指定权限, 语法格式如下所示 :

REVOKE privileges [columns][,privileges[(columns)]] ON database.table 
FROM 'username'@'hostname'[,'username'@'hostname'] …

revoke语法格式中的参数与grant语句中的参数意思相同, privileges参数表示收回的权限,columns表示权限作用于哪列上, 如果不指定该参数表示作用于整个表

使用revoke语句收回user4用户的insert权限, revoke语句如下:

revoke insert on * . * from 'user'@'localhost';

如果权限比较多可以使用收回所以权限的功能

2. 收回用户全部权限, 语法格式如下所示 :

REVOKE ALL PRIVILEGES,GRANT OPTION 
FROM  'username'@'hostname' [,'username'@'hostname'] …

使用revoke语句收回user4用户的insert权限, revoke语句如下:

revoke all privileges,grant option from 'user4'@'localhost';