1、查看表结构
-
MySQL
- desc TableName;
- show create table TableName;
-
SQL Server
- exec sp_help TableName;
2、查看所有数据库
- MySQL
- show databases;
- SQL Server
- select * from master.sys.Databases;
3、查看数据库下的所有表
-
MySQL
- show tables;
-
SQL Server
- select * from DatabaseName.dbo.SysObjects where type=’U’;
4、复制表
-
MySQL
- create table NewTableName like OldTableName; 只复制表结构,含主键、索引等
- create table NewTableName as select * from OldTableName; 复制表结构与数据
- create table NewTableName as select * from OldTableName where 1=0; 只复制表结构
-
SQL Server
- select * into NewTableName from OldTableName; 复制表结构和数据
- select * into NewTableName from OldTableName where 1=0; 只复制表结构
5、限制查询返回行数
- MySQL
- select * from table limit 5; 返回前5行
- select * from table limit 2,5; 从第3行开始,返回前5行,首行索引为0
- SQL Server
- select top 5 * from table;
6、修改表名
- MySQL
- alter table OldTableName rename to NewTableName;
- SQL Server
- exec sp_rename OldTableName,NewTableName;
7、自增约束
-
MySQL
- create table tablename(col1 int auto_increment);
-
SQL Server
- create table tablename(col1 int identity);
8、调用存储过程
-
MySQL
- call ProcedureName();
- call ProcedureName(param1,param2);
-
SQL Server
- exec ProcedureName
- exec ProcedureName param1,param2;
- execute ProcedureName param1,param2;
9、判断数据库是否存在并删除
-
MySQL
- drop database if exists DatabaseName;
-
SQL Server
- if db_id(‘DatabaseName‘) is not null drop database DatabaseName;
10、判断表是否存在并删除
-
MySQL
- drop table if exists TableName;
-
SQL Server
- if object_id(‘TableName’) is not null drop table TableName;
- if exists(select object_id(‘TableName’)) drop table TableName;
11、连接数据库的主机端口语法
- MySQL
- 采用冒号分隔,Host:Port,eg:xxx@domain.com:3306
- SQL Server
- 采用逗号分隔,Host,Port,eg:xxx@domain.com,1433
12、字段识别符
当字段名称与数据库系统的保留关键字一致时,为了让数据库能够成功识别成字段名称,而不是识别成保留关键字,则需要在字段名称上使用相应的符号括起来。
- MySQL
- 采用反引号,如:\
字段名\
- 采用反引号,如:\
- SQL Server
- 采用中括号,如:[字段名]
13、字符串拼接语法
-
MySQL
- 使用concat函数,如:concat(‘string1′,’string2’)
-
SQL Server
- 使用 + 号,如:’string1′ + ‘string2’
14、IF语句
-
MySQL
-
IF表达式:IF(条件表达式,条件为True时的值,条件为False时的值)
-
IF语句:
IF condition1 THEN ... ELSEIF condition1 THEN ... ELSEIF condition1 THEN ... ELSE ... END IF;
-
-
SQL Server
-
IF表达式:IF(条件表达式,条件为True时的值,条件为False时的值)
-
IF语句:
IF condition1 BEGIN ... ... END ELSE BEGIN ... ... END 每个语句块里如果超过一条语句,则必须要使用BEGIN END
-
15、变量
- MySQL
- 系统变量
- 系统变量属于服务器层面,由系统定义,其中又分为全局变量与会话变量。会话变量仅在当前会话中可用,重新连接数据库会重置会话变量的值。全局变量在全局范围都可用,重新连接数据库并不会重置全局变量的值,但不可跨重启,即数据库服务重启会重置全局变量的值。
- SHOW [GLOBAL|SESSION] VARIABLES; –查看所有全局变量或会话变量
- SHOW [GLOBAL|SESSION] VARIABLES LIKE ‘…’; –可以使用LIKE进行模糊匹配查找
- SELECT @@[GLOBAL|SESSION].系统变量名; –查看指定变量的值
- SET @@[GLOBAL|SESSION].系统变量名 = 变量值; –给指定变量赋值
- SET [GLOBAL|SESSION] 系统变量名 = 变量值; –给指定变量赋值
- 用户变量
- 用户变量是用户根据需要自行定义的变量,用户变量不需要提前声明,在用的时候直接用“@变量名”使用就可以,其作用域为当前连接。用户变量无需对其进行声明或初始化,当直接使用未赋值或未声明的用户变量时,变量的值为NULL。注意:两个@符是系统变量,一个@符是用户自定义变量。
- 用户变量在引用的时候直接用“@变量名“即可
- SET @var_name1 := expr1 [, @var_name2 := expr2]… –在定义用户变量的同时赋值
- SET @var_name1 = expr1 [, @var_name2 = expr2]… –使用SET命令赋值时,:=与=的效果一致
- SELECT @var_name1 := expr1 [, @var_name2 := expr2]… –在SELECT语句中赋值时必须使用:=
- SELECT expr1,expr2… INTO @var_name1,@var_name2… FROM TableName
- 使用SELECT INTO方式给变量赋值时,表达式必须为标量值,即查询结果只能为单行,并且不会输出变量赋值后的值,与SET的行为一致,即仅赋值。
- 使用SELECT := 方式给变量赋值时,每一行都会执行一次赋值,故最终变量的结果为最后一行的值,但会输出变量每次赋值时的值列表,即赋值后查询。
- 局部变量
- 局部变量是根据需要在局部生效的变量,访问之前,需要DECLARE声明。可用作存储过程或函数内的局部变量和输入参数,局部变量的作用范围是声明它的那个BEGIN…END块内。
- 局部变量在定义与使用时均不需要加@前缀
- 局部变量的声明:DECLARE 变量名 数据类型 [DEFAULT …] –没有指定默认值时,默认为NULL
- 局部变量的赋值:可通过SET或SELECT INTO方式进行赋值,不支持SELECT var_name := expr方式
- SQL Server
- 全局变量
- 全局变量又称系统变量,是sql server 系统内部使用的变量,其作用范围并不局限于某一程序,而是任何程序均可随时调用。常用于存储一些sql server的配置设置值和效能统计数据。用户可在程序中用全局变量来测试系统的设定值或T-SQL命令执行后的状态值。引用全局变量时,全局变量名必须以‘@@’开头。不能定义与全局变量同名的局部变量。一般只查看系统变量的值,以此来获取相关信息。
- 局部变量
- 局部变量的定义与使用必须带有@前缀,作用范围仅为程序内部。
- 局部变量必须先声明后使用,没有进行赋值时,默认为NULL。
- 局部变量的声明:declare @变量名1 数据类型1 [, @变量名2 数据类型2]…
- set @局部变量=变量值|表达式 –局部变量的赋值,不支持:=运算符,只能用=
- select @局部变量=变量值|表达式 –局部变量的赋值,不支持SELECT 值 into @变量 的方式
- print @局部变量 或 select @局部变量 –局部变量的输出与查看
- 使用SELECT = 方式给变量赋值时,每一行都会执行一次赋值,故最终变量的结果为最后一行的值,但不会输出变量每次赋值时的值列表,即仅赋值,与SET的行为一致。注意与MySQL的区分。
16、触发器
-
MySQL
-
MySQL的触发器允许在执行增删改操作的之前或之后执行触发器内定义的语句,即增删改操作总是生效,与SQL Server的After类型的触发器类似。但MySQL的触发器是针对行级别的,即使一条DML语句影响了多行,也总是一行一行的触发去处理,这一点与SQL Server的After类型的触发器并不一样。
-
语法
CREATE [DEFINER = { user | CURRENT_USER }] TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW [trigger_order] trigger_body trigger_time: { BEFORE | AFTER } trigger_event: { INSERT | UPDATE | DELETE } trigger_order: { FOLLOWS | PRECEDES } other_trigger_name ----------------------------------------------------------- eg: create trigger trigger_name after insert on table_name for each row begin ... end;
-
NEW与OLD
-
MySQL 中定义了 NEW 和 OLD,用来表示触发器的所在表中,触发了触发器的那一行数据,来引用触发器中发生变化的记录内容,具体地:
1、在INSERT型触发器中,NEW用来表示将要(BEFORE)或已经(AFTER)插入的新数据;
2、在UPDATE型触发器中,OLD用来表示将要或已经被修改的原数据,NEW用来表示将要或已经修改为的新数据;
3、在DELETE型触发器中,OLD用来表示将要或已经被删除的原数据; -
使用方法:NEW.columnName | OLD.columnName
-
另外,OLD是只读的,而NEW则可以在触发器中使用 SET 赋值,这样不会再次触发触发器,造成循环调用(如每插入一个学生前,都在其学号前加“2013”)
-
注意:在某个表上定义的触发器的执行块中,不能继续对该表执行增删改的操作。
-
注意:若想在插入或更新数据前修改最新的数据,则需要使用BEFORE类型的触发器,不能是AFTER
-
示例
mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2)); mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00); mysql> delimiter $$ mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account -> FOR EACH ROW -> BEGIN -> IF NEW.amount < 0 THEN -> SET NEW.amount = 0; -> ELSEIF NEW.amount > 100 THEN -> SET NEW.amount = 100; -> END IF; -> END$$ mysql> delimiter ; mysql> update account set amount=-10 where acct_num=137; mysql> select * from account; +----------+---------+ | acct_num | amount | +----------+---------+ | 137 | 0.00 | | 141 | 1937.50 | | 97 | -100.00 | +----------+---------+ mysql> update account set amount=200 where acct_num=137; mysql> select * from account; +----------+---------+ | acct_num | amount | +----------+---------+ | 137 | 100.00 | | 141 | 1937.50 | | 97 | -100.00 | +----------+---------+
- 触发器的查看与删除
#触发器的查看: SHOW TRIGGERS; #方式一:查看所有触发器 SHOW CREATE TRIGGER trigger_name; #方式二:查看指定触发器 SELECT * FROM information_schema.
TRIGGERS
; #方式三:从系统库中查看 ------------------------------------------------------------------- #触发器的删除: DROP TRIGGER [IF EXISTS] trigger_name; -
-
SQL Server
-
SQL Server的触发器根据是否真正执行增删改操作分为INSTEAD OF与AFTER两类。
-
INSTEAD OF触发器又称为替代触发器,替代触发器会将触发触发器的增删改命令替换为触发器内部定义的语句,即触发触发器的增删改命令并不会真正执行,比如:以用户的 update 操作为例,首先获取永久表中相关数据的副本保存到 Deleted 临时表中,对副本数据执行update操作,将结果保存到 Inserted 临时表中,而此时永久表没有任何变化,触发器开始执行,如果触发器没有对永久表操作的相关代码,那么用户的操作将无法作用到永久表。即触发触发器的增删改命令仅仅只是激活了触发器,并不会真正对表进行操作,对表执行操作的反而是触发器内部定义的语句。执行顺序:先根据用户提交的 DML 操作( insert、update、delete ),将意欲操作的相关数据填充到 Inserted 表和 Deleted 表,但不会对SQL物理表进行修改,最后执行触发器内部定义的语句。
-
After触发器可由 DDL操作或 DML操作执行触发。该触发器按语句触发而不是按行触发,即一条语句不管影响了多少行,After 触发器只能触发一次,因此在设计时需要考虑到影响多行的情况,比如一条INSERT语句里带有多行的情况。执行顺序:先执行用户提交的 DML、DML操作,对实际的物理表做出修改,约束检查通过后将相关数据填充到 Inserted 表和 Deleted 表,最后执行触发器内部定义的语句。
-
语法
CREATE TRIGGER trigger_name ON {table|view} [WITH ENCRYPTION|EXECUTE AS] {FOR|AFTER|INSTEAD OF} {[CREATE|ALTER|DROP|INSERT|UPDATE|DELETE ]} [NOT FOR REPLICATION] AS sql_statement [1...n ] 1. trigger_name 这是触发器的名字,它应该遵守SQL Server中标识符规则。 2. table|view 触发器创建在这个表/视图上。 3. ENCRYPTION 这个选项是可选的。如果设置了这个选项,那么这个CREATE TRIGGER语句的原文本将会被加密。 4. EXECUTE AS 这个选项是可选的,这个选项设置了,那么这个触发器就会在安全上下文之下执行。 5. FOR/AFTER FOR/AFTER指定了触发器是After触发器。如果只仅仅指定了FOR关键字,那么AFTER是默认的。AFTER触发器不能在视图上定义。 6. INSTEAD OF INSTEAD OF指定了这个触发器是INSTEAD OF触发器 7. CREATE|ALTER|DROP|INSERT|UPDATE|DELETE 这些关键字指定了哪个操作将在触发器中触发。可以使用这些关键字中的一个或者任何顺序的关键字组合。 8. NOT FOR REPLICATION 这个参数说明了这个触发器不会在响应进程修改表(即使)卷入了这个触发器中触发。 9. AS 在这个之后,我们可以指定触发器执行时的操作和条件 10. sql_statement 这些是触发器的条件和操作。这个触发器这些操作指定了在T-SQL语句之中。 ----------------------------------------------------------- eg: -- after类型的触发器: create trigger trigger_name on table_name for insert as --这里的for等价于after begin ... end; -- instead of类型的触发器 create trigger trigger_name on table_name instead of insert as begin ... end;
-
Inserted与Deleted
-
这两个表由系统来维护,它们存在于内存中而不是在数据库中,可以理解为一个虚拟的表。
-
这两个表的结构总是与被该触发器作用的表的结构相同。
-
触发器执行完成后,与该触发器相关的这两个表也被删除。
-
Deleted表存放由于执行Delete或Update语句而要从表中删除的所有行。
-
Inserted表存放由于执行Insert或Update语句而要向表中插入的所有行。
-
使用方法与普通的表一致。
-
示例
-- 在“商品”表建立删除触发器,实现“商品”表和“订单”表的级联删除。 create trigger goodsdelete_trigger_商品_delete on 商品 after delete as delete from 订单 where 订单.pid in (select pid from deleted); -- 在删除学生表时,如果该学生仍有借书记录(未还)则不能删除 create trigger trigger_学生_Delete on 学生 instead of Delete as begin if not exists(select * from 借书记录, deleted where 借书记录.学号 = deleted.学号) delete from 学生 where 学生.学号 in (select 学号 from deleted) end;
- 触发器的查看、修改与删除
--查看数据库中所有的触发器 use 数据库名 go select * from sysobjects where xtype='TR' --查看指定触发器的内容 use 数据库名 go exec sp_helptext '触发器名称' --触发器的启用与禁用 禁用:alter table 表名 disable trigger 触发器名称 启用:alter table 表名 enable trigger 触发器名称 如果有多个触发器,则各个触发器名称之间用英文逗号隔开。 如果把“触发器名称”换成“ALL”,则表示禁用或启用该表的全部触发器。 --修改触发器语法 ALTER TRIGGER trigger_name ON table_name [ WITH ENCRYPTION ] FOR {[DELETE][,][INSERT][,][UPDATE]} AS sql_statement; --触发器的删除 DROP TRIGGER trigger_name
-
17、存储过程
-
MySQL
- 语法
create procedure 存储过程名称([ [IN|OUT|INOUT] 参数名称 参数的数据类型 , ...]) begin ... end; #存储过程的执行或调用: CALL Procedure_name([参数1,[参数2]...]);
-
存储过程的参数名称不需要带@前缀,参数的赋值与使用同DECLARE声明的局部变量一致。
-
IN 输入参数:表示该参数的值必须在调用存储过程时指定,在存储过程中修改该参数的值不能返回到外部
-
OUT 输出参数:该值可在存储过程内部被改变,并可返回,但其初始值为NULL,读取不了外部的值
-
INOUT 输入输出参数:调用时指定,在存储过程内部可被改变并返回到外部,且其初始值为外部的值
-
注意:OUT与INOUT参数在调用时必须以变量的形式赋值,而不能用常量
-
示例——IN参数的例子
DROP PROCEDURE IF EXISTS test_proc; CREATE PROCEDURE test_proc(in p_in int) BEGIN SELECT p_in; SET p_in = 2; SELECT p_in; END; set @a = 1; CALL test_proc(@a); SELECT @a; -- 结果: +------+ | p_in | +------+ | 1 | +------+ +------+ | p_in | +------+ | 2 | +------+ +------+ | @a | +------+ | 1 | +------+
- 示例——OUT参数的例子
DROP PROCEDURE IF EXISTS test_proc; CREATE PROCEDURE test_proc(out p_in int) BEGIN SELECT p_in; SELECT 2 into p_in; SELECT p_in; END; set @a = 1; CALL test_proc(@a); SELECT @a; -- 结果: +------+ | p_in | +------+ | null | +------+ +------+ | p_in | +------+ | 2 | +------+ +------+ | @a | +------+ | 2 | +------+
- 示例——INOUT参数的例子
DROP PROCEDURE IF EXISTS test_proc; CREATE PROCEDURE test_proc(inout p_in int) BEGIN SELECT p_in; SELECT 2 into p_in; SELECT p_in; END; set @a = 1; CALL test_proc(@a); SELECT @a; -- 结果: +------+ | p_in | +------+ | 1 | +------+ +------+ | p_in | +------+ | 2 | +------+ +------+ | @a | +------+ | 2 | +------+
- 存储过程的查看与删除
#存储过程的查看: #查看所有存储过程的状态: SHOW PROCEDURE STATUS [ like ‘pattern’] ; #查看指定存储过程的定义: SHOW CREATE PROCEDURE proc_name ;|\G #从系统表里查看存储过程的信息: SELECT * FROM information_schema.Routines Where ROUTINE_NAME = ‘proc_name’; ------------------------------------------------------------------- #存储过程的删除: DROP PROCEDURE [IF EXISTS] proc_name;
-
SQL Server
- 语法
create proc | procedure pro_name [{@参数 数据类型} [=默认值] [output], {@参数 数据类型} [=默认值] [output], .... ] as begin ... end; #存储过程的执行或调用: DELCARE @other_param int SET @other_param = 333 EXEC Procedure_name; EXECUTE Procedure_name 1,2; --为参数赋值时,可以不带参数名 EXECUTE Procedure_name @param1=1,@other_param output; --但也可以带上参数名,与Python函数的参数赋值类似 EXECUTE Procedure_name @param1=1,@param2=@other_param output; --输出型参数,若需要输出,则在调用时必须带output关键字
-
注意:OUTPUT参数在调用时必须以变量的形式赋值,而不能用常量
-
存储过程的参数如果不带output则是输入型参数,只能接受而不能输出,类似MySQL里的in类型
-
存储过程的参数如果带了output则是输出型参数,既可以接受也可以输出,类似MySQL的inout类型
-
参数列表中的output关键字表示对应的参数可以被输出,但前提是调用存储过程且为该参数赋值时有带上output关键字,如果赋值时没带output关键字,那即使参数列表有带output关键字,该参数也会被当做输入型参数。
-
即参数列表里的output关键字表示该参数既可当作输入型参数,也可当作输出型参数,具体取决于调用与赋值时有没有带上output关键字。但如果参数列表里不带output关键字,那调用赋值时就不能带output关键字,即该参数只能当作输入性参数。
-
示例
# 不带参数的存储过程 -- 创建存储过程 if (exists (select * from sys.objects where name = 'proc_get_student')) drop proc proc_get_student go create proc proc_get_student as select * from student; -- 调用、执行存储过程 exec proc_get_student; --------------------------------------------------------------------------- # 带输入型参数的存储过程 -- 创建存储过程 IF OBJECT_ID (N'PROC_ORDER_COUNT', N'P') IS NOT NULL DROP procedure PROC_ORDER_COUNT; GO CREATE procedure PROC_ORDER_COUNT @city nvarchar(50) AS SELECT COUNT(OrderID) FROM Orders WHERE City=@city -- 调用、执行存储过程 EXEC PROC_ORDER_COUNT N'GuangZhou'; EXEC PROC_ORDER_COUNT @city=N'GuangZhou'; --------------------------------------------------------------------------- # 带输出型参数的存储过程 -- 创建存储过程 IF OBJECT_ID (N'PROC_ORDER_COUNT', N'P') IS NOT NULL DROP procedure PROC_ORDER_COUNT; GO CREATE procedure PROC_ORDER_COUNT @city nvarchar(50), @count_num int output AS SELECT @count_num = COUNT(OrderID) FROM Orders WHERE City=@city -- 调用、执行存储过程 DECLARE @count int EXEC PROC_ORDER_COUNT N'GuangZhou', @count output; EXEC PROC_ORDER_COUNT N'GuangZhou', @count_num=@count output; select @count;
- 存储过程的查看与删除
#存储过程的查看: select * from sysobjects where xtype='P' select * from sys.procedures ------------------------------------------------------------------- #存储过程的删除: DROP PROCEDURE sp_name; IF OBJECT_ID (N'PROC_NAME', N'P') IS NOT NULL DROP procedure PROC_NAME;