create table goods_innodb(
id int NOT NULL AUTO_INCREMENT,
name varchar(20) NOT NULL,
primary key(id)
)ENGINE=innodb DEFAULT CHARSET=utf8;
create table goods_MyISAM(
id int NOT NULL AUTO_INCREMENT,
name varchar(20) NOT NULL,
primary key(id)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;
--开启手动事务
start transaction;
insert into goods_innodb(id,name)values(null,'Meta20');
--提交
commit;
start transaction;
insert into goods_MyISAM(id,name)values(null,'Meta20');
commit;
create table country_innodb(
country_id int NOT NULL AUTO_INCREMENT,
country_name varchar(100) NOT NULL,
primary key(country_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
create table city_innodb(
city_id int NOT NULL AUTO_INCREMENT,
city_name varchar(50) NOT NULL,
country_id int NOT NULL,
primary key(city_id),
key idx_fk_country_id(country_id),
CONSTRAINT `fk_city_country` FOREIGN KEY(country_id) REFERENCES country_innodb(country_id) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
--不让删除 修改和外键同步更新
insert into country_innodb values(null,'China'),(null,'America'),(null,'Japan');
insert into city_innodb values(null,'Xian',1),(null,'NewYork',2),(null,'BeiJing',1);
delete from country_innodb where country_id = 1;
update country_innodb set country_id = 100 where country_id = 1;
--MyISAM 不支持事务 不支持外键
存储引擎选择
创建存储过程
CREATE PROCEDURE procedure_name ([proc_parameter[,...]])
begin
-- SQL语句
end ;
示例 :
delimiter $
create procedure pro_test1()
begin
select 'Hello Mysql' ;
end$
delimiter ;
调用存储过程
call procedure_name() ;
查看存储过程
-- 查询db_name数据库中的所有的存储过程
select name from mysql.proc where db='db_name';
-- 查询存储过程的状态信息
show procedure status;
-- 查询某个存储过程的定义
show create procedure test.pro_test1 \G;
删除存储过程
DROP PROCEDURE [IF EXISTS] sp_name ;
示例 :
DELIMITER $
CREATE PROCEDURE pro_test3()
BEGIN
DECLARE NAME VARCHAR(20);
SET NAME = 'MYSQL';
SELECT NAME ;
END$
DELIMITER ;
也可以通过select ... into 方式进行赋值操作
DELIMITER $
CREATE PROCEDURE pro_test5()
BEGIN
declare countnum int;
select count(*) into countnum from city;
select countnum;
END$
DELIMITER ;
if条件判断
if search_condition then statement_list
[elseif search_condition then statement_list] ...
[else statement_list]
end if;
根据定义的身高变量,判定当前身高的所属的身材类型
180 及以上 ----------> 身材高挑
170 - 180 ---------> 标准身材
170 以下 ----------> 一般身材
delimiter $
create procedure pro_test6()
begin
declare height int default 175;
declare description varchar(50);
if height >= 180 then
set description = '身材高挑';
elseif height >= 170 and height < 180 then
set description = '标准身材';
else
set description = '一般身材';
end if;
select description ;
end$
delimiter ;
传递参数
create procedure procedure_name([in/out/inout] 参数名 参数类型)
IN : 该参数可以作为输入,也就是需要调用方传入值 , 默认
OUT: 该参数作为输出,也就是该参数可以作为返回值
INOUT: 既可以作为输入参数,也可以作为输出参数
**IN - 输入**
delimiter $
create procedure pro_test5(in height int)
begin
declare description varchar(50) default '';
if height >= 180 then
set description='身材高挑';
elseif height >= 170 and height < 180 then
set description='标准身材';
else
set description='一般身材';
end if;
select concat('身高 ', height , '对应的身材类型为:',description);
end$
delimiter ;
**OUT-输出**
create procedure pro_test5(in height int , out description varchar(100))
begin
if height >= 180 then
set description='身材高挑';
elseif height >= 170 and height < 180 then
set description='标准身材';
else
set description='一般身材';
end if;
end$
调用:
call pro_test5(168, @description)$
select @description$
case结构
switch ()
case
case
default:
方式一 :
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] ...
[ELSE statement_list]
END CASE;
----传递一个int类型的数字 如果为1 则输出星期一
方式二 :
CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] ...
[ELSE statement_list]
END CASE;
需求:
给定一个月份, 然后计算出所在的季度
输入一个数字(1~7) 根据数字判断星期几
输入一个月份 判断这个有几天2月28天。
delimiter $
create procedure pro_test9(month int)
begin
declare result varchar(20);
case
when month >= 1 and month <=3 then
set result = '第一季度';
when month >= 4 and month <=6 then
set result = '第二季度';
when month >= 7 and month <=9 then
set result = '第三季度';
when month >= 10 and month <=12 then
set result = '第四季度';
end case;
select concat('您输入的月份为 :', month , ' , 该月份为 : ' , result) as content ;
end$
delimiter ;
while循环
while search_condition do
statement_list
end while; --别忘记分号
计算从1加到n的数的和值 delimiter $ create procedure pro_test8(n int) begin declare total int default 0; declare num int default 1; while num<=n do set total = total + num; set num = num + 1; end while; select total; end$ delimiter ;
repeat结构
有条件的循环控制语句, 当满足条件的时候退出循环 。while 是满足条件才执行,repeat 是满足条件就退出循环。
REPEAT
statement_list
UNTIL search_condition --不要加分号
END REPEAT;
计算从1加到n的值
delimiter $
create procedure pro_test10(n int)
begin
declare total int default 0;
repeat
set total = total + n;
set n = n - 1;
until n=0
end repeat;
select total ;
end$
delimiter ;
游标/光标 (了解)--能看懂
声明游标:
DECLARE cursor_name CURSOR FOR select_statement ; -- select语句
OPEN 游标:
OPEN cursor_name ;
FETCH 游标:
FETCH cursor_name INTO var_name [, var_name] ...
CLOSE 游标:
CLOSE cursor_name ;
初始化脚本:
create table emp(
id int(11) not null auto_increment ,
name varchar(50) not null comment '姓名',
age int(11) comment '年龄',
salary int(11) comment '薪水',
primary key(`id`)
)engine=innodb default charset=utf8 ;
insert into emp(id,name,age,salary) values(null,'金毛狮王',55,3800),(null,'白眉鹰王',60,4000),(null,'青翼蝠王',38,2800),(null,'紫衫龙王',42,1800);
create PROCEDURE p13()
begin
DECLARE n varchar(20);
DECLARE s int;
DECLARE has_data int default 1;
-- 声明游标
DECLARE my CURSOR for select name,salary from emp;
--
DECLARE EXIT HANDLER FOR NOT FOUND set has_data = 0;
create table if not EXISTS tb_my(
id int primary key auto_increment,
name varchar(20),
salary int
);
-- 开启游标
open my;
while has_data=1 do
-- 取出游标的数据
FETCH my INTO n,s;
insert into tb_my(name,salary) values(n,s);
end while;
close my;
end;
通过循环结构 , 获取游标中的数据 :
DELIMITER $
create procedure pro_test12()
begin
DECLARE id int(11);
DECLARE name varchar(50);
DECLARE age int(11);
DECLARE salary int(11);
DECLARE has_data int default 1;
DECLARE emp_result CURSOR FOR select * from emp;
-- 若没有数据返回,程序继续,并将变量has_data设为0
DECLARE EXIT HANDLER FOR NOT FOUND set has_data = 0;
open emp_result;
repeat
fetch emp_result into id , name , age , salary;
select concat('id为',id, ', name 为' ,name , ', age为 ' ,age , ', 薪水为: ', salary);
until has_data = 0
end repeat;
close emp_result;
end$
DELIMITER ;
存储函数 ---存储过程 函数:函数有返回值。
定义一个存储函数, 请求满足条件的总记录数
delimiter $
create function count_city(countryId int)
returns int
begin
declare cnum int ;
select count(*) into cnum from city where country_id = countryId;
return cnum;
end$
delimiter ;
select count_city(1); select count_city(2);
索引分类
索引的语法
create table student(
id int primary key,
name varchar(20),
phone varchar(11),
email varchar(50),
profession varchar(30),
age int,
gender TINYINT,
status TINYINT,
createtime datetime
);
name字段为姓名字段,该字段的值可能会重复,为该字段创建索引。
CREATE INDEX inx_name on student(name)
phone手机号字段的值,是非空,且唯一的,为该字段创建唯一索引。
CREATE UNIQUE INDEX inx_phone on student(phone)
为profession、age、status创建联合索引。
CREATE INDEX profession_age_status on student(profession,age,status)
为email建立合适的索引来提升查询效率。
CREATE UNIQUE INDEX inx_email on student(email)
索引使用规则
100
EXPLAIN select * from student where profession='软件工程' and age = 31 and status='0'
97
EXPLAIN select * from student where profession='软件工程' and age = 31
93
EXPLAIN select * from student where profession='软件工程'
null
EXPLAIN select * from student where age = 31 and status='0'
null
EXPLAIN select * from student where status='0'
null
EXPLAIN select * from student where profession='软件工程' and age > 31 and status='0'
100
EXPLAIN select * from student where profession='软件工程' and age >= 31 and status='0'
EXPLAIN SELECT * from student where substring(phone,10,2) ='15'
Mysql锁问题
1) 表锁:操作时,会锁定整个表。MyISAM 5.6 后InnoDB支持
2) 行锁:操作时,会锁定当前操作行。
从对数据操作的类型分:
1) 读锁(共享锁):针对同一份数据,多个读操作可以同时进行而不会互相影响。
2) 写锁(排它锁):当前操作没有完成之前,它会阻断其他写操作和读操作。
加读锁 : lock table table_name read;
加写锁 : lock table table_name write;
unlock tables;
InnoDB 行锁 支持事务
行锁特点 :偏向InnoDB 存储引擎,开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。
InnoDB 与 MyISAM 的最大不同有两点:一是支持事务;二是 采用了行级锁。
如何搭建mysql的主从关系
搭建步骤
master
1) 在master 的配置文件(/etc/my.cnf)中,配置如下内容:
#mysql 服务ID,保证整个集群环境中唯一
server-id=1
#mysql binlog 日志的存储路径和文件名
log-bin=/var/lib/mysql/mysqlbin
#错误日志,默认已经开启
#log-err
#mysql的安装目录
#basedir
#mysql的临时目录
#tmpdir
#mysql的数据存放目录
#datadir
#是否只读,1 代表只读, 0 代表读写
read-only=0
#忽略的数据, 指不需要同步的数据库
binlog-ignore-db=mysql
#指定同步的数据库
#binlog-do-db=db01
2) 执行完毕之后,需要重启Mysql:
systemctl restart mysqld
3) 创建同步数据的账户,并且进行授权操作:
设置远程访问。
grant replication slave on *.* to 'root'@'192.168.192.131' identified by 'root';
flush privileges;
4) 查看master状态:
show master status;
File : 从哪个日志文件开始推送日志文件 给从节点
Position : 从哪个位置开始推送日志 从什么位置开始同步
Binlog_Ignore_DB : 指定不需要同步的数据库
slave
在 slave 端配置文件中,配置如下内容:
#mysql服务端ID,唯一
server-id=2
#指定binlog日志
log-bin=/var/lib/mysql/mysqlbin
2) 执行完毕之后,需要重启Mysql:
systemctl restart mysqld
3) 执行如下指令 :slaveof ip port
change master to master_host= '192.168.1.130', master_user='root', master_password='root', master_log_file='mysqlbin.000001', master_log_pos=154;
指定当前从库对应的主库的IP地址,用户名,密码,从哪个日志文件开始的那个位置开始同步推送日志。
4) 开启同步操作
start slave;
show slave status\G;
5) 停止同步操作
stop slave;
或者
解决
查看主从的server_id变量
master_mysql> show variables like 'server_id';
从上面的情形可知,主从mysql已经使用了不同的server_id
看看主从server-uuid=3561b5a3-0e85-11ed-9257-000c29fa144f是否一致
[root@dbsrv2 ~]# more /data/mysqldata/auto.cnf ###从上的uuid,果然出现了重复,原因是克隆了虚拟机,只改server_id不行
[auto]
[root@dbsrv2 ~]# mv /data/mysqldata/auto.cnf /data/mysqldata/auto.cnf.bk ###重命名该文件
[root@dbsrv2 ~]# more /data/mysqldata/auto.cnf ###重启后自动生成新的auto.cnf文件,即新的UUID
service mysql restart ###重启mysql
看看id是否改变
再重新来一遍
j建立成功
MyCat的安装
1、将压缩包上传到Linux指定的目录下并且解压
解压命令:
tar -zxvf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<!-- 配置逻辑库 name:表示逻辑库的名称 dataNode=管理的节点名称 -->
<schema name="testdb" checkSQLschema="true" sqlMaxLimit="1000" dataNode="dn1"></schema>
<!-- dataNode表示定义数据节点的标签 name:节点名称 dataHost=数据的主机名称 database表示真实数据库的名称-->
<dataNode name="dn1" dataHost="localhost1" database="mydb"/>
<!-- 定义真实服务所在的地址 name:表示名称 -->
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<!-- 按照心跳机制来判断真实的数据库是否正常运行-->
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="192.168.1.130:3306" user="root" password="root">
<readHost host="hostS1" url="192.168.1.131:3306" user="root" password="root"></readHost>
</writeHost>
</dataHost>
</mycat:schema>
<!DOCTYPE mycat:serve SYSTEM "server,dtd">
<mycat:serve xmlns:mycat="http://io.mycat/">
<user name='macat'>
<property name="password">123456<property>
<property name="schemas">testdb<property>
</user>
</mycat:server>
cat schema.xml cat server.xml 测试
1、 控制台启动 :去 mycat/bin 目录下执行 ./mycat console
cd ..
cd bin/
./mycat
./mycat console
mysql -umycat -p123456 -P 8066 -h 192.168.56.20
2 、后台启动 :去 mycat/bin 目录下 ./mycat start
测试一下即可