iframe-proxy | Sunbelt Computer Software

933 lines (717 loc) · 16.8 KB
-- Active: 1658517114424@@172.22.164.224@3306@imc_db
SELECT 'Hello ','MySQL',2022+1;
SELECT * FROM imc_db.imc_class;
SELECT class_id,class_name FROM imc_db.imc_class;
USE imc_db;
SELECT class_id,class_name FROM imc_class;
-- WHERE 语句
-- % 代表通配符，任意数量字符
SELECT title FROM imc_course WHERE title LIKE '%MYSQL%';
-- 学习人数等于1000人的课程都有那些？
-- 列出他们的课程标题和学习人数
SELECT title,study_cnt FROM imc_course WHERE study_cnt>1000;
SELECT title, study_cnt
FROM imc_course
    study_cnt BETWEEN 1000 AND 2000;
CREATE TABLE test_is(id int,c1 VARCHAR(10),PRIMARY KEY(id));
INSERT INTO test_is VALUES(1,'a'),(2,'b'),(3,NULL);
SELECT * from test_is WHERE c1=NULL;
SELECT * from test_is WHERE c1 is NULL;
-- LIKE 和 NOT LIKE
SELECT 'this is mysql' LIKE '%mysql';
SELECT 'this is mysql course' LIKE '%mysql%';
SELECT 'this' LIKE '_hi_';
-- IN 和 NOT IN
SELECT course_id, title
from imc_course
    course_id in (1, 3, 5, 7, 9);
SELECT course_id, title
FROM imc_course
    course_id NOT IN (1, 3, 5, 7, 9);
SELECT title, study_cnt
FROM imc_course
    title LIKE '%mysql%' && study_cnt > 5000;
SELECT title, study_cnt
FROM imc_course
    title LIKE '%mysql%' || study_cnt > 5000;
-- UNION ALL 拼接两次查询
SELECT title, study_cnt
FROM imc_course
    title LIKE '%mysql%'
    AND study_cnt < 5000
SELECT title, study_cnt
FROM imc_course
    title NOT LIKE '%mysql%'
    AND study_cnt > 5000;
SELECT title, study_cnt
FROM imc_course
    title LIKE '%mysql%'
    AND study_cnt < 5000
    XOR title NOT LIKE '%mysql%'
    AND study_cnt > 5000;
INSERT INTO
    imc_course(
        title,
        title_desc,
        type_id,
        class_id,
        level_id,
        online_time,
        user_id
        'MySQL 关联测试',
        '测试 MySQL 关联查询',
        NOW(),
    a.course_id,
    a.title,
    b.chapter_name
FROM imc_course a
    LEFT JOIN imc_chapter b ON b.course_id = a.course_id
WHERE a.title like '%MySQL%';
SELECT a.course_id, a.title
FROM imc_course a
WHERE a.course_id NOT IN (
        SELECT b.course_id
        FROM imc_chapter b
-- LEFT JOIN
SELECT a.course_id, a.title
FROM imc_course a
    LEFT JOIN imc_chapter b on b.course_id = a.course_id
WHERE b.course_id IS NULL;
-- RIGHT JOIN
SELECT a.course_id, a.title
FROM imc_chapter b
    RIGHT JOIN imc_course a on b.course_id = a.course_id
WHERE b.course_id IS NULL;
-- GROUP BY
SHOW VARIABLES LIKE 'sql_mode';
    SESSION sql_mode = 'ONLY_FULL_GROUP_BY,
STRICT_TRANS_TABLES,
NO_ZERO_IN_DATE,
NO_ZERO_DATE,
ERROR_FOR_DIVISION_BY_ZERO,
NO_ENGINE_SUBSTITUTION';
SET SESSION sql_mode = (
        SELECT
        REPLACE (
                @ @sql_mode,
                'ONLY_FULL_GROUP_BY,',
    level_name,
    class_name,
    COUNT(*)
FROM imc_course a
    JOIN imc_class b ON b.class_id = a.class_id
    JOIN imc_level c on c.level_id = a.level_id
    level_name,
    class_name;
-- GROUP BY HAVING
    level_name,
    class_name,
    COUNT(*)
FROM imc_course a
    JOIN imc_class b ON b.class_id = a.class_id
    JOIN imc_level c on c.level_id = a.level_id
    level_name,
    class_name
HAVING COUNT(*) > 3;
SELECT COUNT(*),COUNT(DISTINCT user_id) FROM imc_course;
    b.level_name,
    SUM(study_cnt)
FROM imc_course a
    JOIN imc_level b ON b.level_id = a.level_id
GROUP BY level_name;
SELECT sum(study_cnt)/COUNT(study_cnt) FROM imc_course;
SELECT AVG(study_cnt) FROM imc_course;
UPDATE imc_course a
        SELECT
            course_id,
            AVG(content_score) AS avg_content,
            AVG(level_score) AS avg_level,
            AVG(logic_score) AS avg_logic,
            AVG(score) AS avg_score
        FROM imc_classvalue
        GROUP BY
            course_id
    ) b ON a.course_id = b.course_id
    a.content_score = b.avg_content,
    a.level_score = b.avg_level,
    a.logic_score = b.avg_logic,
    a.score = (
        content_score + level_score + logic_score
SHOW WARNINGS;
SELECT * FROM imc_course;
SELECT * FROM imc_classvalue;
SELECT title, study_cnt
from imc_course
WHERE study_cnt = (
        SELECT MIN(study_cnt)
        FROM imc_course
-- ORDER BY
SELECT title, study_cnt from imc_course ORDER BY study_cnt DESC;
SELECT course_id, title
FROM imc_course
ORDER BY course_id
LIMIT 10, 10;
CREATE VIEW vm_course AS 
	    a.course_id,
	    a.title,
	    b.class_name,
	    c.type_name,
	    d.level_name
	FROM imc_course a
	    JOIN imc_class b ON b.class_id = a.class_id
	    JOIN imc_type c ON c.type_id = a.type_id
	    JOIN imc_level d ON d.level_id = a.
SELECT * from vm_course;
FROM imc_course a
    LEFT JOIN imc_chapter b ON a.course_id = b.course_id
WHERE b.chapter_id is NULL;
FROM imc_course a
    LEFT JOIN imc_chapter b ON a.course_id = b.course_id
WHERE b.chapter_id is NULL;
-- DELETE 实战
SHOW CREATE TABLE imc_type;
SELECT * FROM imc_type;
INSERT INTO
    imc_type(type_name)
VALUES ('前端开发'), ('后端开发'), ('移动开发'), ('数据库');
FROM imc_type a
        SELECT
            type_name,
            COUNT(*),
            MIN(type_id) AS min_type_id
        FROM imc_type
        GROUP BY type_name
        HAVING
            COUNT(*) > 1
    ) b ON a.type_name = b.type_name
    AND a.type_id > min_type_id;
FROM imc_type a
        SELECT
            type_name,
            COUNT(*),
            MIN(type_id) AS min_type_id
        FROM imc_type
        GROUP BY type_name
        HAVING
            COUNT(*) > 1
    ) b ON a.type_name = b.type_name
    AND a.type_id > min_type_id;
CREATE UNIQUE INDEX uqx_typename ON imc_type(type_name);
SELECT user_nick,user_status FROM imc_user WHERE user_nick='沙占';
UPDATE imc_user SET user_status=0 WHERE user_nick='沙占';
-- UPDATE 实战
ALTER TABLE imc_course
    is_recommand TINYINT DEFAULT 0 COMMENT '0不推荐，1推荐';
SHOW CREATE TABLE imc_course;
SELECT course_id FROM imc_course ORDER BY RAND() LIMIT 10;
UPDATE imc_course SET is_recommand=1 ORDER BY RAND() LIMIT 10;
SELECT course_id,title FROM imc_course WHERE is_recommand=1;
SELECT CURRENT_DATE(),CURTIME(),NOW();
-- DATE_FORMAT
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s');
-- CONVERT TIME
SELECT SEC_TO_TIME('60'),TIME_TO_SEC('1:00:00');
-- DATEDIFF
    DATEDIFF(NOW(), online_time)
FROM imc_course
ORDER BY 2 DESC;
    -- 当前时间加一天 
    DATE_ADD(NOW(), INTERVAL 1 DAY),
    -- 当前时间加一年 
    DATE_ADD(NOW(), INTERVAL 1 YEAR),
    -- 当前时间减一个半小时
    DATE_ADD(
        NOW(),
        INTERVAL '-1:30' HOUR_MINUTE
    EXTRACT(
        YEAR
        FROM
            NOW()
    EXTRACT(
        MONTH
        FROM
            NOW()
    EXTRACT(
        DAY
        FROM
            NOW()
    EXTRACT(
        HOUR
        FROM NOW()
    CONCAT_WS('-', class_name, title)
FROM imc_course a
    JOIN imc_class b ON a.class_id = b.class_id;
-- LENGTH CHARLENGTH
    class_name,
    LENGTH(class_name),
    CHAR_LENGTH(class_name)
FROM imc_class;
SELECT FORMAT(123456.789,4);
-- LEFT RIGHT
SELECT LEFT('imooc',1);
SELECT RIGHT('imooc',4);
-- SUBSTRING
SELECT SUBSTRING('imooc',2);
-- SUBSTRING_INDEX
SELECT SUBSTRING_INDEX('192.168.0.100','.',-2);
    LOCATE('-', title),
    SUBSTRING(title, 1, LOCATE('-', title) -1),
    SUBSTRING_INDEX(title, '-', 1)
FROM imc_course;
SELECT TRIM(' imooc '),TRIM('x' from 'xxxximoocxx');
-- CASE WHEN
    user_nick,
        WHEN sex = 1 THEN '男'
        WHEN sex = 0 THEN '女'
        ELSE '未知'
    END AS '性别'
FROM imc_user;
    user_nick,
        WHEN sex = 1 THEN '男'
        WHEN sex = 0 THEN '女'
        ELSE '未知'
    END AS '性别'
FROM imc_user
        WHEN sex = 1 THEN '男'
        WHEN sex = 0 THEN '女'
        ELSE '未知'
    END = '男';
WITH cte AS (
        SELECT
            title,
            study_cnt,
            class_id
        FROM imc_course
        WHERE study_cnt > 2000
ORDER BY 1 DESC;
-- WITH RECURSIVE
WITH RECURSIVE test AS (
        SELECT 1 AS n
        UNION ALL
        SELECT 1 + n
        FROM test
        WHERE n < 10
-- WITH RECURSIVE QUERY
    RECURSIVE replay(
        quest_id,
        quest_title,
        user_id,
        replyid,
        q_path
        SELECT
            quest_id,
            quest_title,
            user_id,
            replyid,
            CAST(quest_id AS CHAR(200)) AS q_path
        FROM imc_question
        WHERE
            course_id = 59
            AND replyid = 0
        UNION ALL
        SELECT
            a.quest_id,
            a.quest_title,
            a.user_id,
            a.replyid,
            CONCAT(b.q_path, '>>', a.quest_id) AS q_path
        FROM imc_question a
            JOIN replay b ON a.replyid = b.quest_id
FROM replay;
    test(study_name, class_name, score) AS(
        SELECT
            'sqlercn',
            'MySQL',
        UNION ALL
        SELECT
            'tom',
            'MySQL',
        UNION ALL
        SELECT
            'Jerry',
            'MySQL',
        UNION ALL
        SELECT
            'Gavin ',
            'MySQL',
        UNION ALL
        SELECT
            'sqlercn',
            'PostGreSQL',
        UNION ALL
        SELECT
            'tom',
            'PostGreSOL',
        UNION ALL
        SELECT
            'Jerry',
            'PostGreSQL',
    study_name,
    class_name,
    ROW_NUMBER() OVER(
        PARTITION BY class_name
        ORDER BY
            score DESC
    ) AS rw,
    RANK() OVER(
        PARTITION BY class_name
        ORDER BY
            score DESC
    ) AS rk,
    DENSE_RANK() OVER(
        PARTITION BY class_name
        ORDER BY
            score DESC
    ) AS drk
ORDER BY class_name, rw;
-- 按学习人数对课程进行排名，并列出每类课程学习人数排名前3的课程名称，学习人数以及名次。
WITH tmp AS(
        SELECT
            class_name,
            title,
            score,
            RANK() OVER(
                PARTITION BY class_name
                ORDER BY
                    score DESC
            ) AS cnt
        FROM imc_course a
            JOIN imc_class b ON b.class_id = a.class_id
WHERE cnt <= 3;
-- 每门课程的学习人数占本类课程总学习人数的百分比
WITH tmp AS (
        SELECT
            class_name,
            title,
            study_cnt,
            SUM(study_cnt) OVER(PARTITION BY class_name) AS class_total
        FROM imc_course a
            JOIN imc_class b ON b.class_id = a.class_id
    class_name,
    CONCAT(
        study_cnt / class_total * 100,
        '%'
-- 查询出分类ID为5的课程名称和分类名称
    a.title,
    b.class_name,
    b.class_id
FROM imc_course a
    JOIN imc_class b ON a.class_id = b.class_id AND b.class_id = 5;
    a.title,
    b.class_name,
    b.class_id
FROM imc_course a
    LEFT JOIN imc_class b ON a.class_id = b.class_id AND b.class_id = 5;
-- 查询列不指定表名, 造成 WHERE 失效
FROM imc_course
WHERE title IN (
        SELECT title
        FROM imc_class
-- 查看 慢查询日志 目录
show variables like '%query%';
set global long_query_time = 0;
show global variables like 'long_query_time';
    level_name,
    class_name,
    study_cnt,
    level_score,
    course_id
FROM imc_course a
    JOIN imc_class b ON b.class_id = a.class_id
    JOIN imc_level c on c.level_id = a.level_id
    study_cnt > 3000
    AND a.course_id = (
        SELECT b.course_id
        FROM imc_chapter b
        LIMIT 1
SELECT a.course_id, a.title
FROM imc_course a
WHERE a.course_id = (
        SELECT b.course_id
        FROM imc_chapter b
        LIMIT 1
SELECT a.title
FROM imc_course a
SELECT b.chapter_name
FROM imc_chapter b;
    level_name,
    class_name,
    study_cnt,
    level_score,
    course_id
FROM imc_course a
    JOIN imc_class b ON b.class_id = a.class_id
    JOIN imc_level c on c.level_id = a.level_id
WHERE study_cnt > 3000;
-- 查询出2019年1月1号以后注册的男性会员的呢称
SELECT user_nick, reg_time
FROM imc_user
    sex = 1
    AND reg_time > '2019-01-01';
    COUNT(DISTINCT sex),
        DISTINCT DATE_FORMAT(reg_time, '%Y-%m-%d')
    COUNT(*),
        DISTINCT DATE_FORMAT(reg_time, '%Y-%m-%d')
    ) / COUNT(*),
    COUNT(DISTINCT sex) / COUNT(*)
FROM imc_user;
-- 建立 reg_time 索引, 在筛选率低的字段建索引基本没用
CREATE INDEX idx_regtime ON imc_user(reg_time);
-- 查看高级别 mysql 课程信息
    course_id,
    b.class_name,
    d.type_name,
    c.level_name,
FROM imc_course a
    JOIN imc_class b ON b.class_id = a.class_id
    JOIN imc_level c ON c.level_id = a.level_id
    JOIN imc_type d oN d.type_id = a.type_id
    c.level_name = '高级'
    AND b.class_name = 'MySQL';
-- 建立 a 表索引
SHOW CREATE TABLE imc_course;
CREATE UNIQUE INDEX uqx_classname ON imc_class(class_name);
CREATE UNIQUE INDEX uqx_levelname ON imc_level(level_name);
CREATE INDEX
    idx_classid_typeid_levelid ON imc_course(class_id, type_id, level_id);
-- 需求：查询出不存在课程的分类名称。
INSERT INTO imc_class SET class_name='AI';
SELECT class_name
FROM imc_class
WHERE class_id NOT IN(
        SELECT class_id
        FROM imc_course
-- 效果一样，MySQL 8.0 自动优化 NOT IN 为 LEFT JOIN
SELECT class_name
FROM imc_class a
    LEFT JOIN imc_course b ON a.class_id = b.class_id
WHERE b.class_id IS NULL;
-- 查询对于内容，逻辑和难度三项评分之后大于28分的用户评分
FROM imc_classvalue
        content_score + level_score + logic_score
    ) > 28;
CREATE INDEX
    idx_contentScore_levelScore_logicScore ON imc_classvalue(
        content_score,
        level_score,
        logic_score
ALTER TABLE imc_classvalue
    COLUMN total_score DECIMAL(3, 1) AS (
        content_score + level_score + logic_score
SHOW CREATE TABLE imc_classvalue;
CREATE INDEX idx_totalScore ON imc_classvalue(total_score);
EXPLAIN SELECT * FROM imc_classvalue WHERE total_score > 28;
-- 事务 Transaction SERIALIZABLE
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SHOW VARIABLES LIKE '%ISOLATION%';
SELECT course_id,title,score FROM imc_course WHERE score>9.6;
-- 事务 Transaction repeatable read
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SHOW VARIABLES LIKE '%ISOLATION%';
-- 事务 Transaction READ COMMITTED
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SHOW VARIABLES LIKE '%ISOLATION%';
SELECT course_id,title,score FROM imc_course WHERE course_id=37;
-- 事务 Transaction READ UNCOMMITTED
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SHOW VARIABLES LIKE '%ISOLATION%';
SELECT course_id,title,score FROM imc_course WHERE course_id=37;
-- 事务阻塞 REPEATABLE READ
UPDATE imc_course
SET score = score + 0.1
WHERE score > 9.6 AND score < 9.8;
-- 事务2 另开一个连接
UPDATE imc_course
SET score = score - 0.1
WHERE score > 9.6 AND score < 9.8;
SELECT CONNECTION_ID();
    waiting_pid AS 'blocked pid',
    waiting_query AS 'blocked SQL',
    blocking_pid AS 'running pid',
    blocking_query AS 'running SQL',
    wait_age AS 'blocked time',
    sql_kill_blocking_query AS 'info'
FROM sys.innodb_lock_waits
        UNIX_TIMESTAMP() - UNIX_TIMESTAMP(wait_started)
SET GLOBAL innodb_print_all_deadlocks=on;
SHOW VARIABLES LIKE '%deadlocks%';
-- SESSION 1
UPDATE imc_user SET score=score+10 WHERE user_id=10;
UPDATE imc_course SET score=9.8 WHERE course_id=35;
-- SESSION 2
UPDATE imc_course SET score=9.8 WHERE course_id=35;
UPDATE imc_user SET score=score+10 WHERE user_id=10;
show variables like 'general_log_file';
show variables like 'log_error';
show variables like 'slow_query_log_file';
Sunbelt Computer Software

PL/B Language Development and Support

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Sunbelt Computer Software

PL/B Language Development and Support

FilesExpand file tree

select.sql

Latest commit

History

select.sql

File metadata and controls
