MySQL常用DDL

转载请注明出处

一、SQL分类
DDL—数据定义语言(CREATE，ALTER，DROP，DECLARE)
DML—数据操纵语言(SELECT，DELETE，UPDATE，INSERT)
DCL—数据控制语言(GRANT，REVOKE，COMMIT，ROLLBACK)
----------------------------------------------------------------------------------------------------------------------------------------
二、数据库相关
1：show databases;               查看数据库
2：use databaseName;             打开数据库
3：create database databaseName default charset=utf8       创建数据库
   drop database dbname
4：select database()             查看当前选中的数据库
5：select version()              查看当前数据库版本
6：show create database databaseName   查看建库语句
----------------------------------------------------------------------------------------------------------------------------------------
三、表相关
1：show tables;                  查看数据所有表(先用use databaseName选中当前数据库)
2：desc tableName;               查看表结构
3：show create table tableName   查看建表语句
4：alter table tablename modify columnName column_definition [FIRST | AFTER columnName]
   alter table tablename add    columnName column_definition [FIRST | AFTER columnName]
   alter table tablename change columnName newColumnName column_definition [FIRST | AFTER columnName]
   alter table tablename drop   columnName
   alter table tablename rename [T0] new_tablename;
5：CRUD
   insert into tableName (name, age) values ('aa',12),('bb',13)...
   update tableName set name = 'cc', age = 16 where ...
   delete from tableName where ......
   truncate table tableName
6：SHOW TABLE STATUS FROM DB_NAME    查看某数据库中表的所有信息
7：CREATE(ALTER) TABLE table_name ENGINE = MyISAM(InnoDB) 更改表引擎
----------------------------------------------------------------------------------------------------------------------------------------
四：权限相关
1：mysqladmin -u用户名 -p旧密码 password 新密码
2：Host is not allowed to connect to this MySQL server
   grant ALL on *.* to root@"%" identified by "";

----------------------------------------------------------------------------------------------------------------------------------------
五：索引相关
1：CREATE INDEX indexName ON mytable(username(length)[,password(length)])            创建索引
   ALTER mytable ADD INDEX [indexName] ON (username(length))
   注：如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length
       多列索引遵循最左前缀(Leftmost Prefixing)原则
2：DROP INDEX [indexName] ON mytable      删除索引
3：建立索引的时机
   MySQL只对<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE(like 'to%')才会使用索引

----------------------------------------------------------------------------------------------------------------------------------------
六：启动与关闭MySQL
mysqld_safe&                启动mysql
mysqladmin -u root shutdown 关闭mysql
mysqlshow                   显示mysql中的数据库列表
mysqladmin ping             显示mysql是否正在工作
mysqladmin version          显示mysql的版本

----------------------------------------------------------------------------------------------------------------------------------------
七：批量数据导入与导出
1：mysqldump -uroot -p manager > c:/manager.sql  注意：最后不要加任何东西
2：mysql -uroot -p dbname < c:/manager.sql       恢复时，先创建database，再用此命令。注意：最后不要加任何东西
3：csv格式文件的批量数据导入与导出
(1)select * from tableName into outfile '/tmp/tt.csv' fields terminated by ','  ENCLOSED BY '' LINES TERMINATED BY '/n'这是以csv格式导出数据，字段分隔是一个制表符
(2)load data infile '/tmp/tt.csv' into table tableName fields terminated by ','  
   这是csv格式数据的导入，注意，如果从excle中另存为csv格式，默认是以"，分隔的。
4：insert
(1)select * into target_table from source_table;
    要求目标表target_table不存在，因为在插入时会自动创建
(2)insert into target_table(column1,column2) select column1,5 from source_table;
    要求目标表target_table存在，由于目标表已经存在，所以我们除了插入源表source_table的字段外，还可以插入常量
----------------------------------------------------------------------------------------------------------------------------------------
八：变量相关
1：show [sessin, global] variables like '%v_name%'        查询系统的变量(创建EVENT时要用)
    v_name常用可选：sche, timeout, character
    SET [GLOBAL] wait_timeout=120
----------------------------------------------------------------------------------------------------------------------------------------
九：存储过程，函数，触发器，事件
1：ALTER EVENT event_test ON COMPLETION PRESERVE ENABLE   开启事件(EVENT)
   ALTER EVENT event_test ON COMPLETION PRESERVE DISABLE  停止事件(EVENT)：
2：show procedure status
   select `name` from mysql.proc where db = 'your_db_name' and `type` = 'PROCEDURE'
3：MyISAM支持索引，全文搜索，不支持外键和事务，强调了快速读取操作
   InnoDB支持索引，外键，事务，比MyISAM引擎慢很多，但5.1以上版本两者就相差不多了
----------------------------------------------------------------------------------------------------------------------------------------
十、其它：
SHOW ENGINES                      查看所有引擎

show processlist;                 查看数据库连接情况
show processlist/G;               查看数据库连接情况
show status                       查看数据库状态

让MySQL在查询时不区分大小写
/etc/my.cnf 或 %MySQL_HOME%/my.ini 在 [mysqld]下添加：
lower_case_table_names=1 不区分大小写