一、数据库相关概述
1、数据库的好处
- 1、可以持久化数据到本地(磁盘)
- 2、结构化查询
2、数据库的常见概念
- 1、DB:数据库(DataBase),存储数据的容器
- 2、DBMS:数据库管理系统(DataBase Menage System),又称为数据库软件或数据库产品,用于创建或管理DB
- 3、SQL:结构化查询语言,用于和数据库通信的语言,不是某个数据库软件特有的,而是几乎所有的主流数据库软件通用的语言
3、三、数据库存储数据的特点
- 1、数据存放到表中,然后表再放到库中
- 2、一个库中可以有多张表,每张表具有唯一的表名用来标识自己
- 3、表中有一个或多个列,列又称为“字段”,相当于java中“属性”
- 4、表中的每一行数据,相当于java中“对象”
4、常见的数据库管理系统
mysql、oracle、db2、sqlserver
二、mysql数据库
1、概述
- MySQL 是一个关系型数据库管理系统,由瑞典 MySQL AB 公司开发,目前属于 Oracle 公司。MySQL 是一种关联数据库管理系统,关联数据库将数据保存在不同的表中,而不是将所有数据放在一个大仓库内,这样就增加了速度并提高了灵活性。
2、特点
- 开源,免费
- 数据查询引擎效率非常高
- mysql针对分布式、微服务支持非常友好
- mysql支持标准sql
- 支持千万级数据存储及查询
3、mysql数据库的环境变量配置
第一步,安装mysql数据库
第二步,环境变量配置
变量值即你的安装目录地址
然后在path里新建写入%MYSQL_HOME%\bin
第三步,安装mysql服务:cmd里运行输入mysqld -install
第四步:启动服务:net start mysql
第五步:以超级管理员身份登录mysql -u root
4、如何删除mysql数据库
1、先将mysql服务删除(步骤不全):
net stop mysql
mysqld -remove
三、SQL语句
(一)DML语句
- DML语句包括增添、删除、修改语句
1、增添语句
#一、添加数据语句,添加一行数据:insert into 表名 values (数据1,数据2.....括号里的数据要符合表的结构)
insert into petstore values(4,'张七',null,60000);
insert into petstore values(5,'李八','888888',70000);
insert into petstore values(6,'王九','999999',null);
insert into petstore values(7,null,'101010',90000);
#添加数据语句,为某字段(列)添加数据:insert into 表名(列名1,列名2.....) values (数据1,数据2.....)这两个括号里的数据要互相对应
insert into petstore(tid) values(8);
insert into petstore(tid,aname) values(9,'李白');
(二)DQL语句
1、基础查询
1、语法
select 查询列表 from 表名;
2、特点
(1、查询列表可以是字段、常量、表达式、函数,也可以是多个
(2、查询结果是一个虚拟表
DQL语言:Data Query Language(数据查询语言)
#查询所有
select * from petstore;
#查询某字段
select aname,balance from petstore;
#加条件查询
select * from petstore where balance<10000;
select aname,balance from petstore where balance<10000;
#两列之间运算
select id+age from petstore;
需要注意的几个细节问题:
(1、正常查询或者其他操作时,一般都需要打开指定的库, 用use 库名;
(2、用``(ecs的下面,着重号),区分数据库关键字和字段,比如数据库的一个关键字NAME,我想插入字段name,就要用 name
来区分关键字和字段, 查询叫john的名:select ‘john’(单引号)
3、示例
(1、查询单个字段
select 字段名 from 表名;
(2、查询多个字段
select 字段名,字段名… from 表名;
(3、查询所有字段
select * from 表名
(4、查询常量
select 常量值;
注意:字符型和日期型的常量值必须用单引号引起来,数值型不需要
(5、查询函数
select 函数名(0~n个实参列表);
(6、查询表达式
select 100/1234;
(7、起别名
①as,如select 10099 as “结果”(别名);
②空格,如select 10099 “结果”(别名);
好处:1、便于理解 2、如果要查询的字段有重名的情况,使用别名可以区分开来
(8、去重:查询到的字段有重复时,只想看到一个重复的字段,用distinct
select distinct 字段名 from 表名;
select distinct department_id from employees;//在employees表中值显示一个department_id列中重复的字段
(9、+
作用:做加法运算
select 数值+数值; 直接运算
select 字符+数值;先试图将字符转换成数值,如果转换成功,则继续运算;否则转换成0,再做运算
select null+值;+的两边只要一方为null结果都为null
(10、【补充】concat函数
案例,查询员工名和姓并连接成一个字段,并显示为姓名。
select concat(last_name,first_name)as "姓名" from imployees
功能:拼接字符
select concat(字符1,字符2,字符3,...);
(11、【补充】ifnull函数
功能:判断某字段或表达式是否为null,如果为null 返回指定的值,否则返回原本的值
select ifnull(commission_pct,0) from employees;
ifnull(commission_pct,0)判断commission_pct是否为null,是就返回0,不是就返回原本的值。
(12、【补充】isnull函数
功能:判断某字段或表达式是否为null,如果是,则返回1,否则返回0
(13、【案例】
(1)select last_name,job_id,salary as sal from employees //注意这个,last_name一列,job_id一列,salary起别名作为一列,as只对salary作用
(2)显示表departments的结构
desc departments;
查询表中的全部数据 select * from departments;
2、条件查询
1、语法
select 查询列表 from 表名 where 筛选条件; where一定要放在from后面
【案例】查询工资大于12000的员工信息
select * from employees where salary>12000;
2、筛选条件的分类
(1、简单条件运算符
> < = <> != >=
【案例】查询部门编号不等于90号的员工和部门编号
select last_name,department_id from employees where department_id<>90; 推荐的不等号用<> <=>安全等于
查询什么就select什么
(2、逻辑运算符
作用:用于连接条件表达式
&& and
|| or
! not
【案例】查询工资在10000到20000之间的员工名、工资以及奖金
select last_name,salary,commission_pct from employees where salary>=10000 and salar<=20000;
(3、模糊查询
like:一般搭配通配符使用,可以判断字符型或数值型
通配符:%代表任意多个字符,包含0个字符。
如’%a’表示最后一个为a,前面有0到n个字符。’%a%‘表示含有a的都行,a前有0到n个字符,a后有0到n个字符。
_代表任意单个字符。如’_b%‘表示第二个字符为b的字段,两个下划线’__b%'表示第三个字符为b的字段
between and
in
is null /is not null:用于判断null值
where job_id is null;
【案例】(1)查询所有员工名中包含字符a的员工信息
SELECT * FROM employees WHERE
last_name LIKE ‘%a%’;
【案例】(2)查询员工名中第三个字符为e,第五个字符为a的员工名和工资
select last_name,salary from employees where last_name like ‘__e_a%’;
【案例】(3)查询员工名中第二个字符为_的员工名
SELECT * FROM employees WHERE
last_name LIKE ‘__%’;
用到了转义字符,将关键字_转成了字符
也可以自己定义转义字符(推荐),如
SELECT * FROM employees WHERE
last_name LIKE ‘a%’ escape ‘a’; 此时a就成了转义符号
【案例】(4)查询员工编号在100–120之间的员工信息。
select * from employees where employees_id>=100 and employees_id<=120;
或者,由于只是employees_id,可用between
select * from employees where between 100 and 120;
不在100-120的话,可以not between 100 and 120
【案例】(5)查询员工的工种编号是IT_PROG、AD_VP、AD_PRES中的一个员工和工种编号
select
last_name,job_id
from employees
where job_id=‘IT_PROG’ or job_id=‘AD_VP’ or job_id=‘AD_PRES’;
当查询的比较多的时候用in更方便,如:where job_id in (‘IT_PROG’,‘AD_VP’,‘AD_PRES’);这三个都是job_id的
- is null PK <=>
普通类型的数值 null值 可读性
is null × √ √
<=> √ √ ×
3、排序查询
1、语法
select 查询列表
from 表
[where 筛选条件]
order by 排序列表 asc升序或者desc降序(不写默认升序)
2、特点
(1、asc :升序,如果不写默认升序
desc:降序
(2、排序列表 支持 单个字段、多个字段、函数、表达式、别名
(3、order by的位置一般放在查询语句的最后(除limit语句之外)
4、常见函数
一、概述
功能:类似于java中的方法
好处:提高重用性和隐藏实现细节
调用:select 函数名(实参列表);
函数都是函数名(),写的时候别忘记括号
二、单行函数
-
1、字符函数(下面的函数都没写括号,实际要写,函数都是带括号在后面的)
concat:拼接字符串
substr:截取子串;substr(‘我爱学习学习爱我’,3);将会选取第三个字符到最后的字符 习学习爱我 ,索引是从1开始的
substr(‘我爱学习学习爱我’,3,5)(推荐) 选取第3到第5个的字符,这个可以对指定的字符操作
upper:变大写 super(last_name)将会把last_name中的所有内容变成大写,注意不是将last_name这几个字变为大写。
lower:变小写
replace:替换
length:获取字节长度 length(‘abcd’);输出为4
trim:去前后空格
lpad:左填充
rpad:右填充
instr:获取子串第一次出现的索引,如果找不到返回0,instr(‘我爱学习学习爱我’,‘爱我’)。得到的是7,爱我 在 我爱学习学习爱我 中在第7个开始 -
2、数学函数(下面的函数都没写括号,实际要写)
ceil:向上取整
round:四舍五入
mod:取模
floor:向下取整
truncate:截断
rand:获取随机数,返回0-1之间的小数 -
3、日期函数(下面的函数都没写括号,实际要写)
now:返回当前日期+时间
curdate:返回当前日期,不包含时间
year:返回年
month:返回月
day:返回日
date_format:将日期转换成字符
curdate:返回当前日期
str_to_date:将字符转换成日期
curtime:返回当前时间
hour:小时
minute:分钟
second:秒
datediff:返回两个日期相差的天数
monthname:以英文形式返回月
-
4、其他函数(下面的函数都没写括号,实际要写)
version 当前数据库服务器的版本
database 当前打开的数据库
user当前用户
password(‘字符’):返回该字符的密码形式
md5(‘字符’):返回该字符的md5加密形式 -
5、流程控制函数
①if(条件表达式,表达式1,表达式2):如果条件表达式成立,返回表达式1,否则不成立返回表达式2
②case情况1(要判断的字段或表达式)
case 变量或表达式或字段
when 常量1 then 值1
when 常量2 then 值2
…
else 值n
end
③case情况2
case
when 条件1 then 值1
when 条件2 then 值2
…
else 值n
end
三、分组函数(做统计使用,又称为统计函数,聚合函数)
- 1、分类
max 最大值
min 最小值
sum 和 select sum(salary) from employees
avg 平均值
count 计算个数
rount 保留几位小数 select rount(avg(salary),3); 保留三位小数 - 2、特点
①语法
select max(字段) from 表名;
②支持的类型
sum和avg一般用于处理数值型
max、min、count可以处理任何数据类型
③以上分组函数都忽略null
④都可以搭配distinct使用,实现去重的统计
select sum(distinct 字段) from 表;
⑤count函数
count(字段):统计该字段非空值的个数
count(*):统计结果集的行数
案例:查询每个部门的员工个数
1 xx 10
2 dd 20
3 mm 20
4 aa 40
5 hh 40
count(1):统计结果集的行数
效率上:
MyISAM存储引擎,count()最高
InnoDB存储引擎,count()和count(1)效率>count(字段)
⑥ 和分组函数一同查询的字段,要求是group by后出现的字段
关于外键约束的理解
题目:
student表:
定义Sid为主键约束
定义STid为外键约束,指向教师表的主键
teacher表:
定义Tid为主键约束
Tname为唯一约束
sc表:
定义Sid为主键约束
定义stu_id为外键约束,指向学生的主键
#student是从表,teacher是主表,约束语句写在从表里
#stid作为外键约束,就说明它是teacher的从表
#需要先建主表才能运行从表
CREATE TABLE IF NOT EXISTS Teacher(
tid VARCHAR(10) PRIMARY key,
tname VARCHAR(10) UNIQUE
);
CREATE TABLE IF NOT EXISTS Student(
sid VARCHAR(6) PRIMARY KEY,
sname VARCHAR(10),
sage DATETIME,
ssex VARCHAR(10),
stid VARCHAR(10),
CONSTRAINT fk_teacher_tid FOREIGN key(stid) REFERENCES Teacher(tid)
#约束名为fk_teacher_tid,以本表的stid为外键联系到teacher表的tid字段
#(这个约束名表明外键,连到teacher表的tid,可任定义约束名)
);
CREATE TABLE IF NOT EXISTS SC(
sid VARCHAR(10) PRIMARY KEY,
cid VARCHAR(10),
score DECIMAL(18,1),
stu_id VARCHAR(6),
CONSTRAINT fk_Student_sid FOREIGN KEY(stu_id) REFERENCES Student(sid)
#约束名为fk_Student_sid,以stu_id作为外键,联系到student表的sid
#这个约束名表明本表的外键连到student表的sid字段
#stu_id作为外键约束,就说明它会是另一个表的从表,指向学生的主键说明是student的从表
);