SQL笔记

1.select基本语法

  select *|column_name,
  from table_name
  where (条件)
  order by (指定列操作)
  group by (分组操作)
  having (分组条件)
  computer by (可以添加一些小的计算，如合计等)

2.过滤性
  select distinct (条件)
  from (表名)

3.例子：
      select top 3 *  
      from 球赛结果表
      order by 成绩 desc
解释：查询表 ‘球赛结果表’ 中所有记录的前3个记录所有字段，并且成绩按降序（desc）排列  .
  
4. 查询表中所有字段的前百分之十的行，记录
   select top 10 percent *
   from (表名) 

5.查询表中的所有信息 ：select * from table_name

6.简单查询列：
   select column_1,column_2 (要查询的列名)
   form table_name(要查询的表名)|视图名|数据集合

7.例子：查询陕西省所有的作家
     select state  '陕西省',count(*)
     form authors
     group by state
     having state='陕西省'

表示查询authors表中所有陕西省的作家，并且分组操作以‘陕西省’显示出来

8.给列改名字：
   如把列名为‘price’的列改为‘单价’,方法有三种
   第一，price '单价'
   第二，price as '单价'
   第三，'单价'=price

9.例子：由书店统计的价格和数量得到总书价,直接数据库中操作
       select book_name,price,num,price*num
       from table_name
 computer by sum(price*num)
  表示新增一个字段为书的总价值，即price*num

10.对于一些字段数据的升序和降序操作
   语法：order by column[asc|desc],cloumn[asc|desc],....更多的列
         []中的可选择升序asc和降序desc
例如在某统计表中对生日和年龄进行的排序
  语法：order by 生日 asc,年龄 desc
  
11.查询学生编号小于100的学生所有信息
   select *
   from student
   where stu_id<100

12.按学生的名字来找人：where stu_name='来旭'  // 查找名叫来旭的信息

13.按学生的姓来找人：where stu_name like '来%' 
     //查找所有姓‘来’的信息，“%”表示"来"字后面不管有多少个字
 
    like '来_':  查找姓‘来’的后面只有一个字的姓名
  
   如like 'b[^a]':表示所有以b开头的第二个字符不是a的查询条件
  
14.条件查询：
   where age<50 and age>40     //查询某表中年龄大于40并且小于50的人
   等价写法：age between 40 and 50

15.条件查询：
   where moth(birthday) in (5,8,12)   //查询生日是在5，8，12月出生的人
  等价写法：where  moth(birthday)=5 or
                   moth(birthday)=8 or
                   moth(birthday)=12

16.多表查询：
  表1：student，其字段有stuid,stuname
  表2：score,其字段stuid,crsid,criedif
 连接它们：
    select student.stuid,student.name
    from student join score on student.stuid=score.stuid    //join和on是连接语句和条件
    where student.name='来旭'   //查询的人是叫来旭

 

Tansact-SQL
1>   SQL server管理是由两种类型的数据库--联机事务处（OLTP）理数据库和联机分析处理(OLTP)
SQL server2000由两部分组成：服务器组件和客户端工具
数据库Datebase作用应用：数据存储和新的业务数据分析；
SQL server允许两种类型的用户帐户：系统用户和数据库用户；
例1：将pubs设置未用户victoria的默认数据库；语言如下：
 EXEC sp_defaultdb 'victoria','pubs'
例2：在当前的数据用户为windows NT用户 Corporate/Georgew添加帐户，并取名未Georgew
 ExEC sp_grantdbaccess 'Corporate/Georgew','Georgie'
 sp_revokedbaccess [@name_in_db=]'name'（其中[@name_in_db=]'name'意思是要删除的账户名）
例3：use master
 go
     sp_grantlogin 'onlinedomain/arwen'/*一个合法帐户或组连接到SQL erver*/
     go
     sp_defualtdb @loginame='onlineddomain/arwen'@defdb='books'
     go/*修改一个登陆的默认db*/
例4：下列是显示权限的优先顺序首先给public角色授予select权限然后将特定的权限授予用户Mary 、John和Tom于是这些用户就有了authors表的所有权限；
 代码如下：
 use pubs
 go
 grant select on authors to public
 go
 grant insert,update,deleteon authors to Mary,John,Tom
 go
/*grant all to tj*/这句话的意思将所有的权限授予tj；
SQL server使用grant,revoke,deny命令管理权限
 1>grant授予对象权限，就是允许某个用户对该对象执行某种操作(select,update,insert,delete或esecute)
 2>revoke是撤销对象，语句权限；
 3>deny拒绝对象权限；
全局变量的命名规则是:declear @@----
局部变量的命名规则是：declear @id_jintian ddatetime;
        declear @id_jintian getdate()
               print   @id_jingtian
如果不同用户创建了相同的表时要查看表时需要完整的路径：
select * from tj.stu;
select * from dbo.stu;否则查看的话将会是默认的当前用户；

sp_addlogin 这是添加登录
sp_addUser 这是添加用户

not:select * from employee where not(fname='Ann')
and:select * from employee where Iname='UK' and fname='Ann'
or :select * from employee where regin='UK' or  regin='FRA'

删除数据库的语句是：drop database pubs或者是drop database pubs,sales
例5：在默认的数据库中创建表ibmstudents
use ibmioa
go
create table ibmstudents
(
 stu_id varchar(8)not null,
 stu_name varchar(8)not null,
 stu_sex varchar(2)not null,
 stu_birthday datetime

)
例6：插入一条语句：
insert into ibmstudents(stu_id,stu_name,stu_sex,stu_birthday)values('1001','zhangsan','nan','1985-5-5')
删除表：drop table table_name
删除表中所有的行：use pubs
    go
   delete anthors
删除表中au_Iname是mcbadden的所有行
 use pubs
 go
 delete from authors where au_Iname='mcbadden'
例7：要检索单价大于$50的产品的名称
select productsName from northwind.dbo.products
where unitPrice>$50.00
列表查询找单价为$50或更高的所有产品
select productID,productName,unitprice from northwind.dbo.products
where not unitprice<$50 order by productID
查找书名任意位置的包含单词computer的所有的书名
use pubs
go
select * from titles where title like'%computer%'
查询返回按pub_id升序排列的结果
use pubs
go
select pub_id,type,title_id from titles order by pub_id
列出前5项的结果查询：
use pubs
go
select top 5 pub_id,type,title_id
from titles order by public_id

//查看一个数据库中总共有多少张表；
select * from sysobjects
where type='U' order by name

select name from sysobjects
where type ='S' order by name
//查看数据库中所的列；
select * from syscolumns
//查看数据库中有几种数据类型
select distinct type from sysobjects
或select name from systypes;

select ob.name,ob.uid.name
from sysobjects ob,sysusers ur
where ob.uid=ur.uid and ob.type='U'
order by zds

//给指定的表中添加一个列，语法如下：其中table是指定的所要添加的表，card是要指定添加的列，int是指定的数据类型，可以改为其他的；
alter table test add  card int

//查看数据库中所有的表名及他所有的列名；
select so.name"表名",sc.name"列名"from sysobjects so,syscolumns sc where so.id=sc.id and so.type="U" order by name

//以下的代码是将chapter表中的数据过滤，并将其转移到另一个表中nchapter
insert into nchapter
select distinct chptid,cursid,chptname,chptbrief from chapter
order by chptid

//删除一个数据库中的所有的表的语法：
select 'drop table'+name
from sysobjects
where type='U'

//下面的一段代码是检查约束的一段创建了表的时候设置了检查字段的方法：
create table s
(
sno char(10) not null /* 学号字段*/
constraint pk_sno primary key clustered /*主键约束*/
check(sno like '31300501[0-9][0-9]'),/*检查约束*/
sname char(8) null,/*姓名字段*/
sex char(2) null,/*性别字段*/
age int null,/*年龄字段*/
dept varchar(20) null/*系别字段*/
)

//查看所有的系统用户；
select * from sysusers

查看所有的数据库的名字,只有在master数据库中存在；
use master
go
select * from sysdatabases

 

--带有@@的是全局变量；
select @@servername,@@version

--带有一个@的是局部变量，cast(@icount as char)是将一种数据类型转化为另外一种数据类型；
declare @icount int,@strname varchar(14)
select @icount=12
set @strname='the row count'
print @strname+cast(@icount as char)
go

--space(8)返回的是带有重复空格的字符串；
select 'Olymplic'+space(8)+'in beijing'
go

select getdate();

 

 

 

use ibmsample
go

select name
from sysobjects
where type ='S'
order by name

select distinct type
from sysobjects

select * from syscolumns
where id in
(select id from sysobjects where type = 'U' and name='chang' )

select so.name '表名',sc.name '列名'
from sysobjects so,syscolumns sc
where so.id= sc.id and so.type = 'U'
order by so.name

select name from systypes

select * from sysusers

select text from syscomments
where id = '485576768'

select * from sysfiles

use master
go
select * from sysdatabases

//恢复数据库
use master
restore database company
from backup_company

//创建一个数据库设置它的主文件名称是company_data，物理文件名:Company.mdf,初始大小是10mb,最大尺寸是无限大，增长速度为10%；数据库的日志文件逻辑名称为company_log，物理文件名为company.ldf,初始大小为1mb，最大尺寸为5mb,增长速度为1mb;
create database company
on primary
(
name=company_data,
filename='C:/Program Files/Microsoft SQL Server/MSSQL/Data/company.mdf',
size=10mb,
filegrowth=10%
)
log on
(
name=company_log,
filename='C:/Program Files/Microsoft SQL Server/MSSQL/Data/company.ldf',
size=1mb,
maxsize=5mb,
filegrowth=1mb
)

 

//修改数据库；添加一个包含两个数据文件的文件组个事务日志文件到company数据库中；
alter database company
add filegroup companygroup
go

alter database company
add file
(
name=cmp5,
filename='C:/Program Files/Microsoft SQL Server/MSSQL/Data/companyz5.ndf',
size=2mb,
maxsize=20mb,
filegrowth=1mb
),
(
name=cmp6,
filename='C:/Program Files/Microsoft SQL Server/MSSQL/Data/companyz6.ndf',
size=2mb,
maxsize=20mb,
filegrowth=1mb
)
to filegroup companygroup
go

alter database company
add log file
(
name=company_log4,filename='C:/Program Files/Microsoft SQL Server/MSSQL/Data/companyn.ldf',
size=1mb,
maxsize=10mb,
filegrowth=2mb
)
go

//删除数据库；
drop database company；
其结果为：
--------------------------------------------------------------------------------------
正在删除数据库文件 'C:/Program Files/Microsoft SQL Server/MSSQL/Data/companyn.ldf'。
正在删除数据库文件 'C:/Program Files/Microsoft SQL Server/MSSQL/Data/companyz6.ndf'。
正在删除数据库文件 'C:/Program Files/Microsoft SQL Server/MSSQL/Data/companyz5.ndf'。
正在删除数据库文件 'C:/Program Files/Microsoft SQL Server/MSSQL/Data/company.ldf'。
正在删除数据库文件 'C:/Program Files/Microsoft SQL Server/MSSQL/Data/company.mdf'。
---------------------------------------------------------------------------------------

//在磁盘上创建了一个备份设备
use master
go
exec sp_addumpdevice 'disk','backup——company','C:/Program Files/Microsoft SQL Server/MSSQL/Data/backup/bk_noto.bak'
go

 

 

 

 

 

create table gridet
(
stuid char(10) not null ,
corseid char(8)  not null ,
score float  null,
constraint pk_stucorseid primary key(stuid,corseid),
constraint FK_stuid Foreign key(stuid) references student(stuid),
constraint FK_corseid Foreign key(corseid) references course(corseid)
)
select * from gridet
drop table gridet
create table course
(
 corseid char(8) not null Primary key,
 corname varchar(50) not null ,
 corterm INT null,
 score int  null,
 coresecls varchar(10) null,
 cornote varchar(300) null
)

//创建一个存储过程；
多数存储过程的功能比本例复杂多了，但这里主要说明存储过程的一些基本要点。如前面所述，不同DBMS定义存储过程的语法是不同的。例如，有些DBMS使用begin . . . end或其他关键字指明存储过程定义的开始和结束。在有些DBMS中，下面的SQL语句可创建一个存储过程：

create procedure SHOW_SUPPLIERS
as
select SUPPLIERS.SUP_NAME, COFFEES.COF_NAME
from SUPPLIERS, COFFEES
where SUPPLIERS.SUP_ID = COFFEES.SUP_ID
order by SUP_NAME

 

//创建一个存储过程：

a>删除已有的存储过程mm;
drop proc mm；
b>创建一个查询的存储过程mm,
create procedure mm
(
 @user_id int,
 @user_name varchar(20),
 @user_sex varchar(5)
)
as select user_id,user_name,user_sex from usertable where user_id>@user_id
c>调用这个刚创建的存储过程：
{call mm('8','','')}；

//创建一个插入的存储过程insertmm;
create procedure insertmm
(
 @user_id int,
 @user_name varchar(20),
 @user_sex varchar(5),
 @class_id int
)
as insert into usertable values(@user_id,@user_name,@user_sex,@class_id);
//调用这个插入的存储过程
{call insertmm('13','毛素琴','女','1003')}
//创建一个更新的存储过程updatemm；
create procedure updatemm
(
 @user_id int,
 @user_name varchar(20),
 @user_sex varchar(5),
 @class_id int
)
as update usertable set user_name=@user_name,user_sex=@user_sex,class_id=@class_id  where user_id=@user_id
//调用这个更新的存储过程;
{call updatemm('14','糖果','女','1001')}
//创建一个删除的存储过程deletemm；
create procedure deletemm
(
 @user_id int,
 @user_name varchar(20),
 @user_sex varchar(5),
 @class_id int
)
as delete usertable where user_id=@user_id or user_name=@user_name or user_sex=@user_sex or class_id=@class_id
//调用这个删除的存储过程;
{call deletemm('14','','','')}

//导出mysql.sql文件命令;xatuwg代表数据库的名字;
C:/Program Files/MySQL/MySQL Server 5.0/bin>mysqldump -u root -p xatuwg>links.sql