[大数据] my sql

开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 大数据 -> my sql -> 正文阅读

[大数据]my sql

数据库

通常我们将数据划分成两类：关系型数据库和非关系型数据库；

关系型数据库：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;

大数据最新文章

实现Kafka至少消费一次

亚马逊云科技：还在苦于ETL？Zero ETL的时代

初探MapReduce

【SpringBoot框架篇】32.基于注解+redis实现

Elasticsearch：如何减少 Elasticsearch 集

Go redis操作

Redis面试题

专题五 Redis高并发场景

基于GBase8s和Calcite的多数据源查询

Redis——底层数据结构原理

加:2021-09-12 13:14:07 更:2021-09-12 13:15:46

360图书馆购物三丰科技阅读网日历万年历 2024年11日历

-2024/11/23 19:40:00-

图片自动播放器
↓图片自动播放器↓

TxT小说阅读器
↓语音阅读,小说下载,古典文学↓

一键清除垃圾
↓轻轻一点,清除系统垃圾↓

图片批量下载器
↓批量下载图片,美女图库↓

网站联系: qq:121756557 email:121756557@qq.com IT数码