基于Navicate的基于sql语句（增删改查，多表关联，聚合函数，查询时判断条件，主外键，视图，函数）

 

目录

 一、增删改查

1.1数据库的增,改，删

1.数据库的创建 关键字 DATABAS

2.修改数据库名称  关键字：EXEC sp_renamedb 

3. 数据库的删除 关键字：DROP

1.2 表的创建，修改删除；字段的添加，修改，删除

4.表的 创建，修改 ，删除

5.字段的添加，修改，删除

1.3表数据的增删改查

6.表数据的增加（insert into）

7.表数据的删除 (delete)

8.表数据的更改(update)

 9.表数据的查找**非常重要(select)

10.查询结果当查询条件，字段，和虚拟表

 11.分组 GROUP BY

12.排序 ORDER BY(正：ASC  倒：DESC)

1.4 查询方法的API

13. 查询某个字符串出现的位置 charindex

14. 长度 len

15. 去空格 ltrim/rtrim

16. 截取字符 left/right/substring

 17. 向上/下取整 CEILING/FLOOR

二、聚合函数

18.聚合函数

三、多表关联

19. 左关联 LEFT JOIN

20.右关联 right join

 21.多表关联

23.外联查询：full join

 24.两表合并：union

四、判断条件-数字转为文字

25.判断条件-数字转为文字

五、视图

26.视图

27.Top查询指定行数

六、变量，判断，数据类型转换,循环;函数，存储过程，触发器，事务

28.变量,判断，数据类型转换,循环　(定义变量，变量的赋值方式)

29.创建函数 :function

 30.存储过程：procedure

31.完成的存储案例

32.触发器

 33.事务

怎样优化数据库，提高数据库查询速度：

 

 一、增删改查

1.1数据库的增,改，删

1.数据库的创建 关键字 DATABAS

CREATE DATABASE zy04_db1

2.修改数据库名称  关键字：EXEC sp_renamedb 

EXEC  sp_renamedb 'zy04_db1','zy04_db2'

3. 数据库的删除 关键字：DROP

DROP DATABASE zy04_db1

1.2 表的创建，修改删除；字段的添加，修改，删除

4.表的 创建，修改 ，删除

-- 代码创建表
-- IDENTITY(1,1）自增1
-- null 代表可以为空不
-- 格式：名称 数据类型 为空
create table base_role3(
id int IDENTITY(1,1)not null
,t_name VARCHAR(50) NOT NULL
,T_code VARCHAR(50) null
,t_money decimal(16,2) null
,t_status nchar(3)
)

-- 修改表名 EXEC
EXEC sp_rename 'base_role3','base_role'

-- 删除表名 drop
drop table base_role

5.字段的添加，修改，删除

-- 添加字段 alter
alter  table  表名  add  新字段  varchar(10)  not null

-- 删除字段
alter  table  表名  drop  column  字段 

-- 修改字段
 EXEC  sp_rename  '表名.旧字段'  ,  '新字段'
-- 修改字段数据类型
alter  table  表名  alter  column  字段   varchar(50)

1.3表数据的增删改查

6.表数据的增加（insert into）

insert into 表名 values(对应字段的值)
--第一种：一般不使用，容易出错
 insert into base_user values(4, '赵六' ,20.25  ,'1','1')

--
insert into base_user(id,t_name,t_money,t_status,t_is_admin)values(5,'赵七',20.25,'1','1')

7.表数据的删除 (delete)

-- delete
delete from 表名 --删除所有

--带有条件的删除
delete from 表名 where id=2

8.表数据的更改(update)

 -- 修改 updata
updata 表名 set t_money=52.2,t_name='张三'

-- 带条件的修改
update base_user set t_money=20.25 where id=1

--扩大倍数
 update base_user set t_money=t_money*10 where id=1

 9.表数据的查找**非常重要(select)

----------------普通查找------------
-- 第一种 也是禁止使用的一种，会使执行速度变慢
select * from 表名
-- 第二种 使用字段
select id,t_name,t_money from base_user
-- select 字段名称 from 表名
-- 第三种
--as 修改查询出的字段名称，给字段起别名
select id ,t_name as 姓名,t_money as 金额 from base_user
--表的别名
select id,t_name,t_money from base_user as a

-----------带条件的查找----------
----and 和 or 的用法
select *from base_user
-- 并
where t_is_admin='1'and t_money=50.55
-- 或
where t_is_admin='1'or t_money=50.55
-- 小于	
select *from base_user where id<3

--- in :在··中
select *from base_user where id in (1,3)
-- not in
select *from base_user where id not in (1,3)

--- is
select *from base_user where t_money is null
select *from base_user where t_money is  not  null

----查询以··开头的字段  ：like
-- %代表匹配所有（包括·所有）
select * from base_user where t_money like '2%'
-- 查询以···开头和结尾的字段:
select * from base_user where t_money like '2%5'
select * from base_user where t_money not like '2%5'

10.查询结果当查询条件，字段，和虚拟表

-- 把一个  查询结果当作查询条件的值  来用
select * from base_user where role_id=(select id from base_role where t_code='001')

-- 查询人员时， 查询结果当字段  想知道这个人的角色
select id,(select name from base_role where id=a.role_id) as 角色名称 from base_user as a

-- 查询所有组长的数据   当结果为多个时，用in
select * from base_user where role_id in (select id from base_role where t_code='003')

-- 查询结果当一个表*(通过字段构成虚拟表)用
SELECT 
a.id,a.t_money,a.t_status,a.role_id	
FROM	 (select id,t_money,t_status,role_id from base_user where id>3) as a

   base_user

   base_role

 select id from base_role where t_code='003' 对应下面

 

 11.分组 GROUP BY

-- select 字段，字段 from 表名 group by 字段 字段
SELECT t_money ,role_id FROM base_user GROUP BY t_money,role_id

12.排序 ORDER BY(正：ASC  倒：DESC)

-- 默认为正序排列
select * from 表名 order by 字段
select * from 表名 order by 字段，字段  -- 按书写字段的顺序先后排
-- 倒序排列 DESC
select * from 表名 order by 字段 DESC

1.4 查询方法的API

13. 查询某个字符串出现的位置 charindex

select charindex ('de','abcdef') 
select charindex ('王',t_name) from base_user

14. 长度 len

select len('abcdefr')
select len(t_name) from base_user

15. 去空格 ltrim/rtrim

select ltrim('  aaaaa')--去左边空格
select rtrim('   aaa  ')--去右边
select ltrim(t_money) from base_user

16. 截取字符 left/right/substring

-- 截取字符
select left('abcdef',3)  --从左开始，截取位数
select right('abcdef',3)  --从右开始，截取位数
select SUBSTRING('abcdefghij',3,3) -- 自定义位置

-- 运用
select len(t_money) as len,left(t_name,1) as 截取  from base_user

 17. 向上/下取整 CEILING/FLOOR

select len(t_money) as len,t_money,CEILING(t_money) as 向上取整 ,FLOOR(t_money) as 向下取整, 

二、聚合函数

18.聚合函数

select t_money,
count(t_money) as num , -- conut 得出个数
MIN(t_money) as money_min,  --MIN得出最小
MAX(t_money) as money_max,  --MAX得出最大
AVG(t_money) as moneyavg,  --AVG平均数
SUM(t_money) as moneysun    --总数
from base_user
GROUP BY t_money

-------------------------
-- select后面不跟字段，求的是整个表，只有一行数据，也不用分组
select 
count(*) as num , -- conut 得出个数
MIN(t_money) as money_min,  --MIN得出最小
MAX(t_money) as money_max,  --MAX得出最大
AVG(t_money) as moneyavg,  --AVG平均数
SUM(t_money) as moneysun    --总数
from base_user

三、多表关联

19. 左关联 LEFT JOIN

select 
a.id as userID,
a.t_name as userName,
a.t_money as userMoney,
a.t_status as userStatus,
a.role_id as userRoleId,
a.organ_id as userOrganId,
b.name as roleName,
b.id As roleId,
b.T_code as roleCode

FROM
base_user AS a
LEFT JOIN base_role as b on a.role_id=b.id -- 左关联--关键字：LEFT　JOIN 表名 on 关联的条件--以左表为主

 base_user                       

        

 从这三个表中可以观察出 base-user中的role_id 8在base_role 中 的id根本不存在，此时又是以base_user为主，所以即使7.8不存 在，在主表中也会显现出来，相反副表本就没有的数据也不会出现

 base_role

 

20.右关联 right join

-- =========================右关联查询，以右边表为主查询（右边表查询出所有数据，左边表值展示跟右边表绑定字段的数据）
SELECT
	a.id AS userId,
	a.t_name AS userName,
	a.t_city AS City,
	a.t_money AS userMoney,
	a.t_status AS userStatus,
	a.t_is_admin AS userIsAdmin,
	a.role_id AS roleId ,
	b.t_name as roleName,
	b.t_code as roleCode,
	b.t_status as roleStatus
FROM
	base_user AS a
	RIGHT JOIN base_role AS b ON a.role_id = b.id

 21.多表关联

-- =========================关联多表查询，哪边关联就以那边表为主查询（以哪边表为主，就展示哪边表的数据，另一个表没有字段绑定在主表，就不展示辅表的那条数据）
SELECT
	a.id AS userId,
	a.t_name AS userName,
	a.t_city AS City,
	a.t_money AS userMoney,
	a.t_status AS userStatus,
	a.t_is_admin AS userIsAdmin,
	a.role_id AS roleId ,
	b.t_name as roleName,
	b.t_code as roleCode, 
	b.t_status as roleStatus,
	c.id as organId,
	c.t_name as organName,
	c.t_code as organCode
FROM
	base_user AS a
	RIGHT JOIN base_role  AS b ON a.role_id = b.id
	LEFT JOIN base_organ AS c ON a.organ_id = c.id

22.内联查询:inner join

-- 内联（inner）查询，主表有辅表的绑定数据，才能两表都能查询显示出来（两表共有数据的展示）
SELECT
	a.id AS userId,
	a.t_name AS userName,
	a.t_city AS City,
	a.t_money AS userMoney,
	a.t_status AS userStatus,
	a.t_is_admin AS userIsAdmin,
	a.role_id AS roleId ,
	b.id as BRoleId,
	b.t_name as roleName,
	b.t_code as roleCode, 
	b.t_status as roleStatus
FROM
	base_user AS a
	INNER JOIN base_role as b ON a.role_id = b.id

23.外联查询：full join

（也叫全联查询：两个表的数据都能展示出来）

--外联（full）查询，主表和辅表的数据都能查出来（主表绑定的辅表没有数据，辅表显示为null，辅表数据主表没有绑定，主表显示为null）
SELECT
	a.id AS userId,
	a.t_name AS userName,
	a.t_city AS City,
	a.t_money AS userMoney,
	a.t_status AS userStatus,
	a.t_is_admin AS userIsAdmin,
	a.role_id AS roleId ,
	b.id as BRoleId,
	b.t_name as roleName,
	b.t_code as roleCode, 
	b.t_status as roleStatus
FROM
	base_user AS a
	FULL JOIN base_role as b ON a.role_id = b.id

 24.两表合并：union

（上下查询的两张表的字段数量和顺序要一致）

去重合并（默认）
-- 两表合并:union(上下两张表的字段数量和顺序要一致)
select t_name from base_organ
UNION
select t_name from base_organ1

 不去重合并
-- 两表合并:union(上下两张表的字段数量和顺序要一致)
-- 不去重合并
select t_name from base_organ
UNION ALL
select t_name from base_organ1

四、判断条件-数字转为文字

25.判断条件-数字转为文字

// case when then else end as
select id, t_name, t_city, t_money, t_status 
, CASE t_status
	WHEN '1' THEN '正常'
	WHEN '2' THEN '拉黑'
	WHEN '3' THEN '删除'
	ELSE '未知'
	END as 状态
from base_user

SELECT id, t_name, t_city, t_money
, CASE 
WHEN t_money<20 THEN '钱少'
WHEN t_money>50 THEN '钱多'
ELSE '正常'
END as 金额
FROM base_user

五、视图

26.视图

视图就相当于一个虚拟表，把多个表中的数据汇总到一张表中，创建视图关键字：View

//视图就是把表关联后再改为视图
--视图，关键字：VIEW ***
CREATE VIEW user_pm_v AS
select 
a.id as userId,
a.t_name as userName,
a.t_city as userCity,
b.id as roleId,
b.t_name as roleName,
b.t_code as roleCode
from core_user as a
left join core_role as b on b.id=a.role_id
//也可以添加条件
where b.t_code='002' --002代表产品经理

//视图查询
select　*ｆｒｏｍ　视图名称

27.Top查询指定行数

select 
TOP 10 id, t_name, t_city
from core_user

六、变量，判断，数据类型转换,循环;函数，存储过程，触发器，事务

28.变量,判断，数据类型转换,循环　(定义变量，变量的赋值方式)

1.定义变量关键字：declare，且变量名要以＠开头，更改变量值的时候，必须用set；                  2.通过查询结果给变量赋值

declare @id int, @name varchar(50)
-- 给@id赋值，以set开头
set @id=5
set @id=10
set @id=3
--select name from base_user where id=4
--给变量赋值
set @name=(select name from base_user where id=@id)
print @name

判断 begin end 相当于{},输出关键字：print

declare @id int, @name varchar(50)
-- 给@id赋值，以set开头
set @id=5
set @id=10
if @id<5
begin
	print 'id小于5'
end
else if @id<10        
begin
	print 'id在5到10之间'
end
else
begin
	print 'id大于等于10'
end

 数据类型转换+循环：where

//数据类型转换
convert(数据类型，变量)
declare @id int = 0
while @id<10
begin
	print 'i输出结果：' + convert(varchar, @id)
	insert into test1(t_name, t_code) values ('名称'+convert(varchar, @id), '编号'+convert(varchar, @id))
	set @id=@id+1
end

29.创建函数 :function

create function user_name_f(@id int)--传参
returns VARCHAR(50) --返回数据类型
begin 
declare @name VARCHAR(50)
select @name=name from base_user where id=@id
return @name
END

--使用函数
select dbo.user_name_f(4)

 30.存储过程：procedure

-- 日志表
create table day_log
(
	id int identity(1,1) primary key not null,
	msg varchar(50),
	remark varchar(500)
)
--创建存储过程
create procedure add_one_log
@msg VARCHAR(50),@remark VARCHAR(500)--不带declare的@msg 就是参数
as
insert into code_log(msg,remark) values (@msg,@remark)

--使用存储过程  exec
exec add_one_log '修改','李四修改了数据'

31.完成的存储案例

create procedure add_log_p
@num int,@userName VARCHAR(50),@tableName VARCHAR(100)
as
declare @msg VARCHAR(50)
declare @remark VARCHAR(100)
if @num =1
begin
set @msg='新增'
set @remark=@userName+'新增了'+@tableName
end
else if  @num=2
begin
set @msg='删除'
set @remark=@userName+'删除了'+@tableName
end
else
begin
set @msg='修改'
set @remark=@userName+'修改了'+@tableName
end
insert into code_log(msg,remark) values (@msg,@remark)
--select * from code_log
-- 调用
exec add_log_p 3,'王五','角色'

32.触发器

--触发器
CREATE TRIGGER add_log_t --创建一个触发器（如果已经创建过，把create改为alter），触发器的名字是（add_log_t）
on core_organ
FOR INSERT,UPDATE,DELETE
As
BEGIN
	insert into day_log values('触发器内容','测试一下触发器会不会触发')
END
 
select *from day_log
insert into core_organ values('李白','001')
 
 
--触发器
CREATE TRIGGER add_one_log_t --创建一个触发器（如果已经创建过，把create改为alter），触发器的名字是（add_log_t）
on core_organ
FOR INSERT,UPDATE,DELETE
As
BEGIN
-- 获取操作时只能获取到两种状态（删除和新增）
-- inserted：获取新增的数据，它就表示新增时的那条数据
-- deleted：获取删除的数据，它就表示删除时的那条数据
-- 新增时：inserted有数据
-- 删除时：deleted有数据
-- 修改时：inserted有数据，deleted有数据也有数据
	declare @deleteId int,@insertId int -- 定义两个变量接收操作表数据的id
	select @insertId = id from inserted
	select @deleteId = id from deleted
	if(@deleteId>0 and @insertId >0) -- 修改
		begin
			insert into day_log values('修改','修改了组织架构数据，关联id：'+convert(varchar,@insertId))
		end
	else if(@insertId >0) -- 新增
		begin
			insert into day_log values('新增','新增了组织架构数据，关联id：'+convert(varchar,@insertId))
		end
	else if(@deleteId >0) -- 删除
		begin
			insert into day_log values('删除','删除了组织架构数据，关联id：'+convert(varchar,@deleteId))
		end
END
 
select * from day_log
insert into core_organ values('王维','003')
 
 
 
create trigger add_one_test
on core_role
for insert,update,delete
as
begin
	declare @insertID int ,@deleteID int -- 定义两个变量接收操作表数据的id
	select @insertID = id from inserted
	select @deleteID = id from deleted
	if(@insertID > 0 and @deleteID > 0) --修改
	begin
		insert into day_log values('修改','修改了core_role表中的数据的id是：'+convert(varchar,@insertID))
	end
	else if(@insertID > 0) --新增
	begin 
		insert into day_log values('新增','新增了core_role表中的数据的id是：'+convert(varchar,@insertID))
	end
	else if(@deleteID > 0) -- 删除
	begin
		insert into day_log values('删除','删除了core_role表中的数据的id是：'+convert(varchar,@deleteID))
	end
end
 
select * from day_log
delete from core_role where t_code = '003'
select * from day_log

 33.事务

-- 事务
-- 开启事务
begin transaction 
insert into day_log values('事务','事务111111111')
insert into day_log values('事务','事务222222222')
insert into day_log values('事务','事务333333333')
-- 提交事务
commit
-- 回滚事务
rollback

怎样优化数据库，提高数据库查询速度：

①查询时不使用’*‘查询全部。

②使用索引（给字段设置索引，使用这个字段查询时，设置索引会提高查询速度）。

③分表：数据量大的时候，把数据存入不同表中，。如：人员有十万条数据，分10个表存，每张表

              就存在十万条数据就可以了。

④分库：数据量大的时候，把数据存入不同库中，可以提高执行速度。

⑤读写分离：数据库主从复制（数据库自带主从复制功能），至少创建两个数据库连接，一个连接

                     只读取，一个连接只查询。

⑥做缓存：存入Redis数据库中。

⑦设置唯一键会降低数据库的插入和查询速度 。