SQLServer笔记

 

数据库数据类型：

为了设计结构良好的数据库，需要遵守一些专门的规则，称为数据库的设计范式 。

第一范式（1NF）的目标：确保每列的原子性。

第二范式（2NF）的目标：确保表中的每列，都和主键相关

第三范式（3NF）的目标：确保每列都和主键列直接相关，而不是间接相关

 

删除数据库中的表《两种方法》

Truncate Table <表名>

TRUNCATE  TABLE  Students=DELETE FROM Students

Truncate与Delete的共性

              都可用作表数据删除

Truncate与Delete的区别

              Tuncate称之为截断，效率更高，不记录在日志文件中，也将不可恢复。

限制固定行数：

SELECT TOP 5 SName, SAddress

FROM Students WHERE SSex = 0

返回百分之多少行：（top 28 percent）

SELECT TOP 20 PERCENT SName, SAddress

FROM Students WHERE SSex = 0

 

--把密码列中的O换成0，并把i换成1

《方法一》

Update Card Set PassWord = Replace(PassWord ,'O','0')

Update Card Set PassWord = Replace(PassWord ,'i','1')

《方法二》

Update        Card

Set PassWord = Replace(Replace(PassWord ,'O','0'),'i','1')

--聚合函数《sum（）》

SELECT SUM(ytd_sales) FROM titles WHERE type = 'business'

--《agv（）》

SELECT AVG(SCore) AS 平均成绩

From Score WHERE Score >=60

--max,min

SELECT AVG(SCore) AS 平均成绩, MAX (Score) AS 最高分,

MIN (Score) AS 最低分 From Score WHERE Score >=60

--count(*)计数器

SELECT COUNT (*)  AS 及格人数 From Score

WHERE Score>=60

--关键字联合查询

SELECT * FROM shopping.wares w

WHERE w.`WareID` like '%1%' and Company like '%公司%' and Addr like '%%';

Like 查询【值必须加引号】

select * from goods where BUyNumber like '1%' and userID like '%3' and createtime like '%2010-05-17%' and goodsID like '%%';

 

//查看创建表的脚本代码

Show create table 表名

 

Mysql中时间类型TIMESTAMP

// 使用事务机制操作数据库

    // 注意：把数据库中表的引擎设置为InnoDB ，MySQL中只有InnoDB引擎支持事务

在mysql中如果给类型为Timestamp列中插入null，或者在插入新行时没有给timestamp列赋值，mysql将自动将时间设置为当前的日期和时间。

MYSQl中AUTO_INCREMENT（auto_increment）自动增长/加，从1 开始

Eg。 userID int auto_increment not null primary key,

alter table test add price float(20.3);--保留两位小数

update test set price=2099.12512; 表中为：2099.13

查询前N条记录【limit N】（类似sqlServer top N）

/*****MY SQL查询限制条数*****/

SELECT * FROM users u limit 2;                返回前两条记录

SELECT * FROM users u order by id limit 1,1;  返回第2条记录（两个参数：偏移/限制行数）

分页：如果每页显示10条结果，第一页用limit 0,10 ；第二页用 limit 10, 10

/*****SQL SERVER查询限制条数******/

select top 3 * from users where LoginPwd=1 and sex like '%男%';

常用数据库URL的形式：

l  SQL Server2005 :

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

l  String uri = "jdbc:sqlserver://127.0.0.1:1433;DatabaseName=employeeAdmin";

l  Oracle 9I和10g：

Jdbc:oracle:thin:@localhost:1521:ORCL

l  MySQL：(DreiverClass:com.mysql.jdbc.Driver)

Jdbc:mysql://localhost:3306/databasename

l  JDBC—ODBC桥

Sun.jdbc.odbc.JdbcOdbcDriver

JDBC URL是:jdbc:odbc:datasource-name

 

 

JNDI连接池配置

Context.xml

<?xml version="1.0" encoding="UTF-8"?>

<Context reloadable="true">

<Resource name="bookstore" auth="Container"

type="javax.sql.DataSource"

maxActive="100" maxIdle="30" maxWait="10000"

username="root" password="root"

driverClassName="com.mysql.jdbc.Driver"

url="jdbc:mysql://localhost:3306/bookstore?autoReconnect=true">

</Resource>

</Context>

 

Jsp or servlet get Connection

Context ctx=new InitialContext();

DataSource ds = (DataSource) ctx.lookup("java:comp/env/bookstore");

conn = ds.getConnection();

 

 

 

 

使用事务处理回滚

Connection conn;

conn.setAutoCommit(false);//自动提交                conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);

sql语句·······

conn.rellback();//conn.commit();

//事务隔离级别

//以下 Connection 常量之一：Connection.TRANSACTION_READ_UNCOMMITTED、 指示可以发生脏读 (dirty read)、不可重复读和虚读 (phantom read) 的常量。Connection.TRANSACTION_READ_COMMITTED/ 指示不可以发生脏读的常量；不可重复读和虚读可以发生。

//Connection.TRANSACTION_REPEATABLE_READ   指示不可以发生脏读和不可重复读的常量；虚读可以发生。

或 Connection.TRANSACTION_SERIALIZABLE指示不可以发生脏读、不可重复读和虚读的常量。。（注意，不能使用 Connection.TRANSACTION_NONE，因为它指定了不受支持的事务。）

创建索引

l  何时使用索引

如果对表的操作以查询为主，即经常从表中查询数据，很少有插入、修改和删除操作，这种情况需要使用索引来改进查询性能。

如果对表的操作以更新为主，即经常对表进行插入、修改和删除操作，很少有查询操作，这种情况不适合于使用索引。

索引可以由系统自动创建，也可以由用户手工创建。

创建主键约束时，系统会自动创建唯一聚集索引（与物理存贮一一对应如书目录）；创建唯一约束时，系统会自动创建唯一非聚集索引（非与物理存贮一一对应，如书附录单词表）。

 

l  使用CREATE INDEX语句创建聚集索引

--创建聚集索引，索引列为cno

 

create clustered index IX_Course_cno【索引名】

on Course【表名】

(cno)【列名】

 

l  使用CREATE INDEX语句创建唯一聚集索引【不允许索引列出现重复值的聚集索引】

如果在表中设置了主键系统将自动创建唯一聚集索引

--创建唯一聚集索引，索引列为DepartmentID

 

create unique clustered index IX_Department_DepartmentID

on Department

(DepartmentID)

l  使用CREATE INDEX语句创建非聚集索引

 

--创建非聚集索引，索引列为tname

 

create nonclustered index IX_Teacher_tname

on Teacher

(tname)

 

l  使用CREATE INDEX语句创建唯一非聚集索引

 

--创建唯一非聚集索引，索引列为cname

 

create unique nonclustered index IX_Course_cname

on Course

(cname)

l  查看索引，有两种方式：

Ø  使用管理平台查看索引。

Ø  使用系统存储过程（sp_helpindex 表名）查看索引。

 

l  修改索引：

注意：如果禁用了聚集索引，将导致表中的数据不可用，也就是不能对该表进行查询、插入、修改和删除操作。

Ø  使用系统存储过程sp_rename重命名索引

--重命名索引

sp_rename 'Student.uqSname', 'IX_Student_sname', 'index'

Ø  使用ALTER INDEX语句禁用索引

--禁用索引

alter index IX_Student_sname

on Student

disable

Ø  使用ALTER INDEX语句禁用索引

--禁用索引

alter index IX_Student_sname

on Student

disable

Ø  使用DROP INDEX语句删除索引。

drop index Teacher.IX_Teacher_tname

Insert出现“只允许出现标量表达式”的错误

Insert into a (a1,a2,a3) values (1,select num from b where id=1,3)  这样select语句出现在赋值语句中是不允许的。

可以这样：

insert into a (a1,a2,a3) select 1,num,3 from b where id=1 即可解决