交流群:692356620,有不同的问题或见解可以来群里讨论,或者私聊我qq:1251611916
文章目录
4. mysql数据库操作
4.1 DDL操作
4.1.1 数据库操作
//创建数据库
//语法:CREATE DATABASE [IF NOT EXISTS] 'DB_NAME';
//创建数据库wangqingge
mysql> mysql> create database hjl;
Query OK, 1 row affected (0.00 sec)
//查看当前实例有哪些数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hjl |
| hjl1 |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
//删除数据库
//语法:DROP DATABASE [IF EXISTS] 'DB_NAME';
//删除数据库
mysql> drop database hjl1;
Query OK, 0 rows affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hjl |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
4.1.2 表操作
//创建表
//语法:CREATE TABLE table_name (col1 datatype 修饰符,col2 datatype 修饰符) ENGINE='存储引擎类型';
//在数据库wangqingge里创建表hjl
mysql> use hjl;
Database changed
mysql> create table hjl(id int(11) primary key auto_increment,name varchar(100) noot null,age tinyint(4));
//创建hjl表
Query OK, 0 rows affected (0.09 sec)
//查看当前数据库有哪些表
mysql> show tables;
+---------------+
| Tables_in_hjl |
+---------------+
| hjl |
+---------------+
1 row in set (0.01 sec)
//删除表
//语法:DROP TABLE [ IF EXISTS ] 'table_name';
//删除表hjl
mysqldrop table hjl;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
Empty set (0.00 sec)
4.1.3 用户操作
mysql用户帐号由两部分组成,如’USERNAME’@‘HOST’,表示此USERNAME只能从此HOST上远程登录
这里(‘USERNAME’@‘HOST’)的HOST用于限制此用户可通过哪些主机远程连接mysql程序,其值可为:
- IP地址,如:172.16.12.129
- 通配符
- %:匹配任意长度的任意字符,常用于设置允许从任何主机登录
- _:匹配任意单个字符
//数据库用户创建
//语法:CREATE USER 'username'@'host' [IDENTIFIED BY 'password'];
//创建数据库用户hjl
mysql> create user 'hjl'@'192.168.140.130' identified by 'HJLdrh123!';
Query OK, 0 rows affected (0.00 sec)
[root@duanruhui ~]# mysql -uhjl -p'HJLdrh123!' -h192.168.140.130;
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 6
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> drop user 'hjl'@'192.168.140.130';
Query OK, 0 rows affected (0.00 sec)
4.1.4 查看命令SHOW
mysql> SHOW CHARACTER SET; //查看支持的所有字符集
mysql> SHOW ENGINES; //查看当前数据库支持的所有存储引擎
mysql> SHOW DATABASES; //查看数据库信息
mysql> SHOW TABLES FROM hjl; //不进入某数据库而列出其包含的所有表
//查看表结构
//语法:DESC [db_name.]table_name;
mysql> desc hjl;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(100) | NO | | NULL | |
| age | tinyint(4) | YES | | NULL |
//查看某表的创建命令
//语法:SHOW CREATE TABLE table_name;
mysql> show create table hjl.hjl;
//查看某表的状态
//语法:SHOW TABLE STATUS LIKE 'table_name'\G
mysql> use hjl; //进入数据库
Database changed
mysql> show table status like 'hjl'\G;
//查看hjl表的状态
4.1.5 获取帮助
//获取命令使用帮助
//语法:HELP keyword;
mysql> help create table; //获取创建表的帮助
4.2 DML操作
DML操作包括增(INSERT)、删(DELETE)、改(UPDATE)、查(SELECT),均属针对表的操作。
4.2.1 INSERT语句
//DML操作之增操作insert
//语法:INSERT [INTO] table_name [(column_name,...)] {VALUES | VALUE} (value1,...),(...),...
mysql> use hjl;
Database changed
mysql> INSERT INTO hjl (name,age) VALUE ('tom',20); //一次插入一条记录
Query OK, 1 row affected (0.01 sec)
mysql> insert hjl(name,age) values('tom',20),('jerry',23),('wangqing',25),('sezn'(),28),('zhangshan',26),('zhangshan',20),('lisi',null),('chengshuo',10),('wangqu',3()),('qiuyi',15),('qianliu',20); //一次插入多条记录
4.2.2 SELECT语句
字段column表示法
表示符 | 代表什么? |
---|---|
* | 所有字段 |
as | 字段别名,如col1 AS alias1 当表名很长时用别名代替 |
条件判断语句WHERE
操作类型 | 常用操作符 |
---|---|
操作符 | >,<,>=,<=,=,!= BETWEEN column# AND column# LIKE:模糊匹配 RLIKE:基于正则表达式进行模式匹配 IS NOT NULL:非空 IS NULL:空 |
条件逻辑操作 | AND OR NOT |
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> use wangqingge;
Database changed
mysql> select * from hjl;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sezn | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
| 7 | lisi | NULL |
| 8 | chengshuo | 10 |
| 9 | wangqu | 3 |
| 10 | qiuyi | 15 |
| 11 | qianliu | 20
mysql> SELECT * FROM hjl ORDER BY age DESC;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 7 | lisi | 50 |
| 4 | sezn | 28 |
| 5 | zhangshan | 26 |
| 3 | wangqing | 25 |
| 2 | jerry | 23 |
| 1 | tom | 20 |
| 6 | zhangshan | 20 |
| 11 | qianliu | 20 |
| 10 | qiuyi | 15 |
| 8 | chengshuo | 10 |
| 9 | wangqu | 3 |
+----+-----------+------+
mysql> select * from hjl order by age limit 2,3;
+----+---------+------+
| id | name | age |
+----+---------+------+
| 10 | qiuyi | 15 |
| 1 | tom | 20 |
| 11 | qianliu | 20 |
+----+---------+------+
mysql> SELECT * FROM hjl WHERE age >= 25 AND name = 'zhangshan'
| id | name | age |
+----+-----------+------+
| 5 | zhangshan | 26 |
+----+-----------+------+
mysql> SELECT * FROM hjl WHERE age BETWEEN 23 and 28;
| id | name | age |
+----+-----------+------+
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sezn | 28 |
| 5 | zhangshan | 26
4.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> update hjl set age = 50 where name = 'lisi';
mysql> select * from hjl where name = 'lisi';
| id | name | age |
+----+------+------+
| 7 | lisi | 50 |
+----+------+------+
4.2.4 delete语句
//DML操作之删操作delete
//语法:DELETE FROM table_name [WHERE clause] [ORDER BY 'column_name' [DESC]] [LIMIT [m,]n];
mysql> delete from hjl where id = 7; //删除某条记录
Query OK, 1 row affected (0.00 sec)
mysql> delete from hjl; //删除整张表的内容
Query OK, 6 rows affected (0.00 sec)
4.2.5 truncate语句
truncate与delete的区别:
语句类型 | 特点 |
---|---|
delete | DELETE删除表内容时仅删除内容,但会保留表结构 DELETE语句每次删除一行,并在事务日志中为所删除的每行记录一项 可以通过回滚事务日志恢复数据 非常占用空间 |
truncate | 删除表中所有数据,且无法恢复 表结构、约束和索引等保持不变,新添加的行计数值重置为初始值 执行速度比DELETE快,且使用的系统和事务日志资源少 通过释放存储表数据所用的数据页来删除数据,并且只在事务日志中记录页的释放 对于有外键约束引用的表,不能使用TRUNCATE TABLE删除数据 不能用于加入了索引视图的表 |
//语法:TRUNCATE table_name;
mysql> select * from hjl;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sezn | 28 |
| 5 | zhangshan | 26 |
| 7 | lisi | 50 |
| 8 | chengshuo | 10 |
| 9 | wangqu | 3 |
| 10 | qiuyi | 15 |
| 11 | qianliu | 20 |
+----+-----------+------+
mysql> truncate hjl;
Query OK, 0 rows affected (0.01 sec)
mysql> desc hjl;
+-------+--------------+------+-----+---------+----------------+
| 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 hjl;
Empty set (0.00 sec)
4.3 DCL操作
4.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 |
| hjl |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
//授权hjl用户在所有位置上远程登录访问wangqingge数据库
mysql> grant all on *.* to 'hjl'@'%' identified by 'Hjldrh123!';
Query OK, 0 rows affected, 1 warning (0.00 sec)
4.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)
//查看指定用户hjl的授权信息
mysql> show grants for hjl;
+------------------------------------------+
| Grants for hjl@% |
+------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'hjl'@'%' |
+------------------------------------------+
1 row in set (0.00 sec)
4.3.3 取消授权REVOKmysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
//语法:REVOKE priv_type,... ON db_name.table_name FROM 'username'@'host';
mysql> revoke all on *.* from 'hjl'@'%' ;
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;
Query OK, 0 rows affected (0.00 sec)
alter修改表的结构
使用案例:
https://dev.mysql.com/doc/refman/5.7/en/alter-table-examples.html.
给表重命名
mysql> alter table hjl rename hjl1;
Query OK, 0 rows affected (0.00 sec)
//设置age
mysql> alter table hjl1 modify age tinyint not null;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
增加字段dd,并设置成int(11)
mysql> alter table hjl1 add dd int (11);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
删除字段dd
mysql> alter table hjl1 drop column dd;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0