数据库入门(二)

布满荆棘的人生 2023-09-26 18:49 21阅读 0赞

数学函数  
绝对值:

select ABS(-10) from dual;//返回绝对值

进行向上取整

select ceiling(1.1) from dual

进制转换

SELECT CONV(8,10,2) FROM DUAL//将十进制的8转换为2进制的

保留指定的小数位数

SELECT FORMAT(78.324423442,2) FROM DUAL

日期函数的使用:

SELECT CURRENT_DATE() FROM DUAL //当前的日期
    SELECT CURRENT_TIME() FROM DUAL //当前的时间
    SELECT CURRENT_TIMESTAMP FROM DUAL //当前的时间戳
    SELECT * FROM mes WHERE DATA ADD(send_time,INTERVAL 10 MINUTE) >= NOW() //查询十分钟内的内容

加密函数和系统函数

SELECT USER() FROM DUAL //查询用户
    SELECT DATABASE();//查询当前使用的数据库名称
    SELECT MD5('123456') FROM DUAL //算出一个md5 32 的字符串,常用于用户加密
    SELECT PASSWORD('hsp') FROM DUAL //加密函数,mysql数据库的用户就是这个函数进行加密

流程控制函数

IF (expr1,expr2,expr3) //如果expr1为true,则返回expr2,否则返回expr3
    IFNULL(expr1,expr2) //如果expr1不为空,额返回expr1,否则返回expr2

![在这里插入图片描述][7cb6f9bbac934600b6fdf579cdb42a4f.png]  
查询增强

SELECT *FROM emp WHERE `hiredate` > '1992-01-01' //查询这个时间以后入职的员工
    SELECT * FROM emp WHERE mgr IS NOT NULL //筛选mgr不为空的

分页查询

SELECT *FROM emp ORDER BY empno LIMIT 12,3 //表示从第12的记录开始,显示三条记录
    SELECT COUNT(*),AVG(sal), job FROM emp GROUP BY job //显示每种岗位的雇员总数和平均工资
    SELECT COUNT(*) FROM emp WHERE comm IS NOT NULL //补助不为空的人数
    SELECT COUNT(*),job FROM emp GROUP BY job //显示每种岗位的雇员总数
    SELECT COUNT(DISTINCT mgr) FROM emp //显示管理员的总人数
    GROUP BY 用于进行分组,过滤用having ,ORDER BY 用于排序,limit用于分页

### 多表查询: ###

SELECT *FROM emp,dept WHERE emp.deptno = dept.deptno //多表查询,将部门与雇员结合起来
    SELECT *FROM emp,salgrade WHERE sal >losal AND sal<hisal //显示员工信息以及工资级别
    SELECT worker.ename AS '职员名',boss.ename AS '上级名' FROM emp worker,emp boss WHERE worker.mgr = boss.empno
    //自连接的使用,将一张表用作为两张表进行查询
    SELECT *FROM emp WHERE deptno =(SELECT deptno FROM emp WHERE ename = 'SMITH')//查询与smith相同部门的人,单行子查询
    SELECT ename,job,sal,deptno FROM emp WHERE job IN (SELECT job FROM emp WHERE deptno = 10) //查询与10号部门相同的工作的信息,多行子查询,10号部门工作有很多个
    SELECT * FROM emp WHERE(deptno ,job )=(SELECT deptno,job FROM emp WHERE ename = 'SMITH') //查询与smith相同部门和工作的人

### 表复制 ###

INSERT INTO student  SELECT *FROM student //将student的记录继续插入到student中
    CREATE TABLE student1 LIKE student //创建一张表,表的结构与student一样
    INSERT INTO student1 SELECT DISTINCT *FROM student //将已经去重复的student记录插入到表student中
    //union all 可将两个查询语句的结果合并
    //左外连接:SELECT .... FROM 表1 LEFT JOIN 表2 ON  条件

unique的使用: 字段 字段类型 unique //该字段的值不能够重复但可以为空值

### 外键与主键: ###

使用:\`

CREATE TABLE my_class (
    id INT PRIMARY KEY,
    `name` VARCHAR(32) NOT NULL DEFAULT '') 
    CREATE TABLE mu_stu(
    id INT PRIMARY KEY,
    `name` VARCHAR(32) NOT NULL DEFAULT '',
    class_id INT,
    FOREIGN KEY(class_id) reference`my_class``mu_stu``my_class`s my_class(id)) //外键的使用

细节:  
![在这里插入图片描述][6cb290a5fcc449edbfd65563b54b2800.png]

自增长:字段名 字段类型 auto\_increment

### mysql索引: ###

![在这里插入图片描述][76a6fef9a93b4ad9bb02ad22e38dc516.png]

CREATE TABLE t25 (
    id INT ,
    `name` VARCHAR(32))
    CREATE UNIQUE INDEX id_index ON t25(id);//创建唯一索引,唯一索引的值不会重复
    CREATE INDEX id_index ON t25(id); //普通索引某列内的值可以重复
    DROP INDEX id_index ON t25 //删除t25 表上的id_index索引
    ALTER TABLE t25 DROP PRIMARY KEY //删除表的主键
    SHOW INDEX FROM t25 //查询索引
    SHOW KEYS FROM t25 //查询索引
    //修改索引 先删除索引,再增加索引

### 事务 ###

事务用于保证数据的一致性,由一组相关的dml语句组成,改组的dml语句要么全部成功,要么全部失败.

CREATE TABLE t27(
    id INT,
    `name` VARCHAR(32));
    
    START TRANSACTION //开始事务
    SAVEPOINT a //设置保存点
    //执行dml操作
    INSERT INTO t27 VALUES(100,'tom');
    SELECT *FROM t27;
    
    SAVEPOINT b;//设置保存点b
    INSERT INTO t27 VALUES(200,'jack');
    //回退到b
    ROLLBACK TO b //如果直接写rollback表示直接回退到事务开始状态
    
    COMMIT //提交操作,所有的保存点被删除,不能回退

脏读:当一个事务读取了另一个事务尚未提交的修改  
不可重复读:一个事务读取到另一个事务通过修改删除后提交的数据  
幻读:一个事务读取到另一个事务通过插入后提交的数据  
几种隔离级别的效果:  
![在这里插入图片描述][71eee33b28d54b3aaac2f856fd5c94db.png]  
![在这里插入图片描述][d66dbe52971249c0ba6f0ce4868cbd77.png]

### 存储引擎 ###

![在这里插入图片描述][345b362bfffb415a800741899d3778ce.png]

//查看所有引擎
    SHOW ENGINES
    
    CREATE TABLE t28(
    id INT,
    `name` VARCHAR(32)) ENGINE MYISAM
    //此种引擎添加速度快,不支持外键和事务,支持表级锁
    
    memory引擎 -数据存储在内存中,执行速度很快无io,默认支持索引

如何选择引擎:  
![在这里插入图片描述][436ea81ea0b94aab8e6988830311d27a.png]

### 视图 ###

视图是根据基表来创建的,视图是虚拟的表,视图也有列,数据来源于基表,通过视图可以修改基表的内容,修改基表也会影响到视图。
    视图的本质是一些数据表的内容是保密的,不能让用户看到
    视图的基本使用:

CREATE VIEW emp_view01 AS SELECT empno,ename,job,deptno FROM emp;//创建视图
    
    SELECT *FROM emp_view01
     
     SHOW CREATE TABLE emp_view01 //查看创建视图的命令
     
     DROP VIEW emp_view01 //删除视图

### mysql用户管理 ###

CREATE USER 'hsp_du'@'localhost' IDENTIFIED BY '123456'; //创建用户包括用户名用户ip和用户密码
    SELECT * FROM mysql.user //查看musql数据库的user表
    
    DROP USER 'hsp_du'@'localhost' //删除用户
    grand SELECT,DELETE,CREATE,update ON *.* TO '用户名'@'登录位置即ip' IDENTIFIED BY '密码' //给予用户在*数据库的*对象上的选择,删除,创建,修改权限
    grand改为revoke就是回收权限
    FLUSH PRIVILEGES //权限生效指令,如果权限没有生效用该指令

[7cb6f9bbac934600b6fdf579cdb42a4f.png]: https://img-blog.csdnimg.cn/7cb6f9bbac934600b6fdf579cdb42a4f.png
[6cb290a5fcc449edbfd65563b54b2800.png]: https://img-blog.csdnimg.cn/6cb290a5fcc449edbfd65563b54b2800.png
[76a6fef9a93b4ad9bb02ad22e38dc516.png]: https://img-blog.csdnimg.cn/76a6fef9a93b4ad9bb02ad22e38dc516.png
[71eee33b28d54b3aaac2f856fd5c94db.png]: https://img-blog.csdnimg.cn/71eee33b28d54b3aaac2f856fd5c94db.png
[d66dbe52971249c0ba6f0ce4868cbd77.png]: https://img-blog.csdnimg.cn/d66dbe52971249c0ba6f0ce4868cbd77.png
[345b362bfffb415a800741899d3778ce.png]: https://img-blog.csdnimg.cn/345b362bfffb415a800741899d3778ce.png
[436ea81ea0b94aab8e6988830311d27a.png]: https://img-blog.csdnimg.cn/436ea81ea0b94aab8e6988830311d27a.png