Java与MySQL语句
大纲
- 创建数据库
- 校验规则
- 查看和删除数据库
- 备份和恢复数据库
- 创建表
- MySQL常用数据类型
- 修改表
- CRUD
- 函数
- 多表查询
- 表复制和去重
- 合并查询
- 外连接
- 约束
- 索引
- 事务
- 存储引擎
- 视图
- 用户管理
- 细节加作业
具体案例
语句分类:
1. 创建数据库
代码演示:
在SQLyog里:
# 演示数据库的操作
# 创建数据库
CREATE DATABASE test01;
# 删除数据库
DROP DATABASE test01;
# 创建指定的字符集,校验规则的数据库
CREATE DATABASE test02 CHARACTER SET utf8 COLLATE utf8_bin
2.校验规则
对于utf8_bin是区分大小写的,即我们在查询一个叫tom的数据时,只能查询到它,而utf8_general_ci不区分大小写,同样如果查询一个叫tom的数据,我们还可以查询到如Tom,toM等等
表的字符集和校验规则如果不指定,会默认采用数据库的字符集和校验规则
3. 查看和删除数据库
# 查看所有的数据库
SHOW DATABASES
# 查看某个数据库的定义信息
SHOW CREATE DATABASE test01
# 在创建数据库,或表的时候,为了规避关键字,可以用反引号解决
# 删除数据库
DROP DATABASE test02
4. 备份和恢复数据库
# 实现备份数据库中的数据并恢复
# 备份,需要在Dos下执行,注意保存到自己指定路径的sql文件里
mysqldump -u root -p -B test01 test02 > D:\\bak.sql
# 删除这几个数据库,完成备份
DROP DATABASE test01
DROP DATABASE test02
# 恢复数据库(注意,进入MySQL命令行再执行)
source D:\\bak.sql
# 上面的备份和恢复,都是我们指定的储存路径
# 另一种方式,找到备份的文件,再复制,到这里粘贴再执行
5. 创建表
# 实现在某个数据库下,创建一个名叫user的表
CREATE TABLE `user` (
id INT,`name` VARCHAR(255),`password` VARCHAR(32),`birthday` DATE)
CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB;
如果存在一张表,且我们将要创建的表和存在的表的结构差不多,可以使用like
CREATE TABLE my_table LIKE emp;
6. MySQL常用数据类型(列类型)
当然时间类型还有year存放年,time存放时分秒
有无符号
对于二进制数据类型的使用
# 演示bit的使用
# 说明
# bit(m)m在 1-64
# 添加数据时,按照所确定的m的值来确定,如这里m=8,则范围为0 ~~ 2^8-1
CREATE TABLE test (num BIT(8));
INSERT INTO test VALUES(1);
SELECT * FROM test;
# 查询时可以查我们添加的数,也可以是二进制存储的数
Decimal类型的使用
字符串数据类型的使用
注意:对于存储的上限,CHAR是存储的字符,而VAECHAR是存储的字节,在大小上限为2^16-1后,还要留出三个字节记录大小,所以实用的是65532个,且在不同的编码环境下,有些是三个字节存储一个字符,有些是,两个字节存储一个字符
日期类型的使用
7. 修改表
对于添加列,可以指定添加到哪一个的后面
ALTER TABLE `emp` ADD `image` VARCHAR(32) NOT NULL DEFAULT '' AFTER RESUME
修改列名name到user_name
ALTER TABLE employy CHANGE `name` user_name VARCHAR(64) NOT NULL DEFAULT ''
8. CRUD
增:
注意是否可以为空,和默认值,是在创建表的时候就定义的
修改:
删除:
查:
对于模糊查询,记得添加百分号,来完成模糊查询
9.函数
**注意:**判断用是否为null 用 is,而判断值用 =
下面是查询使用的样表
统计函数
注意:count(*)是返回所有行,而count(列名),如果在该列数据为空,则不会返回这行
也可以进行运算,命名
SELECT SUM(math) AS math_total,SUM(english) AS english_total,
SUM(chinese)AS chinese_total FROM student;
SELECT SUM(math + english + chinese) AS total FROM student;
SELECT SUM(chinese)/COUNT(*) AS aver FROM student;
SELECT AVG(chinese) FROM student;
SELECT MAX(english),MIN(english) FROM student;
注意分组group by 后的顺序是分组顺序
group by是分别按照每一组查询条件
SELECT AVG(sal),MAX(sal),MIN(sal),deptno FROM emp GROUP BY deptno;
这就是按照deptno分组,然后在每一组里面查询sal的均值和最大最小值
SELECT AVG(sal),MAX(sal),MIN(sal),deptno,job FROM emp GROUP BY deptno,job;
这里是在deptno的基础上,先按照deptno的基础上进行分组,再按照job进行细分,最后查询每个deptno的每个job的sal的最大值,最小值,平均值
having
是在分组后,再对所需要的条件进行补充,筛选
SELECT AVG(sal) AS avgsal,deptno FROM emp GROUP BY deptno HAVING avgsal < 2000;
字符串函数
DUAL 是亚元表,是MySQL自带的表
注意: 使用SUBSTRING如果没有指定长度,就默认取到结尾
# 返回所有该信息的字符集
SELECT CHARSET(ename) FROM emp;
# 把字符串和表进行拼接,最后显示的结果也是拼接的
SELECT CONCAT(ename,' job is ',job) FROM emp;
# 查询第二个字符串是从第一个字符串的第几个位置出现的
SELECT INSTR('hanshunping','ping') FROM DUAL;
# 把查询到信息进行大写
SELECT UCASE(ename) FROM emp;
# 把查询到的信息进行小写
SELECT LCASE(ename) FROM emp;
# 显示查询到的信息的左边几个字符
SELECT LEFT(ename,2) FROM emp;
# 显示查询到的信息的右边几个字符
SELECT RIGHT(ename,2) FROM emp;
# 显示查询到的信息的字节长度
SELECT LENGTH(ename) FROM emp;
# 把所要查询的列的欲替换的字符串,用字符串替换
SELECT ename ,REPLACE(job,'MANAGER','经理') FROM emp;
# 判断字符串是否相等,是否区分大小写在于其表的比较排序方式,相同返回0.否则为1
SELECT STRCMP('hsp','hsp') FROM DUAL;
# 显示截取字符串从哪个位置开始,截取几个
SELECT SUBSTRING(ename,1,2) FROM emp;
# 去掉字符串左边的空格
SELECT LTRIM(' sad') FROM DUAL;
# 去掉字符串右边的空格
SELECT RTRIM('sad ') FROM DUAL;
# 去掉字符串两边的空格
SELECT TRIM(' sad ')FROM DUAL;
数学函数
# 绝对值
SELECT ABS(-10) FROM DUAL;
# 转换到二进制
SELECT BIN(10) FROM DUAL;
# 返回向上取整的最小的数
SELECT CEILING(1.1) FROM DUAL;
# 把第一个数从多少进制,转到多少进制
SELECT CONV(8,10,2) FROM DUAL;
# 向下取整,得到比它小的最大整数
SELECT FLOOR(1.1) FROM DUAL;
# 保留小数位数,四舍五入,保留指定位数
SELECT FORMAT(78.1234235,2) FROM DUAL;
# 转换到十六进制
SELECT HEX(18) FROM DUAL;
# 求最小值
SELECT LEAST(0,2,45,34,-1) FROM DUAL;
# 求余
SELECT MOD(10,3) FROM DUAL;
# 返回随机数
SELECT RAND() FROM DUAL;
# 赋予随机数种子,能够保证每个随机数对应相应的种子,然后保持不变
SELECT RAND(2) FROM DUAL;
时间函数
# 当前的日期
SELECT CURRENT_DATE() FROM DUAL;
# 当前时间
SELECT CURRENT_TIME() FROM DUAL
# 当前的时间戳
SELECT CURRENT_TIMESTAMP() FROM DUAL;
# 当前的时间
SELECT NOW() FROM DUAL;
# 如果表中时间数据是日期和时分秒,可以通过函数只读取日期或者时分秒
SELECT id,content,DATE(send_time) FROM mes;
SELECT id,content,TIME(send_time) FROM mes;
# 查询在十分钟内(可以自己更改)的信息,利用的时间加(返回日期)
SELECT * FROM mes WHERE DATE_ADD(send_time,INTERVAL 10 MINUTE) >= NOW();
# 查询在十分钟内(可以自己更改)的信息,利用的时间减(返回日期)
SELECT * FROM mes WHERE DATE_SUB(NOW(),INTERVAL 10 MINUTE) <= send_time;
# 查询两个时间相差多少天,返回相差的天数
SELECT DATEDIFF('2011-11-11 11:11:11','1990-1-1 12:12:12') FROM DUAL;
# 求出两个时间的差,返回相差时间(时分秒表示)
SELECT TIMEDIFF('2020-12-12 13:11:12','2020-12-11 11:11:11')FROM DUAL;
# 取出时间的年月日
SELECT YEAR(NOW()) FROM DUAL;
SELECT MONTH(NOW()) FROM DUAL;
SELECT DAY(NOW()) FROM DUAL;
# 返回1970-1-1到现在的秒数
SELECT UNIX_TIMESTAMP() FROM DUAL;
# 把一个秒数转换为到1970-1-1的日期,要写格式(固定),(意义在能够让把以数字存储的时间转换回正常的表示模式)
SELECT FROM_UNIXTIME(1709104882,'%Y-%m-%d %H:%i:%s')FROM DUAL;
加密和系统函数
# 查询用户(返回用户@IP地址)
SELECT USER() FROM DUAL;
# 查看当前使用的数据库
SELECT DATABASE() FROM DUAL;
# 为字符串算出一个 MD5 32的字符串,常用(用户密码)加密
SELECT MD5('hsp')FROM DUAL;
# 使用password(str)进行加密
SELECT PASSWORD('ltp') FROM DUAL;
流程控制函数
# 类似于三元运算符
SELECT IF(TRUE,'北京','上海')FROM DUAL;
# 如果第一个为空,就返回第二个,否则返回第一个
SELECT IFNULL(NULL,'返回这个')FROM DUAL;
SELECT IFNULL('返回这个','第二个')FROM DUAL;
# 类似多重分支(if else),可以单独的作为一个列的条件
SELECT CASE WHEN FALSE THEN '第一个' WHEN TRUE THEN '返回这个' ELSE '都不满足返回这个' END;
具体使用方法:
SELECT ename,IF(comm IS NULL,0.0,comm) FROM emp;
SELECT ename, IFNULL(comm,0.0) FROM emp;
SELECT ename,(SELECT CASE WHEN job = 'CLERK' THEN '职员' WHEN job = 'MANAGER' THEN '经理' WHEN job = 'SALESMAN' THEN '销售人员' ELSE job END)AS 'job' FROM emp;
查询加强
模糊查询
查询表的结构
排序(按优先级分别排序)
# 查询加强
# 时间日期可以直接比较
SELECT * FROM emp WHERE hiredate > '1992-1-1';
# 模糊查询
# %:表示0到多个任意字符
# _:表示单个字符
SELECT ename ,sal FROM emp WHERE `ename` LIKE 'S%';
# 显示第三个字母为O的人
SELECT ename,sal FROM emp WHERE ename LIKE '__O%';
# 查询表的结构
DESC emp;
# order by 可以按照先后顺序,进行逐级排序
SELECT * FROM emp ORDER BY deptno,sal DESC;
分页查询
# 分页查询
# limit 起始的位置-1,每次读取的行数(该语句放在最后)
SELECT * FROM emp ORDER BY empno LIMIT 0,3;-- 第一页(每页三行)
SELECT * FROM emp ORDER BY empno LIMIT 3,3;-- 第二页(后面以此类推)
总结(组合使用eg)
顺序
# 组合使用
SELECT COUNT(*) job,FORMAT(AVG(sal),1) FROM emp GROUP BY job;
SELECT COUNT(*),COUNT(comm) FROM emp;-- 利用count不统计null
SELECT COUNT(*) FROM emp WHERE comm IS NULL;-- 直接用where也行
# 两种查询思想,一种用where限定,一种利用count不统计null进行统计
SELECT COUNT(IF(comm > 500,comm,NULL)) FROM emp;
SELECT COUNT(*) FROM emp WHERE comm > 500;
# 去重(distinct)
SELECT COUNT(DISTINCT mgr) FROM emp;
SELECT FORMAT(AVG(sal),1) AS avg_sal FROM emp GROUP BY deptno HAVING avg_sal > 1000 DESC LIMIT 0,2;-- 这里是错的,因为format返回的是字符串,不能比较
SELECT AVG(sal) AS avg_sal FROM emp GROUP BY deptno HAVING avg_sal > 1000 ORDER BY avg_sal DESC LIMIT 0,2;
注意
format返回的是字符串
10.查询多表
注意:表名.*表示该表的所有列
下面分别是查询到样表(emp,dept)
多表查询
# 多表查询
# 直接查询两个表,会把两个表的列拼接在一起,而行会从第一张
# 表中取出1行,分别与第二张拼接,最后返回的行数是两个表行数的乘积
SELECT * FROM emp,dept;# 这种多表查询默认返回结果,称为笛卡尔集
# 在笛卡尔集的基础上添加过滤条件
# 当有n个表时,过滤条件至少需要n - 1 个,否则就会出现笛卡尔集
# 当我们的多个表有相同的列名时,指定列名要通过表名来指定
SELECT * FROM emp,dept WHERE emp.deptno = dept.deptno;
SELECT ename,sal,grade FROM emp,salgrade WHERE sal BETWEEN losal AND hisal;
自连接
自连接:把一张表看成两张表
# 自连接
# 把一张表当作几张表使用
# 对一张表起多个名字(原名 别名)
SELECT worker.ename AS '员工' ,boss.ename AS '上级' FROM emp worker ,emp boss WHERE worker.mgr = boss.empno;
子查询
单行子查询,多行子查询
注意:子查询也可以返回一张临时表,然后对表进行查询
# 子查询
# 单行子查询
# 查询和SMITH一个部门的员工
# 1.先查询出他的部门
SELECT * FROM emp WHERE ename = 'SMITH';
# 2.再通过他的部门设置过滤条件
SELECT ename FROM emp WHERE deptno = (SELECT deptno FROM emp WHERE ename = 'SMITH');
# 多行子查询
# 如何查询和部门10的工作相同的雇员的名字,岗位,工资,部门号,但是不含10自己的员工
# 1.查询到10号部门有那些工作(注意工作不能重复)
SELECT DISTINCT job FROM emp WHERE deptno = 10;
# 2.把上面查询到的结果当作子查询
# 下面是完整的语句
SELECT ename,job,sal,deptno
FROM emp
WHERE job IN (
SELECT DISTINCT job
FROM emp
WHERE deptno = 10
) AND deptno <> 10;
# 3.把子查询得到的当作一张临时表(记不清看hsp776)
all 和 any
# all
# 例子:显示工资比部门30的所有员工的工资高的员工的姓名,工资和部门号
SELECT ename,sal,deptno FROM emp WHERE sal > ALL(SELECT sal FROM emp WHERE deptno = 30);# 使用all
SELECT ename,sal,deptno FROM emp WHERE sal > (SELECT MAX(sal) FROM emp WHERE deptno = 30);# 使用max函数
# any
# 例子:显示工资比部门30的任意员工的工资高的员工的姓名,工资和部门号
SELECT ename,sal,deptno FROM emp WHERE sal > ANY(SELECT sal FROM emp WHERE deptno = 30);# 使用any
SELECT ename,sal,deptno FROM emp WHERE sal > (SELECT MIN(sal) FROM emp WHERE deptno = 30);# 使用min函数
多列子查询
# 多列子查询
# 例子:查询与ALLEN的部门和岗位完全相同的所有雇员(并且不含SMITH本人)
# (字段1,字段2...) = (select 字段1,字段2... FROM ...)保证一一对应
SELECT * FROM emp WHERE (deptno,job) = (SELECT deptno,job FROM emp WHERE ename = 'ALLEN') AND ename <> 'ALLEN';# 多列子查询
SELECT * FROM (SELECT deptno, job FROM emp WHERE ename = 'ALLEN') AS people,emp WHERE people.deptno = emp.deptno AND people.job = emp.job AND ename <> 'ALLEN';# 子查询充当临时表
11.表复制和去重
蠕虫复制:当我们要对sql语句进行效率测试时,需要大量的数据,可以通过该方法创建大量的数据
# 在一个表中插入另一个表的数据
CREATE TABLE my_table (id INT,`name` VARCHAR(32),sal DOUBLE,job VARCHAR(32),deptno INT);
INSERT INTO my_table (id,`name`,sal,job,deptno) SELECT empno,ename,sal,job,deptno FROM emp;
# 实现自我复制
INSERT INTO my_table SELECT * FROM my_table;
进行去重:
# 如何去除一张表中的重复记录(这里去重my_table)
# (1) 先创建一张临时表my_temp,该表结构和my_tab一样
CREATE TABLE my_temp LIKE my_table;
# (2) 把my_table的记录通过distinct关键字处理后,复制到my_temp
INSERT INTO my_temp SELECT DISTINCT * FROM my_table;
# (3) 清除掉my_table里所有的记录/或者直接删除my_table后,对my_temp进行改表名
DELETE FROM my_table;
# (4) 把my_temp里面的记录复制回my_table
INSERT INTO my_table SELECT * FROM my_temp;
# (5) 把临时表删掉
DROP TABLE my_temp;
12.合并查询
可以把查询到的几个表的结果拼接起来(列不拼接)
13.外连接
需求分析:我们在查询多表时,使用的是利用where语句对两张或者多张表,形成的笛卡尔集进行筛选根据关联条件,显示所有的匹配记录,匹配不上的,不显示
但是我们有时候需求,一张或几张表的信息需要全部显示(即使没有匹配上),这时需要使用外连接
# 外连接(包含三表联查)
# 创建表test1
CREATE TABLE test1 (id INT,`name` VARCHAR(32));
INSERT INTO test1 VALUES(1,'a'),(2,'b'),(3,'c'),(4,'d'),(11,'asd');
# 创建表test2
CREATE TABLE test2 (id INT,`number` INT);
INSERT INTO test2 VALUES(1,34),(2,23),(3,57),(5,12);
# 创建表test3
CREATE TABLE test3 (id INT,grade INT);
INSERT INTO tes3 VALUES(1,233),(2,455),(3,532),(11,345);
# 左连接
SELECT test1.*,`number` FROM test1 LEFT JOIN test2 ON test1.id = test2.id;
# 右连接
SELECT test1.*,`number` FROM test1 RIGHT JOIN test2 ON test1.id = test2.id;
# 三表联查
SELECT test1.*,`number`,grade FROM (test1 LEFT JOIN test2 ON test1.id = test2.id) LEFT JOIN test3 ON test1.id = test3.id;
SELECT test1.*,`number`,grade FROM test1 LEFT JOIN test2 ON test1.id = test2.id RIGHT JOIN test3 ON test1.id = test3.id;
14.约束
主键:
# 约束
# (1) PRIMARY KEY(主键)
# 一张表只能有一个主键,设置了主键的列,不能添加null和重复元素
# 虽然只一张表最多能有一个主键,但是可以是复合主键
# 这里只设置了一个列为主键
CREATE TABLE test1 (id INT PRIMARY KEY,age INT,`name` VARCHAR(32));
# 复合主键
# 这里需要PRIMARY KEY(列名),下面设置的是id和name的复合主键
CREATE TABLE test2 (id INT, age INT,`name` VARCHAR(32),PRIMARY KEY(id,`name`));
not null 和 unique
# (2)unique
# 一张表可以有多个unique,使用unique的列不能添加重复的元素
# 如果不加not null,则可以添加多个null;
CREATE TABLE test (id INT UNIQUE,`name` VARCHAR(32),age INT);
# unique与not null一起使用,效果类似与主键
CREATE TABLE test1 (id INT UNIQUE NOT NULL,`name` VARCHAR(32),age INT);
FOREIGN KEY(外键)
下面是作用的图解示意:
# foreign key
# 创建主表my_class
CREATE TABLE my_class (id INT ,-- 班级编号
`name` VARCHAR(32) NOT NULL DEFAULT '',PRIMARY KEY(id));
# 创建从表my_stu
CREATE TABLE my_stu(id INT,-- 学生编号
`name` VARCHAR(32) NOT NULL DEFAULT '',class_id INT,PRIMARY KEY(id),
FOREIGN KEY(class_id) REFERENCES my_class(id)-- 指定外键关系
);
# 测试数据
INSERT INTO my_class VALUES(100,'java'),(200,'web');-- 主表添加数据
INSERT INTO my_stu VALUES(1,'tom',100),(2,'jack',200),(3,'hsp',200);-- 从表添加数据
INSERT INTO my_stu VALUES(4,'cy',300);-- 因为主表的id没有300,所以添加失败
细节:
对于第4点,必须指向的外键允许可以为null才能在从表中设置null,否则不行
CHECK
# check
# 对指定的列做出限制
CREATE TABLE test (id INT,`name` VARCHAR(32),sex VARCHAR(6) CHECK( sex IN ('man','woman')),sal DOUBLE CHECK(sal > 1000 AND sal < 2000));
自增长:
# 自增长
CREATE TABLE test (id INT PRIMARY KEY AUTO_INCREMENT,email VARCHAR(32) NOT NULL DEFAULT '',`name` VARCHAR (32) NOT NULL DEFAULT '');
INSERT INTO test VALUES(3,'tom@qq.com','tom');
INSERT INTO test VALUES(NULL,'jack@qq.com','jack');-- 在3的基础上进行自增长
INSERT INTO test (email,`name`) VALUES('lucy@qq,com','lucy');-- 如果不指定值也会进行自增长
自增长要和主键或者unique搭配使用
直接指定值,就不会进行自增长
自增长是按照当前最大的值进行自增长
# 设置自增长的初始值
ALTER TABLE test AUTO_INCREMENT = 100;
15.索引
索引的好处:
我们进行普通查询时,是对整个表进行查询,当表数据很多时,查询速度低。而使用索引,可以通过数据结构的方式(二叉树,B树,B+树…),来优化查询速度,但是只能针对某一个列
索引的类型
对于唯一索引和普通索引的选择,在于该列元素是否能重复
添加/删除 索引
# 索引
# 添加索引
# 例表
CREATE TABLE test (id INT ,`name` VARCHAR(32));
# 添加唯一索引(一般认为该元素不会重复时使用)
CREATE UNIQUE INDEX id_index ON test (id);
# 添加普通索引
# (1)
CREATE INDEX id_index ON test (id);
# (2)
ALTER TABLE test ADD INDEX id_index(id);
# 添加主键索引
# (1)在创建表的时候,添加PRIMARY KEY(主键)
# (2)之后再改
ALTER TABLE test ADD PRIMARY KEY(id);
# 查询表的索引
# (1)
SHOW INDEX FROM test;
# (2)
SHOW INDEXES FROM test;
# (3)
SHOW KEYS FROM test
# 删除索引
# 删除索引
DROP INDEX id_idex ON test
# 删除主键索引
ALTER TABLE test DROP PRIMARY KEY;
# 修改索引:先删除索引,再添加新的索引
索引的使用
16.事务
事务的介绍
回退事务
# 事务
# 回退事务(注意只能从后往前回退,而不能从前面回退到后面去)
# 先创建一张表
CREATE TABLE test1 (id INT,`name` VARCHAR(32));
# 开始事务
# (1)
START TRANSACTION
# (2)
SET autocommit = off;
# 设置保存点
SAVEPOINT a
# 执行dml操作
INSERT INTO test1 VALUES(100,'tom');
SELECT * FROM test1;
SAVEPOINT b;-- 再设置一个保存点
INSERT INTO test1 VALUES(200,'jack');
# 回退到b
ROLLBACK TO b;
# 回退到a
ROLLBACK TO a;
隔离等级
ps:脏读 = 预读,不可重复读 = 错读,幻读 = 多读(也是错读)
具体解释可以看相关资料或者通过老韩800和801集进行理解
# 隔离级别
# 查看当前MySQL的隔离级别
SELECT @@tx_isolation;
# 查看系统当前隔离级别
SELECT @@global.tx_isolation;
# 设置当前会话隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
# 设置系统当前隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
17,存储引擎
查询存储引擎
# 表类型和存储引擎
# 查看所有的存储引擎
SHOW ENGINES;
大致比较
细节说明
常见的存储引擎的选择
# innodb 存储引擎
-- 1.支持事务 2.支持外键 3.支持行级锁
# myisam 存储引擎
-- 1. 添加速度快 2. 不支持外键和事务 3.支持表级锁
CREATE TABLE test (id INT,`name` VARCHAR(32)) ENGINE MYISAM;
# memory 存储引擎
-- 1. 数据存储在内存中[关闭了MySQL服务,数据丢失,但是表结构还在] 2. 执行速度很快(没有IO读写) 3. 默认支持索引(hash表)
# 修改引擎
ALTER TABLE test ENGINE = 存储引擎;
18.视图
需求分析:
视图和表的对应关系
# 视图
# 创建视图
CREATE VIEW test AS SELECT empno,ename,job,deptno FROM emp;
# 查看视图
DESC test;
SELECT * FROM test;
# 修改视图(视图的结构)
ALTER VIEW test AS SELECT empno,job,deptno FROM emp;
# 修改视图来修改基表的数据
UPDATE test SET job = 'MANAGER' WHERE empno = 7369;
# 视图中还可以再使用视图
CREATE VIEW testview AS SELECT empno,ename FROM test;
# 查看创建视图的指令
SHOW CREATE VIEW test;
# 删除视图
DROP VIEW test;
视图查询到的数据,不是视图里面包含数据库文件,而是基表对数据的引用,所以修改视图会影响基表,修改基表也会影响到视图
视图实践场景
19.用户管理
目的:不同的数据库用户,登录到DBMS后,根据相应的权限,可以操作的数据库和数据对象(表,视图。触发器)都不一样
# MySQL的用户管理
# 需求:可以根据不同的开发人员,赋给他相应的MySQL操作权限
# 创建用户(注意这里@符号不能空格)
CREATE USER 'wantian java'@'localhost' IDENTIFIED '123456';
-- (1)'wantian java' @ 'localhost'是用户的完整信息,'wantian java' 是用户名,'localhost'表示登录的ip
-- (2)IDENTIFIED '123456'中的123456是密码,通过password的加密函数储存在数据库中
# 删除用户
DROP USER 'wantian java'@'localhost';
# 修改密码(自己的)
SET PASSWORD = PASSWORD('asdfgh')
# 修改其它用户的密码(首先要有修改它密码的权限)
SET PASSWORD FOR 'root' @ 'localhost' = PASSWORD('123456');
用户的权限
综合 应用
# 创建用户
CREATE USER 'wantian java'@'localhost' IDENTIFIED BY '123';
CREATE DATABASE testdb;-- 创建数据库
# 查询用户表
SELECT * FROM mysql.user;
CREATE TABLE news(id INT,content VARCHAR(32));-- 创建表
INSERT INTO news VALUES(100,'北京烤鱼');
# 授予用户权限
GRANT SELECT,INSERT ON testdb.news TO 'wantian java'@'localhost';
GRANT UPDATE ON testdb.news TO 'wantian java'@'localhost';
# 修改密码
SET PASSWORD FOR 'wantian java'@'localhost' = PASSWORD('abc');
# 收回用户权限
REVOKE SELECT ,UPDATE,INSERT ON testdb.news FROM 'wantian java'@'localhost';
# 删除用户
DROP USER 'wantian java'@'localhost';
用户管理细节
# 相当于是 用户@%,用户可以从任意ip登录
CREATE USER jack;
# 用户ip是192.168开头的可以登录
CREATE USER 'jacl1'@'192.168.%'
DROP USER jack;
DROP USER 'jack1'@'192,168,%'
20.细节加作业
在使用别名时,as可加可不加,如果别名中间有空格,必须把整个别名用双引号框住,如果没有,可用可不用
# 显示名字中不带R的员工
SELECT * FROM emp WHERE ename NOT LIKE '%R%';
# 显示所以员工姓名的左边三个字母
SELECT LEFT(ename,3) FROM emp;
# 显示所有员工的姓名,用a替换所有的A
SELECT REPLACE(ename,'A','a') FROM emp;
# 显示所有员工的姓名,工作和薪资,按工作降序排顺序,工作相同时,按照薪资排序
SELECT ename,jib,sal FROM emp ORDER BY job DESC,sal
# 显示在一个月30天的情况下,所有员工的日薪金,忽略余数
SELECT ename, FLOOR(sal / 30) FROM emp;
# 显示对于每个员工,其加入公司的天数
SELECT ename,DATEDIFF(NOW(),hiredate) AS daies FROM emp;
# 显示姓名字段的任何位置包含‘A’的所有员工的姓名
SELECT ename FROM emp WHERE ename LIKE '%A%';
# 以年月日的方式显示所有员工的服务年限(大概)
SELECT ename, FLOOR(DATEDIFF(NOW(),hiredate)/365) AS '工作年',FLOOR(DATEDIFF(NOW(),hiredate) % 365 / 31) AS '工作月',DATEDIFF(NOW(),hiredate) % 31 AS '工作天' FROM emp;
# 显示至少有一个员工的部门
SELECT COUNT(*) AS c,deptno FROM emp GROUP BY deptno HAVING c > 1;
# 显示薪金比’SMITH’多的所有员工
SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename = 'SMITH');
# 列出受雇日期晚于其上级的所有员工
SELECT worker.ename AS '员工名',worker.hiredate AS '员工入职时间',leader.ename AS '上级名',leader.hiredate AS '上级入职时间' FROM emp AS worker,emp AS leader WHERE worker.hiredate > leader.hiredate AND worker.mgr = leader.empno;
# 列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门
SELECT dname,emp.* FROM dept LEFT JOIN emp ON dept.deptno = emp.deptno
# 列出所有“CLERK” 的姓名及其部门名称
SELECT ename,dname FROM emp,dept WHERE job = 'CLERK' AND emp.deptno = dept.deptno;
# 列出薪资大于公司平均薪资的员工
SELECT * FROM emp WHERE sal > (SELECT AVG(sal) FROM emp);