【MySQL】MySQL常用操作集合

语言介绍

在mysql中有很多的sql语言，但是总结出来都是归属于以下四种类型的操作语言。分别为

• DDL（Data Definition Language）数据库定义语言
  
• DML（Data Manipulation Language）数据操纵语言
  
• DCL（Data Control Language）数据库控制语言
  
• TCL（Transaction Control Language）事务控制语言
  

先对四种语言做简单介绍，然后对常用的命令进行记录。

1.DDL（Data Definition Language）数据库定义语言statements are used to define the database structure or schema.

DDL是SQL语言的四大功能之一。
用于定义数据库的三级结构，包括外模式、概念模式、内模式及其相互之间的映像，定义数据的完整性、安全控制等约束
DDL不需要commit.
CREATE
ALTER
DROP
TRUNCATE
COMMENT
RENAME

2.DML（Data Manipulation Language）数据操纵语言statements are used for managing data within schema objects.

由DBMS提供，用于让用户或程序员使用，实现对数据库中数据的操作。
DML分成交互型DML和嵌入型DML两类。
依据语言的级别，DML又可分成过程性DML和非过程性DML两种。
需要commit.
SELECT
INSERT
UPDATE
DELETE
MERGE
CALL
EXPLAIN PLAN
LOCK TABLE

3.DCL（Data Control Language）数据库控制语言  授权，角色控制等
GRANT 授权
REVOKE 取消授权

4.TCL（Transaction Control Language）事务控制语言
SAVEPOINT 设置保存点
ROLLBACK  回滚
SET TRANSACTION

命令介绍

mysql中经常使用的一些命令

重启数据库：
windows： net start mysql / net stop mysql。它没有restart命令
linux 因为它有各种分支，你真是不知道哪一种可以使用，不过一般有如下几种
使用service命令：service mysql start或者service mysqld start
使用init.d下的命令：/etc/init.d/mysql start
suse下奇葩的一个启动命令：/usr/local/mysql-5.0.67-linux-x86_64-glibc23/support-files/mysql.server start

跳过密码登录：

mysqld_safe --skip-grant-tables

一般是用来忘记密码的时候登录，当你运行上述了命令，再登录的时候就不使用密码也可以登录。

修改数据库密码：

mysqladmin -uroot -poldpassword password "newpassword"

设置远程访问权限

GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'IP' IDENTIFIED BY 'PASSWORD';

然后需要刷新权限，通过下面命令进行权限刷新

FLUSH PRIVILEGES;

备份数据库：
sudo mysqldump -uroot -pabc123 mytest > mytest.sql  (没有创建库的sql)
备份数据库一个表格：
sudo mysqldump -uroot -pabc123 db_name table_name > mytest.sql  (没有创建库的sql)

备份数据库结构：
mysqldump -uroot  -p -d --add-drop-table tablename >/tmp/db.sql
-d 没有数据 --add-drop-table 在每个create语句之前增加一个drop table 

只导出结构也可以用下面语句

mysqldump -u [user] -p[pass] --no-data mydb > mydb.sql

只导出数据
mysqldump -u [user] -p[pass] --no-create-info mydb > mydb.sql
只导出符合条件的数据，注意where后的等号两边不能有空格
mysqldump -u [user] -p[pass] --no-create-info  -- where = "" mydb > mydb.sql
导出某几列

mysql>select column1, column2 where condition into outfile“absPath”

恢复数据库

mysql>use dbName

然后使用source命令，后面参数为脚本文件位置（如这里用到的.sql）
mysql>source d:\wcnc_db.sql
恢复数据库:
sudo mysql -uroot -pabc123 mytest < mytest.sql

复制数据库：

mysqldump db -uroot -ppasswd | mysql newDB -uroot -ppasswd

显示数据库字符编码
show variables like "character%"

修改表的编码

alter table table_name convert to character set gbk

增加主键

alter table tabelname add new_field_id int(5) unsigned default 0 not null auto_increment ,add primary key (new_field_id);

修改主键为自增

alter table tablename ·idname· int auto_increment

显示表的创建语句
show create table table_name
重命名表
alter table t1 rename t2
rename table t1 to t2;
调整字段顺序：
alter table `user_movement_log` CHANGE `GatewayId` `GatewayId` int not null default 0 AFTER RegionID
增加一个新列
alter table t2 add d timestamp;
alter table infos add ex tinyint not null default '0';
alter table tablename add column c_name int not null default 0 AFTER c1_name comment “”

删除字段
alter table tablename drop column c_name;

重命名列
alter table t1 change a b integer;
改变列的类型
alter table t1 change b b bigint not null;
alter table table_name modify column column_name new_type;
更新某列的值：
UPDATE table_name SET colomn_name = value WHERE id = value
插入忽略错误：
insert ignore into table_name values ()；
//使用ignore会忽略错误，插入数值，如果某一列出现错误则插入的数值为各个列的默认值
插入缓冲
insert delayed
索引操作
创建索引
mysql> alter table table_name add index index_name (name);
mysql> create index_type index  index_name on table_name (col1, col2);
查看索引
mysql> show index from table_name
删除索引：
mysql> drop index index_name on table_name；
mysql>alter table tablename drop index emp_name;
触发器操作
drop trigger trigger_name;

查询操作
1：top num

mysql没有select top num 语句，可以先对记录进行排序，然后使用limit num进行查询
select * from my_test order by id desc limit 1;
查询符合某个条件的记录数的前几个。比如查询一天ip被访问次数最多的前5个
select ip, count(freqency) as freq from test group by ip order by freq limit 5;

2：and or查询

当where条件查询中同时存在and与or的时候需要注意了

你可以把or条件放在where语句前面，然后用括号括起来，再加上and条件

select * from tablename where (condition1 or condition2) and condition3

或者and放在前面，后面连接上or条件，这时候or条件可以加括号也可以不加

select * from tablename wherecondition3 and (condition1 or condition2)