mysql查询的基本语法_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;