平常工作总结-sql常用

不念不忘少年蓝@ 2024-04-01 19:30 87阅读 0赞

## sql常用 ##

https://zhuanlan.zhihu.com/p/43036664?utm\_source=qq&utm\_medium=social&utm\_oi=986993813709279232

https://blog.csdn.net/qq\_40233706/article/details/107246777

修改表名：alter table aa rename to bb  
添加列：执行下列命令，列会默认添加到表字段末尾：alter table 表名 add column 列名 varchar(30);  
删除列：alter table 表名 drop column 列名;  
修改列名：alter table 表名 change 列名 新列名 varchar(30);  
达梦数据库修改列名：alter table 表名 rename column 列名 to 新列名;  
修改列属性：alter table 表名 modify 列名 varchar(22);  
修改字段默认值：alter table 表名 alter 字段名 set default 1000;  
查看表中字段的默认值：show columns from 表名  
查看表：desc 表名;  
增加主键：alter table 表名 add constraint primary key (主键id)

### 库 ###

#### 1. 查看现有数据库 ####

`SHOW DATABASES;`

#### 2. 新建数据库 ####

`CREATE DATABASE <数据库名>;`

#### 3. 选择数据库 ####

`USE <数据库名>;`

#### 4. 从.sql文件引入SQL语句 ####

`SOURCE <.sql文件路径>;`

#### 5. 删除数据库 ####

`DROP DATABASE <数据库名>;`

### 表 ###

#### 6. 查看当前数据库中的表 ####

`SHOW TABLES;`

#### 7. 创建新表 ####

CREATE TABLE <表名> (
        <列名1> <列类型1>,
        <列名2> <列类型2>,
        <列名3> <列类型3>,
        PRIMARY KEY (<列名1>),
        FOREIGN KEY (<列名2>) REFERENCES <表名2>(<列名2>)
    );

主键（PRIMARY KEY）用来标识一条记录（一行），所以每条记录的主键值必须是唯一的。主键可以定义在多列上，这称为联合主键（composite primary key）。  
如果我们把表视作具有某种结构的数组（例如，C语言中的struct），那么外键（FOREIGN KEY）可以视作指针。  
例子：

CREATE TABLE instructor (
        ID CHAR(5),
        name VARCHAR(20) NOT NULL,
        dept_name VARCHAR(20),
        salary NUMERIC(8,2), 
        PRIMARY KEY (ID),
        FOREIGN KEY (dept_name) REFERENCES department(dept_name));

在上面的例子中，我们创建了一个教员（instructor）表，该表的主键是ID，外键是教员所在的部门名称（dept\_name），关联部门（department）表。此外，教员表还包括姓名（name）、薪水（salary）。其中，姓名有约束NOT NULL，表示姓名这一项不能为空。

#### 8. 概述表中的列 ####

使用如下语句查看表中的列的基本信息：

`DESCRIBE <表名>;`

#### 9. 在表中插入新纪录 ####

`INSERT INTO <表名> (<列名1>, <列名2>, <列名3>, …) VALUES (<值1>, <值2>, <值3>, …);`  
也可以省略列名（依序在所有列上插入新值）：  
`INSERT INTO <表名> VALUES (<值1>, <值2>, <值3>, …);`

#### 10. 在表中更新记录 ####

`UPDATE <表名> SET <列名1> = <值1>, <列名2> = <值2>, ... WHERE <条件>;`

#### 11. 清空表 ####

`DELETE FROM <表名>;`

#### 12. 删除表 ####

`DROP TABLE <表名>;`

### 查询 ###

#### 13. SELECT ####

SELECT语句可以从表中选择数据：

`SELECT <列名1>, <列名2>, … FROM <表名>;`  
以下语句选择所有内容：

`SELECT * FROM <表名>;`

#### 14. SELECT DISTINCT ####

SELECT DISTINCT过滤掉了重复的值：

`SELECT DISTINCT <列名1>, <列名2>, … FROM <表名>;`

#### 15. WHERE ####

我们之前在更新记录时已经用到了WHERE关键字，用来指明条件。这里我们稍微详细一点地介绍下WHERE。

WHERE的条件通常是：

比较文本（text）  
比较数字（numbers）  
AND、OR、NOT等逻辑运算  
让我们来看一些例子：

SELECT * FROM course WHERE dept_name='Comp. Sci.';
    SELECT * FROM course WHERE credits>3;
    SELECT * FROM course WHERE dept_name='Comp. Sci.' AND credits>3;

#### 16. GROUP BY ####

GROUP BY语句可以分组结果，常用于COUNT、MAX、MIN、SUM、AVG等聚合函数（aggregate functions）。

SELECT <列名1>, <列名2>, …
        FROM <表名>
        GROUP BY <列名>;

让我们来看一个例子，列出每个部门的课程数量：

SELECT COUNT(course_id), dept_name 
         FROM course 
         GROUP BY dept_name;

#### 17. HAVING ####

乍看起来，HAVING和WHERE很像：

SELECT <列名1>, <列名2>, …
        FROM <表名>
        GROUP BY <列名x>
        HAVING <条件>;

那么，HAVING和WHERE有什么不同呢？让我们先来看一个例子，列出开了不止一门课程的部门开设的课程数：

SELECT COUNT(course_id), dept_name 
        FROM course 
        GROUP BY dept_name 
        HAVING COUNT(course_id)>1;

这里HAVING不能换成WHERE，因为WHERE直接针对行操作，且在GROUP BY之前运行（即先通过WHERE筛选行，之后再将筛选出的行通过GROUP BY分组）。假设SQL中不存在HAVING语句，那么我们只能先新建一张表，将COUNT(course\_id)作为新表的列，然后在新表上再通过WHERE进行筛选（当然，实际上SQL提供了派生表、CTE等机制，并不用真的手工建新表）。

#### 18. ORDER BY ####

ORDER BY可以对结果进行排序，在没有明确指定ASC（升序）或DESC（降序）的情况下，默认按升序排列。

SELECT <列名1>, <列名2>, …
    FROM <表名>
    ORDER BY <列名1>, <列名2>, …, ASC|DESC;

例子：

`SELECT * FROM course ORDER BY credits;`  
`SELECT * FROM course ORDER BY credits DESC;`

#### 19. BETWEEN ####

BETWEEN语句用于指定区间。

SELECT <列名1>, <列名2>, …
        FROM <表名>
        WHERE <列名x> BETWEEN <值1> AND <值2>;

其中“值”可能是数字，文本，乃至日期等。

例如，列出薪资在50000和100000之间的教员：

`SELECT * FROM instructor WHERE salary BETWEEN 50000 AND 100000;`

#### 20. LIKE ####

LIKE用于匹配文本中的特定模式。

SELECT <列名1>, <列名2>, …
        FROM <表名>
        WHERE <列名x> LIKE <模式>;

模式中可以使用以下两个通配符：

% （零个、一个或多个字符）  
\_ （单个字符）  
例子：列出课程名中包含“to”的课程，以及课程ID以“CS-”开头的课程。

`SELECT * FROM course WHERE title LIKE '%to%';`  
`SELECT * FROM course WHERE course_id LIKE 'CS-___';`

#### 21. IN ####

IN语句表示值属于某个集合。

SELECT <列名1>, <列名2>, …
        FROM <表名>
        WHERE <列名n> IN (<值1>, <值2>, …);

例子：列出计算机科学、物理、电子工程部门的学生。  
`SELECT * FROM student WHERE dept_name IN ('Comp. Sci.', 'Physics', 'Elec. Eng.');`

#### 22. JOIN ####

JOIN用来组合两张以上表中的值。下图展示了JOIN的三种类型：  
\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-1gHmDgwM-1668611577268)(\_v\_images/20211216113840514\_25975.png =500x)\]

1、Join:两张表关联后，查询结果有交集时才会取值。
    
    2、Left Join：两张表关联后，即使查询结果没有交集，也会取值(如下：会把A中的全部查出，若B中没有重合的，则对应的取值空)。无数值时用Null填充。
    select * from A a left join B b on a.bid=b.id
    
    3、right join与left join 类似

\------------例子-----------------

SELECT <列名1>, <列名2>, …
        FROM <表名1>
        JOIN <表名2>
        ON <表名1.列名x> = <表名2.列名x>

让我们来看三个例子，分别对应三种JOIN的类型。

第一个例子，列出课程时包含开设课程的部门详情：

SELECT * FROM course 
        JOIN department 
        ON course.dept_name=department.dept_name;

第二个例子，列出所有具有前置课程的课程的详情：

SELECT prereq.course_id, title, dept_name, credits, prereq_id 
        FROM prereq 
        LEFT OUTER JOIN course 
        ON prereq.course_id=course.course_id;

最后一个例子，列出所有课程的详情，不管是否具有前置课程：

SELECT course.course_id, title, dept_name, credits, prereq_id 
        FROM prereq 
        RIGHT OUTER JOIN course 
        ON prereq.course_id=course.course_id;

#### 23. 视图 ####

视图（view）是虚拟的SQL表。它包含行和列，和一般的SQL表格很类似。视图总是显示数据库中的最新数据。

`CREATE VIEW`

创建视图：

CREATE VIEW <视图名> AS
        SELECT <列名1>, <列名2>, …
        FROM <表名>
        WHERE <条件>;
    DROP VIEW

删除视图：

`DROP VIEW <视图名>;`  
例如，创建3学分的课程视图：

CREATE VIEW my_view AS
        SELECT * FROM course
        WHERE credits=3;

#### 24. 聚合函数 ####

我们之前已经提到聚合函数，这里列出最常用的一些聚合函数：

COUNT(列名) 返回行数
    SUM(列名) 返回指定列的值之和
    AVG(列名) 返回指定列的平均值
    MIN(列名) 返回指定列的最小值
    MAX(列名) 返回指定列的最大值

#### 25. 嵌套子查询 ####

在SQL请求中，可以嵌套SELECT-FROM-WHERE表达式，称为嵌套子查询（nested subqueries）。

例如，查找2009年秋、2010年春都开的课程：

SELECT DISTINCT course_id 
        FROM section 
        WHERE semester = ‘Fall’ AND year= 2009 AND course_id IN (
            SELECT course_id 
                FROM section 
                WHERE semester = ‘Spring’ AND year= 2010
        );

#### 26. 常用关键字 ####

1、**查询应返回 null**，使用 ifNull（查询，null）方法。第一个参数为空，则取空，不为空，则取出

2、去重，如果有多个相同的数据，使用关键字 **distinct 去重**

3、 **limit**(1,1)。第一个参数默认是0 。这个意思是起始页1，每页多少数据。或者说是取出第二条数据

4、**求平均值**：select AVG(math+english+chinese) AS 总平均分 FROM score

5、\*\*round(x,y) \*\*函数对参数x进行四舍五入的操作，返回值保留小数点后面指定的y位。注：y为0时，即ROUND(X)，返回四舍五入后的整数部分。

6、limit后面不能是表达式，否则报错：select \* from `tab` limit (3-1)\*5, 5; **报错**  
7、Rank、DENSE\_RANK()的区别

相同点：RANK()和DENSE_RANK()的是排名函数
    不同点：
    RANK()是跳跃排序,即如果有两条记录重复,接下来是第三级别
    如：1 2 2 4,会跳过3
    DENSE_RANK()是连续排序,即如果有两条记录重复，接下来是第二级别
    如：1 2 2 3

) \*\*函数对参数x进行四舍五入的操作，返回值保留小数点后面指定的y位。注：y为0时，即ROUND(X)，返回四舍五入后的整数部分。

6、limit后面不能是表达式，否则报错：select \* from `tab` limit (3-1)\*5, 5; **报错**  
7、Rank、DENSE\_RANK()的区别