数据库——MYSQL复习

单表查询

1.查询指定列

select id,name from t_student

2.查询全部列

select * from t_student

3.查询经过计算的值

select id,age-18 from t_student

4.消除取值重复的行

select name distinct from t_student

5.查询满足条件的元祖（比较、确定范围（between 18 and 20）、确定集合（in (‘李四’,‘王五’)）、字符匹配（‘%张%’、‘张_’）、空值(is null、is not null)、多重条件(sex is not null and age > 20)）

select * from t_student where age > 20

select * from t_student where age between 18 and 20

select * from t_student where name in (‘李四’,‘王五’)

select * from t_student where name like ‘%张%’

select * from t_student where name like ‘张_’

select * from t_student where sex is null

select * from t_student where sex is not null

select * from t_student where sex is not null and age > 20

6.Order by子句

select * from t_student order by age asc

select * from t_student order by age desc

7.聚集函数 COUNT/SUM/AVG/MAX/MIN

select count(*) from t_student

select sum(age) from t_student

8.Group by子句

select * from t_student group by age

连接查询

嵌套查询 ANY/ALL/IN/EXISTS

select * from t_student where name in (select name from t_good_stu)

select * from t_score where score < any(select score from t_score where score < 80)

select * from t_score where score < all(select score from t_score where score between 10 and 80)

select * from t_score where exists (select score from t_score where score < 100)

集合查询UNION/INTERSECT/EXCEPT

select * from t_student where age > 20 union select * from t_student where sex = ‘男’

select * from t_student where age > 20 intersect select * from t_student where sex = ‘男’

select * from t_student where age > 20 except select * from t_student where sex = ‘男’

索引

创建索引

CREATE TABLE t_user(id int,username varchar(20),password varchar(20),UNIQUE INDEX(username,password));

CREATE INDEX idx_lastname ON table_name (lastname);

ALTER TABLE t_user ADD INDEX username_index(username)

删除索引

DROP INDEX username_index ON t_user

EXPLAIN 是一个 MySQL 的关键字，用于分析查询语句的执行计划和查询优化器的决策。通过 EXPLAIN，你可以获取关于查询的重要信息，例如使用的索引、连接类型、扫描行数等，以及查询优化器的选择和判断。

视图

视图是虚拟表，数据库没有存储结构和数据。

视图作用：

**简化复杂查询：**通过在视图中定义复杂的查询逻辑，可以简化查询语句，使其更易读和易用。视图隐藏了底层表的复杂性，用户只需关注视图的结构和使用。

**提供数据安全性：**通过视图，可以限制用户对数据的访问权限，只显示他们被授权访问的特定数据和列。视图可以隐藏敏感数据，增强数据的安全性。

**封装数据：**视图将查询逻辑封装在一个虚拟表中，隐藏了底层表的细节。这样用户可以通过视图来访问和操作数据，而不需要直接操作底层表。

**简化数据访问：**视图提供了一个简化的数据访问接口，用户可以直接查询视图而无需了解底层表的结构和关系。视图将复杂的数据结构转化为更易理解和使用的形式。

**逻辑数据组织：**通过视图，可以将多个表的数据按照逻辑上的组织方式进行展示，提供更高层次的数据抽象。视图可以将相关联的数据组合在一起，形成一个更完整和一致的数据视图。

创建视图

CREATE VIEW view_name AS
SELECT column1, column2, …
FROM table
WHERE condition;

更新视图结构

ALTER VIEW view_name AS
SELECT column1, column2, …
FROM table
WHERE condition;

更新视图内容

UPDATE view_name SET column1 = “”

触发器

创建触发器

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table_name
FOR EACH ROW
BEGIN
– 触发器操作语句
END;

INSERT是NEW，DELETE是OLD

CREATE TRIGGER my_trigger
AFTER INSERT ON customers
FOR EACH ROW
BEGIN
INSERT INTO audit_table (customer_id, action) VALUES (NEW.customer_id, ‘INSERT’);
END;

删除触发器

DROP TRIGGER [IF EXISTS] trigger_name;

函数

数学函数

ABS(X)：返回X绝对值

MOD(N,M)：返回N被M除的余数

FLOOR(X)：返回不大于X的最大整数值

CEILING(X)：返回不小于X的最小整数值

ROUND(X)：返回参数X的四舍五入的一个整数

字符串函数

ASCII(str)：返回字符串str的最左面字符的ASCII代码值，如果是空串则返回0

LENGTH(str)：返回字符串str的长度

LOCATE(substr,str)：返回子串substr在字符串str第一个出现的位置，如果substr不是在str里面，返回0

LEFT(str,len)：返回字符串str的最左面len个字符

RIGHT(str,len)：返回字符串str的最右面len个字符

SUBSTRING(str,pos)：从字符串str的起始位置pos返回

TRIM(str)：返回字符串str，所有前缀或后缀空格被删除了

REVERSE(str)：返回颠倒字符顺序的字符串str

日期和时间函数

DAYOFWEEK(date)：但会日期date的星期索引

DATEOFYEAR(date)：返回date在一年中的日数

QUARTER(date)：返回date在一年中的季度

NOW()：YYYY-MM-DD HH:MM:SS格式返回当前时间

加密函数

PASSWORD(str)：对字符串str进行加密

MD5(str)：对字符串str进行加密