【数据库】Mysql数据库管理

---

引言

  MySQL 是最流行的关系型数据库管理系统，在 WEB 应用方面 MySQL 是最好的 应用软件之一。在本篇中，会让大家快速掌握 MySQL 的基本操作，并轻松使用 MySQL 数据库。

---

一、Mysql数据库管理

1. 库和表

  行（记录）：用来描述一个对象的信息。

  列（字段）：用来描述对象的一个属性。

2. 常用的数据类型

数据类型	取值范围
int（整型）	无符号 [0,232-1] 有符号 [-231,231-1]
float（单精度浮点）	4字节32位
double（双精度浮点 ）	8字节64位
char（固定长度的字符类型）	用户指定范围
varchar（可变长度的字符类型）	用户指定范围
text（文本）	文本字符串
image（图片）	根据图片格式类型而定
decimal（5，2）	5个有效长度数字，小数点后面有2位

3. char和varchar区别

  CHAR和VARCHAR类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换

  下表显示了将各种字符串值保存到CHAR(4)和VARCHAR(4)列后的结果，说明了CHAR和VARCHAR之间的差别：

值	CHAR(4)	存储需求	VARCHAR(4)	存储需求
‘’	’ ’	4个字节	‘’	1个字节
‘ab’	'ab ’	4个字节	‘ab’	3个字节
‘abcd’	'ab ’	4个字节	‘abcd’	5个字节
‘abcdefgh’	‘abcd’	4个字节	‘abcd’	5个字节

字节大小

• char无论是否有值，都会占用固定长度的字节大小，保存在磁盘上都是4字节。
• varchar在保存字符时，默认会加一个隐藏的结束符，因此结束符会多算一个字节。

优劣比较

• varchar比char节省磁盘空间。
• 但varchar类型的数据读写速度比char慢，因为char是连续的磁盘空间，·而varchar在多次增删改查中会产生一些磁盘空间碎片。

二、SQL语句

SQL语句用于维护管理数据库，包括数据查询、数据更新、访问控制、对象管理等功能。

1. SQL语句分类

语句	含义
DDL	数据定义语言，用于创建数据库对象，如库、表、索引等
DML	数据操纵语言，用于对表中的数据进行管理
DQL	数据查询语言，用于从数据表中查找符合条件的数据记录
DCL	数据控制语言，用于设置或者更改数据库用户或角色权限

2. 查看数据库结构

show databases;  			 #查看当前服务器中的数据库

提示：不区分大小写，分号表示结束

use 数据库名;         	 	   #切换数据库

show tables;         		 #查看数据库中包含的表

desc(desribe) 表名;   		#查看表的结构
show create table 表名;

3. DDL数据定义语言

用于创建数据库对象，如库、表、索引等

create
drop
alter

3.1 创建新的数据库

CREATE DATABASE 数据库名;

3.2 创建新的表

CREATE TABLE 表名 (字段1 数据类型,字段2 数据类型[,...][,PRIMARY KEY (主键名)]);

注：主键一般选择能代表唯一性的字段不允许取空值（NULL），一个表只能有一个主键。

例：create database mybl;
use mybl;
create table MYB1 (id int NOT NULL,name char(10) NOT NULL, score decimal(5,2),PRIMARY KEY (id));

3.3 删除指定数据表

use 数据库名;
DROP TABLE 表名;

DROP TABLE [数据库名.]表名;

3.4 删除指定数据库

DROP DATABASE 数据库名;

4. DML数据操控语言

数据操纵语言，用于对表中的数据进行管理，用来插入、删除和修改数据库中的数据。

insert
update
delete

4.1 向数据表中插入新的内容

INSERT INTO 表名 VALUES(字段1的值,字段2的值, ...);			  		#按顺序添加数值
INSERT INTO 表名(字段1,字段2[,...]) VALUES(字段1的值,字段2的值, ...);

例：
select * from MYB1;
insert into MYB1 (id,name,sorce) values(1,'小王',99.99);
select * from MYB1;

4.2 查询数据表中的内容

SELECT 字段名1,字段名2[,...] FROM 表名 [WHERE 条件表达式];

例：
select * from CLASS;
select name,score from CLASS where id=1;
select name,score from CLASS where id=2;

4.3 修改、更新表中的数据

UPDATE 表名 SET 字段名1=字段值1[,字段名2=字段值2] [WHERE 条件表达式];		#若使用where会修改表中所有内容

例：
update mayinbin set id=4 where name='小红';
select * from CLASS;

4.4 在数据表中删除指定的数据记录

DELETE FROM 表名 [WHERE 条件表达式];

例：
select * from MYB1;
delete from MYB1 where id=4;
select * from MYB1;

5. DQL数据查询语言

select * from 表名 [WHERE 条件表达式];
select 字段1,字段2,... from 表名 [where 条件表达式];

select name from 表名\G           #以列表方式竖向显示
select * from 表名 limit 2;       #只显示头2行
select * from 表名 limit 2,3;     #显示第2行后的前3行

6. 修改表结构

6.1 修改表名

ALTER TABLE 旧表名 RENAME 新表名;

例：
alter table MYB1 rename school;
select * from school;

6.2 扩展表结构（增加字段）

ALTER TABLE 表名 ADD address varchar(50) default '地址不详';
#default ‘地址不详’：表示此字段设置默认值 地址不详；可与 NOT NULL 配合使用

例：alter table school add address varchar(50) default '地址不详';

6.3 修改字段（列）名，添加唯一键

ALTER TABLE 表名 CHANGE 旧列名 新列名 数据类型 [unique key];

例：
alter table school change name student varchar(20) unique key;
desc school;

每个表中只能由一个主键 ，但是许多内容都需要唯一性，这就是唯一键的作用。

6.4 删除字段

ALTER TABLE 表名 DROP 字段名;
 
例：
select * from school;
alter table school drop sorce;

7. Mysql 的六大约束

参数	含义
primary key	表示此字段的主键约束，此字段数据不能重复，不能为null。一个表中只能由要给主键。
unique key	表示此字段唯一键约束，此字段数据不可以重复。一张表中只能有一个主键，但是一张表中可以有多个唯一键。
not nul	表示此字段为非空约束，此字段的值不允许为NULL。
default	默认值约束 字段的值如果没有设置则使用默认值自动填充。
auto_increment	表示此字段为自增长字段（自增约束），即每条记录自动递增1，默认从1开始递增；自增长字段数据不可以重复；自增长字段必须是主键；如添加的记录数据没有指定此字段的值且添加失败也会自动递增一次。
foregin key	表示此字段为外键约束，保证相关的表的数据的完整性和一致性。

use mybl;
create table if not exists info (				  #表示检测要创建的表是否已存在，如果不存在就继续创建。
id int(4) zerofill primary key auto_increment,    #指定主键的第二种方式。其中int(4) zerofill表示若数值不满4位数，则前面用"0"填充，例如0001。
name varchar(10) not null default '匿名',
cardid int(18) not null unique key,
hobby varchar(50));

注意：int(N) 需要和zerofill primary配合使用，否则N的值没有意义。

8. DCL数据控制语言

8.1 新建用户

create user '用户名'@'来源地址' identified by '密码';

'用户名'：指定将创建的用户名
'来源地址'：指定新创建的用户可在哪些主机上登录，可使用IP地址、网段、主机名的形式，本地用户可用localhost，允许任意主机登录可用通配符%
'密码'：若使用明文密码，直接输入'密码'，插入到数据库时由Mysql自动加密；
       若使用加密密码，需要先使用SELECT PASSWORD('密码'); 获取密文，再在语句中添加 PASSWORD '密文';
        若省略“IDENTIFIED BY”部分，则用户的密码将为空（不建议使用）

例如
create user 'root'@'localhost' identified by '123456';

例如
create user 'root'@'%' identified by  'abc123';

8.2 查看用户信息

#创建后的用户保存在 mysql 数据库的 user 表里
USE mysql;
SELECT User,authentication_string,Host from user;

#查看当前登录用户
select user();

8.3 重命名用户

RENAME USER 'user1'@'localhost' TO 'lisi'@'localhost';

8.4 删除用户

DROP USER 'lisi'@'localhost';

8.5 修改当前登录用户密码

SET PASSWORD = PASSWORD('abc123');

8.6 修改其他用户密码

SET PASSWORD FOR 'lisi'@'localhost' = PASSWORD('123456');

8.7找回root用户密码

修改mysql配置文件 /etc/my.conf ,在 [mysqld] 下面添加 skip-grant-tables
重启mysqld服务，使用 mysql 命令直接登录数据库
执行 update mysql.user set authentication_string = password('密码') where user='root' and host='localhost';
还原配置文件，重启mysqld服务，使用 mysql -u 用户 -p[密码] -h 地址 -P 端口  来验证登录

注意：最后再把 /etc/my.cnf 配置文件里的 skip-grant-tables 删除，并重启 mysql 服务。

8.8 权限管理

授予权限

  GRANT语句：专门用来设置数据库用户的访问权限。当指定的用户名不存在时，GRANT语句将会创建新的用户；当指定的用户名存在时， GRANT 语句用于修改用户信息。

GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'来源地址' [IDENTIFIED BY '密码'];

#权限列表：用于列出授权使用的各种数据库操作，以逗号进行分隔，如“select,insert,update”。使用“all”表示所有权限，可授权执行任何操作。
#数据库名.表名：用于指定授权操作的数据库和表的名称，其中可以使用通配符“*”。例如，使用“kgc.*”表示授权操作的对象为 kgc数据库中的所有表。
#'用户名@来源地址'：用于指定用户名称和允许访问的客户机地址，即谁能连接、能从哪里连接。来源地址可以是域名、IP地址，还可以使用“%”通配符，表示某个区域或网段内的所有地址，如“%.abc.com”、“192.168.145.%”等。
#IDENTIFIED BY：用于设置用户连接数据库时所使用的密码字符串。在新建用户时，若省略“IDENTIFIED BY”部分，则用户的密码将为空。

例如：
#允许用户 zhangsan 在本地查询 mybl 数据库中 所有表的数据记录，但禁止查询其他数据库中的表的记录。
grant select on mybl.* to 'zhangsan'@'localhost' identified by'123456';

#允许用户 lisi 在所有终端远程连接 mysql ，并拥有所有权限。
grant all [PRIVILEGES] on *.* to 'lisi'@'%' identified by '123456';

flush privileges;
quit

mysql -u zhangsan -p123456
use mybl;
show tables;
select * from school;

查看权限

SHOW GRANTS FOR 用户名@来源地址;

例：
SHOW GRANTS FOR 'lisi'@'localhost';

撤销权限

REVOKE 权限列表 ON 数据库名.表名 FROM 用户名@来源地址;

例：
revoke all on *.* from 'lisi'@'localhost';
SHOW GRANTS FOR 'lisi'@'localhost';
#USAGE权限只能用于数据库登陆，不能执行任何操作；USAGE权限不能被回收，即 REVOKE 不能删除用户。

flush privileges;

三、数据表的高级操作

1. 克隆表

create table 新表名 like 旧表名;					#通过 LIKE 方法，复制 KY08 表结构生成 test01 表	
insert into 新表名 (select * from  旧表名);	

#此方法能保证 新表的表结构、表数据 跟旧表都是一致的

create table 新表名 (select * from  旧表名);

2. 清空表，删除表内的所有数据

方法1
delete from 表名;

方法2
truncate table 表名;

3. 新建临时表

  临时表创建成功之后，使用SHOW TABLES命令是看不到创建的临时表的，临时表会在连接退出后被销毁。 如果在退出连接之前，也可以可执行增删改查等操作，比如使用 DROP TABLE 语句手动直接删除临时表。

CREATE TEMPORARY TABLE 表名 (字段1 数据类型,字段2 数据类型[,...][,PRIMARY KEY (主键名)]);

例：
create temporary table test03 (
id int(4) zerofill primary key auto_increment,
name varchar(10) not null,
cardid int(18) not null unique key,
hobby varchar(50));

insert into test03 values(1,'zhangsan',123456,'running');

select * from test03;
show tables;

quit

mysql -u root -p123456
use mybl;
show tables;

4. 外键约束

4.1 概念

  外键的定义：如果同一个属性字段X在表一中是主键，而在表二中不是主键，则字段X称为表二的外键。

4.2 功能

  创建外键约束，保证数据的完整性。

4.3 主键表和外键表的区别

以公共关键字作主键的表为主键表（父表、主表）

以公共关键字作外键的表为外键表（从表、外表）

注意：与外键关联的主表的字段必须设置为主键。要求从表不能是临时表，主从表的字段具备相同的数据类型、字符长度和约束。

4.4 相关操作

#创建主表 profession
create table profession (pid int(4),proname varchar(50));

#创建从表 student
create table student (id int(4) primary key auto_increment,name varchar(10),age int(3),proid int(4));

#为主表 profession 添加一个主键约束。主键名建议以“PK_”开头。
alter table profession add constraint PK_pid primary key (pid);

#为从表 student 表添加外键，并将 student 表的 proid 字段和 profession 表的 pid 字段建立外键关联。外键名建议以“FK_”开头。
alter table student add constraint FK_pro foreign key (proid) references profession (pid);

desc student;

#插入新的数据记录时，要先主表再从表
insert into profession values(1,'科技');
insert into profession values(2,'狠活');
insert into student values(1,'zhangsan',18,1);
insert into student values(2,'lisi',19,1);
insert into student values(3,'wangwu',20,2);

#删数数据记录时，要先从表再主表，也就是说删除主键表的记录时必须先删除其他与之关联的表中的记录。
delete from student where proid=1;
delete from profession where pid=1;

#查看和删除外键约束
show create table student;
desc student;

alter table student drop foreign key FK_pro;
alter table student drop key FK_pro;

总结

1. 如何实现显示10到20行的数据

select *from 表名 limit 9,11;           

2. primary key 与 unique key 区别

• 都不允许有重复字段
• primary key（主键）不允许有null值
• unique key（唯一键）允许有多个null值

3. 如何去克隆表和清空表

#克隆表
create table 新表名 like 旧表名;					#克隆表结构
insert table 新表名 (select * from  旧表名);		#克隆表数据
###以上方式克隆，表结构和表数据都一样

create table 新表名 (select * from  旧表名);
###以上方式克隆，表数据一样，表结构可能不一样


#清空表
delete from 表名;			#一条一条的删除记录，效率较慢，自增字段仍然会按照清空前的最大记录自增
truncate table 表名;			#直接重建表，清空表效率较快，自增字段会重新从1开始自增