mysql基础（二）

一.mysql基础操作

库操作

创建数据库

语法为：

create database +数据库名称 [数据库选项]

mysql> create database yk;
Query OK, 1 row affected (0.01 sec)

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

显示数据库

语法为：

show database —— 显示所有数据库

show create database +数据库名称 —— 显示数据库的创建指令

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| student            |
| sys                |
| yk                 |
+--------------------+
6 rows in set (0.00 sec)
mysql> show create database yk;
+----------+---------------------------------------------------------------+
| Database | Create Database                                               |
+----------+---------------------------------------------------------------+
| yk       | CREATE DATABASE `yk` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)

使用某一个数据库

语法为：

use +数据库名称

mysql> use yk;
Database changed   //出现此字符则表示已使用此库

修改数据库名称

语法为：

alter database +数据库名称

mysql> drop database yk;
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)

删除数据库

语法：

drop database +数据库名称

mysql> create database qwer;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| qwer               |
| sys                |
| yk                 |
+--------------------+
6 rows in set (0.00 sec)
mysql> drop database qwer;
Query OK, 0 rows affected (0.00 sec)

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

表操作

增删改查

创建表

语法：CREATE TABLE table name (col1 datatype 修饰符,col2 datatype 修饰符) ENGINE=‘存储引擎类型’

mysql> use yk  //要先进到库内
Database changed
mysql> CREATE TABLE student( id int(11) not null primary key auto_increment, name varchar(100) not null, age tinyint(4));

//创建stduent数据表 id字段类型为int限制字段长度11 非空 设置为主键 自增长
name字段 类型为varchar字段长度为100 非空
age字段 类型为tinyint 字段长度为4

显示数据表

mysql> show tables;   //查看所有表
+--------------+
| Tables_in_yk |
+--------------+
| student      |
+--------------+
1 row in set (0.00 sec)

mysql> show tables from yk;   //查看指定库内的表
+--------------+
| Tables_in_yk |
+--------------+
| student      |
+--------------+
1 row in set (0.00 sec)

mysql> show create table student;    //显示数据表的创建指令
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                 |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `id` int(11) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 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-20 11:02:08
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

ERROR: 
No query specified

查看数据表

有三种方式，分别语法为：

• desc +表名
• describe +表名
• show columns from +表名

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

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

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

alter修改表结构

mysql> create table ahhh(id int(11) not null);
Query OK, 0 rows affected (0.01 sec)

mysql> desc ahhh;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> alter table ahhh add name varchar(50);    //为表增加一个name字段
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc ahhh;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> alter table ahhh add age tinyint(4) after name;    //在指定字段后添加新字段，在name字段后添加age字段
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

mysql> alter table ahhh add qaz int first;   //在表最前面添加一个字段，为表添加一个字放在最前
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc ahhh;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| qaz   | int(11)     | YES  |     | NULL    |       |
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
| age   | tinyint(4)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql> alter table ahhh drop qaz;   //删除指定字段
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

更改数据表

mysql> rename table ahhh to bbc;   //修改表名
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+--------------+
| Tables_in_yk |
+--------------+
| bbc          |
| student      |
+--------------+
2 rows in set (0.00 sec)

删除数据表

mysql> show tables;
+--------------+
| Tables_in_yk |
+--------------+
| bbc          |
| student      |
+--------------+
2 rows in set (0.00 sec)

mysql> drop table bbc;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+--------------+
| Tables_in_yk |
+--------------+
| student      |
+--------------+
1 row in set (0.00 sec)

插入数据

mysql> insert into student (name,age) values ('jerry',21),('tom',20),('amy',19);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

查看数据

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

删除数据

mysql> delete from student where age <= 20;
Query OK, 2 rows affected (0.00 sec)

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

truncate语句

truncate与delete的区别

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

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

mysql> truncate student;
Query OK, 0 rows affected (0.00 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.00 sec)

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

修改数据

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

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

二.权限管理

创建授权grant

权限类型(priv_type)

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

指定要操作的对象edb_name.table_nam

表示方式	意义
*. *	所有库的所有表
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> create user 'yk'@'127.0.0.1' identified by '123.Com?';
Query OK, 0 rows affected (0.04 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
mysql> grant all on *.* to 'yk'@'localhost' identified by '123.Com?';    //给yk用户可以登录访问数据库的权限
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> grant all on *.* to 'yk'@'%' identified by '123.Com?';  //授权用户在所有位置上远程登录访问数据库
Query OK, 0 rows affected, 1 warning (0.00 sec)
[root@localhost ~]# mysql -uyk -p123.Com?
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.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>

查看当前用户的权限

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

mysql> show grants for yk;
+-----------------------------------------+
| Grants for yk@%                         |
+-----------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'yk'@'%' |
+-----------------------------------------+
1 row in set (0.01 sec)

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

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

取消权限

语法：

REVOKE priv_type,… ON db_name.table_name FROM ‘username’@‘host’;

mysql> show grants for 'yk'@'localhost';
+-------------------------------------------------+
| Grants for yk@localhost                         |
+-------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'yk'@'localhost' |
+-------------------------------------------------+
1 row in set (0.00 sec)
mysql> revoke select on *.* from 'yk'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'yk'@'localhost';
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for yk@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 'yk'@'localhost' |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

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

• GRANT或REVOKE等执行权限操作会保存于表中，mysql的服务进程会自动重读授权表，并更新至内存中
• 对于不能够或不能及时重读授权表的命令，可手动让mysql的服务进程重读授权表