http://www.cnblogs.com/wupeiqi/articles/5713315.html
http://www.cnblogs.com/wupeiqi/articles/5713323.html
http://www.cnblogs.com/wupeiqi/articles/5729934.html
http://www.cnblogs.com/wupeiqi/articles/5716963.html
添加环境变量:
执行vim ~/.bash_profile
PATH=$PATH:/usr/local/mysql/bin
修改密码：
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpass');
SQL语句
创建数据库:
mysql> create database db1 default charset utf8 collate utf8_general_ci;
创建表:
mysql> create table tb1(
-> nid int not null auto_increment primary key,
-> name varchar(16),
-> age int default 19
-> )engine=innodb default charset=utf8;
分组
mysql> select * from tb1;
+-----+--------+------+------+
| nid | name | age | size |
+-----+--------+------+------+
| 1 | 胡悦 | 12 | 中 |
| 2 | 东永 | 14 | 中 |
| 3 | 东永 | 20 | 中 |
| 4 | 小胡 | 13 | 大 |
+-----+--------+------+------+
4 rows in set (0.00 sec)
mysql> select name from tb1 group by name;
+--------+
| name |
+--------+
| 东永 |
| 小胡 |
| 胡悦 |
+--------+
3 rows in set (0.00 sec)
mysql> select age from tb1 group by age;
+------+
| age |
+------+
| 12 |
| 13 |
| 14 |
| 20 |
+------+
4 rows in set (0.00 sec)
mysql> select age from tb1 group by age having max(age)>12;
+------+
| age |
+------+
| 13 |
| 14 |
| 20 |
+------+
3 rows in set (0.00 sec)
#select num,nid,count(*),sum(score),max(score),min(score) from 表 group by num,nid
mysql> select name,nid, count(*) from tb1 group by name,nid;
+--------+-----+----------+
| name | nid | count(*) |
+--------+-----+----------+
| 东永 | 2 | 1 |
| 东永 | 3 | 1 |
| 小胡 | 4 | 1 |
| 胡悦 | 1 | 1 |
+--------+-----+----------+
4 rows in set (0.00 sec)
mysql> select name,count(*) from tb1 group by name;
+--------+----------+
| name | count(*) |
+--------+----------+
| 东永 | 2 |
| 小胡 | 1 |
| 胡悦 | 1 |
+--------+----------+
3 rows in set (0.00 sec)
分页
mysql> select name from tb1 limit 2;
+--------+
| name |
+--------+
| 胡悦 |
| 东永 |
+--------+
2 rows in set (0.00 sec)
mysql> select name from tb1 limit 1 offset 2;
+--------+
| name |
+--------+
| 东永 |
+--------+
1 row in set (0.00 sec)
连表
mysql> select * from tb2;
+-----+------+----------+
| nid | age | location |
+-----+------+----------+
| 1 | 12 | 北京 |
| 2 | 13 | 上海 |
| 3 | 14 | 广州 |
+-----+------+----------+
3 rows in set (0.00 sec)
mysql> select * from tb1;
+-----+--------+------+------+
| nid | name | age | size |
+-----+--------+------+------+
| 1 | 胡悦 | 12 | 中 |
| 2 | 东永 | 14 | 中 |
| 3 | 东永 | 20 | 中 |
| 4 | 小胡 | 13 | 大 |
+-----+--------+------+------+
4 rows in set (0.00 sec)
mysql> select tb1.name,tb2.location from tb1,tb2 where tb1.age = tb2.age;
+--------+----------+
| name | location |
+--------+----------+
| 胡悦 | 北京 |
| 东永 | 广州 |
| 小胡 | 上海 |
+--------+----------+
3 rows in set (0.20 sec)
mysql> select tb1.name,tb2.location
-> from tb1 left join tb2
-> on tb1.age = tb2.age;
+--------+----------+
| name | location |
+--------+----------+
| 胡悦 | 北京 |
| 小胡 | 上海 |
| 东永 | 广州 |
| 东永 | NULL |
+--------+----------+
4 rows in set (0.02 sec)
mysql> insert tb2 (age,location)values(17,'深圳');
Query OK, 1 row affected (0.00 sec)
mysql> select tb1.name,tb2.location
-> from tb1 right join tb2
-> on tb1.age = tb2.age;
+--------+----------+
| name | location |
+--------+----------+
| 胡悦 | 北京 |
| 东永 | 广州 |
| 小胡 | 上海 |
| NULL | 深圳 |
+--------+----------+
4 rows in set (0.00 sec)
添加列
mysql> alter table student add gender varchar(5);
连表练习
select score.sid,student.sname,course.cname
from score
left join student on score.student_id=student.sid
left join course on score.course_id=course.cid;
mysql> select * from class;
+-----+--------------+
| cid | caption |
+-----+--------------+
| 1 | 三年二班 |
| 2 | 一年三班 |
| 3 | 三年一斑 |
+-----+--------------+
3 rows in set (0.00 sec)
mysql> select * from student;
+-----+--------+--------+----------+
| sid | sname | gender | class_id |
+-----+--------+--------+----------+
| 1 | 纲蛋 | 女 | 1 |
| 2 | 铁锤 | 女 | 1 |
| 3 | 山炮 | 男 | 2 |
+-----+--------+--------+----------+
3 rows in set (0.00 sec)
mysql> select * from teacher;
+-----+------+
| tid | name |
+-----+------+
| 1 | 波 |
| 2 | 明 |
| 3 | 发 |
+-----+------+
3 rows in set (0.00 sec)
mysql> select * from course;
+-----+--------+----------+
| cid | cname | teach_id |
+-----+--------+----------+
| 1 | 生物 | 1 |
| 2 | 体育 | 1 |
| 3 | 物理 | 2 |
+-----+--------+----------+
3 rows in set (0.00 sec)
mysql> select * from score;
+-----+------------+-----------+--------+
| sid | student_id | course_id | number |
+-----+------------+-----------+--------+
| 1 | 1 | 1 | 60 |
| 2 | 1 | 1 | 59 |
| 3 | 2 | 2 | 100 |
+-----+------------+-----------+--------+
3 rows in set (0.00 sec)
mysql> select score.sid,student.sname,course.cname
-> from score
-> left join student on score.student_id=student.sid
-> left join course on score.course_id=course.cid;
+-----+--------+--------+
| sid | sname | cname |
+-----+--------+--------+
| 1 | 纲蛋 | 生物 |
| 2 | 纲蛋 | 生物 |
| 3 | 铁锤 | 体育 |
+-----+--------+--------+
3 rows in set (0.05 sec)
函数
==max()==
==min()==
==avg()==
==count()==
mysql> select sum(number)/count(number) from score;
+---------------------------+
| sum(number)/count(number) |
+---------------------------+
| 73.0000 |
+---------------------------+
1 row in set (0.03 sec)
mysql> select avg(number) from score;
+-------------+
| avg(number) |
+-------------+
| 73.0000 |
+-------------+
1 row in set (0.00 sec)
