0724-0729
近两周,我们主要学习了MySQL数据库的基本知识和操作。我对MySQL数据库有了更深入的了解,掌握了一些基本的操作方法。
以下是我在这两周的学习内容和成果总结:
一、基本概念
- 数据库(Database):用于存储和管理数据的仓库。
- 表(Table):数据库中的一个对象,用于存储一组相关的数据。
- 字段(Field):表中的一个列,用于描述数据的结构。
- 记录(Record):表中的一条数据,由多个字段组成。
- 主键(Primary Key):表中唯一标识一条记录的字段。
- 外键(Foreign Key):表中与另一张表关联的字段。
创建外键关系后,主表可以添加数据,与从表关联的数据不可删, 从表符合规则的才能增加、更新、可随意删除。
7.索引(Index):提高查询速度的数据结构。
二、基本操作
- 创建数据库:CREATE DATABASE 数据库名;
- 删除数据库:DROP DATABASE 数据库名;
- 创建表:CREATE TABLE 表名 (字段名1 数据类型, 字段名2 数据类型, …);
- 删除表:DROP TABLE 表名;
- 查看所有表:SHOW TABLES;
- 查看表结构:DESC 表名;
- 增添数据:INSERT INTO 表名 VALUES (值1, 值2, …);
在插入数据时,由于疏忽忘记了指定字段的数据类型,导致插入失败。后来通过查看MySQL的错误提示信息,才发现了这个问题。解决方法是在插入数据的语句中明确指定每个字段的数据类型,一一对应,例如:INSERT INTO table_name (字段名1, 字段名2) VALUES (值1, 值2);
8.修改数据:UPDATE 表名 SET 字段名1=值1, 字段名2=值2 WHERE 条件;
9.删除数据:DELETE FROM 表名 WHERE 条件;
10.查询数据:SELECT * FROM 表名 WHERE 条件;
11.排序数据:SELECT * FROM 表名 ORDER BY 字段名 ASC(升序)/DESC(降序);
12.分组数据:SELECT COUNT(*) FROM 表名 GROUP BY 字段名;
13.连接表查询:SELECT * FROM 表名1 JOIN 表名2 ON 表名1.字段名 = 表名2.字段名;
14.使用子查询:SELECT * FROM 表名 WHERE 字段名 IN (SELECT * FROM 其他表);
多行子查询
查询中嵌套查询.
IN : 在指定的集合范围之内,多选一
NOT IN : 不在指定的集合范围之内
ANY : 子查询返回列表中,有任意一个满足即可
ALL : 子查询返回列表的所有值都必须满足
子查询:
单行单列 select 字段列表 from 表 where 字段名 = 子查询;
多行单列 select 字段列表 from 表 where 字段名 in 子查询;
多行多列 select 字段列表 from (子查询) where 条件;
多表查询中的笛卡尔乘积现象:
多行表在查询时,如果定义了无效连接或者漏写了连接 条件,就会产生笛卡尔乘积现象,所谓的笛卡尔乘积及时每个表的每一行都和其他表 的每一行组合。笛卡尔乘积现象
笛卡尔积:
笛卡尔乘积是指在数学中,两个集合A集合 和 B集合的所有组合情况。在SQL语句中,如何来去除无效的笛卡尔积呢?
我们可以给多表查询加上连接查询的 条件即可。
等值连接查询
n个表进行等值连接查询,最少需要 n-1 个等值条件来约束
通常是在存在主键外键关联关系的表之间的连接进行,使用"="连接相关的表
-- 查询每个分类的所有商品信息
SELECT category.name,goods.name FROM goods,category WHERE
goods.category_no = category.no;
表的别名:
①. tableA as 别名1 , tableB as 别名2 ;
②. tableA 别名1 , tableB 别名2 ;
注意:一旦为表起了别名,就不能再使用表名来指定对应的字段了,此时只 能够使用别名来指定字段。
自链接查询
多表查询不仅可以在多个表之间进行查询,也可以在一个表之中进行多表查询。
查询当前公司员工和所属上级员工的信息
员工信息和员工上级信息都是在员工表emp中的,所以这样的查询是以当前的表中的列mgr关联当前表的另一列emp_no;在sql中提供了自连接查询的方式来完成这样的功能操作。
SELECT
e1.emp_no AS '员工编号',
e1.e_name AS '员工姓名',
e2.emp_no AS '领导编号',
e2.e_name AS '领导姓名'
FROM
emp AS e1,emp AS e2
WHERE
e1.mgr = e2.emp_no;
内连接查询
内连接查询使用 inner join
关键字实现,inner
可以省略。内连接查询时,条件用 on
连接,多个条件使用 ()
将其括起来。
交集
内连接查询的是两张表交集部分的数据。(也就是绿色部分的数据)
和等值查询差不多
外连接
外连接分为左外连接( left outer join )
和右外连接( right outer join )
其 值 outer
可以省略。外连接查询时,条件用on
连接,多个条件使用()
将其括起来. 左外连接表示以左表为主表,右外连接表示以右表为主表。查询时将主表信息在从表中进行匹配。
左外连接
左外连接相当于查询表1(左表)的所有数据,当然也包含表1和表2交集部分的数据。
SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ... ;
右外连接
SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ... ;
注意事项:
左外连接和右外连接是可以相互替换的,只需要调整在连接查询时SQL中, 表结构的先后顺序就可以了。而我们在日常开发使用时,更偏向于左外连 接。
SQL语句的书写顺序:
- select
- from
- where
- group by
- having
- order by
- limit
SQL语句的执行顺序:
- from
- where
- group by
- 聚合
- having
- select
- distinct
- order by
- limit
字符串函数
upper() 将字母转换为大写
lower() 将字母转换为小写
conncat() 字符串拼接
substr/substring 截取字符串
instr 获取子字符串在父字符串中的索引
trim 获取子字符串在父字符串中的索引
ltrim 去掉左端的空格
rtim 去掉右端的空格
replace 替换文本
数字函数
round(x,y) 对x四舍五入,保留y位小数
truncate(x,y) 对x截取,保留位数y
ceil(x) 向上取整
floor(x) 向下取整
abs(x) 取绝对值
日期函数
now() 获取当前日期和时间
current_timestamp() 获取当前时间戳
MONTH(d) 返回日期d中的月份值,1到12
QUARTER(d) 返回日期d是第几季节,返回1到4
SECOND(t) 返回t中的秒钟值
WEEKDAY(d) 日期d是星期几,0表示星期一,1表示星期二
主键约束
一个表中只能有一个主键约束,主键列中的值不允许重复,且不能为NULL值
设置主键约束的方式:
创建表的时候指定主键约束
create table table1(
id int PRIMARY KEY,
name varchar(20));
create table table1(
id int,
name varchar(20),
PRIMARY KEY(id));
create table table1(
id int,
name varchar(20),
constraint pk primary key(id));
自增长列
在MySQL数据库中自增长列必须是主键列,自增长列是int类型的,其值是由数据库自动维护的,是永远都不会重复的
-
标识列必须和一个Key搭配(Key指主键、唯一、外键…)
-
一个表最多有一个标识列
-
标识列的类型只能是数值型
-
标识列可以通过 SET auto_increment_increment = 3; 设置步长(全局,退出数据库重新进入会恢复默认。
创建表的时候设置自增长列
create table table1(
id int PRIMARY KEY auto_increment,
name varchar(20)
);
联合主键
使用场景
-
当单个列无法唯一标识表中的每一行,但多个列组合在一起可以唯一标识每一行数据时。
-
提高查询性能:联合主键可以更有效地支持涉及多个列的查询,避免创建额外的索引。
-
在具有多个外键的关联表中,可以使用联合主键来确保外键引用的准确性。
-
联合主键要求每个列组合的值都是唯一的
唯一约束
对于非主键列中的值也要求唯一性,唯一约束允许有多个 NULL 值
默认约束
为列中的值设置默认值, DEFAULT value,如果已经设置了值,默认值就无效
非空约束
用于保证该字段的值不能为空,not null
修改列的约束确保现有数据满足非空约束条件,否则可能导致操作失败
检查约束
在数据列上设置一些过滤条件,当过滤条件满足的时候才可以进行保存,如果不满足则出现错误
外键约束
外键用来建立主表与从表的关联关系,为两个表的数据建立连接,约束两个表中数据的一致性和完整性。
注意:
-
外键列类型需要与引用列类型一致
-
外键列的值必须是主表中引用列的值或者 NULL
-
一个表可以有多个外键列
-
从表列可以随便删除
-
删除主表数据时,会先检查从表中有没有对此数据的关联,如果有就不能直接删除
聚合函数:
MYSQL 中内置了 5 种聚合函数,分别是: SUM 、 MAX 、 MIN 、 AVG 、 COUNT
。
- sum :求和
select sum(列) from table_name [其他子句];
- max : 求最大值
select max(列) from table_name [其他子句];
- min : 求最小值
select min(列) from table_name [其他子句];
- avg : 求平均值
select avg(列) from table_name [其他子句];
- count : 求数量
select count(列) from table_name [其他子句];
分组
group by
··· having
group by
是对数据进行分组,分组时,表中有相同值的分为一组。分组后可以进行 聚合查询。
group by
分组后的查询中, select 的列不能出现除了 group by 分组条件以及聚 合函数外的其他列
select 列1, 列2, (聚合函数) from table_name group by 列1, 列2;
having
是对 group by
分组后的结果集进行筛选。
select 列1, 列2, (聚合函数) from table_name group by 列1, 列2 having 分组后条件;
group by
··· having
group by
是对数据进行分组,分组时,表中有相同值的分为一组。分组后可以进行 聚合查询。
group by
分组后的查询中, select 的列不能出现除了 group by 分组条件以及聚 合函数外的其他列
select 列1, 列2, (聚合函数) from table_name group by 列1, 列2;
having
是对 group by
分组后的结果集进行筛选。
select 列1, 列2, (聚合函数) from table_name group by 列1, 列2 having 分组后条件;