插入数据：
插入一条数据
INSERT INTO table_name(column_list) VALUES(value_list);
先创建一张数据表：
mysql> create table person
-> (
-> id int unsigned not null auto_increment,
-> name char(40) not null default 'x',
-> age int not null default 0,
-> info char(50) null,
-> primary key(id)
-> );
Query OK, 0 rows affected (0.13 sec)
然后插入数据：
mysql> insert into person(id,name,age,info)
-> values(1,'Green',21,'Lawyer');
Query OK, 1 row affected (0.08 sec)
mysql> insert into person(age,name,id,info)
-> values(22,'Suse',2,'dancer');
Query OK, 1 row affected (0.06 sec)
mysql> insert into person
-> values(3,'Mary',24,'Musicican');
Query OK, 1 row affected (0.06 sec)
mysql> insert into person(name,age,info)
-> values('Willam',20,'sports man');
Query OK, 1 row affected (0.06 sec)
mysql> insert into person(name,age)
-> values('Laura',25);
Query OK, 1 row affected (0.10 sec)
最后查看已插入的数据：
mysql> select * from person;
+----+--------+-----+------------+
| id | name | age | info |
+----+--------+-----+------------+
| 1 | Green | 21 | Lawyer |
| 2 | Suse | 22 | dancer |
| 3 | Mary | 24 | Musicican |
| 4 | Willam | 20 | sports man |
| 5 | Laura | 25 | NULL |
+----+--------+-----+------------+
5 rows in set (0.00 sec)
插入多条数据：
INSERT INTO table_name(column_list) VALUES(value_list1),(value_list2),...,(value_listn);
插入如下多条数据：
mysql> insert into person(name,age,info)
-> values('Evans',27,'secretary'),
-> ('Dale',22,'cook'),
-> ('Edison',28,'singer');
Query OK, 3 rows affected (0.06 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> insert into person
-> values(9,'Harry',21,'magician'),
-> (NULL,'Harriet',19,'pianist');
Query OK, 2 rows affected (0.06 sec)
Records: 2 Duplicates: 0 Warnings: 0
最后查看已插入数据：
mysql> select * from person;
+----+---------+-----+------------+
| id | name | age | info |
+----+---------+-----+------------+
| 1 | Green | 21 | Lawyer |
| 2 | Suse | 22 | dancer |
| 3 | Mary | 24 | Musicican |
| 4 | Willam | 20 | sports man |
| 5 | Laura | 25 | NULL |
| 6 | Evans | 27 | secretary |
| 7 | Dale | 22 | cook |
| 8 | Edison | 28 | singer |
| 9 | Harry | 21 | magician |
| 10 | Harriet | 19 | pianist |
+----+---------+-----+------------+
10 rows in set (0.00 sec)
更新数据
UPDATE table_name SET column_name1=value1,column_name2=value2,...,column_namen=valuen WHERE(condition);
比如 id=10 的数据，
mysql> select * from person where id=10;
+----+---------+-----+---------+
| id | name | age | info |
+----+---------+-----+---------+
| 10 | Harriet | 19 | pianist |
+----+---------+-----+---------+
1 row in set (0.09 sec)
更新该数据，
mysql> update person set age=15,name='LiMing' where id=10;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0
检查更新后，
mysql> select * from person where id=10;
+----+--------+-----+---------+
| id | name | age | info |
+----+--------+-----+---------+
| 10 | LiMing | 15 | pianist |
+----+--------+-----+---------+
1 row in set (0.00 sec)
比如将 age 值为 19 到 22 的数据将 info 字段值改为 student，
mysql> update person set info='student' where age between 19 and 22;
Query OK, 5 rows affected (0.03 sec)
Rows matched: 5 Changed: 5 Warnings: 0
最后检查，
mysql> select * from person where age between 19 and 22;
+----+--------+-----+---------+
| id | name | age | info |
+----+--------+-----+---------+
| 1 | Green | 21 | student |
| 2 | Suse | 22 | student |
| 4 | Willam | 20 | student |
| 7 | Dale | 22 | student |
| 9 | Harry | 21 | student |
+----+--------+-----+---------+
5 rows in set (0.00 sec)
删除数据
DELETE FROM table_name [WHERE <condition>];
删除 id=10 的记录。
mysql> delete from person where id=10;
Query OK, 1 row affected (0.08 sec)
删除 age 在 19 到 22 之间的数据。
mysql> delete from person where age between 19 and 22;
Query OK, 5 rows affected (0.09 sec)
看看剩余数据：
mysql> select * from person;
+----+--------+-----+-----------+
| id | name | age | info |
+----+--------+-----+-----------+
| 3 | Mary | 24 | Musicican |
| 5 | Laura | 25 | NULL |
| 6 | Evans | 27 | secretary |
| 8 | Edison | 28 | singer |
+----+--------+-----+-----------+
4 rows in set (0.00 sec)
删除全部数据，
mysql> delete from person;
Query OK, 4 rows affected (0.06 sec)
最后数据为空。
mysql> select * from person;
Empty set (0.00 sec)
查询
查询数据
SELECT {*| 字段列表 }
[
FROM < 表 1>,< 表 2>,...
[WHERE < 表达式 >]
[GROUP BY <group by definition>]
[HAVING <expression>[{<operator><expression>}...]]
[ORDER BY <order by definition>] （ASC 升序（可省），DESC 降序）
[LIMIT [<offest>,]<row count>]
];
重新创建个数据表，
mysql> create table fruits
-> (-> f_id char(10) not null,
-> s_id int not null,
-> f_name char(255) not null,
-> f_price decimal(8,2) not null,
-> primary key(f_id)
-> );
Query OK, 0 rows affected (0.13 sec)
插入一些数据，
mysql> insert into fruits(f_id,s_id,f_name,f_price)
-> values('a1',101,'apple',5.2),
-> ('b1',101,'blackberry',10.2),
-> ('bs1',102,'orange',11.2),
-> ('bs2',105,'melon',8.2),
-> ('t1',102,'banana',10.3),
-> ('t2',102,'grape',5.3),
-> ('o2',103,'coconut',9.2),
-> ('c0',101,'cherry',3.2),
-> ('a2',103,'apricot',2.2),
-> ('l2',104,'lemon',6.4),
-> ('b2',104,'berry',7.6),
-> ('m1',106,'mango',15.6),
-> ('m2',105,'xbabay',2.6),
-> ('t4',107,'xbababa',3.6),
-> ('m3',105,'xxtt',11.6),
-> ('b5',107,'xxxx',3.6);
Query OK, 16 rows affected (0.06 sec)
Records: 16 Duplicates: 0 Warnings: 0
查询 f_id 和 f_name 字段的数据，
mysql> select f_id,f_name from fruits;
+------+------------+
| f_id | f_name |
+------+------------+
| a1 | apple |
| a2 | apricot |
| b1 | blackberry |
| b2 | berry |
| b5 | xxxx |
| bs1 | orange |
| bs2 | melon |
| c0 | cherry |
| l2 | lemon |
| m1 | mango |
| m2 | xbabay |
| m3 | xxtt |
| o2 | coconut |
| t1 | banana |
| t2 | grape |
| t4 | xbababa |
+------+------------+
16 rows in set (0.00 sec)
查询价格小于 10 元的水果的名称和价格，
mysql> select f_name,f_price from fruits where f_price < 10;
+---------+---------+
| f_name | f_price |
+---------+---------+
| apple | 5.20 |
| apricot | 2.20 |
| berry | 7.60 |
| xxxx | 3.60 |
| melon | 8.20 |
| cherry | 3.20 |
| lemon | 6.40 |
| xbabay | 2.60 |
| coconut | 9.20 |
| grape | 5.30 |
| xbababa | 3.60 |
+---------+---------+
11 rows in set (0.00 sec)
带 IN 关键字查询
在 where 子句中，IN 关键字用来查询满足指定条件范围内的记录。
查询 s_id 为 101 和 103 的记录，
mysql> select s_id,f_name,f_price from fruits where s_id in(101,103);
+------+------------+---------+
| s_id | f_name | f_price |
+------+------------+---------+
| 101 | apple | 5.20 |
| 103 | apricot | 2.20 |
| 101 | blackberry | 10.20 |
| 101 | cherry | 3.20 |
| 103 | coconut | 9.20 |
+------+------------+---------+
5 rows in set (0.00 sec)
可以用 NOT 来检索不在范围内的记录。
查询所有 s_id 不等于 101 和 105 的记录。
mysql> select s_id,f_name,f_price from fruits where s_id not in(101,105);
+------+---------+---------+
| s_id | f_name | f_price |
+------+---------+---------+
| 103 | apricot | 2.20 |
| 104 | berry | 7.60 |
| 107 | xxxx | 3.60 |
| 102 | orange | 11.20 |
| 104 | lemon | 6.40 |
| 106 | mango | 15.60 |
| 103 | coconut | 9.20 |
| 102 | banana | 10.30 |
| 102 | grape | 5.30 |
| 107 | xbababa | 3.60 |
+------+---------+---------+
10 rows in set (0.00 sec)
带 BETWEEN AND 的范围查询
在 where 子句中，BETWEEN AND 可以查询某个区间的值，需要两个端点值。
查询价格在 2.00 元到 10.5 元之间的水果名称和价格，
mysql> select f_name,f_price from fruits where f_price between 2.00 and 10.5;
+------------+---------+
| f_name | f_price |
+------------+---------+
| apple | 5.20 |
| apricot | 2.20 |
| blackberry | 10.20 |
| berry | 7.60 |
| xxxx | 3.60 |
| melon | 8.20 |
| cherry | 3.20 |
| lemon | 6.40 |
| xbabay | 2.60 |
| coconut | 9.20 |
| banana | 10.30 |
| grape | 5.30 |
| xbababa | 3.60 |
+------------+---------+
13 rows in set (0.00 sec)
带 LIKE 的字符匹配查询
在 where 子句中，LIKE 的字符匹配查询，% 通配符，匹配任意长度字符，_匹配单个任意字符
查找所有以 b 开头，以 y 结尾的水果名称。
mysql> select f_name from fruits where f_name like 'b%y';
+------------+
| f_name |
+------------+
| blackberry |
| berry |
+------------+
2 rows in set (0.00 sec)
查询空值
在 where 子句中，IS NULL 语句可以查询空值，IS NOT NULL 查询非空值。
例如查询水果名称不为空的值。
mysql> select f_name from fruits where f_name is not null;
+------------+
| f_name |
+------------+
| apple |
| apricot |
| blackberry |
| berry |
| xxxx |
| orange |
| melon |
| cherry |
| lemon |
| mango |
| xbabay |
| xxtt |
| coconut |
| banana |
| grape |
| xbababa |
+------------+
16 rows in set (0.00 sec)
带 AND 的多条件查询
在 where 子句中，可以用 AND 连接多个条件，当全部满足条件的值才输出。
查询 s_id=101 并且水果价格大于 5 的记录价格和名称，
mysql> select f_name,f_price from fruits where s_id=101 and f_price>5;
+------------+---------+
| f_name | f_price |
+------------+---------+
| apple | 5.20 |
| blackberry | 10.20 |
+------------+---------+
2 rows in set (0.00 sec)
带 OR 的多条件查询
在 where 子句中，可以用 OR 连接多个条件，当满足其中某个条件的值都输出。
查询 s_id=101 或者 s_id=103 的水果名称和价格，
mysql> select s_id,f_name,f_price from fruits where s_id=101 or s_id=103;
+------+------------+---------+
| s_id | f_name | f_price |
+------+------------+---------+
| 101 | apple | 5.20 |
| 103 | apricot | 2.20 |
| 101 | blackberry | 10.20 |
| 101 | cherry | 3.20 |
| 103 | coconut | 9.20 |
+------+------------+---------+
5 rows in set (0.00 sec)
查询结果不重复
在 SELECT 语句中使用 SELECT DISTINCT 可以达到效果。
在 fruits 中查询 s_id 的值，
mysql> select s_id from fruits;
+------+
| s_id |
+------+
| 101 |
| 103 |
| 101 |
| 104 |
| 107 |
| 102 |
| 105 |
| 101 |
| 104 |
| 106 |
| 105 |
| 105 |
| 103 |
| 102 |
| 102 |
| 107 |
+------+
16 rows in set (0.00 sec)
不重复，
mysql> select distinct s_id from fruits;
+------+
| s_id |
+------+
| 101 |
| 103 |
| 104 |
| 107 |
| 102 |
| 105 |
| 106 |
+------+
7 rows in set (0.00 sec)
对查询结果排序
查询 fruits 中的 s_id 和 f_name 和 f_price 字段，s_id 升序，f_price 降序。
mysql> select s_id,f_name,f_price from fruits order by s_id,f_price desc;
+------+------------+---------+
| s_id | f_name | f_price |
+------+------------+---------+
| 101 | blackberry | 10.20 |
| 101 | apple | 5.20 |
| 101 | cherry | 3.20 |
| 102 | orange | 11.20 |
| 102 | banana | 10.30 |
| 102 | grape | 5.30 |
| 103 | coconut | 9.20 |
| 103 | apricot | 2.20 |
| 104 | berry | 7.60 |
| 104 | lemon | 6.40 |
| 105 | xxtt | 11.60 |
| 105 | melon | 8.20 |
| 105 | xbabay | 2.60 |
| 106 | mango | 15.60 |
| 107 | xxxx | 3.60 |
| 107 | xbababa | 3.60 |
+------+------------+---------+
16 rows in set (0.00 sec)
