MySQL单表查询

CREATE DATABASE company DEFAULT CHARSET 'utf8';

CREATE TABLE company.employee5( id int primary key AUTO_INCREMENT not null, name varchar(30) not null, sex enum('male','female') default 'male' not null, hire_date date not null, post varchar(50) not null, job_description varchar(100), salary double(15,2) not null, office int, dep_id int );

insert into company.employee5(name,sex,hire_date,post,job_description,salary,office,dep_id) values ('jack','male','20180202','instructor','teach',5000,501,100), ('tom','male','20180203','instructor','teach',5500,501,100), ('robin','male','20180202','instructor','teach',8000,501,100), ('alice','female','20180202','instructor','teach',7200,501,100), ('tianyun','male','20180202','hr','hrcc',600,502,101), ('harry','male','20180202','hr',NULL,6000,502,101), ('emma','female','20180206','sale','salecc',20000,503,102), ('christine','female','20180205','sale','salecc',2200,503,102), ('zhuzhu','male','20180205','sale',NULL,2200,503,102), ('gougou','male','20180205','sale','',2200,503,102);

-- DQL 数据库查询语言
SELECT * FROM employee5;
SELECT DISTINCT(post) FROM employee5;  -- 去重
SELECT now(); -- 查询当前时间
SELECT DATABASE(); -- 查询当前数据库
SELECT user(); -- 查询当前登录的用户
SELECT SLEEP(10); -- 停滞10秒
SELECT 12455175195159 / 1024 / 1024 / 1024;
SELECT 10 > 5;
SELECT `name`,salary * 16 FROM employee5;-- 计算所有员工的年薪
SELECT CONCAT(`name`,'-',sex) as name_sex FROM employee5; -- 拼接字段，并修改临时列名
SELECT * from employee5 WHERE sex="male"; -- 查询所有的男生
SELECT * FROM employee5 WHERE sex="male" and salary >= 5500; -- 查询薪资大于等5500的男生
SELECT * FROM employee5 WHERE office = '501' or salary > 10000;     -- 查询在501办公室或薪资大于10000以上的员工
SELECT * FROM employee5 WHERE salary > 6000 and salary < 10000; -- 薪资大于6000且小于10000的员工
SELECT * FROM employee5 WHERE salary BETWEEN 6000 AND 10000; -- 薪资大于6000且小于10000的员工，在两者之间
SELECT * FROM employee5 WHERE job_description is not NULL; -- 判断是否为空
SELECT * FROM employee5 WHERE office in (501,503);-- 查询所有在501和503 办公室的员工
SELECT * FROM employee5 ORDER BY salary asc  -- 查询薪资，升序
SELECT * FROM employee5 ORDER BY salary desc -- 查询薪资，降序
SELECT * from employee5 LIMIT 5; -- 分页，查看前5条
SELECT * from employee5 LIMIT 2,5;  -- 第几条之后，数几条
SELECT sex,GROUP_CONCAT(`name`) FROM employee5 GROUP BY sex; -- 分组查询
SELECT * FROM employee5 WHERE `name` LIKE "%m%"; -- 查询名字中带有m的员工
SELECT COUNT(*) FROM employee5; -- 一共有多少条数据被查询
SELECT avg(salary) FROM employee5;-- 工作最高的员工max 最低的员工min 平均值avg
SELECT sum(salary) FROM employee5;
SELECT password("QianFeng@111");  -- 密码加密