sql入门

SQL语句分为四类：

DDL(Data Definition Language) ：数据定义语言，用来定义数据库对象（数据库，表，字段）
DML(Data Danipulation Language)： 数据操作语言，用来对数据库中的数据进行增删改查
DQL(Data Query Language) ：数据查询语言，用来查询数据库中表的记录
DCL(Data Conrtol Language) ：数据控制语言，用来创建数据库用户，控制数据库的访问权限

DDL操作（对数据库进行操作）

数据库操作

连接：

查询:

        show databases;select database();--查询当前数据库

使用：

        use database_name；

创建:

        create database [if not exists] database_name [DEFAULT CHARSET 字符集] [COLLATE 排序规则] ;

删除:

        drop database [if exists] database_name;

数据表操作

创建:

        create table table_name (culumn_name culumn_type,..........)[with (appendonly=true,compresslevel=1~10) distributed randomly--]

查询:

        use mytest; --选择mytest数据库

        desc table_name; --查看表结构

        show create table table_name;--查询指定表的创建语句

修改:alter

        添加表字段:alter table table_name add column_name column_type [comment 注释][约束]

        修改数据表字段类型: alter table table_name modify column_name column_type;

        修改字段名字段类型：alter table table_name old_column_name new_column_name                                                 column_type [comment 注释][约束];

         修改表名:alter table table_name rename to new_table_name;

删除:

        删除字段:alter table table_name drop column_name;

        删除数据表:truncate table table_name; --删除指定表，并重新创建该表

                           drop table [if exists] table_name; --删除数据表

DML操作（增删改）+DQL（查）

        1、“INSERT INTO”语句，用于向表格中增加新的行；

                INSERT INTO TABLENAME VALUES (值1,....)
        2、“DELETE”语句，用于删除表中的行；

                DELETE FROM TABLENAME WHERE columnname = 值
        3、“Update”语句，用于修改表中的数据；

                UPDATE TABLENAME SET columnname = 新值 WHERE columnname = 某值
        4、“SELECT”语句，用于从表中选取数据

                SELECT columnname FROM TABLENAME

条件查询

        SELECT columnname FROM TABLENAME where 查询条件；

          条件运算符    功能

1.                 >    大于
2.                 >=    大于等于
3.                 <    小于
4.                 <=    小于等于
5.                 =    等于
6.                 <> 或 !=    不等于
7.         BETWEEN ... AND ...    在某个范围之内(含最小、最大值)
8.                 IN(...)    在in之后的列表中的值，多选1
9.                 LIKE 占位符    模糊匹配(_匹配单个字符，%匹配任意个字符)
10.                 IS NULL    是NULL
11.                 AND 或 &&    并且(多个条件同时成立)
12.                 OR 或 ||    或者 (多个条件任意一个成立)
13.                 NOT 或 !    非，不是

聚合函数

常见的聚合函数有：**所有的聚合函数都不计算NULL值。

1. count：统计数量；

2. max：最大值；

3. min：最小值；

4. avg：平均值；

5. sum：求和。

字符串函数

• CONCAT(): 连接两个或多个字符串。
• LENGTH(): 返回字符串的长度。
• UPPER(): 将字符串转换为大写。
• LOWER(): 将字符串转换为小写。
• TRIM(): 去除字符串两侧的空格。
• SUBSTRING(): 提取字符串的子串。

数值函数

• ROUND(): 对数值进行四舍五入。
• CEILING(): 向上取整。
• FLOOR(): 向下取整。
• ABS(): 返回数值的绝对值。
• MOD(): 返回两数相除的余数。

日期和时间函数

• NOW(): 返回当前日期和时间。
• CURDATE(): 返回当前日期。
• CURTIME(): 返回当前时间。
• DATE_FORMAT(): 格式化日期/时间值。
• DATEDIFF(): 返回两个日期之间的天数差。
• DAY(), MONTH(), YEAR(): 分别返回日期中的日、月、年部分。

系统函数

• USER(): 返回当前MySQL用户名和主机名。
• DATABASE(): 返回当前数据库名。
• VERSION(): 返回数据库服务器的版本。

其他函数

• IFNULL(value, replace_value): 如果value为NULL，则返回replace_value；否则返回value。
• COALESCE(value1, value2, ...): 返回参数列表中的第一个非NULL值。
• CASE: 条件表达式，用于在查询中执行复杂的条件逻辑。

分组查询

SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];  

WHERE与HAVING区别：

1. 执行时间不同：WHERE是分组之前进行过滤，不满足WHERE条件，不参与分组；而HAVING是分组之后对结果进行过滤；

2. 判断条件不同：WHERE 不能对聚合函数进行判断，而HAVING可以 ;

注意：

1. 执行顺序：WHERE>聚合函数>HAVING；

2. 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

排序

select * from table_name order by id [desc];desc降序排序，默认升序

分页查询

SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;

注意：

1. 起始索引从0开始，起始索引=(查询页码-1)*每页显示记录数；

2. 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT；

3. 当查询的是第一页数据，起始索引可以省略，直接简写为LIMIT 10。

sql执行顺序

以下是SQL查询的逻辑执行顺序（通常用于解释和解释计划）：

1. FROM 和 JOIN

   • 数据库首先确定要从哪些表中检索数据，并执行JOIN操作。
   • 在这一步，会生成一个虚拟表。

2. WHERE

   • 对虚拟表中的行进行过滤，只保留满足WHERE条件的行。

3. GROUP BY

   • 对虚拟表中的行进行分组。
   • 通常与聚合函数（如SUM, COUNT, AVG等）一起使用。

4. HAVING

   • 对分组后的结果进行过滤。
   • HAVING子句通常与GROUP BY一起使用，用于过滤分组后的结果。

5. SELECT

   • 选择要显示的列。
   • 如果使用了聚合函数，则在此步骤中进行计算。
   • 这一步还包括DISTINCT去重操作。

6. ORDER BY

   • 对结果集进行排序。
   • 这是一个消耗资源的操作，特别是在大数据集上。

7. LIMIT/OFFSET（某些数据库支持）

   • 限制返回的行数。
   • 通常用于分页查询。

GYK特供

1. 简单的 CASE 语句

简单的 CASE 语句将表达式与一系列简单表达式进行比较，并返回与第一个匹配项相关联的结果。如果没有匹配项，它将返回 ELSE 子句中的值（如果有的话）。

	SELECT         column_name,
	CASE column_name
	WHEN value1 THEN result1
	WHEN value2 THEN result2
	...
	ELSE result
	END AS new_column_name
	FROM table_name;

示例

假设我们有一个名为 employees 的表，其中包含 salary_level 列，我们想根据 salary_level 的值来返回员工的薪资等级描述：

	SELECT employee_name, salary_level,
	CASE salary_level
	WHEN 1 THEN 'Low'
	WHEN 2 THEN 'Medium'
	WHEN 3 THEN 'High'
	ELSE 'Unknown'
	END AS salary_level_description
	FROM employees;

2. 搜索的 CASE 语句

搜索的 CASE 语句允许你使用复杂的条件表达式（不仅仅是相等性检查）来确定返回哪个结果。

	SELECT column_name,
	CASE
	WHEN condition1 THEN result1
	WHEN condition2 THEN result2
	...
	ELSE result
	END AS new_column_name
	FROM table_name;

示例

假设我们还想根据员工的薪资是否高于某个阈值来分类他们：

	SELECT employee_name, salary,
	CASE
	WHEN salary < 30000 THEN 'Low Salary'
	WHEN salary BETWEEN 30000 AND 60000 THEN 'Medium Salary'
	WHEN salary > 60000 THEN 'High Salary'
	ELSE 'Unknown'
	END AS salary_category
	FROM employees;

注意事项

• CASE WHEN 语句在 SELECT、UPDATE、DELETE 和 SET 等 SQL 语句中都可以使用。
• 在 CASE 语句中，条件会按照它们在语句中出现的顺序进行评估，一旦找到匹配的条件，就会返回相应的结果，并且不会评估后续的条件。
• 如果 CASE 语句中没有 ELSE 子句，并且没有条件匹配，那么将返回 NULL。

开窗函数

开窗函数（也称为窗口函数或 OLAP 函数）允许用户对数据集的子集（称为窗口）执行计算，而不需要使用 GROUP BY 子句进行分组。这些函数通常与 OVER() 子句一起使用，以定义窗口的范围和如何对其进行分区。

以下是一些常用的 PostgreSQL 开窗函数：

1. ROW_NUMBER()

   • 为结果集中的每一行分配一个唯一的序号。

   	SELECT name, age, ROW_NUMBER() OVER (ORDER BY age DESC) AS row_num
   	FROM people;

2. RANK() 和 DENSE_RANK()

   • 为结果集中的每一行分配一个排名。两者之间的主要区别在于处理并列排名的方式。

   	SELECT score, RANK() OVER (ORDER BY score DESC) AS rank
   	FROM scores;

   	SELECT score, DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
   	FROM scores;

3. LEAD() 和 LAG()

   • 访问结果集中当前行的前一行或后一行的值。

   	SELECT date, value, LAG(value) OVER (ORDER BY date) AS prev_value
   	FROM daily_data;

   	SELECT date, value, LEAD(value) OVER (ORDER BY date) AS next_value
   	FROM daily_data;

4. SUM()、AVG()、MIN()、MAX() 作为开窗函数

   • 与常规聚合函数类似，但允许在窗口级别进行计算。

   	SELECT date, sales, SUM(sales) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sales
   	FROM daily_sales;

5. FIRST_VALUE() 和 LAST_VALUE()

   • 返回窗口中第一行或最后一行的值。

   	SELECT date, sales, FIRST_VALUE(sales) OVER (PARTITION BY year ORDER BY date) AS first_sale_of_year
   	FROM daily_sales;

6. NTILE(n)

   • 将有序分区中的行分配到指定数量的近似相等的组中，并为每行返回其组的编号（从 1 到 n）。

   	SELECT score, NTILE(4) OVER (ORDER BY score DESC) AS quartile
   	FROM scores;

        在使用 OVER() 子句时，可以指定 PARTITION BY 子句来定义窗口的分区方式。如果省略 PARTITION BY，则整个结果集被视为一个单一的窗口。

        此外，OVER() 子句还允许你使用 ROWS 或 RANGE 子句来定义窗口的帧规范，从而控制哪些行包含在窗口中。例如，ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 表示窗口从当前分区的第一行开始，到当前行结束。

DCL操作（管理数据库用户，访问权限）

用户控制

创建用户：

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

eg:CREATE USER 'myuser'@'localhost' IDENTIFIED BY '123456';        #创建名为myuser的用户

修改用户:

ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';

eg:ALTER USER 'myuser'@'localhost' IDENTIFIED WITH mysql_native_password BY '123'  #修改用户密码

删除用户：

DROP USER '用户名'@'主机名';

eg:DROP USER 'myuser'@'localhost';                 #删除用户

控制权限

权限	说明
ALL,ALL PRIVILEGES	所有权限
SELECT	查询数据
INSERT	插入数据
UPDATE	修改数据
DELETE	删除数据
ALTER	修改表
DROP	删除数据库/表/视图
CREATE	创建数据库/表

注意：

1. 多个权限之间，使用逗号分隔；

2. 授权时，数据库名和表名可以使用*进行通配，代表所有。

查询权限：

        SHOW GRANTS FOR '用户名'@'主机名';

        eg：SHOW GRANTS FOR 'myuser'@'localhost';            #查看用户权限

授予权限：

        GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';

        GRANT ALL ON mytest.students TO 'myuser'@'localhost';  #给用户myuser授予mytest数据库中的students数据表全部权限
        SHOW GRANTS FOR 'myuser'@'localhost';                #查询用户权限

撤销权限：

        REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

        REVOKE ALL ON mytest.students FROM 'myuser'@'localhost';    #用户myuser撤销mytest数据库中的students数据表全部权限
        SHOW GRANTS FOR 'myuser'@'localhost';                #查询用户权限