【无标题】

emp
CREATE TABLE t1(age INT(5)ZEROFILL);
INSERT INTO t1 VALUES(18);
SELECT * FROM t1;
CREATE TABLE t2(price DOUBLE(5,3));
INSERT INTO t2 VALUES(54.324);
INSERT INTO t2 VALUES(51.324);
#插入时间
CREATE TABLE t3(t1 DATE ,t2 TIME,t3 DATETIME,t4 TIMESTAMP);
INSERT INTO t3 VALUES(“2000-11-22”,“10:22:11”,NULL,NULL);
INSERT INTO t3 VALUES(NULL,NULL,“2021-03-11 10:38:29”,NULL);
#主键约束
CREATE TABLE t4 (id INT PRIMARY KEY,NAME VARCHAR(20));
INSERT INTO t4 VALUES (1,‘aaa’);
#限制主键的值 唯一且非空
INSERT INTO t4 VALUES (1,‘bbb’);
INSERT INTO t4 VALUES (NULL,‘ccc’);
#主键自增（一般和主键约束一起使用）
CREATE TABLE t5(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20));
INSERT INTO t5 VALUES (NULL,‘aaa’);
INSERT INTO t5 VALUES (NULL,‘bbb’);
INSERT INTO t5 VALUES (5,‘ccc’);
INSERT INTO t5 VALUES (NULL,‘fff’);
SELECT * FROM t5;

TRUNCATE TABLE t5; #把表删除 重新创建 此时计数器才会清零

#查询员工表中有那些不同的工作
SELECT DISTINCT job FROM emp;
#查询员工表有那几个不同的部门id
SELECT DISTINCT dept_id FROM emp;

#查询没有上级领导的员工姓名 is null is not null
SELECT * FROM emp;
SELECT NAME FROM emp WHERE manager IS NULL;
SELECT NAME FROM emp WHERE manager IS NOT NULL;

#and | or
#查询一号部门工资高于两千
SELECT * FROM emp WHERE dept_id =1 AND sal>2000;
SELECT * FROM emp WHERE dept_id =3 OR sal=5000;
SELECT * FROM emp WHERE NAME=“孙悟空” OR NAME=“猪八戒”;

#比较运算符
SELECT NAME,sal FROM emp WHERE job<>“程序员”;
SELECT NAME,sal FROM emp WHERE job!=“程序员”;

#between … and …
SELECT * FROM emp WHERE sal BETWEEN 2000 AND 3000;
SELECT * FROM emp WHERE sal NOT BETWEEN 2000 AND 3000;

#in 包含
SELECT * FROM emp WHERE sal IN (3000,1500,5000);
SELECT * FROM emp WHERE NAME IN(“孙悟空”,“猪八戒”,“白骨精”)

SELECT DISTINCT job FROM emp WHERE dept_id=1;

SELECT * FROM emp
SELECT * FROM emp WHERE dept_id=1 AND manager IS NOT NULL;
SELECT * FROM emp WHERE job IN (“销售”,“程序员”,“人事”);

SELECT * FROM emp WHERE comm IS NOT NULL AND comm!=0;
SELECT * FROM emp WHERE comm>0; #更简单

#模糊查询 like %代表0或者多个未知字符
# _：代表1个未知字符
SELECT * FROM emp WHERE NAME LIKE “孙%”;
SELECT NAME FROM emp WHERE NAME LIKE “%精”;
SELECT NAME,job FROM emp WHERE job LIKE “_售%”;
SELECT NAME,sal FROM emp WHERE NAME LIKE “%僧%” AND sal>2000;

#排序 order by +字段名（默认升序）/+desc 降序
按照工资升序
SELECT NAME,sal FROM emp ORDER BY sal;
SELECT NAME,sal FROM emp ORDER BY sal DESC;
SELECT NAME,sal,dept_id FROM emp WHERE dept_id=1 ORDER BY sal DESC;

SELECT NAME,sal,dept_id FROM emp ORDER BY dept_id,sal DESC
SELECT NAME,sal,dept_id FROM emp ORDER BY dept_id,sal

#分页查询 limit
#格式： limit 跳过的条数，请求的条数(每页的条数)
#跳过的条数=（请求页数-1）*每页条数

第一页5条数据 limit 0，5

#第一页10条 limit 0，10
#第2页10条 limit 1，10
SELECT * FROM emp ORDER BY sal DESC
SELECT * FROM emp ORDER BY sal DESC LIMIT 0,3;
SELECT id,NAME FROM emp LIMIT 3,3

SELECT * FROM emp WHERE dept_id=3 AND sal>1500;
SELECT * FROM emp WHERE dept_id=2 OR manager IS NULL
SELECT NAME,sal FROM emp WHERE manager IS NOT NULL ORDER BY sal DESC
SELECT NAME,hiredate FROM emp WHERE dept_id IN(2,3) ORDER BY hiredate DESC
SELECT NAME FROM emp WHERE NAME LIKE “%僧%” OR NAME LIKE “%精%”
SELECT DISTINCT job FROM emp WHERE sal>2000
SELECT * FROM emp ORDER BY sal LIMIT 6,2

#别名
SELECT NAME FROM emp
SELECT NAME AS “名字” FROM emp
SELECT NAME “名字” FROM emp
SELECT NAME 名字 FROM emp

#数值计算
SELECT NAME,sal,3*sal 年终奖 FROM emp
UPDATE emp SET sal=sal+5 WHERE dept_id=3
SELECT * FROM emp

#聚合函数
#可以查询对多条数据进行统计查询，
#统计方式包括：求平均值，最大值，最小值，求和，计数

#平均值avg(字段名)
SELECT AVG(sal) FROM emp WHERE dept_id=2
SELECT AVG(sal) FROM emp WHERE job=“销售”

#最大值 max(字段名)
SELECT MAX(sal) FROM emp WHERE dept_id=1
#最小值 min(字段名)
SELECT MIN(sal) FROM emp WHERE dept_id=1

#求和 sum(字段名)
SELECT SUM(sal) FROM emp WHERE dept_id=1

#技术 count(字段名)
SELECT COUNT(*) FROM emp WHERE sal>2000

SELECT AVG(sal) 平均工资,MAX(sal) 最高工资,
MIN(sal) 最低工资,SUM(sal)工资总和,COUNT(*)人数
FROM emp WHERE dept_id=2

SELECT * FROM emp WHERE dept_id=1 AND NAME LIKE “%僧%”
SELECT COUNT() FROM emp WHERE dept_id IN(2,3) AND sal>1500
SELECT MAX(sal) FROM emp WHERE NAME LIKE “%精%”
SELECT AVG(sal) FROM emp WHERE job=“程序员”
SELECT MIN(sal) FROM emp WHERE job=“销售”
SELECT COUNT() FROM emp WHERE manager IS NOT NULL
SELECT COUNT(1) 人数,AVG(sal) 平均工资 FROM emp WHERE dept_id=3

#分组查询 group by
SELECT dept_id,AVG(sal) 平均工资 FROM emp GROUP BY dept_id
SELECT job,AVG(sal) 平均工资 FROM emp GROUP BY job
SELECT dept_id,MAX(sal) FROM emp GROUP BY dept_id
SELECT job,COUNT(1) FROM emp GROUP BY job
SELECT dept_id,COUNT(1) FROM emp WHERE sal>2000 GROUP BY dept_id
SELECT dept_id,COUNT(1) FROM emp WHERE manager IS NOT NULL GROUP BY dept_id

#where 后面只能写普通条件的字段，不能写聚合函数函数
#having 关键字 作用和where类似都是用来添加条件按的，
#但是having后面专门写聚合函数条件
#而且having要和group by分组查询结合使用，写在分组关键字的后面
SELECT dept_id,AVG(sal) FROM emp
GROUP BY dept_id HAVING AVG(sal)>2000

SELECT job,COUNT(1) c FROM emp GROUP BY job HAVING c>1

SELECT dept_id,SUM(sal) FROM emp
WHERE manager IS NOT NULL
GROUP BY dept_id HAVING SUM(sal)>5400

SELECT dept_id,AVG(sal) FROM emp
WHERE sal BETWEEN 1000 AND 3000
GROUP BY dept_id HAVING AVG(sal)>=2000