MySQL数据库增删改查的基本操作2

MySQL中6种常见的约束：
主键约束:primary key
外键约束:foreign key
非空约束:not null
唯一性约束:unique [key|index]
默认值约束:default
自增约束:auto_increment

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

创建外键约束作用：(误删，修改)，保证数据的完整性和一致性。主键表和外健表的理解
(1)以公共关键字作主键的表为主键表(父表、主表)
(2)以公共关键字作外键的表为外键表(从表、外表)
注意:与外键关联的主表的字段必须设置为主键。要求从表不能是临时表,主表外键字段和从表的字段具备相同的数据类型、字符长度和约束。

示例：建立主表做主键，从表做外键，让主从表关联
#建立主表test4
mysql> create table test4 (hobid int(4),hobname varchar(50));
#建立从表tset5
mysql> create table test5 (id int(4) primary key auto_increment,name varchar(10),age int(3),hobid 
int(4));
#给主表test4添加一个主键约束，主键名建议以‘pk_’开头
mysql> alter table test4 add constraint PK_hobid primary key(hobid);
#给从表test5添加外键，并将从表的hobid的字段和主表的hobid字段建立外键关联，外键名建议以‘FK_’开头，references;引用
mysql> alter table test5 add constraint FK_hobid foreign key(hobid) references test4(hobid);
#使用查询表语句结构命令查看外键关联
mysql> show create table test5;
mysql> desc test5;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(4)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | YES  |     | NULL    |                |
| age   | int(3)      | YES  |     | NULL    |                |
| hobid | int(4)      | YES  | MUL | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
mysql> desc test4;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| hobid   | int(4)      | NO   | PRI | NULL    |       |
| hobname | varchar(50) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
#插入新的数据记录时，必须要先插入主表在插入从表，
mysql> insert into test4 values(1,'runing');
mysql> insert into test5 values(1,'zhangsan',18,1);
#删除数据时，要先从表再主表，删除主表时必须要先删除其他关联其他关联的表
mysql> drop tables test5;
mysql> drop tables test4;
#删除外键约束字段要先删除外键约束，在删除外键名
mysql> alter test5 drop foregin key FK_hobid;
mysql> alter test5 drop key FK_hobid;

数据库用户管理
1、新建用户
create user ‘用户名’@’ 来源地址 ’ [ identified by [password] ’ 密码 ’ ]
‘用户名’：指定将创建的用户名
‘来源地址’：指定新创建的用户可在哪写主机上登录，可使用ip地址（192.168.153.215）、网段（192.168.153.0/24）、主机名的形式（localhost），本地用户可用localhost，允许任意主机登录
可用通配符%
‘密码’：若使用明文密码，直接输入’密码’，插入到数据库时由mysql自动加密
若使用加密密码，需要先使用select password(‘密码’)——》获取密文，然后在语句中添加password ‘密文’ ，若省略identified by部分，则用户的密码将为空

新建用户示例：
#使用明文密码创建一个用户张三，允许他通过本地登录，给予他的密码为123
mysql> create user 'zhangsan'@'localhost' identified by '123'
mysql> select * from user\G
  plugin: mysql_native_password
 authentication_string: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257
#使用非明文密码创建一个用户李四，允许他通过本地登录，给予他的密码为123
mysql> select password('123');   先设置密码123获取一个非明文密码
+-------------------------------------------+
| password('123')                           |
+-------------------------------------------+
| *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+-------------------------------------------+
mysql> create user 'lisi'@'localhost' identified by password '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257';   #非明文密码注入里面
mysql> select * from user\G
plugin: mysql_native_password
 authentication_string: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257

2、查看用户信息
创建后的用户保存在MySQL数据库的user表里

mysql> use mysql;
mysql> select user,authentication_string,host from user;
+---------------+-------------------------------------------+-----------+
| user          | authentication_string                     | host      |
+---------------+-------------------------------------------+-----------+
| root          | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | localhost |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| zhangsan      | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | localhost |
| lisi          | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | localhost |

3、重命名指定

mysql> rename user 'zhangsan'@'localhost' to 'wangwu'@'localhost';    #把表里的用户张三名字改为王五

4、删除用户

drop user ‘lisi’@‘localhost’;

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

set password = password(‘123’);

6、修改其他用户密码

 set password for ‘user1'@'localhost’ = PASSWORD ('123');

7、忘记root密码的解决办法

1、修改/etc/my.cnf 配置文件,免密登陆mysql
[root@localhost ~]# vim /etc/my.cnf
在[mysqld]这一段最后添加一行内容
skip-grant-tables      #登录mysql免密，不需要授权
2、重启服务，然后直接登录
[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# mysql    #直接进入服务 
3、使用SQL语句修改密码
update mysql.user SET AUTHENTICATION_STRING = PASSWORD ('123') where user='root';
4、FLUSH PRIVILEGES;    #刷新规则（即时生效）
5、退出服务，重新登陆
quit
mysql -u root -p123
6、把/etc/my.cnf配置文件里的skip-grant-tables 删除，并重启mysql服务

数据库用户提权
1、grant 提权

GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'来源地址' [IDENTIFIED BY '密码'];
#权限列表:用于列出授权使用的各种数据库操作，以逗号进行分隔，如"select，insert,update"。使用"all"表示所有权限，可授权执行任何操作。
#数据库名.表名:用于指定授权操作的数据库和表的名称，其中可以使用通配符"*"。例如，使用"kgc.*"表示授权操作的对象为kgc数据库中的所有表。
#用户名@来源地址':用于指定用户名称和允许访问的客户机地址，即谁能连接、能从哪里连接。来源地址可以是域名、IP地址，还可以使用"%"通配符，表示某个区域或网段内的所有地址，如"%.xyw.com"、"192.168.226.%"等。
#IDENTIFIED BY:用于设置用户连接数据库时所使用的密码字符串。在新建用户时，若省略"IDENTIFIED BY"部分，则用户的密码将为空。
#允许用户zhangsan在本地查询sch数据库中所有表的数据记录，但禁止查询其他数据库中的表的记录。
GRANT select ON sch.* To 'zhangsan' @ 'localhost' IDENTIFIED BY 'abc123 ';
#允许用户lisi在所有终端远程连接mysql，并拥有所有权限。
GRANT ALL [PRIVILEGES] ON *.* TO 'lisi'@'%’ IDENTIFIED BY '123456';
flush privileges;      #刷新权限(即使是效）

2、查看权限

show grants from '用户名'@'来源地址';
例如：show grants from 'lisi'@'%';

3、撤销权限
revoke 权限列表 on 数据库.表名 from ‘用户名’@‘来源地址’;

revoke all on *.* from 'lisi'@'%';
flush privileges;       #刷新权限(即使是效）
#usage权限只能用于数据库登录，不能执行任何操作
#usage权限不能被删除,机revoke不能删除用户