数据库技术:数据库基础学习之SQL代码实例讲解

–先选择

use student

go

–查看表是否存在，若存在可以查询结果可查看表结构，如果不存在则提示表不存在

sp_help studentinfo

–查询表中所有的%ignore_a_1%

select *from studentinfo;

–先选择数据库

use worker

–创建数据库

create database worker

use worker

–创建workerinfo表

create table workerinfo(

工号  varchar(10) not null primary key,

姓名  varchar(20) not null,

工龄  int,

年龄  varchar(20)

)

go

–删除表

drop table workerinfo

–查看表workerinfo的结构

sp_help workerinfo

–查询表workerinfo的所有信息

select * from workerinfo;

–在libray数据库中，创建customer表和book表，并插入数据，进行查询

use library

go

–创建顾客表

create table customer(

cid       char(8) not null primary key,  –主键

cname     varchar(20) not null,          –姓名

sex       char(2),                       –性别

address   varchar(60),                   –地址

phone     char(15),                      –电话

email     varchar(50)                    –邮箱

)

go

sp_help customer

–创建表

create table book(

bid       char(8) not null primary key,–书编号

bname     varchar(40) not null,        –书名

pub       varchar(20),                 –出版社

author1   varchar(20),                 –作者1

author2   varchar(20),                 –作者2

type      varchar(20),                 –类型

numinput  int not null,                –入库量

numstore  int not null                 –现存量

)

go

–查看表结构

sp_help book

–向表中插入数据:dml:insert

–语法1：默认插入所有列 insert into 表名 values(值1，值2)；

–注意：值得顺序、类型以及宽度要与字段的类型、顺序、宽度一致–

–一条insert只能插入一条

–向学生表中插入一条记录

use student

sp_help studentinfo

select * from studentinfo

insert into studentinfo values('004','张鑫',19,'北京海定');

–向customer表插入数据

use library

sp_help customer

select * from customer

insert into customer values('10001','张鑫','男','北京海淀','88886565','zhangxin@163.com');

select * from customer

insert into customer values('10002','刘小娜','女','北京密云','77776666','liuxiaona@163.com');

insert into customer values('10003','刘振家','男','北京怀柔','67676677','liuzhenjia@163.com');

insert into customer values('10004','李然','女','北京海淀','88886565','liran@163.com');

select * from customer

–向book表插入数据

use library

go

sp_help book

insert into book values('a0001','邓小平理论','人民出版社','邓小平','','政治类型',20,20);

select * from book

use library

select * from customer

select * from book

insert into book values('a0001','邓小平理论','人民出版社','邓小平','','政治',20,20);

insert into book values('a0002','邓小平理论基础','人民出版社','邓小平','','政治',25,25);

insert into book values('b0001','计算机基础','电子出版社','谭强','赵肖','计算机',30,30);

insert into book values('b0002','计算机理论基础','邮电出版社','赵爽','','计算机',35,35);

insert into book values('c0001','军事天地','军事出版社','李娜','李鑫','军事',40,40);

select * from book

/*

第2种语法：向指定的列插入数据

不插入字段，默认为空值

insert into 表名（字段1，字段2…）values(值1，值2，…)

注意，值得类型和顺序保持一一对应

如果字段为非空，必须插入值

*/

–像学生表中插入记录 005 李清

use student

go

sp_help studentinfo

insert into studentinfo (学号,姓名) values('005','李清');

select * from studentinfo 

–练习向customer表以及book表插入部分数据

use library

go

sp_help customer

insert into customer(cid,cname) values('20001','张三');

select * from customer

insert into customer(address,cname,cid) values('北京东城','李四','20002');

select * from customer

go

sp_help book

insert into book (bid,bname,pub,author1,type,numinput,numstore) values('d0003','田径','运动出版社','赵垒','体育',100,100);

select * from book

insert into book(bid,bname,numinput,numstore) values('n0001','娱乐天地',100,100);

select * from book;

/*

删除表中的数据（记录、行）

删除0,1，n行

语法：dml:delete

delete from 表名 where条件

注意：如果不写where 子句，会删除所有的行

满足where中条件的行才会删除

*/

use student 

go 

create table test1(

id    int primary key,

name  varchar(30)

);

insert into test1 values(1,'tom');

insert into test1 values(2,'james');

insert into test1 values(3,'mary');

insert into test1 values(4,'tony');

insert into test1 values(5,'andi');

select * from test1

–比较相等=

delete from test1 where(id=3);

select * from test1;

delete from test1 where(name='mary');

delete from test1 where(name='tony');

–删除test1中所有的数据

delete from test1

select * from test1;

–删除表 drop ddl：drop 和结构有关

drop table test1;

sp_help test1

–删除数据库 ddl：drop

drop datebase testing;

/*

修改表中的数据 dml：update语句

语法：

update 表名

set 字段1=新的值，字段2=新的值，…

where 记录的匹配条件

说明：如果不写where子句，默认修改所有的行

心中要有表

*/

use worker

go

drop table worker;

–先删后创建

create table worker(

id       int not null primary key,

name     varchar(20) not null,

salary   float not null,

phone    varchar(20)

);

insert into worker values(1,'张三',3366.50,'999999');

insert into worker values(2,'李四',4500.00,'995555');

insert into worker values(3,'王五',5300.50,'776666');

insert into worker values(4,'马六',3200.50,'888888');

insert into worker values(5,'赵琦',3690.80,'666887');

select *from worker

–所有员工加薪1000

update worker set salary=salary+1000.0;

update worker set salary=salary+100 where id=3;

–给赵七加薪300

update worker set salary=salary+310 where name='赵琦' ;

–练习可以同时修改多个点和字段

–给id=3的员工加薪300，电话修改为0000000

update worker set salary=salary+300,phone='000000' where id=3;

select * from worker

select * from worker where id=3;

–练习：操作library数据库

use library

sp_help book

go

select * from book

–书为娱乐天地，出版社改为新华出版社

update book set pub='新华出版社' where bname='娱乐天地';

select * from book where bname='娱乐天地'

–将author1位为‘邓小平’改为author2改为‘江泽民’

–numinput and numstore 改为100

update book set author2='江泽民',numinput=100,numstore=100 where author1='邓小平';

/*

查询语句 dml ：select 语句 dql query查询

select  字段名，表达式，函数调用

from 表名

where 查询的物理条件

*/

–查询book表中bid=a0001的部分信息bid，bname,pub

select * from book;

select bid,bname,pub from book  where  bid='a0001';

–查询出bid为‘c0001的所有信息’

select * from book where bid='c0001';

–查询book,给查询的字段起别名

–语法：as 别名  临时的，

select bname as 书名,author1 as 作者,pub as 出版社

from book;

–as可以省略

–查询customer表，字段其别名

–可以省略as

select cid 序号,cname 姓名,address,phone as 电话 from customer;

–sqlserver中还可以通过=起别名

select 书名=bname,书号=bid,pub  from book;

–查询结果拼接

–将多个列合并成一个列显示

select bname as 书名, author1+' '+author2 as 作者 from book;

–使用常量，select 之后可以跟 表达式、字段名、函数调用、常量、*

use worker

sp_help worker

select name,salary, '试用' as 类型  from worker;

select 1+2 from worker;–有几行就算几遍

select 1+2;–sqlserver 中值只写select是可以的

select getdate()as 当前时间;

select '试用' as 类型;

–查询的时候指定条件  where子句

–选择  过滤出需要的行（记录）

–查询出学生表中姓名为张三的所有信息

use student

select * from studentinfo

–等值的比较

select * from studentinfo where 姓名='张三';

–使用比较运算符表示各种条件

–= > < >= <= !> !<

–不等于：!= 或者 <>

–查询顾客表中性别为女的所有顾客的cid，cname,adress,sex 信息

use library

go

sp_help customer

select * from customer

select  cid,cname,address,sex from customer where sex='女';

select * from book where numinput!>50;

select * from book where numinput!=40;

select * from book where numinput<>40;

–查询出不住在北京海淀的顾客

–查询出姓名和住址

select * from customer

select cname,address from customer where address!='北京海淀';

–空值带来的影响

–结论：空值和任何值，包括null都无法直接比较；

–比较后都为假

–判断是否为空 is null

–判断是否不为空 is not null

select * from customer where address is null;

select * from customer where address is not null;

–逻辑表达式

–and  并且   优先级比or 高

–or   或者

–not  非

–查询地址为’北京海淀‘的并且性别为’男‘

select cid,cname,address,sex from customer where address='北京海淀' and sex='男';

select * from customer

–查询库存量在10和35之间，包括边界，的图书信息

select * from book where numstore>=10 and numstore<=35;

–between… and… 包括边界

select * from book where numstore between 10 and 35;

–between m and n  在[m,n]之间 闭区间

–in (m,n,k,…)   只要出现在括号中，返回真列举

select * from book where bid ='b0001' or bid='b0002';

select * from book where bid  in('b0001','b0002');

/*

模糊查询

语法：字段名 like '匹配字符串'

匹配字符串：

%    0个或者多个字符（任意个字符）

——   任意一个字符

[]   在范围内的一个字符

[^]  不在范围内的一个字符

*/

–查询出图书的名称，以’邓小平‘开头

select * from book where bname like '邓小平%';

–查询出图书名称包含有‘小平’的图书

select * from book where bname like '%小平%';

–已知bid员工有5个字符

–查询出bid以“b000”开头的图书信息

select * from book where bid like 'b000_';

–[]只能表示一个字符

–表示字符范围abc开头，后面任意

select * from book  where bid like'[a-c]%';

select * from book  where bid like'[abd]%';

select * from book  where bid like'[a-cn]%';

–不以and开头，后面任意

select * from book  where bid like'[^adn]%';

/*

关于查询的其他技巧：

1.top(n)问题，返回查询结果中前n条

1）表示前n条

select top n 列名 from 表名 where 条件

2）表示前n%

select top n percent 列名 from 表名 where 条件 

*/

use library

sp_help customer

go

–返回查询结果的前3条记录

select top 3 * from customer;

–返回查询结果的前20%的记录

select top 20 percent * from customer;

–返回满足条件的一半数据

select top 50 percent cid,cname,sex from customer where sex='女';

/*

测试时候比较注重数据备份

2.使用select可以复制表

语法：select * into 新表名 from 原表名;

会根据原表的结构创建新的表，同时将查询回的结果插入到新表中

*/

use library

go

sp_help book

select * from book;

–使用book表复制新的表名为book1

select * into book1 from book;

–7行受影响

go

sp_help book1;

select * from book1;

–复制时可以指定条件、行、列

select bid,bname,pub,author1 into book2 from book where pub='人民出版社';

sp_help  book2

select * from book2

–创建新的表book3,只拷贝book表的结构

–不拷贝数据

select * into book3 from book where 1<>1;–永假式 技巧

sp_help book3

select * from book3

/*

分离数据库 library

    将数据库脱离管理器

    注意：不能分离正在使用的数据库

附加数据库 library

    将数据库加入管理器管理     

*/

/*

sql_server常用的数据类型：

1.数值型

int 整数

float 小数

2.字符型

char(n)   定长字符串

varchar(n) 可变长字符串

3.日期型

datatime

*/

select getdate();–返回日期型数据

/*

数据完整性：

       保证数据的准确性、一致性

       关键在于设计

 分为3种：

 1）实体完整性（行完整性）     保证每一行的唯一性 id(pk)  

 2）域完整性  （属性完整性）   确保某个字段的值符合业务要求

   比如：email格式  必须要有@符号

 3）引用完整性（参照完整性）    

   比如：emp表的dept_id要参照于dept表的id才有意义

   

为了实现以上的完整性，需要使用：约束 constraint  各种主流数据库约束语法都相同

分类：

1.主键约束    pk(primary key)

2.唯一约束    uk(unique)

3.外键约束    fk(foreign key)

4.非空约束    nn（not null）

5.检查约束    ck(check key)

6.默认值约束  default

约束是针对表中的某个字段添加的

*/

/*

主键约束  pk  primary key

pk=uk+nn  唯一且非空

具备类似的效果

保证实体的完整性，即每一行都是唯一的

uk:unique

nn:not null

*/

use worker

–创建新的表，员工表emp,提供pk约束

drop table emp;

create table emp(

id      int constraint emp_id_pk primary key,

name    varchar(30) not null,

salary  float

);–列级约束  约束直接定义在字段之后  语法：constraint 约束名称 约束类型

insert into emp values(1,'tom',5000.0); 

insert into emp values(1,'tom',5000.0);

select * from emp;

–表级约束  所有的字段后追加一个约束

create table emp1(

id      int,

name    varchar(30) not null,

salary  float,

constraint emp1_id_pk primary key(id)

);–约束名不能重复，表存在约束就在，名字不能随便取

insert into emp1 values(1,'tom',6000.0);

insert into emp1 values(1,'tom',6000.0);

–需求：创建一张表stu,存在两个pk

–无法将多个 primary key 约束添加到表 'stu'。

–一张表无法同时定义多个pk

drop table stu

create table stu(

id1 int primary key,

–id2 int primary key, –无法将多个 primary key 约束添加到表 'stu'。

name varchar(20)

);

–联合主键

–使用表级约束，定义联合主键

–定义：联合起来是唯一且非空的

–目前只能够使用表级约束

create table stu(

id1 int,

id2 int,

name varchar(30),

constraint stu_id1_id2_pk primary key(id1,id2) 

);

insert into stu values(1,1,'tom');

insert into stu values(1,2,'james');

insert into stu values(1,3,'mary');

select * from stu

insert into stu values(1,1,'tony');–违反了 primary key 约束 'stu_id1_id2_pk'。不能在对象 'dbo.stu' 中插入重复键。

insert into stu values(2,1,'andi');

/*

联合主键表示联合起来唯一且非空

红 蓝

红 绿   单个可重复

红 黑

蓝 绿   组合在一起不重复就可

红 绿   重复了

绿 红   只有一个不同就可以

*/

–通过后期追加约束  ddl：alter

create table emp2(

id int not null,

name varchar(30) not null,

salary float

);

–给emp2表中的id 字段住家pk约束

–使用ddl：alter改变表结构

alter table emp2

add constraint emp2_id_pk primary key(id);

–一个数据库里的约束名是唯一的，不能重复使用

–删除约束（所有约束方式一样）

–根据表和约束名就可删除

alter table emp2

drop constraint emp2_id_pk;

use worker

–唯一约束  uk unique

–保证实体完整性

drop table stu

create table stu(

id    int not null unique,

name  varchar(30)

);

insert into stu values(1,'tom');

insert into stu values(1,'tom');

–追加约束uk name唯一

alter table stu

add constraint stu_name_uk unique(name);

insert into stu values(2,'tom');

insert into stu values(2,'tony');

–联合唯一约束

use worker

drop table stu1

create table stu1(

id    int primary key,

name1 varchar(30),

name2 varchar(30)

);

–给stu1追加一个联合唯一约束

–要求name1 和 name2  两个字段联合唯一

alter table stu1

add constraint stu1_name1_name2_uk unique(name1,name2);

insert into  stu1 values(1,'tom','cat');

insert into  stu1 values(2,'tom','cat');

insert into  stu1 values(2,'tom','candy');

insert into  stu1 values(3,'tom','cat');

select * from stu1

–删除约束

alter table stu1

drop constraint stu1_name1_name2_uk;

–删除记录为id=3

delete from stu1 where id=3;

–有重复数据想要添加约束时要先删除再添加

/*

3.外键约束  fk  foreign key

引用完整性、参照完整性

*/

–父表 dept 部门表

–子表 emp  员工表

–规则：父先与子存在，子先与父消失

drop table emp;

drop table dept;

–父表

create table dept(

  id   int constraint dept_id_pk primary key,

  name varchar(30) not null unique

);

–子表

create table emp(

  id       int constraint emp_id_pk primary key,

  name     varchar(30) not null,

  salary   float,

  dept_id  int,

  –外键约束

  –参照的表的值id必须是唯一的uk

  –dept 表中的id是pk一定是唯一的

  –references 关键字：参照于（引用）

  constraint emp_dept_depid_fk foreign key(dept_id) references dept(id)

);

–先插入dept表的数据

insert into dept values(1,'销售');

insert into dept values(2,'行政');

insert into dept values(3,'研发');

select * from dept

–插入emp表的数据

insert into emp values(101,'tom',5000.0,1);

insert into emp values(102,'james',6000.0,2);

insert into emp values(103,'mary',8000.0,3);

insert into emp values(104,'jobs',9000.0,2);

select * from emp

insert into emp values(105,'tony',7000,1);

–版本2.外键约束时可以后期追加的

–alter

drop table emp;

drop table dept;

–父表

create table dept(

  id   int constraint dept_id_pk primary key,

  name varchar(30) not null unique

);

–子表

create table emp(

  id       int constraint emp_id_pk primary key,

  name     varchar(30) not null,

  salary   float,

  dept_id  int,

);

–给员工表emp的部门dep_id追加一个fk约束

–emp表的demp_id(fk)参照于dept表的id(pk)

alter table emp

add constraint emp_dept_deptid_fk foreign key(dept_id) references dept(id) ;

–先插入dept表的数据

insert into dept values(1,'销售');

insert into dept values(2,'行政');

insert into dept values(3,'研发');

select * from dept

–插入emp表的数据

insert into emp values(101,'tom',5000.0,1);

insert into emp values(102,'james',6000.0,2);

insert into emp values(103,'mary',8000.0,3);

insert into emp values(104,'jobs',9000.0,2);

select * from emp

insert into emp values(105,'tony',7000,6);

delete from emp where id=105; 

–删除外键约束

alter table emp

drop constraint emp_dept_deptid_fk;

/*

在某些数据库中，有时即使两张表有关系，也不加外键约束，目的是：提高系统性能

数据库维护每个约束来增加系统的开销，可以通过其他层面技术来弥补

结论：一般选择加约束，但不绝对

具体问题具体分析

*/

/*

1.主键约束 pk primary key

2.外键约束 fk foreign key

3.唯一约束 uk unique key

4.唯一约束 nn not null

*/

/*

检查约束 ck check

针对某个字段进行数据的有效性检查

如果数据不符合要求，无法插入成功

*/

–针对customer表的email字段来添加约束

–保证email 字段有效性：必须要有@

use library

select * from customer

go

sp_help customer

alter table customer

add constraint customer_email_ck check(email like '%@%');

–以后插入数据就会检查email的格式

insert into customer (cid ,cname,email)values('30001','张无忌','zhangwuji@163.com')

–如何删除约束

alter table customer

drop constraint customer_email_ck 

insert into customer (cid ,cname,email)values('30002','令狐冲','linghuchong@163.com')

insert into customer (cid ,cname,email)values('30003','刘玉','ly')

–又想加约束了

alter table customer

add constraint customer_email_ck check(email like '%@%');

–必须修改对应email

update customer set email='ly@163.com' where cid ='30003'

需要了解更多数据库技术:数据库基础学习之SQL代码实例讲解，都可以关注数据库技术分享栏目—计算机技术网（www.ctvol.com）