MYSQL数据库中的简单查询语句--单表查询

C:\Users\WJW>mysql -uroot -p
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 8.0.23 MySQL Community Server - GPL

Copyright (c) 2000, 2021, 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.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

//展示数据库
mysql> show databases
    -> ;
+--------------------+
| Database           |
+--------------------+
| 111111             |
| fine_food          |
| information_schema |
| mysql              |
| performance_schema |
| sp                 |
| springdb           |
| star               |
| sys                |
| tjdz               |
+--------------------+
10 rows in set (0.01 sec)
//使用数据库

mysql> use springdb;
Database changed
mysql> show tables;
+--------------------+
| Tables_in_springdb |
+--------------------+
| city               |
| province           |
| student            |
+--------------------+
3 rows in set (0.00 sec)

//查看表结构
mysql> desc student;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int          | NO   | PRI | NULL    |       |
| name  | varchar(80)  | YES  |     | NULL    |       |
| email | varchar(100) | YES  |     | NULL    |       |
| age   | int          | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
4 rows in set (0.02 sec)

mysql> use tjdz;
Database changed
mysql> show tables;
+----------------+
| Tables_in_tjdz |
+----------------+
| dept           |
| emp            |
| food           |
| restaurant     |
| salgrade       |
| t_act          |
| t_customer     |
| t_user         |
| user           |
| 测试日期       |
+----------------+
10 rows in set (0.01 sec)

mysql> desc 测试日期;
+--------------+----------+------+-----+---------+-------------------+
| Field        | Type     | Null | Key | Default | Extra             |
+--------------+----------+------+-----+---------+-------------------+
| 订单ID       | char(20) | YES  |     | NULL    |                   |
| 用户ID       | char(6)  | YES  |     | NULL    |                   |
| 美食ID       | char(30) | NO   |     | NULL    |                   |
| 数量         | int      | YES  |     | NULL    |                   |
| 订餐日期     | date     | YES  |     | now()   | DEFAULT_GENERATED |
+--------------+----------+------+-----+---------+-------------------+
5 rows in set (0.00 sec)

mysql> select * from 测试日期;
+----------+----------+----------+--------+--------------+
| 订单ID   | 用户ID   | 美食ID   | 数量   | 订餐日期     |
+----------+----------+----------+--------+--------------+
| 1        | 1        | 1        |      1 | 2021-08-20   |
| 1        | 1        | 1        |      1 | 2021-08-20   |
| 3        | 4        | 56       |     49 | 2021-08-20   |
+----------+----------+----------+--------+--------------+
3 rows in set (0.02 sec)

//查看当前版本
mysql> select version()；
    -> ;
+--------+
| ；     |
+--------+
| 8.0.23 |
+--------+
1 row in set (0.00 sec)

mysql> use student;
ERROR 1049 (42000): Unknown database 'student'
mysql> use springdb;
Database changed
mysql> use student;
ERROR 1049 (42000): Unknown database 'student'
mysql> show tables;
+--------------------+
| Tables_in_springdb |
+--------------------+
| city               |
| province           |
| student            |
+--------------------+
3 rows in set (0.00 sec)
//查询所有信息

mysql> select * from student;
+------+--------+-------------------+------+
| id   | name   | email             | age  |
+------+--------+-------------------+------+
| 1001 | 李四   | lisi@qq.com       |   20 |
| 1002 | 张三   | zs@sina.com       |   28 |
| 1003 | 张飞   | zhangfei@163.com  |   20 |
| 1004 | 刘备   | lb@163.com        |   50 |
| 1005 | 关羽   | gy@163.com        |   50 |
| 1006 | 李白   | libai@163.com     |   60 |
| 1007 | 韩信   | hanxin@163.com    |   40 |
| 1008 | 庄周   | zhuangzhou@qq.com |   12 |
| 1010 | 李信   | lixin@qq.com      |   20 |
+------+--------+-------------------+------+
9 rows in set (0.01 sec)

//根据某个字段查询
mysql> select name,age from student;
+--------+------+
| name   | age  |
+--------+------+
| 李四   |   20 |
| 张三   |   28 |
| 张飞   |   20 |
| 刘备   |   50 |
| 关羽   |   50 |
| 李白   |   60 |
| 韩信   |   40 |
| 庄周   |   12 |
| 李信   |   20 |
+--------+------+
9 rows in set (0.00 sec)

//按照指定值进行查询
mysql> select email from student where id in(1001,006);
+-------------+
| email       |
+-------------+
| lisi@qq.com |
+-------------+
1 row in set (0.00 sec)

mysql> select name,age from student where id <> 1006；
    -> ;
ERROR 1054 (42S22): Unknown column '1006；' in 'where clause'

//查询满足不等于某个值的
mysql> select name,age from student where id <> 1006;
+--------+------+
| name   | age  |
+--------+------+
| 李四   |   20 |
| 张三   |   28 |
| 张飞   |   20 |
| 刘备   |   50 |
| 关羽   |   50 |
| 韩信   |   40 |
| 庄周   |   12 |
| 李信   |   20 |
+--------+------+
8 rows in set (0.00 sec)

//查询满足不等于某个值的
mysql> select name,age from student where id != 1006;
+--------+------+
| name   | age  |
+--------+------+
| 李四   |   20 |
| 张三   |   28 |
| 张飞   |   20 |
| 刘备   |   50 |
| 关羽   |   50 |
| 韩信   |   40 |
| 庄周   |   12 |
| 李信   |   20 |
+--------+------+
8 rows in set (0.00 sec)

mysql> select name,email from student where id in(1001,1006);
+--------+---------------+
| name   | email         |
+--------+---------------+
| 李四   | lisi@qq.com   |
| 李白   | libai@163.com |
+--------+---------------+
2 rows in set (0.00 sec)

mysql> select name,email from student where id = 1001 or id = 1006;
+--------+---------------+
| name   | email         |
+--------+---------------+
| 李四   | lisi@qq.com   |
| 李白   | libai@163.com |
+--------+---------------+
2 rows in set (0.00 sec)

mysql> select name,email from student where id like
    -> %7;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%7' at line 2
/*
    模糊查询
    %以某些字符开始，表示多个字符
    _表示一个字符
*/
mysql> select name,email from student where id like '%7';
+--------+----------------+
| name   | email          |
+--------+----------------+
| 韩信   | hanxin@163.com |
+--------+----------------+
1 row in set (0.00 sec)

//按照age升序排列
//有两个值，①：asc升序：默认的。②：desc：降序
//orderby  最后执行
mysql> select * from student order by age asc;
+------+--------+-------------------+------+
| id   | name   | email             | age  |
+------+--------+-------------------+------+
| 1008 | 庄周   | zhuangzhou@qq.com |   12 |
| 1001 | 李四   | lisi@qq.com       |   20 |
| 1003 | 张飞   | zhangfei@163.com  |   20 |
| 1010 | 李信   | lixin@qq.com      |   20 |
| 1002 | 张三   | zs@sina.com       |   28 |
| 1007 | 韩信   | hanxin@163.com    |   40 |
| 1004 | 刘备   | lb@163.com        |   50 |
| 1005 | 关羽   | gy@163.com        |   50 |
| 1006 | 李白   | libai@163.com     |   60 |
+------+--------+-------------------+------+
9 rows in set (0.00 sec)

mysql> select * from student order by 2 desc;
+------+--------+-------------------+------+
| id   | name   | email             | age  |
+------+--------+-------------------+------+
| 1007 | 韩信   | hanxin@163.com    |   40 |
| 1006 | 李白   | libai@163.com     |   60 |
| 1001 | 李四   | lisi@qq.com       |   20 |
| 1010 | 李信   | lixin@qq.com      |   20 |
| 1003 | 张飞   | zhangfei@163.com  |   20 |
| 1002 | 张三   | zs@sina.com       |   28 |
| 1008 | 庄周   | zhuangzhou@qq.com |   12 |
| 1004 | 刘备   | lb@163.com        |   50 |
| 1005 | 关羽   | gy@163.com        |   50 |
+------+--------+-------------------+------+
9 rows in set (0.00 sec)

mysql> select sun(age) feom student;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'student' at line 1
mysql> select sun(age) from student;
ERROR 1305 (42000): FUNCTION springdb.sun does not exist

/*
求和函数
分组函数不可直接使用在where字句当中。
			怎么解释？
				因为group by是在where语句执行结束之后执行的。
*/
mysql> select sum(age) from student;
+----------+
| sum(age) |
+----------+
|      300 |
+----------+
1 row in set (0.01 sec)

mysql> select max(age) from student;
+----------+
| max(age) |
+----------+
|       60 |
+----------+
1 row in set (0.00 sec)

mysql> select min(age) from student;
+----------+
| min(age) |
+----------+
|       12 |
+----------+
1 row in set (0.00 sec)

//重点：count(*)和count(具体的某个字段)，之间的区别？
//		count(*)：不是统计某个字段中的数据的个数，而是统计总记录的条数。（和某个字段无关）取空值。  
//		count(address):表示统计address字段中不为NULL的数据总量。

mysql> select count(*) from student;
+----------+
| count(*) |
+----------+
|        9 |
+----------+
1 row in set (0.01 sec)

mysql> select count(age) from student;
+------------+
| count(age) |
+------------+
|          9 |
+------------+
1 row in set (0.00 sec)

//空处理函数
mysql> select count(ifnull(age,0)) from student;
+----------------------+
| count(ifnull(age,0)) |
+----------------------+
|                    9 |
+----------------------+
1 row in set (0.00 sec)


//group by : 按照某个字段或某些字段进行分组。
//having ： having是对分组之后的数据进行再次过滤。

//注意：当一条语句有group by 的时候，select后面只能出现分组函数和group by后面的字段。

mysql> select age from student group by age;
+------+
| age  |
+------+
|   20 |
|   28 |
|   50 |
|   60 |
|   40 |
|   12 |
+------+
6 rows in set (0.00 sec)

mysql> select age from student group by age having age > 20;
+------+
| age  |
+------+
|   28 |
|   50 |
|   60 |
|   40 |
+------+
4 rows in set (0.01 sec)

//注意：分组函数一般都会和 group by 联合使用，这也是为什么他被称为分组函数的原因。
//	并且任何一个分组函数（count sum avg max min）都是在group by 执行结束之后才会执行的。
//	当一条sql语句没有group by的话，整张表的数据会自成一组。


/*
总结一个完整的DQL（数据查询）语句怎么写？
		select		5
		  ..
		from		1
		 ..
		where		2
		 ..
		group by	3
		 ..
		having		4【为了过滤分组后的数据而存在的---不可单独出现】
		 ..
		order by	6
		 ......
*/

//distinct关键字去出重复记录
mysql> select count(distinct age) from student;
+---------------------+
| count(distinct age) |
+---------------------+
|                   6 |
+---------------------+
1 row in set (0.01 sec)