【SQL】基本语句

最新推荐文章于 2022-03-17 22:27:43 发布

Francis-Leo

最新推荐文章于 2022-03-17 22:27:43 发布

阅读量328

点赞数 1

分类专栏： ………数据库

本文链接：https://blog.csdn.net/Francis123580/article/details/79071722

版权

………数据库专栏收录该内容

17 篇文章 0 订阅

订阅专栏

定义

[ WITH < common_table_expression > ]                        /* 指定临时命名的结果集 */
SELECT [ ALL | DISTINCT ] 
             [ TOP expression [ PERCENT ] [ WITH TIES] ]                                
    < select_list >                                         /* 指定要选择的列 */
    [ INTO new_talbe ]                                      /* INTO子句，指定结果存入新表 */
    [ FROM table_source ]                                   /* FROM子句，指定表或视图 */
    [ WHERE search_condition ]                              /* WHERE子句，指定查询条件 */
    [ GROUP BY group_by_expression ]                        /* GROUP BY子句，指定分组表达式 */
    [ HAVING search_condition ]                             /* HAVING子句，指定分组统计条件 */
    [ ORDER BY order_expression [ ASC | DESC ] ]            /* ORDER BY子句，指定排序表达式和顺序 */

简单的查询

/* 选择列 */
SELECT * FROM tableA        /* 查询所有 */
SELECT id, name, sex FROM tableA        /* 查询id列，name列 */

/* 定义列别名 */
SELECT id AS iid, name as iname FROM tableA     /* 结果集的列名为iid，iname */
SELECT id=iid, name=iname FROM tableA /* 效果同上 */

/* 替换数据 */
SELECT id, name, age= 
    CASE
        WHEN age ISNULL THEN '未知'
        WHEN age < 18 THEN '未成年'
        WHEN age >= 18 THEN '成年'
        ELSE '非法年龄'
FROM tableA

/* 计算列值 */
SELECT id,name,age=age*10 FROM tableA WHERE id='1111'       /* 年龄10倍显示 */

去重和限制条数

/* 去除重复行 */
SELECT DISTINCT name,age FROM tableA

/* 限制返回行数 */
SELECT TOP 10 id,name FROM tableA       /* 返回前10名 */

简单函数

/* 平均数 */
SELECT AVG(score) AS 'avg_score' FROM tableA WHERE course = 'math'      /* 数学成绩的平均数 */

/* 总和 */
SELECT SUM(score) AS 'sum_score' FROM tableA WHERE id='000' /* id为000的总成绩 */

/* 最大值最小值 */
SELECT  MAX(score) AS 'max_score', MIN(score) AS 'min_score' FROM tableA 

/* 总条数 */
SELECT COUNT(*) AS 'taotal_amount' FROM tableA
SELECT COUNT(score) AS 'good' FROM tableA WHERE score > 60      /* 及格人数 */

范围比较

/* 表达式比较 */
SELECT * FROM tableA WHERE major='art' AND socre>60

/* 模式匹配 */
SELECT id,name FROM tableA WHERE name LIKE '王_'

/* 范围比较 */
SELECT id,name FROM tableA WHERE birth NOT BETWEEN '1990-11-11' AND '1991-11-11'
SELECT id,name FROM tableA WHERE major IN('math','english','chinese')

/* 空值比较 */
SELECT * FROM tableA WHERE sore ISNULL

嵌套查询

/* 子查询 */ 
SELECT * FROM tableA WHERE score IN (SELECT score FROM tableB WHERE course='math')

/* 比较子查询 */
SELECT * FROM tableA WHERE birth < ALL(SELECT birth FROM tableA WHERE marjor='math')         /* 所有 */
SELECT * FROM tableA WHERE major='math' AND score !< ANY(SELECT score FROM tableA WHERE marjor='english')       /* 任何一个 */

/* 结果集作为查询范围 */
SELECT name,score FROM (SELECT name,sex,score FROM tableA WHERE score>90) AS student WHERE sex = 'male'

/* INNER JOIN */
SELECT * FROM tableA INNER JOIN tableB ON tableA.id = tableB.id WHERE tableA.score>90

/* LEFT OUTER JOIN | RIGHT OUTER JOIN | FULL OUTER JOIN */
SELECT tableA.* FROM tableA LEFT OUTER JOIN tableB ON tableA.id = tableB.id

数据筛选

/* 分组 */
SELECT major,AVG(score)AS avgscore COUNT(*)AS Amont FROM tableA GROUP BY magor

/* 分组+结果集 */
SELECT marjor,sex FROM tableA GROUP BY marjor,sex WITH ROLLUP

/* HAVING筛选 */
SELECT major,ROUND(AVG(score),2),COUNT(id) FROM tableA GROUP BY major HAVING AVG(score) > 90

/* ORDER排序 */
SELECT * FROM tableA WHERE sex = 'male' ORDER BY score DESC