select 查询

1、最简单的查询方式

select * from emp;

select empno,ename,sal, from emp;

2 、使用算法表达式

select empno, ename,sal, sal*12 +1000 from emp

3 、使用字段起别名

4、distinct 关键字

去重

5 、排序

排序方式包括升序（asc，缺省）和降序（desc）两种

从低到高（asc默认从低到高）

从高到低（desc）

6 、查询条件中使用逻辑运算符

7 、分页

select * from 表名 limit start,count

第一个数计算：（页数-1）*2 第二个数表示去多少条数据

SELECT * FROM emp;
-- 查询指定信息
SELECT empno,ename,sal FROM emp;

-- 给每个员工加薪 
SELECT empno,ename,sal*1.5 FROM emp;

SELECT empno,ename,sal*12+1000 FROM emp;

-- 修改列名
SELECT empno,ename,sal*12+1000 AS '年薪' FROM emp;
SELECT empno,ename,sal*12+1000 年薪 FROM emp;
SELECT e.empno,e.ename FROM emp e;

-- distinct 去重
-- 有什么部门
SELECT DISTINCT deptno FROM emp;
-- 查看每个部门有什么职位
SELECT DISTINCT deptno,job FROM emp;

-- 查看员工，看薪资排序，从低到高 (asc默认)
SELECT empno,ename,sal FROM emp ORDER BY sal;
SELECT empno,ename,sal FROM emp ORDER BY sal ASC;

-- 查看员工，看薪资排序，从高到低 (desc)
SELECT empno,ename,sal FROM emp ORDER BY sal DESC;

-- 查看员工，按部门，薪资排序(低到高)
SELECT deptno,ename,sal FROM emp ORDER BY deptno,sal;

-- 查看员工，按部门，薪资排序(高到低)
SELECT deptno,ename,sal FROM emp ORDER BY deptno DESC,sal DESC;

-- 查看员工薪资是5000的员工信息
SELECT deptno,ename,sal FROM emp WHERE sal=5000;

-- 查看员工薪资是2000到3000之间的员工信息
SELECT deptno,ename,sal FROM emp WHERE sal>2000 AND sal<3000;
SELECT deptno,ename,sal FROM emp WHERE sal BETWEEN 2000 AND 3000;

-- 查看在10号部门里的员工薪资是2000到3000之间的员工信息
SELECT deptno,ename,sal FROM emp WHERE sal>2000 AND sal<3000 AND deptno=10;

-- 查看没有奖金员工
SELECT deptno,ename,sal,comm FROM emp WHERE comm IS NULL OR comm=0;

-- 查看分页  第一个数计算：（页数-1）*2  第二个数表示去多少条数据
SELECT * FROM emp LIMIT 6,3;
SELECT * FROM emp LIMIT 0,2;
SELECT * FROM emp LIMIT 2,2;

View Code

函数

1、count()

count(*)表示计算总行数,括号中写星与列名，结果是相同的

2 、max() max(列)表示求此列的最大值

3 、min() min(列)表示求此列的最小值

4 、sum() sum(列)表示求此列的和

5 、avg() avg(列)表示求此列的平均值

6 、concat() 拼接字符串concat(str1,str2...)

7、length() 包含字符个数length(str)

8 、截取字符串

left(str,len) 返回字符串str的左端len个字符

right(str,len) 返回字符串str的右端len个字符

substring(str,pos,len) 返回字符串str的位置pos起len个字符

（第一个是字符串第二个是起始位置，第三个是截取长度）

9 、replace() 替换字符串replace(str,from_str,to_str)

10 、时间与日期

year(date) 返回date的年份(范围在1000到9999)

month(date) 返回date中的月份数值

day(date) 返回date中的日期数值

hour(time) 返回time的小时数(范围是0到23)

minute(time) 返回time的分钟数(范围是0到59)

second(time) 返回time的秒数(范围是0到59)

日期计算

使用+-运算符，数字后面的关键字为year、month、day、hour、minute、second

日期格式化

select date_format('2016-12-21','%Y %m %d');
其他：

当前日期 current_date()

当前时间 current_time()

当前日期时间now(）

-- 查看有多少员工
SELECT COUNT(*) FROM emp;
SELECT COUNT(deptno) FROM emp;
SELECT COUNT(1) FROM emp;


-- 查看员工的总薪资
SELECT SUM(sal) FROM emp;

-- 查看员工的最高薪资
SELECT MAX(sal) FROM emp;

-- 查看员工的最低薪资
SELECT MIN(sal) FROM emp;

-- 查看员工的平均薪资
SELECT AVG(sal) FROM emp;

-- 查询员工的总薪资，最高薪资，最低薪资，平均薪资
SELECT SUM(sal),MAX(sal),MIN(sal),AVG(sal) FROM emp;


-- 给员工薪资加上计量单位（'美金'）
SELECT empno, ename,CONCAT(sal,'美元','耶')FROM emp;

-- 查询员工的姓名长度
SELECT LENGTH(ename) FROM emp;

-- 查询员工的姓名起始字母
SELECT SUBSTRING(ename,1,3)FROM emp;


INSERT INTO emp (empno,ename) VALUES('8888','  laoda');
-- 用replace把空格去掉
SELECT REPLACE(ename,' ','')FROM emp WHERE empno='8888';
-- 把l改成x
SELECT REPLACE(ename,'l','x')FROM emp WHERE empno='8888';

SELECT YEAR('2018-2-28');

-- 查看emp表员工入职的年份 
SELECT empno,ename,YEAR(hiredate) FROM emp;

-- 增加时间1个月
SELECT '2017-12-28'+INTERVAL 1 MONTH;

-- 修改emp表员工入职时间的显示方式为yyyy年MM月dd日
SELECT empno,ename,DATE_FORMAT(hiredate,'%Y年%m月%d日') FROM emp;

-- 获取当前日期
SELECT CURRENT_DATE();
-- 获取当前时间
SELECT CURRENT_TIME();
-- 获取当前的日期和时间
SELECT NOW();

View Code

分组查询与过滤

group by 分组

语法格式

select column, group_function(column)
    from table
    [where condition]
    [group by group_by_expression]
    [order by column];

eg：

having 过滤

对分组查询的结果进行过滤，要使用having 从句

having从句过滤分组后的结果，它只能出现在group by从句之后，而where从句要出现在group by从句之前

where过滤行，having过滤分组

执行过程：

from-- where -- group by– having– select-- order by

就是先from 去哪个表里去查 -- 在用where过滤出符合条件的 -- group by在分组 --在having过滤掉分组后的数据 -- 然后select查出来 -- 最后order by 排序

eg：

-- 分组
-- 查看有多少个部门  group by 可以用来去重
SELECT deptno FROM emp GROUP BY deptno;

-- 每个部门每个月发多少薪资
SELECT SUM(sal) FROM emp GROUP BY deptno;
SELECT deptno,SUM(sal) FROM emp GROUP BY deptno;

-- 每个部门的总薪资，最小薪资，最高薪资，平均薪资 
SELECT deptno,SUM(sal),MIN(sal),MAX(sal),AVG(sal) FROM emp GROUP BY deptno;

-- 每个部门的总薪资，最小薪资，最高薪资，平均薪资 ，但是不统计10号部门,升序显示
SELECT deptno,SUM(sal),MIN(sal),MAX(sal),AVG(sal) FROM emp WHERE deptno != 10 GROUP BY deptno ORDER BY deptno DESC;


-- 每个部门的总薪资，最小薪资，最高薪资，平均薪资 ,不统计平均2000以下的
SELECT deptno,SUM(sal),MIN(sal),MAX(sal),AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal)>=2000;

-- 在emp表中，列出工资最小值小于2000的职位
SELECT job,MIN(sal) FROM emp GROUP BY sal HAVING MIN(sal)<2000;

-- 统计人数小于4的部门的平均工资
SELECT deptno,AVG(sal) FROM emp GROUP BY deptno HAVING COUNT(1)<4;

-- 统计各部门的最高工资，排除最高工资小于3000的部门
SELECT deptno, MAX(sal) FROM emp GROUP BY deptno HAVING MAX(sal)<3000;

View Code

约束

表的约束

约束类型	说明
主键约束（Primary Key）	要求主键列数据唯一，并且不允许为空。主键可以包含表的一列或多列，如果包含表的多列，则需要在表级定义。
唯一约束（Unique）	要求该列唯一，允许为空
检查约束（Check）	某列取值范围限制、格式限制等，如年龄的约束
非空约束（not null）	某类内容不能为空
外键约束（Foreign Key）	用于两表间建立关系，需要指定引用主表的那列。外键通常用来约束两个表之间的数据关系，定义外键的那张表称为子表，另一张表称为主表。在表的创建过程中，应该先创建主表，后创建子表。

主键的约束：
主键约束是数据库中最重要的一种约束。在关系中，主键值不可为空，也不允许出现重复，即关系要满足实体完整性规则

主键从功能上看相当于非空且唯一
一个表中只允许一个主键
主键是表中能够唯一确定一个行数据的字段
主键字段可以是单字段或者是多字段的组合

主键创建的两种方式：

-- 主键  创建方式1
CREATE TABLE t_user(
id INT AUTO_INCREMENT PRIMARY KEY,
NAME VARCHAR(10)
);
-- 主键  创建方式二
CREATE TABLE  t_user(
id INT AUTO_INCREMENT,
NAME VARCHAR(10),
PRIMARY KEY(id)
);

唯一约束创建的3种方式：

-- 唯一约束  创1
CREATE TABLE t_user(
id INT AUTO_INCREMENT,
NAME VARCHAR(10) UNIQUE,
PRIMARY KEY(id)
);

-- 唯一约束  创2
CREATE TABLE t_user(
id INT AUTO_INCREMENT,
NAME VARCHAR(10),
PRIMARY KEY(id),
UNIQUE(NAME)
);

-- 唯一约束  创3
CREATE TABLE t_user(
id INT AUTO_INCREMENT,
NAME VARCHAR(10),
PRIMARY KEY(id),
CONSTRAINT uk_name UNIQUE(NAME)
);

注：auto_increment 自增设置后会自动按顺序增加

外键创建的两种方式：

-- 外键的使用
-- 准备工作
CREATE TABLE t_sex(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(2)
)

USE test2;
-- 外键的创建1
CREATE TABLE t_user(
id INT AUTO_INCREMENT PRIMARY KEY,
NAME VARCHAR(10),
sex INT(11),
FOREIGN KEY (sex) REFERENCES t_sex(id)
);

-- 外键创建2
CREATE TABLE t_user(
id INT AUTO_INCREMENT PRIMARY KEY,
NAME VARCHAR(10),
sex INT(11),
CONSTRAINT fk_sex_sex_id FOREIGN KEY (sex) REFERENCES t_sex(id)
);