SQL语句之DQL数据查询语言（select、desc）

1.select查询语句

1.查询表中所有的数据

# 很危险，数据量过大，容易导致down机
mysql> select * from student;

# 先查询数据总量，然后决定是否可以查询所有数据
mysql> select count(*) from student;
+----------+
| count(*) |
+----------+
|        6 |
+----------+
1 row in set (0.01 sec)

2.查看指定列的数据

mysql> select user,host from mysql.user;
+--------+------------+
| user   | host       |
+--------+------------+
| root   | %          |
| root   | 127.0.0.1  |
| lhd    | 172.16.1.% |
| qiudao | 172.16.1.% |
| root   | 172.16.1.% |
| root   | ::1        |
|        | db03       |
| root   | db03       |
|        | localhost  |
| root   | localhost  |
+--------+------------+
10 rows in set (0.01 sec)

3.按条件查询

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

2.简单查询练习

1.将sql文件导入数据

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

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

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

2.查看数据

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;

3.查询练习

# 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.按照步长查询数据
mysql> select id,Name,Population from city limit 50,50;
										#50起始位置  50步长

3.条件查询

# 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';