sql server

sql server查询语句：

--SQL语句
--创建数据库
--create database liangshangTest
use liangshanTest--使用某个数据库
--创建表
create table hero
(
id int,--id号
name varchar(10),--名字
sex varchar(2),--性别
salary int    --薪水
)

--使用sql语句添加数据
insert into hero values(1,'宋江','男',3000)--注意字符要用单引号
insert into hero values(2,'林冲','男',1000)
insert into hero values(3,'吴用','男',2400)

--1.查询所有数据
select * from hero

--2.条件查询
select *from hero where salary>=3000

--更新:将薪资少于2000的工资提高10%
--格式：update  表名  set  字段名=。。。 where 条件
update hero set salary=salary*1.1 where salary<2000
--查询
select * from hero


--删除一张表（把表的数据和结构一起删除）
--drop table hero

--删除：删除薪资大于2500的
--格式：delete from 表名 where 条件
delete from hero where salary>2500
select * from hero

表--支持的数据类型

1、字符类型：

char　：　字长  最大8000字符（非unicode编码）　　　　char(10)    限定长度，若输入长度不够，用空格补全

varchar　：　变长   最大8000字符（非unicode编码）　　　　varchar(10)  10为最大长度，若填入的不到最大长度，则按照填入的长度分配空间

既然varchar这样节省空间，为什么还要用char呢？

　　虽然varchar节省空间，但是在这种情况下应该使用char，在长度确定的情况下（比如有个数据库用于存放学生的学号，存放的是固定长度的学号，）这时候使用char在查询起来就很方便了。

使用char类型，在查询时，根据字长匹配比较；而varchar则需要一个字节一个字节地去匹配，相比于char更耗时。

 

当字符超过8000个时，char类型也不能满足我们的时候，使用text或者ntext

ntext　：　可变长度Unicode数据的最大长度为2的30次方-（1073,741,823）个字符

text　:  可变长度，非Unicode编码，最大长度为2的31次方-（2,147,483,647）个字符

ntext与text区别：ntext是多字节格式存储的unicode，可以存储各种文字。text是字节格式存储的英文，也可以存储中文但是有时候会显示成乱码

nchar ：　　定长，最大字符4000，（unicode编码）　　nchar(10)不足用空格补全

nvarchar :  　　可变长，最大字符4000（unicode编码） 　　（unicode编码）按填入的字符大小分配空间

注意：

1）、一般带有汉字段的使用nvarchar，全英文或者符号使用varchar，因为，nvarchar为unicode字符集，该类型无论是当字母还是当汉字都占两个字节，而varchar非unicode编码，字母占一个字节，汉字占两个字节，nvarchar处理汉字或者其他字符集的速度都比varchar字段快

2）、如果有一些特殊字符在nvarchar中没有的，如日文的某些名片，那当然只能选合适的varchar了，而且这些特有的字符转换到nvarchar会失效

 

2、数字型

bit :  范围0-1

bigint ： -2的63次方—2的63次方 ， 存储大小为8个字节

int  :   -2 的31次方—2 的31次方，存储大小为4个字节

smallint  ：-2的15次方—2的15次方，存储大小为2个字节

tinyint：0-225的整型数据。存储大小为1字节

 

3、浮点

float：不建议使用，因为小数后面可能会出错，可指定保留的位数　　float(10,2)表示10位，小数点后保留两位

4、图片和视频

image保存图片，但是很少用，一般用路径保存图片，在软件公司往往使用图片服务器和图床技术

binary字段可以存放视频，但是我们往往将视频文件放在文件服务器上，sql server中只保存文件路径，存放效率高

4、时期类型

datetime（表示日期）

timestamp（时间戳）

时间戳（timestamp），一个能表示一份数据在某个特定时间之前已经存在的、 完整的、 可验证的数据,通常是一个字符序列，唯一地标识某一刻的时间。

--创建一个职员表
create table clerk
(
    clerkId int primary key,--主键不能重复出现，也不能为NULL
    clerkName nvarchar ,
    clerkAge int
)
--添加数据
insert into clerk values(1,'小米',19)
insert into clerk values(2,'huh',19)
insert into clerk(clerkId,clerkName) values(3,'敌人')

--查询数据
select * from clerk

 SQL server复杂查询语句

--创建一个数据库


--创建职员表，和部门表

--部门表

create table department
(
depId int primary key,--主键
dName nvarchar(30)
)

--职员表
create table emp
(
empId int primary key,
eName varchar(50),
job varchar(50),
hireDate datetime,
sal numeric,
depId int foreign key references department(depId)--根据需要做成外键
--外键注意事项，1.外键只能指向主键    2.主键和外键的数据类型相同
)

insert into department(depId,dName) values(10,'财务部')
insert into department(depId,dName) values(20,'技术部')
insert into department(depId,dName) values(30,'销售部')

insert into emp(empId,eName,job,hireDate,sal,depId)
 values (101,'nick','经理','1992-2-2',10000,10)
insert into emp(empId,eName,job,hireDate,sal,depId)
 values (102,'马克','职员','1998-2-2',5000,10)
insert into emp(empId,eName,job,hireDate,sal,depId)
 values (103,'索亚','职员','2000-2-4',1000,10)
insert into emp(empId,eName,job,hireDate,sal,depId)
 values (104,'灵儿','职员','2004-2-2',1200,10)
insert into emp(empId,eName,job,hireDate,sal,depId)
 values (105,'小猪','职员','2008-2-2',10000,10)
insert into emp(empId,eName,job,hireDate,sal,depId)
 values (106,'佩奇','经理','2010-2-2',5000,20)
insert into emp(empId,eName,job,hireDate,sal,depId)
 values (107,'海伦','职员','1992-2-2',1600,20)
insert into emp(empId,eName,job,hireDate,sal,depId)
 values (108,'洛奇','职员','2003-2-2',10000,20)
insert into emp(empId,eName,job,hireDate,sal,depId)
 values (109,'乔治','经理','2012-2-2',40000,30)
insert into emp(empId,eName,job,hireDate,sal,depId)
 values (110,'乔洋','职员','2000-2-2',8000,30)
insert into emp(empId,eName,job,hireDate,sal,depId)
 values (111,'成成','职员','2013-2-2',8000,30)
insert into emp(empId,eName,job,hireDate,sal,depId)
 values (112,'华夏','职员','2012-2-2',7000,30)

select * from department
select * from emp

--查询佩奇的资料信息
select empId,eName,job,hireDate,sal,depId from emp e where ename='佩奇'

--查询指定列的信息
select eName,job,depId from emp 

--取消重复行（distinct只能消除完全一样的行，并保留一行）
--select distinct 字段 from 表名 where
select distinct depId from emp

--显示没有员工的年工资,其中别名，年工资可以有单引号，双引号，也可以不用
select eName ,sal*13 '年工资' from emp

--查找2000年后入职的员工
select * from emp where hireDate>'2000-1-1'

--显示工资在8000-10000的员工工资
--有两种写法：
--写法一：
select eName,sal from emp where sal>=8000 and sal<=10000
--写法二
select eName,sal from emp where sal between 8000 and 10000

--显示首字符为乔的员工，使用like，%
select eName ,sal from emp where ename like '乔%'

--显示第二个字符为奇的员工，_代表一个字符
select eName ,sal from emp where ename like '_奇%'

--显示某个Id号的员工的信息
select * from emp where empId in(101,110)

--查找·同时满足多条信息的员工
select * from emp where (sal>=10000 or job ='经理') and ename like '乔'

--按照工资的从高到低显示员工工资
--order by 默认是升序排列 asc
--order by desc 降序
select * from emp order by hiredate desc

--按照部门升序二雇员工资降序排列
--order by可以根据不同的字段排序
select * from emp order by depId,sal desc

--显示所有员工的最低工资
select min(sal) from emp 
--显示所有员工的最高工资，并显示员工id和姓名,max
select empId,eName from emp where sal=(select max(sal) from emp)

--显示所有员工的平均工资和工资总和,avg ,sum
select AVG(sal) "平均工资",SUM(sal) 总工资 from emp

--计算有多少员工,count
select COUNT(*)  员工总数 from emp

--显示每个部门,不同职位的平均工资和最高工资,group
select avg(sal)  平均工资,depId from emp group by depId,job

--显示平均工资低于10000的部门号，和平均工资
--having一般和group结合使用
--一般group by--having--order by
select AVG(sal) 平均工资,depId from emp group by depId having AVG(sal)>10000 
order by AVG(sal) asc

--显示员工信息和部门号
--如果两张表存在相同的字段，则需要带表名（别名）
--使用别名就不能使用原表名
select e.depId,dName,ename,empId from emp e,department d where e.depId=d.depId

--显示部门为10的员工
select e.depId,dName,ename,empId from emp e,department d where e.depId=d.depId and e.depId=10

 

 --indentity(1,1)自增长，表示testId字段自增，从1开始，每次自加1
  --先创建一个表，create table test 
  (
  testId int primary key identity(1,1),
  testName varchar(30),
  testPass varchar(30)
 )
 
  insert into test (testName,testPass) values('Archer',123456);
 insert into test(testName,testpass) select testName,testPass from test;
 select * from test 

 约束

1. not null：非空

2.unique：唯一，该值不可以重复，但是可以允许一个为null

3.primary key：主键，一张表只能有一个主键，且主键不能为空也不能重复

4.foreign key：外键，用于主表跟从表的关系，外键约束要定义在从表上，主表必须是具有主键或者unique约束的，或是为null

5.check：用于强制性数据必须满足的条件，比如数据范围的限定

eg：

category  nvarchar(3) check (category in ('食物','日用品'))

6.default：在列中插入默认值

 

备份数据库

backup database 你要备份的数据库名 to disk='路径'
backup database aaa to disk ='c:/sp.bak'

删除数据库

drop database 数据库名
drop database  aaa

恢复数据库

restore database 你的数据库名 from disk='备份文件路径'
restore database aaa from disk='c:/sp.bat'

crud 增（create），查（retrieve），改（update），查（delete）

使用jdbc-odbc桥连方式操作数据库

 由于微软的数据库不是用Java语言来编写的，但是我们需要用java语言连接微软的数据库，这样就要编写一个桥连接，使java语言编写的代码也可以操作数据库。

JDBC, 全称为Java DataBase Connectivity standard, 它是一个面向对象的应用程序接口（API）, 通过它可访问各类关系数据库。JDBC也是java核心类库的一部分。

操作步骤（需要进行异常处理）

1.加载驱动

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

通过调用静态方法forName()加载sun.jdbc.odbc包中JdbcOdbcDriver类来建立JDBC-ODBC桥接器。

2.连接

Connection connection=null;
connection = DriverManager.getConnection("jdbc:orale:thin:@Ip的地址及端口号和数据库的实例名"，"用户名"," 密码")

3.对数据库进行操作

获得一个statements对象：

statements stat=Connection.createstatements()

通过statements对象执行SQL语句：

stat.executeQuery(String sql)返回查询的结果集。

stat.executeUpdate(String sql)返回值为int型，表示影响记录的条数。