常见的SQL语句查询

sql语句整理

show databases; – 查看当前数据库所有的库
quit或exit或 \q; --退出客户端命令：
create database if not exists mydb charset uft8; —判断如果不存在这创建mydb库,并制定编码utf8.
use mydb; – 进入mydb库
show tables; – 查看库中所有表
drop database if exists mydb; – 删除mydb库时判断是否存在

– 创建db10库、emp员工表并插入记录

-- 删除db10库(如果存在)
drop database if exists db10;
-- 重新创建db10库
create database db10 charset utf8;
-- 选择db10库
use db10;

-- 删除员工表(如果存在)
drop table if exists emp;
-- 创建员工表
create table emp(
	id int primary key auto_increment,	-- 员工编号--设置id为主键且唯一不为空
	name varchar(50),					-- 员工姓名
	gender char(1),					    -- 员工性别
	birthday date,					    -- 员工生日
	dept varchar(50),					-- 所属部门
	job varchar(50),					-- 所任职位
	sal double,					        -- 薪资
	bonus double					    -- 奖金
);

-- 往员工表中插入记录
insert into emp values(null,'小李','男','1995-10-25','财务部','老师','1800','300');
insert into emp values(null,'小赵','男','1994-11-6','科技部','老师','2500','600');
insert into emp values(null,'小刘','女','1996-09-14','人事部','老师','1400','300');

创建db20库、dept部门表

-- 重新创建部门表, 要求id, name字段
create table dept(
	id int primary key auto_increment,	-- 部门编号
	name varchar(20)					-- 部门名称
);
-- 往部门表中插入记录
insert into dept values(null, '财务部');
insert into dept values(null, '人事部');
insert into dept values(null, '科技部');
insert into dept values(null, '销售部');

WHERE子句查询
where子句查询语法：select 列名称 | * from表名称 where 列 运算符 值;

where子句后面跟的是条件，条件可以有多个，多个条件之间用连接词（or | and）进行连接
如:

select name,sal from emp
where sal=1400 or sal=1600 or sal=1800;
--查询emp表中薪资为 1400、1600、1800的员工，显示员工姓名和薪资

select name,sal from emp 
where sal not in(1400,1600,1800);
--查询薪资不为1400、1600、1800的员工

select name,sal,bonus from emp 
where sal>3000 and bonus<600;
--查询emp表中薪资大于3000并且奖金小于600的员工，显示员工姓名、薪资、奖金。

模糊查询
like 操作符用于在 where 子句中搜索列中的指定模式。
可以和通配符（%、_）配合使用，其中"%“表示0或多个任意的字符。”_"表示一个任意的字符。
语法：select 列 | * from 表名 where 列名 like 值;
如:

select name from emp
where name like '刘_'; -- 以"刘"开头，并且姓名为两个字
select name from emp
where name like '刘%'; -- 以"刘"开头的
--查询emp表中姓名以"刘"开头，并且姓名为两个字的员工，显示员工姓名。

多行函数查询
多行函数也叫做聚合(聚集)函数，根据某一列或所有列进行统计。

常见的多行函数有：

注意:
(1)多行函数不能用在where子句中
(2)多行函数和是否分组有关，分组与否会直接影响多行函数执行的结果。
(3）多行函数在统计时会对null进行过滤，直接将null丢弃，不参与统计.
如:

select count(*) from emp where sal>3000; 
--统计emp表中薪资大于3000的员工个数

select sal from emp; -- 查询emp表中的所有薪资
select max(sal) from emp; -- 统计emp表中的最高薪资
select min(sal) from emp; -- 统计emp表中的最低薪资

分组查询
group by语句根据一个或多个列对结果集进行分组。
在分组的列上我们可以使用 COUNT，SUM，AVG，MAX，MIN等函数。
语法：select 列 | from 表名 [where子句] group by 列;
如:

select * from emp group by dept;--按照部门分组，部门相同的是一组
select*from emp group by job;--按照职位分组，职位相同的是一组
select job,count(*)from emp group by job;--按照职位分组，统计每个职位的人数

排序查询
使用 group by子句将结果集中记录根据指定的列排序后再返回
语法：select列名 from 表名 group by 列名 [ASC|DESC]

ASC(默认)升序，即从低到高；DESC 降序，即从高到低。

select name,sal from emp order by sal asc;--查询所有员工薪资升序排序--默认是升序排序，因此asc可以省略
select name,sal from emp order by sal desc;--查询所有员工薪资降序排列

分页查询
在mysql中，通过limit进行分页查询：
limit (页码-1)*每页显示记录数, 每页显示记录数

select * from emp limit 0,3; -- 每页显示3条, 查询第1页

-- 按照薪资降序排序查询员工信息
select name,sal from emp order by sal desc;
-- 在上面查询的基础上分页查询,每页显示3条, 查询第1页
select name,sal from emp order by sal desc limit 0,3;

其他函数

select name,birthday from emp
where birthday>='1993-1-1' and birthday<='1995-12-31';
-- 或 将birthday中的年份取出来, 和1993及1995进行比较!
select name,birthday from emp
where year(birthday)>=1993 and year(birthday)<=1995;
--查询emp表中所有在1993和1995年之间出生的员工，显示姓名、出生日期。

select * from emp
where month(birthday)=month(now()); -- 查询本月过生日的员工
select * from emp
where month(birthday)=month(now())+1; -- 查询下个月过生日的员工

select name, concat_ws('/', sal, '元') from emp;
--查询emp表中员工的姓名和薪资（薪资格式为: xxx/元 ）

主键约束
作用: 作为一个唯一标识，唯一的表示一条表记录（作用类似于人的身份证号，可以唯一的表示一个人一样。）

create table stu(
	id int primary key auto_increment,
	...
); --将主键id设置为自增

非空约束
如果为一个列添加了非空约束，那么这个列的值就不能为空，但可以重复。

create table user(
	password varchar(50) not null,
	...
);--添加非空约束，例如为password添加非空约束：

唯一约束
如果为一个列添加了唯一约束，那么这个列的值就必须是唯一的（即不能重复），但可以为空。

create table user(
	username varchar(50) unique not null,
	...
); --添加唯一约束，例如为username添加唯一约束及非空约束：

外键约束
外键其实就是用于通知数据库两张表数据之间对应关系的这样一个列。
这样数据库就会帮我们维护两张表中数据之间的关系。

create table emp(
	id int,
	name varchar(50),
	dept_id int,
	foreign key(dept_id) references dept(id)
);

表关系
一对多(多对一)、一对一、多对多

多表查询

连接查询
笛卡尔积查询：所谓笛卡尔积查询就是指，查询两张表，其中一张表有m条记录，另一张表有n条记录，查询的结果是m*n条。
虽然笛卡尔积查询中包含大量错误数据，但我们可以通过where子句将错误数据剔除，保留下来的就是正确数据。

-- 条件: 员工所属的部门编号等于部门的编号
select * from dept,emp;
where emp.dept_id=dept.id;

select * from dept inner join emp
on emp.dept_id=dept.id; -- 内连接查询
-- 查询员工所属的部门编号等于部门的编号