数据库-1

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)