目录
一、SQLserver
1.建表和查询
【建表语法】
--不需要设置主键
CREATE TABLE TestTable
(
ID NVARCHAR(50),
Name VARCHAR(50),
Age INT,
Content VARCHAR(255),
IsEnable INT,
IsDeleted INT,
Stage VARCHAR(50),
Remarks VARCHAR(100)
);
--设置主键
CREATE TABLE TestTable
(
ID NVARCHAR(50) PRIMARY KEY,
Name VARCHAR(50),
Age INT,
Content VARCHAR(255),
IsEnable INT,
IsDeleted INT,
Stage VARCHAR(50),
Remarks VARCHAR(100)
);
【设置主键语法】
--先设置非空
ALTER TABLE TestTable ALTER COLUMN ID NVARCHAR(50) NOT NULL;
--再设置主键
ALTER TABLE TestTable ADD CONSTRAINT PK_TestTable PRIMARY KEY (ID);
2.增删改查和联表基本语法
-- 1.插入数据到表中,列名和值需要一一对应
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...),(value1, value2, value3, ...);
-- 2.从表中删除满足条件的数据
DELETE FROM table_name WHERE condition;
-- 3.更新表中满足条件的数据
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
-- 4.从表中查询满足条件的列
SELECT column1, column2, ... FROM table_name WHERE condition;
--5.从sc表中查询所有列,条件是cno在'c1', 'c2'之间,sal在1000和1500之间,score大于等于84,t是NULL,b不是NULL,时间在'2022-6-12 11:44:00'和'2022-6-12 11:45:59'之间
select * from sc where cno in('c1', 'c2') and (sal between 1000 and 1500) and score>=84 and t is NULL and b is not NULL and time between '2022-6-12 11:44:00' and '2022-6-12 11:45:59';
--6.模糊匹配
select tno,tn from t where tn like '张%'; -- 表示匹配张%的数据,%的长度(0,∞)。
select tno,tn from t where tn like '张_'; -- 表示匹配张_的数据,_的长度是1。
select tno,tn from t where tn like '%张%'; -- 表示匹配含'张'的数据
--7.联表查询
SELECT Student.stu_id, Student.stu_name, Score.score FROM Student LEFT JOIN Score ON Student.stu_id = Score.stu_id;--左外连接:完整左表+匹配的右表字段(可能出现null)
SELECT Student.stu_id, Student.stu_name, Score.score FROM Student INNER JOIN Score ON Student.stu_id = Score.stu_id;--内连接:只返回匹配行(不会出现null)
快速生成增删改查语句:右键某表点击编写表脚本-选择对应语法-新查询编辑器窗口(select语法可直接点击选择前1000行生成)
增加字段
IF NOT EXISTS (SELECT * FROM sys.columns WHERE Name = N'KeyContent' AND Object_ID = Object_ID(N'T_BU_KeyTime'))--对指定的数据表增加字段
BEGIN
ALTER TABLE T_BU_KeyTime
ADD KeyContent VARCHAR(MAX) NULL --字段类型
END;
3.定义变量和使用变量
---- 定义变量并赋值
DECLARE @ProjectID Varchar(50) = 'E963F8E2-4713-4B7C-A154-CC4ED75A0CAA';
DECLARE @IsDeleted int = 0 ;
---- 使用变量查询数据
SELECT * FROM T_BU_ProjectManDay WHERE ProjectID = @ProjectID AND IsDeleted=@IsDeleted;
4.字段和表名
【根据字段查表名】
-- 注意左上角要选择对应的数据表查询
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'colcunmname' AND TABLE_SCHEMA = 'dbo';
【求两个字段都出现在哪些表中】
- 求并集关键字:UNION
- 求交集关键字:INTERSECT
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = '字段A' AND TABLE_SCHEMA = 'dbo'
INTERSECT
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = '字段B' AND TABLE_SCHEMA = 'dbo';
【根据表名查字段】
-- 注意左上角要选择对应的数据表查询
【SQL server】
SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TableName'
【根据数据库查表名】
-- 注意左上角要选择对应的数据表查询(以下SQL不需要修改)
【SQL server】
SELECT name AS TableName FROM sys.objects WHERE type = 'U' ORDER BY name;
【增加字段】
ALTER TABLE TABLENAME
ADD cinama VARCHAR(300);
5.递归查询
-- 注意左上角要选择对应的数据表查询
-- 父子递归查询(若有联表需要再后面补充)
WITH CTE AS (
SELECT * FROM T_BD_Amoeba WHERE AmoebaID = '4408180d-f097-46ef-9512-8338a618bd0a'
UNION ALL
SELECT a.* FROM T_BD_Amoeba a INNER JOIN CTE b ON b.AmoebaCode = a.ParentAmoeba WHERE a.IsDeleted = 0
)
SELECT * FROM CTE;
-- 父子递归查询(联表示例)
WITH CTE AS (
SELECT * FROM T_BD_Amoeba WHERE AmoebaID = '703ED34E-B33C-46AF-B2AD-A15401633E86'
UNION ALL
SELECT a.* FROM T_BD_Amoeba a INNER JOIN CTE b ON b.AmoebaCode = a.ParentAmoeba WHERE a.IsDeleted = 0
)
SELECT d.AmoebaID,d.AmoebaCode,d.AmoebaName,e.AmoMemberID,e.JionTime,e.ExitTime,e.IsAmoebaLeader,f.ID,f.Name,h.ProRoleName FROM CTE d
LEFT JOIN T_BD_AmoebaMember e ON d.AmoebaID=e.AmoebaID
LEFT JOIN SysUser f ON f.ID=e.UserID
LEFT JOIN SysUserProRole g ON f.ID=g.UserID
LEFT JOIN T_BD_ProjectRole h ON h.ProRoleID=g.ProRoleID
6.修改字段数据类型、修改字段名称
--修改数据类型
ALTER TABLE TestTable
ALTER COLUMN ID VARCHAR(50);
--修改多个数据类型
ALTER TABLE table_name ALTER COLUMN column_name1 new_data_type1, ALTER COLUMN column_name2 new_data_type2;
--修改字段名称(第一个字符串是表名.旧字段名称,第二个字符串是新字段名称,第三个字符串固定不改)
EXEC sp_rename 'TableName.OldColumnName', 'NewColumnName', 'COLUMN';
7.备份和还原数据库
【备份数据库】
第一步,找到你想备份的数据库-右键任务备份
第二步,选路径
然后点击确定,显示备份完成
去指定路径查看,发现已经备份成功:
【还原数据库-bak、btn文件】
先登录本地数据库
设置好安全性
把服务器身份验证改成SQL serve和windos身份验证模式
右键还原数据库
连接字符串参考(第一个名称可以右键属性获得,第二个是数据库名,第三个是免密码验证)
请在SqlMap.config文件改连接字符串。
【附加数据库mdf文件】
在SQLsever数据库中右键附加
【本地连接字符串】
server = LAPTOP-GCHKA4R2; Database = ProManager; Trusted_Connection = SSPI;
【远程连接字符串】
Data Source=192.xxx.xx.xxx;Initial Catalog=数据库名称;Persist Security Info=True;User ID=数据库账号;Password=数据库密码;TrustServerCertificate=true
9.数据库监控:查看网页运行的SQL
第一步,打开数据库,点击profiler,连接好后设置显示的事件
第二步,选中对应的事件查看SQL,并且可将SQL复制黏贴进行查询
第三步,黏贴后需要对SQL做的处理:
1. @param0 等含@的占位符,要替换为对应的字符
2. ‘’要替换为’ (双引号替换为单引号)
10.数据库执行时间计算
declare @d datetime
set @d=getdate();
/*你的SQL开始*/
SELECT * FROM table
/*你的SQL结束*/
select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())
11.固定窗口使用临时表
打开一个窗口,输入以下SQL
SELECT ID,NAME
INTO #TEMPTABLE
FROM Student
注释掉上面的SQL片段,写下以下SQL,发现可以查询到数据
SELECT * FROM #TEMPTABLE
若打开第二个查询窗口,再写一遍上面这句sql,发现查不出数据(对象名 '#MMtable' 无效。)
【说明】
创建的临时表 #TEMPTABLE 只在该窗口内可见,当关闭该窗口时,该临时表会被销毁
12.查某个数据库所有表名:
SELECT name FROM sys.tables;
13.查某个数据库所有表+字段:
DECLARE @databasename NVARCHAR(50) = 'CompanyInfo';
DECLARE @SQL NVARCHAR(MAX);
SET @SQL = '
SELECT
t.name AS TableName,
STUFF((
SELECT '','' + c.name
FROM ' + QUOTENAME(@databasename) + '.sys.columns c
WHERE c.object_id = t.object_id
FOR XML PATH(''''), TYPE).value(''.'', ''NVARCHAR(MAX)''), 1, 1, '''') AS Fields
FROM
' + QUOTENAME(@databasename) + '.sys.tables t;';
EXEC sp_executesql @SQL;
二、PLSQL(Oracle)
1.界面查询数据:
模板套用方法:
2.字段-表名互查
【根据字段查表名】
SELECT TABLE_NAME
FROM ALL_TAB_COLUMNS
WHERE COLUMN_NAME = '字段名';
【根据表名查字段】
快捷方式:
SELECT column_name
FROM all_tab_columns
WHERE table_name = '表名';
3.界面改数据
【记得修改数据或者执行脚本之后要点击提交或者回滚按钮】
4.界面查看数据类型和字段
5.脚本增删改代码
【记得修改数据或者执行脚本之后要点击提交或者回滚按钮】
(1)不存在该id则插入
-- 不存在该id则插入(insert语句由模板给出)
declare
mCount int;
begin
select count(1) into mCount from emr_base_type where type_id ='3A0D14A655E50924E3334DA372202001';
if mCount=0 then
EXECUTE IMMEDIATE 'insert into emr_base_type (type_id, type_name, parent_id, is_leaf, sort_num, remarks, type_code, audit_level, file_owner, defcreateid, defcreatetime, defupdateid, defupdatetime, isdeleted, deleterid, deletiontime, is_enabled, multiple) values (''3A0D14A655E50924E3334DA372202001'', ''病案首页'', NULL, 0, 1, NULL, ''FrontPage'', 2, ''Doctor'', NULL, ''07-10月 -23 04.02.57.1793864 下午'', NULL, NULL, 0, NULL, NULL, 1, 1)';
end if;
select count(1) into mCount from emr_base_type where type_id ='3A0D14A655E50924E3334DA372202002';
if mCount=0 then
EXECUTE IMMEDIATE 'insert into emr_base_type (type_id, type_name, parent_id, is_leaf, sort_num, remarks, type_code, audit_level, file_owner, defcreateid, defcreatetime, defupdateid, defupdatetime, isdeleted, deleterid, deletiontime, is_enabled, multiple) values (''3A0D14A655E50924E3334DA372202002'', ''入院记录'', NULL, 0, 2, NULL, ''Resident'', 2, ''Doctor'', NULL, ''07-10月 -23 04.02.57.1793864 下午'', NULL, NULL, 0, NULL, NULL, 1, 1)';
end if;
end;
(2)有关字段
字段增加
declare
mCount int;
begin
select count(1) into mCount from user_tab_columns t where t.table_name = upper('emr_emp_assist') and t.column_name = upper('IS_ENABLED');
if mCount=0 then
EXECUTE IMMEDIATE 'alter table emr_emp_assist add( IS_ENABLED number(1) null) ';
end if;
end;
字段类型修改
declare
mCount int;
begin
select count(1) into mCount from user_tab_columns t where t.table_name = upper('emr_emp_diagnose') and t.column_name = upper('SORT_NUM');
if mCount=1 then
EXECUTE IMMEDIATE 'alter table emr_emp_diagnose modify( SORT_NUM number(8)) ';
end if;
end;
6.换库查询
7.导出SQL语句
8.需要注意的点:
(1)数据被锁
三、公共数据库
1.left join和inner join区别
【长话短说】inner join(内连接)得到数据更少,直接剔除匹配不到的数据(剔除无关联ID的数据)
【例子】如下图所示,inner join不显示红框的数据,left join会显示
SELECT * FROM student s
LEFT JOIN score sc on s.id=sc.studentid
SELECT * FROM student s
INNER JOIN score sc on s.id=sc.studentid
【查询匹配失败的数据】
SELECT * FROM student s
LEFT JOIN score sc on s.id=sc.studentid
WHERE sc.id is null --查询从表ID为空的数据,就是联表匹配不到的数据
2.联表条件写法差异
--联表:只要满足关联关系,B.IsDeleted是什么则返回什么
SELECT * FROM TABLEA A
LEFT JOIN TABLEB B ON A.ID=B.EXID
--联表不筛选:只要满足关联关系,B.IsDeleted≠0 则返回null
SELECT * FROM TABLEA A
LEFT JOIN TABLEB B ON A.ID=B.EXID AND B.IsDeleted=0
--联表且筛选:只要满足关联关系,B.IsDeleted≠0 则返回null,假如是null则被where过滤掉
SELECT * FROM TABLEA A
LEFT JOIN TABLEB B ON A.ID=B.EXID
WHERE B.IsDeleted=0
--【结论】数据返回多少关系是:第一种=第二种>第三种