mySQL语句
目录
mysql数据库操作
1.数据库DDL操作
/创建数据库
//语法：CREATE DATABASE [IF NOT EXISTS] 'DB_NAME';
//创建数据库lzx
mysql> create database lzx;
Query OK, 1 row affected (0.00 sec)
//查看有哪些数据库
mysql> show databases ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| abc |
| lzx |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
//删除数据库
//语法：DROP DATABASE [IF EXISTS] 'DB_NAME';
//删除数据库zzz
mysql> drop database zzz;
Query OK, 0 rows affected (0.00 sec)
2.DDL表操作
//创建表
//语法：CREATE TABLE table_name (col1 datatype 修饰符,col2 datatype 修饰符) ENGINE='存储引擎类型';
//在数据库lzx里创建表student
mysql> create database lzx;
Query OK, 1 row affected (0.00 sec)
mysql> use lzx; //进入数据库lzx
Database changed
mysql> create table student(id int not null primary key auto_increment,name varchar(100),age tinyint);
Query OK, 0 rows affected (0.00 sec) //创建表且每个id是唯一的
//查看当前有哪些表
mysql> show tables;
+---------------+
| Tables_in_lzx |
+---------------+
| student |
+---------------+
1 row in set (0.00 sec)
//删除表abc
mysql> show tables;
+---------------+
| Tables_in_lzx |
+---------------+
| abc |
| student |
+---------------+
2 rows in set (0.00 sec)
mysql> drop table abc;
Query OK, 0 rows affected (0.00 sec)
//创建表时，设置字段、表的字符编码
mysql> CREATE TABLE tbtest(id int(10),user_name varchar(60) CHARACTER SET GBK COLLATE gbk_chinese_ci,email varchar(60))CHARACTER SET utf8mb4 COLLATE utf8_general_ci;
3.用户DDL操作
MySQL用户帐号由两部分组成，如'USERNAME'@'HOST'，表示此USERNAME只能从此HOST上远程登录；HOST用于限制此用户可通过哪些主机远程连接mysql程序。
HOST的值可为：
- IP地址，如：172.16.12.129
- 通配符
- %：匹配任意长度的任意字符，常用于设置允许从任何主机登录
- _：匹配任意单个字符
//数据库用户创建
//语法：CREATE USER 'username'@'host' [IDENTIFIED BY 'password'];
//创建数据库用户lzx
mysql> create user 'username'@'192.168.71.129' identified by 'Lzx123456!';
Query OK, 0 rows affected (0.00 sec)
//授权刷新表
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
//使用新创建的用户和密码登录
[root@localhost ~]# mysql -ulzx -p'Lzx123456!' -h192.168.71.129
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.23 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, 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.
//删除数据库用户
//语法：DROP USER 'username'@'host';
//删除数据库用户lzx
mysql> drop user 'lzx'@'192.168.71.129';
Query OK, 0 rows affected (0.00 sec)
4.查看命令SHOW
mysql> SHOW CHARACTER SET; //查看支持的所有字符集
mysql> SHOW VARIABLES LIKE '%char%'; //查看客户端的字符编码
mysql> SELECT CHARSET(email) FROM tbtest; //查看某表中某字段使用的字符编码
mysql> SHOW ENGINES; //查看当前数据库支持的所有存储引擎
mysql> SHOW DATABASES; //查看数据库信息
mysql> SHOW TABLES FROM hzz; //不进入某数据库而列出其包含的所有表
//查看表结构
//语法：DESC [db_name.]table_name;
mysql> desc student;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(100) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
//查看某表的创建命令，可以看到创建表时设置的参数
//语法：SHOW CREATE TABLE table_name;
mysql> show create table student;
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) DEFAULT NULL,
`age` tinyint(4) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
//查看某表的状态
//语法：SHOW TABLE STATUS LIKE 'table_name'\G
mysql> use lzx;
Database changed
mysql> show table status like 'lzx\G';
Empty set (0.00 sec)
5.ALTER修改命令
//修改数据库属性
//语法：ALTER DATABASE 'DB_NAME' CHARACTER SET charset_name | COLLATE collation_name
//修改表
//语法：ALTER TABLE <table_name> [option]
//给student表添加新的一列，先查看表结构
mysql> desc student;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(100) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
//给student表添加一列'phone'
mysql> alter table student ADD phone int(11);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
//查看表结构
mysql> desc student;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(100) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| phone | int(11) | YES | | NULL | |
+-------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
//修改atudent表中'phone'列为'sex'
mysql> alter table student change phone sex varchar(4);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(100) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| sex | varchar(4) | YES | | NULL | |
+-------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
//重命名表名为over
mysql> ALTER TABLE student RENAME TO over;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+---------------+
| Tables_in_lzx |
+---------------+
| over |
+---------------+
1 row in set (0.00 sec)
//修改数据库字符编码
mysql> ALTER DATABASE dbtest CHARACTER SET utf8 COLLATE utf8_general_ci;
//修改表字符编码
mysql> ALTER TABLE tbtest CHARACTER SET utf8 COLLATE utf8_general_ci;
6.获取帮助
//获取命令使用帮助
//语法：HELP commond;
mysql> HELP CREATE TABLE; //获取创建表的帮助
DML操作
DML操作包括增(INSERT)、删(DELETE)、改(UPDATE)、查(SELECT)，均属针对表的操作。
1.INSERT语句
//DML操作之增操作insert
//语法：INSERT [INTO] table_name [(column_name,...)] {VALUES | VALUE} (value1,...),(...),...
//一次插入一条记录
mysql> insert into over(name,age) value ('xt',36);
Query OK, 1 row affected (0.01 sec)
//一次插入多条记录
mysql> insert over(name,age) values ('tom',20),('jerry',23),('wangqing',25),('sean',28),('zhangshan',26),('zhangshang',20),('lisi',NULL),('chenshuo',20),('wangwu',3),('qiuyi',15),('qiuxiaotian',20);
Query OK, 11 rows affected (0.00 sec)
Records: 11 Duplicates: 0 Warnings: 0
2.SELECT语句(DQL)
字段column匹配方式:
| 表示符 | 含义 |
|---|---|
| * | 所有字段 |
| as | 字段别名，如col1 AS alias1 当表名很长时用别名代替 |
条件判断语句WHERE:
| 操作类型 | 常用操作符 |
|---|---|
| 操作符 | >，<，>=，<=，=，!= BETWEEN column# AND column# LIKE：模糊匹配 RLIKE：基于正则表达式进行模式匹配 IS NOT NULL：非空 IS NULL：空 |
| 条件逻辑操作 | AND |
| OR | |
| NOT |
//DML操作之查操作select
//语法：SELECT column1,column2,... FROM table_name [WHERE clause] [ORDER BY 'column_name' [DESC]] [LIMIT [m,]n];
mysql> SELECT * FROM over ORDER BY age DESC;
+----+-------------+------+------+
| id | name | age | sex |
+----+-------------+------+------+
| 1 | xt | 36 | NULL |
| 5 | sean | 28 | NULL |
| 6 | zhangshan | 26 | NULL |
| 4 | wangqing | 25 | NULL |
| 3 | jerry | 23 | NULL |
| 2 | tom | 20 | NULL |
| 7 | zhangshang | 20 | NULL |
| 9 | chenshuo | 20 | NULL |
| 12 | qiuxiaotian | 20 | NULL |
| 11 | qiuyi | 15 | NULL |
| 10 | wangwu | 3 | NULL |
| 8 | lisi | NULL | NULL |
+----+-------------+------+------+
12 rows in set (0.00 sec)
mysql> SELECT * FROM over ORDER BY age DESC LIMIT 2,3;
+----+-----------+------+------+
| id | name | age | sex |
+----+-----------+------+------+
| 6 | zhangshan | 26 | NULL |
| 4 | wangqing | 25 | NULL |
| 3 | jerry | 23 | NULL |
+----+-----------+------+------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM over ORDER BY age DESC LIMIT 4;
+----+-----------+------+------+
| id | name | age | sex |
+----+-----------+------+------+
| 1 | xt | 36 | NULL |
| 5 | sean | 28 | NULL |
| 6 | zhangshan | 26 | NULL |
| 4 | wangqing | 25 | NULL |
+----+-----------+------+------+
4 rows in set (0.00 sec)
mysql> select * from over where name ='zhangshan' ;
+----+-----------+------+------+
| id | name | age | sex |
+----+-----------+------+------+
| 6 | zhangshan | 26 | NULL |
+----+-----------+------+------+
1 row in set (0.00 sec)
mysql> select * from over where name = 'zhangshan' or age < 20;
+----+-----------+------+------+
| id | name | age | sex |
+----+-----------+------+------+
| 6 | zhangshan | 26 | NULL |
| 10 | wangwu | 3 | NULL |
| 11 | qiuyi | 15 | NULL |
+----+-----------+------+------+
3 rows in set (0.00 sec)
mysql> update over set age=100 where name = 'wangwu';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM over WHERE age BETWEEN 23 and 28;
+----+-----------+------+------+
| id | name | age | sex |
+----+-----------+------+------+
| 3 | jerry | 23 | NULL |
| 4 | wangqing | 25 | NULL |
| 5 | sean | 28 | NULL |
| 6 | zhangshan | 26 | NULL |
+----+-----------+------+------+
4 rows in set (0.00 sec)
GROUP BY 分组聚合函数
group by 分组功能原理：
1.按照group by指定的列进⾏排序；
2.然后根据group by指定的列去重复，也就是将相同的值分成一组；
3.最后通过聚合函数将其他列的结果进⾏聚合。
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 over set age = 66 where name = 'xt';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from over;
+----+-------------+------+------+
| id | name | age | sex |
+----+-------------+------+------+
| 1 | xt | 66 | NULL |
| 2 | tom | 20 | NULL |
| 3 | jerry | 23 | NULL |
| 4 | wangqing | 25 | NULL |
| 5 | sean | 28 | NULL |
| 6 | zhangshan | 26 | NULL |
| 7 | zhangshang | 20 | NULL |
| 8 | lisi | NULL | NULL |
| 9 | chenshuo | 20 | NULL |
| 10 | wangwu | 100 | NULL |
| 11 | qiuyi | 15 | NULL |
| 12 | qiuxiaotian | 20 | NULL |
+----+-------------+------+------+
12 rows in set (0.00 sec)
4.delete语句
//DML操作之删操作delete
//语法：DELETE FROM table_name [WHERE clause] [ORDER BY 'column_name' [DESC]] [LIMIT [m,]n];
//删除某条记录
mysql> delete from over where id = 66;
Query OK, 0 rows affected (0.00 sec)
//删除整张表的内容
mysql> delete from over;
Query OK, 12 rows affected (0.00 sec)
5.truncate语句
truncate与delete的区别：
| 语句类型 | 特点 |
|---|---|
| delete | DELETE删除表内容时仅删除内容，但会保留表结构 DELETE语句每次删除一行，并在事务日志中为所删除的每行记录一项 可以通过回滚事务日志恢复数据 非常占用空间 |
| truncate | 删除表中所有数据，且无法恢复 表结构、约束和索引等保持不变，新添加的行计数值重置为初始值 执行速度比DELETE快，且使用的系统和事务日志资源少 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放 对于有外键约束引用的表，不能使用TRUNCATE TABLE删除数据 不能用于加入了索引视图的表 |
//语法：TRUNCATE table_name;
mysql> select * from over;
+----+-------------+------+------+
| id | name | age | sex |
+----+-------------+------+------+
| 13 | tom | 20 | NULL |
| 14 | jerry | 23 | NULL |
| 15 | wangqing | 25 | NULL |
| 16 | sean | 28 | NULL |
| 17 | zhangshan | 26 | NULL |
| 18 | zhangshang | 20 | NULL |
| 19 | lisi | NULL | NULL |
| 20 | chenshuo | 20 | NULL |
| 21 | wangwu | 3 | NULL |
| 22 | qiuyi | 15 | NULL |
| 23 | qiuxiaotian | 20 | NULL |
+----+-------------+------+------+
11 rows in set (0.00 sec)
//删除表中所有数据
mysql> truncate over;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from over;
Empty set (0.00 sec)
mysql> desc over;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(100) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| sex | varchar(4) | 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：被授权的用户可将自己的权限副本转赠给其他用户，说白点就是将自己的权限完全复制给另一个用户。不建议使用。
//语法：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 |
| abc |
| lzx |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
//授权hzz用户在数据库本机上登录访问所有数据库
mysql> grant all on *.* to 'lzx'@'%' identified by 'Lzx123456!';
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 lzx;
+------------------------------------------+
| Grants for lzx@% |
+------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'lzx'@'%' |
+------------------------------------------+
1 row in set (0.00 sec)
3.取消授权
//语法：REVOKE priv_type,... ON db_name.table_name FROM 'username'@'host';
mysql> revoke all on *.* from 'lzx'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)
注意：mysql服务进程启动时会读取mysql库中的所有授权表至内存中：
GRANT或REVOKE等执行权限操作会保存于表中，mysql的服务进程会自动重读授权表，并更新至内存中
对于不能够或不能及时重读授权表的命令，可手动让mysql的服务进程重读授权表
mysql> FLUSH PRIVILEGES;
浙公网安备 33010602011771号