mysql5.5-增删查改、权限管理操作

小咪咪 2022-05-10 23:36 168阅读 0赞

### 文章目录 ###

*  一、密码管理
 *   *  1.Linux界面修改
     *  2.登录数据库修改指定用户密码
     *  3.修改当前登录用户密码
     *  4.密码破解
 *  二、数据库的增删查
 *   *  1.增
     *   *  1.1不指定字符集创建数据库
         *  1.2指定gbk字符集创建数据库
         *  1.3指定utf8字符集创建数据库
     *  2.删
     *   *  2.1删除数据库
     *  3.查
     *   *  3.1查看所有数据库
         *  3.2查看当前所在的数据库
         *  3.3查看数据库版本
         *  3.4查看时间
         *  3.5查看数据库状态
         *  3.6查看variable变量
 *  三、数据表结构的增删查改
 *   *  1.增
     *   *  1.1创建默认字符集的数据库，并在该数据库中创建表
         *  1.2增加表的列
         *  1.3指定位置增加列
         *  1.4在第一行添加
     *  2.删
     *   *  2.1删除表结构
     *  3.查
     *   *  3.1查看表结构
         *  3.2查看创建表的语句
     *  4.改
     *   *  4.1修改表结构
 *  四、数据库表内容的增删查改
 *   *  1.增
     *   *  1.2指定列添加
         *  1.3不指定列
         *  1.4批量插入
     *  2.删
     *   *  2.1删除字段
         *  2.2清空表内容
         *  3.2查看指定列
         *  3.3指定条件查询
         *  3.4查看前两行
         *  3.5从第二行开始，显示两行（0为第一行）
         *  3.6explain 查看执行计划
     *  4.改
     *   *  4.1修改表内容
         *  4.2修改表名
 *  五、数据库的权限管理
 *   *  1.授权
     *   *  1.1先创建用户再授权
         *  1.2授权时添加用户
         *  1.3授权本地与局域网内机远程连接
     *  2.回收权限
     *   *  2.1查看用户拥有的权限
         *  2.2回收insert权限
         *  2.3删除用户

# 一、密码管理 #

## 1.Linux界面修改 ##

> \[root@mysql ~\]\# mysqladmin -uroot -p000000 password 123456 -S /data/3307/mysql.sock

## 2.登录数据库修改指定用户密码 ##

> mysql> update mysql.user set password=password(“000000”) where user=“root” and host=“localhost”;  
> Query OK, 1 row affected (0.03 sec)  
> Rows matched: 1 Changed: 1 Warnings: 0  
> mysql> flush privileges;  
> Query OK, 0 rows affected (0.00 sec)

## 3.修改当前登录用户密码 ##

> mysql> set password=password(“000000”);  
> Query OK, 0 rows affected (0.00 sec)  
> mysql> flush privileges;  
> Query OK, 0 rows affected (0.00 sec)

## 4.密码破解 ##

> \[root@mysql ~\]\# vi /data/3307/my.conf  
> \[mysqld\]  
> skip-grant-tables  
> \[root@mysql ~\]\# /data/3307/mysqld restart  
> \[root@mysql ~\]\# mysql -uroot -p -S /data/3307/mysql.sock （直接回车不需要输入密码）

# 二、数据库的增删查 #

## 1.增 ##

### 1.1不指定字符集创建数据库 ###

> mysql> create database liang01;  
> Query OK, 1 row affected (0.00 sec)

### 1.2指定gbk字符集创建数据库 ###

> mysql> create database liang\_gbk default character set gbk collate gbk\_chinese\_ci;  
> Query OK, 1 row affected (0.00 sec)  
> mysql> show create database liang\_gbk\\G  
> \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
> Database: liang\_gbk  
> Create Database: CREATE DATABASE `liang_gbk` /\*!40100 DEFAULT CHARACTER SET gbk \*/  
> 1 row in set (0.00 sec)

### 1.3指定utf8字符集创建数据库 ###

> mysql> create database liang\_utf8 default character set utf8 collate utf8\_general\_ci;  
> Query OK, 1 row affected (0.00 sec)  
> mysql> show create database liang\_utf8\\G  
> \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
> Database: liang\_utf8  
> Create Database: CREATE DATABASE `liang_utf8` /\*!40100 DEFAULT CHARACTER SET utf8 \*/  
> 1 row in set (0.00 sec)  
> =====》  
> == 如果编译的时候指定了字符集则创建数据库就不需要指定字符集，不指定默认是拉丁字符集  
> 字符集不相同是导致数据库中文乱码的最大原因 ==

## 2.删 ##

### 2.1删除数据库 ###

> mysql> drop database liang01;  
> Query OK, 0 rows affected (0.00 sec)

## 3.查 ##

### 3.1查看所有数据库 ###

### 3.2查看当前所在的数据库 ###

### 3.3查看数据库版本 ###

> mysql> select version ();  
> ±-----------+  
> | version () |  
> ±-----------+  
> | 5.5.32-log |  
> ±-----------+  
> 1 row in set (0.00 sec)

### 3.4查看时间 ###

> mysql> select now();  
> ±--------------------+  
> | now() |  
> ±--------------------+  
> | 2018-09-15 17:56:18 |  
> ±--------------------+  
> 1 row in set (0.00 sec)

### 3.5查看数据库状态 ###

> mysql> show global status;

### 3.6查看variable变量 ###

> mysql> show variables;  
> 设置变量  
> set global key\_buffer\_size=…

# 三、数据表结构的增删查改 #

## 1.增 ##

### 1.1创建默认字符集的数据库，并在该数据库中创建表 ###

> mysql> create database liang;  
> Query OK, 1 row affected (0.00 sec)  
> mysql> use liang  
> Database changed  
> mysql> create table student(  
> id int(4) not null,  
> name char(20) not null,  
> age tinyint(2) not null default ‘0’  
> );  
> Query OK, 0 rows affected (0.03 sec)  
> mysql> show tables;  
> ±----------------+  
> | Tables\_in\_liang |  
> ±----------------+  
> | student |  
> ±----------------+  
> 1 row in set (0.00 sec)

### 1.2增加表的列 ###

> mysql> desc student;  
> ±------±-----------±-----±----±--------±------+  
> | Field | Type | Null | Key | Default | Extra |  
> ±------±-----------±-----±----±--------±------+  
> | id | int(4) | NO | | NULL | |  
> | name | char(20) | NO | | NULL | |  
> | age | tinyint(2) | NO | | 0 | |  
> ±------±-----------±-----±----±--------±------+  
> 3 rows in set (0.00 sec)  
> mysql> alter table student add dept varchar(16);  
> Query OK, 0 rows affected (0.04 sec)  
> Records: 0 Duplicates: 0 Warnings: 0  
> mysql> desc student;  
> ±------±------------±-----±----±--------±------+  
> | Field | Type | Null | Key | Default | Extra |  
> ±------±------------±-----±----±--------±------+  
> | id | int(4) | NO | | NULL | |  
> | name | char(20) | NO | | NULL | |  
> | age | tinyint(2) | NO | | 0 | |  
> | dept | varchar(16) | YES | | NULL | |  
> ±------±------------±-----±----±--------±------+  
> 4 rows in set (0.00 sec)

### 1.3指定位置增加列 ###

> mysql> alter table student add sex char(4) after name;  
> Query OK, 0 rows affected (0.03 sec)  
> Records: 0 Duplicates: 0 Warnings: 0  
> mysql> desc student;  
> ±------±-----------±-----±----±--------±---------------+  
> | Field | Type | Null | Key | Default | Extra |  
> ±------±-----------±-----±----±--------±---------------+  
> | id | int(4) | NO | PRI | NULL | auto\_increment |  
> | name | char(20) | NO | MUL | NULL | |  
> | sex | char(4) | YES | | NULL | |  
> | age | tinyint(2) | NO | | 0 | |  
> | dept | varchar(6) | YES | | NULL | |  
> ±------±-----------±-----±----±--------±---------------+  
> 5 rows in set (0.01 sec)

### 1.4在第一行添加 ###

> mysql> alter table student add qq char(4) first;  
> Query OK, 0 rows affected (0.10 sec)  
> Records: 0 Duplicates: 0 Warnings: 0  
> mysql> desc student;  
> ±------±-----------±-----±----±--------±---------------+  
> | Field | Type | Null | Key | Default | Extra |  
> ±------±-----------±-----±----±--------±---------------+  
> | qq | char(4) | YES | | NULL | |  
> | id | int(4) | NO | PRI | NULL | auto\_increment |  
> | name | char(20) | NO | MUL | NULL | |  
> | sex | char(4) | YES | | NULL | |  
> | age | tinyint(2) | NO | | 0 | |  
> | dept | varchar(6) | YES | | NULL | |  
> ±------±-----------±-----±----±--------±---------------+  
> 6 rows in set (0.00 sec)

## 2.删 ##

### 2.1删除表结构 ###

> mysql> desc student  
> \-> ;  
> ±------±-----------±-----±----±--------±---------------+  
> | Field | Type | Null | Key | Default | Extra |  
> ±------±-----------±-----±----±--------±---------------+  
> | id | int(4) | NO | PRI | NULL | auto\_increment |  
> | name | char(20) | NO | MUL | NULL | |  
> | age | tinyint(2) | NO | | 0 | |  
> ±------±-----------±-----±----±--------±---------------+  
> 3 rows in set (0.00 sec)  
> mysql> alter table student drop age;  
> Query OK, 0 rows affected (0.10 sec)  
> Records: 0 Duplicates: 0 Warnings: 0  
> mysql> desc student;  
> ±------±---------±-----±----±--------±---------------+  
> | Field | Type | Null | Key | Default | Extra |  
> ±------±---------±-----±----±--------±---------------+  
> | id | int(4) | NO | PRI | NULL | auto\_increment |  
> | name | char(20) | NO | MUL | NULL | |  
> ±------±---------±-----±----±--------±---------------+  
> 2 rows in set (0.00 sec)

## 3.查 ##

### 3.1查看表结构 ###

### 3.2查看创建表的语句 ###

> mysql> show create table student\\G  
> \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
> Table: student  
> Create Table: CREATE TABLE `student` (  
> `id` int(4) NOT NULL,  
> `name` char(20) NOT NULL,  
> `age` tinyint(2) NOT NULL DEFAULT ‘0’  
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1  
> 1 row in set (0.00 sec)

## 4.改 ##

### 4.1修改表结构 ###

> mysql> desc test01;  
> ±------±---------±-----±----±--------±---------------+  
> | Field | Type | Null | Key | Default | Extra |  
> ±------±---------±-----±----±--------±---------------+  
> | id | int(4) | NO | PRI | NULL | auto\_increment |  
> | name | char(20) | NO | | NULL | |  
> ±------±---------±-----±----±--------±---------------+  
> 2 rows in set (0.00 sec)  
> mysql> alter table test01 change name Name char(20);  
> Query OK, 0 rows affected (0.07 sec)  
> Records: 0 Duplicates: 0 Warnings: 0  
> mysql> desc test01;  
> ±------±---------±-----±----±--------±---------------+  
> | Field | Type | Null | Key | Default | Extra |  
> ±------±---------±-----±----±--------±---------------+  
> | id | int(4) | NO | PRI | NULL | auto\_increment |  
> | Name | char(20) | YES | | NULL | |  
> ±------±---------±-----±----±--------±---------------+  
> 2 rows in set (0.00 sec)

# 四、数据库表内容的增删查改 #

## 1.增 ##

> mysql> create table test(  
> \-> id int(4) not null AUTO\_INCREMENT,  
> \-> name char(20) not null,  
> \-> PRIMARY KEY (id)  
> \-> );  
> Query OK, 0 rows affected (0.35 sec)

### 1.2指定列添加 ###

> mysql> insert into test (id,name) values (1,“laowang”);  
> Query OK, 1 row affected (0.04 sec)  
> mysql> select \* from test;  
> ±—±--------+  
> | id | name |  
> ±—±--------+  
> | 1 | laowang |  
> ±—±--------+  
> 1 row in set (0.02 sec)  
> 由于id值自增列，添加它会自动加一  
> mysql> insert into test (name) values (“ergou”);  
> Query OK, 1 row affected (0.01 sec)  
> mysql> select \* from test;  
> ±—±--------+  
> | id | name |  
> ±—±--------+  
> | 1 | laowang |  
> | 2 | ergou |  
> ±—±--------+  
> 2 rows in set (0.00 sec)

### 1.3不指定列 ###

> mysql> insert into test values (3,“laoli”);  
> Query OK, 1 row affected (0.02 sec)  
> mysql> select \* from test;  
> ±—±--------+  
> | id | name |  
> ±—±--------+  
> | 1 | laowang |  
> | 2 | ergou |  
> | 3 | laoli |  
> ±—±--------+  
> 3 rows in set (0.00 sec)

### 1.4批量插入 ###

> mysql> insert into test (name) values (“xie”), (“shi”), (“li”);  
> Query OK, 3 rows affected (0.00 sec)  
> Records: 3 Duplicates: 0 Warnings: 0  
> mysql> select \* from test;  
> ±—±--------+  
> | id | name |  
> ±—±--------+  
> | 1 | laowang |  
> | 2 | ergou |  
> | 3 | laoli |  
> | 4 | xie |  
> | 5 | shi |  
> | 6 | li |  
> ±—±--------+  
> 6 rows in set (0.00 sec)

## 2.删 ##

### 2.1删除字段 ###

> mysql> select \* from test;  
> ±—±--------+  
> | id | name |  
> ±—±--------+  
> | 1 | laowang |  
> | 2 | ergou |  
> | 3 | laolu |  
> | 4 | xie |  
> | 5 | shi |  
> | 6 | li |  
> ±—±--------+  
> 6 rows in set (0.00 sec)  
> mysql> delete from test where id=6;  
> Query OK, 1 row affected (0.01 sec)  
> mysql> select \* from test;  
> ±—±--------+  
> | id | name |  
> ±—±--------+  
> | 1 | laowang |  
> | 2 | ergou |  
> | 3 | laolu |  
> | 4 | xie |  
> | 5 | shi |  
> ±—±--------+  
> 5 rows in set (0.00 sec)

### 2.2清空表内容 ###

> mysql> truncate table test;  
> Query OK, 0 rows affected (0.09 sec)  
> mysql> select \* from test;  
> Empty set (0.00 sec)

\#\#3.查  
\#\#\#3.1查看表的所有列

> mysql> select \* from test;  
> ±—±--------+  
> | id | name |  
> ±—±--------+  
> | 1 | laowang |  
> | 2 | ergou |  
> | 3 | laoli |  
> | 4 | xie |  
> | 5 | shi |  
> | 6 | li |  
> ±—±--------+  
> 6 rows in set (0.00 sec)

### 3.2查看指定列 ###

> mysql> select id from test;  
> ±—+  
> | id |  
> ±—+  
> | 1 |  
> | 2 |  
> | 3 |  
> | 4 |  
> | 5 |  
> | 6 |  
> ±—+  
> 6 rows in set (0.00 sec)

### 3.3指定条件查询 ###

> mysql> select \* from test where id=3;  
> ±—±------+  
> | id | name |  
> ±—±------+  
> | 3 | laoli |  
> ±—±------+  
> 1 row in set (0.00 sec)

### 3.4查看前两行 ###

> mysql> select \* from test limit 2;  
> ±—±--------+  
> | id | name |  
> ±—±--------+  
> | 1 | laowang |  
> | 2 | ergou |  
> ±—±--------+  
> 2 rows in set (0.00 sec)

### 3.5从第二行开始，显示两行（0为第一行） ###

> mysql> select \* from test limit 1,2;  
> ±—±------+  
> | id | name |  
> ±—±------+  
> | 2 | ergou |  
> | 3 | laoli |  
> ±—±------+  
> 2 rows in set (0.01 sec)

### 3.6explain 查看执行计划 ###

> mysql> explain select \* from test where id=3\\G  
> \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
> id: 1  
> select\_type: SIMPLE  
> table: test  
> type: const  
> possible\_keys: PRIMARY  
> key: PRIMARY  
> key\_len: 4  
> ref: const  
> rows: 1  
> Extra:  
> 1 row in set (0.00 sec)

## 4.改 ##

### 4.1修改表内容 ###

> mysql> update test set name=“laolu” where name=“laoli”;  
> Query OK, 1 row affected (0.01 sec)  
> Rows matched: 1 Changed: 1 Warnings: 0  
> mysql> select \* from test;  
> ±—±--------+  
> | id | name |  
> ±—±--------+  
> | 1 | laowang |  
> | 2 | ergou |  
> | 3 | laolu |  
> | 4 | xie |  
> | 5 | shi |  
> | 6 | li |  
> ±—±--------+

### 4.2修改表名 ###

# 五、数据库的权限管理 #

**grant 授权  
revoke 回收权限  
权限的种类：  
SELECT, INSERT,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**

## 1.授权 ##

### 1.1先创建用户再授权 ###

> mysql> create user “liang01”@“localhost” identified by “000000”;  
> Query OK, 0 rows affected (0.00 sec)  
> mysql> select user,host,password from mysql.user where user=“liang01”;  
> ±--------±----------±------------------------------------------+  
> | user | host | password |  
> ±--------±----------±------------------------------------------+  
> | liang01 | localhost | *032197AE5731D4664921A6CCAC7CFCE6A0698693 |  
> ±--------±----------±------------------------------------------+  
> 1 row in set (0.00 sec)  
> mysql> grant all on . to “liang01”@“localhost”;  
> Query OK, 0 rows affected (0.00 sec)  
> ===》  
> all 所有权限  
> \*.\* 第一个*是所有数据库，第二个是库中的所有表  
> localhost 访问数据库的主机，可用域名，IP地址，ip段替代

> mysql> show grants for “liang01”@“localhost”\\G  
> \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
> Grants for liang01@localhost: GRANT ALL PRIVILEGES ON *.* TO ‘liang01’@‘localhost’ IDENTIFIED BY PASSWORD ‘\*032197AE5731D4664921A6CCAC7CFCE6A0698693’  
> 1 row in set (0.00 sec)

### 1.2授权时添加用户 ###

> mysql> drop user “liang01”@“localhost”;  
> Query OK, 0 rows affected (0.00 sec)  
> mysql> grant all privileges on *.* to “liang01”@“loclahost” identified by “000000”;  
> Query OK, 0 rows affected, 1 warning (0.00 sec)

### 1.3授权本地与局域网内机远程连接 ###

> 方法一：  
> mysql> grant all on *.* to “liang01”@“10.0.0.%” ;  
> Query OK, 0 rows affected (0.00 sec)  
> mysql> select user,host from mysql.user;  
> ±--------±----------+  
> | user | host |  
> ±--------±----------+  
> | liang01 | 10.0.0.% |  
> | root | 127.0.0.1 |  
> | root | localhost |  
> | liang01 | loclahost |  
> ±--------±----------+  
> 4 rows in set (0.00 sec)  
> \[root@mysql ~\]\# mysql -uliang01 -p000000 -h 10.0.0.13 -P 3307  
> Welcome to the MySQL monitor. Commands end with ; or \\g.  
> Your MySQL connection id is 3  
> Server version: 5.5.32-log Source distribution  
> Copyright © 2000, 2013, Oracle and/or its affiliates. All rights reserved.  
> Oracle is a registered trademark of Oracle Corporation and/or its  
> affiliates. Other names may be trademarks of their respective  
> owners.  
> Type ‘help;’ or ‘\\h’ for help. Type ‘\\c’ to clear the current input statement.

> 方法二：  
> mysql> grant all on *.* to “liang01”@“10.0.0.0/24”;  
> Query OK, 0 rows affected (0.00 sec)  
> mysql> select user,host from mysql.user;  
> ±--------±------------+  
> | user | host |  
> ±--------±------------+  
> | liang01 | 10.0.0.% |  
> | liang01 | 10.0.0.0/24 |  
> | root | 127.0.0.1 |  
> | liang01 | localhost |  
> | root | localhost |  
> ±--------±------------+  
> 5 rows in set (0.00 sec)

## 2.回收权限 ##

### 2.1查看用户拥有的权限 ###

> mysql> show grants for “liang01”@“localhost”;  
> ±-----------------------------------------------------+  
> | Grants for liang01@localhost |  
> ±-----------------------------------------------------+  
> | GRANT ALL PRIVILEGES ON *.* TO ‘liang01’@‘localhost’ |  
> ±-----------------------------------------------------+  
> 1 row in set (0.00 sec)

### 2.2回收insert权限 ###

> mysql> revoke insert on *.* from “liang01”@“localhost”;  
> Query OK, 0 rows affected (0.00 sec)  
> mysql> show grants for “liang01”@“localhost”;  
> ±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+  
> | Grants for liang01@localhost |  
> ±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+  
> | GRANT 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 ON *.* TO ‘liang01’@‘localhost’ |  
> ±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+  
> 1 row in set (0.00 sec)

### 2.3删除用户 ###

> mysql> drop user “liang01”@“localhost”;  
> Query OK, 0 rows affected (0.00 sec)