/*----------创建数据库语句----------*/
CREATE DATABASE database_name
[ [ON [filespec]]
[LOG ON [filespec]]
]
filespec内容为：
(
[NAME = logical_file_name,]
FILENAME = 'my_file'
[,size = size]
[,MAXSIZE = {max_size|UNLIMITED}]
[,FILEGROWTH = growth_increment]
)
例子：
CREATE DATABASE test
ON
(
NAME = test_data,
FILENAME = 'G:\test_data.MDF',
SIZE = 10MB,
MAXSIZE = 50MB,
FILEGROTH = 5MB
)
LOG ON
(
NAME = test_log,
FILENAME = 'G:\test_log.LDF', /*注意：LDF*/
SIZE = 10MB,
MAXSIZE = 20MB,
FILEGROTH = 10%
)
/*----------修改数据库语句----------*/
ALTER DATABASE database_name /*database_name：数据库名*/
{
ADD FILE filespec /*要添加的文件*/
ADD LOG FILE filespec /*要添加的日志*/
REMOVE FILE logical_file_name /*要删除的物理文件，文件空时才能删除*/
MODIFY FILE filespec /*要修改的文件*/
MODIFY NAME = new_dbname /*重命名数据库*/
}
filespec内容为：
(
[NAME = logical_file_name,]
FILENAME = 'my_file'
[,size = size]
[,MAXSIZE = {max_size|UNLIMITED}]
[,FILEGROWTH = growth_increment]
)
例子：
ALTER DATABASE test
ADD FILE
(
NAME = test_data_bak,
FILENAME = 'G:\test_data_bak.NDF', /*注意：NDF*/
SIZE = 10MB,
MAXSIZE = 50MB,
FILEGROTH = 5MB
)
GO
ALTER DATABASE test
REMOVE FILE test_data_bak
GO
/*----------使用数据库----------*/
USE test
/*----------删除数据库----------*/
DROP DATABASE test_data
/*----------创建表----------*/
/*----------创建表方法1----------*/
CREATE TABLE table_name /*table_name:表名称*/
(
column_name1 data_type [NULL|NOT NULL] [PRIMARY|UNIQUE] /*column_name1:列名称，data_type：数据类型,PRIMARY:主码，UNIQUE：唯一性约束*/
[FOREIGN KEY[(COLUMN_NAME)]] /*FOREIGN KEY:外码。外码和其它表主码相匹配*/
REFERENCES ref_table [(ref_column)]
[column_name2 data_type ...]
...
)
例子：
USE teat
CREATE TABLE clients
(
cid int(8) PRIMARY KEY,
cname char(8),
address char(50),
authorid int FOREIGN KEY REFERENCES authors(authorid)
)
或
CREATE TABLE sc
(
sno char(9),
cno char(4),
Grade int(8),
PRIMARY KEY(sno,cno),
FOREIGN KEY(sno)REFERENCES Student(sno),
FOREIGN KEY(cno)REFERENCES Course(Cno)
)
/*----------创建表方法2----------*/
USE school
SELECT sno,sname,sclass INTO student1
FROM student
/*----------修改表----------*/
ALTER TABLE table_name
ADD []
DROP
例子：
USE school
ALTER TABLE student1
ADD snation char(10) /*增加行*/
USE school
ALTER TABLE student1
DROP COLUMN sclass /*删除行*/
/*----------删除表----------*/
USE school
DROP TABLE student1
/*--------------------数据操纵语言--------------------*/
/*----------INSERT语句----------*/
USE school
INSERT INTO student
VALUES('200','曾磊','女','1978-2-3','05001')
/*----------UPDATA语句----------*/
USE school
UPDATE student
SET ssex='男'
WHERE sno = '200'
/*----------DELETE语句----------*/
USE school
DELETE student
WHERE sno = '200'
/*--------------------数据查询语言--------------------*/
SELECT 列
FROM 表或视图
[WHERE 查询限定条件]
[GROUP BY 分组表达式]
[HAVING 分组条件]
[ORDER BY 次序表达式[ASC|DESC]]
/*----------1.投影查询----------*/
SELECT sname,ssex,sclass /*默认不删掉重复行*/
FROM student
或
SELECT DISTINCT depart AS '专业' /*加入DISTINCT，删掉重复行，AS起别名*/
FROM teacher
/*----------2.选择查询----------*/
SELECT *
FROM score
WHERE degree BETWEEN 60 AND 80
/*----------3.排序查询----------*/
SELECT *
FROM student
ORDER BY cno,grade DESC /*desc，降序.按cno升序，grade降序*/
/*----------4.含有聚合函数的查询----------*/
聚合函数查询，可以使用GROUP BY和HAVING
SELECT count(DISTINCT sno) /*去重的计数*/
FROM score
SELECT count(sno) /*不去重的计数*/
FROM score
SELECT cno,AVG(degree) AS '平均分'
FROM score
WHERE cno like '3%' /*WHERE不能加聚合函数*/
GROUP BY cno
HAVING COUNT(*)>5 /*having对处理完的分组数据进行筛选。分组后聚合函数的作用域变为每一组。*/
聚合函数
AVG,COUNT,MIN,MAX,SUM,STDEV(标准差),STDEVP(方差)
/*----------5.连接查询----------*/
等值连接：
USE school
SELECT student.sname.score.sno,score.degree
FROM student,score
WHERE student.sno=score.sno
非等值连接
自身连接：
SELECT *
FROM score x,score y
WHERE x.cno>y.cno
/*----------6.子查询----------*/
嵌套查询
SELECT *
FROM score
WHERE sno IN
(SELECT sno
FROM sc)
多个查询连接起来
SELECT tname,tsex FROM teacher
UNION
SELECT sname,ssex FROM student
/*----------7.相关子查询----------*/
每个学生超过自己平均成绩的课程名与成绩
SELECT sno.cno
FROM SC x
WHERE Grade>=(SELECT AVG(grade)
FROM SC y
where x.sno=y.sno)
/*----------8.EXIST子查询----------*/
SELECT sname
FROM studemt
WHERE EXISTS(select *
from student
where sno=student.sno and cno='1')
/*----------9.ANY\ALL子查询----------*/
SELECT sname,sage
FROM student
WHERE sage < ALL
(select sage
from student
where sdept = 'CS')
AND sdept <> 'CS'
/*--------------------建立视图--------------------*/
create view view_one
as
select ...
视图建立后会存储在数据字典中随时使用
而派生表语句执行后会立刻删除
视图同样可以进行增删查改，但是修改的内容无法存储到基本表中
打赏
微信扫一扫，打赏作者吧～
