mysql基本操作一览

前言

因为有些不常用的操作总是用完就忘，每次在使用时又要上网查，查到的还很难第一条就是适用的，所以自己总汇下mysql的一些基础操作，方便查阅也加深记忆。
这里介绍所有基础的表操作，包括，修改表结构，增删改查，表联结，分组等等。注意，大部分情况下只介绍写法，而不介绍功能。
关于存储过程、索引等会在以后介绍

创建表

CREATE TABLE [IF NOT EXISTS] customers
(
  cust_id      int       NOT NULL AUTO_INCREMENT,
  cust_name    char(50)  NOT NULL ,
  cust_address char(50)  NULL [DEFAULT 'SHANGHAI'],
  cust_city    char(50)  NULL ,
  cust_state   char(5)   NULL ,
  cust_zip     char(10)  NULL ,
  cust_country char(50)  NULL ,
  cust_contact char(50)  NULL ,
  cust_email   char(255) NULL ,
  PRIMARY KEY (cust_id)
) ENGINE=InnoDB;

• PRIMARY KEY定义主键
  主键值必须唯一且非空，如果使用多个列，那么这些列的组合值必须唯一，创建多个列组成的主键，使用逗号进行分隔：
  PRIMARY KEY（order_num, order_item）
• AUTO_INCREMENT
  每个表只允许一个AUTO_INCREMENT列，而且它必须被索引（主键自动建立索引）。你也可以自己手动插入一个值代替AUTO_INCREMENT给出的值，不过这样做会产生一些变化：Auto_Incerment的值将基于当前表最大的值开始计数加1.
  使用SELECT last_insert_id()可获得最后一个AUTO_INCREMENT的值。
• 创建新表时，指定的表名必须不存在
• NULL值就是没有值或者缺值，NULL代表允许有空值，NOT NULL代表不允许。
  注*：空字符串‘’不是NULL。可以作为有效的值插入。
• 默认值DEFAULT
  使用默认值定义在NULL/NOT NULL的定义之后.
• ENGINE（引擎）
  最后ENGINE指定的是mysql的引擎，如果不显示指定，mysql使用默认引擎，你可以更改mysql的配置修改默认引擎。以下是3个比较常见的引擎：
  1、InnoDB：可开的事务处理引擎，但不支持全文本搜索
  2、MyISAM：支持全文本搜索，但不支持事务管理。
  3、MEMORY：功能等同与MyISAM，但由于数据存储在内存中，速度很快（适合临时表）
  注*：使用一种引擎的表不能引用使用另一种引擎的表的外键。

修改表结构

为更新表定义，使用“ALTER TABLE 表名 ”语句

添加列

ALTER TABLE usertable
ADD user_age INT;

删除列

ALTER TABLE usertable
DROP COLUMN user_age;

定义外键

ALTER TABLE usertable 
ADD CONSTRAINT user_id FOREIGN KEY (order_id)#有错误，暂跳过

删除表

DROP TABLE usertable;

删除表没有确认，也不能撤销和恢复。

重命名表

RENAME TABLE usertable to usertable1;

使用逗号分隔可重命名多个表。

修改列类型及名称

#修改列类型,不更改名称
alter table usertable modify address char(40);
#或者这样，可以更改名称
alter table usertable change address myaddress char(40);

SELECT语句

子句顺序：

[SELECT] [FROM] [WHERE] [GROUP BY] [HAVING] [ORDER BY] [LIMIT];

where

#基本用法
select * from user where user_id=1;
#使用BETWEEN
select * from user where user_id [NOT] BETWEEN 1 AND 5;
#检查空值
select * from user where username IS NULL;
#组合判断条件,使用AND、OR，注意在组合时,AND的优先级是高于OR的
select * from user where username='jack' AND user_id=1;
#in操作符的使用
select * from user where user_id [NOT] IN (1,2);

order by

#可以指定显示排序，desc表示按username降序排序
select * from user order by username desc
#也可以指定两个参数，优先按第一个参数排序，当按第一个参数无法辨别顺序前
#后是（值相同），按第二个参数排序
select * from user order by username DESC，user_id ASC; 

limit

select * from user limit 0,5;

limit第一个参数是开始检索的行号，第二个是检索的行数。第一行是0。也可以只带一个参数指定要检索的行数

GROUP BY

分组允许把数据分为多个逻辑组，以便能对每个组进行聚集计算

SELECT user_id,count(*) as num from user group by user_id;

注*：

• GROUP BY子句可以包含任意数目的列。这使得能对分组进行嵌套，为数据分组提供更细致的控制
• 如果在GROUP BY子句中嵌套了分组，数据将在最后规定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算。
• GROUP BY子句中列出的每个列都必须是检索列或有效的表达式
• 除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给出<————注意这条
• 如果分组列中具有NULL值，则NULL将作为一个分组返回。
• GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前

过滤分组

select user_id,count(*) as num from usertable group by user_id HAVING count(*) >= 2;

WHERE在数组分组钱进行过滤，HAVING在数据分组后进行过滤（过滤分组）。WHERE排除的数据不会出现在分组中。

INSERT插入

#安全的插入方法:指定列名
INSERT INTO customers
(cust_id, cust_name, cust_address)
VALUES
(10001, 'Coyote Inc.', '200 Maple Lane');
#插入多行数据可以使用逗号分隔，例如：VALUES(...),(...);

还可以利用子查询来插入检索的数据

INSERT INTO customers
(cust_id, cust_name, cust_address)
SELECT
cust_id, cust_name, cust_address
FROM custnew;

这样会把所有从custnew检索到的行插入customers，Mysql不关心select返回的列名，只根据列出顺序匹配列。

UPDATE更新

UPDATE customers
SET cust_email = 'xie@qq.com',
    cust_name = 'xie'
WHERE cust_id = 1;

DELETE删除

DELETE FROM customers WHERE cust_id = 1006;

如果想删除表的所有行，不要使用DELETE（逐行删除）而是使用TRUNCATE TABLE（删除原来的表并重新创建一个表）

表联结

内联结

INNER JOIN

SELECT customers.cust_id, order.order_num FROM customers INNER JOIN orders ON customers.cust_id =orders.cust_id;

内联结选择表的笛卡尔积中符合ON后条件的行，如果联结了多个表，则从左至右依次联结。

左联结、右联结

LEFT OUTER JOIN、RIGHT OUTER JOIN

SELECT customers.cust_id, order.order_num FROM customers INNER JOIN orders ON customers.cust_id =orders.cust_id;

左联结额外选择左边表没有被选中的行。
右联结额外选择右边表没有被选中的行。

组合查询

UNION

SELECT vend_id,prod_id 
FROM products
WHERE prod_price <= 5
UNION
SELECT vend_id,prod_id 
FROM products
WHERE vend_id IN (1001,1002);

显示所有结果，但不包括重复的行

UNION ALL

包括重复的行