mysql DML操作、关联查询、联合查询、聚合函数使用

目录

DML操作

关联查询、联合查询

按条件查询、聚合函数使用

---

DML操作

CREATE DATABASE db2

USE db2;
CREATE TABLE stu(
	sid INT PRIMARY KEY AUTO_INCREMENT,
	sname VARCHAR(100) NOT NULL,
	age INT,
	address VARCHAR(100)
)

INSERT INTO stu VALUES(NULL,'赵明',23,'光明顶1'),(NULL,'周芷若',28,'光明顶2')

#DML-修改操作 update

#修改张无忌的地址是嵩山
UPDATE stu SET address='嵩山'  WHERE sname='张无忌'

#修改周芷若的年龄50和地址峨眉

UPDATE stu SET age='50',address= '峨眉' WHERE sname='周芷若'

#修改所有人的年龄都增加10岁
UPDATE stu SET age=age+10

#DML-删除操作  delete

#删除id=2的记录
DELETE FROM stu WHERE sid=2

#删除表里的所有记录
DELETE FROM stu

#把整张表先删除，再创建和之前相同结构表
TRUNCATE stu

##DML-简单查询语句

#查询stu表所有记录
SELECT sid,sname,age,address FROM stu
SELECT * FROM stu

#查询stu表所有的sid和sname值
SELECT sid,sname FROM stu

#别名操作  as可以省略不写
SELECT sid AS sid_id ,sname AS sname_name  FROM stu s

#着重号``

#查询名字是谢逊信息
SELECT * FROM stu WHERE sname='赵敏'

#查询sid大于2，并且年龄大于30的学生信息

SELECT * FROM stu WHERE sid>2 AND age>30


##演示一对多建表和外键使用
#创建部门表
CREATE TABLE dept(
	did INT PRIMARY KEY,
	dname VARCHAR(100)

)
#创建员工表
CREATE TABLE emp(
	eid INT PRIMARY KEY,
	ename VARCHAR(100),
	age INT,
	edid INT,
	FOREIGN KEY(edid) REFERENCES dept(did)
)

关联查询、联合查询

#笛卡尔积
SELECT * FROM dept，emp

#内连接查询
SELECT * FROM dept INNER JOIN emp ON dept.did=emp.edid

SELECT * FROM dept,emp WHERE dept.did=emp.edid

#外连接-左外连接

# select 字段 from 表1 left join 表2  on 关联条件
 SELECT * FROM dept LEFT JOIN emp ON dept.did=emp.edid
 
 #外连接-右外连接
 SELECT * FROM dept RIGHT JOIN emp ON dept.did=emp.edid
 
 #A表独有的结果
 
SELECT * FROM dept LEFT JOIN emp ON dept.did=emp.edid
WHERE emp.edid IS NULL

#B表独有的结果
SELECT * FROM dept RIGHT JOIN emp ON dept.did=emp.edid
WHERE dept.did IS NULL
 
 
#AB全都有
 
 SELECT * FROM dept LEFT JOIN emp ON dept.did=emp.edid
 UNION
 SELECT * FROM dept RIGHT JOIN emp ON dept.did=emp.edid
 
 
 #A独有加B独有
 SELECT * FROM dept LEFT JOIN emp ON dept.did=emp.edid WHERE emp.edid IS NULL
 UNION
 SELECT * FROM dept RIGHT JOIN emp ON dept.did=emp.edid WHERE dept.did IS NULL
 

按条件查询、聚合函数使用

#查询所有ename并且去重
SELECT DISTINCT ename FROM emp

#查询年龄大于20岁
SELECT * FROM emp WHERE age>20

#查询所有姓张人员信息  %代表通配符匹配任意内容
SELECT * FROM emp WHERE ename LIKE '张%'

#查询名字中以“亮”字结尾
SELECT * FROM emp WHERE ename LIKE '%亮'

#查询姓名包含张
SELECT * FROM emp WHERE ename LIKE '%张%'


#查询姓名里面第一个字母是m，m后面包含三个字母
SELECT * FROM emp WHERE ename LIKE 'M___'

#查询emp中所有记录，根据eid进行排序
SELECT * FROM emp ORDER BY age ASC
SELECT * FROM emp ORDER BY age DESC

#查询区间范围的值
#查询emp年龄23-30范围
SELECT * FROM emp WHERE age>=23 AND age<=30
SELECT * FROM emp WHERE  age BETWEEN 23 AND 100
 
#查询年龄是 21 22 23
SELECT * FROM emp WHERE  age IN(21,22,23)



#limit分页用法
#limit后面有两个参数
#第一个参数查询1数据开始的位置；第二个数据，每页显示多少条记录。
#查询位置开始位置公式：（当前页-1）*每页显示记录数
SELECT * FROM emp LIMIT 0,4
SELECT * FROM emp LIMIT 4,19



#mysql聚合函数
#count()统计功能；sum()求和；avg（）计算平均数；max（）获取最大值；min获取最小值

#count（）计数
SELECT COUNT(*) AS num FROM emp
#查询年龄大于23的人数
SELECT COUNT(*) FROM emp  WHERE age>23

#sum()求和
SELECT SUM(age) FROM emp

#avg()
SELECT AVG(age) FROM emp
##把平均数小数点后面得值进行控制
SELECT CAST(AVG(age)) AS DECIMAL(10,2) FROM emp

#max() min)()
SELECT MAX(age) FROM emp
SELECT MIN(age) FROM emp

#分组查询
#group by 分组字段
#查询每个部门里面有多少个员工
#标准sql里面要求 group by 后面字段必须出现在select后面
SELECT COUNT(*)AS num ,edid AS deptid FROM emp GROUP BY edid