mysql的增删改查和授权

用户操作

创建数据库用户

mysql> create user 'tengjia'@'%' identified by 'Passw0rd.';
Query OK, 0 rows affected (3.36 sec)

使用新创建的账户密码登录

[root@localhost ~]# mysql -utengjia -pPassw0rd.;
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 17
Server version: 8.0.24 MySQL Community Server - GPL

Copyright (c) 2000, 2021, 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 'tengjia'@'%';
Query OK, 0 rows affected (0.13 sec)

mysql> show character set;
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| binary   | Binary pseudo charset           | binary              |      1 |
| cp1250   | Windows Central European        | cp1250_general_ci   |      1 |
| cp1251   | Windows Cyrillic                | cp1251_general_ci   |      1 |
| cp1256   | Windows Arabic                  | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic                  | cp1257_general_ci   |      1 |
| cp850    | DOS West European               | cp850_general_ci    |      1 |
| cp852    | DOS Central European            | cp852_general_ci    |      1 |
| cp866    | DOS Russian                     | cp866_general_ci    |      1 |
| cp932    | SJIS for Windows Japanese       | cp932_japanese_ci   |      2 |
| dec8     | DEC West European               | dec8_swedish_ci     |      1 |
| eucjpms  | UJIS for Windows Japanese       | eucjpms_japanese_ci |      3 |
| euckr    | EUC-KR Korean                   | euckr_korean_ci     |      2 |
| gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
| gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
| gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
| geostd8  | GEOSTD8 Georgian                | geostd8_general_ci  |      1 |
| greek    | ISO 8859-7 Greek                | greek_general_ci    |      1 |
| hebrew   | ISO 8859-8 Hebrew               | hebrew_general_ci   |      1 |
| hp8      | HP West European                | hp8_english_ci      |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak      | keybcs2_general_ci  |      1 |
| koi8r    | KOI8-R Relcom Russian           | koi8r_general_ci    |      1 |
| koi8u    | KOI8-U Ukrainian                | koi8u_general_ci    |      1 |
| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
| latin5   | ISO 8859-9 Turkish              | latin5_turkish_ci   |      1 |
| latin7   | ISO 8859-13 Baltic              | latin7_general_ci   |      1 |
| macce    | Mac Central European            | macce_general_ci    |      1 |
| macroman | Mac West European               | macroman_general_ci |      1 |
| sjis     | Shift-JIS Japanese              | sjis_japanese_ci    |      2 |
| swe7     | 7bit Swedish                    | swe7_swedish_ci     |      1 |
| tis620   | TIS620 Thai                     | tis620_thai_ci      |      1 |
| ucs2     | UCS-2 Unicode                   | ucs2_general_ci     |      2 |
| ujis     | EUC-JP Japanese                 | ujis_japanese_ci    |      3 |
| utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |
| utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
| utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_0900_ai_ci  |      4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.00 sec)

查看当前数据库支持的所有存储引擎

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.14 sec)

查看数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| hanao              |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| tengjia            |
+--------------------+
6 rows in set (1.65 sec)

查看表结构

mysql> desc tengjia.student;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int          | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | NO   |     | NULL    |                |
| age   | tinyint      | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.34 sec)

查看表的创建命令

mysql> show create table tengjia.student;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                       |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `age` tinyint DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.49 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: 1
    Create_time: 2021-04-29 07:36:42
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

获取帮助

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

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    { LIKE old_tbl_name | (LIKE old_tbl_name) }

create_definition: {
    col_name column_definition
  | {INDEX | KEY} [index_name] [index_type] (key_part,...)
      [index_option] ...

DML语法

mysql> insert student(name,age) values('tengjia',18),('ha',18);
Query OK, 2 rows affected (0.14 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  1 | tengjia |   18 |
|  2 | ha      |   18 |
+----+---------+------+
2 rows in set (0.00 sec)

select查询语句
字段表示法
|表示符| 含义 |
|-*-|-所有字段-|
| as | 字段别名 |

条件判断语句where
操作符
|>，<，>=，<=，=，!=
between [column] and [column]
like：模糊匹配
rlike：基于正则表达式进行模式匹配
is not null：非空
is null：空

条件逻辑操作
and or not

order by：排序，默认为升序（ASC）

order by [column]
根据字段进行升序排序
order by [column]
根据字段进行降序排序
order by [column] limit 2
根据字段进行升序排序并只取前两行
order by [column] limit 1,2
更加字段进行升序排序并且略过第1个结果取后面的2个结果

mysql> select * from student where age between 5 and 30 order by age desc limit 1,7;
+----+------+------+
| id | name | age  |
+----+------+------+
|  2 | ha   |   18 |
+----+------+------+
1 row in set (0.00 sec)

update语法

mysql> update student set age = 25 where name = 'ha';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student;
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  1 | tengjia |   18 |
|  2 | ha      |   25 |
+----+---------+------+
2 rows in set (0.01 sec)

delete语法

mysql>  delete from student where name = 'ha' and id = 5;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student;
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  1 | tengjia |   18 |
|  2 | ha      |   25 |
+----+---------+------+
2 rows in set (0.00 sec)

truncate语法
truncate与delete的区别：
delete
delete删除表内容时仅删除内容，但会保留表结构
delete语句每次删除一行
并在事务日志中为所删除的每行记录一项
可以通过回滚事务日志恢复数据
非常占用空间
truncate
删除表中所有数据，且无法恢复
表结构、约束和索引等保持不变，新添加的行计数值重置为初始值
执行速度比delete快，且使用的系统和事务日志资源少
通过释放存储表数据所用的数据页来删除数据
只在事务日志中记录页的释放
对于有外键约束引用的表，不能使用truncate table 删除数据
不能用于加入了索引的视图的表

mysql> select * from student;
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  1 | tengjia |   18 |
|  2 | ha      |   25 |
+----+---------+------+
2 rows in set (0.00 sec)

mysql> select * from student;
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  1 | tengjia |   18 |
|  2 | ha      |   25 |
+----+---------+------+
2 rows in set (0.00 sec)

授权root用户在所有位置上远程登录访问所有数据库，并设置登录密码
权限类型 含义
all 所有权限
select 读取内容的权限
insert 插入内容的权限
update 修改内容的权限
delete 删除内容的权限

mysql> grant all on *.* to 'root'@'%' identified by 'Passw0rd.';
Query OK, 0 rows affected, 1 warning (0.00 sec)

查看当前用户的授权信息

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)

查看指定用户（tengjia）的授权信息

mysql> show grants for 'tengjia'@'%';
+-------------------------------------+
| Grants for tengjia@%                |
+-------------------------------------+
| GRANT USAGE ON *.* TO `tengjia`@`%` |
+-------------------------------------+
1 row in set (0.00 sec)

取消用户（tengjia）的授权REVOKE

mysql> revoke all on *.* from 'tengjia'@'%';
Query OK, 0 rows affected (0.02 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.22 sec)

mysql服务进程启动时会读取mysql库中的所有授权表到内存中

grant或revoke等执行权限操作会保存于表中

mysql服务进程会自动重读授权表，并更新到内存中