MySQL SQL语句大全

MySQL SQL语句大全

关于数据库

1、创建数据库

CREATE DATABASE dbname;

2、删除数据库

DROP DATABASE dbname;

3、备份数据库

1) 备份MySQL数据库的命令

MYSQLDUMP -H hostname -U username -P password databasename > backupfile.sql

2) 直接将MySQL数据库压缩备份

MYSQLDUMP -H hostname -U username -P password databasename | GZIP > backupfile.sql.gz

3) 同时备份多个MySQL数据库

MYSQLDUMP -H hostname -U username -P password -databases databasename1 databasename2 >multibackupfile.sql

4) 仅仅备份数据库结构

MYSQLDUMP -no-data -databases databasename1 databasename2 > mutibackupfile.sql

5) 备份服务器上所有数据库

MYSQLDUMP -all-databases > allbackupfile.sql

6) 还原压缩的MySQL数据库

GUNZIP < backfile.sql.gz | MYSQL -U username -P password databasename

7) 将数据库转移到新服务器

MYSQLDUMP -H hostname -U username -P password databasename | MYSQL -host=*.*.*.* -C databasename

关于表 

1、建表

1) 创建新表

CREATE TABLE newtable(col1 TYPE1[not  null] [primary key], col2 TYPE2[not  null] ...);

2) 根据已有的表创建新表

CREATE TABLE newtable LIKE oldtable;

3) 选取已有表中的某些列建表

CREATE TABLE newtable AS SELECT col1,col2... from oldtable definition only;

2、删除表

DROP TABLE tablename;

3、查看表的字段信息

DESC tablename;

4、查看表的所有信息

SHOW CREATE TABLE tablename;

5、修改表名

ALTER TABLE oldtable RENAME TO newtable;

6、改变表项

1) 增加一列

ALTER TABLE tablename ADD COLUMN col TYPE;

2) 删除一列

ALTER TABLE tablename DROP 

3) 添加主键

ALTER TABLE tablename ADD PRIMARY KEY(col);

4) 删除主键

ALTER TABLE tablename DROP PRIMARY KEY(col);

5) 修改列名

ALTER TABLE tablename CHANGE col1 col2 TYPE;

6) 修改列属性

ALTER TABLE tablename MODIFY col TYPE;

7、对表内数据进行操作

1)查找：SELECT* FROM table1 WHERE 范围

2)插入：INSERT INTO table(field1,field2) VALUES(value1,value2)

3)删除：DELETE FROM table WHERE 范围

4)更新：UPDATE table SET field1=value1WHERE范围

5)查找：SELECT *FROM table WHERE field1 LIKE’%value1%’ ---like的语法很精妙，查资料!

6)排序：SELECT * FROM table ORDER BY field1,field2 [DESC]

7)总数：SELECT COUNT *AS totalcount FROM table

8)求和：SELECT SUM(field1)AS sumvalue FROM table

9)平均：SELECT AVG(field1) AS avgvalue FROM table

a)最大：SELECT MAX(field1) AS maxvalueFROM table

b)最小：SELECTMIN(field1) AS minvalueFROM table

关于索引

1、创建索引

1)  ALTER TABLE用于创建普通索引、UNIQUE索引或PRIMARY KEY索引(索引名可选，缺省为第一索引列名)

ALTER TABLE tablename ADD {INDEX | UNIQUE | PRIMARY KEY} [indexname] (columnlist1, columnlist2...);

2)  CREATE INDEX 用于增加普通索引或UNIQUE索引

CREATE [UNIQUE] INDEX index_name ON tablename (columnlist1, columnlist2...);

【注】： 在创建索引时，可以规定索引能否包含重复值。如果不包含，则索引应该创建 为PRIMARY KEY或UNIQUE索引。对于单列惟一性索引，这保证单列不包含重 复的值。对于多列惟一性索引，保证多个值的组合不重复。

PRIMARY KEY索引和UNIQUE索引非常类似。事实上，PRIMARY KEY索引仅是一 个具有名称PRIMARY的UNIQUE索引。这表示一个表只能包含一个PRIMARY KEY， 因为一个表中不可能具有两个同名的索引。

2、 删除索引

DROP INDEX indexname ON tablename;

ALTER TABLE tablename DROP INDEX indexname;

ALTER TABLE tablename DROP PRIMARY KEY;

【注】： 前两条语句是等价的，删除tablename的索引indexname。而第三条语句只在删除PRIMARY KEY 索引时使用，因为在一个表只可能有唯一一个PRIMARY KEY 索引，因此不需要指定索引名。如果没有创建PRIMARY KEY索引，但表中具有 一个或多个UNIQUE索引，则MySQL将删除第一个UNIQUE索引。如果从表中 删除了某列，则索引会受到影响。对于多列组合的索引，如果删除其中的某 列，则该列也会从索引中删除。如果删除组成索引的所有列，则整个索引将被删除。

3、查看索引

1)  SHOW INDEX FROM tablename;

2) SHOW KEYS FROM tablename;

【注】：

 ·Table 表的名称。

· Non_unique如果索引不能包括重复词，则为0。如果可以，则为1。

· Key_name索引的名称。

· Seq_in_index索引中的列序列号，从1开始。

· Column_name列名称。

· Collation列以什么方式存储在索引中。在MySQL中，有值‘A’（升序） 或NULL（无分类）。

· Cardinality索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或 myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所 以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时， MySQL使用该索引的机会就越大。

· Sub_part如果列只是被部分地编入索引，则为被编入索引的字符的数目。 如果整列被编入索引，则为NULL。

· Packed指示关键字如何被压缩。如果没有被压缩，则为NULL。

· Null如果列含有NULL，则含有YES。如果没有，则该列含有NO。

· Index_type用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）。

· Comment备注

4、什么情况下使用索引

1) 表的主关键字

2) 表的字段唯一约束

3) 直接条件查询的字段 (即SQL中用于条件约束的字段，如where后的条件)

4) 查询中与其它表关联的字段 (字段外键关系)

5) 查询中排序的字段 (排序的字段如果通过索引去访问那将大大提高排序速度)

6) 查询中统计或分组统计的字段

例如：SELECT MAX(col) FROM tablename;

5、什么情况下应不建或少建索引

1) 表记录太少

【说明】：如果一个表只有5条记录，采用索引去访问记录的话，那首先需访问索引表，再通过索引表访问数据表，一般索引表与数据表不在同一个数据块，这种情况下ORACLE至少要往返读取数据块两次。而不用索引的情况下会将所有的数据一次读出，处理速度显然会比用索引快。

2) 经常插入、删除、修改的表

3) 数据重复且分布平均的表字段

【说明】：假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率大约为50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。

4) 经常和主字段一块查询但主字段索引值比较多的表字段

【说明】：如果将过多的字段建立在一个索引中将会增加数据的修改、插入、删除时间。

6、对千万级MySQL数据库建立索引的事项及提高性能的手段

1) 注意事项：

首先，应当考虑表空间和磁盘空间是否足够。我们知道索引也是一种数据，在建立索引的时候势必也会占用大量表空间。因此在对一大表建立索引的时候首先应当考虑的是空间容量问题。

其次，在对建立索引的时候要对表进行加锁，因此应当注意操作在业务空闲的时候进行。

2) 性能调整方面：

首当其冲的考虑因素便是磁盘I/O。物理上，应当尽量把索引与数据分散到不同的磁盘上（不考虑阵列的情况）。逻辑上，数据表空间与索引表空间分开。这是在建索引时应当遵守的基本准则。

其次，我们知道，在建立索引的时候要对表进行全表的扫描工作，因此，应当考虑调大初始化参数db_file_multiblock_read_count的值。一般设置为32或更大。

再次，建立索引除了要进行全表扫描外同时还要对数据进行大量的排序操作，因此，应当调整排序区的大小。

之前，可以在session级别上加大sort_area_size的大小，比如设置为100m或者更大。

以后，如果初始化参数workarea_size_policy的值为TRUE，则排序区从pga_aggregate_target里自动分配获得。

最后，建立索引的时候，可以加上nologging选项。以减少在建立索引过程中产生的大量redo，从而提高执行的速度。

7、MySql在建立索引优化时需要注意的问题

设计好MySql的索引可以让你的数据库飞起来，大大的提高数据库效率。设计MySql索引的时候有一下几点注意：

1) 创建索引

对于查询占主要的应用来说，索引显得尤为重要。很多时候性能问题很简单的就是因为我们忘了添加索引而造成的，或者说没有添加更为有效的索引导致。如果不加索引的话，那么查找任何哪怕只是一条特定的数据都会进行一次全表扫描，如果一张表的数据量很大而符合条件的结果又很少，那么不加索引会引起致命的性能下降。但是也不是什么情况都非得建索引不可，比如性别可能就只有两个值，建索引不仅没什么优势，还会影响到更新速度，这被称为过度索引。

2) 复合索引

比如有一条语句是这样的：select * from users where area=’beijing’ and age=22;

如果我们是在area和age上分别创建单个索引的话，由于mysql查询每次只能使用一个索引，所以虽然这样已经相对不做索引时全表扫描提高了很多效率，但是如果在area、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(area, age,salary)的复合索引，那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引，这被称为最佳左前缀特性。因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。

3) 索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

4) 使用短索引

对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的 列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

5) 排序的索引问题

mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

6) like语句操作

 一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

7) 不要在列上进行运算

select * from users where YEAR(adddate)

8) 不使用NOT IN和操作

NOT IN和操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替，id3则可使用id>3 or id