一.数据库概述
目前来说如果我们要进行数据存储,有几种方式:
我们可以使用集合等方式将数据保存在内存中,但是数据不能持 久化保存,断电/程序退出,数据就清除了
我们还可以将数据保存在普通文件中,可以持久化保存,但是查 找,增加,修改,删除数据比较麻烦,效率低
所以我们需要一个既可以持久化保存数据又可以方便操作的地方来 存储数据,这就是我们接下来要给大家介绍的数据库
数据库(DataBase,DB):指长期保存在计算机的存储设备(硬盘) 上,按照一定规则组织起来,可以被各种用户或应用共享的数据集 合. 还是以文件的方式存在服务器的电脑上的。
说白了就是数据的仓库, 用来持久化保存数据的。
二.SQL语句语法
MySQL数据库的SQL语句不区分大小写,关键字建议使用大写, 以分号结尾。例如:
SELECT * FROM user;
使用/**/、 -- 、# 的方式完成注释
/*
多行注释
*/
-- 单行注释
# 单行注释
SELECT * FROM user;
1.SQL语句分类
Data Definition Language (DDL数据定义语言) 如:操作数据 库,操作表
Data Manipulation Language(DML数据操纵语言),如:对表 中的记录操作增删改
Data Query Language(DQL 数据查询语言),如:对表中的数据 查询操作
Data Control Language(DCL 数据控制语言),如:对用户权限 的设置
2.SQL中数据的常用数据类型
三.DDL操作数据库
1.创建数据库
2. 查看所有的数据库
3. 删除数据库
4. 修改数据库【了解】
5. 其他操作
切换数据库: 在创建表之前一定要指定数据库。use 数据库名
四.DDL操作表
1.创建表
2.SQL约束
约束:即规则,规矩 限制;
作用:数据库中的约束, 就是指 表中的数据内容 不能胡乱填写, 必须按照要求填写. 好保证数据的完整性与安全性。
not null: 非空约束 ;
例如: username varchar(40) not null username这个列不 能有null值
unique:唯一约束, 后面的数据不能和前面重复;
例如: cardNo char(18) unique; cardNo 列里面不可以有重 复数据
primary key;主键约束(非空+唯一);
一般用在表的id列上面. 一张表基本上都有id列的, id列作为 唯一标识的
auto_increment: 自动增长,必须是设置了primary key之后, 才可以使用auto_increment
id int primary key auto_increment; id不需要我们自己维护了, 插入数据的时候直接插入null, 自动的增长进行填充进 去, 避免重复了.
3.查看所有表
五.DML操作表记录-增删改
1.插入记录
-- 创建一张商品表(商品id,商品名称,商品价格,商品数量)
create table product(
id int primary key auto_increment,
pname varchar(100),
price double,
num int
);
-- 方式一: 插入指定列, 如果没有把这个列进行列出来, 以null进行自动赋值。
insert into 表(列,列..) values(值,值..);
insert into product(pname,price) values('Mac',18888);
insert into product(pname,price,num) values('Mac',18888,null);
-- 方式二:插入所有的列
insert into 表 values(值,值....);
insert into product values(null,'小米电脑',5888,10);
insert into product values(null,'华硕电脑',5888,null);
insert into product values(null,'苹果电脑',18000.0,10);
insert into product values(null,'华为5G手机',30000,20);
insert into product values(null,'小米手机',1800,30);
insert into product values(null,'iPhonex',8000,10);
insert into product values(null,'苹果电脑',8000,100);
insert into product values(null,'iPhone7',6000,200);
insert into product values(null,'iPhone6s',4000,1000);
insert into product values(null,'iPhone6',3500,100);
insert into product values(null,'iPhone5s',3000,100);
insert into product values(null,'方便面',4.5,1000);
insert into product values(null,'咖啡',11,200);
insert into product values(null,'矿泉水',3,500);
# 除了整数\小数类型外, 其他字段类型的值必须使用引号引起来 (建议单引号)
# 如果要插入空值, 可以不写字段, 或者插入null
2.更新记录
3.删除记录
delete 和truncate区别【面试题】
DELETE 删除表中的数据,表结构还在; 删除后的数据可以找 回,一条一条的删除. TRUNCATE 删除是把表直接DROP掉,然后再创建一个同样的新表。删除的数据不能找回。执行速度比DELETE快。
工作里面的删除
物理删除: 真正的删除了, 数据不在, 使用delete就属于物理删除
逻辑删除: 没有真正的删除, 数据还在. 搞一个标记, 其实逻辑 删除是更新 例如: state 1 启用 0禁用
六.基本的查询语句
select [*] [列名 ,列名] [列名 as 别名 ...] [distinct 字段] from 表名 [where 条件]
七.简单查询
查询所有的列的记录
查询某张表特定列的记录
去重查询 :去重针对某列, distinct前面不能先出现列名
别名查询
运算查询(+,-,*,/等):运算查询 列名与列名之间是可以 运算的
-- 查询所有的列语法:select * from 表名
-- 需求:查询product表中的所有数据
select * from product;
-- 查询某张表特定列:select 列名,列名,... from 表名
-- 需求:查询product表中的pname,price字段的值
select pname,price from product;
-- 去重查询:select distinct 列名 from 表名
-- 需求:查询price字段,[去重]单个字段去重
select distinct price from product;
-- 需求:查询pname,price字段,[同时去重]多个字段去重
select distinct pname,price from product;
-- 注意:distinct前面不能有字段名
select id,distinct price from product; -- 报错
-- 别名查询
-- 对字段取别名:select 字段 as 别名,字段 as 别
名,... from 表名。注意: as可以省略,一般都会省略
select pname as 商品名称,num as 商品数量 from product;
select pname 商品名称,num 商品数量 from product;
-- 对表取别名:select 表1别名.字段名,... from 表1
as 表1别名。注意: as可以省略 一般都会省略
select p.pname,p.price from product as p;
select p.pname,p.price from product p;
-- 运算查询(+,-,*,/等),null和其他数据进行运算得到是null
-- 需求:计算每个商品的总价(单价*数量)
select price,num from product;
select price*num from product;
select price * num 总价 from product
select price * ifnull(num,0) 总价 from product
八.条件查询
-- 需求:
-- 查询商品价格>3000的商品
-- 查询id=1的商品
-- 查询id<>1的商品
-- 查询价格在3000到6000之间的商品
-- 查询id在1,5,7,15范围内的商品
-- 查询商品名以iPho开头的商品(iPhone系列)
-- 查询商品价格大于3000并且数量大于20的商品 (条件 and 条件 and...)
-- 查询id=1或者价格小于3000的商品
-- 查询商品价格>3000的商品
select * from product where price > 3000;
-- 查询id=1的商品
select * from product where pid = 1;
-- 查询id<>1的商品
select * from product where pid <> 1;
select * from product where pid != 1;
-- 查询价格在3000到6000之间的商品
select * from product where price between 3000 and 6000;
select * from product where price >= 3000 and price <= 6000;
-- 查询id在1,5,7,15范围内的商品
select * from product where pid in(1,5,7,15);
-- 查询商品名以iPho开头的商品(iPhone系列)
select * from product where pname like 'iPho%';
-- 查询商品价格大于3000并且数量大于20的商品 (条件 and 条件 and...)
select * from product where price > 3000 and num > 20;
-- 查询id=1或者价格小于3000的商品
select * from product where pid = 1 or price < 3000;
九.排序查询
有时候我们需要对查询出来的结果排序显示,那么就可 以通过ORDER BY子句将查询出的结果进行排序。排序可 以根据一个字段排,也可以根据多个字段排序,排序只 是对查询的结果集排序,并不会影响表中数据的顺序。
十.聚合函数
十一.分组查询
分组查询是指使用 GROUP BY语句对查询信息进行分组。
GROUP BY怎么分组的? 将分组字段结果中相同内容作 为一组,如按性别将学生分成两组。
GROUP BY将分组字段结果中相同内容作为一组,并且 返回每组的第一条数据,所以单独分组没什么用处。分 组的目的就是为了统计,一般分组会跟聚合函数一起使 用。
注意事项:
根据某一列进行分组, 将分组字段结果中相同内容 作为一组; 有几组 返回的记录就有几条
单独分组 没有意义, 返回每一组的第一条记录
分组的目的一般为了做统计使用, 所以经常和聚合 函数一起使用
在分组里面, 如果select后面的列没有出现在group by后面 展示这个组的这个列的第一个数据
where和having的区别【面试】
十二.分页查询
LIMIT是限制的意思,所以LIMIT的作用就是限制查询记 录的条数. 经常用来做分页查询
语法:
十三.外键约束
1.单表的特点
缺点:表中出现了很多重复的数据(数据冗余),如果要修 改研发部的地址需要修改3个地方。
解决方案:将一张表分成2张表(员工表和部门表)
-- 创建部门表
CREATE TABLE department (
id INT PRIMARY KEY AUTO_INCREMENT,
dep_name VARCHAR(20),
dep_location VARCHAR(20)
);
-- 创建员工表
CREATE TABLE employee (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
age INT,
dep_id INT
);
-- 添加2个部门
INSERT INTO department (dep_name, dep_location) VALUES ('研发部', '广州');
INSERT INTO department (dep_name, dep_location) VALUES('销售部', '深圳');
-- 添加员工,dep_id表示员工所在的部门
INSERT INTO employee (NAME, age, dep_id) VALUES('张三', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES('李四', 21, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES('王五', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES('老王', 20, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES('大王', 22, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES('小王', 18, 2);
问题:当我们在employee的dep_id里面输入不存在的部门,数据依然可以添加.但是并没有对应的部门,不能出现这种情况。employee的dep_id中的内容只能是department表中存在的id
解决方式:使用外键约束。需要约束dep_id字段的值, 只 能是department表中已经存在id
2.外键约束
2.1 外键约束作用
外键约束作用:保证引用数据的完整性。用来维护多表 间关系
外键: 一张从表中的某个字段引用主表中的主键
主表: 约束别人
副表/从表: 使用别人的数据,被别人约束
2.2添加外键语法
1. 新建表时增加外键:
[CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字
段名) REFERENCES 主表名(主键字段名)
注意事项:外键的这个列的类型必须和参照主表主键列的类型一致
关键字解释:
CONSTRAINT -- 约束关键字
FOREIGN KEY(外键字段名) –- 某个字段作为外键
REFERENCES -- 主表名(主键字段名) 表示参照主表中的某个字段
2. 已有表增加外键:
ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名
称] FOREIGN KEY (外键字段名) REFERENCES 主表(主
键字段名);
-- 先删除employee表,因为在创建这张表的时候没有设置外键约束
-- 创建表的时候增加外键
-- 语法:[CONSTRAINT] [外键约束名称] FOREIGN
KEY(外键字段名) REFERENCES 主表名(主键字段名)
-- 创建员工表
CREATE TABLE employee (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
age INT,
dep_id INT,
-- 添加一个外键
constraint fk1 foreign key(dep_id)
references department(id)
);
-- 添加员工,dep_id表示员工所在的部门
INSERT INTO employee (NAME, age, dep_id) VALUES('张三', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES('李四', 21, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES('王五', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES('老王', 20, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES('大王', 22, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES('小王', 18, 2);
-- 验证: 添加错误数据
INSERT INTO employee (NAME, age, dep_id) VALUES ('二王', 20, 5);// 报错
2.3删除外键语法
2.4外键的级【了解】
解决方案:级联操作
在修改和删除主表的主键时,同时更新或删除副表的外 键值,称为级联操作
ON UPDATE CASCADE -- 级联更新,主键发生更新 时,外键也会更新
ON DELETE CASCADE -- 级联删除,主键发生删除时,外键也会删除
具体操作:
删除employee表
重新创建employee表,添加级联更新和级联删除
再次添加数据到员工表和部门表
十四.表与表之间的关系
现实生活中,实体与实体之间肯定是有关系的,比如: 老公和老婆,部门和员工,老师和学生等。那么我们在 设计表的时候,就应该体现出表与表之间的这种关系。
分成三种:
一对多
多对多
一对一
1.1 一对多
1.2多对多
1.3一对一(通常单表)
1.4 多表分析及创建
十五.连接查询
1.环境准备
2 交叉查询【了解】
3. 内连接查询【重点】
交叉查询产生这样的结果并不是我们想要的,那么怎么去除错误的,不想要的记录呢,当然是通过条件过滤。通常要查询的多个表之间都存在关联关系,那么就通过关联关系(主外键关系)去除笛卡尔积。
3.1 隐式内连接
3.2 显示内连接
显示里面是有inner关键字的
4 外连接【重点】
4.1 左外连接
以join左边的表为主表,展示主表的所有数据,根据条件查 询连接右边表的数据,若满足条件则展示,若不满足则以 null显示。
可以理解为:在内连接的基础上保证左边表的数据全部 显示
4.2 右外连接
以join右边的表为主表,展示右边表的所有数据,根据条件 查询join左边表的数据,若满足则展示,若不满足则以null 显示。
可以理解为:在内连接的基础上保证右边表的数据全部 显示
4.3 内连接和外连接的区别
十六.子查询【重点】
1 子查询介绍
2.子查询一
3.子查询二
子查询结果只要是单列多行,肯定在WHERE后面作为条件
子查询结果是单列多行,结果集类似于一个数组,父查 询使用IN运算符
SELECT 查询字段 FROM 表 WHERE 字段 IN (子查询);
4. 子查询三
子查询结果只要是多行多列,肯定在FROM后面作为表
SELECT 查询字段 FROM (子查询) 表别名 WHERE 条 件;
子查询作为表需要取别名,否则这张表没用名称无法访 问表中的字段