MySql基础教程一（查询，插入，更新，删除，创建）

我的学习资料是《MySql必知必会》，被称为sql入门经典，PDF版下载点我。
相关链接：
MySql基础教程二（视图，存储过程，游标，触发器，事务，权限，数据类型）

热身

use 数据库名;show 数据库名或者表名;describe 表名;

查询

过滤，排序，控制显示行数

select * from testtable where cust_id between 1 and 10 order by cust_name desc, cust_id limit 5;
1，排序时默认升序，desc是倒序，在mysql中order by排序时不区分大小写，a和A是一样的。
2，过滤用的between and语句，其实还有比如in (1,2,3)语句，MySQL 支 持 使 用 NOT 对 IN、BETWEEN 和 EXISTS 子句取反。
3，limit还有一种写法是limit 3,4，意思是从第3行开始显示4行。
4，不等于有!=和<>两种写法。
5，当where后边同时有and和or时，and的优先级更高，尽量用括号指明优先级。
6，is null判断是不是空。

字串匹配

select * from testtable where cust_name like 'pa%';
1，like通配符，%是匹配任意个字符包括0个，_是匹配一个字符。like是区分大小写的，同时注意like匹配不到null。
select* from testtable where cust_name regexp '.00';
2，regexp正则，点.表示匹配任意一个字符，所以类似100和200都会被返回。但是注意它和like是不一样的，比如所匹配的字符都是‘100’，like只会返回一模一样的，而regexp只要包含100的都会返回，就是说like匹配整个串，而regexp匹配子串。regexp默认不区分大小写，后边加binary以区分大小写。匹配特殊字符的话前边加 \\ 。
regexp 'qq|ee'; 中间这个 | 是 or 的意思。
regexp '[123]aaa; [123]表示匹配1或2或3。而[^123]表示匹配除这些字符外的东西。[0-9]或[a-z]表示范围。
regexp '^[0-9\\.]'; ^在这里表示匹配串的开始，意思是串的第一个字符为 . 或者数字时才匹配。同理 $ 表示串的结尾。

拼接函数，日期函数

select concat(RTrim(name), '(', country, ')') as vend_title from testtable;
concat()函数是把两列拼接起来，RTrim()函数是去掉字串右边的空格，显示如下，第二列被()包起来了：

mysql中的日期存储为2014-10-01 10:21:34这样的格式，假设它的字段名为cust_date，因此检索指定日期范围内的方法是：
... where date(cust_date) between '2017-01-01' and '2017-01-05';
类似的函数还有month(), year()等等，具体的用到了网上搜一下吧，mysql日期、数值处理函数。

分组，聚集函数

select name,vend_id, count(*) as num_prods from products where prod_price >= 10 group by name,vend_id having count(*) >= 2;
注意几点：
1，如果在SELECT中使用表达式，则在GROUP BY子句中要用相同的表达式，不能使用别名。
2，除聚集计算语句外， SELECT语句中的每个列都必须在GROUP BY子句中给出。
3，where是过滤行，发生在分组前，having是过滤组，发生在分组后。
4，如果分组列中具有NULL值，则NULL将作为一个分组返回。
5，AVG()、MAX()、MIN()、SUM()函数忽略列值为NULL的行。MAX和MIN也可以用在时间日期格式，或者字符串上。
6，COUNT()函数如果指定列名，则忽略指定列的值为空的行，但如果COUNT()函数中用的是星号（*），则不忽略。
7，关于distinct，看下边三个语句，判断对错：
select distinct(name) from testtable;
select distinct(name),sex from testtable;
select sex,distinct(name) from testtable;
第一个语句没问题，对name去重；但是第二个语句无法对name去重了，因为有多个字段时，去重是作用在name和sex的整体上，此时应用分组替代；第三个语句会报错，因为distinct要放在开头，但是count(distinct(name))是可以放在后面的，还要注意distinct不能用于count(*)。

子查询

select cust_name, cust_contact from customers where cust_id in (select cust_id                 from orders                 where order_num in (select order_num                                     from orderitems                                     where prod_id ='TNT2'));

注意子查询的执行顺序是由内而外的。

联结表

在联接两个表时，实际上是将第一个表的每一行与第二个表的每一行配对，where子句使得只返回哪些匹配给定条件的行。如果没有where子句，则第一个表中的每行将与第二个表中的每行配对。返回的行数将是第一个表的行数乘以第二个表中的行数（笛卡尔积），所以，尽量保证所有联接都有where子句。
1，下边语句是内联结的一般写法：

select vend_name, prod_name, prod_price from vendors, products where vendors.vend_id = products.vend_id;

更规范的写法如下：

select vend_name, prod_name, prod_price from vendors INNER JOIN products on vendors.vend_id = products.vend_id;

2，自联结（表自己和自己联结）

select p1.prod_id, p1.prod_name  from products as p1, products as p2where p1.vend_id = p2.vend_id and p2.prod_id = 'DTNTR';

它等价于以下子查询语句，但是自联结的速度比子查询快：

select prod_id, prod_name from products where vend_id = (select vend_id                 from products                 where prod_id = 'DTNTR');

3，外联结

select customers.cust_id, orders.order_num from customers left outer join orders on customers.cust_id = order.cust_id;

使用outer join来指明外联结，外联结还必须指明right或left，left是返回左表的所有行，匹配不上右表的显示null。

组合查询union

使用union操作符来组合数条sql查询，比如需要找出价格小于等于5的所有物品的一个列表，而且还需包括供应商1001和1002生产的所有物品，可以使用如下的sql语句：

select vend_id, prod_id, prod_price from products where prod_price <= 5 unionselect vend_id, prod_id, prod_price from products where vend_id in (1001, 1002); 

上面使用union的sql语句，也可以使用多条where子句完成：

select vend_id, prod_id, prod_price from products where prod_price <= 5 or vend_idin (1001, 1002)

注意：
1，union的每个查询必须包含相同的列、表达式或聚集函数。
2，union从查询结果集中自动去除了重复的行，如果需要显示重复的行，可以使用union all。
3，在用union组合查询时，只能使用一条order by子句，它必须出现在最后一条select语句之后。

全文本搜索

mysql支持几种基本的数据库引擎，并非所有的引擎支持全文本搜索，比如引擎myisam和innodb，只有myisam引擎支持全文本搜索。全文本搜索比like 和regexp具有更强的控制能力和性能。一般在创建表时启用全文本搜索fulltext，定义之后mysql自动维护该索引，在增加、更新和删除行时，索引随之自动更新：

create table productnotes(note_id int NOT NULL AUTO INCREMENT,prod_id char(10) NOT NULL,note_date datetime NOT NULL,note_text text NULL,     primary key(note_id),fulltext(note_text))

match指定被搜索的fulltext列，against指定要使用的搜索表达式，除非使用BINARY方式，否则全文本搜索不区分大小写。返回的顺序是按照匹配的良好程度进行排序的数据。：

select note_text from productnotes where match(note_text) against('rabbit');

有关全文搜索的其他内容，比如查询扩展和布尔方式，可以参考原文，链接在本文最后。

插入

插入一行

insert into customers(cust_name,          cust_address,          cust_city,          cust_contact,          cust_email)values('pope lapew',          '100 main street',         'los angels',          null,          null);

指定列名后，必须在values中给出每个列的值。在插入行时，MySQL将用values列表中的相应值填入列表中的对应项。使用这种语法，还可以省略某些列不赋值使用默认值。insert操作可能很耗时，特别是有许多索引需要更新时，而且他还可能降低select的性能，如果一系列操作中，select操作很重要，则可以使用low_priority关键字，指示mysql 降低insert语句的优先级，比如：insert low_priority into 表名。

插入多行

insert into customers(cust_name,          cust_address,          cust_city) values('pop e lapew',          '100 main street',         'los angels'),          ('m martian',          '42 galaxy way',         'new york');

使用一条insert语句进行多行插入比多条insert语句更快。

把查询结果插入表中

insert into customers(cust_id,          cust_contact,          cust_email,          cust_name,          cust_address,          cust_city)select cust_id,          cust_contact,          cust_email,          cust_name,          cust_address,          cust_city          from custnew;

本例的列名都是一样的，实际上列名无所谓，select中的列是按顺序填入被插入表的。

更新和删除数据

更新行

update custnewset cust_name = 'hh',    cust_email = '[e-mail]'where cust_id = 10007;

update语句中where子句，告诉mysql更新哪一行，否则更新所有行。如果在更新过程中，其中一行或多行出现错误，则整个update操作将被取消，已经更新的行将恢复到它们原来的值。如果想要在发生错误的情况下也继续更新，则可以使用ignore关键字，比如：update ignore 表名;

删除行

delete from custnewwhere cust_id = 10007;

注意，如果没有where子句，则delete将删除表中的所有行，但是如果要删除所有行，应该用truncate table语句，因为它更快。

创建和操作表

创建表

CREATE TABLE customers(cust_id int NOT NULL AUTO_INCREMENT,cust_name char(50) NOT NULL,cust_address char(50) NULL,cust_city char(50) NULL,quantity int NOT NULL DEFAULT 1,PRIMARY KEY (cust_id))ENGINE=InnoDB;

注意：
1，创建表时，指定的表不能存在，否则会出错。
2，如果需要指定多个列组成的主键，则语法如下：primary key (order_num,order_item)。
3，每个表只允许一个自动增量auto_increment列，而且它必须被索引。
4，InnoDB是一个可靠的事务处理引擎，它不支持全文本搜索。
MEMORY 在功能上等同于MyISAM，但由于数据存储在内存，所以速度更快（特别适用于临时表）。
MyISAM是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理。

更新表

使用ALTER TABLE语句，但是在理想状态下，当表中存储了数据后就不应该再去更新表了。所以在设计表时需要花费大量的时间去考虑，以便后期不做太大的改动。
添加一列，必须指明数据类型：ALERT TABLE vendors ADD vend_phone CHAR(20);
删除一列：ALERT TABLE vendors DORP vend_phone;

删除表

DORP TABLE 表名; 永久删除表，不可撤销。
RENAME TABLE table1 TO table2; 重命名表。

原文参考：《mysql必知必会》笔记（二）