mysql 常用操作

导出数据：

mysqldump -ppassword -u username dbname tableName > tableName.sql
mysqldump --user=user --password=pass db table --where="id=1" > dump.sql

导入数据：

mysql db_name < script.sql

查看外键约束：

SELECT
TABLE_NAME,
COLUMN_NAME,
CONSTRAINT_NAME,  
REFERENCED_TABLE_NAME,
REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_NAME = 'My_Table';

全部的表：

SELECT   TABLE_NAME,   COLUMN_NAME,   CONSTRAINT_NAME,      REFERENCED_TABLE_NAME,   REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE ;

去除约束：

ALTER TABLE table_name DROP FOREIGN KEY fk_constraint;

添加约束：

Alter Table Table1 Add Constraint [CONSTRAINTNAME] Foreign Key (Column) References Table2 (Column) On Update Cascade On Delete Cascade;
ALTER TABLE  table_name  ADD CONSTRAINT fk_constraint  FOREIGN KEY (column) REFERENCES table2(column) On Update Cascade On Delete Cascade;

主键：

Alter Table Table add constraint [Primary Key] Primary key(Column1,Column2,.....)

约束不能更改，只能先移除再创建。

将id键复位到1：

ALTER TABLE table_name AUTO_INCREMENT = 1;

改变条目：

UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]

选择前10条：

select * from table1 limit 10