mysql的基础操作

mysql的基础操作

DDL操作

1.数据库操作

/创建数据库
//语法：CREATE DATABASE [IF NOT EXISTS] 'DB_NAME';
//创建数据库lch
mysql> CREATE DATABASE lch;
Query OK, 1 row affected (0.00 sec)

//查看当前实例有哪些数据库
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lch                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

//删除数据库
//语法：DROP DATABASE [IF EXISTS] 'DB_NAME';
mysql> DROP DATABASE IF EXISTS lch;
Query OK, 0 rows affected (0.00 sec)

//删除数据库lch
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

2.表操作

//创建表
//语法：CREATE TABLE table_name (col1 datatype 修饰符,col2 datatype 修饰符) ENGINE='存储引擎类型';
//先进入数据库
mysql> use lch;
Database changed   //进入成功
//创建表student
mysql> CREATE TABLE student (id int NOT NULL,name VARCHAR(100) NOT NULL,age tinyint);
Query OK, 0 rows affected (0.03 sec)

//查看当前数据库有哪些表
mysql> SHOW TABLES;
+---------------+
| Tables_in_lch |
+---------------+
| student       |
+---------------+
1 row in set (0.00 sec)

//删除表
//语法：DROP TABLE [ IF EXISTS ] 'table_name';
//删除表student
mysql> DROP TABLE student;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW TABLES;
Empty set (0.00 sec)

3.用户操作

mysql用户帐号由两部分组成，如'USERNAME'@'HOST'，表示此USERNAME只能从此HOST上远程登录
这里（'USERNAME'@'HOST'）的HOST用于限制此用户可通过哪些主机远程连接mysql程序，其值可为：

IP地址，如：172.16.12.129
通配符
%：匹配任意长度的任意字符，常用于设置允许从任何主机登录
_：匹配任意单个字符
//创建数据库用户
mysql> CREATE USER 'hui'@'192.168.200.8' identified by 'lch123!';
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye
//使用新创建的用户和密码登录
[root@lch ~]# mysql -uhui -p'lch123!' -h192.168.200.8
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 14
Server version: 5.7.37 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> quit
Bye
[root@lch ~]# mysql -uroot -p'lch123!'   //切换用户
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 15
Server version: 5.7.37 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 'hui'@'192.168.200.8';
Query OK, 0 rows affected (0.00 sec)

DML操作

1.表内容的增删改查

插入：insert into 表名（字段1，字段2） values（语句1，语句2），（语句1，语句2）；
修改：update 表名set 字段=更新值 where 判断条件；
删除：delete from 表名 where 判断条件 ；（只删除表的数据，不删除结构）
          delete from 表名（不包括缓存）；
           truncate 表名（删除表中所有数据，包括缓存）；
查看：select字段（*全部字段）from 表名；


//创建一个student的表
mysql> use lch;
Database changed
mysql> create table student(id int(11) not null primary key auto_increment,name varchar(100) not null,age tinyint(4));
Query OK, 0 rows affected (0.01 sec)

//查看表是否创建成功
mysql> desc student;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | NO   |     | NULL    |                |
| age   | tinyint(4)   | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.09 sec)

//增加表内容（insert）
mysql> insert student (name,age) values('tom',20),('jerry',18),('zhangshan',22),('lisi',15);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

//查看表内容（select）
mysql> select * from student;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | tom       |   20 |
|  2 | jerry     |   18 |
|  3 | zhangshan |   22 |
|  4 | lisi      |   15 |
+----+-----------+------+
4 rows in set (0.00 sec)

//删除表内容（detele）
mysql> delete from student where age=15;
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | tom       |   20 |
|  2 | jerry     |   18 |
|  3 | zhangshan |   22 |
+----+-----------+------+
3 rows in set (0.00 sec)

//修改表内容（update）
mysql> update student set age=50 where name='tom';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | tom       |   50 |
|  2 | jerry     |   18 |
|  3 | zhangshan |   22 |
+----+-----------+------+
3 rows in set (0.00 sec)

2.（alter）表结构的添加、删除、修改

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

mysql> select * from student;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | tom       |   50 |
|  2 | jerry     |   18 |
|  3 | zhangshan |   22 |
+----+-----------+------+
3 rows in set (0.00 sec)

添加表结构
语法：alter table table_name add column…
mysql> alter table student add column phone int(3);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+-----------+------+-------+
| id | name      | age  | phone |
+----+-----------+------+-------+
|  1 | tom       |   50 |  NULL |
|  2 | jerry     |   18 |  NULL |
|  3 | zhangshan |   22 |  NULL |
+----+-----------+------+-------+
3 rows in set (0.01 sec)

//在表中指定位置插入列
mysql> alter table student add column studentid int(3) after id;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+-----------+-----------+------+-------+
| id | studentid | name      | age  | phone |
+----+-----------+-----------+------+-------+
|  1 |      NULL | tom       |   50 |  NULL |
|  2 |      NULL | jerry     |   18 |  NULL |
|  3 |      NULL | zhangshan |   22 |  NULL |
+----+-----------+-----------+------+-------+
3 rows in set (0.00 sec)

//删除表结构
语法：alter table table_name drop column…
mysql> alter table student drop column studentid;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+-----------+------+-------+
| id | name      | age  | phone |
+----+-----------+------+-------+
|  1 | tom       |   50 |  NULL |
|  2 | jerry     |   18 |  NULL |
|  3 | zhangshan |   22 |  NULL |
+----+-----------+------+-------+
3 rows in set (0.00 sec)

//修改表结构
语法：alter table table_name modify…
mysql> desc student;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | NO   |     | NULL    |                |
| age   | tinyint(4)   | YES  |     | NULL    |                |
| phone | int(3)       | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> alter table student modify id varchar(40);
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

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

DCL操作

1.创建授权grant

权限类型(priv_type)

权限类型	代表什么？
ALL	所有权限
SELECT	读取内容的权限
INSERT	插入内容的权限
UPDATE	更新内容的权限
DELETE	删除内容的权限

指定要操作的对象db_name.table_name

表示方式	意义
.	所有库的所有表
db_name	指定库的所有表
db_name.table_name	指定库的指定表

WITH GRANT OPTION：被授权的用户可将自己的权限副本转赠给其他用户，说白点就是将自己的权限完全复制给另一个用户。不建议使用。

mysql> CREATE USER 'hui'@'192.168.200.8' identified by 'lch123!';
Query OK, 0 rows affected (0.00 sec)

//授权用户在数据库本机上登录访问所有数据库
mysql> grant all on *.* to 'hui'@'localhost' identified by 'lch123!';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> grant all on *.* to 'hui'@'192.168.200.8' identified by 'lch123!';
Query OK, 0 rows affected, 1 warning (0.00 sec)

//授权用户在192.168.200.7上远程登录访问数据库\
mysql> grant all on *.* to 'hui'@'192.168.200.7' identified by 'lch123!';
Query OK, 0 rows affected, 1 warning (0.00 sec)

//授权用户在所有位置上远程登录访问数据库
mysql> grant all on *.* to 'hui'@'%' identified by 'lch123!';
Query OK, 0 rows affected, 1 warning (0.00 sec)

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)

//查看指定用户的授权信息
mysql> SHOW GRANTS FOR hui;
+------------------------------------------+
| Grants for hui@%                         |
+------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'hui'@'%' |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW GRANTS FOR 'hui'@'localhost';
+--------------------------------------------------+
| Grants for hui@localhost                         |
+--------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'hui'@'localhost' |
+--------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW GRANTS FOR 'hui'@'192.168.200.8';
+------------------------------------------------------+
| Grants for hui@192.168.200.8                         |
+------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'hui'@'192.168.200.8' |
+------------------------------------------------------+
1 row in set (0.00 sec)

3.取消授权REVOKE

//语法：REVOKE priv_type,... ON db_name.table_name FROM 'username'@'host';
mysql> REVOKE ALL ON *.* FROM 'hui'@'192.168.200.8';
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的服务进程重读授权表