一、增加数据
1、插入所有字段,必须按顺序依次插入;如果插入的 数据个数 少于 字段个数 则会报错
mysql> insert into student values(1, "rose", 22);
Query OK, 1 row affected (0.00 sec)
2、插入部分字段,需要指明插入哪几个字段
mysql> insert into student(sid, name) values(2, "李四");
Query OK, 1 row affected
二、修改数据
1、修改所有数据:表中所有数据的 sage 都被改为 33
mysql> update student set sage = 33;
Query OK, 2 rows affected
Rows matched: 2 Changed: 2 Warnings: 0
2、带条件的修改:修改表中 sid 为 1 的数据,sage 被改为 44
mysql> update student set sage = 44 where sid = 1;
Query OK, 0 rows affected
Rows matched: 1 Changed: 0 Warnings: 0
3、修改多个字段
mysql> update student set name="张三", sage=18 where sid = 1;
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
三、删除数据
1、删除所有数据
mysql> delete from student;
Query OK, 2 rows affected
2、带条件的删除
mysql> delete from student where sid = 1;
Query OK, 1 row affected
3、使用 truncate 删除表中的数据
mysql> truncate table student;
Query OK, 0 rows affected
delete 和 truncate 都可以删除表中的所有数据,区别如下:
1)、delete 可以带条件删除,truncate 不能带条件删除;
2)、delete 只能删除表的数据,不能删除表的约束;而 truncate 既可以删除数据,也可以删除约束;
3)、使用 delete 删除的数据可以回滚,truncate 删除的数据不能回滚;
四、查询数据
1、查询所有列
mysql> select * from student;
+-----+------+------+
| sid | name | sage |
+-----+------+------+
| 1 | 张三 | 22 |
| 2 | lisi | 33 |
+-----+------+------+
2 rows in set
2、查询指定的列
mysql> select sid,name from student;
+-----+------+
| sid | name |
+-----+------+
| 1 | 张三 |
| 2 | lisi |
+-----+------+
2 rows in set
3、查询时添加常量列(查询时添加一个班级列,内容为“Java就业班”)
mysql> select sid,name,sage,"Java就业班" as "班级" from student;
+-----+------+------+------------+
| sid | name | sage | 班级 |
+-----+------+------+------------+
| 1 | 张三 | 22 | Java就业班 |
| 2 | lisi | 33 | Java就业班 |
+-----+------+------+------------+
2 rows in set
4、查询时合并列
当前 student 表中的数据为:
mysql> select * from student;
+-----+------+------+---------+-----+--------+
| sid | name | sage | servlet | jsp | gender |
+-----+------+------+---------+-----+--------+
| 1 | 张三 | 22 | 70 | 85 | 男 |
| 2 | lisi | 33 | 90 | 66 | 男 |
+-----+------+------+---------+-----+--------+
2 rows in set
查询时合并 servlet 和 jsp 列,查询总成绩(注意:合并列只能合并 数值(int) 类型的列)
mysql> select sid,name,sage,(servlet+jsp) as "总成绩" from student;
+-----+------+------+--------+
| sid | name | sage | 总成绩 |
+-----+------+------+--------+
| 1 | 张三 | 22 | 155 |
| 2 | lisi | 33 | 156 |
+-----+------+------+--------+
2 rows in set
5、查询时去除重复记录
当前 student 表中的数据为:
mysql> select * from student;
+-----+------+------+---------+-----+--------+
| sid | name | sage | servlet | jsp | gender |
+-----+------+------+---------+-----+--------+
| 1 | 张三 | 22 | 70 | 85 | 男 |
| 2 | lisi | 33 | 90 | 66 | 男 |
| 3 | rose | 18 | 88 | 77 | 女 |
| 4 | Lucy | 28 | 70 | 92 | 女 |
+-----+------+------+---------+-----+--------+
4 rows in set
查询性别列:使用关键字 distinct 去除重复记录
mysql> select distinct gender from student;
+--------+
| gender |
+--------+
| 男 |
| 女 |
+--------+
2 rows in set
distinct 还可以作为函数使用,作用和上面一样
mysql> select distinct(gender) from student;
+--------+
| gender |
+--------+
| 男 |
| 女 |
+--------+
2 rows in set
6、条件查询(where)
1)、逻辑条件:and(与)、or(或)
查询 sid 为 2,且 name 为 lisi 的数据
mysql> select * from student where sid=2 and name="lisi";
+-----+------+------+---------+-----+--------+
| sid | name | sage | servlet | jsp | gender |
+-----+------+------+---------+-----+--------+
| 2 | lisi | 33 | 90 | 66 | 男 |
+-----+------+------+---------+-----+--------+
1 row in set
查询 sid 为 2,或者 name 为 Lucy 的数据
mysql> select * from student where sid=2 or name="Lucy";
+-----+------+------+---------+-----+--------+
| sid | name | sage | servlet | jsp | gender |
+-----+------+------+---------+-----+--------+
| 2 | lisi | 33 | 90 | 66 | 男 |
| 4 | Lucy | 28 | 70 | 92 | 女 |
+-----+------+------+---------+-----+--------+
2 rows in set
2)、比较条件:> < >= <= =(等于) <>(不等于) (between and)
查询 servlet 成绩大于 80 的数据
mysql> select * from student where servlet > 80;
+-----+------+------+---------+-----+--------+
| sid | name | sage | servlet | jsp | gender |
+-----+------+------+---------+-----+--------+
| 2 | lisi | 33 | 90 | 66 | 男 |
| 3 | rose | 18 | 88 | 77 | 女 |
+-----+------+------+---------+-----+--------+
2 rows in set
查询 jsp 成绩大于 70 且小于 90 的数据
mysql> select * from student where jsp>70 and jsp<90;
+-----+------+------+---------+-----+--------+
| sid | name | sage | servlet | jsp | gender |
+-----+------+------+---------+-----+--------+
| 1 | 张三 | 22 | 70 | 85 | 男 |
| 3 | rose | 18 | 88 | 77 | 女 |
+-----+------+------+---------+-----+--------+
2 rows in set
查询 servlet 成绩大于等于 70,且小于等于 90 的数据(使用 between and,包前包后)
mysql> select * from student where servlet between 70 and 90;
+-----+------+------+---------+-----+--------+
| sid | name | sage | servlet | jsp | gender |
+-----+------+------+---------+-----+--------+
| 1 | 张三 | 22 | 70 | 85 | 男 |
| 2 | lisi | 33 | 90 | 66 | 男 |
| 3 | rose | 18 | 88 | 77 | 女 |
| 4 | Lucy | 28 | 70 | 92 | 女 |
+-----+------+------+---------+-----+--------+
4 rows in set
查询性别 不等于男 的数据
mysql> select * from student where gender <>"男";
+-----+------+------+---------+-----+--------+
| sid | name | sage | servlet | jsp | gender |
+-----+------+------+---------+-----+--------+
| 3 | rose | 18 | 88 | 77 | 女 |
| 4 | Lucy | 28 | 70 | 92 | 女 |
+-----+------+------+---------+-----+--------+
2 rows in set
3)、判空条件(null、空字符串):is null、is not null、=""、<>""
当前 student 表中的数据为:
mysql> select * from student;
+-----+------+------+---------+------+--------+
| sid | name | sage | servlet | jsp | gender |
+-----+------+------+---------+------+--------+
| 1 | 张三 | 22 | 70 | 85 | NULL |
| 2 | lisi | NULL | 90 | 66 | 男 |
| 3 | rose | 18 | 88 | NULL | |
| 4 | Lucy | 28 | 70 | 92 | 女 |
+-----+------+------+---------+------+--------+
4 rows in set
查询 gender 列为空的数据(包括 null 和 空字符串)
mysql> select * from student where gender is null or gender="";
+-----+------+------+---------+------+--------+
| sid | name | sage | servlet | jsp | gender |
+-----+------+------+---------+------+--------+
| 1 | 张三 | 22 | 70 | 85 | NULL |
| 3 | rose | 18 | 88 | NULL | |
+-----+------+------+---------+------+--------+
2 rows in set
查询 gender 列不为空的数据(既不能为 null,也不能为 空字符串)
mysql> select * from student where gender is not null and gender<>"";
+-----+------+------+---------+-----+--------+
| sid | name | sage | servlet | jsp | gender |
+-----+------+------+---------+-----+--------+
| 2 | lisi | NULL | 90 | 66 | 男 |
| 4 | Lucy | 28 | 70 | 92 | 女 |
+-----+------+------+---------+-----+--------+
2 rows in set
4)、模糊查询:like
查询 姓“张” 的数据( % 表示任意多个字符)
mysql> select * from student where name like "张%";
+-----+------+------+---------+-----+--------+
| sid | name | sage | servlet | jsp | gender |
+-----+------+------+---------+-----+--------+
| 1 | 张三 | 22 | 70 | 85 | NULL |
+-----+------+------+---------+-----+--------+
1 row in set
查询 姓“张”,且姓名只有两个字的数据( _ 表示任意一个字符)
mysql> select * from student where name like "张_";
+-----+------+------+---------+-----+--------+
| sid | name | sage | servlet | jsp | gender |
+-----+------+------+---------+-----+--------+
| 1 | 张三 | 22 | 70 | 85 | NULL |
+-----+------+------+---------+-----+--------+
1 row in set
7、聚合查询:使用聚合函数的查询;
常用的聚合函数:sum()、avg()、max()、min()、count()
1)、查询 servlet 的总成绩:sum() 求和函数
mysql> select sum(servlet) as "servlet总成绩" from student;
+---------------+
| servlet总成绩 |
+---------------+
| 318 |
+---------------+
1 row in set
2)、查询 servlet 的平均分:avg() 求平均数函数
mysql> select avg(servlet) as "servlet平均分" from student;
+---------------+
| servlet平均分 |
+---------------+
| 79.5000 |
+---------------+
1 row in set
3)、查询 servlet 的最高分:max() 求最大值函数
mysql> select max(servlet) as "servlet最高分" from student;
+---------------+
| servlet最高分 |
+---------------+
| 90 |
+---------------+
1 row in set
4)、统计当前表中有多少数据:count() 函数里面填字段,* 表示全字段;
mysql> select count(*) from student;
+----------+
| count(*) |
+----------+
| 4 |
+----------+
1 row in set
注意:count() 函数统计的数量不包括 null,如下所示表中 gender 列的数据为:
mysql> select sid,name,gender from student;
+-----+------+--------+
| sid | name | gender |
+-----+------+--------+
| 1 | 张三 | NULL |
| 2 | lisi | 男 |
| 3 | rose | |
| 4 | Lucy | 女 |
+-----+------+--------+
4 rows in set
使用 count(gender) 统计出来的数量为 3(count() 函数统计的数量 包括空字符串;)
mysql> select count(gender) from student;
+---------------+
| count(gender) |
+---------------+
| 3 |
+---------------+
1 row in set
8、分页查询:使用 limit 关键字,limit 后面带两个参数(起始行,查询几行),其中起始行的索引值从 0 开始;
1)、查询第 1,2 条记录:起始行为 0,查询 2 行(第 1 页的数据)
mysql> select * from student limit 0,2;
+-----+------+------+---------+-----+--------+
| sid | name | sage | servlet | jsp | gender |
+-----+------+------+---------+-----+--------+
| 1 | 张三 | 22 | 70 | 85 | NULL |
| 2 | lisi | NULL | 90 | 66 | 男 |
+-----+------+------+---------+-----+--------+
2 rows in set
2)、查询第 3,4 条记录:起始行为 2,查询 2 行(第 2 页的数据)
mysql> select * from student limit 2,2;
+-----+------+------+---------+------+--------+
| sid | name | sage | servlet | jsp | gender |
+-----+------+------+---------+------+--------+
| 3 | rose | 18 | 88 | NULL | |
| 4 | Lucy | 28 | 70 | 92 | 女 |
+-----+------+------+---------+------+--------+
2 rows in set
注意:分页查询需要知道两个参数:当前页 和 每页显示多少条数据
分页查询当前页数据的 sql 语句:select * from student limit (当前页-1)*每页显示多少条,每页显示多少条;
9、排序查询:order by 字段 asc/desc
asc:正序;数值列:递增;字母列:a-z
desc:倒序;数值列:递减;字母列:z-a
1)、默认情况下,按照插入记录的顺序排序;
mysql> select * from student;
+-----+------+------+---------+------+--------+
| sid | name | sage | servlet | jsp | gender |
+-----+------+------+---------+------+--------+
| 1 | 张三 | 22 | 70 | 85 | NULL |
| 2 | lisi | NULL | 90 | 66 | 男 |
| 6 | rose | 18 | 88 | NULL | |
| 4 | Lucy | 28 | 70 | 92 | 女 |
| 5 | Jack | 25 | 68 | 75 | 男 |
+-----+------+------+---------+------+--------+
5 rows in set
2)、按照 sid 顺序(正序)排序(默认情况下就是正序,不写 asc 也可以)
mysql> select * from student order by sid;
+-----+------+------+---------+------+--------+
| sid | name | sage | servlet | jsp | gender |
+-----+------+------+---------+------+--------+
| 1 | 张三 | 22 | 70 | 85 | NULL |
| 2 | lisi | NULL | 90 | 66 | 男 |
| 4 | Lucy | 28 | 70 | 92 | 女 |
| 5 | Jack | 25 | 68 | 75 | 男 |
| 6 | rose | 18 | 88 | NULL | |
+-----+------+------+---------+------+--------+
5 rows in set
3)、按照 sid 倒序排序
mysql> select * from student order by sid desc;
+-----+------+------+---------+------+--------+
| sid | name | sage | servlet | jsp | gender |
+-----+------+------+---------+------+--------+
| 6 | rose | 18 | 88 | NULL | |
| 5 | Jack | 25 | 68 | 75 | 男 |
| 4 | Lucy | 28 | 70 | 92 | 女 |
| 2 | lisi | NULL | 90 | 66 | 男 |
| 1 | 张三 | 22 | 70 | 85 | NULL |
+-----+------+------+---------+------+--------+
5 rows in set
4)、按照 servlet 正序,jsp 倒序排序:规则是 先按照 servlet 正序排列,如果 servlet 中有相同的数据,那么 servlet 相同
的这几条数据再按照 jsp 倒序排列;
mysql> select * from student order by servlet asc, jsp desc;
+-----+------+------+---------+------+--------+
| sid | name | sage | servlet | jsp | gender |
+-----+------+------+---------+------+--------+
| 5 | Jack | 25 | 68 | 75 | 男 |
| 4 | Lucy | 28 | 70 | 92 | 女 |
| 1 | 张三 | 22 | 70 | 85 | NULL |
| 6 | rose | 18 | 88 | NULL | |
| 2 | lisi | NULL | 90 | 66 | 男 |
+-----+------+------+---------+------+--------+
5 rows in set
10、分组查询:group by
查询 男、女 各有多少条数据(group by gender:按 gender 分组)
注意:null 和 空字符串 也占一组;
mysql> select gender,count(*) from student group by gender;
+--------+----------+
| gender | count(*) |
+--------+----------+
| NULL | 1 |
| | 1 |
| 女 | 1 |
| 男 | 2 |
+--------+----------+
4 rows in set
11、分组查询后 筛选:用 having 关键字
查询总人数大于 1 的性别:
1)、查询 男、女 的人数;
2)、筛选出人数大于 1 的记录:如果使用了 group by,后面就不能使用 where;
在 group by 分组之后再进行筛选,可以使用关键字 having;
在 group by 分组之前可以使用 where;
mysql> select gender,count(*) from student group by gender having count(*) > 1;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 男 | 2 |
+--------+----------+
1 row in set