目录
1. DML-修改操作
CREATE TABLE `stu` ( `sid` int(11) NOT NULL AUTO_INCREMENT, `sname` varchar(100) NOT NULL, `age` int(11) DEFAULT NULL, `address` varchar(100) DEFAULT NULL, PRIMARY KEY (`sid`) )
1、修改操作sql语句语法
update 表名称 set 字段名称=值, 字段名称=值… where 条件
2、练习
# 修改张无忌的地址是嵩山
UPDATE stu SET address='嵩山' WHERE sname='张无忌'
# 修改周芷若的年龄50和地址峨眉
UPDATE stu SET age=50,address='峨眉' WHERE sname='周芷若'
# 修改所有人的年龄+10
UPDATE stu SET age=age+10
2. DML-删除操作
1、删除操作sql语句语法
delete from 表名称 where 条件 # 删除sid=2的记录 DELETE FROM stu WHERE sid=2
2、删除表所有记录 delete from 和 truncate方式
# 删除表所有记录 DELETE FROM stu # 把整张表先删除,再创建和之前相同结构表 TRUNCATE stu
3. Mysql简单查询操作
1、mysql查询sql语句语法
SELECT 字段1,字段2… FROM 表名称1,表名称2… WHERE 条件 GROUP BY 字段 HAVING 筛选条件 ORDER BY 字段 ASC | DESC LIMIT
2、查询表里面所有记录
# 查询stu表所有记录 SELECT sid,sname,age,address FROM stu SELECT * FROM stu # 查询stu表所有sid和sname值 SELECT sid,sname FROM stu
3、别名
# 别名操作 SELECT sid AS sid_id,sname AS sname_name FROM stu s
4、飘符号(反引号)
CREATE TABLE t1 ( id INT, `name` VARCHAR(100) )
5、简单where条件
# 查询名字是谢逊信息 SELECT * FROM stu s WHERE s.sname='谢逊' # 查询sid大于2,并且年龄大于30的学生信息 SELECT * FROM stu WHERE sid>2 AND age>30
4. 多表关系和建表原则
1、多表之间有哪些关系
(1)一对多关系
* 部门和员工
* 一个部门有很多员工,一个员工属于一个部门
* 部门是一,员工是多
(2)多对多关系
* 学生和课程
* 一个学生可以选择多门课程,一门课程可以被多个学生选择
(3)一对一关系
* 人和身份证号
* 一个人只有一个身份证号,一个身份证号对应一个人
2、一对多关系建表
在多的那一方创建字段,作为外键,关联一的那一方主键
3、多对多建表
4、一对一建表
(1)主键对应建表
5、以一对多为例创建表,使用外键
### 创建部门表 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) )
6、外键主要作用:
(1)用于数据关联
(2)外键保证数据完整性和一致性
* 添加员工时候,添加部门id,这个部门id必须是部门表id
* 删除部门时候,如果部门下面有关联员工,部门不能删除的
7、实际创建表时候,一般外键不声明出来,把作为关联使用就可以了
4. Mysql多表关联查询操作
4.1 mysql关联查询分类
1、笛卡尔积(了解)
2、内连接查询
获取两张表有关联数据
3、外连接-左外连接
左边表所有数据,右边表关联数据
4、外连接-右外连接
右边表所有数据,左边表关联数据
4.2 编写sql语句实现mysql多表关联查询
1、笛卡尔积
# 笛卡尔积
SELECT * FROM dept,emp
2、内连接查询
# 内连接查询 # select 字段 from 表1 inner join 表2 on 关联条件 SELECT * FROM dept INNER JOIN emp ON dept.did=emp.edid SELECT * FROM dept,emp WHERE dept.did=emp.edid
3、外连接-左外连接
# 外连接-左外连接 # select 字段 from 表1 left outer join 表2 on 关联条件 SELECT * FROM dept LEFT OUTER JOIN emp ON dept.did=emp.edid
4、外连接-右外连接
# 外连接-右外连接 # select 字段 from 表1 right outer join 表2 on 关联条件 SELECT * FROM dept RIGHT OUTER JOIN emp ON dept.did=emp.edid
5、A表独有数据
# A表独有 SELECT * FROM dept LEFT JOIN emp ON dept.did=emp.edid WHERE emp.edid IS NULL
6、B表独有数据
# B表独有数据 SELECT * FROM dept RIGHT JOIN emp ON dept.did=emp.edid WHERE dept.did IS NULL
7、AB全有(类似于全连接效果)
# AB全有 SELECT * FROM dept LEFT JOIN emp ON dept.did=emp.edid UNION SELECT * FROM dept RIGHT JOIN emp ON dept.did=emp.edid
8、A独有 + B独有
# 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
5. Mysql查询操作
1、去重操作 DISTINCT
SELECT DISTINCT ename FROM emp
2、where条件使用
# 查询年龄大于20岁 SELECT * FROM emp WHERE age>20
3、模糊查询
# 查询所有姓张人员信息 # %代表通配符,匹配任意内容 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___'
4、排序操作
升序和降序
# 查询emp表所有记录,根据eid进行排序 SELECT * FROM emp ORDER BY eid ASC SELECT * FROM emp ORDER BY eid DESC
5、查询区间范围
# 查询区间范围 # 查询emp表年龄 30-50范围 SELECT * FROM emp WHERE age>=30 AND age<=50 # BETWEEN 开始值 AND 结束值 SELECT * FROM emp WHERE age BETWEEN 30 AND 50 # 查询年龄是 20 40 60 SELECT * FROM emp WHERE age IN(20,40,60)
6、mysql分页查询
mysql实现分页,使用关键字 limit,limit关键字只能使用在mysql数据库中,不是标准sql
7、limit用法
(1)limit后面有两个参数
第一个参数 查询数据开始位置,从0开始
第二个参数 每页显示多少条记录
(2)查询数据开始位置计算公式
* (当前页-1)*每页显示记录数
8、编写分页语句
# 分页语句 SELECT * FROM emp LIMIT 0,3 SELECT * FROM emp LIMIT 3,3
9、mysql针对特定功能,写好一些函数,直接使用这些函数实现特定功能,这些函数成为聚合(集)函数
(1)count() :统计
## 查询表有多少条记录 SELECT COUNT(*) AS num FROM emp ## 查询年龄大于40人员数量 SELECT COUNT(*) FROM emp WHERE age>40
(2)sum():求和
# sum():求和 SELECT SUM(age) FROM emp
(3)avg(): 计算平均数
# avg(): 计算平均数 SELECT AVG(age) FROM emp ## 把平均数值小数点后面位数 SELECT CAST(AVG(age) AS DECIMAL(10,2)) AS avgnum FROM emp
(4)max(): 获取最大值
(5)min(): 获取最小值
10、分组查询
group by 分组字段
11、编写分组sql
# 查询每个部门里面有多少个员工 # 标准sql SELECT COUNT(*) AS num,edid AS deptid FROM emp GROUP BY edid