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

--先选择

use student

go

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

sp_help studentinfo

--查询表中所有的数据

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','[e-mail]');

select * from customer

insert into customer values('10002','刘小娜','女','北京密云','77776666','[e-mail]');

insert into customer values('10003','刘振家','男','北京怀柔','67676677','[e-mail]');

insert into customer values('10004','李然','女','北京海淀','88886565','[e-mail]');

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','张无忌','[e-mail]')

--如何删除约束

alter table customer

drop constraint customer_email_ck 

insert into customer (cid ,cname,email)values('30002','令狐冲','[e-mail]')

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='[e-mail]' where cid ='30003'