简介
数据库相关概念
数据库:存储和管理数据的仓库,数据是有组织的进行存储;简称DB;
数据库管理系统:管理数据库的软件,简称DBMS;
关系型数据库
关系型数据库是建立在关系模型上的数据库;关系型数据库就是由多张能相互连接的二维表()组成的数据库
优点:
-
都是用表结构,格式一致,易于维护
-
都使用通用的DQL语言进行操作,使用方便,可用于复杂查询
-
数据存储在磁盘中,安全
-
每张表都是简单的二维结构,降低了数据冗余;
MySQL数据模型
navicat
一套全面的前端工具,为数据库管理,开发和维护提供了一套直观而强大的图形界面
帮助管理数据库,编写SQL语句;
SQL语法
结构化查询语言,操作关系型数据库的编程语言,定义所有关系型数据库的统一标准(但是每种数据库都有一定自己的独特操作方式,我们称为方言);
通用语法
-
SQL语句可以单行或者多行书写,但是以分号(“;”)结束;
-
SQL语句不区分大小写,(编译的时候全部当做大写);
-
注释:单行注释:--注释语句;多行注释:/*注释语句*/
SQL分类:
-
DDL:操作数据库,表
-
DML:对表中的数据进行insert,update,delete操作;
-
DQL:对表中的数据进行查询select操作;
-
DCL:对数据库进行权限控制
DDL——操作数据库
目的 | 命令语句 |
---|---|
查看数据库 | show databases; |
创建数据库 | create database 数据库名称: |
判断并创建数据库 | create database if not exists 数据库名称; |
删除数据库 | drop database 数据库名称; |
判断并删除数据库 | drop database if exists 数据库名称; |
查看当前使用的数据库 | select database(); |
使用数据库 | use 数据库名称; |
DDL——操作表
查询表
目的 | 命令语句 |
---|---|
查询当前数据下所有表的名称 | show tables; |
查询表结构 | desc 表名称; |
创建表
CREATE TABLE 表名( 字段名1 数据类型, 字段名2 数据类型, ...... 字段名n 数据类型 --最后一个字段无逗号 );
数据类型:
-
数值类型
-
日期和时间类型
-
字符串类型
案例:
结果代码:
create table STUDENT(
id int,
name varchar(10),
gender char(1),
birthday date,
score double(5,2),
email varchar(64),
telphone varchar(15),
status tinyint
);
修改表
目的 | 命令语句 |
---|---|
修改表名 | alter table 表名 rename to 新的表名; |
添加一列 | alter table 表名 add 列名 数据类型; |
修改数据类型 | alter table 表名 modify 列名 新数据类型; |
修改列名和数据类型 | alter table 表名 change 列名 新列名 数据类型; |
删除列 | alter table 表名 drop 列名; |
删除表
目的 | 命令语句 |
---|---|
删除表 | drop table 表名; |
判断表是否存在并删除 | drop table if exists 表名; |
DML——修改表数据
目的 | 语句 |
---|---|
向表指定列插入一行数据 | insert into 表名(列名1,列名2...)values(值1,值2...) |
向表中插入一行数据 | insert into 表名 values(所有列对应的值) |
批量添加数据 | 1: insert into 表名(列名1,列名2...)values(值1,值2...),(值1,值2...)...... 2: insert into 表名 values(所有列对应的值),(所有列对应的值)...... |
修改数据 | update 表名 set 列名1=值1,列名2=值2......[where 条件](如果不加where语句,整个列的值都是设置的值) |
删除数据 | delete from 表名 [where 条件](不加where语句,清空整个表) |
DQL_——查询表数据
select 字段列表
from 表名
where 条件列表
group by 分组字段
having 分组后条件
order by 排序字段
limit 分页限定
基础查询:
语句:
select (列名,......)from 表名; --查询指定列的数据
select * from 表名; --查询所有的数据
select distinct 列名 from 表名; --去除重复记录
select 列名 as 别名 from 表名; --起别名,as可以省略,用空格分隔即可;
-
不加where条件,每次查是将指定列的所有数据查出来
条件查询:
语法:
select 字段列表 from 表名 where 条件列表;
条件:
模糊查询:
模糊查询使用like关键字,可以使用通配符进行占位:
(1)_ : 代表单个任意字符
(2)% : 代表任意个数字符
排序查询:
语法:
SELECT 字段列表 FROM 表名 ORDER BY 排序字段名1 [排序方式1],排序字段名2 [排序方式2]...
排序方式:
ASC:升序排列(默认值)
DESC:降序排列
ps:如果有多个排序条件,当前边的条件值一样时,才会根据第二条件进行排序;
聚合函数:
将一列数据作为一个整体,进行纵向计算。
分类:
函数名 | 功能 |
---|---|
count(列名) | 统计数量 |
max(列名) | 最大值 |
min(列名) | 最小值 |
sum(列名) | 求和 |
avg(列名) | 平局值 |
聚合函数:一般选用不为null的列,null 值不参与所有聚合函数运算;
语法:
SELECT 聚合函数名(列名) FROM 表
分组查询:
语法:
SELECT 字段列表 FROM 表名 [WHERE 分组前条件限定] GROUP BY 分组字段名 [HAVING 分组后条件过滤];
注意:分组之后,查询的字段为聚合函数和分组字段,查询其他字段无任何意义;
where 和 having 区别:
-
执行时机不一样:where 是分组之前进行限定,不满足where条件,则不参与分组,而having是分组之后对结果进行过 滤。
-
可判断的条件不一样:where 不能对聚合函数进行判断,having 可以。
分页查询:
语法:
SELECT 字段列表 FROM 表名 LIMIT 起始索引 , 查询条目数;
起始索引由0开始;
约束
从数据库层面在添加数据的时候进行限制,作用于表中列上的规则,用于限制加入表的数据,在保证了数据库中数据的正确性、有效性和完整性;
分类:
约束名 | 关键字 | 描述 |
---|---|---|
非空约束 | NOT NULL | 保证列中所有的数据不能有null值 |
唯一约束 | UNIQUE | 保证列中所有数据各不相同 |
主键约束 | PRIMARY KEY | 主键是一行数据的唯一标识,要求非空且唯一 |
检查约束 | CHECK | 保证列中的值满足某一条件 |
默认约束 | DEFAULT | 保存数据时,未指定值则采用默认值 |
外键约束 | FOREIGN KEY | 外键用来让两个表的数据之间建立链接,保证数据的一致性和完整性。(副表的主键在主表中有对应,称为外键) |
创建一张有约束的表;
-- 员工表 CREATE TABLE emp ( id INT, -- 员工id,主键且自增长 ename VARCHAR(50), -- 员工姓名,非空且唯一 joindate DATE, -- 入职日期,非空 salary DOUBLE(7,2), -- 工资,非空 bonus DOUBLE(7,2), -- 奖金,如果没有将近默认为0 dep_id );
结果:
-- 员工表
CREATE TABLE emp (
id INT PRIMARY KEY auto_increment, -- 员工id,主键且自增长
ename VARCHAR(50) NOT NULL UNIQUE, -- 员工姓名,非空并且唯一
joindate DATE NOT NULL , -- 入职日期,非空
salary DOUBLE(7,2) NOT NULL , -- 工资,非空
bonus DOUBLE(7,2) DEFAULT 0 -- 奖金,如果没有奖金默认为0
);
外键约束:
-- 创建表时添加外键约束
CREATE TABLE 表名(
列名 数据类型,
…
[CONSTRAINT] [外键名称] FOREIGN KEY(外键列名) REFERENCES 主表(主表列名)
);
-- 建完表后添加外键约束
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);
--删除外键约束
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
表关系
一对一
实现:在任意一方加入外键,关联另一方主键,并且设置外键为唯一(UNIQUE);
一对多
实现:在多的一方建立外键,指向一的一方的主键
多对多
实现:建立第三张中间表,中间表至少包含两个外键,分别关联两方主键,称为桥表;
多表查询
多表查询顾名思义就是从多张表中一次性的查询出我们想要的数据
对于两张表员工表与部门表,假如直接查询数据,不做条件判定,(SQL语句:select * from 员工表,部门表)相当于两张表做了一次笛卡尔积;假设员工表5条数据,部门表3条数据,则查询结果是15条数据;
但是众所周知,员工属于部门,员工表的列中有一行部门号,只要不为空则在部门表中必有一行数据与之对应,在写SQL语句时,将其体现(sql语句:select * from 员工表,部门表 where 员工表.部门号=部门表.id);假设员工表5条数据,部门表3条数据,则查询结果是5条数据(员工表部门号不为空的情况下);
这个就是最普通常见的连接查询,其中连接查询还可以分为内连接与外连接查询两大类;
内连接查询
语法:
-- 隐式内连接 SELECT 字段列表 FROM 表1,表2… WHERE 条件; -- 显示内连接 SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件;
方括号括起来的是可以省略的;
内连接相当于查询表1,表2交集数据
外连接查询
语法:
-- 左外连接 SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件; -- 右外连接 SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件;
左外连接:左表(表1)中所有的数据及两张表能关联的数据。
右外连接:右表(表2)中所有的数据及两张表能关联的数据.
子查询
查询中嵌套查询,称嵌套查询为子查询
子查询的结果不同,作用不同:
-
子查询语句结果是单行单列,子查询语句作为条件值,使用 = != > < 等进行条件判断
-
子查询语句结果是多行单列,子查询语句作为条件值,使用 in 等关键字进行条件判断
-
子查询语句结果是多行多列,子查询语句作为虚拟表
事务
简介:
-
数据库的事务(Transaction)是一种机制、一个操作序列,包含了一组数据库操作命令
-
事务把所有的命令作为一个整体一起向系统提交或撤销操作请求,即这一组数据库命令要么同时成功,要么同时失败。
-
事务是一个不可分割的工作逻辑单元。
特点:
-
原子性(Atomicity): 事务是不可分割的最小操作单位,要么同时成功,要么同时失败
-
一致性(Consistency) :事务完成时,必须使所有的数据都保持一致状态
-
隔离性(Isolation) :多个事务之间,操作的可见性
-
持久性(Durability) :事务一旦提交或回滚,它对数据库中的数据的改变就是永久的
语法:
--开启事务:
START TRANSACTION; 或者 BEGIN;
--提交事务
commit;
--回滚事务
rollback;
说明:mysql中事务是自动提交的。 也就是说我们不添加事务执行sql语句,语句执行完毕会自动的提交事务。
查询事务提交的方式:
SELECT @@autocommit;
设置事务提交的方式:1表示自动提交,0表示手动提交
set @@autocommit = 0;