SQL高级篇

小母牛开飞机.

已于 2022-09-19 15:25:33 修改

阅读量250

点赞数

文章标签： java 数据库 servlet

于 2022-09-15 21:43:38 首次发布

本文链接：https://blog.csdn.net/weixin_65942614/article/details/126879224

版权

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

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/">

<schema name="testdb" checkSQLschema="true" sqlMaxLimit="1000" dataNode="dn1"></schema>

   
   <dataNode name="dn1" dataHost="localhost1" database="mydb"/>

   
   <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>