一、数据库基础理论
1、数据库(DataBase DB): 是计算机中有组织、统一管理的数据的集合。
2、数据库管理系统(DBMS):是数据库管理的信息系统。
3、数据库系统(DBS):数据、用户、计算机的硬件、DBMS的总和。
DB—>DBMS—>DBS
4、实体:现实世界中的事物
5、实体集:实体的集合 。
6、属性:实体的静态特征。
7、关键字:可以标识实体的属性。
8、实体之间的联系(关系):
(1)一对一(1:1):例如 身份证 – 公民(一个身份证对应一个公民)
(2)一对多(1:n):例如 部门–部门员工(一个部门可以有多个员工)
(3)多对多(m:n):例如 学生–课程(一个学生要学习多门课程,一门课程被多个学生去学习)
9、数据模型
(1)层次模型
(2)网状模型
(3)关系模型:用二维表的形式来表示实体之间的联系
组成 | 含义 |
---|---|
属性/字段 | 表中的列 |
元组 | 表中的行 |
域 | 列的取值范围 |
主关键字(主键) | 能够唯一表示一条记录的属性或属性集 |
外部关键字(外键) | 反应表与表之间的关系(若一个表中某列是另一个表的主键,则该列就是外键) |
二、MySQL数据
1、来自于Oracle公司,是典型的关系型数据库、免费的、开源的。
2、安装MySQL
3、配置MySQL的环境变量
(1)步骤1:
将C:\Program Files\MySQL\MySQL Server 5.5\bin配置到系统环境变量的path中。
(2)步骤2:
打开cmd窗口
输入以下指令 mysql -u root -p
根据提示输入密码
-u 指令参数,它后面的root是用户名(root是超级用户)
-p指令参数,表示输入密码
4、常用指令
(1)显示所有数据库
show databases; //指令必须以分号结束
(2)切换数据库
use 数据库名;
(3)创建数据库
create database 数据库名; //数据库名不能带中文字符
(4)显示当前数据库中的所有表
show tables;
(5)删除数据库
drop database 数据库名;
(6)查看表结构
desc 表名;
(7)查看当前使用的数据库
select databases();
(8)执行sql脚本文件(.sql)
source 脚本文件名;
三、SQL(Structured Query Language):结构化查询语言
SQL(Structured Query Language):结构化查询语言,是关系型数据库的操作语言
1、DDL(Data Definition Language)
DDL(Data Definition Language):数据定义语言。用于创建数据库对象(库、表、列)。
(1)创建数据库:create database 数据库名;
(2)创建数据表:show databases;
(3)删除数据库:drop database 数据库名;
(4)SQL数据类型:
数据类型 | 说明 |
---|---|
int | 整型 |
double | 浮点型。double(5,2) |
decimal | 数值型(泛型型)。decimal(M,D),M范围1到65,D范围0到30;eg:decimal(5,2),范围为-999.99到999.99 |
varchar | 固定长度字符串类型(当输入的字符不够长度时不会补空格)。eg:sname varchar(10) sname=‘a1’ |
char | 固定长度字符串类型(当输入的字符长度不够时会补空格)。eg:sname char(10) sname='a1 ’ |
(5)操作表格:
A、创建表;
create table 表名(
列名 数据类型,
列名 数据类型,
列名 数据类型,
…
);
B、删除表格;
drop table 表名;
C、给表格增加列;
alter table 表名 add (列名 数据类型);
D、修改列的数据类型;
ALTER TABLE 表名 MODIFY 列名 数据类型;
E、修改列的名字;
ALTER TABLE 表名 change 原列名 新列名 数据类型;
F、删除列;
ALTER TABLE 表名 DROP 列名;
G、修改表名;
ALTER TABLE 原表名 RENAME TO 新表名;
2、DML(Data Manipulation Language)
DML(Data Manipulation Language):数据操作语言,用来定义数据库记录(数据)
(1)插入记录
A、给指定的列插入数据:
insert into 表名(列名1,列名2,…)values(值1,值2,…);
B、给所有插入数据(顺序按定义表时列的顺序插入)
insert into 表名 values(值1,值2,…);
(2)修改记录
update 表名 set 列名1=值1,列名2=值2,… [where 条件];
(3)删除记录
delete from 表名 [where 条件];
注意:若没有where条件子句,则删除表中的所有记录,保留表结构
3、DCL(Data Control Language)
DCL(Data Control Language):数据库控制语言,创建数据库的用户、设计权限、安全级别。
4、DQL(Data Query Language)
DQL(Data Query Language):数据库查询语言,用于查询记录。
CREATE TABLE stu(
sid CHAR(6),
sname VARCHAR(50),
age INT,
gender VARCHAR(10)
);
INSERT INTO stu VALUES('S_1001', 'liuYi', 35, 'male');
INSERT INTO stu VALUES('S_1002', 'chenEr', 15, 'female');
INSERT INTO stu VALUES('S_1003', 'zhangSan', 95, 'male');
INSERT INTO stu VALUES('S_1004', 'liSi', 65, 'female');
INSERT INTO stu VALUES('S_1005', 'wangWu', 55, 'male');
INSERT INTO stu VALUES('S_1006', 'zhaoLiu', 75, 'female');
INSERT INTO stu VALUES('S_1007', 'sunQi', 25, 'male');
INSERT INTO stu VALUES('S_1008', 'zhouBa', 45, 'female');
INSERT INTO stu VALUES('S_1009', 'wuJiu', 85, 'male');
INSERT INTO stu VALUES('S_1010', 'zhengShi', 5, 'female');
INSERT INTO stu VALUES('S_1011', 'xxx', NULL, NULL);
(1)查询所有列:使用通配符
select * from 表名;//’*‘代表表的所有列
SELECT * FROM xy.stu;
(2)查询指定的列
select sid from stu;
(3)条件查询
①= 、 >、 <、 < >、 >=、 <=
-- 查询年龄为女,并且年龄小于50的记录
select * from stu
where gender='female' and age<50;
-- 查询性别非男的学生记录
select * from stu
where gender <>'male';
②AND(逻辑与) OR(逻辑或) NOT(逻辑非)
-- 查询学号为s_1001,或者姓名为lisi的记录
select * from stu
where sid='s_1001' or sname='liSi';
-- 查询年龄在20到40之间的学生记录
select * from stu
where age>=20 and age<=40;
确定范围:between …and …
select * from stu
where age between 20 and 40;
③where 字段名称 in(值一,值二)
-- 查询学号为S_1001,s_1002,s_1003的记录
select * from stu
where sid in('S_1001','S_1002','S_1003');
④where 字段名称 not in(值一,值二)
-- 查询学号不为S_1001,S_1002,S_1003的记录
select * from stu
where sid not in('S_1001','S_1002','S_1003');
⑤where 字段名称 Is Null / Where 字段名称 Is Not Null
-- 查询年龄为null的记录
select * from stu
where age is NULL;
-- 查询姓名不为null的学生记录
select * from stu
where sname is not null;
(4)模糊查询
它判断列值是否与指定的字符串格式相匹配。可用于char、varchar、text、ntext、datetime和smalldatetime等类型查询。
一般模糊语句如下:
SELECT 字段 FROM 表 WHERE 某字段 Like 条件
其中关于条件,SQL提供了两种匹配模式:
①%:表示任意0个或多个字符。可匹配任意类型和长度的字符,有些情况下若是中文,请使用两个百分号(%%)表示。
☀️举个例子:
创表:
create table user(
u_name varchar(20)
);
insert into user(u_name) values('张三');
insert into user(u_name) values('张猫三');
insert into user(u_name) values('三脚猫');
insert into user(u_name) values('唐三藏');
insert into user(u_name) values('三毛');
insert into user(u_name) values('诸葛亮');
insert into user(u_name) values('刘备');
select * from user where u_name like '%三%';
将会把u_name为“张三”,“张猫三”、“三脚猫”,“唐三藏”等等有“三”的记录全找出来。
另外,如果需要找出u_name中既有“三”又有“猫”的记录,请使用and条件。
SELECT * FROM user WHERE u_name LIKE '%三%' AND u_name LIKE '%猫%';
注意:若使用 SELECT * FROM [user] WHERE u_name LIKE ‘%三%猫%’
虽然能搜索出“三脚猫”,但不能搜索出符合条件的“张猫三”。
②_: 表示任意单个字符。匹配单个任意字符,它常用来限制表达式的字符长度语句:
☀️举个例子:
select * from user where u_name like '_三_';
只找出“唐三藏”这样u_name为三个字且中间一个字是“三”的;
select * from user where u_name like '三_';
注意:只有一个下划线,所以只差找出了‘三毛’,而不能查找出三脚猫。
(5)字段控制查询
CREATE TABLE emp(
empno INT,
ename VARCHAR(50),
job VARCHAR(50),
mgr INT,
hiredate DATE,
sal DECIMAL(7,2),
comm decimal(7,2),
deptno INT
) ;
INSERT INTO emp values(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp values(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp values(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp values(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp values(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp values(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp values(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO emp values(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp values(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp values(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO emp values(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp values(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp values(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);
emp表:
a、去掉重复记录。
SELECT DISTINCT 列名 FROM 表名;
-- 去掉emp表中sal列重复记录
SELECT DISTINCT sal FROM emp;
b、在查询中进行字段计算。
-- 计算每个员工的薪水和奖金之和
select *,sal+comm from emp;
-- 计算每个员工的薪水和奖金之和
select *,sal+comm as '工资总和' from emp;
c、给字段起别名
select empno as '编号',ename as '姓名', sal as '薪水',comm as '奖金' from emp;
(6)查询结果排序:order by子句的使用
stu学生表:
asc 表示升序(默认)
desc 表示降序
-- 查询所有学生记录,按年龄升序排序
select * from stu
order by age desc;
-- 查询所有雇员,按月薪降序排序,如果月薪相同时,按编号升序排序
select * from emp
order by sal desc,empno asc;
(7)聚合函数:SQL已经定义好的函数,在查询语句中可以直接使用
a、count(指定列):统计指定列不为null的行数。
☀️举个例子:
-- 统计emp表中的记录数
select count(*) as '总记录数' from emp;
-- 查询emp表中有奖金的人数
select count(comm) as '奖金人数' from emp; -- 列值null和列值0是不同的,0也包含在内
-- 查询emp表中月薪大于2500的人数(查询中带有where子句)
select count(*) as '大于2500的人数' from emp where sal>2500;
注意:在SQL中任何数和NULL相加结果仍然为NULL。若需要将数值型数据和NULL相加,则需要先将NULL转换为0,然后相加,转换函数是IFNULL(列名,0)
☀️举个例子:
-- 统计月薪与佣金之和大于2500元的人数
select count(*) as '月薪加佣金大于2500' from emp where sal+ifnull(comm,0)>2500;
-- 查询有佣金的人数,以及有领导的人数
select count(*),count(mgr) from emp;
b、sum(指定列名):求和函数
☀️举个例子:
-- 查询所有雇员月薪和
select sum(sal) as '总工资' from emp;
-- 查询所有雇员月薪和,以及所有雇员的佣金和
select sum(sal) as '总工资',sum(comm) as '佣金和' from emp;
-- 查询所有雇员月薪+佣金和
select sum(sal+ifnull(comm,0)) from emp;
c、avg(指定列名):求平均值
☀️举个例子:
-- 统计所有员工平均工资
select count(*) as '员工总数',avg(sal) as '平均工资' from emp;
d、max(列名):求最大值
e、min(列名):求最小值
☀️举个例子:
-- 查询最高工资和最低工资
select max(sal) as '最高工资',min(sal) as '最低工资' from emp;
(8)分组查询:将查询结果进行分组,使用group by子句
☀️举个例子:
-- 查询每个部门的部门编号和每个部门的工资和
select deptno,sum(sal) as 工资和 from emp group by deptno;
-- 查询每个部门的部门编号以及每个部门的人数
select deptno,count(*) from emp group by deptno;
-- 查询每个部门的部门编号以及每个部门工资大于1500的人数
select deptno,count(*) from emp where sal>1500 group by deptno;
HAVING子句:必须和group by子句结合使用,作用是对分组的结果再进行筛选
☀️举个例子:
-- 查询工资总和大于9000的部门编号以及工资和
select deptno,sum(sal)
from emp
group by deptno
having sum(sal)>9000;
(9)分页查询(MySQL)
LIMIT:用来限定查询结果的起始行,以及总行数
limit 起始行索引,连续查询的总行数
例如:limit 0,5 表示从第一行(索引为0)开始,查询5行
☀️举个例子:
-- 分页查询:每页显示5条记录
select * from emp limit 0,5; -- 第一页
select * from emp limit 5,5; -- 第二页
select * from emp limit 10,5; -- 第三页
第一页:
第二页:
第三页:
(10)多表连接查询:在一个查询语句中显示多张表的数据
表与表之间的关系
A、并集:union
-- 多表连接查询:并集(字段数目和字段类型必须相同)
select * from cstudent
union
select * from mstudent;
cstudent表:
mstudent表:
并集:
B、笛卡尔积:是指在数学中,两个集合X和Y的笛卡尔积(Cartesian product),又称直积,表示为X×Y,第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员 。
假设集合A={a, b},集合B={0, 1, 2},则笛卡尔积A×B为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。
-- 多表连接查询:笛卡尔积(第一张表的记录对应第二张表的每一条记录)
select * from cstudent,mstudent;
select * from mstudent,cstudent;
C、连接查询;join,本质是对笛卡尔积进行筛选
前提:建cstudent表,interest表
cstudent表:
interest表:
a、内连接(INNER JOIN):保留关系中所有匹配的数据记录,舍弃不匹配的记录,也称为等值连接。返回的两张表都满足条件的部分(where条件)
☀️例:
-- 连接查询,在cstudent表和interest表上通过id字段建立内连接(等值连接)
select a.id,a.name,a.sex,b.dance,b.base,b.foot
from cstudent as a -- 给cstudent表取别名
inner join interest as b -- 内连接到interest表,并且给该取别名叫b
on a.id=b.id ;-- 等值连接
b、外连接(OUTER JOIN):
①左外连接(左连接):left outer join …on…可以简写为left join …on…
-- 在cstudent表和 interest表上通过id字段建立左连接(保留左表中的所有记录,右表中没有匹配的记录显示null)
select a.id,a.name,a.sex,b.dance,b.base,b.foot
from cstudent as a
left join interest as b
on a.id=b.id;
②右外连接(右连接):right outer join …on…可以简写为right join …on…
-- 在cstudent表和 interest表上通过id字段建立右连接(保留右表中的所有记录,左表中没有匹配的记录显示null)
select a.id,a.name,a.sex,b.dance,b.base,b.foot
from cstudent as a
right join interest as b
on a.id=b.id;
③全连接:full join 在表的笛卡尔积中,除了选择相匹配的数据记录,还会包含左右两边表中不匹配的所有数据记录。重点注意:MySQL不支持full join。
-- 在cstudent表和 interest表上通过id字段建立全连接
select a.id,a.name,a.sex,b.dance,b.base,b.foot
from cstudent as a
full outer join interest as b
on a.id=b.id;
MySQL实现全连接的方法是将左连接和右连接用UNION来实现全连接。
select a.id,a.name,a.sex,b.dance,b.base,b.foot
from cstudent as a
left join interest as b
on a.id=b.id
union
select a.id,a.name,a.sex,b.dance,b.base,b.foot
from cstudent as a
right join interest as b
on a.id=b.id;
④ 交叉连接(CROSS JOIN):
-- 交叉连接
select a.id,a.name,a.sex,b.dance,b.base,b.foot
from cstudent as a
cross join interest as b
on a.id=b.id;
(11) 子查询
① 多表连接查询的缺点:先进行两个表的笛卡尔积,然后在查询符合条件的记录。性能很差。通常用子查询代替多表连接查询。
②什么是子查询:在一个select语句的from子句或者where子句包含另一个select语句。通常把外层的select语句称为主查询,把内层的select语句称为子查询。
❤️where子句的子查询:即在where子句嵌套select语句。将子查询返回的值作为主查询的查询条件。
前提:先创建两张表:
dept表:
emp表:
☀️举个例子:
-- 查询部门编号小于‘设计部’的所有员工信息:where子句的子查询,子查询的返回值是主查询的条件。
select * from emp
where deptno < (
select deptno from dept where dname='设计部' -- 子查询:查询设计部的部门编号
);
☀️举个例子:
-- 查询部门编号小于’设计部‘并且薪水大于1500的员工信息。
select * from emp
where deptno<(
select deptno from dept where dname = '设计部'
)and sal>1500;
☀️举个例子:
-- 同表中的子查询
select * from emp
where (job,sal)=(
select job,sal
from emp
where ename = 'ward'
);
dept表改变(部门编号没有20)
☀️举个例子:
-- 在子查询中使用in、not in
-- (1)查询部门编号在部门表中存在的员工信息,部门表里没有部门编号20.
select * from emp
where deptno in(
select deptno from dept
);
select * from emp
where deptno not in(
select deptno from dept
);
❤️from子句的子查询:内嵌的select语句返回的是一个临时表,然后再从这个临时表中查询符合条件的记录。该方式用于实现多表连接查询.
☀️举个例子:
-- from子句中的子查询:查询员工所在部门编号、部门名称、员工人数、平均工资
select d.deptno,d.dname,counts,avge
from dept as d inner join(
select deptno,count(empno) as counts,avg(sal) as avge
from emp
group by deptno
) as e
on d.deptno=e.deptno