MySQL数据库高级操作（图文详解）

一、案例拓展

use jinlei；
create table if not exists info (
id int(4) zerofill primary key auto_increment,   #指定主键的第二种方式
name varchar(10) not null,
cardid int(18) not null unique key,
hobby varchar (50)) ;

说明：
#if not exists: 表示检测要创建的表是否已存在，如果不存在就继续创建

#int(4) zerofill: 表示若数值不满4位数，则前面用“O"填充，例0001

#auto_ increment: 表示此字段为自增长字段，即每条记录自动递增1，默认从1开始递增;自增长字段数据不可以重复:自增长字段必须是主键;如添加的记录数据没有指定此字段的值且添加失败也会自动递增一次

#unique key:表示此字段唯一键约束， 此字段数据不可以重复:一张表中只能有一个主键，但是一张表中可以有多个唯一键

#not null:表示此字段不允许为NULL

二、数据库高级操作

1、克隆表

方法一

create table qq  like bzx1； #通过LIKE方法，复制bzx1表结构生成qq表
insert into qq select * from bzx1;

方法二

CREATE TABLE ww (SELECT * from bzx1); #获取数据表的表结构、索引等信息
SELECT * from ww；
直接将表bzx1 格式以及内容一起复制到ww表

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

 delete from qq;
 truncate  table  ww；

3、创建临时表

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

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

三、创建外键约束，保证数据的完整性和一致性

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

说明：
以公共关键字作主键的表为主键表(父表、主表)
以公共关键字作外键的表为外键表(从表、外表)

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

1、创建主从表

create table zhu (hobid int (4) , hobname varchar (50)) ; #创建主表
create table cong (id int(4) primary key auto_increment, name varchar (10),age int(3),hobid int(4));  #创建从表

2、为主表zhu添加一一个主键约束。主键名建议以“PK_ "开头

alter table zhu add constraint PK_hobid primary key (hobid) ;

3、为从表cong表添加外键，并将cong表的hobid 字段和zhu表的hobid 字段建立外键关联。外键名建议以“FK”开头

alter table cong add constraint FK_hob foreign key (hobid) references zhu (hobid) ;
desc cong;

4、插入新的数据记录时，要先主表再从表

insert into zhu values(1, 'runing');

insert into cong values (1, 'zhangsan');

5、删数数据记录时，要先从表再主表，也就是说删除主键表时必须先删除其他与之关联的表

drop tables cong;
drop tables zhu;

四、MYSQL中6中常见的约束

主键约束( primary key )

外键约束(foreign key )

非空约束(not null )

唯一性约束(unique [keylindex] )

默认值约束(default )

自增约束(auto increment )

五、数据库用户管理

1、新建用户

CREATE USER '用户名'@'来源地址’[IDENTIFIED BY [PASSWORD] ' 密码'];

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

CREATE USER 'user1 '@'localhost' IDENTIFIED BY '123456';

SELECT PASSWORD('abc123');

CREATE USER 'user2'@'localhost' IDENTIFIED BY PASSWORD ' * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9';

2、查看用户信息

#创建后的用户保存在mysql 数据库的user 表里

USE mysql;
SELECT User, authentication string, Host from user;

3、重命名用户

rename use 'user1'@'localhost' to 'zhangsan'@'%';

4、修改当前登录用户密码

SET PASSWORD = PASSWORD('abc123') ;

5、修改其他用户登录密码

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

六、忘记root密码解决办法

1、修改/etc/my.cnf 配置文件

vim /etc/my.cnf
[mysqld]
skip-grant-tables    #添加，使登录mysql不使用授权表

systemctl restart mysqld

mysql     #直接登录

2、使用update 修改root 密码，刷新数据库

UPDATE mysql.user SET AUTHENTICATION_STRING = PASSWORD('abc123') where user=' root' ;

FLUSH PRIVILEGES;

quit

mysql -u root -p123456

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

七、数据库用户授权

1、授予权限

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

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

1. 权限列表:用于列出授权使用的各种数据库操作，以逗号进行分隔，如“select, insert, update"。使用“all" 表示所有权限，可授权执行任何操作

2. 数据库名.表名:用于指定授权操作的数据库和表的名称，其中可以使用通配符 “ ✳”。例如，使用" jinlei.✳"表示授权操作的对象为kgc数据库中的所有表

3. ‘用户名@来源地址’:用于指定用户名称和允许访问的客户机地址，即谁能连接、能从哪里连接。来源地址可以是域名、IP地址，还可以使用“%”通配符，表示某个区域或网段内的所有地址，如“% . jinlei.com”、“192.168.80.%"等。

4. IDENTIFIED BY:用于设置用户连接数据库时所使用的密码字符串。在新建用户时，若省略“IDENTIFIED BY" 部分，则用户的密码将为空。

允许用户zhangsan在本地查询jinlei数据库中所有表的数据记录，但禁止查询其他数据库中的表的记录

GRANT select ON jinlei.* TO ' zhangsan'@'localhost' IDENTIFIED BY '123456' ;

允许用户zhangsan在所有终端远程连接mysql，并拥有所有权限

GRANT ALL ON . TO ‘zhangsan’@‘%’ IDENTIFIED BY ‘123456’;

flush privileges;
quit

mysql -u zhangsan -p 123456

2、查看权限

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

SHOW GRANTS FOR 'zhangsan'@'%';

3、撤销权限

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

#USAGE权限只能用于数据库登陆，不能执行任何操作; USAGE权限 不能被回收，即REVOKE不能删除用户