sql语句-DQL-摩杜云开发者社区

DQL数据查询语言

select的基础用法

## 查看数据库
root@localhost [(none)] >show databases;
## 查看表
root@localhost [(none)] >show tables;
root@localhost [mysql] >show tables from zls;
## 查看建库语句相关信息
mysql> show create database linux50;
+----------+------------------------------------------------------------------+
| Database | Create Database                                                  |
+----------+------------------------------------------------------------------+
| linux50  | CREATE DATABASE `linux50` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+------------------------------------------------------------------+
## 查看表结构
root@localhost [(none)] >desc zls.stu10;
## 查看建表语句（注释信息）
mysql> show create table linux50.student;
## 查看创建用户语句
mysql> show grants for test1@'%';

INSERT, SELECT,UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE,REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES,EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE
## 查看当前所在数据
mysql> select database();
## 查看字符集
mysql> show charset;
## 查看校验规则
mysql> show collation;
## 查看变量
mysql> show variables like '%log%';
## 查看存储引擎
mysql> show engines;
mysql> show processlist;
mysql> show full processlist;

select的查询

总结

## 查询所有数据
select * from 库名.表名;
## 使用where条件查询数据
select * from 库名.表名 where x=x;
## 多条件查询
select * from 库名.表名 where x1=x1 or x2=x2;
## 范围查询
mysql> select * from 库名.表名 where x>x;
## 模糊查询 like
mysql> select * from 库名.表名 where sname like '%y';
mysql> select * from 库名.表名 where sname like '%y%';
mysql> select * from 库名.表名 where sname like 'y%';
## 多条件查询 or and
select * from 表名 where 字段1='CHN' or 字段1='USA';
select * from 表名 where 字段1 in ('CHN','USA');
## 联合查询 union all
select * from 表名 where countrycode='CHN' union all select * from 表名 where countrycode='USA';
## 分页查询 limit
select * from 表名 limit 60;
select * from city limit 0,60;
select * from city limit 60,60;
select * from city limit 120,60;
## 排序查询 order by
-正序order by 列名
select * from city where countrycode='CHN' or countrycode='USA' order by population;
-倒叙order by 列名 desc
select * from city where countrycode='CHN' or countrycode='USA' order by population desc;

## group by
-## 聚合函数
### count()统计函数(有空数据时不统计)(统计查询出来的结果有多少行)
用法select count(字段名) from 表名;
select count(*) from student;[统计表里面多少行，企业里不能直接用select * from....]
### distinct()去重函数
用法select distinct(字段名) from 表名;
### sum() 求和
### avg() 求平均值函数
### max() 求最大值函数
### min() 求最小值函数
-## group by用法口诀：
1.遇到统计想函数
2.形容词前group by
3.函数中央是名词
4.列名select后添加
用法：select 字段名1,sum(字段名2) from 表名 group by 字段名;

root@localhost [(none)] >show tables from world;
+-----------------+
| Tables_in_world |
+-----------------+
| city			  | 	// 城市表
| country 		  | 	// 国家表
| countrylanguage | 	// 国家语言表
+-----------------+

mysql> desc city;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int(11)  | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

## 查询所有数据
mysql> select * from linux50.student;

## 使用where条件查询数据
mysql> select * from linux50.student where sno=19;
+-----+--------+------+------+---------------------+-------+--------+
| sno | sname | sage | ssex | sbirthday | class | status |
+-----+--------+------+------+---------------------+-------+--------+
| 19 | yuanli | 3 | 1 | 2023-07-25 10:47:01 | L5 | 1 |
+-----+--------+------+------+---------------------+-------+--------+

## 多条件查询
mysql> select * from linux50.student where sno=19 or sno=20;
+-----+--------+------+------+---------------------+-------+--------+
| sno | sname | sage | ssex | sbirthday | class | status |
+-----+--------+------+------+---------------------+-------+--------+
| 19 | yuanli | 3 | 1 | 2023-07-25 10:47:01 | L5 | 1 |
| 20 | wyd | 2 | 1 | 2021-11-12 00:00:00 | L6 | 1 |
+-----+--------+------+------+---------------------+-------+--------+

## 范围查询
mysql> select * from linux50.student where sno>10;
+-----+--------+------+------+---------------------+-------+--------+
| sno | sname | sage | ssex | sbirthday | class | status |
+-----+--------+------+------+---------------------+-------+--------+
| 11 | yuanli | 3 | 1 | 2023-07-25 10:46:27 | L5 | 1 |
| 12 | wyd | 2 | 1 | 2021-11-12 00:00:00 | L6 | 1 |
| 13 | yuanli | 3 | 1 | 2023-07-25 10:46:28 | L5 | 1 |
| 14 | wyd | 2 | 1 | 2021-11-12 00:00:00 | L6 | 1 |
| 15 | yuanli | 3 | 1 | 2023-07-25 10:46:28 | L5 | 1 |
| 16 | wyd | 2 | 1 | 2021-11-12 00:00:00 | L6 | 1 |
| 17 | yuanli | 3 | 1 | 2023-07-25 10:46:28 | L5 | 1 |
| 19 | yuanli | 3 | 1 | 2023-07-25 10:47:01 | L5 | 1 |
| 20 | wyd | 2 | 1 | 2021-11-12 00:00:00 | L6 | 1 |
+-----+--------+------+------+---------------------+-------+--------+

mysql> select * from linux50.student where sno>10 and sno<15;
+-----+--------+------+------+---------------------+-------+--------+
| sno | sname | sage | ssex | sbirthday | class | status |
+-----+--------+------+------+---------------------+-------+--------+
| 11 | yuanli | 3 | 1 | 2023-07-25 10:46:27 | L5 | 1 |
| 12 | wyd | 2 | 1 | 2021-11-12 00:00:00 | L6 | 1 |
| 13 | yuanli | 3 | 1 | 2023-07-25 10:46:28 | L5 | 1 |
| 14 | wyd | 2 | 1 | 2021-11-12 00:00:00 | L6 | 1 |
+-----+--------+------+------+---------------------+-------+--------+

## 模糊查询 like
mysql> select * from linux50.student where sname like '%y';
mysql> select * from linux50.student where sname like '%y%';
mysql> select * from linux50.student where sname like 'y%';
+-----+--------+------+------+---------------------+-------+--------+
| sno | sname | sage | ssex | sbirthday | class | status |
+-----+--------+------+------+---------------------+-------+--------+
| 1 | yuanli | 4 | 1 | 2023-07-25 10:46:11 | L5 | 1 |
| 3 | yuanli | 3 | 1 | 2023-07-25 10:46:26 | L5 | 1 |
| 5 | yuanli | 3 | 1 | 2023-07-25 10:46:26 | L5 | 0 |
| 7 | yuanli | 3 | 1 | 2023-07-25 10:46:27 | L5 | 1 |
| 9 | yuanli | 3 | 1 | 2023-07-25 10:46:27 | L5 | 1 |
| 11 | yuanli | 3 | 1 | 2023-07-25 10:46:27 | L5 | 1 |
| 13 | yuanli | 3 | 1 | 2023-07-25 10:46:28 | L5 | 1 |
| 15 | yuanli | 3 | 1 | 2023-07-25 10:46:28 | L5 | 1 |
| 17 | yuanli | 3 | 1 | 2023-07-25 10:46:28 | L5 | 1 |
| 19 | yuanli | 3 | 1 | 2023-07-25 10:47:01 | L5 | 1 |
+-----+--------+------+------+---------------------+-------+--------+

## 多条件查询 or and
root@localhost [world] >select * from city where countrycode='CHN' or
countrycode='USA';
root@localhost [world] >select * from city where countrycode in ('CHN','USA');

## 联合查询 union all
root@localhost [world] >select * from city where countrycode='CHN' union all select *
from city where countrycode='USA';

## 分页查询 limit
root@localhost [world] >select * from city limit 60;
root@localhost [world] >select * from city limit 0,60;
root@localhost [world] >select * from city limit 60,60;
root@localhost [world] >select * from city limit 120,60;

## 排序查询 order by
root@localhost [world] >select * from city where countrycode='CHN' or
countrycode='USA' order by population;
root@localhost [world] >select * from city where countrycode='CHN' or
countrycode='USA' order by population desc;

## group by
函数
-# 统计世界上每个国家的总人口数
sum(population)
group by countrycode

select countrycode,sum(population) from city group by countrycode;
-#统计中国各个省的人口数量
sum(population)
group by district

select district,sum(population) from city where countrycode='CHN' group by district;

#统每个国家的城市数量(练习)
count(name)
group by countrycode

select countrycode,count(name) from city group by countrycode;
-----加别名————————
root@localhost [world] >select countrycode as '国家' ,count(name) as '城市数量' from
city group by countrycode order by count(name) limit 10;
+--------+--------------+
| 国家   | 城市数量     |
+--------+--------------+
| BRN    |            1 |
| ESH    |            1 |
| STP    |            1 |
| VIR    |            1 |
| MNP    |            1 |
| GRL    |            1 |
| BTN    |            1 |
| SUR    |            1 |
| BHR    |            1 |
| LCA    |            1 |
+--------+--------------+

select高级用法（连表查询）

传统连接

# 世界上小于100人的人口城市是哪个国家的？
国家名 城市名 人口数量
select country.name,city.name,city.population
from city,country
where city.countrycode=country.code
and city.population < 100;
+----------+-----------+------------+
| name     | name      | population |
+----------+-----------+------------+
| Pitcairn | Adamstown |         42 |
+----------+-----------+------------+

# 世界上小于100人的人口城市是哪个国家的,说什么语言？
国家名 城市名 人口数量 语言
select country.name,city.name,city.population,countrylanguage.language
from country,city,countrylanguage
where city.countrycode=country.code
and city.countrycode=countrylanguage.countrycode
and city.population < 100;
+----------+-----------+------------+-------------+
| name     | name      | population | language    |
+----------+-----------+------------+-------------+
| Pitcairn | Adamstown |         42 | Pitcairnese |
+----------+-----------+------------+-------------+

内连接

join on(全写inner join on)
## 小表在前，大表在后，字段
A join B on 1 join C on 2
A join B join C on 1 and 2(这个用法不规范)
用法：
select 表名1.字段名1,表名2.字段名2,表名3.字段名3
from 表名1
join 表名2 on 表名1.字段5=表名2.字段6
join 表名3 on 表名1.字段7=表名3.字段8
where 条件;

-# 世界上小于100人的人口城市是哪个国家的,说什么语言
select city.name,country.name,city.Population,countrylanguage.Language 
from city
join country on country.code=city.countrycode
join countrylanguage on countrylanguage.countrycode=city.countrycode
where city.Population<100;
+-----------+----------+------------+-------------+
| name      | name     | Population | Language    |
+-----------+----------+------------+-------------+
| Adamstown | Pitcairn |         42 | Pitcairnese |
+-----------+----------+------------+-------------+

select country.name,city.name,city.population,countrylanguage.language
from city join country
join countrylanguage
on city.countrycode=country.code
and city.countrycode=countrylanguage.countrycode
where city.population < 100;

自连接

自动找到等价条件 natural join

前提条件：
## 1.等价条件的列名
## 2.数据值必须一致


-# 世界上小于100人的人口城市说什么语言？
select city.name,countrylanguage.language
from city natural join countrylanguage
where city.population<100;
+-----------+-------------+
| name      | language    |
+-----------+-------------+
| Adamstown | Pitcairnese |
+-----------+-------------+

外连接

out join外连接	
	- 左外连接 left join
	- 右外连接 right join

A left join B on 1 left join C on 2
用法：select 表名1.字段名1,表名2.字段名2,表名3.字段名3
from 表名1
left join 表名2 
on 表名1.字段5=表名2.字段6
left join 表名3 on 表名1.字段7=表名3.字段8
and 条件; 

-#左外连接（意思是只显示左边的列）	
select city.name,city.countrycode,country.name
from city left join country
on city.countrycode=country.code
and city.population<100;
+----------------+-------------+------+
| name           | countrycode | name |
+----------------+-------------+------+
| Kabul          | AFG         | NULL |
| Qandahar       | AFG         | NULL |
| Herat          | AFG         | NULL |
| Mazar-e-Sharif | AFG         | NULL |
| Amsterdam      | NLD         | NULL |
| Rotterdam      | NLD         | NULL |
| Haag           | NLD         | NULL |
| Utrecht        | NLD         | NULL |
| Eindhoven      | NLD         | NULL |
| Tilburg        | NLD         | NULL |
+----------------+-------------+------+

-#右外连接（意思是只显示右边的列）
+------+------+----------------------+
| name | code | name                 |
+------+------+----------------------+
| NULL | ABW  | Aruba                |
| NULL | AFG  | Afghanistan          |
| NULL | AGO  | Angola               |
| NULL | AIA  | Anguilla             |
| NULL | ALB  | Albania              |
| NULL | AND  | Andorra              |
| NULL | ANT  | Netherlands Antilles |
| NULL | ARE  | United Arab Emirates |
| NULL | ARG  | Argentina            |
| NULL | ARM  | Armenia              |
+------+------+----------------------+