1、select
1.1 作用
获取mysql中的数据行
1.2 单独使用select
1.2.1
select @@XXX；获取参数信息
select @@port; 查端口号
show variables; 查看所有参数
show variables like '%innodb%'; |查参数
1.2.2 select 函数（）；
select now(); 函数加括号
| mysql> select database(); |
库 |
| mysql> select now(); |
时间 |
| mysql> select version(); |
版本 |
1.3 sql92标准的使用语法
####### 1.3.1 select语法执行顺序
select开始--> from 字句 --> where子句 -->
group by 子句 --> select 后执行条件-->
having子句 --> order by -->limit
| desc city |
表结构 |
| id |
自增的无关列 |
| name |
城市名字 |
| countrycode |
所在国家代号 |
| district |
中国省的意思 每个是洲的意思 |
| populiation |
城市人口数量 |
| select * from jyt; |
相对路径查询 生产中使用较少 |
| select * from root.jyt |
绝对路径 生产中使用较少 |
| select name,populication from jyt; |
查看两列的内容 |
| select name,populication from root.jyt; |
查看两列的内容 |
where
| where |
相当于grep |
说明 |
| where配合等值查询 |
select * from world.city where countrycode='chn'; |
查询表中的中国城市信息 |
| where配合不等值查询 |
select * from world.city where Population<100; |
人口小于100人的城市 （>,<,<=,>=,<>） |
| where配合模糊查询 |
select * from world.city where CountryCode like 'c%'; |
国家以c开头 禁止%开头 |
| where配合逻辑连接符（AND or） |
select * from world.city where Population > 10000 AND Population < 20000; |
select * from world.city where population between 10000 and 20000; |
|
select * from world.city where CountryCode='chn' OR CountryCode='usa'; |
select * from world.city where countrycode in ('chn','usa'); |
|
SELECT * FROM world.city WHERE CountryCode='chn' UNION ALL SELECT*FROM world.city WHERE CountryCode='usa'; |
推荐 union 去重 加all不去重 默认去重 |
常用聚合函数
| 函数 |
例子 |
| avg() |
select district,avg(population) from city where countrycode='chn' group by district; |
| count() |
select countrycode,count(name) from city group by countrycode; |
| sum() |
select countrycode,sum(population) from city group by countrycode ; |
| max() |
- |
| min() |
- |
| group_concat() |
select countrycode,group_concat(district) from city group by countrycode; |
order by
| order by |
排序 |
| 查询统计总数 |
select district,sum(population) from city where countrycode='chn' group by district; |
| 查询统计总数并排序降序 |
SELECT district,sum(population) FROM city WHERE countrycode='chn' GROUP BY district ORDER BY SUM(Population) DESC; |
| 查询中国所有的城市，并以人口数降序输出 |
select*from city where countrycode='chn' order by population desc; |
| - |
- |
| limit m,n 跳过m行显示n行 |
limit x offset y 跳过y行显示x行 |
| 前5行 |
SELECT*FROM city WHERE countrycode='chn' ORDER BY population DESC LIMIT 5; |
| 显示6-10行 |
SELECT*FROM city WHERE countrycode='chn' ORDER BY population DESC LIMIT 5,5; |
| 显示6-10行 |
select*from city where countrycode='chn' order by population desc limit 5 offset 5; |
show
| - |
- |
| SHOW DATABASES; |
查看所以库 |
| SHOW TABLES; |
查看所以表 |
| SHOW COLUMNS FROM 表名； |
查看表内所以内容 数字表名要加反应号 例如： 132 |
