sql语句大全

最新推荐文章于 2023-02-26 23:31:20 发布
最新推荐文章于 2023-02-26 23:31:20 发布
阅读量291 收藏 8
点赞数
文章标签： 数据库 设计模式 人工智能
原文链接：http://www.cnblogs.com/leskang/p/5922349.html
版权
mysql数据类型：
MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。
  • 数值类型

MySQL支持所有标准SQL数值数据类型。

这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。

关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。

BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。

作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

类型大小范围（有符号-默认）范围（无符号）用途
TINYINT1 字节(-128，127)(0，255)小整数值
SMALLINT2 字节(-32 768，32 767)(0，65 535)大整数值
MEDIUMINT3 字节(-8 388 608，8 388 607)(0，16 777 215)大整数值
INT或INTEGER4 字节(-2 147 483 648，2 147 483 647)(0，4 294 967 295)大整数值
BIGINT8 字节(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)(0，18 446 744 073 709 551 615)极大整数值
FLOAT4 字节(-3.402 823 466 E+38，1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)0，(1.175 494 351 E-38，3.402 823 466 E+38)单精度
浮点数值
DOUBLE8 字节(1.797 693 134 862 315 7 E+308，2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)双精度
浮点数值
DECIMAL对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2依赖于M和D的值依赖于M和D的值
小数值
注：M：数据的总长度（不包括小数点）   D：小数位
例如：decimal（5,2） 123.45
存入数据的时候按四舍五入计算
 
  • 日期和时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

TIMESTAMP类型有专有的自动更新特性，将在后面描述。

类型大小
(字节)		范围格式用途
DATE31000-01-01/9999-12-31YYYY-MM-DD日期值
TIME3'-838:59:59'/'838:59:59'HH:MM:SS时间值或持续时间
YEAR11901/2155YYYY年份值
DATETIME81000-01-01 00:00:00/9999-12-31 23:59:59YYYY-MM-DD HH:MM:SS混合日期和时间值
TIMESTAMP81970-01-01 00:00:00/2037 年某时YYYYMMDD HHMMSS混合日期和时间值，时间戳
  • 字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

类型大小用途
CHAR0-255字节定长字符串
VARCHAR0-65535 字节变长字符串
TINYBLOB0-255字节不超过 255 个字符的二进制字符串
TINYTEXT0-255字节短文本字符串
BLOB0-65 535字节二进制形式的长文本数据
TEXT0-65 535字节长文本数据
MEDIUMBLOB0-16 777 215字节二进制形式的中等长度文本数据
MEDIUMTEXT0-16 777 215字节中等长度文本数据
LONGBLOB0-4 294 967 295字节二进制形式的极大文本数据
LONGTEXT0-4 294 967 295字节极大文本数据

CHAR和VARCHAR类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

BINARY和VARBINARY类类似于CHAR和VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

BLOB是一个二进制大对象，可以容纳可变数量的数据。有4种BLOB类型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。

有4种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这些对应4种BLOB类型，有相同的最大长度和存储需求。
 
mysql命令：
 
数据库操作：
1、显示所有数据库：Show databases；
2、创建数据库：Create database 数据库名；
3、删除数据库：Drop database 数据库名；
 
表操作：
CREATE TABLE 表名( 属性名 数据类型 [完整性约束条件],
                              属性名 数据类型 [完整性约束条件],
                              .
                              .
                              属性名 数据表格 [完整性约束条件]
);
 
约束条件
说明
PRIMARY KEY
标识该属性为该表的主键，可以唯一的标识对应的记录
FOREIGN KEY
标识该属性为该表的外键，与某表的主键关联
NOT NULL
标识该属性不能为空
UNIQUE
标识该属性的值是唯一的
AUTO_INCREMENT
标识该属性的值自动增加
DEFAULT
为该属性设置默认值
 
注： UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。简单来说：unique=primary key+not null
区别：1、primary key只能有一个，unique可以有多个同时存在；
          2、primary key不能为空，而unique可以为空；
primary key一般在设计逻辑中用作记录标识，而unique只是用来保证唯一性
UNIQUE (bookId)//新建表时将bookId设为唯一
//添加唯一约束
ALTER TABLE Persons
ADD UNIQUE (Id_P)
CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)//设置Id_P和LastName都为唯一并命名为uc_PersonID
//删除唯一约束
ALTER TABLE Persons
DROP INDEX uc_PersonID
 
建表时创建外键
FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)//设置本表Id_p字段和Persons表的Id_p主键关联
 
查看表结构：
1，查看基本表结构： DESCRIBE(或DESC） 表名；//返回表字段及其详细信息表格
2，查看表详细结构： SHOW CREATE TABLE 表名；//返回结果是创建表的sql
 
删除/清空 表：
1、 drop table  表名； //彻底删除表格，删除字段数据，释放内存空间
2、 truncate 表名；  //删除表格数据，不删除字段，释放内存空间
3、 delete from 表名；或delete * from 表名;// 删除内容不删除定义，不释放空间,系统一行一行地删,效率较truncate低
 
truncate和delete对比:
1、truncate 将重新设置高水平线和所有的索引。在对整个表和索引进行完全浏览时，经过 truncate 操作后的表比Delete操作后的表要快得多。 当表被清空后表和表的索引将重新设置成初始大小，而delete则不能。 
2、truncate 是隐式提交，不能触发任何Delete触发器，不能通过rollback命令撤回，而delete可以，因为delete 语句每次删除一行，都在事务日志中为所删除的每行记录一项 ；
3、truncate table 在功能上与不带 WHERE 子句的 delete语句相同：二者均删除表中的全部行。但 truncate 比 delete速度快，且使用的系统和事务日志资源少，因为delete是一行一行的删除。
注意：truncate不能清空父表
 
修改表：
1，修改表名  ALTER TABLE  旧表名  RENMAE 新表名 ；
2，修改字段  ALTER TABLE 表名  CHANGE  旧属性名  新属性名  新数据类型
3，增加字段  ALTER TABLE  表名 ADD 属性名  数据类型 [完整性约束条件] [FIRST | AFTER 属性名 2]
4，删除字段  ALTER TABLE  表名  DROP 属性名
 
插入数据
  • 给表的所有字段插入数据
     格式：INSERT INTO 表名 VALUES(值 1，值 2，值 3，...，值 n)；
  • 给表的指定字段插入数据
     格式：INSERT INTO 表名(属性 1，属性 2，...，属性 n) VALUES(值 1，值 2，值 3，...，值 n)；
  • 同时插入多条记录
     INSERT INTO 表名 [(属性列表)]  VALUES(取值列表 1)，(取值列表 2)  ...， (取值列表 n)；
 
更新数据
UPDATE 表名  SET 属性名 1=取值 1，属性名 2=取值 2， ...， 属性名 n=取值 n  WHERE 条件表达式；
 
删除数据
DELETE FROM 表名 [WHERE  条件表达式]
 
查询表
 
单表查询：
 
条件查询 WHERE：
SELECT 字段 1，字段 2，字段 3...FROM 表名 WHERE 字段 [NOT] IN (元素 1，元素 2，元素 3)；//查找属性（不为）为元素1、元素2和元素3的结果
SELECT 字段 1，字段 2...FROM 表名 WHERE 条件表达式 1 AND 条件表达式 2 [...AND 条件表达式 n] //and多条件查询
SELECT 字段 1，字段 2...FROM 表名 WHERE 条件表达式 1 OR 条件表达式 2 [...OR 条件表达式 n] //or多条件查询
范围查询 BETWEEN AND：
SELECT 字段 1，字段 2，字段 3...FROM 表名 WHERE 字段 [NOT] BETWEEN 取值 1 > 指定值；//大于小于等符号
SELECT 字段 1，字段 2，字段 3...FROM 表名 WHERE 字段 [NOT] BETWEEN 取值 1 AND 取值 2；//两端都包含
模糊查询 LIKE：
SELECT 字段 1，字段 2，字段 3...FROM 表名 WHERE 字段 [NOT] LIKE ‘字符串’；
“%”代表任意字符；
“_” 代表单个字符；
空值查询 NOT NULL：
SELECT 字段 1，字段 2，字段 3...FROM 表名 WHERE 字段 IS [NOT] NULL；
去重复查询 DISTINCT ：
SELECT DISTINCT 字段名 FROM 表名；
排序 ORDER BY
SELECT 字段 1，字段 2...FROM 表名 ORDER BY 属性名 [ASC|DESC] //ASC升序（默认） DESC降序
分组查询 GROUP BY
GROUP BY 属性名 [HAVING 条件表达式][WITH ROLLUP]
1，单独使用(毫无意义)；
2，与 GROUP_CONCAT()函数一起使用；
3，与聚合函数一起使用；
4，与 HAVING 一起使用(限制输出的结果)；
5，与 WITH ROLLUP 一起使用(最后加入一个总和行)；
 
GROUP_CONCAT()函数示例：
语句 SELECT locus,GROUP_CONCAT(id) FROM info WHERE locus IN('AB086827','AF040764') GROUP BY locus; 的返回结果为
+----------+------------------+
| locus    | GROUP_CONCAT(id) |
+----------+------------------+
| AB086827 | 1,2              |
| AF040764 | 23,24          |
+----------+------------------+
即将查询到的括号内字段的结果合并到一起并用逗号隔开
可以将逗号更换成其他符号，如：
语句 SELECT locus,GROUP_CONCAT(distinct id ORDER BY id DESC SEPARATOR '_') FROM info WHERE locus IN('AB086827','AF040764') GROUP BY locus;的返回结果为
+----------+----------------------------------------------------------+
| locus    | GROUP_CONCAT(distinct id ORDER BY id DESC SEPARATOR '_') |
+----------+----------------------------------------------------------+
| AB086827 | 2_1                                                               |
| AF040764 | 24_23                                                           |
+----------+----------------------------------------------------------+
同时有CONCAT函数将查询结果连成一个字符串，但是查询的字段中有null时则整个结果为null，如：
SELECT CONCAT(id, ‘，’, name,age) AS con FROM info LIMIT 1;返回结果为
+----------+
| con      |
+----------+
| 1,BioCyc23 |
+----------+
 
WITH ROLLUP会在返回结果的最后加上一行将查询出的如果是数字结果则将所有结果数字相加，如果是字符结果则将所有字符合并
SELECT gradeName,GROUP_CONCAT(stuName) FROM t_student GROUP BY gradeName WITH ROLLUP;
 
分页查询 limit：
SELECT 字段 1，字段 2...FROM 表名 LIMIT 初始位置，记录数；
 
聚合函数查询：
一，COUNT()函数
COUNT()函数用来统计记录的条数； 与 GOUPE BY 关键字一起使用；
 
二，SUN()函数
SUM()函数是求和函数； 与 GOUPE BY 关键字一起使用；如：SELECT stuName,SUM(score) FROM t_grade GROUP BY stuName;//根据学生姓名分组算出每个学生的分数总和
注意：当用group by 分组查询时只能查出分组的字段和聚合函数的结果，其他字段无法查出
三，AVG()函数
AVG()函数是求平均值的函数； 与 GOUPE BY 关键字一起使用；//SELECT stuName,AVG(score) FROM t_grade WHERE stuName="张三";//算出张三的平均分
 
四，MAX()函数
MAX()函数是求最大值的函数； 与 GOUPE BY 关键字一起使用；
 
MIN()函数
MIN()函数是求最小值的函数； 与 GOUPE BY 关键字一起使用；
 
多表查询
连接查询：
连接查询是将两个或两个以上的表按照某个条件连接起来，从中选取需要的数据；
t_book表：                                                                                                t_bookType表：
                              
如：SELECT * from t_book,t_booktype; 返回结果将两个表的所有组合结果返回
1、内连接查询：
内连接查询是一种最常用的连接查询。内连接查询可以查询两个或者两个以上的表；
SELECT * from t_book b,t_booktype t where t.id=b.id;
或SELECT * from t_book,t_booktype where t_book.id=t_booktype.id;
查询结果：
也可这么写来查询需要的字段：SELECT bookName,author,bookTypeName from t_book,t_booktype where t_book.id=t_booktype.id;
注意：如果查询的字段（如id）中两个表都有时这种写法会出错，因为无法得知id是哪个表的字段，所以平时多表查询时要写别名
修改为：SELECT tb.bookName,tb.author,tby.id from t_book tb ,t_booktype tby where tb.id=tby.id;
 
2、外连接查询
外连接可以查出某一张表的所有信息；
SELECT 属性名列表 FROM  表名 1 LEFT|RIGHT JOIN 表名 2 ON 表名 1.属性名 1=表名 2.属性名 2；
  • 左连接查询：
     可以查询出“表名 1”的所有记录，而“表名 2”中，只能查询出匹配的记录；如：
     select * from t_book tb left JOIN t_booktype tby on tb.bookTypeId=tby.id; 查询结果：
     
  • 右连接查询：
     可以查询出“表名 2”的所有记录，而“表名 1”中，只能查询出匹配的记录；如：
     select * from t_book tb right JOIN t_booktype tby on tb.bookTypeId=tby.id; 查询结果：
     
  • 多条件连接查询
     即where后面有多个条件and连接查询
 
子查询
1 、带 In 关键字的子查询
一个查询语句的条件可能落在另一个 SELECT 语句的查询结果中。
 
2 、带比较运算符的子查询
子查询可以使用比较运算符。
 
3 、带 （not）Exists 关键字的子查询
假如子查询查询到记录，则进行外层查询，否则，不执行外层查询；not exists表示内层没有内容时才查询外层
如：SELECT * from t_book where EXISTS(select * from t_booktype);//表示t_booktype中返回的有内容时才执行外层t_book的查询
 
4 、带 Any 关键字的子查询
ANY 关键字表示满足其中任一条件；如：SELECT * FROM t_book WHERE price>= ANY (SELECT price FROM t_pricelevel);//表示筛选价格大于任意t_pricelevel表中选出的价格的结果，即比最小的大的价格就满足条件
 
5 、带 All 关键字的子查询
ALL 关键字表示满足所有条件；如：SELECT * FROM t_book WHERE price>= ALL (SELECT price FROM t_pricelevel);//表示筛选价格大于所有t_pricelevel表中选出的价格的结果，即比最大的价格大的才满足条件
 
合并查询
  • UNION
     使用 UNION 关键字是，数据库系统会将所有的查询结果合并到一起，然后去除掉相同的记录；
  • UNION ALL
     使用 UNION ALL，不会去除掉系统的记录；
 
索引
 
索引定义
索引是由数据库表中一列或者多列组合而成，其作用是提高对表中数据的查询速度，建立索引查询速度有数量级提升 ；
类似于图书的目录，方便快速定位，寻找指定的内容；
 
索引的优缺点
优点：提高查询数据的速度；
缺点：创建和维护索引的时间增加了；
 
索引分类
1，普通索引
这类索引可以创建在任何数据类型中；
2，唯一性索引
使用 UNIQUE 参数可以设置，在创建唯一性索引时，限制该索引的值必须是唯一的；
3，全文索引
使用 FULLTEXT 参数可以设置，全文索引只能创建在 CHAR，VARCHAR，TEXT 类型的字段上。主要作用
就是提高查询较大字符串类型的速度；只有 MyISAM 引擎支持该索引，Mysql 默认引擎不支持；
4，单列索引
在表中可以给单个字段创建索引，单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引；
5，多列索引
多列索引是在表的多个字段上创建一个索引；
6，空间索引
使用 SPATIAL 参数可以设置空间索引。空间索引只能建立在空间数据类型上，这样可以提高系统获取空间数
据的效率；只有 MyISAM 引擎支持该索引，Mysql 默认引擎不支持；
 
创建索引
  • 创建表的时候创建索引
CREATE TABLE 表名 (属性名 数据类型 [完整性约束条件],
                              属性名 数据类型 [完整性约束条件],
                              ....
                              属性名 数据类型
                              [UNIQUE | FULLTEXT | SPATIAL ]  INDEX| KEY
                              [别名] (属性名 1 [(长度)] [ASC | DESC])
);
1，创建普通索引
CREATE TABLE mytable(
id INT PRIMARY KEY AUTO_INCREMENT,
bookId INT,
bookName VARCHAR(20) NOT NULL,
price DECIMAL(6,2),
INDEX(bookId)   //创建普通索引
//UNIQUE INDEX (bookId)   //创建唯一性索引
//UNIQUE INDEX bokId(bookId)   //创建唯一性索引并起别名为bokId
//UNIQUE INDEX bokId(bookId，id)   //创建多列索引并起别名为bokId
);
2，创建唯一性索引
3，创建全文索引（mysql默认搜索引擎不支持）
4，创建单列索引 
5，创建多列索引
6，创建空间索引（不支持）
 
  • 在已经存在的表上创建索引
CREATE [ UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名  ON 表名  (属性名 [(长度)] [ ASC | DESC])；
如：CREATE INDEX bookId ON mytable(bookId); //创建普通索引
      CREATE UNIQUE INDEX bookId ON mytable(bookId); //创建唯一索引
      CREATE UNIQUE INDEX bookId_price ON mytable(bookId,price); //创建多列索引
  • 用 ALTER TABLE 语句来创建索引
ALTER TABLE 表名 ADD [ UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 (属性名 [(长度)] [ ASC | DESC])；
如：ALTER TABLE mytable ADD INDEX index_bookId(bookId); //创建普通索引
      ALTER TABLE mytable ADD UNIQUE INDEX index_bookId(bookId); //创建唯一索引
      ALTER TABLE mytable ADD UNIQUE INDEX index_bookId_price(bookId,price); //创建多列索引
 
删除索引
DROP INDEX 索引名 ON 表名 ； 如：drop index index_bookId_price on mytable;
 
 
视图
第一节：视图的引入
1，视图是一种虚拟的表，是从数据库中一个或者多个表中导出来的表。
2，数据库中只存放了视图的定义，而并没有存放视图中的数据，这些数据存放在原来的表中。
3，使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。
 
第二节：视图的作用
1，使操作简便化；
2，增加数据的安全性；
3，提高表的逻辑独立性；
 
第三节：创建视图
CREATE [ ALGORITHM ={ UNDEFIEND | MERGE | TEMPTABLE }]
               VIEW 视图名 [ ( 属性清单) ]
               AS SELECT 语句  [ WITH [  CASCADED | LOCAL ] CHECK OPTION ]；
 
ALGORITHM 是可选参数，表示视图选择的算法；
“视图名”参数表示要创建的视图的名称；
“属性清单”是可选参数，其指定了视图中各种属性的名词，默认情况下与 SELECT 语句中查询的属性相同；
SELECT 语句参数是一个完整的查询语句，标识从某个表查出某些满足条件的记录，将这些记录导入视图中；
WITH CHECK OPTION 是可选参数，表似乎更新视图时要保证在该视图的权限范围之内；
 
ALGORITHM 包括 3 个选项 UNDEFINED、MERGE 和 TEMPTABLE。其中，
UNDEFINED 选项表示 MySQL 将 自动选择所要使用的算法；
MERGE 选项表示将使用视图的语句与视图定义合并起来，使得视图定义的某一部分 取代语句的对应部分；
TEMPTABLE 选项表示将视图的结果存入临时表，然后使用临时表执行语句；
CASCADED 是可选参数，表示更新视图时要满足所有相关视图和表的条件，该参数为默认值；
LOCAL 表示更新视图时，要 满足该视图本身的定义条件即可；
如：创建视图：create view v1 as select * from mytable where bookId >30;
      查询使用：select * from v1;
     创建视图并给返回的结果栏目定义栏目名：CREATE VIEW v1(b,p) AS select bookId,bookName from mytable;
     返回结果如：
     注意查询视图字段时有别名的话按照别名查询，没有别名可以按照原字段名称查询；
 
第四节：查看视图
4.1 DESCRIBE 视图名称； //查看视图字段属性信息
4.2 SHOW TABLE STATUS LIKE ‘视图名称’；// 查看视图基本信息，因为视图是虚表，故查询结果字段都为空；此sql也可以查询普通表的基本信息；后面没有like指定视图时表示查询库中所有表和视图的基本信息
4.3 SHOW CREATE VIEW 视图名称；//查看视图详细信息
4.3 在 views 表中查看视图详细信息
 
第五节：修改视图
5.1 CREATE  OR  REPLACE  VIEW 语句修改视图
CREATE  OR  REPLACE  [ ALGORITHM ={ UNDEFINED | MERGE | TEMPTABLE }]
                                         VIEW 视图名 [( 属性清单 )]
                                         AS SELECT 语句  [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]；
如：create or replace  view v1 as select * from mytable where bookId < 30;
 
5.2 ALTER 语句修改视图
ALTER  [ ALGORITHM ={ UNDEFINED | MERGE | TEMPTABLE }]
             VIEW 视图名 [( 属性清单 )]
             AS SELECT 语句
             [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]；
如：alter view v1 as select * from mytable;
 
第六节：更新视图
更新视图是指通过视图来插入（INSERT）、更新（UPDATE）和删除（DELETE）表中的数据。因为视图是一个虚
拟的表，其中没有数据。通过视图更新时，都是转换基本表来更新。更新视图时，只能更新权限范围内的数据。
超出了范围，就不能更新。
 
6.1 插入(INSERT)
     CREATE VIEW v1(b,p) AS select bookId,bookName from mytable;
     INSERT into v1 values(44,'think in java');//插入视图，再次查看视图v1时会看到插入的数据，视图操作实际是对表的操作，可以看到mytable表中增加了一条数据，但是其他没有插入的字段则是显示默认值
6.2 更新(UPDATE)
     update v1 set b=8,p='设计模式' where b=44
6.3 删除(DELETE)
     delete from v1 where b=44;
 
第七节：删除视图
删除视图是指删除数据库中已存在的视图。删除视图时，只能删除视图的定义，不会删除数据；
DROP VIEW [ IF EXISTS ] 视图名列表 [ RESTRICT | CASCADE ]
如： DROP VIEW  IF EXISTS  v1;
 
触发器
第一节：触发器的引入
触发器（TRIGGER）是由事件来触发某个操作。这些事件包括 INSERT 语句、UPDATE 语句和 DELETE 语句。
当数据库系统执行这些事件时，就会激活触发器执行相应的操作。
 
第二节：创建与使用触发器
2.1 创建只有一个执行语句的触发器
CREATE TRIGGER 触发器名  BEFORE | AFTER 触发事件
                              ON 表名  FOR EACH ROW 事件发生时要执行的语句
如：CREATE TRIGGER trig_book AFTER INSERT
             ON t_book FOR EACH ROW
             UPDATE t_bookType SET bookNum=bookNum+1 WHERE new.bookTypeId=t_booktype.id;
表示t_book表中国执行插入操作时触发t_bookType的更新操作，将bookNum加1
注：new是中间量，表示插入（更新）时的那条数据，如果是删除时用old，表示删除的那条数据
 
2.2 创建有多个执行语句的触发器
CREATE TRIGGER 触发器名  BEFORE | AFTER 触发事件
                              ON 表名  FOR  EACH  ROW  
                              BEGIN  
                                   执行语句列表  
                              END
如：
DELIMITER |
CREATE TRIGGER trig_book2 AFTER DELETE
    ON t_book FOR EACH ROW
    BEGIN
       UPDATE t_bookType SET bookNum=bookNum-1 WHERE old.bookTypeId=t_booktype.id;
       INSERT INTO t_log VALUES(NULL,NOW(),'在book表里删除了一条数据');
       DELETE FROM t_test WHERE old.bookTypeId=t_test.id;
    END
|
DELIMITER ;
注：DELIMITER | 包裹起来避免触发多条sql时遇到分号中断执行的情况
 
第三节：查看触发器
3.1 SHOW TRIGGERS; 语句查看触发器信息
3.2 在 triggers 表中查看触发器信息
 
第四节：删除触发器
DROP TRIGGER 触发器名；
 
 
mysql常用函数（部分举例）
 
第一节：日期和时间函数
1，CURDATE()  返回当前系统日期； 如：select CURDATE() ,CURTIME(),MONTH(created_at) from theme13；
2，CURTIME()  返回当前系统时间；
3，MONTH(d)  返回日期 d 中的月份值，范围是 1~12
 
第二节：字符串函数
1，CHAR_LENGTH(s)   计算字符串 s 的字符数；
2，UPPER(s)  把所有字母变成大写字母；
3，LOWER(s)   把所有字母变成小写字母；
 
第三节：数学函数
1，A BS(x)   求绝对值
2，SQRT(x)   求平方根
3，MOD(x,y)   求余
 
第四节：加密函数
1，PASSWORD(str)     一般对用户的密码加密 不可逆
2，MD5(str)    普通加密 不可逆
3，ENCODE(str，pswd_str)     加密函数，结果是一个二进制数，必须使用 BLOB 类型的字段来保存它；
4，DECODE(crypt_str，pswd_str)     解密函数；
 
 
 
存储过程和函数的引入
 
第一节：存储过程和函数的引入
 
存储过程和函数是在数据库中定义一些 SQL 语句的集合，然后直接调用这些存储过程和函数来执行已经定义好
的 SQL 语句。存储过程和函数可以避免开发人员重复的编写相同的 SQL 语句。而且，存储过程和函数是在 MySQL
服务器中存储和执行的，可以减少客户端和服务器端的数据传输；
 
第二节：创建存储过程和函数
 
1 创建存储过程
 
CREATE PROCEDURE sp_name([proc_parameter[,...]])
[characteristic...] routine_body
 
sp_name 参数是存储过程的名称；
proc_parameter 表示存储过程的参数列表；
characteristic 参数指定存储过程的特性；
routine_body 参数是 SQL 代码的内容，可以用 BEGIN...END 来标志 SQL 代码的开始和结束。
proc_parameter 中的每个参数由 3 部分组成。这 3 部分分别是输入输出类型、参数名称和参数类型。
[ IN | OUT | INOUT ] param_name type
其中，IN 表示输入参数；OUT 表示输出参数；INOUT 表示既可以是输入，也可以是输出；param_name 参数是
存储过程的参数名称；type 参数指定存储过程的参数类型，该类型可以是 MySQL 数据库的任意数据类型；
Characteristic 参数有多个取值。其取值说明如下：
LANGUAGE SQL：说明 routine_body 部分是由 SQL 语言的语句组成，这也是数据库系统默认的语言。
[ NOT ] DETERMINISTIC ：指明存储过程的执行结果是否是确定的。DETERMINISTIC 表示结果是确定的。每
次执行存储过程时，相同的输入会得到相同的输出。NOT DETERMINISTIC 表示结果是非确定的，相同的输入
可能得到不同的输出。默认情况下，结果是非确定的。
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } ：指明子程序使用 SQL 语句的限制；
CONTAINS SQL 表示子程序包含 SQL 语句，但不包含读或写数据的语句；NO SQL 表示子程序中不包含 SQL
语句；READS SQL DATA 表示子程序中包含读数据的语句；MODIFIES SQL DATA 表示子程序中包含写数据的
语句。默认情况下，系统会指定为 CONTAINS SQL；
SQL SECURITY { DEFINER | INVOKER }；指明谁有权限来执行。DEFINER 表示只有定义者自己才能够执行；
INVOKER 表示调用者可以执行。默认情况下，系统指定的权限是 DEFINER。
COMMENT ‘string’ ：注释信息；
如：
DELIMITER &&
CREATE PROCEDURE pro_book ( IN bT INT,OUT count_num INT)
   READS SQL DATA
   BEGIN
     SELECT COUNT(*) FROM t_book WHERE bookTypeId=bT;
   END
   &&
DELIMITER ;
 
调用：CALL pro_book(1,@total);
 
2 创建存储函数
CREATE FUNCTION sp_name ( [func_parameter[,...]] )
RETURNS type
[ characteristic... ] routine_body
sp_name 参数是存储函数的名称；func_parameter 表示存储函数的参数列表；RETURNS type 指定返回值的
类型；characteristic 参数指定存储过程的特性，该参数的取值与存储过程中的取值是一样的；routine_body 参数
是 SQL 代码的内容，可以用 BEGIN...END 来标志 SQL 代码的开始和结束；
func_parameter 可以由多个参数组成，其中每个参数由参数名称和参数类型组成，其形式如下：
param_name type
其中，param_name 参数是存储函数的参数名称；type 参数指定存储函数的参数类型，
 
该类型可以是 MySQL 数据库的任意数据类型；
如：
DELIMITER &&
CREATE FUNCTION func_book (bookId INT)
 RETURNS VARCHAR(20)
 BEGIN
  RETURN ( SELECT bookName FROM t_book WHERE id=bookId );
 END
    &&
DELIMITER ;
 
SELECT func_book(2);
 
3 变量的使用
  • 定义变量
DECLARE var_name [,...] type [ DEFAULT value ]
如：
DELIMITER &&
CREATE PROCEDURE pro_user()
    BEGIN
     DECLARE a,b VARCHAR(20) ;
     INSERT INTO t_user VALUES(NULL,a,b);
    END
&&
DELIMITER ;
调用该存储过程后会向数据库添加一条空值记录
//将t_user2表数据读取出来直接插入到t_user中
DELIMITER &&
CREATE PROCEDURE pro_user3()
    BEGIN
     DECLARE a,b VARCHAR(20) ;
     SELECT userName2,password2 INTO a,b FROM t_user2 WHERE id2=1;
     INSERT INTO t_user VALUES(NULL,a,b);
    END
&&
DELIMITER ;
 
  • 为变量赋值
SET var_name = expr [,var_name=expr] ...
SELECT col_name[,...] INTO var_name[,...]
FROM table_name WHERE condition
如：
DELIMITER &&
CREATE PROCEDURE pro_user2()
    BEGIN
     DECLARE a,b VARCHAR(20) ;
     SET a='java1234',b='123456';
     INSERT INTO t_user VALUES(NULL,a,b);
    END
&&
DELIMITER ;
 
4 游标的使用
查询语句可能查询出多条记录，在存储过程和函数中使用游标来逐条读取查询结果集中的记录。游标的使
用包括声明游标、打开游标、使用游标和关闭游标。游标必须声明在处理程序之前，并且声明在变量和条
件之后。
1，声明游标
     DECLARE cursor_name CURSOR FOR select_statement ;
2，打开游标
     OPEN cursor_name;
3，使用游标
     FETCH cursor_name INTO var_name [,var_name ... ]；
4，关闭游标
     CLOSE cursor_name；
 
如：
DELIMITER &&
CREATE PROCEDURE pro_user4()
    BEGIN
     DECLARE a,b VARCHAR(20) ;
     DECLARE cur_t_user2 CURSOR FOR SELECT userName2,password2 FROM t_user2;  //声明游标
     OPEN cur_t_user2;   //打开游标
     FETCH cur_t_user2 INTO a,b;   //将游标数据赋给a和b
     INSERT INTO t_user VALUES(NULL,a,b);  
     CLOSE cur_t_user2;  //关闭游标
    END
&&
DELIMITER ;
 
5 流程控制的使用
存储过程和函数中可以使用流程控制来控制语句的执行。MySQL 中可以使用 IF 语句、CASE 语句、LOOP语句、LEAVE 语句、ITERATE 语句、REPEAT 语句和 WHILE 语句来进行流程控制。
  • IF 语句
IF search_condition THEN statement_list
[ ELSEIF search_condition THEN statement_list ]...
[ ELSE statement_list ]
END IF
如：
          DELIMITER &&
CREATE PROCEDURE pro_user5(IN bookId INT)
    BEGIN
     SELECT COUNT(*) INTO @num FROM t_user WHERE id=bookId;
     IF @num>0 THEN UPDATE t_user SET userName='java12345' WHERE id=bookId;
     ELSE
       INSERT INTO t_user VALUES(NULL,'2312312','2321312');
     END IF ;
    END
&&
DELIMITER ;
注：@num表示定义的会话变量或者全局变量
  • CASE 语句
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list]...
[ELSE statement_list ]
END CASE
如：
DELIMITER &&
CREATE PROCEDURE pro_user6(IN bookId INT)
    BEGIN
     SELECT COUNT(*) INTO @num FROM t_user WHERE id=bookId;
     CASE @num
      WHEN 1 THEN UPDATE t_user SET userName='java12345' WHERE id=bookId;
      WHEN 2 THEN INSERT INTO t_user VALUES(NULL,'2312312','2321312');
      ELSE INSERT INTO t_user VALUES(NULL,'231231221321312','2321312321312');
     END CASE ;
    END
&&
DELIMITER ;
  • LOOP，LEAVE 语句
LOOP 语句可以使某些特定的语句重复执行，实现一个简单的循环。但是 LOOP 语句本身没有停止循环的语句，必须是遇到 LEAVE 语句等才能停止循环。LOOP 语句的语法的基本形式如下：
[begin_label：]LOOP
Statement_list
END LOOP [ end_label ]
LEAVE 语句主要用于跳出循环控制。语法形式如下：
LEAVE label
如：
          DELIMITER &&
CREATE PROCEDURE pro_user7(IN totalNum INT)
    BEGIN
      aaa:LOOP
        SET totalNum=totalNum-1;
        IF totalNum=0 THEN LEAVE aaa ;
        ELSE INSERT INTO t_user VALUES(totalNum,'2312312','2321312');
        END IF ;
      END LOOP aaa ;
    END
&&
DELIMITER ;
  • ITERATE 语句
ITERATE 语句也是用来跳出循环的语句。但是，ITERATE 语句是跳出本次循环，然后直接进入下一次循环。基本语法：
ITERATE label ;
如：
DELIMITER &&
CREATE PROCEDURE pro_user8(IN totalNum INT)
    BEGIN
      aaa:LOOP
        SET totalNum=totalNum-1;
        IF totalNum=0 THEN LEAVE aaa ;
        ELSEIF totalNum=3 THEN ITERATE aaa ;
        END IF ;
        INSERT INTO t_user VALUES(totalNum,'2312312','2321312');
      END LOOP aaa ;
    END
&&
DELIMITER ;
  • REPEAT 语句
REPEAT 语句是有条件控制的循环语句。当满足特定条件时，就会跳出循环语句。REPEAT 语句的基本语法形式如下：
[ begin_label : ] REPEAT
Statement_list
UNTIL search_condition
END REPEAT [ end_label ]
如：
DELIMITER &&
CREATE PROCEDURE pro_user9(IN totalNum INT)
    BEGIN
      REPEAT
         SET totalNum=totalNum-1;
         INSERT INTO t_user VALUES(totalNum,'2312312','2321312');
         UNTIL totalNum=1
      END REPEAT;
    END
&&
DELIMITER ;
//表示重复直到totalNum=1结束循环
  • WHILE 语句
[ begin_label : ] WHILE search_condition DO
Statement_list
END WHILE [ end_label ]
如：
DELIMITER &&
CREATE PROCEDURE pro_user10(IN totalNum INT)
    BEGIN
     WHILE totalNum>0 DO
      INSERT INTO t_user VALUES(totalNum,'2312312','2321312');
      SET totalNum=totalNum-1;
     END WHILE ;
    END
&&
DELIMITER ;
 
第三节：调用存储过程和函数
 
1 调用存储过程
     CALL sp_name( [parameter[,...]] )
2 调用存储函数
     fun_name( [parameter[,...]] )
 
第四节：查看存储过程和函数
 
4.1 SHOW STATUS 语句查看存储过程和函数的状态
     SHOW { PROCEDURE | FUNCTION } STATUS [ LIKE ‘pattern’ ] ;//pattern代表存储过程名
     如：SHOW PROCEDURE STATUS LIKE 'pro_book';
4.2 SHOW CREATE 语句查看存储过程的函数的定义
     SHOW CREATE { PROCEDURE | FUNCTION } sp_name ;
     如：SHOW CREATE PROCEDURE pro_book;
4.3 从 information_schema.Routines 表中查看存储过程和函数的信息
 
第五节：修改存储过程和函数
 
ALTER { PROCEDURE | FUNCTION } sp_name [ characteristic ... ]
characteristic :
{ CONTAINS SQL } NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT ‘string’
其中，sp_name 参数表示存储过程或函数的名称；characteristic 参数指定函数的特性。CONTAINS SQL 表示子程
序包含 SQL 语句，但不包含读或写数据的语句；NO SQL 表示子程序中不包含 SQL 语句；READS SQL DATA
表 示 子 程 序 中 包 含 数 据 的 语 句 ； MODIFIES
SQL DATA 表 示 子 程 序 中 包 含 写 数 据 的 语 句 。 SQL
SECURITY{ DEFINER | INVODER } 指明谁有权限来执行。 DEFINER 表示只有定义者自己才能够执行；
INVODER 表示调用者可以执行。COMMENT ‘string’ 是注释信息。
如：
ALTER PROCEDURE pro_book  COMMENT '我来测试一个COMMENT'; //修改pro_book 的注释为'我来测试一个COMMENT'
 
第六节：删除存储过程和函数
DROP {PROCEDURE | FUNCTION } sp_name ;
如：
DROP PROCEDURE pro_user3;
 
 
数据备份与还原
 
第一节：数据备份
 
备份数据可以保证数据库中数据的安全，数据库管理员需要定期的进行数据库备份；
 
1.1 使用 mysqldump 命令备份
mysqldump -u username -p dbname table1 table2 ... > BackupName.sql
dbname 参数表示数据库的名称；table1 和 table2 参数表示表的名称，没有该参数时将备份整个数据库；
BackupName.sql 参数表示备份文件的名称，文件名前面可以加上一个绝对路径。通常以 sql 作为后缀。
如：
mysqldump -u root -p db_book > c:\db_book.sql     //将整个db_book数据库备份到c盘db_book.sql文件中，当然图形界面都是可以的
 
1.2 使用 sqlyog 图形工具备份
 
第二节：数据还原
 
2.2 使用 mysql 命令还原
 
Mysql -u root -p [dbname] < backup.sql
dbname 参数表示数据库名称。该参数是可选参数，可以指定数据库名，也可以不指定。指定数据库名时，表
示还原该数据库下的表。不指定数据库名时，表示还原特定的一个数据库。而备份文件中有创建数据库的语句。
 
2.3 使用 sqlyog 图形工具还原
 

转载于:https://www.cnblogs.com/leskang/p/5922349.html

badiu_30394251
关注
  • 0
    点赞
  • 踩
  • 8
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
SQL server数据库常用代码大全
weixin_45229366的博客
06-18 2846
SQL 语句目录插入数据表数据 创建临时表 代码太多，全部打包放资源里了，有需要的朋友可以去我的资源里搜索【SQL server数据常用代码大全】power函数获取数值的n次方 round函数保留指定位数小数点（指定位数后四舍五入） sign函数判断正数负数和零 sqrt平方根函数 convert转换数据类型函数 CURRENT_USER返回当前用户名字 datalength返回双字符串字节长度（一个中文2个字节） Host_name返回当前用户所登录的计算机名字 system_user返回当前所
数据库系统基础速成—SQL代码查询语言篇
Lion_Dreams的博客
05-24 3063
本文写了一些常用的SQL代码，语句，几乎包含了所有基础的SQL代码，只有少数几个不常用的并未列出。其次为了使代码框中的文字更加清晰，代码框中的文字并没有使用注释符进行注释。1、基本数据类型 2、创建数据库关系（create） 3、主键声明（primary key） 4、外键声明（foreign key） 5、约束语句之不允许为空值约束（not null） 6、关系中添加(插入)数据（insert into） 7、删除所有元组(表中所有行的数据)（delete from）删除一个关系
常用经典SQL语句大全完整版--详解+实例
WuJun_025的博客
03-20 3万+
下列语句部分是Mssql语句，不可以在access中使用。 　　SQL分类： 　　DDL—数据定义语言(CREATE，ALTER，DROP，DECLARE) 　　DML—数据操纵语言(SELECT，DELETE，UPDATE，INSERT) 　　DCL—数据控制语言(GRANT，REVOKE，COMMIT，ROLLBACK) 　　首先,简要介绍基础语句： 　　1、说明：创建数据库 CREATE D...
SQL语句大全（详解）
xmp
02-26 6825
史上最全常用SQL语句大全，适用于绝大数数据库语句。
mysql转移数增删改查_[转]MySql之增删改查总结
weixin_36104309的博客
01-19 322
连接命令：mysql -h[主机地址] -u[用户名] -p[用户密码]创建数据库：create database [库名]显示所有数据库: show databases;打开数据库:use [库名]当前选择的库状态:SELECT DATABASE();创建数据表:CREATE TABLE [表名]([字段名] [字段类型]([字段要求]) [字段参数], ......);显示数据表字段:desc...
Access数据库-SQL语句大全.mdb
03-28
Access数据库-SQL语句资料.mdb
泛微系统SQL语句大全
12-29
**泛微系统SQL语句大全** 在IT行业中，泛微系统是一种广泛应用的企业级协同办公软件，主要用于提升组织的管理效率和工作流程自动化。本资源集合了泛微系统中与SQL Server数据库交互时常用的各种SQL语句，涵盖了组织...
SQL语句大全实例
04-09
文档对主要sql语句进行了清晰的罗列，配以实例讲解，更加清晰易学
常用SQL语句大全
03-04
软件介绍 常用SQL语句大全 语 句 功 能　--数据操作　SELECT --从数据库表中检索数据行和列　INSERT --向数据库表添加新数据行　DELETE --从数据库表中删除数据行　...Tags： 常用SQL语句大全 SQL语句大全 SQL
SQL语句大全
01-24
通用的SQL语句汇总，便于初学者学习借用，含简单数据查询和表单数据插入、删除、修改等
MySql数据库增删改查常用语句命令
热门推荐
赵小白的博客
05-03 8万+
文章目录增删改查语句库操作表操作增删改查实例准备表插入数据修改表数据删除表数据查询表数据常见的MySQL语句命令常见MySQL字段含义 增删改查语句 增删改查的语句命令为： 操作 命令 增 insert 删 delete 改 update 查 select或者show 库操作 操作 代码 创建数据库 create database shujuku; ...
Transact SQL 常用语句以及函数
tuzhengjun的专栏
03-30 689
Transact SQL 常用语句以及函数Transact SQL  语 句 功 能 ========================================================================　　--数据操作 　　 SELECT --从数据库表中检索数据行和列 　　　　　　INSERT --向数据库表添加新数据行 　　　　　　DELETE --从数据库表中
mysql group by 用法解析(详细)
hjwang1的专栏
02-18 1万+
转自：http://www.5iphp.com/comment/3 group by 用法解析 group by语法可以根据给定数据列的每个成员对查询结果进行分组统计，最终得到一个分组汇总表。 SELECT子句中的列名必须为分组列或列函数。列函数对于GROUP BY子句定义的每个组各返回一个结果。 某个员工信息表结构和数据如下：   id  name  dept  s
mysql数据库增删改查基础语句_Mysql数据库基础增删改查常用语句命令
weixin_30095833的博客
02-02 2561
Mysql增删改查常用语句命令一、增删改查语句总览创建： create(创建数据库)增：insert(插入表数据)删：drop、delete(删除表、删除表数据)改：update、alter(更改表数据、插入新字段)查：select、show、describe/desc(查询表数据、查看所有表、查看表结构)二、创建、删除数据库创建mydatabase数据库mysql> create data...
mysql删除delete_mysql 删除操作(delete+TRUNCATE)
weixin_34010875的博客
01-18 175
delete语法单表语法：DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name[WHERE where_definition][ORDER BY ...][LIMIT row_count]多表语法：DELETE [LOW_PRIORITY] [QUICK] [IGNORE]tbl_name[.*] [, tbl_name[.*] ...]FROM...
经典SQL语句大全(绝对的经典)
u011863767的博客
10-09 2万+
转自：http://www.cnblogs.com/dajiang02/archive/2011/08/13/2136929.html
sqly语句查找到重复的数据
foreverking
11-15 462
--找到重复纪录select  sortcolid from Tab_SpeaialSort  group by sortcolid having count(*) > 1-- 2） 删除重复记录-- -- delete from table A where A.rowid --该文章转载自网络大本营：http://www.xrss.cn/Dev/DataBase/200711101749
设计模式---单件（C++版） 一个简单实例(避免用户忘记delete单件实例)
hada1234的专栏
12-03 849
 上次提供的代码没有考虑到客户忘记delete这一点；所以在网上看了一些别人的代码，整理了一下发了上来！ #include memory>#include string>#include iostream>using namespace std;/**//* #include auto_ptr是可以避免忘记ｄｅｌｅｔｅ如下auto_ptr pi ( new Int( 5 ) );cou
经典的SQL语句大全
s365348751的专栏
05-14 196
SQL
数据库sql语句大全
最新发布
11-21
数据库SQL语句大全指的是所有与数据库相关的SQL语句集合。SQL是Structured Query Language的缩写，是一种用于对关系型数据库进行操作的标准语言。 数据库SQL语句大全通常包括数据定义语言（DDL）、数据操纵语言（DML）、数据查询语言（DQL）、数据控制语言（DCL）等各种类型的语句。DDL包括创建、修改和删除表格、索引以及其他数据库对象的语句；DML包括添加、修改、删除数据记录的语句；DQL是用于查询数据库中数据的语句；DCL主要用于控制数据库访问权限的语句。 一般来说，数据库SQL语句大全会包括数据库管理系统所支持的所有标准SQL语句以及特定数据库管理系统所支持的扩展语句。这些语句可以满足用户在数据库管理、查询、维护、优化等方面的需求。 在实际工作中，通过学习数据库SQL语句大全，用户可以更加全面深入地了解数据库的操作方法，并能够根据具体的业务需求编写符合要求的SQL语句。通过灵活运用SQL语句，可以提高数据库操作的效率和准确性。 总之，数据库SQL语句大全是数据库中非常重要的知识库，可以帮助用户更好地管理和操作数据库系统。通过学习和熟练掌握SQL语句大全，用户可以在数据库管理和使用过程中获得更多的便利和收益。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值