本单元目标
一、为什么要学习数据库
二、数据库的相关概念
DBMS、DB、SQL
三、数据库存储数据的特点
四、初始MySQL
MySQL产品的介绍
MySQL产品的安装 ★
MySQL服务的启动和停止 ★
MySQL服务的登录和退出 ★
MySQL的常见命令和语法规范
五、DQL语言的学习 ★
基础查询 ★
条件查询 ★
排序查询 ★
常见函数 ★
分组函数 ★
分组查询 ★
连接查询 ★
子查询 √
分页查询 ★
union联合查询 √
六、DML语言的学习 ★
插入语句
修改语句
删除语句
七、DDL语言的学习
库和表的管理 √
常见数据类型介绍 √
常见约束 √
八、TCL语言的学习
事务和事务处理
九、视图的讲解 √
十、变量
十一、存储过程和函数
十二、流程控制结构
数据库的好处
1.持久化数据到本地
2.可以实现结构化查询,方便管理
数据库相关概念
1.DB (DateBase):数据库,保存一组有组织的数据的容器 (DateBase)
2.DBMS(Datebase Management System):数据库管理系统,又称为数据库软件(产品),用于管理DB中的数据
3.SQL(Structure Query Language):结构化查询语言,用于和DBMS通信的语言(SQL语言)
数据库存储数据的特点
1.将数据放到表中,表再放到库中
2.一个数据库中可以有多个表,每个表都有一个的名字,用来标识自己。表名具有唯一性。
3.表具有一些特性,这些特性定义了数据在表中如何存储,类似java中 “类”的设计。
4.表由列组成,我们也称为字段。所有表都是由一个或多个列组成的,每一列类似java 中的”属性”
5.表中的数据是按行存储的,每一行类似于java中的“对象”。
MySQL产品的介绍和安装
MySQL服务的启动和停止
方式一:计算机——右击管理——服务
方式二:cmd通过管理员身份运行(重点)
net start 服务名(启动服务)
net stop 服务名(停止服务)
MySQL服务的登录和退出
方式一:通过mysql自带的客户端(找到打开就行)
只限于root用户
输入exit或者按crtl+C就可以退出
方式二:通过windows自带的客户端
登录:
mysql 【-h 主机名 -P 端口号 】-u 用户名 -p(直接接密码)【】:连接本机时内容可省略
然后输入密码(-p后直接加密码也行,但-p和密码之间不能有空格)
退出:
exit或ctrl+C
MySQL的常见命令
1.查看当前所有的数据库
show databases;
test 库里面是空的,其他不能动。
2.打开指定的库
use 库名
3.查看当前库的所有表
show tables;
4.查看其它库的所有表
show tables from 库名;(没有出当前库,只是查看一下这个库里的表)
看当前所在的库:select database();
5.创建表
create table 表名(
列名 列类型,
列名 列类型,
。。。
);
6.查看表结构
desc 表名;
7.查看服务器的版本
方式一:登录到mysql服务端
select version();
方式二:没有登录到mysql服务端
mysql --version
或
mysql --V
MySQL的语法规范
1.不区分大小写,但建议关键字大写,表名、列名小写
2.每条命令最好用分号结尾
3.每条命令根据需要,可以进行缩进 或换行
4.注释
单行注释:#注释文字
单行注释:--(空格)注释文字
多行注释:/* 注释文字 */
SQL的语言分类
DQL(Data Query Language):数据查询语言
select
DML(Data Manipulate Language):数据操作语言
insert 、update、delete
DDL(Data Define Languge):数据定义语言
create、drop、alter
TCL(Transaction Control Language):事务控制语言
commit、rollback
SQL的常见命令
show databases; 查看所有的数据库
use 库名; 打开指定 的库
show tables ; 显示库中的所有表
show tables from 库名;显示指定库中的所有表
create table 表名(
字段名 字段类型,
字段名 字段类型
); 创建表
desc 表名; 查看指定表的结构
select * from 表名;显示表中的所有数据
DQL语言的学习
进阶1:基础查询
语法:
SELECT 要查询的东西
【FROM 表名】;
类似于Java中 :System.out.println(要打印的东西);
特点:
①通过select查询完的结果 ,是一个虚拟的表格,不是真实存在
② 要查询的东西 可以是常量值、可以是表达式、可以是字段、可以是函数
进阶1:基础查询代码块
进阶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;
#第二种方式,可以按照不同顺序进行显示,按F12进行整理
SELECT
`first_name`,#双击表中的字段进行选择,` `着重号,可以区分字段和关键字
`last_name`,
`email`,
`phone_number`,
`job_id`,
`salary`,
`commission_pct`,
`manager_id`,
`department_id`,
`hiredate`
FROM
employees ;
#4.查询常量值
SELECT 100;
SELECT 'john';#没有字符和字符串的区分,统一用''
#5.查询表达式
SELECT 100*98;
#6.查询函数
SELECT VERSION();#显示方法(java)
#7.起别名
/*
好处:
1.便于理解字段意思
2.如果要查询的字段有重名的情况,使用别名可以区分出来
*/
#方式1:使用AS
SELECT 100*98 AS 结果;
SELECT last_name AS 姓,first_name AS 名 FROM employees;
#方式2:使用空格
SELECT last_name 姓,first_name 名 FROM employees;
#特殊情况
#案例:查询salary,显示结果为 out put ,如果不加双引号就会报错,因为out也是一个关键字
SELECT salary AS "out put" FROM employees;
#8.去重
#案例:查询员工表中涉及到的所有部门编号
SELECT department_id FROM employees;#去重前
SELECT DISTINCT department_id FROM employees;#去重后,在字段前加关键字:distinct
#9.+号的作用
/*
java中的+号:
1.运算符
2.连接符
mysql中的+号:
仅仅只要一个功能:运算符
select 100+90;两个操作数都为数值型,则做加法运算
select '123'+90; 其中一方为字符型,试图将字符型数值转换成数值型,
如果转换成功,则继续做加法运算
select 'john'+90;如果转换成失败,则将字符型数值转换成0;
select null+10; 只要其中一方为null,则结果肯定为null;
*/
#案例:查询员工名和姓连接成一个字段,并显示为姓名
SELECT
last_name + first_name AS 姓名
FROM
employees ;
#上述案例+号没有连接符的作用
#用concat可以拼接多个字符串,使得上述案例成功
SELECT CONCAT(last_name,first_name) AS 姓名 FROM employees;
进阶2:条件查询
条件查询:根据条件过滤原始表的数据,查询到想要的数据
语法:
select
要查询的字段|表达式|常量值|函数
from
表
where
条件 ;
分类:
一、条件表达式
示例:salary>10000
条件运算符:
> < >= <= = != <>
二、逻辑表达式
示例:salary>10000 && salary<20000
逻辑运算符:
and(&&):两个条件如果同时成立,结果为true,否则为false
or(||):两个条件只要有一个成立,结果为true,否则为false
not(!):如果条件成立,则not后为false,否则为true
三、模糊查询
示例:last_name like 'a%'
进阶2:条件查询代码块
进阶2:条件查询
/*
语法:
select
查询列表(最后执行)
from
表名 (先执行)
where
筛选条件;(条件成立true就可以显示,不成立false就pass)相当于java中的if(),再执行
分类:
一.按条件表达式筛选
条件运算符:> < = <>(不等于) >= <=
二.按逻辑表达式筛选
逻辑运算符:&& || !
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到2000之间的员工名,工资以及奖金
SELECT
last_name,
salary,
commission_pct
FROM
employees
WHERE salary >= 10000 AND salary <= 20000 ;
#案例2:查询部门编号不是在90到110之间,或者工资高于15000的员工信息
SELECT
last_name
FROM
employees
WHERE NOT (
department_id >= 90
AND department_id <= 110
)
OR salary > 15000 ;
#三 模糊查询
/*
like
特点:
1.一般和通配字符搭配使用
通配符:
% 任意多个字符,包含0个字符
_任意单个字符
between and
注意事项:
1.可以提高语句的简洁度
2.包含临界值
3.不可以颠倒值大小顺序 如果是between 120 and 100:表示>=120 <=10
in
注意事项:
1.含义:判断某字段的值是否属于in列表中的某一项
2.特点:使用in提高语句简洁度
in列表的值类型必须同统一或兼容
字段不可以使用通配符
is null|is not null
注意事项:
=或者<>不能用于判断null值
is null 或 is not null 可以判断null值
*/
#1.like
#案例1:查询员工名中包含字符a的员工信息
SELECT
*
FROM
employees
WHERE last_name LIKE '%a%' ;#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 '_$_%' ESCAPE '$';#转义字符为‘$’;推荐使用这个
#WHERE last_name LIKE '_/_%'; 也可以用/用来转义
#2.between and
#案例1:查询员工编号在100-120之间的员工信息
SELECT
*
FROM
employees
WHERE employee_id BETWEEN 100
AND 120 ;
#3.in
#案例:查询员工的工种编号是 IT_PROG,AD_VP,AD_PRES中的一个员工名和工种编号
SELECT
last_name,
job_id
FROM
employees
WHERE job_id IN ('IT_PROG', 'AD_VP', 'AD_PRES') ;
#4.is null
#案例1:查询没有奖金的员工名和奖金率
SELECT
last_name,
commission_pct
FROM
employees
WHERE commission_pct IS NULL ;
#案例2:查询有奖金的员工名和奖金率
SELECT
last_name,
commission_pct
FROM
employees
WHERE commission_pct IS NOT NULL ;
#安全等于 <=> 可以判断null值,也可以判断常量值(可读性不高,不建议使用)
#案例1:查询没有奖金的员工名和奖金率
SELECT
last_name,
commission_pct
FROM
employees
WHERE commission_pct <=> NULL ;
进阶3:排序查询
语法:
select
要查询的东西
from
表
where
条件
order by 排序的字段|表达式|函数|别名 【asc(升序)|desc(降序)】
进阶3:排序查询代码块
进阶3:排序查询
/*
引入:
select * from employees;
语法:
select 查询列表 3
from 表 1
where 筛选条件 2
order by 排序列表 asc|desc 4
特点:
1.asc 代表的是升序,desc代表的是降序
如果不写,默认是升序
2.order by 子句中可以支持单个字段,多个字段,表达式,函数,别名
3.order by 子句一般是放在查询语句的最后面,但是limit子句除外
*/
#案例1:查询员工信息,要求工资从高到低排序
SELECT * FROM employees ORDER BY salary DESC;
#案例:查询员工信息,要求工资从低到高排序(ASC可以省略)
SELECT * FROM employees ORDER BY salary ASC;
#案例2:查询部门编号>=90的员工信息,按入职时间先后进行排序【添加筛选条件】
SELECT
*
FROM
employees
WHERE department_id >= 90
ORDER BY hiredate ;
#案例3:按年薪的高低显示员工的信息和年薪【按表达式排序】
SELECT
*,
salary * 12 * (1+ IFNULL(commission_pct, 0)) AS 年薪
FROM
employees
ORDER BY salary * 12 * (1+ IFNULL(commission_pct, 0)) DESC;
#案例4:按年薪的高低显示员工的信息和年薪【按别名排序】
SELECT
*,
salary * 12 * (1+ IFNULL(commission_pct, 0)) AS 年薪
FROM
employees
ORDER BY 年薪 DESC;
#案例5:按照姓名的长度显示员工的姓名和工资【按函数排序】
SELECT
LENGTH(last_name) AS 字节长度,
last_name,
salary
FROM
employees
ORDER BY LENGTH(last_name) DESC ;
#案例6:查询员工信息,要求先按工资排序,再按员工编号排序[按多个字段排序]
SELECT
*
FROM
employees
ORDER BY salary ASC,
employee_id DESC ;
进阶4:常见函数
一、单行函数
1、字符函数
concat拼接
substr截取子串
upper转换成大写
lower转换成小写
trim去前后指定的空格和字符
ltrim去左边空格
rtrim去右边空格
replace替换
lpad左填充
rpad右填充
instr返回子串第一次出现的索引
length 获取字节个数
2、数学函数
round 四舍五入
rand 随机数
floor向下取整
ceil向上取整
mod取余
truncate截断
3、日期函数
now当前系统日期+时间
curdate当前系统日期
curtime当前系统时间
str_to_date 将字符转换成日期
date_format将日期转换成字符
4、流程控制函数
if 处理双分支
case语句 处理多分支
情况1:处理等值判断
情况2:处理条件判断
5、其他函数
version版本
database当前库
user当前连接用户
进阶4:常见函数代码块
进阶4:常见函数
/*
概念:类似于Java的方法,将一组逻辑语句封装在方法体中,对外暴露方法名
好处:1.隐藏了实现细节 2.提高了代码的重用性
调用:select 函数名(实参) 【from 表】;(如果函数里面没有用到表中的字段,就不用写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_date
date_format
其他函数:
version
database
user
控制函数:
if
case
*/
USE myemployees;
#一.字符函数
#1.length 获取参数值的字节个数
SELECT LENGTH('john') ;
SELECT LENGTH('张三丰hahaha');
SHOW VARIABLES LIKE '%char%';
#2.concat 拼接字符
SELECT CONCAT(last_name,'_',first_name) AS `name` FROM employees;
#3.upper lower
SELECT UPPER('john');
SELECT LOWER('joHn');
#示例:将姓变大写,名变小写,然后拼接
SELECT CONCAT(UPPER(last_name),LOWER(first_name)) AS `name` FROM employees;
#4.substr substring :截取字符
#注意:索引从1开始
#截取从指定索引处所有字符
SELECT SUBSTR('李莫愁爱上了陆展元',7) out_put;
#截取从指定索引处到指定字符长度的字符
SELECT SUBSTR('李莫愁爱上了陆展元',1,3);
#案例:姓名中首字符大写,其他字符小写,然后用_拼接,显示出来
SELECT
CONCAT(
UPPER(SUBSTR(last_name, 1, 1)),
SUBSTR(last_name, 2),
'_',
UPPER(SUBSTR(first_name, 1, 1)),
SUBSTR(first_name, 2)
)
FROM
employees ;
#5.instr:返回字串第一次出现的索引,如果找不到返回0
SELECT INSTR('杨不悔爱上了殷梨亭','殷梨亭') AS out_put;
#6.trim:取掉前字符串中后中的指定字符
SELECT TRIM(' 张翠山 ')AS out_put;
SELECT TRIM('a' FROM 'aaaa张aa翠aaa山aaaa') ;
#7.lpad 用指定的字符实现左填充指定长度
SELECT LPAD('殷素素',10,'*') output;
#8.rpad 用指定的字符实现右填充指定长度
#9.replace 替换
SELECT REPLACE('张无忌爱上了周芷若','周芷若','赵敏') AS out_put;
#二.数学函数
#1.round 四舍五入
SELECT ROUND(-4.7);
SELECT ROUND(1.567,2);#小数点后保留两位
#2.ceil 向上取整,返回>=该参数的最小整数
SELECT CEIL(-1.02);
#3.floor 向下取整,返回<=该参数的最大整数
SELECT FLOOR(-9.9);
#4.truncate 截断
SELECT TRUNCATE(1.65,1);#截取后一位
#5.mod取余 mod(a,b): a-a/b*b;
SELECT MOD(-10,3);
#三.日期函数
#1.now 返回当前系统日期+时间
SELECT NOW();
#2.curdate 返回当前系统日期,不包含时间
SELECT CURDATE();
#3.curtime 返回当前时间,不包含日期
SELECT CURTIME();
#4.可以获取指定的部分,年月日,小时,分钟,秒
SELECT YEAR(NOW()) 年;
SELECT YEAR('1998-1-1') 年;
SELECT YEAR(hiredate) 年 FROM employees;
SELECT MONTH(NOW()) 月;
SELECT MONTHNAME(NOW())月;
#5.str_to_date 将字符通过指定的格式转换为日期
SELECT STR_TO_DATE('1998-3-2','%Y-%m-%d');
#查询入职日期为1992-4-3号的员工信息
SELECT * FROM employees WHERE hiredate=STR_TO_DATE('4-3 1992','%m-%d %Y');
#6.date_format 将日期转换成字符
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日');
#查询有奖金的员工名和入职日期(xx月/xx日 xx年)
SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %Y年') 入职日期 FROM employees WHERE commission_pct IS NOT NULL;
#四.其他函数
SELECT VERSION();
SELECT DATABASE();#查看当前库
SELECT USER();#查看当前用户
#五.流程控制函数
#1.if函数 if else 的效果
SELECT IF(10>5,'big','small');#有点像三元运算符
SELECT last_name,commission_pct,IF(commission_pct IS NULL,'哈哈垃圾','niubi') 备注 FROM employees;
#2.case函数的使用一: switch case 的效果
/*
case 要判断的字段或者表达式
when 常量1 then 要显示的值1或者语句1;
when 常量2 then 要显示的值2或者语句2;
...
else 要显示的值n或语句n; (默认的情况相当于java中的default)
end
*/
/*案例:查询员工的工资,要求
部门号=30,显示的工资为1.1倍
部门号=40,显示的工资为1.2倍
部门号=50,显示的工资为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;
#3.case函数的使用二:类似于 多重if
/*
java中:
if(){
...}else if(){
...}else if(){
...}else{
...}
mysql中:
case
when 条件1 then 要显示的值1或 语句1;
when 条件12 then 要显示的值2或 语句2;
...
else 要显示的值n或 语句n
end
*/
#案例:查询员工的工资情况
/*
如果工资>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 求和