Mysql查询语句大全

Mysql 查询语句大全

\1.两表之间的查询，例如：查询员工表中部门号与部门表中部门号相等

select * from tb_emp ,tb_dept where tb_emp.deptno=tb_dept.deptno;

(这是同时显示两张表中相等的depton 所有字段)（tb_emp ,tb_dept这都是表名）

2.select tb_e.deptno from tb_e, tb_d where tb_e.deptno=tb_d.deptno;

（这是只显示员工表中的tb_e.deptno，并且条件是员工表中部门号与部门表中部门号相等）3.给字段取别名

select product_price*12 totsl_product_price from productinfo;

等价select product_price*12 from productinfo;

也可以这样写select product_price*12 " totsl product_price" from productinfo;

有特殊的字符时用双引号的方法，（特殊字符是：中文，日文，分号等）（totsl product_price 是product_price*12）

****** 0 和空还有空格不是一个概念

例如：

select * from emp where description is null;

select * from emp where description =0;

select * from emp where description ='空格';

查询的结果都市不一样的。

distinct 关键字可以查询结果中清除重复的行，他的作用范围是后面的所有字段的组合；

例如：

select distinct deptno ,deptname from emp where deptno=23;

totsl_product_price是product_price的别名；

select ename, sal*12 as '年薪'from emp; 别名的作用是让查询出来的结果可以让别人（外行）看了一目了然的作用

上面是针对产品价格计算出总金额，价格*12，也就是对字段值进行加减乘除，

*****任何含有空值的表达式计算后的值都是空值。（空值+20=空值，等等）

不等值查询（mysql两者都支持）

select * from productinfo where product_id!=33;

oracl的不等值查询

select * from productinfo where product_id<>'33';

小于查询

select * from productinfo where product_id<'33';

大于查询

select * from productinfo where product_id>'33';

等于查询

select * from productinfo where product_id='33';

在一定值范围内查询例如1000--5000的查询

select ename, sal as '月薪'from emp where sal>=1000 and sal<=5000;

在两个值之间的查询between...... and （包含最小值和最大值）

select ename, sal as '月薪'from emp where sal between 1000 and 5000;

in 查询在什么里面

示例：在emp表中查询部门编号为25 25 20 这三个值中的某些字段

select ename, sal,deptno from emp where deptno in (25,26,20);

not in 刚好与上面的相反

like 是做模糊查询使用

and 表示要满足所有条件例如：

select ename, sal,deptno from emp where ename like '%ff' and deptname='市场部';

or 查询表示只要满足其中一个条件就行，例如

select ename, sal,deptno from emp where ename like '%ff' or deptname='市场部';

not in 取反的意思表示不包含

优先级级别（可以用括号提高优先级别）

排序用的是order by

降序desc

默认是升序

注意排序时排序的字段只能是int 类型否则排序的效果会出现不理想的结果

*/*****可以在这样做sal是varchar 对sal排序时：

select ename as '姓名', sal as'月薪' from emp order by sal+0;

例如:自己建表的时候，把一个字段类型创建为varchar(2) ，其实应该建为int(2)的。因为我只允许输出数字。这本来也没什么，无非就是占点空间，懒得改了。但是今天在后台发现排序有问题。于是，没办法，改之。下面简单说一下MySQL的varchar排序问题，引以为戒。

下面，我从数据库里面以server_id排一下序，大家来看一下排序后的结果：

select server_id from cardserver where game_id = 1 order by server_id desc limit 10;

+-----------+

| server_id |

+-----------+

| 8 |

| 7 |

| 6 |

| 5 |

| 4 |

| 3 |

| 2 |

| 10 |

| 1 |

+-----------+

很明显，我想要的结果应该是10,8,7,6,5 这样的。但是这个10排在了2的后面。按照字符串来排的。其实我是想把它当做数值来排。

手动转换类型：

用下面的方法就可以了，使server_id+0之后再排序，问题解决了。

select server_id from cardserver where game_id = 1 order by server_id+0 desc limit 10;

+-----------+

| server_id |

+-----------+

| 10 |

| 8 |

| 7 |

| 6 |

| 5 |

| 4 |

| 3 |

| 2 |

| 1 |

+-----------+

使用MySQL函数CAST/CONVERT：

mysql为我们提供了两个类型转换函数：CAST和CONVERT，现成的东西我们怎能放过？

CAST() 和CONVERT() 函数可用来获取一个类型的值，并产生另一个类型的值。

这个类型可以是以下值其中的一个：

BINARY[(N)]

CHAR[(N)]

DA TE

DA TETIME

DECIMAL

SIGNED [INTEGER]

TIME

UNSIGNED [INTEGER]

所以我们也可以用CAST解决问题：

select server_id from cardserver where game_id = 1 order

by CAST(server_id as SIGNED) desc limit 10;

也可以使用CONVERT来搞定此问题：

select server_id from cardserver where game_id = 1 order

by CONVERT(server_id,SIGNED) desc limit 10;

在productinfo表中查询update_time ,create_time这两个字段，条件是product_id='33';

select update_time ,create_time from productinfo where product_id='33';

在两个值之间的查询between...... and

select ename, sal as '月薪'from emp where sal between 1000 and 5000;

插入数据时一定要注意字段的类型int型不用加单引号，varchar 型的要加上单引号

例如：

insert into emp (deptno,ename ,sex, eemail ,deptname ,sal) values (30,'luo1','nan','123456','开发部','3000');

auto_increment 自动增长

description 描述，

修改列的类型

alter table 表名modify 列名列类型

修改列名

alter table 表名change 旧列名新列名列类型

alter table emp change name ename varchar(20);

增加列

alter table 表名add column 字段名(列名) 列类型（column 可以有和无标准的应该有column ）

alter table emp add column moblie int (11);

alter table 表名add column 字段名(列名) 列类型after/befor 列名（字段名）

删除列把上面的add改为drop就行了；

修改表名alter table 表名rename 新表名；

alter table emp rename tb_emp;

rename table 表名to新表名

rename table tb_emp to emp;

删除表中的某一个字段（注意删除此字段时此字段一低昂要是不关联的字段，否则删除失败，删除成功数据也伴随着删除了）

语法是：alter table 表名drop 字段名；

alter table emp drop eemail;

**** 删除表中的莫一条记录用的时候delete 语句

例如：delete from emp where id =40;

****** 更新某一条记录的数据时用update语句update ...set

例如; update emp set ename='kkkk22' where id=20;

更新多个字段的值update emp set ename ='222255',sex='女'where id=10;

约束。

check 约束在mysql中不起作用；

>= ,=< ,= >xAND

where 的条件

Incorrect integer value: '' for column 'sal' at row 10

函数，单行函数，字段值的函数

单行函数：concat（str1，str2，....）连接函数

字段值的函数select now();

插入时间数据

insert into emp (birth) values ( now());

插入时间可以用时间函数，也可以手动插入，

手动插入：

insert into emp (birth,birthday,hierday)values('1999-02-06','1998-03-02','2001-05-26');插入的时间要在单引号内。

insert into emp (birth,birthday,hierday)values('19990206','19980302','20010526');这样也行。insert into emp (birth,birthday,hierday)value('1999/02/06','1998/05/02','2001/05/06');这也行

**** 注意函数后面要紧跟着括号，不能有空格号；

常用的数据类型是int ，char ，varchar，时间的是：date，datetime，等

聚合函数是对一组值进行计算，并返回单个值，也叫组合函数，除了count（）以外，聚合函数都会忽略null值。

下列是聚合函数（五个：avg，sum，max，min，count）

select avg(sal) from emp; 对emp表中的sal求平均值。

select sum(sal) from emp; 求和

select count(sal) from emp; 求总共的行数

select max(sal) from emp;

select min(sal) from emp;

select avg(sal) as '工资平均值',max(sal) as '最高工资',min(sal) as'最低工资',sum(sal) as'工资总和', count(sal) as '总共的记录' from emp;

****** count 不统计null，统计的事记录数；

select count(*) from emp; 星号是统计所有的记录。

ifnull（sal，0）是如果有值就取第一个sal，否则就取零。

例如：

select avg(ifnull(sal,0)) from emp;

分组统计：Group by 子句是真正作用在于与各种聚合函数配合使用。它是用来查询出来的数据进行分组。

分组的含义是：把该列具有相同值的多条记录当成一条记录处理，最后只输出一条记录。分组函数忽略空值。如果需要排序在用order by 子句。

当查询时一定要注意

select deptno, avg(ifnull(sal,0)) from emp; 这个语句是错误的，因为deptno没有出现在聚合函数中，也没有出现在Group by子句中，后面应该跟Group by 子句；

select deptno, avg(sal) from emp group by deptno; 查询每一个部门的平均工资。

select deptno, count(*) from emp group by deptno; 查询每一个部门的总数

select deptno, avg(sal) as '工资平均值',max(sal) as '最高工资',min(sal) as'最低工资',sum(sal) as'工资总和', count(sal) as '总共的记录' from emp group by deptno;这是查询每一个部门的工资具体情况。

where 子句中部能使用分组函数，因此用having 来代替where；

例如：select deptno, avg(sal) as '工资平均值',max(sal) as '最高工资',min(sal) as'最低工资',sum(sal) as'工资总和', count(sal) as '总共的记录' from emp where avg(sal)>300 group by deptno;（错误语句）

select deptno, avg(sal) as '工资平均值',max(sal) as '最高工资',min(sal) as'最低工资',sum(sal) as'工资总和', count(sal) as '总共的记录' from emp group by deptno having avg(sal)>3000;******* having 语句只能在Group by 后面。

分组加条件只能用having，

where 是在分组前进行条件过滤，where子句中部能使用聚合函数

having是在分组后进行条件过滤。

select deptno, avg(sal) as '工资平均值',max(sal) as '最高工资',min(sal) as'最低工资',sum(sal) as'工资总和', count(sal) as '总共的记录' from emp group by deptno having avg(sal)>3000 order by avg(sal) asc; order by 一般是都在最后；

limit 用来限制显示的行数，他常用来分页。

select * from emp limit 5; 这是查询前五条记录

select * from emp limit 5,10;查询的是6——16条数据，10这个参数表示要查10调记录，五代表偏移量为五。

limit 语句还在order by 的后面。limit 是mysql特有的。

语法：limit 偏移量，行数；

例如：select * from emp limit 4,2; 取出的数据是第5,6条记录，

取第一条数据：select * from emp limit 0,1;

limit 的偏移量是从零开始的，所以取出的数据时从偏移量加上1开始取限制的行数。select id,deptno,sex from emp limit 3,5; 取出的事从第四条记录开始取5条记录。

*****多表连接查询：

1.使用单个select语句从多个表中取出相关的数据。通过多表之间的关系，构建相关数据查询据。

2.多表连接通常是建立在相互关系的父子表上的。父子表示有关系的表。关系是通过外键形成的。

3.标准语法：select....from join_table JOIN_TYPE join_table on join_condition WHERE where_condition;

*join_table 参与连接的表

*JOIN_TYPE 连接的类型：内连接，外连接，交叉连接，自连接

*join_condition 连接条件

*where_condition where过滤条件

第一个;交叉连接/笛卡尔连接select * from emp，dept；（在实际中此连接基本不用）select count（*）from emp；//是17条记录

select count（*）from dept；//是4条记录

select count（*）from emp，dept；//是68=4*17；

select * from emp，dept；返回的是两张表的交叉乘积

标准语法：select * from emp cross join dept；

第二内连接连接条件就是主外键关联

1.等值连接：select * from emp,dept where emp.deptno=dept.deptno;

select * from emp e , dept d where e.deptno=d.deptno;和上面的一样，只是省略了as

select * from emp as e , dept as d where e.deptno=d.deptno;

其中给了表取别名emp as e。

标准写法是：select * from emp inner join dept on emp.deptno=dept.deptno;

第三：外连接：在外连接当中，某些building满足条件的列也会显示出来，也就是说，只限制其中一个表的行，而不限制另一个表的行

1.左外连接标准语法

select * from dept left join emp on dept.deptno=emp.deptno; 这里部门表（dept）作为主表，或者说左边的表为主表，左边的记录会全部显示，如果没有找到记录就补null；

select * from dept left join emp on emp.deptno=dept.deptno;一样的效果

select * from dept left join emp on emp.deptno=dept.deptno;这就不一样了左表（主表）发生该变了。

在Oracle中可以这样写;select * from emp,dept where emp.deptno=dept.deptno(+);

2.右外连接

select * from dept right join emp on emp.deptno=dept.deptno;

方法与左外连接一样，只是把left该为right就行了

3.自连接：参与连接的表都是同一张表(通过给表取别名虚拟出)

第四子查询：所查新出来的结果为主表提供查询的条件。

需求：查询工资比luoa高的所有员工，

第一步：select sal from emp where ename ='luoa'; 查出的结果是luoa的sal为300

第二步：select * from emp where sal >300;

select * from emp where sal >(select sal from emp where ename = 'luoa');这里要求select sal from emp where ename = 'luoa'查出来的结果必须唯一。

查询和luoa同一个部门的员工

1.select deptno from emp where ename = 'luoa'；查出的结果是luoa的deptno为35

2. select * from emp where deptno=35;

select * from emp where sal >(select deptno from emp where ename = 'luoa');

查询月薪工资最高的员工名字

select max(sal) from emp; 查出的max（sal）为65120

select ename from emp where sal='65120';

select ename from emp where sal=(select max(sal) from emp );

****单行只查询不能返回多个结果，如果子查询为空时，不会报错，查出的结果为空。