使用mysql以及连接数据库

最新推荐文章于 2024-08-03 19:27:22 发布

2301_79655496

最新推荐文章于 2024-08-03 19:27:22 发布

阅读量1k

点赞数 20

分类专栏：程序员文章标签：数据库 mysql

本文链接：https://blog.csdn.net/2301_79655496/article/details/137974644

版权

程序员专栏收录该内容

267 篇文章 0 订阅

订阅专栏

create temporary table yyy (id int);

create temporary table if not exists yyy (

id bigint auto_increment,

name varchar(20) not null unique, – 唯一，不重复的

birth datetime,

gender enum(‘f’, ‘m’), – Female Male

minzu set(‘汉’, ‘俄罗斯’),

created_at timestamp,

primary key (id)

);

create table aaa (id int auto_increment primary key, name varchar(20))

default charset = gbk

engine = InnoDB

auto_increment = 7

comment = `这只是一个测试表而已`;

create table gbk_test2 (vvv varchar(4)) default charset = gbk;

create table gbk_test3 (vvv varchar(4)) default charset = utf8;

insert into gbk_test2 values (‘中国’);

insert into gbk_test3 values (‘中国’);

select * from gbk_test2;

select * from gbk_test3;

select length(vvv) from gbk_test2;

select length(vvv) from gbk_test3;

select char_length(vvv) from gbk_test2;

select char_length(vvv) from gbk_test3;

一致性，完整性

| id | 名字 | xxx |

| — | — | — |

| 1 | Java | 2 |

| 2 | 太极剑法 | 1 |

| 3 | MySQL指南 | 22 |

| id | 名字 | 生日 |

| — | — | — |

| 1 | 张三丰 | x |

| 2 | 我 | y |

| 3 | 你好 | z |

6 数据类型

大致分为 6 类:

整数: bit / bool / tinyint / smallint / mediumint / int / bitint
浮点数: float / double / decimal
字符串: char / varchar / tinytext / text / mediumtext / longtext
BLOB(Binary Large Object): tinyblob / blob / mediumblob / longblob
日期: date(2018-9-6) / time (03:33:49) / datetime(2018-9-6 03:34:08) / timestamp
其他: binary / varbinary / enum / set / geometry / point / LineString …

问题:

char 跟 varchar 有什么区别？
varchar 最多存储的长度是多少？
varchar 跟 text 类型有什么区别？
set/enum 该怎么去使用？

create table char_demo ( aaa char(40), bbb varchar(40), ccc text(40) ); insert into char_demo values ('123', '123', '123'); select length(aaa), length(bbb), length(ccc) from char_demo; SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH'; select length(aaa), length(bbb), length(ccc) from char_demo;

table char_demo

(

aaa char(40),

bbb varchar(40),

ccc text(40)

);

insert into char_demo values (‘123’, ‘123’, ‘123’);

select length(aaa), length(bbb), length(ccc) from char_demo;

SET sql_mode = ‘PAD_CHAR_TO_FULL_LENGTH’;

select length(aaa), length(bbb), length(ccc) from char_demo;

总结 Char vs Varchar vs Text:

char(n) 和 varchar(n) 中间的 n 代表字符的个数，不是字节的个数
如果数据超过 n 的限制，那么数据将会被截断
char 是固定宽度，如果数据长度不满足 n，那么将会在右边用空格补齐；varchar 是变长宽度
varchar 是标准类型；text 不是标准类型
varchar 跟 text 都最大保存 65535 字节长度的数据。但是 text 还有 mediumtext/longtext 它可以支持存储更多的内容
varchar 的存储格式是 stored-inline, text 的存储格式是 off-record。一般情况来说，varchar 的速度要比 text 快

BLOB 类型，可以存储大字段。

ENUM 跟 SET 是两种比较特殊的字符串类型，他们对字段进行了一定约束:

create table person ( id int auto_increment primary key, name varchar(30) not null, gender char(2) -- 限制，只有男、女、未知 ); create table person ( id int auto_increment primary key, name varchar(30) not null, gender enum('男', '女', '未知') -- 限制，只有男、女、未知 ); create table person2 ( id int auto_increment primary key, name varchar(30) not null, gender enum('男', '女', '未知'), -- 限制，只有男、女、未知 country set('中国', '美国', '南非') ); insert into person3 (name, gender, country) values ('灭绝', '女', '美国,南非');

table person (

id int auto_increment primary key,

name varchar(30) not null,

gender char(2) – 限制，只有男、女、未知

);

create table person (

id int auto_increment primary key,

name varchar(30) not null,

gender enum(‘男’, ‘女’, ‘未知’) – 限制，只有男、女、未知

);

create table person2 (

id int auto_increment primary key,

name varchar(30) not null,

gender enum(‘男’, ‘女’, ‘未知’), – 限制，只有男、女、未知

country set(‘中国’, ‘美国’, ‘南非’)

);

insert into person3 (name, gender, country) values (‘灭绝’, ‘女’, ‘美国,南非’);

7 主键约束

主键，primary key，从性能和实际出发，应该遵循下面几点:

尽量短，避免占用太多空间
唯一，不能跟其他行的数据重复
不会改变
代理键

所谓代理键是指跟业务无关的字段:

因为和业务无关，所以可以使用 int 等类型尽量短
因为和业务无关，所以可以避免因为业务变动引起的主键变化
不要相信自己的直觉，因为在直觉上感觉不会变的东西，往往都是经不起实践考验的

8 表的修改

基本语法:

ALTER [ONLINE|OFFLINE] [IGNORE] TABLE tbl_name [alter_specification [, alter_specification] ...] [partition_options] alter_specification: table_options | ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name] | ADD [COLUMN] (col_name column_definition,...) | ADD {INDEX|KEY} [index_name] [index_type] (key_part,...) [index_option] ... | ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (key_part,...) [index_option] ... | ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (key_part,...) [index_option] ... | ADD FULLTEXT [INDEX|KEY] [index_name] (key_part,...) [index_option] ... | ADD SPATIAL [INDEX|KEY] [index_name] (key_part,...) [index_option] ... | ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (col_name,...) reference_definition | ALGORITHM [=] {DEFAULT|INPLACE|COPY} | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT} | CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name] | [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name] | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name] | {DISABLE|ENABLE} KEYS | {DISCARD|IMPORT} TABLESPACE | DROP [COLUMN] col_name | DROP {INDEX|KEY} index_name | DROP PRIMARY KEY | DROP FOREIGN KEY fk_symbol | FORCE | LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE} | MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name] | ORDER BY col_name [, col_name] ... | RENAME [TO|AS] new_tbl_name | ADD PARTITION (partition_definition) | DROP PARTITION partition_names | TRUNCATE PARTITION {partition_names | ALL} | COALESCE PARTITION number | REORGANIZE PARTITION partition_names INTO (partition_definitions) | EXCHANGE PARTITION partition_name WITH TABLE tbl_name | ANALYZE PARTITION {partition_names | ALL} | CHECK PARTITION {partition_names | ALL} | OPTIMIZE PARTITION {partition_names | ALL} | REBUILD PARTITION {partition_names | ALL} | REPAIR PARTITION {partition_names | ALL} | REMOVE PARTITIONING key_part: col_name [(length)] [ASC | DESC] index_type: USING {BTREE | HASH} index_option: KEY_BLOCK_SIZE [=] value | index_type | WITH PARSER parser_name | COMMENT 'string' table_options: table_option [[,] table_option] ... table_option: AUTO_INCREMENT [=] value | AVG_ROW_LENGTH [=] value | [DEFAULT] CHARACTER SET [=] charset_name | CHECKSUM [=] {0 | 1} | [DEFAULT] COLLATE [=] collation_name | COMMENT [=] 'string' | CONNECTION [=] 'connect_string' | {DATA|INDEX} DIRECTORY [=] 'absolute path to directory' | DELAY_KEY_WRITE [=] {0 | 1} | ENGINE [=] engine_name | INSERT_METHOD [=] { NO | FIRST | LAST } | KEY_BLOCK_SIZE [=] value | MAX_ROWS [=] value | MIN_ROWS [=] value | PACK_KEYS [=] {0 | 1 | DEFAULT} | PASSWORD [=] 'string' | ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT} | STATS_AUTO_RECALC [=] {DEFAULT|0|1} | STATS_PERSISTENT [=] {DEFAULT|0|1} | STATS_SAMPLE_PAGES [=] value | TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}] | UNION [=] (tbl_name[,tbl_name]...)

IGNORE] TABLE tbl_name

[alter_specification [, alter_specification] …]

[partition_options]

alter_specification:

table_options

| ADD [COLUMN] col_name column_definition

[FIRST | AFTER col_name]

| ADD [COLUMN] (col_name column_definition,…)

| ADD {INDEX|KEY} [index_name]

[index_type] (key_part,…) [index_option] …

| ADD [CONSTRAINT [symbol]] PRIMARY KEY

[index_type] (key_part,…) [index_option] …

| ADD [CONSTRAINT [symbol]]

UNIQUE [INDEX|KEY] [index_name]

[index_type] (key_part,…) [index_option] …

| ADD FULLTEXT [INDEX|KEY] [index_name]

(key_part,…) [index_option] …

| ADD SPATIAL [INDEX|KEY] [index_name]

(key_part,…) [index_option] …

| ADD [CONSTRAINT [symbol]]

FOREIGN KEY [index_name] (col_name,…)

reference_definition

| ALGORITHM [=] {DEFAULT|INPLACE|COPY}

| ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}

| CHANGE [COLUMN] old_col_name new_col_name column_definition

[FIRST|AFTER col_name]

| [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]

| CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]

| {DISABLE|ENABLE} KEYS

| {DISCARD|IMPORT} TABLESPACE

| DROP [COLUMN] col_name

| DROP {INDEX|KEY} index_name

| DROP PRIMARY KEY

| DROP FOREIGN KEY fk_symbol

| FORCE

| LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}

| MODIFY [COLUMN] col_name column_definition

[FIRST | AFTER col_name]

| ORDER BY col_name [, col_name] …

| RENAME [TO|AS] new_tbl_name

| ADD PARTITION (partition_definition)

| DROP PARTITION partition_names

| TRUNCATE PARTITION {partition_names | ALL}

| COALESCE PARTITION number

| REORGANIZE PARTITION partition_names INTO (partition_definitions)

| EXCHANGE PARTITION partition_name WITH TABLE tbl_name

| ANALYZE PARTITION {partition_names | ALL}

| CHECK PARTITION {partition_names | ALL}

| OPTIMIZE PARTITION {partition_names | ALL}

| REBUILD PARTITION {partition_names | ALL}

| REPAIR PARTITION {partition_names | ALL}

| REMOVE PARTITIONING

key_part:

col_name [(length)] [ASC | DESC]

index_type:

USING {BTREE | HASH}

index_option:

KEY_BLOCK_SIZE [=] value

| index_type

| WITH PARSER parser_name

| COMMENT ‘string’

table_options:

table_option [[,] table_option] …

table_option:

AUTO_INCREMENT [=] value

| AVG_ROW_LENGTH [=] value

| [DEFAULT] CHARACTER SET [=] charset_name

| CHECKSUM [=] {0 | 1}

| [DEFAULT] COLLATE [=] collation_name

| COMMENT [=] ‘string’

| CONNECTION [=] ‘connect_string’

| {DATA|INDEX} DIRECTORY [=] ‘absolute path to directory’

| DELAY_KEY_WRITE [=] {0 | 1}

| ENGINE [=] engine_name

| INSERT_METHOD [=] { NO | FIRST | LAST }

| KEY_BLOCK_SIZE [=] value

| MAX_ROWS [=] value

| MIN_ROWS [=] value

| PACK_KEYS [=] {0 | 1 | DEFAULT}

| PASSWORD [=] ‘string’

| ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}

| STATS_AUTO_RECALC [=] {DEFAULT|0|1}

| STATS_PERSISTENT [=] {DEFAULT|0|1}

| STATS_SAMPLE_PAGES [=] value

| TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}]

| UNION [=] (tbl_name[,tbl_name]…)

基本示例:

-- 添加列 alter table person add column qq varchar(20); alter table person add qq varchar(20); -- 可以去掉 column 关键词 alter table person add tel varchar(20) after gender; -- 指定位置 alter table person add (xxx int, yyy int); -- 修改列 ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20); -- 删除主键 alter table person change id id int; -- 需要将 auto_increment 去掉 alter table person drop primary key; -- 增加主键 alter table person add primary key (id); -- 增加/删除外键 alter table book add constraint fk_authorid foreign key (authorid) references author (authorid); -- 增加/删除索引 ALTER TABLE t2 ADD INDEX (d), ADD UNIQUE (a); -- 重命名 alter table rename to people;

alter table person add column qq varchar(20);

alter table person add qq varchar(20); – 可以去掉 column 关键词

alter table person add tel varchar(20) after gender; – 指定位置

alter table person add (xxx int, yyy int);

– 修改列

ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);

– 删除主键

alter table person change id id int; – 需要将 auto_increment 去掉

alter table person drop primary key;

– 增加主键

alter table person add primary key (id);

– 增加/删除外键

alter table book

add constraint fk_authorid

foreign key (authorid)

references author (authorid);

– 增加/删除索引

ALTER TABLE t2 ADD INDEX (d), ADD UNIQUE (a);

– 重命名

alter table rename to people;

9 引擎(Engine)

MySQL 为了满足各种需求，提供了不同的引擎实现，比如 MyISAM/InnoDB 等。

show engines

可以为数据库指定默认引擎，只需要在 my.ini 配置文件中增加 default-storage-engine=InnoDB 就可以了
也可以在创建表的时候，手动指定使用的引擎:

create table xxx (id int) engine=MyISAM;

在早期(<5.0)MySQL 的默认引擎是 MyISAM，它的特点是快，它的缺点是功能简单，不支持事务包括外键等功能
目前(>5)默认引擎是 InnoDB，它为数据库提供了事务支持、外键支持、锁的支持等特性

事务相关的语法有:

start transaction; -- begin; savepoint xxx; -- 存档 rollback; -- 回滚 rollback to xxx; -- 回档到某个保存点 commit; -- 提交事务 -- 创表 create table xxx_inno (id int) engine=InnoDB; create table xxx_isam (id int) engine=MyISAM; -- 查看 show create table xxx_inno; show create table xxx_isam; -- 查看 xxx_inno 的事务支持情况 -- 首先，开启事务 start transaction; -- 然后，插入数据 insert into xxx_inno values (111); insert into xxx_inno values (222); -- 现在，在其他的连接中，是查询不到这数据的 select * from xxx_inno; -- 建立保存点(存档) savepoint s1; -- 再插入更多数据 insert into xxx_inno values (333); -- 现在，在当前事务中，可以查到3条数据 select * from xxx_inno; -- 使用 rollback 回档，将回到插入 333 前的状态 rollback to s1; -- 提交事务 commit; -- 现在，在其他的连接中，就能查询到这些数据了 select * from xxx_inno; -- 查看 xxx_isam 的事务支持情况 start transaction; insert into xxx_isam values (666); -- 在没有 commit 的时候，在其他连接中竟然能查询这数据 -- 说明 MyISAM 是不支持事务的 select * from xxx_isam;

transaction; – begin;

savepoint xxx; – 存档

rollback; – 回滚

rollback to xxx; – 回档到某个保存点

commit; – 提交事务

– 创表

create table xxx_inno (id int) engine=InnoDB;

create table xxx_isam (id int) engine=MyISAM;

– 查看

show create table xxx_inno;

show create table xxx_isam;

– 查看 xxx_inno 的事务支持情况

– 首先，开启事务

start transaction;

– 然后，插入数据

insert into xxx_inno values (111);

insert into xxx_inno values (222);

– 现在，在其他的连接中，是查询不到这数据的

select * from xxx_inno;

– 建立保存点(存档)

savepoint s1;

– 再插入更多数据

insert into xxx_inno values (333);

– 现在，在当前事务中，可以查到3条数据

select * from xxx_inno;

– 使用 rollback 回档，将回到插入 333 前的状态

rollback to s1;

– 提交事务

commit;

– 现在，在其他的连接中，就能查询到这些数据了

select * from xxx_inno;

– 查看 xxx_isam 的事务支持情况

start transaction;

insert into xxx_isam values (666);

– 在没有 commit 的时候，在其他连接中竟然能查询这数据

– 说明 MyISAM 是不支持事务的

select * from xxx_isam;

10 存储过程

[WARNNING] 存储过程不建议使用:

难以调试，难以维护
业务逻辑跟数据库耦合度过高，不便于迁移
对数据库的压力过大，需要分拆
MySQL 对存储过程的支持有些弱鸡

10.1 基本语句

-- 存储过程的定义 delimiter // -- 使用 delimiter 指令，将默认的分隔符(;)换为我们自定义的分隔符 create procedure ppx1 (p int) -- 创建存储过程，名字 ppx1, 一个入参 int 类型的 p begin -- 使用 begin 跟 end 将所有语句包括起来，相当于 {} set @x = 0; -- set 是用来定义或者设置变量的值的，用户级别的变量，需要使用 @ 作为前缀 repeat -- repeat .. until .. end repeat 这是使用循环的其中一种方式 set @x = @x + 1; -- 在循环体中，我们就可以进行我们不断重复的业务、事情了 until @x > p end repeat; -- 使用 until 表示循环结束的条件。类似于 while 循环中的 break end delimiter ; -- 过程定义完成后，需要将分隔符还原为默认(;) -- 调用存储过程 call ppx1(100); -- 查看用户定义变量的值 select @x;

– 使用 delimiter 指令，将默认的分隔符(;)换为我们自定义的分隔符

create procedure ppx1 (p int) – 创建存储过程，名字 ppx1, 一个入参 int 类型的 p

begin – 使用 begin 跟 end 将所有语句包括起来，相当于 {}

set @x = 0; – set 是用来定义或者设置变量的值的，用户级别的变量，需要使用 @ 作为前缀

repeat – repeat … until … end repeat 这是使用循环的其中一种方式

set @x = @x + 1; – 在循环体中，我们就可以进行我们不断重复的业务、事情了

until @x > p end repeat; – 使用 until 表示循环结束的条件。类似于 while 循环中的 break

end

delimiter ; – 过程定义完成后，需要将分隔符还原为默认(😉

– 调用存储过程

call ppx1(100);

– 查看用户定义变量的值

select @x;

求和 1-n:

create procedure sum2 (y int) begin declare x int; -- 声明局部变量 set x = 0; -- 为局部变量赋值 set @sum = 0; repeat set x = x + 1; set @sum = @sum + x; until x >= y end repeat; end

procedure sum2 (y int)

begin

declare x int; – 声明局部变量

set x = 0; – 为局部变量赋值

set @sum = 0;

repeat

set x = x + 1;

set @sum = @sum + x;

until x >= y end repeat;

end

10.2 变量定义

变量分类：

局部变量，使用在存储过程或函数中，这样的变量使用 declare 进行声明，使用 set 进行赋值。作用域是过程中。
用户变量，定义 set @xxx = 333; select @xxx := 333; 需要用 @ 符号进行修饰
系统变量，系统自带的，内建的变量

可以通过命令行的方式进行设置 mysqld –default_storage_engine=MyISAM
可以在 my.ini 配置文件中进行配置 default_storage_engine=MyISAM
可以通过 set 的方式进行动态设置
系统变量，分为两类:

全局的(global)

show global variables; set global default_storage_engine=MyISAM; set @@global.default_storage_engine=MyISAM;

global variables;

set global default_storage_engine=MyISAM;

set @@global.default_storage_engine=MyISAM;

会话的(session)

show session variables; show local variables; show variables; set session default_storage_engine=MyISAM; set local default_storage_engine=MyISAM; set @@session.default_storage_engine=MyISAM; set @@local.default_storage_engine=MyISAM; set default_storage_engine=MyISAM; set @@default_storage_engine=MyISAM;

session variables;

show local variables;

show variables;

set session default_storage_engine=MyISAM;

set local default_storage_engine=MyISAM;

set @@session.default_storage_engine=MyISAM;

set @@local.default_storage_engine=MyISAM;

set default_storage_engine=MyISAM;

set @@default_storage_engine=MyISAM;

10.3 参数与游标

如果让存储过程跟外边进行数据交互，需要用到参数

第一种，在过程内部使用用户变量 @xxx

delimiter // create procedure pp1 () begin declare v1 int default 100; set @aaa = v1*101; end // delimiter ; -- 调用 call pp1(); select @aaa;

create procedure pp1 ()

begin

declare v1 int default 100;

set @aaa = v1*101;

end //

delimiter ;

– 调用

call pp1();

select @aaa;

另外，可以将表中查询到的数据赋予参数:

delimiter // drop procedure if exists pp2; create procedure pp2 () begin select count(*) into @bbb from t_person; select weixin into @ccc from t_person limit 1; end // delimiter ; -- 调用 call pp2(); select @bbb, @ccc;

drop procedure if exists pp2;

create procedure pp2 ()

begin

select count(*) into @bbb from t_person;

select weixin into @ccc from t_person limit 1;

end //

delimiter ;

– 调用

call pp2();

select @bbb, @ccc;

使用过程内部定义用户变量的方式，容易在调用的时候产生冲突、混淆，所以最好使用外部定义，结合 out/inout 类型参数:

delimiter // drop procedure if exists pp3; create procedure pp3 (inout bbb varchar(30), inout ccc varchar(20)) -- in out in out begin insert into t_person (name, weixin) value (bbb, ccc); select count(*) into bbb from t_person; select weixin into ccc from t_person limit 1; end // delimiter ; -- 调用 set @ddd = '3333'; set @eee = 'sdfsfsd'; call pp3(@ddd, @eee); select @ddd, @eee;

drop procedure if exists pp3;

create procedure pp3 (inout bbb varchar(30), inout ccc varchar(20)) – in out in out

begin

insert into t_person (name, weixin) value (bbb, ccc);

select count(*) into bbb from t_person;

select weixin into ccc from t_person limit 1;

end //

delimiter ;

– 调用

set @ddd = ‘3333’;

set @eee = ‘sdfsfsd’;

call pp3(@ddd, @eee);

select @ddd, @eee;

如果需要处理表中的多行数据，就需要用到游标(Cursor):

delimiter // drop procedure if exists pp4; create procedure pp4 (out sum bigint) begin declare iii int; declare done int default 0; declare c_person cursor for select weixin from t_person; -- 1. 游标的定义 declare continue handler for not found set done = 1; -- 捕获系统抛出的 not found 错误，如果捕获到，将 done 设置为 1 set sum = 0; open c_person; -- 2. 打开游标 xxx:loop fetch c_person into iii; -- 3. 使用游标 if done = 1 then -- 4. 设定退出条件 leave xxx; end if; set sum = sum + iii; end loop; close c_person; -- 5. 关闭游标 end // delimiter ; -- 调用 set @sum = 0; call pp4(@sum); select @sum;

drop procedure if exists pp4;

create procedure pp4 (out sum bigint)

begin

declare iii int;

declare done int default 0;

declare c_person cursor for select weixin from t_person; – 1. 游标的定义

declare continue handler for not found set done = 1; – 捕获系统抛出的 not found 错误，如果捕获到，将 done 设置为 1

set sum = 0;

open c_person; – 2. 打开游标

xxx:loop

fetch c_person into iii; – 3. 使用游标

if done = 1 then – 4. 设定退出条件

leave xxx;

end if;

set sum = sum + iii;

end loop;

close c_person; – 5. 关闭游标

end //

delimiter ;

– 调用

set @sum = 0;

call pp4(@sum);

select @sum;

10.4 JDBC 操作存储过程示例

存储过程示例:

delimiter // drop procedure if exists pp6; create procedure pp6 (inout bbb varchar(30), inout ccc varchar(20)) -- in out in out begin insert into t_person (name, weixin) value (bbb, ccc); select count(*) into bbb from t_person; select weixin into ccc from t_person limit 1; select * from lagou_city where province='广东省'; select * from lagou_pos order by salary_min desc limit 10; end // delimiter ;

drop procedure if exists pp6;

create procedure pp6 (inout bbb varchar(30), inout ccc varchar(20)) – in out in out

begin

insert into t_person (name, weixin) value (bbb, ccc);

select count(*) into bbb from t_person;

select weixin into ccc from t_person limit 1;

select * from lagou_city where province=‘广东省’;

select * from lagou_pos order by salary_min desc limit 10;

end //

delimiter ;

JDBC代码:

// Statement PrepareStatement CallableStatement public static void main(String[] args) { Connection connection = null; CallableStatement ps = null; ResultSet rs = null; try { connection = DBUtil.getMySQLConnection(); ps = connection.prepareCall("call pp6(?, ?)"); ps.setString(1, "dfasd"); ps.setString(2, "16463"); ps.registerOutParameter(1, Types.VARCHAR); ps.registerOutParameter(2, Types.VARCHAR); boolean hasMore = ps.execute(); while (hasMore) { rs = ps.getResultSet(); while (rs.next()) { System.out.println(rs.getObject(1) + "/" + rs.getObject(2)); } rs.close(); hasMore = ps.getMoreResults(); } System.out.println(ps.getObject(1)); System.out.println(ps.getObject(2)); } catch (Exception e) { e.printStackTrace(); } finally { DBUtil.tryRelease(connection, ps, rs); } }

Statement PrepareStatement CallableStatement

public static void main(String[] args) {

Connection connection = null;

CallableStatement ps = null;

ResultSet rs = null;

try {

connection = DBUtil.getMySQLConnection();

ps = connection.prepareCall(“call pp6(?, ?)”);

ps.setString(1, “dfasd”);

ps.setString(2, “16463”);

ps.registerOutParameter(1, Types.VARCHAR);

ps.registerOutParameter(2, Types.VARCHAR);

boolean hasMore = ps.execute();

while (hasMore) {

rs = ps.getResultSet();

while (rs.next()) {

System.out.println(rs.getObject(1) + “/” + rs.getObject(2));

}

rs.close();

hasMore = ps.getMoreResults();

}

System.out.println(ps.getObject(1));

System.out.println(ps.getObject(2));

} catch (Exception e) {

e.printStackTrace();

} finally {

DBUtil.tryRelease(connection, ps, rs);

}

11 优化(Optimization)

11.1 解释计划(Explain)

可以查看数据内部是怎么分析执行相关语句的，根据这些可以对语句进行优化

explain select c.city as city2, p.* from lagou_pos p join lagou_city c on p.city = c.cid where c.province = '广东省' order by salary_min desc limit 10;

select c.city as city2, p.* from lagou_pos p

join lagou_city c on p.city = c.cid

where c.province = ‘广东省’

order by salary_min desc limit 10;

11.2 索引

索引是跟引擎相关的，索引的主要分类有:

B-Tree 索引，这是默认的索引形式，是最普遍的方式
R-Tree 索引，主要用于空间数据库字段上
Hash 索引，只用在 Memory 引擎上，使用场景比较简单

MySQL 不支持函数索引，但是可以支持前缀索引:

-- 创建 create index idx_pos_city_01 on lagou_pos(city); -- 普通索引 create unique index skdfjks on lagou_pos(pid); -- 唯一索引 create index idex_city_p01 on lagou_city (province(2)); -- 前缀索引 create index idex_city_p01 on lagou_city (province, city); -- 组合索引 -- 索引的创建原则: -- 1. 频繁读取的字段，适合创建 -- 2. 更新频繁的字段，不适合 -- 3. 数据差异性大的字段才适合，比如性别就不适合 -- 4. 索引要适当，因为索引会占用更多空间，并让查询的过程变得复杂。如果不恰当使用，非但不能增加效率，反而会拖累效率 -- 删除 drop index idx_pos_city_01 on lagou_pos; alter table lagou_pos drop index idx_pos_city_01; -- 查看表上的所有索引 show index from lagou_pos;

create index idx_pos_city_01 on lagou_pos(city); – 普通索引

create unique index skdfjks on lagou_pos(pid); – 唯一索引

create index idex_city_p01 on lagou_city (province(2)); – 前缀索引

create index idex_city_p01 on lagou_city (province, city); – 组合索引

– 索引的创建原则:

– 1. 频繁读取的字段，适合创建

– 2. 更新频繁的字段，不适合

– 3. 数据差异性大的字段才适合，比如性别就不适合

– 4. 索引要适当，因为索引会占用更多空间，并让查询的过程变得复杂。如果不恰当使用，非但不能增加效率，反而会拖累效率

– 删除

drop index idx_pos_city_01 on lagou_pos;

alter table lagou_pos drop index idx_pos_city_01;

– 查看表上的所有索引

show index from lagou_pos;

12 UseCases

12.1 使用存储过程批量插入数据

创建表，使用存储过程插入 10w 条数据

首先，创建表，比如:

create table t_person ( pid int auto_increment primary key, name varchar(20) not null, weixin varchar(20) not null );

table t_person (

pid int auto_increment primary key,

name varchar(20) not null,

weixin varchar(20) not null

);

其次，编写函数，求取随机字符串。将求取随机字符串的方法提取出来的目的是为了复用，这也是函数的目的:

drop function if exists rand_str; delimiter $$ create function rand_str(size int, type int) returns varchar(255) begin declare seed varchar(255); declare seed_no varchar(255) default '1234567890'; declare seed_en varchar(255) default 'qwertryuiopasdfghjklzxcvbnm'; declare ret varchar(255) default ''; declare i int default 0; set seed = case type -- if .. else / case when when 1 then seed_en when 2 then seed_no else concat(seed_en, seed_no) end; while i < size do set ret = concat(ret, substring(seed, floor(length(seed) * rand() + 1), 1)); set i = i + 1; end while; return ret; end$$ delimiter ; -- 调用 select rand_str(5, 1); -- 根据上述函数，求取 5 到 12 位长度的一个随机字符串 -- 思路一 -- 第一步，求取 0-9 之间的一个随机数字 select rand_str(1, 2); -- 第二步，将这个随机数字加上 5 ，得到的结果就是 5 到 14 之间的一个随机数字 select rand_str(1, 2) + 5; -- 第三步，求取 5 到 14 位长度的一个随机数字 select rand_str(rand_str(1, 2) + 5, 1); -- 第四步，将上述字符串，超过长度的部分，截取掉，让其最长的长度是 12。任务完成 select left(rand_str(rand_str(1, 2) + 5, 1), 12); -- 思路二 -- 第一步，得到一个随机字符串，长度为 5 select rand_str(5, 1); -- 第二步，得到一个随机字符串，长度为 0 到 9 之间 select rand_str(rand_str(1, 2), 1); -- 第三步，将两个字符串拼接，那么长度是 5 到 14 之间 select concat(rand_str(5, 1), rand_str(rand_str(1, 2), 1)); -- 最后一步，截断 select left(concat(rand_str(5, 1), rand_str(rand_str(1, 2), 1)), 12); -- 思路三 select concat(rand_str(5, 1), left(rand_str(rand_str(1, 2), 1), 7)); -- TODO 题目 -- 封装一个函数 rand_str2(m, n) -- 传入两个数字，返回一个 m~n 长度的随机字符串 -- 在实现中，尽量使用刚才封装好的 rand_str 函数

function if exists rand_str;

delimiter $$

create function rand_str(size int, type int)

returns varchar(255)

begin

declare seed varchar(255);

declare seed_no varchar(255) default ‘1234567890’;

declare seed_en varchar(255) default ‘qwertryuiopasdfghjklzxcvbnm’;

declare ret varchar(255) default ‘’;

declare i int default 0;

set seed = case type – if … else / case when

when 1 then seed_en

when 2 then seed_no

else concat(seed_en, seed_no) end;

while i < size do

set ret = concat(ret, substring(seed, floor(length(seed) * rand() + 1), 1));

set i = i + 1;

end while;

return ret;

end$$

delimiter ;

– 调用

select rand_str(5, 1);

– 根据上述函数，求取 5 到 12 位长度的一个随机字符串

– 思路一

– 第一步，求取 0-9 之间的一个随机数字

select rand_str(1, 2);

– 第二步，将这个随机数字加上 5 ，得到的结果就是 5 到 14 之间的一个随机数字

select rand_str(1, 2) + 5;

– 第三步，求取 5 到 14 位长度的一个随机数字

select rand_str(rand_str(1, 2) + 5, 1);

– 第四步，将上述字符串，超过长度的部分，截取掉，让其最长的长度是 12。任务完成

select left(rand_str(rand_str(1, 2) + 5, 1), 12);

– 思路二

– 第一步，得到一个随机字符串，长度为 5

select rand_str(5, 1);

– 第二步，得到一个随机字符串，长度为 0 到 9 之间

select rand_str(rand_str(1, 2), 1);

– 第三步，将两个字符串拼接，那么长度是 5 到 14 之间

select concat(rand_str(5, 1), rand_str(rand_str(1, 2), 1));

– 最后一步，截断

select left(concat(rand_str(5, 1), rand_str(rand_str(1, 2), 1)), 12);

– 思路三

select concat(rand_str(5, 1), left(rand_str(rand_str(1, 2), 1), 7));

– TODO 题目

– 封装一个函数 rand_str2(m, n)

– 传入两个数字，返回一个 m~n 长度的随机字符串

– 在实现中，尽量使用刚才封装好的 rand_str 函数

然后，编写存储过程，实现数据的插入:

delimiter // drop procedure if exists proc_person_data; create procedure proc_person_data (num int) begin declare ii int default 0; -- while .. end while -- repeat .. until .. end repeat -- loop_label:loop ... leave loop_label .. end loop; set autocommit = 0; while ii < num do insert into t_person (name, weixin) values (concat('user_', rand_str(6, 1)), rand_str(8, 2)); set ii = ii + 1; if ii % 100000 = 1 then commit ; end if; end while; commit ; set autocommit = 1; end; //

drop procedure if exists proc_person_data;

create procedure proc_person_data (num int)

begin

declare ii int default 0;

– while … end while

– repeat … until … end repeat

– loop_label:loop … leave loop_label … end loop;

set autocommit = 0;

while ii < num do

insert into t_person (name, weixin) values (concat(‘user_’, rand_str(6, 1)), rand_str(8, 2));

set ii = ii + 1;

if ii % 100000 = 1 then commit ; end if;

end while;

commit ;

set autocommit = 1;

end;

12.2 行列转换

表中的数据是这样的:

INSERT INTO `t_score` VALUES ('王海', '语文', '86'), ('王海', '数学', '83'), ('王海', '英语', '93'), ('陶俊', '语文', '88'), ('陶俊', '数学', '84'), ('陶俊', '英语', '94'), ('刘可', '语文', '80'), ('刘可', '数学', '86'), ('刘可', '英语', '88'), ('李春', '语文', '89'), ('李春', '数学', '80'), ('李春', '英语', '87');

INTO `t_score` VALUES

(‘王海’, ‘语文’, ‘86’),

(‘王海’, ‘数学’, ‘83’),

(‘王海’, ‘英语’, ‘93’),

(‘陶俊’, ‘语文’, ‘88’),

(‘陶俊’, ‘数学’, ‘84’),

(‘陶俊’, ‘英语’, ‘94’),

(‘刘可’, ‘语文’, ‘80’),

(‘刘可’, ‘数学’, ‘86’),

(‘刘可’, ‘英语’, ‘88’),

(‘李春’, ‘语文’, ‘89’),

(‘李春’, ‘数学’, ‘80’),

(‘李春’, ‘英语’, ‘87’);

要求查询的结果是这样:

| — | — | — | — | — |

| 刘可 | 80 | 86 | 88 | 254 |

| 李春 | 89 | 80 | 87 | 256 |

| 王海 | 86 | 83 | 93 | 262 |

| 陶俊 | 88 | 84 | 94 | 266 |

| TOTAL | 343 | 333 | 362 | 1038 |

方法不拘，但是要思考，要实现。

实现方法:

-- 1 select name, max(case subject when '数学' then score else 0 end) Math, max(case subject when '语文' then score else 0 end) Chinese, max(case subject when '英语' then score else 0 end) English, sum(score) score from t_score group by name union all select 'TOTAL', sum(case subject when '数学' then score else 0 end), sum(case subject when '语文' then score else 0 end), sum(case subject when '英语' then score else 0 end), sum(score) from t_score; -- 2 select IFNULL(NAME,'TOTAL') name, -- name or 'TOTAL' SUM(case subject when '数学' then score else 0 end) Math, SUM(case subject when '语文' then score else 0 end) Chinese, SUM(case subject when '英语' then score else 0 end) English, sum(score) score from t_score group by name WITH ROLLUP; -- 3 select IFNULL(NAME,'TOTAL') name, -- name or 'TOTAL' SUM(if (subject = '数学',score, 0) ) Math, SUM(if (subject = '语文',score, 0) ) Chinese, SUM(if (subject = '英语',score, 0) ) English, sum(score) score from t_score group by name WITH ROLLUP;

select name,

max(case subject when ‘数学’ then score else 0 end) Math,

max(case subject when ‘语文’ then score else 0 end) Chinese,

max(case subject when ‘英语’ then score else 0 end) English,

sum(score) score

from t_score group by name

union all

select ‘TOTAL’,

sum(case subject when ‘数学’ then score else 0 end),

sum(case subject when ‘语文’ then score else 0 end),

sum(case subject when ‘英语’ then score else 0 end),

sum(score)

from t_score;

– 2

select

IFNULL(NAME,‘TOTAL’) name, – name or ‘TOTAL’

SUM(case subject when ‘数学’ then score else 0 end) Math,

SUM(case subject when ‘语文’ then score else 0 end) Chinese,

SUM(case subject when ‘英语’ then score else 0 end) English,

sum(score) score

from t_score group by name WITH ROLLUP;

– 3

select

IFNULL(NAME,‘TOTAL’) name, – name or ‘TOTAL’

SUM(if (subject = ‘数学’,score, 0) ) Math,

SUM(if (subject = ‘语文’,score, 0) ) Chinese,

SUM(if (subject = ‘英语’,score, 0) ) English,

sum(score) score

from t_score group by name WITH ROLLUP;

13 备份与恢复

各种灾难防不胜防:

操作系统崩溃
突然的断电
文件系统损坏
硬盘故障

所以需要多种不同的数据备份策略:

热备份、冷备份
全量备份、增量备份
多机备份，灾后重建

不仅为了灾难，数据的迁移，也需要掌握相关的备份、恢复技巧。

在 MySQL 中:

不同引擎的备份机制是不一样的
最常用的备份方式是 mysqldump 命令

基本的导入、导出:

# 数据库备份 mysqldump -uroot -proot > ~/xxx.sql # > 和 < 是管道语句 mysqldump --all-databases --master-data --single-transaction > ~/xxx.sql mysqldump -uroot -proot --databases lagou test > ~/lagou.sql # 数据库恢复 mysql -uroot -proot < ~/xxx.sql mysql> source ~/xxx.sql

数据库备份

mysqldump -uroot -proot > ~/xxx.sql # > 和 < 是管道语句

mysqldump --all-databases --master-data --single-transaction > ~/xxx.sql

mysqldump -uroot -proot --databases lagou test > ~/lagou.sql

数据库恢复

mysql -uroot -proot < ~/xxx.sql

mysql> source ~/xxx.sql

在 InnoDB 引擎下，为了提高 source 导入效率，需要：

set names utf8; -- 需要正确的客户端编码 SET autocommit=0; -- 将自动提交关闭，避免每一条语句都会出发日志写入操作 SET unique_checks=0; -- 将唯一约束检查关闭 SET foreign_key_checks=0; -- 将外键约束检查关闭 ... SQL import statements ... INSERT INTO yourtable VALUES (1,2), (5,5), ...; -- 尽量使用这种方式的导入语句 COMMIT; -- 提交事务 -- 恢复设置 set autocommit=1; SET unique_checks=1; SET foreign_key_checks=1;

names utf8; – 需要正确的客户端编码

SET autocommit=0; – 将自动提交关闭，避免每一条语句都会出发日志写入操作

SET unique_checks=0; – 将唯一约束检查关闭

SET foreign_key_checks=0; – 将外键约束检查关闭

… SQL import statements …

INSERT INTO yourtable VALUES (1,2), (5,5), …; – 尽量使用这种方式的导入语句

COMMIT; – 提交事务

– 恢复设置

set autocommit=1;

SET unique_checks=1;

SET foreign_key_checks=1;

https://dev.mysql.com/doc/refman/5.6/en/optimizing-innodb-bulk-data-loading.html

14 数据迁移示例

将 sqlite3 格式的 lagou.db 中的数据迁移到 MySQL 中

14.1 命令行的方式

首先，从 sqlite 中导出:

sqlite3 lagou.db

.output d:/data/lagou.sql

.dump

然后，修改 lagou.sql 文件，让其兼容 MySQL 数据库。

之后就可以连接 MySQL 进行导入了:

drop table if exists lagou_position; set names utf8; set autocommit=0; source d:/data/lagou.sql commit; select count(*) from lagou_position;

table if exists lagou_position;

set names utf8;

set autocommit=0;

source d:/data/lagou.sql

commit;

select count(*) from lagou_position;

14.2 JDBC 的方式

需要注意的问题:

如何使用 jdbc 同时连接操作两个数据库
如何在 jdbc 中合理释放资源
异常的捕获与抛出，如何正确处理异常逻辑
jdbc 中批量导入数据的方式
MySQL 驱动中的 rewriteBatchedStatements 参数
代码的格式与规范问题

代码示例:

import java.sql.*; public class DataMigrateFromSQLiteToMySQL { // create table lagou_p1 as select * from lagou_position limit 0; public static void main(String[] args) { DataMigrateFromSQLiteToMySQL migrate = new DataMigrateFromSQLiteToMySQL(); migrate.doMigrate(); } public void doMigrate() { // 数据从哪里来？ Connection liteConn = null; Statement liteSt = null; ResultSet liteRs = null; // 数据到哪里去？ Connection mysqlConn = null; PreparedStatement mysqlPs = null; try { // 数据从这里来 liteConn = this.getSQLiteConnection(); liteSt = liteConn.createStatement(); liteRs = liteSt.executeQuery("select * from lagou_position"); // 数据到这里去 mysqlConn = this.getMySQLConnection(); mysqlPs = mysqlConn.prepareStatement("insert into lagou_p1 values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"); // 关闭自动提交，也就是开启事务 mysqlConn.setAutoCommit(false); int i = 0; long startTime = System.currentTimeMillis(); // 计时开始 // 来吧，迁移吧！ while(liteRs.next()) { for (int j = 1; j < 20; j++) { mysqlPs.setObject(j, liteRs.getObject(j)); } mysqlPs.addBatch(); // 每 10000 条，向数据库发送一次执行请求 if (i++ % 10000 == 0) { mysqlPs.executeBatch(); } } mysqlPs.executeBatch(); mysqlConn.commit(); // 提交事务 long stopTime = System.currentTimeMillis(); // 计时结束 // 输出结果 System.out.println("总共多少数据:" + i); System.out.println("一共花费 " + (stopTime - startTime) / 1000.0 + " 秒"); } catch (Exception e) { DBUtil.tryRollback(mysqlConn); } finally { DBUtil.tryRelease(liteConn, liteSt, liteRs); DBUtil.tryRelease(mysqlConn, mysqlPs, null); } } private Connection getSQLiteConnection() throws Exception { Class.forName("org.sqlite.JDBC"); return DriverManager.getConnection("jdbc:sqlite:d:/data/lagou.db"); } private Connection getMySQLConnection() throws Exception { Class.forName("org.mariadb.jdbc.Driver"); // 注意 rewriteBatchedStatements 参数！ return DriverManager.getConnection("jdbc:mariadb://127.0.0.1:3306/lagou?rewriteBatchedStatements=true", "root", "xxx"); } } class DBUtil { // 释放资源 // 注意关闭顺序 // 注意异常处理方式 public static void tryRelease(Connection conn, Statement st, ResultSet rs) { if (rs != null) { try { rs.close(); } catch (SQLException ignored) { } } if (st != null) { try { st.close(); } catch (SQLException ignored) { } } if (conn != null) { try { conn.close(); } catch (SQLException ignored) { } } } // 尝试回滚 public static void tryRollback(Connection conn) { try { if (conn != null) { conn.rollback(); } } catch (SQLException ignored) { } } }

java.sql.*;

public class DataMigrateFromSQLiteToMySQL {

// create table lagou_p1 as select * from lagou_position limit 0;

public static void main(String[] args) {

DataMigrateFromSQLiteToMySQL migrate = new DataMigrateFromSQLiteToMySQL();

migrate.doMigrate();

}

public void doMigrate() {

// 数据从哪里来？

Connection liteConn = null;

Statement liteSt = null;

ResultSet liteRs = null;

// 数据到哪里去？

Connection mysqlConn = null;

PreparedStatement mysqlPs = null;

try {

// 数据从这里来

liteConn = this.getSQLiteConnection();

liteSt = liteConn.createStatement();

liteRs = liteSt.executeQuery(“select * from lagou_position”);

// 数据到这里去

mysqlConn = this.getMySQLConnection();

mysqlPs = mysqlConn.prepareStatement(“insert into lagou_p1 values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)”);

// 关闭自动提交，也就是开启事务

mysqlConn.setAutoCommit(false);

int i = 0;

long startTime = System.currentTimeMillis(); // 计时开始

// 来吧，迁移吧！

while(liteRs.next()) {

for (int j = 1; j < 20; j++) {

mysqlPs.setObject(j, liteRs.getObject(j));

}

mysqlPs.addBatch();

// 每 10000 条，向数据库发送一次执行请求

if (i++ % 10000 == 0) {

mysqlPs.executeBatch();

}

mysqlPs.executeBatch();

mysqlConn.commit(); // 提交事务

long stopTime = System.currentTimeMillis(); // 计时结束

// 输出结果

System.out.println(“总共多少数据:” + i);

System.out.println(“一共花费 " + (stopTime - startTime) / 1000.0 + " 秒”);

} catch (Exception e) {

DBUtil.tryRollback(mysqlConn);

} finally {

DBUtil.tryRelease(liteConn, liteSt, liteRs);

DBUtil.tryRelease(mysqlConn, mysqlPs, null);

}

private Connection getSQLiteConnection() throws Exception {

Class.forName(“org.sqlite.JDBC”);

return DriverManager.getConnection(“jdbc:sqlite:d:/data/lagou.db”);

}

private Connection getMySQLConnection() throws Exception {

Class.forName(“org.mariadb.jdbc.Driver”); // 注意 rewriteBatchedStatements 参数！

return DriverManager.getConnection(“jdbc:mariadb://127.0.0.1:3306/lagou?rewriteBatchedStatements=true”, “root”, “xxx”);

}

class DBUtil {

// 释放资源

// 注意关闭顺序

// 注意异常处理方式

public static void tryRelease(Connection conn, Statement st, ResultSet rs) {

if (rs != null) {

try {

rs.close();

} catch (SQLException ignored) {

}

if (st != null) {

try {

st.close();

} catch (SQLException ignored) {

}

if (conn != null) {

try {

conn.close();

} catch (SQLException ignored) {

}

// 尝试回滚

public static void tryRollback(Connection conn) {

try {

if (conn != null) {

conn.rollback();

}

} catch (SQLException ignored) {

}

15 数据处理示例

15.1 数据清理

create table lagou_position_result as select distinct * from lagou_position where pid is not null and city is not null and position is not null and field is not null and salary_min is not null and salary_max is not null and workyear is not null and education is not null and ptype is not null and pnature is not null and advantage is not null and company_id is not null and company_short_name is not null and company_full_name is not null and company_size is not null and financestage is not null;

table lagou_position_result as

select distinct *

from lagou_position

where pid is not null

and city is not null

and position is not null

and field is not null

and salary_min is not null

and salary_max is not null

and workyear is not null

and education is not null

and ptype is not null

and pnature is not null

and advantage is not null

and company_id is not null

and company_short_name is not null

and company_full_name is not null

and company_size is not null

and financestage is not null;

15.2 表的分离

-- 源数据 select * from china_city limit 4; select * from lagou_position_bk limit 2; -- lagou_position_bk (工作信息，地区信息，分类信息，公司信息) -- 要求，创建三张表 -- lagou_city (cityid, province, city, district) # 全国的省市县信息，需要从 china_city 表中提取出来 -- lagou_company (cid, name, short_name, size, financestage) # 所有的公司表，从 lagou_position_bk 中分离出来 -- lagou_position (pid, cityid, cid, position, field, salary_min, salary_max, workyear, education, ptype, pnature, advantage, published_at, updated_at)

select * from china_city limit 4;

select * from lagou_position_bk limit 2; – lagou_position_bk (工作信息，地区信息，分类信息，公司信息)

– 要求，创建三张表

– lagou_city (cityid, province, city, district) # 全国的省市县信息，需要从 china_city 表中提取出来

– lagou_company (cid, name, short_name, size, financestage) # 所有的公司表，从 lagou_position_bk 中分离出来

– lagou_position (pid, cityid, cid, position, field, salary_min, salary_max, workyear, education, ptype, pnature, advantage, published_at, updated_at)

lagou_city 创建过程:

-- 373 市 3341 县总和 3714 select count(*) from china_city where depth=2; select count(*) from china_city where depth=3; -- 分步创建 create table lagou_city01 as select d.id, p.cityName as province, c.cityName as city, d.cityName as district from (select * from china_city where depth=3) d join china_city c on d.parentId = c.id and c.depth=2 join china_city p on c.parentId = p.id and p.depth=1; insert into lagou_city01 select c.id, p.cityName as province, c.cityName as city, null as district from (select * from china_city where depth=2) c join china_city p on c.parentId = p.id and p.depth = 1; select count(*) from lagou_city01; -- 或者使用 union 语句 create table lagou_city as select d.id, p.cityName as province, c.cityName as city, d.cityName as district from (select * from china_city where depth=3) d join china_city c on d.parentId = c.id and c.depth=2 join china_city p on c.parentId = p.id and p.depth=1 union select c.id, p.cityName as province, c.cityName as city, null as district from (select * from china_city where depth=2) c join china_city p on c.parentId = p.id and p.depth = 1;

select count(*) from china_city where depth=2;

select count(*) from china_city where depth=3;

– 分步创建

create table lagou_city01 as

select d.id, p.cityName as province, c.cityName as city, d.cityName as district from

(select * from china_city where depth=3) d

join china_city c on d.parentId = c.id and c.depth=2

join china_city p on c.parentId = p.id and p.depth=1;

insert into lagou_city01

select c.id, p.cityName as province, c.cityName as city, null as district from (select * from china_city where depth=2) c

join china_city p on c.parentId = p.id and p.depth = 1;

select count(*) from lagou_city01;

– 或者使用 union 语句

create table lagou_city as

select d.id, p.cityName as province, c.cityName as city, d.cityName as district from

(select * from china_city where depth=3) d

join china_city c on d.parentId = c.id and c.depth=2

join china_city p on c.parentId = p.id and p.depth=1

union

select c.id, p.cityName as province, c.cityName as city, null as district from (select * from china_city where depth=2) c

join china_city p on c.parentId = p.id and p.depth = 1;

lagou_company 公司分离:

drop table if exists lagou_company; create table lagou_company as select distinct t.company_id as cid, t.company_short_name as short_name, t.company_full_name as full_name, t.company_size as size, t.financestage from lagou_position_bk t;

table if exists lagou_company;

create table lagou_company as

select distinct t.company_id as cid,

t.company_short_name as short_name,

t.company_full_name as full_name,

t.company_size as size,

t.financestage

from lagou_position_bk t;

将公司、城市信息从主表分离出去:

create table lagou_position as select pid, cid as city, company_id as company, position, field, salary_min, salary_max, workyear, education, ptype, pnature, advantage, published_at, updated_at from ( -- position 表中 district 为空的数据 select p.*, c.cid from (select * from lagou_position_bk where district is null) p join lagou_city c on c.city like concat(p.city, '%') and c.district is null union all -- position 表中 district 不为空的数据 select p.*, c.cid from (select * from lagou_position_bk where district is not null) p join lagou_city c on c.city like concat(p.city, '%') and c.district like concat(p.district, '%') ) as ppp; -- 也可以分步进行。使用 union 语句虽然会简化语句，但效率会比较低 create table lagou_position as select pid, cid as city, company_id as company, position, field, salary_min, salary_max, workyear, education, ptype, pnature, advantage, published_at, updated_at from (select * from lagou_position_bk where district is null) p join lagou_city c on c.city like concat(p.city, '%') and c.district is null; insert into lagou_position select pid, cid as city, company_id as company, position, field, salary_min, salary_max, workyear, education, ptype, pnature, advantage, published_at, updated_at from (select * from lagou_position_bk where district is not null) p join lagou_city c on c.city like concat(p.city, '%') and c.district like concat(p.district, '%');

table lagou_position

select pid, cid as city, company_id as company, position, field, salary_min, salary_max, workyear, education, ptype, pnature, advantage, published_at, updated_at from

(

– position 表中 district 为空的数据

select p.*, c.cid from (select * from lagou_position_bk where district is null) p

join lagou_city c on c.city like concat(p.city, ‘%’) and c.district is null

union all

– position 表中 district 不为空的数据

select p.*, c.cid from (select * from lagou_position_bk where district is not null) p

join lagou_city c on c.city like concat(p.city, ‘%’) and c.district like concat(p.district, ‘%’)

) as ppp;

– 也可以分步进行。使用 union 语句虽然会简化语句，但效率会比较低

create table lagou_position as

select pid, cid as city, company_id as company, position, field, salary_min, salary_max, workyear, education, ptype, pnature, advantage, published_at, updated_at

from (select * from lagou_position_bk where district is null) p

join lagou_city c on c.city like concat(p.city, ‘%’) and c.district is null;

insert into lagou_position

select pid, cid as city, company_id as company, position, field, salary_min, salary_max, workyear, education, ptype, pnature, advantage, published_at, updated_at

from (select * from lagou_position_bk where district is not null) p

join lagou_city c on c.city like concat(p.city, ‘%’) and c.district like concat(p.district, ‘%’);

注意:

分表过程中、分完的表，需要添加主键或索引，否则关联查询会特别特别慢
使用 create as 语句分表会比较简单，但这个过程存在数据的复制，会比较占用硬盘存储
除了 create as 语句，也可以在基表上进行数据操作（删改）。但是注意数据的备份，当心不慎操作导致的数据永久丢失
备份迁移需要在系统停机的情况下进行

16 省市县模式数据库设计

最朴素的实现方式:

create table area_demo1 ( area_id int auto_increment primary key, sheng varchar(20) not null, shi varchar(20) not null, xian varchar(20) not null, quhao varchar(20) not null, youbian varchar(20), jingdu decimal(10, 10), weidu decimal(10, 10) ); insert into area_demo1 (sheng, shi, xian, quhao) values ('广东省', '珠海市', '斗门区', '22323'), ('广东省', '珠海市', '香洲区', '22324'), ('广东省', '珠海市', '金湾区', '22325'), ('广东省', '广州市', '天河区', '11155'), ('广东省', '广州市', '越秀区', '11156'), ('广东省', '广州市', '白云区', '11157'); select * from area_demo1 where shi='珠海市' or shi='广州市'; select distinct shi from area_demo1 where sheng='广东省'; select * from area_demo1;

table area_demo1 (

area_id int auto_increment primary key,

sheng varchar(20) not null,

shi varchar(20) not null,

xian varchar(20) not null,

quhao varchar(20) not null,

youbian varchar(20),

jingdu decimal(10, 10),

weidu decimal(10, 10)

);

insert into area_demo1 (sheng, shi, xian, quhao) values

(‘广东省’, ‘珠海市’, ‘斗门区’, ‘22323’),

(‘广东省’, ‘珠海市’, ‘香洲区’, ‘22324’),

(‘广东省’, ‘珠海市’, ‘金湾区’, ‘22325’),

(‘广东省’, ‘广州市’, ‘天河区’, ‘11155’),

(‘广东省’, ‘广州市’, ‘越秀区’, ‘11156’),

(‘广东省’, ‘广州市’, ‘白云区’, ‘11157’);

select * from area_demo1 where shi=‘珠海市’ or shi=‘广州市’;

select distinct shi from area_demo1 where sheng=‘广东省’;

select * from area_demo1;

按照范式，需要分表:

create table s_sheng (pid int auto_increment primary key, p_name varchar(20)); insert into s_sheng (p_name) values ('广东省'), ('山东省'), ('广西省'), ('湖南省'); create table s_shi (sid int auto_increment primary key, s_name varchar(20), sheng int references s_sheng(id)); insert into s_shi (s_name, sheng) values ('珠海市', 1), ('广州市', 1), ('湛江', 1), ('桂林市', 3), ('南宁市', 3), ('柳州', 3); create table s_xian (xid int auto_increment primary key, x_name varchar(20), shi int references s_shi(id), youbian varchar(20), jingdu varchar(20), weidu varchar(20)); insert into s_xian (x_name, shi) values ('斗门区', 1), ('金湾区', 1), ('香洲区', 1), ('白云区', 2), ('越秀区', 2), ('天河区', 2), ('锦绣区', 5), ('风景区', 5); select * from s_sheng; select * from s_shi; select * from s_xian; delete from s_xian where xid > 8;

table s_sheng (pid int auto_increment primary key, p_name varchar(20));

insert into s_sheng (p_name) values (‘广东省’), (‘山东省’), (‘广西省’), (‘湖南省’);

create table s_shi (sid int auto_increment primary key, s_name varchar(20), sheng int references s_sheng(id));

insert into s_shi (s_name, sheng) values

(‘珠海市’, 1), (‘广州市’, 1), (‘湛江’, 1),

(‘桂林市’, 3), (‘南宁市’, 3), (‘柳州’, 3);

create table s_xian (xid int auto_increment primary key, x_name varchar(20), shi int references s_shi(id), youbian varchar(20), jingdu varchar(20), weidu varchar(20));

insert into s_xian (x_name, shi) values

(‘斗门区’, 1), (‘金湾区’, 1), (‘香洲区’, 1),

(‘白云区’, 2), (‘越秀区’, 2), (‘天河区’, 2),

自我介绍一下，小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。

深知大多数Java工程师，想要提升技能，往往是自己摸索成长或者是报班学习，但对于培训机构动则几千的学费，着实压力不小。自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

因此收集整理了一份《2024年Java开发全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，基本涵盖了95%以上Java开发知识点，真正体系化！

由于文件比较大，这里只是将部分目录截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频，并且会持续更新！

如果你觉得这些内容对你有帮助，可以扫码获取！！（备注Java获取）

最后的话

无论是哪家公司，都很重视Spring框架技术，重视基础，所以千万别小看任何知识。面试是一个双向选择的过程，不要抱着畏惧的心态去面试，不利于自己的发挥。
同时看中的应该不止薪资，还要看你是不是真的喜欢这家公司，好了希望这篇文章对大家有帮助！

部分截图：
在这里插入图片描述

《互联网大厂面试真题解析、进阶开发核心学习笔记、全套讲解视频、实战项目源码讲义》点击传送门即可获取！

按照范式，需要分表:

table s_sheng (pid int auto_increment primary key, p_name varchar(20));

insert into s_sheng (p_name) values (‘广东省’), (‘山东省’), (‘广西省’), (‘湖南省’);

create table s_shi (sid int auto_increment primary key, s_name varchar(20), sheng int references s_sheng(id));

insert into s_shi (s_name, sheng) values

(‘珠海市’, 1), (‘广州市’, 1), (‘湛江’, 1),

(‘桂林市’, 3), (‘南宁市’, 3), (‘柳州’, 3);

create table s_xian (xid int auto_increment primary key, x_name varchar(20), shi int references s_shi(id), youbian varchar(20), jingdu varchar(20), weidu varchar(20));

insert into s_xian (x_name, shi) values

(‘斗门区’, 1), (‘金湾区’, 1), (‘香洲区’, 1),

(‘白云区’, 2), (‘越秀区’, 2), (‘天河区’, 2),

自我介绍一下，小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。

因此收集整理了一份《2024年Java开发全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。[外链图片转存中…(img-4knuhXwl-1713524092213)]

[外链图片转存中…(img-HFfUaOz1-1713524092213)]

[外链图片转存中…(img-WijOesoM-1713524092214)]

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，基本涵盖了95%以上Java开发知识点，真正体系化！

由于文件比较大，这里只是将部分目录截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频，并且会持续更新！

如果你觉得这些内容对你有帮助，可以扫码获取！！（备注Java获取）

最后的话

部分截图：
[外链图片转存中…(img-tEajyEFg-1713524092214)]

《互联网大厂面试真题解析、进阶开发核心学习笔记、全套讲解视频、实战项目源码讲义》点击传送门即可获取！

2301_79655496

关注

20
点赞
踩
26

收藏

觉得还不错? 一键收藏
0
评论
使用mysql以及连接数据库

变量分类：局部变量，使用在存储过程或函数中，这样的变量使用 declare 进行声明，使用 set 进行赋值。作用域是过程中。用户变量，定义 set @xxx = 333;需要用 @ 符号进行修饰系统变量，系统自带的，内建的变量可以通过命令行的方式进行设置 mysqld –default_storage_engine=MyISAM可以在 my.ini 配置文件中进行配置 default_storage_engine=MyISAM可以通过 set 的方式进行动态设置全局的(global)
复制链接

扫一扫