• [技术干货] MySQL临时表
    顾名思义,临时表就是临时用来存储数据的表,是建立在系统临时文件夹中的表,如果使用得当,完全可以像普通表一样进行各种操作。我们常使用临时表来存储中间结果集。如果需要执行一个很耗资源的查询或需要多次操作大表时,可以把中间结果或小的子集放到一个临时表里,再对这些表进行查询,以此来提高查询效率。临时表主要适用于需要临时保存数据的一些场景。一般情况下,临时表通常是在应用程序中动态创建或者由 MySQL 内部根据需要自己创建。临时表可以分为内部临时表和外部临时表。外部临时表外部临时表也可称为会话临时表,这种临时表只对当前用户可见,它的数据和表结构都存储在内存中。当前会话中断或结束后,数据表数据就会丢失,MySQL 会自动删除表并释放其所占空间。1)创建临时表创建临时表很容易,在 CREATE TABLE 语句上添加 TEMPORARY 关键字,如下所示:CREATE TEMPORARY TABLE <表名>...临时表的命名可以和非临时表同名,但是同名后非临时表将对当前会话不可见,直到临时表被删除。2)查询临时表创建了临时表之后,运行 SHOW TABLES 命令不会列出临时表,以及在 INFORMATION_SCHEMA 数据库中也不存在临时表的信息,这不是 Bug,而是设计就是如此。我们可以使用以下命令来查看临时表:SHOW CREATE TABLE <表名>; 3)删除临时表当然,我们也可以在当前会话中手动销毁临时表,SQL 语句如下:DROP TABLE <表名>;例 下面是创建 tmp_table 表,并对它进行操作。mysql> CREATE TEMPORARY TABLE tmp_table (     -> id INT NOT NULL,     -> name VARCHAR(10) NOT NULL); Query OK, 0 rows affected (0.03 sec) mysql> SHOW TABLES; +-------------------+ | Tables_in_test    | +-------------------+ | product           | | product_price     | | score             | | student           | | student_comment   | | tb_student        | | tb_student_course | | tb_students_info  | | tb_students_score | | tb_usertest       | +-------------------+ 10 rows in set (0.01 sec) mysql> SHOW CREATE TABLE tmp_table \G *************************** 1. row ***************************        Table: tmp_table Create Table: CREATE TEMPORARY TABLE `tmp_table` (   `id` int(11) NOT NULL,   `name` varchar(10) COLLATE utf8_unicode_ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci 1 row in set (0.00 sec) mysql> DROP TABLE tmp_table; Query OK, 0 rows affected (0.01 sec) mysql> SHOW CREATE TABLE tmp_table \G ERROR 1146 (42S02): Table 'test.tmp_table' doesn't exist如果你在执行 DROP TABLE 命令之前退出当前 MySQL 会话,再使用 SHOW CREATE TABLE 命令来读取 tmp_table 表,会发现数据库中没有该表的存在,因为在退出会话时该临时表已经被销毁了。外部临时表也有一些限制,使用时需注意以下几点:所用数据库账号需要有建立和使用临时表的权限在同一条 SQL 语句中,不能关联2次相同的临时表不能用 RENAME 来重命名一个临时表,可以用 ALTER TABLE 来代替。内部临时表内部临时表是一种特殊轻量级的临时表,不同于手工创建的临时表,它是被 MySQL 自动创建的。在 SQL 的执行过程中可能会用到临时表来存储某些操作的中间结果,该过程由 MySQL 自动完成,用户无法手工干预,且这种内部表对用户来说是不可见的。我们可以通过 EXPLAIN 或者 SHOW STATUS 查看 MySQL 是否使用了内部临时表帮助完成某个操作。内部临时表在 SQL 语句的优化过程中扮演着非常重要的角色,MySQL 中的很多操作都要依赖于内部临时表来进行优化。但是使用内部临时表需要创建表以及中间数据的存取代价,所以用户在写 SQL 语句的时候应该尽量的去避免使用临时表。以下是生成内部临时表的几种可能:使用 ORDER BY/GROUP BY 的列并非全来自于表连接的第一个表Distinct 和 ORDER BY 联合使用多表连接需要保存中间结果集内部临时表有两种类型,一种是 HEAP 临时表,这种临时表的所有数据都会存在内存中,对于这种表的操作不需要 IO 操作;另一种是 OnDisk 临时表,顾名思义,这种临时表会将数据存储在磁盘上。OnDisk 临时表用来处理中间结果比较大的操作。以下情况可能阻碍 MySQL 使用 HEAP 临时表,改用 OnDisk 临时表:数据表中含有 BLOB 类型或 TEXT 类型字段列;在 GROUP BY 或 DISTINCT 任何条件中含有超过 512 字节的列;如果使用了 UNION 或 UNION ALL,而且 SELECT 列中含有任何超过 512 字节的列;如果 HEAP 临时表的大小大于 MAX_HEAP_TABLE_SIZE 系统变量的值,HEAP 临时表将会被自动转换成 OnDisk 临时表。MySQL 5.7 版本中,OnDisk 临时表可以通过 INTERNAL_TMP_DISK_STORAGE_ENGINE 系统变量选择使用 MyISAM 引擎或者 InnoDB 引擎。Created_tmp_tables 和 Created_tmp_disk_tables 变量用来记录所用临时表的数目。
  • [技术干货] SQL行转列和列转行代码详解
    行列互转,是一个经常遇到的需求。实现的方法,有case when方式和2005之后的内置pivot和unpivot方法来实现。在读了技术内幕那一节后,虽说这些解决方案早就用过了,却没有系统性的认识和总结过。为了加深认识,再总结一次。行列互转,可以分为静态互转,即事先就知道要处理多少行(列);动态互转,事先不知道处理多少行(列)。--创建测试环境 USE tempdb; GO IF OBJECT_ID('dbo.Orders') IS NOT NULL  DROP TABLE dbo.Orders; GO CREATE TABLE dbo.Orders (  orderid  int    NOT NULL PRIMARY KEY NONCLUSTERED,  orderdate datetime  NOT NULL,  empid   int    NOT NULL,  custid  varchar(5) NOT NULL,  qty    int    NOT NULL ); CREATE UNIQUE CLUSTERED INDEX idx_orderdate_orderid  ON dbo.Orders(orderdate, orderid); INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)  VALUES(30001, '20020802', 3, 'A', 10); INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)  VALUES(10001, '20021224', 1, 'A', 12); INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)  VALUES(10005, '20021224', 1, 'B', 20); INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)  VALUES(40001, '20030109', 4, 'A', 40); INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)  VALUES(10006, '20030118', 1, 'C', 14); INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)  VALUES(20001, '20030212', 2, 'B', 12); INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)  VALUES(40005, '20040212', 4, 'A', 10); INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)  VALUES(20002, '20040216', 2, 'C', 20); INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)  VALUES(30003, '20040418', 3, 'B', 15); INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)  VALUES(30004, '20020418', 3, 'C', 22); INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)  VALUES(30007, '20020907', 3, 'D', 30); GO行转列-静态方案:--行转列的静态方案一:CASE WHEN,兼容sql2000 select custid, sum(case when YEAR(orderdate)=2002 then qty end) as [2002], sum(case when YEAR(orderdate)=2003 then qty end) as [2003], sum(case when YEAR(orderdate)=2004 then qty end) as [2004] from orders group by custid; GO --行转列的静态方案二:PIVOT,sql2005及以后版本 select * from (select custid,YEAR(orderdate) as years,qty from orders) as ord pivot(sum(qty) for years in([2002],[2003],[2004]))as p GO行转列-动态方案:加入了xml处理和SQL注入预防判断--既然是用到了动态SQL,就有一个老话题:SQL注入。建一个注入性字符的判断函数。 CREATE FUNCTION [dbo].[fn_CheckSQLInjection] (  @Col nvarchar(4000) ) RETURNS BIT --如果存在可能的注入字符返回true,反之返回false AS BEGIN DECLARE @result bit;  IF     UPPER(@Col) LIKE UPPER(N'%0x%')  OR UPPER(@Col) LIKE UPPER(N'%;%')  OR UPPER(@Col) LIKE UPPER(N'%''%')  OR UPPER(@Col) LIKE UPPER(N'%--%')  OR UPPER(@Col) LIKE UPPER(N'%/*%*/%')  OR UPPER(@Col) LIKE UPPER(N'%EXEC%')  OR UPPER(@Col) LIKE UPPER(N'%xp_%')  OR UPPER(@Col) LIKE UPPER(N'%sp_%')  OR UPPER(@Col) LIKE UPPER(N'%SELECT%')  OR UPPER(@Col) LIKE UPPER(N'%INSERT%')  OR UPPER(@Col) LIKE UPPER(N'%UPDATE%')  OR UPPER(@Col) LIKE UPPER(N'%DELETE%')  OR UPPER(@Col) LIKE UPPER(N'%TRUNCATE%')  OR UPPER(@Col) LIKE UPPER(N'%CREATE%')  OR UPPER(@Col) LIKE UPPER(N'%ALTER%')  OR UPPER(@Col) LIKE UPPER(N'%DROP%')  SET @result=1  ELSE  SET @result=0  return @result END GO --行转列的动态方案一:CASE WHEN,兼容sql2000 DECLARE @T TABLE (years INT NOT NULL PRIMARY KEY); INSERT INTO @T  SELECT DISTINCT YEAR(orderdate) from orders; DECLARE @Y INT; SET @Y=(SELECT MIN(years) from @T); DECLARE @SQL NVARCHAR(4000)=N''; WHILE @Y IS NOT NULL BEGIN  SET @SQL=@SQL+N',sum(case when YEAR(orderdate)='+CAST(@Y AS NVARCHAR(4)) +N' then qty end) as '+QUOTENAME(@Y);  SET @Y=(SELECT MIN(years) from @T where years>@Y); END IF dbo.fn_CheckSQLInjection(@SQL)=0 SET @SQL=N'SELECT custid'+@SQL+N' FROM orders group by custid' PRINT @SQL EXEC sp_executesql @SQL GO --行转列的动态方案二:PIVOT,sql2005及以后版本 DECLARE @T TABLE (years INT NOT NULL PRIMARY KEY); INSERT INTO @T  SELECT DISTINCT YEAR(orderdate) from orders; DECLARE @Y INT; SET @Y=(SELECT MIN(years) from @T); DECLARE @SQL NVARCHAR(4000)=N'';   --这里使用了xml处理来处理类组字符串 SET @SQL=STUFF((SELECT N','+QUOTENAME(years) FROM @T  FOR XML PATH('')),1,1,N''); IF dbo.fn_CheckSQLInjection(@SQL)=0 SET @SQL=N'select * from (select DISTINCT custid,YEAR(orderdate) as years,qty from orders) as ord pivot(sum(qty) for years in('+@SQL+N'))as p'; PRINT @SQL; EXEC SP_EXECUTESQL @SQL; GO列转行:--列转行的静态方案:UNPIVOT,sql2005及以后版本 SELECT * FROM dbo.pvtCustOrders SELECT custid,years,qty from dbo.pvtCustOrders unpivot(qty for years in([2002],[2003],[2004]))as up GO --列转行的动态方案:UNPIVOT,sql2005及以后版本 --因为行是动态所以这里就从INFORMATION_SCHEMA.COLUMNS视图中获取列来构造行,同样也使用了XML处理。 DECLARE @SQL NVARCHAR(4000)=N''; SET @SQL=STUFF((SELECT N','+QUOTENAME(COLUMN_NAME ) FROM INFORMATION_SCHEMA.COLUMNS WHERE ORDINAL_POSITION>1 AND TABLE_NAME='PvtCustOrders' FOR XML PATH('')),1,1,N'') SET @SQL=N'SELECT custid,years,qty      from dbo.pvtCustOrders      unpivot(qty for years in('+@SQL+'))as up'; PRINT @SQL; EXEC SP_EXECUTESQL @SQL;总结:学无止境,在解决问题中成长。
  • [技术干货] SQL Server 分页编号的另一种方式
    今天看书讲T-SQL,看到了UNBOUNDED PRECEDING,就想比对下ROW_NUMBER()的运行速度。sql及相关的结果如下,数据库中的数据有5W+。ROW_NUMBER(): SET STATISTICS TIME ON SELECT   ROW_NUMBER() OVER(ORDER BY Id DESC) rn,Id FROM   dbo.T_MyCourse运行结果:              UNBOUNDED PRECEDING SET STATISTICS TIME ON SELECT   SUM(1) OVER(ORDER BY Id DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) rn,Id FROM   dbo.T_MyCourse运行结果:            通过运行之后,看到结果,使用微软官方提供的方法进行编号排序,速度明显的提高。不过我使用上述方法进行分页获取数据的时候结果又有点不一样。分页获取数据:ROW_NUMBER() 分页获取数据:SET STATISTICS TIME ON SELECT   * FROM   (   SELECT     ROW_NUMBER() OVER(ORDER BY Id DESC) rn,Id   FROM     dbo.T_MyCourse   )a WHERE   a.rn BETWEEN 55 AND 444             执行sql命令:DBCC DROPCLEANBUFFERS ,清除数据库缓存后的结果           UNBOUNDED分页获取数据:SET STATISTICS TIME ON SELECT   * FROM   (   SELECT     SUM(1) OVER(ORDER BY Id DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) rn,Id   FROM     dbo.T_MyCourse   )a WHERE   a.rn BETWEEN 22 AND 444           UNBOUNDED这个方式下执行了DBCC DROPCLEANBUFFERS 清除缓存的sql也没有用,执行时间没有变化。通过上述结果,看到ROW_NUMBER()获取分页的数据明显更快,我猜测是微软对ROW_NUMBER()这个方法做了优化,可能是有缓存,读取的缓存中的数据然后进行分页。
  • [技术干货] SQL Server三种锁定模式的知识讲解
    sql server 锁定模式有三种:共享( S锁),更新(U锁),排他(X锁);S锁是共享锁,如果事务T对数据A加上共享锁后,则其他事务只能对A再加共享锁,不能加排他锁。获准共享锁的事务只能读数据,不能修改数据。个人理解为,对数据A的操作就只能是SELECT ,(联想下,S锁,不就是Select的首字母么),其他事务对A数据的UPDATE ,DELETE都不能进行;U锁是更新锁。用于可更新的资源中。防止当多个会话在读取、锁定以及随后可能进行的资源更新时发生常见形式的死锁。原理解释:更新锁       更新 (U) 锁可以防止通常形式的死锁。一般更新模式由一个事务组成,此事务读取记录,获取资源(页或行)的共享 (S) 锁,然后修改行,此操作要求锁转换为排它 (X) 锁。       如果两个事务获得了资源上的共享模式锁,然后试图同时更新数据,则一个事务尝试将锁转换为排它 (X) 锁。共享模式到排它锁的转换必须等待一段时间,因为一个事务的排它锁与其它事务的共享模式锁不兼容;发生锁等待。        第二个事务试图获取排它 (X) 锁以进行更新。由于两个事务都要转换为排它 (X) 锁,并且每个事务都等待另一个事务释放共享模式锁,因此发生死锁。若要避免这种潜在的死锁问题,请使用更新 (U) 锁。一次只有一个事务可以获得资源的更新 (U) 锁。      如果事务修改资源,则更新 (U) 锁转换为排它 (X) 锁。否则,锁转换为共享锁。      个人理解,打个比方,A,B都拿把枪指着对方说:”我等你放下枪我就放下枪“,大家都等对方放下枪,于是就这么死僵持着......X锁是排他锁,也叫独占锁,事务对A资源的insert ,update ,delete要用独占,不然的话乱套了;这是为了保护数据再一次操作中的准确性;个人理解就是,一次一个....
  • [技术干货] MySQL中SQL Mode的查看与设置详解
    MySQL中SQL Mode的查看与设置MySQL可以运行在不同的模式下,而且可以在不同的场景下运行不同的模式,这主要取决于系统变量 sql_mode 的值。本文主要介绍一下这个值的查看与设置,主要在Mac系统下。对于每个模式的意义和作用,网上很容易找到,本文不做介绍。按作用区域和时间可分为3个级别,分别是会话级别,全局级别,配置(永久生效)级别。会话级别:查看-select @@session.sql_mode;修改-set @@session.sql_mode='xx_mode' set session sql_mode='xx_mode'session均可省略,默认session,仅对当前会话有效全局级别:查看-select @@global.sql_mode;修改-set global sql_mode='xx_mode'; set @@global.sql_mode='xx_mode';需高级权限,仅对下次连接生效,不影响当前会话(亲测过),且MySQL重启后失效,因为MySQL重启时会重新读取配置文件里对应值,如果需永久生效需要修改配置文件里的值。配置修改(永久生效):打开 vi /etc/my.cnf在下面添加[mysqld] sql-mode = "xx_mode"注意:[mysqld]必须加,且sql-mode中间是“-”,而不是下划线。保存退出,重启服务器,即可永久生效。因为Mac下安装MySQL没有配置文件,所以需要自己手动添加。ps最后额外加一点东西,就是Mac下MySQL的启动、停止、重启等操作。主要有两种方式,一是点击”系统偏好设置“对应的MySQL面板,可实现管理。二是命令行方式。MySQL相关的执行脚本,常用的主要是下面两个:/usr/local/mysql/support-files/mysql.server /usr/local/mysql/bin/mysqlmysql.server是控制服务器的启停等操作。mysql.server start|stop|restart|statusmysql主要用于连接服务器。mysql -uroot -p **** -h **** -D **有些需要sudo权限,且可将相关路径添加到环境变量,可简化书写,至于如何添加是不做介绍了。知识点扩展:Strict Mode阐述根据 mysql5.0以上版本 strict mode (STRICT_TRANS_TABLES) 的限制:1).不支持对not null字段插入null值2).不支持对自增长字段插入''值,可插入null值3).不支持 text 字段有默认值看下面代码:(第一个字段为自增字段)$query="insert into demo values('','$firstname','$lastname','$sex')";上边代码只在非strict模式有效。Code代码$query="insert into demo values(NULL,'$firstname','$lastname','$sex')";上边代码只在strict模式有效。把空值''换成了NULL.
  • [技术干货] MySQL UPDATE多表关联更新
    MySQL 可以基于多表查询更新数据。对于多表的 UPDATE 操作需要慎重,建议在更新前,先使用 SELECT 语句查询验证更新的数据与自己期望的是否一致。下面我们建两张表,一张表为 product 表,用来存放产品信息,其中有产品价格字段 price;另外一张表是 product_price 表。现要将 product_price 表中的价格字段 price 更新为 product 表中价格字段 price 的 80%。操作前先分别查看两张表的数据,SQL 语句和运行结果如下:mysql> SELECT * FROM product; +----+-----------+-----------------------+-------+----------+ | id | productid | productname           | price | isdelete | +----+-----------+-----------------------+-------+----------+ |  1 |      1001 | C语言中文网Java教程   |   100 |        0 | |  2 |      1002 | C语言中文网MySQL教程  |   110 |        0 | |  3 |      1003 | C语言中文网Python教程 |   120 |        0 | |  4 |      1004 | C语言中文网C语言教程  |   150 |        0 | |  5 |      1005 | C语言中文网GoLang教程 |   160 |        0 | +----+-----------+-----------------------+-------+----------+ 5 rows in set (0.02 sec) mysql> SELECT * FROM product_price; +----+-----------+-------+ | id | productid | price | +----+-----------+-------+ |  1 |      1001 |  NULL | |  2 |      1002 |  NULL | |  3 |      1003 |  NULL | |  4 |      1004 |  NULL | |  5 |      1005 |  NULL | +----+-----------+-------+ 5 rows in set (0.01 sec)下面是 MySQL 多表更新在实践中的几种不同写法。执行不同的 SQL 语句,仔细观察 SQL 语句执行后表中数据的变化,很容易就能理解多表联合更新的用法。1. 使用UPDATE在 MySQL 中,可以使用“UPDATE table1 t1,table2,...,table n”的方式来多表更新,SQL 语句和运行结果如下:mysql> UPDATE product p, product_price pp SET pp.price = p.price * 0.8 WHERE p.productid= pp.productId; Query OK, 5 rows affected (0.02 sec) Rows matched: 5  Changed: 5  Warnings: 0 mysql> SELECT * FROM product_price; +----+-----------+-------+ | id | productid | price | +----+-----------+-------+ |  1 |      1001 |    80 | |  2 |      1002 |    88 | |  3 |      1003 |    96 | |  4 |      1004 |   120 | |  5 |      1005 |   128 | +----+-----------+-------+ 5 rows in set (0.00 sec)2. 通过INNER JOIN另外一种方法是使用 INNER JOIN 来多表更新。SQL 语句如下:mysql> UPDATE product p INNER JOIN product_price pp ON p.productid= pp.productid SET pp.price = p.price * 0.8; Query OK, 5 rows affected (0.09 sec) Rows matched: 5  Changed: 5  Warnings: 0 mysql> SELECT * FROM product_price; +----+-----------+-------+ | id | productid | price | +----+-----------+-------+ |  1 |      1001 |    80 | |  2 |      1002 |    88 | |  3 |      1003 |    96 | |  4 |      1004 |   120 | |  5 |      1005 |   128 | +----+-----------+-------+ 5 rows in set (0.00 sec)3. 通过LEFT JOIN也可以使用 LEFT JOIN 来做多表更新,如果 product_price 表中没有产品价格记录的话,将 product 表的 isdelete 字段设置为 1。在 product 表添加 1006 商品,且不在 product_price 表中添加对应信息,SQL 语句如下。mysql> UPDATE product p LEFT JOIN product_price pp ON p.productid= pp.productid SET p.isdelete = 1 WHERE pp.productid IS NULL; Query OK, 1 row affected (0.04 sec) Rows matched: 1  Changed: 1  Warnings: 0 mysql> SELECT * FROM product; +----+-----------+-----------------------+-------+----------+ | id | productid | productname           | price | isdelete | +----+-----------+-----------------------+-------+----------+ |  1 |      1001 | C语言中文网Java教程   |   100 |        0 | |  2 |      1002 | C语言中文网MySQL教程  |   110 |        0 | |  3 |      1003 | C语言中文网Python教程 |   120 |        0 | |  4 |      1004 | C语言中文网C语言教程  |   150 |        0 | |  5 |      1005 | C语言中文网GoLang教程 |   160 |        0 | |  6 |      1006 | C语言中文网Spring教程 |  NULL |        1 | +----+-----------+-----------------------+-------+----------+ 6 rows in set (0.00 sec)4. 通过子查询也可以通过子查询进行多表更新,SQL 语句和执行过程如下:mysql> UPDATE product_price pp SET price=(SELECT price*0.8 FROM product WHERE productid = pp.productid); Query OK, 5 rows affected (0.00 sec) Rows matched: 5  Changed: 5  Warnings: 0 mysql> SELECT * FROM product_price; +----+-----------+-------+ | id | productid | price | +----+-----------+-------+ |  1 |      1001 |    80 | |  2 |      1002 |    88 | |  3 |      1003 |    96 | |  4 |      1004 |   120 | |  5 |      1005 |   128 | +----+-----------+-------+ 5 rows in set (0.00 sec)另外,上面的几个例子都是在两张表之间做关联,只更新一张表中的记录。MySQL 也可以同时更新两张表,如下语句就同时修改了两个表。UPDATE product p INNER JOIN product_price pp ON p.productid= pp.productid SET pp.price = p.price * 0.8, p.dateUpdate = CURDATE()两张表做关联,同时更新了 product_price 表的 price 字段和 product 表的 dateUpdate 两个字段。值得一提的是,日常开发中,一般都是用单表 UPDATE 语句,很少写多表关联的 UPDATE。
  • [技术干货] MySQL子查询
    子查询是 MySQL 中比较常用的查询方法,通过子查询可以实现多表查询。子查询指将一个查询语句嵌套在另一个查询语句中。子查询可以在 SELECT、UPDATE 和 DELETE 语句中使用,而且可以进行多层嵌套。在实际开发时,子查询经常出现在 WHERE 子句中。子查询在 WHERE 中的语法格式如下:WHERE <表达式> <操作符> (子查询)其中,操作符可以是比较运算符和 IN、NOT IN、EXISTS、NOT EXISTS 等关键字。1)IN | NOT IN当表达式与子查询返回的结果集中的某个值相等时,返回 TRUE,否则返回 FALSE;若使用关键字 NOT,则返回值正好相反。2)EXISTS | NOT EXISTS用于判断子查询的结果集是否为空,若子查询的结果集不为空,返回 TRUE,否则返回 FALSE;若使用关键字 NOT,则返回的值正好相反。例 1使用子查询在 tb_students_info 表和 tb_course 表中查询学习 Java 课程的学生姓名,SQL 语句和运行结果如下。mysql> SELECT name FROM tb_students_info      -> WHERE course_id IN (SELECT id FROM tb_course WHERE course_name = 'Java'); +-------+ | name  | +-------+ | Dany  | | Henry | +-------+ 2 rows in set (0.01 sec)结果显示,学习 Java 课程的只有 Dany 和 Henry。上述查询过程也可以分为以下 2 步执行,实现效果是相同的。1)首先单独执行内查询,查询出 tb_course 表中课程为 Java 的 id,SQL 语句和运行结果如下。mysql> SELECT id FROM tb_course      -> WHERE course_name = 'Java'; +----+ | id | +----+ |  1 | +----+ 1 row in set (0.00 sec)可以看到,符合条件的 id 字段的值为 1。2)然后执行外层查询,在 tb_students_info 表中查询 course_id 等于 1 的学生姓名。SQL 语句和运行结果如下。mysql> SELECT name FROM tb_students_info      -> WHERE course_id IN (1); +-------+ | name  | +-------+ | Dany  | | Henry | +-------+ 2 rows in set (0.00 sec)习惯上,外层的 SELECT 查询称为父查询,圆括号中嵌入的查询称为子查询(子查询必须放在圆括号内)。MySQL 在处理上例的 SELECT 语句时,执行流程为:先执行子查询,再执行父查询。例 2与例 1 类似,在 SELECT 语句中使用 NOT IN 关键字,查询没有学习 Java 课程的学生姓名,SQL 语句和运行结果如下。mysql> SELECT name FROM tb_students_info      -> WHERE course_id NOT IN (SELECT id FROM tb_course WHERE course_name = 'Java'); +--------+ | name   | +--------+ | Green  | | Jane   | | Jim    | | John   | | Lily   | | Susan  | | Thomas | | Tom    | | LiMing | +--------+ 9 rows in set (0.01 sec)可以看出,运行结果与例 1 刚好相反,没有学习 Java 课程的是除了 Dany 和 Henry 之外的学生。例 3使用=运算符,在 tb_course 表和 tb_students_info 表中查询出所有学习 Python 课程的学生姓名,SQL 语句和运行结果如下。mysql> SELECT name FROM tb_students_info     -> WHERE course_id = (SELECT id FROM tb_course WHERE course_name = 'Python'); +------+ | name | +------+ | Jane | +------+ 1 row in set (0.00 sec)结果显示,学习 Python 课程的学生只有 Jane。例 4使用<>运算符,在 tb_course 表和 tb_students_info 表中查询出没有学习 Python 课程的学生姓名,SQL 语句和运行结果如下。mysql> SELECT name FROM tb_students_info     -> WHERE course_id <> (SELECT id FROM tb_course WHERE course_name = 'Python'); +--------+ | name   | +--------+ | Dany   | | Green  | | Henry  | | Jim    | | John   | | Lily   | | Susan  | | Thomas | | Tom    | | LiMing | +--------+ 10 rows in set (0.00 sec)可以看出,运行结果与例 3 刚好相反,没有学习 Python 课程的是除了 Jane 之外的学生。例 5查询 tb_course 表中是否存在 id=1 的课程,如果存在,就查询出 tb_students_info 表中的记录,SQL 语句和运行结果如下。mysql> SELECT * FROM tb_students_info     -> WHERE EXISTS(SELECT course_name FROM tb_course WHERE id=1); +----+--------+------+------+--------+-----------+ | id | name   | age  | sex  | height | course_id | +----+--------+------+------+--------+-----------+ |  1 | Dany   |   25 | 男   |    160 |         1 | |  2 | Green  |   23 | 男   |    158 |         2 | |  3 | Henry  |   23 | 女   |    185 |         1 | |  4 | Jane   |   22 | 男   |    162 |         3 | |  5 | Jim    |   24 | 女   |    175 |         2 | |  6 | John   |   21 | 女   |    172 |         4 | |  7 | Lily   |   22 | 男   |    165 |         4 | |  8 | Susan  |   23 | 男   |    170 |         5 | |  9 | Thomas |   22 | 女   |    178 |         5 | | 10 | Tom    |   23 | 女   |    165 |         5 | | 11 | LiMing |   22 | 男   |    180 |         7 | +----+--------+------+------+--------+-----------+ 11 rows in set (0.01 sec)由结果可以看到,tb_course 表中存在 id=1 的记录,因此 EXISTS 表达式返回 TRUE,外层查询语句接收 TRUE 之后对表 tb_students_info 进行查询,返回所有的记录。EXISTS 关键字可以和其它查询条件一起使用,条件表达式与 EXISTS 关键字之间用 AND 和 OR 连接。例 6查询 tb_course 表中是否存在 id=1 的课程,如果存在,就查询出 tb_students_info 表中 age 字段大于 24 的记录,SQL 语句和运行结果如下。mysql> SELECT * FROM tb_students_info     -> WHERE age>24 AND EXISTS(SELECT course_name FROM tb_course WHERE id=1); +----+------+------+------+--------+-----------+ | id | name | age  | sex  | height | course_id | +----+------+------+------+--------+-----------+ |  1 | Dany |   25 | 男   |    160 |         1 | +----+------+------+------+--------+-----------+ 1 row in set (0.01 sec)结果显示,从 tb_students_info 表中查询出了一条记录,这条记录的 age 字段取值为 25。内层查询语句从 tb_course 表中查询到记录,返回 TRUE。外层查询语句开始进行查询。根据查询条件,从 tb_students_info 表中查询 age 大于 24 的记录。拓展子查询的功能也可以通过表连接完成,但是子查询会使 SQL 语句更容易阅读和编写。一般来说,表连接(内连接和外连接等)都可以用子查询替换,但反过来却不一定,有的子查询不能用表连接来替换。子查询比较灵活、方便、形式多样,适合作为查询的筛选条件,而表连接更适合于查看连接表的数据。
  • [技术干货] MySQL修改和删除事件(ALTER/DROP EVENT)
    修改事件在 MySQL 中,事件创建之后,可以使用 ALTER EVENT 语句修改其定义和相关属性。修改事件的语法格式如下:ALTER EVENT event_name     ON SCHEDULE schedule     [ON COMPLETION [NOT] PRESERVE]     [ENABLE | DISABLE | DISABLE ON SLAVE]     [COMMENT 'comment']    DO event_body;另外,ALTER EVENT 语句还有一个用法就是让一个事件关闭或再次让其活动。例 1修改 e_test 事件,让其每隔 30 秒向表 tb_eventtest 中插入一条数据,SQL 语句和运行结果如下所示:mysql> ALTER EVENT e_test ON SCHEDULE EVERY 30 SECOND     -> ON COMPLETION PRESERVE     -> DO INSERT INTO tb_eventtest(user,createtime) VALUES('MySQL',NOW()); Query OK, 0 rows affected (0.04 sec) mysql> TRUNCATE TABLE tb_eventtest; Query OK, 0 rows affected (0.04 sec) mysql> SELECT * FROM tb_eventtest; +----+-------+---------------------+ | id | user  | createtime          | +----+-------+---------------------+ |  1 | MySQL | 2020-05-21 13:23:49 | |  2 | MySQL | 2020-05-21 13:24:19 | +----+-------+---------------------+ 2 rows in set (0.00 sec)由结果可以看出,修改事件后,表 tb_eventtest 中的数据由原来的每 5 秒插入一条,变为每 30 秒插入一条。使用 ALTER EVENT 语句还可以临时关闭一个已经创建的事件。例 2临时关闭事件 e_test 的具体代码如下所示:mysql> ALTER EVENT e_test DISABLE; Query OK, 0 rows affected (0.00 sec)查询 tb_eventtest 表中的数据,SQL 语句如下:SELECT * FROM tb_eventtest;为了确定事件已关闭,可以查询两次(每次间隔 1 分钟)tb_eventtest 表的数据,SQL 语句和运行结果如下所示:mysql> TRUNCATE TABLE tb_eventtest; Query OK, 0 rows affected (0.05 sec) mysql> SELECT * FROM tb_eventtest; Empty set (0.00 sec) mysql> SELECT * FROM tb_eventtest; Empty set (0.00 sec)由结果可以看出,临时关闭事件后,系统就不再继续向表 tb_eventtest 中插入数据了。删除事件在 MySQL 中,可以使用 DROP EVENT 语句删除已经创建的事件。语法格式如下:DROP EVENT [IF EXISTS] event_name;例 3删除事件 e_test,SQL 语句和运行结果如下:mysql> DROP EVENT IF EXISTS e_test; Query OK, 0 rows affected (0.01 sec) mysql> SELECT * FROM information_schema.events \G Empty set (0.00 sec)
  • SQL注入是什么,如何避免
    SQL 注入SQL 注入(SQL Injection)是发生在 Web 程序中数据库层的安全漏洞,是网站存在最多也是最简单的漏洞。主要原因是程序对用户输入数据的合法性没有判断和处理,导致攻击者可以在 Web 应用程序中事先定义好的 SQL 语句中添加额外的 SQL 语句,在管理员不知情的情况下实现非法操作,以此来实现欺骗数据库服务器执行非授权的任意查询,从而进一步获取到数据信息。简而言之,SQL 注入就是在用户输入的字符串中加入 SQL 语句,如果在设计不良的程序中忽略了检查,那么这些注入进去的 SQL 语句就会被数据库服务器误认为是正常的 SQL 语句而运行,攻击者就可以执行计划外的命令或访问未被授权的数据。SQL 注入已经成为互联网世界 Web 应用程序的最大风险,我们有必要从开发、测试、上线等各个环节对其进行防范。下面介绍 SQL 注入的原理及避免 SQL 注入的一些方法。SQL注入的原理SQL 注入的原理主要有以下 4 点:1)恶意拼接查询我们知道,SQL 语句可以查询、插入、更新和删除数据,且使用分号来分隔不同的命令。例如:SELECT * FROM users WHERE user_id = $user_id其中,user_id 是传入的参数,如果传入的参数值为“1234; DELETE FROM users”,那么最终的查询语句会变为:SELECT * FROM users WHERE user_id = 1234; DELETE FROM users如果以上语句执行,则会删除 users 表中的所有数据。2)利用注释执行非法命令。SQL 语句中可以插入注释。例如:SELECT COUNT(*) AS 'num' FROM game_score WHERE game_id=24411 AND version=$version如果 version 包含了恶意的字符串'-1' OR 3 AND SLEEP(500)--,那么最终查询语句会变为:SELECT COUNT(*) AS 'num' FROM game_score WHERE game_id=24411 AND version='-1' OR 3 AND SLEEP(500)--以上恶意查询只是想耗尽系统资源,SLEEP(500) 将导致 SQL 语句一直运行。如果其中添加了修改、删除数据的恶意指令,那么将会造成更大的破坏。3)传入非法参数SQL 语句中传入的字符串参数是用单引号引起来的,如果字符串本身包含单引号而没有被处理,那么可能会篡改原本 SQL 语句的作用。 例如:SELECT * FROM user_name WHERE user_name = $user_name如果 user_name 传入参数值为 G'chen,那么最终的查询语句会变为:SELECT * FROM user_name WHERE user_name ='G'chen'一般情况下,以上语句会执行出错,这样的语句风险比较小。虽然没有语法错误,但可能会恶意产生 SQL 语句,并且以一种你不期望的方式运行。4)添加额外条件在 SQL 语句中添加一些额外条件,以此来改变执行行为。条件一般为真值表达式。例如:UPDATE users SET userpass='$userpass' WHERE user_id=$user_id;如果 user_id 被传入恶意的字符串“1234 OR TRUE”,那么最终的 SQL 语句会变为:UPDATE users SET userpass= '123456' WHERE user_id=1234 OR TRUE;这将更改所有用户的密码。避免SQL注入对于 SQL 注入,我们可以采取适当的预防措施来保护数据安全。下面是避免 SQL 注入的一些方法。1. 过滤输入内容,校验字符串过滤输入内容就是在数据提交到数据库之前,就把用户输入中的不合法字符剔除掉。可以使用编程语言提供的处理函数或自己的处理函数来进行过滤,还可以使用正则表达式匹配安全的字符串。如果值属于特定的类型或有具体的格式,那么在拼接 SQL 语句之前就要进行校验,验证其有效性。比如对于某个传入的值,如果可以确定是整型,则要判断它是否为整型,在浏览器端(客户端)和服务器端都需要进行验证。2. 参数化查询参数化查询目前被视作是预防 SQL 注入攻击最有效的方法。参数化查询是指在设计与数据库连接并访问数据时,在需要填入数值或数据的地方,使用参数(Parameter)来给值。MySQL 的参数格式是以“?”字符加上参数名称而成,如下所示:UPDATE myTable SET c1 = ?c1, c2 = ?c2, c3 = ?c3 WHERE c4 = ?c4在使用参数化查询的情况下,数据库服务器不会将参数的内容视为 SQL 语句的一部分来进行处理,而是在数据库完成 SQL 语句的编译之后,才套用参数运行。因此就算参数中含有破坏性的指令,也不会被数据库所运行。3. 安全测试、安全审计除了开发规范,还需要合适的工具来确保代码的安全。我们应该在开发过程中应对代码进行审查,在测试环节使用工具进行扫描,上线后定期扫描安全漏洞。通过多个环节的检查,一般是可以避免 SQL 注入的。有些人认为存储过程可以避免 SQL 注入,存储过程在传统行业里用得比较多,对于权限的控制是有一定用处的,但如果存储过程用到了动态查询,拼接 SQL,一样会存在安全隐患。下面是在开发过程中可以避免 SQL 注入的一些方法。1. 避免使用动态SQL避免将用户的输入数据直接放入 SQL 语句中,最好使用准备好的语句和参数化查询,这样更安全。2. 不要将敏感数据保留在纯文本中加密存储在数据库中的私有/机密数据,这样可以提供了另一级保护,以防攻击者成功地排出敏感数据。3. 限制数据库权限和特权将数据库用户的功能设置为最低要求;这将限制攻击者在设法获取访问权限时可以执行的操作。4. 避免直接向用户显示数据库错误攻击者可以使用这些错误消息来获取有关数据库的信息。一些编程框架对于写出更安全的代码也有一定的帮助,因为它提供了一些处理字符串的函数和使用查询参数的方法。但同样,你仍然可以编写出不安全的 SQL 语句。所以归根到底,我们需要有良好的编码规范,并能充分利用参数化查询、字符串处理和参数校验等多种办法来保护数据库和程序的安全。说明:SQL 注入技术不是单凭一篇文章就可以讲完的,这里只带领大家掌握 SQL 注入的原理及常见的几种防止 SQL 注入的方法。
  • [技术干货] MySQL创建事件(CREATE EVENT)
    MySQL创建事件(CREATE EVENT)在 MySQL 中,可以通过 CREATE EVENT 语句来创建事件,其语法格式如下:CREATE EVENT [IF NOT EXISTS] event_name     ON SCHEDULE schedule     [ON COMPLETION [NOT] PRESERVE]     [ENABLE | DISABLE | DISABLE ON SLAVE]     [COMMENT 'comment']     DO event_body;从上面的语法可以看出,CRATE EVENT 语句由多个子句组成,各子句的详细说明如下表所示。子句说明DEFINER可选用于定义事件执行时检查权限的用户IF NOT EXISTS可选用于判断要创建的事件是否存在EVENT event_name必选用于指定事件名称,event_name 的最大长度为 64 个字符如果未指定 event_name,则默认为当前的 MySQL 用户名(不区分大小写)ON SCHEDULE schedule必选用于定义执行的时间和时间间隔schedule 表示触发点ON COMPLETION [NOT] PRESERVE可选用于定义事件是否循环执行,即是一次执行还是永久执行,默认为一次执行,即 NOT PRESERVEENABLE | DISABLE | DISABLE ON SLAVE可选,用于指定事件的一种属性。其中,关键字 ENABLE 表示该事件是活动的,即调度器检查事件是否必须调用;关键字 DISABLE 表示该事件是关闭的,即事件的声明存储到目录中,但是调度器不会检查它是否应该调用;关键字 DISABLE ON SLAVE 表示事件在从机中是关闭的。如果不指定以上 3 个选项中的任何一个,默认为 ENABLECOMMENT 'comment'可选,用于定义事件的注释DO event_body必选用于指定事件启动时所要执行的代码,可以是任何有效的 SQL 语句、存储过程或者一个计划执行的事件。如果包含多条语句,则可以使用 BEGIN..END 复合结构在 ON SCHEDULE 子句中,参数 schedule 的值为一个 AT 子句,用于指定事件在某个时刻发生,其语法格式如下:AT timestamp [+ INTERVAL interval]...     | EVERY interval     [STARTS timestamp [+ INTERVAL interval] ...]     [ENDS timestamp[+ INTERVAL interval]...]参数说明如下:timestamp:一般用于只执行一次,表示一个具体的时间点,后面加上一个时间间隔,表示在这个时间间隔后事件发生。EVERY 子句:用于事件在指定时间区间内每隔多长时间发生一次,其中 STARTS 子句用于指定开始时间;ENDS 子句用于指定结束时间。interval:一般用于周期性执行,表示一个从现在开始的时间,其值由一个数值和单位构成。例如,使用“4 WEEK”表示 4 周,使用“'1:10'HOUR_MINUTE”表示 1 小时 10 分钟。间隔的长短用 DATE_ADD() 函数支配。interval 参数可以是以下值:YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |     WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |     DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND一般情况下,不建议使用不标准(以上未加粗关键字)的时间单位。例 1在 test 数据库中创建一个名称为 e_test 的事件,用于每隔 5 秒向表 tb_eventtest 中插入一条数据。创建 tb_eventtest 表,SQL 语句和运行结果如下:mysql> CREATE TABLE tb_eventtest(     -> id INT(11) PRIMARY KEY AUTO_INCREMENT,     -> user VARCHAR(20),     -> createtime DATETIME); Query OK, 0 rows affected (0.07 sec)创建 e_test 事件,SQL 语句和运行结果如下:mysql> CREATE EVENT IF NOT EXISTS e_test ON SCHEDULE EVERY 5 SECOND     -> ON COMPLETION PRESERVE     -> DO INSERT INTO tb_eventtest(user,createtime)VALUES('MySQL',NOW()); Query OK, 0 rows affected (0.04 sec)创建事件后,查询 tb_eventtest 中的数据,SQL 语句和运行结果如下:mysql> SELECT * FROM tb_eventtest; +----+-------+---------------------+ | id | user  | createtime          | +----+-------+---------------------+ |  1 | MySQL | 2020-05-21 10:41:39 | |  2 | MySQL | 2020-05-21 10:41:44 | |  3 | MySQL | 2020-05-21 10:41:49 | |  4 | MySQL | 2020-05-21 10:41:54 | +----+-------+---------------------+ 4 rows in set (0.01 sec)从结果可以看出,系统每隔 5 秒插入一条数据,这说明事件创建执行成功了。
  • [技术干货] sql分类及语法
    sql分类:(1)DDL:数据库定义语言,用于定义表,列,索引等数据库对象. create,drop,alter等(2)DML:数据库操纵语言,增删改查数据库的记录insert,delete,update,select等(3)DCL:数据库控制语言,定义了数据库的表,字段,用户的访问权限和安全级别 grant,revoke等2.DDL语句(1)创建数据库:create database test1;(2)删除数据库:drop database test1;(3)创建表:create table user(name varchar(10),age int(2));(4)删除表:drop table user;(5)修改表:alter table user modify name varchar(20);         alter table user add column sex varchar(10);        alter table user drop column age;         Alter table user change age age1 int(4);     Alter table user rename user1;3.DML语句(1)插入记录insert into user(name,age)values(‘zhangsan’,18);(2)删除记录:delete from user where name=’张三’(3)更新记录:update user set age=1 where name=’ 张三’(4)查询记录:select * from user;(5)查询不重复的记录j:select distinct name from user;(6)排序查询:select from user order by age desc;(7)限制排序:select * from user order by age limit 1,30;(8)聚合:select name,age from user group by age;(9)聚合后条件:select name,age from user group by age having age > 18;(having和where的区别:having是聚合后条件,where是聚合前条件)(10)表连接: ①内连接:Select ename,deptname from emp,dept where emp.deptno=dept.deptno;②外连接:左连接:(查询 emp 中所有用户名和所在部门名称:) select ename,deptname from emp left join dept on emp.deptno=dept.deptno; 右连接:select ename,deptname from emp left join dept on emp.deptno=dept.deptno;· 左连接:包含所有的左边表中的记录甚至是右边表中没有和它匹配的记录· 右连接:包含所有的右边表中的记录甚至是左边表中没有和它匹配的记录(11)子查询:需要的条件是另一个select的结果(子查询的关键字主要包括in, not in,=,!=,exists,not exists )(如果查询结果数唯一,可用=代替in)select * from emp where deptno in(select deptno from dept);select * from emp where deptno = (select deptno from dept limit 1);(12)记录联合:select * from t1 union select * from t2select * from t1 union all select * from t2(union和union all的区别:union是将union all查询出来的结果进行了一次distinct,去除重复后的结果)4.DCL语句(1)授权:创建一个数据库用户 z1,具有对 sakila 数据库中所有表的 SELECT/INSERT 权限:grant select,insert on sakila.* to 'z1'@'localhost' identified by '123';(2)收回权限:由于权限变更,需要将 z1 的权限变更,收回 INSERT,只能对数据进行 SELECT 操作:revoke insert on sakila.* from 'z1'@'localhost';mysql支持的数据类型数字类型,字符串类型,日期和时间类型1.数值类型2.字符串类型3.日期类型mysdql中的运算符(1)算数运算符(2)比较运算符(3)逻辑运算符(4)位运算符mysql中常用的函数1.字符串函数2.数值函数3.日期时间函数4.其它常用函数表类型(存储引擎的选择)1.查询当前数据库支持的存储引擎Show engines \G2.Mysql常见存储引擎对比    (Mysql在V5.1之前默认存储引擎是MyISAM;在此之后默认存储引擎是InnoDB)(1)MyisamMysql在V5.1之前默认存储引擎是MyISAM;在此之后默认存储引擎是InnoDBMyisam不支持事务和外键优势是访问速度快(2)InnodbInnodb提供事务和外键占用磁盘空间保留索引,写的效率比innodb差(3)Memory数据存放在内存中访问速度非常快(使用hash索引)但服务一旦关闭,表中的数据丢失
  • [技术干货] 获取SqlServer存储过程定义的三种方法
    存储过程的概念存储过程Procedure是一组为了完成特定功能的SQL语句集合,经编译后存储在数据库中,用户通过指定存储过程的名称并给出参数来执行。存储过程中可以包含逻辑控制语句和数据操纵语句,它可以接受参数、输出参数、返回单个或多个结果集以及返回值。由于存储过程在创建时即在数据库服务器上进行了编译并存储在数据库中,所以存储过程运行要比单个的SQL语句块要快。同时由于在调用时只需用提供存储过程名和必要的参数信息,所以在一定程度上也可以减少网络流量、简单网络负担。存储过程的优点                        A、 存储过程允许标准组件式编程                                存储过程创建后可以在程序中被多次调用执行,而不必重新编写该存储过程的SQL语句。                                而且数据库专业人员可以随时对存储过程进行修改,但对应用程序源代码却毫无影响,                                从而极大的提高了程序的可移植性。                       B、  存储过程能够实现较快的执行速度                                如果某一操作包含大量的T-SQL语句代码,分别被多次执行,那么存储过程要比批处理的执行速度快得多。                                因为存储过程是预编译的,在首次运行一个存储过程时,查询优化器对其进行分析、优化,并给出最终被存在系统表中的存储计划。                                而批处理的T-SQL语句每次运行都需要预编译和优化,所以速度就要慢一些。                      C、   存储过程减轻网络流量                              对于同一个针对数据库对象的操作,如果这一操作所涉及到的T-SQL语句被组织成一存储过程,                               那么当在客户机上调用该存储过程时,网络中传递的只是该调用语句,否则将会是多条SQL语句。                               从而减轻了网络流量,降低了网络负载。                       D、 存储过程可被作为一种安全机制来充分利用                               系统管理员可以对执行的某一个存储过程进行权限限制,从而能够实现对某些数据访问的限制,                               避免非授权用户对数据的访问,保证数据的安全。  重点给大家介绍SqlServer存储过程定义的三种方法,一起看看吧!第一种:declare @p_text varchar(max) SELECT @p_text= text  FROM syscomments  WHERE id = ( SELECT id FROM sysobjects WHERE name = ' 存储过程名称 ') print @p_text但当存储过程内容比较长的时候,text的内容显示不完全。第二种:declare @p_text varchar(max) SELECT @p_text= definition FROM sys.sql_modules  JOIN sys.objects ON sys.sql_modules.object_id=sys.objects.object_id --and type='P'  and sys.objects.name='sp_MSupd_dboInd_IndependenceReportTo' print @p_text内容格式良好,但是Print本身不能显示超过 8000字节的内容,所以存储过程内容太长这种方式还是不行。第三种:exec sp_helptext '[sp_MSupd_dboRpt_Customer]'返回定义的内容但是一个文本行内容一个记录行,这样就可以解决上面的不足了。
  • [技术干货] Sql Server数据库常用Transact-SQL脚本
    Transact-SQLTransact-SQL(又称 T-SQL),是在 Microsoft SQL Server 和 Sybase SQL Server 上的 ANSI SQL 实现,与 Oracle 的 PL/SQL 性质相近(不只是实现 ANSI SQL,也为自身数据库系统的特性提供实现支持),在 Microsoft SQL Server 和 Sybase Adaptive Server 中仍然被使用为核心的查询语言。数据库1、创建数据库USE master ;  GO  CREATE DATABASE Sales  ON ( NAME = Sales_dat,   FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\saledat.mdf',   SIZE = 10,   MAXSIZE = 50,   FILEGROWTH = 5 )  LOG ON ( NAME = Sales_log,   FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\salelog.ldf',   SIZE = 5MB,   MAXSIZE = 25MB,   FILEGROWTH = 5MB ) ;  GO2、查看数据库SELECT name, database_id, create_date  FROM sys.databases ;3、删除数据库DROP DATABASE Sales;表1、创建表CREATE TABLE PurchaseOrderDetail  (   ID uniqueidentifier NOT NULL  ,LineNumber smallint NOT NULL  ,ProductID int NULL  ,UnitPrice money NULL  ,OrderQty smallint NULL  ,ReceivedQty float NULL  ,RejectedQty float NULL  ,DueDate datetime NULL );2、删除表DROP TABLE dbo.PurchaseOrderDetail;3、重命名表EXEC sp_rename 'Sales.SalesTerritory', 'SalesTerr';列1、添加列ALTER TABLE dbo.doc_exa ADD column_b VARCHAR(20) NULL, column_c INT NULL ;2、删除列ALTER TABLE dbo.doc_exb DROP COLUMN column_b;3、重命名列EXEC sp_rename 'Sales.SalesTerritory.TerritoryID', 'TerrID', 'COLUMN';约束1、主键--在现有表中创建主键 ALTER TABLE Production.TransactionHistoryArchive  ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARY KEY CLUSTERED (TransactionID);   --在新表中创建主键 CREATE TABLE Production.TransactionHistoryArchive1  (   TransactionID int IDENTITY (1,1) NOT NULL   , CONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARY KEY CLUSTERED (TransactionID)  ) ;   --查看主键  SELECT name FROM sys.key_constraints  WHERE type = 'PK' AND OBJECT_NAME(parent_object_id) = N'TransactionHistoryArchive';  GO  --删除主键 ALTER TABLE Production.TransactionHistoryArchive  DROP CONSTRAINT PK_TransactionHistoryArchive_TransactionID;  GO视图1、创建视图CREATE VIEW V_EmployeeHireDate  AS SELECT p.FirstName, p.LastName, e.HireDate  FROM HumanResources.Employee AS e JOIN Person.Person AS p  ON e.BusinessEntityID = p.BusinessEntityID ;  GO2、删除视图DROP VIEW V_EmployeeHireDate;存储过程1、创建存储过程CREATE PROCEDURE P_UspGetEmployeesTest   @LastName nvarchar(50),   @FirstName nvarchar(50)  AS  SELECT FirstName, LastName, Department   FROM HumanResources.vEmployeeDepartmentHistory   WHERE FirstName = @FirstName AND LastName = @LastName   AND EndDate IS NULL;  GO2、删除存储过程DROP PROCEDURE P_UspGetEmployeesTest;3、执行存储过程EXEC P_UspGetEmployeesTest N'Ackerman', N'Pilar';  -- Or  EXEC P_UspGetEmployeesTest @LastName = N'Ackerman', @FirstName = N'Pilar';  GO  -- Or  EXECUTE P_UspGetEmployeesTest @FirstName = N'Pilar', @LastName = N'Ackerman';  GO4、重命名存储过程EXEC sp_rename 'P_UspGetAllEmployeesTest', 'P_UspEveryEmployeeTest2';5、带有输出参数的存储过程CREATE PROCEDURE P_UspGetEmployeeSalesYTD  @SalesPerson nvarchar(50),  @SalesYTD money OUTPUT  AS   SELECT @SalesYTD = SalesYTD   FROM SalesPerson AS sp   JOIN vEmployee AS e ON e.BusinessEntityID = sp.BusinessEntityID   WHERE LastName = @SalesPerson;  RETURN  GO   --调用 DECLARE @SalesYTDBySalesPerson money;  EXECUTE P_UspGetEmployeeSalesYTD   N'Blythe',   @SalesYTD = @SalesYTDBySalesPerson OUTPUT;  GO数据类型                                
  • [技术干货] Sql Server数据把列根据指定内容拆分数据的方法实例
    由于工作需要,需要把数据把列根据指定的内容拆分数据其中一条数据实例 select id , XXXX FROM BIZ_PAPER where  id ='4af210ec675927fa016772bf7dd025b0'  拆分方法:select t3.id ,t3.XXXX as XXXX from ( select A.id , B.XXXX from ( SELECT id, XXXX = CONVERT(xml,'<root><v>' + REPLACE(XXXX , ',', '</v><v>') + '</v></root>') FROM BIZ_PAPER) A outer apply( SELECT XXXX = N.v.value('.', 'varchar(100)') FROM A.XXXX .nodes('/root/v') N(v)) B) t3 where t3.id ='4af210ec675927fa016772bf7dd025b0'结果在自己研究这行代码之后,做出如下讲解,如果有错误的地方还请指教SELECT id, XXXX = CONVERT(xml,'<root><v>' + REPLACE(XXXX , ',', '</v><v>') + '</v></root>') FROM BIZ_PAPER这一行的重点在于CONVERT,XML是指类型,xml 数据类型实例拆分为关系数据,则 nodes() 方法非常有用,至于XML类型的数据,我后面进行补充REPLACE 指按照 ' , ' 进行替换,并且按照指定的内容进行拼接最后的结果为outer apply这个就是表的关联,就像是left join ,但是没有on 作为关联条件,所以通过拆分之后多出来的数据就是通过这个进行关联后产生的SELECT XXXX = N.v.value('.', 'varchar(100)') FROM A.XXXX .nodes('/root/v') N(v)N.v.value('.','varchar(100)'),N是表,别名,v是列,value函数是读取标签之间的值,对于这个列子,读取的为<v>和</v>中间的值;这个可以去了解 xml类型的常用的三个方法 :value()、nodes()、exist()            value的第一个参数是一个字符串文字,从 XML 实例内部检索数据。            XQuery 必须最多返回一个值。 否则,将返回错误;value的第二个参数是指将查询结果转化为何种类型的数据。此处,'.'表示当前目录,即<v>目录,另外'..'表示上级目录,'/'表示根目录,这个跟Linux是一样的总的来说,这个语句的重点在于xml类型的使用和outer apply的使用,其他的都很好理解。这个就是我自己理解后的讲解,部分位置我自己也还没有理解透
  • [技术干货] 关于SQL优化引发的思考
    什么样的列必须要创建索引? 当一个列出现在where条件中,该列没有创建索引并且选择性大于20%,那么该列就必须创建索引,从而提升SQL 查询性能。当然了,如果表只有几百 条数据,那我们就不用创建索引了。 什么样的列需要收集直方图? 当列出现在 where条件中,列的选择性小于1%并且该列没有收 集过直方图,这样的列就应该收集直方图。注意: 千万不能对没有出现在where条件中 的列收集直方 图。对没有出现在where条件中的列收集直方图完 全是做无用功,浪费数据库资源。 执行计划中Rows是真的吗? 执行计划里面的Rows是 假的。执行计划中的Rows是根据统计信息以及一些数学公式计算出来的。 在做SQL优化的时候,经常需要做的工作就是 帮助CBO计算出比较准确的Rows。注意:我们说 的是比较准确的Rows。CBO是无法得到精确的 Rows 的,因为对表收集统计信息的时候,统计信息 一般都不会按照100%的标准采样收集,即使按照 100%的标准采样收集了表的统计信息,表中的数 据也 随时在发生变更。另外计算Rows的数学公式目 前也是有缺陷的,CBO永远不可能计算得到精确的 Rows。怎么快速找到执行计划的入口? 回答:我们可以利用光标移动**,先将光标放在Id=0这一步,然后一直向下向右 移动光标,直到找到没有儿子的Id,这个Id就是执行计划的入口。 怎么判断是哪个表与哪个表进行关联的? 回答:我们先找到表在执行计划中的Id,然后看这个Id(或者是这个Id的父亲)与 谁对齐(利用光标上下移动),它与谁对齐,就与谁进行关联。为什么有时候索引扫描比全表扫描更慢? 假设一个表有100万行数据,表的段大小为1GB。如果对表进行全表扫描,最理想的情况下,每次I/O都读取1MB数据(128个块),将1GB的表从磁盘读 入buffer cache需要1 024次I/O。在实际情况中,表的段前16个extent,每个extent都只有8个块,每次I/O只能读取8个块,而不是128个块,表中有部分 块会被缓存在buffer cache中,会引起I/O中断,那么将1GB的表从磁盘读入buffer cache可能需要耗费1 500次物理I/O。 从表中查询5万行数据,走索引。假设一个索引叶子块能存储100行数据,那么5万行数据需要扫描500个叶子块(单块读),也就是需要500次物理 I/O,然后有5万条数据需要回表,假设索引的集群因子很小(接近表的块数),假设每个数据块存储50行数据,那么回表需要耗费1 000次物理I/O(单 块读),也就是说从表中查询5万行数据,如果走索引,一共需要耗费大概1 500次物理I/O。如果索引的集群因子较大(接近表的总行数),那么回表 要耗费更多的物理I/O,可能是3 000次,而不是1000次。 走索引返回的数据越多,需要耗费的I/O次数也就越多,因此,返回大量数据应该走全表扫描或者是INDEX FAST FULLSCAN,返回少量数据才走索引 扫描。根据上述理论,我们一般建议返回表中总行数5%以内的数据,走索引扫描,超过5%走全表扫描。两表关联走不走NL是看两个表关联之后返回的数据量多少?还是看驱动表返回的数据量多少? 回答:如果两个表是1∶N关系,驱动表为1,被驱动表为N并且N很大,这时即使驱动表返回数据量很少,也不能走嵌套循环,因为两表关联之后返回的 数据量会很多。所以判断两表关联是否应该走NL应该直接查看两表关联之后返回的数据量,如果两表关联之后返回的数据量少,可以走NL;返回的数 据量多,应该走HASH连接。 此外,还需要关注被驱动表的过滤条件和是否需要回表 大表是否可以当嵌套循环(NL)驱动表? 回答:可以,如果大表过滤之后返回的数据量很少就可以当NL驱动表。select * from a,b where a.id=b.id; 如果a有100条数据,b有100万行数据,a与b是1∶N关系,N很低,应该怎么优化SQL? 回答:因为a与b是1∶N关系,N很低,我们可以在b的连接列(id)上创建索引,让a与b走嵌套循环(a nl b),这样b表会被扫描100次,但是每次扫描 表的时候走的是id列的索引(范围扫描)。怎么优化HASH连接? 回答:因为HASH连接需要将驱动表的select列和join列放入PGA中,所以,我们应该尽量避免书写select * from....语句,将需要的列放在select list中, 这样可以减少驱动表对PGA的占用,避免驱动表被溢出到临时表空间,从而提升查询性能。怎么优化排序合并连接? 回答:如果两表关联是等值关联,走的是排序合并连接,我们可以将表连接方式改为HASH连接。如果两表关联是非等值关联,比如>,>=,<,<=, <>,这时我们应该先从业务上入手,尝试将非等值关联改写为等值关联,因为非等值关联返回的结果集“类似”于笛卡儿积,当两个表都比较大的时候, 非等值关联返回的数据量相当“恐怖”。如果没有办法将非等值关联改写为等值关联,我们可以考虑增加两表的限制条件,将两个表数据量缩小。当执行计划中有笛卡儿连接应该怎么优化呢? 首先应该检查表是否有关联条件,如果表没有关联条件,那么应该询问开发与业务人员为何表没有关联条件,是否为满足业务需求而故意不写关联条件。 其次应该检查离笛卡儿连接最近的表是否真的返回1行数据,如果返回行数真的只有1行,那么走笛卡儿连接是没有问题的,如果返回行数超过1行,那就需要 检查为什么Rows会估算错误,同时要纠正错误的Rows。纠正错误的Rows之后,优化器就不会走笛卡儿连接了。 我们可以使用HINT /+opt_param('_optimizer_mjc_enabled', 'false') / 禁止笛卡儿连接。
总条数:868 到第 页
上滑加载中