记录操作:增删改查
插入数据
- 插入完整数据(顺序插入)
- 语法一: INSERT INTO 表名(字段1,字段2,字段3…字段n) VALUES(值1,值2,值3…值n);
语法二: INSERT INTO 表名 VALUES (值1,值2,值3…值n); - 指定字段插入数据
语法: INSERT INTO 表名(字段1,字段2,字段3…) VALUES (值1,值2,值3…); - 插入多条记录
语法: INSERT INTO 表名 VALUES (值1,值2,值3…值n), (值1,值2,值3…值n), (值1,值2,值3…值n); - 插入查询结果
语法: INSERT INTO 表名(字段1,字段2,字段3…字段n) SELECT (字段1,字段2,字段3…字段n) FROM 表2 WHERE …;
insert into stu_info(id,name,age) select stu_id,stu_name,age2 from new_stu_info where stu_id = 11;
更新数据
语法:
UPDATE 表名 SET
字段1=值1,
字段2=值2,
WHERE CONDITION;
实例:
UPDATE mysql.user SET password=password(‘123’)
where user=’root’ and host=’localhost’;=
update stu_info set name="EE",age=300 where id=5
删除数据
语法:
DELETE FROM 表名
WHERE CONITION;
实例:
DELETE FROM mysql.user
WHERE password=’’;
delete from stu_info where age=18;
查询数据
单表查询
-
语法
SELECT DISTINCT 字段1,字段2… FROM 表名
WHERE 条件
GROUP BY field
HAVING 筛选
ORDER BY field
LIMIT 限制条数 -
优先级
from
where
group by
select
distinct
having
order by
limit
1.找到表:from
2.拿着where指定的约束条件,去文件/表中取出一条条记录
3.将取出的一条条记录进行分组group by,如果没有group by,则整体作为一组
4.执行select(去重)
5.将分组的结果进行having过滤
6.将结果按条件排序:order by
7.限制结果的显示条数
实例:
company.employee
员工id id int
姓名 emp_name varchar
性别 sex enum
年龄 age int
入职日期 hire_date date
岗位 post varchar
职位描述 post_comment varchar
薪水 salary double
办公室 office int
部门编号 depart_id int
#建表
create table employee(
id int not null unique auto_increment,
emp_name varchar(20) not null,
sex enum('male','female') not null default 'male',
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int,
depart_id int
);
#插入数据
insert into employee(emp_name,sex,age,hire_date,post,salary,office,depart_id) values
('dapeng','male',18,'20170301','boss',7300.33,401,1),
('alex','male',78,'20150302','teacher',1000000.31,401,1),
('wupeiqi','male',81,'20130305','teacher',8300,401,1),
('yuanhao','male',73,'20140701','teacher',3500,401,1),
('liwenzhou','male',28,'20121101','teacher',2100,401,1),
('jingliyang','female',18,'20110211','teacher',9000,401,1),
('jinxin','male',18,'19000301','teacher',30000,401,1),
('成龙','male',48,'20101111','teacher',10000,401,1),
('歪歪','female',48,'20150311','sale',3000.13,402,2),
('丫丫','female',38,'20101101','sale',2000.35,402,2),
('丁丁','female',18,'20110312','sale',1000.37,402,2),
('星星','female',18,'20160513','sale',3000.29,402,2),
('格格','female',28,'20170127','sale',4000.33,402,2),
('张野','male',28,'20160311','operation',10000.13,403,3),
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3);
#简单查询
SELECT * FROM employee;
SELECT emp_name,salary FROM employee;
#避免重复
SELECT DISTINCT post FROM employee;
#四则运算查询
SELECT emp_name, salary*12 AS Annual_salary FROM employee;
where约束
- 语法
- 比较运算符:> < >= <= <> !=
- between 80 and 100 值在80到100之间
- in(80,90,100) 值是80或90或100
- like ‘e%’
通配符可以是%或_,
%表示任意多字符
_表示一个字符 - 逻辑运算符:在多个条件直接可以使用逻辑运算符 and or not
#1:单条件查询
SELECT emp_name FROM employee
WHERE post='sale';
#2:多条件查询
SELECT emp_name,salary FROM employee
WHERE post='teacher' AND salary>10000;
#3:关键字BETWEEN AND
SELECT emp_name,salary FROM employee
WHERE salary BETWEEN 10000 AND 20000;
SELECT emp_name,salary FROM employee
WHERE salary NOT BETWEEN 10000 AND 20000;
#4:关键字IS NULL(判断某个字段是否为NULL不能用等号,需要用IS)
SELECT emp_name,post_comment FROM employee
WHERE post_comment IS NULL;
SELECT emp_name,post_comment FROM employee
WHERE post_comment IS NOT NULL;
SELECT emp_name,post_comment FROM employee
WHERE post_comment=''; 注意''是空字符串,不是null
ps:
执行
update employee set post_comment='' where id=2;
再用上条查看,就会有结果了
#5:关键字IN集合查询
SELECT emp_name,salary FROM employee
WHERE salary=3000 OR salary=3500 OR salary=4000 OR salary=9000 ;
SELECT emp_name,salary FROM employee
WHERE salary IN (3000,3500,4000,9000) ;
SELECT emp_name,salary FROM employee
WHERE salary NOT IN (3000,3500,4000,9000) ;
#6:关键字LIKE模糊查询
通配符’%’
SELECT * FROM employee
WHERE emp_name LIKE 'eg%';
通配符’_’
SELECT * FROM employee
WHERE emp_name LIKE 'al__';
group by
单独使用GROUP BY关键字分组
SELECT post FROM employee GROUP BY post;
表中teacher为一组,sale为一组,operation为一组。
注意:我们按照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;#按照岗位分组,并查看每个组有多少人
PS:
1.如果我们用unique的字段作为分组的依据,则每一条记录自成一组,这种分组没有意义
2.多条记录之间的某个字段值相同,该字段通常用来作为分组的依据
聚合函数
强调:聚合函数聚合的是组的内容,若是没有分组,则默认一组
实例
SELECT COUNT(*) FROM employee; #总记录数
select count(1) from emploee
SELECT COUNT(*) FROM employee WHERE depart_id=1;#统计depart_id为1的
SELECT MAX(salary) FROM employee;
SELECT MIN(salary) FROM employee;
SELECT AVG(salary) FROM employee;
SELECT SUM(salary) FROM employee;
SELECT SUM(salary) FROM employee WHERE depart_id=3;
分组之后having过滤
执行优先级从高到低:where > group by > having
- Where 发生在分组group by之前,因而Where中可以有任意字段,但是绝对不能使用聚合函数。
- Having发生在分组group by之后,因而Having中可以使用分组的字段,无法直接取到其他字段,可以使用聚合函数
#想要得知每个部门中:薪水高于10000的人有哪些
#错误实例:
select post,group_concat(emp_name) from employee group by post # 分组之后并没有salary字段
select post,group_concat(emp_name) from employee group by post having salary > 10000;
#正确示例:你需要在分组前先把薪水高于10000的人过滤出来,然后再进行分组
select post,group_concat(emp_name) from employee where salary > 10000 group by post
order by
按单列排序
SELECT * FROM employee ORDER BY salary;
SELECT * FROM employee ORDER BY salary ASC;
SELECT * FROM employee ORDER BY salary DESC;
按多列排序:先按照age排序,如果年纪相同,则按照薪资排序
SELECT * from employee
ORDER BY age,
salary DESC;
实例
# 正序倒序实例单字段:
select id,emp_name,salary from employee where post='teacher' order by salary ASC;
select id,emp_name,salary from employee where post='teacher' order by salary desc;
# 正序倒序实例多字段: 先以第一个字段进行排序,如果字段相同则以下一个字段进行排序
select id,emp_name,age,salary from employee where post='teacher' order by age desc,salary desc;
# 示例:查询每个部门中:薪水高于1000的人有哪些并根据部门人数进行排序操作
select post,count(emp_name) as numbers from employee where salary > 1000 group by post order by numbers ASC;
limit
# 限制输出:limit
select id,emp_name,salary from employee where post='teacher' limit 3 # 输出前3行内容 起始位置为0
select id,emp_name,salary from employee where post='teacher' limit 5,2 # 输出的是第6和第7行内容,即从第六行开始输出两行
正则表达式:REGEXP关键字
实例:
select * from employee where emp_name regexp "^al"
#以al开头的员工名字
select * from employee where emp_name regexp "ao$"
#以ao结尾的员工名字
select * from employee where emp_name regexp "m{2}"
#同时出现两次m的员工名字
- 小结:关于对字符串的匹配
- = ‘string’
- like 通配符
- regexp 正则表达式
多表查询
- 建表及准备数据
#建表
create table department(
id int,
name varchar(20)
);
create table employee2(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);
#插入数据
insert into department values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');
insert into employee2(name,sex,age,dep_id) values
('egon','male',18,200),
('alex','female',48,201),
('wupeiqi','male',38,201),
('yuanhao','female',28,202),
('liwenzhou','male',18,200),
('jingliyang','female',18,204) ;
-
语法
重点:外链接语法
格式:
SELECT 字段列表
FROM 表1 INNER|LEFT|RIGHT JOIN 表2
ON 表1.字段 = 表2.字段; -
交叉链接:不适用任何匹配条件。生成笛卡尔积:第一张表n×第二张表m
MariaDB [test]> select * from employee2,department;
- 内连接:只连接匹配的行
查询每个员工所在的部门(两张表之间的关系:dep_id 和 部门id)
MariaDB [test]> select
employee2.id,employee2.name,employee2.age,employee2.sex,department.name
from employee2 inner join department on employee2.dep_id=department.id;
或
MariaDB [test]> select
employee2.id,employee2.name,employee2.age,employee2.sex,department.name from
employee2,department where employee2.dep_id=department.id;
+‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐+
| id | name | age | sex | name |
+‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐+
| 1 | egon | 18 | male | 技术 |
| 2 | alex | 48 | female | 人力资源 |
| 3 | wupeiqi | 38 | male | 人力资源 |
| 4 | yuanhao | 28 | female | 销售 |
| 5 | liwenzhou | 18 | male | 技术 |
+‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐+
- 外链接之左连接:优先显示左表全部记录
左表6号员工在右表department中没有匹配,但还是显示。
MariaDB [test]> select employee2.id,employee2.name,department.name as depart_name from
employee2 left join department on employee2.dep_id=department.id;
+‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐+
| id | name | depart_name |
+‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐+
| 1 | egon | 技术 |
| 5 | liwenzhou | 技术 |
| 2 | alex | 人力资源 |
| 3 | wupeiqi | 人力资源 |
| 4 | yuanhao | 销售 |
| 6 | jingliyang | NULL |
+‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐+
- 外链接之右连接:优先显示右表全部记录
右表department中运营在左表中没有匹配,但还是显示。
MariaDB [test]> select employee2.id,employee2.name,department.name as depart_name from
employee2 right join department on employee2.dep_id=department.id;
+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐+
| id | name | depart_name |
+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐+
| 1 | egon | 技术 |
| 2 | alex | 人力资源 |
| 3 | wupeiqi | 人力资源 |
| 4 | yuanhao | 销售 |
| 5 | liwenzhou | 技术 |
| NULL | NULL | 运营 |
+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐+
- 全外连接:显示左右两个表全部记录
MariaDB [test]> select * from employee2 left join department on employee2.dep_id = department.id
‐> union
‐> select * from employee2 right join department on employee2.dep_id = department.id;
+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐+
| id | name | sex | age | dep_id | id | name |
+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐+
| 1 | egon | male | 18 | 200 | 200 | 技术 |
| 5 | liwenzhou | male | 18 | 200 | 200 | 技术 |
| 2 | alex | female | 48 | 201 | 201 | 人力资源 |
| 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 |
| 4 | yuanhao | female | 28 | 202 | 202 | 销售 |
| 6 | jingliyang | female | 18 | 204 | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | 203 | 运营 |
+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐+
#注意 union与union all的区别:union会去掉相同的纪录
- 符合条件连接查询
示例1:以内连接的方式查询employee和department表,并且employee表中的age字段值必须大于25,即找出年龄 大于25岁的员工以及员工所在的部门
select employee2.name,department.name from employee2 inner join department
on employee2.dep_id = department.id
where employee2.age > 25;
示例2:以内连接的方式查询employee和department表,并且以age字段的升序方式显示
select * from employee2 inner join department
on employee.dep_id = department.id
order by employee2.age asc;
总结:对多表进行查询的时候,先把连接查询写下来(找到多张表之间的对应关系),然后根据查询记录可以当成单表进行操作(关键字:where ORDER BY …)
子查询
1:子查询是将一个查询语句嵌套在另一个查询语句中。
2:内层查询语句的查询结果,可以为外层查询语句提供查询条件。
3:子查询中可以包含:IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
4:还可以包含比较运算符:= 、 !=、> 、<等
- 带IN关键字的子查询
#查询平均年龄在25岁以上的部门名
select id,name from department
where id in
(select dep_id from employee2 group by dep_id having avg(age) > 25);
#查看技术部员工姓名
select name from employee2`
where dep_id in
(select id from department where name='技术');
#查看不足1人的部门名(子查询得到的是有人的部门id)
select name from department where id not in (select distinct dep_id from employee);