#插入、查找数据
[mysql>create table if not exists exam_score(
..>id int(4) not null primary key auto_increment,
..>name char(20) not null,
..>score double(6,2)); #用多个list插入多行数据
[mysql> insert into exam_score values (1,'Zhao',95.33),(2,'Qian',94.33),(3,'Sun',44.55),(4,'Li',33.55);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
#展示整张表
mysql> select * from exam_score;
+----+------+-------+
| id | name | score |
+----+------+-------+
| 1 | Zhao | 95.33 |
| 2 | Qian | 94.33 |
| 3 | Sun | 44.55 |
| 4 | Li | 33.55 |
+----+------+-------+
4 rows in set (0.00 sec)
mysql> insert into exam_score values (5,'Zhou',66.33),(6,'Wu',99.32),(7,'Zheng',33.2),(8,'Wang',99.3);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
#查询按照字段id在0到2之间的行数据
mysql> select * from exam_score order by id limit 0,2;
+----+------+-------+
| id | name | score |
+----+------+-------+
| 1 | Zhao | 95.33 |
| 2 | Qian | 94.33 |
+----+------+-------+
2 rows in set (0.00 sec)
#查询整张表中的前两行
mysql> select * from exam_score limit 0,2;
+----+------+-------+
| id | name | score |
+----+------+-------+
| 1 | Zhao | 95.33 |
| 2 | Qian | 94.33 |
+----+------+-------+
2 rows in set (0.00 sec)
#按照name字段升序显示整张表
mysql> select * from exam_score order by name asc;
+----+-------+-------+
| id | name | score |
+----+-------+-------+
| 4 | Li | 33.55 |
| 2 | Qian | 94.33 |
| 3 | Sun | 44.55 |
| 8 | Wang | 99.30 |
| 6 | Wu | 99.32 |
| 1 | Zhao | 95.33 |
| 7 | Zheng | 33.20 |
| 5 | Zhou | 66.33 |
+----+-------+-------+
8 rows in set (0.01 sec)
#按照name降序显示
mysql> select * from exam_score order by name desc;
+----+-------+-------+
| id | name | score |
+----+-------+-------+
| 5 | Zhou | 66.33 |
| 7 | Zheng | 33.20 |
| 1 | Zhao | 95.33 |
| 6 | Wu | 99.32 |
| 8 | Wang | 99.30 |
| 3 | Sun | 44.55 |
| 2 | Qian | 94.33 |
| 4 | Li | 33.55 |
+----+-------+-------+
8 rows in set (0.00 sec)
#where条件查询
mysql> select * from exam_score where name='Li';
+----+------+-------+
| id | name | score |
+----+------+-------+
| 4 | Li | 33.55 |
+----+------+-------+
1 row in set (0.00 sec) mysql> select * from exam_score where id=3;
+----+------+-------+
| id | name | score |
+----+------+-------+
| 3 | Sun | 44.55 |
+----+------+-------+
1 row in set (0.00 sec) mysql> select * from exam_score where name='Zhao' and score<=99.0;
+----+------+-------+
| id | name | score |
+----+------+-------+
| 1 | Zhao | 95.33 |
+----+------+-------+
1 row in set (0.00 sec)
#修改数据
#连接表所在的数据库
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A Database changed
mysql> select * from exam_score;
+----+-------+-------+
| id | name | score |
+----+-------+-------+
| 1 | Zhao | 95.33 |
| 2 | Qian | 94.33 |
| 3 | Sun | 44.55 |
| 4 | Li | 33.55 |
| 5 | Zhou | 66.33 |
| 6 | Wu | 99.32 |
| 7 | Zheng | 33.20 |
| 8 | Wang | 99.30 |
+----+-------+-------+
8 rows in set (0.00 sec)
#用update修改符合条件的字段的值
#通过条件定位到行，修改行的某些字段的值
#也可以修改多个字段的值，中间用逗号隔开
mysql> update exam_score set score =62.5 where name='Zheng' and score<60.0;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
#配合replace修改符合条件的字段的值
#replace(字段，旧值，新值)
mysql> update exam_score set name=replace(name,'Wu','Xie') where id=6;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from exam_score;
+----+-------+-------+
| id | name | score |
+----+-------+-------+
| 1 | Zhao | 95.33 |
| 2 | Qian | 94.33 |
| 3 | Sun | 44.55 |
| 4 | Li | 33.55 |
| 5 | Zhou | 66.33 |
| 6 | Xie | 99.32 |
| 7 | Zheng | 62.50 |
| 8 | Wang | 99.30 |
+----+-------+-------+
8 rows in set (0.00 sec)
删除记录／数据表
#删除表
mysql> delete from exam_score where id=4;
Query OK, 1 row affected (0.00 sec)
#delete清空整张表,表还在只是空了
mysql> delete from exam_score;
Query OK, 7 rows affected (0.00 sec)
mysql> select * from exam_score;
Empty set (0.00 sec)
#drop清空表
mysql> drop table exam_score;
Query OK, 0 rows affected (0.01 sec) mysql> select * from exam_score;
ERROR 1146 (42S02): Table 'test.exam_score' doesn't exist