沧州网站建设设计,太原网络推广,扬州注册公司,网站建设选平台一、介绍
MySQL数据操作#xff1a; DML
在MySQL管理软件中#xff0c;可以通过SQL语句中的DML语言来实现数据的操作#xff0c;包括 使用INSERT实现数据的插入 UPDATE实现数据的更新 使用DELETE实现数据的删除 使用SELECT查询数据以及。
二、插入数据INSERT
1. 插入完整…一、介绍
MySQL数据操作 DML
在MySQL管理软件中可以通过SQL语句中的DML语言来实现数据的操作包括 使用INSERT实现数据的插入 UPDATE实现数据的更新 使用DELETE实现数据的删除 使用SELECT查询数据以及。
二、插入数据INSERT
1. 插入完整数据顺序插入语法一INSERT INTO 表名(字段1,字段2,字段3…字段n) VALUES(值1,值2,值3…值n);语法二INSERT INTO 表名 VALUES (值1,值2,值3…值n);2. 指定字段插入数据语法INSERT INTO 表名(字段1,字段2,字段3…) VALUES (值1,值2,值3…);3. 插入多条记录语法INSERT INTO 表名 VALUES(值1,值2,值3…值n),(值1,值2,值3…值n),(值1,值2,值3…值n);4. 插入查询结果语法INSERT INTO 表名(字段1,字段2,字段3…字段n) SELECT (字段1,字段2,字段3…字段n) FROM 表2WHERE …;
三、更新数据UPDATE
语法UPDATE 表名 SET字段1值1,字段2值2,WHERE CONDITION;示例UPDATE mysql.user SET passwordpassword(‘123’) where user’root’ and host’localhost’; 四、删除数据DELETE
语法DELETE FROM 表名 WHERE CONITION;示例DELETE FROM mysql.user WHERE password’’;练习更新MySQL root用户密码为mysql123删除除从本地登录的root用户以外的所有用户
五、查询数据SELECT-单表查询
1、单表查询的语法
SELECT 字段1,字段2... FROM 表名WHERE 条件GROUP BY fieldHAVING 筛选ORDER BY fieldLIMIT 限制条数
2、关键字的执行优先级
from
where
group by
having
select
distinct
order by
limit
1.找到表:from
2.拿着where指定的约束条件去文件/表中取出一条条记录
3.将取出的一条条记录进行分组group by如果没有group by则整体作为一组
4.将分组的结果进行having过滤
5.执行select
6.去重
7.将结果按条件排序order by
8.限制结果的显示条数
3、简单查询
准备表和记录
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,
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
);# 查看表结构
mysql desc employee;
-------------------------------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-------------------------------------------------------------------------
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| sex | enum(male,female) | NO | | male | |
| age | int(3) unsigned | NO | | 28 | |
| hire_date | date | NO | | NULL | |
| post | varchar(50) | YES | | NULL | |
| post_comment | varchar(100) | YES | | NULL | |
| salary | double(15,2) | YES | | NULL | |
| office | int(11) | YES | | NULL | |
| depart_id | int(11) | YES | | NULL | |
-------------------------------------------------------------------------# 插入记录
# 三个部门教学销售运营
insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values
(egon,male,18,20170301,老男孩驻沙河办事处外交大使,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)
;# ps如果在windows系统中插入中文字符select的结果为空白可以将所有字符编码统一设置成gbk
简单查询 SELECT id,name,sex,age,hire_date,post,post_comment,salary,office,depart_id FROM employee;SELECT * FROM employee;SELECT name,salary FROM employee;# 避免重复DISTINCTSELECT DISTINCT post FROM employee; # 通过四则运算查询SELECT name, salary*12 FROM employee;SELECT name, salary*12 AS Annual_salary FROM employee;SELECT name, salary*12 Annual_salary FROM employee;# 定义显示格式CONCAT() 函数用于连接字符串SELECT CONCAT(姓名: ,name, 年薪: , salary*12) AS Annual_salary FROM employee;CONCAT_WS() 第一个参数为分隔符SELECT CONCAT_WS(:,name,salary*12) AS Annual_salary FROM employee;结合CASE语句SELECT(CASEWHEN NAME egon THENNAMEWHEN NAME alex THENCONCAT(name,_BIGSB)ELSEconcat(NAME, SB)END) as new_nameFROMemp;
小练习
1 查出所有员工的名字薪资,格式为名字:egon 薪资:3000
2 查出所有的岗位去掉重复
3 查出所有员工名字以及他们的年薪,年薪的字段名为annual_year
答案
select concat(名字:,name, ,薪资:,salary,) from employee;select distinct depart_id from employee;select name,salary*12 annual_salary from employee;
4、WHERE约束 where字句中可以使用
1. 比较运算符 !
2. between 80 and 100 值在10到20之间
3. in(80,90,100) 值是10或20或30
4. like egon%pattern可以是%或_%表示任意多字符_表示一个字符
5. 逻辑运算符在多个条件直接可以使用逻辑运算符 and or not1:单条件查询SELECT name FROM employeeWHERE postsale;#2:多条件查询SELECT name,salary FROM employeeWHERE postteacher AND salary10000;#3:关键字BETWEEN ANDSELECT name,salary FROM employee WHERE salary BETWEEN 10000 AND 20000;SELECT name,salary FROM employee WHERE salary NOT BETWEEN 10000 AND 20000;#4:关键字IS NULL(判断某个字段是否为NULL不能用等号需要用IS)SELECT name,post_comment FROM employee WHERE post_comment IS NULL;SELECT name,post_comment FROM employee WHERE post_comment IS NOT NULL;SELECT name,post_comment FROM employee WHERE post_comment; 注意是空字符串不是nullps执行update employee set post_comment where id2;再用上条查看就会有结果了#5:关键字IN集合查询SELECT name,salary FROM employee WHERE salary3000 OR salary3500 OR salary4000 OR salary9000 ;SELECT name,salary FROM employee WHERE salary IN (3000,3500,4000,9000) ;SELECT name,salary FROM employee WHERE salary NOT IN (3000,3500,4000,9000) ;#6:关键字LIKE模糊查询通配符’%’SELECT * FROM employee WHERE name LIKE eg%;通配符’_’SELECT * FROM employee WHERE name LIKE al__;
小练习
1. 查看岗位是teacher的员工姓名、年龄 2. 查看岗位是teacher且年龄大于30岁的员工姓名、年龄 3. 查看岗位是teacher且薪资在9000-1000范围内的员工姓名、年龄、薪资 4. 查看岗位描述不为NULL的员工信息 5. 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资 6. 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资 7. 查看岗位是teacher且名字是jin开头的员工姓名、年薪
答案
1. select name,age from employee where post teacher;
2. select name,age from employee where postteacher and age 30;
3. select name,age,salary from employee where postteacher and salary between 9000 and 10000;
4. select * from employee where post_comment is not null;
5. select name,age,salary from employee where postteacher and salary in (10000,9000,30000);
6. select name,age,salary from employee where postteacher and salary not in (10000,9000,30000);
7. select name,salary*12 from employee where postteacher and name like jin%;
5、分组查询:GROUP BY
1、首先明确一点分组发生在where之后即分组是基于where之后得到的记录而进行的
2、分组指的是将所有记录按照某个相同字段进行归类比如针对员工信息表的职位分组或者按照性别进行分组等 取每个部门的最高工资 取每个部门的员工数 取男生数和女生数 小窍门‘每’这个字后面的字段就是我们分组的依据
4、大前提 可以按照任意字段分组但是分组完毕后比如group by post只能查看post字段如果想查看组内信息需要借助于聚合函数 ONLY_FULL_GROUP_BY SQL_MODE设置
mysql select global.sql_mode;
-------------------
| global.sql_mode |
-------------------
| |
-------------------
1 row in set (0.00 sec)mysql select * from emp group by post;
------------------------------------------------------------------------------------------------------------
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
------------------------------------------------------------------------------------------------------------
| 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 |
| 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 |
| 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
| 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL | 7300.33 | 401 | 1 |
------------------------------------------------------------------------------------------------------------
4 rows in set (0.00 sec)# 由于没有设置ONLY_FULL_GROUP_BY,于是也可以有结果默认都是组内的第一条记录但其实这是没有意义的mysql set global sql_modeONLY_FULL_GROUP_BY;
Query OK, 0 rows affected (0.00 sec)mysql quit #设置成功后一定要退出然后重新登录方可生效
Byemysql use db1;
Database changed
mysql select * from emp group by post; #报错
ERROR 1055 (42000): db1.emp.id isnt in GROUP BY
mysql select post,count(id) from emp group by post; #只能查看分组依据和使用聚合函数
---------------------------------------
| post | count(id) |
---------------------------------------
| operation | 5 |
| sale | 5 |
| teacher | 7 |
| 老男孩驻沙河办事处外交大使 | 1 |
---------------------------------------
4 rows in set (0.00 sec)
GROUP BY
单独使用GROUP BY关键字分组SELECT post FROM employee GROUP BY post;注意我们按照post字段分组那么select查询的字段只能是post想要获取组内的其他相关信息需要借助函数GROUP BY关键字和GROUP_CONCAT()函数一起使用SELECT post,GROUP_CONCAT(name) FROM employee GROUP BY post;#按照岗位分组并查看组内成员名SELECT post,GROUP_CONCAT(name) as emp_members FROM employee GROUP BY post;GROUP BY与聚合函数一起使用select post,count(id) as count from employee group by post;#按照岗位分组并查看每个组有多少人
强调
如果我们用unique的字段作为分组的依据则每一条记录自成一组这种分组没有意义
多条记录之间的某个字段值相同该字段通常用来作为分组的依据
四 聚合函数
强调聚合函数聚合的是组的内容若是没有分组则默认一组
示例 SELECT COUNT(*) FROM employee;SELECT COUNT(*) FROM employee WHERE depart_id1;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_id3;
小练习 1. 查询岗位名以及岗位包含的所有员工名字 2. 查询岗位名以及各岗位内包含的员工个数 3. 查询公司内男员工和女员工的个数 4. 查询岗位名以及各岗位的平均薪资 5. 查询岗位名以及各岗位的最高薪资 6. 查询岗位名以及各岗位的最低薪资 7. 查询男员工与男员工的平均薪资女员工与女员工的平均薪资 #题1分组
mysql select post,group_concat(name) from employee group by post;
--------------------------------------------------------------------------------------------------
| post | group_concat(name) |
--------------------------------------------------------------------------------------------------
| operation | 张野,程咬金,程咬银,程咬铜,程咬铁 |
| sale | 歪歪,丫丫,丁丁,星星,格格 |
| teacher | alex,wupeiqi,yuanhao,liwenzhou,jingliyang,jinxin,成龙 |
| 老男孩驻沙河办事处外交大使 | egon |
--------------------------------------------------------------------------------------------------#题目2
mysql select post,count(id) from employee group by post;
----------------------------------------------------
| post | count(id) |
----------------------------------------------------
| operation | 5 |
| sale | 5 |
| teacher | 7 |
| 老男孩驻沙河办事处外交大使 | 1 |
----------------------------------------------------#题目3
mysql select sex,count(id) from employee group by sex;
-------------------
| sex | count(id) |
-------------------
| male | 10 |
| female | 8 |
-------------------#题目4
mysql select post,avg(salary) from employee group by post;
--------------------------------------------------------
| post | avg(salary) |
--------------------------------------------------------
| operation | 16800.026000 |
| sale | 2600.294000 |
| teacher | 151842.901429 |
| 老男孩驻沙河办事处外交大使 | 7300.330000 |
--------------------------------------------------------#题目5
mysql select post,max(salary) from employee group by post;
------------------------------------------------------
| post | max(salary) |
------------------------------------------------------
| operation | 20000.00 |
| sale | 4000.33 |
| teacher | 1000000.31 |
| 老男孩驻沙河办事处外交大使 | 7300.33 |
------------------------------------------------------#题目6
mysql select post,min(salary) from employee group by post;
------------------------------------------------------
| post | min(salary) |
------------------------------------------------------
| operation | 10000.13 |
| sale | 1000.37 |
| teacher | 2100.00 |
| 老男孩驻沙河办事处外交大使 | 7300.33 |
------------------------------------------------------#题目七
mysql select sex,avg(salary) from employee group by sex;
-----------------------
| sex | avg(salary) |
-----------------------
| male | 110920.077000 |
| female | 7250.183750 |
-----------------------
六、多表查询
# 建表
create table department(
id int,
name varchar(20)
);create table employee(
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 employee(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)
;# 查看表结构和数据
mysql desc department;
-----------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-----------------------------------------------
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
-----------------------------------------------mysql desc employee;
-------------------------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-------------------------------------------------------------------
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| sex | enum(male,female) | NO | | male | |
| age | int(11) | YES | | NULL | |
| dep_id | int(11) | YES | | NULL | |
-------------------------------------------------------------------mysql select * from department;
--------------------
| id | name |
--------------------
| 200 | 技术 |
| 201 | 人力资源 |
| 202 | 销售 |
| 203 | 运营 |
--------------------mysql select * from employee;
--------------------------------------
| id | name | sex | age | dep_id |
--------------------------------------
| 1 | egon | male | 18 | 200 |
| 2 | alex | female | 48 | 201 |
| 3 | wupeiqi | male | 38 | 201 |
| 4 | yuanhao | female | 28 | 202 |
| 5 | liwenzhou | male | 18 | 200 |
| 6 | jingliyang | female | 18 | 204 |
--------------------------------------
2、多表连接查询
重点外链接语法
SELECT 字段列表FROM 表1 INNER|LEFT|RIGHT JOIN 表2ON 表1.字段 表2.字段;
1 交叉连接不适用任何匹配条件。生成笛卡尔积mysql select * from employee,department;
----------------------------------------------------------
| id | name | sex | age | dep_id | id | name |
----------------------------------------------------------
| 1 | egon | male | 18 | 200 | 200 | 技术 |
| 1 | egon | male | 18 | 200 | 201 | 人力资源 |
| 1 | egon | male | 18 | 200 | 202 | 销售 |
| 1 | egon | male | 18 | 200 | 203 | 运营 |
| 2 | alex | female | 48 | 201 | 200 | 技术 |
| 2 | alex | female | 48 | 201 | 201 | 人力资源 |
| 2 | alex | female | 48 | 201 | 202 | 销售 |
| 2 | alex | female | 48 | 201 | 203 | 运营 |
| 3 | wupeiqi | male | 38 | 201 | 200 | 技术 |
| 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 |
| 3 | wupeiqi | male | 38 | 201 | 202 | 销售 |
| 3 | wupeiqi | male | 38 | 201 | 203 | 运营 |
| 4 | yuanhao | female | 28 | 202 | 200 | 技术 |
| 4 | yuanhao | female | 28 | 202 | 201 | 人力资源 |
| 4 | yuanhao | female | 28 | 202 | 202 | 销售 |
| 4 | yuanhao | female | 28 | 202 | 203 | 运营 |
| 5 | liwenzhou | male | 18 | 200 | 200 | 技术 |
| 5 | liwenzhou | male | 18 | 200 | 201 | 人力资源 |
| 5 | liwenzhou | male | 18 | 200 | 202 | 销售 |
| 5 | liwenzhou | male | 18 | 200 | 203 | 运营 |
| 6 | jingliyang | female | 18 | 204 | 200 | 技术 |
| 6 | jingliyang | female | 18 | 204 | 201 | 人力资源 |
| 6 | jingliyang | female | 18 | 204 | 202 | 销售 |
| 6 | jingliyang | female | 18 | 204 | 203 | 运营 |
----------------------------------------------------------
2 内连接只连接匹配的行#找两张表共有的部分相当于利用条件从笛卡尔积结果中筛选出了正确的结果
#department没有204这个部门因而employee表中关于204这条员工信息没有匹配出来
mysql select employee.id,employee.name,employee.age,employee.sex,department.name from employee inner join department on employee.dep_iddepartment.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 | 技术 |
-------------------------------------------#上述sql等同于
mysql select employee.id,employee.name,employee.age,employee.sex,department.name from employee,department where employee.dep_iddepartment.id;
3 外链接之左连接优先显示左表全部记录# 以左表为准即找出所有员工信息当然包括没有部门的员工
# 本质就是在内连接的基础上增加左边有右边没有的结果
mysql select employee.id,employee.name,department.name as depart_name from employee left join department on employee.dep_iddepartment.id;
------------------------------
| id | name | depart_name |
------------------------------
| 1 | egon | 技术 |
| 5 | liwenzhou | 技术 |
| 2 | alex | 人力资源 |
| 3 | wupeiqi | 人力资源 |
| 4 | yuanhao | 销售 |
| 6 | jingliyang | NULL |
------------------------------4 外链接之右连接优先显示右表全部记录# 以右表为准即找出所有部门信息包括没有员工的部门
# 本质就是在内连接的基础上增加右边有左边没有的结果
mysql select employee.id,employee.name,department.name as depart_name from employee right join department on employee.dep_iddepartment.id;
-------------------------------
| id | name | depart_name |
-------------------------------
| 1 | egon | 技术 |
| 2 | alex | 人力资源 |
| 3 | wupeiqi | 人力资源 |
| 4 | yuanhao | 销售 |
| 5 | liwenzhou | 技术 |
| NULL | NULL | 运营 |
-------------------------------5 全外连接显示左右两个表全部记录全外连接在内连接的基础上增加左边有右边没有的和右边有左边没有的结果
# 注意mysql不支持全外连接 full JOIN
# 强调mysql可以使用此种方式间接实现全外连接
select * from employee left join department on employee.dep_id department.id
union
select * from employee right join department on employee.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会去掉相同的纪录
3、符合条件连接查询
# 示例1以内连接的方式查询employee和department表并且employee表中的age字段值必须大于25,即找出年龄大于25岁的员工以及员工所在的部门
select employee.name,department.name from employee inner join departmenton employee.dep_id department.idwhere age 25;# 示例2以内连接的方式查询employee和department表并且以age字段的升序方式显示
select employee.id,employee.name,employee.age,department.name from employee,departmentwhere employee.dep_id department.idand age 25order by age asc; 4、子查询
#1子查询是将一个查询语句嵌套在另一个查询语句中。
#2内层查询语句的查询结果可以为外层查询语句提供查询条件。
#3子查询中可以包含IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
#4还可以包含比较运算符 、 !、 、等
# 带IN关键字的子查询
# 查询平均年龄在25岁以上的部门名
select id,name from departmentwhere id in (select dep_id from employee group by dep_id having avg(age) 25);# 查看技术部员工姓名
select name from employeewhere 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);
