目录
Oracle、Mysql支持的事务隔离级别和默认的事务隔离级别:
一、为什么要学习数据库
数据库的好处
1.持久化数据到本地
2.可以实现结构化查询,方便管理
二、数据库的相关概念
数据库相关概念
1.DB:数据库,保存一组有组织的数据的容器
2.DBMS:数据库管理系统,又称为数据库软件(产品),用于管理DB中的数据
3.SQL:结构化查询语言,用于和DBMS通信的语言
三、数据库存储数据的特点
数据库存储数据的特点
1.将数据放到表中,表再放到库中
2.一个数据库中可以有多个表,每个表都有一个的名字,用来标识自己。表名具有唯一性。
3.表具有一些特性,这些特性定义了数据在表中如何存储,类似java中 “类”的设计。
4.表由列组成,我们也称为字段。所有表都是由一个或多个列组成的,每一列类似java 中的“属性”。
5.表中的数据是按行存储的,每一行类似于java中的“对象”。
常见的DBMS:MySQL、Oracle、DB2、SqlServer
Oracle:贵
DB2:适合处理大量数据
SqlServer:只能安装在windows操作系统下
DBMS分为两类:
–基于共享文件系统的DBMS,如Access
–基于客户机——服务器的DBMS,如MySQL、Oracle、SqlServer
客户机服务器,简称C/S架构,C代表客户端,S代表服务端。
MySQL使用时需要安装客户端和服务端。
“安装数据库”一般指安装数据库的服务端。
四、初始MySQL
MySQL产品的介绍和安装
MySQL服务的启动和停止
方式一:计算机——右击管理——服务(Windows管理工具——服务)
方式二:通过管理员身份运行(cmd——以管理员身份运行)
net start 服务名(启动服务)(net start mysql)
net stop 服务名(停止服务)(net stop mysql)
MySQL服务的登录和退出
方式一:通过mysql自带的客户端(只限于root用户)
方式二:通过windows自带的客户端
登录:mysql -uroot -p123456
mysql 【-h主机名 -P端口号 】-u用户名 -p密码(对于连接本机的端口号为3306的登录,【】中内容可以省略)
若显示密码:mysql -h localhost -P 3306 -u root -p123456
(注意:hlocalhost、P3306、uroot之间有无空格都可,但是p123456之间不能有空格,其中123456是账号root的密码)
若不想显示密码:mysql -h localhost -P 3306 -u root -p 按下回车键,再输入密码。
退出:exit或快捷键ctrl+c(此方式只适合root用户)
MySQL的常见命令
1.查看当前所有的数据库:show databases;
共4个数据库,其中:
information_schema用于保存元数据信息
mysql用于保存用户信息
performance_schema搜集性能信息、性能参数
sys
前3个不能动
2.打开指定的库:use 库名;
3.查看当前库的所有表:show tables;
4.查看其它库的所有表:show tables from 库名;
use sys;
show tables;
show tables from mysql;#在sys库中查看mysql库中所有的表
select database();#查看目前在哪个库,不要忘记()
5.创建表:
create table 表名(
列名 列类型,
列名 列类型,
…#最后一个字段不用加,
);
6.查看表结构,查看、插入、修改、删除表数据
查看表结构:desc 表名;
查看表数据:select * from 表名;
插入表数据:insert into 表名 (id,name) values(1,'john');
修改表数据:update 表名 set name='rose' where id=1;
删除表数据:delete from 表名 where id=1;
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脚本myemployees.sql,手动刷新。
五、DQL语言(数据查询语言)
进阶1:基础查询
语法:
SELECT 查询列表 #要查询的东西可以有多个
【FROM 表名】;
特点:
通过select查询完的结果,是一个虚拟的表格,不是真实存在的;
查询列表可以是:表中的字段、常量值、表达式、字段、函数
补充:
在做查询或一些操作之前,建议先打开/启用指定的库:use myemployees;
着重号`用以区分字段和关键字(键盘左上角第2排第1个)
要执行谁就选中谁,点击“执行查询”或者F9
不区分“字符”和“字符串”,没有“字符串”的概念,只要是字符,都用’’
commission_pct(奖金率)字段中有null,因此利用ifnull函数:
ifnull (字段名,如果该字段为null想要返回的值)
进阶2:条件查询
条件查询:
根据条件过滤原始表的数据,查询到想要的数据
语法:
select 查询列表
from 表名
where 筛选条件;
分类:
一、按条件表达式筛选
简单条件运算符: > < >= <= = != <>(mysql中标准写法为<>,也支持!=)
案例1:查询工资大于12000员工信息
select * from employees where salary>12000;
案例2:查询部门编号不等于90号的员工名和部门编号
select last_name,department_id from employees where department_id <>90;
二、按逻辑表达式筛选
逻辑运算符:
and(&&):两个条件同时成立,结果为true,否则为false
or(||):两个条件只要有一个成立,结果为true,否则为false
not(!):如果条件成立,则not后为false,否则为true
案例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 department_id<90 or department_id>110 or salary>15000;
select * from employees where not (department_id>=90 and department_id<=110) or salary>15000;
三、模糊查询
符号(较为复杂的条件运算符):
(一)like
特点:一般和通配符搭配使用
通配符:
①%:任意多个字符,包含0个字符
案例1:查询员工名中包含字符a的员工信息(字符型的值必须用''引起来)
SELECT * FROM employees WHERE last_name LIKE '%a%';
②_:任意单个字符
案例2:查询员工名中第三个字符为e,第五个字符为a的员工信息
SELECT * FROM employees WHERE last_name LIKE '__e_a%';
案例3:查询员工名中第二个字符为_的员工信息
SELECT * FROM employees WHERE last_name LIKE '_\_%';
可随意指定转义字符,但要同时使用escape
SELECT * FROM employees WHERE last_name LIKE '_$_%' ESCAPE '$';
(二)between and
①提高语句的简洁度
②包含临界值
③两个临界值不要调换顺序
案例:查询员工编号在100到120之间的员工信息
SELECT * FROM employees WHERE employee_id >= 120 AND employee_id<=100;
SELECT * FROM employees WHERE employee_id BETWEEN 120 AND 100;
(三)in
含义:判断某字段的值是否属于in列表中的某一项
特点:
①提高语句简洁度
②in列表的值类型必须一致或兼容
③in列表中不支持通配符
案例:查询工种编号是IT_PROG、AD_VP之一的员工名和工种编号
SELECT last_name,job_id FROM employees WHERE job_id = 'IT_PROT' OR job_id = 'AD_VP';
SELECT last_name,job_id FROM employees WHERE job_id IN( 'IT_PROT' ,'AD_VP');
(四)is null/is not null
=或<>不能用于判断null值,而is null或is not null可以判断null值
案例1:查询没有奖金的员工名
SELECT last_name FROM employees WHERE commission_pct IS NULL;
案例1’:查询有奖金的员工名
SELECT last_name FROM employees WHERE commission_pct IS NOT NULL;
安全等于<=>(判断是否等于null值,也可以判断是否等于普通类型的值,可读性较差)
案例1’’:查询没有奖金的员工名
SELECT last_name FROM employees WHERE commission_pct <=> NULL;
案例2:查询工资为12000的员工信息
SELECT last_name,salary FROM employees WHERE salary <=> 12000;
is null和<=>比较:
IS NULL:仅仅可以判断NULL值,可读性较高,建议使用
<=>:既可以判断NULL值,又可以判断普通的数值,可读性较低
进阶3:排序查询
语法:
select 查询列表
from 表名
where 筛选条件
order by 排序列表 【asc|desc】
特点:
asc代表升序,不写默认升序,desc代表降序
order by子句可以支持 单个字段、别名、表达式、函数、多个字段
order by子句在查询语句的最后面(除了limit子句)
案例:
1.按单个字段排序
SELECT * FROM employees ORDER BY salary DESC;
2.添加筛选条件再排序
案例:查询部门编号>=90的员工信息,并按员工编号降序
SELECT * FROM employees WHERE department_id>=90 ORDER BY employee_id DESC;
3.按表达式排序
案例:查询员工信息,按年薪降序
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪 FROM employees ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;
4.按别名排序
案例:查询员工信息,按年薪升序
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪 FROM employees ORDER BY 年薪 ASC;
5.按函数排序
案例:查询员工名,并且按名字的长度降序
SELECT last_name FROM employees ORDER BY LENGTH(last_name) DESC;
6.按多个字段排序
案例:查询员工信息,要求先按工资降序,再按employee_id升序
SELECT * FROM employees ORDER BY salary DESC,employee_id ASC;
进阶4:常见函数
概念:
类似于java的方法,将一组逻辑语句封装在方法体中,对外暴露方法名
好处:
隐藏了实现细节
提高代码的重用性
调用:
select 函数名(实参列表) 【from 表名】;
函数中参数用到表中字段需要加【from表名】,没用到就不用加
特点:
①叫什么(函数名)
②干什么(函数功能)
一、单行函数:做处理使用,传进一个值,返回一个值
(一)字符函数
1.length获取字节个数
SELECT LENGTH('张三丰hahaha');
SHOW VARIABLES LIKE '%char%'
显示字符集,utf8中1个汉字占3个字符, gdk中1个汉字占2个字符
2.concat拼接字符串
SELECT CONCAT(last_name,'_',first_name) 姓名 FROM employees;
3.upper转换成大写,lower转换成小写
示例:将姓变大写,名变小写,然后拼接
SELECT CONCAT(UPPER(last_name),LOWER(first_name)) FROM employees;
4.substr/substring截取字符(注意:索引从1开始)
案例1:截取从指定索引处后面所有字符
SELECT SUBSTR('李莫愁爱上了陆展元',7) out_put;
案例2:截取从指定索引处指定字符长度的字符
SELECT SUBSTR('李莫愁爱上了陆展元',1,3) out_put;
其中,3表示截取长度
案例3:姓名中首字符大写,其他字符小写然后用_拼接,显示出来(视频中“姓名”指“姓”)
select concat( upper(substr(concat(last_name,first_name),1,1)), '-', lower(substr(concat(last_name,first_name),2))) from employees;
5.instr返回子串第一次出现的索引(找不到返回0)
6.trim去前后空格和指定的字符
SELECT TRIM('a' FROM 'aaaaaaaaa张a翠山aaaaaaaaaaaaaaaaaa') AS out_put;
结果:张a翠山
7.ltrim去左边空格,rtrim去右边空格
8.lpad用指定的长度实现左填充,rpad用指定的长度实现右填充
SELECT LPAD('殷素素',10,'*') AS out_put;
结果的总字符个数为10
SELECT LPAD('殷素素',2,'*') AS out_put;
结果:殷素
9.replace替换(都会替换)
SELECT REPLACE('周芷若张无忌爱上了周芷若','周芷若','赵敏');
(二)数学函数
1.round 四舍五入
SELECT ROUND(1.567,2);
结果保留2位小数
2.ceil向上取整,返回大于等于该参数的最小整数
3.floor向下取整,返回小于等于该参数的最大整数
4.truncate截断
SELECT TRUNCATE(1.69999,1);
结果保留1位小数,1.6
5.mod取余(mod(a,b)=a-a/b*b,结果的正负与被除数保持一致)
6.rand 随机数,返回0到1之间的小数
(三)日期函数
1.now当前系统日期+时间
2.curdate当前系统日期(cur指current)
3.curtime当前系统时间
4.可以获取指定的部分,年、月、日、小时、分钟、秒
SELECT YEAR(NOW()) 年;
SELECT MONTH(NOW()) 月;
SELECT MONTHNAME(NOW()) 月;
以英文形式返回月
5.str_to_date 将字符通过指定的格式转换成日期
SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3 1992','%c-%d %Y');
6.date_format将日期转换成字符
SELECT DATE_FORMAT('2021/9/14','%Y年%m月%d日');
结果:2021年09月14日
7.datediff函数返回两个日期相差的天数
案例:查询员工表中的最大入职时间和最小入职时间的相差天数(difference)
SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) diffrence FROM employees;
(四)其他函数
1.version当前数据库服务器的版本
2.database当前打开的数据库
3.user当前连接用户
4.password(‘字符’)返回该字符的密码形式,即自动加密
5.md5(‘字符’)返回该字符md5加密形式
(五)流程控制函数
1.if函数:if else 的效果
SELECT IF(10<5,'大','小');
SELECT last_name,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
使用二:类似于多重if(处理条件判断)
case when 条件1 then 要显示的值1或语句1 when 条件2 then 要显示的值2或语句2 ... else 要显示的值n或语句n end
二、分组函数/统计函数/聚合函数/组函数:做统计使用,传进一组值,返回一个值
sum求和
max最大值
min最小值
avg平均值
count计数
特点:
1.sum和avg一般用于处理数值型,max、min、count可以处理任何数据类型
2.以上五个分组函数都忽略null值,null值并未参与运算,除了count(*)
3.都可以搭配distinct使用,用于统计去重后的结果(distinct 字段)
4.count的参数可以支持:
(1)字段
(2)*(结果是总行数)
(3)常量值,一般放1(假设加了一列,该列每个格子中都是1,结果是总行数)
效率:
MYISAM存储引擎下,COUNT(*)的效率高
INNODB存储引擎下,COUNT(*)和COUNT(1)的效率差不多,比COUNT(字段)要高一些,因为COUNT(字段)需要先判断该字段的值是否为null,有筛选过程。
建议使用COUNT(*)
5.和分组函数一同查询的字段要求是group by后的字段
进阶5:分组查询
语法:
select分组函数,列〈要求出现在group by的后面〉
from表
【where 筛选条件】
group by 分组的列表
【having 分组后的筛选】
【order by 子句】
执行顺序:
from->where->group by->having->select->order by
order by除limit语句之外放在最后
order by支持别名,经验证group by和having也支持别名,但少用
特点:
1.和分组函数一同查询的字段必须是group by后出现的字段
2.【面试题】
筛选分类
数据源
位置
连接的关键字
分组前筛选
原始表