数据库入门(二)

数学函数
绝对值:

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

查询增强

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)) //外键的使用

细节:

自增长:字段名 字段类型 auto_increment

mysql索引:

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 //提交操作,所有的保存点被删除,不能回退

脏读:当一个事务读取了另一个事务尚未提交的修改
不可重复读:一个事务读取到另一个事务通过修改删除后提交的数据
幻读:一个事务读取到另一个事务通过插入后提交的数据
几种隔离级别的效果:

存储引擎

//查看所有引擎
SHOW ENGINES

CREATE TABLE t28(
id INT,
`name` VARCHAR(32)) ENGINE MYISAM
//此种引擎添加速度快,不支持外键和事务,支持表级锁

memory引擎 -数据存储在内存中,执行速度很快无io,默认支持索引

如何选择引擎:

视图

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

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 //权限生效指令,如果权限没有生效用该指令