SQL语言
- 数据库定义语言(DDL):CREATE, DROP, ALTER 等语句。
- 数据库操作语言(DML):INSERT, UPDATE, DELETE 等语句。
- 数据库查询语言(DQL):SELECT等语句。
- 数据库控制语言(DCL):GRANT, REVOKE, COMMIT, ROLLBACK等语句。
SQL数据类型
字符型:
char 定长 最大8000字符(非unicode编码) 例:char(10) ‘小明’ 前四个字符放‘小明’,后添6个空格补全
varchar 变长 最大8000字符(非unicode编码) 例:varchar(10) ‘小明’ sql server分配四个字符
nchar 定长 最大8000字符(unicode编码) 例:nchar(10) ‘小明’ 前四个字符放‘小明’,后添6个空格补全
nvarchar 变长 最大8000字符(unicode编码) 例:nvarchar(10) ‘小明’ sql server分配四个字符
ntext 变长(unicode) 数据 最大长度为 2 的 30 次方 - 1 个字符
text 变长(非unicode) 数据 最大长度为 2 的 31 次方 - 1 个字符
数字型:
bit 范围 0 到 1
int 范围 负的 2 的 31 次方 到 正的 2 的 31 次方 - 1
bigint 范围 负的2 的63次方到正的2的63次方 - 1
float 小数,不推荐使用
numeric 小数,推荐使用
日期类型:
datetime
timestamp
SQL语法
create database 数据库名
create table 表名 (字段名1 数据类型,字段名2 数据类型,....)
drop table 表名
insert into 表名 values(字段1,字段2,字段3,...)
insert into 表名 (字段名1,字段名2,...) values (字段1,字段2,...)
update 表名 set 字段名1=‘新值’ ,字段名2=‘新值’ ... where 字段=‘值’
delete from 表名; 删除全部数据
delete from 表名 where 字段名1=‘值’ and 字段名2=‘值’ .....; 删除指定数据
select * from 表名 where 条件
select 字段名1,字段名2 .... from 表名 where 条件
select distinct 字段名1,字段名2 .... from 表名 where 条件; 取消重复行
backup datebase 数据库名 to disk = ‘备份文件路径’
restore database 数据库名 from disk = ‘备份文件路径’
create database bbb create table hero ( heroID int, heroName nvarchar(50), sex char(2)) insert into hero values(1,'宋江','男') insert into hero values(2,'吴用','男') select * from hero insert into hero values(3,'卢俊义','男') insert into hero values(4,'公孙胜','男') select * from hero update hero set heroName='刘备' where heroID=4 insert into hero (heroID,heroName) values(5,'高俅') alter table hero add tel varchar(11) alter table hero alter column tel nvarchar(11) exec sp_rename 'hero.tel','phone','column' alter table hero drop column tel create table test1 (test1ID int primary key identity(1,2), --自增长,从1开始,每次增加2 test1Name varchar(30) unique, --唯一 test1Passwd varchar(10) not null ) select * from test1 alter table test1 add tel varchar(11) alter table test1 alter column tel nvarchar(11) exec sp_rename 'test1.tel','phone','column' --修改字段名 alter table test1 drop column phone exec sp_rename 'dbo.test1','test' --修改表名 --复合主键/default/check create table test2 ( test1ID int foreign key references test1(test1ID), test1Name varchar(30) , test1Passwd varchar(10) not null, sal int check (sal >=1000 and sal<=2000), testTime datetime default getdate(), --没有给定值时使用默认值 testsex char(2) check(testsex in ('男','女')) default '男', primary key (test1ID,test1Name) )
create table dept( deptno int primary key, dname nvarchar(30), loc nvarchar(30)) create table emp( empno int primary key, ename nvarchar(30), job nvarchar(30), mgr int, hiredate datetime, sal numeric(10,2), comm numeric(10,2), deptno int foreign key references dept(deptno)) insert into dept values(1,'ACCOUNTING','NEW YORK') insert into dept values(2,'RESEARCH','DALLAS') insert into dept values(3,'SALES','CHICAGO') insert into dept values(4,'OPERATTIONS','BOSTON') SELECT * FROM DEPT update dept set deptno=9 where deptno=3 delete from emp INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,2) INSERT INTO emp VALUES(7499,'ALLEN','SALESMAN',7698,'1981-1-17',1600,300,3) INSERT INTO emp VALUES(7521,'WARD','SALESMAN',7698,'1980-2-17',1250,500,3) INSERT INTO emp VALUES(7566,'JONES','MANAGER',7839,'1980-1-7',2975,NULL,2) INSERT INTO emp VALUES(7654,'MARTIN','SALESMAN',7698,'1982-5-17',1250,1400,3) INSERT INTO emp VALUES(7698,'BLAKE','MANAGER',7839,'1981-2-6',2850,NULL,3) INSERT INTO emp VALUES(7782,'CLARK','MANAGER',7839,'1980-7-5',2450,NULL,1) INSERT INTO emp VALUES(7788,'SCOTT','ANALYST',7566,'1980-12-17',3000,NULL,2) INSERT INTO emp VALUES(7839,'KING','PRESIDENT',NULL,'1980-12-17',5000,NULL,1) INSERT INTO emp VALUES(7844,'TURNER','SALESMAN',7698,'1980-12-17',1500,0,3) INSERT INTO emp VALUES(7876,'ADAMS','CLERK',7788,'1980-12-17',1100,NULL,2) INSERT INTO emp VALUES(7900,'JAMES','CLERK',7698,'1980-12-17',950,NULL,3) INSERT INTO emp VALUES(7902,'FORD','ANALYST',7566,'1980-12-17',3000,NULL,2) INSERT INTO emp VALUES(7934,'MILIER','CLERK',7782,'1980-12-17',1300,NULL,1) INSERT INTO emp VALUES(9999,'YZG','CLERK',7782,'1980-12-17',2456.34,55.66,1) SELECT * FROM emp SELECT DISTINCT deptno FROM emp SELECT ename,sal+ISNULL(comm,0) 年工资 from emp SELECT ename,sal+comm 年工资 from emp select * from emp where sal>3000 select * from emp where sal between 2000 and 5000 select * from emp where hiredate<'1982-1-1' select * from emp where ename like 'S%' select * from emp where ename like '__O%' select * from emp where empno in (7788,7499,7521) select * from emp where mgr is null select * from emp order by sal select * from emp order by sal desc select * from emp order by deptno,sal desc select avg(sal), min(sal),deptno,job from emp group by deptno,job select count(*) from emp select AVG(sal),deptno from emp group by deptno having AVG(sal)<2000 select * from emp,dept where dept.dname='sales' and emp.deptno = dept.deptno select e1.ename,e1.mgr,e2.ename, e2.empno from emp e1,emp e2 where e2.empno=e1.mgr select e1.ename,e1.mgr,e2.ename,e2.empno from emp e1 left join emp e2 on e2.empno=e1.mgr select top 5 * from emp order by empno desc select * from emp ,(select AVG(sal) myavg,deptno from emp group by deptno) tmp where emp.deptno=tmp.deptno and emp.sal>tmp.myavg select top 6 * from emp where empno not in (select top 4 empno from emp order by empno) order by empno