数据库MySQL

目录

常用数据类型

元素

创建表

插入记录

简单查询

修改数据

删除数据

删除表

常用字段约束

别名

字段别名

表别名

消除重复记录

模糊查询

范围查询

空判断

排序

聚合函数

分组

分组后的数据筛选

数据分页显示

连接查询

自关联

子查询

MySQL内置函数

存储过程

定义

创建存储过程

调用存储过程

删除存储过程

视图

创建视图

使用视图

删除视图

事务

定义

事务命令

索引

创建索引

调用索引

查看索引

删除索引

优缺点

---

常用数据类型

1.整数：（1）有符号：int      （2）无符号：int unsigned
 
2.小整数：（1）有符号：tinyint (-128,127)      （2）无符号：tinyint unsigned (0,255)
 
3.小数：decimal(m,n)表示共有m位数，其中小数占n位
 
4.字符串：varchar
 
5.日期：datetime，例如‘2024-01-01 12:29:59’

元素

1.数据库 - database
 
2.表 - table
 
3.字段（列）- field
 
4.记录（行）- record

创建表

CREATE TABLE 表名(
    字段名 数据类型，
    ···
);

插入记录

INSERT INTO 表名 VALUES(···);--按字段顺序写入
INSERT INTO 表名 (字段1,···) VALUES (值1,···);--按指定字段写入

简单查询

SELECT * FROM 表名;--查询所有字段
SELECT 字段1,字段2,··· FROM 表名;--查询指定字段

修改数据

UPDATE 表名 SET 字段1=值1,字段2=值2,··· WHERE 条件;

删除数据

DELETE FROM 表名 WHERE 条件;
TRUNCATE TABLE 表名;

DELETE和TRUNCATE的区别：

1.速度上：TRUNCATE>DELETE

2.删除部分数据用DELETE

3.保留表而删除所有数据，自增长字段从1开始，用TRUNCATE。对于DELETE而言，删除数据之后再插入记录，自增长字段会从删除前的顺序继续增长，不会重新开始。

删除表

DROP TABLE 表名;--删除表
DROP TABLE IF EXISTS 表名;--如果存在该表，则删除

常用字段约束

1.主键：Primary Key,自增长：auto-increment

2.非空：not null

3.唯一：unique

4.默认值：default

CREATE TABLE 表名(
    字段名 数据类型 约束，
    ···
);

别名

字段别名

SELECT card AS 身份证,name AS 姓名,sex AS 性别 FROM students;
--其中AS可省略

表别名

SELECT * FROM students AS stu;--其中AS可省略

消除重复记录

使用distinct可以消除重复记录。

SELECT DISTINCT sex FROM students;--消除性别重复的记录

模糊查询

1.使用like实现模糊查询

2.%：任意多个任意字符

3._：一个任意字符

SELECT * FROM students WHERE name like "张%";--查询姓张的记录
SELECT * FROM students WHERE name like "_阳_";--查询叫做某阳某的记录

范围查询

1.in：非连续的范围

2.between ··· and ···：连续的范围

SELECT * FROM students WHERE age in (20,30,40);--查询年龄为20或30或40的记录
SELECT * FROM students WHERE age BETWEEN 20 AND 40;--查询年龄为20到40之间的记录

空判断

1.is null：为空

2.is not null：不为空

null与‘’意义不同：1.null代表什么都没有

                             2.‘’代表长度为0的字符串

排序

1.使用ORDER BY实现排序，其中ASC代表升序，DESC代表降序，省略不写默认为ASC。

2.ORDER BY与WHERE结合使用时，WHERE要写在前面。

SELECT * FROM students WHERE sex = '女' ORDER BY age DESC;
--查询性别为女的记录，按年龄从大到小的顺序排列

聚合函数

注意：聚合函数不能在WHERE条件中使用

1.count(*)：总记录数

2.max(字段)：最大值

3.min(字段)：最小值

4.sum(字段)：求和

2.avg(字段)：求平均值，若字段中有null，则忽略不计

SELECT count(*) FROM students;--查询学生总数
SELECT max(age) FROM students;--查询年龄最大值
SELECT min(age) FROM students;--查询年龄最小值
SELECT sum(age) FROM students;--查询年龄总和
SELECT avg(age) FROM students;--查询年龄平均值

分组

1.使用GROUP BY实现分组。

2.分组的目的是配合聚合函数，聚合函数会对每一组的数据分别进行统计。

SELECT sex,count(*) FROM students GROUP BY sex;--查询各种性别的人数
SELECT age,count(*) FROM students GROUP BY age;--查询各种年龄的人数

分组后的数据筛选

1.使用HAVING实现筛选。

2.HAVING必须跟在GROUP BY之后使用，不能独立出现。

3.HAVING之后可以使用聚合函数。

SELECT sex,count(*) FROM students GROUP BY sex HAVING sex = '男';
--查询男生总人数

数据分页显示

1.获取部分行

SELECT * FROM students limit start,count;
--从start开始，获取count条数据
--start默认从0开始
--limit总是出现在SELECT语句的最后

2.分页

--已知：每页显示m条数据
--查询第n页的数据
SELECT * FROM students limit (n - 1) * m,m;

连接查询

 

--内连接
SELECT * FROM 表1 INNER JOIN 表2 ON 表1.字段 = 表2.字段;
--隐式内连接
SELECT * FROM 表1，表2 WHERE 表1.字段 = 表2.字段;
--左连接
SELECT * FROM 表1 LEFT JOIN 表2 ON 表1.字段 = 表2.字段;
--右连接
SELECT * FROM 表1 RIGHT JOIN 表2 ON 表1.字段 = 表2.字段;

自关联

--同一张表进行关联
SELECT * FROM 表名 别名1 INNER JOIN 表名 别名2 ON 别名1.字段 = 别名2.字段;

子查询

1.SELECT嵌套查询，被嵌套在内部的SELECT语句称为子查询。

2.标量子查询：子查询返回结果只有一行一列。

3.列子查询：子查询返回结果为一列多行。

4.表级子查询：子查询返回结果为多行多列（一张表）。

SELECT * FROM students
WHERE age > (SELECT avg(age) FROM students);--查询年龄大于平均年龄的记录
 
--表级子查询
SELECT * FROM (SELECT * FROM students WHERE sex = '女') stu 
INNER JOIN scores ON stu.studentNo = scores.studentNo;--查询所有女生的成绩

MySQL内置函数

1.拼接字符串：concat(str1,str2,···)

2.计算字符个数：length(str)，如果字符串中包含utf-8格式的汉字，一个汉字length返回3

3.截取字符串：（1）left(str,len)返回字符串str左端的len个字符

                         （2）right(str,len)返回字符串str右端的len个字符

                         （3）substring(str,pos,len)返回字符串str的pos位置起的len个字符

其中中文和英文字母个数一致。

4.去除空格：（1）ltrim(str)返回删除左侧空格的字符串str

                     （2）rtrim(str)返回删除右侧空格的字符串str

                     （3）trim(str)返回删除左右两侧空格的字符串str

5.四舍五入：round(n,d)，n表示原数字，d表示小数位数，d默认为0

6.随机数：rand()，返回值为0-1.0的浮点数

--常用随机排序
SELECT * FROM students ORDER BY rand();

7.当前日期：current-date()

   当前时间：current-time()

   当前日期+时间：now()

存储过程

定义

存储过程PROCEDURE，也翻译为存储程序，是一条或多条SQL语句的集合。

创建存储过程

CREATE PROCEDURE 存储过程名称(参数列表)
BEGIN
SQL语句
END

调用存储过程

CALL 存储过程(参数列表)

删除存储过程

DROP PROCEDURE 存储过程；
DROP PROCEDURE IF EXISTS 存储过程；

视图

创建视图

CREATE VIEW 视图名称 AS SELECT语句;

使用视图

SELECT * FROM 视图名称;
--内容只可查询，不可修改

删除视图

DROP VIEW 视图名称;
DROP VIEW IF EXISTS 视图名称;

事务

定义

所谓事务，它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。

事务是数据库维护数据一致性的单位，在每个事务结束时，都能保持数据一致性。

事务命令

1.开启事务：begin，开启事务后修改或删除记录语句，变更会写到缓存中，而不会立刻生效。

2.回滚事务：rollback，放弃修改。

3.提交事务：commit，将修改的数据写入实际的表中。

索引

创建索引

CREATE INDEX 索引名称 ON 表名(字段名称(长度));
--如果指定字段时字符串，需要指定长度，建议长度与定义字段时的长度一致
--字段类型如果不是字符串，可以不填写字段长度
--对于主键，系统会自动建立索引

调用索引

不需要显式地调用索引的语句，只要WHERE条件后面用到的字段建立索引，那么系统会自动调用，从而加快查询速度。

查看索引

SHOW INDEX FROM 表名;

删除索引

DROP INDEX 索引名称 ON 表名;

优缺点

优：加快查询速度

缺：降低了更新表的速度，因为更新表时还要维护索引