八、Navicat管理sql server的简单sql语句

目录

1.修改数据库名字的sql语句

2.创建表语句：

3.添加字段

4.修改字段

5.删除字段

6.修改字段数据类型

7.修改表名

8.删除表

9.查询表中所有字段(在工作中禁止使用)，执行速度会变慢

10.查询表中某些字段

11.查询表中某些字段,并把查询出的字段列名转化为中文

12.查询表中某些字段，并给表起个别名

13.查询

14.修改

15.删除

16.group by 分组

17.order by 排序

18. 函数

19.聚合函数

 20.左关联查询：left join

 20.右关联查询:right join

21.多表关联查询 

22.内联查询:inner join

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

24.两表合并：union（上下查询的两张表的字段数量和顺序要一致）

25. 查询时将字段值，条件判断转化成想要的

26.视图

27.定义变量（declare）、if判断语句、循环语句

28.创建一个函数

29.创建存储过程

30.触发器

31.事务

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

1.修改数据库名字的sql语句

EXEC sp_renamedb ‘新数据库名字’,’原来的数据库名字’

2.创建表语句：

CREATE TABLE zy05_role

(
    id int  not null  identity(1,1)  primary key,
    t_name  varchar(50)  not null ,
    t_code  varchar(50)  null,
    t_money  decimal(16,2)  null,
    t_status  nchar(3)
)

3.添加字段

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

4.修改字段

EXEC  sp_rename  '表名.旧字段'  ,  '新字段'

5.删除字段

alter  table  表名  drop  column  字段 

6.修改字段数据类型

alter  table  表名  alter  column  字段   varchar(50)

7.修改表名

EXEC  sp_rename  '旧表名'  ,  '新表名'

8.删除表

drop  table 表名

9.查询表中所有字段(在工作中禁止使用)，执行速度会变慢

select*from base_user

10.查询表中某些字段

select id,t_name,t_money from base_user

11.查询表中某些字段,并把查询出的字段列名转化为中文

select id as id编号 , t_name  as 名字 , t_money as 资金 from base_user

12.查询表中某些字段，并给表起个别名

select 用户表.id , 用户表.t_name , t_money  from  base_user  as 用户表

13.查询

select * from  base_user  where  t_status= '1'  and  t_is_admin = '0'

select * from  base_user  where  t_status= '1'  or   t_is_admin = '1'

查询id=1的字段

Select * from  base_user  where  id = 1

select*from base_user where id <= 3
-- in(1,2,3) id等于其中一个即可（等效于id = 1 or id = 2 or id = 3）
select*from base_user where id in (1,2,3) 
select*from base_user where id not in (1,2,3) 
select*from base_user where t_city = ''
select*from base_user where t_city is null

-- 查询城市包含郑的数据
select *from base_user where t_city like '%郑%'
-- 查询城市不包含郑的数据
select *from base_user where t_city not like '%郑%'、


-- 查询所有角色是项目经理的人员（base_role中t_code : 001 项目经理，002：产品经理，003：销售经理）
select id from base_role where t_name ='项目经理'
select *from base_user where role_id = (select id from base_role where t_name ='项目经理')


-- base_role 表中有产品经理1和产品经理2，t_code都是002，id不同时
-- 查询所有角色是产品经理的人员（base_role中t_code : 001 项目经理，002：产品经理..，003：销售经理）
select id from base_role where t_code ='002'
select *from base_user where role_id in (select id from base_role where t_code ='002')

-- 查询人员时，想知道这个人员的角色名称
select *,(select t_name from base_role where id = a.role_id) as 角色名称 from base_user as a

14.修改

update  base_user  set  t_name = '第一个'  where id = 1

15.删除

delete  from  base_user  where  id = 4

16.group by 分组

分组查询

-- 1，根据性别分组，统计男性员工和女性员工的数量
select gender,count(*) from emp group by gender

-- 2．根据性别分组，统计男性员工和女性员工的平均年龄
select genden,avg(age) from emp group by gender

-- 3，查询年龄小于45的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址(having:分组后的筛选)
select wonrkedress, count(*) address_count from emp where age < 45 group by workedress having address_count >= 3

17.order by 排序

• ASC：升序（默认）
• DESC：降序

--排序查询
-- 1．根据年龄对公司的员工进行升序排序select * from emp order by age asc;select * from emp order by age desc;
select *from emp order by age;
-- 2．根据入职时间，对员工进行降序排序
select *from emp order by entrydate desc;
--3．根据年龄对公司的员工进行升序排序，年龄相同，再按照入职时间进行降序排序select * from emp order by age asc , entrydate desc;
select * from emp order by age asc , entrydate asc;

18. 函数

-- 查询某个字符串出现的未知
select charindex('b','abcdefg') -- 开始位置为2
select len('abcdefg') -- 长度为7
select ltrim('   abc   ') -- 去掉左边的空格:'abc  '
select rtrim('   abc   ') -- 去掉右边的空格:'   abc'

-- 截取字符串
select left('abcdefg',2) -- 从左边开始截取两个长度的字符串
select right('abcdefg',2) -- 从右边开始截取两个长度的字符串
select substring('abcdefg',2,3) --从第二个字符开始截取三个字符长度

-- 取整
select floor(22.66) --向下取整
select ceiling(22.66) --向上取整

-- 转大写、转小写
select upper('abcdefg') --转大写
select lower('ABCDEFG') --转小写

-- 取绝对值
select abs(-10)

-- 生成一个0-1之间的随机数
select rand()
select rand()*10
select floor(rand()*10) --生成一个[0,10)之间的随机整数

-- 四舍五入
select round(66.45,1) -- 66.50
select round(66.44,1) -- 66.40
select round(66.4456,2) -- 66.4500

-- 得出幂运算
select power(4,2) --16
select power(4,3) --64

-- 获取当前时间
select getdate()
select id,t_name,getdate() as 当前时间 from base_user


select left(t_city,2) as 截取 ,t_city from base_user




--知果第一个参强不是null，则返回第一个参数，如果第一个参数是nlll，则返回第二个参数
select ISNULL{null,'未知')

19.聚合函数

-- 聚合函数
select avg(id) as id的平均值  from base_user
select sum(id) as id的总和 from base_user
select max(id) as 最大的id from base_user
select max(t_money) as 最大的资金 from base_user

select t_city ,count(*) as 城市分组数量,max(t_money) as 最大资金 ,min(id) as 最小id from base_user group by t_city

 20.左关联查询：left 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
	LEFT JOIN base_role AS b ON a.role_id = b.id
where a.id = 3

 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. 查询时将字段值，条件判断转化成想要的

select t_status ,case t_status  --可写选择的字段，也可以不写（不写时下面每个判断就要再写一遍要判断的字段名）
	when '1' then '正常'
	when '2' then '拉黑'
	when '3' then '禁用'
	else '未知'
	end as 状态
from  base_user


select t_is_admin ,case  
	when t_is_admin='1'  then '是'
	when t_is_admin='0'  then '否'
	else '未知'
	end as 是否是管理员
from base_user


select t_money,
	case 
	when  t_money <30.00 then '钱少' 
	when  t_money >30.00 then '钱多' 
	else '未知'
	end as 资金
from base_user

26.视图

select*from
(select 
    a.id AS userId,
	a.t_name as userName,
	a.t_city as City,
	a.role_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 a.role_id = b.id) as c

-- 创建一个视图（相当于一个虚拟的表）
create view core_user_role_v as 
select 
    a.id AS userId,
	a.t_name as userName,
	a.t_city as City,
	a.role_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 a.role_id = b.id

-- 查询视图
select * from core_user_role_v  where userId = 1


-- 创建一个视图，这个视图只包含正常status的人员数据
create view core_normalStatus_v as
select
    a.id AS userId,
	a.t_name as userName,
	a.t_city as City,
	a.t_money AS userMoney,
	a.t_is_admin AS userIsAdmin,
	a.role_id as roleId ,
	b.t_name as roleName,
	b.t_code as roleCode,
	case a.t_status
	when '1' then '正常'
	when '2' then '黑户'
	when '3' then '禁用'
	end as status
from core_user as a
left join core_role as b on a.role_id = b.id
where a.t_status = '1'


-- 创建视图，这个视图里面包含人员和角色数据，以角色编号分组
-- 显示出每一个角色人员数量 ，每一个角色对应人员的金额总和
-- 不分组查询会有重复（产生：笛卡尔集）
create view test_v as
select 
	a.t_name as roleName,
	a.t_code as roleCode,
    count(b.id) as userCount,
    sum(b.t_money) as allMoney
from core_role as a
	left join core_user as b on b.role_id = a.id 
group by a.t_code,a.t_name


-- 创建视图, 这个视图包含人员和角色数据，以人员状态分组
-- 显示出每一个角色的数量
create view test2_v as 
select 
    a.t_status as userStatus,
    count(b.id) as roleCount
from core_user as a
    left join core_role as b on a.role_id = b.id
group by a.t_status


-- 创建视图, 这个视图包含人员和角色数据，以人员状态分组
-- 显示出每一个角色的数量
create view test2_v as 
select 
	a.t_status as userStatus,
	count(b.id) as roleCount
from core_user as a
	left join core_role as b on a.role_id = b.id
group by a.t_status

27.定义变量（declare）、if判断语句、循环语句

-- declare 定义变量，以@开头
declare @id int ,@name varchar(50)
-- 给@id赋值，以set开头
set @id = 5
select  @id

if(@id < 5)       --() 可省略
begin -- 相当于{
	print 'id小于5'
end 
else if(@id>5)   --相当于else if{}
begin  
	print 'id大于5'
end   
begin  
	print 'id等于5'
end   

select @name = t_name from core_user where id = @id
print @name

set @name =(select t_name from core_user where id = @id)
print @name



-- while 循环
declare @id int = 1;
while (@id <10)
begin 
	print @id
	set @id = @id+1
end
-- while 循环
declare @id int = 1;
while (@id <10)
begin 
	print '输出结果：'+convert(varchar,@id)
	insert into test_table1 values('名字'+convert(varchar,@id),'编号'+convert(varchar,@id))
	set @id = @id+1
end

28.创建一个函数

-- 函数
-- 传入人员id ，返回一个人员名称
create function user_name_f(@id int)
returns  varchar(50) --返回值类型
begin
-- declare @name varchar(50) --定义一个变量
-- select @name = t_name from core_user where id = @id
-- return @name
-- 简写
	declare @name varchar(50) = (select t_name from core_user where id = @id)
	return @name
end


-- 调用函数
select dbo.user_name_f(5)

29.创建存储过程

-- 日志表
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)
as 
insert into day_log values(@msg,@remark)

-- 使用存储过程
EXEC add_one_log '修改','李四修改了角色名称'  -- day_log日志表中就插入了一条数据


-- 创建存储过程
create procedure add_log
@num int ,@userName varchar(50),@tableName varchar(100) --形参
as 
declare @msg varchar(50) ,@remark varchar(500)
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 day_log values(@msg,@remark)

-- 调用存储过程
EXEC add_log 1,'王二','角色表'

30.触发器

--触发器
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

31.事务

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

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

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

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

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

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

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

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

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

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

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

1.尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

2.尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num is null
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num=0

3.尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

4.尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num=10 or num=20
可以这样查询：
select id from t where num=10
union all
select id from t where num=20

5、in和 not in 也要慎用，否则会导致全表扫描，如：
select id from t where num in(1,2,3)
对于连续的数值，能用between就不要用 in 了：
select id from t where num between 1 and 3

6.下面的查询也将导致全表扫描：
select id from t where name like '%abc%'

7.尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t where num/2=100
应改为:
select id from t where num=100*2

8.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t where substring(name,1,3)='abc'--name以abc开头的id
应改为:
select id from t where name like 'abc%'

9.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

10.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

11.不要写一些没有意义的查询，如需要生成一个空表结构：
select col1,col2 into #t from t where 1=0
这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：
create table #t(...)

12.很多时候用 exists 代替 in 是一个好的选择：
select num from a where num in(select num from b)
用下面的语句替换：
select num from a where exists(select 1 from b where num=a.num)

13.并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。

14.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，
因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。
一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

15.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。
这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

16.尽可能的使用 varchar 代替 char ，因为首先变长字段存储空间小，可以节省存储空间，
其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

17.任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

18.避免频繁创建和删除临时表，以减少系统表资源的消耗。

19.临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。

20.在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，
以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

21.如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

22.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。

23.使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。

24.与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。
在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。

25.尽量避免大事务操作，提高系统并发能力。

26.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。