学懂MySql数据库
概念：用于存储和管理数据的仓库。
特点：
- 持久化存储数据的。其实数据库就是一个文件系统
- 方便存储和管理数据
- 使用了统一的方式操作数据库 – SQL
SQL语句
1.什么是SQL？
- Structured Query Language：结构化查询语言
- 其实就是定义了操作所有关系型数据库的规则。每一种数据库操作的方式存在不一样的地方，称为“方言”。
- 事实上，常见的关系型数据库SQL语句都是比较相似的，所以你学会了MySQL中的SQL语句，之后去操作比如 Oracle或者其他关系型数据库，也是非常方便的
2.SQL通用语法
- SQL 语句可以单行或多行书写，以分号结尾。
- 可使用空格和缩进来增强语句的可读性。
- MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写。
- 3 种注释
单行注释: – 注释内容 或
#注释内容(mysql 特有)
多行注释: /* 注释 */
3.SQL分类
- DDL(Data Definition Language)数据定义语言：用来定义数据库对象：数据库，表，列等。
- 关键字：create, drop,alter 等
- DML(Data Manipulation Language)数据操作语言：用来对数据库中表的数据进行增删改。
- 关键字：insert, delete, update等
- DQL(Data Query Language)数据查询语言**（重点）**：用来查询数据库中表的记录(数据)。
- 关键字：select, where等
- DCL(Data Control Language)数据控制语言(了解)
- 用来定义数据库的访问权限和安全级别，及创建用户。
- 关键字：GRANT， REVOKE 等
数据库的操作
1.查看当前的数据库
# 查看所有的数据表 SHOW TABLES; # 查看某一个表结构 DESC student;
2.创建数据表
#加了反引号是为了不然SQL语句将数据表当成关键字显示 CREATE TABLE IF NOT EXISTS `user`( name VARCHAR(20), age INT, height DOUBLE );
Sql的数据类型-数字类型
- 我们知道不同的数据会划分为不同的数据类型，在数据库中也是一样：
- MySQL支持的数据类型有：数字类型，日期和时间类型，字符串（字符和字节）类型，空间类型和 JSON数 据类型。
- 数字类型 ：
- MySQL的数字类型有很多： p 整数数字类型：INTEGER，INT，SMALLINT，TINYINT，MEDIUMINT，BIGINT；
- 浮点数字类型：FLOAT，DOUBLE（FLOAT是4个字节，DOUBLE是8个字节）；
- 精确数字类型：DECIMAL，NUMERIC（DECIMAL是NUMERIC的实现形式）。
- 日期类型：
- YEAR以YYYY格式显示值 ：范围 1901到2155，和 0000。
- DATE类型用于具有日期部分但没有时间部分的值： DATE以格式YYYY-MM-DD显示值 ； 支持的范围是 ‘1000-01-01’ 到 ‘9999-12-31’；
- DATETIME类型用于包含日期和时间部分的值： DATETIME以格式’YYYY-MM-DD hh:mm:ss’显示值； 支持的范围是1000-01-01 00:00:00到9999-12-31 23:59:59;
- TIMESTAMP数据类型被用于同时包含日期和时间部分的值： 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’
Sql的数据类型-字符串类型
MySQL的字符串类型表示方式如下：
- CHAR类型在创建表时为固定长度，长度可以是0到255之间的任何值； 在被查询时，会删除后面的空格；
- VARCHAR类型的值是可变长度的字符串，长度可以指定为0到65535之间的值； 在被查询时，不会删除后面的空格；
- BINARY和VARBINARY 类型用于存储二进制字符串，存储的是字节字符串； https://dev.mysql.com/doc/refman/8.0/en/binary-varbinary.html
- BLOB用于存储大的二进制类型；
- TEXT用于存储大的字符串类型。
表约束
1. 主键：PRIMARY KEY
一张表中，我们为了区分每一条记录的唯一性，必须有一个字段是永远不会重复，并且不会为空的，这个字段我们通常会 将它设置为主键：
- 主键是表中唯一的索引；
- 并且必须是NOT NULL的，如果没有设置 NOT NULL，那么MySQL也会隐式的设置为NOT NULL；
- 主键也可以是多列索引，PRIMARY KEY(key_part, …)，我们一般称之为联合主键；
建议：开发中主键字段应该是和业务无关的，尽量不要使用业务字段来作为主键；
2. 唯一：UNIQUE
某些字段在开发中我们希望是唯一的，不会重复的，比如手机号码、身份证号码等，这个字段我们可以使用UNIQUE来约 束：
- 使用UNIQUE约束的字段在表中必须是不同的；
- 对于所有引擎，UNIQUE 索引允许NULL包含的列具有多个值NULL。
3. 不能为空：NOT NULL
某些字段我们要求用户必须插入值，不可以为空，这个时候我们可以使用 NOT NULL 来约束。
**4. 默认值：DEFAULT **
某些字段我们希望在没有设置值时给予一个默认值，这个时候我们可以使用 DEFAULT来完成
5. 自动递增：AUTO-INCREMENT
某些字段我们希望不设置值时可以进行递增，比如用户的id，这个时候可以使用AUTO_INCREMENT来完成
DDL代码实践时间
1.创建一个完整的数据表
#完整的创建表语法 CREATE TABLE IF NOT EXISTS `users`( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) NOT NULL, age INT DEFAULT 0, telPhone VARCHAR(20) DEFAULT '' NOT NULL );
2.修改表
# 1. 修改表名 ALTER TABLE `users` RENAME TO `user`; # 2. 添加一个新的列 ALTER TABLE `user` ADD `updateTime` TIMESTAMP; # 3. 修改字段的名称 ALTER TABLE `user` CHANGE `telPhone` `phoneNum` VARCHAR(30); # 4. 修改字段的类型 ALTER TABLE `user` MODIFY `name` VARCHAR(30); #5.删除某一个字段 ALTER TABLE `user` DROP `age`;
DML代码实践时间
1.创建一张新的表
CREATE TABLE IF NOT EXISTS `products`( `id` INT PRIMARY KEY AUTO_INCREMENT, `title` VARCHAR(20), `description` VARCHAR(200), `price` DOUBLE, `publishTime` DATETIME );
2.插入数据
#插入数据 INSERT INTO products(title,description,price,publishTime) VALUES('iPhone','iPhone只要998',988.88,'2022-5-17'); INSERT INTO `products` (`title`, `description`, `price`, `publishTime`) VALUES ('huawei', 'P40只要888', 888.88, '2022-5-11'
3.删除数据
# 删除数据 # 删除表中所有的数据 DELETE FROM `products`; #删除符合条件的数据 DELETE FROM `products` WHERE `title` = 'huawei';
4.
# 修改数据 # 修改表中所有数据 UPDATE `products` SET `title` = 'iPhone15',`price`=1299.88; # 修改符合条件的数据 UPDATE `products` SET `title` = 'iPhone12',`price`=1299.99 WHERE `title`='huawei';
5.
# 我们希望修改之后可以直接显示最新的更新时间 ALTER TABLE `products` ADD `updateTime` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
DQL代码实践时间
创建products表，准备数据
#创建表结构 CREATE TABLE IF NOT EXISTS `products` ( id INT PRIMARY KEY AUTO_INCREMENT, brand VARCHAR(20), title VARCHAR(100) NOT NULL, price DOUBLE NOT NULL, score DECIMAL(2,1), voteCnt INT, url VARCHAR(100), pid INT );
完成如下表结构创建
注意：这里之后需要自己插入数据
1.基本查询
# 1. 基本查询 SELECT *FROM `products`; # 2. 查询指定的字段 SELECT title,price FROM `products`; # 3. 对字段起一个别名 SELECT title as phoneTitle,price as currentPrice FROM `products`;
2.在开发中，我们希望根据条件来筛选我们的数据，这个时候我们要使用条件查询。
where 的比较运算符
# where 比较运算符 # 条件判断语句 # 案例：查询价格小于1000的手机 SELECT title, price FROM `products` WHERE price < 1000; # 案例二：价格等于999的手机 SELECT * FROM `products` WHERE price = 999; # 案例三：价格不等于999的手机 SELECT * FROM `products` WHERE price != 999; SELECT * FROM `roducts` WHERE price <> 999; # 案例四：查询品牌是华为的手机 SELECT * FROM `products` WHERE brand = '华为';
where的逻辑运算符
# where 逻辑语句 # 案例一：查询1000到2000之间的手机 SELECT * FROM `products` WHERE price > 1000 AND price < 2000; SELECT * FROM `products` WHERE price > 1000 && price < 2000; # BETWEEN AND 包含等于 SELECT * FROM `products` WHERE price BETWEEN 1099 AND 2000; # 案例二：价格在5000以上或者是品牌是华为的手机 SELECT * FROM `products` WHERE price > 5000 || brand = '华为';
3.模糊查询：询使用LIKE关键字，结合两个特殊的符号
- %表示匹配任意个的任意字符；
- _表示匹配一个的任意字符
# 模糊查询 SELECT *FROM `products` WHERE title LIKE '%M%'; SELECT * FROM `products` WHERE title LIKE '%P%'; SELECT * FROM `products` WHERE title LIKE '_P%';
4.对查询结果进行排序
我们希望讲结果按照某种方式进行排序，这个时候使用的是ORDER BY，ORDER BY有两个常用的值：
- ASC：升序排列
- DESC：降序排列
# 对结果进行排序 SELECT *FROM `products` WHERE brand = '华为'||brand = '小米'; SELECT *FROM `products` WHERE brand = '华为'||brand = '小米' ORDER BY price ASC;
5.分页查询
当数据库中的数据非常多时，一次性查询到所有的结果进行显示是不太现实的：
- 在真实开发中，我们都会要求用户传入offset、limit或者page等字段；
- 它们的目的是让我们可以在数据库中进行分页查询；
- 它的用法有[LIMIT {[offset,] row_count | row_count OFFSET offset}]
# 分页查询 SELECT *FROM `products` LIMIT 20 OFFSET 0; SELECT * FROM `products` LIMIT 30 OFFSET 30; # 另外一种写法：offset, row_count SELECT * FROM `products` LIMIT 90, 30;
聚合函数
聚合函数表示对值集合进行操作的组（集合）函数
# 聚合函数的使用 # 求所有手机的价格的总和 SELECT SUM(price) totalPrice FROM `products`; # 求一下华为手机的价格的总和 SELECT SUM(price) FROM `products` WHERE brand = '华为'; # 求华为手机的平均价格 SELECT AVG(price) FROM `products` WHERE brand = '华为'; # 最高手机价格和最低手机价格 SELECT MAX(price) FROM `products`; SELECT MIN(price) FROM `products`; # 求华为手机的个数 SELECT COUNT(*) FROM `products` WHERE brand = '华为'; SELECT COUNT(*) FROM `products` WHERE brand = '苹果'; SELECT COUNT(url) FROM `products` WHERE brand = '苹果'; SELECT COUNT(price) FROM `products`; SELECT COUNT(DISTINCT price) FROM `products`;
认识Group By
事实上聚合函数相当于默认将所有的数据分成了一组，如果我们希望划分多组，这个时候就需要使用Group By。GROUP BY通常和聚合函数一起使用： 表示我们先对数据进行分组，再对每一组数据，进行聚合函数的计算
#GROUP BY SELECT brand, AVG(price), COUNT(*), AVG(score) FROM `products` GROUP BY brand;
*注意：**这里的brand不是随便加的，根据brand分类才能根据brand显示出来
补充：HAVING 的使用
# 在产品表中根据品牌分类，计算商品的个数，平均价格（重命名为avgPrice），平均测评分并且筛选出平均价格大于2000的商品 SELECT brand, AVG(price) avgPrice, COUNT(*), AVG(score) FROM `products` GROUP BY brand HAVING avgPrice > 2000;
