软件测试培训第30天
所学内容的整体整理（中上）:SQL数据库的表格创建与数据的插入、修改以及删除
SQL
数据库的价值目标:
数据库的技术(不只是界面 还要知道数据库逻辑 1.要对数据库的设计理解 2.还有数据库对象的关系3.数据库的常见命令)
常见数据库：MySQL Access(单机) MS-SQL(交互好) Oracle Sybase DB2
MySQL 小巧 效率高 免费
后三种Oracle Sybase DB2是大型收费，数据安全和备份好
数据库作用：组织、存储、处理
数据库管理系统 SQLServer，Oracle，MySQL
表 基本组成单元 有记录和 字段组成
表中每一条数据
存储数据
数据分类
字段位置变化 不影响程序
索引 建立在字段上 可以对字段进行排序 同一张表索引
更新索引字段值的时候 新增会打乱索引的顺序时
视图 建立在数据库上 封装查询命令 存储数据 方便查询调用 提示安全性 显示结果固定
如果在执行事务时没有执行完就实行事务回滚
检查ＳＱＬ服务是否启动
图形界面建库 建表
企业管理器
命令行方式
查询分析
主键 用一标记该表中的记录 不能重复 不能为空
外键 通过外键与其他表中的数据进行关联 只能是以存在主键值
当前表的数据要被其他表使用时 要有主键
外键 有主键存在时就要设置对应的外键
约束：
1主键约束
1 主键约束 primary key
--特点:约束的字段数据，不能为空、不能重复。
如果插入或者更新的数据为空或者重复将不允许进行操作。
--语法：
1)不命名的主键约束
create table student
(
No int primary key,
Name char(10),
Sex char(2),
ruxu datetime
);
2)命名的主键约束
create table student
(
No int,
Name char(10),
Sex char(2),
ruxu datetime,
constraint pk_001 primary key(No)
);
3)删除主键约束
--语法：alter table 表名 drop constraint 约束名;
alter table student drop constraint pk_001;
4)改主键约束
--语法：alter table 表名 alter column 字段名 类型 not null;
--alter table 表名 add constraint 约束名 primary key (字段名);
alter table student alter column No int not null;
alter table student add constraint PK_002 primary key(No);
2 非空约束 not null
1)增加非空约束
create table student1
(
No int not null,
Name char(10),
Sex char(2),
ruxu datetime
);
2)改非空约束
alter table student1 alter column Name char(10) not null;
3 外键约束 FOREIGN KEY
--新增外键关系
1)非命名的外键约束
create table grade
(
no int foreign key references student(No),-- 字段名 字段类型 foreign key references 主表（主键字段）
grade float
);
2)命名的外键约束
create table grade
(
No int,
grade float,
constraint fk_001 foreign key (no) references student(no)
);
3)删除外键约束
alter table grade drop constraint fk_002;
4)改外键约束
alter table grade add constraint FK_002 foreign key(no) references student(no) on delete cascade; --当主表进行数据删除时，从表一起删除
alter table grade add constraint FK_002 foreign key(no) references student(no) on update cascade; --当主表进行数据更新时，从表一起更新
alter table grade add constraint FK_002 foreign key(no) references student(no) on delete no action; --当主表进行数据删除时，违反了外键约束，拒绝进行删除操作
alter table grade add constraint FK_002 foreign key(no) references student(no) on update no action; --当主表进行数据更新时，违反了外键约束，拒绝进行更新操作
4 默认约束
--default 当 一个字段不填写内为空时，默认插入一条数据
1)增加default 约束
create table sex
(
id int,
sex char(2) default '男'
);
2)改 default 约束
5 检查约束 check
--只有满足检查约束的数据才能添加到表中，不满足的加不进来
1)不命名的检查约束
create table balance
(
id int,
money float check (money >= 0)
);
2)命名的检查约束
create table balance
(
id int,
money float,
constraint CK_001 check (money >= 0)
);
3)删除检查约束
alter table balance drop constraint ck_001;
4)改检查约束
alter table balance add constraint CK_002 check (money >= 0);
6 唯一约束 unique
--对于字段输入的内容必须是唯一的，不能重复，但可以为空，多个字段都可以输入唯一约束
1)不命名唯一约束
create table tiger
(
tig_id int,
tig_name char(10) unique
);
2)命名唯一约束
create table tiger
(
tig_id int,
tig_name char(10),
constraint UN_001 unique(tig_name)
);
3)删除唯一约束
alter table tiger drop constraint UN_001;
4)改唯一约束
alter table tiger add constraint UN_002 unique(tig_name);
SQL语句
创建数据库.
语法：create database 数据库名字;
数据库名字不能使用数字作为开头。可以使用字母或者_作为开头
create database _51testing;
开辟两块空间，一个是保存日志的，一个是保存表的
删库 DROP DATABASE 库名；
单行注释 ——
多行注释 /* */
切换数据库
语法：use 数据库名；
use _51testing;
转换当前使用数据库
备份数据库
BACKUP DATABASE 库名 TO DISK=’文件名’；
如BACKUP DATABASE xuanke TO DISK=’d:\test.bak’;
恢复数据库
RESTORE DATABASE 库名 FROM DISK=’文件名’;
如RESTORE DATABASE xuanke FROM DISK=’D:\test.bak’;
表、字段、类型
建表
语法：create table 表名字 ( 字段名 字段类型 字段约束 ， 字段名 字段类型 字段约束 );
create table tmp
(
NO int primarykey,
NAME char(10)
);
字段约束可以没有
删除表
语法：drop table 表名
注意：外键约束
drop table grade
建立外键
Sid INT FOREIGN KEY (sid) REFERENCES student (sid)
CREATE DATABASE xuanke1;
USE xuanke1;
--DROP database xuanke1;
CREATE TABLE student1
(sid INT PRImary KEY,
sname CHAR(20) NOT NULL,
sex CHAR(2) NOT NULL CHECK(sex IN ('男','女')),
card_id CHAR(20) NOT NULL unique CHECK(len(card_id) = 18),
calss ChAR(20) NOT NULL,
major CHAR(20) NOT NULL,
birth_year INT CHECK(birth_year BETWEEN 1900 And 3000) NOT NULL,
enrollment DATETIME NOT NULL,
tel CHAR(20)
)
CREATE TABLE course1
(
cid INT PRIMARY KEY,
cname CHAR NOT NULL,
tname CHAR(20) NOT NULL,
ctime DATETIME NOT NULL,
cadress CHAR(20) NOT NULL,
pre_course_id INT,
)
CREATE TABLE student_course1
(
sid INT FOREIGN KEY (sid) REFERENCES student1 (sid),
cid INT FOREIGN KEY (cid) REFERENCES course1 (cid) PRIMARY KEY(sid,cid),
grade INT
)
插入一条记录
在表中增加数据（insert into）
语法：
1对表中所有的字段添加数据：insert into 表名 values (第一个字段的值，第二个字段的值，第三个字段的值.....);
注意1：values 值得个数一定要和表中字段的个数相等，一一对应。
注意2：values 值得数据类型，必须要和表中字段的数据类型相匹配。int fload 值是数字，如果char 值得字符要用'值'。
注意： 英文单引号 逗号 ，日期 要加单引号
insert into student values (5,'郑佳祺','男','003','2011-08-23','北京','1979-01-01');
insert into student values (6,'','男','003','2011-08-23','北京','1979-01-01');
insert into student values (7,'庞鹏珏','男',3,'2011-08-23','北京','1979-01-01');
2对表中指定字段添加数据：insert into 表名(字段1，字段2，字段3) values (值1，值2，值3);
insert into student(stuid,stname,sex,family) values (8,'齐倩','女','天津');
insert into student(stuid,stname,sex,family,birthday) values (9,'例子1','女','天津','');
insert into student(stuid,stname,sex,family,class) values (10,'例子2','女','天津','');
insert into student(stuid,stname,sex,family,class) values (11,'例子3','女','天津',NULL);
insert into student(stuid,stname,sex,family,class) values (12,'例子4','女','天津','NULL');
删除数据
语法：1.delete from 表名
注意: 删除表中数据时要考虑约束。
同样用法 truncate table 表名
delete from grade
truncate table grade;
2.delete from 表名 [where 条件]
delete from student where stname = '例子1';
1）单一条件
DELETE FROM student WHERE sid=500;
2）复合条件
DELETE FROM student WHERE sex='男' AND major='计算机';
DELETE FROM student WHERE major='计算机' OR major='1';
DELETE FROM student WHERE sid BETWEEN 1 AND 500;
DELETE FROM student WHERE sid>=1 AND sid<=500;
3）模糊条件
DELETE FROM student WHERE major LIKE '计算机%'; ———— % 通配符（计算机后面任意位任意字符）
DELETE FROM student WHERE major LIKE '%务';
DELETE FROM student WHERE class LIKE '4%期';
DELETE FROM student WHERE class LIKE '4_期'; ———— _ 通配符（4后面一位的任意字符）
DELETE FROM student WHERE sname LIKE '[xw]%'; ———— [] 从括号中取任意一个值
Truncate table student 删除student表记录内容 但是后面不能加条件（不记录日志，速度快）
更新表数据（修改一条记录）
更新表数据
--语法：update 表名 set 字段 = 值，字段2=值[where 条件]
--注意1：字段类型 和 值得类型 要匹配，主键的值可以被修改
--注意2：为了保证表数据的完整一直，最好在更新是增加WHERE 条件。
select * from student
update student set sex = '男' ;
update student set sex = '女' where family = '天津'
update student set family = '广州' where family = '北京'
将学号小于50的记录的入学时间更改为2011-5-18
UPDATE student SET enrollment='2011-5-18' WHERE sid<50;
将‘计算机’专业的学生转成“计算机科学与技术”
UPDATE student SET major='计算机科学与技术' WHERE major LIKE '计算机';
将专业为“信管”开头和“汽车”开头的专业转成“自动化控制”
UPDATE student SET major='自动化控制' WHERE major LIKE '信管%' OR major LIKE '汽车%';
将所有省为“北京”和“北京市”的记录统一成“北京”，并将这些记录的班级改成49期
UPDATE student SET province='北京',class='49期' WHERE province LIKE '北京%';
将入学时间大于1985-1-1记录的出生时间改为比入学年月早18年
UPDATE student SET borth_year=year(enrollment)-18 WHERE enrollment > '1985-1-1';
year() 是求DATE类型的年份
