常用的数据库脚本

在平时的测试工作中，经常会遇到如何检查数据正确性的问题。常用的方法时，取界面上的数据和数据库中的数据进行比较，若是一致，则功能基本上是没有问题的。
—-更新一条记录

 update custom_info set custom_name=‘test’ where custom_id=1

—更新多条记录

 update custom_info set custom_name=‘test’ ,custom_gender=‘female’ where custom_id=1

—删除记录

delete from custom_info where custom_name=‘test’ or custom_name=‘lucy’

—-删除多条记录

delete from custom_info where custom_name=‘test’ and  custom_name=‘lucy’

—-一次插入多条记录的三种方法

insert into 表名 values(‘’values1,’values2’,’values3’)

—-将数据插入到另一个表中

insert into  表名(列名) select 列名 from 表名
insert into test(custom_name) select custom_name from custom_info

注意：这种插入方式，新表要是已经存在的。
—第三种方法，将现有表中的数据插入到新表中，注意新表会自动创建
select 列名 into 新表名 from 旧表名

select custom_name into test1 from custom_info 

—查询
—查询yongh用户的年龄总和

select sum(cstomer_age) as 总年龄  from customer_infor

—查询最小的年龄

 select min(customer_age) as 最小年龄 from customer_info

—–查询最大年龄

 select max(customer_age) as 最大年龄   from  customer_info

—查询表中所有用户的平均年龄

 select avg(customer_age) as 平均年龄 from customer_info

—查询表中一共有多少条数据

select count(*) as 记录数 from customer_info

—查询含有特定字符,如abc的用户信息

select * from customer_info where name like ‘%abc%’

—查询姓赵的名字是两个字的记录

select * from customer_info where name like ‘赵_’

—添加序号列

select ROW_NUMBER() over(order by customer_id) as 序号, customer_name, customer_gender, customer_age from customer_info

—查询每个课程有多少人选择

select count(*) as totalNum from customer_info group by CourseID

—查询男生女生的平均年龄

select AVG(customer_age) as 平均年龄, student_gender as 性别 from student_info group by student_gender

—-内连接查询即多张表（比如两张表）一起查询
如 course_info 表中存储的数据格式如下：

Course_id cousrse_name
1                English
2.              Chinese
3               Math
4               Physical
5               biography

student_info 表中存储的是学生的信息，如

course_id  student_no student_name. student_gender 
1               1001             Amy               Female
2              1002             Bob             Male
3            1003             Carl             Male
4               1004            David           Male
5              1005             Evan            Female
6              1006             Fanny          Female

方法一：

select student_no student_name, student_gender, course_name course_info.Course_id from student_info inner join course_info on student_info.course_id=course_info.Course_id

方法二：

select student_no student_name, student_gender, course_name course_info.Course_id from student_info, course_info where student_info.course_id=course_info.Course_id

个人更喜欢第二种方式

—外连接
—一张表里的所有数据都被查询出来，另一张表里和它匹配的数据被查询出来，不匹配的不查询出来

select course_name, student_name from course_info left join student_info on student_info.course_id=course_info.Course_id
select course_name, student_name from student_info right join course_info on student_info.course_id=course_info.Course_id

—创建表

create database DB_Book

Use DB_Book

create table book type
{
    TypeID int primary key identity, —自增列
    TypeName varchar(50) not null,
}

Create table book
{
    BOOKID int primary key identity,  —自增列
    BOOKName varchar(50) not null,
    BOOKAuthor varchar(30) not null,
    BOOKDate varchar(50) not null,
    TypeID int foreign key references booktype(TypeID)
}

insert into  book type values(‘古典’), (‘科幻’), (‘现代’), (‘言情’) —添加信息
insert into book values (‘三国演义’, ‘赵一’, ’2010-1-1’, 1), (‘水浒传’, ‘钱二’, ’2011-1-1’, 1), (‘西游记’, ‘孙三’, ’2012-1-1’, 1),(‘红楼梦’, ‘李四’, ’2014-1-1’, 1)

—分别查询每类图书的数量

select count (*) as 数量, typeName as 类型 from book, booktype where book.typeid = booktype.typeid group by typeName
—同一张表中查询同名用户的信息
select * from customer_info a, customer_info b where a.Customer_Name = b.Customer_Name and a.customer_id <> b.customer_id