mysql学习笔记（1）

mysql 常用命令：

mysql之类的关系型数据库最擅长的是——把一样东西与另一样东西联系起来，从多个信息中把你想要的信息找出来。



desc tablename；显示某个表的属性

show tables ； 显示当前数据库中所有表名

show databases ；显示当前连接的服务器上的所有数据库名


//学生信息表
CREATE TABLE student
{
   name   varchar(20) NOT NULL,
   sex    ENUM('F','M') NOT NULL,
   student_id  INT UNSIGNED NOT NULL AUTO_INCREMENT,
   PRIMARY KEY (student_id)
}ENGINE InnoDB;

DESCRIBE student 'sex' 显示sex可选的枚举值

外键是一个给定的键值，它必须与另外一个数据表的某个键值相匹配。

外键的用处可以保证不会出现几个用外键管理的表中有不相匹配的信息。

//考试事件表
CREATE TABLE grade_event
{
   date        DATE NOT NULL,
   category    ENUM('TEST','QUIZ') NOT NULL,
   event_id    INT UNSIGNED NOT NULL AUTO_INCREMENT,
   PRIMARY KEY (event_id)
 
}ENGINE InnoDB;

//成绩表
CREATE TABLE score
{
   student_id    INT UNSIGNED NOT NULL,
   event_id      INT UNSIGNED NOT NULL,
   score         INT NOT NULL,
   PRIMARY KEY (student_id,event_id),
   INDEX (event_id),
   FOREIGN KEY (event_id) REFERENCES grade_event (event_id),
   FOREIGN KEY (student_id) REFERENCES student (student_id),
}ENGINE InnoDB;
为什么要多一个INDEX，因为foreign key里的列，要么在PRIMARY KEY（）中第一个列出来，要么需要建一个索引，成绩表中的PRIMARY KEY 第一个列出来的是student_id,所以要
建一个event_id索引。

添加一行：

INSERT INTO student VALUE ('qingwu.fu','M',NULL);

//将文本里的数据添加到数据库中

LOAD DATA LOCAL FILE 'sutdent.txt' INTO TABLE sutdent;

如果省略了 LOCAL  说明数据文件是在服务器上的，上面的语句表示数据文件在客户端的机器上。

如果 LOCAL 功能在启动时处于禁止的状态，可以用一下命令重启 mysql：
mysql --local--infile 数据库名
如果还不行，说明local功能未激活，需要激活一下。或者使用下面的命令导入数据：

mysqlimport --local 数据库名 student.txt;
由于该命令是通过数据文件的名字来确定与之对应的数据表的。上面的命令就可以将 student.txt 中数据 导入 到student 表中。


一定要细心倾听别人多查询要求的描述，根据描述正确的选用适当的sql逻辑描述符。

SELECT last_name, first_name from president
WHERE state IN ('MA','VA');
当比较一列和一大组值的时候，IN()非常合适。

SELECT last_name, first_name from president
ORDER BY state DESC,last_name ASC;                  //排序

有时候排序的列值会有null，默认是将null的结果排在末尾，下面的语句可以将null的结果排在前面。

SELECT last_name, first_name, death from president
ORDER BY IF(death IS NULL,0,1),death DESC;

IF()先判断第一个参数是否为真，如果为真返回第二参数值，否则返回第三个参数值。

//限制查询个数 + LIMIT

SELECT last_name, first_name from president LIMIT 5;      //返回查询结果的前五个即可
SELECT last_name, first_name from president LIMIT 10,5;   //返回查询结果的跳过前十个，后面的5个

从数据表中任意抽取一条或者一组记录：

SELECT last_name, first_name from president
ORDER BY RAND() LIMIT 1;

SELECT last_name, first_name from president
ORDER BY RAND() LIMIT 3;

//如何对输出列进行求值和命名 +AS

SELECT CONCAT(first_name,' ',last_name) AS president name,
       CONCAT(city, ',' ,state) AS place of birth
FROM president;


//与日期有关的问题

SELECT * FROM grade_event WHERE data = '2012-07-28';

SELECT first_name, last_name
FROM president
WHERE death >= '1970-01-01' AND death <= '1980-01-01';

其中日期的年、月、日 可以用YEAR() MONTH() DAYOFMONTH()分别取出来；

SELECT first_name,last_name
FROM president
WHERE MONTH(birth) = 3;

返回 今天 的日期函数：  CURDATE() 永远返回当天的日期；

如果想得到两个日期的间隔，直接做减法就行了。TIMESTAMPDIFF()非常有用，可以指定计算结果的单位，如YEAR 计算的是年的间隔。
下面计算年龄最大的五位总统:

SELECT first_name,last_name,birth,death,
TIMESTAMPDIFF(YEAR,birth,death) AS age,
from president
WHERE death IS NOT NULL
ORDER BY age DESC LIMIT 5;


将某个日期转化成天，可以计算两个日期的间隔天数：函数 TO_DAYS()

下面把资格以及失效以及在60天内需要充会员费的会员查出来：

SELECT first_name,last_name,expiration
FROM member
WHERE (TO_DAYS(expiration)-TO_DAYS(CURDATE()))<60;

或者
SELECT first_name,last_name,expiration
FROM member
WHERE TIMESTAMPDIFF(DAY,CURDATE(),expiration)<60;

DATE_ADD()和DATA_SUB()对给定的日期加上或者减去某个时间段，返回一个日期；

查找 20世纪 70年代去世的总统：
SELECT first_name,last_name,death
FROM president
WHERE birth > '1970-1-1'
AND death<DATA_ADD('1970-1-1',INTETVAL 10 YEAR);

//模式匹配  LIKE, NOT LIKE

下划线 '_'只能匹配一个字符，百分号 '%'能匹配任意个字符序列

SELECT first_name, last_name
FROM president
WHERE first_name LIKE '%W%';

//如何设置和使用 SQL 变量 @变量名

查找比Andrew Jackson总统出生早的总统

SELECT @birth:=birth
FROM president
WHERE last_name = 'Jackson' AND first_name = 'Andrew';

SELECT first_name, last_name，birth
FROM president
WHERE birth < @birth ORDER BY birth;

//如何生成统计信息

DISTINCT 去重

SELECT DISTINCT state FROM president ORDER BY state;

COUNT() 用来计数：

SELECT COUNT(*) FROM president;

COUNT(*)用来统计总的记录数，而COUNT(列名)返回该列不为NULL的选中记录数；

SELECT COUNT(*)，COUNT(death) FROM president;

COUNT()和DISTINCT 可以结合起来用：

SELECT COUNT(DISTINCT state) FROM president;

统计男女生人数的语句：分类统计，肯定用 GROUP BY 了

SELECT sex,COUNT(*) FROM student GROUP BY sex;

将各个州出生的总统数统计出来并降序排序：

SELECT state COUNT(*) AS count
FROM president
GROUP BY state ORDER BY count DESC;

上面的语句还可以与 LIMIT 结合使用，用于选出 出生总统数前四的州。在上面的语句后加入 LIMIT 4 即可；

HAVING 子句，他与WHERE 的相同之处是都是用来设置查询条件的，不同之处，HAVING 允许COUNT()之类的汇总结果出现。

SELECT state COUNT(*) AS count
FROM president
GROUP BY state HAVING count>1 ORDER BY count DESC;

除了 COUNT()以外，还有 MIN() MAX() AVG() 和SUM()之类的统计函数：

WITH ROLLUP 会将以上的统计结果做一个总结，

SELECT sex,COUNT(*) FROM student GROUP BY sex WITH ROLLUP; 会在最后将总的人数都总结出来。

//如何从多个表里检索信息： JOIN

用来查询给定日期的分数的语句;

SELECT student_id,date,score,category
FROM grade_event INNER JOIN score
ON grade_event.event_id = score.event_id
WHERE date = '2012-07-28';

LEFT JOIN :将从第一个表（LEFT JOIN左边的表）里中每一个选择数据行形成一个输出行。

联接不一定只能用到不同的表中，也可以用到同一个表联接；当然，要用不同的别名，以作区分。

嵌套子查询。
查询全勤的学生;

SELECT * FROM student
WHERE student_id NOT IN (SELECT student_id FROM absence);

//删除或更新现有的数据记录  DELETE  UPDATE

越简单的DELETE越危险：  DELETE FROM table;
如果对删除的内容不放心，可以先将DELETE的WHERE子句放到 SELECT中查询，看一看

//与客户程序mysql交互的技巧

简化连接过程：1、创建一个选项文件，将连接用的参数设置好
              2、使用shell的历史命令
              3、利用shell别名和脚本