数据库的好处
- 实现数据 持久化
- 使用完整的管理系统统一管理,易于查询
相关概念
- DB:数据库(database):存储数据的“仓库”,保存了一系列有组织的数据
- DBMS:数据库管理系统(database management system),数据库是通过DBMS创建和操作的容器
- SQL:结构化查询语言,专门用来和数据库通信的语言
DBMS分为两类:
- 基于共享文件系统DBMS(Access)
- 基于客户机——服务器的DBMS(MySQL、Oracle、SqlServer)
SQL的优点:
- 几乎所有的DBMS都支持SQL
- 简单易学
数据库的特点:
- 将数据放到表中,表再放到库中
- 表名具有唯一性
- 表具有一些特性,这些特性定义了数据在表中如何存储,类似于Java中“类”的设计
- 表由列组成,也称作字段,所有的表都是由一个或多个列组成的,每一列类似Java类中的“属性”
- 表中的数据是按行存储的,每一行类似于Java中的“对象”
MySQL优点
- 成本低
- 性能高
- 安装和使用简单
MySQL启动
mysql [-h 主机名 -P 端口号] -u root -p
退出:exit或ctrl+c
MySQL常见命令
- 查看当前所有的数据库:
show databases;
- 打开指定的库:
use 库名;
- 查看当前库的所有表:
show tabels;
- 查看其他库的所有表:
show tabels from 库名;
- 创建表:
create tabel 表名(
列名 列类型,
列名 列类型,
...
)
- 查看表结构:
desc 表名;
- 查看服务器版本:
select version();
MySQL语法规范
- 不区分大小写,但是建议关键字大写,表名列名小写
- 每条命令用分号结尾
- 每条命令根据需要进行缩进或换行
- 注释
- 单行注释:#注释文字
- 单行注释:-- 注释文字
- 多行注释:/*注释文字*/
MySQL查询
进阶一:基础查询
#进阶1:基础查询
/*
语法:
select 查询列表 from 表名;
类似于:System.out.println(打印东西)
特点:
1.查询列表可以是:表中的字段、常量值、表达式、函数
2.查询的结果是一个虚拟的表格
*/
USE myemployees;
#1.查询表中的单个字段
SELECT last_name FROM employees;
#2.查询表中的多个字段
SELECT last_name, salary, email FROM employees;
#3.查询表中的所有字段
SELECT * FROM employees
#4.查询常量值
select 100;
select 'john';
#5.查询表达式
SELECT 100*98;
#6.查询函数
SELECT version( );
#7.起别名
/*
1.便于理解
2.如果查询的字段有重名的情况,使用别名可以区分开来
*/
#方式一:
SELECT 100%98 AS 结果;
#方式二:
SELECT last_name 姓, first_name 名 FROM employees;
#案例:查询salary,显示结果为out put
SELECT salary AS "out put" FROM employees;
#8.去重:查询员工表中设涉及到的所有部门编号
SELECT DISTINCT department_id FROM employees;
#9.+号的作用:作为运算符
SELECT 100+90;#加法运算
SELECT '123'+90;#试图将字符型数值转换为数值型,转换成功继续做加法
SELECT 'john'+90;#如果转换失败,则将字符型数值转换为0
SELECT null+10;#只要其中一方为null,则结果肯定为null
#案例:查询员工名和姓连接成一个字段,并显示为 姓名
SELECT CONCAT(last_name, ' ', first_name) AS 姓名 FROM employees;
#10.显示表的结构
DESC departments;
#多个属性组合,一旦有一个null存在,那么输出为null
#可以用IFNULL来判断是否为null,并做替换
SELECT
IFNULL( commission_pct, 0 ) AS 奖金率,
commission_pct
FROM
employees;
#练习:显示出表employees中员工姓名和奖金率,列之间用逗号连接,列头显示成OUT_PUT
SELECT
CONCAT( first_name, ',', last_name, ',', IFNULL( commission_pct, 0 ) ) AS out_put
FROM
employees
进阶二:条件查询
#进阶2:条件查询
/*
语法:
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;
#二、按逻辑表达式筛选
#案例1:查询工资在10000到20000之间的员工名、工资、奖金
SELECT
last_name,
salary,
commission_pct
FROM
employees
WHERE
salary >= 10000
AND salary <= 20000;
#案例2:查询部门编号不是在90~110之间,或者工资高于15000的员工信息
SELECT
*
FROM
employees
WHERE
NOT(department_id >= 90 AND department_id <= 110) OR salary>15000;
#三、模糊查询
#1.LIKE
#特点:1.一般和通配符%、_搭配使用,%代表任意个字符,_代表单个字符
#案例1:查询员工名中包含字符a的员工信息
SELECT
*
FROM
employees
WHERE
last_name LIKE '%a%';
#案例2:查询员工名中第三个字符为e,第五个字符为a的员工名和工资
SELECT
last_name,
salary
FROM
employees
WHERE
last_name LIKE '__n_l%';
#案例3:查询员工名中第二个字符为_的员工名
SELECT
last_name
FROM
employees
WHERE
last_name LIKE '_\_%';
#或者last_name LIKE '_$_%' ESCAPE '$';指定$为转义字符
#2.BETWEEN AND
#注意事项:包含临界值,不能颠倒顺序,值类型要一致或可以隐式转换
#案例1:查询员工编号在100到120之间的员工信息
SELECT
*
FROM
employees
WHERE
employee_id BETWEEN 100
AND 120;
#3.IN
#特点:1.in列表的值类型要一致或可以隐式转换
# 2.in列表中的值不可以用通配符
#案例:查询员工的工种编号是IT_PROG、AD_VP、AD_PRES其中的一个员工名和工种编号
SELECT
last_name,
job_id
FROM
employees
WHERE
job_id IN ( 'IT_PROT', 'AD_VP', 'AD_PRES' );
#4.IS NULL
#IS NOT NULL
#注:不能写=NULL或<>NULL,因为=或<>不能判断NULL值
#案例1:查询没有奖金的员工名和奖金率
SELECT
last_name,
commission_pct
FROM
employees
WHERE
commission_pct IS NULL;
#安全等于 <=>
#案例1:查询没有奖金的员工名和奖金率
SELECT
last_name,
commission_pct
FROM
employees
WHERE
commission_pct <=> NULL;
#案例2:查询工资为12000的员工名和工资
SELECT
last_name,
salary
FROM
employees
WHERE
salary <=> 12000;
#IS NULL:仅可以判断NULL值,可读性较高,建议使用
#<=>:既可以判断NULL,又可以判断普通数值,可读性较低
#练习:查询工号为176的员工的姓名和年薪
SELECT
last_name,
salary * 12 * ( 1+IFNULL ( commission_pct, 0 ) ) AS 年薪
#注意这里commission_pct可能为NULL,所以要处理一下
FROM
employees;
练习:
#试问
SELECT
*
FROM
employees
#和
SELECT
*
FROM
employees
WHERE
commission_pct LIKE '%%'
AND last_name LIKE '%%'
#结果是否一样
#答:如果结果包含null,不一样;不包含null,一样
进阶三:排序查询
#引入
SELECT
*
FROM
employees;
/*
语法:
SELECT 查询列表
FROM 表
[WHERE 筛选条件]
ORDER BY 排序列表 [ASC/DESC]
特点:
1.ASC升序,DESC降序。默认ASC
2.一般放在查询语句的最后面,limit子句除外
*/
#案例1:查询员工信息,要求工资从高到低排序
SELECT
*
FROM
employees
ORDER BY
salary DESC;
#案例2:查询部门编号>=90的员工信息,按入职时间的先后进行排序
SELECT
*
FROM
employees
WHERE
department_id >= 90
ORDER BY
hiredate;
#案例3:按年薪的高低现实员工信息和年薪
SELECT
*,
12 * salary * ( 1 + IFNULL( commission_pct, 0 ) ) AS '年薪'
FROM
employees
ORDER BY
12 * salary * ( 1 + IFNULL( commission_pct, 0 ) ) DESC;
#案例4:按姓名的长度显示员工的姓名和工资
SELECT
CONCAT( first_name, last_name ) AS NAME,
LENGTH( CONCAT( first_name, last_name ) ) AS length,
salary
FROM
employees
ORDER BY
LENGTH( NAME );
#案例5:查询员工信息,要求先按工资排序,再按员工编号排序
SELECT
*
FROM
employees
ORDER BY
salary ASC,
employee_id DESC;
#练习1:选择工资不在8000到17000的员工的姓名和工资,按工资降序
SELECT
last_name,
salary
FROM
employees
WHERE
salary NOT BETWEEN 8000 AND 17000
ORDER BY
salary DESC;
#练习2:查询邮箱中包含e的员工信息,并先按邮箱的字节数降序,再按部门号升序
SELECT
*
FROM
employees
WHERE
email LIKE '%e%'
ORDER BY
LENGTH( email ) DESC,
department_id ASC;
进阶四:常见函数
#进阶4:常见函数---单行函数
/*
概念:类似于Java的方法,将一组逻辑语句封装在方法体中,对外暴露方法名
好处:1.隐藏了实现细节 2.提高代码的重用性
调用:select 函数名(实参列表)【from 表】;
特点:
1.叫什么(函数名)
2.干什么(函数功能)
分类:
1.单行函数:如concat、length、ifnull等
2.分组函数
功能:做统计使用,又称为统计函数、聚合函数、组函数
常见单行函数函数总结:
字符函数:
length
concat
substr
instr
trim
upper
lower
lpad
rpad
replace
数学函数:
round
ceil
floor
truncate
mod
日期函数
now
curdate
curtime
year
month
monthname
day
hour
minute
second
str_to_data
date_format
其它函数:
version
user
控制函数:
if
case
*/
#一、字符函数
#1.length
SELECT LENGTH('中文haha');#一个中文占3个字节
#2.concat
SELECT
CONCAT( last_name, '_', first_name ) 姓名
FROM
employees;
#3.upper、lower
SELECT UPPER('haha');
#4.substr、substring
#索引从1开始
SELECT SUBSTR('李莫愁爱上了陆展元',7) out_put;
SELECT SUBSTR('李莫愁爱上了陆展元',1, 3) out_put;#3代表长度
#案例:姓名中首字符大写,其它字符小写然后用_拼接,显示出来
SELECT
CONCAT( UPPER( SUBSTR( last_name, 1, 1 ) ), '_', LOWER( SUBSTR( last_name, 2 ) ) ) out_put
FROM
employees;
#5.instr:返回字串第一次出现的起始索引
SELECT
INSTR( '杨不悔爱上了殷六侠', '殷六侠' ) AS out_put;
#6.trim
SELECT TRIM( ' 张翠山 ' ) AS out_put;
SELECT TRIM('a' FROM 'aaaaa张翠山aaaa') AS out_put;
#7.lpad、rlpad:用指定的字符和长度实现左/右填充,注意这个长度是填充后的总长度
SELECT LPAD('徐震震', 10, '*') AS out_put;
#9.replace:替换
SELECT REPLACE('张无忌爱上了周芷若', '周芷若', '赵敏') out_put;
#二、数学函数
#round四舍五入
SELECT ROUND(-1.65);
SELECT ROUND(1.567, 2);#保留两位取整
#ceil向上取整,返回>=该参数的最小整数
SELECT CEIL(-1.92);
#floor向下取整
SELECT FLOOR(-9.19);
#truncate 截断
SELECT TRUNCATE(1.654321, 2);#小数点后保留2位
#mod取余
SELECT MOD(-10, 3);
#三、日期函数
#now
SELECT NOW();
#current
SELECT CURRENT_DATE;
SELECT CURRENT_TIME;
SELECT CURRENT_USER;
#获取指定的部分,年月日时分秒
SELECT YEAR(NOW()) 年;
SELECT YEAR('1994-9-20') 年;
SELECT MONTHNAME(NOW()) 月;
#str_to_date 将字符通过指定的格式转换成日期
SELECT STR_TO_DATE('1994-9-20','%Y-%c-%d') AS out_put;
SELECT
*
FROM
employees
WHERE
hiredate = STR_TO_DATE( '4-3 1992', '%c-%d %Y' );
#date_format 将日期转换成字符
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日') out_put;
#四、其它函数
SELECT VERSION();
SELECT DATABASE();
#五、流程控制函数
#1.if函数:if else的效果
SELECT IF(10<5,'大','小') 比较大小;#类似于三元表达式
SELECT
last_name,
commission_pct,
IF
( commission_pct IS NULL, '没奖金,哈哈', '有奖金,嘿嘿' ) 备注
FROM
employees;
#2.case函数的使用一:switch case的效果
/*
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1
when 常量2 then 要显示的值2或语句2
...
else 要显示的值n或语句n
end
*/
/*案例:查询员工的工资,要求:
部门号=30,显示的工资为1.1倍
部门号=40,显示的工资为1.2倍
部门号=50,显示的工资为1.3倍
其它部门,显示的工资为原工资
*/
SELECT last_name, 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;
#3.case函数的使用二:类似于多重if
/*
case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
...
else 要显示的值n或语句n
end
*/
/*
案例:查询员工的工资的情况
如果工资>20000,显示A级别
如果工资>15000,显示B级别
如果工资>10000,显示C级别
否则,显示D级别
*/
SELECT last_name, salary,
CASE
WHEN salary>20000 THEN
'A'
WHEN salary>15000 THEN
'B'
WHEN salary>10000 THEN
'C'
ELSE
'D'
END AS 级别
FROM employees;
练习:
#1.将员工的姓名按首字母排序,并写出姓名的长度
SELECT CONCAT(first_name,last_name) 姓名, LENGTH(CONCAT(first_name,last_name)) 姓名长度
FROM employees
ORDER BY 姓名;
/*
2.输出以下内容
job grade
AD_PRES A
ST_MAN B
IT_PROG C
*/
SELECT DISTINCT job_id job,
CASE job_id
WHEN 'AD_PRES' THEN
'A'
WHEN 'ST_MAN' THEN
'B'
WHEN 'IT_PROG' THEN
'C'
END AS grade
FROM employees
WHERE job_id IN('AD_PRES', 'ST_MAN', 'IT_PROG')
ORDER BY grade;
分组函数:
/*
二、分组函数
功能:
用作统计使用,又称为聚合函数或统计函数或组函数
分类:
sum、avg、max、min、count
特点:
1.sum和avg一般只支持数值型,max、min、count支持任何类型
2.以上分组函数都忽略null
3.可以和distinct搭配实现去重的运算
4.count函数的详细介绍
5.和分组函数一同查询的字段要求是group by后的字段
*/
#1.简单的使用
SELECT SUM(salary) 总工资
FROM employees;
#平均工资,保留两位小数
SELECT ROUND(AVG(salary),2) 平均工资
FROM employees;
#和distinct搭配
SELECT SUM(DISTINCT salary) 去重后求和, SUM(salary) 直接求和
FROM employees;
#count的详细介绍
SELECT COUNT(*) 总行数
FROM employees;
SELECT COUNT(1) 总行数
FROM employees;
#日期之差
#查询员工表中的最大入职时间和最小入职时间的相差天数
SELECT DATEDIFF(MAX(hiredate), MIN(hiredate)) 相差天数
FROM employees;