数据库 基本用法语句

use [0325]  --使用0325数据库
 create table student      --创建表格
 (
   code int primary key identity(1,1) not null,  --列
   name varchar(50)not null,    --列
   sex varchar(50)not null,    --列
   age int not null,          --列
   [weight] int not null,         --列
   hight int not null,       --列
   idno int not null,      --列
   iddress varchar(50) not null,    --列
 )
 go      --连接符
 insert into student values(‘张三‘,‘男‘,18,78,180,371521,‘淄博‘)  --添加表中信息
 insert into student values(‘李四‘,‘女‘,21,55,170,371522,‘济南‘)
 insert into student values(‘王五‘,‘男‘,17,70,178,371523,‘周村‘)
 insert into student values(‘赵六‘,‘女‘,22,50,167,371524,‘北京‘)
 insert into student values(‘田七‘,‘男‘,23,81,175,371525,‘南京‘)
 insert into student values(‘赵丹‘,‘男‘,27,81,175,371525,‘南京‘)
 
 go
 select *from student                                   --查询表中所有数据
 select *from student where code=3                      --查询code=3的信息全部显示 *所有信息
 select name,sex  from student where code=3             --查询只显示code=3的信息
 select *from student where code!=3                     --查询显示code!=3的信息
 select *from student where code>=3                     --查询code>=3的信息全部显示 *所有信息 
 select distinct iddress from student                   --去重查询.........
 
 select top 4 name from student                         --查找头4行的名字.........
 select top 4 *from student                             --查找头4行信息........... 
 
 
 select *from student where weight between 70 and 80          --查询重量在70到80之间的  
 select *from student where age in (18,23,25)                       --  查找年龄范围在18，23，25的 ........       在什么参数范围之内  
 select *from student where age not in (18,23,25)                --  查找年龄范围不在18，23，25的 ........  
 select *from student where weight >=70 and weight<=80        --逻辑语 and        
 select *from student where weight <= 70 or weight >=  80     --逻辑语or

    
 select *from student where name like‘王%‘                --%任意字符长度 
 
 select *from student order by age  ,hight desc                  --asc升序 desc降序 对年龄和身高进行排序 ......主牌 次牌 
 
 select *from student where age+5>28                       --年龄加5大于28的         +-*/% 
 update student set age = age +age * 0.15 where name like ‘赵%‘       --把有第一个为赵的年龄增长15%
 update student set name=‘赵丹‘,sex=‘女‘,hight=190 where name=‘赵六‘        --update修改 
 update student set age =22 where code=8
 
 delete from student                            --删除所有数据
 delete from student where code = 15             --删除一行信息
 delete from student where code >=10      --删除>=10的行数信息
 --查询年龄不在身高是178人的年龄范围之内的信息
 select *from student where hight in(178)               --子查询   分布查开
 select *from student where age not in (17)
 --查询身高不在年龄是23岁人的身高范围之内的信息
 select *from student where hight not in(select hight from student where age in(23))    --子查询
 --名字叫赵丹的人年龄比code=1的张三那个人的年龄大三岁的人的信息
 select *from student where age - 3 =(select age from student where code = 1)and name=‘赵丹‘  
  --名字叫赵丹的人年龄比姓王的年龄大4岁的人的信息
 select *from student where name=‘赵丹‘and age - 4 in (select age from student where name like‘李%‘)  
  --名字叫赵丹的人年龄比姓王的年龄大6岁的人的信息
 select *from student where name=‘赵丹‘and age - 6 in (select age from student where name like‘李%‘)