mysql基础2

已于 2022-04-22 02:29:59 修改
阅读量478 收藏
点赞数
文章标签： linux mysql
于 2022-04-20 20:44:15 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_69041923/article/details/124306773
版权

mysql基础2

文章目录

1.服务器监听的两种socket地址

socket类型说明
IP socket默认监听在TCP的3306端口，支持远程通信
Unix sock监听在sock文件上（/tmp/mysql.sock,/var/lib/mysql/msql.sock）
仅支持本地通信
server地址只能是：localhost，127.0.0.1
[root@localhost ~]# ls /var/lib/mysql
auto.cnf         ib_logfile0         private_key.pem
ca-key.pem       ib_logfile1         public_key.pem
ca.pem           ibtmp1              server-cert.pem
client-cert.pem  mysql               server-key.pem
client-key.pem   mysql.sock          sys
ib_buffer_pool   mysql.sock.lock
ibdata1          performance_schema

2. 数据库操作

2.1 创建数据库

[root@localhost ~]# mysql -uroot -pRunTime123!
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

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

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

2.2 删除数据库

mysql> drop database school;
Query OK, 0 rows affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

3. 表操作

3.1 创建表

mysql> create database school;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use school;		//进入表
Database changed

mysql> show tables;		//查看表
Empty set (0.00 sec)



创建表：
mysql> CREATE TABLE info(name varchar(50) not null,age tinyint,sex varchar(6) not null,salary float);
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;		//查看表
+------------------+
| Tables_in_school |
+------------------+
| info             |
+------------------+
1 row in set (0.00 sec)

mysql> DESC info;		//查看表的结构
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name   | varchar(50) | NO   |     | NULL    |       |
| age    | tinyint(4)  | YES  |     | NULL    |       |
| sex    | varchar(6)  | NO   |     | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

3.2 删除表

mysql> drop table info;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
Empty set (0.00 sec)

4. 用户操作

root@10.10.10.1

root@10.10.10._

root@10.10.10.%

root@‘%’

4.1 数据库用户创建

mysql> create user tom@192.168.163.1 identified by 'RunTime123!';
Query OK, 0 rows affected (0.00 sec)

4.2 数据库用户删除

mysql> drop user tom@192.168.163.1;
Query OK, 0 rows affected (0.00 sec)

4.3 数据库用户所在位置

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
31 rows in set (0.00 sec)

mysql> select * from user\G

5. 查看命令SHOW

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 |
| cp850    | DOS West European               | cp850_general_ci    |      1 |
| hp8      | HP West European                | hp8_english_ci      |      1 |

......



mysql> SHOW ENGINES;		//查看当前数据库支持的所有存储引擎
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

查看某表的创建命令

mysql> SHOW CREATE TABLE school.info

mysql> create table student(id int not null,name varchar(30),age tinyint);
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> create table student1(id int not null,name varchar(30),salary float);
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> create table student2(id int not null,name varchar(30),sex varchar(6));
Query OK, 0 rows affected (0.01 sec)

查看表的状态：（在哪个数据库创建的，就在哪个数据库查看）
mysql> SHOW TABLE STATUS LIKE 'student%'\G		//%相当于通配符
*************************** 1. row ***************************
           Name: student
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2022-04-19 20:22:05
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
*************************** 2. row ***************************
           Name: student1
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2022-04-19 20:22:05
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
*************************** 3. row ***************************
           Name: student2
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2022-04-19 22:14:36
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
3 rows in set (0.00 sec)

6. 获取帮助

mysql> help create database;	//查看数据库的帮助文档
mysql> help create table;		//查看表的帮助文档

7. DML操作

DML操作包括增（INSERT）、删（DELETE）、改（UPDATE）、查（SELECT），均属针对表的操作。

7.1 INSERT语句（增）

mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
| age   | tinyint(4)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> select * from student;
Empty set (0.00 sec)

mysql> insert student value(1,'tom',15);
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | tom  |   15 |
+----+------+------+
1 row in set (0.00 sec)

mysql> insert student values(2,'jerry',16),(3,'zhangsan',20),(4,'lis,i',18),(5,'wangwu',19),(6,'qianliu',22);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | tom      |   15 |
|  2 | jerry    |   16 |
|  3 | zhangsan |   20 |
|  4 | lisi     |   18 |
|  5 | wangwu   |   19 |
|  6 | qianliu  |   22 |
+----+----------+------+
6 rows in set (0.00 sec)

mysql> insert student value(1,'tom',15);
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | tom      |   15 |
|  2 | jerry    |   16 |
|  3 | zhangsan |   20 |
|  4 | lisi     |   18 |
|  5 | wangwu   |   19 |
|  6 | qianliu  |   22 |
|  1 | tom      |   15 |
+----+----------+------+
7 rows in set (0.00 sec)


mysql> use school;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> create table info(id int not null primary key,name varchar(30)),age tinyint);
Query OK, 0 rows affected (0.00 sec)

mysql> desc info;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
| age   | tinyint(4)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert info values(1,'tom',15),(2,'jerry',16);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from info;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | tom   |   15 |
|  2 | jerry |   16 |
+----+-------+------+
2 rows in set (0.00 sec)

mysql> insert info values(1,'tom',15);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'		//主键的唯一性

主键自动增长的配置方法

mysql> create table info1(id int not null primary key auto_increment,name varchar(30),age tinyint);
Query OK, 0 rows affected (0.01 sec)

mysql> desc info1;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(30) | YES  |     | NULL    |                |
| age   | tinyint(4)  | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> insert info1(name,age) values('tom',15),('jerry',16);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from info1;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | tom   |   15 |
|  2 | jerry |   16 |
+----+-------+------+
2 rows in set (0.00 sec)

mysql> show create table info1;		//查看该表如何创建的
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                    |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| info1 | CREATE TABLE `info1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

7.2 update语句（改）

mysql> select * from info1;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | tom   |   15 |
|  2 | jerry |   16 |
+----+-------+------+
2 rows in set (0.00 sec)

mysql> update info1 set age=18 where id = 2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from info1;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | tom   |   15 |
|  2 | jerry |   18 |
+----+-------+------+
2 rows in set (0.00 sec)

7.3 SELECT语句（查）

表示符代表什么？
*所有字段
as字段别名，如col1 AS alias1
当表名很长时用别名代替
mysql> select name,age from info1;
+-------+------+
| name  | age  |
+-------+------+
| tom   |   15 |
| jerry |   18 |
+-------+------+
2 rows in set (0.00 sec)

mysql> select name as '姓名',age as '年龄' from info1;
+--------+--------+
| 姓名   | 年龄   |
+--------+--------+
| tom    |     15 |
| jerry  |     18 |
+--------+--------+
2 rows in set (0.00 sec)

mysql> select name as n,age as a from info1;
+-------+------+
| n     | a    |
+-------+------+
| tom   |   15 |
| jerry |   18 |
+-------+------+
2 rows in set (0.00 sec)


mysql> insert info1(name,age) values('zhangsan',16),('wangwu',19),('qianliu',20),('zhangsan',20);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> insert info1(name) value('lisi');
Query OK, 1 row affected (0.01 sec)

mysql> select * from info1;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | tom      |   15 |
|  2 | jerry    |   18 |
|  3 | zhangsan |   16 |
|  4 | wangwu   |   19 |
|  5 | qianliu  |   20 |
|  6 | zhangsan |   20 |
|  7 | lisi     | NULL |
+----+----------+------+
7 rows in set (0.00 sec)

mysql> select * from info1 where age>19;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  5 | qianliu  |   20 |
|  6 | zhangsan |   20 |
+----+----------+------+
2 rows in set (0.00 sec)

mysql> select * from info1 where age>=19;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  4 | wangwu   |   19 |
|  5 | qianliu  |   20 |
|  6 | zhangsan |   20 |
+----+----------+------+
3 rows in set (0.00 sec)

mysql> select * from info1 where age between 18 and 20;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  2 | jerry    |   18 |
|  4 | wangwu   |   19 |
|  5 | qianliu  |   20 |
|  6 | zhangsan |   20 |
+----+----------+------+
4 rows in set (0.00 sec)

mysql> select * from info1 where age is NULL;
+----+------+------+
| id | name | age  |
+----+------+------+
|  7 | lisi | NULL |
+----+------+------+
1 row in set (0.00 sec)

mysql> select * from info1 where age is null;
+----+------+------+
| id | name | age  |
+----+------+------+
|  7 | lisi | NULL |
+----+------+------+
1 row in set (0.00 sec)

mysql> select * from info1 where age is not null;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | tom      |   15 |
|  2 | jerry    |   18 |
|  3 | zhangsan |   16 |
|  4 | wangwu   |   19 |
|  5 | qianliu  |   20 |
|  6 | zhangsan |   20 |
+----+----------+------+
6 rows in set (0.00 sec)

mysql> select * from info1 where name='zhangsan';
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  3 | zhangsan |   16 |
|  6 | zhangsan |   20 |
+----+----------+------+
2 rows in set (0.00 sec)

mysql> select * from info1 where name='zhangsan' and age=20;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  6 | zhangsan |   20 |
+----+----------+------+
1 row in set (0.00 sec)

mysql> select * from info1 where name='zhangsan' or age=20;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  3 | zhangsan |   16 |
|  5 | qianliu  |   20 |
|  6 | zhangsan |   20 |
+----+----------+------+
3 rows in set (0.00 sec)

mysql> select * from info1 where not age=20;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | tom      |   15 |
|  2 | jerry    |   18 |
|  3 | zhangsan |   16 |
|  4 | wangwu   |   19 |
+----+----------+------+
4 rows in set (0.00 sec)

mysql> select * from info1 where name='zhangsan' and not age=20;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  3 | zhangsan |   16 |
+----+----------+------+
1 row in set (0.00 sec)
排序
mysql> select * from info1 order by age;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  7 | lisi     | NULL |
|  1 | tom      |   15 |
|  3 | zhangsan |   16 |
|  2 | jerry    |   18 |
|  4 | wangwu   |   19 |
|  5 | qianliu  |   20 |
|  6 | zhangsan |   20 |
+----+----------+------+
7 rows in set (0.00 sec)

mysql> select * from info1 order by age desc;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  5 | qianliu  |   20 |
|  6 | zhangsan |   20 |
|  4 | wangwu   |   19 |
|  2 | jerry    |   18 |
|  3 | zhangsan |   16 |
|  1 | tom      |   15 |
|  7 | lisi     | NULL |
+----+----------+------+
7 rows in set (0.01 sec)

mysql> select * from info1 order by age desc limit 1;
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  5 | qianliu |   20 |
+----+---------+------+
1 row in set (0.00 sec)

mysql> select name from info1 order by age desc limit 1;
+---------+
| name    |
+---------+
| qianliu |
+---------+
1 row in set (0.00 sec)

7.4 delete语句（删）

删除表里面的内容

先简单说下进入数据库，查看有哪些库，找到要删除的那张表所在的库，进去并打开该表
[root@localhost ~]# mysql -uroot -p'RunTime123!'
mysql> show databases;
mysql> use school;
mysql> show tables;
mysql> select * from info1;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | tom      |   15 |
|  2 | jerry    |   18 |
|  3 | zhangsan |   16 |
|  4 | wangwu   |   19 |
|  5 | qianliu  |   20 |
|  6 | zhangsan |   20 |
|  7 | lisi     | NULL |
+----+----------+------+
7 rows in set (0.00 sec)

mysql> delete from info1 where id=4;		//删除某一行
Query OK, 1 row affected (0.01 sec)

mysql> select * from info1;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | tom      |   15 |
|  2 | jerry    |   18 |
|  3 | zhangsan |   16 |
|  5 | qianliu  |   20 |
|  6 | zhangsan |   20 |
|  7 | lisi     | NULL |
+----+----------+------+
6 rows in set (0.00 sec)

mysql> delete from info1;			//删除表里内容所有
Query OK, 6 rows affected (0.00 sec)

内容被删，表还在：
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| info             |
| info1            |
+------------------+
2 rows in set (0.00 sec)

mysql> desc info1;		//查看表结构
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(30) | YES  |     | NULL    |                |
| age   | tinyint(4)  | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

8. truncate语句

truncate与delete的区别：

语句类型特点
deleteDELETE删除表内容时仅删除内容，但会保留表结构
DELETE语句每删除一行，就会在事务日志中记录一行
可以通过回滚事务日志恢复数据
非常占用空间
truncate删除表中所有数据，且无法恢复
表结构、约束和索引等保持不变，新添加的行计数值重置为初始值
执行速度比DELETE快，且使用的系统和事务日志资源少
通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放
对于有外键约束引用的表，不能使用TRUNCATE TABLE删除数据
不能用于加入了索引视图的表
mysql> insert info1(name,age) value('tom',15);
Query OK, 1 row affected (0.00 sec)

mysql> select * from info1;
+----+------+------+
| id | name | age  |
+----+------+------+
|  8 | tom  |   15 |
+----+------+------+
1 row in set (0.00 sec)
//这里发现，之前用delete删了东西之后，再添加内容，计数编号不会从1开始，而是往后继续排

mysql> truncate table info1;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from info1;
Empty set (0.00 sec)

mysql> insert info1(name,age) value('tom',15);
Query OK, 1 row affected (0.00 sec)

mysql> select * from info1;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | tom  |   15 |
+----+------+------+
1 row in set (0.00 sec)
//这里发现，用truncate删了东西之后，再添加内容，计数编号会从1开始重新排

ps：无论是用delete还是truncate，都是删除表的内容，而不是删表

9. DCL操作（数据控制语言，设置权限）

9.1 创建授权grant

常用权限类型（priv_type）
权限类型代表什么？
ALL所有权限
SELECT读取内容的权限
INSERT插入内容的权限
UPDATE更新内容的权限
DELETE删除内容的权限
mysql> grant all on *.* to 'kong'@'localhost' identified by 'RunTime123!';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> flush privileges;		//刷新表的权限
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'kong'@'localhost';		//查看权限
+---------------------------------------------------+
| Grants for kong@localhost                         |
+---------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'kong'@'localhost' |
+---------------------------------------------------+
1 row in set (0.00 sec)

9.2 取消授权REVOKE

mysql> revoke select on *.* from 'kong'@'localhost';
Query OK, 0 rows affected (0.00 sec)
//取消了查看select权限，还剩什么权限呢：
mysql> show grants for 'kong'@'localhost';
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for kong@localhost                                                                                                                                                                                                                                                                                                                              |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT 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 ON *.* TO 'kong'@'localhost' |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
//剩下的这些权限加上select权限，就构成了所有权限all

mysql> help alter table; //表修改的帮助文档

小孔学Linux
关注
mysql索引详解
学Java，找哪吒
07-07 10万+
MySQL的存储引擎架构将查询处理与数据的存储/提取相分离
Mysql数据库基础语句
m0_54594153的博客
09-11 3331
DDL语句可用于创建数据库对象（库，表，索引）删除数据库和表2.1 创建数据库和表—create#创建一个名为nba的数据库#创建一个名为james的表,（定义id不为空，名字不为空，年龄不为空，成绩可为空默认为“0”，主键为id）# 在任意库中创建nba库中的james表sql语言的分类DDL数据定义语言，用于创建和删除数据库表操作create（创建） drop（删除）DML：数据操作语言，用于管理表中的记录，对数据进行增，删，该，的操作。
mysql查看innodb版本_mysql中查看innodb版本的方法
weixin_39649405的博客
01-26 528
SELECT * FROM information_schema.plugins\Gmysql 5.6.3-m6 的innodb版本为 1.2.3查看版本方法,可以看到大版本SELECT * FROM information_schema.plugins;SELECT @@innodb_version;MySQL 5.5:MySQL Plugin Status Date5.5.18 1.1.8 G...
MySQL基础知识总结
徒挽的博客
11-03 3434
MySQL基础知识总结
【MySQL】数据库基础知识
热门推荐
m0_51064412的博客
05-20 5万+
文章目录一. 什么是数据库二. 为什么要有数据库三. 数据库分类四. 数据库的基本使用1. MySQL安装2. 检查MySQL后端服务器是否启动3. 连接MySQL服务器 一. 什么是数据库 数据库（database）是一整套的数据存储容灾解决方案。是用来组织、存储和管理数据的仓库。 当今世界是一个充满着数据的互联网世界，充斥着大量的数据。数据的来源有很多，比如出行记录、消费记录、浏览的网页、发送的消息等等。除了文本类型的数据，图像、音乐、声音都是数据。 为了方便管理互联网世界中的数据，就有了数据库管理系统
MySQL数据库基础
2021dragon的博客
11-30 6371
数据库是按照数据结构来组织、存储和管理数据的仓库，是一个长期存储在计算机内的、有组织的、可共享的、统一管理的大量数据的集合。虽然单纯的使用文件也可以存储数据，但会存在如下缺点：安全性问题：数据误操作后无法进行回滚。不利于数据的查询和管理：没有将存储的数据以某种数据结构组织起来。控制不方便：数据的控制需要用户自己来完成。不利于存储海量数据：数据量越大用户操控数据的成本越高。为了解决上述问题，于是专家们设计出了更利于管理数据的数据库。
【MySQL基础】MySQL基本操作详解
小杨的博客
10-29 3万+
大家好，我是小杨！今天我将详细的为大家介绍MySQL数据库的基本操作，希望大家能够从中收获多多！
Mysql基础（入门）
weixin_53150440的博客
09-03 7804
Mysql入门（一看就会）
MYSQL基础及性能优化
不才不才不不才的博客
07-23 1万+
如果user中的权限为Y,则不检查后面的db等的权限。读锁，当前session只能读本表，其他操作（写本表、或读写其他表）都报错，其他session对本表的写操作会阻塞。表示索引中使用的字节数，根据这个值可以判断索引的使用情况，特别是组合索引的时候，判断所有的索引字段是否被使用到。事务A多次读取同一个表中的数据，事务B在A读取的过程新增或删除了数据，导致A多次读到的数据记录数不一致。如果一定要使用，请使用覆盖索引。事务A多次读取同一数据，事务B在A读取的过程中对数据进行了修改，导致A多次读到的数据不一致。.
MySQL基础
qq_53149124的博客
05-20 2524
用户自定义变量通常以一个”@“开始，作用域只针对于当前会话有效。创建变量和更新变量SET @<变量名>=<值>;SET @<变量名>:=<值>;SELECT @<变量名>:=<值>;查看变量值SELECT @<变量名>;删除变量#删除变量只需要给变量设置为NULL值就行SET @<变量名>=NULL;CREATE FUNCTION <函数名>([<参数列表>]) RETURNS <返回值类型> [<characteristics>]BEGIN#其它计算或SQL操作...
MySQL基础PDF版
05-16
2. **MySQL基础** - **DBMS**：数据库管理系统（DataBase Management System），如MySQL，用于科学地组织和存储数据，高效地获取和维护数据。 - **SQL语句分类**：主要包括DDL（数据定义语言）、DML（数据操作语言...
MySQL数据库基础实例教程(第2版)教学大纲.pdf
06-24
《MySQL数据库基础实例教程(第2版)》是一门针对软件技术、移动互联等相关专业设计的专业必修课程，旨在培养学生对数据库应用系统的开发、管理和维护能力。课程通过项目模拟和职业体验式学习，使学生在理论与实践中...
康师傅mysql基础pdf
01-28
康师傅mysql基础pdf可能是一份教程或教材，涵盖了MySQL的基础知识，包括安装、配置、基本操作以及一些核心概念。在这里，我们将深入探讨MySQL的一些关键点。 1. **安装与配置**: MySQL的安装通常涉及下载安装包，...
mysql mysql基础介绍
09-24
MySQL 基础介绍 MySQL 是一种流行的开源关系型数据库管理系统（RDBMS），它使用 SQL（Structured Query Language）作为其操作语言。MySQL 具有高性能、可靠性和易于使用的特点，支持大量的并发用户连接，可以处理...
MySQL 基础教程参考手册
03-21
MySQL数据库开发的三十六条军规_石展_完整.pdf mysql容灾及自动化切换.pdf MySQL 性能优化最佳实践.pdf ...MySQL存储过程基础教程.pdf MySql 手册.pdf MySQL_中文参考手册.pdf Mysql双机热备安装手册(V).doc
linux 网络序
CodeHouse的博客
09-30 348
在将ip字符串转网络序存储时。根据整形二进制显示判断。
Linux 再入门整理：详解 /etc/fstab 文件
最新发布
一只奋斗的猪
10-01 649
`/etc/fstab` 文件是 Linux 系统中用于定义和管理文件系统的挂载信息的配置文件。它的作用是告诉系统在启动时，应该如何自动挂载各种文件系统。挂载是 Linux 操作系统中一种将存储设备与目录树关联的操作。通过挂载，存储设备中的文件可以通过目录访问。
在 Kali Linux 中安装 Impacket
weixin_44023460的博客
09-30 186
在安装 Impacket 之前，你需要确保安装了 Python 和一些必要的依赖。通常，Kali 已经预装了 Python。步骤 3：克隆 Impacket 仓库。步骤 4：进入 Impacket 目录。如果你看到帮助信息，说明安装成功。步骤 5：安装 Impacket。
【Linux实践】实验四：Shell实用功能及文件权限
Fulling的博客
09-25 780
实验内容 1、使用命令“cat /etc/named.conf”设置为别名named，然后再取消别名。 2、使用echo命令和输出重定向创建文本文件/root/nn，内容是hello，然后再使用追加重定向输入内容为word。 3、使用管道方式分页显示/etc/passwd的内容。 4、分别用文字设定法和数字设定法，对/root/ab文件设置权限，所有者为读取、写入和执行权限，同组用户为读取和写入权限，其他用户没有任何权限。 5、将文件/root/ab所有者更改为用户zhangsan。 6、将目录/root/
MySQL基础操作与管理指南
“mysql基础知识小结，包括启动、关闭、登录MySQL的方法，以及SQL语言的五大类别：DQL、DML、TPL、DCL、DDL，还有密码管理和root密码找回。” 在MySQL的基础知识中，了解如何正确操作MySQL服务器至关重要。首先，...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值