#插入、查找数据
[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