数据库相关概念
1.DB: 数据库,保存一组有组织的数据的容器
2.DBMS:
3.SQL:结构化查询语言,用于DBMS通信
MYSQL服务的登录和退出
登录:mysql 【-h主机名 -p端口号】 -用户名 -密码
退出: exit , ctrl c 如: mysql -u root -p123456
MYSQL的常见命令
1.查看当前所有的数据库: show database;
2.打开指定的库 :use库名
3.查看当前库的所有类 :show tables;
4.查看其他库的所有类 :show table 表名;
5.创建表 : create table 表名(
列名 列类型,
列名 列类型,
。。。
);
6.查看表结构 :desc 表名;
7.查看服务器的版本
方式一:登录到mysql 服务端 select version();
方式二:没有登录到mysql服务端 mysql --version 或 mysql --V
注释:
单行注释:#注释文字 或 -- 注释文字
多行注释:/* 注释文字 */
基础查询
USE myemployees;
-- 查表中单个字段
SELECT last_name FROM employees;
-- 查询表中的多个字段
SELECT last_name,salary,email FROM employees;
-- 查询表中的所有字段
SELECT * FROM employees;
-- 查询常量值
SELECT 100;
SELECT 'join';
-- 查询表达式
SELECT 100*23;
-- 查询函数
SELECT VERSION();
-- 起别名 as 或者用空格
SELECT 100*123 AS 结果;
-- 去重
SELECT DISTINCT department_id FROM employees;
-- + 号的作用:只有一个功能 运算符加法运算
SELECT 'join'+99; -- 结果为99 转换失败,将字符型数值转换为0
SELECT '133'+99; -- 结果为232,转换成功 继续做加法运算
SELECT NULL+99; -- 结果为null,只要其中一方为null,则结果肯定为null
-- 拼接 CONCAT 可以嵌套 ifnull(a,0)
SELECT CONCAT('a','b','c')AS 结果;
SELECT CONCAT (last_name,first_name)AS 姓名 FROM employees;
条件查询
#条件查询
/*语法
select
查询列表
from
表名
where
筛选条件;
分类
按条件表达式筛选
条件运算符 > < = <> >= <=
按逻辑表达式
逻辑运算符 && || !
and or not
模糊查询
like
between and
in
is null
*/
-- 1.查询工资>12000的员工信息
SELECT *
FROM employees
WHERE salary>12000;
-- 2.查询部门编号不等有90的员工名和部门名
SELECT last_name,department_id
FROM employees
WHERE department_id != 90;
-- 3.查询工资在10000到20000之间的员工名,工资以及奖金
SELECT last_name,salary,commission_pct
FROM employees
WHERE salary>=10000 AND salary<=20000;
-- 4.查询部门编号不是在90到110之间,或者工资高于15000的员工信息
SELECT *
FROM employees
WHERE department_id<90 OR department_id>110 OR salary>15000;
-- 5.查询员工名中包含字符a的员工信息
SELECT *
FROM employees
WHERE last_name LIKE '%a%';
-- 6.查询员工名中第三个字符为a 第五个字符为a的员工的工资和员工名
SELECT salary,last_name
FROM employees
WHERE last_name LIKE '__n_l%';
-- 7.查询员工名中第二个字符为_的员工名
SELECT last_name
FROM employees
WHERE last_name LIKE '_$_%' ESCAPE '$';
-- 8.查询员工编号在100-120的员工信息
SELECT *
FROM employees
WHERE employee_id BETWEEN 100 AND 120; -- 包含临界值,不可调换顺序的
-- 9.询员工的工种编号是 IT_PROG,AD_PRES的一个员工名和编号
SELECT last_name,job_id
FROM employees
WHERE job_id IN('IT_PROG','AD_PRES'); -- in列表的值的了类型必须统一或兼容
-- 10.查询没有奖金的员工名和奖金率
SELECT last_name,commission_pct
FROM employees
WHERE commission_pct IS NULL;
-- 11.安全等于
SELECT last_name,commission_pct
FROM employees
WHERE commission_pct <=>NULL;
-- 12.查询员工号为176的员工的姓名和部门号和年薪
SELECT last_name,department_id,salary*12*(1+IFNULL(commission_pct,0))AS 年薪
FROM employees
WHERE employee_id = 176;
#练习
-- 13.查询没有奖金,且工资小于18000的salary,last_name
SELECT salary,last_name
FROM employees
WHERE commission_pct IS NULL AND salary<18000;
#14.试问select *from employees;和
# selet * from empleyees
# where commission_pct like'%%'and last_name like '%%';
# 不一样的,判断的字段有null值就不一样了
# 换成or的话是一样的
排序
USE myemployees;
-- 15.查询员工信息,要求工资从高到底排序
SELECT *
FROM employees
GROUP BY salary DESC; -- desc 降序 默认升序
-- 16.查询部门编号大于等于90的员工信息,按入职时间的先后顺序进行排序
SELECT *
FROM employees
WHERE department_id>=90
GROUP BY hiredate;
-- 17.按年薪的高低显示员工的信息和年薪
SELECT *, salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees
GROUP BY 年薪;
-- 18.按姓名的长度显示员工的姓名和工资
SELECT LENGTH(last_name) AS 名字长度,last_name,salary
FROM employees
ORDER BY 名字长度;
-- 19.查询员工信息,先按工资排序再按员工编号排序
SELECT *
FROM employees
ORDER BY salary ASC ,department_id DESC;
特点:order by 字句中可以支持单个字段,多个字段,表达式,函数,别名
order by 字句一般是放在查询语句的最后面,limit语句除外
函数
#字符函数
-- length()获取字节个数 utf-8每个汉字三个字节15
SELECT LENGTH('丁芳芳linlin');
SELECT LENGTH('join'); -- 4
SHOW VARIABLES LIKE '%char%';
-- concat()拼接字符串
SELECT CONCAT(last_name,'_',first_name) 姓名
FROM employees;
-- upper() 转为大写, lower()转为小写
-- substr ,substring 索引从1 开始
-- 两个参数的截取:从指定索引处开始截取指定长度的字符
SELECT SUBSTR('我想你了,琳琳',5) out_put; -- ,琳琳
-- instr 返回子串第一次出现的索引,如果找不到返回0
-- trim去除字符串两边的空格
-- lpad 左填充,用指定的字符实现左填充指定的长度,
-- rpad 右填充同理
SELECT LPAD('何苗苗',6,'AA') OUT_PUT;
-- replace 替换
SELECT REPLACE('AABBCC','AA','DD') SOUT; -- DDBBCC
#数学函数
-- round四舍五入 负数先算绝对值再加符号
SELECT ROUND(1.56);
SELECT ROUND(1.238,2);-- 小数点后保留两位 1.24
-- ceil 向上取整,返回大于等于该参数的最小整数
SELECT CEIL(1.00); -- 1
SELECT CEIL(-2.35);-- -2
-- floor 向下取整,返回小于等于该参数的最大整数
SELECT FLOOR(9.83); -- 9
SELECT FLOOR(-23.34); -- 24
-- truncate 截断
SELECT TRUNCATE(23.599,2); -- 23.59
-- MOD()取余
/*有意思
mod(a,b):a-a/b*b
mod(-10,-3):-10-(-10)/(-3)*(-3) = -1
*/
SELECT MOD(10,-3); -- 1 符号与10的符号相同
#日期函数
-- now() 返回当前系统日期 + 时间
SELECT NOW();-- 2020-04-07 16:26:42
-- curdate() 返回当前系统日期,不包含时间
SELECT CURDATE();-- 2020-04-07
-- currime()
SELECT CURTIME();-- 16:29:37
-- 获取年
SELECT YEAR(NOW()) 年;-- 2020
-- STR_TO_DATE查询入职日期为1992-4-3的员工信息
SELECT *
FROM employees WHERE hiredate = STR_TO_DATE('4-3 1992','%c-%d %Y');
-- date-formate() 将日期转换为字符
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日')AS sout;
#流程控制函数
-- if函数:if else的结果
SELECT IF( 10>3,'大于','小于');
-- CASE函数的使用 switch-case
-- 查询员工的工资,要求
/*
部门号 = 30,显示的工资为1.1倍
部门号 = 40,显示的工资为1.2倍
部门号 = 40,显示的工资为1.3倍
*/
SELECT salary department_id,
CASE department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary
END AS 新工资
FROM employees;
-- 查询员工的工资情况
/*
如果工资>20000,显示A级别
如果工资>15000,显示B级别
如果工资>10000,显示C级别
否则,显示D级别
*/
SELECT salary,
CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END AS 工资级别
FROM employees;
#分组函数
-- sum avg min max count
SELECT SUM(salary) ,AVG(salary) ,MIN(salary), MAX(salary),COUNT(salary)
FROM employees;
-- distinct与分组函数搭配
SELECT SUM(DISTINCT salary)
FROM employees;
-- count() 函数,统计行数
SELECT COUNT(*) FROM employees;
和分组函数一同查询的字段要求是group by后的字段
分组查询
#分组函数
-- 查询每个部门的平均工资
SELECT AVG(salary), department_id
FROM employees
GROUP BY department_id;
-- 查询邮箱中包含a字符的,每个部门的平均工资
SELECT AVG(salary), department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;
-- 查询有奖金的每个领导手下员工的最高工资
SELECT MAX(salary),manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;
-- 查询那个部门的员工个数大于2
SELECT COUNT(*) c ,department_id
FROM employees
GROUP BY department_id
HAVING c>2;
-- 查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
SELECT job_id,MAX(salary) m
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING m>12000 ;
-- 查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个,以及其最低
SELECT manager_id,MIN(salary) m
FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING m>5000;
-- 查询按员工姓名的长度分组,查询每一组的员工个数,筛选员工个数>5的有哪些
SELECT COUNT(*) c, LENGTH(last_name) m
FROM employees
GROUP BY m
HAVING c>5;
-- 查询每个部门每个工种的员工的平均工资,平均工资大于10000的,按平均工资的高低显示
SELECT AVG(salary) a,department_id,job_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id, job_id
HAVING a>10000
ORDER BY a DESC;
-- 查询各个管理者手下员工的最低工资,其中最低工资不能低于6000,没有管理者的员工不计算在内
SELECT MIN(salary) m,manager_id
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING m>=6000;
All that work will definitely pay off