mysql一些常用指令

mysql的配置文件，在linux与osx中为my.cnf文件，其中mysql、client都是客户端的，mysqld表示下面的内容属于服务器端；windows中配置文件为my.ini，其它相同

 

设置编码方式为utf-8：

在client下添加default-character-set=utf8 

在mysqld下添加character-set-server=utf8 与 init_connect='SET NAMES utf8'

其他的一些参数请自行查找，暂且略过

 

因为在客户端执行sql语句时经常会出现一些错误代码，这时候可以使用perror xxx，来输出详细信息。

比如：

mysql> perror 1064;

 

从文本文件执行sql语句：

比如有一个文本文件，路径为~/sql.txt (或xxx.sql)

可以这样执行：

bash> mysql < ~/sql.txt

如果正在运行mysql，可以使用如下使用：

mysql> source ~/sql.txt
mysql> \. ~/sql.txt

 

查看数据库存储引擎：

mysql> show engines;

 MyISAM：不支持事务，该引擎的表存储为3个文件，其文件名称与表名相同，但后缀分别以 .myd、.myi、.frm结尾，其中，以.myd为后缀名的文件存储数据、以.myi为后缀名的文件存储索引、以.frm为后缀的文件存储表的结构；插入数据快，主要用于插入新记录和读出记录。

MEMORY：使用hash索引，所有数据存储在内存中，处理速度快但不安全，适合用于存储临时数据的临时表；这类数据库只适用于相对较小的数据库表。

InnoDB：提供了良好的事务管理、崩溃修复能力和并发控制、支持外键。缺点在于读写效率稍差、占用的数据空间相对较大；如果对事务的完整性要求较高，需要频繁的进行更新和删除操作，那么可以选择InnoDB，因为该存储引擎可以实现事务的提交和回滚。InnoDB为mysql默认存储引擎

 

创建数据库：

mysql> CREATE DATABASE IF NOT EXISTS newdb;

 查看数据库：

mysql> SHOW CREATE DATABASE newdb;

使用数据库：

mysql> USE newdb;

删除数据库：

mysql> DROP DATABASE IF EXISTS newdb;

 

创建数据库表：

mysql> CREATE TABLE newtable(field1 type restraint, field2 type restraint,...);

 查看表结构：

mysql> DESCRIBE newtable;
mysql> DESC newtable;

 查看表的详细结构：

mysql> SHOW CREATE TABLE newtable;

 添加表数据：

mysql> INSERT INTO newtable(field1,field2,...) 
                   VALUES(data1,data2,...),(data1,data2,...);

查看表数据：

mysql> SELECT * FROM newtable;

删除表数据：

mysql> DELETE FROM newtable; 没有WHERE进行控制则清空该表

修改表名：

mysql> ALTER TABLE newtable RENAME new_newtable;

字段类型修改：

mysql> ALTER TABLE <表名> MODIFY <字段名> <数据类型>;
mysql> ALTER TABLE new_newtable MODIFY field1 INT;

添加字段：

mysql> ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] [FIRST|AFTER 已存在字段名];
mysql> ALTER TABLE new_newtable ADD newfield INT FIRST;

字段位置修改：

mysql> ALTER TABLE <表名> MODIFY <字段1> <数据类型> FIRST|AFTER <字段2>;
mysql> ALTER TABLE new_newtable MODIFY newfield INT AFTER field1;

删除字段：

mysql> ALTER TABLE <表名> DROP <字段名>;
mysql> ALTER TABLE new_newtable DROP newfield;

删除表：

mysql> DROP TABLE new_newtable;

 

 

主键：

是表的标志列，在mysql中支持主键组的使用，主键对用户通常没有意义。

唯一地标志一行数据、作为一个可以被外键引用的有效对象。

创建表时添加主键：

mysql> CREATE TABLE table1(
id INT,
name VARCHAR(30),
tid INT,
PRIMARY KEY(id)
); 

无主键时添加主键的两种方式：

mysql> ALTER TABLE table1 ADD PRIMARY KEY(id);
mysql> ALTER TABLE table1 MODIFY id INT PRIMARY KEY;

删除主键、无须填写主键名称：

mysql> ALTER TABLE table1 DROP PRIMARY KEY; 

 

外键：

主键用于标志表中的数据，而外键用于表之间的联系。

所有的表必须是InnoDB类型、要建立外键的字段必须建立约束。

添加外键约束：

mysql> ALTER TABLE table1
ADD CONSTRAINT 外键名称 FOREIGN KEY(tid)
REFERENCES table2(id)
ON DELETE RESTRICT
ON UPDATE CASCADE;

外键所在的字段必须与关联的字段类型对应，

 

下面是几种关联操作类型：

CASCADE：外键表中外键字段会跟随父表被更新，或所在的列会被删除。

NO ACTION：不进行任何关联操作。

RESTRICT：相当于NO ACTION，即不进行任何操作。拒绝父表修改外键关联列，删除记录。

SET NULL：在父表的外键关联字段被修改或删除时，外键表的外键被设置为空NULL。

 

外键与主键均可使用SHOW来查看：

mysql> SHOW CREATE TABLE table1; 

删除外键：

mysql> ALTER TABLE table1 DROP FOREIGN KEY 外键名;

一个表中的外键可能不止一个，上述语句可以删除指定的一个外键，删除多个外键可重复上述语句。

 

为字段添加默认值：

mysql> ALTER TABLE 表名称 ALTER 字段名称 SET DEFAULT 默认值数据;
mysql> ALTER TABLE table1 ALTER name SET DEFAULT '表1';

删除默认值：

mysql> ALTER TABLE 表名称 ALTER 字段名称 DROP DEFAULT;

 

唯一性约束：

可以保证一列或几列不出现重复值。

创建表时添加唯一性约束的两种方式：

mysql> CREATE TABLE table1(
id INT NOT NULL UNIQUE, // 1，声明字段时直接添加
name VARCHAR(30) NOT NULL, 
UNIQUE(name) // 2，在所有字段后定义
);

删除唯一性约束：

mysql> ALTER TABLE table MODIFY name VARCHAR(30) NOT NULL;

添加唯一性约束：

mysql> ALTER TABLE 表名 ADD UNIQUE(字段列表);
mysql> ALTER TABLE table1 ADD UNIQUE(name);

 

自增约束：

mysql表中只能由一个AUTO_INCREMENT字段，

自增字段必须被定义为键，如外键、主键。

创建表时添加自增约束：

mysql> CREATE TABLE table1(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(30)
);

在表中直接添加、其中id为主键：

mysql> ALTER TABLE table2 MODIFY id INT NOT NULL AUTO_INCREMENT; 

 删除自增约束：

mysql> ALTER TABLE table2 MODIFY id INT NOT NULL;

 自增约束的删除，并不影响其主见设置。

 

删除指定名称的约束：

可通过SHOW CREATE TABLE来查看约束名称，

mysql> SHOW CREATE TABLE 表名;
mysql> ALTER TABLE 表名 DROP INDEX 约束名;

 

根据条件查询：

mysql> SELECT 查询字段 FROM table_name WHERE 条件表达式;

查询条件：

比较：=、<、<=、>、>=、!=、!>、!<、<>；

指定范围：BETWEEN AND、NOT BETWEEN AND；

指定集合：IN、NOT IN；

匹配字符：LIKE、NOT LIKE；

是否为空值：IS NULL、IS NOT NULL；

多个查询条件：AND、OR；

 

分组查询：

GROUP BY 字段名 [HAVING 条件表达式] [WITH ROLLUP]

两个可选的表达式，前者用来限制分组后的显示，满足条件表达式的结果将会被显示出来；后者将会在所有记录的最后加上一条，该记录时上面所有记录的总和。

可以指定多个字段进行分组，当第一个字段的值相同时，再把相等的记录按照第二个或之后的字段进行分组；

 

排序查询：

ORDER BY 字段名 [ASC | DESC]

ASC：升序、DESC：降序。

 

LIMIT限制查询数量：

LIMIT 初始位置， 查询数量

初始位置从0开始，可以不填，默认为0.

 

避免重复查询：

SELECT DISTINCT 字段名称

 

外链接查询：

mysql> SELECT 字段表 FROM 表1 LEFT|RIGHT JOIN 表2 ON 表1.字段=表2.字段;

 

正则表达式：

字段名 REGEXP '匹配方式'
mysql> SELECT * FROM table1 WHERE field1 REGEXP 'some';

REGEXP为regular expression的缩写。

 

更新数据：

mysql> UPDATE 表名 SET 字段1=值1, 字段2=值2,... WHERE 条件表达式;

 

删除数据：

mysql> DELETE FROM 表名 [WHERE 条件表达式];

 

用户自定义变量：

mysql> SET @user_variable1 = expression1, @user_variable2 = expression2,...;
mysql> SET @user1 = 1, @user2 = 2, @user3 = 3;

在SELECT中需要使用:=才行：

mysql> SELECT @t2 := 7;

 

一些系统变量：

CURRENT_DATE：系统日期

CURRENT_TIME：系统时间

CURRENT_TIMESTAMP：系统日期和时间

使用SHOW VARIABLES可以得到系统变量的所有会话变量清单：

mysql> SHOW VARIABLES;

系统变量可分为全局系统变量和会话系统变量。

 

IF语句、相当于三目运算符：

IF(条件，结果1，结果2)

条件成立返回结果1，否则返回结果2。

 

合并多个字符串：

CONCAT(str1,str2,str3,...)
CONCAT_WS(x,str1,str2,str3)

第二个全称为：CONCAT WITH SEPARATOR，使用指定的连接符号进行连接。

 

截取指定位置的字符串：

SUBSTRING(str,pos,len)

与java中不同，最后一个参数表示截取的长度，pos的取值从1开始

 

删除空格或指定字符：

TRIM([BOTH|LEADING|TRAILING] [remstr] FROM str)

 

ABS(X)：返回X的绝对值

CEIL(X)：向上取整

FLOOR(X)：向下取整

ROUND(X)：返回离X最近的整数

RAND()、RAND(X)：返回一个范围在0到1之间的随机浮点值。如果已指定一个整数参数X，则它被用作种子值

 

 随机生成整数：

mysql> SELECT ROUND(RAND() * 1000);