[系统运维]mysql常用

注意

• mysql在windows上不区分大小写，linux上区分大小写，统一不要小写
• mysql不区分单双引号作用
• 数据库注释使用-- (后面有个空格)
• SQL语句结束使用
• mysql中name，value等内部关键字使用时加上两个单引号即可

登录

# 本地
mysql -u root -p
# 全称,-P端口
mysql -h ip -P port -u root -p

数据库

注意

创建数据库

-- 创建数据库，COLLATE 排序规则
CREATE DATABASE IF NOT EXISTS xcrj_db DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci;
-- 查看创建结果
show databases;

删除数据库

DROP DATABASE IF EXISTS xcrj_db;

查看数据库

show databases;

使用数据库

use xcrj_db;

表

创建表

DROP TABLE IF EXISTS `xcrj_table`;
CREATE TABLE `xcrj_table`  (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  `item_id` bigint(20) UNSIGNED NULL DEFAULT NULL COMMENT '项目id',
  `value` int(11) NULL DEFAULT NULL COMMENT '值',
  `description` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '描述',
  `score` double DEFAULT NULL COMMENT '分数',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  INDEX idx_name (`description`(20)),
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic COMMENT = "xcrj表";

删除表

DROP table xcrj_table;

修改表-修改表名

ALTER  TABLE xcrj_table RENAME TO xcrj_new_table;

修改表-增加字段-末尾

ALTER TABLE xcrj_table ADD `xcrj_field` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '名称';

修改表-增加字段-开头

ALTER TABLE xcrj_table ADD `xcrj_field2` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '名称' FIRST;

修改表-增加字段-指定字段之后

ALTER TABLE xcrj_table ADD `xcrj_field3` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '名称' AFTER `name2`;

修改表-删除字段

ALTER TABLE xcrj_table DROP `xcrj_field3`;

修改表-修改字段名和字段类型

-- `name`旧字段名，`new_name_id`新字段名
ALTER  TABLE xcrj_table CHANGE `xcrj_field` `xcrj_new_field` int(11) NULL DEFAULT NULL COMMENT 'xcrj字段';

修改表-修改字段类型

ALTER TABLE xcrj_table MODIFY `xcrj_field` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'xcrj字段' ;

修改表-增加索引-普通索引

ALTER TABLE xcrj_table ADD INDEX idx_name(`name`);

修改表-增加索引-唯一索引

ALTER TABLE xcrj_table ADD UNIQUE uk_name(`name`);

修改表-增加索引-主键索引

ALTER TABLE xcrj_table ADD PRIMARY KEY pk_id(`id`);

修改表-增加主键

ALTER  TABLE xcrj_table RENAME TO xcrj_new_table;

查看指定表

-- 使用数据库
use xcrj_db;
-- create语句，DDL语句
SHOW create table xcrj_table;
-- 表描述
desc xcrj_table;
-- 表详情
explain xcrj_table;
-- 表状态
show table status like "xcrj_table" \G;

查看所有表

show tables;

索引

注意

• 索引为char，varchar类型时，可以设定索引长度

创建普通索引

-- varchar 长度=20时，索引区分度=90%
create index idx_xcrj_field on xcrj_table(`xcrj_field`(20));
-- 或
ALTER TABLE xcrj_table ADD INDEX idx_xcrj_field (`xcrj_field`(20));

创建唯一索引

create unique index uk_xcrj_field on xcrj_table(xcrj_field(20));
-- 或
ALTER TABLE xcrj_table ADD UNIQUE uk_xcrj_field(`xcrj_field`(20));

创建主键索引

-- 创建表后创建主键索引
ALTER TABLE xcrj_table ADD PRIMARY KEY pk_id(`id`);
-- 创建表时创建主键索引
DROP TABLE IF EXISTS `xcrj_table`;
CREATE TABLE `xcrj_table`  (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  `description` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '描述',
  INDEX idx_name (`description`(20)),
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic COMMENT = "xcrj表";

创建复合索引

create index idx_field1_field2 on xcrj_table(xcrj_field1(20),xcrj_field2(20));
-- 或
ALTER TABLE xcrj_table ADD INDEX idx_field1_field2( `xcrj_field1`, `xcrj_field2` );

创建全文索引

create FULLTEXT INDEX full_description on xcrj_table(description);
-- 或
ALTER TABLE xcrj_table ADD FULLTEXT INDEX full_description( `description` );

删除索引

drop index idx_xcrj_field on xcrj_table;

查看索引

-- 详细索引
show index from xcrj_table;
-- 表情况，包括索引
explain xcrj_table;

视图

创建视图

create view xcrj_view as select item_id,value from xcrj_table;

删除视图

drop view xcrj_view;

修改视图

alter view xcrj_view as select value,description from xcrj_table;

查看视图

-- create语句，DDL语句
show create view xcrj_view;
-- 视图描述
desc xcrj_view;
-- 视图详情
explain xcrj_view;
-- 视图状态
show table status like "xcrj_view" \G;
-- 视图信息
use information_schema;
select * from views where table_name='xcrj_view';

查询视图

-- 类似有查询表
select * from xcrj_view;

CRUD

注意

• delete加上order by limit的原因，防止误操作删除全部数据
• select语句执行顺序：where》group having》order by》聚集函数
• for循环select * from xcrj_table where id=?，可以用select * from xcrj_table id in (1,2,3)代替
• select where any some 和 all需要前面跟上比较运算符，例如>=any，>=some，>=all
• 字段值为Null要单独考虑

插入所有列

insert into xcrj_table value(v1-1,v2-1,v3-1);

批量插入所有列

insert into xcrj_table values(v1-1,v2-1,v3-1),(v1-2,v2-2,v3-2);

批量插入指定列

insert into xcrj_table(column1,column2,column3) values(v1-1,v2-1,v3-1),(v1-2,v2-2,v3-2);

插入查询的结果

insert into xcrj_table(c1,c2,c3) select (c1,c2,c3) from table where condition;

删除

delete from xcrj_table where `name`='xcrj';

删除-限制数量

-- 加上order by limit的原因，防止误操作删除全部数据
delete from xcrj_table where `name`='xcrj' order by id limit 2;

修改

update xcrj set `name`='xcrj007' where `name`='xcrj';

查询语法

select [all|distinct|sum(column)] from table where condition group by column having condition order by column [desc|asc];

简单查询

select * from xcrj_table where `name`='xcrj';
select `value`,`name` from xcrj_table where `name`='xcrj';

union-并集

SELECT * FROM xcrj_table1 UNION SELECT * FROM xcrj_table2;

in

SELECT * FROM xcrj_table WHERE id IN (1,2,3);
SELECT * FROM xcrj_table WHERE id in (select id from xcrj_table1);

any

-- 与子查询返回的任何值比较，为true ，返回true，where true
SELECT * FROM xcrj_table WHERE id >=any (select id from xcrj_table1 where id in (1,2,9));
-- 如果子查询结果存在NULL，where Null
SELECT * FROM xcrj_table WHERE id >=any (select id from xcrj_table1);
-- 如果子查询结果存在NULL，where Null
SELECT * FROM xcrj_table WHERE id >=any (select id from xcrj_table1);

some

-- any的别称
-- 与子查询返回的任何值比较，为true ，返回true，where true
SELECT * FROM xcrj_table WHERE id >=some(select id from xcrj_table1 where id in (1,2,9));

all

-- 与子查询返回的所有比较，为true ，返回true，where true
SELECT * FROM xcrj_table WHERE id >=all(select id from xcrj_table1 where id in (1,2,9));

-- 如果子查询结果为空，都为TRUE，where true
SELECT * FROM xcrj_table WHERE id >=all(select id from xcrj_table1);

-- 如果子查询结果存在NULL，整个查询结果为Empty
SELECT * FROM xcrj_table WHERE id >=all(select id from xcrj_table1);