MySQL全部笔记总结
MySQL笔记
-------------------------------Mysql 数据库-----------------------------------------
-- 基本知识 --
-
Sql DB DBMS 之间的关系？
DB： DataBase (数据库，数据库在硬盘上以文件的形式存在)DBMS： DataBase Management System (数据库管理系统：Mysql,Oracle,DB2,Sybase)
SQL: 结构化查询语言 (Structured Query Language)，SQL 语句在执行的时候内部也会先进行编译，sql语句的编 译由DBMS完成 -
数据库中的表
* 表分为行和列： - 行：行叫做 数据/记录(data)，每条记录又叫做元组。 - 列：列叫做字段/属性(column) - table 是数据库的基本组成单元，所有数据都以表格的形式组织，目的是可读性强 - 每一个字段包含哪些属性：字段名 数据类型，相关的约束 -
SQL语句的分类
DQL(data query language): 数据查询语言 select操作,用来查询所需要的数据
DML(data manipulation language): 数据操作语言，主要是数据库增删改三种操作,用于改变数据表中的数据,它和事务相关，执行完后需要经过事务控制语句提交后才真正的将改变应用到数据库中。
包括： Insert：将数据插入到数据库表中 Update：更新数据库表中已存在的数据 Delete：删除数据库表中的数据DDL(data defination language): 数据库定义语言，主要是建表、删除表、修改表字段等操作,用于建立，修改，删除数据库对象。
包括：Create：创建表或者其他对象的结构 Alter：修改表或者其他对象的结构 Drop：删除表或者其他对象的结构 TRUNCATE：删除表数据，保留表结构TCL:(Tranction Control Language) 事务控制语言,用来维护数据一致性的语句
包括： Commit：提交，确认已经进行的数据改变 RollBack：回滚，取消已经进行的数据改变 SavePoint：保存点，使当前的事务可以回退到指定的保存点，便于取消部分改变DCL:(data control language) : 数据库控制语言，用于执行权限的授予和收回操作
包括： GRANT：授予，用于给用户或角色授予权限 REVOKE：用于收回用户或者角色已有的权限 CREATE USER：创建用户
4.数据类型(列类型)
*不同的列类型所占用的存储空间和效率是不同的。
-
数值型：整型，浮点型，定点型
-
-
1字节=8位 也就是 00000000~11111111 转换 二进制 0~255
-
0 0000000~0 1111111 0 ~ 127
1 0000000~1 1111111 -0 ~ -127 故 有符号 为 -128~127
-
-
加unsigned 表示无符号，可以影响存储的范围。
-
alter table jobinfo add snum smallint(5) zerofill not null default 0;
其中smallint(5) 表示数值的宽度为 5位数 ； zerofill 意思为 不够的位数补0；
-
添加zerofill参数时，数据必然是unsigned类型。
类型 占用空间 默认值 取值范围
tinyint 1字节 0~255 or -128~127
smallint 2字节 0~65535 or -32768~32767
mediuint 3字节 0~16777215(一千六百万) or -8883608~8388607(八百三十万)
int 4字节 0~42949672959(42亿) or -2147483648~2147483647(21亿)
bigint 8字节 千亿
--基本语句--
- 查看mysql版本 select version();
1.库操作
-
连接数据库
-
mysql -u root -p
-
mysql -u root -p****** // 在p后可直接输入密码。
-
-
当链接上服务器以后我们首先面对的是库，如果我们相对某张表或者某行数据做相关操作的话，需要先选择库。
-
展示库列表 show databases;
-
查看数据库中的表： show tables from emp;(database name)
-
选库 use databasename(库名);
-
创建数据库 create database 数据库名 [charset 字符集(utf8)]
-
初始化/导入数据： source D:\A\B\C.sql;
-
删除数据库 drop database databasename;
-
-
在mysql中，表/列 可以改名字，database不能改名字。除非使用IDE，phpMySqlAdmin 工具，它会把该库下的所有表复制到新库，在删除旧库。
-
表操作
* 展示表 show tables; * 查看表结构 desc tablename； * 查看创建表的语句： show create table dept; // 查看创建dept表的时候的语句 * 创建表 建表的过程就是定义字段的过程 1. create table stu //stu 表示tablename (id int, sname varchar(10)) engine myisam charset utf8; //engine 表示引擎和性能特点相关。 2. -> create table jobinfo ( id int primary key auto_increment, //primary 主键；auto_increment 自增量。 name varchar(10) not null default ' ' , gender char(1) not null default ' ' , company varchar(10) not null default' ' , salary decimal(6,2) not null default ' ' , fanbu int not null default 0 ) engine myisam charset utf8; *插入新列 alter table jobinfo add workage tinyint unsigned not null default 0; *改表名 rename table stu to newstu; //stu 表示tablename，改表名字。 *插入数据 1. 整体插入 insert into newstu values (1,'张三','男',18), (2,'施歌','男',58); 2. 部分插入 insert into stu //需要添加数据的表 (id,sname,age,sex) //需要添加数据的字段，如果某字段不需要添加数据，不写即可 values (185478, '电信', 25, '男') ; *删除表数据 delete from stu //删除表数据只能删除整条数据，不能单独删除某条数据的某个值 where age=25; *清空表数据 truncate newstu; //清空表中的数据，并不删除表结构，重构空表 返回：Empty set (0.00 sec) * 删除表 drop table stu; //stu 表示tablename， 删除表结构 * 删除表的速度 drop > truncate > delete * 修改数据 update jobinfo set salary=salary+500，fanbu=2000 // set 后可以修改多条数据，每条数据用逗号隔开 where name='施自强' and gender='男'; //and表示且，or表示或 * 查找表数据 select * from tablename; //其中 * 代表列，表名后的where条件代表行。 +------+----------+------+------+ | id | sname | sex | age | +------+----------+------+------+ // select （查什么）所需要的数据1，数据2，数据3 from | 1 | zhangsan | 男 | 18 | tablename where 所需要以上数据的人（查谁的） | 2 | zlisn | 男 | 58 | +------+----------+------+------+ * 按条件查找表数据 select name,company,salary from jobinfo where id=19501;
3.常见问题
* 乱码怎么解决？
- 告诉服务器我使用的编码方式 : set names GBK/UTF8/...
* 怎么把键入的SQL语句输出到一个sql文件里： tee D:\1106.sql
-- 简单查询
* 语法：
select 字段名，字段名，字段名 from 表名;
* 查询员工的名字
select ename from employee;
* 查看某条SQL 语句的执行情况 explain关键字
explain select * from emp where sal>5000;
分组函数(多行处理函数)
-
分组函数包括 count、max、min、avg、sum
-
分组函数的特点是 : 输入多行，输出一行。
-
分组函数自动忽略NULL(所以使用分组函数不需要加 is not null 这个条件)
select count(comm) from emp; 查询出四条数据 因为忽略了null。
-
单行处理函数
-
单行输处理函数的特点是: 输入一行，输出一行。
-
计算每一个员工的年薪(年薪=(月薪+月补助)*12)
select ename,(sal+comm)*12 as yearSal from emp;
+--------+---------------+
| ename | (sal+comm)*12 |
+--------+---------------+
| SMITH | NULL |
| ALLEN | 22800.00 |
| WARD | 21000.00 |
| JONES | NULL |
| MARTIN | 31800.00 |
| BLAKE | NULL |
| CLARK | NULL |
| SCOTT | NULL |
| KING | NULL |
| TURNER | 18000.00 |
| ADAMS | NULL |
| JAMES | NULL |
| FORD | NULL |
| MILLER | NULL |
+--------+---------------+
为什么会存在NUll值呢?
因为在数据库中，只要数学表达式中存在NULL，那么该表达式最终的值都是NUll。-
那么怎么解决表达式中存在Null的情况呢？
可以使用空值处理函数: ifnull(可能为NULL的字段,被当做什么值处理)
查询员工的年薪:
mysql> select ename,(sal+ifnull(comm,0)) as yearSal from emp; +--------+----------+ | ename | yearSal | +--------+----------+ | SMITH | 1300.00 | | ALLEN | 1900.00 | | WARD | 1750.00 | | JONES | 23800.00 | | MARTIN | 2650.00 | | BLAKE | 22800.00 | | CLARK | 19600.00 | | SCOTT | 3000.00 | | KING | 5000.00 | | TURNER | 1500.00 | | ADAMS | 1100.00 | | JAMES | 950.00 | | FORD | 3000.00 | | MILLER | 1300.00 | +--------+----------+ -
-
查询所有薪资高于公司平均薪资的员工
mysql> select ename,sal from emp where sal>(select avg(sal) from emp);+-------+----------+
| ename | sal |
+-------+----------+
| CLARK | 19600.00 |
| BLAKE | 22800.00 |
| JONES | 23800.00 |
+-------+----------+-
注意: 分组函数不能直接使用在where子句之中。
-
count(*) 统计总记录条数
-
count(某个字段) 统计该字段不为NULL的记录条数
-
-
group by 和 Having
-
group by 是按照某个字段或者某些字段进行分组
-
having 是对分组后的数据进行二次过滤
-
分组函数一般都会和group by联合使用,并且任何一个分组函数都是在group by 语句执行结束之后才会执行的。
-
当整个sql语句中没有group by的时候，整张表自成一组。
-
where 子句在group by子句之前执行; 而分组函数在group by子句之后执行， 所以 group by不能用在where 子句之中。
-
执行顺序
select 5
..
from 1
..
where 2
..
group by 3
..
having 4
..
order by 6
..
-
-- 连接查询
* 根据表的连接方式分别 92语法 和 99语法
* 根据表的连接方式划分： 内连接 ，外连接 ， 全连接
- 内连接分为： 等值连接， 非等值连接， 自连接
- 外连接分为： 左外连接(左连接)，右外连接(右连接)
* 在表的连接查询中有一种现象被称为笛卡尔积(笛卡尔乘积现象)
* 避免笛卡尔积现象(避免笛卡尔积现象，并不会减少底层数据库的匹配次数，只不过显示的数据条数减少了)
-加条件进行过滤
* 内连接
- 等值连接 最大的特点是条件是等量关系
> 语法 ：
A表
inner join
B 表
on
连接条件
where
筛选语句
> 查询员工的部门名称 显示部门名称和员工姓名
select e.ename,d.deptname from employee e inner join dept d on e.deptid = d.deptid;
- 内连接中的非等值连接 最大的特点是： 连接条件中的关系是非等量关系
> 查询员工的工资等级，要求显示员工姓名和工资等级
SELECT E.ENAME ,S.GRADE FROM EMP E INNER JOIN SALGRADE S ON E.SAL BETWEEN S.LOSAL AND S.HISAL
- 内连接中的自连接 自大特点： 一张表看做两张表
SELECT A.ENAME ,B.ENAME FROM EMP A INNER JOIN EMP B ON A.MGR= B.EMPNO;
-
外连接
- 什么是外连接？ 和内连接有什么区别？
内连接：假设A 表 和 B 表进行内连接，凡是A表和B 表能够匹配的记录都会查询出来，A和B 表没有主副之分。
外连接： 假设A表和B 表进行外连接，AB两张表中会有一张表时主表，一张表是副表，主要查询主表中的数据，捎带 着查询副表，如果副表中的数据没有和主表中的数据匹配上的话，副表自动模拟出NULL与主表匹配。
- 外连接分类
左外连接 左边的表是主表
SELECT E.ENAME ,E.SAL ,E.JOB ,D.DNAME FROM EMP E LEFT OUTER JOIN DEPT D ON E.DEPTNO=D.DEPTNO;右外连接 右边的表是主表
SELECT E.ENAME ,E.SAL ,E.JOB ,D.DNAME FROM EMP E RIGHT OUTER JOIN DEPT D ON E.DEPTNO=D.DEPTNO;左连接有右连接的写法， 右连接也有左连接的写法
- 三表连接
SELECT E.ENAME ,D.DNAME ,S.GRADE FROM EMP E INNER JOIN DEPT D ON E.DEPTNO=D.DEPTNO INNER JOIN SALGRADE S ON E.SAL BETWEEN S.LOSAL AND S.HISAL
- 四表连接
SELECT E.ENAME ,D.DNAME ,S.GRADE , M.ENAME 领导 FROM EMP E INNER JOIN DEPT D ON E.DEPTNO=D.DEPTNO INNER JOIN SALGRADE S ON E.SAL BETWEEN S.LOSAL AND S.HISAL LEFT OUTER JOIN EMP M ON E.MGR=M.EMPNO
-- 子查询
- 什么是子查询？ 子查询可以出现在哪里？
# 子查询就会select语句中嵌套select语句
# 子查询可以出现在：select from where 等后边
- WHERE 子句中使用子查询
> 找出高于平均薪资的员工信息
SELECT * FROM EMP WHERE SAL >(SELECT AVG(SAL) FROM EMP )
- FROM 子句中使用子查询
> 查询出每个部门平均薪资的薪资等级
SELECT TEMP.DEPTNO,TEMP.AVGSAL, S.GRADE
FROM SALGRADE S ,(SELECT DEPTNO ,ROUND(AVG(SAL), 2) AVGSAL FROM EMP GROUP BY DEPTNO )TEMP
WHERE TEMP.AVGSAL BETWEEN S.LOSAL AND S.HISAL
> 查询出每个部门薪水等级的平均值
SELECT E.DEPTNO,AVG(S.GRADE )
FROM EMP E
INNER JOIN SALGRADE S
ON E.SAL BETWEEN S.LOSAL AND S.HISAL
GROUP BY DEPTNO
- 在SELECT 后边嵌套子查询
> 查询出每个员工所在的部门名称
# 连接查询实现
SELECT E.ENAME ,D.DNAME
FROM EMP E
INNER JOIN DEPT D
ON E.DEPTNO = D.DEPTNO
# 子查询实现
SELECT E.ENAME ,(
SELECT D.DNAME FROM DEPT D WHERE E.DEPTNO=D.DEPTNO
) DNAME
FROM EMP E
-- 查询结果集的连接
-
UNION 将查询结果集相加
查询出工作岗位是 'SALESMAN','MANAGER' 的员工信息
实现方法一
SELECT EMPNO ,ENAME ,JOB
FROM EMP
WHERE JOB IN ('SALESMAN','MANAGER')
# 使用union实现
SELECT ENAME ,JOB FROM EMP WHERE JOB ='SALESMAN'
UNION
SELECT ENAME,JOB FROM EMP WHERE JOB ='MANAGER'
-- LIMIT
-
limit 是mysql特有的，其他数据库没有
-
limit取结果集中的部分数据
-
语法：
-
limit startindex , length
-
startindex 表示起始位置
-
length 表示长度取几个
-
LIMIT 是SQL 语句最后执行的环节。
取出公司工资前五名的员工信息
SELECT ENAME ,salary FROM employee ORDER BY salary DESC LIMIT 0,5
SELECT ENAME ,salary FROM employee ORDER BY salary DESC LIMIT 5 // 只写5的话默认从0开始取
取第四到第九的值
SELECT ENAME ,salary FROM employee ORDER BY salary DESC LIMIT 3,6
- 通用的标准分页SQL
假设每页显示3条数据
页数 X n 第一页： 0,3 第二页： 3,3 第三页： 6,3 第四页： 9,3每页显示n条记录
N*(页数-1)=X
-
-- 存储引擎
-
数据库中的各表在创建表时均被存储引擎来处理
-
建表时可以设置存储引擎和字符集
-
默认的存储引擎是InonDB,默认的字符集是UTF-8
-
MYsql支持多种存储引擎，每一种存储引擎代表了一种存储方式
-
查看Mysql的存储引擎
- show engines \g;
-
常见的存储引擎
1.MYISAM 存储引擎
使用这个存储引擎，每个MyISAM在磁盘上存储成三个文件。
（1）frm文件：存储表的定义数据
（2）MYD文件：存放表具体记录的数据
（3）MYI文件：存储索引
frm和MYI可以存放在不同的目录下。MYI文件用来存储索引，但仅保存记录所在页的指针，索引的结构是B+树结构。下面这张图就是MYI文件保存的机制：
从这张图可以发现，这个存储引擎通过MYI的B+树结构来查找记录页，再根据记录页查找记录。并且支持全文索引、B树索引和数据压缩。
支持数据的类型也有三种：
（1）静态固定长度表
这种方式的优点在于存储速度非常快，容易发生缓存，而且表发生损坏后也容易修复。缺点是占空间。这也是默认的存储格式。
（2）动态可变长表
优点是节省空间，但是一旦出错恢复起来比较麻烦。
（3）压缩表
上面说到支持数据压缩，说明肯定也支持这个格式。在数据文件发生错误时候，可以使用check table工具来检查，而且还可以使用repair table工具来恢复。
有一个重要的特点那就是不支持事务，但是这也意味着他的存储速度更快，如果你的读写操作允许有错误数据的话，只是追求速度，可以选择这个存储引擎。
2、InnoDB
InnoDB是默认的数据库存储引擎，他的主要特点有：
（1）可以通过自动增长列，方法是auto_increment。
（2）支持事务。默认的事务隔离级别为可重复度，通过MVCC（并发版本控制）来实现的。
（3）使用的锁粒度为行级锁，可以支持更高的并发；
（4）支持外键约束；外键约束其实降低了表的查询速度，但是增加了表之间的耦合度。
（5）配合一些热备工具可以支持在线热备份；
（6）在InnoDB中存在着缓冲管理，通过缓冲池，将索引和数据全部缓存起来，加快查询的速度；
（7）对于InnoDB类型的表，其数据的物理组织形式是聚簇表。所有的数据按照主键来组织。数据和索引放在一块，都位 于B+数的叶子节点上；
当然InnoDB的存储表和索引也有下面两种形式：
（1）使用共享表空间存储：所有的表和索引存放在同一个表空间中。
（2）使用多表空间存储：表结构放在frm文件，数据和索引放在IBD文件中。分区表的话，每个分区对应单独的IBD文件， 分区表的定义可以查看我的其他文章。使用分区表的好处在于提升查询效率。
对于InnoDB来说，最大的特点在于支持事务。但是这是以损失效率来换取的。
3、Memory
将数据存在内存，为了提高数据的访问速度，每一个表实际上和一个磁盘文件关联。文件是frm。
（1）支持的数据类型有限制，比如：不支持TEXT和BLOB类型，对于字符串类型的数据，只支持固定长度的行，VARCHAR会 被自动存储为CHAR类型；
（2）支持的锁粒度为表级锁。所以，在访问量比较大时，表级锁会成为MEMORY存储引擎的瓶颈；
（3）由于数据是存放在内存中，一旦服务器出现故障，数据都会丢失；
（4）查询的时候，如果有用到临时表，而且临时表中有BLOB，TEXT类型的字段，那么这个临时表就会转化为MyISAM类型 的表，性能会急剧降低；
（5）默认使用hash索引。
（6）如果一个内部表很大，会转化为磁盘表。
在这里只是给出3个常见的存储引擎。使用哪一种引擎需要灵活选择，一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求，使用合适的存储引擎，将会提高整个数据库的性能。
-- 事务 transaction
-
什么是事务？
-
一个事务是一个完整的业务单元，不可再分。只有DML语句才支持事务(insert delete update),事务的存在是为了保证数 据的完整性，安全性。
例如：
银行转账，从A账户向B 账户转账10000元，需要执行两条UPDATE语句，这样的两条UODATE语句需要同时执行成功或者同时执行失败，不允许出现一条执行成功，而另一条执行失败的情况，要想保证两条语句同时执行成功或者失败就需要使用事务。
- MYSQL 事务默认情况下是自动提交的。(执行任何一条DML语句都会提交一次)
-
> 关闭自动提交
start transaction；
-
事务的特性？ACID
-
Atomicity 原子性 ：事务是最小的工作单元，不可再分。
-
Consistency 一致性 ：事务必须保证多条DML语句同时执行成功或者失败
-
Isolation 隔离性 ：事务A与事务B 具有隔离
隔离性存在隔离级别的概念，理论上分成四个隔离级别
第一级别：读未提交(read and uncommitted)
对方事务还未提交的数据也可以读到，读未提交数据存在脏读现象(dirty read)，表示读到了不稳定数据第二级别：读已提交(read and committed)
可以读到对方事务提交后的数据，读已提交存在的不可重复读现象第三级别：可重复读(repeatable read)
解决了不可重复读问题，不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。第四级别：序列化读/串行化读
这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁，事务排队。在这个级别，可能导致大量的超时现象和锁竞争。设置隔离级别：
set global transaction isolation level read uncommitted;查看事务的隔离级别
select @@global.transaction_isolation;- Durability 持久性 : 数据最后必须持久化到硬盘文件中，事务才算成功的结束。
-
-- 索引
-
什么是索引由什么用？
-
索引相当于一本书的目录，通过目录可以快速的找到对应的资源。
-
添加索引是给表中的某个字段添加的。主键和unique唯一约束的字段会自动添加索引
-
在数据库中，一般查询一张表的时候有两种检索方式：
全表扫描
根据索引检索(效率高，因为缩小了扫描的范围)
索引虽然可以提交检索效率，但是不能随意添加索引，因为索引也是数据库中的对象，需要进行维护。
比如： 一张表中的某个字段的数据经常被修改，这样的字段就不适合添加索引，因为数据修改后，相应的索引也 需要被修改，进行重新排序。实例
# 根据ENAME找出员工的信息SELECT * FROM EMP WHERE ENAME='SMITH'
当ENAME字段中没有索引的时候，以上SQL语句会进行全表扫描，扫描全部的ENAME值。
当ENAME字段上添加了索引的时候，SQL 根据索引进行扫描，快速定位。
-
-
创建索对象
-
普通的索引的创建：
CREATE INDEX (自定义)索引名 ON 数据表(字段);
-
复合索引的创建：
CREATE INDEX (自定义)索引名 ON 数据表(字段1,字段2 ...);
-
例子 给emp表中的sal字段添加索引
create index emp_sal_index on emp(sal);
-
-
查看执行计划 (看有没有使用索引进行检索)
- 可以使用explain命令加在要分析的sql语句前面，在执行结果中查看key这一列的值，如果为NULL，说明没有使用索引。
例： explain select * from emp where sal>5000;
以下通过explain显示出mysql执行的字段内容：id: SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符.
select_type: SELECT 查询的类型.
table: 查询的是哪个表
partitions: 匹配的分区
type: join 类型
possible_keys: 此次查询中可能选用的索引
key: 此次查询中确切使用到的索引.
ref: 哪个字段或常数与 key 一起被使用
rows: 显示此查询一共扫描了多少行. 这个是一个估计值.
filtered: 表示此查询条件所过滤的数据的百分比
extra: 额外的信息
-
删除索引对象
drop index emp_sal_index on emp;
-
什么时候需要给字段添加索引？
-
数据量庞大
-
需要添加索引的子弹很少的DML操作。
-
该字段经常出现在的WHERE子句中。(经常根据哪个字段查询)
-
主键和unique唯一约束的字段会自动添加索引，所以根据主键查询效率较高.
-
* 索引的实现原理？
- 索引底层采用的数据结构是B*Tree，通过B*Tree缩小了扫描范围，索引对底层数据进行了排序分区。
- 索引会携带数据在表中的物理地址，最终通过索引检索到数据之后，获取到关联的物理地址，通过物理地址定位表中的 数据，效率大幅提升。
例:
SELECT * FROM EMP WHERE ENAME='SMITH'
转化：
SELECT * FROM EMP WHERE 物理地址=0xx3
* 索引的分类
单一索引： 给单个字段添加索引
复合索引： 给多个字段联合起来添加一个索引
复合索引的创建：
CREATE INDEX (自定义)索引名 ON 数据表(字段1,字段2 ...);
主键索引： 主键字段自动添加索引
唯一索引： 有unique约束的字段上会自动添加索引。
* 索引什么时候会失效？
- 模糊查询时以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效。
select * from emp where ename like '%A%' // 索引会失效，因为不知道第一个字母是什么
- or语句前后没有同时使用索引。当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时， 才会生效
- 当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引失效。
- 组合索引，不是使用第一列索引，索引失效。
- 数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描。
- 在索引列上使用 IS NULL 或 IS NOT NULL操作。索引是不索引空值的，所以这样的操作不能使用索引，可以用其他的办 法处理，例如：数字类型，判断大于0，字符串类型设置一个默认值，判断是否等于默认值即可。
- 在索引字段上使用not，<>，!=。不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。
优化方法： key<>0 改为 key>0 or key<0。
- 对索引字段进行计算操作、字段上使用函数。（索引为 emp(ename,empno,sal)）
-- 视图(view)
-
什么是视图？
-
站在不同的角度去看待数据(同一张表的数据站在不同的角度去看待)
-
视图(VIEW)也被称作虚表，即虚拟的表，是一组数据的逻辑表示,其本质是对应于一条SELECT语句，结果集被赋予一个名 字，即视图名字。视图本身并不包含任何数据，它只包含映射到基表的一个查询语句，当基表数据发生变化，视图数据也 随之变化。
-
视图是从一个或者几个基本表（或视图）导出的表。它与基本表不同，是一个虚表。
数据库中只存放视图的定义，而不存放视图对应的数据，这些数据仍存放在原来的基本表中。所以一旦基本表中发生数据变化，从视图中查询出的数据也就随之改变了。从这个意义上讲，视图就是一个窗口，通过视图可以看到数据库中自己想了解的数据变化。 -
数据库中的数据都是存储在表中的，而视图只是一个或多个表依照某个条件组合而成的结果集，
一般来说你可以用update，insert，delete等sql语句修改表中的数据，而对视图只能进行select操作。
但是也存在可更新的视图，对于这类视图的update，insert和delete等操作最终会作用于与其相关的表中数据。
因此，表是数据库中数据存储的基础，而视图只是为了满足某种查询要求而建立的一个对象。 -
表是物理存在的，你可以理解成计算机中的文件！
视图是虚拟的内存表，你可以理解成Windows的快捷方式！
-
-
创建视图
CREATE VIEW 视图名
AS 子查询
WITH CHECK OPTION //可以省略
-
删除视图
DROP VIEW 视图名 [CASCADE] //CASCADE为级联删除，可以省略.
-
对视图进行增删改查会影响到原表数据
-
视图的作用？
-
如果需要经常执行某项复杂查询，可以基于这个复杂查询建立视图，此后查询此视图即可，简化复杂查询
-
视图本质上就是一条SELECT语句，所以当访问视图时，只能访问到所对应的SELECT语句中涉及到的列，隐藏了表的实现细 节，对基表中的其它列 起到安全和保密的作用，可以限制数据访问。
-
-- 常用DBA命令
* MySQL 创建用户
> CREATE USER username IDENTIFIED BY 'password';
create user zhangsan identified by 'zhangsan'
* 查看用户
SELECT USER,NOST FROM USER(该表位于 mysql 库中)
* 授权
- 语法
GRANT privileges ON databasename.tablename TO 'username'@'host'
- 说明:
privileges：用户的操作权限，如SELECT，INSERT，UPDATE等，如果要授予所的权限则使用ALL
databasename：数据库名
tablename：表名，如果要授予该用户对所有数据库和表的相应操作权限则可用*表示，如*.*
ALL PRIVILEGES 是表示所有权限，你也可以使用select、update等权限。
ON 用来指定权限针对哪些库和表。
*.* 中前面的*号用来指定数据库名，后面的*号用来指定表名。
TO 表示将权限赋予某个用户。
- 例子:
GRANT SELECT, INSERT ON test.user TO 'pig'@'%';
GRANT ALL ON *.* TO 'pig'@'%';
GRANT ALL ON maindataplus.* TO 'pig'@'%';
- 注意:
用以上命令授权的用户不能给其它用户授权，如果想让该用户可以授权，用以下命令:
GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;
* 设置与更改用户密码
- 命令:
SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');
如果是当前登陆用户用:
SET PASSWORD = PASSWORD("newpassword");
- 例子:
SET PASSWORD FOR 'pig'@'%' = PASSWORD("123456");
* 撤销用户权限
- 命令:
REVOKE privilege ON databasename.tablename FROM 'username'@'host';
- 说明:
privilege, databasename, tablename：同授权部分
- 例子:
REVOKE SELECT ON *.* FROM 'pig'@'%';
* 删除用户
DROP USER 'username'@'host';
* 授权详细解释
- 语法：
GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;
1. jack@'localhost' 表示jack用户，@后面接限制的主机，可以是IP、IP段、域名以及%，%表示任何地方。注意：这 里%有的版本不包括本地，以前碰到过给某个用户设置了%允许任何地方登录，但是在本地登录不了，这个和版本有 关系，遇到这个问题再加一个localhost的用户就可以了。
IDENTIFIED BY 指定用户的登录密码。
WITH GRANT OPTION 这个选项表示该用户可以将自己拥有的权限授权给别人。注意：经常有人在创建操作用户的时候不指定WITH GRANT OPTION选项导致后来该用户不能使用GRANT命令创建用户或者给其它用户授权。
备注：可以使用GRANT重复给用户添加权限，权限叠加，比如你先给用户添加一个select权限，然后又给用户添加一个insert权限，那么该用户就同时拥有了select和insert权限。
2、刷新权限
flush privileges;每次修改都需要重新刷新权限
3、查看权限
查看当前用户的权限：
mysql> show grants;
查看某个用户的权限：
mysql> show grants for 'jack'@'%';
4、回收权限
revoke delete on *.* from 'jack'@'localhost';
5、删除用户
首先查看
select host,user,password from user;
删除
drop user 'jack'@'localhost';
6、对账户重命名
rename user 'jack'@'%' to 'jim'@'%';
示例：
设置普通dba管理某个数据的权限
Grant ALL privileges on testdb to usernameDBA@’localhost’
Localhost是限制本地
Grant ALL on testdb to userNameDBA@’%’
ip没有限制
设置能查询mysql服务器上所有数据库的表
Grant select on *.* to userName@’%’
username用户不管在哪登陆都能操作mysql所有的数据库和表
Grant ALL on *.* to username@’%’
Username用户可以管理mysql服务器中所有的数据库
grant 作用在单个数据库上：
grant select on testdb.* to username@localhost;
grant 作用在单个数据表上：
Grant select on testdb.tablename to username@’%’
Grant作用在表的列上
Grant select(id,sex,rank) on testdb.tableName to username@’%’
查看用户权限
Show grants for username
赋予权限
Grant select on databaseName.* to username
Grant select, update, delete, insert on databaseName.* to username
回收权限
Revoke select, delete on databaseName.* from username
每次权限的修改都需要刷新权限
Flush privileges；
设置整个数据库的权限
Grant ALL on databaseName.* to username和 revoke ALL
Grant ALL on databaseName.table to username
授权给普通用户查询，插入，更新，删除对数据库所有表的权利
%标识这个用户ip没有限制
grant select, insert, update, delete on testdb.* to common_user@’%’
授权给开发者用户在testdb数据库创建表的权限，
限制ip只能是192.168.0开头的用户
grant create on testdb.* to developer@’192.168.0.%’;
设置索引的权限 设置用户在数据库上创建索引的权限
限制ip只能是192.168.0开头的用户
grant index on testdb.* to userName@’192.168.0.%’;
设置存储过程的权限
grant create routine on testdb.* to username@’192.168.0.%’;
grant alter routine on testdb.* to developer@’192.168.0.%’;
-
导出和导入
- 导出：
在Windows的DOS窗口中执行：
导出整个库
mysqldump 数据库名>c:\database\backup\emp.sql -uroot -p123456导出库下的某张表
mysqldump 数据库名 表名>c:\database\backup\emp.sql -uroot -p123456- 导入：
mysql -u root -p 登录数据库
mysql> use 数据库 选库
然后使用source命令，后面参数为脚本文件(如这里用到的.sql)
mysql> source d:/dbname.sql
-- 数据库设计三范式
* 什么是设计范式？
- 设计范式是设计表的依据，按照三范式设计的表不会出现数据冗余，规范的数据库是需要满足一些规范的来优化数据数据存储方式的，在关系型数据库中这些规范就可以称为范式。
* 三范式都有哪些？
- 第一范式 1NF
任何一张表都应该有主键，并且表中的每一个字段原子性不可再分。数据库表的每一列/字段都是不可再分割的原子数据项，而不能是集合，数组，记录等非原子数据项。如果实体中的某个属性有多个值时，必须拆分为不同的字段。
- 第二范式 2NF
第二范式建立在第一范式的基础之上，且所有非主键字段完全依赖主键，不能产生部分依赖。当存在多个主键的时候，才会发生不符合第二范式的情况。比如有两个主键，不能只依赖于其中一个主键。
# 解决方法： 多对多的情况时，设计三张表，两个外键。比如老师和学生是多对多的关系，设计一个学生表，一个老师表，一个关系表，尽量不要使用联合主键，容易出现数据冗余。
关系表
id sno tno
-----------------------
------------------------
- 第三范式
# 第三范式建立在第二范式的基础之上，所有非主键字段直接依赖主键字段，不能产生传递依赖。
# 如 ：字段A 依赖于某个非主键字段，而这个非主键字段又依赖于主键字段，那么字段A就是间接依赖于主键，这被称作传递依赖于主键字段。
# 一对多关系，设计两张表，多的表加外键。
- 注意：在实际开发中，以满足客户需求为主，有时候需要拿冗余换速度(多表连接查询效率低于单表查询)
-- 表的设计
* 一对一关系的表的设计方案
例如:
t_user_login 用户登录信息表
lid pk username password
1 张三 123456
2 李四 123456
t_user_detail 用户详细信息表
uid pk+fk(方案一) real_name tel addr 加 lid(方案二)fk+unique
1 张三丰 13722635478 北京 1
2 李四华 13261552584 天津 2
-
方案一： 主键共享 id字段 即使两个表的主键，也是 t_user_detail表的外键
-
方案二： 外键唯一
-- 练习题
- 取得每个部门最高薪水的人员名称
SELECT DEPTNO,ENAME
FROM EMP
WHERE SAL IN (
SELECT MAX(SAL)
FROM EMP
GROUP BY DEPTNO )
-
哪些人的薪水在部门的平均工资之上
SELECT E.DEPTNO,E.ENAME,E.SAL
FROM EMP E ,(SELECT DEPTNO,ROUND(AVG(SAL) ,2) SAL FROM EMP GROUP BY DEPTNO )TEMP
WHERE E.DEPTNO=TEMP.DEPTNO AND E.SAL >TEMP.SAL
2.
SELECT E.DEPTNO,E.ENAME,E.SAL
FROM EMP E
JOIN
(SELECT DEPTNO,ROUND(AVG(SAL) ,2) SAL FROM EMP GROUP BY DEPTNO )TEMP
ON E.DEPTNO=TEMP.DEPTNO AND E.SAL >TEMP.SAL
-
求每个部门工资等级的平均值
SELECT E.DEPTNO ,AVG(S.GRADE)AGRADEFROM EMP E
JOIN SALGRADE S
ON E.SAL BETWEEN S.LOSAL AND S.HISAL
GROUP BY E.DEPTNO
-
不用分组函数MAX，取到最高薪水
-
使用limit
SELECT ENAME ,SAL
FROM EMP
ORDER BY SAL DESC
LIMIT 1
-
使用自连接
SELECT ENAME , SAL
FROM EMP
WHERE SAL NOT IN (
SELECT A.SAL
FROM EMP A
JOIN EMP B
WHERE A.SAL<B.SAL
)
-
-
-
取得平均薪水最高的部门编号
SELECT E.DEPTNO , MAX(T.AVGSAL) MAXSAL
FROM EMP E
JOIN
(SELECT DEPTNO , AVG(SAL) AVGSAL FROM EMP GROUP BY DEPTNO )T
ON E.DEPTNO=T.DEPTNO
-
取得薪水最高的部门名称
SELECT D.DNAME , MAX(T.AVGSAL) MAXSAL FROM DEPT DJOIN
(SELECT DEPTNO , AVG(SAL) AVGSAL FROM EMP GROUP BY DEPTNO )T
ON D.DEPTNO=T.DEPTNO
-
求每个部平均薪水的等级
SELECT TEMP.DNAMES,TEMP.AVGSAL,S.GRADEFROM SALGRADE S
JOIN
(SELECT D.DNAME DNAMES, AVG(E.SAL) AVGSAL FROM EMP E JOIN DEPT D ON E.DEPTNO=D.DEPTNO GROUP BY D.DNAME )TEMP
ON TEMP.AVGSAL BETWEEN S.LOSAL AND S.HISAL
-
找出比普通员工最高薪资还要高的领导姓名
SELECT ENAME
FROM EMP
WHERE SAL >(SELECT MAX(SAL) FROM EMP WHERE JOB <>'MANAGER') AND JOB ='MANAGER'
