插入数据:

插入一条数据

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)