建立一个数据库

建立一个数据库 ?建立一个数据库 create database 数据库名 撤消一个数据库 drop database 数据库名 指定当前数据库 use 数据库名 在当前数据库中创建新表 Create table 基本表名 ( 字段名 字段属性 完整性约束， ……n) 在字段属性中 数据类型[(长度)] [ identity(初始值，步长值) ][ null/not null ] identity用于指定该列为自动编号字段(标识列); null/not null指定该列允许空值(默认)或不允许空值。 1( 设置not null值: create table s ( sno char(10) not null, sname char(8), age smallint , sex char(2) ) 在字段约束中 primary key [(主键列名)] unique [(惟一列名)] [foreign key [(外键列名)]] references 引用表名(引用列名) check(检查表达式) default 默认值 2(缺省约束default: create table s ( sno char(10) not null, sname char(8), age smallint, sex char(2) default?男„) --性别缺省时默认为?男? 3(主键约束primary key create table s ( sno char(10) , sname char(8), age smallint, sex char(2) default?男„, primary key(sno)) --S表的主键设置为sno 有了主键约束,主键为非空,所以sno的not null 可以省写 4(外键约束foreign key…references… create table SC (sno char(10) not null, cno char(3) not null, grade numeric (3,1), primary key(sno,cno), foreign key (sno) references S(sno), foreign key (cno) references C(cno)) --与S的sno及C的cno建立外键 5.检查约束check create table s ( sno char(10) primary key, sname char(8) not null, age smallint check(age between 15 and 45), sex char(2) default?男„,) check(age between 15 and 45)相当于 check(age>=15 and age<=45) 6.建立course，增加一条件:课程名不能重复 建立course，增加一条件:课程名不能重复 create table Course (cno char(5) not null, cname nvarchar(30) not null, credit tinyint not null check(credit>0), pcno char(5), primary key(cno), foreign key(pcno) references course(cno) unique(cname)) 总结 初级经济法重点总结下载党员个人总结TXt高中句型全总结.doc高中句型全总结.doc理论力学知识点总结pdf ，如: create table Course (cno char(5) not null, cname nvarchar(30) not null, credit tinyint not null check(credit>0), pcno char(5), primary key(cno), foreign key(pcno) references course(cno) ) ?用Alter table语句修改表结构 Alter Table 表名 ?add 列名 数据类型[(长度)] [null/not null] [default ?默认值? ] 使用add子句添加列 add 字段名 数据类型[(长度)][null/not null][ default „ 默认值? ] alter table 供货商表 新增字段可同时设置空值约束、默认值约束。 不允许为空时，必须给新增加的列指定默认值，否则语句执行错误。 add 联系电话char(13) not null default '00000000' 注意:添加的字段若不允许为空则必须设置默认值，如果不允许为空又不需要默认值，可在添加字段时先允许为空，再用alter column子句修改为不允许为空，则没有默认值。 ?alter column 列名 数据类型[(长度)][null/not null] alter table 表名 alter column 字段名 数据类型[(长度)][null/not null] 将原来允许为空值的列改为不允许为空，必须保证表中记录在该列没有空值，且该列没有创建索引。 改变数据类型时，如原来设置了默认值约束，则应解除或删除约束后再修改，否则很容易发生错误。 如:将《供货商表》的“联系电话”字段，数据长度改为20，允许为空(null可以省略)。代码如下: USE diannaoxs ALTER TABLE 供货商表 alter column 联系电话 char(20) ?drop column 字段名 [ , …n ] ?add constraint { 约束名 约束类型定义} 列约束定义 [ , …n ] [ FOR 列名 ] 使用add constraint子句可一次向表的不同字段添加一个或多个约束，其语法格式: alter table 表名 add constraint 约束名{约束类型及定义[FOR 列名]}[,…n ] 添加约束必须指定约束名，而且必须是惟一，不能与其他对象同名。 约束类型及定义没有指定列名，必须用FOR指定列名(如默认值约束);否则不使用FOR。 如只允许有一个约束且已经设置了约束(检查约束除外)，原约束未解除不能添加新的约束。 如:为《供货商表》的“供货商”添加约束“惟一厂家”，代码如下: alter table 供货商表 add constraint 惟一厂家 unique(供货商) 注意:约束定义中已使用了字段名，若再使用“FOR 供货商”会产生错误。 运行后显示“命令已成功完成。”，“惟一厂家”的惟一约束添加成功。 ?drop constraint 约束名 [ , …n ] ?INSERT语句 插入数据的语句是INSERT语句。方式有两种:元组值的插入和查询结果的插入。 1、插入元组值 一次插入一个元组值 insert into 基本表名(列名表) values(元组值) 如:往SC插入一个元组(S5,C8),成绩未知。 insert into SC(sno，cno) values (?S5?,?C8?) 一次插入多个元组 Insert into 基本表名(列名表) Select 元组值1 union all Select 元组值2 union all Select 元组值3 如:往表中连续插入三个元组，可以写成: Insert Into sc Select 's12','c1',89 union all Select 's16','c2',99 union all Select 's18','c3',80 2、插入子查询结果 Insert into 基本表名(列名表) select查询语句 如:在student中，把学生的专业，平均年龄存入另一个已知的基本表S_G(sno,Avgage)。 insert into S_G(sno,Avgage) select sno,avg(age) from student group by sno ?创建索引 create [unique] [cluster] Index <索引名>on <表名>(<列名>[<次序>][,<列名>[<次序>] ]…) 用<表名>指定要建索引的基本表名字 索引可以建立在该表的一列或多列上，各列名之间用逗号分隔 用<次序>指定索引值的排列次序，升序:ASC，降序:DESC。缺省值:ASC Unique表明此索引的每一个索引值只对应唯一的数据记录 ? 聚集索引 创建聚集索引后，表中的记录按照索引属性值的顺序进行存储。 每个表只能有一个聚集索引。 如:在student表中按sname建立一个聚集索引 Create clustered index Stusname(索引名) on student(Sname) 基本表名(列名表ASC/DESC) Cluster表示要建立的索引是聚集索引 ? 逻辑(非聚集)索引 逻辑索引相当于一个映射，把索引的属性值转换为对应记录的地址或地址集。 逻辑索引不影响表中记录的存储顺序。 Create [unique] index 索引名on 基本表名(列名表 ASC/DESC) 若指定[UNIQUE]选项,系统将保证基本表中的任意两行记录在索引列上或组合的索引列上具有唯一值 索引可以建立在该表的一列或多列上，各列名之间用逗号分隔 默认为升序(ASC)排列 如:为学生-课程数据库中的Student，Course，SC三个表建立索引。其中Student表按学号升序建唯一索引，Course表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引。 Createunique indexstusno on student(Sno) Create unique index coucno on course(cno) create unique index scno on sc(sno asc,cno desc) ?数据库的修改 ?删除记录:DELETE语句 DELETE FROM <表名> [WHERE <条件>] 删除单个元组 例:删除学号为S11的学生的选课信息(表名为:SC) delete ftom sc where sno=’s11’ 删除低于平均工资的老师记录。 delete from PROF where SAL< (select avg(SAL) from PROF) 删除整个关系中的所有数据 例:删除所有学生的选课信息 Delete From SC 删除多个元组 例:删除选课而未参加考试的学生的选课信息 Delete From SC Where grade is null ?修改记录:UPDATE语句 update 表名 set列名=表达式/子查询 列名 = [，表达式/子查询]… [where 条件表达式] 如:所有学生年龄加1 Update Student Set age = age + 1 将学号(s#)为S1学生性别更改为„女? Update Student Set sex= „女' Where s# = 'S1„ 将选修C1课程的学生的成绩改为该课程的平均成绩 Update SC Set grade = (Select avg(grade) 先计算avg，再做Update From SC Where C# = 'C1') Where C# = 'C1' ?删除数据库中的对象:DROP语句 1、删除基本表 drop table 表名 如:drop table sc 撤消基本表后，基本表的定义、表中数据、索引、以及由此表导出的视图的定义都被删除。 2、删除索引 drop index 表名.索引名 如:drop index student.xh_kch 删除索引时，系统会同时从数据字典中删去有关该索引的描述。 对于SQL Server，每个表的索引名必须唯一，但不同表的索引名可以相同。 3、删除数据库 DROP DATABASE <数据库名> 如:drop database QTGL ?如何修改基本表 修改基本表定义(ALTER) 格式: alter table 表名 [add 子句] 增加新列或添加主键、外键 [drop column子句] 删除列 [alter column子句] 修改列的定义 如:1、Student表中增加一列表示所在系: 列名:sdept 类型: varchar(20) alter table Student add sdept varchar(20) 2、Student表中增加一列表示年龄: 列名:sage 类型:int 默认值:22 alter table Student add sage int default 22 3、Student表中删除联系电话(phone)这一列 alter table Student drop column phone 4、修改Student表中姓名(sname)这一列的类型为varchar(16) alter table Student alter column sname Varchar(16) 在定义表的时候如果漏了设置主键或外键，如何添加主键和外键 Alter table S ADD Primary KEY (S#) Alter table SC ADD FOREIGN KEY (S#) REFERENCES S(S#) 若在创建表格没有创建约束名，则使用默认约束名。 sp_help [表名] 执行结果返回七个结果集，第七个显示了主键和默认值的信息 Constraint_name:找到相应约束条件的约束名。 如果创建了S表、C表、SC表，并设置SC的外键为(S#)和(C#)，可有时候删除S表 的记录或者删除C表的记录时会发生错误，这是为什么,该如何解决, S( S# , SNAME ,AGE,SEX,DEPT ) SC( S#,C#,GRADE) C( C#, CNAME ,TEACHER) 第二章 SQL语言—单表查询 ?基本结构 select [ALL/DISTINCT] 目标列表达式 from 表名或者视图名 where 件表达式 GROUP BY 列名1 HAVING 条件表达式 ORDER BY 列名2 ASC/DESC 例1、 查询全体学生的学号和姓名。 Select Sno,sname From student 例2 、查询全体学生的姓名、学号、年龄。 Select sname,sno,sage From student ?要求用小写字母表示所有系名。 Select sname.?Year of Birth?,2007-Sage,LOWER(Sdept) From STUDENT 【Lower()字符串函数，将大写装换成小写字母。】 【UPPER()字符串函数，将小写字符转换成大写字母】 ?用户可以通过指定别名来改变查询结果的列标题 Select Sname NAME,?Year of Birth?BIRTH,2007-Sage as DEPARTMENT from STUDENT ?取消重复行distinct Select distinct sno from sc ?如果没有指定distinct，则缺省为ALL，即保留重复行 select ALL Sno from SC ?确定集合 IN、NOT IN 谓词IN可以用来查找属性值属于指定集合的元组 如:查询既不是信息系、数学系，也不是计算机系的学生姓名和性别 Select sname,sage from student Where sdept not in(„IS?,?MA?,?CS?)系不是信息系、数学系、计算机系 ?字符匹配 谓词LIKE可以用来进行字符串的匹配。其一般语法格式: [NOT] LIKE „<匹配串>? [ESCAPE „ <换码字符> ? ] 含义:查找指定的属性列值与<匹配串>相匹配的元组 如:查询姓名为李勇的学生信息 Select * from student Where sname like „李勇? 匹配串可以是一个完整的字符串，也可以含有通配符,和_。其中: ,(百分号)代表任意长度(可以为0)的字符串。 例如a%b表示以a开头，以b结尾的任意长度的字符串。如acb，addgb，ab等都满足该 匹配串。 _(下横线) 代表任意单个字符 例如a_b代表以a开头，以b结尾的长度为3的任意字符串，比如acb，afb都满足该匹配 串. 如:查询所有姓刘的学生姓名、学号和性别 Select sname,sno,ssex ftom student Where sname like „刘%? ?要查询的字符串本身就含有,或_，这时就要使用ESCAPE?<转义字符>’短语对通配符 进行转义 查询DB_design的课程的课程号和学分 select Cno,Ccredit from Course where cname like „db\_design?escape‘\’ 查询以“DB_”开头，且倒数第三个字符为i的课程的详细情况 select * from Course where cname like „DB\_%i__?ESCAPE?\? ?逻辑运算符AND和OR可用来联结多个查询条件。 如:查询信息系(IS)、数学系(MA)和计算机系(CS)学生的姓名和性别 Select sname,ssex from student Where sdept=?IS? OR Sdept=?MA? OR Sdept=?CS? 等价于 Select sname,ssex from student Where sdept in(„IS?,„MA?,„CS?) AND的优先级高于OR，但用户可以用括号改变优先级。 如:检索年龄大于25岁的男学生的学号和姓名，结果按年龄降序、学号升序排序。 Select Sno,sname From student Where Sage>25 and Ssex=?男? Order by sage desc,sno asc 集函数(或称聚集函数) , 聚集函数用于对数据表的的记录进行统计，如统计记录总数、平均值、最小值和最 大值等。 count(*):统计表中所有行的行数。 count(列名):统计表中指定列非null值的行数。 count(distinct 列名):统计表中指定列名除去重复值的行数。 avg(distinct/all 列名):求一列值的平均值(此列必须是数值型) SUM(distinct/all 列名):求一列值的总和(此列必须是数值型) max(distinct/all 列名):求一列中的最大值 min(distinct/all 列名):求一列中的最小值。 where子句用于对分组前的每个记录进行筛选。 Having 是对分组以后对结果进行筛选。 group by 与 order by 。 order by 子句可以与Group by 子句共同使用来查询结果进行排序。 order by 子句必须跟在Group by 子句后面，顺序不能调换。 group by子句、 order by中不能包含Select清单组成部分以外的列名。 ?查询语句的一般格式: SELECT all/distinct 目标表达式 FROM 表名或者视图名 WHERE 条件表达式 GROUP BY 列名1 HAVING 条件表达式 ORDER BY 列名2 ASC/DESC • WHERE子句作用于基本表或视图(分组前)，HAVING子句作用于组 • group by子句、 order by中不能包含Select清单组成部分以外的列名。 ?修改基本表定义(ALTER) alter table 表名 [add 子句] 增加新列或添加主键、外键 [drop column子句] 删除列 [alter column子句] 修改列的定义 [Drop constraint 约束名] 删除约束 如果要删除或修改有约束限制的字段，则应先删除其约束，然后在删除或修改。 • WHERE子句作用于基本表或视图(分组前)，HAVING子句作用于组 • order by 子句必须跟在Group by 子句后面，顺序不能调换。 • group by子句、 order by中不能包含Select清单组成部分以外的列名。 连接查询 在查询中，同时涉及两个或两个以上的表，要根据表中数据的情况作连接。 连接查询:一个查询涉及两个以上的表 2、连接条件(或称连接谓词) 连接条件，其一般格式为: [<表名1>.]<列名1> <比较运算符> [<表名2.>]<列名2> 连接条件中的列名称为连接字段 其中比较运算符主要有:,、>、<、>=、<=、!=。 当连接运算符为,时，称为等值连接。使用其他运算符称为非等值连接。 需要连接的表要具有公共属性(个人认为是主外键的联系) 如果连接条件中两个属性里的任何一个属性包含空值，那一条记录就将被剔除，当这两个属性都是空值时也同样被剔除。 一、广义笛卡尔积(不带连接谓词的连接) 例: SELECT Student.* , SC.* FROM Student, SC 带连接谓词的连接 SELECT SNAME ,CNO FROM STUDENT,SC WHERE STUDENT.SNO=SC.SNO 二、等值与非等值连接查询 [<表名1>.]<列名1> <比较运算符> [<表名2.>]<列名2> 当比较运算符为?,?时，称为等值连接。 使用其他运算符时，称为非等值连接。 连接查询注意事项:要连接的表要有公共属性、连接的属性是主键和外键、若连接的两个表包含相同的字段，则需要在字段前加其所属的表名。 2、 使用别名:若连接的表名较长，可为表起别名、SQL语言允许在FROM子句中为表名 指定别名、为表名执行别名，只需将别名写在表名之后，它们之间用空格隔开就可以了。 如:select s.sno,sname From student s,sc Where cno=?002? and s.sno=sc.sno 三、自身连接 连接操作不仅可以在两个表之间进行，也可以是一个表与自己进行连接。一个表与其自己进行连接，称为表的自身连接 需要给表起别名以示区别 由于所有属性名都是同名属性，因此必须使用别名前缀 如:查询每一门课的间接先修课(即先修课的先修课) SELECT FIRST.Cno，SECOND.Cpno FROM Course FIRST，Course SECOND?SQL语言允许在FROM子句中为表名指定别名 WHERE FIRST.Cpno = SECOND.Cno 查询与刘晨在同一个系学习的学生的详细资料 select b.* from student a, student b where a.Sdept=b.Sdept and a.Sname='刘晨' 既选修了001又选修002课程的学生学号 select a.sno from sc a,sc b where a.sno=b.sno and a.cno='001' and b.cno='002' 四、外连接 外连接与普通连接的区别 1.普通连接操作只输出满足连接条件的元组 2.外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出 , 左外连接:外连接符(*)出现在连接条件的左边 , 右外连接:外连接符(*)出现在连接条件的右边 如:查询每个学生的基本情况以及选课情况。包括没有选修课程的学生 select student.*,cno,score from student,sc where student.sno=sc.sno select student.*,cno,score from student,sc where student.sno*=sc.sno 查询学生的学号，选修课的课程名，成绩，要求没有学生选修的课程名也显示出来。 select sc.sno,cname,score from sc,course where sc.cno=*course.cno 五、复合条件连接 WHERE子句中含多个连接条件时，称为复合条件连接 如:查询选修002号课程且成绩在90分以上的所有学生的学号、姓名 Select Student.Sno, student.Sname From Student, SC Where Student.Sno = SC.Sno and SC.Cno= ' 002 ' and SC.Score > 90?复合条件连接 多表连接 连接操作除了可以是两表连接，还可以是两张以上的表的连接，称为多表连接。 Select student.sno,sname,cname,score From student,sc,coutse Where student.sno=sc.sno and coutse,cno=sc.cno?多表连接标志 关系数据库与SQL语言----嵌套查询 嵌套查询 一个SELECT-FROM-WHERE语句为一个查询块，将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中,这样的查询称为嵌套查询，而置入的SELECT语 句称为子查询。 如: select Sname from Student where sno in (select sno from sc where cno=„001?) 一、使用in的 子查询 表达式[not] in(子查询) 判断表达式的值是否在子查询的结果中。 如:查询与“刘晨”在同一个系学习的学生。 select Sno,Sname,Sdept from Student where Sdept IN (select Sdept from Student where Sname ='刘晨') 选修了001号课程的学生的学号及姓名。 Select sno,sname from student where Sno in(select sno from SC where cno= „001' ) 二、带有比较运算符的子查询 当用户确切知道内层查询返回的是单值，可以用>,<,>=,<=,!=,<>等比较运算符 查询与刘晨在同一个系学习的学生 select Sno,Sname,Sdept from Student where Sdept = (select Sdept from Student where Sname =„刘晨?) 三、集合之间的比较 some/any/all子查询(some与any等效) , 表达式 比较运算符 any (子查询) 表达式的值至少与子查询结果中的一个值相比满足比较运算符 。 , 表达式 比较运算符 all (子查询) 表达式的值与子查询结果中的所有的值相比都满足比较运算符。 集合之间的比较 查询其他系中比信息系某个学生年龄小的学生姓名年龄 select Sname,Sage from student where Sage 'is' 等价于 select Sname,Sage from student where Sage <(select max(Sage) from student where Sdept='is') and Sdept< >'is' 四、使用exists的子查询 测试集合是否为空[not] exists(子查询) 判断子查询的结果集合中是否有任何元组存在。 如:查询所有选修了1号课程的学生姓名。用嵌套查询 SELECT Sname FROM Student WHERE EXISTS /*相关子查询*/ (SELECT * FROM SC WHERE SC.Sno=Student.Sno AND Cno= ' 1 ') 五、在Delete、Update、Insert 语句中使用子查询 与delete 一起使用子查询 删除计算机cs系的学生的选课信息 delete sc where Sno in ( select Sno from student where sdept=‘cs’ ) 与update 一起使用子查询 修改SC表中性别为„男?的学生的成绩为70分 update sc set score=70 where Sno in ( select Sno from student where sex='男' ) 与insert 一起使用子查询 在Student中，把性别为男的学生的学号，姓名存入另一个已知的基本表S_x(sno,sname)。 insert into S_x ( Sno , Sname ) select sno , sname from student where sex=„男?