一、查询logs表中所有信息

1
2
3
4
5
6
7
8
9
10
11
12
mysql> select * from logs;                        
+----------+---------+---------------------+
| name     | caozuo  | uptime              |
+----------+---------+---------------------+
| guang3   | xinzeng | 2017-11-24 17:11:53 |
| guang3   | xiugai  | 2017-11-24 17:15:21 |
| guang3   | xiugai  | 2017-11-24 17:15:59 |
| guanggai | xiugai  | 2017-11-24 17:16:41 |
| guang    | delete  | 2017-11-24 17:21:05 |
| guang2   | delete  | 2017-11-24 17:21:05 |
+----------+---------+---------------------+
6 rows in set (0.00 sec)

二、查询logs表中显示表中数

1
2
3
4
5
6
7
mysql> select count(*) from logs;                    
+----------+
| count(*) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)

三、去重查询logs表中列name信息

1
2
3
4
5
6
7
8
9
10
mysql> select distinct name from logs ;               
+----------+
| name     |
+----------+
| guang3   |
| guanggai |
| guang    |
| guang2   |
+----------+
4 rows in set (0.01 sec)

四、查询logs表中列uptime大于2017-11-24 17:16:41的信息

1
2
3
4
5
6
7
8
mysql> select * from logs where uptime > '2017-11-24 17:16:41';
+--------+--------+---------------------+
| name   | caozuo | uptime              |
+--------+--------+---------------------+
| guang  | delete | 2017-11-24 17:21:05 |
| guang2 | delete | 2017-11-24 17:21:05 |
+--------+--------+---------------------+
2 rows in set (0.00 sec)

五、查询logs表中列uptime大于2017-11-24 17:16:41且小于2017-11-24 17:21:02的信息

1
2
3
4
5
6
7
8
9
10
mysql> select * from logs where uptime between '2017-11-24 17:11:41' and '2017-11-24 17:21:02';
+----------+---------+---------------------+
| name     | caozuo  | uptime              |
+----------+---------+---------------------+
| guang3   | xinzeng | 2017-11-24 17:11:53 |
| guang3   | xiugai  | 2017-11-24 17:15:21 |
| guang3   | xiugai  | 2017-11-24 17:15:59 |
| guanggai | xiugai  | 2017-11-24 17:16:41 |
+----------+---------+---------------------+
4 rows in set (0.00 sec)

六、查询logs表中列name为guanggai并且caozuo为guang3或xiugai的信息

1
2
3
4
5
6
7
mysql> select * from logs where (caozuo='guang3' or caozuo='xiugai') and name='guanggai';
+----------+--------+---------------------+
| name     | caozuo | uptime              |
+----------+--------+---------------------+
| guanggai | xiugai | 2017-11-24 17:16:41 |
+----------+--------+---------------------+
1 row in set (0.00 sec)

七、logs表中以列uptime倒叙显示信息

1
2
3
4
5
6
7
8
9
10
11
12
mysql> select * from logs order by uptime desc;
+----------+---------+---------------------+
| name     | caozuo  | uptime              |
+----------+---------+---------------------+
| guang    | delete  | 2017-11-24 17:21:05 |
| guang2   | delete  | 2017-11-24 17:21:05 |
| guanggai | xiugai  | 2017-11-24 17:16:41 |
| guang3   | xiugai  | 2017-11-24 17:15:59 |
| guang3   | xiugai  | 2017-11-24 17:15:21 |
| guang3   | xinzeng | 2017-11-24 17:11:53 |
+----------+---------+---------------------+
6 rows in set (0.00 sec)

八、查询logs表中name与uptime并以uptime正序显示

1
2
3
4
5
6
7
8
9
10
11
mysql> select name,uptime from logs order by uptime asc;            
+----------+---------------------+
| name     | uptime              |
+----------+---------------------+
| guang3   | 2017-11-24 17:11:53 |
| guang3   | 2017-11-24 17:15:21 |
| guang3   | 2017-11-24 17:15:59 |
| guang2   | 2017-11-24 17:21:05 |
| guang    | 2017-11-24 17:21:05 |
+----------+---------------------+
6 rows in set (0.00 sec)

九、向logs表中插入name=haha，caozuo=xinzeng的数据

1
2
mysql> insert into logs(name,caozuo) values('haha','xinzeng');
Query OK, 1 row affected (0.01 sec)

十、将logs表中列name=guang3更改为name=guang1且caozuo=xiugaii2

1
2
3
mysql> update logs set name='guang1',caozuo='xiugaii2' where name='guang3';
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0

十一、删除logs表中列name=guang3的数据

1
2
mysql> delete from logs where name='guang3';
Query OK, 3 rows affected (0.00 sec)

十二、删除logs表

1
2
mysql> delete from logs;
Query OK, 4 rows affected (0.00 sec)

十三、查询test表前2行

1
2
3
4
5
6
7
8
mysql> select * from test limit 2;
+-----------+------+---------------------+
| name      | sex  | time                |
+-----------+------+---------------------+
| guanggai2 | V    | 2017-11-24 17:11:53 |
| xoapmig   | m    | 2017-12-04 15:58:24 |
+-----------+------+---------------------+
2 rows in set (0.00 sec)

十四、查询test表第二条至第三条数据

1
2
3
4
5
6
7
8
mysql> select * from test limit 1,2;                              
+---------+------+---------------------+
| name    | sex  | time                |
+---------+------+---------------------+
| xoapmig | m    | 2017-12-04 15:58:24 |
| xoapmig | m    | 2017-10-11          |
+---------+------+---------------------+
2 rows in set (0.01 sec)

十五、查询test表name中第4个字母为n的数据

1
2
3
4
5
6
7
mysql> select * from test where name like "%%%n%”;                   
+-----------+------+---------------------+
| name      | sex  | time                |
+-----------+------+---------------------+
| guanggai2 | V    | 2017-11-24 17:11:53 |
+-----------+------+---------------------+
1 row in set (0.00 sec)

十六、查询test表name中类似x*ap的数据

1
2
3
4
5
6
7
mysql> select * from test where name like 'x_ap';
+------+------+-----------+
| name | sex  | time      |
+------+------+-----------+
| xoap | m    | 2017-9-11 |
+------+------+-----------+
1 row in set (0.00 sec)

十七、查询pet表包含正好5个字符的名字

1
2
3
4
5
6
7
8
mysql> SELECT * FROM pet WHERE name LIKE "_____";            
+-------+--------+---------+-----+------------+------+
| name  | owner  | species | sex | birth      | death|
+-------+--------+---------+-----+------------+------+
| Claws | Gwen   | cat     | m   | 1994-03-17 | NULL |
+------+---------+---------+-----+------------+------+
| Buffy | Harold | dog     | f   | 1989-05-13 | NULL |
+-------+--------+---------+-----+------------+------+

十八、查询test表name中以ig结尾的数据

1
2
3
4
5
6
7
8
mysql> select * from test where name regexp "ig$”;                
+---------+------+---------------------+
| name    | sex  | time                |
+---------+------+---------------------+
| xoapmig | m    | 2017-12-04 15:58:24 |
| Xoapmig | m    | 2017-10-11          |
+---------+------+---------------------+
2 rows in set (0.00 sec)
1
2
如 SELECT * FROM ［user］ WHERE u_name LIKE ‘%三%’
将会把u_name为“张三”，“张猫三”、“三脚猫”，“唐三藏”等等有“三”的记录全找出来。