初学MySql

最新推荐文章于 2024-10-15 09:42:46 发布

zhanpengpeg

最新推荐文章于 2024-10-15 09:42:46 发布

阅读量961

点赞数 7

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/zhanpengpeg/article/details/138978389

版权

数据库

分为关系型数据库和非关系型数据库，MySql为一种较为常用的关系型数据库。

MySql地址：https://dev.mysql.com/downloads/mysql/

下载安装后并配置环境变量（此处安装步骤和环境变量不在阐述），在cmd窗口通过mysql --version查看是否成功。

基本使用

// cmd
mysql -uroot -pMysql123. // -u是user缩写，-p是password缩写
show databases // 查看所有数据库
// 默认会有4个数据库，分别为
// information_schema; performance_schema; mysql; sys
create database music_db; // sql语句结束处要加分号
use music_db
create table singer(
    name varchar(20),
    age int,
    height double
);
insert into singer (name, age, height) values ('taylor swifter', 18, 1.88);

MySQL默认的数据库

information_schema：信息数据库，其中包括MySQL在维护的其他数据库、表、列、访问权限等信息；

performance_schema：性能数据库，记录着MySQL Server数据库引擎在运行过程中的一些资源消耗相关的信息；

mysql：用于存储数据库管理者的用户信息、权限信息以及一些日志信息等；

sys：相当于是一个简易版的performance_schema，将性能数据库中的数据汇总成更容易理解的形式；

MySql的常见GUI工具：Navicat、SQLYog(免费)、TablePlus等。

SQL

SQL全称Structured Query Language，称之为结构化查询语言，简称SQL；

使用SQL编写出来的语句，就称之为SQL语句；

SQL语句可以用于对数据库进行操作；

SQL一些常用规范：

通常关键字使用大写的，比如CREATE、TABLE、SHOW等等；

一条语句结束后，需要以分号（;）结尾；

如果遇到关键字作为表明或者字段名称，可以使用反引号（``）包裹；

常见的SQL语句我们可以分成四类：

DDL（Data Definition Language）：数据定义语言；

可以通过DDL语句对数据库或者表进行：创建、删除、修改等操作；

-- 查看当前所有数据库
SHOW DATABASES;
-- 使用某个数据库
USE music_db;
-- 查看目前选中的是哪个数据库（即正在使用数据库）
SELECT DATABASE();
-- 创建一个新的数据库
-- CREATE DATABASE test_db; 不安全，重复创建会报错
CREATE DATABASE IF NOT EXISTS test_db;
-- 删除一个数据库
-- DROP DATABASE test_db; 不安全，删除不存在数据库会报错
DROP DATABASE IF EXISTS test_db;
-- 修改数据库
-- 修改数据库的字符集和排序规则，一般使用默认的，不去修改
ALTER DATABASE bilibili CHARACTER SET = utf8 COLLATE = utf8_unicode_ci;

-- 查看当前数据库中有哪些表
SHOW TABLES;
-- 查看有一张表的表结构
DESC t_singer;
-- 创建一张新表
CREATE TABLE IF NOT EXISTS users(
    name VARCHAR(20),
    age INT,
    height DOUBLE
)
-- 修改表
-- 修改表名
ALTER TABLE `users` RENAME TO `t_users`;
-- 表添加字段
ALTER TABLE `t_users` ADD createTime TIMESTAMP;
-- 表修改字段名（如果字段已经有值，修改前后值类型不一至会报错）
ALTER TABLE `t_users` CHANGE createTime createAt DATETIME;
-- 表删除字段
ALTER TABLE `t_users` DROP createAt;
-- 表修改字段类型
ALTER TABLE `t_users` MODIFY id BIGINT;

-- 删除表
DROP TABLE IF EXISTS `users`

DML（Data Manipulation Language）：数据操作语言；

可以通过DML语句对表进行：添加、删除、修改等操作；

-- 插入
INSERT INTO `t_products` (title, description, price, publishTime) VALUES ('iphone100', 'latest smart phone', 6000, '2030-12-10');
-- 删除（全部删除）
DELETE FROM `t_products`;
-- 删除（删除指定记录）
DELETE FROM `t_products` WHERE id = 4;
-- 修改（全部修改）
UPDATE `t_products` SET price = 10000;
-- 修改（修改指定记录）
UPDATE `t_products` SET price = 10000, title = 'iphone100 pro' WHERE id = 5;
-- 当修改一条数据时用最新时间戳记录
ALTER TABLE `t_products` ADD `updateTime` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

DQL（Data Query Language）：数据查询语言；

可以通过DQL从数据库中查询记录；（重点）

// 在node中插入数据到数据库表
// 首先下载mysql2库
const mysql = require('mysql2')

const connection = mysql.createConnection({
    host: 'localhost',
    port: 3306,
    user: 'root',
    password: 'rootPwd123.',
    database: 't_products'
});

const statement = `INSERT INTO products SET ?;`
const phoneJson = require('./phone.json');

for (let phone of phoneJson) {
    connection.query(statement, phone);
}

-- 查询表中所有数据的所有字段
SELECT * FROM `t_products`;
-- 查询指定字段
SELECT id, title, brand, price FROM `t_products`;
-- 查询指定字段并设置别名
SELECT id AS phoneId, title AS phoneTitle, price FROM `t_products`;

SELECT * FROM `t_products` WHERE brand = '小米';
SELECT * FROM `t_products` WHERE brand != '华为';
-- 与逻辑
SELECT * FROM `t_products` WHERE brand = '小米' AND price < 2000;
SELECT * FROM `t_products` WHERE brand = '华为' && price > 5000;
-- 或逻辑
SELECT * FROM `t_products` WHERE brand = '红米' OR price < 1000;
SELECT * FROM `t_products` WHERE brand = '苹果' || price > 7000;
-- 区间
SELECT * FROM `t_products` WHERE price >= 2000 && price <= 3000;
SELECT * FROM `t_products` WHERE price BETWEEN 2000 AND 3000;
-- 枚举方式
SELECT * FROM `t_products` WHERE brand = '小米' OR brand = '红米';
SELECT * FROM `t_products` WHERE brand IN ('小米', '红米');

-- 模糊查询，使用LIKE关键字，结合%和_两个特殊字符
SELECT * FROM `t_products` WHERE title LIKE 'v%'; -- 可以查出vivoS20等
SELECT * FROM `t_products` WHERE title LIKE '%v%'; -- 可以查出vivoS20、华为nova9等
SELECT * FROM `t_products` WHERE title LIKE '__M%'; -- 可以查出华为Mate30等

-- 结果升降序处理
SELECT * FROM `t_products`
  WHERE price < 1000
  ORDER BY score DESC; -- 按score降序
SELECT * FROM `t_products`
  WHERE price < 1000
  ORDER BY score ASC; -- 按score升序

-- 分页查询
SELECT * FROM `t_products` LIMIT 20; -- 只拿查询到的前20条结果
SELECT * FROM `t_products` LIMIT 20 OFFSET 40; -- 查询到的第40条后的20条结果
SELECT * FROM `t_products` LIMIT 40， 20; -- 另一种写法

DCL（Data Control Language）：数据控制语言；

对数据库、表格的权限进行相关访问控制操作；

SQL的数据类型：

数字类型，日期和时间类型，字符串（字符和字节）类型，空间类型和 JSON数据类型。

数字类型：

1：整数数字类型：INTEGER，INT，SMALLINT，TINYINT，MEDIUMINT，BIGINT；

2：浮点数字类型：FLOAT，DOUBLE（FLOAT是4个字节，DOUBLE是8个字节）；

3：精确数字类型：DECIMAL，NUMERIC（DECIMAL是NUMERIC的实现形式）；

日期类型：

1：YEAR，以YYYY格式显示值，范围 1901到2155，和 0000。

2：DATE，以格式YYYY-MM-DD显示值，用于具有日期部分但没有时间部分的值：支持的范围是 '1000-01-01' 到 '9999-12-31'；

3：DATETIME，以格式'YYYY-MM-DD hh:mm:ss'显示值；用于包含日期和时间部分的值：支持的范围是1000-01-01 00:00:00到9999-12-31 23:59:59;

4：TIMESTAMP，以格式'YYYY-MM-DD hh:mm:ss'显示值；用于同时包含日期和时间部分的值：但是它的范围是UTC的时间范围：'1970-01-01 00:00:01'到'2038-01-19 03:14:07';

此外，DATETIME或TIMESTAMP 值可以包括在高达微秒（6位）精度的后小数秒一部分，比如DATETIME表示的范围可以是'1000-01-01 00:00:00.000000'到'9999-12-31 23:59:59.999999';

字符串类型：

1：CHAR，在创建表时为固定长度，长度可以是0到255之间的任何值；在被查询时，会删除后面的空格；

2：VARCHAR，是可变长度的字符串，长度可以指定为0到65535之间的值；在被查询时，不会删除后面的空格；

3：BINARY和VARBINARY 类型用于存储二进制字符串，存储的是字节字符串；详见地址https://dev.mysql.com/doc/refman/8.0/en/binary-varbinary.html

4：BLOB，用于存储大的二进制类型；

5：TEXT，用于存储大的字符串类型；

表约束

主键（PRIMARY KEY），是表中唯一的索引，MySQL会隐式的设置为NOT NULL；

唯一（UNIQUE），唯一不重复的字段，null情况除外；

不能为空（NOT NULL），要求必须插入值；

默认值（DEFAULT），在没有插入值值时给予一个默认值；

自动递增（AUTO_INCREMENT），不设置值也可以自动进行递增；

外键约束等...

CREATE TABLE IF NOT EXISTS `users`(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20) UNIQUE NOT NULL,
    level INT DEFAULT 0,
    score INT DEFAULT(60),
    telPhone VARCHAR(20) UNIQUE
)

高级特性

聚合函数：对值的集合进行操作的组（集合）函数。

-- 计算平均值
SELECT AVG(price) FROM `t_products` WHERE `brand` = '小米';
SELECT AVG(score) AS miAvgScore FROM `t_products` WHERE `brand` = '小米';
SELECT ROUND(AVG(price), 2) FROM `t_products` WHERE `brand` = '小米'; -- 保留两位小数
-- 计算最大值
SELECT MAX(price) FROM `t_products` WHERE `brand` = '小米';
-- 计算最小值
SELECT MIN(price) FROM `t_products` WHERE `brand` = '小米';
-- 计算总和
SELECT SUM(price) FROM `t_products` WHERE `brand` = '小米';
-- 计算条目数量
SELECT COUNT(*) FROM `t_products`;
SELECT COUNT(*) FROM `t_products` WHERE `brand` = '小米';

值分组：GROUP BY

SELECT brand, AVG(price) FROM `t_products` GROUP BY brand;
-- 在GROUP BY分组语句中要用HAVING而不是WHERE
SELECT brand, AVG(price) AS avgPrice 
FROM `t_products` 
GROUP BY brand 
HAVING avgPrice > 5;

外键约束：字段的值必须是受约束表关联字段已经有的值

-- 假设已有一张singer表如下
CREATE TABLE IF NOT EXISTS `t_singer`(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    website VARCHAR(100),
    worldRank INT
);

id	name	website	worldRank
1	小米	www.xiaomi.com	3
2	华为	www.huawei.com	9
3	苹果	www.iphone.com	1

-- 新建表时就添加外键约束方式
CREATE TABLE IF NOT EXISTS `t_song`(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    duration INT,
    singer_id INT,
    FOREIGN KEY (singer_id) REFERENCES t_singer(id)
);

-- 已经存在表的情况下添加外键约束（通过新增字段再添加外键约束方式）
ALTER TABLE `t_song` ADD `singer_id` INT;
ALTER TABLE `t_song` ADD FOREIGN KEY (singer_id) REFERENCES brand(id);

字段被外键约束的情况下，在更新该字段的值时，如果新值不是外键约束中的值，则默认不允许修改并提示报错。这和on update和on delete属性有关。

RESTRICT（默认属性）：当更新或删除某个记录时，会检查该记录是否有关联的外键记录，有的话会报错的，不允许更新或删除；

NO ACTION：和RESTRICT是一致的，是在SQL标准中定义的；

CASCADE：当更新或删除某个记录时，会检查该记录是否有关联的外键记录，有的话：

更新：那么会更新对应的记录；

删除：那么关联的整条记录会被一起删除掉；

SET NULL：当更新或删除某个记录时，会检查该记录是否有关联的外键记录，有的话，将对应的值设置为NULL；

所以要修改外键的更新和删除策略，可以直接更新外键测更新和删除策略，但是这非常麻烦，建议先删除原来的外键，再新建一次这个外键并加上外键的更新策略。

-- 显示创建该表时的完整sql命令
SHOW CREATE TABLE `t_products`;
-- 删除原来的外键
-- 默认外键名可能是如下，也也能是别的
ALTER TABLE `t_products` DROP FOREIGN KEY products_ibfk_1;
-- 再次新建外键并附加更新和删除策略
ALTER TABLE `t_products`
    ADD FOREIGN KEY (brand_id)
    REFERENCES `t_brand`(id)
    ON UPDATE CASCADE
    ON DELETE CASCADE;

最基本的多表查询，更精确的结果要通过SQL JOIN实现

-- 默认会将两张表各自的每条数据都两两组合一次得到结果
-- 比如a表有100条数据，b表有30条数据，那么得到的结果就有3000条
SELECT * FROM `t_products`, `t_brand`;
-- 按条件筛选结果
SELECT * FROM `t_products`, `t_brand` WHERE `t_products`.brand_id = `t_brand`.id;

表连接

表连接主要有4种：以左表为主的左连接（LEFT [OUTER] JOIN）；以右表为主的右连接（RIGHT [OUTER] JOIN）；得到左右表的交集的内连接（[CROSS/INNER] JOIN）；SQL标准但是MySql不支持的全连接（FELL JOIN）。

-- 左连接
SELECT * FROM `t_products` 
    LEFT JOIN `t_brand` 
    ON `t_products`.brand_id = `t_brand`.id;

-- 右连接
SELECT * FROM `t_products` 
    RIGHT JOIN `t_brand` 
    ON `t_products`.brand_id = `t_brand`.id;

-- 内连接
SELECT * FROM `t_products` 
    JOIN `t_brand` 
    ON `t_products`.brand_id = `t_brand`.id;
-- 内连接的结果和下面不使用表连接直接通过WHERE得到的结果一样，但是他们含义不一样
SELECT * FROM `t_products`, `t_brand` WHERE `t_products`.brand_id = `t_brand`.id;

-- 全连接，由于MySql不支持，通过联合左连接和右连接实现
(SELECT * FROM `t_products` LEFT JOIN `t_brand` ON `t_products`.brand_id = `t_brand`.id)
UNION
(SELECT * FROM `t_products` RIGHT JOIN `t_brand` ON `t_products`.brand_id = `t_brand`.id);

当开发种遇到两张表存在多对多关系时，创建一张记录两张表中的数据关系。

-- 学校中学生和课程案例。一个学生可以选多门课，也可以一门课都不选

-- 创建学生表
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('john', 18);
INSERT INTO `students` (name, age) VALUES('tom', 22);
INSERT INTO `students` (name, age) VALUES('jack', 25);
INSERT INTO `students` (name, age) VALUES('lucy', 16);

INSERT INTO `courses` (name, price) VALUES ('英语', 100);
INSERT INTO `courses` (name, price) VALUES ('语文', 80);
INSERT INTO `courses` (name, price) VALUES ('数学', 100);
INSERT INTO `courses` (name, price) VALUES ('历史', 60);
INSERT INTO `courses` (name, price) VALUES ('生物', 75);

-- 创建关系表
CREATE TABLE IF NOT EXISTS `students_select_courses`(
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    FOREIGN KEY (student_id) REFERENCES students(id) ON UPDATE CASCADE,
    FOREIGN KEY (course_id) REFERENCES courses(id) ON UPDATE CASCADE
);
-- 模拟学生选课情况，在关系表中创建记录
INSERT INTO `students_select_courses` (student_id, course_id) VALUES (1, 1);
INSERT INTO `students_select_courses` (student_id, course_id) VALUES (1, 3);
INSERT INTO `students_select_courses` (student_id, course_id) VALUES (2, 2);
INSERT INTO `students_select_courses` (student_id, course_id) VALUES (3, 3);
INSERT INTO `students_select_courses` (student_id, course_id) VALUES (3, 4);

-- 查询
-- 查询所有的学生选择的所有课程
-- AS可以省略，只留一个空格代替即可
SELECT 
    stu.id studentId, stu.name studentName, cs.id courseId, cs.name courseName, cs.price coursePrice
FROM `students` stu
JOIN `students_select_courses` ssc
ON stu.id = ssc.student_id
JOIN `courses` cs 
ON ssc.course_id = cs.id;

-- 查询所有的学生选课情况
SELECT 
    stu.id studentId, stu.name studentName, cs.id courseId, cs.name courseName, cs.price coursePrice
FROM `students` stu
LEFT JOIN `students_select_courses` ssc
ON stu.id = ssc.student_id
LEFT JOIN `courses` cs 
ON ssc.course_id = cs.id;

-- lucy同学选择了哪些课程（这里使用左连接是因为如果查询的同学没有选课，内连接连同学信息都不会展示）
SELECT 
    stu.id studentId, stu.name studentName, cs.id courseId, cs.name courseName, cs.price coursePrice
FROM `students` stu
LEFT JOIN `students_select_courses` ssc
ON stu.id = ssc.student_id
LEFT JOIN `courses` cs 
ON ssc.course_id = cs.id
WHERE stu.id = 4;

-- 查询哪些学生是没有选课的
SELECT 
    stu.id studentId, stu.name studentName, cs.id courseId, cs.name courseName, cs.price coursePrice
FROM `students` stu
LEFT JOIN `students_select_courses` ssc
ON stu.id = ssc.student_id
LEFT JOIN `courses` cs
ON ssc.course_id = cs.id
WHERE cs.id IS NULL;

-- 查询哪些课程没有被学生选择
SELECT 
stu.id studentId, stu.name studentName, cs.id courseId, cs.name courseName, cs.price coursePrice
FROM `students` stu
RIGHT JOIN `students_select_courses` ssc
ON stu.id = ssc.student_id
RIGHT JOIN `courses` cs
ON ssc.course_id = cs.id
WHERE stu.id IS NULL;

因为本人是前端，后续还会继续学习MySQL在nodejs的使用，大家一起加油鸭~