mysql基础
- mysql的程序组成
1.1 mysql工具使用
1.2 服务器监听的两种socket地址 - mysql数据库操作
2.1 DDL操作
2.1.1 数据库操作
2.1.2 表操作
2.1.3 用户操作
2.1.4 查看命令SHOW
2.1.5 获取帮助
2.2 DML操作
2.2.1 INSERT语句
2.2.2 SELECT语句
2.2.3 update语句
2.2.4 delete语句
2.2.5 truncate语句
2.3 DCL操作
2.3.1 创建授权grant
2.3.2 查看授权
2.3.3 取消授权REVOKE
1.mysql的程序组成
- 客户端
- mysql：CLI交互式客户端程序
- mysql_secure_installation：安全初始化，强烈建议安装完以后执行此命令
- mysqldump：mysql备份工具
- mysqladmin
- 服务器端
- mysqld
1.1 mysql工具使用
//语法：mysql [OPTIONS] [database]
//常用的OPTIONS：
-uUSERNAME //指定用户名，默认为root
-hHOST //指定服务器主机，默认为localhost，推荐使用ip地址
-pPASSWORD //指定用户的密码
[root@localhost ~]# mysql -uroot -pwyn123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.38 MySQL Community Server (GPL)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
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> exit
Bye
[root@localhost ~]# mysql -uroot -pwyn123 -hlocalhost
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.38 MySQL Community Server (GPL)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
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> exit
Bye
-P# //指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307
-V //查看当前使用的mysql版本
[root@localhost ~]# mysql -V
mysql Ver 14.14 Distrib 5.7.38, for Linux (x86_64) using EditLine wrapper
-e //不登录mysql执行sql语句后退出，常用于脚本
[root@localhost ~]# mysql -uroot -pwangyani123! -h127.0.0.1
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.38 MySQL Community Server (GPL)
Copyright (c) 2000, 2022, 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>
//注意，不推荐直接在命令行里直接用-pPASSWORD的方式登录，而是使用-p选项，然后交互式输入密码
[root@localhost ~]# mysql -uroot -p -h127.0.0.1
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.38 MySQL Community Server (GPL)
Copyright (c) 2000, 2022, 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>
[root@localhost ~]# mysql -uroot -p -h 127.0.0.1 -e 'SHOW DATABASES;'
Enter password:
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| runtime |
| sys |
+--------------------+
1.2 服务器监听的两种socket地址
| socket类型 | 说明 |
|---|---|
| ip socket | 默认监听在tcp的3306端口，支持远程通信 |
| unix sock | 监听在sock文件上(/tmp/mysql.sock，/var/lib/mysql/mysql.sock） 仅支持本地通信server地址只能是：localhost，127.0.0.1 |
2. mysql数据库操作
2.1 DDL操作
2.1.1 数据库操作
//创建数据库
//语法：CREATE DATABASE [IF NOT EXISTS] ‘DB_NAME’;
//创建数据库runtime
mysql> CREATE DATABASE IF NOT EXISTS runtime;
Query OK, 1 row affected (0.00 sec)
//查看当前数据库有哪些表
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.03 sec)
[root@localhost ~]# mysql -uroot -pwyn123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.38 MySQL Community Server (GPL)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.03 sec)
mysql> create database runtime;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| runtime |
| sys |
+--------------------+
5 rows in set (0.00 sec)
//删除数据库
//语法：DROP DATABASE [IF EXISTS] ‘DB_NAME’;
//删除数据库runtime
mysql> DROP DATABASE IF EXISTS runtime;
Query OK, 0 rows affected (0.00 sec)
mysql> drop database runtime;
Query OK, 0 rows affected (0.03 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
2.1.2 表操作
//创建表
//语法：CREATE TABLE table_name (col1 datatype 修饰符,col2 datatype 修饰符) ENGINE=’存储引擎类型’;
//在数据库runtime里创建表linux
mysql> create database runtime;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| runtime |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use runtime; //进入runtime数据库
Database changed
mysql> create table linux(id int not null,name varchar(10) not null,age tinyint(3));
Query OK, 0 rows affected (0.03 sec)
mysql> show tables;
+-------------------+
| Tables_in_runtime |
+-------------------+
| linux |
+-------------------+
1 row in set (0.00 sec)
//查看当前数据库有哪些表
mysql> show tables;
+-------------------+
| Tables_in_runtime |
+-------------------+
| linux |
+-------------------+
1 row in set (0.00 sec)
//删除表
//语法：DROP TABLE [ IF EXISTS ] ‘table_name’;
//删除表linux
mysql> drop table linux;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
Empty set (0.00 sec)
2.1.3 用户操作
mysql用户帐号由两部分组成，如’USERNAME’@’HOST’，表示此USERNAME只能从此HOST上远程登录
这里（’USERNAME’@’HOST’）的HOST用于限制此用户可通过哪些主机远程连接mysql程序，其值可为：
- IP地址，如：172.16.12.129
- 通配符
- %：匹配任意长度的任意字符，常用于设置允许从任何主机登录
- _：匹配任意单个字符
//数据库用户创建
//语法：CREATE USER ‘username’@’host’ [IDENTIFIED BY ‘password’];
//创建数据库用户haha
mysql> create user 'haha'@'localhost' identified by 'wangyani123';
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
//使用新创建的用户和密码登录
[root@localhost ~]# mysql -uhaha -pwangyani123 -hlocalhost
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.38 MySQL Community Server (GPL)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
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>
//删除数据库用户
//语法：DROP USER ‘username’@’host’;
mysql> drop user 'haha'@'localhost';
Query OK, 0 rows affected (0.00 sec)
2.1.4 查看命令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 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| tis620 | TIS620 Thai | tis620_thai_ci | 1 |
| euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| cp1250 | Windows Central European | cp1250_general_ci | 1 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| cp866 | DOS Russian | cp866_general_ci | 1 |
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
| macce | Mac Central European | macce_general_ci | 1 |
| macroman | Mac West European | macroman_general_ci | 1 |
| cp852 | DOS Central European | cp852_general_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |
| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| utf16 | UTF-16 Unicode | utf16_general_ci | 4 |
| utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 |
| cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| cp1257 | Windows Baltic | cp1257_general_ci | 1 |
| utf32 | UTF-32 Unicode | utf32_general_ci | 4 |
| binary | Binary pseudo charset | binary | 1 |
| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
| gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 |
+———-+———————————+———————+——–+
41 rows in set (0.00 sec)
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.01 sec)
mysql> show databases; //查看数据库信息
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| performance_schema |
| runtime |
| sys |
+——————–+
5 rows in set (0.00 sec)
mysql> show tables from runtime; //不进入某数据库而列出其包含的所有表
+——————-+
| Tables_in_runtime |
+——————-+
| linux |
+——————-+
1 row in set (0.00 sec)
//查看表结构
//语法：DESC [db_name.]table_name;
mysql> desc runtime.linux;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(10) | NO | | NULL | |
| age | tinyint(3) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.02 sec)
//查看某表的创建命令
//语法：SHOW CREATE TABLE table_name;
mysql> show create table runtime.linux;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| linux | CREATE TABLE `linux` (
`id` int(11) NOT NULL,
`name` varchar(10) NOT NULL,
`age` tinyint(3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
//查看某表的状态
//语法：SHOW TABLE STATUS LIKE ‘table_name’\G
mysql> use runtime; //进入数据库runtime
Database changed
mysql> show table status like 'linux'\G //查看linux表的状态
*************************** 1. row ***************************
Name: linux
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-07-25 19:03:37
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
2.1.5 获取帮助
//获取命令使用帮助
//语法：HELP keyword;
mysql> HELP CREATE TABLE; //获取创建表的帮助
Name: 'CREATE TABLE'
Description:
Syntax:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_options]
[partition_options]
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
[table_options]
[partition_options]
[IGNORE | REPLACE]
[AS] query_expression
......
......
2.2 DML操作
DML操作包括增(INSERT)、删(DELETE)、改(UPDATE)、查(SELECT)，均属针对表的操作。
2.2.1 INSERT语句
//DML操作之增操作insert
//语法：INSERT [INTO] table_name [(column_name,…)] {VALUES | VALUE} (value1,…),(…),…
mysql> use runtime;
Database changed
mysql> insert into linux(id,name,age) value(1,'tom',20); //一次插入一条记录
Query OK, 1 row affected (0.00 sec)
mysql> insert into linux(id,name,age) values(2,'harry',22),(3,'alice',24),(4,'jerry',21),(6,'xiaoyu',27),(7,'baibai',21),(8,'xiaowwu',26); //一次插入多条记录
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
2.2.2 SELECT语句
字段column表示法
| 表示符 | 代表什么？ |
|---|---|
| * | 所有字段 |
| as | 字段别名，如col1 AS alias1 当表名很长时用别名代替 |
条件判断语句WHERE
| 操作类型 | 常用操作符 |
|---|---|
| 操作符 | <，>=，<=，=，!= BETWEEN column# AND column# LIKE：模糊匹配 RLIKE：基于正则表达式进行模式匹配 IS NOT NULL：非空 IS NULL：空 |
| 条件逻辑操作 | AND OR NOT |
mysql> select * from linux;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | tom | 20 |
| 2 | harry | 22 |
| 3 | alice | 24 |
| 4 | jerry | 21 |
| 6 | xiaoyu | 27 |
| 7 | baibai | 21 |
| 8 | xiaowu | 26 |
+----+--------+------+
7 rows in set (0.01 sec)
mysql> select name from linux;
+--------+
| name |
+--------+
| tom |
| harry |
| alice |
| jerry |
| xiaoyu |
| baibai |
| xiaowu |
+--------+
7 rows in set (0.00 sec)
mysql> select * from linux where age >=25;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 6 | xiaoyu | 27 |
| 8 | xiaowu | 26 |
+----+--------+------+
2 rows in set (0.01 sec)
mysql> select * from linux where age >= 25 and name = 'xiaowu';
+----+--------+------+
| id | name | age |
+----+--------+------+
| 8 | xiaowu | 26 |
+----+--------+------+
1 row in set (0.00 sec)
mysql> select * from linux where age !=21;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | tom | 20 |
| 2 | harry | 22 |
| 3 | alice | 24 |
| 6 | xiaoyu | 27 |
| 8 | xiaowu | 26 |
+----+--------+------+
5 rows in set (0.00 sec)
mysql> select * from linux where age < 21;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | tom | 20 |
+----+------+------+
1 row in set (0.00 sec)
mysql> select * from linux where age between 22 and 26;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 2 | harry | 22 |
| 3 | alice | 24 |
| 8 | xiaowu | 26 |
+----+--------+------+
3 rows in set (0.00 sec)
mysql> select * from linux where name like '%xi%';
+----+--------+------+
| id | name | age |
+----+--------+------+
| 6 | xiaoyu | 27 |
| 8 | xiaowu | 26 |
+----+--------+------+
2 rows in set (0.01 sec)
mysql> select * from linux where age is not null;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | tom | 20 |
| 2 | harry | 22 |
| 3 | alice | 24 |
| 4 | jerry | 21 |
| 6 | xiaoyu | 27 |
| 7 | baibai | 21 |
| 8 | xiaowu | 26 |
+----+--------+------+
7 rows in set (0.00 sec)
mysql> insert into linux(id,name,age) value(9,'wuwubu',NULL);
Query OK, 1 row affected (0.00 sec)
mysql> select * from linux;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | tom | 20 |
| 2 | harry | 22 |
| 3 | alice | 24 |
| 4 | jerry | 21 |
| 6 | xiaoyu | 27 |
| 7 | baibai | 21 |
| 8 | xiaowu | 26 |
| 9 | wuwubu | NULL |
+----+--------+------+
8 rows in set (0.01 sec)
mysql> select * from linux where age is null;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 9 | wuwubu | NULL |
+----+--------+------+
1 row in set (0.00 sec)
mysql> select * from linux where id = '4' and name = 'jerry';
+----+-------+------+
| id | name | age |
+----+-------+------+
| 4 | jerry | 21 |
+----+-------+------+
1 row in set (0.00 sec)
mysql> select * from linux where id = '4' or age = '21';
+----+--------+------+
| id | name | age |
+----+--------+------+
| 4 | jerry | 21 |
| 7 | baibai | 21 |
+----+--------+------+
2 rows in set (0.00 sec)
mysql> select * from linux where not name = 'baibai';
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | tom | 20 |
| 2 | harry | 22 |
| 3 | alice | 24 |
| 4 | jerry | 21 |
| 6 | xiaoyu | 27 |
| 8 | xiaowu | 26 |
| 9 | wuwubu | NULL |
+----+--------+------+
7 rows in set (0.00 sec)
ORDER BY：排序，默认为升序（ASC）
| ORDER BY语句 | 意义 |
|---|---|
| ORDER BY ‘column_name’ | 根据column_name进行升序排序 |
| ORDER BY ‘column_name’ DESC | 根据column_name进行降序排序 |
| ORDER BY ’column_name’ LIMIT 2 | 根据column_name进行升序排序并只取前2个结果 |
| ORDER BY ‘column_name’ LIMIT 1,2 | 根据column_name进行升序排序并且略过第1个结果取后面的2个结果 |
//DML操作之查操作select
//语法：SELECT column1,column2,… FROM table_name [WHERE clause] [ORDER BY ‘column_name’ [DESC]] [LIMIT [m,]n];
mysql> select * from linux order by age;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 9 | wuwubu | NULL |
| 1 | tom | 20 |
| 4 | jerry | 21 |
| 7 | baibai | 21 |
| 2 | harry | 22 |
| 3 | alice | 24 |
| 8 | xiaowu | 26 |
| 6 | xiaoyu | 27 |
+----+--------+------+
8 rows in set (0.00 sec)
mysql> select * from linux order by age desc;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 6 | xiaoyu | 27 |
| 8 | xiaowu | 26 |
| 3 | alice | 24 |
| 2 | harry | 22 |
| 4 | jerry | 21 |
| 7 | baibai | 21 |
| 1 | tom | 20 |
| 9 | wuwubu | NULL |
+----+--------+------+
8 rows in set (0.00 sec)
mysql> select * from linux order by age limit 2;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 9 | wuwubu | NULL |
| 1 | tom | 20 |
+----+--------+------+
2 rows in set (0.00 sec)
mysql> select * from linux order by age limit 1,2;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | tom | 20 |
| 7 | baibai | 21 |
+----+--------+------+
2 rows in set (0.00 sec)
2.2.3 update语句
//DML操作之改操作update
//语法：UPDATE table_name SET column1 = new_value1[,column2 = new_value2,…] [WHERE clause] [ORDER BY ‘column_name’ [DESC]] [LIMIT [m,]n];
mysql> select * from linux;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | tom | 20 |
| 2 | harry | 22 |
| 3 | alice | 24 |
| 4 | jerry | 21 |
| 6 | xiaoyu | 27 |
| 7 | baibai | 21 |
| 8 | xiaowu | 26 |
| 9 | wuwubu | NULL |
+----+--------+------+
8 rows in set (0.00 sec)
mysql> update linux set age = 24 where name = 'wuwubu';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from linux where name = 'wuwubu';
+----+--------+------+
| id | name | age |
+----+--------+------+
| 9 | wuwubu | 24 |
+----+--------+------+
1 row in set (0.00 sec)
2.2.4 delete语句
//DML操作之删操作delete
//语法：DELETE FROM table_name [WHERE clause] [ORDER BY ‘column_name’ [DESC]] [LIMIT [m,]n];
mysql> select * from linux;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | tom | 20 |
| 2 | harry | 22 |
| 3 | alice | 24 |
| 4 | jerry | 21 |
| 6 | xiaoyu | 27 |
| 7 | baibai | 21 |
| 8 | xiaowu | 26 |
| 9 | wuwubu | 24 |
+----+--------+------+
8 rows in set (0.00 sec)
mysql> delete from linux where id = 9; //删除某条记录
Query OK, 1 row affected (0.00 sec)
mysql> select * from linux;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | tom | 20 |
| 2 | harry | 22 |
| 3 | alice | 24 |
| 4 | jerry | 21 |
| 6 | xiaoyu | 27 |
| 7 | baibai | 21 |
| 8 | xiaowu | 26 |
+----+--------+------+
7 rows in set (0.00 sec)
mysql> delete from linux; //删除整张表的内容
Query OK, 7 rows affected (0.01 sec)
mysql> select * from linux;
Empty set (0.00 sec)
mysql> desc linux;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(10) | NO | | NULL | |
| age | tinyint(3) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
2.2.5 truncate语句
truncate与delete的区别：
| 语句类型 | 特点 |
|---|---|
| delete | DELETE删除表内容时仅删除内容，但会保留表结构 DELETE语句每次删除一行，并在事务日志中为所删除的每行记录一项 可以通过回滚事务日志恢复数据 非常占用空间 |
| truncate | 删除表中所有数据，且无法恢复表结构、约束和索引等保持不变，新添加的行计数值重置为初始值 执行速度比DELETE快，且使用的系统和事务日志资源少 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志记录页的释放 对于有外键约束引用的表，不能使用TRUNCATE TABLE删除数据 不能用于加入了索引视图的表 |
//语法：TRUNCATE table_name;
mysql> select * from linux;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | tom | 20 |
| 2 | harry | 22 |
| 3 | alice | 24 |
| 4 | jerry | 21 |
| 6 | xiaoyu | 27 |
| 7 | baibai | 21 |
| 8 | xiaowu | 26 |
+----+--------+------+
7 rows in set (0.00 sec)
mysql> truncate linux;
Query OK, 0 rows affected (0.05 sec)
mysql> select * from linux;
Empty set (0.01 sec)
mysql> desc linux;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(10) | NO | | NULL | |
| age | tinyint(3) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
2.3 DCL操作
2.3.1 创建授权grant
权限类型(priv_type)
| 权限类型 | 代表什么？ |
|---|---|
| ALL | 所有权限 |
| SELECT | 读取内容的权限 |
| INSERT | 插入内容的权限 |
| UPDATE | 更新内容的权限 |
| DELETE | 删除内容的权限 |
指定要操作的对象db_name.table_name
| 表示方式 | 意义 |
|---|---|
| . | 所有库的所有表 |
| db_name | 指定库的所有表 |
| db_name.table_name | 指定库的指定表 |
WITH GRANT OPTION：被授权的用户可将自己的权限副本转赠给其他用户，说白点就是将自己的权限完全复制给另一个用户。不建议使用。
GRANT priv_type,… ON [object_type] db_name.table_name TO ‘username’@’host’ [IDENTIFIED BY ‘password’] [WITH GRANT OPTION];
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| runtime |
| sys |
+--------------------+
5 rows in set (0.00 sec)
//授权tom用户在数据库本机上登录访问所有数据库
mysql> grant all on *.* to 'tom'@'localhost' identified by 'wangyani123!';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> grant all on *.* to 'tom'@'127.0.0.1' identified by 'wangyani123!';
Query OK, 0 rows affected, 1 warning (0.00 sec)
//授权tom用户在192.168.56.166上远程登录访问runtime数据库
mysql> grant all on runtime.* to 'tom'@'192.168.56.166' identified by 'wangyani123!';
Query OK, 0 rows affected, 1 warning (0.00 sec)
//授权tom用户在所有位置上远程登录访问runtime数据库
mysql> grant all on *.* to 'tom'@'%' identified by 'wangyani123!';
Query OK, 0 rows affected, 1 warning (0.00 sec)
2.3.2 查看授权
//查看当前登录用户的授权信息
mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)
//查看指定用户tom的授权信息
mysql> show grants for tom;
+------------------------------------------+
| Grants for tom@% |
+------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'tom'@'%' |
+------------------------------------------+
1 row in set (0.00 sec)
mysql> show grants for 'tom'@'localhost';
+--------------------------------------------------+
| Grants for tom@localhost |
+--------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'tom'@'localhost' |
+--------------------------------------------------+
1 row in set (0.00 sec)
mysql> SHOW GRANTS FOR 'tom'@'127.0.0.1';
+-------------------------------------------------------+
| Grants for tom@127.0.0.1 |
+-------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'tom'@'127.0.0.1' |
+-------------------------------------------------------+
1 row in set (0.00 sec)
2.3.3 取消授权REVOKE
//语法：REVOKE priv_type,… ON db_name.table_name FROM ‘username’@’host’;
mysql> revoke all on *.* from 'tom'@'192.168.56.166';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
注意：mysql服务进程启动时会读取mysql库中的所有授权表至内存中：
- GRANT或REVOKE等执行权限操作会保存于表中，mysql的服务进程会自动重读授权表，并更新至内存中
- 对于不能够或不能及时重读授权表的命令，可手动让mysql的服务进程重读授权表
mysql> FLUSH PRIVILEGES;
扫一扫
416
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
