程序猿必备技能之MySQL基础篇

-- 创建用户
-- username:用户名，ip:该用户访问ip，password：密码，newPassword:新密码
create user 'username'@'ip' identified by 'password';
-- 删除用户
drop user 'username'@'ip';
-- 修改用户
rename user 'username'@'ip'; to 'newUserName'@'ip';;
-- 修改密码
set password for 'username'@'ip' = Password('newPassword')

-- privilege:授予的权限，dbName:数据库名，tableName：表名
-- 查看权限
-- @ip表示用户只能在当前ip下才能访问数据库，它支持通配%(表示任意) 可以表示为@%（任意ip）
show grants for 'username'@'ip'
-- 授权
-- password表示用户密码，flush privileges：表示刷新权限，
grant privilege  on dbName.tableName to   'username'@'ip' identified by "password" flush privileges；
-- 取消权限
revoke privilege on dbName.tableName from 'username'@'ip'

all privileges  -- 除grant外的所有权限
select          -- 仅查权限
select,insert   -- 查和插入权限
usage                   -- 无访问权限
alter                   -- 使用alter table
alter routine           -- 使用alter procedure和drop procedure
create                  -- 使用create table
create routine          -- 使用create procedure
create temporary tables -- 使用create temporary tables
create user             -- 使用create user、drop user、rename user和revoke  all privileges
create view             -- 使用create view
delete                  -- 使用delete
drop                    -- 使用drop table
execute                 -- 使用call和存储过程
file                    -- 使用select into outfile 和 load data infile
grant option            -- 使用grant 和 revoke
index                   -- 使用index
insert                  -- 使用insert
lock tables             -- 使用lock table
process                 -- 使用show full processlist
select                  -- 使用select
show databases          -- 使用show databases
show view               -- 使用show view
update                  -- 使用update
reload                  -- 使用flush
shutdown                -- 使用mysqladmin shutdown(关闭MySQL)
…………

-- 创建数据库dbName
create database dbName default charset utf8 collate utf8_general_ci;
-- 使用数据库
use dbName;
drop database dbName;
-- 创建表
-- column_name列名称，column_type列类型
create table if not exists tableName (column_name column_type);
-- 删除表
drop table tableNamea;

-- 添加列
alter table tableName add column_name column_type;
-- 添加实例
ALTER TABLE `user_info` ADD COLUMN `id` VARCHAR (11) DEFAULT NULL COMMENT '平台ID' AFTER `id_number`;
-- 删除列
alter table tableName drop column column_name;
-- 修改列
alter table tableName modify column column_name column_type;
alter table tableName change old_column_name new_column_name column_type;
-- 添加主键
alter table tableName add primary key(column_name);
-- 删除主键
alter table tableName drop primary key;
alter table tableName  modify  column_name int, drop primary key;
-- 添加外键，stable从表名称,fk_name外键名称，fk外键，mtable主表名称，pk主键字段
alter table stable add constraint fk_name（如：FK_从表_主表） foreign key stable(fk) references mtable(pk);
-- 删除外键
alter table tableName drop foreign key fk_name
-- 修改默认值,default_value默认值
alter table tableName alter column_name set default default_value;
-- 删除默认值
alter table tableName alter column_name drop default;

-- 创建索引,indexName索引名称
create index indexName ON tableName(column_name(length));
-- 删除索引
drop index [indexName] ON tableName;
-- 创建唯一索引
create unique index indexName ON tableName(column_name(length));

-- 添加主键,column_list主键列表,因为主键可以是多个
alter table tableName add primary key (column_list)
-- 创建唯一索引
alter table tableName add unique (column_list)
-- 修改表结构，添加索引（普通索引）
alter table tableName add index indexName(column_list);
-- 添加全文索引
alter table tableName add fulltext indexName(column_list);
-- 删除索引
alter table tableName drop index indexName;
-- 删除主键
alter table tableName drop primary key;

-- 重置数据库表自增序列,id为主键
alter table tableName drop id;
alter table tableName add id int unsigned not null auto_increment first,add primary key (id);
-- 指定自增序列开始值
alter table tableName auto_increment = 100;

-- 插入一条
insert into tableName (field1,field2,...fieldN) values (value1,value2,...valueN);
-- 插入多条
insert into tableName (field1,field2,...fieldN) values (value1,value2,...valueN),(value1,value2,...valueN),(……);
-- 全值插入
insert into tableName (value1,value2,...valueN);
-- 基于已经存在的表插入数据
insert into tableName1 (field1,field2...) select (field2,field2...) from tableName2

-- 删除表数据， []表示可选项，condition表示条件
delete from tableName [[where condition1 [and [or]] condition2.....];
-- 清空表所有数据，保留表结构
truncate table tableName;
-- 删除表，表结构、数据全部删除，同时释放磁盘空间，也就是表彻底从当前数据库消失了
drop table tableName;

-- n数字是检索的行数，m数字是指从第m行开始，也就是查询的数据从m行开始计算
select field1, field2,...fieldN from tableName [[where condition1 [and [or]] condition2.....] [limit n][offset m];
-- 模糊查询语句语法
select field1, field2,...fieldN from tableName where field1 like condition1 [and [or]] condition2.....
-- 查询合并[all | distinct] 条件可选，默认union具有distinct去重功能
select field1, field2,...fieldN from tableName1 [where conditions]
union [ALL | distinct]
select field1, field2,...fieldN from tableName2 [where conditions];
-- 排序查询语句语法,order by 默认asc升序
select field1, field2,...fieldN from tableName1, tableName2... order by field1, [field2...] [asc [desc]]
-- 分组查询,这里需要注意，where与having的区别,where 分组前过滤，having分组后过滤，分组查询中，条件中有聚合函数必须使用having
-- where/having/function 在from后面的执行顺序：where>function>having
select column_name, function(column_name) from tableName
[where conditions]
group by column_name
[having [function_conditions|simple_conditions]];
-- 连接查询
select [distinct] field1, field2,...fieldN from left_tableName
[inner|left|right] join right_tableName on conditions [and|or conditions]
[where conditions]
[group by column_name]
[having [function_conditions|simple_conditions]]
[order by field1, [field2...] [asc [desc]]]
[limit n][offset m]

-- 更新数据,new-value新值
update tableName set field1=new-value1, field2=new-value2
[where conditions]
-- 关联更新
update left_tableName
[inner|left|right] join right_tableName on conditions
set field1=new-value1, field2=new-value2,……
[where conditions]
-- 替换某个字段中的字符
update tableName set field=replace(field, 'old-string', 'new-string')
[where conditions]

-- 显示开启事务，两种开启方式是等价的
begin/start transaction;
-- 显示提交事务，两种提交方式是等价的
commit/commit work;
-- 事务回滚，回滚结束用户的事务，并撤销正在进行未提交的修改
rollback/rollback work;
-- 创建事务保存点，一个事务可以有多个保存点，保存点主要用于事务内部回滚
savepoint idenfier;
-- 删除事务保存点
release savepoint idenfier;
-- 将事务回滚到某一个保存点
rollback to identifier;
-- 查看设置事务隔离级别,隔离级别有read uncommit、read commit、repeatable read、serializable
select @@global.tx_isolation, @@session.tx_isolation;
-- 设置当前会话事务隔离级别
set session transaction isolation level REPEATABLE READ;
-- 设置全局事务隔离级别
set global transaction isolation level REPEATABLE READ;
-- 禁止自动提交
set autocommit=0
-- 开启自动提交
set autocommit=1

-- 显示当前数据库中所有表的名称。
show tables或show tables from database_name;
-- 显示mysql中所有数据库的名称。
show databases;
-- 显示表中列名称。
show columns from table_name from database_name; 或show columns from database_name.table_name;
-- 显示一个用户的权限，显示结果类似于grant 命令。
show grants for user_name;
-- 显示表的索引。
show index from table_name;
-- 显示一些系统特定资源的信息，例如，正在运行的线程数量。
show status;
-- 显示系统变量的名称和值。
show variables;
-- 显示系统中正在运行的所有进程，也就是当前正在执行的查询。大多数用户可以查看他们自己的进程，但是如果他们拥有process权限，就可以查看所有人的进程，包括密码。
show [full] processlist;
-- 显示当前使用或者指定的database中的每个表的信息。信息包括表类型和表的最新更新时间。
show table status;
-- 显示服务器所支持的不同权限。
show privileges;
-- 显示create database 语句是否能够创建指定的数据库。
show create database database_name;
-- 显示create database 语句是否能够创建指定的数据库。
show create table table_name;
-- 显示innoDB存储引擎的状态。
show innodb status;
-- 显示BDB存储引擎的日志。
show logs;
-- 显示最后一个执行的语句所产生的错误、警告和通知。
show warnings;
-- 只显示最后一个执行语句所产生的错误。
show errors;
--显示安装后的可用存储引擎和默认引擎。
show [storage] engines;
-- 查看表锁，Table_locks_waited数值高，锁表锁频次高
show status like 'table%';
-- 数值高说明，表锁比较严重
show status like 'innodb_row_lock%';

[mysqld]
# 主从复制配置
#设置server-id,保证主从唯一
server-id=1 
#开启二进制日志
log-bin=logPath
# 主库可以读写
read-only=0
# 设置不要复制的数据库
binlog-ignore-db=mysql
# 可设置定复制的数据库
# binlog-do-db=mysql

[mysqld]
# 主从复制配置
# 设置server-id,保证主从唯一
server-id=2
# 开启二进制日志
log-bin=logPath

# 创建用户rickslave为用户名，可自行替换  localhost为从库ip IDENTIFIED BY '密码'
create user 'rickslave'@'localhost' IDENTIFIED BY 'rickslave';
# 授权
grant replication slave on *.* to 'rickslave' @'localhost';
# 查看主库的状态，记住file和position的值，mysqlbin.000006，67需要在从库进行热备份配置。
show master status
# 处理的线程
show processlist
# 刷新权限
flush privileges;

# 从库复制功能主库配置, master_host为主库地址，master_user为从库用户，master_password为密码，master_log_file和master_log_pos为show master status 查询出对应的值
change master to master_host = 'localhost',
master_user = 'rickslave', master_password = 'rickslave',
master_log_file = 'mysqlbin.000006', master_log_pos =2031

# 启动从服务器复制功能
start slave
# 停用从库服务器复制功能
stop slave
显示从库状态,当Slave_IO_Running和Slave_SQL_Running为Yes时说明功能正常
show slave status