--创建库 先检查是否有该库名，有先删除，再创建 use master go
if exists (select * from sysdatabases where name='jmn') drop database jmn
create database jmn on ( name='jmn_mdf', fileName='d:\MMSQL\jmn.mdf', size=4MB, maxsize=20MB, filegrowth=3MB ) log on( name='jmn_ldf', fileName='d:\MMSQL\jmn.ldf', size=1MB, maxsize=20MB, filegrowth=30% )
--创建表 同样是先删后建 use jmn go
if exists (select * from sysobjects where name='student') drop table student
create table student( studentNo int not null, loginpwd nvarchar(20) not null, studentName nvarchar(20) not null, sex bit not null, gradeId int not null, phone varchar(50) null, bornDate datetime not null, address varchar(255) null, idEntitycard varchar(18) not null )
----------------------创建约束 主键约束 alter table student add constraint pk_studentNo primary key (studentNo)
唯一约束
alter table student add constraint uq_idcard unique (idEntityCard)
默认约束
alter table student add constraint df_address default ('地址不详') for address
检查约束
alter table student add constraint ck_bornDate check(bornDate>'1980-1-1')
引用（关联）约束 对应两张表的相同主键
alter table student add constraint fk_gradeId foreign key(gradeId) references grade(gradeId)
删除约束
alter table student drop constraint df_stuAddre
--------------select 语句的用法
添加别名
select studentNo as '学生编号', studentName as '学生姓名' 姓别=sex from student
需要同时满足两个条件时
select * from student where loginpwd=1223456 and phone=1234
查询前3条 学生名
select Top 3 studentName from student 查询前 百分之50 学生名 select Top 50 percent studentName from student
显示所有 总分>60 结果按降序排列 如有重复的 以第二规则 学生号 进行降序排列 （order by 是升序）
select * from where total>60 order by total desc, studentNo asc
两列合并为一列显示 如果是null的用空白表示
select studentName + isnull(phone, '') from student
charIndex
从'my jmn school'以第1位开始找'jmn' 返回 4
select CHARINDEX('jmn','my jmn school',1)
len
查询长度 空格一起算 返回16
select len('my jmn school')
convert(varchar(5),sex) 转变类型,不同类型不能拼接
select studentname + convert(varchar(5),sex) from student
----------------select语句总结 select 列名，列名，列名 frome 表名 whrer 查询条件表达式 order by 排序 asc或desc
--------------------模糊查询 like
第三位是2或3的记录，[^2,3]% 不是2或3的记录 select * from student where loginpwd like '[2,3]%'
select * from student where loginpwd like '张%'
select * from student where loginpwd like '张_'
and
select * from student where stuResult>=40 and stuResult<70
between 之间
select * from student where stuResult between 40 and 70
in
select from student
where sex in ('男','女')
--等同于 select from student
where sex='男' or sex='女
---------------聚合函数
sum 总数， avg 平均值, max 最大值, min 最小值， count 计数,
select SUM(stuResult)as '所有男同学的总分' from student where sex='男'
----------------分组查询 select studentNo, count() from student where stuResult<60 -显示<60的记录 group by studentNo -分组并计算各的统计值 having COUNT()>1 -去掉<1 的记录
------------内连接
selec 表1.列名,表2.列名 from 表1 inner join 表2 on 表1.列名 = 表2.列名
----------内连查询 等值连接 - 两张表有相同的列 select from student select from result
select loginpwd, studentName, sex, emp.studentResult --从下面语言拿到临时表 from student inner join result as emp --表名1 inner join 表名2 as 生成临时表 on student.studentNo = emp.studentNo --两张表对应的列
或者 不等于 select stu.studentNo, studentName, sex, emp.studentResult from student as stu inner join result as emp on stu.studentNo <> emp.studentNo
--------------------外连接 left,right,full 如left 会显示左边表的全部 右表没有的以null显示,right和full以此类推 select from student select from subject select lfs.studentNo, studentName, subjectNo from student as lfs full join subject as rig on lfs.studentNo = rig.subjectNo
-------------insert语句 select * from student
注意，1.值与列数要相同 2.不能为标识列赋值 如studentNo 是系统自动生成的 3.要符合约束要求、 非空的不能没有值 4.列有设置默认值的 可用default insert into student (studentNo,loginpwd,sex,gradeId,studentName,phone,bornDate,idEntityCard) values('9','5678','0','9','yggggy','1233234','1990-01-01 00:00:00.000','12')
批量插入 把表2 选出来的列全部插到表1
insert into tongxunlu select studentName, studentNo, phone from student
批量插入2 向表tongxunlu 插入三条记录
insert into tongxunlu (name,no) select 'xiaoming', '18' union select 'xiaozhang', '20' union select 'xiaonhong', '17'
批量插入3 从student中选出两列 插入到新表 --只能是新建表
select studentName,idEntityCard into tongxunlu1 from student
批量插入４ 从student中选出两列 插入到新表 --只能是新建表
每一行会按顺序添加一个ｉｎｄｅｘ值
select * from tongxunlu3 select studentName,stuResult , IDENTITY(int,1,1) as studentNo into tongxunlu3 from student