这篇文章是我看了《深入浅出MySQL》一书的笔记，相关内容可以浏览Linux公社 www.linuxidc.com。文章的代码有些与书中相同，有些是我自己写的，数据也和书中的数据不一样。
以下内容包含建表，插入，更新，查找，删除操作。
启动和关闭MySQL
（1） 启动服务：
注意是root模式，并且到mysql或bin目录下cd /usr/local/mysql/bin
[root@localhost bin]# service mysql start（2）关闭服务：
[root@localhost bin]# service mysql stop(3) 启动 MySQL 服务之后，还需连接到 MySQL 服务器
[root@localhost mysql]# bin/mysql -uroot -p表的创建和字段修改（DDL语句）
（1） 创建数据库和删除数据库
mysql> create database test1;mysql> drop database test1;（2） 查看有哪些数据库
mysql> show databases;（3） 选择要操作的数据库
mysql> use test1;（4） 查看已选择的数据库中创建的所有数据表
mysql> show tables;
Empty set (0.01 sec)（5） 创建表和删除表
mysql> create table emp(
-> ename varchar(10),
-> hiredate date,
-> sal decimal(10,2),
-> deptno int(2)
-> );
mysql> drop table emp;
Query OK, 0 rows affected (0.08 sec)
Query OK, 0 rows affected (0.18 sec)（6） 查看一下表的定义
mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename | varchar(10) | YES | | NULL | |
| hiredate | date | YES | | NULL | |
| sal | decimal(10,2) | YES | | NULL | |
| deptno | int(2) | YES | | NULL | |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.02 sec)（7） 查看更全面的表定义信息，engine（存储引擎）和 charset（字符集），\G表字段竖着排列
mysql> show create table emp \G;
*************************** 1. row ***************************
Table: emp
Create Table: CREATE TABLE `emp` (
`ename` varchar(10) DEFAULT NULL,
`hiredate` date DEFAULT NULL,
`sal` decimal(10,2) DEFAULT NULL,
`deptno` int(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.07 sec)
（8） 修改表 emp 的 ename 字段定义，将 varchar(10)改为 varchar(20)：
mysql> alter table emp modify ename varchar(20);（9） 表 emp 上字段 age的新增加和删除表字段，类型为 int(3)：
mysql> alter table emp add column age int(3);
mysql> alter table emp drop column age;（10） 修改字段名及类型
mysql> alter table emp change age age1 int(4);（11） 新增字段 birth date 且加在 ename 之后：
mysql> alter table emp add birth date after ename;（12） 把age1放在字段最前面：
mysql> alter table emp modify age1 int(3) first;表记录的插入、更新、删除和查询（DML语句）：
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(10) | YES | | NULL | |
| age | int(5) | YES | | NULL | |
| sex | varchar(5) | YES | | NULL | |
| class | varchar(20) | YES | | NULL | |
| grade | int(5) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)（1）．插入记录
mysql> insert into student values("张三丰",103,"男","IOS毕业班",100);若出现以下错误;
"\xA0\xE4\xB8\x89...' for column 'name' at row values("张三丰")则需修改编码位utf8
mysql> alter table student convert to character set utf8;（2）．更新记录
mysql> update student set age=38 where name='张三丰';（3）.删除记录
mysql> delete from student where age=38;（4）.查询记录
mysql> select *from emp;
+-----------+------------+------+------+
| ename | hiredate | sal | num |
+-----------+------------+------+------+
| 张三丰 | 2000-01-01 | 2000 | 1 |
| 李峰 | 2003-01-01 | 4500 | 2 |
| 张飞 | 1998-12-06 | 2300 | 3 |
| 田甜 | 1298-12-03 | 5640 | 4 |
| 田甜 | 1298-12-03 | 5430 | 4 |
+-----------+------------+------+------+
5 rows in set (0.00 sec)1.去重
mysql> select distinct num from emp;
+------+
| num |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
4 rows in set (0.00 sec)2．查询num=1的记录
mysql> select *from emp where num=1;
+-----------+------------+------+------+
| ename | hiredate | sal | num |
+-----------+------------+------+------+
| 张三丰 | 2000-01-01 | 2000 | 1 |
+-----------+------------+------+------+
1 row in set (0.00 sec)3.查询num=4且sal>5500d记录
mysql> select *from emp where num=4 and sal>5500;;
+--------+------------+------+------+
| ename | hiredate | sal | num |
+--------+------------+------+------+
| 田甜 | 1298-12-03 | 5640 | 4 |
+--------+------------+------+------+
1 row in set (0.00 sec)4. 由多个不同的排序字段进行排序
mysql> select *from emp order by sal;
+-----------+------------+------+------+
| ename | hiredate | sal | num |
+-----------+------------+------+------+
| 张三丰 | 2000-01-01 | 2000 | 1 |
| 张飞 | 1998-12-06 | 2300 | 3 |
| 李峰 | 2003-01-01 | 4500 | 2 |
| 田甜 | 1298-12-03 | 5430 | 4 |
| 田甜 | 1298-12-03 | 5640 | 4 |
+-----------+------------+------+------+
5 rows in set (0.00 sec)5.先对num进行升序，在由num相同的sal进行降序
mysql> select *from emp order by num asc,sal desc;
+-----------+------------+------+------+
| ename | hiredate | sal | num |
+-----------+------------+------+------+
| 张三丰 | 2000-01-01 | 2000 | 1 |
| 李峰 | 2003-01-01 | 4500 | 2 |
| 张飞 | 1998-12-06 | 2300 | 3 |
| 田甜 | 1298-12-03 | 5640 | 4 |
| 田甜 | 1298-12-03 | 5430 | 4 |
+-----------+------------+------+------+
5 rows in set (0.00 sec)6.显示 emp 表中按照 sal 排序后的前 3 条记录
mysql> select *from emp order by sal limit 3;
+-----------+------------+------+------+
| ename | hiredate | sal | num |
+-----------+------------+------+------+
| 张三丰 | 2000-01-01 | 2000 | 1 |
| 张飞 | 1998-12-06 | 2300 | 3 |
| 李峰 | 2003-01-01 | 4500 | 2 |
+-----------+------------+------+------+
3 rows in set (0.00 sec)7.要显示 emp 表中按照 sal 排序后从第二条记录开始，显示 3 条记录：
mysql> select *from emp order by sal limit 1,3;
+--------+------------+------+------+
| ename | hiredate | sal | num |
+--------+------------+------+------+
| 张飞 | 1998-12-06 | 2300 | 3 |
| 李峰 | 2003-01-01 | 4500 | 2 |
| 田甜 | 1298-12-03 | 5430 | 4 |
+--------+------------+------+------+
3 rows in set (0.00 sec)8.统计各班人数
mysql> select class ,count(class) from student group by class;
+-----------------+--------------+
| class | count(class) |
+-----------------+--------------+
| IOS基础班 | 1 |
| IOS毕业班 | 2 |
| javaee基础班 | 1 |
| ui基础班 | 2 |
+-----------------+--------------+
4 rows in set (0.00 sec)9.统计各班人数，再对总人数进行汇总在最后一行
mysql> select class ,count(class) from student group by class with rollup; +-----------------+--------------+
| class | count(class) |
+-----------------+--------------+
| IOS基础班 | 1 |
| IOS毕业班 | 2 |
| javaee基础班 | 1 |
| ui基础班 | 2 |
| NULL | 6 |
+-----------------+--------------+
5 rows in set (0.00 sec)10．统计好后再过滤出人数>1的班级
mysql> select class ,count(class) from student group by class with rollup having count(1)>1;
+--------------+--------------+
| class | count(class) |
+--------------+--------------+
| IOS毕业班 | 2 |
| ui基础班 | 2 |
| NULL | 6 |
+--------------+--------------+
3 rows in set (0.00 sec)11，对列进行求和，最大值，最小值进行统计：
mysql> select sum(sal),max(hiredate),min(sal) from emp;
+----------+---------------+----------+
| sum(sal) | max(hiredate) | min(sal) |
+----------+---------------+----------+
| 19870 | 2003-01-01 | 2000 |
+----------+---------------+----------+
1 row in set (0.00 sec)（5）．表连接
mysql> select *from emp;
+-----------+------------+------+------+
| ename | hiredate | sal | num |
+-----------+------------+------+------+
| 张三丰 | 2000-01-01 | 2000 | 1 |
| 李峰 | 2003-01-01 | 4500 | 2 |
| 张飞 | 1998-12-06 | 2300 | 3 |
| 田甜 | 1298-12-03 | 5640 | 4 |
| 张伟 | 2013-05-07 | 7822 | 5 |
| 王凯丽 | 2010-06-23 | 6660 | 6 |
+-----------+------------+------+------+
6 rows in set (0.00 sec)
mysql> select *from student;
+-----------+------+------+-----------------+-------+------+
| name | age | sex | class | grade | num |
+-----------+------+------+-----------------+-------+------+
| 李峰 | 23 | 男 | IOS基础班 | 90 | 2 |
| 张飞 | 36 | 男 | javaee基础班 | 40 | 3 |
| 田甜 | 23 | 男 | ui基础班 | 80 | 4 |
| 张三丰 | 103 | 男 | IOS毕业班 | 100 | 1 |
| 张伟 | 45 | 女 | IOS毕业班 | 78 | 5 |
| 王凯丽 | 56 | 女 | ui基础班 | 78 | 6 |
+-----------+------+------+-----------------+-------+------+
6 rows in set (0.00 sec)1.对class和hiredate进行左连接
mysql> select class,hiredate from emp left join student on emp.num = student.num;
+-----------------+------------+
| class | hiredate |
+-----------------+------------+
| IOS基础班 | 2003-01-01 |
| javaee基础班 | 1998-12-06 |
| ui基础班 | 1298-12-03 |
| IOS毕业班 | 2000-01-01 |
| IOS毕业班 | 2013-05-07 |
| ui基础班 | 2010-06-23 |2.对class和hiredate进行右连接：
mysql> select class,hiredate from emp right join student on emp.num = student.num;
+-----------------+------------+
| class | hiredate |
+-----------------+------------+
| IOS毕业班 | 2000-01-01 |
| IOS基础班 | 2003-01-01 |
| javaee基础班 | 1998-12-06 |
| ui基础班 | 1298-12-03 |
| IOS毕业班 | 2013-05-07 |
| ui基础班 | 2010-06-23 |
+-----------------+------------+（6）.子查询：关键字主要包括 in、not in、=、!=、exists、not exits
mysql> select * from emp where num in(select num from student where sex="男"); +-----------+------------+------+------+
| ename | hiredate | sal | num |
+-----------+------------+------+------+
| 张三丰 | 2000-01-01 | 2000 | 1 |
| 李峰 | 2003-01-01 | 4500 | 2 |
| 张飞 | 1998-12-06 | 2300 | 3 |
| 田甜 | 1298-12-03 | 5640 | 4 |
+-----------+------------+------+------+改为表连接
mysql> select emp.* from emp,student where emp.num=student.num and sex="男";
+-----------+------------+------+------+
| ename | hiredate | sal | num |
+-----------+------------+------+------+
| 李峰 | 2003-01-01 | 4500 | 2 |
| 张飞 | 1998-12-06 | 2300 | 3 |
| 田甜 | 1298-12-03 | 5640 | 4 |
| 张三丰 | 2000-01-01 | 2000 | 1 |
+-----------+------------+------+------+
4 rows in set (0.00 sec)