SQL语句

插入語句

INSERT INTO [TABLE_NAME] (column1, column2, column3,...columnN) VALUES (value1, value2, value3,...valueN);

修改語句

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

删除語句

DELETE FROM [table_name] WHERE [condition];

数据的查询

SELECT column1, column2, columnN FROM table_name;

---

• 数据查询

   （一）简单查询

1、基本查询

从表中查询某些列的值，这是最基本的查询语句：

SELECT 列名1,列名2 FROM 表名

2、Where（条件）

按照一定的条件查询数据

SELECT 列名1,列名2  FROM 表名 WHERE 列名1  运算符  值

例：SELECT MC FROM [GALAXY].[dbo].[LACK_EMAIL] WHERE MC = '126'

=	等于
<>	不等于
>	大于
<	小于
>=	大于等于
<=	小于等于
BETWEEN	在某个范围内 在两个值之间，比如我从学生中查询年龄在18-20之间的学生信息 例：SELECT MC,MC_NAME,OPID FROM [GALAXY].[dbo].[LACK_EMAIL] WHERE MC BETWEEN '126' AND '140'
LIKE	通配符 描述 % 替代一个或多个字符 例：查询名字中含有“生”的学生信息 SELECT ID,Name FROM Students WHERE Name LIKE '%生%' _ 仅替代一个字符 例：查询姓名为两个字，且姓张学生信息 SELECT ID,Name FROM Students WHERE Name LIKE '张_' [charlist] 字符列中的任何单一字符 例：查询姓氏为张、李的学生信息 SELECT ID,Name FROM Students WHERE Name LIKE '[张李]%' [^charlist] 或者 [!charlist] 不在字符列中的任何单一字符 例：查询姓氏非张、李的学生信息 SELECT ID,Name FROM Students WHERE Name LIKE '[^张李]%' 或者： SELECT ID,Name FROM Students WHERE Name LIKE '[!张李]%' 作用：模糊查询。LIKE关键字与通配符一起使用
AND	AND 在 WHERE 子语句中把两个或多个条件结合起来。表示和的意思，多个条件都成立 例：SELECT MC,MC_NAME,OPID,email,memail FROM [GALAXY].[dbo].[LACK_EMAIL] WHERE MC>'136' and MC_NAME LIKE '葉%'
OR	OR可在 WHERE 子语句中把两个或多个条件结合起来。或关系，表示多个条件，只有一个符合即可。 例： SELECT MC,MC_NAME,OPID,email,memail FROM [GALAXY].[dbo].[LACK_EMAIL] WHERE MC>'136' OR MC_NAME LIKE '葉%'
NOT	NOT对于条件的否定，取非。 例：SELECT MC,MC_NAME,OPID,email,memail FROM [GALAXY].[dbo].[LACK_EMAIL] WHERE MC_NAME NOT LIKE '葉%'
IN	IN 操作符允许我们在 WHERE 子句中规定多个值。表示：在哪些值当中。 例：查询年龄是18、19、20的学生信息 SELECT ID,Name FROM Students WHERE Age IN (18,19,20)

3.ORDER BY（排序）

对需要查询后的结果进行排序

例： SELECT MC,MC_NAME,OPID,email,memail FROM [GALAXY].[dbo].[LACK_EMAIL] ORDER BY MC ASC

标识	含义	说明
ASC	升序	默认
DESC	倒序

4.AS(Alias 别名)

我们可以将查询的列，或者表指定需要的名字，如表名太长，用其简称，在连表查询中经常用到。

1) 将结果列改为需要的名称

例：SELECT ID AS StudentID,Name AS StudentName FROM Students

2）用表名的别名，标识列的来源

例：SELECT S.ID,S.Name,M.Name AS MajorName FROM Students AS S LEFT JOIN Majors AS MON S.MajorID = M.ID

3)在合计函数中，给合计结果命名

例：SELECT COUNT(ID) AS StudentCount FROM Students

       5、Distinct

查询时忽略重复值。

SELECT DISTINCT 列名称 FROM 表名称

1. 查询学生所在城市名，排除重复

例：SELECT DISTINCT City FROM Student

1. 查询成绩分布分布情况

例： SELECT DISTINCT(Score),Count(ID) FROM Student GROUP BY Score

6、MAX/MIN

MAX 函数返回一列中的最大值。NULL 值不包括在计算中。

MIN 函数返回一列中的最小值。NULL 值不包括在计算中。

例：SELECT MIN (MC) FROM [GALAXY].[dbo].[LACK_EMAIL]

7、AVG

AVG 函数返回数值列的平均值

例：SELECT AVG(Score) FROM SC WHERE CourseID='C001'

8.COUNT

COUNT() 函数返回匹配指定条件的行数。

例：SELECT  COUNT (city) FROM [dbo].[userInfo]

     

     9、GROUP BY

GROUP BY 语句用于结合合计函数，根据一个或多个列对结果集进行分组。

例：SELECT City,COUNT(ID) FROM Students GROUP BY City

10、HAVING

在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用。

SELECT column_name, aggregate_function(column_name)

FROM table_name

WHERE column_name operator value

GROUP BY column_name

HAVING aggregate_function(column_name) operator value

例：查询平均成绩大于等于60，且学生ID等于1的学生的ID及平均成绩。

SELECT StudentID,AVG(Score)

FROM SC

WHERE StudentID='1' 

GROUP BY StudentID

HAVING AVG(Score)>=60

11、TOP

TOP 子句用于规定要返回的记录的数目。对于大数据很有用的，在分页时也会常常用到。

例：查询年龄最大的三名学生信息

SELECT TOP 3 ID,Name FROM Students ORDER BY Age DESC

或

SELECT ID,Name,Age FROM Students WHERE Age IN (SELECT TOP 3 Age FROM Students)

          

12.CASE語句

计算条件列表，并返回多个可能的结果表达式之一。
CASE 表达式有两种格式：

CASE 简单表达式，它通过将表达式与一组简单的表达式进行比较来确定结果

CASE input_expression

          WHEN when_expression THEN result_expression [ ...n ]

          [ ELSE else_result_expression ] END

CASE 搜索表达式，它通过计算一组布尔表达式来确定结果

         CASE

          WHEN Boolean_expression THEN result_expression [ ...n ]

          [ ELSE else_result_expression ] END

例:查询学生信息，根据年龄统计是否成年，大于等于18为成年，小于18为未成年

SELECT ID, Name, CASE WHEN Age>=18 THEN '成年' ELSE '未成年'END AS 是否成年FROM Students

例：统计男女生中未成年、成年的人数

SELECT CASE WHEN Sex=0 THEN '男' ELSE '女' END AS '性别',

SUM(CASE WHEN Age<18 THEN 1 ELSE 0 END) AS '未成年',

SUM(CASE WHEN Age>=18 THEN 1 ELSE 0 END) AS '成年'

FROM StudentsGROUP BY Sex

• 连表查询

1. Union

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。

当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。

注意：使用UNION时，两张表查询的结果有相同数量的列、列类型相似。

例：基本UNION查询，查询学校教师、学生的总的信息表，包括ID和姓名

SELECT ID,Name FROM Students

UNION

SELECT ID,Name FROM Teachers

  

2.INNER JOIN

INNER JOIN（内连接），也成为自然连接

作用：根据两个或多个表中的列之间的关系，从这些表中查询数据。

注意: 内连接是从结果中删除其他被连接表中没有匹配行的所有行，所以内连接可能会丢失信息。

重点：内连接，只查匹配行。

      SELECT fieldlist

FROM table1

[INNER]（可省）

join table2

ON table1.column=table2.column

例：实例：查询学生信息，包括ID,姓名、专业名称

SELECT Students.ID,Students.Name,Majors.Name AS MajorName

FROM Students INNER JOIN Majors

ON Students.MajorID = Majors.ID

3.外连接

与内连接相比，即使没有匹配行，也会返回一个表的全集。

（内连接的两个表只会返回匹配起的行，但是外连接，以左外连接为例，左边的那个表将会返回所有行，无论是否匹配，不能匹配的用null占位，右边的只会返回匹配的行）

（1）LEFT JOIN 左外连接

结果集保留左表的所有行，但只包含第二个表与第一表匹配的行。第二个表相应的空行被放入NULL值。

例：使用左连接查询学生的信息，其中包括学生ID，学生姓名和专业名称。

SELECT Students.ID,Students.Name,Majors.Name AS MajorName

FROM Students LEFT JOIN Majors 

ON Students.MajorID = Majors.ID

（2）RIGHT JOIN 右外连接

（3）FULL JOIN 全连接

会把两个表所有的行都显示在结果表中

例：使用全连接查询学生的信息，其中包括学生ID，学生姓名和专业名称。

SELECT Students.ID,Students.Name,Majors.Name AS MajorName

FROM Students FULL JOIN Majors

ON Students.MajorID = Majors.ID

     

4、CROSS JOIN

交叉连接。交叉连接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉连接也称作笛卡尔积。 

笛卡儿积：笛卡尔乘积，也叫直积。假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1), (b,2)}。可以扩展到多个集合的情况。类似的例子有，如果A表示某学校学生的集合，B表示该学校所有课程的集合，则A与B的笛卡尔积表示所有可能的选课情况。

例：交叉连接查询学生的信息，其中包括学生ID，学生姓名和专业名称。

SELECT Students.ID,Students.Name,Majors.Name AS MajorName

FROM Students CROSS JOIN Majors

• 数据删除

1. delete 

删除表中的行：delete语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存

DELETE FROM 表名称 WHERE 列名称 = 值，

例：delete from [test].[dbo].[userInfo] where username = '2'

2.Drop

删除表：删除内容和定义，释放空间。简单来说就是把整个表去掉.以后要新增数据是不可能的,除非新增一个表。

   

3.Truncate

清空表中的数据：删除内容、释放空间但不删除定义(保留表的数据结构)。与drop不同的是,只是清空表数据而已。

注意:truncate 不能删除行数据,要删就要把表清空。

• 数据增加

1. INSERT INTO

用于向表格中插入新的行。

INSERT INTO 表名称 VALUES (值1, 值2,....)

INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)

例：INSERT INTO [test].[dbo].[person](name,age,sex) VALUES ('liliya',20,'男')

• 数据修改

1、UPDATE

修改表中现有的记录

UPDATE [table_name]

SET column1 = value1, column2 = value2...., columnN = valueN

例：UPDATE [test].[dbo].[person] set name = 'jan' where name = 'JAN'

• 函數

1、concat()函数

功能:将多个字符串连接成一个字符串。

语法:concat(str1, str2,…) 

例：SELECT a.inputid,concat('DDBI',a.inputid) AS CustomsNumber,a.vendorcode, a.vendorname,b.operationtime,

                (CASE WHEN a.state ='10' THEN '暫存' 

                      WHEN a.state ='20' THEN '人工審核中' 

                      WHEN a.state ='30' THEN '審核通過' 

                      WHEN a.state ='40' THEN '退件' 

                      WHEN a.state ='50' THEN '海關通過' 

                      ELSE '异常' END) AS state

  FROM  i90005_imagerecognition_head a INNER JOIN i90005_imagerecognition_flow b

   on  a.inputid = b.inputid

   where a.vendorcode = 'MS112CN3' and b.state = '10'