热门标签
热门文章
- 1NLP实战：Pytorch实现7大经典深度学习中文文本分类-TextCNN+TextRNN+FastText+TextRCNN+TextRNN_Attention+DPCNN+Transformer
- 2解决git clone报错RPC failed； curl 56 GnuTLS recv error (-9)_error: rpc failed; curl 56 gnutls recv error (-9):
- 3Scrapy爬虫教程：实战网站图片爬取_头歌scrapy爬虫之网站图片爬取
- 4AIOT入门指南：探索人工智能与物联网的交汇点_aiot产品学习
- 5MHA、MQA、GQA区别和联系
- 6Autoit 3 常用的语句
- 7catalog能否用于mysql_在数据库中，schema、catalog分别指的是什么？
- 8给定一个单词，如果该单词以er、ly或者ing后缀结尾， 则删除该后缀（逐句精解）_给定一个单词,如果该单词以er、ly或者ing后缀结尾, 则删除该后缀(题目保证删除后
- 9机器人学习--pitch yaw roll_roll pitch yaw
- 10MySQL-JDBC编程_jdbc把用户信息改成scanner输入的信息
当前位置: article > 正文
【MySQL基础】一文总结MySQL必须掌握的常用命令_mysql命令语句大全
作者：AllinToyou | 2024-04-12 09:45:10
赞
踩
mysql命令语句大全
文章目录
基础命令
MYSQL注释方式
-- 单行注释
/*
多行注释
哈哈哈哈哈
哈哈哈哈
*/
- 1
- 2
- 3
- 4
- 5
- 6
- 7
连接数据库
mysql -u root -p12345678
- 1
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LCyXjZE5-1688559235379)(assets/1688556594985-16.png)]](https://img-blog.csdnimg.cn/4a2507a359044d4bac33db66d155b4ea.png)
退出数据库连接
使用exit;命令可以退出连接
查询MYSQL版本
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.27 |
+-----------+
1 row in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
查看所有数据库
show databases;
- 1
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BrfI5JJ0-1688559235380)(assets/1688556594986-18.png)]](https://img-blog.csdnimg.cn/c179e81ad1eb4b17a2caaf816d1468fa.png)
使用数据库
如果想要操作数据库，需要使用use 数据库名;来选择要操作的数据库
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-c1Bc6SSg-1688559235381)(assets/1688556594986-19.png)]](https://img-blog.csdnimg.cn/29f9de5125144c578cc2564cf99b506a.png)
查看所选择数据库的所有表
show tables;
- 1
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YjMDbbYV-1688559235381)(assets/1688556594986-20.png)]](https://img-blog.csdnimg.cn/9f7c7951e8a94c2e960cd4316a30c772.png)
查看表的具体信息
使用describe 表名;可以查看表中的字段信息
mysql> describe user; +--------------+---------------+------+-----+-------------------+-----------------------------------------------+ | Field | Type | Null | Key | Default | Extra | +--------------+---------------+------+-----+-------------------+-----------------------------------------------+ | id | bigint | NO | PRI | NULL | auto_increment | | userName | varchar(256) | YES | | NULL | | | userAccount | varchar(256) | NO | UNI | NULL | | | userAvatar | varchar(1024) | YES | | NULL | | | gender | tinyint | YES | | NULL | | | userRole | varchar(256) | NO | | user | | | userPassword | varchar(512) | NO | | NULL | | | accessKey | varchar(512) | YES | | NULL | | | secretKey | varchar(512) | YES | | NULL | | | createTime | datetime | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED | | updateTime | datetime | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP | | isDelete | tinyint | NO | | 0 | | +--------------+---------------+------+-----+-------------------+-----------------------------------------------+ 12 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
创建数据库
create database [if not exists] 数据库名称 character set 字符编码;
mysql> create database practice character set utf8;
Query OK, 1 row affected, 1 warning (0.01 sec)
-- 已存在的数据库不能重复创建，不然会报错
mysql> create database practice character set utf8;
ERROR 1007 (HY000): Can't create database 'practice'; database exists
-- 使用if not exists，可以判断数据库不存在的时候才创建数据库，这样就不会报错
mysql> create database if not exists practice character set utf8;
Query OK, 1 row affected, 2 warnings (0.01 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
如果数据库名字有短横杆，用``包住，不然可能会报错
create database `second-hand-market` character set utf8mb4;
- 1
查看创建数据库的语句
mysql> show create database practice;
+----------+------------------------------------------------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------------------------------------------+
| practice | CREATE DATABASE `practice` /*!40100 DEFAULT CHARACTER SET utf8 */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
修改用户密码
数据表管理
创建数据表
如：
CREATE TABLE IF NOT EXISTS `student` (
`id` BIGINT NOT NULL auto_increment COMMENT '学号' PRIMARY KEY,
`name` VARCHAR ( 30 ) NOT NULL DEFAULT '匿名' COMMENT '姓名'
) COMMENT '学生表'
- 1
- 2
- 3
- 4
查看创建数据表的语句
mysql> use practice;
Database changed
mysql> show create table student;
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table
|+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| student | CREATE TABLE `student` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` varchar(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='学生表' |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
查看表的结构
mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | bigint | NO | PRI | NULL | auto_increment |
| name | varchar(30) | NO | | 匿名 | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
修改表名
alter table 旧表名 rename as 新表名;
mysql> alter table student rename as teacher;
Query OK, 0 rows affected (0.02 sec)
- 1
- 2
删除表
drop table if exists 表名;
mysql> show tables;
+--------------------+
| Tables_in_practice |
+--------------------+
| teacher |
+--------------------+
1 row in set (0.00 sec)
mysql> drop table if exists teacher;
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
Empty set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
数据表字段管理
给指定表增加字段
alter table 表名 add 字段名 列属性;
mysql> alter table teacher add gender bigint; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc teacher; +--------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+----------------+ | id | bigint | NO | PRI | NULL | auto_increment | | name | varchar(30) | NO | | 匿名 | | | gender | bigint | YES | | NULL | | +--------+-------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) mysql> alter table teacher add age int(11); Query OK, 0 rows affected, 1 warning (0.02 sec) Records: 0 Duplicates: 0 Warnings: 1 mysql> desc teacher; +--------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+----------------+ | id | bigint | NO | PRI | NULL | auto_increment | | name | varchar(30) | NO | | 匿名 | | | gender | bigint | YES | | NULL | | | age | int | YES | | NULL | | +--------+-------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
修改指定表的字段
修改列属性
alter table 表名 modify 字段名 列属性;
mysql> alter table teacher modify age varchar(11);
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc teacher;
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| id | bigint | NO | PRI | NULL | auto_increment |
| name | varchar(30) | NO | | 匿名 | |
| gender | bigint | YES | | NULL | |
| age | varchar(11) | YES | | NULL | |
+--------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
同时修改字段名和字段属性
alter table 表名 change 旧字段名 新字段名 列属性;
mysql> desc teacher; +--------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+----------------+ | id | bigint | NO | PRI | NULL | auto_increment | | name | varchar(30) | NO | | 匿名 | | | gender | bigint | YES | | NULL | | | age | varchar(11) | YES | | NULL | | +--------+-------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) mysql> alter table teacher change age age1 int(2); Query OK, 0 rows affected, 1 warning (0.07 sec) Records: 0 Duplicates: 0 Warnings: 1 mysql> desc teacher; +--------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+----------------+ | id | bigint | NO | PRI | NULL | auto_increment | | name | varchar(30) | NO | | 匿名 | | | gender | bigint | YES | | NULL | | | age1 | int | YES | | NULL | | +--------+-------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
删除指定表的字段
mysql> desc teacher; +--------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+----------------+ | id | bigint | NO | PRI | NULL | auto_increment | | name | varchar(30) | NO | | 匿名 | | | gender | bigint | YES | | NULL | | | age1 | int | YES | | NULL | | +--------+-------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) mysql> alter table teacher drop gender; Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc teacher; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | bigint | NO | PRI | NULL | auto_increment | | name | varchar(30) | NO | | 匿名 | | | age1 | int | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
给表添加外键
创建表的时候增加外键
-- 创建专业表
CREATE TABLE IF NOT EXISTS `major` (
`id` BIGINT NOT NULL auto_increment COMMENT '专业id' PRIMARY KEY,
`name` VARCHAR ( 30 ) NOT NULL COMMENT '专业名称'
) COMMENT '专业表'
-- 创建学生表，并将学生表的major_id声明为外键，引用专业表的id字段
CREATE TABLE IF NOT EXISTS `student` (
`id` BIGINT NOT NULL auto_increment COMMENT '学号' PRIMARY KEY,
`name` VARCHAR ( 30 ) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`major_id` BIGINT NOT NULL COMMENT '专业id',
key `FK_majorId` (`major_id`),
CONSTRAINT `FK_majorId` FOREIGN KEY (`major_id`) REFERENCES `major`(`id`)
) COMMENT '学生表'
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gA063Sls-1688559235382)(assets/1688556594986-23.png)]](https://img-blog.csdnimg.cn/2f8897ec9bb9489ea3277378318564a4.png)
给已有表添加外键
ALTER TABLE 表名 ADD CONSTRAINT 约束名 FOREIGN KEY(作为外键的列的字段名) REFERENCES 引用哪个表(哪个字段)
- 1
【案例】
ALTER TABLE `student` ADD CONSTRAINT `FK_majorId` FOREIGN KEY(`major_id`) REFERENCES `major`(`id`);
- 1
外键使用建议
当使用了外键时，如果需要删除被引用表的记录，需要先删除引用表的记录。如上面的例子所示，如果需要删除一个专业，需要先删除这个专业所对应的学生。
建议：使用外键约束会让开发者很痛苦，测试数据不方便，建议不使用外键
数据插入语句 INSERT
数据插入
insert into `表名` (`字段名1`,`字段名2`) values('字段值1','字段值2');
- 1
一次性插入多条数据
insert into `表名` (`字段名1`,`字段名2`) values('字段值11','字段值12'),('字段值21','字段值22'),('字段值31','字段值32');
- 1
案例
mysql> desc student; +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+----------------+ | id | bigint | NO | PRI | NULL | auto_increment | | name | varchar(30) | NO | | 匿名 | | | major_id | bigint | NO | MUL | NULL | | +----------+-------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) mysql> desc major; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | bigint | NO | PRI | NULL | auto_increment | | name | varchar(30) | NO | | NULL | | +-------+-------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec) -- insert into `表名` (`字段名`) values('字段值'); mysql> insert into `major` (`name`) values('计算机科学'); Query OK, 1 row affected (0.01 sec) mysql> insert into `major` (`name`) values('工业工程'); Query OK, 1 row affected (0.01 sec) mysql> select * from major; +----+-----------------+ | id | name | +----+-----------------+ | 1 | 计算机科学 | | 2 | 工业工程 | +----+-----------------+ 2 rows in set (0.00 sec) -- 插入多条数据 mysql> insert into `major` (`name`) values('自动化'),('机械工程'),('工业设计'); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from major; +----+-----------------+ | id | name | +----+-----------------+ | 1 | 计算机科学 | | 2 | 工业工程 | | 3 | 自动化 | | 4 | 机械工程 | | 5 | 工业设计 | +----+-----------------+ 5 rows in set (0.00 sec) mysql> insert into `student` (`name`,`major_id`) values('小明','1'); Query OK, 1 row affected (0.01 sec) mysql> select * from student; +----+--------+----------+ | id | name | major_id | +----+--------+----------+ | 1 | 小明 | 1 | +----+--------+----------+ 1 row in set (0.00 sec) -- 插入数据的时候，不一定要给表的所有字段赋值，只赋值部分字段也可以，前提是省略的字段可以为空 mysql> insert into `student` (`major_id`) values('1'); Query OK, 1 row affected (0.01 sec) -- 插入数据的时候，没有写入学生的名字，MYSQL会自动填入默认值“匿名” mysql> select * from student; +----+--------+----------+ | id | name | major_id | +----+--------+----------+ | 1 | 小明 | 1 | | 2 | 匿名 | 1 | +----+--------+----------+ 2 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
数据更新语句 UPDATE
没有指定匹配条件的话，会所有表的所有记录
mysql> select * from major; +----+-----------------+ | id | name | +----+-----------------+ | 1 | 计算机科学 | | 2 | 工业工程 | | 3 | 自动化 | | 4 | 机械工程 | | 5 | 工业设计 | +----+-----------------+ 5 rows in set (0.00 sec) mysql> update major set name='智能制造'; Query OK, 5 rows affected (0.01 sec) Rows matched: 5 Changed: 5 Warnings: 0 mysql> select * from major; +----+--------------+ | id | name | +----+--------------+ | 1 | 智能制造 | | 2 | 智能制造 | | 3 | 智能制造 | | 4 | 智能制造 | | 5 | 智能制造 | +----+--------------+ 5 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
根据条件来修改记录
mysql> update major set name='智能科学与技术' where id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from major;
+----+-----------------------+
| id | name |
+----+-----------------------+
| 1 | 智能科学与技术 |
| 2 | 智能制造 |
| 3 | 智能制造 |
| 4 | 智能制造 |
| 5 | 智能制造 |
+----+-----------------------+
5 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
一次性修改多个字段的值
mysql> select * from student; +----+--------+----------+ | id | name | major_id | +----+--------+----------+ | 1 | 小明 | 1 | | 2 | 匿名 | 1 | +----+--------+----------+ 2 rows in set (0.00 sec) mysql> update student set name='李华',major_id=2 where id = 1; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from student; +----+--------+----------+ | id | name | major_id | +----+--------+----------+ | 1 | 李华 | 2 | | 2 | 匿名 | 1 | +----+--------+----------+ 2 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
稍微高级一点的使用
update load_product,product set load_product.product_id = product.id where load_product.code=product.code
- 1
where条件
where条件可以使用的操作符
多个条件共同使用，使用and连接，如where id=1 and name=‘李华’
mysql> select id,name as 专业名称 from major; +----+-----------------------+ | id | 专业名称 | +----+-----------------------+ | 1 | 智能科学与技术 | | 2 | 智能制造 | | 3 | 智能制造 | | 4 | 智能制造 | | 5 | 智能制造 | +----+-----------------------+ 5 rows in set (0.00 sec) mysql> select id,name as 专业名称 from major where name='智能制造'; +----+--------------+ | id | 专业名称 | +----+--------------+ | 2 | 智能制造 | | 3 | 智能制造 | | 4 | 智能制造 | | 5 | 智能制造 | +----+--------------+ 4 rows in set (0.00 sec) mysql> select id,name as 专业名称 from major where name!='智能制造'; +----+-----------------------+ | id | 专业名称 | +----+-----------------------+ | 1 | 智能科学与技术 | +----+-----------------------+ 1 row in set (0.00 sec) mysql> select id,name as 专业名称 from major where not name='智能制造'; +----+-----------------------+ | id | 专业名称 | +----+-----------------------+ | 1 | 智能科学与技术 | +----+-----------------------+ 1 row in set (0.00 sec) mysql> select id,name as 专业名称 from major where name='智能制造' and id =2; +----+--------------+ | id | 专业名称 | +----+--------------+ | 2 | 智能制造 | +----+--------------+ 1 row in set (0.00 sec) mysql> select id,name as 专业名称 from major where id < 2; +----+-----------------------+ | id | 专业名称 | +----+-----------------------+ | 1 | 智能科学与技术 | +----+-----------------------+ 1 row in set (0.00 sec) mysql> select id,name as 专业名称 from major where id >= 2; +----+--------------+ | id | 专业名称 | +----+--------------+ | 2 | 智能制造 | | 3 | 智能制造 | | 4 | 智能制造 | | 5 | 智能制造 | +----+--------------+ 4 rows in set (0.00 sec) mysql> select id,name as 专业名称 from major where id >= 2 and id <= 4; +----+--------------+ | id | 专业名称 | +----+--------------+ | 2 | 智能制造 | | 3 | 智能制造 | | 4 | 智能制造 | +----+--------------+ 3 rows in set (0.00 sec) mysql> select id,name as 专业名称 from major where id = 2 or id = 4; +----+--------------+ | id | 专业名称 | +----+--------------+ | 2 | 智能制造 | | 4 | 智能制造 | +----+--------------+ 2 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
判空
当然，还可以用来判空或者非空，并不是直接=null，而是is null和is not null
UPDATE sys_user
SET avatar = concat( "https://hahaha/", FLOOR(RAND()*29+1), ".png" )
WHERE
avatar IS NULL
OR avatar = "";
- 1
- 2
- 3
- 4
- 5
and和or的使用
SELECT
*
FROM
chat
WHERE
( from_who = "admin" AND to_who = "user1" )
OR ( to_who = "admin" AND from_who = "user1" )
ORDER BY
create_time DESC;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
数据删除 DELETE
删除所有数据
delete from 表名;
- 1
使用delete删除所有数据，如果重启数据库，会出现如下现象
- InnoDB 自增列会从1开始（存在内存中，断电即失）
- MyISAM 自增列继续从上一个自增量开始（存在文件中，不会丢失）
删除指定数据
delete from 表名 where 条件;
- 1
数据表清空 TRUNCATE
清空一个数据表的所有数据，自增id计数器重新恢复到1，表的结构和索引约束不会改变
truncate 表名
- 1
数据查询语言Data Query LANGUAGE(DQL) SELECT
查询一个表中的所有数据
select * from 表名;
- 1
查询指定字段的数据
select 字段1,字段2,字段3 from 表名;
- 1
注意：能查询部分字段的，千万不要查询*，这样会增加查询时间
给字段取别名
mysql> select name from major; +-----------------------+ | name | +-----------------------+ | 智能科学与技术 | | 智能制造 | | 智能制造 | | 智能制造 | | 智能制造 | +-----------------------+ 5 rows in set (0.00 sec) mysql> select name as 专业名称 from major; +-----------------------+ | 专业名称 | +-----------------------+ | 智能科学与技术 | | 智能制造 | | 智能制造 | | 智能制造 | | 智能制造 | +-----------------------+ 5 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
给表取别名
mysql> select student.id as 学号,student.name as 姓名,major.name as 专业 from student left join major on student.major_id = major.id; +--------+--------+-----------------------+ | 学号 | 姓名 | 专业 | +--------+--------+-----------------------+ | 1 | 李华 | 智能制造 | | 2 | 匿名 | 智能科学与技术 | +--------+--------+-----------------------+ 2 rows in set (0.00 sec) mysql> select s.id as 学号,s.name as 姓名,m.name as 专业 from student as s left join major as m on s.major_id = m.id; +--------+--------+-----------------------+ | 学号 | 姓名 | 专业 | +--------+--------+-----------------------+ | 1 | 李华 | 智能制造 | | 2 | 匿名 | 智能科学与技术 | +--------+--------+-----------------------+ 2 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
取别名可以更方便开发者编写sql语句
字符串拼接
mysql> select concat('专业名称：',name) from major;
+--------------------------------------+
| concat('专业名称：',name) |
+--------------------------------------+
| 专业名称：智能科学与技术 |
| 专业名称：智能制造 |
| 专业名称：智能制造 |
| 专业名称：智能制造 |
| 专业名称：智能制造 |
+--------------------------------------+
5 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
查询结果数据去重
重复的数据只显示一条
mysql> select name as 专业名称 from major; +-----------------------+ | 专业名称 | +-----------------------+ | 智能科学与技术 | | 智能制造 | | 智能制造 | | 智能制造 | | 智能制造 | +-----------------------+ 5 rows in set (0.00 sec) mysql> select distinct name as 专业名称 from major; +-----------------------+ | 专业名称 | +-----------------------+ | 智能科学与技术 | | 智能制造 | +-----------------------+ 2 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
查询时增加计算
mysql> select id,name as 专业名称 from major; +----+-----------------------+ | id | 专业名称 | +----+-----------------------+ | 1 | 智能科学与技术 | | 2 | 智能制造 | | 3 | 智能制造 | | 4 | 智能制造 | | 5 | 智能制造 | +----+-----------------------+ 5 rows in set (0.00 sec) -- 将所有数据的 id 增加 1 mysql> select id+1,name as 专业名称 from major; +------+-----------------------+ | id+1 | 专业名称 | +------+-----------------------+ | 2 | 智能科学与技术 | | 3 | 智能制造 | | 4 | 智能制造 | | 5 | 智能制造 | | 6 | 智能制造 | +------+-----------------------+ 5 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
模糊查询
连接查询
七种连接方式
https://blog.csdn.net/laodanqiu/article/details/131233741
自连接
用途：一个表同时存储了父类数据和子类数据，如省市区表同时存储了中国的所有省、市、区的数据，希望查询出广东省下面的所有城市
mysql> desc province_city_region; +-------------+-------------+------+-----+-------------------+-----------------------------------------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+-------------------+-----------------------------------------------+ | id | bigint | NO | PRI | NULL | auto_increment | | create_time | datetime | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED | | update_time | datetime | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP | | is_deleted | tinyint | YES | | 0 | | | name | varchar(50) | YES | | NULL | | | type | tinyint | YES | | NULL | | | parent_id | bigint | YES | | NULL | | +-------------+-------------+------+-----+-------------------+-----------------------------------------------+ 7 rows in set (0.00 sec) mysql> select a.id,a.name as 市名称,b.name as 省名称 from province_city_region as a,province_city_region as b where b.name="广东省" and a.parent_id=b.id; +------+-----------+-----------+ | id | 市名称 | 省名称 | +------+-----------+-----------+ | 4792 | 茂名市 | 广东省 | | 4798 | 湛江市 | 广东省 | | 4808 | 东莞市 | 广东省 | | 4809 | 江门市 | 广东省 | | 4817 | 清远市 | 广东省 | | 4826 | 佛山市 | 广东省 | | 4832 | 阳江市 | 广东省 | | 4837 | 汕头市 | 广东省 | | 4845 | 河源市 | 广东省 | | 4852 | 珠海市 | 广东省 | | 4856 | 汕尾市 | 广东省 | | 4861 | 深圳市 | 广东省 | | 4868 | 梅州市 | 广东省 | | 4877 | 揭阳市 | 广东省 | | 4883 | 韶关市 | 广东省 | | 4894 | 惠州市 | 广东省 | | 4900 | 潮州市 | 广东省 | | 4904 | 广州市 | 广东省 | | 4916 | 肇庆市 | 广东省 | | 4925 | 中山市 | 广东省 | | 4926 | 云浮市 | 广东省 | +------+-----------+-----------+ 21 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
分页
【查询语句】limit 数据索引起始值 数据量的大小
limit 0,5:查询的是第0,1,2,3,4条数据
limit 2,5:查询的是第2,3,4,5,6条数据
排序
【查询语句】order by 字段 ASC / DESC
- ASC（升序）
- DESC（降序）
mysql> select * from province_city_region order by id asc limit 0,10; +------+---------------------+---------------------+------------+-----------+------+-----------+ | id | create_time | update_time | is_deleted | name | type | parent_id | +------+---------------------+---------------------+------------+-----------+------+-----------+ | 2928 | 2023-02-10 20:38:54 | 2023-02-10 20:38:54 | 0 | 山东省 | 0 | 0 | | 2929 | 2023-02-10 20:38:54 | 2023-02-10 20:38:54 | 0 | 莱芜市 | 1 | 2928 | | 2930 | 2023-02-10 20:38:54 | 2023-02-10 20:38:54 | 0 | 莱城区 | 2 | 2929 | | 2931 | 2023-02-10 20:38:54 | 2023-02-10 20:38:54 | 0 | 钢城区 | 2 | 2929 | | 2932 | 2023-02-10 20:38:54 | 2023-02-10 20:38:54 | 0 | 济南市 | 1 | 2928 | | 2933 | 2023-02-10 20:38:54 | 2023-02-10 20:38:54 | 0 | 历城区 | 2 | 2932 | | 2934 | 2023-02-10 20:38:54 | 2023-02-10 20:38:54 | 0 | 历下区 | 2 | 2932 | | 2935 | 2023-02-10 20:38:54 | 2023-02-10 20:38:54 | 0 | 长清区 | 2 | 2932 | | 2936 | 2023-02-10 20:38:54 | 2023-02-10 20:38:54 | 0 | 平阴县 | 2 | 2932 | | 2937 | 2023-02-10 20:38:54 | 2023-02-10 20:38:54 | 0 | 章丘市 | 1 | 2932 | +------+---------------------+---------------------+------------+-----------+------+-----------+ 10 rows in set (0.00 sec) mysql> select * from province_city_region order by id desc limit 0,10; +------+---------------------+---------------------+------------+--------------------------+------+-----------+ | id | create_time | update_time | is_deleted | name | type | parent_id | +------+---------------------+---------------------+------------+--------------------------+------+-----------+ | 5851 | 2023-02-10 20:39:11 | 2023-02-10 20:39:11 | 0 | 天峨县 | 2 | 5840 | | 5850 | 2023-02-10 20:39:11 | 2023-02-10 20:39:11 | 0 | 凤山县 | 2 | 5840 | | 5849 | 2023-02-10 20:39:11 | 2023-02-10 20:39:11 | 0 | 南丹县 | 2 | 5840 | | 5848 | 2023-02-10 20:39:11 | 2023-02-10 20:39:11 | 0 | 环江毛南族自治县 | 2 | 5840 | | 5847 | 2023-02-10 20:39:11 | 2023-02-10 20:39:11 | 0 | 巴马瑶族自治县 | 2 | 5840 | | 5846 | 2023-02-10 20:39:11 | 2023-02-10 20:39:10 | 0 | 东兰县 | 2 | 5840 | | 5845 | 2023-02-10 20:39:11 | 2023-02-10 20:39:10 | 0 | 金城江区 | 2 | 5840 | | 5844 | 2023-02-10 20:39:11 | 2023-02-10 20:39:10 | 0 | 罗城仫佬族自治县 | 2 | 5840 | | 5843 | 2023-02-10 20:39:11 | 2023-02-10 20:39:10 | 0 | 宜州市 | 1 | 5840 | | 5842 | 2023-02-10 20:39:11 | 2023-02-10 20:39:10 | 0 | 都安瑶族自治县 | 2 | 5840 | +------+---------------------+---------------------+------------+--------------------------+------+-----------+ 10 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
子查询
注意：子查询的效率高于连表查询
查询常用函数
UPDATE sys_user
SET avatar = concat( "https://hahaha/", FLOOR(RAND()*29+1), ".png" )
WHERE
avatar IS NULL
OR avatar = "";
- 1
- 2
- 3
- 4
- 5
使用函数可能导致字段的索引失效，造成效率下降，建议直接读出来，然后用java进行处理
分组过滤
mysql> select name,gender from user limit 0,10; +-----------------+--------+ | name | gender | +-----------------+--------+ | 系统管理员 | 0 | | 企业管理员 | 0 | | 门店管理员 | 0 | | 郗淑 | 0 | | 柳之 | 1 | | 夏侯之 | 1 | | 吉滢 | 0 | | 独孤杰 | 1 | | 蒙飘 | 0 | | 卫柔 | 0 | +-----------------+--------+ 10 rows in set (0.00 sec) -- 根据 gender 进行分组 mysql> select name,gender from user group by gender limit 0,10; +-----------------+--------+ | name | gender | +-----------------+--------+ | 系统管理员 | 0 | | 柳之 | 1 | +-----------------+--------+ 2 rows in set (0.01 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
能使用where，就不要使用having，不然可能导致索引失效
SELECT语法完整结构
[]：代表可选
{}：代表必选
数据加密
【建表sql】
DROP TABLE IF EXISTS `user`; CREATE TABLE `user` ( `id` BIGINT NOT NULL auto_increment COMMENT '主键' PRIMARY KEY, `create_time` datetime DEFAULT CURRENT_TIMESTAMP NOT NULL COMMENT '创建时间', `update_time` datetime DEFAULT CURRENT_TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', `is_deleted` TINYINT DEFAULT 0 NULL COMMENT '是否删除 0：未删除 1：已删除', `username` VARCHAR ( 50 ) DEFAULT NULL COMMENT '用户名', `password` VARCHAR ( 255 ) NOT NULL COMMENT '密码', `gender` TINYINT DEFAULT 0 NULL COMMENT '性别 0：男 1：女', `age` INT DEFAULT NULL COMMENT '年龄' ) COMMENT '用户表'; -- 插入数据 mysql> insert into user(`username`,`password`,`gender`,`age`) values('admin','123456',0,18),('hello','123456',0,28),('word','123456',1,17); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
【密码MD5加密操作】
mysql> select * from user; +----+---------------------+---------------------+------------+----------+----------+--------+------+ | id | create_time | update_time | is_deleted | username | password | gender | age | +----+---------------------+---------------------+------------+----------+----------+--------+------+ | 1 | 2023-07-05 10:16:16 | 2023-07-05 10:16:16 | 0 | admin | 123456 | 0 | 18 | | 2 | 2023-07-05 10:16:16 | 2023-07-05 10:16:16 | 0 | hello | 123456 | 0 | 28 | | 3 | 2023-07-05 10:16:16 | 2023-07-05 10:16:16 | 0 | word | 123456 | 1 | 17 | +----+---------------------+---------------------+------------+----------+----------+--------+------+ 3 rows in set (0.00 sec) -- 加密id=1的数据的密码 mysql> update user set password=MD5(password) where id=1; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from user; +----+---------------------+---------------------+------------+----------+----------------------------------+--------+------+ | id | create_time | update_time | is_deleted | username | password | gender | age | +----+---------------------+---------------------+------------+----------+----------------------------------+--------+------+ | 1 | 2023-07-05 10:16:16 | 2023-07-05 10:18:38 | 0 | admin | e10adc3949ba59abbe56e057f20f883e | 0 | 18 | | 2 | 2023-07-05 10:16:16 | 2023-07-05 10:16:16 | 0 | hello | 123456 | 0 | 28 | | 3 | 2023-07-05 10:16:16 | 2023-07-05 10:16:16 | 0 | word | 123456 | 1 | 17 | +----+---------------------+---------------------+------------+----------+----------------------------------+--------+------+ 3 rows in set (0.00 sec) -- 加密所有数据的密码 mysql> update user set password=MD5(password); Query OK, 3 rows affected (0.01 sec) Rows matched: 3 Changed: 3 Warnings: 0 mysql> select * from user; +----+---------------------+---------------------+------------+----------+----------------------------------+--------+------+ | id | create_time | update_time | is_deleted | username | password | gender | age | +----+---------------------+---------------------+------------+----------+----------------------------------+--------+------+ | 1 | 2023-07-05 10:16:16 | 2023-07-05 10:19:01 | 0 | admin | 14e1b600b1fd579f47433b88e8d85291 | 0 | 18 | | 2 | 2023-07-05 10:16:16 | 2023-07-05 10:19:01 | 0 | hello | e10adc3949ba59abbe56e057f20f883e | 0 | 28 | | 3 | 2023-07-05 10:16:16 | 2023-07-05 10:19:01 | 0 | word | e10adc3949ba59abbe56e057f20f883e | 1 | 17 | +----+---------------------+---------------------+------------+----------+----------------------------------+--------+------+ 3 rows in set (0.00 sec) -- 插入数据的时候就执行加密 mysql> insert into user(`username`,`password`,`gender`,`age`) values('md5',MD5('123456'),0,18); Query OK, 1 row affected (0.01 sec) mysql> select * from user; +----+---------------------+---------------------+------------+----------+----------------------------------+--------+------+ | id | create_time | update_time | is_deleted | username | password | gender | age | +----+---------------------+---------------------+------------+----------+----------------------------------+--------+------+ | 1 | 2023-07-05 10:16:16 | 2023-07-05 10:19:01 | 0 | admin | 14e1b600b1fd579f47433b88e8d85291 | 0 | 18 | | 2 | 2023-07-05 10:16:16 | 2023-07-05 10:19:01 | 0 | hello | e10adc3949ba59abbe56e057f20f883e | 0 | 28 | | 3 | 2023-07-05 10:16:16 | 2023-07-05 10:19:01 | 0 | word | e10adc3949ba59abbe56e057f20f883e | 1 | 17 | | 4 | 2023-07-05 10:19:46 | 2023-07-05 10:19:46 | 0 | md5 | e10adc3949ba59abbe56e057f20f883e | 0 | 18 | +----+---------------------+---------------------+------------+----------+----------------------------------+--------+------+ 4 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
在存储密码这种数据时，一定要进行加密，直接使用MD5加密也还是不安全的，可以被暴力破解，可以使用更加安全的盐值加密
声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/AllinToyou/article/detail/410135
推荐阅读
相关标签
