DQL 数据查询语言

查询数据（SELECT）

# 查询所有数据 — 很危险，数据量过大，容易导致内存溢出而宕机
mysql> select * from student;

# 先查询数据总量，然后决定是否可以查询所有数据
mysql> select count(distinct countrycode) from city;
+-----------------------------+
| count(distinct countrycode) |
+-----------------------------+
|                         232 |
+-----------------------------+
1 row in set (0.00 sec)

mysql> select count(countrycode) from city;
+--------------------+
| count(countrycode) |
+--------------------+
|               4079 |
+--------------------+
1 row in set (0.00 sec)

mysql> select count(*) from city;
+----------+
| count(*) |
+----------+
|     4079 |
+----------+
1 row in set (0.00 sec)


# 查询指定列数据
mysql> select user,host from mysql.user;
+--------+------------+
| user   | host       |
+--------+------------+
| root   | %          |
| root   | 127.0.0.1  |
| lhd    | 172.16.1.% |
| zzzwqh | 172.16.1.% |
| root   | 172.16.1.% |
| root   | ::1        |
|        | db03       |
| root   | db03       |
|        | localhost  |
| root   | localhost  |
+--------+------------+
10 rows in set (0.01 sec)

条件查询（SELECT，WHERE）

mysql> select name,gender from student where name='小王';
+--------+--------+
| name   | gender |
+--------+--------+
| 小王   | f      |
+--------+--------+
1 row in set (0.00 sec)

查询示例

导入一个 world 数据库，点击下载，解压即可

导入数据（命令行，SOURCE）

# 方式一：
[root@db03 ~]#  mysql -uroot -p123 < world.sql 

# 方式二:
mysql> source /root/world.sql;

# 方式三:
mysql> . /root/world.sql;

查询数据（SELECT，WHERE，COUNT，LIMIT，ORDER BY，DESC）

mysql> use world;
Database changed
mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city            |
| country         |
| countrylanguage |
+-----------------+
3 rows in set (0.00 sec)

mysql> select count(*) from city;
+----------+
| count(*) |
+----------+
|     4079 |
+----------+
1 row in set (0.00 sec)

mysql> select * from city;

# 1.查看表结构
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)

# 2.查看所有数据
mysql> select * from city;

# 3.查看指定列的数据
mysql> select Name,Population from city;

# 4.查看数据时排序（按照人口数量）
# 升序
mysql> select Name,Population from city order by Population;
# 降序
mysql> select Name,Population from city order by Population desc;

# 5.查询部分数据
# 查看前十条数据
mysql> select Name,Population from city order by Population desc limit 10;

# 6.按照步长查询数据，第一个 50 表示起始位置，第二个 50 表示步长
mysql> select id,Name,Population from city limit 50,50;
# 第一个 50 表示起始位置，第二个 50 表示步长

条件查询（or，in，union all，and，like，=，<，>，<=，>=，!=，<>）

# 1.条件查询就是使用where语句，where语句可以使用的符号
条件符号：= < > <= >= != <> or and like
	精确匹配：=
	范围匹配：< > <= >= != <>
	模糊匹配：like
	连接语句：or and
	
# 2.查询中国的城市人口
mysql> select name,population from city where CountryCode='CHN';

# 3.查询黑龙江人口数量
mysql> select name,population from city where countrycode='CHN' and District='heilongjiang';

# 4.查询中国人口数量小于 100000 的城市
mysql> select name,population from city where countrycode='CHN' and population < 100000;

# 5.模糊匹配
# 匹配以 N 结尾的数据
mysql> select name,countrycode from city where countrycode like '%N';
# 匹配以 N 开头的数据
mysql> select name,countrycode from city where countrycode like 'N%';
# 匹配包含 N 的数据
mysql> select name,countrycode from city where countrycode like '%N%';

# 6.查询中国或美国的人口数量
# 使用 or
mysql> select name,population from city where countrycode = 'CHN' or countrycode = 'USA';
# 使用 in
mysql> select name,population from city where countrycode in ('CHN','USA');
# 使用 union all，效率最高
mysql> select name,population from city where countrycode = 'CHN' union all select name,population from city where countrycode = 'USA';