select ascii('a');
select char(97);
select concat(12,34,'ab');
select length('abc');
select substring('abc123',2,3);
select trim(' bar ');
select trim(leading 'x' FROM 'xxxbarxxx');
select trim(both 'x' FROM 'xxxbarxxx');
select trim(trailing 'x' FROM 'xxxbarxxx');
select space(10);
select replace('abc123','123','def');
select lower('aBcD');
select abs(-32);
select mod(10,3);
select 10%3;
select floor(2.3);
select ceiling(2.3);
select round(1.6);
select pow(2,3);
select PI();
select rand();
select year('2016-12-21');
select '2016-12-21'+interval 1 day;
select date_format('2016-12-21','%Y %m %d');
select current_date();
select current_time();
select now()
对某个表进行【增/删/改】操作的前后如果希望触发某个特定的行为时,可以使用触发器,触发器用于定制用户对表的行进行【增/删/改】前后的行为。
1、创建基本语法
# 插入前
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
...
END
# 插入后
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
...
END
# 删除前
CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN
...
END
# 删除后
CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
BEGIN
...
END
# 更新前
CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
BEGIN
...
END
# 更新后
CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
BEGIN
...
END
示例1:
1 delimiter //
2 CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
3 BEGIN
4
5 IF NEW. NAME == 'alex' THEN
6 INSERT INTO tb2 (NAME)
7 VALUES
8 ('aa')
9 END IF;
10 END//
11 delimiter ;
示例2:
delimiter //
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
IF NEW. num = 666 THEN
INSERT INTO tb2 (NAME)
VALUES
('666'),
('666') ;
ELSEIF NEW. num = 555 THEN
INSERT INTO tb2 (NAME)
VALUES
('555'),
('555') ;
END IF;
END//
delimiter ;
特别的:NEW表示即将插入的数据行,OLD表示即将删除的数据行。
2、删除触发器
DROP TRIGGER tri_after_insert_tb1;
3、使用触发器
触发器无法由用户直接调用,而知由于对表的【增/删/改】操作被动引发的。
insert into tb1(num) values(666)
存储过程是一个SQL语句集合,当主动去调用存储过程时,其中内部的SQL语句会按照逻辑执行。
1、创建存储过程
-- 创建存储过程
delimiter //
create procedure p1()
BEGIN
select * from t1;
END//
delimiter ;
-- 执行存储过程
call p1()
对于存储过程,可以接收参数,其参数有三类:
-- 创建存储过程
delimiter \\
create procedure p1(
in i1 int,
in i2 int,
inout i3 int,
out r1 int
)
BEGIN
DECLARE temp1 int;
DECLARE temp2 int default 0;
set temp1 = 1;
set r1 = i1 + i2 + temp1 + temp2;
set i3 = i3 + 100;
end\\
delimiter ;
-- 执行存储过程
set @t1 =4;
set @t2 = 0;
CALL p1 (1, 2 ,@t1, @t2);
SELECT @t1,@t2;
有参数的存储过程
delimiter //
create procedure p1()
begin
select * from v1;
end //
delimiter ;
delimiter //
create procedure p2(
in n1 int,
inout n3 int,
out n2 int,
)
begin
select * from v1;
set n2 = n1 + 100;
set n3 = n3 + n1 + 100;
end //
delimiter ;
delimiter \\
CREATE PROCEDURE p4 (
in nid int
)
BEGIN
PREPARE prod FROM 'select * from student where sid > ?';
EXECUTE prod USING @nid;
DEALLOCATE prepare prod;
END\\
delimiter ;
delimiter //
create procedure p3()
begin
declare ssid int; -- 自定义变量1
declare ssname varchar(50); -- 自定义变量2
DECLARE done INT DEFAULT FALSE;
DECLARE my_cursor CURSOR FOR select sid,sname from student;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
open my_cursor;
xxoo: LOOP
fetch my_cursor into ssid,ssname;
if done then
leave xxoo;
END IF;
insert into teacher(tname) values(ssname);
end loop xxoo;
close my_cursor;
end //
delimter ;
2、删除存储过程
drop procedure proc_name;
3、执行存储过程
-- 无参数
call proc_name()
-- 有参数,全in
call proc_name(1,2)
-- 有参数,有in,out,inout
set @t1=0;
set @t2=3;
call proc_name(1,2,@t1,@t2)
1 #!/usr/bin/env python
2 # -*- coding:utf-8 -*-
3 import pymysql
4
5 conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
6 cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
7 # 执行存储过程
8 cursor.callproc('p1', args=(1, 22, 3, 4))
9 # 获取执行完存储的参数
10 cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3")
11 result = cursor.fetchall()
12
13 conn.commit()
14 cursor.close()
15 conn.close()
16
17
18 print(result)
show create table students;
alter table '表名' engine=innodb;
开启begin;
提交commit;
回滚rollback;
delimiter \\
create PROCEDURE p1(
OUT p_return_code tinyint
)
BEGIN
DECLARE exit handler for sqlexception
BEGIN
-- ERROR
set p_return_code = 1;
rollback;
END;
DECLARE exit handler for sqlwarning
BEGIN
-- WARNING
set p_return_code = 2;
rollback;
END;
START TRANSACTION;
DELETE from tb1;
insert into tb2(name)values('seven');
COMMIT;
-- SUCCESS
set p_return_code = 0;
END\\
delimiter ;
索引,是数据库中专门用于帮助用户快速查询数据的一种数据结构。类似于字典中的目录,查找字典内容时可以根据目录查找到数据的存放位置,然后直接获取即可。
MySQL中常见索引有:
1、普通索引
普通索引仅有一个功能:加速查询
create table in1(
nid int not null auto_increment primary key,
name varchar(32) not null,
email varchar(64) not null,
extra text,
index ix_name (name)
)
创建索引
create index index_name on table_name(column_name)
drop index_name on table_name; #删除
show index from table_name; # 查看索引
注意:对于创建索引时如果是BLOB 和 TEXT 类型,必须指定length。
create index ix_extra on in1(extra(32));
2、唯一索引
唯一索引有两个功能:加速查询 和 唯一约束(可含null)
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)
create unique index 索引名 on 表名(列名) # 创建唯一索引
drop unique index 索引名 on 表名 #删除
3、主键索引
主键有两个功能:加速查询 和 唯一约束(不可含null)
alter table 表名 add primary key(列名); #创建主键
alter table 表名 drop primary key;
alter table 表名 modify 列名 int, drop primary key; #删除
4、组合索引
组合索引是将n个列组合成一个索引
其应用场景为:频繁的同时使用n列来进行查询,如:where n1 = 'alex' and n2 = 666。
create index 索引名 on 表名(name,email);
如上创建组合索引之后,查询:
注意:对于同时搜索n个条件时,组合索引的性能好于多个单一索引合并。
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 ;
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 ;
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 ;
conn=connect(参数列表)
cursor1=conn.cursor()
#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql
# 创建连接
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
# 创建游标
cursor = conn.cursor()
# 执行SQL,并返回收影响行数
effect_row = cursor.execute("update hosts set host = '1.1.1.2'")
# 执行SQL,并返回受影响行数
#effect_row = cursor.execute("update hosts set host = '1.1.1.2' where nid > %s", (1,))
# 执行SQL,并返回受影响行数
#effect_row = cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)])
cursor.execute("select * from hosts")
# 获取第一行数据
row_1 = cursor.fetchone()
# 获取前n行数据
# row_2 = cursor.fetchmany(3)
# 获取所有数据
# row_3 = cursor.fetchall()
# 提交,不然无法保存新建或者修改的数据
conn.commit()
# 关闭游标
cursor.close()
# 关闭连接
conn.close()