mysql查询 查询语法 sql基本查询操作 sql教程 （一）

mysql基本查询操作

1 下载数据

# git下载
git clone https://github.com/hxgdzyuyi/tang_poetry.git
# zip下载
https://github.com/hxgdzyuyi/tang_poetry/archive/master.zip

2 创建数据库

# 创建数据库
create database tang_poerty

3 导入数据

mysql -u root -p -h localhost tang_poetry < tang_poetry.sql

4 使用数据库

use tang_poetry;

5 查看数据表

show tables;

6 查看数据表的描述

desribe poets;
desribe poetries;

7 基础查询

SELECT 
    column_1, column_2, ...
FROM
    table_1
[INNER | LEFT |RIGHT] JOIN table_2 ON conditions
WHERE
    conditions
GROUP BY column_1
HAVING group_conditions
ORDER BY column_1
LIMIT offset, length;

1.查询一个表的全部信息

# select * from tableName;
# 查询poets的全部信息
select * from poets;

2. 查询指定字段

# select column_1, column_2 from tableName
# 从peots表从查询出id和name列
select id, name frome poets;

3.条件查询，注意是一个=号

where句子中常用的操作符

= 匹配等于
> 匹配大于
>= 匹配大于等于
<= 匹配小于等于
< 匹配小于
<> 或 != 匹配不等于

select column_1 from tableName where column_2 = "XXX";
# 查询poets数据库中id为3的数据
select name from poets where id = 3；
# 查询创建日期>2014-01-01的值并且id<3的值
select name, id from poets where id < 3 and created_at > "2014-01-01";

4.聚合查询

聚合查询
avg（column） 求平均值
max（column） 求最大值
min （column）求最小值
sum（column） 求和
count（column）统计个数
# 统计poets中名字的个数
select count(name) from poets;

5.分组查询

group by column 根据条件进行分组
# 根据诗人id对诗集进行分类
select title from poetries group by poet_id；
# 统计诗人id<10的人每人写了多少诗
select count(title) from poetries where poet_id < 10 group by poet_id;

# having
# having和group by 一样都能进行分组查询，having通常和group by 连用，但是group by后面不能跟聚合函数。
# 根据诗人id进行分组，查询出写诗数量大于100的诗人分别写了多少诗。
select count(title) from poetries group by poet_id  having count(title) > 100;
# 统计出写诗数量>100条的诗人id
 select poet_id from poetries group by poet_id  having count(title) > 100;

8. 查询不重复字段

distinct 不重复
select distinct column_1, column_2 from tableName

# 查询不重复的诗名
select distinct title from poetries;

9. 查询结果排序

order by

# 降序排序
# desc 
查询诗人id<10，写过的诗数目 默认
select poet_id, count(title) from poetries where poet_id < 10 group by poet_id order by poet_id;
# 按数目进行排序升序
select poet_id, count(title) from poetries where poet_id < 10 group by poet_id order by count(title);
# desc 降序
# select poet_id, count(title) from poetries where poet_id < 10 group by poet_id order by count(title) desc;

10.模糊查询

select * from table1 where name like "X%";
'%a'     //以a结尾的数据
'a%'     //以a开头的数据
'%a%'    //含有a的数据
'_a_'    //三位且中间字母是a的
'_a'     //两位且结尾字母是a的
'a_'     //两位且开头字母是a的

# 查询前10条李开头的数据
select * from poets where name like "李%" limit 1, 10;
# 查询李开头并且名字是三个字的
select * from poets where name like "李__" limit 1, 10;