<ul><li>SQL分类</li></ul>DDL—数据定义语言(CREATE,ALTER, DROP, DECLARE)
DML—数据操纵语言(SELECT,DELETE,UPDATE,INSERT)
DCL—数据控制语言(GRANT, REVOKE,COMMIT,ROLLBACK)
--------------------------------------------------------------------------------------------------------------------------------
<ul><li>数据库相关</li></ul>
-- <span style="font-family: Arial, Helvetica, sans-serif;">查看数据库</span>
show databases;
-- 打开数据库
use databaseName;
-- <span style="font-family: Arial, Helvetica, sans-serif;">创建数据库</span>
create database ${dbname} default charset=utf8
-- 删除数据库
drop database dbname
-- 查看当前操作数据库
select database();
-- 查看MySQL版本
select version();
-- <span style="font-family: Arial, Helvetica, sans-serif;">查看建库语句</span>
show create database ${dbname}
--------------------------------------------------------------------------------------------------------------------------------
<ul><li><span style="font-family: Arial, Helvetica, sans-serif;">表相关</span></li></ul><span style="font-family: Arial, Helvetica, sans-serif;">show tables; 查看数据所有表(先用use databaseName选中当前数据库)</span>
desc tableName; 查看表结构
show create table tableName 查看建表语句
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;
CRUD
INSERT INTO ${dbname} (name, age) values ('aa',12),('bb',13)...
UPDATE ${dbname} set name = 'cc', age = 16 where ...
DELETE FROM tableName where ......
TRUNCATE TABLE tableName
SHOW TABLE STATUS FROM DB_NAME 查看某数据库中表的所有信息
CREATE(ALTER) TABLE table_name ENGINE = MyISAM(InnoDB) 更改表引擎
----------------------------------------------------------------------------------------------------------------------------------------
四:权限相关
mysqladmin -u用户名 -p旧密码 password 新密码
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
--------------------------------------------------------------------------------------------------------------------------------
九:存储过程,函数,触发器,事件
ALTER EVENT event_test ON COMPLETION PRESERVE ENABLE 开启事件(EVENT)
ALTER EVENT event_test ON COMPLETION PRESERVE DISABLE 停止事件(EVENT)
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 不区分大小写