表中添加字段
mysql> alter table leaf add leaf_pri int(5); Query OK, 4 rows affected (0.06 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from leaf; +---------+-----------+----------+----------+----------+------------+----------+ | leaf_id | leaf_name | leaf_age | leaf_sal | leaf_sex | leaf_bir | leaf_pri | +---------+-----------+----------+----------+----------+------------+----------+ | 101 | leaf | 24 | 8000 | male | 2013-08-06 | NULL | | 102 | lea | 34 | 7000 | male | 2013-07-06 | NULL | | 103 | le | 20 | 6000 | male | 2013-06-06 | NULL | | 104 | l | 10 | 5000 | male | 2013-05-06 | NULL | +---------+-----------+----------+----------+----------+------------+----------+
新添加字段赋值
mysql> update leaf set leaf_pri="100"; Query OK, 4 rows affected (0.06 sec) Rows matched: 4 Changed: 4 Warnings: 0 mysql> select * from leaf; +---------+-----------+----------+----------+----------+------------+----------+ | leaf_id | leaf_name | leaf_age | leaf_sal | leaf_sex | leaf_bir | leaf_pri | +---------+-----------+----------+----------+----------+------------+----------+ | 101 | leaf | 24 | 8000 | male | 2013-08-06 | 100 | | 102 | lea | 34 | 7000 | male | 2013-07-06 | 100 | | 103 | le | 20 | 6000 | male | 2013-06-06 | 100 | | 104 | l | 10 | 5000 | male | 2013-05-06 | 100 | +---------+-----------+----------+----------+----------+------------+----------+ 4 rows in set (0.00 sec)
两字段数据相加
mysql> select leaf_name,leaf_sal,leaf_pri,leaf_sal+leaf_pri from leaf; +-----------+----------+----------+-------------------+ | leaf_name | leaf_sal | leaf_pri | leaf_sal+leaf_pri | +-----------+----------+----------+-------------------+ | leaf | 8000 | 100 | 8100 | | lea | 7000 | 100 | 7100 | | le | 6000 | 100 | 6100 | | l | 5000 | 100 | 5100 | +-----------+----------+----------+-------------------+
distinct 过滤重复, 必须使用列名
mysql> select distinct leaf_pri from leaf; +----------+ | leaf_pri | +----------+ | 100 | +----------+ 1 row in set (0.00 sec)
mysql> select distinct leaf_name,leaf_pri from leaf; +-----------+----------+ | leaf_name | leaf_pri | +-----------+----------+ | leaf | 100 | | lea | 100 | | le | 100 | | l | 100 | +-----------+----------+ 4 rows in set (0.00 sec)
选取表中leaf_sex=male的人
mysql> select leaf_id,leaf_name,leaf_sex from leaf where leaf_sex='male'; +---------+-----------+----------+ | leaf_id | leaf_name | leaf_sex | +---------+-----------+----------+ | 101 | leaf | male | | 102 | lea | male | | 103 | le | male | | 104 | l | male | | 105 | leaf | male | | 106 | lea | male | | 107 | lea | male | +---------+-----------+----------+ 7 rows in set (0.00 sec)
选取表中leaf_pri=200的人
mysql> select leaf_id,leaf_name,leaf_pri from leaf where leaf_pri=200; +---------+-----------+----------+ | leaf_id | leaf_name | leaf_pri | +---------+-----------+----------+ | 105 | leaf | 200 | | 106 | lea | 200 | | 107 | lea | 200 | +---------+-----------+----------+ 3 rows in set (0.00 sec)
between and 表示一个范围,选取的是两端的闭区间
mysql> select leaf_name,leaf_sal from leaf where leaf_sal between 7000 and 8000; +-----------+----------+ | leaf_name | leaf_sal | +-----------+----------+ | leaf | 8000 | | lea | 7000 | | lea | 8000 | +-----------+----------+ 3 rows in set (0.00 sec)
in 表示一个集合，是离散值。含义为等于其中任意一个值
mysql> select leaf_name,leaf_age from leaf where leaf_age in (20,24); +-----------+----------+ | leaf_name | leaf_age | +-----------+----------+ | leaf | 24 | | le | 20 | | leaf | 20 | | lea | 20 | | lea | 24 | +-----------+----------+ 5 rows in set (0.00 sec)
mysql> select leaf_name,leaf_age from leaf where leaf_name in ('leaf','lea'); +-----------+----------+ | leaf_name | leaf_age | +-----------+----------+ | leaf | 24 | | lea | 34 | | leaf | 20 | | lea | 20 | | lea | 24 | +-----------+----------+ 5 rows in set (0.00 sec)
模糊查询
like和通配符进行查找 通配符的种类: 1) %表0或者多个字符； 2) _表示任意单个字符
mysql> select leaf_name from leaf where leaf_name like '_ea%'; +-----------+ | leaf_name | +-----------+ | leaf | | lea | | leaf | | lea | | lea | +-----------+ 5 rows in set (0.00 sec)
高级检索
mysql> select leaf_name,leaf_sal,leaf_pri from leaf where leaf_sal>6000 and leaf _name='leaf'; +-----------+----------+----------+ | leaf_name | leaf_sal | leaf_pri | +-----------+----------+----------+ | leaf | 8000 | 100 | +-----------+----------+----------+ 1 row in set (0.00 sec)
mysql> select leaf_name,leaf_sal,leaf_pri from leaf where leaf_sal>6000 and (lea f_name='leaf' or leaf_pri=200); +-----------+----------+----------+ | leaf_name | leaf_sal | leaf_pri | +-----------+----------+----------+ | leaf | 8000 | 100 | | lea | 8000 | 200 | +-----------+----------+----------+ 2 rows in set (0.00 sec)