初识SQL语句

1、create 使用
CREATE DATABASE test//j建库
ON
{
name = test_data,
FIlENAME = 'D:test_data.mdf',
SIZE = 3,
MAXSIZE = 5,
FILEGROWTH = 1
}
LOG on
{
name = test_log,
FILENAME = 'D:\test_data_log',
SIZE = 1MB,
MAXSIZE = 2MB,
FILEGROWTH = 1
}
go
//表
use test
Create Table teacher//建表
{
ID int Identity(1,1) not null,
Name varchar(20) NOT NULL,
Sex char(2) null,
Phone varchar(20) not null,
Sallary smallmoney null,
Address varchar(20) null,
Email varchar(30) null,
}


2、drop//删除

DROP DATABASE test
DROP TABLE teacher


3、alter//更改
// add :file ;log file filegroup
//remove:file filegroup
//modify:name file filegroup

ALTER DATABASE books
modify name = livary

ALTER TABLE books
ADD price money default 23.8

Alter Table books
DROP COLUMN price


4、Insert//插入
Insert into student values (01,'wangming','nan','1980-1-1')
select *from student where student_id = 01
insert *from student select *from teacher
select *from student


5、delete//删除
delete student where student_id = 1
Delete student

6、update//更新
update student set sex='女' where sudent_id = 1
update books set isread = 'true'

7、select//查找
use lib
select *from books
select book_name,price from books
select book_name,price,price*0.8 as newprice from books
select top 10 * from hosts
select * from books where Not(price>40)
select * from books where price<40 and price>20
select * from books where price<40 or price>20
select * from student where 姓名 like '高%'
select * from student where 姓名 like '[陈-高，肖-赵]%'//查询所有介于陈-高并且肖-赵的学生信息
select * from books where price between 20 and 40
select * from books order by price asc//将所有图书价格从高到底排列


8、grant//权限
grant select,insert on student to sal with grant option
//将student表的查询权和插入权用户sa1，sa1同时获得将这些权限转授给别的用户的权限
Deny update on student to sal// 拒绝sal用户对student表进行修改的操作
revoke insert on student from sal cascade//收回sal用户对student表的插入权
//cascade表示收回权限时要引起连锁回收。后期会补


9、declare//声明
//数据声明：用于声明一个或多个局部变量、游标变量、函数、过程，在用declare所有的变量被赋值NULL，需要用set或select赋值
//用到在查查怎么写的吧


10、set//赋值
set{@local_variable = expression}


11、print//输出
print @local_varable | @@FUNCTION


12、BEGIN end//代码块

declare @num int
select @num = 0
while @num <10
begin
select @num = @num+1
Select @num
end


13、goto//跳转

begin
select stu_id from student
goto skip
select * from student
skip:
print 'the second select did not get exrcuted'
end


14、if else//选择

declare @x int ,@y int ,@z int
select @x = 2,@y = 5,@z = 4
if @x > @y
print 'x>y'
else
if @y > @z
print 'y>z'
else
print 'z>y'


15、case//不会用 多选项

select score
case
when score>90 then '非常优秀'
when score>80 then '优秀'
when score>70 then '良好'
when score>60 then '及格'
else '不及格'
end
form score


16、while//循环

declare @i int
set @i = 5
while @i >-1
begin
print @i
set @i = @i -1
if @i = 1
break
end


17、waitfor//暂停停止执行SQL语句、语句块或者存储过程，直到所设定的时间已过或者已到才执行
waitfor time '11:00:00'
waitfor delay '01:00'


18、return
//用于结束当前的程序，返回到上一个调用它的程序或者其他程序
//return要指定返回值，如果没有， 系统会根据程序执行的结果返回一个内定值
//如果运行出现多个错误，sqlserver系统将返回绝对值最大的数值，如果此时定义的返回值，则返回用户定义的值，return不能返回NULL


19、批处理：从客户机传递到服务器上的一组完整的数据和SQL指令（也可以是一条也可以包涵多条sql数据）
//有很多注意事项，等用到的时候在写吧。
use STU
go
select * from student
go
//此处使用了两个批处理，第一个批处理完成打开数据库STU的任务，第二个批处理完成学生信息查询任务