MySQL⽤户管理及SQL语句详解
1.1 MySQL⽤户管理
1.1.1 ⽤户的定义
3306 [(none)]>select user,host from mysql.user; #查询⽤户和主机
+---------------+-----------+
| user | host |
+---------------+-----------+
| root | 10.0.0.% |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)
1.1.2 ⽤户的作⽤
1、⽤户登录
TCP/IP⽅式（远程、本地）：
mysql -uroot -poldboy123 -h 10.0.0.51 -P3306
Socket⽅式(仅本地)：
mysql -uroot -poldboy123 -S /tmp/mysql.sock
⽤户名@'⽩名单'
wordpress@'10.0.0.%'
wordpress@'%'
wordpress@'10.0.0.200'
wordpress@'localhost'
wordpress@'db02'
wordpress@'10.0.0.5%'
wordpress@'10.0.0.0/255.255.254.0'
2、⽤于管理数据库及数据
增：
mysql> create user oldboy@'10.0.0.%' identified by '123';
查：
mysql> desc mysql.user; ----> authentication_string
mysql> select user ,host ,authentication_string from mysql.user 查⽤户密码，⼀般都是加密
改:
mysql> alter user oldboy@'10.0.0.%' identified by '456';
删：
mysql> drop user oldboy@'10.0.0.%';
命令:
grant 权限 on 权限范围 to ⽤户 identified by '密码'
权限
对数据库的读、写等操作
（insert update、select、delete、drop、create等）
ALL:
SELECT,INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE,
REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATIO ALL : 以上所有权限，⼀般是普通管理员拥有的
with grant option：超级管理员才具备的，给别的⽤户授权的功能
权限范围
*.* 所有库所有表 ---->管理员⽤户
wordpress.* 指定当前库所有表授权 ---->开发和应⽤⽤户
wordpress.t1 当前库指定表 ti 授权
1.1.3 【练习题】按照要求创建⽤户
⽤户只能通过10.0.0.0/24⽹段访问，⽤户名为clsn 密码为123
这个⽤户只能对clsn数据库下的对象进⾏增insert create、改update 、查select；
创建命令：
grant select,create,insert,update on clsn.* to 'clsn'@'10.0.0.%' identified by '123';
查看⽤户权限
mysql> show grants for app@'10.0.0.%';
1.14 本地管理员⽤户密码忘记.
[root@db01 ~]# mysqld_safe --skip-grant-tables --skip-networking &
mysql
mysql> flush privileges;
mysql> alter user root@'localhost' identified by '123';
查看当前存在的⽤户:
select user,host from mysql.user;
企业⾥创建⽤户⼀般是授权⼀个内⽹⽹段登录，最常见的⽹段写法有两种。
⽅法1：172.16.1.%（%为通配符，匹配所有内容）。
⽅法2：172.16.1.0/255.255.255.0，但是不能使⽤172.16.1.0/24，是个⼩遗憾。
标准的建⽤户⽅法：
create user 'web'@'172.16.1.%' identified by 'web123';
1.1.5⽤户授权
给⽤户授权
# 创建⽤户
create user 'clsn'@'localhost' identified by 'clsn123';
# 查看⽤户
select user,host from mysql.user;
# 授权所有权限给clsn⽤户
GRANT ALL ON *.* TO 'clsn'@'localhost';
SHOW GRANTS FOR 'clsn'@'localhost'\G
创建⽤户的同时授权
grant all on *.* to clsn@'172.16.1.%' identified by 'clsn123';
# 刷新权限
flush privileges; #<==可以不⽤。
创建⽤户然后授权
create user 'clsn'@'localhost' identified by 'clsn123';
GRANT ALL ON *.* TO 'clsn'@'localhost';
授权和root⼀样的权限
grant all on *.* to system@'localhost' identified by 'clsn123' with grant option;
用补码形式实现运算授权给⽤户select,create,insert,update 权限
grant select,create,insert,update on clsn.* to 'clsn'@'10.0.0.%' identified by '123';
回收权限
REVOKE INSERT ON *.* FROM clsn@localhost;
可以授权的⽤户权限
INSERT,SELECT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN,
PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER,
CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE,
REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER
ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE
【⽰例】博客授权收回⽰例
grant select,insert,update,delete,create,drop on blog.* to 'blog'@'172.16.1.%' identified by 'blog123';
revoke create,drop on blog.* from 'blog'@'172.16.1.%';
授权博客类的最多权限：select,insert,update,delete
1.2 MySQL 客户端⼯具
1.2.2.1 source命令的使⽤
mysql> SOURCE /data/mysql/world.sql
或者使⽤⾮交互式：(尽量避免使⽤mysql 导⼊数据，会产⽣⼤量的⽆⽤⽇志)
mysql</data/mysql/world.sql
1.2.2 mysqladmin命令说明
基本语法
mysqladmin -u<name> -p<password> commands
1.3 SQL语句⼊门
1.3.1.1 sql语句及其种类
DDL（Data Definition Language，数据定义语⾔）⽤来创建或者删除存储数据⽤的数据库以及数据库中的表等对象。DDL 包含以下⼏种指令。
CREATE：
创建数据库和表等对象
access下载链接DROP：删除数据库和表等对象
ALTER：修改数据库和表等对象的结构
●DML
DML（Data Manipulation Language，数据操纵语⾔）⽤来查询或者变更表中的记录。DML 包含以下⼏种指令。
SELECT：查询表中的数据
INSERT：向表中插⼊新数据
UPDATE：更新表中的数据
DELETE：删除表中的数据
●DCL
DCL（Data Control Language，数据控制语⾔）⽤来确认或者取消对数据库中的数据进⾏的变更。除此之外，还可以对 RDBMS的⽤户是否有权限操作数据库中的对象（数据库表等）进⾏设定。DCL 包含以下⼏种指令。
COMMIT：确认对数据库中的数据进⾏的变更
取消对数据库中的数据进⾏的变更
GRANT：赋予⽤户操作权限
REVOKE：取消⽤户的操作权限
1.3.1.1 数据库查看
查看数据库--查看全部
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| clsn |
+--------------------+
查看数据库--模糊匹配
mysql> show databases like "%s%";
+--------------------+
| Database (%s%) |
+--------------------+
| information_schema |
| clsn |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
1.3.1.2 数据库操作
创建⼀个数据库
mysql> create database haha;
通过show 命令能够查看创建的数据库的格式
mysql> show create database haha;
+----------+---------------------------------------------------------------+
| Database | Create Database |
+----------+---------------------------------------------------------------+
| haha | CREATE DATABASE `haha` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)
创建数据库时定义字符编码
mysql> create database clsn charset utf8 ;
mysql> show create database clsn; 查询数据库定义信息。
存在的数据库修改字符编码：
mysql> alter database clsn charset gbk;
修改数据库编码格式⽰例
mysql> alter database haha charset gbk
-
> ;
Query OK, 1 row affected (0.00 sec)
mysql> show create database haha;
+----------+--------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------+
| haha | CREATE DATABASE `haha` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)
标准修改系统
ALTER DATABASE [db_name] CHARACTER SET charset_name COLLATE collation_name; ALTER DATABASE clsn CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
查看⽀持的字符集和校对规则.
mysql> show character set;
+----------+-----------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
删除数据库
mysql> drop database haha;
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
切库
mysql> use clsn;
Database changed
html水平居中查看当前所在数据库
mysql> select database();
+------------+
| database() |
+------------+
| clsn |
+------------+
1 row in set (0.00 sec)
查看当前登陆的⽤户
mysql> select user();
+----------------+
| user() |
+----------------+
powerpoint官方下载 免费完整版2019| root@localhost |
+----------------+
1 row in set (0.00 sec)
mysql> show tables;
+----------------+
| Tables_in_clsn |
+----------------+
| t1 |
| t2 |
| test |
+----------------+
3 rows in set (0.00 sec)
1.3.1.3 DDL语句之管理表
表的属性：
字段、数据类型、索引
mysql面试题sql
默认：字符集、引擎
表定义（列）：
表名、列名
列属性（数据类型、列约束）
创建表
create table t3 (id int);
创建更多的表；
create table test(id int);
create table t1(idcard int ,name char(30),sex char(4));
查看表结构
mysql> desc t2
-
> ;
+--------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| idcard | int(11) | YES | | NULL | |
| name | char(30) | YES | | NULL | |
| sex | char(4) | YES | | NULL | |
| addr | char(4) | NO | | NULL | |
+--------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)
查看建表语句
mysql> show create table t2\G
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`idcard` int(11) DEFAULT NULL,
`name` char(30) DEFAULT NULL,
`sex` char(4) DEFAULT NULL,
`addr` char(4) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
1.3.1.4 修改表的定义
修改表名字
mysql> rename table t3 to haha;
mysql> show tables ;
+----------------+
| Tables_in_clsn |
+----------------+
| haha |
修改表名字，⽅法⼆。
mysql> alter table haha rename to people;
mysql> show tables;
+----------------+
| Tables_in_clsn |
+----------------+
| people |
修改表结构
mysql> alter table people add addr char(40) NOT NULL; mysql> desc people;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| addr | char(40) | NO | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
指定添加年龄列到name列后⾯的位置，⽰例如下：mysql> alter table people add age int(4) after name;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc people;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| addr | char(40) | NO | | NULL | |
| name | int(40) | YES | | NULL | |
| age | int(4) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)
通过下⾯的命令在第⼀列添加qq字段。
mysql> alter table people add telnum int first;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc people;
+--------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| telnum | int(11) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
| addr | char(40) | NO | | NULL | |
| name | int(40) | YES | | NULL | |
| age | int(4) | YES | | NULL | |
+--------+----------+------+-----+---------+-------+
5 rows in set (0.00 sec)
同时添加多个列定义：
mysql> alter table people add id1 int first ,add sex char(4) after name ;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc people;
+--------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| id1 | int(11) | YES | | NULL | |
| telnum | int(11) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
| addr | char(40) | NO | | NULL | |
| name | int(40) | YES | | NULL | |
| sex | char(4) | YES | | NULL | |
| age | int(4) | YES | | NULL | |
+--------+----------+------+-----+---------+-------+
7 rows in set (0.00 sec)
删除表结构：
mysql> alter table people drop sex;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
priorityqueue小顶堆mysql> desc people;
+--------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| id1 | int(11) | YES | | NULL | |
| telnum | int(11) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
| addr | char(40) | NO | | NULL | |
| name | int(40) | YES | | NULL | |
| age | int(4) | YES | | NULL | |
+--------+----------+------+-----+---------+-------+
6 rows in set (0.00 sec)
修改表定义
mysql> alter table people modify name char(20);
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc people;
+--------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| id1 | int(11) | YES | | NULL | |
| telnum | int(11) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
| addr | char(40) | NO | | NULL | |
| name | char(20) | YES | | NULL | |
| age | int(4) | YES | | NULL | |
+--------+----------+------+-----+---------+-------+
6 rows in set (0.00 sec)
修改列名：
mysql> alter table people change name people_name char(30) ;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc people;
+-------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| id1 | int(11) | YES | | NULL | |
| telnum | int(11) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
| addr | char(40) | NO | | NULL | |
| people_name | char(30) | YES | | NULL | |
| age | int(4) | YES | | NULL | |
+-------------+----------+------+-----+---------+-------+
6 rows in set (0.00 sec)
1.3.1.5 DDL语句⼩结
create database xxx charset # 创建数据库设置字符集
create table xxx (id int,xxxxxx) # 创建表
drop table # 删除表
drop database # 参数数据库
alter table add/drop/modify/change # 表内容修改
create user # 创建⽤户
drop user #删除⽤户
1.3.2 DCL数据库控制语⾔
grant：⽤户授权
GRANT ALL ON *.* TO 'clsn'@'localhost';
SHOW GRANTS FOR 'clsn'@'localhost'\G
创建⽤户的同时进⾏授权
grant select,create,insert,update on clsn.* to 'clsn'@'10.0.0.%' identified by '123'; revoke：回收权限
REVOKE INSERT ON *.* FROM clsn@localhost;
收回权限⽰例：
revoke drop,delete on *.* from sys@localhost;
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论