数据库的概念
数据库的好处?
1.可以持久化数据到本地;
2、结构化查询
数据库存储数据的特点?
1、数据存放到表中,表放到库中;
2、一个库可以有多张表,用表名来标识自己;
3、表中有一个或多个列,列称为字段;
常见的数据库管理系统?
MySQL;oracle,db2,sqlserver
MySQL数据库优点?
1、开源,免费,成本低;2、性能高,移植性好;3、体积小,便于安装
DQL语言
1、基础查询
SELECT 100; #查询常量
SELECT 'Join'; #查询字符
SELECT * FROM employees; #查询所有
SELECT 100*98; #查询表达式
SELECT VERSION(); #查询函数
SELECT last_name AS 姓 FROM employees;
SELECT last_name 姓 FROM employees; #起别名
SELECT last_name "out put" FROM employees; #别名和关键字重复时用双引号
SELECT DISTINCT department_id FROM employees; #DISTINCT关键字去重
SELECT 100+90; #两个都为数值型,则做加法
SELECT '123'+90; #只要其中一方为字符型,试图将字符型转换为数值型
SELECT ‘john’+90; #转换失败,则将字符型转换成0
SELECT null+10; #只要一方为null,则结果为null
DESC employees; #显示表employees的结构
SELECT CONCAT('a','b','c') AS 结果; #结果:abc
#concat拼接
SELECT CONCAT(last_name,first_name) AS 姓名 FROM employees;
#IFNULL判断是否为空
SELECT IFNULL(commission_pct,0) AS 奖金率,commission_pct
FROM employees;
2、条件查询
语法:select
查询列表 3
from
表名 1
where
筛选条件; 2
//1、按条件表达式:<,>,<=,>=,!=,<>,=
SELECT *
FROM employees
WHERE salary>12000;
2、按逻辑表达式筛选:&&,||,!,and,or,not
SELECT last_name,salary,commission_pct
FROM employees
WHERE salary>=10000 AND salary<=20000;
SELECT *
FROM employees
WHERE NOT(department_id>=90 AND department_id<=110) OR salary>15000;
3、模糊查询:like,between and,in,is null
//1、LIKE:与通配符连用(%:任意多个字符,包含0个字符)
//(_:任意单个字符)
SELECT *
FROM employees
WHERE last_name LIKE '%a%'; #查询员工中包含字符a的员工信息
#查询第二个字符为下划线_的员工名
SELECT last_name
FROM employees
WHERE last_name LIKE '_\_%'; #用'\'转义
或
WHERE last_name LIKE '_$_%' ESCAPE '$'; #escape转义
#2、BETWEEN AND:包含临界值
SELECT *
FROM employees
WHERE employee_id BETWEEN 100 AND 120;
#3、IN关键字
SELECT last_name,job_id
FROM employees
WHERE job_id IN ('IT_POOT','AD_VP');
#4、IS NULL或IS NOT NULL
SELECT last_name,comission_pct
FROM employees
WHERE comission_pct IS NULL;
3、排序查询
语法:
SELECT 查询列表
FROM 表 【WHERE 筛选条件】
ORDER BY 排序列表 【ASC | DESC】
#DESC:降序
#ASC:升序,不写默认升序
SELECT * FROM employees ORDER BY salary DESC;
SELECT *
FROM employees
WHERE department_id>=90
ORDER BY hiredate;
#按别名排序
SELECT *,salary*12*(1+IFNULL(comission_pct,0)) 年薪
FROM employees
ORDER bY 年薪 DESC;
#按函数排序
SELECT LENGTH(last_name) 字节长度
FROM employees
ORDER BY LENGTH(last_name) DESC;
#按多个字段排序
SELECT *
FROM employees
ORDER BY salary DESC,employees_id ASC;
4、常见函数
1、单行函数
concat、length、ifnull等
#字符函数
#1、length:统计参数值的字节个数
SELECT LENGTH('john'); #4
SELECT LENGTH('张三丰abc') #12
#2、concat:拼接字符串
SELECT CONCAT('a','b');
#3、upper,lower
SELECT UPPER('john'); #变大写
SELECT lower('john'); #变小写
#4、substr,substring
SELECT SUBSTR('李莫愁爱上了陆展元',6) output; #了陆展元(索引从1开始)
#数学函数
#1、round:四舍五入
SELECT ROUND(1.65); #2
#2、ceil:向上取整
SELECT CEIL(1.00); #1
#日期函数
#其他函数
#流程控制函数
SELECT IF(10<5,'大','小');
#case函数使用一
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;
#case函数使用二
SELECT salary,
CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
END AS 工资级别
FROM employees
2、分组函数(统计函数)
sum,avg,max,min,count
和分组函数一同查询的字段一般是GROUP BY
SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT COUNT(salary) FROM employees;
#和distinct使用
SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;
#count函数
#count(*)查询表的总行数
SELECT COUNT(*) FROM employees;