一：显示数据库及模糊查询（like） 

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db_classes         |
| db_user            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)mysql> show databases like "db%";
+----------------+
| Database (db%) |
+----------------+
| db_classes     |
| db_user        |
+----------------+
2 rows in set (0.01 sec)

mysql> use db_user;
Database changed
mysql> show tables;
+-------------------+
| Tables_in_db_user |
+-------------------+
| db_info           |
+-------------------+
1 row in set (0.00 sec)mysql> select * from db_info;
+----+------+--------+------+---------+
| id | name | gender | age  | address |
+----+------+--------+------+---------+
|  1 | zs   | 男     |   18 | 西安    |
|  2 | zs1  | 女     |   18 | NULL    |
|  3 | zs2  | 女     |   18 | NULL    |
+----+------+--------+------+---------+
3 rows in set (0.00 sec)

mysql> desc db_info;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int          | NO   | PRI | NULL    |       |
| name    | varchar(255) | NO   |     | NULL    |       |
| gender  | char(1)      | YES  |     | NULL    |       |
| age     | int          | YES  |     | 18      |       |
| address | varchar(255) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> use db_classes;
Database changed
mysql> show tables;
+----------------------+
| Tables_in_db_classes |
+----------------------+
| db_hero              |
+----------------------+
1 row in set (0.00 sec)mysql> select *from db_hero;
+------+--------------+--------------+------+--------+-----------+--------------+--------------+--------+
| id   | name         | nickname     | age  | gender | address   | wuqi         | world        | types  |
+------+--------------+--------------+------+--------+-----------+--------------+--------------+--------+
|    1 | 孙悟空       | 齐天大圣     |  500 | 男     | 花果山    | 金箍棒       | 西游世界     | 妖族   |
|    2 | 猪刚鬣       | 猪八戒       |  600 | 男     | 高老庄    | 九齿钉耙     | 西游世界     | 妖族   |
|    3 | 武松         | 打虎哥       |   30 | 男     | 吴国      | 拳头         | 水浒世界     | 人族   |
|    4 | 鲁智深       | 杨柳哥       |   20 | 男     | 吴国      | 铲杖         | 水浒世界     | 人族   |
|    5 | 太白金星     | 太上老君     | 5000 | 男     | 天庭      | 毛笔         | 西游世界     | 仙族   |
+------+--------------+--------------+------+--------+-----------+--------------+--------------+--------+
5 rows in set (0.01 sec)mysql> desc db_hero;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int          | YES  |     | NULL    |       |
| name     | varchar(100) | YES  |     | NULL    |       |
| nickname | varchar(100) | YES  |     | NULL    |       |
| age      | int          | YES  |     | NULL    |       |
| gender   | varchar(20)  | YES  |     | NULL    |       |
| address  | varchar(100) | YES  |     | NULL    |       |
| wuqi     | varchar(100) | YES  |     | NULL    |       |
| world    | varchar(100) | YES  |     | NULL    |       |
| types    | varchar(20)  | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
9 rows in set (0.00 sec)

二：插入数据到数据库表中

mysql> insert into db_hero values(null,"武大郎","大朗",25,"男","吴国","无","水浒世界","人族");
Query OK, 1 row affected (0.00 sec)

mysql> select * from db_hero;
+------+--------------+--------------+------+--------+-----------+--------------+--------------+--------+
| id   | name         | nickname     | age  | gender | address   | wuqi         | world        | types  |
+------+--------------+--------------+------+--------+-----------+--------------+--------------+--------+
|    1 | 孙悟空       | 齐天大圣     |  500 | 男     | 花果山    | 金箍棒       | 西游世界     | 妖族   |
|    2 | 猪刚鬣       | 猪八戒       |  600 | 男     | 高老庄    | 九齿钉耙     | 西游世界     | 妖族   |
|    3 | 武松         | 打虎哥       |   30 | 男     | 吴国      | 拳头         | 水浒世界     | 人族   |
|    4 | 鲁智深       | 杨柳哥       |   20 | 男     | 吴国      | 铲杖         | 水浒世界     | 人族   |
|    5 | 太白金星     | 太上老君     | 5000 | 男     | 天庭      | 毛笔         | 西游世界     | 仙族   |
| NULL | 武大郎       | 大朗         |   25 | 男     | 吴国      | 无           | 水浒世界     | 人族   |
+------+--------------+--------------+------+--------+-----------+--------------+--------------+--------+
6 rows in set (0.00 sec)

mysql> insert into db_hero values(7,"武大郎","大朗",25,"男","吴国","无","水浒世界","人族");
Query OK, 1 row affected (0.01 sec)

mysql> select * from db_hero;
+------+--------------+--------------+------+--------+-----------+--------------+--------------+--------+
| id   | name         | nickname     | age  | gender | address   | wuqi         | world        | types  |
+------+--------------+--------------+------+--------+-----------+--------------+--------------+--------+
|    1 | 孙悟空       | 齐天大圣     |  500 | 男     | 花果山    | 金箍棒       | 西游世界     | 妖族   |
|    2 | 猪刚鬣       | 猪八戒       |  600 | 男     | 高老庄    | 九齿钉耙     | 西游世界     | 妖族   |
|    3 | 武松         | 打虎哥       |   30 | 男     | 吴国      | 拳头         | 水浒世界     | 人族   |
|    4 | 鲁智深       | 杨柳哥       |   20 | 男     | 吴国      | 铲杖         | 水浒世界     | 人族   |
|    5 | 太白金星     | 太上老君     | 5000 | 男     | 天庭      | 毛笔         | 西游世界     | 仙族   |
| NULL | 武大郎       | 大朗         |   25 | 男     | 吴国      | 无           | 水浒世界     | 人族   |
|    7 | 武大郎       | 大朗         |   25 | 男     | 吴国      | 无           | 水浒世界     | 人族   |
+------+--------------+--------------+------+--------+-----------+--------------+--------------+--------+
7 rows in set (0.00 sec)

三：删除数据库表中的数据

mysql> delete from db_hero where id=1;
Query OK, 1 row affected (0.01 sec)

mysql> select * from db_hero;
+------+--------------+--------------+------+--------+-----------+--------------+--------------+--------+
| id   | name         | nickname     | age  | gender | address   | wuqi         | world        | types  |
+------+--------------+--------------+------+--------+-----------+--------------+--------------+--------+
|    2 | 猪刚鬣       | 猪八戒       |  600 | 男     | 高老庄    | 九齿钉耙     | 西游世界     | 妖族   |
|    3 | 武松         | 打虎哥       |   30 | 男     | 吴国      | 拳头         | 水浒世界     | 人族   |
|    4 | 鲁智深       | 杨柳哥       |   20 | 男     | 吴国      | 铲杖         | 水浒世界     | 人族   |
|    5 | 太白金星     | 太上老君     | 5000 | 男     | 天庭      | 毛笔         | 西游世界     | 仙族   |
| NULL | 武大郎       | 大朗         |   25 | 男     | 吴国      | 无           | 水浒世界     | 人族   |
|    7 | 武大郎       | 大朗         |   25 | 男     | 吴国      | 无           | 水浒世界     | 人族   |
|    8 | 红孩儿       | 无           |   25 | 男     | 妖界      | 无           | 西游世界     | 仙族   |
| NULL | 金角大王     | NULL         | NULL | NULL   | NULL      | NULL         | NULL         | NULL   |
| NULL | 银角大王     | NULL         | NULL | 男     | NULL      | NULL         | NULL         | NULL   |
| NULL | 银角大王     | NULL         | NULL | 男     | NULL      | NULL         | NULL         | NULL   |
| NULL | 孙尚香       | NULL         | NULL | 女     | NULL      | NULL         | NULL         | NULL   |
| NULL | 马超         | NULL         | NULL | 男     | NULL      | NULL         | NULL         | NULL   |
+------+--------------+--------------+------+--------+-----------+--------------+--------------+--------+
12 rows in set (0.00 sec)

mysql> delete from db_hero where name="银角大王";
Query OK, 2 rows affected (0.00 sec)

mysql> select * from db_hero;
+------+--------------+--------------+------+--------+-----------+--------------+--------------+--------+
| id   | name         | nickname     | age  | gender | address   | wuqi         | world        | types  |
+------+--------------+--------------+------+--------+-----------+--------------+--------------+--------+
|    2 | 猪刚鬣       | 猪八戒       |  600 | 男     | 高老庄    | 九齿钉耙     | 西游世界     | 妖族   |
|    3 | 武松         | 打虎哥       |   30 | 男     | 吴国      | 拳头         | 水浒世界     | 人族   |
|    4 | 鲁智深       | 杨柳哥       |   20 | 男     | 吴国      | 铲杖         | 水浒世界     | 人族   |
|    5 | 太白金星     | 太上老君     | 5000 | 男     | 天庭      | 毛笔         | 西游世界     | 仙族   |
| NULL | 武大郎       | 大朗         |   25 | 男     | 吴国      | 无           | 水浒世界     | 人族   |
|    7 | 武大郎       | 大朗         |   25 | 男     | 吴国      | 无           | 水浒世界     | 人族   |
|    8 | 红孩儿       | 无           |   25 | 男     | 妖界      | 无           | 西游世界     | 仙族   |
| NULL | 金角大王     | NULL         | NULL | NULL   | NULL      | NULL         | NULL         | NULL   |
| NULL | 孙尚香       | NULL         | NULL | 女     | NULL      | NULL         | NULL         | NULL   |
| NULL | 马超         | NULL         | NULL | 男     | NULL      | NULL         | NULL         | NULL   |
+------+--------------+--------------+------+--------+-----------+--------------+--------------+--------+
10 rows in set (0.00 sec)

mysql> use db_user;
Database changed
mysql> show tables;
+-------------------+
| Tables_in_db_user |
+-------------------+
| db_info           |
+-------------------+
1 row in set (0.00 sec)mysql> select *from db_info;
+----+------+--------+------+---------+
| id | name | gender | age  | address |
+----+------+--------+------+---------+
|  1 | zs   | 男     |   18 | 西安    |
|  2 | zs1  | 女     |   18 | NULL    |
|  3 | zs2  | 女     |   18 | NULL    |
+----+------+--------+------+---------+
3 rows in set (0.00 sec)

3.1truncate指令

该指令也可以删除数据，注意：这种数据删除不通过数据字典，是无法恢复的，请慎用！！！！

主要使用场景：清除测试数据！！！！

mysql> truncate db_info;
Query OK, 0 rows affected (0.02 sec)

mysql> select *from db_info;
Empty set (0.01 sec)mysql> desc db_info;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int          | NO   | PRI | NULL    |       |
| name    | varchar(255) | NO   |     | NULL    |       |
| gender  | char(1)      | YES  |     | NULL    |       |
| age     | int          | YES  |     | 18      |       |
| address | varchar(255) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> insert into db_info values(1,"张三","男",18,"西安");
Query OK, 1 row affected (0.00 sec)mysql> select * from db_info;
+----+--------+--------+------+---------+
| id | name   | gender | age  | address |
+----+--------+--------+------+---------+
|  1 | 张三   | 男     |   18 | 西安    |
+----+--------+--------+------+---------+
1 row in set (0.00 sec)

mysql> insert into db_info values(2,"离李四","男",18,"西安");
Query OK, 1 row affected (0.01 sec)mysql> insert into db_info values(三,"王五","男",18,"西安");
ERROR 1054 (42S22): Unknown column '三' in 'field list'
mysql> insert into db_info values(3,"王五","男",18,"西安");
Query OK, 1 row affected (0.01 sec)mysql> insert into db_info values(4,"孙尚香","女",18,"西安");
Query OK, 1 row affected (0.01 sec)mysql> insert into db_info values(5,"鲁智深","男",18,"西安");
Query OK, 1 row affected (0.01 sec)mysql> select* from db_info;
+----+-----------+--------+------+---------+
| id | name      | gender | age  | address |
+----+-----------+--------+------+---------+
|  1 | 张三      | 男     |   18 | 西安    |
|  2 | 离李四    | 男     |   18 | 西安    |
|  3 | 王五      | 男     |   18 | 西安    |
|  4 | 孙尚香    | 女     |   18 | 西安    |
|  5 | 鲁智深    | 男     |   18 | 西安    |
+----+-----------+--------+------+---------+
5 rows in set (0.00 sec)

四： 增加数据

4.1update指令

update 表名称 set 字段名称=新的值 [,字段=xxx [,……] [where 条件];

mysql> update db_info set age=44 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0mysql> update db_info set age=55 where id="2";
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from db_info;
+----+-----------+--------+------+---------+
| id | name      | gender | age  | address |
+----+-----------+--------+------+---------+
|  1 | 张三      | 男     |   44 | 西安    |
|  2 | 离李四    | 男     |   55 | 西安    |
|  3 | 王五      | 男     |   18 | 西安    |
|  4 | 孙尚香    | 女     |   18 | 西安    |
|  5 | 鲁智深    | 男     |   18 | 西安    |
+----+-----------+--------+------+---------+
5 rows in set (0.00 sec)

mysql> update db_info set age = age+1;
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5  Changed: 5  Warnings: 0mysql> select * from db_info;
+----+-----------+--------+------+---------+
| id | name      | gender | age  | address |
+----+-----------+--------+------+---------+
|  1 | 张三      | 男     |   45 | 西安    |
|  2 | 离李四    | 男     |   56 | 西安    |
|  3 | 王五      | 男     |   19 | 西安    |
|  4 | 孙尚香    | 女     |   19 | 西安    |
|  5 | 鲁智深    | 男     |   19 | 西安    |
+----+-----------+--------+------+---------+
5 rows in set (0.00 sec)mysql> update db_info set address="四川绵阳";
Query OK, 5 rows affected (0.01 sec)
Rows matched: 5  Changed: 5  Warnings: 0mysql> select *from db_info;
+----+-----------+--------+------+--------------+
| id | name      | gender | age  | address      |
+----+-----------+--------+------+--------------+
|  1 | 张三      | 男     |   45 | 四川绵阳     |
|  2 | 离李四    | 男     |   56 | 四川绵阳     |
|  3 | 王五      | 男     |   19 | 四川绵阳     |
|  4 | 孙尚香    | 女     |   19 | 四川绵阳     |
|  5 | 鲁智深    | 男     |   19 | 四川绵阳     |
+----+-----------+--------+------+--------------+
5 rows in set (0.00 sec)

4.2replace语句

replace语句结构和insert的语法结构一模一样

replace into 表名称[(字段1 [,字段2, ……])] {values|value}(字段值, [……]) [,()];
​

注意：replace 的sql语句，是集更新和插入于一体的一个SQL。

如果插入的数据不存在（主键、unqiue修饰的字段），执行insert执行

如果插入的数据存在（主键、unqiue修饰的字段），则先执行删除语句，再执行insert语句。

mysql> replace into db_info(id,name,gender) values(6,"曹操","男");
Query OK, 1 row affected (0.01 sec)mysql> select *from db_info;
+----+-----------+--------+------+--------------+
| id | name      | gender | age  | address      |
+----+-----------+--------+------+--------------+
|  1 | 张三      | 男     |   45 | 四川绵阳     |
|  2 | 离李四    | 男     |   56 | 四川绵阳     |
|  3 | 王五      | 男     |   19 | 四川绵阳     |
|  4 | 孙尚香    | 女     |   19 | 四川绵阳     |
|  5 | 鲁智深    | 男     |   19 | 四川绵阳     |
|  6 | 曹操      | 男     |   18 | NULL         |
+----+-----------+--------+------+--------------+
6 rows in set (0.00 sec)mysql> replace into db_info(id,name,gender) values(7,"张飞","男");
Query OK, 1 row affected (0.01 sec)mysql> select *from db_info;
+----+-----------+--------+------+--------------+
| id | name      | gender | age  | address      |
+----+-----------+--------+------+--------------+
|  1 | 张三      | 男     |   45 | 四川绵阳     |
|  2 | 离李四    | 男     |   56 | 四川绵阳     |
|  3 | 王五      | 男     |   19 | 四川绵阳     |
|  4 | 孙尚香    | 女     |   19 | 四川绵阳     |
|  5 | 鲁智深    | 男     |   19 | 四川绵阳     |
|  6 | 曹操      | 男     |   18 | NULL         |
|  7 | 张飞      | 男     |   18 | NULL         |
+----+-----------+--------+------+--------------+
7 rows in set (0.00 sec)

 五：查询语句（select）

使用select查询数据库表中的某些数据。

select 语法结构

select {字段|函数|其他} from 表名称 [where]

mysql> select 56;
+----+
| 56 |
+----+
| 56 |
+----+
1 row in set (0.00 sec)mysql> select 56+45;
+-------+
| 56+45 |
+-------+
|   101 |
+-------+
1 row in set (0.00 sec)mysql> select 56*45;
+-------+
| 56*45 |
+-------+
|  2520 |
+-------+
1 row in set (0.01 sec)mysql> select 56-45;
+-------+
| 56-45 |
+-------+
|    11 |
+-------+
1 row in set (0.01 sec)mysql> select 56%45;
+-------+
| 56%45 |
+-------+
|    11 |
+-------+
1 row in set (0.00 sec)mysql> select 100+"张三";
+--------------+
| 100+"张三"   |
+--------------+
|          100 |
+--------------+
1 row in set, 1 warning (0.01 sec)mysql> select "100"+200-> ;
+-----------+
| "100"+200 |
+-----------+
|       300 |
+-----------+
1 row in set (0.00 sec)mysql> select 100+"200张三";
+-----------------+
| 100+"200张三"   |
+-----------------+
|             300 |
+-----------------+
1 row in set, 1 warning (0.00 sec)mysql> select 100+"张三100";
+-----------------+
| 100+"张三100"   |
+-----------------+
|             100 |
+-----------------+
1 row in set, 1 warning (0.00 sec)

+----------+
| null + 1 |
+----------+
|     NULL |
+----------+
1 row in set (0.01 sec)mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)mysql> select database();
+------------+
| database() |
+------------+
| db_user    |
+------------+
1 row in set (0.00 sec)mysql> select uuid();
+--------------------------------------+
| uuid()                               |
+--------------------------------------+
| ce2e757d-11de-11ef-8f71-0a0027000012 |
+--------------------------------------+
1 row in set (0.02 sec)mysql> select uuid();
+--------------------------------------+
| uuid()                               |
+--------------------------------------+
| e4f66188-11de-11ef-8f71-0a0027000012 |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> select * from db_info;
+----+-----------+--------+------+--------------+
| id | name      | gender | age  | address      |
+----+-----------+--------+------+--------------+
|  1 | 张三      | 男     |   45 | 四川绵阳     |
|  2 | 离李四    | 男     |   56 | 四川绵阳     |
|  3 | 王五      | 男     |   19 | 四川绵阳     |
|  4 | 孙尚香    | 女     |   19 | 四川绵阳     |
|  5 | 鲁智深    | 男     |   19 | 四川绵阳     |
|  6 | 曹操      | 男     |   18 | NULL         |
|  7 | 张飞      | 男     |   18 | NULL         |
+----+-----------+--------+------+--------------+
7 rows in set (0.00 sec)mysql> select id,name from db_info;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 张三      |
|  2 | 离李四    |
|  3 | 王五      |
|  4 | 孙尚香    |
|  5 | 鲁智深    |
|  6 | 曹操      |
|  7 | 张飞      |
+----+-----------+
7 rows in set (0.00 sec)mysql> select id from db_info;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
+----+
7 rows in set (0.00 sec)

5.1查询当前时间

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2024-05-15 16:14:39 |
+---------------------+
1 row in set (0.00 sec)

5.2模糊查询（like）

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db_classes         |
| db_user            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

5.2.1查询以db_开头的数据库

mysql> show databases like 'db_%';
+-----------------+
| Database (db_%) |
+-----------------+
| db_classes      |
| db_user         |
+-----------------+
2 rows in set (0.00 sec)

5.2.2查询数据库表中以张开头的信息

mysql> select * from db_info where name like "张%";
+----+--------+--------+------+--------------+
| id | name   | gender | age  | address      |
+----+--------+--------+------+--------------+
|  1 | 张三   | 男     |   45 | 四川绵阳     |
|  7 | 张飞   | 男     |   18 | NULL         |
+----+--------+--------+------+--------------+
2 rows in set (0.01 sec)

5.2.3查询数据库表中以香结尾的信息

mysql> select * from db_info where name like "%香";
+----+-----------+--------+------+--------------+
| id | name      | gender | age  | address      |
+----+-----------+--------+------+--------------+
|  4 | 孙尚香    | 女     |   19 | 四川绵阳     |
+----+-----------+--------+------+--------------+
1 row in set (0.00 sec)

5.2.4查询数据库表中含有“香”的信息

mysql> select * from db_info where name like "%香%";
+----+-----------+--------+------+--------------+
| id | name      | gender | age  | address      |
+----+-----------+--------+------+--------------+
|  4 | 孙尚香    | 女     |   19 | 四川绵阳     |
+----+-----------+--------+------+--------------+
1 row in set (0.00 sec)mysql> select * from db_info where name like "%尚%";
+----+-----------+--------+------+--------------+
| id | name      | gender | age  | address      |
+----+-----------+--------+------+--------------+
|  4 | 孙尚香    | 女     |   19 | 四川绵阳     |
+----+-----------+--------+------+--------------+
1 row in set (0.00 sec)mysql> select * from db_info where name like "%张%";
+----+--------+--------+------+--------------+
| id | name   | gender | age  | address      |
+----+--------+--------+------+--------------+
|  1 | 张三   | 男     |   45 | 四川绵阳     |
|  7 | 张飞   | 男     |   18 | NULL         |
+----+--------+--------+------+--------------+
2 rows in set (0.00 sec)mysql> insert into db_info(id,name) values(8,"秀秀");
Query OK, 1 row affected (0.00 sec)mysql> select * from db_info;
+----+-----------+--------+------+--------------+
| id | name      | gender | age  | address      |
+----+-----------+--------+------+--------------+
|  1 | 张三      | 男     |   45 | 四川绵阳     |
|  2 | 离李四    | 男     |   56 | 四川绵阳     |
|  3 | 王五      | 男     |   19 | 四川绵阳     |
|  4 | 孙尚香    | 女     |   19 | 四川绵阳     |
|  5 | 鲁智深    | 男     |   19 | 四川绵阳     |
|  6 | 曹操      | 男     |   18 | NULL         |
|  7 | 张飞      | 男     |   18 | NULL         |
|  8 | 秀秀      | NULL   |   18 | NULL         |
+----+-----------+--------+------+--------------+
8 rows in set (0.00 sec)

5.3查询名字第二个字为”秀“的信息

mysql> select * from db_info where name like "_秀%";
+----+--------+--------+------+---------+
| id | name   | gender | age  | address |
+----+--------+--------+------+---------+
|  8 | 秀秀   | NULL   |   18 | NULL    |
+----+--------+--------+------+---------+
1 row in set (0.00 sec)

5.4查询以张开头的信息

mysql> select * from db_info where name like "_秀%";
+----+--------+--------+------+---------+
| id | name   | gender | age  | address |
+----+--------+--------+------+---------+
|  8 | 秀秀   | NULL   |   18 | NULL    |
+----+--------+--------+------+---------+
1 row in set (0.00 sec)

5.5查询以深结尾的信息

mysql> select * from db_info where name regexp "深$";
+----+-----------+--------+------+--------------+
| id | name      | gender | age  | address      |
+----+-----------+--------+------+--------------+
|  5 | 鲁智深    | 男     |   19 | 四川绵阳     |
+----+-----------+--------+------+--------------+
1 row in set (0.00 sec)

mysql> select gender from db_info;
+--------+
| gender |
+--------+
| 男     |
| 男     |
| 男     |
| 女     |
| 男     |
| 男     |
| 男     |
| NULL   |
+--------+
8 rows in set (0.00 sec)mysql> select distinct gender from db_info;
+--------+
| gender |
+--------+
| 男     |
| 女     |
| NULL   |
+--------+
3 rows in set (0.01 sec)mysql> select gender from db_info group by gender;
+--------+
| gender |
+--------+
| 男     |
| 女     |
| NULL   |
+--------+
3 rows in set (0.00 sec)

六：聚会函数-count
统计数据

select count(id) from user;
select count(gender) from user;
select count(id) from user;
select count(1) from user;
select count(1) from user where gender="男";

mysql> use db_user;
Database changed
mysql> select * from db_info;
+----+-----------+--------+------+--------------+
| id | name      | gender | age  | address      |
+----+-----------+--------+------+--------------+
|  1 | 张三      | 男     |   45 | 四川绵阳     |
|  2 | 离李四    | 男     |   56 | 四川绵阳     |
|  3 | 王五      | 男     |   19 | 四川绵阳     |
|  4 | 孙尚香    | 女     |   19 | 四川绵阳     |
|  5 | 鲁智深    | 男     |   19 | 四川绵阳     |
|  6 | 曹操      | 男     |   18 | NULL         |
|  7 | 张飞      | 男     |   18 | NULL         |
|  8 | 秀秀      | NULL   |   18 | NULL         |
+----+-----------+--------+------+--------------+
8 rows in set (0.00 sec)mysql> select count(*) from db_info;
+----------+
| count(*) |
+----------+
|        8 |
+----------+
1 row in set (0.01 sec)mysql> select count(gender) from db_info;
+---------------+
| count(gender) |
+---------------+
|             7 |
+---------------+
1 row in set (0.00 sec)mysql> select count(id) from db_info;
+-----------+
| count(id) |
+-----------+
|         8 |
+-----------+
1 row in set (0.00 sec)mysql> select count(1) from db_info;
+----------+
| count(1) |
+----------+
|        8 |
+----------+
1 row in set (0.00 sec)mysql> select count(1) from db_info where gender="男";
+----------+
| count(1) |
+----------+
|        6 |
+----------+
1 row in set (0.01 sec)mysql> select count(1) from db_info where gender="女";
+----------+
| count(1) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)mysql> select count(gender),gender from db_info group by gender;
+---------------+--------+
| count(gender) | gender |
+---------------+--------+
|             6 | 男     |
|             1 | 女     |
|             0 | NULL   |
+---------------+--------+
3 rows in set (0.00 sec)
mysql> select gender,count(gender) from db_info group by gender having count(gender) > 4;
+--------+---------------+
| gender | count(gender) |
+--------+---------------+
| 男     |             6 |
+--------+---------------+
1 row in set (0.00 sec)mysql> select name from db_info;
+-----------+
| name      |
+-----------+
| 张三      |
| 离李四    |
| 王五      |
| 孙尚香    |
| 鲁智深    |
| 曹操      |
| 张飞      |
| 秀秀      |
+-----------+
8 rows in set (0.00 sec)mysql> select * from db_info order by id;
+----+-----------+--------+------+--------------+
| id | name      | gender | age  | address      |
+----+-----------+--------+------+--------------+
|  1 | 张三      | 男     |   45 | 四川绵阳     |
|  2 | 离李四    | 男     |   56 | 四川绵阳     |
|  3 | 王五      | 男     |   19 | 四川绵阳     |
|  4 | 孙尚香    | 女     |   19 | 四川绵阳     |
|  5 | 鲁智深    | 男     |   19 | 四川绵阳     |
|  6 | 曹操      | 男     |   18 | NULL         |
|  7 | 张飞      | 男     |   18 | NULL         |
|  8 | 秀秀      | NULL   |   18 | NULL         |
+----+-----------+--------+------+--------------+
8 rows in set (0.00 sec)

七：排序  order by 字段 [{asc | desc }]

如果存在排序，必须是在分组之后

select 字段
from  表名
[where 条件]
[group by 字段 [, 字段 [,……]] ]
[having 筛选条件]
order by 字段 [{asc |  desc  }]  [, 字段 [{asc |  desc  }]]
案例select * from user order by age ;
select * from user order by age desc;
select * from user order by age asc;
​
select * from user order by age , id desc;
​

年龄排序 mysql> select * from db_info order by age;
+----+-----------+--------+------+--------------+
| id | name      | gender | age  | address      |
+----+-----------+--------+------+--------------+
|  6 | 曹操      | 男     |   18 | NULL         |
|  7 | 张飞      | 男     |   18 | NULL         |
|  8 | 秀秀      | NULL   |   18 | NULL         |
|  3 | 王五      | 男     |   19 | 四川绵阳     |
|  4 | 孙尚香    | 女     |   19 | 四川绵阳     |
|  5 | 鲁智深    | 男     |   19 | 四川绵阳     |
|  1 | 张三      | 男     |   45 | 四川绵阳     |
|  2 | 离李四    | 男     |   56 | 四川绵阳     |
+----+-----------+--------+------+--------------+
8 rows in set (0.00 sec)年龄升序mysql> select * from db_info order by age, id desc;
+----+-----------+--------+------+--------------+
| id | name      | gender | age  | address      |
+----+-----------+--------+------+--------------+
|  8 | 秀秀      | NULL   |   18 | NULL         |
|  7 | 张飞      | 男     |   18 | NULL         |
|  6 | 曹操      | 男     |   18 | NULL         |
|  5 | 鲁智深    | 男     |   19 | 四川绵阳     |
|  4 | 孙尚香    | 女     |   19 | 四川绵阳     |
|  3 | 王五      | 男     |   19 | 四川绵阳     |
|  1 | 张三      | 男     |   45 | 四川绵阳     |
|  2 | 离李四    | 男     |   56 | 四川绵阳     |
+----+-----------+--------+------+--------------+
8 rows in set (0.00 sec)mysql> select * from db_info where id>0 group by id order by age, id desc;
+----+-----------+--------+------+--------------+
| id | name      | gender | age  | address      |
+----+-----------+--------+------+--------------+
|  8 | 秀秀      | NULL   |   18 | NULL         |
|  7 | 张飞      | 男     |   18 | NULL         |
|  6 | 曹操      | 男     |   18 | NULL         |
|  5 | 鲁智深    | 男     |   19 | 四川绵阳     |
|  4 | 孙尚香    | 女     |   19 | 四川绵阳     |
|  3 | 王五      | 男     |   19 | 四川绵阳     |
|  1 | 张三      | 男     |   45 | 四川绵阳     |
|  2 | 离李四    | 男     |   56 | 四川绵阳     |
+----+-----------+--------+------+--------------+
8 rows in set (0.01 sec)mysql> select * from db_info where id>0 group by id having count(id) = 1 order by age, id desc;
+----+-----------+--------+------+--------------+
| id | name      | gender | age  | address      |
+----+-----------+--------+------+--------------+
|  8 | 秀秀      | NULL   |   18 | NULL         |
|  7 | 张飞      | 男     |   18 | NULL         |
|  6 | 曹操      | 男     |   18 | NULL         |
|  5 | 鲁智深    | 男     |   19 | 四川绵阳     |
|  4 | 孙尚香    | 女     |   19 | 四川绵阳     |
|  3 | 王五      | 男     |   19 | 四川绵阳     |
|  1 | 张三      | 男     |   45 | 四川绵阳     |
|  2 | 离李四    | 男     |   56 | 四川绵阳     |
+----+-----------+--------+------+--------------+
8 rows in set (0.00 sec)mysql> sclect count(*) from db_info;

八：别名的使用
别名（alias ），就是将原有名称，重新赋予一个新的名称

在查询SQL时，可以将查询的结果重新设置名称（字段、表名称）。

字段 as 别名 ，as可以省略select count(*) as count from user;
​
select count(*) count from user;

mysql> select count(*) as count from db_info;
+-------+
| count |
+-------+
|     8 |
+-------+
1 row in set (0.00 sec)mysql> select count(*)  count from db_info;
+-------+
| count |
+-------+
|     8 |
+-------+
1 row in set (0.00 sec)

mysql> select id, name, age from db_info where id<6;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | 张三      |   45 |
|  2 | 离李四    |   56 |
|  3 | 王五      |   19 |
|  4 | 孙尚香    |   19 |
|  5 | 鲁智深    |   19 |
+----+-----------+------+
5 rows in set (0.00 sec)mysql> select * from (select id, name, age from db_info where id<6) as t where t.id = 1;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | 张三   |   45 |
+----+--------+------+
1 row in set (0.01 sec)

九：分页查询

查询非常多，此时全部展示是不现实的，因此，需要通过分页展示部分数据

注意：不同的关系型数据库，实现分页效果，可能SQL不一样！！！

MySQL使用 limit关键字实现分页！！！

 

mysql> select * from db_info limit 3;
+----+-----------+--------+------+--------------+
| id | name      | gender | age  | address      |
+----+-----------+--------+------+--------------+
|  1 | 张三      | 男     |   45 | 四川绵阳     |
|  2 | 离李四    | 男     |   56 | 四川绵阳     |
|  3 | 王五      | 男     |   19 | 四川绵阳     |
+----+-----------+--------+------+--------------+
3 rows in set (0.00 sec)mysql> select * from db_info limit 5;
+----+-----------+--------+------+--------------+
| id | name      | gender | age  | address      |
+----+-----------+--------+------+--------------+
|  1 | 张三      | 男     |   45 | 四川绵阳     |
|  2 | 离李四    | 男     |   56 | 四川绵阳     |
|  3 | 王五      | 男     |   19 | 四川绵阳     |
|  4 | 孙尚香    | 女     |   19 | 四川绵阳     |
|  5 | 鲁智深    | 男     |   19 | 四川绵阳     |
+----+-----------+--------+------+--------------+
5 rows in set (0.00 sec)mysql> select * from db_info limit 0,5;
+----+-----------+--------+------+--------------+
| id | name      | gender | age  | address      |
+----+-----------+--------+------+--------------+
|  1 | 张三      | 男     |   45 | 四川绵阳     |
|  2 | 离李四    | 男     |   56 | 四川绵阳     |
|  3 | 王五      | 男     |   19 | 四川绵阳     |
|  4 | 孙尚香    | 女     |   19 | 四川绵阳     |
|  5 | 鲁智深    | 男     |   19 | 四川绵阳     |
+----+-----------+--------+------+--------------+
5 rows in set (0.00 sec)mysql> select * from db_info limit 5,5;
+----+--------+--------+------+---------+
| id | name   | gender | age  | address |
+----+--------+--------+------+---------+
|  6 | 曹操   | 男     |   18 | NULL    |
|  7 | 张飞   | 男     |   18 | NULL    |
|  8 | 秀秀   | NULL   |   18 | NULL    |
+----+--------+--------+------+---------+
3 rows in set (0.00 sec)

C:\Users\GUOGUO>mysql --help
mysql  Ver 8.1.0 for Win64 on x86_64 (MySQL Community Server - GPL)
Copyright (c) 2000, 2023, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Usage: mysql [OPTIONS] [database]-?, --help          Display this help and exit.-I, --help          Synonym for -?--auto-rehash       Enable automatic rehashing. One doesn't need to use'rehash' to get table and field completion, but startupand reconnecting may take a longer time. Disable with--disable-auto-rehash.(Defaults to on; use --skip-auto-rehash to disable.)-A, --no-auto-rehashNo automatic rehashing. One has to use 'rehash' to gettable and field completion. This gives a quicker start ofmysql and disables rehashing on reconnect.--auto-vertical-outputAutomatically switch to vertical output mode if theresult is wider than the terminal width.-B, --batch         Don't use history file. Disable interactive behavior.(Enables --silent.)--bind-address=name IP address to bind to.--binary-as-hex     Print binary data as hex. Enabled by default forinteractive terminals.--character-sets-dir=nameDirectory for character set files.--column-type-info  Display column type information.-c, --comments      Preserve comments. Send comments to the server. Thedefault is --comments (keep comments), disable with--skip-comments.(Defaults to on; use --skip-comments to disable.)-C, --compress      Use compression in server/client protocol.-#, --debug[=#]     This is a non-debug version. Catch this and exit.--debug-check       This is a non-debug version. Catch this and exit.-T, --debug-info    This is a non-debug version. Catch this and exit.-D, --database=name Database to use.--default-character-set=nameSet the default character set.--delimiter=name    Delimiter to be used.--enable-cleartext-pluginEnable/disable the clear text authentication plugin.-e, --execute=name  Execute command and quit. (Disables --force and historyfile.)-E, --vertical      Print the output of a query (rows) vertically.-f, --force         Continue even if we get an SQL error.--histignore=name   A colon-separated list of patterns to keep statementsfrom getting logged into syslog and mysql history.-G, --named-commandsEnable named commands. Named commands mean this program'sinternal commands; see mysql> help . When enabled, thenamed commands can be used from any line of the query,otherwise only from the first line, before an enter.Disable with --disable-named-commands. This option isdisabled by default.-i, --ignore-spaces Ignore space after function names.--init-command=name SQL Command to execute when connecting to MySQL server.Will automatically be re-executed when reconnecting.--local-infile      Enable/disable LOAD DATA LOCAL INFILE.-b, --no-beep       Turn off beep on error.-h, --host=name     Connect to host.--dns-srv-name=name Connect to a DNS SRV resource-H, --html          Produce HTML output.-X, --xml           Produce XML output.--line-numbers      Write line numbers for errors.(Defaults to on; use --skip-line-numbers to disable.)-L, --skip-line-numbersDon't write line number for errors.-n, --unbuffered    Flush buffer after each query.--column-names      Write column names in results.(Defaults to on; use --skip-column-names to disable.)-N, --skip-column-namesDon't write column names in results.--sigint-ignore     Ignore SIGINT (CTRL-C).-o, --one-database  Ignore statements except those that occur while thedefault database is the one named at the command line.-p, --password[=name]Password to use when connecting to server. If password isnot given it's asked from the tty.-,, --password1[=name]Password for first factor authentication plugin.-,, --password2[=name]Password for second factor authentication plugin.-,, --password3[=name]Password for third factor authentication plugin.-W, --pipe          Use named pipes to connect to server.-P, --port=#        Port number to use for connection or 0 for default to, inorder of preference, my.cnf, $MYSQL_TCP_PORT,/etc/services, built-in default (3306).--prompt=name       Set the mysql prompt to this value.--protocol=name     The protocol to use for connection (tcp, socket, pipe,memory).-q, --quick         Don't cache result, print it row by row. This may slowdown the server if the output is suspended. Doesn't usehistory file.-r, --raw           Write fields without conversion. Used with --batch.--reconnect         Reconnect if the connection is lost. Disable with--disable-reconnect. This option is enabled by default.(Defaults to on; use --skip-reconnect to disable.)-s, --silent        Be more silent. Print results with a tab as separator,each row on new line.--shared-memory-base-name=nameBase name of shared memory.-S, --socket=name   The socket file to use for connection.--server-public-key-path=nameFile path to the server public RSA key in PEM format.--get-server-public-keyGet server public key--ssl-mode=name     SSL connection mode.--ssl-ca=name       CA file in PEM format.--ssl-capath=name   CA directory.--ssl-cert=name     X509 cert in PEM format.--ssl-cipher=name   SSL cipher to use.--ssl-key=name      X509 key in PEM format.--ssl-crl=name      Certificate revocation list.--ssl-crlpath=name  Certificate revocation list path.--tls-version=name  TLS version to use, permitted values are: TLSv1.2,TLSv1.3--ssl-fips-mode=nameSSL FIPS mode (applies only for OpenSSL); permittedvalues are: OFF, ON, STRICT--tls-ciphersuites=nameTLS v1.3 cipher to use.--ssl-session-data=nameSession data file to use to enable ssl session reuse--ssl-session-data-continue-on-failed-reuseIf set to ON, this option will allow connection tosucceed even if session data cannot be reused.--tls-sni-servername=nameThe SNI server name to pass to server-t, --table         Output in table format.--tee=name          Append everything into outfile. See interactive help (\h)also. Does not work in batch mode. Disable with--disable-tee. This option is disabled by default.-u, --user=name     User for login if not current user.-U, --safe-updates  Only allow UPDATE and DELETE that uses keys.-U, --i-am-a-dummy  Synonym for option --safe-updates, -U.-v, --verbose       Write more. (-v -v -v gives the table output format).-V, --version       Output version information and exit.-w, --wait          Wait and retry if connection is down.--connect-timeout=# Number of seconds before connection timeout.--max-allowed-packet=#The maximum packet length to send to or receive fromserver.--net-buffer-length=#The buffer size for TCP/IP and socket communication.--select-limit=#    Automatic limit for SELECT when using --safe-updates.--max-join-size=#   Automatic limit for rows in a join when using--safe-updates.--show-warnings     Show warnings after every statement.-j, --syslog        Log filtered interactive commands to syslog. Filtering ofcommands depends on the patterns supplied via histignoreoption besides the default patterns.--plugin-dir=name   Directory for client-side plugins.--default-auth=name Default authentication client-side plugin to use.--binary-mode       By default, ASCII '\0' is disallowed and '\r\n' istranslated to '\n'. This switch turns off both features,and also turns off parsing of all clientcommands except\C and DELIMITER, in non-interactive mode (for inputpiped to mysql or loaded using the 'source' command).This is necessary when processing output from mysqlbinlogthat may contain blobs.--connect-expired-passwordNotify the server that this client is prepared to handleexpired password sandbox mode.--compression-algorithms=nameUse compression algorithm in server/client protocol.Valid values are any combination of'zstd','zlib','uncompressed'.--zstd-compression-level=#Use this compression level in the client/server protocol,in case --compression-algorithms=zstd. Valid range isbetween 1 and 22, inclusive. Default is 3.--load-data-local-dir=nameDirectory path safe for LOAD DATA LOCAL INFILE to readfrom.--fido-register-factor=nameSpecifies authentication factor, for which registrationneeds to be done.--authentication-oci-client-config-profile=nameSpecifies the configuration profile whose configurationoptions are to be read from the OCI configuration file.Default is DEFAULT.--oci-config-file=nameSpecifies the location of the OCI configuration file.Default for Linux is ~/.oci/config and %HOME/.oci/configon Windows.--telemetry-client  Load the telemetry_client plugin.--plugin-authentication-kerberos-client-mode=nameKerberos authentication mode. Valid values: SSPI, GSSAPI.If not specified, default is SSPIDefault options are read from the following files in the given order:
C:\WINDOWS\my.ini C:\WINDOWS\my.cnf C:\my.ini C:\my.cnf D:\mysql8.1.0\mysql-8.1.0-winx64\mysql-8.1.0-winx64\my.ini D:\mysql8.1.0\mysql-8.1.0-winx64\mysql-8.1.0-winx64\my.cnf
The following groups are read: mysql client
The following options may be given as the first argument:
--print-defaults        Print the program argument list and exit.
--no-defaults           Don't read default options from any option file,except for login file.
--defaults-file=#       Only read default options from the given file #.
--defaults-extra-file=# Read this file after the global files are read.
--defaults-group-suffix=#Also read groups with concat(group, suffix)
--login-path=#          Read this path from the login file.Variables (--variable-name=value)
and boolean options {FALSE|TRUE}           Value (after reading options)
------------------------------------------ -------------------------------
auto-rehash                                TRUE
auto-vertical-output                       FALSE
bind-address                               (No default value)
binary-as-hex                              FALSE
character-sets-dir                         (No default value)
column-type-info                           FALSE
comments                                   TRUE
compress                                   FALSE
database                                   (No default value)
default-character-set                      utf8mb4
delimiter                                  ;
enable-cleartext-plugin                    FALSE
vertical                                   FALSE
force                                      FALSE
histignore                                 (No default value)
named-commands                             FALSE
ignore-spaces                              FALSE
init-command                               (No default value)
local-infile                               FALSE
no-beep                                    FALSE
host                                       (No default value)
dns-srv-name                               (No default value)
html                                       FALSE
xml                                        FALSE
line-numbers                               TRUE
unbuffered                                 FALSE
column-names                               TRUE
sigint-ignore                              FALSE
port                                       3306
prompt                                     mysql>
quick                                      FALSE
raw                                        FALSE
reconnect                                  TRUE
shared-memory-base-name                    (No default value)
socket                                     (No default value)
server-public-key-path                     (No default value)
get-server-public-key                      FALSE
ssl-ca                                     (No default value)
ssl-capath                                 (No default value)
ssl-cert                                   (No default value)
ssl-cipher                                 (No default value)
ssl-key                                    (No default value)
ssl-crl                                    (No default value)
ssl-crlpath                                (No default value)
tls-version                                (No default value)
tls-ciphersuites                           (No default value)
ssl-session-data                           (No default value)
ssl-session-data-continue-on-failed-reuse  FALSE
tls-sni-servername                         (No default value)
table                                      FALSE
user                                       (No default value)
safe-updates                               FALSE
i-am-a-dummy                               FALSE
connect-timeout                            0
max-allowed-packet                         16777216
net-buffer-length                          16384
select-limit                               1000
max-join-size                              1000000
show-warnings                              FALSE
plugin-dir                                 (No default value)
default-auth                               (No default value)
binary-mode                                FALSE
connect-expired-password                   FALSE
compression-algorithms                     (No default value)
zstd-compression-level                     3
load-data-local-dir                        (No default value)
fido-register-factor                       (No default value)
authentication-oci-client-config-profile   (No default value)
oci-config-file                            (No default value)
telemetry-client                           FALSEC:\Users\GUOGUO>
C:\Users\GUOGUO>
C:\Users\GUOGUO>
C:\Users\GUOGUO>mysql -uroot -p123456;
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)C:\Users\GUOGUO>mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.1.0 MySQL Community Server - GPLCopyright (c) 2000, 2023, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.