[电脑基础知识]sql基本语言帮助.doc

SQLSERVER HELP 1数据库的基础知识1.1：数据库中对表的基本概念1.2：完整性约束1.3： 数据类型2 SQL四种基本语言2.1: 数据定义语言（DDL-DATA DEFINITION LANGUAGE）2.1.1创建数据库2.1.2修改数据库2.1.3 删除数据库2.2.1 创建表2.2.2 修改表2.2.3 删除表2.2: 数据操纵语言（DML-DATA MANIPULATION LANGUAGE）2.2.1插入数据2.2.2更新数据2.2.3 删除数据2.2.4 查询数据2.3: 数据控制语言（DCL-DATA CONTROL LANGUAGE）2.3.1申请用户2.3.2 赋予权限2.3.3 拒绝权限2.3.4 收回权限2.4: 其他语言要素（ADDITIONAL LANGUAGE ELEMENTS）3 sql函数3.1 聚合函数3.2 日期函数3.3 字符串函数3.4 数学函数3.5 转换函数3.6 其他函数4 sql 高级查询4.1： 多表联接查询4.1.1内联接查询 (inner joinon)4.1.2外联接查询4.13 交叉联接查询 cross join4.2: 子查询4.2.1带关键字in/not in4.2.2 Having /where 子句中的子查询4.2.3 Exists/not exists 子查询4.3：联合查询 union/union all1数据库的基础知识1.1：数据库中对表的基本概念1.1.1:关系 一 张二维表1.1.2:记录（或元组）关系表中的一行1.1.3:字段（或属性）关系表中的一列1.1.4:域 即字段的值域，也就是字段的取值范围1.1.5:数据项（或分量）某一记录中的一个字段值1.1.6:主关键字（主码） 简称主键，是关系表中一个或者多个字段的集合，能唯一标识关系中的每一个记录。1.1.7:如果关系R2的一个和一组属性X不是R2的主码，而是另一个关系R1的主码，则称该属性或属性组X是关系R2的外部关系键或外码。1.1.8:关系模式 是对关系的一种抽象的描述，起描述格式为“关系名”（字段1，字段2，.，字段n）1.2：完整性约束1:实体完整性。1.1.1 ：Unique(唯一性约束)1.1.2 ：primary key(主键约束)1.1.3 ：identity(标识列)2：参照完整性。如果关系R1中存在外部关键字X，哪么X的每个值等于R2中某个主关键字，或者取空值。3：域完整性。3.1：check(检查约束)3.2：foreign key(外键约束)3.3：default(默认值约束)3.4：not null(不为空约束)4：用户自定义完整性。1.3： 数据类型分类数据类型整数数据类型INT 、ITEGER 、SMALLINT 、 TINYINT 、 BIGINT 浮点数据类型REAL 、 FLOAT 、 DECIMAL 、 NUMERIC 二进制数据类型BINARY 、VARBINARY逻辑数据类型BIT字符数据类型CHAR 、 NCAHR 、 VARCHAR 、 NVARHCAR文本和图形数据类型TEXT 、 NEXT 、 IMAGE日期和时间数据类型DATETIME 、 SMALLDATETIME货币数据类型MONEY 、 SMALLMONEY特定数据类型TIMESTAMP 、 UNIQUEIDENTITY用户自定义数据类型SYSNAME新数据类型SQL_VARIANT 、 TABLE整数bigint从 -263 (-9223372036854775808) 到 263-1 (9223372036854775807) 的整型数据（所有数字）。int从 -231 (-2,147,483,648) 到 231 - 1 (2,147,483,647) 的整型数据（所有数字）。smallint从 -215 (-32,768) 到 215 - 1 (32,767) 的整数数据。tinyint从 0 到 255 的整数数据。bitbit1 或 0 的整数数据。decimal 和 numericdecimal从 -1038 +1 到 1038 1 的固定精度和小数位的数字数据。numeric功能上等同于 decimal。money 和 smallmoneymoney货币数据值介于 -263 (-922,337,203,685,477.5808) 与 263 - 1 (+922,337,203,685,477.5807) 之间，精确到货币单位的千分之十。smallmoney货币数据值介于 -214,748.3648 与 +214,748.3647 之间，精确到货币单位的千分之十。近似数字float从 -1.79E + 308 到 1.79E + 308 的浮点精度数字。real从 -3.40E + 38 到 3.40E + 38 的浮点精度数字。datetime 和 smalldatetimedatetime从 1753 年 1 月 1 日到 9999 年 12 月 31 日的日期和时间数据，精确到百分之三秒（或 3.33 毫秒）。smalldatetime从 1900 年 1 月 1 日到 2079 年 6 月 6 日的日期和时间数据，精确到分钟。字符串char固定长度的非 Unicode 字符数据，最大长度为 8,000 个字符。varchar可变长度的非 Unicode 数据，最长为 8,000 个字符。text可变长度的非 Unicode 数据，最大长度为 231 - 1 (2,147,483,647) 个字符。Unicode 字符串nchar固定长度的 Unicode 数据，最大长度为 4,000 个字符。 nvarchar可变长度 Unicode 数据，其最大长度为 4,000 字符。sysname 是系统提供用户定义的数据类型，在功能上等同于 nvarchar(128)，用于引用数据库对象名。ntext可变长度 Unicode 数据，其最大长度为 230 - 1 (1,073,741,823) 个字符。二进制字符串binary固定长度的二进制数据，其最大长度为 8,000 个字节。varbinary可变长度的二进制数据，其最大长度为 8,000 个字节。image可变长度的二进制数据，其最大长度为 231 - 1 (2,147,483,647) 个字节。其它数据类型cursor游标的引用。sql_variant一种存储 SQL Server 支持的各种数据类型（text、ntext、timestamp 和 sql_variant 除外）值的数据类型。table一种特殊的数据类型，存储供以后处理的结果集。timestamp数据库范围的唯一数字，每次更新行时也进行更新。uniqueidentifier全局唯一标识符 (GUID)。2 SQL四种基本语言2.1: 数据定义语言（DDL-DATA DEFINITION LANGUAGE）2.2: 数据操纵语言（DML-DATA MANIPULATION LANGUAGE）2.3: 数据控制语言（DCL-DATA CONTROL LANGUAGE）2.4: 其他语言要素（ADDITIONAL LANGUAGE ELEMENTS）2.1: 数据定义语言（DDL-DATA DEFINITION LANGUAGE）2.1.1创建数据库 语法CREATE DATABASE database_name ON < filespec > ,.n , < filegroup > ,.n LOG ON < filespec > ,.n COLLATE collation_name FOR LOAD | FOR ATTACH < filespec > := PRIMARY ( NAME = logical_file_name , FILENAME = 'os_file_name' , SIZE = size , MAXSIZE = max_size | UNLIMITED , FILEGROWTH = growth_increment ) ,.n < filegroup > :=FILEGROUP filegroup_name < filespec > ,.n 注意：是不是觉得看着上面太繁琐啦！那就来看看简单的！create database student -创建 数据库 库名on primary -设置数据文件为主文件 省略primary 默认第一个文件为主文件(name='', -数据文件名filename='',-数据文件存放的物理路径，请确认电脑上有这样的路径地址size='',-设置数据文件的起始大小，单位一般写为MB，而不是Mmaxsize='',-数据文件的最大容量filegrowth=-文件的增长量，可以用MB来写，也可以用百分比来写 但是，不允许超过maxsize的值)log on-声明日志文件(name='', -数据文件名filename='',-数据文件存放的物理路径size='',-设置数据文件的起始大小，单位一般写为MB，而不是Mmaxsize='',-数据文件的最大容量filegrowth=-文件的增长量，可以用MB来写，也可以用百分比来写 但是，不允许超过maxsize的值)Go注意： 用户定义文件组的数据文件必须以逗号结束，)前的逗号省略。示例1： 创建 student 数据库Create database student -系统自己默认文件组示例2：用户自定义文件组create database student on primary (name='student_data', filename='F:DATABASESTUDENT1_DATA.MDF', SIZE=10MB, MAXSIZE=20MB, FILEGROWTH=15%)log on(name='student_log',filename='F:DATABASESTUDENT1_LOG.LDF',SIZE=2MB,MAXSIZE=10MB,FILEGROWTH=1MB)Go2.1.2修改数据库添加文件 Alter database database_name add file( 数据组文件，同上)删除文件Alter databasedatabase_name Remove filename更改文件Alter databasedatabase_nameModify file ( 数据组文件 ，要修改的内容)2.1.3 删除数据库 DROP DATABASE database_name2.1.3创建表CREATE TABLE database_name. owner .| owner. table_name( < column_definition >| column_name AS computed_column_expression| < table_constraint > := CONSTRAINT constraint_name | PRIMARY KEY | UNIQUE ,.n ) ON filegroup | DEFAULT TEXTIMAGE_ON filegroup | DEFAULT < column_definition > := column_name data_type COLLATE < collation_name > DEFAULT constant_expression | IDENTITY ( seed , increment ) NOT FOR REPLICATION ROWGUIDCOL < column_constraint > .n < column_constraint > := CONSTRAINT constraint_name NULL | NOT NULL PRIMARY KEY | UNIQUE CLUSTERED | NONCLUSTERED WITH FILLFACTOR = fillfactor ON filegroup | DEFAULT | FOREIGN KEY REFERENCES ref_table ( ref_column ) ON DELETE CASCADE | NO ACTION ON UPDATE CASCADE | NO ACTION NOT FOR REPLICATION | CHECK NOT FOR REPLICATION ( logical_expression ) < table_constraint > := CONSTRAINT constraint_name PRIMARY KEY | UNIQUE CLUSTERED | NONCLUSTERED ( column ASC | DESC ,.n ) WITH FILLFACTOR = fillfactor ON filegroup | DEFAULT | FOREIGN KEY ( column ,.n ) REFERENCES ref_table ( ref_column ,.n ) ON DELETE CASCADE | NO ACTION ON UPDATE CASCADE | NO ACTION NOT FOR REPLICATION | CHECK NOT FOR REPLICATION ( search_conditions ) 注意： 我们来看个简单的吧！Use 数据库名 在哪个数据库下建表create table 表名 -创建什么样的表(列名1 数据类型1 约束1，列名2 数据类型2 约束2，. . ., . . ., . . .,列名n 数据类型n 约束n，)注意：创建表 一定有列名示例：CREATE table stu_info -创建学生信息表( studentid int primary key, -定义主关键字 学号name varchar(10) null, -姓名gender char(4) null check( gender='男'or gender='女'),-域完整性约束 性别 birthday smalldatetime null,-出生日期 address text null default ('地址不详'),-默认值家庭地址 e_mail varchar(200) null, -e_mail home_tel char(15) null，-家庭电话)图书信息（书号，书名，出版社，版次，出版日期，作者，书价，进价，数量）create table cunbook 图书信息表(bno int primary key identity(1,1), -由1开始 自动增加1bnamevarchar(20) null,-书名publishvarchar(20) null,-出版社pnumint null,-版次ptimedatetime null,-出版时间auth varchar(10) null,-作者bpricedecimal(6,2) null,-进价spricedecimal(6,2) null,-售价bnumint null,-数量 )销售（日期，书号，数量，金额）create table xiaoshou( xstime datetime , bno int, xsnumint null, xsprice decimal(10,2) null, constraint pk_two primary key(xstime,bno),-设置xstime,bno为主键 constraint fk_bno foreign key (bno) references cunbook(bno) ,-设置bno为外键)注意：以上数据表在以后的学习中都会用到2.1.4修改表 2.2.1添加 列 Alter table table_nameAdd 列名 数据类型 2.2.2添加约束Alter table table_nameAdd constraint 约束名 约束类型 -参照xiaoshou图书表 注意：对已有数据的表，数据必须满足添加的约束类型，不是不可以添加约束 2.2.3删除 列 约束Alter table table_nameDrop column 列名 -删除列Alter table table_nameDrop constraint 约束名 -删除约束2.2.4 修改 列Alter table table_name Alter column 列名 数据类型 2.1.5 删除表Drop table table_name2.2: 数据操纵语言（DML-DATA MANIPULATION LANGUAGE）2.2.1插入数据语法 请一定要记住Insert into 表名（列名1，列名2，.列名n）values(值1，值2，.值n)示例：insert into stu_info(studentid,name,gender,birthday,address,e_mail,home_tel)values(20080101,'王明','男','1983-09-16 00:00:00','湖南长沙','lia','0731-6321075')只向某些列插入数据时可以省略不插入数据的列名insert into stu_info (studentid,name,gender,birthday)values(20080102,'周晓川','男','1970-08-24 00:00:00')插入的数据为全值时 列名可以省略示例：insert into stu_info values(20080103,'夏花','女','1988-01-19 00:00:00','江西省赣州市','lievca','130254121326') 可以从其他表中查出数据插入表中insert into stu_infoselect 20080104,'刘华','男','1972-11-11 00:00:00','中国台湾省','asdsua','1378655646' 同时可以使用union 或者 union all关键字insert into stu_infoselect 20080108,'桂花','女','1990-09-11 00:00:00','山东青岛','ldfgehua','13786993346'union allselect 20080105,'毛浙东','男','1960-08-15 00:00:00','湖南省湘潭市','liudehmsua','555023346'union allselect 20080106,'杨明','男','1978-08-09 00:00:00', '世界之窗','liugddehua','13786123346'union allselect 20080107,'你我他','男','1977-02-11 00:00:00','山东青岛','fgdehua','13785523346'注意：插入数据是对表中的新空记录项操作！，字符，日期型数据类型加 单引号。显示结果：_ 看到桂花这样的姓名的学生，毛浙东这样大龄学生 可不准笑哦_2.2.2：更新数据Update 表名 set 列名=value where 条件2.1示例： 把学号为20080107的地址改为默认值Update stu_info set address=default where studentid=20080107 -如上结果2.2不带条件的数据更新，把列名的数据全部更新 Update stu_info set address=default 2.3 更改多个列的值Update stu_info set name=杨晓明,address= null where studentid=20080106 注意 ：跟新是对表中已有数据的更新2.2.3：删除表中数据语法Delete from 表 where 条件示例：删除学号为20080103的学生信息Delete from stu_info where studentid=20080103删除多条信息Delete from stu_info where gender=女删除表中所有数据Delete from stu_info注意：删除表（drop 表不存在了） 和删除表中的数据（表还在，数据没了）的区别2.2.4：查询数据语法 Select select_list -要查询的清单。from table_list -从那些表中查得数据。where search_conditions -条件列表。Group by group_by_list-按什么分组。Having search_conditions-查询条件，但是having中的条件只跟在 group by 后出现。Order by order_list asc|desc-按什么排序。示例：-常量的查询以及带上一点运算符select 1select 'abc'select 15.6select 1+2,2-1,2*4,2%5,6/3select 'abc'+'efd'-全部查询 查询stu_info 表中的所有数据select * from stu_infoselect studentid,name,gender,birthday,address,home_telfrom stu_info以上两种查询结果一样得出同 stu_info 表中所有数据-部分查询 查询stu_info 表中 studentid,name,gender 信息select studentid,name,gender from stu_info-带条件查询 查询性别为女 地址不为空的学生信息select * from stu_info where gender='女'and address is not null -注意不为空时放在where 条件后要用is。多个条件并合 用关键字 and-消除重复项 用关键字 distinct 查询性别有几种select gender from stu_infoselect distinct gender from stu_info-查询 带百分比的记录数量 只进不退 查询stu_info 中40%的数据0select * from stu_infoselect top 40 percent * from stu_info-查询前 5条记录select top 5 * from stu_info-比较查询 查询stu_net成绩不为75分的学号select studentid from score where stu_net!=75查询stu_c 成绩大于70,stu_sql 成绩小于90分的学生成绩select * from stu_ score where stu_c>70 and stu_sql<90-范围查询 查询stu_c成绩在75-90分的学生成绩 -between.and 只针对于整型的数据select * from stu_scorewhere stu_c between 75 and 90查询 王二，张三，王峰的基本信息select * from stu_infowhere sname='王二'or sname='张三'or sname='王峰'select * from stu_infowhere sname in('王二','张三','王峰') -in() 没有数据类型的限制-查询数据为空时 一定要用上is 关键字select * from stu_infowhere address is nullselect * from stu_infowhere address is not nullselect * from stu_infowhere address='地址不详'-模糊查询-like % _ n字符字符-%代表多个字符，可以是一个 也可以是多个-也可以取相反的条件查询电话号码为6开头的学生信息select * from stu_infowhere home_tel like '6%'电话号码不为6开头的学生信息select * from stu_infowhere tel not like '6%'-_代表一个字符 查询电话号码的第二个字为5的学生信息select * from stu_infowhere home_tel like '_5%'-字符 存在这样的字符 相当于 in() 但是只代表一个字符查询电话号码开头字母为4，或5，或6的学生信息select * from stu_infowhere home_ tel like '465%'-相当于 not in() 只代表一个字符。查询电话号码开头字母不为4，或5，或6，第二个数字是 7或8 或9的学生信息select * from stu_infowhere home_ tel like '456789%' -查询stu_score 表中信息 按照stu_sql降序排列 stu_java升序排列Select * from stu_scoreorder by stu_sql desc,stu_java asc注意：得出的数据先按照stu_sql 降序排列，之后相同的数据项再安装stu_java 升序排列-stu_score表中按照studentid 分组Select * from stu_scoreSelect studentid,sum(stu_c),sum(stu_sql),sum(stu_java),sum(stu_net) from stu_scoreGroup by studentid注意：select studentid ,stu_c,stu_sql,stu_java,stu_net from stu_score group by studentid 则会报错列 'stu_score.stu_c' 在选择列表中无效，因为该列既不包含在聚合函数中，也不包含在 GROUP BY 子句中。因为group by 分组需要与聚合函数结合使用。列名没有在聚合函数内，就必须放到group by 后面。-查询学生成绩信息 其中stu_c大于50 stu_sql 小于90 ，按照studentid 分组和排序，且学号不为20080102。select studentid,sum(stu_c),avg(stu_sql) from stu_score where stu_c>50 and stu_sql<90group by studentidhaving(studentid!=20080102)-带having条件一定得跟在group by 后面order by studentid desc-取别名使用关键AS 或者直接空格 或者加双引号 ，不但可以给列名取别名还可以给表取别名 示例Select studentid as 学号,stu_c as c语言成绩 , stu_sql sql成绩 from stu_score 2.3: 数据控制语言（DCL-DATA CONTROL LANGUAGE）2.3.1申请用户添加用户 exec sp_addlogin 'ss','123','student',null把用户加入登录权限exec sp_adduser 'ss'2.3.2赋予权限语法语句权限：GRANT ALL | statement ,.n TO security_account ,.n 对象权限：GRANT ALL PRIVILEGES | permission ,.n   ( column ,.n ) ON table | view | ON table | view ( column ,.n ) | ON stored_procedure | extended_procedure | ON user_defined_function TO security_account ,.n WITH GRANT OPTION AS group | role 示例： 把对publish 表达增，删，改，查 权限赋予 ss用户grant select ,delete,insert,updateon publishto ss 2.3.3拒绝权限：DENY ALL | statement ,.n TO security_account ,.n 对象权限：DENY ALL PRIVILEGES | permission ,.n ( column ,.n ) ON table | view | ON table | view ( column ,.n ) | ON stored_procedure | extended_procedure | ON user_defined_function TO security_account ,.n CASCADE 示例：拒绝对publish增，删，改，查 权限赋予 ss用户Deny select ,delete,insert,updateon publishto ss 2.3.4收回权限REVOKE ALL | statement ,.n FROM security_account ,.n 对象权限：REVOKE GRANT OPTION FOR ALL PRIVILEGES | permission ,.n ( column ,.n ) ON table | view | ON table | view ( column ,.n ) | ON stored_procedure | extended_procedure | ON user_defined_function TO | FROM security_account ,.n CASCADE AS group | role 示例 从ss用户处收回对publish表