[sql]用户授权&约束&数据类型

mysql授权

参考: 用户授权

授权命令|权限|库.表|用户@允许主机|密码
-|-|-|-|-
grant|all|on test.*|to 'maotai'@'192.168.2.%' |identified by '123456';

给权限+库==>用户


MySQL [(none)]> grant all on *.* to 'maotai'@'192.168.2.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> show grants for 'maotai'@'192.168.2.%';
+--------------------------------------------------------------------------------------------------------------------------+
| Grants for maotai@192.168.2.%                                                                                            |
+--------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'maotai'@'192.168.2.%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| GRANT ALL PRIVILEGES ON `test`.* TO 'maotai'@'192.168.2.%'                                                               |
+--------------------------------------------------------------------------------------------------------------------------+

pymysql测试db的是否可以连同

import pymysql as ms
conn = ms.connect(host='192.168.2.11',port=3306,user='root',passwd='123456')

if conn:
    print("连接mysql成功...")
else:
    print("连接mysql失败...")

查看所有权限

MySQL [(none)]> help revoke;

MySQL [(none)]> REVOKE INSERT ON test.* FROM 'maotai'@'%'; #1,注意关键字on 2,注意@前后都要有双引号

MySQL [(none)]> show grants for 'maotai'@'%';
...
GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `test`.* TO 'maotai'@'%' |

- 交互式查看权限
[root@n1 ~]# mysql -h 192.168.2.11 -uroot -p123456 -e "show grants for 'maotai'@'%';"


- 交互式查看权限并导出
[root@n1 ~]# mysql -h 192.168.2.11 -uroot -p123456 -e "show grants for 'maotai'@'%';"|grep -i grant|tail -1|tr ',' '\n' > mysql_grants.txt

- mysql的17种权限
[root@n1 ~]# awk '{print NR,$1}' mysql_grants.txt 
1 SELECT
2 INSERT
3 UPDATE
4 DELETE
5 CREATE
6 DROP
7 REFERENCES
8 INDEX
9 ALTER
10 CREATE
11 LOCK
12 EXECUTE
13 CREATE
14 SHOW
15 CREATE
16 ALTER
17 EVENT

- 查看结构
MySQL [(none)]> select * from mysql.user\G
...
*************************** 3. row ***************************
                  Host: 192.168.2.%
                  User: maotai
              Password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: Y
         Shutdown_priv: Y
          Process_priv: Y
             File_priv: Y
            Grant_priv: N
       References_priv: Y
            Index_priv: Y
            Alter_priv: Y
          Show_db_priv: Y
            Super_priv: Y
 Create_tmp_table_priv: Y
      Lock_tables_priv: Y
          Execute_priv: Y
       Repl_slave_priv: Y
      Repl_client_priv: Y
      Create_view_priv: Y
        Show_view_priv: Y
   Create_routine_priv: Y
    Alter_routine_priv: Y
      Create_user_priv: Y
            Event_priv: Y
          Trigger_priv: Y
Create_tablespace_priv: Y

- 授权命令
GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `test`.* TO 'maotai'@'%';

约束

CREATE TABLE users (
	id INT(4) NOT NULL auto_increment PRIMARY KEY,
	NAME VARCHAR (40) NOT NULL,
	age TINYINT (2) NOT NULL DEFAULT '18',
	gender VARCHAR (30) DEFAULT 'female'
);

- not null       # 不为空
- auto_increment # 自增长
- primary key    # 主键
- default '18'   # 指定默认值



insert into users values(
1, 'maotai',22,'male'
);


MySQL [bbs]> desc users;
+--------+-------------+------+-----+---------+----------------
| Field  | Type        | Null | Key | Default | Extra          
+--------+-------------+------+-----+---------+----------------
| id     | int(4)      | NO   | PRI | NULL    | auto_increment 
| NAME   | varchar(40) | NO   |     | NULL    |                
| age    | tinyint(2)  | NO   |     | 18      |                
| gender | varchar(30) | YES  |     | female  |                
+--------+-------------+------+-----+---------+----------------
4 rows in set (0.00 sec)

- 查看建表语句
mysql> show create table user;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table

     |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user  | CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(40) DEFAULT NULL,
  `age` int(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 |