mysql学习笔记

1，建立表与索引
（1）CREATE的参数与命名
参数：IF NOT EXISTS
作用：要建立的表不存在时才建立表
例子：
CREATE TABLE IF NOT EXISTS Table_Name(Column_Name Data_Type)

命名规范：
表名用复数，字段名称用单数
复合名称中间用下划线
所有数据库对象名称尽量前后保持一致

（2）使用Script建立数据库
把语句复制到文本文件中，存成*.sql

（3）更改现有表的相关信息
更改表名称：
ALTER TABLE customers RENAME customer_table;
更改字段名称：
ALTER TABLE customer_table CHANGE name customer_name VARCHAR(30);
更改字段类型：
ALTER TABLE customer_table CHANGE customer_name VARCHAR(50);
删除表：
DROP TABLE table_name;
删除部分字段：
ALTER TABLE table_name DROP field_name;
删除Primary Key：
ALTER TABLE table_name DROP PRIMARY KEY;
增加字段：
ALTER TABLE table_name ADD column_name data_type;

（4）使用数据库索引
索引可以加快SELECT的速度，但是会降低INSERT、UPDATA、DELETE的速度。
通常选择WHERE子句常出现的字段或者值不会重复的字段（Key）来建立索引。
默认情况下，MySQL自动为表中的Primary Key建立索引。

增加索引：
CREATE INDEX index_name ON table_name (column_list);
或者：
ALTER TABLE table_name ADD INDEX（column_name);

删除索引：
DROP INDEX index_name ON table_name;
或者：
ALTER TABLE table_name DROP INDEX column_name;

2，数据库的增加删除操作
（1）INSERT 插入数据
INSERT INTO的语法如下：
INSERT INTO [LOW_PRIORITY | DELAYED] Tbl_Name [(Column_name,......)] VALUES (expression,...);

其中字符串类型需要用‘’或者“”包围。转义字符：\0,\n,\t,\r,\b,\',\",\\,\%,\_
数字类型，如果在浮点域插于整数值，会自动转换。
如果插入有唯一性的字段（PRIMARY KEY, AUTO_INCREMENT, UNIQUE），发生重复，则INSERT被忽略。

以下REPLACE语句即使遇到唯一性字段重复也会覆盖：
REPLEACE [LOW_PRIORITY | DELAYED] Tbl_Name [(Column_name,......)] VALUES (expression,...);

（2）UPDATE 更新数据
语法：
UPDATE [LOW_PRIORITY] Tbl_Name SET col_name1=expr1, col_name1=expr1,... [WHERE expr] [LIMIT #]
LIMIT #限制最多只有#个数据行更新。

（3）DELETE 删除数据
语法：
DELETE [LOW_PRIORITY] FROM tbl_name [WHERE expr] [LIMIT #]

如果不加上WHERE，整个表被删除
可以先用SELECT查看有多少笔数据可以删除

DELETE只是将数据的连接删除，空间实际上没有从硬盘释放。可以用下列命令释放：
OPTIMIZE TABLE tbl_name;

3，表达式与相关函数
(1)常用数学函数：
ABS(X)
SIGN(X)
MOD(N,M)
FLOOR(X)
CEILING(X)
ROUND(X)
ROUND(X,D)：四舍五入到小数第D位
EXP(X)
LOG(X)：求ln（x）
LOG10(X)
POW(X,Y) OR POWER(X,Y)
SQRT(X)
PI()
SIN(X),COS(X),TAN(X),ASIN(X),ACOS(X),ATAN(X)
ATAN2(X,Y)：求y/x的ARCTAN函数
COT(X)
RAND(N)
LEAST(N1,N2,N3,...)：在参数间比较，返回最小值
GREATEST(N1,N2,N3,...)
DEGREE(X)：弧度转化为角度
RADIANS(X)：角度转化为弧度
TRUNCATE(X,D)：将X的小数以下D位舍去

（2）比较运算
=(相等)
！= OR <>(不等于)
IS NULL
IS NULL(exp)
IS NOT NULL
BETWEEN A AND B
IN(Value1,Value2,...)
NOT IN(Value1,Value2,...)
COLESCE(LIST)：在LIST的所有项目中，找出第一个不是NULL值的数据
INTERVAL(N,N1,N2,...)：N小于Nk时，返回k，否则返回0.

（3）逻辑运算符
AND,OR,NOT

LIKE运算符和通配符
‘%’：可以替代任何字符
‘_’或‘？’：可替代一个字符
例如：
mysql> select * from customers where address like '*5';

（4）字符串函数
ASCII(X)：返回字符串最左边字符的ASCII值
CONV(N,FROM_BASE,TO_BASE)：N为底的FROM_BASE转成TO_BASE，N为字符串或数字
BIN(N)：十进制N转化为二进制
OCT(N)
HEX(N)
CHAR(N,...)：将ASCII字符N转换为对应的字符
CONCAT(S1,S2,...)
LENGTH(STRING)
INSTR(S1,S2) 或 LOCATE(S1,S2)：s1在s2第一次出现的位置
LPAN(STRING,LEN,PANSTR)：将PANSTR从左边填入，再加上STRING，总长度为LEN
RPAN(STRING,LEN,PANSTR)：将PANSTR从右边填入，再加上STRING，总长度为LEN
LEFT(STR,LEN)：从STR左边取LEN长度的字符串
RIGHT(STR,LEN)
SUBSTRING(STR,POS,LEN)
SUBSTRING_INDEX(STR,DELIM,COUNT)：将STR用DELIM切成数个字符串，取出COUNT返回，COUNT为负数时，从
                                  字符串结尾开始
LTRIM(STR)：删除STR开头的空白
RTRIM(STR)
TRIM([[BOTH | LEADING | TRAILING] [REMSTR] FROM] STR)：删除STR中的REMSTR，默认是BOTH
SPACE(N)：产生N个空白字符
REPLACE(STRING,FROM_STR,TO_STR)：用TO_STR替代FROM_STR
REPEAT(STR,COUNT)：产生COUNT个STR
REVERSE(STR)
INSERT(STR,POS,LEN,NEWSTR)：STR的POS之后插入长度为LEN的NEWSTR
ELT(N,S1,S2,...)：根据N选择S1,S2,...中的第N个返回
FIELD(STRING,S1,S2,...)：STRING与后面的比较，第几个相同则返回几，否则返回0
FIND_IN_SET(STRING,STR_LIST)：找出STRING是STR_LIST中的第几个，STR_LIST之间用‘，’分割
LCASE(STR) 或 LOWER(STR)
UCASE(STR) 或 UPPER(STR)

4，数据库的查询操作
（1）SELECT
语法：
SELECT column1,column2,..FROM table1,table2,.. WHERE conditions [LIMIT #]
可以用‘*’代表所有字段
用LIMIT # 限制只返回前#个结果

如果同一个字段出现在FROM后一个以上的表中时，要显示的指明选取哪个表的哪个字段。
例如：
SELECT order.order_ID FROM orders, customers WHERE customers.tustomer_ID = orders.customer_ID

可以将SQL语句中表的名称用别名（Alias）代替，可以介绍重复的输入，例如：
SELECT order.order_ID FROM orders AS O, customers AS C
WHERE C.tustomer_ID = O.customer_ID

可以将别名与内部函数连用，结果显示在以别名为field_name的列中，例如：
mysql> select max(id) as maxid from customers

（2）ORDER BY
SELECT的结果以建立的时间为序返回。
可以用ORDER BY显示的指定顺序，例如：

参数为字段名称：
SELECT * FROM customers ORDER BY address;

参数为正整数，对应前面SELECT选择的字段：
SELECT * FROM customers ORDER BY 2;

参数为ASC/DESC：指定递增还是递减，默认为递增：
SELECT * FROM customers ORDER BY 2 DESC：

（3）DISTINCT，删掉选取数据字段中的重复内容：
例如：
SELECT DISTINCT address FROM customers

（4）GROUP BY
指定字段，根据该字段分组
例如：
mysql> select address,count(*) from customers group by address;
+---------+----------+
| address | count(*) |
+---------+----------+
| city1   |        2 |
| city2   |        3 |
| city3   |        1 |
| city4   |        1 |
| city5   |        1 |
+---------+----------+
将地址分组，计算数据行有该address的行的个数。

类似COUNT的函数还有：AVG(EXPR),SUM(EXPR),MIN(EXPR),MAX(EXPR),STD(EXPR)（标准差）

（5）HAVING
常与GROUP连用，先过滤数据，再分组，例如：
mysql> select address,count(*) from customers group by address having count(*)=2;

5，数据的导入与导出
（1）数据导入的方法
批量模式导入数据：
  将要执行的插入数据的SQL语句存成文本文件，输入命令：mysql < data.sql
  （以上命令在windows环境下不一定能用）
利用Mysqlimport
利用load data infile的方法

（2）数据导出的方法
利用导出工具Mysqldump
利用select outfile