MySQL+SQL Server速记草稿

MySQL部分

DDL语句
创建数据库song

create database song;

如果不存在创建

create database if not exists song;

指定默认字符集

create database song default charset utf8mb4;

查看所有数据库

show databases;

删除数据库

drop database song;

如果存在则删除

drop database if exists song; 

使用数据库

use song;

查看当前所在库

select database();

查询当前数据库的所有表

show tables;

查询表结构

desc tablename;

查询指定表的建表语句

show create table tablename;

表结构创建

create table tb_user(
	id int comment '序号',
	name varchar(50) comment "名字",
	age int comment "年龄",
	gender char(1) "性别"
) comment '用户表';

一些数据类型

tinyint
smallint
mediumint
int integer
bigint
double
decimal

age tinyint unsigned;--无符号
score double(4,1);--长度4,1个小数
char --定长字符串 性能号
varchar --变长字符串
tinyblob
tinytext
blob
text
mediumblob
mediumtext
longblob
longtext
name char(10);--十个字符
name varchar(10);
date
time
year
datetime
timestamp  --时间戳

创建表

create table emp (
	id int comment '编号',
	wokno varchar(10) comment '工号',
	name varchar(10) comment '姓名',
	gender char(1) comment '性别',
	age tinyint unsigned comment '年龄',
	idcard char(18) comment '身份证号',
	entrydate date comment '入职时间'
) comment '员工表';

修改字段数据类型

 alter table emp modify name char(10);

修改字段名和数据类型

alter table emp change name username varchar(10) comment '用户名';

增加字段

alter table emp add nickname varchar(20) comment '昵称';

删除表中字段

alter table emp drop 字段名;

修改表名

alter table emp rename to tb_emp;

删除表

drop table employee;
drop table if exists employee; 
truncate table employee; --把表删除然后重新创建一张一样的新空表```
添加外键约束

```sql
alter table emp add constraint fk_emp_depart foreign key emp(depart_id) references depart(id);

删除外键约束
alter table emp drop foreign key fk_emp_depart;

DML语句
添加数据

insert into users(name, age, gender) values('小明',18,'男');
insert into users values('小明', 18,'男');
insert into users(name, age, gender), values("小红",19,'女'),('小还',20,'男');
insert into users values('小红',19,'女'),('小还','男');

修改数据

update users set name = '小红';

删除数据

delete from users where id = 1;

DQL语句

select
字段列表
from
表名
where
条件
group by
分组字段列表
having
分组后条件 
order by 
排序字段列表
limit
分页参数

查询多个字段

select 字段1, 字段2, 字段3 from 表名;
select * from 表名; --尽量不要写*号不直观

设置别名

select 字段1， [as 别名], 字段2 [as 别名], from 表名;
--去除重复记录 
select distinct 字段列表 from 表名;

create table emp (
	id int comment '编号',
	workno char(10) comment '工号',
	name varchar(20) comment '姓名',
	gender char(1) comment '性别',
	age tinyint comment '年龄',
	idcard char(18) comment '身份证号',
	workaddress varchar(50) comment '工作地点',
	entrydate date comment '入职时间'
) comment '职工表';
insert into emp(id, workno, name, gender, age, idcard, workaddress, entrydate)
	values(1, '1', '柳岩', '女', 20, '255415263254152365', '北京', '2000-01-01'),
		(2, '2', '张无忌', '男', 18, '415258411258741254', '江苏', '2006-03-01'),
		(3, '3', '韦一笑', '男', 16, '522145230212354625', '南京', '2009-05-01'),
		(4, '4', '赵敏', '女', 21, '856521452365215478', '四川', '2018-06-01'),
		(5, '5', '小昭', '女', 23, '251245123658978546', '上海', '2022-07-01'),
		(6, '6', '杨巧', '男', 26, '201235214521523654', '贵州', '2028-03-26'),
		(7, '7', '小明', '女', 20, '255125415251254125', '北京', '2033-01-01'),
		(8, '8', '小李', '男', 18, '415257811258741254', '江苏', '2034-03-01'),
		(9, '9', '小猪', '男', 16, '522145230872354625', '南京', '2023-05-01'),
		(10, '10', '小红', '女', 21, '856521478365215478', '四川', '2022-06-01'),
		(11, '11', '小花', '女', 23, '251245453658978546', '上海', '2034-07-01'),
		(12, '12', '小迪', '男', 26, '200123021254125632', '贵州', '2014-03-26');

条件查询
比较运算符

, >=, <, <=, =, <>或!=, between…and…, in(…), like_ 或like%, is null
逻辑运算符
and 或 &&, or 或 ||, not 或 !

select id,name, case role when 1 then 'admin' when 2 then 'user' else 'other' end 'role' from users;
select id,name, case when age > 18 then '成年' when age < 18 then '未成年' else 'other' from users;

排序

select * form demand order by see_num desc, user_id asc;
select * from demand limit 3 offset 2; offset等于上一次查询到得总数
--3 0
--3 3
--3 6

select user_id, max(see_num), min(see_num), avg(see_num) from demand group by user_id;
select user_id, count(1) from demand group by user_id having count(1) > 2;

//查询demand表，条件see_num大于2，根据user_id分组,取组中的数量大于2的组，根据see_num降序排序，取第一条数据
select user_id, count(user_id),sum(see_num) from demand where see_num > 2 group by user_id having count(user_id) > 2 order by sum(see_num) desc limit 1;

左右连接
左外连接查询，主表居左，从表居右

主表 left outer join 从表 on 主表.x = 从表.id;

右外连接查询

从表 right outer join 主表表 on 主表.x = 从表.id;

展示需求，以及发布该需求的作者

select * from demand left outer join users on demand.user_id = users.user_id;
select demand.*, users.nickname from demand left outer join users on demand.user_id = users.user_id;

内连接 表 inner join 表 on 条件

select demand.*, users.nickname from demand inner join users on demand.user_id = users.user_id;

上下连接

select user_id, dem_id from collect union select user_id, nickname from users;

sql执行顺序

from
join
on
where
group by 
having
order by
limit

用户管理
查看用户

select user, authentication_string, host from mysql.user;

sql导入

mysql -uroot -p databasename < 文件路径;

导出导出结构和数据

mysqldump -uroot -p databasename > 路径;
导出结构mysqldump -uroot -p -d databasename > 路径;

索引
在数据库中索引的核心作用是：加速查找
在开发过程中会为那些经常会被搜索的列创建索引，以提高程序的响应速度
为什么加上索引之后速度能有这么大的提示呢？因为索引的底层原理是基于B+Tree的数据结构存储的

在创建数据库的时候，如果指定不同的引擎，底层使用的B+Tree结构的原理有些不同
myisame引擎，非聚簇索引（数据和索引结构分开存储）
innodb引擎，聚簇索引（数据和主键索引结构存储在一起）

create table tablename (
) engine=innodb default charset=utf8;

在innodb引擎下，索引底层都是基于B+Tree数据结构存储（聚簇索引），在开发过程中常见的索引类型有：
主键索引：加速查找、不能为空、不能重复。+联合主键索引
唯一索引：加速查找、不能重复。+联合唯一索引
普通索引：加速查找。+联合索引
主键和联合主键索引：

create table 表名(
	id int not null auto_increment primary key,
	name varchar(32) nut null
);
create table 表名(
	id int not null auto_increment,
	name varchar(32) not null,
	primary(id)
);
create table 表名(
	id int not null auto_increment,
	name varchar(32) not null
	primary key(id, name) --如果有多列，称为联合主键（不常用且myisam引擎支持）
);
alter table 表名 add primary key(列名);
alter table 表名 drop primary key;
--注意：删除索引时可能会报错，自增列必须定义为键
alter table 表名 change id id int not null;
--唯一和联合唯一索引：
create table 表名(
	id int not null auto_increment primary key,
	name varchar(32) not null,
	email varchar(64),
	unique ix_name (name),
	unique ix_email(email)
);

create table 表 (
	id int not null auto_increment primary key,
	name varchar(32) not null,
	unique (列1,列2) --如果有多列，称为联合唯一索引
);
create unique index 索引名 on 表名(列名);
drop index 索引名 on 表名;

--普通索引联合索引
create table 表名(
	id int not null auto_increment primary key,
	name varchar(32) not null,
	email varchar(64) not null,
	index ix_email (email),
	index ix_name (name)
);
create table 表名(
	id int not null auto_increment primary key,
	name varchar(32) not null,
	email varchar(64) not null,
	index ix_email(name, email)
);
create index 索引名 on 表名(列名);
drop index 索引名 on 表名;

explain + SQL语句;
其中比较重要的是type，SQL性能比较的重要标志，性能从低到高依次：all < index < range < index_merge < ref_or_null < ref <eq_ref < system/const
ALL，全表扫描，数据表从头到尾找一遍。（一般未命中索引都会执行全表扫描），特别的如果有limit，则找到后就不在继续向下扫描
存储过程
触发器
视图
事务
InnoDB引擎支持事务，mysiam不支持
事务:要成功都成功，要失败都失败
事务的四大特性：原子性（Atomicity）原子性是指事务包含的所以操作不可分割，要么全部成功，要么全部失败回滚；隔离性（Consistency）执行的前后数据的完整性保持一致；隔离性（Isolation）一个事务执行的过程中，不受其他事务的干扰；持久性（Durability）事务一旦结束，数据就持久到数据库

begin;--开启事务
commit;--提交事务
rollback;--回滚

锁
MySQL中自带了锁的功能，可以帮助我们实现开发过程中遇到的同时处理数据的情况，对于数据库中的锁，从锁的范围来讲有：
1、表级锁：即A操作表时，其他人对整个表都不能操作，等A操作完之后，才能继续
2、行级锁：即A操作表时，其他人对指定的行数据不能操作，其他可以操作，等待A操作完之后，才能继续
MYISAM支持表级锁，不支持行级锁，InnoDB支持表级锁和行级锁，即在MYISAM下如果要加锁，无论怎么加都会是表级锁，在InnDB支持下如果是基于索引查询的数据则是则是行级锁，否则就是表锁

数据库连接池

SQLServer部分

一、环境安装
1、数据库引擎安装
2、数据库引擎操作客户端安装

if exists(select * from ysy.databases where name = 'DBTEST')
	drop database DBTEST
--创建数据库
create database DBTEST
on --数据文件
(
	name = 'DBTEST_log', --逻辑名称
	filename = 'D:\DATA\DBTEST.mdf', --物理路径和名称
	size = 5MB, --文件的初始大小
	filerowth = 2MB --文件增长方式，可以大写也可小写，也可写吧百分比
)
log on --日志文件
(
	name = 'DBTEST', --逻辑名称
	filename = 'D:\DATA\DBTEST_log.ldf', --物理路径和名称
	size = 5MB, --文件的初始大小
	filegrowth = 2MB, --文件增长方式，可以大写也可小写，也可写百分比
)
if exists(select * from ysy.databases where name = 'DBTEST')
	drop database DBTEST
--创建数据库
create database DBTEST
on --数据文件
(
	name = 'DBTEST_log', --逻辑名称
	filename = 'D:\DATA\DBTEST.mdf', --物理路径和名称
	size = 5MB, --文件的初始大小
	filerowth = 2MB --文件增长方式，可以大写也可小写，也可写百分比
)
log on --日志文件
(
	name = 'DBTEST', --逻辑名称
	filename = 'D:\DATA\DBTEST_log.ldf', --物理路径和名称
	size = 5MB, --文件的初始大小
	filegrowth = 2MB, --文件增长方式，可以大写也可小写，也可写百分比
)

二、创建表及表的相关操作
1、创建表

if exists(select * from sys.objects where name = 'department' and type = 'U')
	drop table department;
create table department (
	dep_id int not null identity(1, 1) primary key,
	dep_name varchar(50) not null,
	dep_remark text
);
--nvarchar(100); char,varchar,text前面加n:存储unicode字符，对中文友好
create table [rank](
	r_id int not null identity(1, 1) primary key,
	r_name nvarchar(50) not null,
	r_remark text
);
create table people(
	peo_id int not null identity(1, 1) primary key,
	dep_id int references department(dep_id) not null, --添加外键约束
	r_id int references [rank](r_id) not null,
	peo_name nvarchar(50) not null,
	p_sex nvarchar(1) default('男') check(p_sex='男' or peo_sex='女') not null,
	p_brith datetime not null, --smalldatetime
	--sqlserver中没有double，有float，但是float有精度问题
	p_salary decimal(12, 2) check(p_salary >= 1000 and p_salary <= 1000000) not null,
	p_phone varchar(11) unique not null, --unique唯一约束
	p_address varchar(200),
	p_addTime smalldatetime default(getdate()) --默认添加当前时间
)

2、修改表结构
(1)删除表中字段

alter table table_name drop colum column_name; -- 删除table_name中的colum_name字段

(2)修改表中字段数据类型

alter table table_name alter colum colum_name datatype; --修改table_name中colum_name字段数据类型为datatype

(3)添加字段

alter table table_name add colum_name varchar(20);

3、维护约束
(1)删除约束

alter table 表名 drop contraint 约束名;

(2)添加约束

alter table 表名 add constraint 约束名称 check(表达式); --如:alter table add constraint CK_people_p_saly check(p_salary >= 1000 and p_salary <= 1000000);
alter table 表名 add constraint 约束名 primary key(列名); --添加主键约束
alter table 表名 add constraint 约束名 unique(列名) --添加唯一约束
alter table 表明 add constraint 约束名 default '默认值' for 列名 --添加默认值约束
alter table 表名 add constraint 约束名 foreign key(列名) references

三、示例数据库

create table accountInfo --账户信息表
(
	accountid int primary key identity(1,1),--账户编号
	accountcode char(18) not null,--身份证
	accountphone char(11) not null,--电话
	accountname varchar(20) not null,--姓名
	opendate smalldatetime not null--开户时间
)
create table bankcard --银行卡
(
	cardno char(30) primary key,--卡号
	cardpwd char(6) not null,--密码
	cardmoney money not null,--余额
	cardstate int not null,--1正常，2挂失，3冻结，4注销
	carddate smalldatetime default(getdate()),--开卡时间
	accountid int not null--账户编号（账户信息表）
)
create table cardexchange --交易信息表
(
	exchangeid int primary key identity(1,1),--交易编号
	moneyoutbank money not null,--取钱金额
	moneyinbank money not null,--存钱金额
	exchangedate smalldatetime not null, 交易时间
	cardno char(30) not null--银行卡号
)
create table cardtransfer --转账信息表
(
	transferid int primary key identity(1,1), --编号
	cardnoout char(30) not null,--转出卡号
	cardnoin char(30) not null,--转入卡号
	transfermoney money not null,--交易金额
	transferdate smalldatetime not null--交易时间
)
create table cardstatechange --银行卡状态信息变红表
(
	stateid int primary key identity(1,1),--编号
	cardno char(30) not null,--卡号
	oldstate int not null,--原始状态
	newstate int not null,--新状态
	statewhy varchar(200) not null, --状态变化原因
	statedate smalldatetime not null--记录产生时间
)
insert into accountinfo(accountcode, accountphone,accountname,opendate)
('522345666709898787','18273890989','郭小军',GETDATE()),
('533453199709098989','19387876745','刘一库',GETDATE()),
('388987199920989878','18273333989','张敏敏',GETDATE()),
('599878766789801234','18239870989','米兰',GETDATE())

insert into bankcard(cardno,accountid,cardpwd,cardmoney,cardstate)
values('2989878980987676',1,'123456',0,1)
insert into bankcard(cardno,accountid,cardpwd,cardmoney,cardstate)
values('9938293849987676',2,'123456',0,1)
insert into bankcard(cardno,accountid,cardpwd,cardmoney,cardstate)
values('9829348392234344',3,'123456',0,1)

四、数据操作语句
1、插入数据语句

insert into department(dep_name, dep_rank) values('hhh', 'xxx');
insert into department(dep_name, dep_rank) values('',''),('','');
--一次性插入多条数据
insert into department(dep_name, dep_rank)
select '111','xxx' union
select '222','xxx' union
select '333','xxx' 

2、修改和删除数据
(1)修改

update 表名 set 字段1 = 值1, 字段2 = 值2 where 条件 --修改
update people set peoplesalary = peoplesalary + 1000;
update people set peoplesalary = peoplesalary + 500 where p_id = 7;

(2)删除

delete from 表名 where 条件 --删除

(3)关于删除(drop, truncate, delete)

drop table 表名 --删除表对象
truncate table 表名 --清空数据,表对象即表结构依然存在
delete from 表名 --删除所有数据,表对象即表结构依然存在
--truncate 和 delete 的区别：truncate清空所有数据，不能有条件，delete可以删除所有数据，也可以带条件删除,truncate 原理是删除原表在创建一张和原表结构相同的空表

4、基础查询

select * from 表名 where 条件
select distinct (字段名) from 表名 where 条件 --去重

5、条件查询
SQL中常用的运算符

大于
< 小于
= 等于
!= 不等于
= 大于等于
<= 小于等于
IS NULL 比较为空
IS NOT NULL 比较为不空
in 比较是否在其中
like 模糊查询
between value1 and value2 比较是否在value1,value2两者之间
and 逻辑与
or 逻辑或
not 逻辑非

6、排序order by

select * from 表名 order by 字段名 desc --降序排序, 不写默认升序排序ASC
DATEDIFF(YEAR,出生日期,GETDATE()) --计算年龄
YEAR(getdate()) - YEAR(出生日期); --计算年龄
DATEDIFF(day,date1,date2) --计算两个日期间天数
YEAR(日期) --取年
MONTH(日期) --取月
DAY(日期) --取天

–查询工资比zaoyun高的人

select * from people where p_salary > 
(select p_salary from people where p_name = 'zaoyun')
--查询和zaoyun在同一个城市的人
select * from people where p_address = 
(select p_address from people where p_name = 'zaoyun')

7、模糊查询like
模糊查询使用like关键字和通配符结合来实现，通配符具体含义如下
% 代表匹配0个字符、1个字符、或多个字符
_ 代表匹配有且只有一个字符
[] 代表匹配范围内
[^] 代表匹配不在范围内

select * from dep where name like '刘%' --查询姓刘的人员
select * from dep where name like '%尚%' --查询名字包含尚字人员
select * from dep where name like '%尚%' or name like '%史%' --名字含尚和史
select * from dep where name like '刘_' --查询姓刘的人员名字是2个字
SUBSTRING('hello,world',3,1) --截取字符
LEN(String) --计算字符段长度
select * from dep where SUBSTRING(name, 1,1) = '刘' and len(name) = 2
--查询名字最后一个字为香，名字为3个字的人员
select * from dep where name like '__香' --方法一
select * from dep where SUBSTRING(name, 3,1) = '香' and LEN(name) = 3--方法二
--查询电话开头是138的人员
select * from dep where phon like '138%'
--查询所有电话开头是138的，第四位好像是7或者8，最后一位是5的人员
select * from dep where phon like '138[7,8]%5'
--查询所有电话开头是138的，第四位好像是2-5之间，最后一个号码不是2和3
select * from dep where phon like '138[2,3,4,5]%[^2,3]'
select * from dep where phon like '138[2-5]%[^2-3]'

8、group by 分组查询

--根据员工所在地区分组统计员人数，员工工资总和，平均工资，最高工资和最低工资
方案一：使用union关键字
select 
'address' as 地址, 
count(*) as 人数,
sum(p_salary) as 工资总和,
avg(p_salary) as 平均工资,
max(p_salary) as 最高工资,
min(p_salary) as 最低工资
from people
where address = '北京'
union
select 
'贵州' as 地址, 
count(*) as 人数,
sum(p_salary) as 工资总和,
avg(p_salary) as 平均工资,
max(p_salary) as 最高工资,
min(p_salary) as 最低工资
from people
where address = '贵州'

--方案二：group by分组查询:注意，group by查询配合聚合函数使用，只能选择聚合函数查询的结果，或group by 后面的字段
select 
address as 地区,
count(*) as 人数,
sum(p_salary) as 工资总和,
avg(p_salary) as 平均工资,
max(p_salary) as 最高工资,
min(p_salary) as 最低工资
from people
group by address
--根据员工所在地区分组统计员人数，员工工资总和，平均工资，最高工资和最低工资1985年出生及以后的人员不参与统计
select 
address as 地区,
count(*) as 人数,
sum(p_salary) as 工资总和,
avg(p_salary) as 平均工资,
max(p_salary) as 最高工资,
min(p_salary) as 最低工资
from people
where p_brith < '1985-01-01'
group by address

--根据员工所在地区分组统计员人数，员工工资总和，平均工资，最高工资和最低工资1985年出生及以后的人员不参与统计,只查询至少有两个人以上的
--普通条件使用where关键字(写在group by 的前面),聚合函数结果条件使用having关键字(写在group by 后面)
select 
address as 地区,
count(*) as 人数,
sum(p_salary) as 工资总和,
avg(p_salary) as 平均工资,
max(p_salary) as 最高工资,
min(p_salary) as 最低工资
from people
where p_brith < '1985-01-01'
group by address having count(*) >= 2

9、多表查询
(1)笛卡尔乘积

select * from table1,table2 --查询结果将table1和table2所有记录依次排列组合成新的结果

(2)简单多条查询

select * from table1, table2 where table1.id = table2.tab1_id;
select * from table1, table2, table3 where table1.id = table2.tab1_id and table1.id = table3.tab1_id;

10、内连接查询

select * from table1 inner join table2 on table1.id = table2t.tab1_id
select *
from table1 
inner join table2 on table1.id = table2.tab1_id
inner join table3 on table1.id = table3.tab1_id
--简单多表查询和内连接查询共同特点：不符合主外键关系的数据不会查询出来

11、外链接查询(左外连接、右外连接、全外联)
(1)左外连接：以左表关系为主进行数据查询，主外键关系找不到的数据为null

select * from people left outer join department on people.id = department.dep_id

(2)右连：A left join B = B right join A
(3)全外联：两张表的数据，无论是否符合关系都要显示

select * from people full join department on people.id = department.dep_id


--根据部门分组统计人数,男,工资总和，平均工资，最高工资，最低工资，平均工资10000一下不参与统计，根据平均工资降序排序
select 
dep_name as 部门名称, 
count(*) as 人数,
sum(p_salary) as 工资总和,
avg(p_salary) as 平均工资,
max(p_salary) as 最高工资,
min(p_salary) as 最低工资
from people inner join dep on people.id = dep.peo_id
where sex = '男'
group by dep.id,depname
having avg(salary) >= 10000
order by avg(salary) desc
--根据部门名称，然后根据职级名称分组统计人员数、工资总和、平均工资、最高工资、最低工资
select 
depname as 部门名称,
rankname as 职级名称,
sum(salary) as 工资总和,
avg(salary) as 平均工资,
max(salary) as 最高工资,
min(Salary) as 最低工资
from people
inner join dep on dep.peo_id = people.id
inner join rank on rank.peo_id = people.id 
group by people.rank_id = rank.id, rankname

(4)自连接（自己连自己）

create table dept(
	dep_id int primary key, 
	dep_name varchar(50),
	fa_id int --上级部门
)
select * fromdept as a
inner join dept as b on a.fa_id = b.dep_id

12、子查询
(1)查询余额比卡号’2222222222222222’多的银行卡信息，显示卡号，身份证，姓名，余额

select cardno,accountcode,accountname,cardmoney from bankcard inner join accountinfo on bankcard.accounid=accountinfo.accountid
where cardmoney > 
(
	select cardmoney from bankcard where cardno='2222222222222222'
)

(2)从所用账户信息中查询出余额最高的交易明细(存钱取钱信息)

--方案一:如果有并列最高该语句不适用
select * from cardexchange where carno = 
(
	select top 1 cardno from bankcard order by cardmoney desc
) 
--方案二
select * from cardexchange where carno in
(
	select cardno from bankcard where cardmoney = (select max(cardmoney) from bankcard)
)

(3)、查询有取款记录的银行卡及账户信息，显示卡号，身份证，姓名，余额

select cardno,accountcode accountname,cardmoney from bankcard 
iner join accountinfo on bankcard.accountid=accountinfo.accountid
where cardno in
(
	select cardno from cardexchange where moneyoutbrank > 0
)

(4)查询出没有存款记录的银行卡及账户信息，显示卡号，身份证号，姓名，余额

select cardno,accountcode,accountname,cardmoney from bankcard
inner join accountinfo on bankcard.accountid=accountinfo.accountid
where cardno not in
(
	select cardno from cardexchange where moneyoutbrank>0
)

(5)查询卡号为’3333333333333333’当天是否有收到转账

if exists (select * from cardtransfer where cardno='33...33' and convert(varchar(22),GETDATE(),23)=convert(varchar(22),transferdate,23))
	begin
		print '有收到转账'
	end
else
	begin
		print '没有收到转账'
	end

(6)查询出交易次数(存款取款操作)最多的银行卡账户信息

--方案一(不能查出并列最多的)
select top 1 temp.counts,countbankcard.cardno,accountcode,accountname,cardmoney from bankcard
inner join accountinfo on brankcard.accountid = accountinfo.accountid 
inner join (select cardno, count(*) as counts from cardexchange group by cardno) as temp on temp.cardno=bankcard.cardno
order by temp.counts desc
--方案二
select temp.counts,countbankcard.cardno,accountcode,accountname,cardmoney from bankcard
inner join accountinfo on brankcard.accountid = accountinfo.accountid 
inner join (select cardno, count(*) as counts from cardexchange group by cardno) as temp on temp.cardno=bankcard.cardno
where temp.counts=(select max(count(*)) as counts from cardexchange group by cardno))

(7)查询出没有转账交易记录的银行卡账户信息，显示卡号，身份证，姓名，余额

select cardno,accountcode,accountname,cardmoney from brankcard
inner join accountinfo.accountid=bankcard.accountid
where cardno not in (select cardnoin from cardtransfer) and cardno not in (select cardnoout from cardtransfer)

五、聚合函数

count(*)
max(字段名)
sum(字段名)
avg(字段名)
round(字段名,2) -- 四舍五入小数保留位数

六、SQLServer信息打印、变量、go语句、运算符、
1、信息打印

print 'hello' --在消息窗口打印
select 'hello,sql' --在结果窗口打印

2、变量

--变量:(1)局部变量；(2)全局变量
--(1)局部变量:以@开头，先声明，再赋值
declare @str varchar(20)
set @str = 'hello sql' --赋值方法1
--select @str = 'hello sql' --赋值方法2
--set和select赋值的区别，set:赋值变量指定的值，select:一般用于表中查询出的数据赋值给变量，查询结果有多条，取最后一条赋值
--exp:select @a = 字段名 from 表名 
print @str

–(2)全局变量:以@@开头，由系统进行定义和维护

@@INENTITY --返回最后插入的标识值
 
insert into accountinfo(accountcode, accountphone,accountname,opendate)
('522345666709387845','12345990989','李英',GETDATE())
declare @accountid int 
set @accountid = @@IDENTITY
insert into bankcard(cardno,accountid,cardpwd,cardmoney,cardstate)
values('2989878980987676',@accountid,'123456',0,1)

select * from accountinfo
select * from bankcard
declare @accountid int
select @accountid = 
(select accountid from accountinfo where accountcode='988767677876734521')
select cardno, cardmoney from bankcard where accounid = @accountid

3、go语句

--(1)等待go语句之前的代码执行完成才能执行后面的代码
--(2)批处理结束的一个标志
declare @num int
set @num = 100
set @num = 200
go
declare @num1 int --作用范围在两个go之间
set @num1 = 100
go
set @num1 = 200 --报错

4、SQL中使用的运算符

算术运算符：+，-，*，/，%
逻辑运算符：and,or,like,between,in,exists,not,all,any
赋值运算符：=
字符串运算符：+
比较运算符：=,>,>=,<,<=,<>
位运算符: |,&,^
复合运算符：+=,-+,/+,%=,*=
--运算示例
declare @c int = 5
declare @k int = 10
declare @zc int
declare @mj int
set @zc = (@c + @k)*2
set @mj = @c * @k
print 'zc=' + cast(@zc as varchar(10))
print 'mj' + Convert(varchar(10),@mj) 
--Convert(varchar(10),num)转换为字符串
--cast(@zc as varchar(10))
--身份证:344545444567898789,开户，查询身份证在账户表中是否存在，不存在则进行开户开卡，存在则不开户直接开卡
declare @accountid int
if exists (select * from accountinfo where accountcode = '34454445767898789')
	begin
		select @accountid = 
		(select accoundid from accountinfo where accountcode='34454445767898789')
		insert into bankcard(cardno,accountid,cardpwd,cardmoney,cardstate) 
		values('3449809897876767',@accountid,'123456',0,1)
	end
else
	begin
		insert into accountinfo(accountcode,accountphone,accountname,opendate)
		values('344567988787434543','12345434579','剑兰天',getdate())
		set @accountid = @@IDENTITY
		insert into bankcard(cardno,accountid,cardpwd,cardmoney,cardstate) 
		values('344980998970979667',@accountid,'123456',0,1)
	end
	
--先查询是否开户，如果开户查询是否大于3张，如果大于3张提示不能开卡,如果小于3张，则在bankcard中开卡;如果没有开户，则先开户(在accountinfo中添加用户)在开卡(在bankcard中添加卡)
declare @accountid int
declare @cardcount int
if exists (select * from accountinfo where accountcode = '34454445767898789')
	begin
		select @accountid = 
		(select accoundid from accountinfo where accountcode='34454445767898789')
		select @cardcount = 
		(select count(*) from bankcard where accountid = @accountid)
		if @cartcount <= 2
			begin
				insert into bankcard(cardno,accountid,cardpwd,cardmoney,cardstate) 
				values('3449809897876767',@accountid,'123456',0,1)
			end
		else
			begin
				print '最多只能有三张银行卡'
			end
	end
else
	begin
		insert into accountinfo(accountcode,accountphone,accountname,opendate)
		values('344567988787434543','12345434579','剑兰天',getdate())
		set @accountid = @@IDENTITY
		insert into bankcard(cardno,accountid,cardpwd,cardmoney,cardstate) 
		values('344980998970979667',@accountid,'123456',0,1)
	end
	
--查询银行卡账户余额，是不是所有的账户都超过了3000
if 3000 < ALL(select cardmoney from bankcard) --数字写在 < 前面 ALL:所有数据都必须满足 3000<
	begin
		print '所有的银行卡余额都超过了3000'
	end
else
	begin
		print '不是所有的银行卡余额都超过3000'
	end
--查询银行卡账户余额，是否含有账户余额超过30000的信息
if 30000 < ANY(select cardmoney from bankcard) --ANY:只要有一个查过30000条件就成立
	begin
		print '有cardmoney>30000的'
	end
else
	begin
		print '没有cardmoney>30000的'
	end

七、流程控制
1、选择分支结构
(1)case…when … then … else … end 语句

--写法1
select 
case 
	when id % 2 = 10 then '十' 
	when id % 2 = 9 then '九' else '八' 
end '转换' 
from 表名
--写法2
select 
case id % 2 
	when 10 then '十' 
	when 9 then '九' else '八' 
end '转换' 
from 表名

(2) if…else

--某用户银行卡号为'9999999999999999',该用户执行取钱操作，取500元，余额充足则进行取钱操作，并提示成功，否则提示余额不足
declare @balance money
select @balance = (select cardmoney from bankcard where cardno = '9999999999999999')
if @balance >= 500
	begin
		update bankcard set cardmoney = cardmoney - 500 where cardno = '99999999999999999'
		insert into cardexchange(cardno,moneyinbank,moneyoutbank,exchangedate) 
		values('999999999999999',0,500,getdate())
		print '取钱成功'
	end
else
	begin
		print '余额不足'
	end

2、循环结构
(1)循环打印案例

declare @i int = 1
while @i <= 10
	begin
		print @i
		set @i += 1
	end
--案例打印乘法表
--char(9)制表符
--char(10)换行符
declare @i int = 1
declare @j int = 1
declare @str varchar(1000) = ''
while @i <= 9
	begin
		while @j <= @i
			begin
				set @str += Convert(varchar(1), @i) + ' x ' + Convert(varchar(1), @j) + ' = ' + Convert(varchar(2),@i * @j) + char(9)
				set @j += 1
			end
		print @str
		set @i += 1
	end

八、分页查询
1、top方式分页(数据量大时查询效率不高)

--每页10条数据
--查询第一页
select top 5 * from tablename
--查询第二页
select top 5 * from tablename
where tableid not in
(
	select top 5 tableid from tablename
)
--每页m条数据,第n页
select top m * from tablename
where tableid not in
(
	select top m*(n-1) tableid from tablename
)
--如:
declare @pagesize int = 5
declare @pageindex int = 1
select top(@pagesize) * from tablename
where tableid not in (select top(@pagesize * (@pageindex - 1)) from student)

2、使用row_number分页

--每页m条数据,第n页
select * from 
(select ROW_NUMBER() over(order by tableid) as rowid,* from tablename) as temp
where temp.rowid between (n-1)*m+1 and n*m

九、事务

--如A账户有1000元,A取款6000元,取款失败,但是向取款记录表中添加了一条取款信息,通过事务管理解决,要么同事成功，要么同时失败
@@error --每执行一行代码全局变量保存sql出错代码编号
begin transaction --开启事务
	declare @myError int = 0
	update bankcard set cardmoney=cardmoney-6000 where cardno='44...44'
	set @myError += @@ERROR
	@@error
	insert into cardexchange(cardno,moneyinbank,moneyoutbank,exchangdate)
	values cardexchange('44...44',0,6000,GETDATE())
	set @myError += @@ERROR
	if @myError = 0
		begin 
			commit transaction
			print '成功'
		end
	else
		begin
			rollback transaction
			print '失败'
		end
--假设A向B转账1000元,(添加check约束,设置账户余额必须>=0),(1)A扣除1000元,(2)B添加1000元,(3)生成转账记录,(4)使用事务管理
begin transaction
	declare @myError int = 0
	update bankcard set cardmoney=cardmoney-1000 where cardno='A'
	set @myError += @@ERROR
	update bankcard set cardmoney = cardmoney+1000 where cardno='B'
	set @myError += @@ERROR
	insert into cardtransfer(cardnout,cardnoin,transfermoney,transferdate)
	values(A,B,1000,GETDATE())
	set @myError += @@ERROR
	if @myError = 0
		begin
			commit transaction
			print '成功'
		end
	else
		begin
			rollback transaction
			print '失败'
		end

十、索引
索引:提高检索查询效率
1、SQL SERVER索引类型
(1)聚集索引(又称聚类索引，簇集索引)
聚集索引：根据数据行的键值在表或视图中的排序存储这些数据行，每一个表有一个聚集索引。聚集索引是一种对硬盘上实际数据重新组织以按指定的一列或多列值排序（类似字典中的拼音索引）(物理存储排序)
(2)非聚集索引(非聚类索引,非聚集索引)
非聚集索引：具有独立于数据行的结构，包含非聚集索引键值，且每个键值项都有指向的包含该键值的数据行的指针（类似字典中的偏旁部首索引）（逻辑存储顺序）
(3)SQL SERVER中索引其他分类
按数据唯一性区分：“唯一索引”,“非唯一索引”、按列个数区分：“单列索引”,“多列索引”

2、创建索引的方式
(1)通过显示的 create index 命令
(2)在创建约束时作为隐含的对象
主键约束（聚集索引）
唯一约束（唯一索引）
3、创建索引语法

create [unique] [clustered] [nonclustered] --唯一，聚集，非聚集
index <index 索引名> on <表名或视图名><字段名> [asc|desc][...n]
exp:给accountinfo表中accountcode添加索引
create unique nonclustered index index_code
on accountinfo(accountcode)
with
(
	...
	...
	...
)
--查询时显示说明要用索引(先创建再使用)
select * from accountinfo with(index=index_code)  where accountcode = '6234..12'

3、索引查看(sys.indexes)

select * from sys.indexes where name = 'index_code'

5、删除索引

drop index index_code on accountinfo

十一、视图
(1)创建视图

--视图:可以理解成虚拟表，提供的功能单一，就是方便查询
--显示卡号，身份证号，姓名，余额
select cardno,accountcode,accountname,cardmoney from bankcard
inner join accountinfo on bankcard.accountid=accountinfo.accountid
--创建视图，实现显示卡号，身份证，姓名，余额
create view view_account_card
as
select cardno,accountcode,accountname,cardmoney from bankcard
inner join accountinfo on bankcard.accountid=accountinfo.accountid
go
--使用
select * from view_account_card

(2)删除视图

drop view viewname

(3)注意
视图是虚拟表，SQLSERVER提供了在该表上作增、删、改、查操作功能,但是在实际应用场景一般都不会使用这些功能
十二、游标

十三、函数
1、编写一个函数求银行的金额总和(没有参数，返回标量值)

create function getSumMoney() returns money
as
begin
	declare @sum money
	select @sum = (select sum(cardmoney) from bankcard)
	return @sum
end
--调用
select dbo.getSumMoney()

2、传入用户编号，返回账户姓名

create function getAccName(@accid int) returns varchar(30)
as
begin
	select declare @accname=(select accname from accountinfo where accid=@accid)
	return @accname
end
--调用
select dbo.getAccId(2)

3、传递开始时间和结束时间，返回交易记录（存钱取钱）交易记录中包含姓名，卡号，存钱金额，取钱金额，交易时间

create function getRecordByDate(@start varchar(30),@end varchar(30))
returns @result table
(
	accountname varchar(20),
	cardno varchar(30),
	moneyinbank money,
	moneyoutbank money,
	exchangedate smalldatetime
)
as
begin
	select accountinfo.accountname,bankcard.cardno,moneyinbank,moneyoutbank,exchangedate from cardexchange
	inner join  bankcard on cardexchange.cardno=bankcard.cardno
	inner join accountinfo on bankcard.accountid=accountinfo.accountid
	where exchangedate between @start + '00:00:00' and @end + '23:59:59'
	return
end
go
--调用
select * from getRecordByDate('2020-1-1','2020-3-4')
--方案二(函数体内只能有return+sql查询结果)
create function getRecordByDate(@start varchar(30),@end varchar(30))
return table
as
return
	select accountinfo.accountname,bankcard.cardno,moneyinbank,moneyoutbank,exchangedate from cardexchange
	inner join  bankcard on cardexchange.cardno=bankcard.cardno
	inner join accountinfo on bankcard.accountid=accountinfo.accountid
	where exchangedate between @start + '00:00:00' and @end + '23:59:59'
go
--删除函数
drop function 函数名

4、SQL SERVER中内置函数

DATEDIFF (datepart, startdate, enddate)

参数说明：
datepart：需要计算的日期部分(年、月、日、小时、分钟、秒等）
startdate：起始日期
enddate：结束日期
datepart参数值 含义
year or yy,yyyy 计算日期之间整年的差异
quarter or qq,q 计算日期之间整季度的差异
month or mm,m 计算日期之间整月的差异
dayofyear or dy,y 计算日期之间整年的天数差异
day or dd,d 计算日期之间整天的差异
week or wek,ww 计算日期之间整周的差异
hour or hh 计算日期之间整小时的差异
minute or mi n 计算日期之间整分钟的差异
second or ms 计算日期之间整秒的差异

--求年龄函数
create function getAge(@birth varchar(30),@nowDate varchar(30))
returns varchar(10)
as
begin
	declare @age varchar(10)
	if DATEDIFF(year, @birth, @nowDate) <> 0
		begin
			set @age = CONVERT(varchar(10),DATEDIFF(year, @birth, @nowDate)) + '岁'
		end
	else if DATEDIFF(day, @birth, @nowDate) <> 0 
		begin
			set @age = CONVERT(varchar(10),DATEDIFF(day, @birth, @nowDate)) + '天'
		end
	else if DATEDIFF(hour, @birth, @nowDate) <> 0
		begin
			set @age = CONVERT(varchar(10),DATEDIFF(hour, @birth, @nowDate)) + '小时'
		end
	else 
		begin
			set @age = CONVERT(varchar(10),DATEDIFF(MINUTE, @birth, @nowDate)) + '分钟'
		end
	return @age
end
go
--判断是否是统一个日期函数,是返回1，否则返回0
create function isDay(@date1 varchar(30),@date2 varchar(30)) returns int
as
begin
	declare @datestr1 varchar(30) = ''
	declare @datestr2 varchar(30) = ''
	declare @y1 varchar(30) = year(@date1)
	declare @m1 varchar(30) = month(@date1)
	declare @d1 varchar(30) = day(@date1)
	declare @y2 varchar(30) = year(@date2)
	declare @m2 varchar(30) = month(@date2)
	declare @d2 varchar(30) = day(@date2)
	set @datestr1 = @y1 + @m1 + @d1
	set @datestr2 = @y2 + @m2 + @d2
	if @datestr1 = @datestr2
		return 1
	return 0
end
go

十四、触发器(在项目中谨慎使用，少用)
触发器分类：instead of 触发器、after触发器
1、 instead of 触发器(事前触发器)
在执行操作之前被执行
2、after触发器(事后触发器)
在执行操作之后被执行
(1)假设有部门表和员工表，在添加员工的时候，该员工的部门编号如果在部门表中找不到，则自动添加部门信息，部门名称为新部门

create trigger tri_insertPeople on people after insert --在添加插入数据之后出发
as
--if exists(select * depaertment where dep_id = 添加员工时的部门编号)
if not exists(select * from depaertment where dep_id = (select departmentid from inserted))
	begin
		insert into department(departmentid,departmentname) values(select departmentid from inserted,'新部门')
	end
go

(2)触发器实现删除一个部门的时候将部门下的所有员工全部删除

create trigger tri_deletedept on department after delete
as
delete from people where dep_id = (select dep_id from deleted)
go

(3)创建一个出发器，删除一个部门的时候判断该部门下是否有员工，有则不删除，没有则删除

create trigger deletedept on department instead of deleted --事前触发
as
if not exists(select * from people where dep_id = (select dep_id from deleted))
	delete from department where dep_id = (select dep_id from deleted)
go

(4)修改一个部门编号之后，将该部门下所有员工的部门编号同步进行修改

create trigger tri_updatedep on department after updated
as
update people set dep_id = (select dep_id from inserted)
where dep_id = (select dep_id from deleted)
go

十五、存储过程
存储过程(procedure)是SQL语句和流程控制语句的预编译集合
1、没有输入参数，没有输出参数的存储过程
–定义存储过程实现查询出账户余额最低的银行卡账户信息，显示银行卡号，姓名，账户余额

create proc proc_minmoneycard
as
select top 1 cardno,accountname,cardmoney from bankcard 
inner join account on bankcard.accountid=accountinfo.accountid
order by cardmoney
go
--方案二
create proc proc_minmoneycard
as
select cardno,accountname,cardmoney from bankcard 
inner join account on bankcard.accountid=accountinfo.accountid
where cardmoney = 
(select min(cardmoney) from bankcard)
go
--下次再使用直接调用: exec proc_minmoneycard

2、有输入参数，没有输出参数的存储过程
--模拟银行卡存钱操作，传入银行卡号，存钱金额，实现存钱操作
create proc proc_saving
@cardno varchar(30)
@money money
as
update bankcard set cardmoney = cardmoney +  @money 
where cardno = @cardno
insert into cardexchange(cardno,moneyinbank,moneyoutbank,exchangedate)
values(@cardno,@money,0,getdate())
go
-- 调用：exec proc_saving '333333',1000

3、有输入参数，没有参数，但是有返回值的存储过程（返回值必须是整数）

--模拟银行卡取钱操作，传入银行卡，取钱金额，实现取钱操作，取钱成功，返回1，取钱失败返回-1
create proc proc_outmoney
@cardno varchar(30)
@money money
as
update bankcard set cardmoney=cardmoney-@money
where cardno=@cardno
if @@ERROR <> 0
	return -1
insert into cardexchange(cardno,moneyinbank,moneyoutbank,exchangdate)
values(@cardno,0,@money,getdate())
return 1
go
--调用：declare @returnvalue int exec pro_outmoney '44444', 1000 

4、有输入参数，有输出参数的存储过程

--查询出某时间段的银行卡存取款信息以及存款总金额，取款总金额，传入开始时间，结束时间，显示存取款交易信息的同时，返回存款总金额，取款总金额
create proc proc_query
@startdate varchar(30)
@enddate varchar(30)
@sunin money output
@sumout money output
as
select @sumin = (select sum(moneyinbank) from cardexchange where exchangedate between @stardate +'00:00:00' and @enddate + '23:59:59')
select @sumout = (select sum(moneyoutbank) from cardexchange where exchangedate between @stardate +'00:00:00' and @enddate + '23:59:59')
select * from cardexchange where exchangedate between @stardate +'00:00:00' and @enddate + '23:59:59'
go
--调用：
--declare @sumin
--declare @sumout
--exec proc_query '2020-1-1','2021-1-1',@sumin output,@sumout output

5、具有同时输入输出参数的存储过程

--密码升级，传入卡号和密码，如果用户名密码正确，并且密码长度<8，自动升级成8位密码
floor(10) -- 0-9之间随机整数
create proc proc_pwdupgrade
@cardno varchar(30)
@pwd nvarchar(20) output --output是否又输入又输出关键看调用的地方是否赋值
as
if not exists(select * from bandcard where cardno=@cardno and cardpwd=@pwd)
	set pwd=''
else
	begin
		if len(@pwd) < 8
			begin
				declare @len int = 8-len(@pwd)
				declare @i int = 1
				while @i <= @len
					begin
						set @pwd += convert(varchar(1),foor(rand()*10)
						set @i = @i+1
					end
					update bankcard set cardpwd=@pwd where cardno=@cardno
			end
	end
go
--调用：
--declare @pwd nvarchar(20) = '123456'
--exec proc_pwdupgade '55555555',@pwd output