mysql约束，mysql 命令限制_MySQL 命令总结

数据库支持的存储引擎： show engines

创建表时设置存储引擎：create table tn (cn int)engine=innodb defualt charset=utf8

修改存储引擎：alter table tn engine=myisam dufault charset=gbk

mysql约束？设置 auto_increment 的初始值，默认从1开始：alter table tn auto_increment=10

查询当前线程最后插入记录使用的值：select last_insert_id()

关闭外键检查：set foreign_key_checks=0

查看表的状态：show table status like 'tn'

mysql最多多少字段、增加外键：alter table tn1 constraint fkname add foreign key(indexname) references tn2(indexname) on delete no action on update cascade

cascade 跟随父表更新删除

restrict 默认值，和 no action 一样，限制父表更新删除

set null 父表更新删除，子表设置为 null

mysql删除数据库命令，创建表时添加外键：create table tn (cn int primary key,foreign key(cn) references tn2(cn) on update cascade on delete restrict)

删除外键：alter table tn drop foreign key fkname

查看字符集：show character set，information_schema.character_sets

查看字符集的校对规则：show collation like 'utf8%'，information_schema.collations

mysql命令行，服务器级字符集，在 my.cnf 文件中设置： [mysqld] character-set-server=utf8

查看服务器字符集：show variables like 'character_set_server'

显示数据库字符集和校对规则：show variables like 'character_set_database'，show variables like 'collation_database'

修改数据库字符集：alter database db character set utf8

登录mysql命令？显示表字符集：show create table tn

修改表字符集：alter table tn default character set utf8

设置连接字符集，在 my.cnf 文件中设置：[mysql] default-character-set=utf8，或每次通过命令 set names utf8

导出表：mysqldump -uroot -p db>newdb.sql

MySQL限制返回结果。-d 不导出插入的数据

-t 不导出表创建表结构

-n 不导出创建数据库的语句

--quick 该选项用于转储大的表。它强制 mysqldump 从服务器一次一行地检索表中的行而不是检索所有的行，并在输出前将它缓存到内存中

mysqldump命令。--extended-insert 使用包括几个 values 列表的多行 insert 语法。这样使转储文件更小，重载文件时可以加速插入

--no-create-info 不导出每个转储表的 create table 语句

--default-character-set=Latin1 按照原有的字符集导出所有数据，这样导出的文件中，所有中文都是可见的，不会保存成乱码

导入数据：mysql -uroot -p db

Mysql锁？创建索引：create [unique|fulltext|spatial]index indexname on tname(cname(length),...)

增加索引：alter table tn add index indexname(length)

删除索引：drop index indexname on tname 或 alter table tname drop index indexname

创建视图： create [or replace] [algorithm = {undefined | merge | temptable}] view viewname as select_statement [with [cascaded | local] check option]

mysql备份数据库命令，修改视图： alter [algorithm = {undefined | merge | temptable}] view viewname as select_statement [with [cascaded | local] check option]

删除视图：drop view viewname

查看视图：show create view viewname，show table status like 'viewname'

修改定界符： delimiter ;

mysql in，创建存储过程： create procedure pname([parameter[,...]])[characteristic ...] routine_body

创建函数：create function fname([parameter[,...]])returns type[characteristic ...] routine_body

修改存储过程或函数：alter {procedure|function} name [characteristic...]

调用过程：call name(parameter[,...])

mysql数据库命令大全，删除存储过程或函数：drop {procedure|function} [if exists] name

查看存储过程或函数的状态：show [procedure|function] status like 'name'

查看存储过程或函数的定义：show create [procedure|function] name

查看 information_schema.routines表了解存储过程和函数的信息

定义变量：declare varname[,...] type [default value]

变量赋值：set varname=value,[varname=value]...

通过查询赋值：select cname into varname from tname

定义条件：declare condition_name condition for sqlstate_value|mysql_error_code

定义条件处理：create continue|exit|undo handler for (sqlstate_value|condition_name|sqlwarning|not fond|sqlexception|mysql_error_code)[,...] sp_statement

创建光标：declare cursor_name cursor for select_statement

open 光标：open cursor_name

fetch 光标：fetch cursor_name into var_name[,var_name]...

close 光标：close curosr_name

变量，条件，处理程序，光标都是通过 declare 定义的，它们之间是有先后顺序要求的。(变量，条件，光标，处理程序)

if 语句：if search_condition then statement_list [elseif search_condition then statement_list]... [else statement_list] endif

case 语句： case case_value when when_value then statement_list [when when_value then statement_list]...[else statement_list] end case 或 case when search_condition then statement_list [when search_condition then statement_list]... [else statement_list] end case

loop 语句: [begin_label:]loop statement_list end loop[end_label]

leave 语句：leava label 退出循环

iterate 语句：iterate label 放弃循环剩下的语句，进入下一个循环

repeat 语句：[begin_label:] repeat statement_list until search_condition end repeat [end_label] 满足条件退出循环，至少执行一次

while 语句：[begin_label:] while search_condition do statement_list end while[end_label] 满足条件执行循环

事件调度器：create event eventname on schedule every 5 second do insert into tname values(1,2,3)

查看事件：show events

查看事件状态：show variables like '%scheduler%'

打开事件调度器：set global event_scheduler = 1

禁用事件：alter event eventname disabled

删除事件：drop event eventname

清空表：truncate table tablename

查看线程：show processlist

创建触发器：create trigger triggername before|after update|delete|insert on tname for each row begin ... end

删除触发器：drop trigger triggername

查看触发器：show triggers 或 information_schema.triggers 表

表锁：lock tables tname {read [local]|[low_priority]write},tname... 或 lock table tname read|write

表解锁：unlock tables

开始事务：start transaction 或 begin

提交事务：commit [and chain|release]

回滚：rollback [to savepoint pointname]

关闭自动提交：set autocommit=0

指定事务回滚的位置：savepoint pointname

删除位置：release savepoint pointname

查看 SQL mode：select @@sql_mode

设置 SQL mode：set (session|global)? sql_mode='modes'

range 分区：create table tname (...)partition by range [columns](cname)(partition p0 values less than(10),partition p1 values less than(20),...partition pn values less than maxvalue)

删除分区(range|list)：alter table tname drop partition p0

增加分区(range|list)：alter table tname add partition (partition p3 values less than (30))

list 分区：create table tname (...) partition by list[columns](cname)(partition p0 in (1,3,5),partition p1 in (2,4,6),...)

hash 分区(整数)：create table tname (...) partition by [linear] hash (cname) partitions n

key 分区：create table tname(...) partition by [linear] key (cname) partitions n

子分区：create table tname(...)partition by range|list (cname) subpartition by hash|key(cname) subpartitions n (partition p0 values less than (10),partition p1 values less than (20))

重组分区(range|list)：alter table tname reorganize partition p0,p1 into (partition p0 values less than (20))

合并分区(key|hash)：alter table tname coalesce partition 2

增加分区(key|hash)：alter table tname add partition partitions 8

显示SQL执行频率：show status like 'com%'

显示SQL执行计划： explain select * from tname 或 explain extended select * from tname 或 explain partitions select * from tname

检查是否支持profile：select @@have_profiling

检查是否开启profiling：select @@profiling

设置profiling：set profiling = 1

查看profiles：show profiles

查看profile：show profile [all|cpu|block io|context switch| page faults] for query queryid

查看索引使用情况：show status like 'handler_read%'

分析表：analyze [local|no_write_to_binlog] table tname[,tname]...

检查表：check table tname[,tname]... [quick|fast|medium|extended|changed]

优化表：optimize [local|no_write_to_binlog] table tname[,tname]...

设置 MyISAM 表非唯一索引的更新： alter table tname enable|disable keys

设置唯一性校验：set unique_checks=0|1

显示表的索引：show index from tname

SQL 提示：select * from tname use index(indexname) 或 select * from tname ignore index (indexname) 或 select * from tname force index(indexname)

随机排序：order by rand()

不排序：order by null

bit_or(cn) 和 bit_and(cname)

优化表的数据类型：select * from tn procudure analyse()

查看表锁争夺情况：show status like 'table%'

降低更新请求的优先级：set low_priority_updates=1

查看行锁争夺情况：show status like 'innodb_row_lock%'

共享读锁：select * from tn where ... lock in share mode

排他写锁：select ... for update

行锁是给索引加锁，不然会给所有行加锁，相同索引的值都会加锁

查看隔离级别：select @@tx_isolation

查看发生死锁的原因：show innodb status

查看所有线程的状态：show engine innodb status

查看缓存区大小：show variables like 'key_buffer_size'

查看查询缓存：show variables like 'query_cache'

显示所有命令 mysql --help

查看当前连接的用户：select current_user()

连接时设置连接字符集：mysql -uroot -p --default-character-set=utf8

在 mysqld 开启 binlog：bin-log=master-bin

在 mysqld 设置 binlogindex：bin-log-index=master-bin.index

查看所有 binlog：show master logs

查看最新 binlog：show master status

binlog 读取：mysqlbinlog binlogname-bin.000001 或 show binlog events in 'binlogname-bin.000001' [from pos] [limit]

刷新 binlog(所有新的记录都记录在新的 binlog 中)：flush logs

清空binlog：reset master

删除 binlog.000006 之前的 binlog：purge master logs to 'binlog.000006'

删除某时间之前的 binlog：purge master logs before '2017-11-8 14:47:00'

从 binlog 恢复：mysqlbinlog --stop-position=1234 binlog-bin.000002 | mysql -uroot -p

在 mysqld 设置日志的过期天数：expire_logs_day=n

设置查询日志：set global general_log=1

导出表：select * from tn into outfile 'filename'

导入表：load data infile 'filename' into table tn [fileds terminated by ',' (optionally)? enclosed by '"' ...]

创建用户(授予权限)：grant all privileges on *.* to name@'127.0.0.1' identified by '123' [with grant option|max_queries_per_hours n|max_updates_per_hours n|max_connections_per_hours n|max_user_connections n]

查看用户权限：show grants for user@host

收回权限：revoke all privileges,grant option on *.* from user@host[,user...]

修改密码：set password for user@host=password('123')

删除用户：drop user username@host

刷新权限：flush privileges|flush user_resources|mysqladmin reload

设置 mysqld 跳过权限表：skip-grant-tables