90后做网站赚了,襄阳网站排名优化,茶文化网站建设规划书范文,徐州网站的优化一、加强查询
where子句#xff0c;oder by子句
-- 查询加强
-- ■ 使用where子句
-- ?如何查找1992.1.1后入职的员工
-- 老师说明#xff1a; 在mysql中,日期类型可以直接比较, 需要注意格式
SELECT * FROM empWHERE hiredate 1992-01-01
-- ■ 如何使用like操作符…一、加强查询
where子句oder by子句
-- 查询加强
-- ■ 使用where子句
-- ?如何查找1992.1.1后入职的员工
-- 老师说明 在mysql中,日期类型可以直接比较, 需要注意格式
SELECT * FROM empWHERE hiredate 1992-01-01
-- ■ 如何使用like操作符(模糊)
-- %: 表示0到多个任意字符 _: 表示单个任意字符
-- ?如何显示首字符为S的员工姓名和工资
SELECT ename, sal FROM empWHERE ename LIKE S%
-- ?如何显示第三个字符为大写O的所有员工的姓名和工资
SELECT ename, sal FROM empWHERE ename LIKE __O%-- ■ 如何显示没有上级的雇员的情况
SELECT * FROM empWHERE mgr IS NULL;
-- ■ 查询表结构
DESC emp -- 使用order by子句
-- ?如何按照工资的从低到高的顺序[升序]显示雇员的信息
SELECT * FROM empORDER BY sal
-- ?按照部门号升序而雇员的工资降序排列 , 显示雇员信息SELECT * FROM empORDER BY deptno ASC , sal DESC;
分页查询 SELECT * FROM emp ORDER BY empno LIMIT 每页显示记录数 * (第几页-1) , 每页显示记录数 -- 分页查询
-- 按雇员的id号升序取出 每页显示3条记录请分别显示 第1页第2页第3页-- 第1页
SELECT * FROM emp ORDER BY empno LIMIT 0, 3;
-- 第2页
SELECT * FROM emp ORDER BY empno LIMIT 3, 3;
-- 第3页
SELECT * FROM emp ORDER BY empno LIMIT 6, 3;
-- 推导一个公式
SELECT * FROM empORDER BY empno LIMIT 每页显示记录数 * (第几页-1) , 每页显示记录数-- 测试
SELECT job, COUNT(*) FROM emp GROUP BY job;
-- 显示雇员总数以及获得补助的雇员数
SELECT COUNT(*) FROM emp WHERE mgr IS NOT NULL;
SELECT MAX(sal) - MIN(sal) FROM emp;
使用分组函数和分组子句 增强group by
(1)显示每种岗位的雇员总数、平均工资。
(2)显示雇员总数以及获得补助的雇员数。
(3)显示管理者的总人数。
(4)显示雇员工资的最大差额。
-- 增强group by 的使用-- (1) 显示每种岗位的雇员总数、平均工资。
SELECT COUNT(*), AVG(sal), job FROM emp GROUP BY job;
-- (2) 显示雇员总数以及获得补助的雇员数。
-- 思路: 获得补助的雇员数 就是 comm 列为非null, 就是count(列)如果该列的值为null, 是
-- 不会统计 , SQL 非常灵活需要我们动脑筋.
SELECT COUNT(*), COUNT(comm)FROM emp -- 老师的扩展要求统计没有获得补助的雇员数
SELECT COUNT(*), COUNT(IF(comm IS NULL, 1, NULL))FROM emp SELECT COUNT(*), COUNT(*) - COUNT(comm)FROM emp -- (3) 显示管理者的总人数。小技巧:尝试写-修改-尝试[正确的]
SELECT COUNT(DISTINCT mgr) FROM emp; -- (4) 显示雇员工资的最大差额。
-- 思路 max(sal) - min(sal)
SELECT MAX(sal) - MIN(sal) FROM emp;SELECT * FROM e
mp;
select * from dept;
多子句使用 -- 应用案例请统计各个部门group by 的平均工资 avg
-- 并且是大于1000的 having并且按照平均工资从高到低排序 order by
-- 取出前两行记录 limit 0, 2SELECT deptno, AVG(sal) AS avg_salFROM empGROUP BY deptnoHAVING avg_sal 1000ORDER BY avg_sal DESCLIMIT 0,2 二、多表查询 说明
多表查询是指基于两个和两个以上的表查询.在实际应用中,查询单个表可能不能满足你的需求(如下面的课堂练习)需要使用到(dept表和emp表)
多表查询练习
显示雇员名,雇员工资及所在部门的名字[笛卡尔集]
老韩小技巧:多表查询的条件不能少于表的个数-1,否则会出现笛卡尔集
如何显示部门号为10的部门名、员工名和工资
显示各个员工的姓名工资及其工资的级别 -- 多表查询
-- ?显示雇员名,雇员工资及所在部门的名字 【笛卡尔集】
/*老韩分析1. 雇员名,雇员工资 来自 emp表2. 部门的名字 来自 dept表3. 需求对 emp 和 dept查询 ename,sal,dname,deptno4. 当我们需要指定显示某个表的列重复时需要 表名.列表名
*/
SELECT ename,sal,dname,emp.deptnoFROM emp, dept WHERE emp.deptno dept.deptno-- 过滤条件select * from emp;
select * from dept;
select * from salgrade;
-- 老韩小技巧多表查询的条件不能少于 表的个数-1, 否则会出现笛卡尔集
-- ?如何显示部门号为10的部门名、员工名和工资
SELECT ename,sal,dname,emp.deptnoFROM emp, dept WHERE emp.deptno dept.deptno and emp.deptno 10-- ?显示各个员工的姓名工资及其工资的级别-- 思路 姓名工资 来自 emp 13
-- 工资级别 salgrade 5
-- 写sql , 先写一个简单然后加入过滤条件...
select ename, sal, grade from emp , salgradewhere sal between losal and hisal; 自连接(用的较多)
自连接是指在同一张表的连接查询[将同一张表看做两张表]
-- 多表查询的 自连接-- 思考题: 显示公司员工名字和他的上级的名字-- 老韩分析 员工名字 在emp, 上级的名字的名字 emp
-- 员工和上级是通过 emp表的 mgr 列关联
-- 这里老师小结
-- 自连接的特点 1. 把同一张表当做两张表使用
-- 2. 需要给表取别名 表名 表别名
-- 3. 列名不明确可以指定列的别名 列名 as 列的别名
SELECT worker.ename AS 职员名 , boss.ename AS 上级名FROM emp worker, emp bossWHERE worker.mgr boss.empno;
SELECT * FROM emp; 表子查询
子查询是指嵌入在其它sq|语句中的select语包也叫嵌套查询
单行子查询
单行子查询是指只返回一行数据的子查询语句
多行子查询
多行子查询指返回多行数据的子查询使用关键字
-- 子查询的演示
-- 请思考如何显示与SMITH同一部门的所有员工?
/*1. 先查询到 SMITH的部门号得到2. 把上面的select 语句当做一个子查询来使用
*/
SELECT deptno FROM emp WHERE ename SMITH-- 下面的答案.
SELECT * FROM empWHERE deptno (SELECT deptno FROM emp WHERE ename SMITH)-- 课堂练习:如何查询和部门10的工作相同的雇员的
-- 名字、岗位、工资、部门号, 但是不含10号部门自己的雇员./*1. 查询到10号部门有哪些工作2. 把上面查询的结果当做子查询使用
*/
select distinct job from emp where deptno 10;-- 下面语句完整select ename, job, sal, deptnofrom empwhere job in (SELECT DISTINCT job FROM emp WHERE deptno 10) and deptno 10
子查询当做临时表使用
-- 查询ecshop中各个类别中价格最高的商品-- 查询 商品表
-- 先得到 各个类别中价格最高的商品 max group by cat_id, 当做临时表
-- 把子查询当做一张临时表可以解决很多很多复杂的查询select cat_id , max(shop_price) from ecs_goodsgroup by cat_id-- 这个最后答案
select goods_id, ecs_goods.cat_id, goods_name, shop_price from (SELECT cat_id , MAX(shop_price) as max_priceFROM ecs_goodsGROUP BY cat_id) temp , ecs_goodswhere temp.cat_id ecs_goods.cat_id and temp.max_price ecs_goods.shop_price 在多行子查询中使用all操作符
-- all 和 any的使用-- 请思考:显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号SELECT ename, sal, deptnoFROM empWHERE sal ALL(SELECT sal FROM empWHERE deptno 30)
-- 可以这样写
SELECT ename, sal, deptnoFROM empWHERE sal (SELECT MAX(sal) FROM empWHERE deptno 30) -- 请思考:如何显示工资比部门30的其中一个员工的工资高的员工的姓名、工资和部门号SELECT ename, sal, deptnoFROM empWHERE sal any(SELECT sal FROM empWHERE deptno 30)
-- 可以这样写SELECT ename, sal, deptnoFROM empWHERE sal (SELECT min(sal) FROM empWHERE deptno 30) 多列子查询
-- 多列子查询-- 请思考如何查询与allen的部门和岗位完全相同的所有雇员(并且不含allen本人)
-- (字段1 字段2 ...) (select 字段 1字段2 from 。。。。)-- 分析: 1. 得到smith的部门和岗位SELECT deptno , jobFROM emp WHERE ename ALLEN-- 分析: 2 把上面的查询当做子查询来使用并且使用多列子查询的语法进行匹配
SELECT * FROM empWHERE (deptno , job) (SELECT deptno , jobFROM emp WHERE ename ALLEN) AND ename ! ALLEN-- 请查询 和宋江数学英语语文
-- 成绩 完全相同的学生
SELECT * FROM studentWHERE (math, english, chinese) (SELECT math, english, chineseFROM studentWHERE name 宋江)SELECT * FROM student;--
子查询练习
-- 子查询练习-- 请思考查找每个部门工资高于本部门平均工资的人的资料
-- 这里要用到数据查询的小技巧把一个子查询当作一个临时表使用-- 1. 先得到每个部门的 部门号和 对应的平均工资SELECT deptno, AVG(sal) AS avg_salFROM emp GROUP BY deptno-- 2. 把上面的结果当做子查询, 和 emp 进行多表查询
--
SELECT ename, sal, temp.avg_sal, emp.deptnoFROM emp, (SELECT deptno, AVG(sal) AS avg_salFROM emp GROUP BY deptno) temp where emp.deptno temp.deptno and emp.sal temp.avg_sal-- 查找每个部门工资最高的人的详细资料SELECT ename, sal, temp.max_sal, emp.deptnoFROM emp, (SELECT deptno, max(sal) AS max_salFROM emp GROUP BY deptno) temp WHERE emp.deptno temp.deptno AND emp.sal temp.max_sal-- 查询每个部门的信息(包括部门名,编号,地址)和人员数量,我们一起完成。-- 1. 部门名,编号,地址 来自 dept表
-- 2. 各个部门的人员数量 -》 构建一个临时表select count(*), deptno from empgroup by deptno;select dname, dept.deptno, loc , tmp.per_num as 人数from dept, (SELECT COUNT(*) as per_num, deptno FROM empGROUP BY deptno) tmp where tmp.deptno dept.deptno-- 还有一种写法 表.* 表示将该表所有列都显示出来, 可以简化sql语句
-- 在多表查询中当多个表的列不重复时才可以直接写列名SELECT tmp.* , dname, locFROM dept, (SELECT COUNT(*) AS per_num, deptno FROM empGROUP BY deptno) tmp WHERE tmp.deptno dept.deptno
表的复制
有时为了对某个sq|语句进行效率测试我们需要海量数据时可以使用此法为表创建海量数据。
-- 表的复制
-- 为了对某个sql语句进行效率测试我们需要海量数据时可以使用此法为表创建海量数据CREATE TABLE my_tab01 ( id INT,name VARCHAR(32),sal DOUBLE,job VARCHAR(32),deptno INT);
DESC my_tab01
SELECT * FROM my_tab01;-- 演示如何自我复制
-- 1. 先把emp 表的记录复制到 my_tab01
INSERT INTO my_tab01 (id, name, sal, job,deptno)SELECT empno, ename, sal, job, deptno FROM emp;
-- 2. 自我复制
INSERT INTO my_tab01SELECT * FROM my_tab01;
SELECT COUNT(*) FROM my_tab01;-- 如何删除掉一张表重复记录
-- 1. 先创建一张表 my_tab02,
-- 2. 让 my_tab02 有重复的记录CREATE TABLE my_tab02 LIKE emp; -- 这个语句 把emp表的结构(列)复制到my_tab02desc my_tab02;insert into my_tab02select * from emp;
select * from my_tab02;
-- 3. 考虑去重 my_tab02的记录
/*思路 (1) 先创建一张临时表 my_tmp , 该表的结构和 my_tab02一样(2) 把my_tmp 的记录 通过 distinct 关键字 处理后 把记录复制到 my_tmp(3) 清除掉 my_tab02 记录(4) 把 my_tmp 表的记录复制到 my_tab02(5) drop 掉 临时表my_tmp
*/
-- (1) 先创建一张临时表 my_tmp , 该表的结构和 my_tab02一样create table my_tmp like my_tab02
-- (2) 把my_tmp 的记录 通过 distinct 关键字 处理后 把记录复制到 my_tmp
insert into my_tmp select distinct * from my_tab02;-- (3) 清除掉 my_tab02 记录
delete from my_tab02;
-- (4) 把 my_tmp 表的记录复制到 my_tab02
insert into my_tab02select * from my_tmp;
-- (5) drop 掉 临时表my_tmp
drop table my_tmp;select * from my_tab02;
合并查询union/union all
有时在实际应用中为了合并多个select语句的结果可以使用集合操作符号
-- union all 就是将两个查询结果合并不会去重
-- union 就是将两个查询结果合并会去重
-- 合并查询SELECT ename,sal,job FROM emp WHERE sal2500 -- 5SELECT ename,sal,job FROM emp WHERE jobMANAGER -- 3-- union all 就是将两个查询结果合并不会去重
SELECT ename,sal,job FROM emp WHERE sal2500 -- 5
UNION ALL
SELECT ename,sal,job FROM emp WHERE jobMANAGER -- 3-- union 就是将两个查询结果合并会去重
SELECT ename,sal,job FROM emp WHERE sal2500 -- 5
UNION
SELECT ename,sal,job FROM emp WHERE jobMANAGER -- 3
外连接
左外连接(如果左侧的表完全 显示我们就说是左外连接)
使用左连接(显示所有人的成绩如果没有成绩也要显示该人的姓名和id号,成绩显示为空) select .. from表1 left join表2 on条件 [表1:就是左表表2:就是右表]
右外连接(如果右侧的表完全显示我们就说是右外连接) select .. from表1 right join表2 on条件 [表1:就是左表表2:就是右表]
-- 外连接-- 比如列出部门名称和这些部门的员工名称和工作
-- 同时要求 显示出那些没有员工的部门。-- 使用我们学习过的多表查询的SQL 看看效果如何?SELECT dname, ename, job FROM emp, deptWHERE emp.deptno dept.deptnoORDER BY dname
SELECT * FROM dept;SELECT * FROM emp;-- 创建 stu
/*
id name
1 Jack
2 Tom
3 Kity
4 nono*/
CREATE TABLE stu (id INT,name VARCHAR(32));
INSERT INTO stu VALUES(1, jack),(2,tom),(3, kity),(4, nono);
SELECT * FROM stu;
-- 创建 exam
/*
id grade
1 56
2 76
11 8*/
CREATE TABLE exam(id INT,grade INT);
INSERT INTO exam VALUES(1, 56),(2,76),(11, 8);
SELECT * FROM exam;-- 使用左连接
-- 显示所有人的成绩如果没有成绩也要显示该人的姓名和id号,成绩显示为空SELECT name, stu.id, gradeFROM stu, examWHERE stu.id exam.id;-- 改成左外连接
SELECT name, stu.id, gradeFROM stu LEFT JOIN examON stu.id exam.id;-- 使用右外连接显示所有成绩如果没有名字匹配显示空)
-- 即右边的表(exam) 和左表没有匹配的记录也会把右表的记录显示出来
SELECT name, stu.id, gradeFROM stu RIGHT JOIN examON stu.id exam.id;-- 列出部门名称和这些部门的员工信息(名字和工作)
-- 同时列出那些没有员工的部门名。5min
-- 使用左外连接实现
SELECT dname, ename, jobFROM dept LEFT JOIN empON dept.deptno emp.deptno-- 使用右外连接实现SELECT dname, ename, jobFROM emp RIGHT JOIN deptON dept.deptno emp.deptno--
三、约束
1.基本介绍
约束用于确保数据库数据满足特定的商业规则。
在mysq|中约束包括: not null、unique,primary key,foreign key,和check五种.
2.主键primary key 用于唯一的标示表行的数据当定义主键约束后该列不能重复
-- 主键使用-- id name email
CREATE TABLE t17(id INT PRIMARY KEY, -- 表示id列是主键 name VARCHAR(32),email VARCHAR(32));-- 主键列的值是不可以重复
INSERT INTO t17VALUES(1, jack, jacksohu.com);
INSERT INTO t17VALUES(2, tom, tomsohu.com);INSERT INTO t17VALUES(1, hsp, hspsohu.com);SELECT * FROM t17;
2.1主键的细节
.在实际开发中每个表往往都会设计一个主键.
-- 主键使用的细节讨论
-- primary key不能重复而且不能为 null。
INSERT INTO t17VALUES(NULL, hsp, hspsohu.com);
-- 一张表最多只能有一个主键, 但可以是复合主键(比如 idname)
CREATE TABLE t18(id INT PRIMARY KEY, -- 表示id列是主键 name VARCHAR(32), PRIMARY KEY -- 错误的email VARCHAR(32));
-- 演示复合主键 (id 和 name 做成复合主键)
CREATE TABLE t18(id INT , name VARCHAR(32), email VARCHAR(32),PRIMARY KEY (id, name) -- 这里就是复合主键);INSERT INTO t18VALUES(1, tom, tomsohu.com);
INSERT INTO t18VALUES(1, jack, jacksohu.com);
INSERT INTO t18VALUES(1, tom, xxsohu.com); -- 这里就违反了复合主键
SELECT * FROM t18;-- 主键的指定方式 有两种
-- 1. 直接在字段名后指定字段名 primakry key
-- 2. 在表定义最后写 primary key(列名);
CREATE TABLE t19(id INT , name VARCHAR(32) PRIMARY KEY, email VARCHAR(32));CREATE TABLE t20(id INT , name VARCHAR(32) , email VARCHAR(32),PRIMARY KEY(name) -- 在表定义最后写 primary key(列名));-- 使用desc 表名可以看到primary key的情况DESC t20 -- 查看 t20表的结果显示约束的情况
DESC t183.not null 非空 unique独一 -- unique的使用CREATE TABLE t21(id INT UNIQUE , -- 表示 id 列是不可以重复的.name VARCHAR(32) , email VARCHAR(32));INSERT INTO t21VALUES(1, jack, jacksohu.com);INSERT INTO t21VALUES(1, tom, tomsohu.com);-- unqiue使用细节
-- 1. 如果没有指定 not null , 则 unique 字段可以有多个null
-- 如果一个列(字段) 是 unique not null 使用效果类似 primary key
INSERT INTO t21VALUES(NULL, tom, tomsohu.com);
SELECT * FROM t21;
-- 2. 一张表可以有多个unique字段CREATE TABLE t22(id INT UNIQUE , -- 表示 id 列是不可以重复的.name VARCHAR(32) UNIQUE , -- 表示name不可以重复 email VARCHAR(32));
DESC t22
4.foregin key外键
外键约束 约束创建和删除
用于定义主表和从表之间的关系:外键约束要定义在从表上主表则必须具有主键约束或是unique约束当定义外键约束后要求外键列数据必须在主表的主键列存在或是为null -- 外键演示-- 创建 主表 my_class
CREATE TABLE my_class (id INT PRIMARY KEY , -- 班级编号name VARCHAR(32) NOT NULL DEFAULT );-- 创建 从表 my_stu
CREATE TABLE my_stu (id INT PRIMARY KEY , -- 学生编号name VARCHAR(32) NOT NULL DEFAULT ,class_id INT , -- 学生所在班级的编号-- 下面指定外键关系FOREIGN KEY (class_id) REFERENCES my_class(id))
-- 测试数据
INSERT INTO my_class VALUES(100, java), (200, web);
INSERT INTO my_class VALUES(300, php);SELECT * FROM my_class;
INSERT INTO my_stu VALUES(1, tom, 100);
INSERT INTO my_stu VALUES(2, jack, 200);
INSERT INTO my_stu VALUES(3, hsp, 300);
INSERT INTO my_stu VALUES(4, mary, 400); -- 这里会失败...因为400班级不存在INSERT INTO my_stu VALUES(5, king, NULL); -- 可以, 外键 没有写 not null
SELECT * FROM my_class;-- 一旦建立主外键的关系数据不能随意删除了
DELETE FROM my_classWHERE id 100; 4.1外键细节
1.外键指向的表的字段要求是primary key或者是unique
2.表的类型是innodb,这样的表才支持外键
3.外键字段的类型要和主键字段的类型一致(长度可以不同)
4.外键字段的值必须在主键字段中出现过或者为null [前提是外键字段允许为nul]
5.一旦建立主外键的关系数据不能随意删除了外表没有数据指向主表时才能删除主表的内容
5.check
用于强制行数据必须满足的条件,假定在sal列上定义了check约束,并要求sal列值在1000 ~ 2000之间如果不再1 000 ~ 2000之间就会提示出错。
老师提示: oracle 和sql server均支持check ,但是mysq15.7目前还不支持check ,只做语法校验但不会生效。
-- 演示check的使用
-- mysql5.7目前还不支持check ,只做语法校验但不会生效
-- 了解
-- 学习 oracle, sql server, 这两个数据库是真的生效.-- 测试
CREATE TABLE t23 (id INT PRIMARY KEY,name VARCHAR(32) ,sex VARCHAR(6) CHECK (sex IN(man,woman)),sal DOUBLE CHECK ( sal 1000 AND sal 2000));-- 添加数据
INSERT INTO t23 VALUES(1, jack, mid, 1);
SELECT * FROM t23;
约束练习
-- 使用约束的课堂练习CREATE DATABASE shop_db;-- 现有一个商店的数据库shop_db记录客户及其购物情况由下面三个表组成
-- 商品goods商品号goods_id商品名goods_name单价unitprice商品类别category
-- 供应商provider);
-- 客户customer客户号customer_id,姓名name,住址address,电邮email性别sex,身份证card_Id);
-- 购买purchase购买订单号order_id客户号customer_id,商品号goods_id,购买数量nums);
-- 1 建表在定义中要求声明 [进行合理设计]
-- (1)每个表的主外键
-- (2)客户的姓名不能为空值
-- (3)电邮不能够重复;
-- (4)客户的性别[男|女] check 枚举..
-- (5)单价unitprice 在 1.0 - 9999.99 之间 check-- 商品goods
CREATE TABLE goods (goods_id INT PRIMARY KEY,goods_name VARCHAR(64) NOT NULL DEFAULT ,unitprice DECIMAL(10,2) NOT NULL DEFAULT 0 CHECK (unitprice 1.0 AND unitprice 9999.99),category INT NOT NULL DEFAULT 0,provider VARCHAR(64) NOT NULL DEFAULT );-- 客户customer客户号customer_id,姓名name,住址address,电邮email性别sex,
-- 身份证card_Id);
CREATE TABLE customer(customer_id CHAR(8) PRIMARY KEY, -- 程序员自己决定name VARCHAR(64) NOT NULL DEFAULT ,address VARCHAR(64) NOT NULL DEFAULT ,email VARCHAR(64) UNIQUE NOT NULL,sex ENUM(男,女) NOT NULL , -- 这里老师使用的枚举类型, 是生效card_Id CHAR(18)); -- 购买purchase购买订单号order_id客户号customer_id,商品号goods_id,
-- 购买数量nums);
CREATE TABLE purchase(order_id INT UNSIGNED PRIMARY KEY,customer_id CHAR(8) NOT NULL DEFAULT , -- 外键约束在后goods_id INT NOT NULL DEFAULT 0 , -- 外键约束在后nums INT NOT NULL DEFAULT 0,FOREIGN KEY (customer_id) REFERENCES customer(customer_id),FOREIGN KEY (goods_id) REFERENCES goods(goods_id));
DESC goods;
DESC customer;
DESC purchase;
6.自增长
在某张表中存在一个id列(整数),我们希望在添加记录的时候该列从开始自动的增长怎么处理?
-- 演示自增长的使用
-- 创建表
CREATE TABLE t24(id INT PRIMARY KEY AUTO_INCREMENT,email VARCHAR(32)NOT NULL DEFAULT ,name VARCHAR(32)NOT NULL DEFAULT );
DESC t24
-- 测试自增长的使用
INSERT INTO t24VALUES(NULL, tomqq.com, tom);INSERT INTO t24(email, name) VALUES(hspsohu.com, hsp);SELECT * FROM t24;
6.1自增长使用细节
1.一般来说自增长是和primary key配合使用的
2.自增长也可以单独使用[但是需要配合一个unique]
3.自增长修饰的字段为整数型的(虽然小数也可以但是非常非常少这样使用)
4.自增长默认从1开始你也可以通过如下命令修改alter table表名auto increment xXX;
5.如果你添加数据时给自增长宇段(列)指定的有值则以指定的值为准建议如果指定了自增长一般来说就按照自增长的规则来添加数据
-- 修改默认的自增长开始值
ALTER TABLE t25 AUTO_INCREMENT 100
CREATE TABLE t25(id INT PRIMARY KEY AUTO_INCREMENT,email VARCHAR(32)NOT NULL DEFAULT ,name VARCHAR(32)NOT NULL DEFAULT );
INSERT INTO t25VALUES(NULL, maryqq.com, mary);
INSERT INTO t25VALUES(666, hspqq.com, hsp);
SELECT * FROM t25;
四、索引
1.引入
创建海量表
-- 创建测试数据库 tmp
CREATE DATABASE tmp;CREATE TABLE dept( /*部门表*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
dname VARCHAR(20) NOT NULL DEFAULT ,
loc VARCHAR(13) NOT NULL DEFAULT
) ;#创建表EMP雇员
CREATE TABLE emp
(empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*编号*/
ename VARCHAR(20) NOT NULL DEFAULT , /*名字*/
job VARCHAR(9) NOT NULL DEFAULT ,/*工作*/
mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上级编号*/
hiredate DATE NOT NULL,/*入职时间*/
sal DECIMAL(7,2) NOT NULL,/*薪水*/
comm DECIMAL(7,2) NOT NULL,/*红利*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/
) ;#工资级别表
CREATE TABLE salgrade
(
grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
losal DECIMAL(17,2) NOT NULL,
hisal DECIMAL(17,2) NOT NULL
);#测试数据
INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);DELIMITER $$#创建一个函数名字 rand_string可以随机返回我指定的个数字符串
CREATE FUNCTION rand_string(n INT)
RETURNS VARCHAR(255) #该函数会返回一个字符串
BEGIN
#定义了一个变量 chars_str 类型 varchar(100)
#默认给 chars_str 初始值 abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZDECLARE chars_str VARCHAR(100) DEFAULTabcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ; DECLARE return_str VARCHAR(255) DEFAULT ;DECLARE i INT DEFAULT 0; WHILE i n DO# concat 函数 : 连接函数mysql函数SET return_str CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1RAND()*52),1));SET i i 1;END WHILE;RETURN return_str;END $$#这里我们又自定了一个函数,返回一个随机的部门号
CREATE FUNCTION rand_num( )
RETURNS INT(5)
BEGIN
DECLARE i INT DEFAULT 0;
SET i FLOOR(10RAND()*500);
RETURN i;
END $$#创建一个存储过程 可以添加雇员
CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
#set autocommit 0 把autocommit设置成0#autocommit 0 含义: 不要自动提交SET autocommit 0; #默认不提交sql语句REPEATSET i i 1;#通过前面写的函数随机产生字符串和部门编号然后加入到emp表INSERT INTO emp VALUES ((STARTi) ,rand_string(6),SALESMAN,0001,CURDATE(),2000,400,rand_num());UNTIL i max_numEND REPEAT;#commit整体提交所有sql语句提高效率COMMIT;END $$#添加8000000数据
CALL insert_emp(100001,8000000)$$#命令结束符再重新设置为;
DELIMITER ;SELECT COUNT(*) FROM emp;-- 在没有创建索引时我们的查询一条记录
SELECT * FROM emp WHERE empno 1234567
-- 使用索引来优化一下 体验索引的牛-- 在没有创建索引前 , emp.ibd 文件大小 是 524m
-- 创建索引后 emp.ibd 文件大小 是 655m [索引本身也会占用空间.]
-- 创建ename列索引,emp.ibd 文件大小 是 827m-- empno_index 索引名称
-- ON emp (empno) : 表示在 emp表的 empno列创建索引
CREATE INDEX empno_index ON emp (empno)-- 创建索引后 查询的速度如何SELECT * FROM emp WHERE empno 1234578 -- 0.003s 原来是4.5s-- 创建索引后只对创建了索引的列有效
SELECT * FROM emp WHERE ename PjDlwy -- 没有在ename创建索引时时间4.7sCREATE INDEX ename_index ON emp (ename) -- 在ename上创建索引
2.索引的原理
没有索引为什么会慢? 因为全表扫描。
使用索引为什么会快?形成一个索引的数据结构比如二叉树
索引的代价
磁盘占用
lI.对dml(update delete insert)语包的效率影响 3.索引的类型
1.主键索引主键自动的为主索引(类型Primary)
2.唯一索引(UNIQUE)
3.普通索引(INDEX)
4.全文索引(FULLTEXT) 一般开发不使用mysq|自带额全文索引而是使用:全文搜索Sol和ElasticSearch (ES)
4.索引的使用 -- 演示mysql的索引的使用
-- 创建索引
CREATE TABLE t25 (id INT ,name VARCHAR(32));A-- 查询表是否有索引
SHOW INDEXES FROM t25;
-- 添加索引
-- 添加唯一索引
CREATE UNIQUE INDEX id_index ON t25 (id);
-- 添加普通索引方式1
CREATE INDEX id_index ON t25 (id);
-- 如何选择
-- 1. 如果某列的值是不会重复的则优先考虑使用unique索引, 否则使用普通索引
-- 添加普通索引方式2
ALTER TABLE t25 ADD INDEX id_index (id)-- 添加主键索引
CREATE TABLE t26 (id INT ,name VARCHAR(32));
ALTER TABLE t26 ADD PRIMARY KEY (id)SHOW INDEX FROM t25-- 删除索引
DROP INDEX id_index ON t25
-- 删除主键索引
ALTER TABLE t26 DROP PRIMARY KEY-- 修改索引 先删除在添加新的索引-- 查询索引
-- 1. 方式
SHOW INDEX FROM t25
-- 2. 方式
SHOW INDEXES FROM t25
-- 3. 方式
SHOW KEYS FROM t25
-- 4 方式
DESC t25
5.小结
哪些列上适合使用索引
1.较频繁的作为查询条件字段应该创建索引 select * from emp where empno 1
2.唯一性太差的字段不适合单独创建索引即使频繁作为查询条件 select * from emp where sex 男
3.更新非常频繁的字段不适合创建索引 select * from emp where logincount 1
4.不会出现在WHERE子句中字段不该创建索引
五、事务 1.什么是事务
事务用于保证数据的一致性它由一组相关的dmI语句组成,该组的dmI语句要么全部成功要么全部失败。如:转账就要用事务来处理用以保证数据的一致性。
事务和锁
当执行事务操作时(dm|语句) ,mysql会在表上加锁,防止其它用户改表的数据.这对用户来讲是非常重要的 回退事务
在介绍回退事务前先介绍一下保存点(savepoint).保存点是事务中的点.用于取消部分事务当结束事务时(commit) ,会自动的删除该事务所定义的所有保存点.当执行回退事务时通过指定保存点可以回退到指定的点这里我们作图说明
提交事务
使用commit语句可以提交事务.当执行了commit语句子后会确认事务的变化、结束事务、删除保存点、释放锁数据生效。当使用commit语句结束事务子后其它会话将可以查看到事务变化后的新数据 1.1事务的细节
1.如果不开始事务默认情况下dml操作是自动提交的不能回滚
2.如果开始一-个事务你没有创建保存点.你可以执行rollback,默认就是回退到你事务开始的状态.
3.你也可以在这个事务中(还没有提交时)创建多个保存点.比如: savepoint aaa;执行dml , savepoint bbb;
4.你可以在事务没有提交前选择回退到哪个保存点.
5. mysq|的事务机制需要innodb的存储引擎才可以使用,myisam不好使.
6.开始一个事务start transaction, set autocommitoff;
-- 讨论 事务细节
-- 1. 如果不开始事务默认情况下dml操作是自动提交的不能回滚
INSERT INTO t27 VALUES(300, milan); -- 自动提交 commitSELECT * FROM t27-- 2. 如果开始一个事务你没有创建保存点. 你可以执行 rollback
-- 默认就是回退到你事务开始的状态
START TRANSACTION
INSERT INTO t27 VALUES(400, king);
INSERT INTO t27 VALUES(500, scott);
ROLLBACK -- 表示直接回退到事务开始的的状态
COMMIT;-- 3. 你也可以在这个事务中(还没有提交时), 创建多个保存点.比如: savepoint aaa;
-- 执行 dml , savepoint bbb-- 4. 你可以在事务没有提交前选择回退到哪个保存点
-- 5. InnoDB 存储引擎支持事务 , MyISAM 不支持
-- 6. 开始一个事务 start transaction, set autocommitoff;
2.事务隔离级别
1.多个连接开启各自事务操作数据库中数据时 数据库系统要负责隔离操作以保证各个连接在获取数据时的准确性。(通俗解释)
2.如果不考虑隔离性可能会引发如下问题: 脏读 不可重复读 幻读
脏读(dirty read):当一个事务读取另一个事务尚未提交的修改时产生脏读
不可重复读(nonrepeatable read):同-查询在同一事务中多次进行由于其他提交事务所做的修改或删除每次返回不同的结果集此时发生不可重复读。
幻读(phantom read):同一查询在同一事务中多次进行由于其他提交事务所做的插入操作每次返回不同的结果集此时发生幻读。 -- 演示mysql的事务隔离级别-- 1. 开了两个mysql的控制台
-- 2. 查看当前mysql的隔离级别
SELECT tx_isolation;-- mysql SELECT tx_isolation;
-- -----------------
-- | tx_isolation |
-- -----------------
-- | REPEATABLE-READ |
-- ------------------- 3.把其中一个控制台的隔离级别设置 Read uncommittedSET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED-- 4. 创建表
CREATE TABLE account(id INT,name VARCHAR(32),money INT);-- 查看当前会话隔离级别
SELECT tx_isolation
-- 查看系统当前隔离级别
SELECT global.tx_isolation
-- 设置当前会话隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- 设置系统当前隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL [你设置的级别]
mysql默认的事务隔离级别是repeatable read 一般情况下没有特殊要求没有必要修改(因为该级别可以满足绝大部分项目需求) 3.事务的acid特性
1.原子性(Atomicity)
原子性是指事务是一个不可分割的工作单位事务中的操作要么都发生要么都不发生。
2.一致性(Consistency)
事务必须使数据库从一个一致性状态变换到另外一个一致性状态
3.隔离性(Isolation)
事务的隔离性是多个用户并发访问数据库时数据库为每一一个用户开启的事务不能被其他事务的操作数据所干扰多个并发事务之间要相互隔离。
4.持久性(Durability)
持久性是指一个事务一旦被提交 它对数据库中数据的改变就是永久性的接下来即使数据库发生故障也不应该对其有任何影响 六、表类型和存储类型
1.基本介绍
1. MySQL的表类型由存储引擎(Storage Engines)决定主要包括MyISAM、innoDB、Memory等。
2.MySQL数据表主要支持六种类型,分别是: CSV、Memory、ARCHIVE、MRG MYISAM、MYISAM、InnoBDB。
3.这六种又分为两类一类是”事务安全型”(transaction-safe),比如:InnoDB;其余都属于第二类 称为”非事务安全 型”(non-transaction-safe)[mysiam和memory]. 2.细节说明
1. MyISAM不支持事务、 也不支持外键但其访问速度快对事务完整性没有要求
2. InnoDB存储引擎提供 了具有提交、回滚和崩溃恢复能力的事务安全。但是比起MyISAM存储引擎InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。
3. MEMORY存储引擎使用存在内存中的内容来创建表。 每个MEMORY表只实际对应一个磁盘文件。MEMORY类型的表访问非常得快因为它的数据是放在内存中的并且默认使用HASH索引。但是一旦服务关闭表中的数据就会丢失掉表的结构还在。
-- 表类型和存储引擎-- 查看所有的存储引擎
SHOW ENGINES
-- innodb 存储引擎是前面使用过.
-- 1. 支持事务 2. 支持外键 3. 支持行级锁-- myisam 存储引擎
CREATE TABLE t28 (id INT,name VARCHAR(32)) ENGINE MYISAM
-- 1. 添加速度快 2. 不支持外键和事务 3. 支持表级锁START TRANSACTION;
SAVEPOINT t1
INSERT INTO t28 VALUES(1, jack);
SELECT * FROM t28;
ROLLBACK TO t1-- memory 存储引擎
-- 1. 数据存储在内存中[关闭了Mysql服务数据丢失, 但是表结构还在]
-- 2. 执行速度很快(没有IO读写) 3. 默认支持索引(hash表)CREATE TABLE t29 (id INT,name VARCHAR(32)) ENGINE MEMORY
DESC t29
INSERT INTO t29VALUES(1,tom), (2,jack), (3, hsp);
SELECT * FROM t29-- 指令修改存储引擎
ALTER TABLE t29 ENGINE INNODB
3.如何选择表的储存引擎
1.如果你的应用不需要事务处理的只是基本的CRUD操作那么MyISAM是不二选择,速度快
2.如果需要支持事务选择InnoDB.
3.Memory存储引擎就是将数据存储在内存中由于没有磁盘I./O的等待速度极快。但由于是内存存储引擎所做的任何修改在服务器重启后都将消失。(经典用法用户的在线状态())
七、视图
1.基本概念
1.视图是一个虚拟表其内容由查询定义。同真实的表一样,视图包含列其数据来自对应的真实表(基表)
2.视图和基表关系的示意图 2.使用细节
1. create view视图名as select语句
2.alter view视图名as select语句-更新成新的视图
3. I SHOW CREATE VIEW视图名
4. drop view视图名1,视图名2
3.视图细节讨论
1.创建视图后到数据库去看对应视图只有一一个视图结构文件(形式:视图名.frm)
2.视图的数据变化会影响到基表基表的数据变化也会影响到视图[insert update delete ]针对前面的雇员管理系统----
mysql create view myview as select empno ,ename , job, comm from emp;
mysql select * from myview;
mysql update myview set comm 200 where empno 7369; //修改视图,对基表都有变化
mysql update emp set comm 100 where empno 7369; //修改基表对视频也有变化
3.视图中可以再使用视图
-- 视图的使用
-- 创建一个视图emp_view01只能查询emp表的(empno、ename, job 和 deptno ) 信息-- 创建视图
CREATE VIEW emp_view01ASSELECT empno, ename, job, deptno FROM emp; -- 查看视图
DESC emp_view01SELECT * FROM emp_view01;
SELECT empno, job FROM emp_view01;-- 查看创建视图的指令
SHOW CREATE VIEW emp_view01
-- 删除视图
DROP VIEW emp_view01;-- 视图的细节-- 1. 创建视图后到数据库去看对应视图只有一个视图结构文件(形式: 视图名.frm)
-- 2. 视图的数据变化会影响到基表基表的数据变化也会影响到视图[insert update delete ]-- 修改视图 会影响到基表UPDATE emp_view01 SET job MANAGER WHERE empno 7369SELECT * FROM emp; -- 查询基表SELECT * FROM emp_view01-- 修改基本表 会影响到视图UPDATE emp SET job SALESMAN WHERE empno 7369-- 3. 视图中可以再使用视图 , 比如从emp_view01 视图中选出empno,和ename做出新视图
DESC emp_view01CREATE VIEW emp_view02ASSELECT empno, ename FROM emp_view01SELECT * FROM emp_view02
视图最佳实践
1.安全。一些数据表有着重要的信息。有些字段是保密的不能让用户直接看到。这时就可以创建一个视图在这张视图中只保留一部分字段。这样用户就可以查询自己需要的字段不能查看保密的字段。
2. 性能。关系数据库的数据常常会分表存储使用外键建立这些表的之间关系。这时数据库查询通常会用到连接(JOIN) 。这样做不但麻烦效率相对也比较低。如果建立一个视图将相关的表和字段组合在一起就可以避免使用JOIN查询数据。
3.灵活。如果系统中有一-张旧的表这张表由于设计的问题即将被废弃。然而很多应用都是基于这张表不易修改。这时就可以建立一-张视图视图中的数据直接映射到新建的表。这样就可以少做很多改动也达到了升级数据表的目的。
课堂练习
-- 视图的课堂练习
-- 针对 emp dept , 和 salgrade 张三表.创建一个视图 emp_view03
-- 可以显示雇员编号雇员名雇员部门名称和 薪水级别[即使用三张表构建一个视图]/*分析: 使用三表联合查询得到结果将得到的结果构建成视图*/
CREATE VIEW emp_view03ASSELECT empno, ename, dname, gradeFROM emp, dept, salgradeWHERE emp.deptno dept.deptno AND (sal BETWEEN losal AND hisal) DESC emp_view03
SELECT * FROM emp_view03
八、mysql用户
1.创建用户和删除用户
mysq中的用户都存储在系统数据库mysql中user表中其中user表的重要字段说明:
1. host:允许登录的 位置”, localhost表示该用户只允许本机登录也可以指定ip地址比如:192.168.1.100
2. user:用户名:
3. authentication string: 密码是通过mysq|l的password(函数加密之后的密码。
-- 修改自己的密码, 没问题SET PASSWORD PASSWORD(abcdef)-- 修改其他人的密码 需要权限SET PASSWORD FOR rootlocalhost PASSWORD(123456)-- Mysql用户的管理
-- 原因当我们做项目开发时可以根据不同的开发人员赋给他相应的Mysql操作权限
-- 所以Mysql数据库管理人员(root), 根据需要创建不同的用户赋给相应的权限供人员使用-- 1. 创建新的用户
-- 解读 (1) hsp_edulocalhost 表示用户的完整信息 hsp_edu 用户名 localhost 登录的IP
-- (2) 123456 密码, 但是注意 存放到 mysql.user表时是password(123456) 加密后的密码
-- *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
CREATE USER hsp_edulocalhost IDENTIFIED BY 123456SELECT host, user, authentication_string FROM mysql.user-- 2. 删除用户
DROP USER hsp_edulocalhost-- 3. 登录-- root 用户修改 hsp_edulocalhost 密码, 是可以成功.
SET PASSWORD FOR hsp_edulocalhost PASSWORD(123456) 2.赋予权限
给用户授权
基本语法:
grant权限列表on 库.对象名to 用户名’ 登录位置’[identified by( 密码’]
说明:
1,权限列表多个权限用逗号分开
grant select on ....
grant select, delete, create on ....
grant all [privileges] on ...
//表示赋予该用户在该对象.上的所有权限
2.特别说明 * :代表本系统中的所有数据库的所有对象(表视图存储过程)库.* :表示某个数据库中的所有数据对象(表视图存储过程等), identified by可以省略也可以写出
(1)如果用户存在就是修改该用户的密码。
(2)如果该用户不存在就是创建该用户
回收用户授权
基本语法:
revoke权限列表on库对象名from ‘用户名 登录位置;
权限生敦指令
如果权限没有生效可以执行下面命令.
基本语法:
FLUSH PRIVILEGES;
-- 演示 用户权限的管理-- 创建用户 shunping 密码 123 , 从本地登录
CREATE USER shunpinglocalhost IDENTIFIED BY 123-- 使用root 用户创建 testdb ,表 news
CREATE DATABASE testdb
CREATE TABLE news (id INT ,content VARCHAR(32));
-- 添加一条测试数据
INSERT INTO news VALUES(100, 北京新闻);
SELECT * FROM news;-- 给 shunping 分配查看 news 表和 添加news的权限
GRANT SELECT , INSERT ON testdb.newsTO shunpinglocalhost-- 可以增加update权限
GRANT UPDATE ON testdb.newsTO shunpinglocalhost-- 修改 shunping的密码为 abc
SET PASSWORD FOR shunpinglocalhost PASSWORD(abc);-- 回收 shunping 用户在 testdb.news 表的所有权限
REVOKE SELECT , UPDATE, INSERT ON testdb.news FROM shunpinglocalhost
REVOKE ALL ON testdb.news FROM shunpinglocalhost-- 删除 shunping
DROP USER shunpinglocalhost
细节说明
1.在创建用户的时候如果不指定Host,则为%%表示表示所有IP都有连接权限
create user xxx;
2.你也可以这样指定
create user xxx’ 192.168.1.%表示xxx用户在192.1 68.1.*的ip可以登录mysqI
3.在删除用户的时候如果host不是%,需要明确指定用户host值
-- 说明 用户管理的细节
-- 在创建用户的时候如果不指定Host, 则为% , %表示表示所有IP都有连接权限
-- create user xxx;CREATE USER jackSELECT host, user FROM mysql.user-- 你也可以这样指定
-- create user xxx192.168.1.% 表示 xxx用户在 192.168.1.*的ip可以登录mysqlCREATE USER smith192.168.1.%-- 在删除用户的时候如果 host 不是 %, 需要明确指定 用户host值DROP USER jack -- 默认就是 DROP USER jack%DROP USER smith192.168.1.%
