Mysql数据库（三）—高级操作

Mysql数据库（三）—高级操作

​​一、指定主键的另一种方式​

use ky17;create table if not exists sss(id int(4) zerofill primary key auto_increment,name varchar(20) not null,cardid varchar(18) not null unique key,hobby varchar(50));

二、克隆/复制一个表

​方法一：​

​create table 新表名 like 复制的表名;  ​

​复制格式，能够复制表的格式到新表，但是没有内容​

​insert into 新表名 select * from 复制的表名; ​

​复制原表内容到新表​

​原始表数据和数据类型​

​使用create table 新表名 like 复制的表名; ​

​使用insert into 新表名 select * from 复制的表名; 内容复制过来了​

方法二；

create table 新表名 (select * from 复制的表名)

​数据结构和数据一起复制过来了，克隆​

三，mysql常见约束

四，外键约束

1、外键的定义

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

2、作用

创建外键约束的作用：保证数据的完整性和一致性

3、主表和从表

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

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

注意：与外键关联的主表字段必须设置为主键，要求从表不能是临时表。

主表主键的字段与从表外键的字段具有相同的数据类型，字符长度和约束。

4、主表和从表的操作原则和示范

​①、首先创建两张表，并且其中一张表的字段和另一张表的主键相同 （iphon字段）​

②、为主表添加主键约束，并为从表添加外键​

​ ③、查看结构情况显示外键关联

④插入数据（先主表，再从表）

⑤、删除数据记录（先从表，后主表）​

​⑥、如果要删除外键约束字段（先删除外键约束，再删除外键名​

五，数据库用户管理

​1、新建用户​

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

​ 用户名：指定将创建的用户名​

​ 来源地址：指定新创建的用户可在哪些主机上登录，可使用IP地址、网段、主机名的形式，本地用户可用localhost，允许任意主机登录，可使用通配符 %​

​ 密码：若使用明文密码，直接输入’密码’，插入到数据库时由Mysql自动加密;​

​若使用加密密码，需要先使用SELECT PASSWORD(‘密码’); 获取密文，再在语句中添加PASSWORD ‘密文’;若省略“IDENTIFIED BY”部分，则用户的密码将为空（不建议使用）​

2、查看用户信息

use mysql;

​select user,authentication_string,Host from user;     authentication_string  验证字符串​

3、重命名用户

rename user 'lisi'@'localhost' to 'wangwu'@'localhost';

​select user,authentication_string,Host from user;​

4、删除用户

drop user 'wangwu'@'localhost';

​select User,authentication_string,Host from user;​

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

set password = password('123456');

quit

​mysql -u root -p​

6、修改其他用户密码

set password for 'zhangsan'@'localhost' = password('111111');

use mysql;

​select User,authentication_string,Host from user;​

7、mysql的root登录密码忘记了处理办法

​①、修改配置文件，改成免密登录，并重启mysql服务​

②、免密登录mysql，使用 update 修改 root 密码，并刷新数据库​

③、退出数据库，注释掉配置文件中的免密登录项，并重启mysql服务​

八、数据库用户授权

​1、授予权限​

​grant 提权​

​grant 权限列表 on 数据库名.表名 to '用户名'@'来源地址' [identified by '密码'];​

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

​数据库名.表名：用于指定授权操作的数据库和表的名称，其中可以使用通配符 * 。lucien.* 表示授权操作的对象为lucien数据库中的所有表。​

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

​ identified by：用于设置用户连接数据库时所使用的密码字符串。在新建用户时，若省略 “identified by” 部分， 则用户的密码将为空。​

2、查看权限

​show grants for 用户名@来源地址;​

3、撤销权限

​revoke 权限列表 on 数据库名.表名 from 用户名@来源地址;​

总结

这一章节，我们需要了解一些简单的 DBA 的高级操作

1、需要了解的5个项：

if not exists：表示检测要创建的表是否存在，如果不存在就创建表

int(4) zerofill：表示若数值不满4位数，则前面用“0”填充

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

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

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

2、关于克隆一个表的操作

有两种方法可进行克隆/复制一个表

方法一：先复制格式再复制内容

create table 新表名 like 复制的表名;

insert into 新表名 select * from 复制的表名;

方法二：直接全部复制（包括格式和内容）

create table 新表名 (select * from 复制的表名)

3、清空表操作

有三种方式：delete、truncate 和 drop

三种操作的对比：

删除速度：drop > truncate > delete

安全性：delete 最好

4、mysql的6个常见约束方式

主键约束：primary key

外键约束：foreign key

非空约束：not null

唯一约束：unique [key

默认值约束：default

自增约束：auto_increment

5、关于外键的定义、作用和主表从表的一些操作注意点

定义：我把外键简单理解为外部表格的主键。

作用：保证数据的完整性和一致性。能更好的避免误删的情况出现。

添加主键约束：

alter table fengchu add constraint PK_cardid primary key(cardid);

添加外键：

alter table wolong add constraint FK_card foreign key(cardid) references fengchu(cardid);

主表和从表：

主表：公共关键字作为主键的表

从表：公共关键字作为外键的表

操作注意点：

插入数据时，先主表，后从表

删除数据时，先从表，后主表

删除约束字段：先删除外键约束，再删除外键名

alter table 表名 drop foreign key FK_card;

alter table 表名 drop key FK_card;

7、用户管理

‘用户名’@'来源地址’是一个整体，不管是创建、删除、重命名、改密码等操作

所有的 mysql 用户信息都存放在 mysql 库的 user 表中，

可使用 select User,authentication_string,Host from user;查看

关于root龙湖忘记登录数据库的密码，修改完配置文件后，免密登录mysql之后，并没有提权，所以我们不能直接使用set 来修改密码，需要使用 update mysql.user set authentication_string = password(‘010230’) where user=‘root’;

在给用户授权的时候，可以使用通配符代表所有。

不建议省略 “identified by” 部分，因为这样就代表不需要密码登录，会很不安全。

才进行撤销权限操作的时候，最后剩下的USAGE代表登录权限，为不可回收权限。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。