MySql语句常用命令整理---单表查询
初始化t_employee表
-
创建t_employee表
-- DROP TABLE IF EXISTS test; CREATE TABLE t_employee ( _id INTEGER PRIMARY KEY AUTO_INCREMENT, empno INTEGER(11) NOT NULL, name VARCHAR(100) NOT NULL, job VARCHAR(100), mgr INTEGER(11), hirdate DATE NOT NULL, sal DOUBLE(8 , 2 ), comm DOUBLE(8 , 2 ), deptno INTEGER(11) );
- 修改字段
ALTER table t_employee change hirdate hiredate DATE NOT NULL; ALTER table t_employee change name ename VARCHAR(100) NOT NULL;
-- alert tbale** modify column aa varchar(60) default null comment '名称'
-- double (8,2) 总共占8位数字,小数点后占2位,小数点前占6位。2代表精度。
-
插入数据
INSERT INTO t_employee (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES ('7369', 'SMITH', 'CLERK', '7902', '2011-03-12', null, null, '20'), ('7499', 'ALLEN', 'SALESMAN', '7698', '2012-03-12', '1600.00', '300.00', '30'), ('7521', 'WARD', 'SALESMAN', '7698', '2013-03-12', '1250.00', '500.00', '30'), ('7566', 'JONES', 'MANAGER', '7839', '2011-03-12', '2975.00', null, '20'), ('7654', 'MARTIN', 'SALESMAN', '7698', '2011-03-12', '1250.00', '1400.00', '30'), ('7698', 'BLAKE', 'MANAGER', '7839', '2011-03-12', '2850.00', null, '30'), ('7782', 'CLARK', 'MANAGER', '7839', '2015-03-12', '2450.00', null, '10'), ('7788', 'SCOTT', 'ANALYST', '7566', '2011-03-12', '3000.00', null, '20'), ('7839', 'KING', 'PRESIDENT', null, '2011-03-12', '5000.00', null, '10'), ('7844', 'TURNER', 'SALESMAN', '7698', '2014-03-12', '1500.00', '0.00', '30'), ('7876', 'ADAMS', 'CLERK', '7788', '2016-03-12', '1100.00', null, '20'), ('7900', 'JAMES', 'CLERK', '7698', '2015-03-12', '950.00', null, '30'), ('7902', 'FORD', 'ANALYST', '7566', '0000-00-00', '3000.00', null, '20'), ('7934', 'MILLER', 'CLERK', '7782', '2011-03-12', '1300.00', null, '10');
简单查询
-
查询所有员工的年薪
Mysql中支持 + - * / %等数学运算。
-- as 起别名使查询结果更直观
SELECT ename,sal * 12 as totalSal FROM t_employee;
-
查询结果的拼接显示
SELECT CONCAT(ename,"的年薪为",sal * 12,"美元") AS total FROM t_employee;
- 关系运算符和逻辑运算符关系运算符:
>
<
=
!=
>=
<=
- 逻辑运算符:
and
&&
or
||
xor(异或)
not
!
- between… and … : 范围查询
- is null / is not null: 是否为null/是否不为null
- in:枚举类型范围查询
- like : 模糊查询
- 查询工作为CLERK,并且薪水大于800的员工信息
select * from t_employee where job = 'CLERK' and sal > 800; select * from t_employee where job = 'CLERK' && sal > 800;
-
查询薪水在800和1500之间的员工信息
select * from t_employee where sal between 800 and 1500; -- 或者 select * from t_employee where sal>=800 and sal<=1500;
-
查询薪水不在800和1500之间的员工信息
select * from t_employee where sal not between 800 and 1500; select * from t_employee where sal >1500 or sal<800;
-
查询mgr为null的员工的信息/查询comm奖金不为null的员工的信息
SELECT * FROM t_employee WHERE mgr IS NULL; select * from t_employee where mgr is not null;
-
查询工号不是7521、7782、7566和7788的员工信息
select * from t_employee where empno not in (7521,7782,7566,7788); select * from t_employee where empno != 7521 && empno != 7782 && empno != 7566 and empno != 7788;
-
查询员工姓名中以A开头的员工的信息
SELECT * FROM t_employee WHERE ename LIKE ("A%");
-
查询员工姓名中第二个字母为A的员工的信息
select * from t_employee where ename like '_A%';
-
查询员工姓名中含A的员工的信息
SELECT * FROM t_employee WHERE ename LIKE ("%A%");
查询结果排序
-
查询所有员工信息,并将员工按照工资的升序排列/降序排列
SELECT * FROM t_employee order by sal ASC; select * from t_employee order by sal desc;
- 多字段排序:查询所有员工信息,并将员工按照工资的升序排列,如果工资相同,则按照入职日期降序排序。
SELECT * FROM t_employee order by sal asc,hiredate desc;
限制数据查询数量(分页查询)
- 分页查询全部员工信息,每页查询5条。
SELECT * FROM t_employee limit 0,5;-- 第一页 select * from t_employee limit 5,5;-- 第二页 select * from t_employee limit 10,5;-- 第三页 -- 如果客戶端发送来的数据是页码a和每页条数b -- SELECT * FROM t_employee limit (a-1)*b,b;
- 查询奖金为null的前两条记录
SELECT * FROM t_employee where comm is null limit 0,2;
统计函数
- 查询公司领取奖金的人数
select count(comm) from t_employee where comm != 0; -- NULL 值不包括在计算中
-
查询员工领取奖金的平均值
select avg(comm) from t_employee where comm != 0;
- 查询所有员工工资的总和
select sum(sal) from t_employee;
-
查询员工中最高工资和最低工资
select max(sal),min(sal) from t_employee;
分组数据查询
- 查询每个部门员工的工资总和
select deptno, sum(sal) from t_employee group by deptno;
- 查询每个部门员工的人数、工资总和、平均工资、最高工资和最低工资
select deptno,count(empno),sum(sal),avg(sal),max(sal),min(sal) from t_employee group by deptno;
- 按照部门编号和入职日期分组,统计每组的工资总和、平均工资
select deptno,hiredate,sum(sal),avg(sal) from t_employee group by deptno,hiredate;
- 按照部门编号分组,查询每组工资总和大于10000的部门的人数、总工资、平均工资
select deptno,count(*),sum(sal) as allSal,avg(sal) from t_employee group by deptno having allSal > 10000;
select deptno,count(*),sum(sal) as allSal,avg(sal) from t_employee where comm = 0 is null group by deptno having allSal > 10000;
- having只能用在group by之后,对分组后的结果进行筛选;
- where作用在硬盘上的数据,having作用在内存中的数据,所以where效率高,在两者都能使用的情况下,优先选择where;
- where后面不能跟别名, having可以。
- where后的条件表达式里不允许使用聚合函数,而having可以
更新操作
-
设置SMITH员工的奖金为300
update t_employee set comm = 300 where ename = `SMITH`;
删除操作
-
删除姓名SMITH的记录
delete from t_employee where ename = SMITH;
-
删除全部数据
delete from t_employee; truncate student;
-
两者区别:
-
delete 将删除的条数返回,truncate则返回0;
-
delete 较慢,而truncate则速度快;
-
delete 不会改变自增值,而truncate则会讲自增值置为1从头开始;
-
drop table 表名,整个表都删除
-
http://www.cnblogs.com/chrisghb8812/p/9249855.html
-