创建表:
create table emp(
id int,
name varchar(100),
gender varchar(10),
birthday date,
salary float(10,2),
entry_date date,
resume text
);
1、insert into
INSERT INTO emp(id,name,gender,birthday,salary,entry_date,resume)
VALUES(1,'zhangsan','female','1990-5-10',10000,'2015-5-5-','good girl');
2、UPDATE
UPDATE emp SET salary=4000,gender='female' WHERE name='lisi';
3、DELETE
DELETE FROM emp WHERE name=‘zs’;
4、SELECT
SELECT 列名 FROM表名
【WHERE --> GROUP BY -->HAVING–> ORDER BY–>LIMIT 】
- where
条件查询就是在查询时给出WHERE子句,在WHERE子句中可以使用如下运算符及关键字:
- =、!=、<>、<、<=、>、>=;
- BETWEEN…AND;
- IN(set);
- IS NULL;
- AND;
- OR;
- NOT;
- LIKE
_ 任意一个字母
% 任意0~n个字母 - dintinct
SELECT DISTINCT sal FROM emp;
SELECT *, sal+IFNULL(comm,0) AS total FROM emp;//comm列有很多记录的值为NULL,因为任何东西与NULL相加结果还是NULL,所以结算结果可能会出现NULL。下面使用了把NULL转换成数值0的函数IFNULL
- group by
聚合函数
- sum:计算指定列的数值和,如果指定列类型不是数值类型,那么计算结果为0;
- avg:计算指定列的平均值,如果指定列类型不是数值类型,那么计算结果为0;
- max:计算指定列的最大值,如果指定列是字符串类型,那么使用字符串排序运算;
- min
- count:统计指定列不为NULL的记录行数;
统计月薪与佣金之和大于2500元的人数:
SELECT COUNT(*) AS cnt FROM emp WHERE sal+IFNULL(comm,0) > 2500;
avg只会统计不为null的字段的和的平均值,
当两个字段相加时,只要有一个字段为null,相加的结果就是null,
所以当 sum(sal+comm)当一条记录只要有一个字段为null,这条记录的sal+comm就是null,所以sum求和的时候是不会计算为null的记录的
SELECT avg(sal),avg(comm),sum(sal+IFNULL(comm,0)) FROM cat
注:凡和聚合函数同时出现的列名,则一定要写在group by 之后
SELECT deptno,COUNT(*) FROM emp GROUP BY deptno;//查询每个部门的部门编号以及每个部门的人数
SELECT deptno, SUM(sal) FROM emp GROUP BY deptno HAVING SUM(sal) > 9000;//查询工资总和大于9000的部门编号以及工资和
- order by
SELECT * FROM stu ORDER BY sage ASC;//ASC可不写 默认就说升序排列
SELECT * FROM stu ORDER BY age DESC;
SELECT * FROM emp ORDER BY sal DESC,empno ASC;//先按照sal降序排列,sal一样的按照empno 升序排列
- limit
SELECT * FROM emp LIMIT 3, 10;//查询10行记录,起始行从3开始
5、多表查询
- 内连接
SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno;
SELECT * FROM emp e INNER JOIN dept d ON e.deptno=d.deptno;
- 外连接
SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.deptno=d.deptno;//左外连接
SELECT * FROM emp e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno;//右外连接
- 自然连接
大家也都知道,连接查询会产生无用笛卡尔积,我们通常使用主外键关系等式来去除它。而自然连接无需你去给出主外键等式,它会自动找到这一等式:
两张连接的表中名称和类型完全一致的列作为条件,例如emp和dept表都存在deptno列,并且类型一致,所以会被自然连接找到!
当然自然连接还有其他的查找条件的方式,但其他方式都可能存在问题!
SELECT * FROM emp NATURAL JOIN dept;
SELECT * FROM emp NATURAL LEFT JOIN dept;
SELECT * FROM emp NATURAL RIGHT JOIN dept;
- 子查询
一个select语句中包含另一个完整的select语句。
子查询就是嵌套查询,即SELECT中包含SELECT,如果一条语句中存在两个,或两个以上SELECT,那么就是子查询语句了。
子查询出现的位置:
- where后,作为条为被查询的一条件的一部分;
当子查询出现在where后作为条件时,还可以使用如下关键字:any,all - from后,作表;
SELECT * FROM emp WHERE sal > ALL (SELECT sal FROM emp WHERE deptno=30)//工资高于30号部门所有人的员工信息
SELECT * FROM emp WHERE (job,sal) IN (SELECT job,sal FROM emp
WHERE ename='MARTIN')//查询工作和工资与MARTIN(马丁)完全相同的员工信息
SELECT * FROM emp WHERE empno IN(SELECT mgr FROM emp GROUP BY mgr
HAVING COUNT(mgr)>=2);//有2个以上直接下属的员工信息
SELECT e.ename, e.sal, d.dname, d.loc FROM emp e, (SELECT dname,loc,deptno
FROM dept) d WHERE e.deptno=d.deptno AND e.empno=7788//查询员工编号为7788的员工名称、员工工资、部门名称、部门地址