[数据库SQL实战] 基本语法记录
本文是个人在练习数据库相关习题所使用的SQL关键字和函数简单使用样例，用以备忘。
本文内容主要是再加工，对大部分SQL关键字和函数都演示了简单的测试使用样例。
通过举例的方式，粗略地介绍了其基本使用方法。
DATABASES 数据库
创建 CREATE
创建数据库
-- 如果不存在test数据库，就创建字符集为utf8的test数据库
CREATE DATABASE IF NOT EXISTS test CHARACTER SET utf8;
查看 SHOW/USE
查看数据库信息
-- 显示数据库列表
SHOW DATABASES;
-- 显示创建数据库信息 (\G表示竖着打印)
SHOW CREATE DATABASE test\G;
切换数据库(环境)
-- 切换到test数据库
USE test
-- 注1：分号可加可不加
-- 注2：可以直接切换，不管当前环境
查看数据库的数据表列表
-- 通过information_schema.TABLES获得所有数据库的表名，指定获取哪个数据库的表名
SELECT table_name
FROM information_schema.TABLES
WHERE TABLE_SCHEMA='employees';
-- 显示当前环境下数据表列表
SHOW TABLES;
-- 可以显示直接指定环境下表格列表
SHOW TABLES FROM DATABASES;
MySQL 中的 information_schema 数据库
删除 DROP
删除数据库
-- 删除数据库test
DROP DATABASES test;
TABLE 数据表 (重点)
创建 CREATE
创建数据表
-- 正常创建数据表
-- 学生表students
CREATE TABLE IF NOT EXISTS students (
stu_no char(10) NOT NULL,
name varchar(10) NOT NULL,
gender int(11) NOT NULL,
PRIMARY KEY (stu_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 复制已有表的结构建立新表
-- stu_test表与students表结构一样(主键和外键都有) 但是个空表
CREATE TABLE IF NOT EXISTS stu_test LIKE students;
-- 基于已有表的数据建立新表
-- stu_test2表与students表结构类似(字段和类型都一样，但主键和外键没有)
CREATE TABLE IF NOT EXISTS stu_test2 AS SELECT * FROM students;
查看 SHOW/DESC/SELECT
查看数据表信息
-- 显示创建数据表信息 (\G表示竖着打印)
SHOW CREATE TABLE students\G;
-- 显示数据表字段定义信息
DESC students;
-- 等同于
SHOW COLUMNS FROM students;
查看表格数据
-- 显示students表格所有数据
SELECT * FROM students;
修改 INSERT INTO/REPLACE/UPDATE/RENAME/ALTER
插入数据到表格
-- 正常插入数据到表格
INSERT INTO students(stu_no, name, gender, class_no)
VALUES("10001", "张三", 1, "201603"),
("10002", "王喜", 0, "201604");
-- 注意：如果某列关联了外键，那对应的外键必须有相应内容，否则会报以下错误
-- ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`class_no`) REFERENCES `classes` (`class_no`))
INSERT INTO classes(class_no, number)
VALUES("201603", 46), ("201604", 44);
-- 直接从已有数据表插入数据到新表
-- 将students表stu_no大于10000的数据插入stu_test表
INSERT INTO stu_test AS SELECT * FROM students WHERE stu_no > 10000;
更新数据到表格
-- 更新stu_test表stu_no字段的值 将该字段的值+2(字符也适用)
UPDATE stu_test SET stu_no = stu_no + 2;
-- 将表students中stu_no='10002'的class_no改为'201605'
-- 使用REPLACE关键字更新数据
REPLACE INTO students(stu_no, name, gender, class_no) VALUES('10002', '王喜', 0, '201605');
-- 使用REPLACE函数更新数据 直接选择字段更新
UPDATE students SET class_no=REPLACE(class_no, '201604', '201605') WHERE stu_no='10002';
-- 直接使用UPDATE关键字
UPDATE students SET class_no='201605' WHERE stu_no='10002';
更改数据表名称
-- 将表stu_test2改成stu_test3
RENAME stu_test2 TO stu_test3;
-- 以上语句，等同于
ALTER TABLE stu_test2 RENAME stu_test3;
删除 DROP/DELETE
删除表格数据
-- 删除表格数据
-- 清空students表数据
DELETE FROM students;
删除表格
-- 删除数据表students
DROP TABLES students;
关键字
ORDER BY [AES/DESC] 排序
-- 先进行逆序排序，然后只输出第一个
-- 对hire_date字段排序降序，此时最晚的时间排在第一个，再用LIMIT取出
SELECT * FROM employees ORDER BY hire_date DESC LIMIT 1;
LIMIT m,n 限制输出
-- 入职时间hire_date 排名倒数order by ? desc 第三limit 2,1
SELECT * FROM employees ORDER BY hire_date DESC LIMIT 2,1;
DISTINCT 去重
-- 给入职时间排名(去重)
select distinct hire_date from employees order by hire_date desc;
GROUP BY ... HAVING 分组
-- 计数使用count 通过group by对每个员工编号分组 通过having进行条件筛选
SELECT emp_no, COUNT(emp_no) AS t FROM salaries GROUP BY emp_no HAVING t > 15;
LIKE 含有某字符串的信息
-- 找出描述信息description包含'robot'的电影分类
SELECT f.category_id, f.description
FROM film AS f
WHERE f.description LIKE '%robot%';
IN/NOT IN (不)从中选取
-- 从manager表中选出所有manager员工emp_no, 再从员工表反选
SELECT emp_no FROM employees WHERE emp_no NOT IN (SELECT emp_no FROM dept_manager);
EXISTS/NOT EXISTS (不)存在条件成立的值
-- exists()就类似一个函数
-- 输入de.emp_no 输出是否存在'e.emp_no=de.emp_no'等式成立 返回T/F
SELECT *
FROM employees AS e
WHERE NOT EXISTS (
SELECT *
FROM dept_emp AS de
WHERE e.emp_no=de.emp_no
);
- 用 NOT EXISTS 关键字的方法如下：
意在 employees 中挑选出令(SELECT * FROM dept_emp WHERE emp_no = employees.emp_no)不成立的记录EXISTS对外表用loop逐条查询，每次查询都会查看EXISTS的条件语句，
当 EXISTS里的条件语句能够返回记录行时(无论记录行是的多少，只要能返回)，条件就为真，返回当前loop到的这条记录;
反之如果EXISTS里的条 件语句不能返回记录行，则当前loop到的这条记录被丢弃，
EXISTS的条件就像一个bool条件，当能返回结果集则为true，不能返回结果集则为false。
VIEW 视图
创建 CREATE
建立视图
-- 创建视图stu_view 有字段name和sc(学号stu_no和班号class_no的组合)
CREATE VIEW stu_view AS SELECT name, CONCAT(stu_no, ' ', class_no) AS sc FROM students;
查看 SHOW/SELECT
-- 显示创建视图信息 (\G表示竖着打印)
SHOW CREATE VIEW stu_view \G;
-- 同数据表一样
-- 显示视图字段定义信息
DESC stu_view;
-- 等同于
SHOW COLUMNS FROM stu_view;
-- 查看视图内容
SELECT * FROM stu_view;
删除 DROP/DELETE
删除视图数据
-- 删除视图stu_view 字段sc = '10001 201603'的数据
DELETE FROM stu_view WHERE sc = '10001 201603';
删除视图
DROP VIEW stu_view;
INDEX 索引
创建 CREATE/ALTER
建立索引
-- 给students表的stu_no字段创建索引
CREATE INDEX idx_stu_no ON students(stu_no);
-- 给students表的name字段其中5个字节创建索引
CREATE INDEX idx_name ON students (name(5));
-- 给students表的stu_no字段添加索引
ALTER TABLE students ADD INDEX(stu_no);
查看 SHOW/FORCE INDEX
查看表格索引
-- 显示students表的索引列表(主键索引、外键索引、唯一索引、所添加的索引)
SHOW INDEX FROM students;
强制使用索引
-- 在查询students表stu_no字段大于"10001"的数据时，强制其使用所建立的索引
SELECT stu_no, name FROM students FORCE INDEX(idx_stu_no) WHERE stu_no > "10001";
- 实例
-- MySQL中，使用 FORCE INDEX 函数进行强制索引查询
EXPLAIN SELECT * FROM salaries FORCE INDEX(idx_emp_no) WHERE emp_no=10005;
SELECT * FROM salaries WHERE emp_no=10005; -- 4ms (+5ms)
SELECT * FROM salaries FORCE INDEX(idx_emp_no) WHERE emp_no=10005; -- 2ms (+1ms)
MySQL force Index 强制索引概述
Mysql中的force index和ignore index
删除 DROP/ALTER
删除索引
-- 删除students表的idx_name索引
DROP INDEX idx_name ON students;
-- 删除students表的idx_name索引
ALTER TABLE students DROP INDEX idx_name;
TRIGGER 触发器
创建 CREATE
建立触发器
-- 创建触发器'stu_sync'
-- 当students表插入数据后，将指定数据插入stu_test表内
DELIMITER $$
CREATE TRIGGER stu_sync
AFTER INSERT ON students FOR EACH ROW
BEGIN
INSERT INTO stu_test VALUES(NEW.stu_no, NEW.name, NEW.gender, NEW.class_no);
END$$
DELIMITER ;
查看 SHOW/SELECT
显示所创建的触发器
-- 显示创建触发器的信息 (\G表示竖着打印)
SHOW CREATE TRIGGER stu_sync\G;
-- 所有创建的触发器都存放在INFORMATION_SCHEMA数据库的TRIGGERS表格
SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='stu_sync';
-- 显示本环境(数据库)下的触发器
SHOW TRIGGERS;
删除 DROP
删除触发器
-- 删除触发器stu_sync
DROP TRIGGER stu_sync;
COLUMNS 字段(列)
查看 SHOW
显示表格字段
-- 显示数据表students的索引字段信息
SHOW COLUMNS FROM students;
添加 ALTER ... ADD
添加字段/主键/外键
-- 给students表添加字段class_no
ALTER TABLE students ADD class_no varchar(10) NOT NULL;
-- 添加多个字段
ALTER TABLE students ADD a INT,ADD b INT,ADD c INT;
-- 给classes表添加主键class_no
ALTER TABLE classes ADD PRIMARY KEY(class_no);
-- 给students表添加外键class_no 关联classes表
ALTER TABLE students ADD FOREIGN KEY (class_no) REFERENCES classes(class_no);
-- 注意：如果关联表没有主键，或所管理列不是主键，会出现以下报错。
-- ERROR 1215 (HY000): Cannot add foreign key constraint
查看 SELECT
查看表格的所有外键
-- 查看数据库test的students表所有字段所关联的外键
SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME,
REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE CONSTRAINT_SCHEMA ='test' AND
REFERENCED_TABLE_NAME = 'students';
-- 特别在想要删除该表，但因为其他表关联它作为外键而无法删除的时候
-- Cannot delete or update a parent row: a foreign key constraint fails
修改 ALTER ... MODIFY/CHANGE
修改字段/主键/外键
-- 给students表的字段修改类型
ALTER TABLE students MODIFY b tinyint NOT NULL DEFAULT 1;
ALTER TABLE students MODIFY a varchar(10);
-- 给students表已经填入数据的字段 缩小数据类型
ALTER TABLE students MODIFY stu_no char(6);
-- 给students表的字段修改名称和类型
ALTER TABLE students CHANGE b c CHAR(20);
删除 ALTER ... DROP
删除字段/主键/外键
-- 删除字段c
ALTER TABLE students DROP COLUMN c;
-- 给students表删除外键'students_ibfk_1'
ALTER TABLE students DROP FOREIGN KEY students_ibfk_1;
FUNCTION 函数
常用函数
EXPLAIN 查看执行计划
-- 执行计划 EXPLAIN
EXPLAIN SELECT * FROM employees;
- EXPLAIN 介绍
EXPLAIN是一个执行SQL语句的模拟优化器，可以通过EXPALIN来查看增删查改操作的执行计划，即MySQL是如何处理sql语句，分析查询语句或者表结构的性能。
作用 通过查看EXPALIN结果，可以知道
1、表的读取顺序
2、数据读取操作的操作类型
3、哪些索引可以使用
4、哪些索引被实际使用
5、表之间的引用
6、每张表有多少行被优化器查询
CONCAT/CONCAT_WS/GROUP_CONCAT 字符串拼接函数
-- 取巧 直接手动加空格作为分隔符
SELECT CONCAT(last_name, ' ' ,first_name) AS Name
FROM employees;
-- 使用CONCAT_WS()函数
SELECT CONCAT_WS(' ', last_name, first_name) AS Name
FROM employees;
-- GROUP_CONCAT()函数的基本使用
SELECT dept_no, group_concat(emp_no) AS employees
FROM dept_emp
GROUP BY dept_no
- CONCAT()函数用法 简述
CONCAT(str1,str2,…)
返回结果为连接参数产生的字符串。如有任何一个参数为NULL，则返回值为NULL。
CONCAT_WS(separator,str1,str2,...)
CONCAT()的特殊形式。第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。
分隔符可以是一个字符串，也可以是其它参数。
mysql多个字段拼接
concat、concat_ws、group_concat函数用法
CURRENT_TIMESTAMP 获取时间
- MySQL 获取当前时间函数：
current_timestamp() localtime() localtimestamp() sysdate()
- MySQL 获取当前日期：
curdate() = current_date()
SUBSTR/RIGHT/LENGTH 字符串切割相关函数
- RIGHT(s,n)
返回字符串 s 的后 n 个字符
- SUBSTR(s,start,len)
从字符串 s 的 start 位置截取长度为 len 的子字符串
- LENGTH(s)/CHAR_LENGTH(s)
返回字符串 s 的字符数
创建函数
样例
-- 创建一个输入字符串s 返回'Hello, $s!'
CREATE FUNCTION `hello`(s CHAR(20)) RETURNS char(50)
RETURN CONCAT('Hello, ', s, '!')
-- 查看函数信息
SHOW CREATE FUNCTION hello\G;
-- 使用函数hello
SELECT hello('world') AS `result`;
- 实例：leetcode 数据库 176、第二高的薪水
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
-- 在limit中参数不可以有运算。如果想要运算，则需要用set语句定义好变量
SET N = N - 1;
RETURN (
SELECT IFNULL((
SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT N,1), null
) AS getNthHighestSalary
);
END
