数据库基础学习——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','[email protected]');
select * from customer
insert into customer
values('10002','刘小娜','女','北京密云','77776666','[email protected]');
insert into customer
values('10003','刘振家','男','北京怀柔','67676677','[email protected]');
insert into customer
values('10004','李然','女','北京海淀','88886565','[email protected]');
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','张无忌','[email protected]')
--如何删除约束
alter table customer
drop constraint customer_email_ck 
insert into customer (cid
,cname,email)values('30002','令狐冲','[email protected]')
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='[email protected]' where cid ='30003'

• « 上一篇：区块链技术在金融领域应用发展情况
• » 下一篇：MySQL高可用之组复制技术(3)：配置多主模型的组复制