数据库总结

数据库

顾名思义,存数数据的仓库

优点:1)执行效率高

        2)底层有树结构在里面

        3)数据库支持"事物"来解决业务中存在的问题,即使中途关机数据依然保存

数据库的分类

关系型数据库:

书写sql语句(Structure Query Language:结构化查询语言)

• oracle:收费,大型公司企业居多
• mysql:免费,中小型公司使用居多
• sqlserver:跟.net平台兼容性比较好一些
• sqllite:轻量级的关系数据库,嵌入式里面居多
• mariaDB:跟mysql同级别的

非关系型数据库:

• 键值(key-value)存储数据库:redis做数据缓存
• 分布式系统架构:使用redis分布式缓存
• 文档类型数据库:MongoDb

mysql的基本语法

-- :普通注释 当行注释
/* mysql的多行注释 */
#特殊注释

-- 查询当前mysql中自带的所有库有哪些
mysql> show databases;

Database

information_schema                mysql的默认配置库
mysql                             有user表(管理员用户表):root用户
performance_schema                mysql其他库(性能相关)
test                              测试库,但是不用它,自己创建新的库
 
4 rows in set (0.00 sec)

-- 创建库
-- create database 库名;
例:mysql>create datebase myEE_2022;
Query ok,1 row affected(0.00 sec)
mysql>show databases; 

Database
information_schema
myee_2022
mysql
performance_schema
test

5 rows in set (0.00 sec)

-- create datebase if not exists 库名;


-- 查询创建库的字符集
-- show create database 库名;

-- 修改库的字符集
-- alter database 库名 default character set 字符集名称;

-- 删除库
-- drop database 库名;
-- drop database if exists 库名;    如果存在这个库删除

mysql常见的数据类型

int:整数类型,默认最大长度11位字符,给int类型的数据的时候,当前存储的是值的真实长度

例:    年龄字段age int类型
      
int(字符数):整数类型,给int(3)这个值的时候,实际存储3位,但是赋值的时候不够3位(使用很少)

varchar:字符串类型,最大支持255个长度,指定varchar(指定长度)

date:仅仅是日期类型

datetime:日期+时间类型

timestap:时间戳(例:管理员添加一个商品,商品上架的时间:当前系统瞬时时间 2021-5-11 16:50:34)

double:小数类型 double(几位数,小数点后保留的位数)

例:double(4,2):4位,小数点后保留2位

clob:大字符类型,支持"大文本"

blob:大字节类型,最大支持4G

建表语法

-- 建表之前,必须使用哪个库
-- use 库名;

/*
    create table 表名(
        字段名称1 字段类型1,
        字段名称2 字段类型2,
        ...
        ...
        字段名称n 字段类型n
);

-- 查询当前库中有哪些表
-- show tables;

-- 查看表的结构
-- desc 表名;

-- 修改表的字段名称
-- alter table 表名 change 旧字段名称 新的字段名称 以前的字段数据类型;

-- 修改表的字段类型 modify
-- alter table 表名 modify 字段名称 新的字段类型;

-- 修改表:给表中添加一个新的字段
-- alter table 表名 add 字段名称 字段类型;

-- 复制一张表
-- create table 新表名 like 旧表名;

-- 删除表
-- drop table 表名;
-- drop table if exists 表名;

sql工具语法

-- sqlYog工具中,按tab键有提示信息
-- 使用库
USE myee_2022;

-- 查询这个库中有哪些表
SHOW TABLES;

-- 创建一张新的表学生表,id,姓名,年龄,性别,住址
CREATE TABLE student(
    id INT ,  -- 学号
    NAME VARCHAR(10), -- 姓名
    age INT,  -- 年龄
    gender VARCHAR(5), -- 性别
    address VARCHAR(50) -- 住址
) ;

-- 查询学生表的结构
DESC student;

-- DML语句:数据库操作语句
-- 给学生表中插入数据
-- 插入数据的语法1:
 insert into 表名 values (值1,值2,值3,值4...值n); 

-- 插入全部数据
 INSERT INTO student VALUES((1,'高圆圆',42,'女','西安市') ;

-- 插入表的数据支持 :一次插入多条数据
-- insert into 表名 values(值1,值2,值3,值4...值n),(值1,值2,值3,值4...值n),(值1,值2,值3,值4...值n);

-- 插入数据的语法2:插入部分字段,没有插入的字段,默认值就是null, 也支持一次性插入多条数据
 insert into 表名(字段名称1,字段名称2....) values(值1,值2...) ;


-- DML语句之修改表记录
-- 一般实际开发中修改,都是带条件修改
-- update 表名 set 字段名称 = 值 where 条件;

-- 语法2,一次性修改多个字段
-- update 表名 set 字段名称1 = 值,字段名称2 = 值2...where 条件;

-- 语法3,不带条件,属于批量修改(部分场景可以用)
-- update 表名 set 字段名称 = 值,字段名称2 = 值2...

-- DML语句之删除表的记录
-- delete from 表名 where 条件;   // 带条件删除记录

-- delete from 表名:删除全表数据
-- truncate table 表名:删除全表数据

两者区别

delete from 表名;仅仅是将当前表的数据所有记录删除,表还在,不会影响自增长主键id的值(非业务字段)

        目前 id=7 -----当将这个表所有数据清除,再次插入数据的时候,id=8

turncate table 表名;将表数据记录清除,删除表,自动创建一张一样的表,直接影响了自增长主键id的值

        目前id=7-----通过这个语句删除了表,自动创建,再次插入数据的时候 id=1

DQL语句最通用的语法

-- 1)DQL语句之最基本的查询语句 select
-- 查询全表数据
-- *代表所有字段,仅仅是自己玩的时候可以用,实际开发中不能用*,需要写上全部的字段名称
select
        *
from
        表名;

-- 查询全部字段的时候,给字段起一个别名 as'别名名称',as可以省略
select
    id AS'编号',
    name AS'年龄',
    age AS'性别',
    address AS'地址',
    math AS'数学成绩'
from
    student3;

-- 当表的名称比较长的时候,可以给表名起一个别名
select
    s.id'学生编号',
    s.name'学生姓名',
    s.age'学生年龄',
    s.sex'学生性别',
    s.address'学生地址',
    s.math'数学成绩'
from
    student3 s;

-- 可以查询部分字段
-- 需求:查询学生的姓名以及数学和英语成绩
SELECT
	NAME'姓名',
	math'数学成绩',
	english'英语成绩'
FROM
	student3;

-- 发现字段冗余(重复度大)字段去重,DISTINCT后面跟上字段名称

-- 2)DQL语句之条件查询 where关键字
-- 2.1)使用 赋值运算符=,比较运算符<,<=,>=,>,!=,mysql中的不等于<>
-- 2.2)java中逻辑运算符:&&,||    mysql推荐使用and,or
-- 2.3)针对两个范围查询,可以使用&&,也可以使用and,也可以"字段名称between 值1 and 值2"

- 需求:查询年龄大于20岁的学生所有信息
SELECT
	*
FROM
	student3
WHERE
	age>20;

-- 需求:查询年龄在20到30之间的学生的姓名,年龄,住址,数学和英语成绩信息
SELECT
	NAME'姓名',
	age'年龄',
	address'住址',
	math'数学成绩',
	english'英语成绩'
FROM
	student3
WHERE	age>=20&&age<=30; -- &&逻辑双与

-- mysql中使用and表示并列关系
SELECT 
  NAME '姓名',
  age '年龄',
  address '住址',
  math '数学成绩',
  english '英语成绩' 
FROM
  student3 
  WHERE age>=20 AND age<=30; -- and连接

 -- 使用的between 值1 and 值2
 SELECT
    NAME '姓名',
    age '年龄',
    address '住址',
    math '数学成绩',
    english '英语成绩' 
FROM
    student3 
WHERE
   age BETWEEN 20 AND 30;

 -- 需求:查询年龄是20岁学生的所有信息
 SELECT * FROM student3 WHERE age=20;

 -- 需求:查询年龄不是20岁的学生的所有信息
 SELECT * FROM student3 WHERE age!=20; -- != java中的用法

 SELECT * FROM student3 WHERE age<>20; -- mysql中的不等于 <> 

-- 需求:查询年龄是18岁或者是20或者是45岁的学生的所有信息
 SELECT
	*
FROM
	student3
WHERE
	age=18||age=20||age=45; -- java中逻辑双或||

SELECT
	*
 FROM
  student3
 WHERE
	age=18 OR age=20 OR age=45; -- Mysql中的or这个表示 或(并集)

-- 这面这个格式优化为in(值1,值2,值3..值n);in集合语句
SELECT
	*
FROM
	student3
WHERE
	age IN(18,20,45);
  
-- Java语言中:去判断某个条件的内容为null,mysql语言不支持这个格式==null
-- 需求;查询学生的英语成绩为null的学生所有信息
-- select * from student3 where english == null;

-- mysql中判断某个字段为null,使用的语法是is null
-- 判断某个字段不为null,使用的语法是 is not null
SELECT * FROM student3 WHERE english IS NULL;

-- 关于int类型字段求和的时候,注意:int类型的值 + null 值 = null;
-- 查询学生的姓名以及英语和数学总分
SELECT
 NAME'姓名',
 (math+english)'总分'
 FROM
 student3;
 -- 上面这种情况:不太友好,数学成绩有值,但是结果求和是null
 -- mysql提供函数 ifnull(字段名称,值);如果字段名称是null,给一个默认值
 SELECT
  NAME'姓名',
  (math+IFNULL(english,0))'总分'
  FROM
  student3;


-- 3)DQL语句之where条件后面加入模糊查询 -- 关键字like
-- select 字段列表 from 表名 where 字段名称 like '%xxx%';
-- %:代表任何多个字符或者某个字符(使用居多):网址门户系统---"搜索商品",模糊搜索
-- _:代表某个单个字符

-- 需求:查询所有姓马的学生信息
SELECT
	*
FROM
	student3
WHERE
	NAME LIKE '%马%'
	
-- 查询学生姓名三个字符并且第二个字符是化的人
SELECT
	*
FROM
	student3
WHERE
	NAME LIKE'%_化_%'

-- 查询姓名为三个字符的学生信息 
SELECT
	*
FROM
	student3
WHERE
	NAME LIKE'___';


-- 聚合函数查询:针对int类型:单行单列数据
-- select 聚合函数 from 表名 where 还可以指定条件;

-- count(字段名称):查询表中记录,字段名称都是使用id,非业务字段
-- avg(字段名称):查询这个字段中平均值
-- sum(字段列表):求和函数
-- max(字段名称):最大值
-- min(字段名称):最小值

-- 需求:查询表的总记录数
-- select count(english)'总记录数'from student3; -- 前提条件使用业务字段查询,不能有null值
-- select count(ifnull(english,0))'总记录数'from student3;
SELECT 
    COUNT(id)'总条数'
FROM 
    student3;

-- 查询数学平均分 avg(字段名称)
SELECT 
    AVG(math)'数学平均分'
FROM 
    student3;

-- 数学和英语成绩求和的学生信息(姓名,和总分)
SELECT
    SUM(math+IFNULL(english,0))'总分'
FROM
	student3;

-- max()和min()
SELECT 
    MAX(math)'数学最高分'
FROM 
    student3;

-- select语句嵌套select语句 ---子查询
-- 需求:查询出学生数学成绩大于数学平均分的学生所有信息;
-- 使用where 条件 后面带上 比较运算符..

-- 1)查询数学平均分是多少
    SELECT AVG(math)FROM student3; --79.5000
-- 2)查询大于数学平均分的学生信息
SELECT
	*
FROM
    student3
WHERE
    math>79.5000;

-- 一步走
SELECT
	*
FROM
	student3
WHERE
	math>(SELECT AVG(math)FROM student3);

-- DQL之排序查询 order by
-- select 字段列表 from 表名 order by 字段名称 排序规则(asc(默认值就是升序)或者desc降序)

-- 需求:按照数学成绩升序排序 单个字段
SELECT 
    *
FROM 
    student3
ORDER BY 
	math ASC ; -- 默认不写排序规则,就是asc 升序

-- 同时条件和order by,order by在where的后面
-- 需求:数学成绩大于56分的学生进行数学的降序排序
SELECT
    NAME,
    math
FROM
    student3 
WHERE  
    math > 56    -- 先满足条件,才能排序!where 必须order by之前
ORDER BY 
    math DESC ;

-- 多个字段要同时排序,首先第一个字段排序规则,然后才是第二个字段
-- 需求:数学成绩降序,英语成绩升序排序
SELECT 
    *
FROM 
    student3 
ORDER BY 
	math DESC,english ASC;	

-- DQL之分组查询 group by
-- select 字段列表 from 表名 group by 分组字段名称;
-- 注意事项:1)查询的字段列表中可以使用分组字段
           2)group by之后不能使用聚合函数

-- 带条件分组查询的语法:where条件必须放在group by之前,否则语法出错
-- select 字段列表包含分组字段,聚合函数...from 表名 where 条件 group by 分组字段;

-- 需求:按照性别分组,查询出他们的数学成绩的平均分,数学成绩大于70分的参与分组
SELECT
	sex'性别',
	AVG(math)'数学平均分'
FROM
	student3
WHERE
	math>70
GROUP BY
	sex;

-- 筛选查询 having
-- where条件,group by,having必须先有条件,分组,然后才筛选
-- 注意:筛选的后面可以使用聚合函数,group by的后面是不能使用聚合函数的
SELECT
	sex'性别',
	COUNT(id)人数,
	AVG(math)'数学平均分'
FROM
	student3
WHERE
	math>70
GROUP BY
	sex
HAVING	-- 后面可以使用聚合函数
	人数>2;

-- 分页查询 Limit
-- select 字段列表 from 表名 limit 起始行数,每页显示的条数;

-- 起始行数:从0开始算的
-- 起始行数=(当前页码-1)*每页显示的条数
-- 前提条件:每页显示2条

-- 第一页数据
SELECT * FROM student3 LIMIT 0,2;
-- 第二页数据
SELECT * FROM student3 LIMIT 2,2;
-- 第三页数据
SELECT * FROM student3 LIMIT 4,2;
-- 第四页数据
SELECT * FROM student3 LIMIT 6,2;
-- 第五页数据
SELECT * FROM student3 LIMIT 8,2;

数据库的约束

-- 概念:限制用户操作数据库的一种行为(非法行为)
-- 直接插入null值或者某个非业务字段Id一直在重复
-- 创建一张表stu表

USE myee_22032;
CREATE TABLE stu(
	id INT,
	NAME VARCHAR(20),
	gender VARCHAR(3) DEFAULT'女'
	);
INSERT INTO stu VALUES(1,'刘亦菲','女'),(2,'胡歌','男');
INSERT INTO stu(id,NAME)VALUES(3,'彭于晏');

-- 上面gender字段没有插入数据,默认就是null,空值(没有意义)

-- 1)默认约束 default:如果某个字段没有插入数据,那么默认约束起作用了

-- 通过sql语句将默认约束去掉
ALTER TABLE stu MODIFY gender VARCHAR(3);

-- 通过sql语句给gender字段加上默认约束
Alter TABLE stu MODIFY gender VARCHAR(3) DEFAULT'男';

-- 2)非空约束 not null,当前这个字段值不能为null

CREATE TABLE stu(
	id INT, -- 编号
	NAME VARCHAR(20), -- 姓名
	age INT, -- 年龄
	address VARCHAR(50) NOT NULL -- 地址,加入非空约束
);

-- 地址插入一个null值
    INSERT INTO stu VALUES(3.'张佳宁',32,NULL); -- 这种直接插入null也属于非法行为
-- 当address字段设置not null约束,直接限定用户不能插入null值
-- Column'address'cannot be null

-- insert into stu(id,name,age)values(3,'张佳宁',32);
-- 'address' doesn't have a default value 地址没有默认值

-- 通过sql语句将非空约束去掉--修改表的类型
ALTER TABLE stu MODIFY address VARCHAR(50);

-- 通过sql语句将非空约束加上
ALTER TABLE stu MODIFY address VARCHAR(50) NOT NULL;

-- 3)唯一约束unique(属于一种index:索引)
CREATE TABLE stu(
	id INT, -- 编号
	NAME VARCHAR(20), -- 姓名
	phone_number VARCHAR(11)UNIQUE -- 电话号码 必须设置唯一的
);
INSERT INTO stu VALUE(1,'高圆圆','13388886666'),(2,'张三','13255559999');
INSERT INTO stu VALUES(3,'张佳宁','13388886666'); -- 重复值
-- Duplicate entry '13388886666' for key 'phone_number'

-- 通过sql语句将唯一约束删除
-- alter table stu modify phone_number varchar(11); -- 修改表的字段类型

-- 删除唯一约束的语法:alter table 表名 drop index 索引的字段名称
ALTER TABLE stu DROP INDEX phone_number;

-- 通过sql语句添加唯一约束,此时语法就和修改表的字段类型的语法一致
ALTER TABLE stu MODIFY phone_number VARCHAR(11)UNIQUE;


-- 4)主键约束 primary key(非空且唯一)一般和auto_increment(自增长约束)使用多一些
-- 设置在id非业务字段上
CREATE TABLE stu(
	id INT PRIMARY KEY AUTO_INCREMENT  , -- 编号 加入主键约束,加入自增长
	NAME VARCHAR(20) -- 姓名
);

INSERT INTO stu VALUES(1,'高圆圆'),(2,'文章');
INSERT INTO stu VALUES(1,'赵又廷');-- Duplicate entry '1' for key 'PRIMARY'
-- 'PRIMARY' id是一个主键,值不能重复
INSERT INTO stu VALUES(NULL,'王宝强');

-- 通过sql将主键约束删除
-- alter table 表名 drop primary key;

-- 通过sql语句将主键约束加入(唯一起作用)
ALTER table stu MODIFY id INT PRIMARY KEY;

-- 加入自增长约束之后,不需要给id赋值了,自己自增
-- 自增长约束可以自己插入新的id,然后在之前id上继续自增
-- mysql有一个函数:获取最后一次自增长主键的id值


-- 5)外键约束 foreign key
-- 描述员工信息,创建一个表,员工编号,员工姓名,员工性别,员工所在部门
CREATE TABLE emp(
	id INT PRIMARY KEY AUTO_INCREMENT, -- 员工编号
	emp_name VARCHAR(10), -- 员工姓名
	emp_gender VARCHAR(5), -- 员工性别
	emp_dept_name VARCHAR(10) -- 员工所在的部门名称
);

-- 插入员工表数据
INSERT INTO emp(emp_name,emp_gender,emp_dept_name)VALUES
('文章','男','开发部'),
('高圆圆','女','测试部'),
('王宝强','男','运维部'),
('姚笛','女','测试部'),
('赵又廷','男','开发部') ,
('马保国','男','运维部') ;

-- 上面存在的问题:员工表中包含员工信息以及部门信息,这种情况不能在数据库中出现
-- 查询出来的数据部门信息冗余,重复性大,优化改进:一张表始终描述一件事情
-- 将员工表拆分出来:将部门信息放在部门表中

-- 创建一张部门表 dept表
CREATE TABLE dept(
    id int PRIMARY KEY AUTO_INCREMENT,--主键自增长约束 部门编号
    dept_name VARCHAR(20) -- 部门名称
);
-- 插入三个部门
INSERT INTO dept(dept_name)VALUES('开发部'),('测试部'),('运维部') ;

-- 创建员工表
CREATE TABLE emp(
id INT PRIMARY KEY AUTO_INCREMENT, -- 员工编号
emp_name VARCHAR(10), -- 员工姓名
emp_gender VARCHAR(5), -- 员工性别
dept_id INT -- 员工的部门id
);

INSERT INTO emp(emp_name,emp_gender,dept_id)
VALUES('文章','男',1),
('高圆圆','女',2),
('王宝强','男',3),
('姚笛','女',2),
('赵又廷','男',1) ,
('马保国','男',3) ;

-- 插入了一条员工数据
INSERT INTO emp(emp_name,emp_gender,dept_id)VALUES('张三丰','男',4);

-- 问题2:员工表中插入一条不存在的部门,两种表没有建立关系
-- 解决:外键约束 foreign key,员工表dept_id和部门表主键id进行关联
-- 外键所在的表-- 从表(员工表)
-- 部门表:主表
-- 员工表:从表

DROP TABLE emp;

CREATE TABLE emp(
id INT PRIMARY KEY AUTO_INCREMENT, -- 员工编号
emp_name VARCHAR(10), -- 员工姓名
emp_gender VARCHAR(5), -- 员工性别
dept_id INT, -- 员工的部门id;
FOREIGN KEY(dept_id)	-- 作用在这个dept_id字段上
REFERENCES dept(id)	-- 关联主表的主键id
);

SELECT * FROM emp;
-- 删除3号部门
-- 加入外键约束:修改和删除是很麻烦,需要先修改或者删除从表数据,然后修改/删除主表数据
-- 将id为3的和id为6的清除
DELETE FROM emp WHERE id=3 OR id=6;
DELETE FROM dept WHERE id=3;

SELECT * FROM dept ;
SELECT * FROM emp ;