mysql 修改用户某个表只有查询权限 mysql修改数据表中的数据

标题

• 库的相关操作

• 创建库
• 修改库
• 删除库
• 修改mysql默认字符集的方法

• 表的相关操作

• 修改表的字符集

• 修改表结构

• mysql修改表、字段、库的字符集
• 复制表
• SQL的约束作用
• 创建表时添加约束
• 修改表时添加或删除约束
• 表中添加删除表级约束、添加表级约束
• 修改表(字段)注释、表名
• 修改列
• 修改列名
• 添加列
• 在指定位置插入新字段
• 调整字段顺序：
• 删除列
• 批量增修
• 添加索引
• 修改外键
• 删除索引

• MySQL脚本，支持重复执行

• 自增长列/标识列

• 创建自定义函数
• 删除函数

这里面考虑到运维那边运行脚本使用的话，你就得支持让它不报错，得让他可重复执行，下面这些只是开发时候执行一次时候使用，这种写法放到最后，java开发不需要太过关心，了解即可

库的相关操作

创建库

create database 【if not exists】 库名【 character set 字符集名】;

修改库

MySQL修改数据库名称比较麻烦，不支持直接修改，需要通过其它方式间接达到修改数据库名称的目的。
在 MySQL 5.1.23 之前的旧版本中，我们可以使用 RENAME DATABASE 来重命名数据库，但此后版本，因为安全考虑，删掉了这一条命令。

alter database 库名 character set 字符集名;-- 不是修改库名，而是修改字符集

如何修改MySQL数据库名称

三种方式修改 MySQL 数据库名

删除库

drop database 【if exists】 库名;

修改mysql默认字符集的方法

(1) 最简单的修改方法，就是修改mysql的my.ini文件中的字符集键值，
如 default-character-set = utf8
character_set_server = utf8
修改完后，重启mysql的服务，service mysql restart
使用 mysql> SHOW VARIABLES LIKE ‘character%’;查看，发现数据库编码均已改成utf8
(2) 还有一种修改mysql默认字符集的方法，就是使用mysql的命令

mysql> SET character_set_client = utf8 ;  
mysql> SET character_set_connection = utf8 ;  
mysql> SET character_set_database = utf8 ;  
mysql> SET character_set_results = utf8 ;   
mysql> SET character_set_server = utf8 ;  
mysql> SET collation_connection = utf8 ; 
mysql> SET collation_database = utf8 ;  
mysql> SET collation_server = utf8 ;

一般就算设置了表的mysql默认字符集为utf8并且通过UTF-8编码发送查询，你会发现存入数据库的仍然是乱码。问题就出在这个connection连接层上。解决方法是在发送查询前执行一下下面这句：

SET NAMES 'utf8';

它相当于下面的三句指令：

SET character_set_client = utf8;  
SET character_set_results = utf8;   
SET character_set_connection = utf8;

表的相关操作

修改表的字符集

/*
	修改表结构:修改表的字符集
	格式:
		alter table 表名 character set 新的字符集;
	注意:
			表中如果有数据,修改字符集,容易产生乱码utf8--gbk
*/
-- 把fenlei表的字符集修改为gbk
ALTER TABLE fenlei CHARACTER SET gbk;

修改表结构

mysql修改表、字段、库的字符集

# 修改数据库字符集
ALTER DATABASE db_name DEFAULT CHARACTER SET character_name [COLLATE ...];
 
# 把表默认的字符集和所有字符列（CHAR,VARCHAR,TEXT）改为新的字符集：
ALTER TABLE tbl_name CONVERT TO CHARACTER SET character_name [COLLATE ...]
# 如：ALTER TABLE logtest CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
 
# 只是修改表的默认字符集：
ALTER TABLE tbl_name DEFAULT CHARACTER SET character_name [COLLATE...];
# 如：ALTER TABLE logtest DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
 
# 修改字段的字符集：
ALTER TABLE tbl_name CHANGE c_name c_name CHARACTER SET character_name [COLLATE ...];
# 如：ALTER TABLE logtest CHANGE title title VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci;
 
# 查看数据库编码：
SHOW CREATE DATABASE db_name;
 
# 查看表编码：
SHOW CREATE TABLE tbl_name;
 
# 查看字段编码：
SHOW FULL COLUMNS FROM tbl_name;

复制表

#复制表的结构
create table 表名 like 旧表;
#复制表的结构+数据
create table 表名  select 查询列表  from 旧表 【where 筛选】;

SQL的约束作用

创建表时添加约束

create table 表名(
	字段名 字段类型 not null,#非空
	字段名 字段类型 primary key,#主键
	字段名 字段类型 unique,#唯一
	字段名 字段类型 default 值,#默认
	constraint 约束名 foreign key(字段名) references 主表(被引用列)
)
#设置通用的写法，即，外键约束用标记约束，其他的都用列级约束
#约束名建议写成：自己起的名字_本表名_引用表名

修改表时添加或删除约束

1、非空
添加非空
alter table 表名 modify column 字段名 字段类型 not null;
删除非空
alter table 表名 modify column 字段名 字段类型【null】 ;
2、默认
添加默认
alter table 表名 modify column 字段名 字段类型 default 值;
删除默认
alter table 表名 modify column 字段名 字段类型 ;
3、主键
添加主键
alter table 表名 add【 constraint 约束名】 primary key(字段名);
删除主键
alter table 表名 drop primary key;
4、唯一
添加唯一
alter table 表名 add【 constraint 约束名】 unique(字段名);
删除唯一
alter table 表名 drop index 索引名;
5、外键
添加外键
alter table 表名 add【 constraint 约束名】 foreign key(字段名) references 主表(被引用列);
删除外键
alter table 表名 drop foreign key 约束名;

表中添加删除表级约束、添加表级约束

1. 删除表级约束
alter table 表名 drop foreign key 外码名;
2. 添加表级约束
alter table 表名 add CONSTRAINT 约束名 foreign key (外码字段名) references 被参照表的表名(被参照主码名);
如果添加约束的时候失败，可能的原因是：外码字段的值   与   被参照主码的值    不一致。
解决办法：1. 让有冲突的值一致    2. 清空两张表中任意一个表。

修改表(字段)注释、表名

修改表注释：
ALTER TABLE tb_user COMMENT '用户信息表2';
修改字段注释：（同时修改字段类型、属性、约束等）
ALTER  TABLE 表名 MODIFY [COLUMN] 字段名 新数据类型 新类型长度  新默认值  新注释;
ALTER TABLE tb_user MODIFY COLUMN name VARCHAR(30) NOT NULL COMMENT '姓名2';
注意：修改时要把该字段的完整定义写上，如字段类型、属性等。千万不要因为只是为了修改一个注释，而把该字段定义的其它属性给覆盖掉了。  COLUMN关键字可以省略不写
修改字段名：
ALTER  TABLE 表名 CHANGE [column] 旧字段名 新字段名 新数据类型;
ALTER TABLE tb_user CHANGE 列名 新起列名 VARCHAR(30) COMMENT '刷卡结果';
alter  table table1 change column1 column2 decimal(10,1) DEFAULT NULL COMMENT '注释'
修改表名
ALTER TABLE 旧表名 RENAME TO 新表名 ;

修改列

/*
	修改表结构:修改列的类型长度及约束,使用关键字modify(改变,修改)
	格式:
		alter table 表名 modify 字段名 新的数据类型(长度) [约束];
	注意:
			列中如果有数据,修改数据类型可能会报错varchar-->int
*/
-- 修改category表中age字段,类型修改为varchar(2),去掉非空约束
ALTER TABLE category MODIFY age VARCHAR(2);

修改列名

/*
	修改表结构:修改列名(同时可以把列的数据类型和约束一起进行修改),使用关键字change(改变)
	格式:
		alter table 表名 change 旧列名 新列名 数据类型(长度) [约束];
*/
-- 修改category表中age字段,字段名修改descr,类型varchar(50),添加非空约束
ALTER TABLE category CHANGE age descr VARCHAR(50) NOT NULL;

添加列

/*	修改表结构:表创建好之后,对表的结构进行修改(修改字段,添加字段,删除字段...)
	格式:
		alter(修改) table(表) 表名...;
	修改表结构:添加列,使用关键字add(添加)
	格式:
		alter table 表名 add 字段名 数据类型(长度) [约束];
*/
-- 给category表中添加一个字段age,类型int,添加一个非空约束
ALTER TABLE category ADD age INT NOT NULL;

在指定位置插入新字段

ALTER TABLE 表名 ADD [COLUMN] 字段名 字段类型 是否可为空 COMMENT '注释' AFTER 指定某字段 ;
-- 在name字段后面添加erp字段(注：不能使用before关键字)
alter table table_name add column erp varchar(50) not null comment 'erp账号' after name ;

调整字段顺序：

alter table 表名
change 字段名 新字段名 字段类型 默认值 after 字段名(跳到哪个字段之后)

删除列

/*
	修改表结构:删除列,使用关键字drop
	格式:
		alter table 表名 drop 列名;
*/
-- 删除category表中descr字段
ALTER TABLE category DROP descr;
-- 删除外键
ALTER TABLE articles DROP FOREIGN KEY articles_ibfk_1;

批量增修

批量插入：
alter table 表名 add (字段1 类型(长度),字段2 类型(长度),字段3 类型(长度));
批量修改：
alter table 表 change 修改前字段名  修改后字段名称 int(11) not null,
change 修改前字段名  修改后字段名称 int(11) not null,
change 修改前字段名  修改后字段名称 int(11) not null

批量删除：

添加索引

mysql添加索引(5种)

添加PRIMARY KEY（主键索引）
ALTER TABLE table_name ADD PRIMARY KEY ( 列名) 
添加UNIQUE(唯一索引)
ALTER TABLE table_name ADD UNIQUE (列名) 
添加INDEX(普通索引)
ALTER TABLE table_name ADD INDEX index_name ( 列名) 
添加FULLTEXT(全文索引)
ALTER TABLE table_name ADD FULLTEXT ( 列名) 
添加多列索引
ALTER TABLE table_name ADD INDEX index_name ( 列名1, 列名2, 列名3 )

修改外键

语法：
alter table 从表 add [constraint][外键名称] foreign key (从表外键字段名) references 主表 (主表的主键);
[外键名称]用于删除外键约束的，一般建议“_fk”结尾
alter table 从表 drop foreign key 外键名称

删除索引

删除索引可以使用ALTER TABLE或DROP INDEX语句来实现。DROP INDEX可以在ALTER TABLE内部作为一条语句处理，其格式如下：

mysql>drop index index_name on table_name ;

mysql>alter table table_name drop index index_name ;

mysql>alter table table_name drop primary key ;

MySQL脚本，支持重复执行

MySQL脚本
一、建表删表
前言：以下脚本语句，使用前建议先进行自测一遍，如有错误及时提醒更正

（一） 新增表
**新增表的脚本可以从Navicat中转储导出，但是要更改下 建表语句 为 ：CREATE TABLE IF NOT EXISTS **

1、模板

create table if not exists 数据库名.表名 (具体的建表语句
) engine = 存储引擎 default charset = 字符集 collate = 校对集 comment = '表注释';

2、参考样例

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE  IF NOT EXISTS `crr_policy_index`  (
    `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '主键自增',
    `index_code` varchar(32)   NOT NULL  DEFAULT '' COMMENT '指标唯一编码',
    `policy_id` int(10)   COMMENT '策略ID',
    `index_group` varchar(32)   NOT NULL  DEFAULT ''  COMMENT '分类',
    `version` int(4) NOT NULL DEFAULT 0 COMMENT '版本',
    `index_desc` varchar(128)    DEFAULT ''  COMMENT '描述',
    `index_name` varchar(32)   NOT NULL  DEFAULT ''  COMMENT '指标名称',
    `type` varchar(32)   NOT NULL  DEFAULT '' COMMENT '类型',
    `subject_labels` varchar(32)    DEFAULT '' COMMENT '主体名称',
    `expressions` varchar(128)  DEFAULT '' COMMENT '表达式',
    `options` longtext COMMENT '下拉框内容',
    `threshold_sign` varchar(32)    DEFAULT '' COMMENT '阈值符号',
    `threshold` varchar(32) NOT NULL  DEFAULT '' COMMENT '阈值',
    `modify_time` datetime(0)   DEFAULT now() COMMENT '修改时间',
    `operator_code` varchar(32)  DEFAULT '' COMMENT '操作人',
    PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB AUTO_INCREMENT = 17 CHARACTER SET = utf8 COLLATE = utf8_general_ci comment = '指标策略表' ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

（二）删除表
1、模板

drop table if exists 数据库名.表名;

2、参考样例

drop table if exists crr_policy_index;

二、表字段更改
（一）表字段新增
1、模板

set @v_count = 0;
select count(*) into @v_count from information_schema.columns where TABLE_SCHEMA='数据库名' and TABLE_NAME='表名' and column_name = '字段名';
set @sql = if(@v_count = 0,"增加字段语句","select '表名.字段名 is OK.'");
prepare stmt from @sql;
execute stmt;

2、参考样例

set @v_count = 0;
select count(*) into @v_count from information_schema.columns where TABLE_SCHEMA='toolsforcreatetable' and TABLE_NAME='crr_policy_index' and column_name = 'process_instance';
set @sql = if(@v_count = 0,"alter table crr_policy_index add column process_instance varchar(255) NOT NULL COMMENT '流程实例id';","select '表名.字段名 is OK.'");
prepare stmt from @sql;
execute stmt;

（二）表字段删除或修改
1、模板

set @v_count = 0;
select count(*) into @v_count from information_schema.columns where TABLE_SCHEMA='数据库名' and TABLE_NAME='表名' and column_name = '字段名';
set @sql = if(@v_count = 1,"删除或修改字段语句","select '表名.字段名 is deleted.'");
prepare stmt from @sql;

2、修改字段样例

set @v_count = 0;
select count(*) into @v_count from information_schema.columns where TABLE_SCHEMA='toolsforcreatetable' and TABLE_NAME='crr_policy_index' and column_name = 'process_instance';
set @sql = if(@v_count = 1,"alter table crr_policy_index modify column process_instance varchar(255) NOT NULL COMMENT '流程实例id1';","select 'crr_policy_index.process_instance is deleted.'");
prepare stmt from @sql;
execute stmt;

3、删除字段样例

set @v_count = 0;
select count(*) into @v_count from information_schema.columns where TABLE_SCHEMA='toolsforcreatetable' and TABLE_NAME='crr_policy_index' and column_name = 'process_instance';
set @sql = if(@v_count = 1,"alter table crr_policy_index drop column process_instance ;","select 'crr_policy_index.process_instance is deleted.'");
prepare stmt from @sql;
execute stmt;
-- DEALLOCATE PREPARE stmt; 释放

三、主键新增与删除
（一）新增主键
1、模板

set @v_count = 0;
select count(*) into @v_count from information_schema.table_constraints where TABLE_SCHEMA='数据库名' and TABLE_NAME='表名' and CONSTRAINT_NAME = 'PRIMARY';
set @sql = if(@v_count = 0,"alter table 表名 add primary key (字段列表)","select '表名 PRIMARY KEY is OK.'");
prepare stmt from @sql;
execute stmt;

2、参考样例

set @v_count = 0;
select count(*) into @v_count from information_schema.table_constraints where TABLE_SCHEMA='toolsforcreatetable' and TABLE_NAME='crr_policy_index' and CONSTRAINT_NAME = 'PRIMARY';
set @sql = if(@v_count = 0,"alter table crr_policy_index add primary key (id,index_code)","select 'crr_policy_index PRIMARY KEY is OK.'");
prepare stmt from @sql;
execute stmt;

（二）删除主键
1、模板

set @v_count = 0;
select count(*) into @v_count from information_schema.table_constraints where TABLE_SCHEMA='数据库名' and TABLE_NAME='表名' and CONSTRAINT_NAME = 'PRIMARY';
set @sql = if(@v_count = 1,"alter table 表名 drop primary key ","select '表名 PRIMARY KEY is deleted.'");
prepare stmt from @sql;
execute stmt;

2、参考样例

set @v_count = 0;
select count(*) into @v_count from information_schema.table_constraints where TABLE_SCHEMA='toolsforcreatetable' and TABLE_NAME='crr_policy_index' and CONSTRAINT_NAME = 'PRIMARY';
set @sql = if(@v_count = 1,"alter table crr_policy_index drop primary key ","select '表名 PRIMARY KEY is deleted.'");
prepare stmt from @sql;
execute stmt;

四、索引
（一）新增索引
推荐普通索引名的格式：idx_每列首字母（如果索引字段只有一个，建议索引名为 idx_表名_列名）,如：idx_i_t, idx_index_group

推荐索引名的格式：uk_每列首字母（如果索引字段只有一个，建议索引名为 uk_表名_列名）,如：uk_i_t, uk_index_group

1、普通索引
（1）模板

set @v_count = 0;
select count(*) into @v_count from information_schema.statistics where TABLE_SCHEMA='数据库名' and TABLE_NAME='表名' and INDEX_NAME = '索引名';
set @sql = if(@v_count = 0,"create index 索引名 on 表名(字段列表)","select '表名 索引名 is OK.'");
prepare stmt from @sql;
execute stmt;

（2）参考样例

set @v_count = 0;
select count(*) into @v_count from information_schema.statistics where TABLE_SCHEMA='toolsforcreatetable' and TABLE_NAME='crr_policy_index' and INDEX_NAME = 'idx_index_group';
set @sql = if(@v_count = 0,"create index idx_index_group1 on crr_policy_index(index_group)","select 'crr_policy_index idx_index_group is OK.'");
prepare stmt from @sql;
execute stmt;

2、唯一索引 加unique
（1）模板

set @v_count = 0;
select count(*) into @v_count from information_schema.statistics where TABLE_SCHEMA='数据库名' and TABLE_NAME='表名' and INDEX_NAME = '索引名';
set @sql = if(@v_count = 0,"create unique index 索引名 on 表名(字段列表)","select '表名 索引名 is OK.'");
prepare stmt from @sql;
execute stmt;

（2）参考样例

set @v_count = 0;
select count(*) into @v_count from information_schema.statistics where TABLE_SCHEMA='toolsforcreatetable' and TABLE_NAME='crr_policy_index' and INDEX_NAME = 'uk_i_t';
set @sql = if(@v_count = 0,"create unique index uk_i_t on crr_policy_index(index_group,type)","select 'crr_policy_index uk_i_t is OK.'");
prepare stmt from @sql;
execute stmt;

（二）修改索引
注意: 在MySQL中并没有提供修改索引的直接指令，一般情况下，我们需要先删除掉原索引，再根据需要创建一个同名的索引，从而变相地实现修改索引操作

（三）删除索引
注意这里是@v_count >= 1（多字段索引时）

（1）模板

set @v_count = 0;
select count(*) into @v_count from information_schema.statistics where TABLE_SCHEMA='数据库名' and TABLE_NAME='表名' and INDEX_NAME = '索引名';
set @sql = if(@v_count >= 1,"alter table 表名 drop index 索引名","select '表名 索引名 is deleted.'");
prepare stmt from @sql;
execute stmt;

（2）参考样例

set @v_count = 0;
select count(*) into @v_count from information_schema.statistics where TABLE_SCHEMA='toolsforcreatetable' and TABLE_NAME='crr_policy_index' and INDEX_NAME = 'uk_i_t';
set @sql = if(@v_count >= 1,"alter table crr_policy_index drop index uk_i_t","select 'crr_policy_index uk_i_t is deleted.'");
prepare stmt from @sql;
execute stmt;

五、表数据增删改
（一）表添加数据
方式一: 先查后加
（1）模板

set @v_count = 0;
select count(*) into @v_count from 表名 where 主键条件;
set @sql = if(@v_count = 0, "insert into 表名(列名) values(列名);", "select '数据已存在.'");
prepare stmt from @sql;
execute stmt;
commit;

（2）参考样例

set @v_count = 0;
select count(*) into @v_count from 表名 where 主键条件;
set @sql = if(@v_count = 0, "INSERT INTO crr_policy_index(id, index_code, policy_id, index_group, version, index_desc, index_name, type, subject_labels, expressions, options, threshold_sign, threshold, modify_time, operator_code) VALUES (1, NULL, NULL, '1', 0, NULL, NULL, '2', NULL, NULL, NULL, NULL, NULL, 'current_timestamp()', NULL);", "select '数据已存在.'");
prepare stmt from @sql;
execute stmt;
commit;

方式二：先删后增
（1）模板

delete from 表名 where 主键条件;
insert into 表名(列名) values (列名);

（2）参考样例

delete from crr_policy_index where id = 1;

INSERT INTO crr_policy_index(id, index_code, policy_id, index_group, version, index_desc, 
index_name, type, subject_labels, expressions, options,
 threshold_sign, threshold, modify_time, operator_code) VALUES (1, NULL, NULL, '1', 0, NULL, NULL, '2',
  NULL, NULL, NULL, NULL, NULL, 'current_timestamp()', NULL);

（二）表删除数据
（1）模板

set @v_count = 0;
select count(*) into @v_count from 表名 where 主键条件;
set @sql = if(@v_count = 1, "delete 语句", "select '数据已删除.'");
prepare stmt from @sql;
execute stmt;
commit;

（2）参考样例

set @v_count = 0;
select count(*) into @v_count from crr_policy_index where id = 1;
set @sql = if(@v_count = 1, "delete from crr_policy_index where id = 1;", "select '数据已删除.'");
prepare stmt from @sql;
execute stmt;
commit;

自增长列/标识列

如果要更改步长

set auto_increment_increment=值;

语法

#一、创建表时设置自增长列
create table 表(
	字段名 字段类型 约束 auto_increment
)
 
#二、修改表时设置自增长列
alter table 表 modify column 字段名 字段类型 约束 auto_increment
 
#三、删除自增长列
alter table 表 modify column 字段名 字段类型 约束

创建自定义函数

CREATE FUNCTION `underlineToCamel` (
	paramString VARCHAR ( 200 )) RETURNS VARCHAR ( 200 ) CHARSET utf8 DETERMINISTIC BEGIN
	SET paramString = LOWER( paramString );
	SET paramString = REPLACE ( paramString, '_a', 'A' );
SET paramString = REPLACE ( paramString, '_z', 'Z' );
	SET paramString = REPLACE ( paramString, '_', '' );
	RETURN paramString;
END

删除函数

drop function underlineToCamel;