0022 sql 必会的基本操作

一，数据库

01，创建数据库

Create database 名称

on primary

{

   name = '名称' ,

   filename = 'c:\xx\名称.mdf' ,

   size = 10mb,         --数据库的初始大小

   filegrowth =1mb,     --如果初始大小不够用了，每次增长1mb。

   maxsize = 20mb      --数据库的空间上限，填写unlimited表示无限制

}

log on

{

   name = '名称_log' ,

   filename = "c:\xx\名称_log.ldf"

   size = 5mb,

   filegrowth = 10%,    --增长的方式有两种，一种是按照固定大小增长，一种是按照百分比增长！

   maxsize = 10mb

}




02，删除数据库

       只能用drop

            drop database 名称

二 , 数据库表

01,创建数据库表

--使用哪个数据库，如果不写这一句是默认的数据库，也可以用鼠标选当前数据库

use testDB

--创建表

   Create Table tablename

   (

      --id表示字段名

      --int 数据类型

      --primary key 主键

      --not null 非空

      --identity（1，1）初始值是1 每次自增长1

      id int primary key not null identity(1,1),

      --unique 唯一

      name varchar (20) not null unique

   )




02,删除表

        Drop table 表名

03, 修改表结构

      --增加列
              Alter table 表名 
              Add 列名 类型
     --删除列
              Alter table 表名 
              drop cloumn 列名
     --修改列，修改列类型
              Alter table 表名
              Alter column 列名 type

三 , 数据插入

01, 规范一些使用插入语句的小规范

     1）中文字符串前最好加一个 N
     2）列名用中括号扩起来 ---- 像这样  [列名]
 
02, 常规写法

      Insert into tableName
            ( [column1] , [column2] )
      values
            (N'中文','11ds')
 
03, 多行一条语句插入多行

      insert into 表名 （[列1],[列2]）
      select  '值1','值2' union all     --这里呢，union 和 union all的 区别
                                               --主要是对于重复值得处理，union 会过滤掉重复行，而union all会全插进去
      select  '值3','值4' union          
      select  '值5','值6'
 
04, 复制到新表 将原有表中的数据复制到一个不存在的新表中

      select * into newtable from oldtable
      --仅复制表结构如何做呢？
      select * into newtable from oldtable where 1<>1
      select top 0 * into newtable from oldtable 
 
05, 插入其他表的数据  向一个已有表中，复制其他表的数据

      insert into tablename(column,column2)
      select column,column2 from oldtable
 
06, 强行写入 强行写入标识字段

     --对于已经设置自动增长的列，默认情况我们无法对其输入值。
     --可以用一下语句去强行写入。
 
     --1）开启添加（解除添加的限制）
            Set indentity_insert tablename On
     --2）可以手动插入id了
            insert into 表明 (id，name) values ('1002','大二')
     --3）关闭手动插入
            Set indentity_insert tablename off

四 ，数据的删除和更新

        01, 删除

            1）删除记录
                  Delete from 表名 where id ='xx'

            2）删除所有数据，并回归初始化标识字段。
                  Truncate table 表名

            3）delete与truncate区别
                 a. truncate是能使种子回到初始值
                 b. truncate不能加条件
                 c. truncate不能涉及触发器
                 d. truncate性能要比delete高得多

        02, 更新

            1）基础的update
                 update 表名 
                 set [列名]='值'
                 where [列名] ='值'

            2）和replace一起使用
                 --19岁以上名字中的'星'特换成'★'。
                 update 表名
                 set name = replace(name,'星','★')
                 where age > 19

五，NULL 数据处理和类型转换

    01, Null数据的处理

         1）检索出null值
              select * from 表 where xx is null
   
         2）null值替换
              select 
              name，
              isnull ( cast (字段 as varchar(20)) , '空')
              from 表名
 
     02, 数据类型转换
         1）Cast
              --'101'可以用表中字段来替换
              select cast('101' as varchar(20))
    
         2）Convert
              select convert(varchar(20),100)

六，数据查询

01, 基础的查询
    1）重命名列
         select name as '姓名' from 表名
    2）定义常量列
         select 是否 ='是' from 表名
    3）top用法 percent
         --这种写法可以获取前20%条字段
         select top 20 percent * from 表名
    4）去除重复列
         select distinct 列名 from 表名
    5）聚合函数
         max    avg    count    min    sum
         --多个聚合结果 在一个结果集中
         select
         最大年龄 = (select max(age) from 表名),
         最小年龄 = (select min(age) from 表名)
    6）between and
         select * from 表 where xx  between 5 and 6
     
02, Union 使用Union将两个结果集汇聚在一起
     --     年龄      工资
     -- ————————
     --      19       $20000 
     --      50       $20005
     --      30       $23000
     --     汇总     $63005

     --   查询各年龄段工资，同时显示所有工资汇总。（像上边的表）
     select
     --把年龄转换成varchar类型
     Convert(varchar(10),[age]) as 年龄
     Sum([salary]) as 工资
     from  员工表
     group by age
     --将两个结果集，合并成一个结果集
     union
     select
     --汇总是一个常量列
     '汇总' , sum(salary)
     from 员工表
     使用union合并两个结果集时，
     两个结果集列数必须一致，并且数据类型对应。
     这就是代码中，把年龄转换成varchar的原因。
 
03, Order by
     -- Order by 用于结果集排序，
     -- 其Order他后边不只可以接一个字段，
     -- 也能接一个 表达式。
    Select * 
    from 表 
    order by (age+salary)/2.0 desc

七，字符串函数和时间函数

      字符串函数 
 
    01, 大小写转换
         --upper 转化成大写 
         --lower  转换成小写
         select upper('AsaR')
 
    02, 长度
         --len 字数
         --datalength 字节数
 
    03, 去除前后空格 
         --rtrim 去除右边空格
         --ltrim  去除左边空格
 
    04, 字符串截取
        --Left('串',15)  从左侧开始 截取15个字节
        --right('串',15) 从右侧开始 截取15个字节
        --SubString('串',5,5)  从左侧第5个字节开始，截取5个字节
 
    05, 字符串替换
        --replace('你们','你','我')  把你们  替换成  我们
 
    时间函数
 
    01, 获取当前日期
         --getdate()
 
    02, 100天以后
         --dateadd(day,100,getdate())
 
     03, 时间差函数
         --dateiff(year,'1990/10/11',getdate())
 
     04, 查询年月日
         --year(时间)  获取年
         --month(时间) 获取月
         --day(时间)    获取日
     ————————————————
    --计算出每个年份出生的人数
    select year(birthday),count(*)
    from 表
    group by year([birthday])
 
    05, 获取日期的年、月、日、时、分、秒
         datepart(year,日期)   
         datepart(month,日期)  
         datepart(day,日期)  
         datepart(hour,日期)  
         datepart(minute,日期)  
         datepart(second,日期) 

八，数据库的完整性约束

      实体完整性
      01, 建表时定义主键

           Create table 表名
           (
                Sno int identity(1,1),
                Sname nvarchar(20),
                --设置主键
                Primary key (Sno)
            )
 
      02, 添加主键

           alter table 表名 
           add constraint PK_表名_Sno
           primary key(id)
      参照完整性 01 建表时定义外键

      create table 表名
      (
           sno int identity(1,1) primary key,
           cno int not null,
           foreign key(cno) References
           表名2(Cno)
           on Delete cascade     --级联删除
           on update cascade    --级联更新
           -- on delete on action  删除管制
       )
 
     03, 添加外键
          alter table 表名
          add constraint FK_表名_表名2
          Foreign key(cid) references 表名2(cid)
     用户定义完整性1.非空约束
          alter table 表名
          alter column name varchar(20) not null
 
     唯一约束
          alter table 表名
          add constraint UQ_表名_列名 unique(列)
 
     检查约束
          alter table 表名
          add constraint CK_表名_列名 check(age>5)
 
     默认约束
          alter table 表名
          add constraint DF_表名_列名 default('男')
          for gender

     删除约束    --删除约束
          alter table 表名 drop constraint DF_表名_列