SQL语句学习笔记

-- SQL大小写不敏感
SHOW DATABASES;

-- 创建数据库
CREATE DATABASE `study_sql`;

-- 修改数据库
ALTER DATABASE `study_sql` CHARACTER SET 'utf16le';

-- 指定当前数据库
USE study_sql;




-- 单表操作部分
-- 如果表存在，则删除表
DROP TABLE IF EXISTS `study_table_one`;

-- 创建表
CREATE TABLE `study_table_one` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '记录编号',
  `student_no` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '学号',
  `student_name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '姓名',
  `subject_no` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '科目编号',
  `subject_name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '科目名称',
  `score` int(11) NOT NULL COMMENT '分数',
  PRIMARY KEY (`id`),
  KEY `student_no_index` (`student_no`),
  KEY `subject_no_index` (`subject_no`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COMMENT='学习表一';
ALTER TABLE `study_sql`.`study_table_one` COMMENT = '学习表一';

-- 为表添加索引
-- CREATE INDEX index_name ON table_name (column_name);
CREATE INDEX score_index ON study_table_one(score);

-- 修改表结构
-- ALTER TABLE table_name DROP INDEX index_name;
ALTER TABLE `study_sql`.`study_table_one` DROP INDEX `subject_no_index`,ADD INDEX `subject_no_index`(`subject_no`);

-- 重命名索引
ALTER TABLE `study_sql`.`study_table_one` RENAME INDEX `subject_no_index` TO `subject_index`;

-- 展示表的所有索引
SHOW INDEX FROM study_table_one;

-- 指定字段，插入数据
INSERT INTO `study_sql`.`study_table_one`(`id`, `student_no`, `student_name`, `subject_no`, `subject_name`, `score`) VALUES (1, '201601', '张三', '0001', '数学', 98);
INSERT INTO `study_sql`.`study_table_one`(`id`, `student_no`, `student_name`, `subject_no`, `subject_name`, `score`) VALUES (2, '201601', '张三', '0002', '语文', 66);
INSERT INTO `study_sql`.`study_table_one`(`id`, `student_no`, `student_name`, `subject_no`, `subject_name`, `score`) VALUES (3, '201602', '李四', '0001', '数学', 60);
INSERT INTO `study_sql`.`study_table_one`(`id`, `student_no`, `student_name`, `subject_no`, `subject_name`, `score`) VALUES (4, '201602', '李四', '0003', '英语', 78);
INSERT INTO `study_sql`.`study_table_one`(`id`, `student_no`, `student_name`, `subject_no`, `subject_name`, `score`) VALUES (5, '201603', '王五', '0001', '数学', 99);
INSERT INTO `study_sql`.`study_table_one`(`id`, `student_no`, `student_name`, `subject_no`, `subject_name`, `score`) VALUES (6, '201603', '王五', '0002', '语文', 99);
INSERT INTO `study_sql`.`study_table_one`(`id`, `student_no`, `student_name`, `subject_no`, `subject_name`, `score`) VALUES (7, '201603', '王五', '0003', '英语', 98);

-- 查询数据
SELECT * FROM study_table_one;

-- 指定字段，查询数据
SELECT student_no FROM study_table_one;

-- 字段别名
SELECT student_no std_num FROM study_table_one;

-- 条件范围查询
SELECT student_no,student_name FROM study_table_one WHERE score >= 60;

-- 表别名、字段别名、条件查询
SELECT s.student_no AS std_num FROM study_table_one AS s WHERE s.student_no = 201603;

-- DISTINCT去重，注意：DISTINCT作用于SELECT后的结果集中的不同行之间
SELECT DISTINCT student_no,score FROM study_table_one;

-- 多条件查询
SELECT * FROM study_table_one WHERE (score BETWEEN 66 AND 98) AND (score <> 78) AND subject_name IN ('数学','英语');

-- 字符串模糊匹配
SELECT * FROM study_table_one WHERE score NOT LIKE '%8' OR student_no LIKE '_016_3' ORDER BY score;

-- 指定字段，插入数据
INSERT INTO `study_sql`.`study_table_one`(`id`, `student_no`, `student_name`, `subject_no`, `subject_name`, `score`) VALUES (8, '201602', '李四', '0002', '语文', 59);
INSERT INTO `study_sql`.`study_table_one`(`id`, `student_no`, `student_name`, `subject_no`, `subject_name`, `score`) VALUES (9, '201602', '李四', '0004', '大物', 59);
INSERT INTO `study_sql`.`study_table_one`(`id`, `student_no`, `student_name`, `subject_no`, `subject_name`, `score`) VALUES (10, '201602', '李四', '0005', 'C语言', 200);

-- 复杂查询
-- GROUP BY:http://www.runoob.com/sql/sql-groupby.html
-- GROUP BY 语句通常用于结合聚合函数使用，根据一个或多个列对结果集进行分组
-- GROUP BY 语句通过一定的规则将一个数据集划分成若干个小的区域，然后针对若干个小区域进行数据处理
-- 查询总分150分以上的学生的总分表（不及格的科目不计分），并按总分由高到低排序
SELECT
    student_no,
    student_name,
    SUM(score) AS TotalScore
FROM
    study_table_one
WHERE
    score >= 60
GROUP BY
    student_no
HAVING
    TotalScore >= 150
ORDER BY
    TotalScore DESC
;

-- 对上面结果中同学的个数进行统计
SELECT
    COUNT(*) AS "人数"
FROM (
    SELECT
        SUM(score) AS TotalScore
    FROM
        study_table_one
    WHERE
        score >= 60
    GROUP BY
        student_no
    HAVING
        TotalScore >= 150
    ORDER BY
        TotalScore DESC
)AS total_score_table;

-- GROUP BY 复杂用法：https://blog.csdn.net/liu_shi_jun/article/details/51329472

-- 逐级排序
SELECT * FROM study_table_one ORDER BY subject_no DESC,student_no DESC,score ASC;

-- 按条件更新数据
UPDATE study_table_one SET student_name='王二',subject_name="大英" WHERE score <= 60;

-- 按条件删除数据
DELETE FROM study_table_one WHERE score <= 60 AND student_name != '王二';

-- limit查询
SELECT * FROM study_table_one LIMIT 3;

-- 字符串正则匹配
SELECT * FROM study_table_one WHERE subject_name REGEXP '[大文]';

-- 字段不为空
SELECT * FROM study_table_one WHERE score IS NOT NULL;




-- 多表操作部分
-- 创建表二
CREATE TABLE `study_table_two` (
  `student_no` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '学号',
  `student_name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '学生姓名',
  `ID_Card` varchar(18) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '身份证号',
  `Address` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '家庭住址',
  `Phone_Number` varchar(11) NOT NULL COMMENT '手机号',
  PRIMARY KEY (`student_no`),
  KEY `ID_Card_index` (`ID_Card`),
  KEY `Phone_Number_index` (`Phone_Number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学习表二';

-- 插入数据到表二
INSERT INTO `study_sql`.`study_table_two`(`student_no`, `student_name`, `ID_Card`, `Address`, `Phone_Number`) VALUES ('201601', '张三', '422201199608260811', '湖北省孝感市孝南区', '18327653303');
INSERT INTO `study_sql`.`study_table_two`(`student_no`, `student_name`, `ID_Card`, `Address`, `Phone_Number`) VALUES ('201602', '李四', '422205266680450677', '湖南省长沙市', '14595554726');
INSERT INTO `study_sql`.`study_table_two`(`student_no`, `student_name`, `ID_Card`, `Address`, `Phone_Number`) VALUES ('201603', '王五', '455501799955446622', '湖南省长沙市湖南大学', '18647954460');

-- 为表一添加到表二的外键
ALTER TABLE `study_sql`.`study_table_one` ADD FOREIGN KEY (`student_no`) REFERENCES `study_sql`.`study_table_two` (`student_no`);

-- 双层SELECT查询，等价于INNER JOIN查询
SELECT * FROM study_table_one WHERE student_no IN (SELECT student_no FROM study_table_two WHERE Phone_Number = '18327653303');

-- 展示表结构
DESC study_table_two;

-- SELECT临时表、INNER JOIN ON
SELECT
    study_table_one.*,
    study_table_three.ID_Card,
    study_table_three.Address,
    study_table_three.Phone_Number
FROM
    study_table_one
	INNER JOIN
    ( SELECT student_no,ID_Card,Address,Phone_Number FROM study_table_two WHERE ID_Card = '422201199608260811' ) AS study_table_three
    ON study_table_one.student_no = study_table_three.student_no;

-- INNER JOIN: 如果表中有至少一个匹配，则返回行（JOIN默认为INNER JOIN）
-- LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行
-- RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行
-- FULL JOIN: 只要其中一个表中存在匹配，就返回行（MySQL没有FULL JOIN 这个东西）

-- 全连接、左连接、右连接
SELECT * FROM
    study_table_one
    LEFT JOIN
    ( SELECT student_no,ID_Card,Address,Phone_Number FROM study_table_two WHERE ID_Card = '422201199608260811' ) AS study_table_three
    ON study_table_one.student_no = study_table_three.student_no
UNION ALL
SELECT * FROM
    study_table_one
    RIGHT JOIN
    ( SELECT student_no,ID_Card,Address,Phone_Number FROM study_table_two WHERE ID_Card = '422201199608260811' ) AS study_table_three
    ON study_table_one.student_no = study_table_three.student_no;

-- UNION 操作符用于合并两个或多个 SELECT 语句的结果集到一个结果集中，直接拼接，不考虑类型、字段等，因此：
-- 请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列，列也必须拥有相似的数据类型，同时，每条 SELECT 语句中的列的顺序必须相同
-- 否则查出来的结果集一般是无意义的脏集
-- 默认地，UNION 操作符选取不同的值，相同的值被过滤掉，如果允许重复的值，请使用 UNION ALL
-- UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名




-- MySQL不支持SELECT INTO，备份一个表的数据到另一个表：

-- 1、备份表未创建，取消自增
-- CREATE TABLE new_table(SELECT Columns FROM old_table WHERE Condition);
CREATE TABLE study_table_one_backup(SELECT * FROM study_table_one);

-- 2、备份表已创建
-- INSERT INTO backup_table(Columns)(SELECT Columns FROM Source_table WHERE Condition);
INSERT INTO study_table_one_backup(student_no)(SELECT id FROM study_table_one);

-- 复制表结构
CREATE TABLE study_table_one_backup LIKE study_table_one;




-- 约束(Constraints)
-- 1、ALTER TABLE TABLE_NAME ADD CONSTRAINT CONSTRAINT_NAME CONSTRAINT_CONTENT;
-- 2、ALTER TABLE TABLE_NAME DROP CONSTRAINT CONSTRAINT_NAME;-- 注意CONSTRAINT_TYPE和CONSTRAINT_NAME的坑
-- 3、CREATE TABLE TABLE_NAME(
--     ...
--     Column_Name Column_TYPE CONSTRAINT_CONTENT,
--     CONSTRAINT CONSTRAINT_NAME CONSTRAINT_CONTENT,
--     ...
-- );




-- NULL、NOT NULL，MODIFY COLUMN是属性全量替换，不是个别修改
ALTER TABLE `study_sql`.`study_table_one_backup` MODIFY COLUMN `id` int(11) NULL DEFAULT 0 COMMENT '记录编号' FIRST;
ALTER TABLE `study_sql`.`study_table_one_backup` MODIFY COLUMN `id` int(11) NOT NULL DEFAULT 0 COMMENT '记录编号' FIRST;
-- CHANGE，同MODIFY
ALTER TABLE study_table_one_backup CHANGE student_name student_name VARCHAR(15);

-- UNIQUE
ALTER TABLE `study_sql`.`study_table_one_backup` ADD INDEX(`id`);
ALTER TABLE `study_sql`.`study_table_one_backup` ADD UNIQUE(`id`);
ALTER TABLE study_table_one_backup ADD CONSTRAINT CONSTRAINT_NAME UNIQUE(id);
ALTER TABLE study_table_one_backup DROP INDEX CONSTRAINT_NAME;

-- PRIMARY KEY
ALTER TABLE `study_sql`.`study_table_one_backup` ADD PRIMARY KEY (`id`);
ALTER TABLE study_table_one_backup ADD CONSTRAINT PRIMARY_KEY PRIMARY KEY (id);
ALTER TABLE study_table_one_backup DROP PRIMARY KEY;

-- FOREIGN KEY
ALTER TABLE `study_sql`.`study_table_one_backup` ADD FOREIGN KEY (`id`) REFERENCES `study_sql`.`study_table_one` (`id`);
ALTER TABLE study_table_one_backup ADD CONSTRAINT FOREIGN_KEY FOREIGN KEY (id) REFERENCES study_table_one(id);
ALTER TABLE study_table_one_backup DROP FOREIGN KEY FOREIGN_KEY;

-- CHECK
-- MySQL无效？CHECK子句会被分析，但是会被忽略
ALTER TABLE study_table_one_backup ADD CONSTRAINT CHECK_SCORE CHECK (score >= 0 AND score <= 100);
-- MySQL不支持
-- ALTER TABLE study_table_one_backup DROP CHECK CHECK_SCORE;

-- DEFAULT默认值
ALTER TABLE `study_sql`.`study_table_one_backup` MODIFY COLUMN `student_name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '刘依阳' COMMENT '姓名' AFTER `student_no`;
ALTER TABLE study_table_one_backup ALTER COLUMN student_name SET DEFAULT '刘依阳';
ALTER TABLE study_table_one_backup ALTER COLUMN student_name DROP DEFAULT;

-- 清空表数据
-- TRUNCATE TABLE table_name;




-- 表中列的增改删
-- 新增列
ALTER TABLE study_table_one_backup ADD column_name int(11);

-- MySQL不支持
-- ALTER TABLE table_name ALTER COLUMN column_name datatype;
-- ALTER TABLE study_table_one_backup ALTER COLUMN column_name varchar(11);

-- 删除列
ALTER TABLE study_table_one_backup DROP COLUMN column_name;

-- 更改AUTO_INCREMENT的起始值，一个表只能有一个AUTO_INCREMENT字段，并且必须是KEY索引字段
ALTER TABLE study_table_one_backup AUTO_INCREMENT=100;




-- 视图
-- 创建视图CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition;
-- 查询视图SELECT column_name(s) FROM view_name WHERE condition;
-- 查看视图DESC view_name;
-- 更新视图CREATE OR REPLACE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition;
          ALTER VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition;
-- 撤销视图DROP VIEW view_name;

-- 视图中的删除操作，最终是通过删除基本表中的记录实现的
-- 注意，虽然视图更新的方式有多种，但是，并不是所有情况下，都能执行视图的更新操作
-- 当视图中包含如下内容时，视图的更新操作不能被执行：
-- 1、视图中包含，基本表中被定义为非空的列
-- 2、在定义视图的SELECT语句后的字段列表中，使用了数学表达式
-- 3、在定义视图的SELECT语句后的字段列表中，使用了聚合函数
-- 4、在定义视图的SELECT语句中，使用了DISTINCT、UNION、TOP、GROUP BY或者HAVING子句




-- MySQL内置函数：https://www.cnblogs.com/f-ck-need-u/archive/2017/10/26/7740235.html-- 1-
-- MySQL数据类型：http://www.cnblogs.com/f-ck-need-u/p/7729251.html




-- 当前数据库所有表的状态
SHOW TABLE STATUS;

-- 如果不存在，则创建库
CREATE DATABASE IF NOT EXISTS study_sql;

-- 查询建表语句
SHOW CREATE TABLE `study_table_one`;

-- 重命名表
ALTER TABLE study_table_one_backup RENAME TO study_table_one_backup;

-- 查看一个表的所有字段（简单版）
SHOW COLUMNS FROM `study_table_one_backup`;

-- 展示表结构
DESC study_table_one_backup;

-- 查看一个表的所有字段（详细版）
SHOW FULL COLUMNS  FROM `study_table_one_backup`;

-- 查看一个数据库的所有表
SHOW TABLES;
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'study_sql';

-- 查看一个数据库的所有约束（详细版）
SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE CONSTRAINT_SCHEMA = 'study_sql';

-- 查看一个数据库的所有约束（简单版）
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_SCHEMA = 'study_sql';

-- 查询计划
EXPLAIN SELECT * FROM study_table_one_backup;

-- 查看触发器
SHOW TRIGGERS FROM study_sql;

-- 删除触发器
DROP TRIGGER IF EXISTS study_sql.trigger_name;

-- 创建触发器
DELIMITER $
    CREATE TRIGGER TRIGGER_NAME AFTER INSERT
    ON study_table_one_backup FOR EACH ROW
    BEGIN
        DECLARE aINT INT;
        SET aINT = (SELECT id FROM study_table_one_backup LIMIT 1);
        UPDATE study_table_one_backup SET id = aINT + 1 WHERE id = aINT;
    END$
DELIMITER ;

-- 查看MySQL版本信息：
STATUS;
SELECT VERSION();




-- SELECT语句的结构
SELECT
    [ALL | DISTINCT | DISTINCTROW]
    [HIGH_PRIORITY]
    [STRAIGHT_JOIN]
    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
    [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [ FROM table_references
      [WHERE where_condition]
      [GROUP BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]]
      [HAVING where_condition]
      [ORDER BY {col_name | expr | position} [ASC | DESC], ...]
      [LIMIT {[offset,] row_count | row_count OFFSET offset}]
      [PROCEDURE procedure_name(argument_list)]
      [INTO OUTFILE 'file_name' [CHARACTER SET charset_name] [export_options]


INTO DUMPFILE 'file_name'    INTO var_name [, var_name] ]

      [[FOR UPDATE | LOCK IN SHARE MODE] [WAIT n | NOWAIT] ] ]


export_options:
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]




-- SQL语句实例：http://www.cnblogs.com/solid/archive/2010/08/27/1810275.html
-- SQL实例整理：https://blog.csdn.net/u013142781/article/details/50836476
-- SQL语句进阶教程：https://blog.csdn.net/u011001084/article/details/51318434