基础语句
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 表,建立一个只包含学号、姓名、年龄的女学生表。
#根据表的 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);