android 数据库 【MySQL基础】一文总结MySQL必须掌握的常用命令

文章目录

基础命令MYSQL注释方式连接数据库退出数据库连接查询MYSQL版本查看所有数据库使用数据库查看所选择数据库的所有表查看表的具体信息创建数据库查看创建数据库的语句修改用户密码数据表管理创建数据表查看创建数据表的语句查看表的结构修改表名删除表

数据表字段管理给指定表增加字段修改指定表的字段删除指定表的字段

给表添加外键创建表的时候增加外键给已有表添加外键外键使用建议

数据插入语句 INSERT数据插入一次性插入多条数据案例

数据更新语句 UPDATE没有指定匹配条件的话，会所有表的所有记录根据条件来修改记录一次性修改多个字段的值稍微高级一点的使用

where条件判空and和or的使用

数据删除 DELETE数据表清空 TRUNCATE数据查询语言Data Query LANGUAGE(DQL) SELECT查询一个表中的所有数据查询指定字段的数据给字段取别名给表取别名字符串拼接查询结果数据去重查询时增加计算模糊查询连接查询七种连接方式自连接

分页排序子查询查询常用函数分组过滤SELECT语法完整结构

数据加密

基础命令

MYSQL注释方式

-- 单行注释

/*

多行注释

哈哈哈哈哈

哈哈哈哈

*/

连接数据库

mysql -u root -p12345678

退出数据库连接

使用exit;命令可以退出连接

查询MYSQL版本

mysql> select version();

+-----------+

| version() |

+-----------+

| 8.0.27 |

+-----------+

1 row in set (0.00 sec)

查看所有数据库

show databases;

使用数据库

如果想要操作数据库，需要使用use 数据库名;来选择要操作的数据库

查看所选择数据库的所有表

show tables;

查看表的具体信息

使用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)

创建数据库

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)

如果数据库名字有短横杆，用``包住，不然可能会报错

create database `second-hand-market` character set utf8mb4;

查看创建数据库的语句

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)

修改用户密码

数据表管理

创建数据表

如：

CREATE TABLE IF NOT EXISTS `student` (

`id` BIGINT NOT NULL auto_increment COMMENT '学号' PRIMARY KEY,

`name` VARCHAR ( 30 ) NOT NULL DEFAULT '匿名' COMMENT '姓名'

) COMMENT '学生表'

查看创建数据表的语句

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)

查看表的结构

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)

修改表名

alter table 旧表名 rename as 新表名;

mysql> alter table student rename as teacher;

Query OK, 0 rows affected (0.02 sec)

删除表

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)

数据表字段管理

给指定表增加字段

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)

修改指定表的字段

修改列属性

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)

同时修改字段名和字段属性

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)

删除指定表的字段

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)

给表添加外键

创建表的时候增加外键

-- 创建专业表

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 '学生表'

给已有表添加外键

ALTER TABLE 表名 ADD CONSTRAINT 约束名 FOREIGN KEY(作为外键的列的字段名) REFERENCES 引用哪个表(哪个字段)

【案例】

ALTER TABLE `student` ADD CONSTRAINT `FK_majorId` FOREIGN KEY(`major_id`) REFERENCES `major`(`id`);

外键使用建议

当使用了外键时，如果需要删除被引用表的记录，需要先删除引用表的记录。如上面的例子所示，如果需要删除一个专业，需要先删除这个专业所对应的学生。

建议：使用外键约束会让开发者很痛苦，测试数据不方便，建议不使用外键

数据插入语句 INSERT

数据插入

insert into `表名` (`字段名1`,`字段名2`) values('字段值1','字段值2');

一次性插入多条数据

insert into `表名` (`字段名1`,`字段名2`) values('字段值11','字段值12'),('字段值21','字段值22'),('字段值31','字段值32');

案例

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)

数据更新语句 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)

根据条件来修改记录

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)

一次性修改多个字段的值

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)

稍微高级一点的使用

update load_product,product set load_product.product_id = product.id where load_product.code=product.code

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)

判空

当然，还可以用来判空或者非空，并不是直接=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 = "";

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;

数据删除 DELETE

删除所有数据

delete from 表名;

使用delete删除所有数据，如果重启数据库，会出现如下现象

InnoDB 自增列会从1开始（存在内存中，断电即失）MyISAM 自增列继续从上一个自增量开始（存在文件中，不会丢失）

删除指定数据

delete from 表名 where 条件;

数据表清空 TRUNCATE

清空一个数据表的所有数据，自增id计数器重新恢复到1，表的结构和索引约束不会改变

truncate 表名

数据查询语言Data Query LANGUAGE(DQL) SELECT

查询一个表中的所有数据

select * from 表名;

查询指定字段的数据

select 字段1,字段2,字段3 from 表名;

注意：能查询部分字段的，千万不要查询*，这样会增加查询时间

给字段取别名

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)

给表取别名

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)

取别名可以更方便开发者编写sql语句

字符串拼接

mysql> select concat('专业名称：',name) from major;

+--------------------------------------+

| concat('专业名称：',name) |

+--------------------------------------+

| 专业名称：智能科学与技术 |

| 专业名称：智能制造 |

| 专业名称：智能制造 |

| 专业名称：智能制造 |

| 专业名称：智能制造 |

+--------------------------------------+

5 rows in set (0.00 sec)

查询结果数据去重

重复的数据只显示一条

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)

查询时增加计算

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)

模糊查询

连接查询

七种连接方式

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)

分页

【查询语句】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)

子查询

注意：子查询的效率高于连表查询

查询常用函数

UPDATE sys_user

SET avatar = concat( "https://hahaha/", FLOOR(RAND()*29+1), ".png" )

WHERE

avatar IS NULL

OR avatar = "";

使用函数可能导致字段的索引失效，造成效率下降，建议直接读出来，然后用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)

能使用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

【密码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)

在存储密码这种数据时，一定要进行加密，直接使用MD5加密也还是不安全的，可以被暴力破解，可以使用更加安全的盐值加密

好文链接

 您阅读本篇文章共花了：