数据库
通常我们将数据划分成两类:关系型数据库和非关系型数据库;
关系型数据库:MySQL、Oracle、DB2、SQL Server、Postgre SQL等;
关系型数据库通常我们会创建很多个二维数据表;
数据表之间相互关联起来,形成一对一、一对多、多对对等关系;
之后可以利用SQL语句在多张表中查询我们所需的数据;
支持事务,对数据的访问更加的安全;
◼ 非关系型数据库:MongoDB、Redis、Memcached、HBse等;
非关系型数据库的英文其实是Not only SQL,也简称为NoSQL;
相当而已非关系型数据库比较简单一些,存储数据也会更加自由(甚至我们可以直接将一个复杂的json对象直接塞入到数据库中);
NoSQL是基于Key-Value的对应关系,并且查询的过程中不需要经过SQL解析,所以性能更高;
NoSQL通常不支持事物,需要在自己的程序中来保证一些原子性的操作;
◼ 如何在开发中选择他们呢?具体的选择会根据不同的项目进行综合的分析,我这里给一点点建议:
目前在公司进行后端开发(Node、Java、Go等),还是以关系型数据库为主;
比较常用的用到非关系型数据库的,在爬取大量的数据进行存储时,会比较常见;
# 设置字符编码 set character_set_client=utf8; set
character_set_connection=utf8; set
character_set_database=utf8; set
character_set_results=utf8; set
character_set_server=utf8;
1.显示数据库
infomation_schema:信息数据库,其中包括MySQL在维护的其他数据库、表、 字段、访问权限等信息; performance_schema:性能数据库,记录着MySQL Server数据库引擎在运行 过程中的一些资源消耗相关的信息;
mysql:用于存储数据库管理者的用户信息、权限信息以及一些日志信息等;
sys:相当于是一个简易版的performance_schema,将性能数据库中的数据汇 总成更容易理解的形式;
2.查询数据库
# 查询数据库
SHOW DATABASES;
3.注意事项
注释:# –
末尾必须加;
如果遇到关键字 可以使用``
必须使用英文的; , ()
4.创建数据库
创建数据库
CREATE DATABASE coderhub;
5.使用数据库(选中)
选中数据库
USE coderhub;
6.创建数据库表
# 创建数据库表
CREATE TABLE `user`(
uname VARCHAR(20),
age INT,
height DOUBLE
);
7.查询表结构
# 查询表结构
DESC user;
8.插入数据
# 添加记录
INSERT INTO `user`(uname,age,height) VALUES('zhangsan',18,188.8);
INSERT INTO `user`(uname,age,height) VALUES('李四',20,170.8);
9.查询表记录
# 查询表记录
SELECT * FROM `user`;
10.SQL语句的分类
DDL:数据定义语言,对数据库或者表进行:创建、删除、修改等操作
DML:数据操作语言,对表进行:添加、删除、修改等操作
DQL:数据库查询语句(重点),从数据库中查询记录
DCL:数据控制语言,对数据库、表格的权限进行相关访问控制操作
11.查询当前被选中的数据库
# 查询当前被选中的数据库
SELECT DATABASE();
12.如果不存在则创建
-- 如果不存在就创建
CREATE DATABASE IF NOT EXISTS bilibili;
13.创建时设置数据库的字符编码
-- 如果不存在就创建
CREATE DATABASE IF NOT EXISTS bilibili DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
14.删除数据库
-- 删除数据库
DROP DATABASE coderhub;
15.不存在就不删除数据库
-- 存在就删除数据库,不存在就不操作
DROP DATABASE IF EXISTS bilibili;
16.修改数据库
-- 修改数据库的字符编码和排序规则
ALTER DATABASE bilibili CHARACTER SET = utf8mb4 COLLATE=utf8mb4_croatian_ci;
17.查询所有的表
-- 查询所有的表
SHOW TABLES;
18.如果表不存在则创建
-- 创建表
CREATE TABLE IF NOT EXISTS `user`(
`name` VARCHAR(20),
age INT,
height DOUBLE
);
19.sql的数据类型-数字类型
整数:INTEGER,INT,SMALLINT,TINYINT,MEDIUMINT,BIGINT;=>number
常用int类型
小数:浮点数字类型:FLOAT,DOUBLE(FLOAT是4个字节,DOUBLE是8个字节)
常用DOUBLE类型
精确数字类型:DECIMAL,NUMERIC(DECIMAL是NUMERIC的实现形式);
20.sql的数据类型-日期类型
DATETIME 时间日期 范围1000-01-01 00:00:00 9999-12-31 23:59:59
TIMESTAMP 时间日期 范围1970-01-01 00:00:01 2038-01-19 03:14:07
21.sql的数据类型-字符串类型
CHAR 固定长度 0-255 查询时会删除后边空格
VARCHAR 可变长度 0-65535 查询是不会删除后边的空格
TEXT 用于存储大的字符串类型
22.主键:PRIMARY KEY
为了确保记录的唯一性,可以设置某一个字段为主键
主键是表中唯一的索引;
并且必须是NOT NULL的,如果没有设置 NOT NULL,那么MySQL也会隐式的设置为NOT NULL;
主键也可以是多字段索引,PRIMARY KEY(key_part, …),我们一般称之为联合主键;
建议:开发中主键字段应该是和业务无关的,尽量不要使用业务字段来作为主键;
23.唯一:UNIQUE
某些字段在开发中我们希望是唯一的,不会重复的,比如手机号码、身份证号码等,这个字段我们可以使用UNIQUE来约 束:
使用UNIQUE约束的字段在表中必须是不同的;
对于所有引擎,UNIQUE 索引允许NULL包含的字段具有多个值NULL
24.不能为空:NOT NULL
某些字段我们要求用户必须插入值,不可以为空,这个时候我们可以使用 NOT NULL 来约束;
25.默认值:DEFAULT
某些字段我们希望在没有设置值时给予一个默认值,这个时候我们可以使用 DEFAULT来完成;
26.自动递增:AUTO_INCREMENT
某些字段我们希望不设置值时可以进行递增,比如用户的id,这个时候可以使用AUTO_INCREMENT来完成;
27.创建一个完整的表
-- 创建完整的表
CREATE TABLE IF NOT EXISTS `user`(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(20) NOT NULL,
age INT DEFAULT 0,
phone VARCHAR(12) DEFAULT '' UNIQUE NOT NULL,
birthday DATETIME,
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
28.删除数据库表
-- 删除数据库表
DROP TABLE `user`;
29.如果存在就删除
-- 表存在就删除
DROP TABLE IF EXISTS `user`;
30.修改表名称
-- 修改表的名称
ALTER TABLE `user` RENAME TO `person`;
31.添加新的字段
-- 添加新的字段
ALTER TABLE `user` ADD `sex` INT DEFAULT 0;
32.删除字段
-- 删除字段
ALTER TABLE `user` DROP `sex`;
33.修改字段
-- 修改字段的名称
ALTER TABLE `user` CHANGE `sex` `gender` INT DEFAULT 0;
34.修改字段的数据类型
-- 修改字段的数据类型
ALTER TABLE `user` MODIFY `gender` CHAR(2);
35.插入记录
-- 插入数据(推荐)
INSERT INTO `user` SET `name`='老王',age=20,phone='17602900101',birthday='2021-08-08 11:07:08';
INSERT INTO `user`(name,age,phone,birthday) VALUES('张三',18,'17602900172','2021-08-08 11:07:08');
INSERT INTO `user`(name,phone,birthday) VALUES('李四','17602900173','2021-08-08 11:07:08');
INSERT INTO `user`(name,age,phone,birthday) VALUES('王五',18,'17602900174','2021-08-08 11:07:08');
INSERT INTO `user`(name,age,phone,birthday) VALUES('赵六',18,'17602900175','2021-08-08 11:07:08');
-- 不推荐
INSERT INTO `user` VALUES(null,'马奇',null,'17602900100','2021-08-08 11:07:08',null);
36.删除记录
-- 删除数据
DELETE FROM `user`;
-- 删除符合条件记录
DELETE FROM `user` WHERE id=8;
37.修改记录
-- 修改全部记录
UPDATE `user` SET age=50;
-- 修改符合条件的记录
UPDATE `user` SET age = 18 where id = 9;
38.基本查询
-- 查询所有记录和字段 *表示所有字段
SELECT * FROM `user`;
-- 查询所有记录的指定字段
SELECT `name`,age,phone FROM `user`;
-- 指定字段的别名
SELECT `name` as '姓名',age as '年龄',phone as '手机号' FROM `user`;
39.条件查询
-- 查询年龄大于18
SELECT name,age FROM `user` WHERE age>18;
-- 查询年龄小于50
SELECT name,age FROM `user` WHERE age<50;
-- 查询年龄大于等于18
SELECT name,age FROM `user` WHERE age>=18;
-- 查询年龄小于等于50
SELECT name,age FROM `user` WHERE age<=50;
-- 查询年龄等于28
SELECT name,age FROM `user` WHERE age=28;
-- 查询年龄不等于28
SELECT name,age FROM `user` WHERE age!=28;
-- 并列查询 and &&
SELECT name,age FROM `user` WHERE age=18 and `name`='李四';
-- 并列条件查询 and &&
SELECT name,age FROM `user` WHERE age=18 && `name`='李四';
-- 或者 or ||
SELECT name,age FROM `user` WHERE age=18 or `name`='老王';
-- 或者 or ||
SELECT name,age FROM `user` WHERE age=18 || `name`='老王';
-- in
SELECT name,age FROM `user` WHERE `name` IN ('老王','李四');
-- 模糊查询 LIKE
-- % 任意数量任意字符
-- _ 一个任意字符
SELECT * FROM `user` WHERE phone LIKE '176%';
SELECT * FROM `user` WHERE phone LIKE '%100';
SELECT * FROM `user` WHERE phone LIKE '%029%';
SELECT * FROM `user` WHERE age LIKE '_0'
-- 排序 ORDER BY ASC 升序 DESC 降序
SELECT * FROM `user` ORDER BY age DESC;
SELECT * FROM `user` ORDER BY age ASC;
-- 分页查询 一页两条 分3页 (page-1)*页码数
-- 第一个数字 数量 第二个数字 开始位置
SELECT * FROM `user` LIMIT 2 OFFSET 0;
SELECT * FROM `user` LIMIT 2 OFFSET 2;
SELECT * FROM `user` LIMIT 2 OFFSET 4;
-- 第一个数字 开始位置 第二个数字 数量
SELECT * FROM `user` LIMIT 0,2;
SELECT * FROM `user` LIMIT 2,2;
SELECT * FROM `user` LIMIT 4,2;
-- 分页查询降序
SELECT * FROM `user` ORDER BY id DESC LIMIT 0,2;
SELECT * FROM `user` ORDER BY id DESC LIMIT 2,2;
SELECT * FROM `user` ORDER BY id DESC LIMIT 4,2;
-- 聚合函数
-- AVG 平均年龄
SELECT AVG(age) as '平均年龄' FROM `user`;
-- MAX 最大年龄
SELECT MAX(age) as '最大年龄' FROM `user`;
-- MIN 最小年龄
SELECT MIN(age) as '最小年龄' FROM `user`;
-- SUM 年龄之和
SELECT SUM(age) as '年龄之和' FROM `user`;
-- COUNT 查询数量
SELECT COUNT(*) as '数量' FROM `user` WHERE age>18;
-- 根据年龄分组
SELECT age FROM `user` GROUP BY age;
SELECT age as '年龄',COUNT(*) as '数量',AVG(age) as '平均数',MAX(age) as '最大年龄',MIN(age) as '最小年龄' FROM `user` GROUP BY age;
-- HAVING 加入筛选条件
SELECT age as '年龄',COUNT(*) as '数量',AVG(age) as '平均数',MAX(age) as '最大年龄',MIN(age) as '最小年龄' FROM `user` GROUP BY age HAVING age>=0;
1.添加外键关联(没有创建表的情况)
-- 创建数据库
CREATE DATABASE IF NOT EXISTS `mall`;
-- 使用数据库
USE `mall`;
-- 创建品牌表
CREATE TABLE IF NOT EXISTS `brand`(
`id` INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(20),
`website` VARCHAR(200),
`worldrank` INT
);
-- 创建商品表
CREATE TABLE IF NOT EXISTS `products`(
`id` INT PRIMARY KEY AUTO_INCREMENT,
`title` VARCHAR(20),
`description` VARCHAR(200),
`price` DOUBLE,
`publishtime` DATETIME,
-- 品牌id
`bid` INT,
-- 外键关联
foreign key (bid) references brand(id)
);
-- 添加商品数据
INSERT INTO `products`(title,description,price,publishtime) VALUES ('华为', '华为P40只要4999', 4999, '2020-11-11');
INSERT INTO `products`(title,description,price,publishtime) VALUES ('小米', '小米10只要3999', 3999, '2020-11-11');
INSERT INTO `products`(title,description,price,publishtime) VALUES ('苹果', 'iPhone12只要5888', 5888, '2020-10-10');
INSERT INTO `products`(title,description,price,publishtime) VALUES ('OPPO', 'OPPO只要998', 998.88, '2020-10-10');
-- 添加品牌数据
insert into brand values(null,'华为','www.huawei.com',1);
insert into brand values(null,'小米','www.mi.com',10);
insert into brand values(null,'苹果','www.apple.com',5);
insert into brand values(null,'oppo','www.opple.com',15);
insert into brand values(null,'google','www.google.com',3);
insert into brand values(null,'京东','www.jd.com',8);
-- 修改商品表中的品牌id字段
update products set bid = 1 where title = '华为';
update products set bid = 2 where title = '小米';
update products set bid = 3 where title = '苹果';
update products set bid = 4 where title = 'OPPO';
SELECT * FROM `brand`;
SELECT * FROM `products`;
DESC `products`;
2.添加外键关联(表已经创建好的情况)
-- 如果表存在就删除
DROP TABLE IF EXISTS `brand`;
DROP TABLE IF EXISTS `products`;
-- 创建品牌表
CREATE TABLE IF NOT EXISTS `brand`(
`id` INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(20),
`website` VARCHAR(200),
`worldrank` INT
);
-- 创建商品表
CREATE TABLE IF NOT EXISTS `products`(
`id` INT PRIMARY KEY AUTO_INCREMENT,
`title` VARCHAR(20),
`description` VARCHAR(200),
`price` DOUBLE,
`publishtime` DATETIME
);
-- 给商品表添加bid字段
ALTER TABLE `products` ADD `bid` INT;
-- 添加外键关联
ALTER TABLE `products` ADD foreign key (bid) references brand(id);
-- 添加商品记录
INSERT INTO `products`(title,description,price,publishtime) VALUES ('华为', '华为P40只要4999', 4999, '2020-11-11');
INSERT INTO `products`(title,description,price,publishtime) VALUES ('小米', '小米10只要3999', 3999, '2020-11-11');
INSERT INTO `products`(title,description,price,publishtime) VALUES ('苹果', 'iPhone12只要5888', 5888, '2020-10-10');
INSERT INTO `products`(title,description,price,publishtime) VALUES ('OPPO', 'OPPO只要998', 998.88, '2020-10-10');
-- 添加品牌数据
insert into brand values(null,'华为','www.huawei.com',1);
insert into brand values(null,'小米','www.mi.com',10);
insert into brand values(null,'苹果','www.apple.com',5);
insert into brand values(null,'oppo','www.opple.com',15);
insert into brand values(null,'google','www.google.com',3);
insert into brand values(null,'京东','www.jd.com',8);
-- 修改商品表中的品牌id字段
update products set bid = 1 where title = '华为';
update products set bid = 2 where title = '小米';
update products set bid = 3 where title = '苹果';
update products set bid = 4 where title = 'OPPO';
3.被外键约束的表(以id作为外键)的记录,不能随便删除,不能随便修改id
-- 因为外键约束不能随便更改或者删除
> 1451 - Cannot delete or update a parent row: a foreign key constraint fails (`mall`.`products`, CONSTRAINT `products_ibfk_1` FOREIGN KEY (`bid`) REFERENCES `brand` (`id`))
4.笛卡尔积(直积)
-- 笛卡尔积
SELECT * FROM `products`,`brand`;
-- 笛卡尔积 筛选
SELECT * FROM `products`,`brand` WHERE `products`.bid = `brand`.id;
5.连接查询
-- 左连接查询
SELECT * FROM `products` LEFT JOIN `brand` ON `products`.bid = `brand`.id;
SELECT * FROM `products` LEFT JOIN `brand` ON `products`.bid = `brand`.id WHERE brand.id is null;
-- 右连接查询
SELECT * FROM `products` RIGHT JOIN `brand` ON `products`.bid = `brand`.id;
SELECT * FROM `products` RIGHT JOIN `brand` ON `products`.bid = `brand`.id WHERE products.id is null;
-- 内连接
SELECT * FROM `products` JOIN `brand` ON `products`.bid = `brand`.id;
-- 全连接
(SELECT * FROM `products` LEFT JOIN `brand` ON `products`.bid = `brand`.id)
UNION
(SELECT * FROM `products` RIGHT JOIN `brand` ON `products`.bid = `brand`.id);
(select * from `products` left join `brand` on `products`.bid = `brand`.id where `brand`.id is null) union
(select * from `products` right join `brand` on `products`.bid = `brand`.id where `products`.id is null);
6.查询多张表的记录(查询所有的学生选择的所有课程)
# 创建学生表
create table if not exists `students`(
id int primary key auto_increment,
name varchar(20) not null,
age int
);
# 创建课程表
create table if not exists `courses`(
id int primary key auto_increment,
name varchar(20) not null,
price double not null
);
# 添加学生记录
insert into students(name,age) values('张三',18);
insert into students(name,age) values('李四',19);
insert into students(name,age) values('王五',20);
insert into students(name,age) values('赵六',23);
insert into students(name,age) values('马奇',25);
# 添加课程记录
insert into courses(name,price) values('JAVA编程基础',50.8);
insert into courses(name,price) values('JavaScript编程基础',70.23);
insert into courses(name,price) values('VueJS开发实战',99.9);
insert into courses(name,price) values('微信小程序开发',88.8);
insert into courses(name,price) values('React开发实战',78.9);
-- 创建关系表(中间表)
CREATE TABLE `students_select_courses`(
id INT PRIMARY KEY AUTO_INCREMENT,
sid INT,
cid INT,
foreign key (sid) references students(id) on update cascade,
foreign key (cid) references courses(id) on update cascade
);
# 查询中间表结构
DESC students_select_courses;
# 张三学习了JAVA编程基础和JavaScript编程基础
INSERT INTO students_select_courses VALUES(null,1,1);
INSERT INTO students_select_courses VALUES(null,1,2);
# 王五学习了VueJS开发实战和微信小程序开发
INSERT INTO students_select_courses VALUES(null,3,3);
INSERT INTO students_select_courses VALUES(null,3,4);
SELECT * FROM students;
SELECT * FROM courses;
SELECT * FROM students_select_courses;
# 查询所有的学生选择的所有课程
SELECT
students.id AS '编号',
students.name AS '姓名',
courses.name AS '课程名称',
courses.price AS '价格'
FROM
students
JOIN students_select_courses ON students.id = students_select_courses.sid
JOIN courses ON courses.id = students_select_courses.cid;
7.查询单个学生的课程
# 张三学了哪些课程
SELECT
students.id AS '编号',
students.name AS '姓名',
courses.name AS '课程名称',
courses.price AS '价格'
FROM
students
JOIN students_select_courses ON students.id = students_select_courses.sid
JOIN courses ON courses.id = students_select_courses.cid
WHERE
students.name = '张三';
#王五同学选择了哪些课程(注意,这里必须用左连接,事实上上面也应该使用的是左连接)
SELECT
students.id AS '编号',
students.name AS '姓名',
courses.name AS '课程名称',
courses.price AS '价格'
FROM
students
LEFT JOIN students_select_courses ON students.id = students_select_courses.sid
LEFT JOIN courses ON courses.id = students_select_courses.cid
WHERE
students.id = 3;
8.查询哪些学生没有选择和哪些课程没有被选择
# 哪些学生是没有选课的
SELECT
students.id AS '编号',
students.name AS '姓名'
FROM
students
LEFT JOIN students_select_courses ON students.id = students_select_courses.sid
LEFT JOIN courses ON courses.id = students_select_courses.cid
WHERE
courses.id is null;
# 哪些课程没有被选择
SELECT
courses.id AS '编号',
courses.name AS '姓名'
FROM
students
RIGHT JOIN students_select_courses ON students.id = students_select_courses.sid
RIGHT JOIN courses ON courses.id = students_select_courses.cid
WHERE
students.id is null;