Mysql基础

虽然这些看起来都比较简单，希望能够通过不断的积累能够帮助到人去减少平时犯的错误。

 

1、统计重复数据

   例如这是统计3月7号到4月20号的金额大于1的且字段‘step’不为空订单，sql如下

SELECT
	o.sn,os.step1 as '步骤',os.service_no,oi.`name`,o.order_amount,o.paymoney,o.member_id
FROM
	order o
LEFT JOIN order_items oi ON o.order_id = oi.order_id
LEFT JOIN self_service os on oi.service_number = os.service_no
 
WHERE
		o.order_amount >= 1
		AND o.create_time / 1000 BETWEEN UNIX_TIMESTAMP('2016-03-07 00:00:00')
		AND UNIX_TIMESTAMP('2016-04-20 23:59:59')
	  os.step1 is not NULL 

    然后现在查出来后，要查出重复数据超过1条的数据，这时可以：

  

select count(*) as c,t.sn from (
SELECT
	o.sn,os.step1 as '步骤',os.service_no,oi.`name`,o.order_amount,o.paymoney,o.member_id
FROM
	order o
LEFT JOIN order_items oi ON o.order_id = oi.order_id
LEFT JOIN self_service os on oi.service_number = os.service_no
 
WHERE
		o.order_amount >= 1
		AND o.create_time / 1000 BETWEEN UNIX_TIMESTAMP('2016-03-07 00:00:00')
		AND UNIX_TIMESTAMP('2016-04-20 23:59:59')
	  os.step1 is not NULL 
) t GROUP BY t.sn HAVING c >1;

 

 

2、过滤重复数据

   2.1 可以通过distinct

  

select distinct sn from order

  2.2也可以通过group by

 

 

select * from order group by sn

   如果group by两个字段以上，如group by sn,name,则表示sn和name两个字段值同时相同时才能合并。

 

 

3、mysql注意项

   3.1  使用 AUTO_INCREMENT使id自增

   3.2  escape转义

     select * from temp where name like ‘\_%’ escape ‘\’;

指定\为转义字符，上面的就可以查询name中包含“_”的数据
 3.3 between v and v2
     大于等于v且小于等于v2select * form temp where age between 20 and 25; 
 3.4concat函数
     字符串连接select concat(name, ‘-eco’) from temp;concat和null进行连接，会导致连接后的数据成为null
 3.5唯一约束和主键约束

唯一约束：
是指定table的列或列组合不能重复，保证数据的唯一性。虽然唯一约束不允许出现重复的值，但是可以为多个null，同一个表可以有多个唯一约束，多个列组合的约束。在创建唯一约束的时候，如果不给唯一约束名称，就默认和列名相同，唯一约束不仅可以在一个表内创建，而且可以同时多表创建组合唯一约束。MySQL会给唯一约束的列上默认创建一个唯一索引；

主键约束：
相当于唯一约束+非空约束的组合，主键约束列不允许重复，也不允许出现空值；如果的多列组合的主键约束，那么这些列都不允许为空值，并且组合的值不允许重复。每个表最多只允许一个主键，建立主键约束可以在列级别创建，也可以在表级别上创建。MySQL的主键名总是PRIMARY，当创建主键约束时，系统默认会在所在的列和列组合上建立对应的唯一索引。

 

4、mysql复制表

    

• 使用 SHOW CREATE TABLE 命令获取创建数据表(CREATE TABLE) 语句，该语句包含了原数据表的结构，索引等。
• 复制以下命令显示的SQL语句，修改数据表名，并执行SQL语句，通过以上命令 将完全的复制数据表结构。
• 如果你想复制表的内容，你就可以使用 INSERT INTO ... SELECT 语句来实现

 

1、SHOW CREATE TABLE runoob_tbl

2、mysql> INSERT INTO clone_tbl (runoob_id,
    ->                        runoob_title,
    ->                        runoob_author,
    ->                        submission_date)
    -> SELECT runoob_id,runoob_title,
    ->        runoob_author,submission_date
    -> FROM runoob_tbl;

 

 

5、mysql临时表

   MySQL 临时表在我们需要保存一些临时数据时是非常有用的。临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间。

   例子：复制城市表的一张临时表：

   5.1、show create table tb_city，得到：

   

CREATE TABLE `tb_city` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `CityName` varchar(50) DEFAULT NULL,
  `ZipCode` varchar(50) DEFAULT NULL,
  `ProvinceID` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=3360 DEFAULT CHARSET=utf8

 

 

    5.2、创建临时表

 

CREATE TEMPORARY TABLE `tb_city_temp` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `CityName` varchar(50) DEFAULT NULL,
  `ZipCode` varchar(50) DEFAULT NULL,
  `ProvinceID` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=3360 DEFAULT CHARSET=utf8

   

 

   5.3插入数据

  

insert INTO tb_city_temp (ID,CityName,ZipCode,ProvinceID) select 
ID,CityName,ZipCode,ProvinceID from tb_city;

  

表的设计原则】
使用临时表一般都意味着性能比较低，特别是使用磁盘临时表，性能更慢，因此我们在实际应用中应该尽量避免临时表的使用。

如果实在无法避免，也应该尽量避免使用磁盘临时表。

常见的方法有：

1）创建索引：在ORDER BY或者GROUP BY的列上创建索引，这样可以避免使用临时表；
2）分拆很长的列，可以避免使用磁盘临时表：一般情况下，TEXT、BLOB，大于512字节的字符串，基本上都是为了显示信息，而不会用于查询条件，因此表设计的时候，应该将这些列独立到另外一张表。

【如何判断使用了临时表？】
使用explain查看执行计划，Extra列看到Using temporary就意味着使用了临时表。

 

mysql临时表应用场景：相关链接：http://uule.iteye.com/blog/2037913

在SQL Server的性能调优中,如何在一段需要长时间的代码或被频繁调用的代码中处理临时数据集,表变量和临时表是两种选择。

临时表是有它的作用的,但不能滥用,当一个查询涉及很多表时,查询的笛卡尔积是非常大的,单纯的用索引可能解决不了查询时间的问题.这个时候可以用临时表,将一个查询分成多步完成,减少输出查询结果时的笛卡尔积,会对提高查询效率有很大的帮助.比如
查询的主表有上百W条数据,条件都是针对主表字段,然后1:M:M关联两个子表,如果直接关联查询,那么笛卡尔积可能为100W*M*100W*n*100W,可能就会造成耗时很长或查询超时.用临时表先筛选出主表的数据,比如筛选取主表后还有1000条数据满足条件要求,这时再用临时表来关联两个子表,笛卡尔积就会下降一个几何积,会明显提高查询速度.
什么时候用临时表,取决于查询的复杂成度和数据量,简单的查询用临时表就得不偿失了. 

如利用临时表来组织数据，比普通表会更加的简洁、紧凑。这主要是在临时表中可以实现很多的特性。如可以进行预处理计算。如当发现基本标中的索引不怎么合适，也可以在数据库临时表中重新创建索引以优化原有的索引。特别是当需要多次访问某个表或者视图的时候，利用临时表来组织数据是一个提高效率的好方法。即使只是一个简单的查询，其效率的提升也是很明显的。为此，使用临时表最明显的一个好处就似乎可以提高数据库的性能，特别是查询的性能。

　　另外使用临时表还可以减少中间表的产生。在进行某些操作时，本来往往需要一些中间表的帮助才可以完成。而现在数据库管理员可以让数据库在需要时自动生成中间表，并在用完后进行自动删除。如此的话，中间表的建立与删除就不需要数据库管理员人为的管理了。所以，使用临时表可以减少数据库系统中的垃圾表，也可以降低用户的工作量。

 

 

6、mysql索引

   

一、索引分单列索引和组合索引
单列索引：即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。
组合索引：即一个索包含多个列。

二、介绍一下索引的类型
　　1、普通索引
　　这是最基本的索引，它没有任何限制。它有以下几种创建方式：
　　（1）创建索引：CREATE INDEX indexName ON tableName(tableColumns(length));如果是CHAR，VARCHAR类型，length可以小于字段实际长度;如果是 BLOB 和 TEXT 类型，必须指定 length，下同。
　　（2）修改表结构：ALTER tableName ADD INDEX [indexName] ON (tableColumns(length))
　　（3）创建表的时候直接指定：CREATE TABLE tableName ( [...], INDEX [indexName] (tableColumns(length)) ;

　　2、唯一索引
　　它与前面的"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：
　　（1）创建索引：CREATE UNIQUE INDEX indexName ON tableName(tableColumns(length))
　　（2）修改表结构：ALTER tableName ADD UNIQUE [indexName] ON (tableColumns(length))
　　（3）创建表的时候直接指定：CREATE TABLE tableName ( [...], UNIQUE [indexName] (tableColumns(length));

　　3、主键索引
　　它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引：CREATE TABLE testIndex(i_testID INT NOT NULL AUTO_INCREMENT,vc_Name VARCHAR(16) NOT NULL,PRIMARY KEY(i_testID)); 当然也可以用 ALTER 命令。记住：一个表只能有一个主键。

　　4、全文索引
　　MySQL 从 3.23.23 版开始支持全文索引和全文检索。
　　删除索引的语法：DROP INDEX index_name ON tableName；
三、单列索引和组合索引
　　为了形象地对比两者，再建一个表：
　　mysql > CREATE TABLE myIndex ( i_testID INT NOT NULL AUTO_INCREMENT,
vc_Name VARCHAR(50) NOT NULL,
vc_City VARCHAR(50) NOT NULL,
i_Age INT NOT NULL, i_SchoolID INT NOT NULL,
PRIMARY KEY (i_testID)
);
　　在这 10000 条记录里面 7 上 8 下地分布了 5 条 vc_Name="erquan" 的记录，只不过 city,age,school 的组合各不相同。
　　来看这条 T-SQL：SELECT i_testID FROM myIndex WHERE vc_Name='erquan' AND vc_City='郑州' AND i_Age=25;
首先考虑建单列索引：
　　在 vc_Name 列上建立了索引。执行 T-SQL 时，MYSQL 很快将目标锁定在了 vc_Name=erquan 的 5 条记录上，取出来放到一中间结果集。在这个结果集里，先排除掉 vc_City 不等于"郑州"的记录，再排除 i_Age 不等于 25 的记录，最后筛选出唯一的符合条件的记录。
　　虽然在 vc_Name 上建立了索引，查询时MYSQL不用扫描整张表，效率有所提高，但离我们的要求还有一定的距离。同样的，在 vc_City 和 i_Age 分别建立的单列索引的效率相似。
　　为了进一步榨取 MySQL 的效率，就要考虑建立组合索引。就是将 vc_Name,vc_City，i_Age 建到一个索引里：
　　ALTER TABLE myIndex ADD INDEX name_city_age (vc_Name(10),vc_City,i_Age);
　　建表时，vc_Name 长度为 50，这里为什么用 10 呢？因为一般情况下名字的长度不会超过 10，这样会加速索引查询速度，还会减少索引文件的大小，提高 INSERT 的更新速度。
　　执行 T-SQL 时，MySQL 无须扫描任何记录就可以找到唯一的记录！！
　　肯定有人要问了，如果分别在 vc_Name,vc_City，i_Age 上建立单列索引，让该表有 3 个单列索引，查询时和上述的组合索引效率一样吗？大不一样，远远低于我们的组合索引。虽然此时有了三个索引，但 MySQL 只能用到其中的那个它认为似乎是最有效率的单列索引。
　　建立这样的组合索引，其实是相当于分别建立了
　　vc_Name,vc_City,i_Age
　　vc_Name,vc_City
　　vc_Name

　　这样的三个组合索引！为什么没有 vc_City，i_Age 等这样的组合索引呢？这是因为 mysql 组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引，下面的几个 T-SQL 会用到：
　　SELECT * FROM myIndex WHREE vc_Name="erquan" AND vc_City="郑州"
　　SELECT * FROM myIndex WHREE vc_Name="erquan"
　　而下面几个则不会用到：
　　SELECT * FROM myIndex WHREE i_Age=20 AND vc_City="郑州"
　　SELECT * FROM myIndex WHREE vc_City="郑州"

四、使用索引
　　到此你应该会建立、使用索引了吧？但什么情况下需要建立索引呢？一般来说，在 WHERE 和 JOIN 中出现的列需要建立索引，但也不完全如此，因为 MySQL 只对 <，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE(后面有说明)才会使用索引。
　　SELECT t.vc_Name FROM testIndex t LEFT JOIN myIndex m ON t.vc_Name=m.vc_Name WHERE m.i_Age=20 AND m.vc_City='郑州' 时，有对 myIndex 表的 vc_City 和 i_Age 建立索引的需要，由于testIndex 表的 vc_Name 开出现在了 JOIN 子句中，也有对它建立索引的必要。
　　刚才提到了，只有某些时候的 LIKE 才需建立索引？是的。因为在以通配符 % 和 _ 开头作查询时，MySQL 不会使用索引，如 SELECT * FROM myIndex WHERE vc_Name like'erquan%'会使用索引，而 SELECT * FROM myIndex WHEREt vc_Name like'%erquan' 就不会使用索引了。

五、索引的不足之处
　　上面说了那么多索引的好话，它真的有像传说中那么优秀么？当然会有缺点了。
　　1、虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行 INSERT、UPDATE 和DELETE。因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件。(索引适用于不需要经常修改数据的数据库中)
　　2、建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。
　　讲了这么多，无非是想利用索引提高数据库的执行效率。不过索引只是提高效率的一个因素。如果你的MySQL 有大数据的表，就需要花时间研究建立最优秀的索引或优化查询语句。

 7、mysql存储引擎

   

2.1、MyISAM
MyISAM表是独立于操作系统的，这说明可以轻松地将其从Windows服务器移植到Linux服务器；每当我们建立一个MyISAM引擎的表时，就会在本地磁盘上建立三个文件，文件名就是表明。例如，我建立了一个MyISAM引擎的tb_Demo表，那么就会生成以下三个文件：

1.tb_demo.frm，存储表定义；
2.tb_demo.MYD，存储数据；
3.tb_demo.MYI，存储索引。

MyISAM表无法处理事务，这就意味着有事务处理需求的表，不能使用MyISAM存储引擎。MyISAM存储引擎特别适合在以下几种情况下使用：

1.选择密集型的表。MyISAM存储引擎在筛选大量数据时非常迅速，这是它最突出的优点。
2.插入密集型的表。MyISAM的并发插入特性允许同时选择和插入数据。例如：MyISAM存储引擎很适合管理邮件或Web服务器日志数据。

2.2、InnoDB

InnoDB是一个健壮的事务型存储引擎，这种存储引擎已经被很多互联网公司使用，为用户操作非常大的数据存储提供了一个强大的解决方案。InnoDB是作为默认的存储引擎。InnoDB还引入了行级锁定和外键约束，在以下场合下，使用InnoDB是最理想的选择：

1.更新密集的表。InnoDB存储引擎特别适合处理多重并发的更新请求。
2.事务。InnoDB存储引擎是支持事务的标准MySQL存储引擎。
3.自动灾难恢复。与其它存储引擎不同，InnoDB表能够自动从灾难中恢复。
4.外键约束。MySQL支持外键的存储引擎只有InnoDB。
5.支持自动增加列AUTO_INCREMENT属性。

一般来说，如果需要事务支持，并且有较高的并发读取频率，InnoDB是不错的选择。

   

MyISAM 适合于一些需要大量查询的应用，但其对于有大量写操作并不是很好。甚至你只是需要update一个字段，整个表都会被锁起来，而别的进程，就算是读进程都无法操作直到读操作完成。另外，MyISAM 对于 SELECT COUNT(*) 这类的计算是超快无比的。

InnoDB 的趋势会是一个非常复杂的存储引擎，对于一些小的应用，它会比 MyISAM 还慢。他是它支持“行锁” ，于是在写操作比较多的时候，会更优秀。并且，他还支持更多的高级应用，比如：事务。