【MySQL基础】一文总结MySQL必须把握的常用命令

基础命令

MYSQL解释方式

1. -- 单行注释
3. /*
4. 多行注释
5. 哈哈哈哈哈
6. 哈哈哈哈
7. */

复制代码

毗连数据库

1. mysql -u root -p12345678

复制代码

退出数据库毗连

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

查询MYSQL版本

1. mysql> select version();
2. +-----------+
3. | version() |
4. +-----------+
5. | 8.0.27    |
6. +-----------+
7. 1 row in set (0.00 sec)

复制代码

查看全部数据库

1. show databases;

复制代码

使用数据库

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

查看所选择数据库的全部表

1. show tables;

复制代码

查看表的详细信息

使用describe 表名;可以查看表中的字段信息

1. mysql> describe user;
2. +--------------+---------------+------+-----+-------------------+-----------------------------------------------+
3. | Field        | Type          | Null | Key | Default           | Extra                                         |
4. +--------------+---------------+------+-----+-------------------+-----------------------------------------------+
5. | id           | bigint        | NO   | PRI | NULL              | auto_increment                                |
6. | userName     | varchar(256)  | YES  |     | NULL              |                                               |
7. | userAccount  | varchar(256)  | NO   | UNI | NULL              |                                               |
8. | userAvatar   | varchar(1024) | YES  |     | NULL              |                                               |
9. | gender       | tinyint       | YES  |     | NULL              |                                               |
10. | userRole     | varchar(256)  | NO   |     | user              |                                               |
11. | userPassword | varchar(512)  | NO   |     | NULL              |                                               |
12. | accessKey    | varchar(512)  | YES  |     | NULL              |                                               |
13. | secretKey    | varchar(512)  | YES  |     | NULL              |                                               |
14. | createTime   | datetime      | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED                             |
15. | updateTime   | datetime      | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
16. | isDelete     | tinyint       | NO   |     | 0                 |                                               |
17. +--------------+---------------+------+-----+-------------------+-----------------------------------------------+
18. 12 rows in set (0.00 sec)

复制代码

创建数据库

1. create database [if not exists] 数据库名称 character set 字符编码;
2. mysql> create database practice character set utf8;
3. Query OK, 1 row affected, 1 warning (0.01 sec)
5. -- 已存在的数据库不能重复创建，不然会报错
6. mysql> create database practice character set utf8;
7. ERROR 1007 (HY000): Can't create database 'practice'; database exists
9. -- 使用if not exists，可以判断数据库不存在的时候才创建数据库，这样就不会报错
10. mysql> create database if not exists practice character set utf8;
11. Query OK, 1 row affected, 2 warnings (0.01 sec)

复制代码

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

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

复制代码

查看创建数据库的语句

1. mysql> show create database practice;
2. +----------+------------------------------------------------------------------------------------------------------+
3. | Database | Create Database                                                                                      |
4. +----------+------------------------------------------------------------------------------------------------------+
5. | practice | CREATE DATABASE `practice` /*!40100 DEFAULT CHARACTER SET utf8 */ /*!80016 DEFAULT ENCRYPTION='N' */ |
6. +----------+------------------------------------------------------------------------------------------------------+
7. 1 row in set (0.00 sec)

复制代码

修改用户密码

数据表管理

创建数据表

如：

1. CREATE TABLE IF NOT EXISTS `student` (
2.     `id` BIGINT NOT NULL auto_increment COMMENT '学号' PRIMARY KEY,
3.     `name` VARCHAR ( 30 ) NOT NULL DEFAULT '匿名' COMMENT '姓名'
4. ) COMMENT '学生表'

复制代码

查看创建数据表的语句

1. mysql> use practice;
2. Database changed
3. mysql> show create table student;
4. +---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table   | Create Table
5.                                                                                                                        |+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| student | CREATE TABLE `student` (
6.   `id` bigint NOT NULL AUTO_INCREMENT COMMENT '学号',
7.   `name` varchar(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
8.   PRIMARY KEY (`id`)
9. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='学生表'          |
10. +---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

复制代码

查看表的结构

1. mysql> desc student;
2. +-------+-------------+------+-----+---------+----------------+
3. | Field | Type        | Null | Key | Default | Extra          |
4. +-------+-------------+------+-----+---------+----------------+
5. | id    | bigint      | NO   | PRI | NULL    | auto_increment |
6. | name  | varchar(30) | NO   |     | 匿名    |                |
7. +-------+-------------+------+-----+---------+----------------+
8. 2 rows in set (0.00 sec)

复制代码

修改表名

alter table 旧表名 rename as 新表名;

1. mysql> alter table student rename as teacher;
2. Query OK, 0 rows affected (0.02 sec)

复制代码

删除表

drop table if exists 表名;

1. mysql> show tables;
2. +--------------------+| 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;
3. Empty set (0.00 sec)

复制代码

数据表字段管理

给指定表增长字段

alter table 表名 add 字段名 列属性;

1. mysql> alter table teacher add gender bigint;
2. Query OK, 0 rows affected (0.02 sec)
3. Records: 0  Duplicates: 0  Warnings: 0
5. mysql> desc teacher;
6. +--------+-------------+------+-----+---------+----------------+
7. | Field  | Type        | Null | Key | Default | Extra          |
8. +--------+-------------+------+-----+---------+----------------+
9. | id     | bigint      | NO   | PRI | NULL    | auto_increment |
10. | name   | varchar(30) | NO   |     | 匿名    |                |
11. | gender | bigint      | YES  |     | NULL    |                |
12. +--------+-------------+------+-----+---------+----------------+
13. 3 rows in set (0.00 sec)
15. mysql> alter table teacher add age int(11);
16. Query OK, 0 rows affected, 1 warning (0.02 sec)
17. Records: 0  Duplicates: 0  Warnings: 1
19. mysql> desc teacher;
20. +--------+-------------+------+-----+---------+----------------+
21. | Field  | Type        | Null | Key | Default | Extra          |
22. +--------+-------------+------+-----+---------+----------------+
23. | id     | bigint      | NO   | PRI | NULL    | auto_increment |
24. | name   | varchar(30) | NO   |     | 匿名    |                |
25. | gender | bigint      | YES  |     | NULL    |                |
26. | age    | int         | YES  |     | NULL    |                |
27. +--------+-------------+------+-----+---------+----------------+
28. 4 rows in set (0.00 sec)

复制代码

修改指定表的字段

   修改列属性
  alter table 表名 modify 字段名 列属性;

1. mysql> alter table teacher modify age varchar(11);
2. Query OK, 0 rows affected (0.07 sec)
3. Records: 0  Duplicates: 0  Warnings: 0
5. mysql> desc teacher;
6. +--------+-------------+------+-----+---------+----------------+
7. | Field  | Type        | Null | Key | Default | Extra          |
8. +--------+-------------+------+-----+---------+----------------+
9. | id     | bigint      | NO   | PRI | NULL    | auto_increment |
10. | name   | varchar(30) | NO   |     | 匿名    |                |
11. | gender | bigint      | YES  |     | NULL    |                |
12. | age    | varchar(11) | YES  |     | NULL    |                |
13. +--------+-------------+------+-----+---------+----------------+
14. 4 rows in set (0.00 sec)

复制代码

  同时修改字段名和字段属性
  alter table 表名 change 旧字段名 新字段名 列属性;

1. mysql> desc teacher;
2. +--------+-------------+------+-----+---------+----------------+
3. | Field  | Type        | Null | Key | Default | Extra          |
4. +--------+-------------+------+-----+---------+----------------+
5. | id     | bigint      | NO   | PRI | NULL    | auto_increment |
6. | name   | varchar(30) | NO   |     | 匿名    |                |
7. | gender | bigint      | YES  |     | NULL    |                |
8. | age    | varchar(11) | YES  |     | NULL    |                |
9. +--------+-------------+------+-----+---------+----------------+
10. 4 rows in set (0.00 sec)
12. mysql> alter table teacher change age age1 int(2);
13. Query OK, 0 rows affected, 1 warning (0.07 sec)
14. Records: 0  Duplicates: 0  Warnings: 1
16. mysql> desc teacher;
17. +--------+-------------+------+-----+---------+----------------+
18. | Field  | Type        | Null | Key | Default | Extra          |
19. +--------+-------------+------+-----+---------+----------------+
20. | id     | bigint      | NO   | PRI | NULL    | auto_increment |
21. | name   | varchar(30) | NO   |     | 匿名    |                |
22. | gender | bigint      | YES  |     | NULL    |                |
23. | age1   | int         | YES  |     | NULL    |                |
24. +--------+-------------+------+-----+---------+----------------+
25. 4 rows in set (0.00 sec)

复制代码

删除指定表的字段

1. mysql> desc teacher;
2. +--------+-------------+------+-----+---------+----------------+
3. | Field  | Type        | Null | Key | Default | Extra          |
4. +--------+-------------+------+-----+---------+----------------+
5. | id     | bigint      | NO   | PRI | NULL    | auto_increment |
6. | name   | varchar(30) | NO   |     | 匿名    |                |
7. | gender | bigint      | YES  |     | NULL    |                |
8. | age1   | int         | YES  |     | NULL    |                |
9. +--------+-------------+------+-----+---------+----------------+
10. 4 rows in set (0.00 sec)
12. mysql> alter table teacher drop gender;
13. Query OK, 0 rows affected (0.06 sec)
14. Records: 0  Duplicates: 0  Warnings: 0
16. mysql> desc teacher;
17. +-------+-------------+------+-----+---------+----------------+
18. | Field | Type        | Null | Key | Default | Extra          |
19. +-------+-------------+------+-----+---------+----------------+
20. | id    | bigint      | NO   | PRI | NULL    | auto_increment |
21. | name  | varchar(30) | NO   |     | 匿名    |                |
22. | age1  | int         | YES  |     | NULL    |                |
23. +-------+-------------+------+-----+---------+----------------+
24. 3 rows in set (0.00 sec)

复制代码

给表添加外键

创建表的时间增长外键

1. -- 创建专业表
2. CREATE TABLE IF NOT EXISTS `major` (
3.     `id` BIGINT NOT NULL auto_increment COMMENT '专业id' PRIMARY KEY,
4.     `name` VARCHAR ( 30 ) NOT NULL COMMENT '专业名称'
5. ) COMMENT '专业表'
7. -- 创建学生表，并将学生表的major_id声明为外键，引用专业表的id字段
8. CREATE TABLE IF NOT EXISTS `student` (
9.     `id` BIGINT NOT NULL auto_increment COMMENT '学号' PRIMARY KEY,
10.     `name` VARCHAR ( 30 ) NOT NULL DEFAULT '匿名' COMMENT '姓名',
11.     `major_id` BIGINT NOT NULL COMMENT '专业id',
12.     key `FK_majorId` (`major_id`),
13.     CONSTRAINT `FK_majorId` FOREIGN KEY (`major_id`) REFERENCES `major`(`id`)
14. ) COMMENT '学生表'

复制代码

给已有表添加外键

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

复制代码

【案例】

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

复制代码

外键使用建议

当使用了外键时，如果须要删除被引用表的记录，须要先删除引用表的记录。如上面的例子所示，如果须要删除一个专业，须要先删除这个专业所对应的学生。
   建议：使用外键束缚会让开发者很痛楚，测试数据不方便，建议不使用外键
  数据插入语句 INSERT

数据插入

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

复制代码

一次性插入多条数据

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

复制代码

案例

1. mysql> desc student;
2. +----------+-------------+------+-----+---------+----------------+
3. | Field    | Type        | Null | Key | Default | Extra          |
4. +----------+-------------+------+-----+---------+----------------+
5. | id       | bigint      | NO   | PRI | NULL    | auto_increment |
6. | name     | varchar(30) | NO   |     | 匿名    |                |
7. | major_id | bigint      | NO   | MUL | NULL    |                |
8. +----------+-------------+------+-----+---------+----------------+
9. 3 rows in set (0.00 sec)
11. mysql> desc major;
12. +-------+-------------+------+-----+---------+----------------+
13. | Field | Type        | Null | Key | Default | Extra          |
14. +-------+-------------+------+-----+---------+----------------+
15. | id    | bigint      | NO   | PRI | NULL    | auto_increment |
16. | name  | varchar(30) | NO   |     | NULL    |                |
17. +-------+-------------+------+-----+---------+----------------+
18. 2 rows in set (0.00 sec)
20. --  insert into `表名` (`字段名`) values('字段值');
21. mysql> insert into `major` (`name`) values('计算机科学');
22. Query OK, 1 row affected (0.01 sec)
24. mysql> insert into `major` (`name`) values('工业工程');
25. Query OK, 1 row affected (0.01 sec)
27. mysql> select * from major;
28. +----+-----------------+
29. | id | name            |
30. +----+-----------------+
31. |  1 | 计算机科学      |
32. |  2 | 工业工程        |
33. +----+-----------------+
34. 2 rows in set (0.00 sec)
36. -- 插入多条数据
37. mysql> insert into `major` (`name`) values('自动化'),('机械工程'),('工业设计');
38. Query OK, 3 rows affected (0.01 sec)
39. Records: 3  Duplicates: 0  Warnings: 0
41. mysql> select * from major;
42. +----+-----------------+
43. | id | name            |
44. +----+-----------------+
45. |  1 | 计算机科学      |
46. |  2 | 工业工程        |
47. |  3 | 自动化          |
48. |  4 | 机械工程        |
49. |  5 | 工业设计        |
50. +----+-----------------+
51. 5 rows in set (0.00 sec)
53. mysql> insert into `student` (`name`,`major_id`) values('小明','1');
54. Query OK, 1 row affected (0.01 sec)
56. mysql> select * from student;
57. +----+--------+----------+
58. | id | name   | major_id |
59. +----+--------+----------+
60. |  1 | 小明   |        1 |
61. +----+--------+----------+
62. 1 row in set (0.00 sec)
64. -- 插入数据的时候，不一定要给表的所有字段赋值，只赋值部分字段也可以，前提是省略的字段可以为空
65. mysql> insert into `student` (`major_id`) values('1');
66. Query OK, 1 row affected (0.01 sec)
67. -- 插入数据的时候，没有写入学生的名字，MYSQL会自动填入默认值“匿名”
68. mysql> select * from student;
69. +----+--------+----------+
70. | id | name   | major_id |
71. +----+--------+----------+
72. |  1 | 小明   |        1 |
73. |  2 | 匿名   |        1 |
74. +----+--------+----------+
75. 2 rows in set (0.00 sec)

复制代码

数据更新语句 UPDATE

没有指定匹配条件的话，会全部表的全部记录

1. mysql> select * from major;
2. +----+-----------------+
3. | id | name            |
4. +----+-----------------+
5. |  1 | 计算机科学      |
6. |  2 | 工业工程        |
7. |  3 | 自动化          |
8. |  4 | 机械工程        |
9. |  5 | 工业设计        |
10. +----+-----------------+
11. 5 rows in set (0.00 sec)
13. mysql> update major set name='智能制造';
14. Query OK, 5 rows affected (0.01 sec)
15. Rows matched: 5  Changed: 5  Warnings: 0
17. mysql> select * from major;
18. +----+--------------+
19. | id | name         |
20. +----+--------------+
21. |  1 | 智能制造     |
22. |  2 | 智能制造     |
23. |  3 | 智能制造     |
24. |  4 | 智能制造     |
25. |  5 | 智能制造     |
26. +----+--------------+
27. 5 rows in set (0.00 sec)

复制代码

根据条件来修改记录

1. mysql> update major set name='智能科学与技术' where id = 1;
2. Query OK, 1 row affected (0.01 sec)
3. Rows matched: 1  Changed: 1  Warnings: 0
5. mysql> select * from major;
6. +----+-----------------------+
7. | id | name                  |
8. +----+-----------------------+
9. |  1 | 智能科学与技术        |
10. |  2 | 智能制造              |
11. |  3 | 智能制造              |
12. |  4 | 智能制造              |
13. |  5 | 智能制造              |
14. +----+-----------------------+
15. 5 rows in set (0.00 sec)

复制代码

一次性修改多个字段的值

1. mysql> select * from student;
2. +----+--------+----------+
3. | id | name   | major_id |
4. +----+--------+----------+
5. |  1 | 小明   |        1 |
6. |  2 | 匿名   |        1 |
7. +----+--------+----------+
8. 2 rows in set (0.00 sec)
10. mysql> update student set name='李华',major_id=2 where id = 1;
11. Query OK, 1 row affected (0.01 sec)
12. Rows matched: 1  Changed: 1  Warnings: 0
14. mysql> select * from student;
15. +----+--------+----------+
16. | id | name   | major_id |
17. +----+--------+----------+
18. |  1 | 李华   |        2 |
19. |  2 | 匿名   |        1 |
20. +----+--------+----------+
21. 2 rows in set (0.00 sec)

复制代码

稍微高级一点的使用

1. 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=‘李华’

1. mysql> select id,name as 专业名称 from major;
2. +----+-----------------------+
3. | id | 专业名称              |
4. +----+-----------------------+
5. |  1 | 智能科学与技术        |
6. |  2 | 智能制造              |
7. |  3 | 智能制造              |
8. |  4 | 智能制造              |
9. |  5 | 智能制造              |
10. +----+-----------------------+
11. 5 rows in set (0.00 sec)
13. mysql> select id,name as 专业名称 from major where name='智能制造';
14. +----+--------------+
15. | id | 专业名称     |
16. +----+--------------+
17. |  2 | 智能制造     |
18. |  3 | 智能制造     |
19. |  4 | 智能制造     |
20. |  5 | 智能制造     |
21. +----+--------------+
22. 4 rows in set (0.00 sec)
24. mysql> select id,name as 专业名称 from major where name!='智能制造';
25. +----+-----------------------+
26. | id | 专业名称              |
27. +----+-----------------------+
28. |  1 | 智能科学与技术        |
29. +----+-----------------------+
30. 1 row in set (0.00 sec)
32. mysql> select id,name as 专业名称 from major where not name='智能制造';
33. +----+-----------------------+
34. | id | 专业名称              |
35. +----+-----------------------+
36. |  1 | 智能科学与技术        |
37. +----+-----------------------+
38. 1 row in set (0.00 sec)
40. mysql> select id,name as 专业名称 from major where name='智能制造' and id =2;
41. +----+--------------+
42. | id | 专业名称     |
43. +----+--------------+
44. |  2 | 智能制造     |
45. +----+--------------+
46. 1 row in set (0.00 sec)
48. mysql> select id,name as 专业名称 from major where id < 2;
49. +----+-----------------------+
50. | id | 专业名称              |
51. +----+-----------------------+
52. |  1 | 智能科学与技术        |
53. +----+-----------------------+
54. 1 row in set (0.00 sec)
56. mysql> select id,name as 专业名称 from major where id >= 2;
57. +----+--------------+
58. | id | 专业名称     |
59. +----+--------------+
60. |  2 | 智能制造     |
61. |  3 | 智能制造     |
62. |  4 | 智能制造     |
63. |  5 | 智能制造     |
64. +----+--------------+
65. 4 rows in set (0.00 sec)
67. mysql> select id,name as 专业名称 from major where id >= 2 and id <= 4;
68. +----+--------------+
69. | id | 专业名称     |
70. +----+--------------+
71. |  2 | 智能制造     |
72. |  3 | 智能制造     |
73. |  4 | 智能制造     |
74. +----+--------------+
75. 3 rows in set (0.00 sec)
77. mysql> select id,name as 专业名称 from major where id = 2 or id = 4;
78. +----+--------------+
79. | id | 专业名称     |
80. +----+--------------+
81. |  2 | 智能制造     |
82. |  4 | 智能制造     |
83. +----+--------------+
84. 2 rows in set (0.00 sec)

复制代码

判空

固然，还可以用来判空大概非空，并不是直接=null，而是is null和is not null

1. UPDATE sys_user
2. SET avatar = concat( "https://hahaha/", FLOOR(RAND()*29+1), ".png" )
3. WHERE
4.         avatar IS NULL
5.         OR avatar = "";

复制代码

and和or的使用

1. SELECT
2.         *
3. FROM
4.         chat
5. WHERE
6.         ( from_who = "admin" AND to_who = "user1" )
7.         OR ( to_who = "admin" AND from_who = "user1" )
8. ORDER BY
9.         create_time DESC;

复制代码

数据删除 DELETE

   删除全部数据

1. delete from 表名;

复制代码

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

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

   删除指定数据

1. delete from 表名 where 条件;

复制代码

数据表清空 TRUNCATE

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

1. truncate 表名

复制代码

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

查询一个表中的全部数据

1. select * from 表名;

复制代码

查询指定字段的数据

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

复制代码

注意：能查询部门字段的，千万不要查询*，这样会增长查询时间
给字段取别名

1. mysql> select name from major;
2. +-----------------------+
3. | name                  |
4. +-----------------------+
5. | 智能科学与技术        |
6. | 智能制造              |
7. | 智能制造              |
8. | 智能制造              |
9. | 智能制造              |
10. +-----------------------+
11. 5 rows in set (0.00 sec)
13. mysql> select name as 专业名称 from major;
14. +-----------------------+
15. | 专业名称              |
16. +-----------------------+
17. | 智能科学与技术        |
18. | 智能制造              |
19. | 智能制造              |
20. | 智能制造              |
21. | 智能制造              |
22. +-----------------------+
23. 5 rows in set (0.00 sec)

复制代码

给表取别名

1. mysql> select student.id as 学号,student.name as 姓名,major.name as 专业 from student left join major on student.major_id = major.id;
2. +--------+--------+-----------------------+
3. | 学号   | 姓名   | 专业                  |
4. +--------+--------+-----------------------+
5. |      1 | 李华   | 智能制造              |
6. |      2 | 匿名   | 智能科学与技术        |
7. +--------+--------+-----------------------+
8. 2 rows in set (0.00 sec)
10. 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;
11. +--------+--------+-----------------------+
12. | 学号   | 姓名   | 专业                  |
13. +--------+--------+-----------------------+
14. |      1 | 李华   | 智能制造              |
15. |      2 | 匿名   | 智能科学与技术        |
16. +--------+--------+-----------------------+
17. 2 rows in set (0.00 sec)

复制代码

取别名可以更方便开发者编写sql语句
字符串拼接

1. mysql> select concat('专业名称：',name) from major;
2. +--------------------------------------+
3. | concat('专业名称：',name)            |
4. +--------------------------------------+
5. | 专业名称：智能科学与技术             |
6. | 专业名称：智能制造                   |
7. | 专业名称：智能制造                   |
8. | 专业名称：智能制造                   |
9. | 专业名称：智能制造                   |
10. +--------------------------------------+
11. 5 rows in set (0.00 sec)

复制代码

查询效果数据去重

重复的数据只体现一条

1. mysql> select name as 专业名称 from major;
2. +-----------------------+
3. | 专业名称              |
4. +-----------------------+
5. | 智能科学与技术        |
6. | 智能制造              |
7. | 智能制造              |
8. | 智能制造              |
9. | 智能制造              |
10. +-----------------------+
11. 5 rows in set (0.00 sec)
13. mysql> select distinct name as 专业名称 from major;
14. +-----------------------+
15. | 专业名称              |
16. +-----------------------+
17. | 智能科学与技术        |
18. | 智能制造              |
19. +-----------------------+
20. 2 rows in set (0.00 sec)

复制代码

查询时增长计算

1. mysql> select id,name as 专业名称 from major;
2. +----+-----------------------+
3. | id | 专业名称              |
4. +----+-----------------------+
5. |  1 | 智能科学与技术        |
6. |  2 | 智能制造              |
7. |  3 | 智能制造              |
8. |  4 | 智能制造              |
9. |  5 | 智能制造              |
10. +----+-----------------------+
11. 5 rows in set (0.00 sec)
13. -- 将所有数据的 id 增加 1
14. mysql> select id+1,name as 专业名称 from major;
15. +------+-----------------------+
16. | id+1 | 专业名称              |
17. +------+-----------------------+
18. |    2 | 智能科学与技术        |
19. |    3 | 智能制造              |
20. |    4 | 智能制造              |
21. |    5 | 智能制造              |
22. |    6 | 智能制造              |
23. +------+-----------------------+
24. 5 rows in set (0.00 sec)

复制代码

含糊查询

毗连查询

七种毗连方式

https://blog.csdn.net/laodanqiu/article/details/131233741
自毗连

用途：一个表同时存储了父类数据和子类数据，如省市区表同时存储了中国的全部省、市、区的数据，希望查询出广东省下面的全部城市

1. mysql> desc province_city_region;
2. +-------------+-------------+------+-----+-------------------+-----------------------------------------------+
3. | Field       | Type        | Null | Key | Default           | Extra                                         |
4. +-------------+-------------+------+-----+-------------------+-----------------------------------------------+
5. | id          | bigint      | NO   | PRI | NULL              | auto_increment                                |
6. | create_time | datetime    | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED                             |
7. | update_time | datetime    | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
8. | is_deleted  | tinyint     | YES  |     | 0                 |                                               |
9. | name        | varchar(50) | YES  |     | NULL              |                                               |
10. | type        | tinyint     | YES  |     | NULL              |                                               |
11. | parent_id   | bigint      | YES  |     | NULL              |                                               |
12. +-------------+-------------+------+-----+-------------------+-----------------------------------------------+
13. 7 rows in set (0.00 sec)
15. 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;
16. +------+-----------+-----------+
17. | id   | 市名称    | 省名称    |
18. +------+-----------+-----------+
19. | 4792 | 茂名市    | 广东省    |
20. | 4798 | 湛江市    | 广东省    |
21. | 4808 | 东莞市    | 广东省    |
22. | 4809 | 江门市    | 广东省    |
23. | 4817 | 清远市    | 广东省    |
24. | 4826 | 佛山市    | 广东省    |
25. | 4832 | 阳江市    | 广东省    |
26. | 4837 | 汕头市    | 广东省    |
27. | 4845 | 河源市    | 广东省    |
28. | 4852 | 珠海市    | 广东省    |
29. | 4856 | 汕尾市    | 广东省    |
30. | 4861 | 深圳市    | 广东省    |
31. | 4868 | 梅州市    | 广东省    |
32. | 4877 | 揭阳市    | 广东省    |
33. | 4883 | 韶关市    | 广东省    |
34. | 4894 | 惠州市    | 广东省    |
35. | 4900 | 潮州市    | 广东省    |
36. | 4904 | 广州市    | 广东省    |
37. | 4916 | 肇庆市    | 广东省    |
38. | 4925 | 中山市    | 广东省    |
39. | 4926 | 云浮市    | 广东省    |
40. +------+-----------+-----------+
41. 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（降序）
  

1. mysql> select * from province_city_region order by id asc limit 0,10;
2. +------+---------------------+---------------------+------------+-----------+------+-----------+
3. | id   | create_time         | update_time         | is_deleted | name      | type | parent_id |
4. +------+---------------------+---------------------+------------+-----------+------+-----------+
5. | 2928 | 2023-02-10 20:38:54 | 2023-02-10 20:38:54 |          0 | 山东省    |    0 |         0 |
6. | 2929 | 2023-02-10 20:38:54 | 2023-02-10 20:38:54 |          0 | 莱芜市    |    1 |      2928 |
7. | 2930 | 2023-02-10 20:38:54 | 2023-02-10 20:38:54 |          0 | 莱城区    |    2 |      2929 |
8. | 2931 | 2023-02-10 20:38:54 | 2023-02-10 20:38:54 |          0 | 钢城区    |    2 |      2929 |
9. | 2932 | 2023-02-10 20:38:54 | 2023-02-10 20:38:54 |          0 | 济南市    |    1 |      2928 |
10. | 2933 | 2023-02-10 20:38:54 | 2023-02-10 20:38:54 |          0 | 历城区    |    2 |      2932 |
11. | 2934 | 2023-02-10 20:38:54 | 2023-02-10 20:38:54 |          0 | 历下区    |    2 |      2932 |
12. | 2935 | 2023-02-10 20:38:54 | 2023-02-10 20:38:54 |          0 | 长清区    |    2 |      2932 |
13. | 2936 | 2023-02-10 20:38:54 | 2023-02-10 20:38:54 |          0 | 平阴县    |    2 |      2932 |
14. | 2937 | 2023-02-10 20:38:54 | 2023-02-10 20:38:54 |          0 | 章丘市    |    1 |      2932 |
15. +------+---------------------+---------------------+------------+-----------+------+-----------+
16. 10 rows in set (0.00 sec)
18. mysql> select * from province_city_region order by id desc limit 0,10;
19. +------+---------------------+---------------------+------------+--------------------------+------+-----------+
20. | id   | create_time         | update_time         | is_deleted | name                     | type | parent_id |
21. +------+---------------------+---------------------+------------+--------------------------+------+-----------+
22. | 5851 | 2023-02-10 20:39:11 | 2023-02-10 20:39:11 |          0 | 天峨县                   |    2 |      5840 |
23. | 5850 | 2023-02-10 20:39:11 | 2023-02-10 20:39:11 |          0 | 凤山县                   |    2 |      5840 |
24. | 5849 | 2023-02-10 20:39:11 | 2023-02-10 20:39:11 |          0 | 南丹县                   |    2 |      5840 |
25. | 5848 | 2023-02-10 20:39:11 | 2023-02-10 20:39:11 |          0 | 环江毛南族自治县         |    2 |      5840 |
26. | 5847 | 2023-02-10 20:39:11 | 2023-02-10 20:39:11 |          0 | 巴马瑶族自治县           |    2 |      5840 |
27. | 5846 | 2023-02-10 20:39:11 | 2023-02-10 20:39:10 |          0 | 东兰县                   |    2 |      5840 |
28. | 5845 | 2023-02-10 20:39:11 | 2023-02-10 20:39:10 |          0 | 金城江区                 |    2 |      5840 |
29. | 5844 | 2023-02-10 20:39:11 | 2023-02-10 20:39:10 |          0 | 罗城仫佬族自治县         |    2 |      5840 |
30. | 5843 | 2023-02-10 20:39:11 | 2023-02-10 20:39:10 |          0 | 宜州市                   |    1 |      5840 |
31. | 5842 | 2023-02-10 20:39:11 | 2023-02-10 20:39:10 |          0 | 都安瑶族自治县           |    2 |      5840 |
32. +------+---------------------+---------------------+------------+--------------------------+------+-----------+
33. 10 rows in set (0.00 sec)

复制代码

子查询

注意：子查询的效率高于连表查询
查询常用函数

【数学运算】

1. SELECT ABS(-8)--绝对值
2. SELECT CEILING(9.4)--向上取整
3. SELECT FLOOR(9.4)--向下取整
4. SELECT RAND()一一返回一个0~1之间的随机数
5. SELECT SIGN (10)一一判断一个数的符号(负数返回-1，正数返回1,0返回0)

复制代码

【字符串函数】

1. SELECT CHAR_LENGTH('即使再小的帆也能远航')--字符串长度
2. SELECT CONCAT('哈'，'哈'，'哈')--拼接字符串
3. SELECT INSERT('我爱编程helloworld',1,2,'超级热爱')--从某个位置开始替换某个长度
4. SELECT LOWER('Abc')--转化为小写字母
5. SELECT UPPER('Abc')--转化为大写字母
6. SELECT INSTR('Abc','bc')--返回第一次出现的子串的索引
7. SELECT REPLACE('你好'，'好'，'坏')--替换出现的指定字符串
8. SELECT SUBSTR('你吃饭了吗'，1,3)--返回指定的子字符串（源字符串，截取的位置，截取的长度）
9. SELECT REVERSE('我和你')--反转
10. -- 查询学生，将查询出来的数据姓氏进行替换
11. SELECT REPLACE(student_name,'王'，'玩')FROM student

复制代码

【时间和日期函数】

1. SELECT CURRENT_DATE()--获取当前日期
2. SELECT CURDATE()-一获取当前日期
3. SELECT NOW()--获取当前的时间
4. SELECT LOCALTIME()--本地时间
5. SELECT SYSDATE()-一系统时间
6. SELECT YEAR(NOW())
7. SELECT MONTH(NOW())
8. SELECT DAY(NOW())
9. SELECT HOUR(NOW())
10. SELECT MINUTE(NOW())
11. SELECT SECOND(NOW())

复制代码

【数据库相干】

1. SELECT SYSTEM USER();
2. SELECT USER();
3. SELECT VERSION();--查询数据库版本

复制代码

【统计相干】

1. 都能够统计表中的数据（想查询一个表中有多少个记录，就使用这个cout())
2. SELECT COUNT(`BornDate`) FROM student;--Count(字段)，会忽略所有的nul1值
3. SELECT COUNT(*) FROM student;--Count(*),不会忽略null值，本质计算行数
4. SELECT COUNT(1) FROM result;--Count(1),不会忽略忽略所有的nul1值，本质计算行数
5. SELECT SUM(`StudentResult`) AS 总和 FROM result;
6. SELECT AVG(`StudentResult`) AS 平均分 FROM result;
7. SELECT MAX(`StudentResult`) AS 最高分 FROM result;
8. SELECT MIN(`StudentResult`) As 最低分 FROM result;

复制代码

注意事项

1. UPDATE sys_user
2. SET avatar = concat( "https://hahaha/", FLOOR(RAND()*29+1), ".png" )
3. WHERE
4.         avatar IS NULL
5.         OR avatar = "";

复制代码

使用函数可能导致字段的索引失效，造成效率下降，建议直接读出来，然后用java举行处理
分组过滤

1. mysql> select name,gender from user limit 0,10;
2. +-----------------+--------+
3. | name            | gender |
4. +-----------------+--------+
5. | 系统管理员      |      0 |
6. | 企业管理员      |      0 |
7. | 门店管理员      |      0 |
8. | 郗淑            |      0 |
9. | 柳之            |      1 |
10. | 夏侯之          |      1 |
11. | 吉滢            |      0 |
12. | 独孤杰          |      1 |
13. | 蒙飘            |      0 |
14. | 卫柔            |      0 |
15. +-----------------+--------+
16. 10 rows in set (0.00 sec)
18. -- 根据 gender 进行分组
19. mysql> select name,gender from user group by gender limit 0,10;
20. +-----------------+--------+
21. | name            | gender |
22. +-----------------+--------+
23. | 系统管理员      |      0 |
24. | 柳之            |      1 |
25. +-----------------+--------+
26. 2 rows in set (0.01 sec)

复制代码

能使用where，就不要使用having，否则可能导致索引失效
SELECT语法完整结构

[]：代表可选
{}：代表必选
数据加密

【建表sql】

1. DROP TABLE
2. IF
3.         EXISTS `user`;
4. CREATE TABLE `user` (
5.         `id` BIGINT NOT NULL auto_increment COMMENT '主键' PRIMARY KEY,
6.         `create_time` datetime DEFAULT CURRENT_TIMESTAMP NOT NULL COMMENT '创建时间',
7.         `update_time` datetime DEFAULT CURRENT_TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
8.         `is_deleted` TINYINT DEFAULT 0 NULL COMMENT '是否删除 0：未删除 1：已删除',
9.         `username` VARCHAR ( 50 ) DEFAULT NULL COMMENT '用户名',
10.         `password` VARCHAR ( 255 ) NOT NULL COMMENT '密码',
11.         `gender` TINYINT DEFAULT 0 NULL COMMENT '性别 0：男 1：女',
12.         `age` INT DEFAULT NULL COMMENT '年龄'
13. ) COMMENT '用户表';
15. -- 插入数据
16. mysql> insert into user(`username`,`password`,`gender`,`age`) values('admin','123456',0,18),('hello','123456',0,28),('word','123456',1,17);
17. Query OK, 3 rows affected (0.01 sec)
18. Records: 3  Duplicates: 0  Warnings: 0

复制代码

【密码MD5加密操作】

1. mysql> select * from user;
2. +----+---------------------+---------------------+------------+----------+----------+--------+------+
3. | id | create_time         | update_time         | is_deleted | username | password | gender | age  |
4. +----+---------------------+---------------------+------------+----------+----------+--------+------+
5. |  1 | 2023-07-05 10:16:16 | 2023-07-05 10:16:16 |          0 | admin    | 123456   |      0 |   18 |
6. |  2 | 2023-07-05 10:16:16 | 2023-07-05 10:16:16 |          0 | hello    | 123456   |      0 |   28 |
7. |  3 | 2023-07-05 10:16:16 | 2023-07-05 10:16:16 |          0 | word     | 123456   |      1 |   17 |
8. +----+---------------------+---------------------+------------+----------+----------+--------+------+
9. 3 rows in set (0.00 sec)
11. -- 加密id=1的数据的密码
12. mysql> update user set password=MD5(password) where id=1;
13. Query OK, 1 row affected (0.01 sec)
14. Rows matched: 1  Changed: 1  Warnings: 0
16. mysql> select * from user;
17. +----+---------------------+---------------------+------------+----------+----------------------------------+--------+------+
18. | id | create_time         | update_time         | is_deleted | username | password                         | gender | age  |
19. +----+---------------------+---------------------+------------+----------+----------------------------------+--------+------+
20. |  1 | 2023-07-05 10:16:16 | 2023-07-05 10:18:38 |          0 | admin    | e10adc3949ba59abbe56e057f20f883e |      0 |   18 |
21. |  2 | 2023-07-05 10:16:16 | 2023-07-05 10:16:16 |          0 | hello    | 123456                           |      0 |   28 |
22. |  3 | 2023-07-05 10:16:16 | 2023-07-05 10:16:16 |          0 | word     | 123456                           |      1 |   17 |
23. +----+---------------------+---------------------+------------+----------+----------------------------------+--------+------+
24. 3 rows in set (0.00 sec)
26. -- 加密所有数据的密码
27. mysql> update user set password=MD5(password);
28. Query OK, 3 rows affected (0.01 sec)
29. Rows matched: 3  Changed: 3  Warnings: 0
31. mysql> select * from user;
32. +----+---------------------+---------------------+------------+----------+----------------------------------+--------+------+
33. | id | create_time         | update_time         | is_deleted | username | password                         | gender | age  |
34. +----+---------------------+---------------------+------------+----------+----------------------------------+--------+------+
35. |  1 | 2023-07-05 10:16:16 | 2023-07-05 10:19:01 |          0 | admin    | 14e1b600b1fd579f47433b88e8d85291 |      0 |   18 |
36. |  2 | 2023-07-05 10:16:16 | 2023-07-05 10:19:01 |          0 | hello    | e10adc3949ba59abbe56e057f20f883e |      0 |   28 |
37. |  3 | 2023-07-05 10:16:16 | 2023-07-05 10:19:01 |          0 | word     | e10adc3949ba59abbe56e057f20f883e |      1 |   17 |
38. +----+---------------------+---------------------+------------+----------+----------------------------------+--------+------+
39. 3 rows in set (0.00 sec)
41. -- 插入数据的时候就执行加密
42. mysql> insert into user(`username`,`password`,`gender`,`age`) values('md5',MD5('123456'),0,18);
43. Query OK, 1 row affected (0.01 sec)
45. mysql> select * from user;
46. +----+---------------------+---------------------+------------+----------+----------------------------------+--------+------+
47. | id | create_time         | update_time         | is_deleted | username | password                         | gender | age  |
48. +----+---------------------+---------------------+------------+----------+----------------------------------+--------+------+
49. |  1 | 2023-07-05 10:16:16 | 2023-07-05 10:19:01 |          0 | admin    | 14e1b600b1fd579f47433b88e8d85291 |      0 |   18 |
50. |  2 | 2023-07-05 10:16:16 | 2023-07-05 10:19:01 |          0 | hello    | e10adc3949ba59abbe56e057f20f883e |      0 |   28 |
51. |  3 | 2023-07-05 10:16:16 | 2023-07-05 10:19:01 |          0 | word     | e10adc3949ba59abbe56e057f20f883e |      1 |   17 |
52. |  4 | 2023-07-05 10:19:46 | 2023-07-05 10:19:46 |          0 | md5      | e10adc3949ba59abbe56e057f20f883e |      0 |   18 |
53. +----+---------------------+---------------------+------------+----------+----------------------------------+--------+------+
54. 4 rows in set (0.00 sec)

复制代码

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

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。