老师的博客:http://www.cnblogs.com/wupeiqi/articles/5713323.html
总结
导出与导入 导出:mysqldump -u root -p 数据库 > 新建名称.sql 导入:一般先创建数据库 create database 数据库: myslq -u root -p 数据库 < 被导入的文件.sql 注意,此时是在命令窗口执行的 不用加; 视图review 创建:create view 名 as sql语句; 此时只是一个虚拟的表,只是一段语句,如果里面的表变化的,查询出来的值也会改变的 修改:alter view 名 as sql语句; 删除:drop view 名; 对于视图的操作只能是查看作用:select * from 名; 触发器trigger: 创建: delimiter \ CREATE TRIGGER 触发器名 BEFORE INSERT ON 表名1 FOR EACH ROW BEGIN INSERT INTO 操作的表2 ( 列名,列名 ) VALUES ( NEW.名,'值'); END \ delimiter; #delimiter 是修改终止符 #NES.名 表示你对表1输入某个信息。与before相对是after #new相对的是old,表示被删除的值 #两个insert可以根据具体的操作变为delect ,update #详细代码见http://www.cnblogs.com/wupeiqi/articles/5713323.html 函数function: 函数有自定和内置的,官方的网址:https://dev.mysql.com/doc/refman/5.7/en/functions.html 中文网址:http://doc.mysql.cn/mysql5/refman-5.1-zh.html-chapter/functions.html#encryption-functions 创建函数: delimiter \ CREATE FUNCTION f2 ( num2 INT, num1 INT ) RETURNS INT BEGIN DECLARE a DEFAULT INT 1; SET a = num1 * num2; RETURN ( a ) ; END \ delimiter; 里面的分隔符使用; 开始是returns 后面是return 调用函数: select f2(9,5); #45 这个创建时因为mysql的master和slave的关系,被视为不安全的函数,因此需要修改配置文件。 最后我执行完函数又改了回来。 函数是不能写select * from 这样的语句的,储存过称可以 储存过程proceduce 创建: 简单: delimiter // CREATE PROCEDURE p1 ( ) BEGIN SELECT * FROM class; INSERT INTO class ( caption ) VALUES ( '高三五班' ); END // 调用,在mysql中 call p1(); 在pymysql中 cursor.callproc('p1') 传参:in out inout in: delimiter // CREATE PROCEDURE p2 (in cha char(10) ) BEGIN SELECT * FROM class; INSERT INTO class ( caption )VALUES( cha ); END // delimiter; inout: delimiter // CREATE PROCEDURE p3 (in cha char(10), inout num int ) BEGIN set num=233; SELECT * FROM class; INSERT INTO class ( caption )VALUES( cha ); END // delimiter; 调用函数: set @b=1; call p3('牛头',@b); select @b; #@b表示一个全局变量,在哪都可用,没有return,但是inout的值可以拿到,你可发现@b的值已经变了。 删除:drop procedure 名; 事物(proceduce) 处理失败与成功处理 下面是小写,方便看。 delimiter // create procedure p5 (inout check_num tinyint) begin declare exit handler for SQLEXCEPTION begin -- deal with error set check_num=1; rollback; end; start transaction ; select * from class; insert into class(caption)values('高三十九班'),('拒水小学一年级九班'); commit; select * from class; set check_num=2; -- success deal with process end // delimiter ; 正确的格式: delimiter // CREATE PROCEDURE p5 ( INOUT check_num TINYINT ) -- 只有一个参数不写;多个写 BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN -- deal with error SET check_num = 1; ROLLBACK; END; START TRANSACTION; SELECT * FROM class; INSERT INTO class ( caption ) VALUES ( '高三十九班'), ('拒水小学一年级九班' ); COMMIT; SELECT * FROM class; SET check_num = 2; -- success deal with process END // delimiter; 游标 里面可以写类似于for循环,请看代码; delimiter // create procedure p9() begin -- 声明变量 不写; declare a_id int; declare a_value int ; declare num int ; declare done int default false; declare my_cursor cursor for select id,value1 from a;-- 建立游标 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; -- 开始循环 open my_cursor; lhy:loop fetch my_cursor into a_id,a_value; if done=True then leave lhy;-- 离开循环 end if ; set num = a_id + a_value; insert into B(num) values(num); end loop lhy; -- 结束循环 close my_cursor; commit; end // delimiter; call p9(); 这是美化之后的: delimiter // CREATE PROCEDURE p9 ( ) BEGIN-- 声明变量 DECLARE a_id INT; DECLARE a_value INT; DECLARE num INT; DECLARE done INT DEFAULT FALSE; DECLARE my_cursor CURSOR FOR SELECT id,value1 FROMa;-- 建立游标 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; -- 开始循环 OPEN my_cursor; lhy:LOOP FETCH my_cursor INTO a_id,a_value; IF done = TRUE THEN -- 这里没有;否者会报错 LEAVE lhy;-- 离开循环 END IF; SET num = a_id + a_value; INSERT INTO B ( num )VALUES( num ); END LOOP lhy;-- 结束循环 CLOSE my_cursor; COMMIT; -- 记得提交不然就回滚了,没有输入值 END // delimiter; 动态执行sql(防止sql注入): 先定义一个: delimiter \ create procedure p4 ( in nid int -- 声明变量 ) begin set @nid=nid; -- 设置 @赋值 prepare prod from 'select * from student where sid > ?'; -- 预编译?是站位符 execute prod using @nid; -- 将?赋值,将prof 变为正常语言 deallocate prepare prod; -- 执行Prof end\ delimiter ; call p4(0); -- 调用 然后我发现还是能注入,只是取不到第一个值 例如 call p4(100 or 1=1)取到除了第一个,其余取去到了 美化的代码: delimiter \ CREATE PROCEDURE p4 ( IN nid INT) BEGIN SET @nid = nid; PREPARE prod FROM 'select * from student where sid > ?'; EXECUTE prod USING @nid; DEALLOCATE PREPARE prod; END \ delimiter; 索引 对性能要求不高的用函数之类的,因为如果用了函数就不会走索引了。 普通索引:加速查找 创建列表时:index ix_name (name) index 名(列名,列名) 创建:create index index_name on table_name(column_name) create index 名 on 表名(列名,列名) 删除:drop index_name on table_name; drop 索引名 on 表名 查看:show index from table_name 注意:对于创建索引时如果是BLOB 和 TEXT 类型,必须指定length。 create index ix_extra on in1(extra(32)); 唯一索引:加速查询 和 唯一约束(可含null) 创建:1.create table in1( nid int not null auto_increment primary key, name varchar(32) not null, email varchar(64) not null, extra text, unique ix_name (name) ) 2.create unique index 索引名 on 表名(列名,列名) 一队表示这一对的组合不能组合 删除:drop unique index 索引名 on 表名 主键索引:加速查询 和 唯一约束(不可含null) 创建:create table table_name( nid int not null auto_increment primary key, name varchar(32) not null, email varchar(64) not null, extra text, index ix_name (name) ) OR create table table_name( nid int not null auto_increment, name varchar(32) not null, email varchar(64) not null, extra text, primary key(ni1), index ix_name (name) ) 另外一种:alter table 表名 add primary key(列名); 删除:alter table 表名 drop primary key; alter table 表名 modify 列名 int, drop primary key; 组合索引:将n个列组合成一个索引 创建:create table in3( nid int not null auto_increment primary key, name varchar(32) not null, email varchar(64) not null, extra text ) create index ix_name_email on in3(name,email); 当然也可以在创建表示直接创建组合索引,上面三种都可以; 最左前缀匹配:name and email -- 使用索引 name -- 使用索引 email -- 不使用索引 注意:对于同时搜索n个条件时,组合索引的性能好于多个单一索引合并。 应该避免:- like '%xx' select * from tb1 where name like '%cn'; - 使用函数 select * from tb1 where reverse(name) = 'wupeiqi'; - or select * from tb1 where nid = 1 or email = 'seven@live.com'; 特别的:当or条件中有未建立索引的列才失效,以下会走索引 select * from tb1 where nid = 1 or name = 'seven'; select * from tb1 where nid = 1 or email = 'seven@live.com' and name = 'alex' - 类型不一致 如果列是字符串类型,传入条件是必须用引号引起来,不然... select * from tb1 where name = 999; - != select * from tb1 where name != 'alex' 特别的:如果是主键,则还是会走索引 select * from tb1 where nid != 123 - > select * from tb1 where name > 'alex' 特别的:如果是主键或索引是整数类型,则还是会走索引 select * from tb1 where nid > 123 select * from tb1 where num > 123 - order by select email from tb1 order by name desc; 当根据索引排序时候,选择的映射如果不是索引,则不走索引 特别的:如果对主键排序,则还是走索引: select * from tb1 order by nid desc; - 组合索引最左前缀 如果组合索引为:(name,email) name and email -- 使用索引 name -- 使用索引 email -- 不使用索引 同时注意: - 避免使用select * - count(1)或count(列) 代替 count(*) - 创建表时尽量时 char 代替 varchar - 表的字段顺序固定长度的字段优先 - 组合索引代替多个单列索引(经常使用多个条件查询时) - 尽量使用短索引 - 使用连接(JOIN)来代替子查询(Sub-Queries) - 连表时注意条件类型需一致 - 索引散列值(重复少)不适合建索引,例:性别不适合 其他的语句: if条件语句:delimiter \ create procedure proc_if () begin declare i int default 0; if i = 1 then select 1; elseif i = 2 then select 2; else select 7; end if; end\ delimiter ; 循环语句:while循环: delimiter \ create procedure proc_while () begin declare num int ; set num = 0 ; while num < 10 do; select num ; set num = num + 1 ; end while end\ delimiter ; repeat循环:delimiter \ create procedure proc_repeat () begin declare i int ; set i = 0 ; repeat select i; set i = i + 1; until i >= 5 end repeat; end\ delimiter ; loop循环:begin declare i int default 0; loop_label: loop set i=i+1; if i<8 then; iterate loop_label; end if; if i>=10 then leave loop_label; end if; select i; end loop loop_label; end
常见的内置函数:
HAR_LENGTH(str) 返回值为字符串str 的长度,长度的单位为字符。一个多字节字符算作一个单字符。 对于一个包含五个二字节字符集, LENGTH()返回值为 10, 而CHAR_LENGTH()的返回值为5。 CONCAT(str1,str2,...) 字符串拼接 如有任何一个参数为NULL ,则返回值为 NULL。 CONCAT_WS(separator,str1,str2,...) 字符串拼接(自定义连接符) CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL)。 CONV(N,from_base,to_base) 进制转换 例如: SELECT CONV('a',16,2); 表示将 a 由16进制转换为2进制字符串表示 FORMAT(X,D) 将数字X 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 D 位, 并将结果以字符串的形式返回。若 D 为 0, 则返回结果不带有小数点,或不含小数部分。 例如: SELECT FORMAT(12332.1,4); 结果为: '12,332.1000' INSERT(str,pos,len,newstr) 在str的指定位置插入字符串 pos:要替换位置其实位置 len:替换的长度 newstr:新字符串 特别的: 如果pos超过原字符串长度,则返回原字符串 如果len超过原字符串长度,则由新字符串完全替换 INSTR(str,substr) 返回字符串 str 中子字符串的第一个出现位置。 LEFT(str,len) 返回字符串str 从开始的len位置的子序列字符。 LOWER(str) 变小写 UPPER(str) 变大写 LTRIM(str) 返回字符串 str ,其引导空格字符被删除。 RTRIM(str) 返回字符串 str ,结尾空格字符被删去。 SUBSTRING(str,pos,len) 获取字符串子序列 LOCATE(substr,str,pos) 获取子序列索引位置 REPEAT(str,count) 返回一个由重复的字符串str 组成的字符串,字符串str的数目等于count 。 若 count <= 0,则返回一个空字符串。 若str 或 count 为 NULL,则返回 NULL 。 REPLACE(str,from_str,to_str) 返回字符串str 以及所有被字符串to_str替代的字符串from_str 。 REVERSE(str) 返回字符串 str ,顺序和字符顺序相反。 RIGHT(str,len) 从字符串str 开始,返回从后边开始len个字符组成的子序列 SPACE(N) 返回一个由N空格组成的字符串。 SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len) 不带有len 参数的格式从字符串str返回一个子字符串,起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串,起始于位置 pos。 使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。假若这样,则子字符串的位置起始于字符串结尾的pos 字符,而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一个负值。 mysql> SELECT SUBSTRING('Quadratically',5); -> 'ratically' mysql> SELECT SUBSTRING('foobarbar' FROM 4); -> 'barbar' mysql> SELECT SUBSTRING('Quadratically',5,6); -> 'ratica' mysql> SELECT SUBSTRING('Sakila', -3); -> 'ila' mysql> SELECT SUBSTRING('Sakila', -5, 3); -> 'aki' mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2); -> 'ki' TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) TRIM(remstr FROM] str) 返回字符串 str , 其中所有remstr 前缀和/或后缀都已被删除。若分类符BOTH、LEADIN或TRAILING中没有一个是给定的,则假设为BOTH 。 remstr 为可选项,在未指定情况下,可删除空格。 mysql> SELECT TRIM(' bar '); -> 'bar' mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx'); -> 'barxxx' mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx'); -> 'bar' mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz'); -> 'barx'
摘自老师的博客
。