Mysql笔记

 1 功能介绍

1) An EXPLAIN statement to show how the optimizer resolves a query.

2) Support for up to 64 indexes per table (32 before MySQL 4.1.2). Each index may consist of 1 to 16 columns or parts of columns. The maximum index width is 1000 bytes (767 forInnoDB); before MySQL 4.1.2, the limit is 500 bytes. An index may use a prefix of a column forCHAR, VARCHAR, BLOB, or TEXT column types.

3) The Connector/J interface provides MySQL support for Java client programs that use JDBC connections. Clients can be run on Windows or Unix. Connector/J source is available.

4) sql 扩展:

/*! MySQL-specific code */

 

2. tutorial

. SELECT VERSION(), CURRENT_DATE;

. SELECT SIN(PI()/4), (4+1)*5;

. SELECT VERSION(); SELECT NOW();

. select user();

. mysql> SELECT ->USER() -> \c

. mysql> GRANT ALL ON menagerie.* TO 'your_mysql_name'@'your_client_host';

. LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;

. SELECT * FROM pet WHERE birth >= '1998-1-1';

. SELECT name, birth, CURDATE(),
    -> (YEAR(CURDATE())-YEAR(birth))
    -> - (RIGHT(CURDATE(),5)<RIGHT(birth,5))
    -> AS age
    -> FROM pet ORDER BY name;

. SELECT 1 IS NULL, 1 IS NOT NULL;

. In MySQL, 0 or NULL means false and anything else means true. The default truth value from a boolean operation is1.

"_"to match any single character and "%" to match an arbitrary number of characters (including zero characters). In MySQL, SQL patterns are case-insensitive by default.

. SELECT * FROM pet WHERE name REGEXP '^.....$';

. SELECT * FROM pet WHERE name REGEXP '^.{5}$';

. SELECT DATABASE();

. mysql <batch-file > mysql.out

. mysql> SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;

. mysql> SELECT * FROM shop WHERE price=@min_price OR price=@max_price;

. CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL, day INT(2) UNSIGNED ZEROFILL);

 

3. Mysql programs

. mysql --host=localhost --user=myname --password mydb

. mysql --protocol=TCP

. mysql --host=remote.example.com --port=13306

. mysqld_safe is the recommended way to start amysqld server on Unix. mysqld_safe adds some safety features such as restarting the server when an error occurs and logging runtime information to an error log file. A description of error logging is given later in this section.

. mysqld_multi is designed to manage severalmysqld processes that listen for connections on different Unix socket files and TCP/IP ports. It can start or stop servers, or report their current status.

. mysqld_multi start 17

. mysql> help me

. mysql> source file_name
. mysql> \. file_name
. Some query results are much more readable when displayed vertically, instead of in the usual horizontal table format. Queries can be displayed vertically by terminating the query with \G instead of a semicolon

 

4. 优化Sql语句
方法：

. 为where条件后面的列创建index, 为类似的查询语句创建index, 为了节省硬盘空间, index尽量少。

. 尽量减少全表扫描，尤其是大的表.

* 第一步查看"EXPLAIN"计划。

* 调整Mysql缓存大小

* 尽量where中少用括号

* 减少or的使用，可以考虑使用union

* 把多个insert语句合并成一个insert语句加value list

* 尽量为列设定default值

* truncate table比delete from table_name快，但不是transation safe.

* 减少使用table-level和column-level privileges可以提高性能

*

7.2.4. Optimizing INFORMATION_SCHEMA Queries