MySql语句常用命令整理---单表查询

2021-09-06 23:34:34

初始化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)

);

修改字段

Mysql修改字段类型，修改字段名

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

('', 'SMITH', 'CLERK', '', '2011-03-12', null, null, ''),

('', 'ALLEN', 'SALESMAN', '', '2012-03-12', '1600.00', '300.00', ''),

('', 'WARD', 'SALESMAN', '', '2013-03-12', '1250.00', '500.00', ''),

('', 'JONES', 'MANAGER', '', '2011-03-12', '2975.00', null, ''),

('', 'MARTIN', 'SALESMAN', '', '2011-03-12', '1250.00', '1400.00', ''),

('', 'BLAKE', 'MANAGER', '', '2011-03-12', '2850.00', null, ''),

('', 'CLARK', 'MANAGER', '', '2015-03-12', '2450.00', null, ''),

('', 'SCOTT', 'ANALYST', '', '2011-03-12', '3000.00', null, ''),

('', 'KING', 'PRESIDENT', null, '2011-03-12', '5000.00', null, ''),

('', 'TURNER', 'SALESMAN', '', '2014-03-12', '1500.00', '0.00', ''),

('', 'ADAMS', 'CLERK', '', '2016-03-12', '1100.00', null, ''),

('', 'JAMES', 'CLERK', '', '2015-03-12', '950.00', null, ''),

('', 'FORD', 'ANALYST', '', '0000-00-00', '3000.00', null, ''),

('', 'MILLER', 'CLERK', '', '2011-03-12', '1300.00', null, '');

简单查询

查询所有员工的年薪

Mysql中支持 + - * / %等数学运算。

-- as 起别名使查询结果更直观

SELECT ename,sal * 12 as totalSal FROM t_employee;

查询结果的拼接显示

SELECT CONCAT(ename,"的年薪为",sal * 12,"美元") AS total FROM t_employee;

CONCAT 采用数量可变的字符串自变量并将它们连接到单个字符串。所有参数都隐式转换为字符串类型，然后串联在一起。 Null 值被隐式转换为空字符串。条件查询条件中，支持下列内容

关系运算符和逻辑运算符关系运算符：
- > < = != >= <=
逻辑运算符：
- 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;

** 注意：在Mysql中，如果字段的值为null，则该值为最小值，因此在降序排序中将最后显示，而在升序排序中则将最先显示。**

多字段排序：查询所有员工信息，并将员工按照工资的升序排列，如果工资相同，则按照入职日期降序排序。

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;

AVG 函数返回数值列的平均值。NULL 值不包括在计算中。

查询所有员工工资的总和

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;

** where条件和having条件的区别 **

having只能用在group by之后，对分组后的结果进行筛选；

　　　 where和组函数一起使用时，where肯定在group by 之前，会在group by之前进行筛选结果，优先级高于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

码农公寓