MySQL常用命令总结

  • 2015-06-07
  • 84
  • 0
  • 0

1、概述

1.1 什么是SQL语句

SQL(Structured Query Language),即结构化查询语言,用以存取数据以及查询、更新和管理关系型数据库系统,同时也是数据库脚本文件的扩展名。
SQL语句默认是不区分大小写的,但建议SQL关键字都用大写。

1.2 分类

分类
说明
常用语句
DQL
数据检索语句。
select
DDL
数据定义语句,这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象。
create、drop、alter等。
DML
数据操纵语句,用于添加、删除、更新数据库记录,并检查数据完整性。
insert、delete、update等。
DCL
数据控制语句,用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。
grant、revoke等。
TPL
事物处理语言,它能确保被DML语句影响的表的所有行及时得以更新。
begin、transaction、commit、rollback等。
CCL
指针控制语言,用于对一个或多个表单独行的操作。
declare cursor、fetch into、update where current等。

:每个sql语句后必须加;或\g,否则不能执行。

2、获取帮助

格式

help 命令;
例如

mysql> help grant;
Name: 'GRANT'
Description:
Syntax:
GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user [auth_option] [, user [auth_option]] ...
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH {GRANT OPTION | resource_option} ...]

GRANT PROXY ON user
    TO user [, user] ...
    [WITH GRANT OPTION]

object_type: {
    TABLE
  | FUNCTION
  | PROCEDURE
}

3、DQL语句

通配符星号(*),查询多个字段名时,字段名之间用逗号隔开。
尽量不用*,影响查询效率,可以。
查询:select 字段名1,字段名2 from tablename where 条件;
多条件查询:select 字段名1,字段名2 from tablename where 条件1 and 条件2;
查询:select 字段名1,字段名2 from tablename where field1 like ’%value1%’;
升序查询:select 字段名1,字段名2 from tablename order by asc;
升序查询:select 字段名1,字段名2 from tablename order by desc;
多表联查:select table1.字段名1 table2.字段名2 from table1,table2 where 条件;
总数:select count as totalcount from table1;
求和:select sum(field1) as sumvalue from table1;
平均:select avg(field1) as avgvalue from table1;
最大:select max(field1) as maxvalue from table1;
最小:select min(field1) as minvalue from table1;

4、DML语句

添加:insert into table1(field1,field2) values(value1,value2);
删除:delete from table1 where 范围;
修改:update table1 set field1=value1 where 范围;

5、DDL语句

5.1 查看和使用数据库

查看数据库:show databases;
使用数据库:use dbname;

5.2 创建和删除数据库

创建数据库:create database dbname;
删除数据库:drop database dbname;
查看创建的数据库的字符集:show create database tablename\G;

5.3 创建和删除表

创建表:create table tablename(字段名1 类型1 not null primary key,字段名2 类型2 not null,..);
例如:

CREATE TABLE person (
number INT(11),
name VARCHAR(255),
birthday DATE
);

删除表(逻辑清除,按行删):drop table tablename;
删除表(清空物理文件,更快):truncate table tablename;
查看创建表:show create table dbname.tablename;
查看表结构:desc dbname.tablename;
查看表结构:show columns from dbname.tablename;

5.4 添加表的字段

默认是添加到最后,格式如下:
alter table tablename add 字段名 字段类型;
若要指定添加到某个字段前或后,可以用before或after,格式如下:
alter table tablename add 字段名 字段类型 before 字段名;
alter table tablename add 字段名 字段类型 after 字段名;

5.5 改变表的字段

修改字段名
alter table tablename change 原字段名  改后的字段名 字段类型;
改变字段类型
alter table tablename modify column 字段名 字段类型;

5.5 创建索引

建立索引,可以加快查询速度,但会影响更新速度。
按主键查询是最快的,每个表只有一个主键列,但可以有多个普通的索引列。
添加主键索引:alter table tablename add primary key(column);
例如:alter table hello.user add primary key(id);
一般不建议建表后再增加主键索引,建议建表时创建。
添加普通索引:alter table tablename add index index_name(column);
例如:alter table hello.user add index index_test(name);
添加唯一索引:alter table tablename add unique(column);
添加多列索引(联合索引):alter table tablename add index index_name(column1,column2…);
按条件查询数据时,联合索引是有前缀生效特性的,即index(a,b,c)仅a,ab,abc三个查询条件列可以走索引,b,bc,c,ac无法使用索引。
索引会占用系统空间,更新数据库时还需要维护索引数据,因此索引不是越多越好,数据量小的表无需建立索引,写频繁、读少的业务尽量少建立索引。尽量在唯一值多、数据量大的表上建立索引。

4.6 删除索引

方法和创建差不多。
删除主键索引:alter table tablename drop primary key;
删除普通索引:alter table tablename drop index index_name

6、DCL语句

创建用户:create user ‘username’@’hostname’ identified by ‘password’;
授予用户权限:grant 权限 on dbname.tablename to username@hostname identified by ‘password’;
若要授权远程登录,hostname可以写成域名或ip地址段。
远程登录的时候需要加“-h 地址段”,若不加为本地登录。
ip地址段:%表示任意地址,如果是允许所有地址登录,则写”%”;如果部分地址段,可以写成”192.168.10.%”,也可以写成”192.168.10.0/255.255.255.0″。
查看用户权限:show grants for username@hostname;
例如,创建一个admin01用户,对test库具备所有权限,允许从localhost主机登录管理数据库,密码为123456:

create user 'admin01'@'localhost' identified by '123456';
grant all on test.* to 'admin01'@'localhost' identified by '123456';

收回用户权限:revoke 权限 on dbname.tablename from username@hostname;
权限:SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER
也就是说all privileges(也可以简写成all)包括以上权限。
部署软件的时候可以授权:select、insert、update、delete、create、drop。日常管理只需授权前四个即可。

删除用户

drop user 'username'@'hostname';

7、查看数据库状态和进程

查看当前会话的数据库状态信息:

show status;

查看整个数据库运行状态信息:

show global status;

查看正在执行的SQL语句:

show processlist;

查看正在执行的完整SQL语句:

show full processlist;

查看数据库的参数信息:

show variables;

评论

还没有任何评论,你来说两句吧