SQL基本概念、SQL数值类型、建表对表结构的修改，修改表名，列名，表的查询、别名、表的拆分合并、distinct第四次课

SQL 语言基础

1 SQL基本概念

1） SQL：结构化查询语言。使用SQL命令，给出几个用户参数，系统根据这些参数从底层数据库提取结果。这个过程是封装了的，结构化的。

2） SQL发展过程：

3） SQL语言的主要功能概括

　　数据定义功能：create , drop,  alter 对表头结构的建，删，改

　　数据查询：select  最主要应用的，最复杂部分

　　数据操纵：insert , update ,delete 数据内容增，改，删

　　数据控制： grant，revoke,deny 授权用户权限控制

4 ）SQL SERVER 中主要基本的数据类型

2 SQL数值类型

(1) 数值型

<1>准确型：能精确存储数据，如整数，定点小数

  Int      4字节，表示范围： -2^31~ 2^31-1

 　bigint    8字节

 　smallint   2字节

 　tinyint :  1字节，且是无符号数，表示0-255

  numeric(p,q)  小数，指定最多为p位，其中有q位小数,如表示 numeric(4，1）表示0-100.0之间的数学成绩，有一位小数

  不要定义float ，应该使用 :数学  numeric(4,1)

  定义身高以米为单位，两位小数：  身高 numeric(3,2)

<2>近似型： float 型 8字节  ---类似C中的double

        real 型  4字节 ---类似C中的float

  数据库中少用近似型，多用准确型

（2）字符串型  string

一个字符包括英文，其它非英文如中文汉字。早期一个英文字符占1B，一个汉字占2B，在计算串长时易错。后来全世界的字符统一编码，不管是什么字符都统一为2B内码

任何变量要定义，如早期char[20] 是定长串，是否用完都是占20个字节，存在浪费，好处是数据定长，便于随机查找。如个人信息的备注内容，长度差别巨大，一般使用变长串，用多少字节占多少空间，如早期串指针。

常用的数据库中字符串类型：

 Char(n)  n取值 1-8000  普通编码，  定长

 Nchar(n) 统一字符编码，n取值1-4000  定长

 Varchar(n ) n取值1-8000,变长串

　Nvarchar(n)  n取值1-4000, 统一字符编码的变长串

 如：定义一个姓名列，性别列，个人说明列

  姓名: char(6), nchar(6)

  性别：char(1), nchar(1)     f /m  , 1/0, 男/女

  个人说明： varchar(100), nvarchar(100) ，每个人说明情况内容长度差别大，故使用变长串，反而节约空间.

  Text ,  Ntext  可存储海量字符，如小说的内容，最多20亿个字符（10亿个汉字）

总结：定长串nchar( ) ,  变长串nvarchar(  ),

长篇内容使用Ntext,如果列是数字或英文字母char( ) 

(3) 日期时间型

Datetime:占8字节空间来存放一个标准的日期时间，早支持1753年

    形如：1999-12-1 12:33:55 12.332

Smalldatetime ：占四字节，只支持 1900-1-1 -2079年

 前者支持到毫秒，后得支持到分钟

说明：一个日期是相对于1900-1-1的间隔天数，一个日期对应的是一个整数。所以日期其实是一个整数，使用公式显现成一个日期格式.

  　　日期1-日期2=整数n  

  　　今天的78天后是哪一天？  select GETDATE()+78

时间是一个相对于当天从00:00:00开始的秒数.

特别注意： 字符串常量，日期时间常量在sqlserver用单引号

（4）binary、image型

常用于存放二进制的文件内容，如一个WORD文件，一张照片

  

3 建立数据库 

1） 建立基本表

 Create  table 表名(列名及参数和约束 )

例：建立一个课程表

create table 课程表(   
课程号 char(3),课程名 nchar(12),学分 tinyint ,
开课学期 tinyint,课程说明 nvarchar(200)
)

2）对表结构增、删、改（modify structure）

对表名进行变更，弄不好有可能造成大量旧数据内容丢失。

主句： alter table 表名  修改子句

• 向已有表增加新列

　　Alter table 表名  add  字段名及参数

　　例：向上面的课程表增加一列所属专业编号

  alter table 课程表 add 所属专业 nchar(5)

 (实际开发中，宽度5是参考“专业表”的同列宽度)

例：一次性同时增加多列，如向课程表增加开课学院，任课老师

alter table 课程表 add 开课学院 nchar(10),任课教师 nchar(6)

• 删除表中的原有列

　　Alter table 表名 drop  column 列名

　　alter table 课程表 drop column 开课学院,任课教师

• 修改已有列的字段列参数(不能改列名，只能修改类型，宽度，约束)

　　Alter table 表名 alter column 列名(参数)

　　例：把所属专业列的nchar(5)改为char(20)

　　alter table 课程表 alter column 所属专业 char(20)

(3)更名基本表名

先将表复制进新表在删除

例：把上课程表2更名为课程3

  Select *  into 课程表3  from 课程表2

  Drop table  课程表2

把原2表全部表头和内容复制3表，再把2表删除掉

(4)对已有列更名

例：假设原表课程表2已有大量数据，不可能重新建表，现要把”所属专业”改为“从属专业”；

alter table 课程表 add 从属专业 char(20)
update 课程表 set 从属专业=所属专业
alter table 课程表 drop column 所属专业
select * from 课程表

不要在意列的左右顺序，也不要在意行数据的上下位置顺序

 

又如：把学生表的生日列修改为“出生日期”列:

alter table 学生表 add 出生日期 smalldatetime

update 学生表 set 出生日期=生日

alter table 学生表 drop column 生日

 

4  简单的基本SQL语句

 

（1）基本查询

 

形如：Select exps from table where exps2

 

 

 

select 学号,姓名 from 学生表 where 性别='男'
select * from 学生表  where 性别='男'
select 学号+姓名,YEAR(getdate())-YEAR(出生日期) from 学生表
select 学号+姓名 as xhxm,YEAR(getdate())-YEAR(出生日期) as nl
 from 学生表 as xs
select 学号  as 编号,出生日期 as 生日 from 学生表
select 学号  编号,出生日期 生日 from 学生表 xs

// 实年龄
select 学号,姓名,YEAR(GETDATE())-YEAR(出生日期) as 年龄 from 学生表

//求虚岁
  select 学号,姓名,convert(numeric(3,0),(datediff(day,出生日期,GETDATE()))*1.0/365+0.5)   from 学生表

select convert(numeric(2,0), 34.15+0.5)

 

（2）表名和列名的别名问题

 

以后编程时要引用这些数据时，方便书写

 

select 学号 as xh, 姓名 as xm,身高 sg  from 学生表 as xsb

 

列名，表达计算结果列名，表名都可以取alias ,方便后继编程引用

 

（3）表的拆分和合并

 

格式：select exps1 into tablename from ……

 

把查询结果不以视图显示，而是默默地存放到一个新表中去（物理表）

 

select * into T1 from 学生表 where 性别='男'

 

select * from t1

 

select * into T2 from 学生表 where 性别='女'

 

以上操作可以把大表拆分成多个小的表

 

实例：把学生表中女生数据存放到一个“学生女”表中，列有学号，姓名，性别，年龄

 

select 学号,姓名,性别,
  DATEDIFF(YEAR,出生日期,GETDATE())as 年龄 into 学生女
  from 学生表 where 性别='女'

select * from 学生女

 

反之，如何把多个结构相同的表合并成一个大表？

 

如：准备一个空格T3，把T1,T2内容合并到T3

 

select * into t3 from 学生表 where 5>6

 

select * from t3

 

insert into t3 select * from t2

 

insert into t3 select * from t1

 

实例：把学生表中姓杨的学生和学生女表中年龄在以上的的学生合并成一个新表

 

select 学号,姓名,性别 into temp from 学生表 where 1>2

select * from temp

insert into temp select 学号,姓名,性别 
    from  学生表 where LEFT(姓名,1)='杨'

insert into temp select 学号,姓名,性别 
       from  学生女 where 年龄>=35

 

（4）去掉查询结果的重复值--distinct

 

用于求类别，求集合

 

例：求学生表中哪些学生没有选课 

 

select * from 学生表 where 学号 not in(select distinct 学号 from 成绩表)

 

select distinct 性别 from 学生表

 

//有哪些学生选了课？

 

select distinct 学号 from 成绩表

 

//那些学生没选课

 

select 姓名,性别 from 学生表

 

  where 学号 not in (select distinct 学号 from 成绩表) 

 

例：求学生表中哪几种姓氏

 

select distinct LEFT(姓名,1) from 学生表