1.数据库简介
1.1简介
数据库(DataBase,DB):指长期保存在计算机的存储设备上,按照一定规则组织起来,可以被各种用户或应用共享的数据集合。简单理解位数据的仓库。(通过数据库管理系统创建和操作的)
数据库管理系统(DataBase Management System,DBMS):指一种操作和管理数据库的大型软件,用于建立使用和维护数据库,对数据库进行统一管理和控制,以保证数据库的安全性和完整性。用户通过数据库管理系统访问数据库中的数据。(管理数据库的)
1.2常见的数据库管理系统
关系型数据库管理系统(SQL)
-
Oracle:可以运行在UNIX、Windows等主流操作系统平台,完全支持所有的工业标准,并获得最高级别的ISO标准安全性认证。
-
MySQL:也属于Oracle旗下产品。是最流行的关系型数据库管理系统之一,在WEB应用方面,MySQL是最好的RDBMS(关系数据库管理系统)应用软件。
-
DB2:是IBM公司的产品,DB2数据库系统采用多进程多线索体系结构。
-
Microsoft SQL Server:SQL Server是Microsoft公司推出的关系型数据库管理系统
非关系型数据库(NOSQL)
-
Redis:键值(key-value)存储数据库
-
HBase:列存储数据库
-
MongoDb:文档型数据库
-
Graph:图形数据库
2.MySQL在dos命令下的一些常用操作
可以先将Mysql的bin目录路径添加到系统环境变量path中,之后直接进入cmd就可以进行操作。
启动、关闭mysql,需要管理员身份打开cmd
- 关闭mysql: net stop mysql
- 启动mysql: net start mysql
- mysql的dos客户端连接:mysql -uroot -p
3.SQL语句分类
- DDL(Data Definition Language):数据定义语言,用来定义数据库对象:库、表、字段
- DML(Data Manipulation Language):数据操作语言,用来定义数据库记录(数据)
- DCL(Data Control Language):数据控制语言,用来定义访问权限和安全级别
- DQL(Data Query Language):数据查询语言,用来查询记录(数据)
3.1.数据库的DDL语句:对数据库、数据表、数据字段进行操作
注意:数据库名、表名、字段名可以使用反勾号` 括住,也可以不括。如果SQL关键字一般要括住
数据库模式定义语言DDL,是用于描述数据库中要存储的现实世界实体的语言
create : 创建
drop : 删除
alter:修改
show:显示
操作数据库
创建数据库:库名 test
create database test;
create database `create`;
create datebase `order`;
create database test charset utf8;//指定编码创建数据库
修改数据库:
alter database test character set gbk
查询数据库信息:
show databases;//展示所有数据库
select database();//查看当前使用的数据库,如果是null表示没有指定数据库
use 数据库名;//指定使用这个数据库
删除数据库
drop database test;
操作数据表
-
创建数据表(注意mysql中不区分大小写)
create table student(id int,name char(8),phone varchar(11),score float)
因为id和ID指的是同一个,所有报错,mysql不区分大小写
-
int:整形(4个字节)
-
double:浮点型(8个字节),近似值。
如:double(5,2)表示最多5位,其中两位是小数,取值范围-999.99~999.99
-
decimal:精确数值数据,最大位数可以是65。
-
char:固定长度字符串类型;char(10)代表固定10个字符,不足的补空格
优点:查询速度块; 缺点:浪费空间。
-
varchar:可变长度的字符串类型;varchar(10)最多存储10个字符。
优点:节省空间 缺点:查询速度慢
-
text:大文本字符串类型;有字符编码,存储比较大的文本数据。
-
blob:Binary Large Object二进制大对象数据;可以存储图片、音频、视频
-
date:日期类型,格式为 yyyy-MM-dd
-
time:时间类型,格式为hh:mm:ss
-
timestamp:时间戳类型 yyyy-MM-dd hh:mm:ss 会自动赋值
-
datetime:日期时间类型 yyyy-MM-dd hh:mm:ss
-
修改数据表
alter table 旧表名 rename 新表名;//修改表的名称 alter table student charset utf8;//修改表的字符编码
-
修改表字段
alter table 表名 add column 字段名 类型; //添加新字段 alter table 表名 drop column 字段名; //删除字段 alter table 表名 change 字段原名 字段新名 字段类型 ;//修改字段
- 查询数据表
show tables;//展示数据库里面所有的数据表
desc 表名;//查看指定数据表的信息
- 删除数据表
drop table 表名
4.数据库的DML语句:对数据库中的数据进行插入、删除、修改操作(重点)
注意:在mysql中,字符串类型和日期类型都要用单引号括起来。空值:null
数据操纵语言DML(Data Manipu;ation Language),用户通过它可以实现对数据库的基本操作。
crud : Create、read、update、delete
4.1. insert
insert into 表 values(值1,值2,值n);
insert into 表(字段1,字段2,字段n) values(值1,值2,值n);
两种语法的区别:
基本语法1的插入语句,表中有多少个字段就必须要插入多少个值。一个不能多,一个也不能少。若有默认值,不想传,可以写上null。
基本语法2中,除非有必填字段必须要写入值外。如果有默认值的不想写可以忽略不写。mysql会自动补主默认值
#往表中插入数据
#insert into 表明(字段名) values(值)
#指定列名插入,列名要与values里面的值一一对应.sql语句里面,字符串可以使用'' 也可以使用""
INSERT INTO student(NAME,age,address,createDate) VALUES("赵云",33,"广东",'2020-2-2 2-2-2')
INSERT INTO student(NAME,age,address) VALUES("刘备",23,"福建")
INSERT INTO student(NAME,age,address,createDate) VALUES("张飞",32,'广西','2020-3-5 3:3:2')
INSERT INTO student(NAME,age,address) VALUES("关羽",44,"湖南")
#不写列名的情况下,必须要写所有的列.因为id设置了自增,所以当它为null的时候,也相当于自动增长
INSERT INTO student VALUES(NULL,"诸葛亮",44,"北京",'2018-3-4 23-44-22')
#createDate字段的类型为timestamp会以时间戳自动赋值
INSERT INTO student VALUES(9,"曹操",55,"河北",NULL)
#NOW()函数代表当前时间
INSERT INTO student VALUES(NULL,"孙权",33,"江东",NOW())
#插入多条记录
INSERT INTO student(NAME,age,address) VALUES("詹姆斯",33,'湖人'),("杜兰特",33,'篮网'),('威少',33,'火箭')
INSERT INTO student VALUES(NULL,"哈登",33,'火箭',NOW()),(NULL,"保罗",33,'雷霆',NOW()),(NULL,"库里",33,'勇士',NULL)
4.2 update 更改
update 表名 set 字段1=值1,字段2=值2,字段3=值3,字段n=值n [where 条件]
#更改表中数据 update 表名 set 字段1=值1,字段2=值2,字段n=值n [where 条件]
#将表中所有数据的字段age值改为33,createDate改为当前时间
UPDATE student SET age=33,createDate=NOW()
#根据where条件更改指定字段的值
UPDATE student SET age=18 WHERE NAME='张飞'
UPDATE student SET age=20,createDate='2018-3-4 4-3-3' WHERE id=9
#将所有数据的age字段值都+10
UPDATE student SET age=age+10
4.3.delete 删除
delete from 表名 [where 列名=值]
#删除表中数据
#根据where条件删除指定数据
DELETE FROM student WHERE id=16
DELETE FROM student WHERE NAME='曹操'
#直接删除全部数据.表结构还在,数据通过日志还可以找回
DELETE FROM student
#truncate先把表drop删除掉,在创建一个名字相同的空表.原表结构不存在,数据无法找回.效率比delete高
TRUNCATE TABLE student
5.关系型数据库
5.1简单说就是表与表之间可以建立关系.建立关系的基础就是主外键.
主键:是唯一标识一条记录,不能有重复的,不能为空的,是用来保证数据的完整性。
-
int类型,自增
-
varchar类型,UUID
-
自定义(当前时间+时间戳+机器码+int自增序列),适用于分布式系统
外键:是另外一张表的主键,外键可以重复,可以是空值,用来和其他表建立联系。所以外键一定是涉及到至少两张表。
外键的使用需要满足下列的条件:
-
两张表必须都是InnoDB表,并且它们没有临时表
InnoDB是数据库的引擎。mysql常见的引擎有两种:InnoDB和MyISAM,其中MyISAM不支持外键
建立外键关系的对应列必须具有相似的InnoDB内部数据类型
建立外键关系的对应列必须建立了索引。
假如显式的给出了CONSTRAINT symbol,那symbol在数据库中必须是唯一的。假如没有显式的给出,InnoDB会自动的创建。
5.2表与表之间的关系
- 一对一(一个人对应一张身份证)
- A表的主键是B表的外键
- A表的某个字段,是B表的外键,且设置了唯一索引
- 一对多(一个商品类别对应多个商品)
- A表的某个字段,是B表的外键
- 多对多(老师和班级)
- 一般通过第三方表来描述两个表之间的多对多关系。(新建一张中间表,每一列中存储了另外两张表的外键)
5.3数据库三范式
第一范式(1NF)
1NF的定义为:符合1NF的关系中的每个属性都不可再分
1NF是所有关系型数据库的最基本要求,你在关系型数据库管理系统(RDBMS),例如SQL Server,Oracle,MySQL中创建数据表的时候,如果数据表的设计不符合这个最基本的要求,那么操作一定是不能成功的。
学号 班级 姓名 系名 系主任 课程 语文 高数 英语
只满足1NF会有问题:数据冗余过大,插入异常,删除异常,修改异常;
冗余: 相同数据重复出现
插入问题: 3月份新开一个系,但是没有学生和班级; 如何插入数据?
删除问题: 某个班级不存在了,学生和系信息怎么办?
修改问题: 如果李四转到英语系,涉及到多条数据更改
----------------------------------------------------------
第二范式(2NF)
2NF的定义为: 主键依赖,该表里面的其他字段,必须和主键相关;
即一张表格只描述一个对象(主键),其它列名(副键)与对象之间相互完全依赖。
生产一批香蕉,需要给香蕉进行记录,以便追踪去了哪里;首先给每把香蕉进行编号,然后描述他的颜色,成熟度,香味等;
第三范式(3NF)
3NF的定义为: 主键以外的字段,不能有相互依赖的关系;
比如,你想要对香蕉进行【编号】、【颜色】、【香味】、【香味得分】、【香味评级】这种列名,那么你要另开一张专门描述香味的表格。
比如,你想要做【学号】、【姓名】、【性别】、【班级】、【班主任】这种列名,由于【班主任】受【班级】影响,那么你要另开一张专门描述班级的表格。
6.DQL语句(数据库查询语句)
#DQL语句:查询语句,结果绝对不会影响原本数据库的数据
#语法: select 列名 from 表名[vhere-->group by-->having-order by-->limit
#组合使用,必须按照顺序
/**
select 列名
from 表名
where 行条件
group by 对结果分组
having 分组后的行条件
order by 列名 asc/desc 对列名进行升序/降序
limit offset_start,row_count 结果限定,从第几行开始,显示几行
**/
-- ---------------------------------------
#基本查询
SELECT * FROM stu;-- 查询stu表的所有字段.*代表所有的字段
SELECT sid,sname,age,gender FROM stu
SELECT sid,sname FROM stu
#给字段起别名
#select 字段1 as 别名1,字段2 as 别名2 from 表名
#select 字段1 别名1,字段2 别名2
SELECT sid AS '学号',sname AS '姓名' FROM stu;-- 为字段起别名 as 别名
SELECT sid '学号',sname '姓名' FROM stu; -- 起别名
-- ---------------------------------------
#条件查询 通过where进行条件限定,条件是可以组合的 and(与) or(或) not(非)
#比较运算符
# = != > < >= <=
SELECT sid,sname,age,gender FROM stu WHERE age=15; -- 查询年龄为15的学生
SELECT DISTINCT sid,sname,age,gender FROM stu;-- DISTINCT 查询不重复的结果
SELECT sid,sname,age,gender FROM stu WHERE gender='male';-- 查询所有性别为male的学生
SELECT sid,sname,age,gender FROM stu WHERE age!=15; -- 查询所有年龄不等于15的学生,不包括age=null的
SELECT sid,sname,age,gender FROM stu WHERE age<>15; -- <>这个也是不等于
SELECT sid,sname,age,gender FROM stu WHERE age>15;
SELECT sid,sname,age,gender FROM stu WHERE age<15;
SELECT sid,sname,age,gender FROM stu WHERE age>=15;
SELECT sid,sname,age,gender FROM stu WHERE age<=15;
#between a and b 查询区间的值,包括 a和b
SELECT sid,sname,age,gender FROM stu WHERE age BETWEEN 15 AND 50; -- 查询年龄在15-50之间的所有记录,包括15和50
#in(值A,值B,值C)
SELECT sid,sname,age,gender FROM stu WHERE age IN(15,45,75,80,30) -- 查询年龄在集合中的学生记录
#is null
SELECT sid,sname,age,gender FROM stu WHERE age IS NULL;-- 查询年龄为null的学生记录
#is not null
SELECT sid,sname,age,gender FROM stu WHERE age IS NOT NULL; -- 查询年龄不为null的学生记录
# and(与)
SELECT sid,sname,age,gender FROM stu WHERE age=15 AND gender="male";-- 查询年龄为15,并且 性别为male的学生记录
# or(或)
SELECT sid,sname,age,gender FROM stu WHERE age=15 OR gender="male" ;-- 查询年龄为15,或者 性别为male的学生记录
# not(非)
SELECT sid,sname,age,gender FROM stu WHERE NOT age=15 AND gender="male" -- 查询年龄不等于15,并且性别为male的学生记录
# between and + AND
SELECT sid,sname,age,gender FROM stu WHERE age BETWEEN 15 AND 60 AND gender="male" -- 查询年龄在15-60之间,并且性别为male的学生记录
# + - * / %
SELECT sid,sname,age FROM stu WHERE (age+5)>30; -- 查询年龄加5之后大于30的记录
SELECT sid,sname,age FROM stu WHERE (age-5)>50;
SELECT sid,sname,age FROM stu WHERE (age*2)<50;
SELECT sid,sname,age FROM stu WHERE (age/2)>30;
# ifnull(字段,默认值) 通过该字段为null,那么就是默认值
SELECT sid,sname,IFNULL(age,180),IFNULL(gender,'male') FROM stu; -- 如果age=null,就默认为180
-- ---------------------------------------
#模糊查询
# - :代表 一个 字符;
# % :代表任意字符 0-n个
SELECT sid,sname,age,gender FROM stu WHERE sname LIKE '_h%';-- 查询名字的第二个字符为h的记录
SELECT sid,sname,age,gender FROM stu WHERE sname LIKE '%e%';-- 查询名字中包含e的记录
SELECT sid,sname,age,gender FROM stu WHERE sname LIKE "s%" ;-- 查询名字首字符为s的记录
SELECT sid,sname,age,gender FROM stu WHERE sname LIKE "%i"; -- 查询名字的最后一个字符为i的记录
SELECT sid,sname,age,gender FROM stu WHERE sname LIKE '%i%' AND age>40;-- 查询名字中包含i字符,并且年龄大于40岁的记录
-- ---------------------------------------
#排序 order by 字段 asc/desc(升序/降序)
#排序 order by 字段1 asc,字段2 desc
SELECT * FROM stu ORDER BY age ASC;-- 对年龄进行升序ASC,默认是asc升序
SELECT * FROM stu ORDER BY age DESC; -- 对年龄进行降序 desc
SELECT * FROM stu ORDER BY sname ASC; -- 可以对字符串进行排序,按照每一个字母的ascii值进行排
SELECT * FROM stu ORDER BY age ASC,sname DESC -- 先对年龄进行升序,如果年龄一样,再对姓名进行降序
-- ---------------------------------------
#聚合函数
#count() 统计不为null的行数
#max() 求最大值,如果指定列是字符串类型,那么使用字符串排序运算
#min() 求最小值
#sum() 求和,如果指定列表示数字类型,那么计算结果为0
#avg() 求平均数,如果指定列表示数字类型,那么计算结果为0
SELECT COUNT(*) FROM stu; -- 查询全部不为null的记录数
SELECT COUNT(age) FROM stu; -- 查询年龄不为null的记录数
SELECT MAX(age) FROM stu;-- 查询年龄最大的记录
SELECT MIN(age) FROM stu;-- 查询年龄最小的记录
SELECT MAX(sal) FROM emp;-- 查询emp表中工资最高的记录
SELECT SUM(age) FROM stu;-- 查询所有年龄之和
SELECT SUM(sal) FROM emp;-- 查询所有工资的总和
SELECT SUM(sal),SUM(comm) FROM emp -- 查询所有的工资的总和 以及 所有的佣金的总和
SELECT AVG(age) FROM stu;-- 查询年龄平均值
SELECT AVG(sal) FROM emp;-- 查询工资平均值
SELECT AVG(ename) FROM emp
#curdate() 当前日期
#curtime() 当前时间
#NOW() 当前日期+时间
-- ---------------------------------------
#concat :字符串拼接,如果有null进行拼接,结果也为null
SELECT *,CONCAT(job,mgr,sal,comm) FROM emp
SELECT *,CONCAT(job,mgr,sal,IFNULL(comm,'')) FROM emp -- 如果comm为null,则转为默认的空字符串进行拼接
-- ---------------------------------------
#分组查询 group by 字段:根据字段分组
SELECT * FROM emp
SELECT deptno AS '部门编号',SUM(sal) AS '部门工资总和' FROM emp GROUP BY deptno -- 查询每个部门的编号和每个部门的工资和
SELECT deptno '部门编号',COUNT(empno) '部门人数' FROM emp GROUP BY deptno -- 查询每个部门的编号和每个部门的人数
SELECT deptno,COUNT(*)FROM emp WHERE sal>1500 GROUP BY deptno -- 查询每个部门的编号和每个部门工资>1500的人数
-- ---------------------------------------
#having 分组之后对数据进行过滤;
#where 分组之前对数据进行过滤
SELECT deptno,SUM(sal) FROM emp GROUP BY deptno HAVING SUM(sal)>9000 -- 查询工资总和>9000的部门编号和工资总和
-- ---------------------------------------
#分页查询 limit 记录的起始位置,记录的条数
SELECT * FROM emp WHERE sal>2000 ORDER BY sal ASC LIMIT 0,5 -- 查询工资>2000的5条记录
SELECT * FROM emp WHERE sal>2000 ORDER BY sal DESC LIMIT 5 -- 默认是从0开始,所以0可以不写
-- ---------------------------------------------------
#连接查询
#内连接
#mysql里面的方言
SELECT * FROM student,t_class; -- 不推荐使用,因为会把所有的显示,尽管班级编号对应不上,所有笛卡尔积
SELECT * FROM student,t_class WHERE student.`cid`=t_class.`cid` -- -进行通过cid筛选
#标准的sql语句的内查询 关键字 inner join on(关键字,符合两个表的主外键关系,通过主外键来关联)
SELECT * FROM student INNER JOIN t_class ON(student.cid=t_class.cid)
SELECT sid,`name`,cname FROM student INNER JOIN t_class ON(student.cid=t_class.cid)
#外连接:主表和从表
#主表的记录会全部显示,从表的记录要根据主表来显示,主表存在的从表才显示
#如果主表有,从表没有,那么从表会以null来填充
#左连接: A left join B : 左边A为主表,右边B为从表
#右连接: A right joun B: 左边A为从表,右边B为主表
#左连接
SELECT * FROM student LEFT JOIN t_class ON(student.cid=t_class.cid) -- student为主表,t_class为从表,主表全部显示
SELECT sid,`name`,cname FROM student LEFT JOIN t_class ON(student.cid=t_class.cid) ORDER BY sid
#起别名
SELECT sid,`name`,cname FROM student s LEFT JOIN t_class c ON(s.cid=c.cid)
#右连接
SELECT * FROM student RIGHT JOIN t_class ON(student.`cid`=t_class.`cid`)-- t_class为主表,student为从表,主表全部显示,从表就是主表有的才会显示
-- 如果先要展示的字段在两个表中都存在,那么必须在该字段前面指定是哪个表的:表名.字段名
SELECT `name`,t_class.cid,cname FROM student RIGHT JOIN t_class ON(student.`cid`=t_class.`cid`)
#起别名
SELECT `name`,s.cid,cname FROM student s RIGHT JOIN t_class c ON(s.cid=c.cid)
# 1: 查看某个部门下面的所有员工,需要展示部门信息;
SELECT * FROM emp LEFT JOIN dept ON(emp.`deptno`=dept.`deptno`)
SELECT ename,job,dept.`deptno`,dname FROM emp LEFT JOIN dept ON(emp.`deptno`=dept.`deptno`)
#多对多查询 2: teacher/class/ teacher_class 三表的连接查询; 查看某某老师负责哪些班级授课
SELECT * FROM teacher LEFT JOIN teacher_class ON(teacher.tid=teacher_class.tid)
LEFT JOIN t_class ON(teacher_class.`cid`=t_class.`cid`)
-- 显示优化+取别名
SELECT t.tid,tname,cname FROM teacher t LEFT JOIN teacher_class tc ON(t.tid=tc.tid)
LEFT JOIN t_class c ON(tc.cid=c.cid)
-- ---------------------------------------------------
#子查询 查询里面嵌套查询
#1. 查询工资高于JONES的员工。先通过一个查询查找出JONES员工的工资
SELECT * FROM emp WHERE sal>(SELECT sal FROM emp WHERE ename='JONES')
#2. 查询与SCOTT同一个部门的员工。
SELECT * FROM emp WHERE deptno=(SELECT deptno FROM emp WHERE ename='SCOTT')
-- ---------------------------------------------------
#if(判断,真返回,假返回) : 用于两个结果的字段
SELECT `name`,IF(sex=0,'女','男') AS 'sex' FROM person;-- 判断字段,如果是0,返回女,如果是1返回男
#case 字段 when '条件' then '结果' end
SELECT `name`,
CASE sex
WHEN 0 THEN '女' -- 如果是0为女
WHEN 1 THEN '男' -- 如果是1为男
WHEN 2 THEN '保密' -- 如果是2保密
ELSE '未知' -- 其他为未知
END AS 'sex' -- 取别名
FROM person
#ifnull(字段,默认值) 如果为null,就用默认值替代(默认值位自己指定的值)
SELECT `name`,IFNULL(age,18) FROM person
#isnull 如果是null,返回1;如果不是null,返回0
SELECT `name`,ISNULL(age) FROM person
#if(判断,真返回,假返回) : 用于两个结果的字段
SELECT name
,IF(sex=0,‘女’,‘男’) AS ‘sex’ FROM person;-- 判断字段,如果是0,返回女,如果是1返回男
#case 字段 when ‘条件’ then ‘结果’ end
SELECT name
,
CASE sex
WHEN 0 THEN ‘女’ – 如果是0为女
WHEN 1 THEN ‘男’ – 如果是1为男
WHEN 2 THEN ‘保密’ – 如果是2保密
ELSE ‘未知’ – 其他为未知
END AS ‘sex’ – 取别名
FROM person
#ifnull(字段,默认值) 如果为null,就用默认值替代(默认值位自己指定的值)
SELECT name
,IFNULL(age,18) FROM person
#isnull 如果是null,返回1;如果不是null,返回0
SELECT name
,ISNULL(age) FROM person