SQL常用基础语句（SQL常用增删改查语句及案例）

一、添加数据

• 语法：
  * insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n);

• 注意：
  （1） 列名和值要一一对应。
  （2） 如果表名后，不定义列名，则默认给所有列添加值
  insert into 表名 values(值1,值2,...值n);
  （3） 除了数字类型，其他类型需要使用引号(单双都可以)引起来

二、删除数据

• 语法：
  * delete from 表名 [where 条件]
• 注意：
  （1） 如果不加条件，则删除表中所有记录。
  （2） 如果要删除所有记录
• 使用推荐：
  （1）delete from 表名; -- 不推荐使用。有多少条记录就会执行多少次删除操作
  （2）TRUNCATE TABLE 表名; -- 推荐使用，效率更高 先删除表，然后再创建一张一样的表。

三、修改数据

• update 表名 set 列名1 = 值1, 列名2 = 值2,... [where 条件];

• 注意：
  如果不加任何条件，则会将表中所有记录全部修改。

 四、查询数据

1） 多个字段的查询：

• 语法
  select 字段名1，字段名2... from 表名；
• 注意：
  如果查询所有字段，则可以使用*来替代字段列表。
• 去除重复：distinct
• 计算列
  （1） 一般可以使用四则运算计算一些列的值。（一般只会进行数值型的计算）
  （2） ifnull(表达式1,表达式2)：null参与的运算，计算结果都为null
  （3） 表达式1：哪个字段需要判断是否为null
  （4） 如果该字段为null后的替换值。
• 起别名：as：as也可以省略

2）条件查询：

• where子句后跟条件
• 运算符：
  (1) != 、 > 、< 、<= 、>= 、= 、<>
  (2) BETWEEN...AND
  (3) IN( 集合)
  (4) LIKE：模糊查询
  (5) 占位符：
  (6) _:单个任意字符
  (7) %：多个任意字符
  (8) IS NULL :为空集
  (9) and 或 &&:并且
  (10) or 或 || :或
  (11) not 或 !:非

3）查询语句：

1. 排序查询

（1） 语法：order by 子句
（2） order by 排序字段1 排序方式1 ， 排序字段2 排序方式2...
（3） 排序方式： ASC：升序，默认的； DESC：降序。

• 注意： 如果有多个排序条件，则当前边的条件值一样时，才会判断第二条件。

1. 聚合函数：将一列数据作为一个整体，进行纵向的计算。

（1）count：计算个数
* 一般选择非空的列：主键
* count(*)
（2） max：计算最大值
（3） min：计算最小值
（4） sum：计算和
（5） avg：计算平均值

• 注意：聚合函数的计算，排除null值。
  解决方案：
  （1） 选择不包含非空的列进行计算
  （2） IFNULL函数

4）分组查询:

• 语法：group by 分组字段；

• 注意：
  （1） 分组之后查询的字段：分组字段、聚合函数
  （2） where 和 having 的区别？
  * where 在分组之前进行限定，如果不满足条件，则不参与分组。having在分组之后进行限定，如果不满足结果，则不会被查询出来

• where 后不可以跟聚合函数，having可以进行聚合函数的判断。

5）分页查询：

• 语法：limit 开始的索引,每页查询的条数;

• 公式：开始的索引 = （当前的页码 - 1） * 每页显示的条数-- 每页显示3条记录

五、常用基础语句应用 

1、说明：创建数据库
CREATE DATABASE database-name

2、说明：删除数据库
drop database dbname

3、说明：备份sql server
--- 创建 备份数据的 device
USE master
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat'
--- 开始 备份
BACKUP DATABASE pubs TO testBack

4、说明：创建新表
create table tabname
(col1 type1 [not null] [primary key],
col2 type2 [not null],..)
根据已有的表创建新表：
A：create table tab_new like tab_old (使用旧表创建新表)
B：create table tab_new as select col1,col2… from tab_old definition only

5、说明：删除新表
drop table tabname

6、说明：增加一个列
Alter table tabname add column col type
注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。

7、说明：添加主键： 
Alter table tabname add primary key(col)
说明：删除主键：
 Alter table tabname drop primary key(col)

8、说明：创建索引：
create [unique] index idxname on tabname(col….)
删除索引：
drop index idxname
注：索引是不可更改的，想更改必须删除重新建。

9、说明：创建视图：
create view viewname as select statement
删除视图：
drop view viewname

10、说明：几个简单的基本的sql语句
选择：select * from table1 where 范围
插入：insert into table1(field1,field2) values(value1,value2)
删除：delete from table1 where 范围
更新：update table1 set field1=value1 where 范围
查找：select * from table1 where field1 like ’%value1%’ ---like的语法很精妙，查资料!
排序：select * from table1 order by field1,field2 [desc]
总数：select count(*) as totalcount from table1
求和：select sum(field1) as sumvalue from table1
平均：select avg(field1) as avgvalue from table1
最大：select max(field1) as maxvalue from table1
最小：select min(field1) as minvalue from table1

11、说明：几个高级查询运算词
A： UNION 运算符(并)
    UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结                
    果表。
    当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自     
    TABLE1 就是来自 TABLE2。
B： EXCEPT 运算符（差）
    EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。
    当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。
C： INTERSECT 运算符（交）
    INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。
    当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL)，不消除重复行。
注：使用运算词的几个查询结果行必须是一致的。

创建、插入数据

create database mydatabase;
# 指定使用哪个数据库
use mydatabase;
# 创建表
CREATE  TABLE  STUDENTS(
SNO      NUMERIC (6, 0) NOT NULL,
NAME     CHAR (8) NOT NULL,
AGE      NUMERIC(3,0),#第一个数据为有效数据位数；第二个数据为保留的小数位
SEX      CHAR(2),
BPLACE   CHAR(20),
PRIMARY KEY(SNO));#设定主关键字,是表中的一个或多个字段，它的值用于唯一地标识表中的某一条记录。
CREATE  TABLE  COURSES
(
		CNO     CHAR(4)  NOT NULL,
        CNAME   char(10) NOT NULL,
        CREDIT   INT,
        PRIMARY KEY(CNO)
        );
       
CREATE  TABLE  ENROLLS
(		SNO      NUMERIC(6,0)  NOT NULL,
		CNO     CHAR(4)  NOT NULL,
        GRADE   INT,
        PRIMARY KEY(SNO,CNO),
        #外关键字 是用于建立或加强两个表数据之间的链接的一列或多列。
        #参考学生表中的学号SNO
        FOREIGN KEY(SNO) REFERENCES STUDENTS(SNO),
        FOREIGN KEY(CNO) REFERENCES COURSES(CNO),
        CHECK ((GRADE IS NULL) AND (GRADE BETWEEN 0 AND 100)));
        
CREATE  TABLE  TEACHERS
(
		TNO     NUMERIC(4)  NOT NULL,
        TNAME   char(8),
        AGE     NUMERIC(3,0),
        PS      char(8),
        PRIMARY KEY(TNO)
        );
CREATE  TABLE  TEACHING
(       CNO     CHAR(4)  NOT NULL,
		CLASS   CHAR(8)  ,
		TNO     NUMERIC(4) ,
        SUM     NUMERIC(3,0),
        PRIMARY KEY(CNO)
        );
        
# 向表中添加数据
alter table TEACHING change  column SUM SNUM varchar(8);
alter table STUDENTS change  column NAME SNAME varchar(8);#--修改表列名
INSERT INTO  STUDENTS(SNO, SNAME,AGE,SEX,BPLACE) VALUES
(990027,'胡伟',22,'男','湖北'),
(990652,'张春明',24,'男','河北'),
(990668,'王玉',22,'女','四川'),
(990674,'丁晓春',24,'男','黑龙江'),
(990676,'贺正生',23,'男','湖南'),
(990684,'刘文革',24,'女','辽宁'),
(991091,'程会军',23,'男','山西');       
INSERT INTO  COURSES(CNO, CNAME,CREDIT) VALUES
('C1','数据库',3),
('C2','数据结构',3),
('C3','操作系统',4),
('C4','软件工程',3);
INSERT INTO  ENROLLS(SNO,CNO,GRADE) VALUES
(990027,'C1',90),
(990027,'C3',95),
(990027,'C4',88),
(990652,'C1',88),
(990652,'C4',83),
(990668,'C3',84),
(990674,'C2',77),
(990676,'C3',90),
(990684,'C3',85),
(990684,'C1',82),
(991091,'C2',93);
INSERT INTO  TEACHERS(TNO,TNAME,AGE,PS) VALUES
(1420,'周振华',38,'副教授'),
(1481,'刘建平',30,'讲师'),
(1433,'王志伟',28,'讲师');
alter table TEACHING drop primary key;
INSERT INTO  TEACHING(CNO,CLASS,TNO,SNUM) VALUES
('C1','E851',1420,30),
('C2','E851',1420,22),
('C3','E651',1481,30),
('C3','E852',1481,28),
('C4','E851',1433,24),
('C1','E852',1420,28);

 删除表，增加列

#根据表的 STUDENTS 表，建立一个只包含学号、姓名、年龄的女学生表。
CREATE TABLE GIRL
        AS SELECT SNO, SNAME, AGE
        FROM STUDENTS
        WHERE SEX=' 女 ';

#删除教师表 TEACHER,
DROP  TABLE  TEACHERS;
#在教师表中增加住址列。
ALTER TABLE STUDENTS ADD (ADDR CHAR(50));

视图操作

#建立一个只包括教师号、姓名和年龄的视图 FACULTY ，( 在视图定义中不能包含 ORDER BY 子句 )
CREATE VIEW FACULTY AS SELECT 
TNO, TNAME, AGE FROM TEACHERS;

#从学生表、课程表和选课表中产生一个视图 GRADE_TABLE ，它包括学生姓名、课程名和成绩。
CREATE VIEW GRADE_TABLE
AS SELECT  SNAME,CNAME,GRADE
FROM  STUDENTS,COURSES,ENROLLS
WHERE  STUDENTS.SNO = ENROLLS.SNO AND
COURSES.CNO=ENROLLS.CNO;

#删除视图 GRADE_TABLE
DROP VIEW GRADE_TABLE RESTRICT;

索引 

#在学生表中按学号建立索引。
CREATE  UNIQUE  INDEX  ST ON STUDENTS (SNO);
#删除按学号所建立的索引
DROP INDEX ST ON STUDENTS;

单表操作

#把所有学生的年龄增加一岁
UPDATE STUDENTS SET AGE ＝ AGE+1
# 找出 3 个学分的课程号和课程名。
         SELECT CNO, CNAME
         FROM 　 COURSES
         WHERE 　 CREDIT ＝ 3
#查询年龄大于 22 岁的学生情况。
         SELECT  *
         FROM   STUDENTS
         WHERE  AGE ＞ 22
#找出籍贯为河北的男生的姓名和年龄。
         SELECT SNAME, AGE
         FROM 　 STUDENTS
         WHERE 　 BPLACE ＝ ' 河北 '  AND  SEX ＝ ' 男 '
#找出年龄在 20 ～ 23 岁之间的学生的学号、姓名和年龄，并按年龄升序排序。 (ASC （升序）或 DESC （降序）声明排序的方式，缺省为升序。 )
         SELECT SNO, SNAME, AGE
         FROM   STUDENTS
         WHERE  AGE BETWEEN 20 AND 23
         ORDER  BY  AGE

• 条件比较运算符＝、＜和逻辑运算符 AND （与），此外还可以使用的运算符有：＞、＞＝、＜＝、＜＞、 NOT （非）、 OR （或）等。谓词 LIKE 只能与字符串联用，常常是 “ ＜列名＞ LIKE pattern” 的格式。特殊字符 “_” 和 “%” 作为通配符。
• 谓词 IN 表示指定的属性应与后面的集合（括号中的值集或某个查询子句的结果）中的某个值相匹配，实际上是一系列的 OR （或）的缩写。谓词 NOT IN 表示指定的属性不与后面的集合中的某个值相匹配。

#找出年龄小于 23 岁、籍贯是湖南或湖北的学生的姓名和性别。
SELECT SNAME, SEX
        FROM   STUDENTS
        WHERE  AGE ＜ 23  AND  BPLACE  LIKE' 湖％ '
        #或
SELECT SNAME, SEX
        FROM   STUDENTS
        WHERE  AGE ＜ 23  AND  BPLACE  IN  ( ' 湖南 ' ， ' 湖北 ' )

#找出学生表中籍贯是空值的学生的姓名和性别。（在 SQL 中不能使用条件：＜列名＞＝ NULL 。在 SQL 中只有一个特殊的查询条件允许查询 NULL 值：）
SELECT SNAME, SEX
       FROM   STUDENTS
       WHERE  BPLACE IS NULL

多表操作

#找出成绩为 95 分的学生的姓名。（子查询）
 SELECT SNAME
        FROM 　 STUDENTS
        WHERE 　 SNO ＝
              (SELECT SNO
               FROM   ENROLLS
               WHERE  GRADE ＝ 95)
#找出成绩在 90 分以上的学生的姓名。
SELECT  SNAME
        FROM   STUDENTS
        WHERE  SNO IN
                (SELECT SNO
                FROM ENROLLS
                WHERE GRADE ＞ 90)
#查询全部学生的学生名和所学课程号及成绩。（连接查询）
 SELECT  SNAME, CNO, GRADE
        FROM   STUDENTS, ENROLLS
        WHERE  STUDENTS.SNO ＝ ENROLLS.SNO

当构造多表连接查询命令时，必须遵循两条规则。

• 第一，连接条件数正好比表数少 1 （若有三个表，就有两个连接条件 ) ；
• 第二，若一个表中的主关键字是由多个列组成，则对此主关键字中的每一个列都要有一个连接条件（也有少数例外情况）

#找出籍贯为山西或河北，成绩为 90 分以上的学生的姓名、籍贯和成绩。
SELECT  SNAME, BPLACE, GRADE
        FROM   STUDENTS, ENROLLS
        WHERE  BPLACE IN (‘ 山西 ’ ， ‘ 河北 ’)  AND  GRADE ＞  	  ＝ 90  AND 　 STUDENTS.SNO=ENROLLS.SNO
  
#查出课程成绩在 80 分以上的女学生的姓名、课程名和成绩。（ FROM 子句中的子查询）
SELECT  SNAME,CNAME, GRADE
        FROM   (SELECT SNAME, CNAME , GRADE
                        FROM  STUDENTS, ENROLLS,COURSES
                        WHERE  SEX ＝ ' 女 ')
        AS TEMP (SNAME, CNAME,GRADE)
        WHERE  GRADE ＞ 80

表达式与函数的使用

内部函数：
SQL 标准中只使用 COUNT 、 SUM 、 AVG 、 MAX 、 MIN 函数，称之为聚集函数（ Set Function ）。

COUNT 函数的结果是该列统计值的总数目，
SUM 函数求该列统计值之和，
AVG 函数求该列统计值之平均值，
MAX 函数求该列最大值，
MIN 函数求该列最小值。
GROUP BY 与 HAVING ：
GROUP BY 子句把一个表按某一指定列（或一些列）上的值相等的原则分组，然后再对每组数据进行规定的操作 GROUP BY 子句总是跟在 WHERE 子句后面，当 WHERE 子句缺省时，它跟在 FROM 子句后面。HAVING 子句常用于在计算出聚集之后对行的查询进行控制。

#查询各课程的学时数。（算术表达式由算术运算符＋、－、 * 、／与列名或数值常量所组成。）
 SELECT  CNAME,COURSE_TIME ＝ CREDIT*16
        FROM   COURSES

#找出教师的最小年龄- --28。
SELECT  MIN(AGE)
	 FROM   TEACHERS

#统计年龄小于等于 22 岁的学生人数。（统计）
SELECT  COUNT(*)
        FROM   STUDENTS
        WHERE  AGE < ＝ 22

# 找出学生的平均成绩和所学课程门数。
SELECT  SNO, AVG(GRADE), COURSES ＝ COUNT(*)
        FROM   ENROLLS
        GROUP  BY SNO

#找出年龄超过平均年龄的学生姓名。
SELECT SNAME
FROM STUDENTS
WHERE AGE ＞
      (SELECT  AVG(AGE)
        FROM   STUDENTS)
        
#找出各课程的平均成绩，按课程号分组，且只选择学生超过 3 人的课程的成绩。
 SELECT CNO, AVG(GRADE), STUDENTS ＝ COUNT(*)
         FROM ENROLLS
         GROUP BY CNO
         HAVING COUNT(*) >= 3

相关子查询

当一个子查询涉及到一个来自外部查询的列时，称为相关子查询（ Correlated Subquery) 。相关子查询要用到存在测试谓词 EXISTS 和 NOT EXISTS ，以及 ALL 、 ANY （ SOME ）等。

# 查询没有选任何课程的学生的学号和姓名。
SELECT SNO, SNAME
        FROM   STUDENTS
        WHERE  NOT EXISTS
              (SELECT *
              FROM ENROLLS
              WHERE ENROLLS.SNO=STUDENTS.SNO)

内、外连接

内连接：等值连接，不等连接

select  * from A,B where A.d = B.d
select * from A inner join B on A.d <>B.d

外连接分为左连接（LEFT JOIN）或左外连接（LEFT OUTER JOIN）、右连接（RIGHT JOIN）或右外连接（RIGHT OUTER JOIN）、全连接（FULL JOIN）或全外连接（FULL OUTER JOIN）。
我们就简单的叫：左连接、右连接和全连接。
外连接的作用是在做连接操作时避免丢失信息。

左连接：（显示左表全部行，右边无的用NULL代替）
概念：返回左表中的所有行，如果左表中行在右表中没有匹配行，则结果中右表中的列返回空值。

右连接：（显示右表全部行，左边无的用NULL代替）
概念：恰与左连接相反，返回右表中的所有行，如果右表中行在左表中没有匹配行，则结果中左表中的列返回空值。

全连接：（返回左表和右表中的所有行）
概念：返回左表和右表中的所有行。当某行在另一表中没有匹配行，则另一表中的列返回空值

#列出全部教师的姓名及其任课的课程号、班级。
#左连接
SELECT TNAME, CNO, CLASS
     FROM TEACHERS LEFT OUTER JOIN TEACHING USING (TNO);

#右连接
SELECT TNAME, CNO, CLASS
     FROM TEACHERS RIGHT OUTER JOIN TEACHING USING (TNO);