MYSQL入门基础SQL

CRUD

以用户表举例，有如下表结构

CREATE TABLE `user_info` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `name` varchar(64) NOT NULL COMMENT '用户名',
  `age` int NOT NULL COMMENT '年龄',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户信息表';

增（Create）

-- 新增一条记录用户名为 张三 年龄为 20
INSERT INTO user_info (name, age) VALUES ('张三', 20);

删（Delete）

-- 根据 主键ID删除
DELETE FROM `user_info` WHERE id = 1;
-- 根据 用户名删除
DELETE FROM `user_info` WHERE name = '张三'

查（Read）

-- 根据 主键ID查询全部字段
SELECT * FROM `user_info` WHERE id = 1;
-- 根据 用户名查询全部字段
SELECT * FROM `user_info` WHERE name = '张三';
-- 根据 主键ID 仅查询用户名和年龄字段
SELECT name, age FROM `user_info` WHERE id = 1;
-- 根据 用户名 仅查询年龄字段
SELECT age FROM `user_info` WHERE name = '张三';
-- 查询年龄大于18岁的用户名
SELECT name FROM `user_info` WHERE age > 18;
-- 范围查询-查询年龄在18岁到60之间的的用户信息
SELECT name FROM `user_info` WHERE age BETWEEN 18 AND 60;
-- 模糊查询-查以 张 开头的用户
SELECT * FROM `user_info` WHERE name LIKE '张%';

改（Update）

-- 修改 张三年龄为99
UPDATE `user_info` SET age = 99 WHERE name = '张三';
-- 修改 主键ID为1的用户名和年龄改为李四 60
UPDATE `user_info` SET name = '李四', age = 60 WHERE id = 1;

连接查询

A表 和 B表 通过某个字段关联查询（如name关联）

注意点

• 被用作关联查询的字段要建立索引
• 用小表驱动大表：将数据量小的表首先连接，再去连接数据量大的表

左外连接

效果：左表(A表)的数据全部展示，右表(B表)满足连接条件的记录展示，不满足条件的记录则全是展示NULL

SELECT * FROM A LEFT JOIN B ON A.name = B.name

右外连接

效果：右表(B表)的数据全部展示，左表(A表)满足连接条件的记录展示，不满足条件的记录则全是展示NULL

SELECT * FROM A RIGHT JOIN B ON A.name = B.name

内连接

效果：左表(A表) 和 右表(B表) 同时满足条件记录的交集

SELECT * FROM A INNER JOIN B ON A.name = B.name

联合查询

• 将多条 SELECT语句结果 进行 拼接。每一条SELECT语句结果字段数必须严格一致
• 只要求SELECT结果字段一样，跟数据类型无关

  -- 两个SELECT结果集拼接效果
  SELECT name, age FROM A WHERE A.name = '张三' UNION ALL SELECT name, age FROM B WHERE B.age > 20;

子查询

在一条SELECT语句中又包含了一条SELECT语句，即在某个查询结果集上再进行一个查询

From子查询

-- 将里面的查询结果定义别名为A，再用一条SELECT查询A
SELECT * FROM (SELECT name, age FROM user_info) AS A;

Where子查询

-- 将里面的查询结果集 作为外层查询的条件（内层查出年龄大于50的用户名，外层查包含这一批用户名的用户）
SELECT * FROM user_info WHERE name IN (SELECT name FROM user_info WHERE age > 50)

Exists子查询

Exists是否存在的意思，返回的结果只有0和1。Exists子查询用于判断满足某个条件才展示（跨库或多表情况下使用）

-- 查询用户信息，要求有某个用户年龄大于100，才展示记录
SELECT * FROM user_info WHERE EXISTS (SELECT age FROM user_info WHERE age > 100)

索引

ALTER TABLE table_name ADD [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (col_name[length],…) [ASC|DESC]

主键(聚集索引)

是一种特殊的唯一索引，一张表只能定义一个主键 primary key

ALTER TABLE user_info ADD PRIMARY KEY(id);

组合索引

索引定义多个列 INDEX(columnA, columnB)
如果创建了一个联合索引(key1,key2,key3)，那么相当于创建了(key1)、(key1,key2)、(key1,key2,key3) 三个索引，这就是最左匹配原则

ALTER TABLE user_info ADD INDEX idx_name_age (name, age);

唯一索引(UNIQUE)

保证数据记录的唯一性

ALTER TABLE user_info ADD UNIQUE idx_name(name);

普通索引

索引定义一个列 INDEX(columnA)

ALTER TABLE user_info ADD INDEX idx_age(age);

全文索引(FULLTEXT)

将整个字段内容分词，便于查到长文本中出现的某个词。
与LIKE相似，但只支持数据库引擎为MYISAM的数据表

ALTER TABLE user_info ADD FULLTEXT INDEX fulltext_name(name);
-- 全文索引的查询方式跟LIKE有所不同
SELECT * FROM user_info WHERE MATCH(name) AGAINST ('四');