一.前言
随着文件中存储的内容越来越多,在文件中修改和查找某些数据已经变得非常困难了,所以人们发明了一种专门的软件来管理存储的数据,这些数据依照一定格式保存,通过这个软件可以方便的对数据进行增删改查操作,从而极大的提升了数据管理效率,人们就把这个管理数据的软件叫做数据库管理系统(英文:Database Management System
,简称:DBMS
)
MySQL是一种开源的关系型数据库,以其高性能、稳定性和可靠性而闻名,由于其体积小、速度快,尤其是开放源码这一特点,并被广泛应用于各种规模的应用程序和网站
今天我就不给大家介绍如何安装了,而是说一些理论知识和语法,大家可以先收藏,毕竟收藏了就是学完了。
二.SQL简介
对数据库进行查询和修改操作的语言叫做 SQL(Structured Query Language,结构化查询语言)。SQL是专为数据库而建立的操作命令集,是一种功能齐全的数据库语言。
SQL 是一种数据库查询和程序设计语言,用于存取数据以及查询、更新和管理关系数据库系统。与其他程序设计语言(如 C语言、Java 等)不同的是,SQL 由很少的关键字组成,每个 SQL 语句通过一个或多个关键字构成。
在使用它时,只需要发出“做什么”的命令,“怎么做”是不用使用者考虑的。SQL功能强大、简单易学、使用方便,已经成为了数据库操作的基础,并且现在几乎所有的数据库(Oracle、DB2、Sybase、SQL Server )均支持sql。
SQL的规范:
在数据库系统中,SQL语句不区分大小写(建议用大写) 。但字符串常量区分大小写。建议命令大写,表名库名小写;
SQL语句可单行或多行书写,以“;”结尾。关键词不能跨多行或简写。
用空格和缩进以及折行来提高语句的可读性。子句通常位于独立行,便于编辑,提高可读性。 SELECT column1, column2, column3
FROM table1
WHERE column1 = 'value1'
AND column2 = 'value2'
AND column3 = 'value3'注释 -- 单行注释
/*
多行注释
*/
三.数据库操作
我们安装好并且配置好密码之后,执行mysql -u root -p,然后输入密码就能连接到你的数据库了
-- 1.查看数据库
show databases; -- 查看所有数据库
SHOW DATABASES LIKE '%test%';-- 查看名字中包含 test 的数据库
show create database db_name; -- 查看数据库的创建方式
-- 2.创建数据库(在磁盘上创建一个对应的文件夹)
create database [if not exists] db_name [character set xxx]-- 3.修改数据库
alter database db_name [character set xxx]-- 4.删除数据库
drop database [if exists] db_name;
-- 5.使用数据库
use db_name; -- 切换数据库 注意:进入到某个数据库后没办法再退回之前状态,但可以通过use进行切换
select database(); -- 查看当前使用的数据库
-- 数据库备份
mysqldump -u username -p password database_name > backup.sql
数据库的操作的话没什么好说的,我们在开发一般都是连接创建一个数据库,然后连接上,在里面创建表来进行操作,这里给大家演示一下就好
我们就把大概的都展示了,接下来我会用这个数据库来进行表的操作
使用 DROP DATABASE 命令时要非常谨慎,在执行该命令后,MySQL 不会给出任何提示确认信息。
DROP DATABASE 删除数据库后,数据库中存储的所有数据表和数据也将一同被删除,而且不能恢复。因此最好在删除数据库之前先将数据库进行备份。
四.数据表操作
数据表是数据库的重要组成部分,每一个数据库都是由若干个数据表组成的。比如,在电脑中一个文件夹有若干excel文件。这里的文件夹就相当于数据库,excel文件就相当于数据表。
MySQL 的数据类型有大概可以分为 5 种,分别是整数类型、浮点数类型、日期和时间类型、字符串类型、二进制类型等。
4.1 数据库数据类型
这里先给出截图给大家
在 MySQL 中,布尔类型被称为 BOOLEAN
或 BOOL
类型。BOOLEAN
类型用于存储布尔值,即 TRUE
(真)或 FALSE
(假)。
MySQL 中的布尔类型可以有以下几种表示方式:
-
整数类型:布尔类型可以表示为整数类型,其中
0
表示FALSE
,非零整数表示TRUE
。常见的表示TRUE
的整数值为1
。 -
字符串类型:布尔类型也可以表示为字符串类型,其中
'0'
表示FALSE
,而'1'
表示TRUE
。这种表示方式更贴近人类可读的布尔值。
需要注意的是,尽管 MySQL 支持布尔类型的不同表示方式,但它并没有专门的存储布尔值的数据类型。在实际使用中,你可以选择使用 TINYINT(1)
或 VARCHAR(1)
等其他数据类型来存储布尔值,但约定使用 0
和 1
或 '0'
和 '1'
来表示布尔值。
4.2 创建数据表
-- 语法
CREATE TABLE tab_name(
field1 type [约束条件],
field2 type,
...
fieldn type -- 一定不要加逗号,否则报错!
)[character set utf8];show tables; -- 查看所有的创建的表信息
这里我现在用pycham里面的数据库管理工具和大家演示实例
CREATE TABLE student(
name varchar(20),
gender bit,
age int,
birth date,
gpa double(8,2) unsigned-- 平均绩点
)character set=utf8;
show tables;
这样就成功创建了 ,然后可以看到生成了一个student表
4.3 约束
约束是一种限制,它通过限制表中的数据,来确保数据的完整性和唯一性。使用约束来限定表中的数据很多情况下是很有必要的。在 MySQL 中,约束是指对表中数据的一种约束,能够帮助数据库管理员更好地管理数据库,并且能够确保数据库中数据的正确性和有效性。例如,在数据表中存放年龄的值时,如果存入 200、300 这些无效的值就毫无意义了。因此,使用约束来限定表中的数据范围是很有必要的。
添加记录:
INSERT <表名> 字段1,...字段n VALUES (值1,...值n) ;
4.3.1 非空约束
非空约束用来约束表中的字段不能为空。比如,在用户信息表中,如果不添加用户名,那么这条用户信息就是无效的,这时就可以为用户名字段设置非空约束。
创建表时可以使用NOT NULL
关键字设置非空约束
CREATE TABLE user(
name VARCHAR(22),
age int NOT NULL
);
insert user (name,age) values ('往日情怀酿作酒',19);
4.3.2 唯一约束
唯一约束(Unique Key)是指所有记录中字段的值不能重复出现。例如,为name字段加上唯一性约束后,每条记录的name值都是唯一的,不能出现重复的情况。
创建表时可以使用UNIQUE
关键字设置非空约束
例如,在用户信息表中,要避免表中的用户名重名,就可以把用户名列设置为唯一约束。
CREATE TABLE user(
name VARCHAR(22) UNIQUE,
age int
);
insert user (name) values ("yuan");
4.3.3 默认值约束
默认值约束用来约束当数据表中某个字段不输入值时,自动为其添加一个已经设置好的值。
创建表时可以使用DEFAULT
关键字设置默认值约束
CREATE TABLE user(
name VARCHAR(22),
gender bit default 1
);
insert user (name) values ("yuan");
4.3.4 主键约束
主键约束是使用最频繁的约束。在设计数据表时,一般情况下,都会要求表中设置一个主键。主键是表的一个特殊字段,该字段能唯一标识该表中的每条信息。
-- 没有唯一能标识该表中的每条记录的字段值
CREATE TABLE user(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);
insert user (name) values ("yuan");
select * from user;
一张表中最多只能有一个主键
主键类型不一定必须是整型
表中如果没有设置主键,默认设置NOT NULL和UNIQUE的字段为主键;此外,表中如果有多个NOT NULL和UNIQUE的字段,则按顺序将第一个设置NOT NULL和UNIQUE的字段设为主键。所以主键一定是非空且唯一,但非空且唯一的字段不一定是主键。
4.4 查看表
desc employee; -- 查看表结构,等同于show columns from tab_name
show tables -- 查看当前数据库中的所有的表
show create table tab_name -- 查看当前数据库表建表语句
4.5 删除表
DROP TABLE [IF EXISTS] 表名1 [ ,表名2, 表名3 ...]
4.6 修改表结构
修改表名和编码
修改表名
ALTER TABLE <旧表名> RENAME [TO] <新表名>;
修该表所用的字符集
ALTER TABLE 表名 [DEFAULT] CHARACTER SET <字符集名>
修改表字段
增加列(字段)
ALTER TABLE <表名> ADD <新字段名><数据类型>[约束条件][first|after 字段名];增加多个字段
alter table users2 add addr varchar(20), add age int first, add birth varchar(20) after name;删除某字段
ALTER TABLE <表名> DROP <字段名>;修改某字段类型
ALTER TABLE <表名> MODIFY <字段名> <数据类型> [完整性约束条件][first|after 字段名];修改某字段名
ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型> [完整性约束条件][first|after 字段名];
五.表记录操作
5.1 添加记录
INSERT 语句有两种语法形式,分别是 INSERT…VALUES 语句和 INSERT…SET 语句。
5.1.1 INSERT…VALUES语句
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);
INSERT [INTO] <表名> [ <列名1> [ , … <列名n>] ] VALUES (值1…,值n),
(值1…,值n),
...
(值1…,值n);
-- 用单条 INSERT 语句处理多个插入要比使用多条 INSERT 语句更快。
INSERT [INTO] <表名> [ <列名1> [ , … <列名n>] ] VALUES (值1…,值n),
(值1…,值n),
...
(值1…,值n);
-- 用单条 INSERT 语句处理多个插入要比使用多条 INSERT 语句更快。
案例
CREATE TABLE student
(
id int primary key auto_increment,
name varchar(20) not null,
gender bit default 1,
age int,
birth date
)character set=utf8;
-- 单行插入
INSERT
student (name,gender,age,birth) VALUES
("yuan",1,18,"2002-11-12"),
-- 多行批量插入
INSERT student (name,gender,age,birth) VALUES
("张三",1,22,"2000-12-12"),
("李四",1,32,"1990-12-12"),
("王五",0,42,"1980-06-06");
5.1.2 INSERT…SET语句
INSERT INTO table_name
SET column1 = value1, column2 = value2, ...;
此语句用于直接给表中的某些列指定对应的列值,即要插入的数据的列名在 SET 子句中指定。对于未指定的列,列值会指定为该列的默认值。这个语句平时用的不多,了解即可
5.2 查询语句
这个在数据库操作中是最为重要的部分,无论是后面的删除,修改值,都是基于查询来的,这里我会具体和大家讲
标准语法:
-- 查询语法:
SELECT *|field1,filed2 ... FROM tab_name
WHERE 条件
GROUP BY field
HAVING 筛选
ORDER BY field
LIMIT 限制条数
-- Mysql在执行sql语句时的执行顺序:
-- from where select group by having order by
准备数据:
CREATE TABLE emp( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20), gender ENUM("male","female","other"), age TINYINT, dep VARCHAR(20), province VARCHAR(20), salary DOUBLE(7,2), birthday DATE ) CHARACTER SET=utf8; INSERT INTO emp (name, gender, age, dep, province, salary, birthday) VALUES ("George", "male", 24, "教学部", "河北省", 6000, '1999-02-18'), ("danae", "male", 32, "运营部", "北京", 12000, '1992-07-12'), ("Sera", "male", 38, "运营部", "河北省", 7000, '1986-11-05'), ("Echo", "male", 19, "运营部", "河北省", 9000, '2002-03-28'), ("Abel", "female", 24, "销售部", "北京", 9000, '1999-09-10'), ("John", "male", 28, "教学部", "山东省", 8000, '1993-06-15'), ("Alice", "female", 32, "运营部", "北京", 10000, '1990-12-25'), ("Bob", "male", 24, "教学部", "河北省", 9000, '1999-08-03'), ("Cather", "female", 28, "销售部", "山东省", 6000, '1993-04-20'), ("David", "male", 34, "销售部", "山东省", 12000, '1987-09-01'), ("Emily", "female", 22, "教学部", "北京", 7000, '1999-01-08'), ("Frank", "male", 24, "教学部", "河北省", 9000, '1999-07-17'), ("Grace", "female", 32, "运营部", "北京", 8000, '1990-05-30'), ("Henry", "male", 38, "运营部", "河北省", 9000, '1984-03-12'), ("Ivy", "female", 19, "运营部", "河北省", 9000, '2002-08-22'), ("Jack", "male", 24, "销售部", "北京", 8000, '1999-10-05'), ("Kelly", "female", 28, "教学部", "山东省", 10000, '1993-02-28'), ("Leo", "male", 32, "运营部", "北京", 6000, '1990-11-11'), ("Megan", "female", 24, "教学部", "河北省", 12000, '1999-06-03'), ("Nick", "male", 34, "销售部", "山东省", 7000, '1987-07-08'), ("Olivia", "female", 22, "销售部", "山东省", 9000, '1999-03-18'), ("Peter", "male", 24, "教学部", "北京", 8000, '1999-12-01'), ("Queen", "female", 32, "运营部", "河北省", 9000, '1990-09-15'), ("Ryan", "male", 38, "运营部", "北京", 8000, '1984-11-20'), ("Sandy", "female", 19, "运营部", "河北省", 10000, '2002-05-07'), ("Tom", "male", 24, "销售部", "北京", 7000, '1999-04-27'), ("Uma", "female", 28, "教学部", "山东省", 9000, '1993-08-14'), ("Victor", "male", 32, "运营部", "北京", 6000, '1990-02-05'), ("Wendy", "female", 24, "教学部", "河北省", 12000, '1999-07-23'), ("Xander", "male", 34, "销售部", "山东省", 7000, '1987-12-16'), ("Yvonne", "female", 22, "销售部", "山东省", 9000, '1999-01-30'), ("Zack", "male", 24, "教学部", "北京", 8000, '1999-10-13');
我们把这些数据都插入进去,接下来就来执行我们的操作了
5.2.1 查询字段(select)
SELECT * FROM emp;
SELECT name,dep,salary FROM emp;
这样我们就能看到查询的内容了,通常我们查询还需要进行筛选,接下来我们都讲筛选语句
5.2.2 where语句
-- where字句中可以使用:
--比较运算符:
> < >= <= <> != =
between 10 and 100 值在10到100之间
in(10,20,30) 值是10或20或30
like 'yuan%'
/*
pattern可以是%或者_,
如果是%则表示任意多字符,此例如唐僧,唐国强
如果是_则表示一个字符唐_,只有唐僧符合。两个_则表示两个字符:__
*/-- 正则
SELECT * FROM emp WHERE emp_name REGEXP '^yu';
SELECT * FROM emp WHERE name REGEXP 'n$';-- 逻辑运算符
在多个条件直接可以使用逻辑运算符 and or not
练习
-- 查询年纪大于24的员工
SELECT * FROM emp WHERE age > 24;
-- 查询年龄在20到30之间的员工
SELECT * FROM emp WHERE age between 20 and 30;
-- 查询工资等于7000,8000和9000的所有员工
SELECT * FROM emp WHERE salary in (7000,8000,9000);
-- 查询名字以A开头的员工
SELECT * FROM emp WHERE name like "A%";
-- 查询名字包含A的员工
SELECT * FROM emp WHERE name like "%A%";
SELECT * FROM emp WHERE REGEXP_LIKE(name, '.*A.*',"c");
-- 查询教学部的男老师信息
SELECT * FROM emp WHERE dep="教学部" AND gender="male";
-- 查询名字以A开头的员工并且工资大于等于10000的员工的姓名
SELECT * FROM emp WHERE name like "A%" and salary>=10000;
-- 查询年龄小于25或工资低于10000的员工
SELECT * FROM emp WHERE age<25 or salary < 8000;
-- 日期相关的
SELECT *
FROM emp
WHERE birthday > '1990-01-01';
SELECT *
FROM emp
WHERE MONTH(birthday) = 12;
这里命令太多我就不一个个演示了
5.2.3 order:排序
按指定的列进行,排序的列即可是表中的列名,也可以是select语句后指定的别名
-- 语法:
select *|field1,field2... from tab_name order by field [Asc|Desc]
-- Asc 升序、Desc 降序,其中asc为默认值 ORDER BY 子句应位于SELECT语句的结尾。
练习:
-- 按年龄从高到低进行排序
SELECT * FROM emp ORDER BY age DESC ;
-- 按工资从低到高进行排序
SELECT * FROM emp ORDER BY salary;
-- 先按工资排序,工资相同的按年龄排序
SELECT * FROM emp ORDER BY salary,age;
5.2.4 group by:分组查询
GROUP BY 语句根据某个列对结果集进行分组。分组一般配合着聚合函数完成查询。
常用聚合(统计)函数
-
max()
:最大值。 -
min()
:最小值。 -
avg()
:平均值。 -
sum()
:总和。 -
count()
:个数。
在MySQL的SQL执行逻辑中,where条件必须放在group by前面!也就是先通过where条件将结果查询出来,再交给group by去分组,完事之后进行统计,统计之后的查询用having。
-- 练习案例
-- 查询男女员工各有多少人
select count(*) as count from emp group by gender;
-- 查询教学部的员工最高工资:
select max(salary) as '教学部最高工资' from emp where dep='教学部';
-- 查询平均薪水超过8000的部门
SELECT dep FROM emp GROUP BY dep HAVING AVG(salary) > 8000;
5.2.5 limit:记录条数限制
SELECT * from emp limit 10;
SELECT * from emp limit 2,5; -- 跳过前两条显示接下来的五条纪录
SELECT * from emp limit 2,2;
5.2.6 distinct:查询去重
-- 获取员工表中不重复的年龄值和薪水值,并按照相应的字段进行升序排序。
SELECT distinct age from emp order by age;
SELECT distinct salary from emp order by salary;
5.3 更新记录
UPDATE <表名> SET 字段 1=值 1 [,字段 2=值 2… ] [WHERE 子句 ]
5.4 删除记录
DELETE FROM <表名> [WHERE 子句] [ORDER BY 子句] [LIMIT 子句]
六. 案例练习
题目要求:
-
创建一个名为
shop
的数据库 -
在
shop
数据库创建一个名为orders
的表,包含以下字段:-
id (整数型,主键,自增)
-
customer_id (整数型,顾客ID)
-
order_date (日期型,订单日期)
-
order_time (时间型,订单时间)
-
total_amount (浮点型,订单总额)
-
status (字符串型,订单状态)
-
shipping_address (字符串型,配送地址)
-
payment_method (字符串型,支付方式)
-
payment_status (字符串型,支付状态)
-
-
创建表:
CREATE TABLE orders ( id INT PRIMARY KEY AUTO_INCREMENT, customer_id INT, order_date DATE, order_time TIME, total_amount DECIMAL(10, 2), status VARCHAR(50), shipping_address VARCHAR(100), payment_method VARCHAR(50), payment_status VARCHAR(50) )
-
插入订单记录
INSERT INTO orders (order_id, customer_id, order_date, order_time, total_amount, status, shipping_address, payment_method, payment_status) VALUES (1, 1, '2024-05-07', '10:00:00', 100.50, '已完成', '北京市朝阳区', '微信支付', '已支付'), (2, 2, '2024-05-07', '11:30:00', 56.20, '已发货', '上海市浦东新区', '支付宝', '已支付'), (3, 3, '2024-05-07', '13:45:00', 220.00, '待发货', '广州市天河区', '微信支付', '已支付'), (4, 4, '2024-05-07', '14:20:00', 75.80, '已取消', '深圳市福田区', '支付宝', '已支付'), (5, 5, '2024-05-07', '16:10:00', 180.90, '已发货', '成都市武侯区', '支付宝', '已支付'), (6, 1, '2024-05-07', '09:30:00', 50.00, '待付款', '北京市朝阳区', NULL, '未支付'), (7, 2, '2024-05-08', '14:00:00', 120.00, '待付款', '上海市静安区', NULL, '未支付'), (8, 3, '2024-05-08', '16:30:00', 89.50, '待付款', '广州市越秀区', NULL, '未支付'), (9, 4, '2024-05-08', '18:45:00', 75.20, '待付款', '深圳市南山区', NULL, '未支付'), (10, 5, '2024-05-08', '20:20:00', 200.00, '待付款', '成都市锦江区', NULL, '未支付'), (11, 1, '2024-05-09', '11:15:00', 150.80, '已发货', '北京市海淀区', '微信支付', '已支付'), (12, 2, '2024-05-09', '13:40:00', 95.60, '已发货', '上海市徐汇区', '支付宝', '已支付'), (13, 3, '2024-05-09', '16:00:00', 180.00, '已完成', '广州市白云区', '微信支付', '已支付'), (14, 4, '2024-05-09', '18:20:00', 60.50, '已完成', '深圳市龙岗区', '支付宝', '已支付'), (15, 5, '2024-05-09', '20:45:00', 210.30, '已发货', '成都市高新区', '微信支付', '已支付'), (16, 1, '2024-05-10', '09:10:00', 80.00, '已完成', '北京市朝阳区', '支付宝', '已支付'), (17, 2, '2024-05-10', '12:30:00', 120.50, '待发货', '上海市浦东新区', '微信支付', '已支付'), (18, 3, '2024-05-10', '15:15:00', 45.20, '已取消', '广州市天河区', '支付宝', '已支付'), (19, 4, '2024-05-10', '17:40:00', 160.90, '待发货', '深圳市福田区', '微信支付', '已支付'), (20, 5, '2024-05-10', '17:41:00', 78.90, '待发货', '深圳市福田区', '微信支付', '已支付'), (21, 1, '2024-05-11', '10:30:00', 75.60, '待付款', '北京市朝阳区', NULL, '未支付'), (22, 2, '2024-05-11', '12:45:00', 90.20, '已发货', '上海市静安区', '支付宝', '已支付'), (23, 3, '2024-05-11', '15:00:00', 200.00, '已发货', '广州市越秀区', '微信支付', '已支付'), (24, 4, '2024-05-11', '17:20:00', 65.80, '已取消', '深圳市南山区', '支付宝', '已支付'), (25, 5, '2024-05-11', '19:40:00', 180.90, '待发货', '成都市锦江区', '微信支付', '已支付'), (26, 1, '2024-05-12', '10:15:00', 150.80, '已完成', '北京市海淀区', '支付宝', '已支付'), (27, 2, '2024-05-12', '12:30:00', 95.60, '已完成', '上海市徐汇区', '微信支付', '已支付'), (28, 3, '2024-05-12', '15:10:00', 180.00, '已发货', '广州市白云区', '支付宝', '已支付'), (29, 4, '2024-05-12', '17:35:00', 60.50, '已发货', '深圳市龙岗区', '微信支付', '已支付'), (30, 5, '2024-05-12', '20:00:00', 210.30, '待付款', '成都市高新区', NULL, '未支付');
-
完成相应查询
-- 查询订单表中总订单金额。
-- 查询订单表中订单总额最高的订单记录。
-- 查询订单ID为5的订单详情。
-- 查询客户ID为3的所有订单记录。
-- 查询订单状态为"已发货"的订单数量。
-- 查询总金额超过100元的订单数量。
-- 查询订单表中各个订单状态的订单数量。
-- 查询订单表中每个客户的订单数量。
-- 查询订单表中每个客户的总订单金额。
-- 查询订单表中订单总额超过平均订单金额的订单记录。
-- 查询订单表中最近一周内的订单记录。
-- 查询订单表中每个客户的最近一笔订单记录。
-- 查询订单日期为2024-05-07的所有订单记录。
-- 查询支付方式为"微信支付"的订单数量。
-- 查询订单总金额最高的订单信息。
-- 查询收货地址中包含"北京市"关键词的订单记录。
-- 查询每个客户的订单总数,并按订单总数降序排列。
-- 查询每个客户的订单总金额,并按订单总金额升序排列。
-- 查询订单日期为2024-05-10且总金额大于50的订单数量。
-- 查询订单状态不为"已取消"的订单数量。
-- 查询每个客户的订单平均金额,并按平均金额降序排列。
-- 查询支付方式为空值或为默认值的订单数量。
-- 删除客户ID为3且订单状态为"已取消"的所有订单记录
-- 更新订单状态为"待付款"且订单日期为2024-05-10的所有订单的收货地址为"上海市浦东区"
这个案例大家可以做做,我比较懒就没做,不会的可以问问gpt
七.总结
今天说的是mysql的基础,下一章会说一点进阶的关系,pymysql等等,当然,mysql的知识远不止这些,大家借助gpt好好学习,一定能手拿把掐的
八.补充
有什么问题私我,记得点赞关注加收藏哦,有求必应,有什么需求已经想要学习资料都能看我名字