11个最常用的MySQL语句

原帖地址

• 作者： Sachin Khosla
• 译者：Sheneyan(子乌)
• 时间：2010-10-06
• 英文原文：The 10 Most Common MySQL Queries

子乌注：嗯，纯入门的mysql教程。

1、创建表

在正常情况下估计没几个人会每次都使用脚本创建表(嗯，深得我心……)。不过，当你开始创建一个应用的时候，你需要创建数据库表。你可以使用下列语句来创建一个带主键的表。

Language: MySQL

CREATE TABLE  'emp' (
  'id' INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  'name' VARCHAR(45) NOT NULL,
  'lastname' VARCHAR(45),
  'dept' VARCHAR(45) DEFAULT 'sales',
  PRIMARY KEY ('id')
)
ENGINE = InnoDB;

上述语句在指定数据库中创建了一个名为“emp”的表。同时会有一个有主键约束的自增长字段“id”，自增保证了每次插入新行的时候id都会加1，而主键约束则用来检验重复值。字段“dept”被赋予了默认值（当没设定内容时为“sales”）。你可以在创建表的时候指定“引擎”。否则会使用默认引擎（MyISAM）。我们在这里使用“InnoDB”是因为它支持外键和事务处理。

2、 插入语句

现在你的数据库里有了一张表，我们接下来看看如何在表里插数据。你可能已经使用插入语句很多次了，不过你用过既可以插入也可以更新（需要的话）的语句吗？这可以使用关键字“on DUPLICATE KEY”实现。下面是个例子：

简单的插入语句

Language: MySQL

insert into emp (name,lastname,dept) values('new name','new last name','Accounts');

插入/更新语句

Language: MySQL

insert into emp (id,name,lastname,dept) 
values('key that already exists', 'new name','new last name','Accounts')
on duplicate KEY UPDATE  name='default name', lastname='default last name';

在上面的查询语句中，如果传递给“id”的值已经存在，我们可以更新该行的内容而不是插入新行。当你需要检验字段而不是直接插入自增值时这会相当有用。

3、 连接

因为连接有太多类型而显得很复杂。在不同的数据库中连接拥有不同的名称：自连接（self join），外连接（outer join），内连接（inner join），自然连接（natural join）等等。我们通常在两张表之间使用连接以获取数据。

让我们假设我们有另一张叫做“departments”的表，在其中存放了所有部门的数据。如果你想要获取带部门信息的雇员信息，查询语句应该是长这样子：

常规连接（REGULAR JOIN子乌注：这并不是连接的一种定义，只是来与左连接和右连接区分而已）

Language: MySQL

select emp.name,department.name from emp 
join department on emp.dept = department.name

左连接（LEFT JOIN）

Language: MySQL

select emp.name,department.name from emp 
left join department on emp.dept = department.name

右连接（RIGHT JOIN）

Language: MySQL

select emp.name,department.name from emp 
right join department on emp.dept = department.name

常规连接会取得两张表中满足条件的全部数据。左连接会匹配符合条件的记录，不过它也会返回左表中不符合条件的数据。另一方面，右连接会取得右表中所有不符合条件的数据。（子乌注：话说我还是喜欢oracle中外联的简化写法(+)，多好看啊）

4、 创建和删除约束

如果你经常需要编辑/创建/删除表的约束，你可以这么写：

添加主键

Language: MySQL

ALTER TABLE 'TABLE_NAME'  
DROP PRIMARY KEY, ADD PRIMARY KEY ('COLUMN_NAME');

删除主键

Language: MySQL

ALTER TABLE 'TABLE_NAME'  DROP PRIMARY KEY;

在添加主键的时候，我们需要先删除已经添加的主键然后将新主键加到新字段上。否则将不允许加主键。

5、 ORDER BY，HAVING子句

Order by用于将数据按照指定字段重新整理，而having则用于过滤结果集。当你需要从一个大数据库中获取特定信息时这会非常有用：

Language: MySQL

Select * from TABLE_NAME 
group by dept  having salary > 10000;

上述查询语句中会返回根据部门进行组织且工资大于10000的员工数据。值得注意的是当我们使用没有“order by”的“having”子句时，它更像“where”子句(子乌注：对于oracle用户来说（我忘记sqlserver是不是也是这样了……）需要注意的地方是，mysql的group by可以不用将字段列明！而且结果也不太一样！确实更像where一些)。

6、 添加索引和搜索查询

为一个字段创建索引毫无疑问会减少（原文是increase……）对数据库的查询，而索引是基于字段基础创建。例如，在我们的示例表“emp”，如果我们大部分获取数据的查询都是使用字段id，那为字段id建一个索引会是个明智的决定。

查询时，MySQL首先在已建立的索引中搜索ID，如果找不到则会在表级上运行查询。所以，正确地建立索引将提高这个过程的速度。

为了创建索引，可以使用以下语句：

建表时创建索引

Language: MySQL

CREATE TABLE  'emp' (
Name varchar(45),
  'id' INT, INDEX(ID)
)

子乌注：如果已经是主键则无须创建索引。

为已存在的表创建索引

Language: MySQL

Create Index id_index on emp(ID);

7、 复合函数查询

复合函数用于对从数据库取得的数据进行数学运算。这些函数包括：sum(总和)、count(总数)等。例如，如果你想计算工作于特定部门的雇员数，你可以这么写：

Language: MySQL

select distinct name,count(dept) as cnt from emp
 group by dept  order by cnt DESC;

上述语句会返回员工姓名及部门数，并会根据返回的总数对数据进行逆序组合。(子乌注：对oracle用户来说（呃……我主要用oracle工作），order by的参数得是count(dept)而非cnt)

8、 变更及修改表字段

表创建后可能会频繁修改。通过下面的语句我们可以看到如何给一个已存在的表添加/修改字段。

添加字段

Language: MySQL

Alter table 'emp' add column salary varchar(45);

修改字段

Language: MySQL

Alter table 'emp' modify column salary int(10);

重命名字段

Language: MySQL

Alter table 'emp' change salary salary2 varchar(45);

删除字段

Language: MySQL

Alter table 'emp' drop column salary;

子乌注：对oracle用户来说（我又来了……），尽量不要随意修改表结构，否则与该表相关的所有对象（存储、触发器、索引、函数等等）全部会失效，需要重新编译。

9、 使用非重复值创建新表

假设你的数据库表中已经有了上千的数据，而你发现数据录入并不正确，存在着重复记录。现在你打算清理所有的垃圾并创建一个新表。让我们看看该如何做吧：

Language: MySQL

create table 'emp2' as select * from emp
 where 1 group by 'lastname';

嗯，现在创建了一个只有唯一“last name”的雇员信息的新表“emp2”。你可以使用不同的接口来排序你的表。

10、 复制数据库并优化表

很多时候你的应用需要进行计划备份并通过邮件发送或将其存放在安全的地方。你可以写个脚本执行并设置一个cron来做这事情。这会节省大量用于备份你的数据的工作并避免发生无可挽回的错误。让我们看看你该如何创建数据库的备份并同时优化表。

Language: Bash

mysqldump –h localhost –u username –ppassword databasename > backup_file.sql

这会创建一个sql dump文件，可以将其再次导入以恢复数据库。

11、 优化表

在MySQL中优化表相当的简单，就像标题所说的那样：

Language: MySQL

Optimize table TABLE_NAME

结论

你已经学会了这些在每天编码中都会用到的最常用的MySQL语句。你可以针对自己的工作场景来改进他们。