数据查询、修改指令
DML
-
insert语句
- insert into 表名字段名 values(值列表); (插入一行的数据)
例:
insert into students(id,name,age,height,gender,ls. id,is_ .delete) values (0,小明,18,180.00,2,1,0);
insert into students values (0,小明',18,180.00,2,1,0);
注:对于设置了自动增长的id输.入0或者null,字段名是可选的,但值列表和字段名一-对应,多个列表和多个值之间使用逗号分隔
- insert into 表名字段名 values (值列表1),(值列表2),……; (插入多行数据)
例:
insert into students (id,name,age,height,gender,cls_ id,is_ delete) values (0,佩奇',8,130.00,2,1,0),0,超人;36,180.00,1,2,0);
注:为避免表结构发生变化引发错误,建议插入数据时写明具体字段名
-
update语句
- update 表名 set 字段1=值1,字段2=值2, where条件;
例: UPDATE student SET name =邓超",gender=1 WHERE id=1;
注: 1、更新多列数据使用逗号隔开; 2.添加条件限制
-
delete语句
- delete from 表名 where 条件;
例:
delete from students;(慎用)
delete from students where id=1;
-
truncate语句(用的少)
- truncate table 表名;
例:
TRUNCATE TABLE students;
TRUNCATE TABLE students where id = 1;
DQL
一、
-
select基本查询
- select 列名 from 表名;
例:
-- SELECT * FROM classes; *代表全部
-- SELECT name FROM classes;
-- SELECT name,gender FROM student;
-- SELECT name as "姓名",gender as "性别" from student; as 起别名
-
where条件查询
- select 列名 from 表名 where 条件;
例:
-- SELECT name,gender FROM student WHERE id=1;
-- SELECT name,gender FROM student WHERE name="邓超";
-
where多条件查询
- select 列名 from 表名 where 条件运算符条件;
例:
-- SELECT * FROM student WHERE gender=2 and cls_id=2;
-- SELECT * FROM student WHERE gender!=2;
-
like模糊查询
- select 列名 from 表名 where 字段 like'值';
例:
--select * from students where name like '小%';
-- SELECT * FROM classes WHERE name LIKE"%08%";
-
in关键字查询
- Select 列名 from 表名 where 字段 in(值1,值2,…);
例:
-- SELECT *FROM student WHERE id IN(1,7,8);
-
between关键字查询
- select 列名 from 表名where 字段 between 值1 and 值2;
例:
-- SELECT *FROM student WHERE id BETWEEN 5 and 10;
-
distinct关键字查询(去重)
- select distinct 字段名1,字段名2…from 表名;
例:
-- SELECT DISTINCT gender FROM student;
-
order by关键字查询(排序)
- SELECT <字段名列表> FROM <表名>[WHERE <查询条件>][ORDER BY <排序的列名>[ASC 或 DESC]];
例:
-- SELECT * FROM student ORDER BY height;
-- SELECT * FROM student ORDER BY height DESC,age;
-- SELECT * FROM student ORDER BY height DESC,age DESC;
-
limit关键字查询(分页使用)
- SELECT <字段名列表> FROM <表名> [WHERE <查询条件>][ORDER BY <排序的列名> [ASC 或 DESC]][LIMIT <行数>];
例:
-- SELECT * FROM student LIMIT 3;
-- SELECT * FROM student LIMIT 3,3; limit(start(行标0开始),length)
-
group by
-
单独使用GROUP BY关键字分组
- SELECT post FROM employee GROUP BY post;
-
注意:我们按照post字段分组,那么select查询的字段只能是post,想要获取组内的其他相关信息,需要借助函数
-
GROUP BY关键字和GROUP_CONCAT()函数一起使用
- SELECT post,GROUP_CONCAT(emp_name) FROM employee GROUP BY post; #按照岗位分组,并查看组内成员名
- SELECT post,GROUP_CONCAT(emp_name) as emp_members FROM employee GROUP BY post;
-
GROUP BY与聚合函数一起使用
- select post,count(id) as count from employee group by post; #按照岗位分组,并查看每个组有多少人
-
强调:
- 如果我们用unique的字段作为分组的依据,则每一条记录自成一组,这种分组没有意义多条记录之间的某个字段值相同,该字段通常用来作为分组的依据
-
HAVING过滤
-
having与where不同点
- where针对表中的列发挥作用,查询数据;having针对查询结果中的列发挥作用,筛选数据
- where后面不能写分组函数,而having后面可以使用分组函数
- having只用于group by分组统计语句
- 执行优先级从高到低:where > group by > having
- Where 发生在分组group by之前,因而Where中可以有任意字段,但是绝对不能使用聚合函数。
- Having发生在分组group by之后,因而Having中可以使用分组的字段,无法直接取到其他字段,可以使用聚合函数
- select * from emp where salary > 100000;
-
-
使用正则表达式查询
- SELECT * FROM employee WHERE emp_name REGEXP '^ale';
- SELECT * FROM employee WHERE emp_name REGEXP 'on$';
- SELECT * FROM employee WHERE emp_name REGEXP 'm{2}';
小结:对字符串匹配的方式
WHERE emp_name = 'egon';
WHERE emp_name LIKE 'yua%';
WHERE emp_name REGEXP 'on$';
二、
-
连接查询
-
显示内连接查询
- select 字段 from 表1 inner join 表2 on 连接条件 where 条件;
-
例:
-- SELECT s.name,c.name FROM student s INNER JOIN classes c on s.cls_id = c.id;
-
隐式内连接
- select 字段 from 表1,表2 where 表.条件 = 表2.条件;
例:
-- select s.name as '名字',c.name as '班级' from students s, classes c where s.cls_id = c.id;
-
左外连接
- select 字段 from 表1 left join 表2 on 连接条件 where 条件;
例:
-- SELECT c.name,t.name FROM classes c LEFT JOIN teacher t on c.t_id = t.id;
-
右外连接
- select 字段 from 表1 right join 表2 on 连接条件 where 条件;
例:
-- SELECT c.name,t.name FROM classes c RIGHT JOIN teacher t on c.t_id = t.id;
-
聚合函数
- select 聚合函数<字段> from 表名 where 条件 group by 聚合函数;
例:
-- SELECT AVG(height) as '平均身高' FROM student;
-- SELECT COUNT(*) as '总数' FROM student;
-- SELECT MAX(age) FROM student;
-- SELECT MIN(age) FROM student;
-- SELECT SUM(age) FROM student;
-
子查询
-
简单子查询
- SELECT * FROM student WHERE cls_id=(SELECT cls_id FROM student WHERE name ='赵六');
-
any/some子查询
- SELECT * FROM student WHERE cls_id = ANY (SELECT id FROM classes WHERE t_id = (SELECT id FROM teacher WHERE name = '王老师'));
注:any和some用法相同,都是返回子查询结果的任意一个值
-
all子查询
- select * from students where cls_id >= all(select id from classes where teacher_id = (select id from teachers where name='赵老师') );
注:外侧中的查询结果要大于等于子查询结果的所有值
-
in子查询
- select * from students where id in (1,5,6,10);
注:子查询返回多行数据时可以使用IN关键字查询包含在内的数据
-
not in子查询
- select * from students where id not in (1,5,6,10);
注:子查询返回多行数据时可以使用NOT IN关键字查询不包含在内的数据
-
exists子查询
- drop table if exists temp; // 删除表
- SELECT * FROM student WHERE EXISTS(SELECT * FROM teacher WHERE name ='王老师');
-
not exists子查询
- select * from classes where not exists (select * from teachers where name='王');
-
通配符 |
解释 |
示例 |
_ |
一个字符 |
A LIKE 'C_' |
% |
任意长度的字符串 |
B LIKE 'CO%' |
[ ] |
括号中所指定范围内的一个字符 |
C LIKE '9W0[1-2]' |
[^] |
不在括号中所指定范围内的一个字符 |
D LIKE '9W0[^1-2]' |