mysql数据库查询操作

数据查询语言

SELECT 列名1,列名2...FROM 表名 [WHERE条件]

查询所有字段用* 不带where条件就把表的所有记录查出来

=    //赋值和判断都是

!=   <> //两种不等于

<  >   <=    >=

OR 或者 ||

AND 且 &&

BETWEEN 5 AND 10

IN

NOT IN

LIKE  '%值%'

一些简单的查询，以user表为例，sex-》0表示男，1表示女

1. 查询所有

sleect * from user

2.查询age为33的数据

select * from user where age=33;

3.查询sex不是男的数据

select * from user where sex!=0;

4.查询age大于30的数据

select * from user where age>30

5.查询age=30或者age=40的数据

select * from user where age=30 or age=40;

6.查询age=40并且user_name=liming的数据

select * from user where age=40 and user_name='liming';

7.查询age在34至50之间的数据

select * from user where age between 34 and 50;

8.查询age包含33，40的数据

select * from user where age in (33,40);

9.查询age不包含33，40的数据

select * from user where age not in(33,40);

10.查询user_name包含g的数据

select * from user where user_name like "%g%";

查询user_name以g结尾的数据(左模糊匹配)

select * from user where user_name like '%g';

查询user_name以g开头的数据(右模糊匹配)

select * from user where user_name like 'g%';

11.起别名 AS

select user_name as name，user_age as age from user;

12.字段合并 CONCAT(字段1,字段2....),CONCAT_WS(分隔符,字段1,字段2......)

select CONCAT(user_name,user_age) from user;

select id,CONCAT_WS('==',user_name,user_age) from user;

14.过滤重复的字段内容 distinct

select distinct(user_age),id,user_name from user;

15.查询条数限制 LIMIT 开始数，取几条(从0开始算，比如添0,3，就是从1开始取3条，开始数不填默认从0开始取)

select * from user limit 3;

select * from user limit 2,2;(从第3条开始，取2条)

16.排序 oreder by 字段名 asc或desc

select * from user order by id asc (默认，升序排列);

select * from user orde by id desc(降序);

17.统计函数

select count(*) from user;  查询表的记录数

select sum(age) from user; 查询此列的和

select avg(age) from uer;查询此列的平均数

select max(age) from user;查询此列的最大值

select min(age) from user;查询此列的最小值

18.分组查询   group by 字段名

select  sum(user_age),sex from user group by sex; 

group by 字段名 having 条件

select  sum(user_age),sex from user group by sex having count(user_age)>70; 

19.内连接 左链接 右链接  以下面两个表为例

内连接

select s.name,m.mark from student as s inner join mark as m on s.id=m.stu_id;

外连接

select s.name,m.mark from student as s left join mark as m on s.id=m.stu_id;

右连接

select s.name,m.mark from student as s right join mark as m on s.id=m.stu_id;

19.子查询

select * from student where in (select stu_id from mark);

20.字符集

20.字符集

转载于:https://blog.51cto.com/woaijorden/2148665