mysql基础

#SQL语句的分类：

1、DQL(数据查询语言):所有的SELECT语句都是数据查询语句

2、DDL(数据定义语言):CREATE DROP ALTER等，对数据库、表(结构)进行增删改操作

3、DML(数据操作语言):INSERT DELETE UPDATA等对表中数据进行操作的语句

4、TCL(事物控制语言):COMMIT提交事务 BACKROLL回滚事务

5、DCL(数据控制语言):GRANT某用户赋予权限 REVOKE撤销某用户权限等对权限进行操作的语句

#MYSQL常用命令（不是标准sql语句，是mysql命令）

1、登录mysql数据库管理系统
mysql -uroot -p123456;

2、查看所有数据库
show databases;

3、创建数据库
create database student；
CREATE DATABASE IF NOT EXISTS dbname DEFAULT CHARSET utf8;

4、删除数据库
drop database student;

5、使用指定的数据库
use student；

6、查看当前数据库中有哪些表
show tables；

7、执行sql脚本（初始化数据）
source D:\data\student.sql(脚本所在路径)

8、查看表结构
desc 表名；

9、结束一条语句 \c

10、退出mysql exit

11、查看某个表的创建语句
show create table 表名；

#简单的查询语句（DQL）

1、语法：
select 字段名1，字段名2，......,字段名n from 表名；

重点注意：
-任何一条sql语句以；结尾
-sql语句不区分大小写

2、查询字段中可以进行数学运算
select name1，name2*12 AS '别名' from table名；
注意：-别名或字段是中文时用单引号（‘’）包起来，所有的字符串都用单引号包起来
-mysql数据库支持 “” ，但不建议使用，因为别的数据库不支持

3、select * from table；不建议使用（ * 效率很低）

#简单的查询语句之（条件查询）

1、语法：
select 字段1，字段.... from table名 where 条件；

2、条件中可使用的条件运算 > < >= <= != <> between...and... is null and or in not like
注意: //between...and...是闭区间，左边的数要小于右边的数，还可以用于字符，用于字符时是左闭右开区间（不常用）
//and和or同时存在时，and优先级高，也就是说and会自动把它两边的式子联合起来查询（开发时，如果不确定，就用小括号，小括号的优先级最高）
//like模糊查询时注意 % 和 _ ,%代表任意多个字符，_代表任意一个字符，需要表示%或者_时用转义字符\% \_
例如： eg1: 找出所有姓张的人的信息
select * from table where name like '张%';

eg2: 找出名字中包含’国’字的人的信息
select * from table where name like '%国%';

3、对查询出来的数据进行（排序）(order by)
eg1: 查找学生的姓名，班级，年龄，并按年龄升序排列
select name , class , age from student order by age; //不写默认升序
select name , class , age from student order by age asc; //升序
select name , class , age from student order by age desc;//降序

eg2：查找学生姓名，年龄，班级，并按年龄升序排列，年龄相同情况下按名字的降序排列
select name , class , age from student order by age asc , name desc ;
（前面的）起主导作用 -->越靠前的字段起的主导作用越大
注意：order by后面可以写阿拉伯数字，表示按第几列排序（不建议使用，一旦列的顺序改变，sql语句就无法使用）
语句的执行顺序： from -> where -> select -> order by

4、分组函数(也叫多行处理函数)（对某一组数据进行操作）
count() 数据条数
max() 最大值
min() 最小值
avg() 平均值
sum() 求和
*分组函数计算时自动忽略null值
*count(*) 统计查询出来的数据记录的总条数，和某个字段无关
*count(某字段) 统计查询出来的数据记录中该字段不为null的记录条数

eg1: 求学生成绩的平均值
select avg(grade) from table;

eg2: 查询出工资高于平均工资的员工
select name , salary from 工资表 where salary > avg(salary);***错误的***

5、单行处理函数（输入一行，输出一行）
eg：计算每一位员工的年薪
select name, (salary+补贴)*12 from 工资表；
问题：如果员工的补贴为NULL，则(salary+补贴)*12的计算结果为NULL
(sql语言规定，再表达式计算过程中，如果有NULL参与运算，则整个的值为NULL)
解决：引入ifnull()空处理函数，语法： ifnull(可能为null的数据，拿什么来代替); -->属于单行处理函数
select name, (salary+ifnull(补贴),0)*12 from 工资表；

#字符串函数
1、length(str) 字符串的字节长度（一个汉字占三个字节）
2、char_length(str) 字符串的长度
3、concat(str1,str2,str3....) 将str1,str2....合并为一个字符串
4、caoncat_ws(X , str1,str2,str3....) 和concat函数一样，不过会在每个字符串之间添加一个X作为分隔符
5、insert(s1 , X , len , s2) 字符串s2替换s1的X位置开始的长度为len的字符串
6、lower(s) 将s字符串中的所有字符变为小写字母
7、upper(s) 将s字符串中的所有字符变为大写字母
8、left(s , n) 截取字符串s的前（左边）n个字符
9、right(s , n) 截取字符串s的后（右边）n个字符
10、trim([leading/trailing/both] from s) 去掉s字符串前面/后面/两头的空格（默认去掉两头的空格）
11、rtrim(s) ltrim(s) 去掉s字符串右边/左边的空格
12、replace(s , s1 ,s2) 将字符串s中的字符串s1用s2替换
13、substring(s , start , length) 从字符串s的start（下表从1开始）位置截取长度为length的子字符串
14、substr(s , start , length) 从字符串s的start（下表从1开始）位置截取长度为length的子字符串（如果开始的位置是负数，则从后面数）
15、reverse(s) 将字符串s顺序反过来
16、position(substr in str) 返回子串substr在字符串str中第一次出现的位置，若不存在则返回0
17、locate(substr , str ， pos) 返回字串substr在字符串str中pos位置后第一次出现的位置，不存在返回0

#日期时间相关函数
1. CURDATE() CURRENT_DATE() 返回当前日期
2. CURTIME(),CURRENT_TIME() 返回当前时间
3. NOW() SYSDATE() 返回当前日期和时间
4. MONTH(d) 返回日期d中的月份值，1 到 12
5. MONTHNAME(d) 返回日期当中的月份名称，如 November
6. DAY(d) 返回日期值 d 的日期部分
7. DAYNAME(d) 返回日期 d 是星期几，如 Monday,Tuesday
8. DAYOFMONTH(d) 计算日期 d 是本月的第几天
9. DAYOFWEEK(d) 日期 d 今天是星期几，1 星期日，2 星期一，以此类推
10.DAYOFYEAR(d) 计算日期 d 是本年的第几天
11.HOUR(t) 返回 t 中的小时值
12.LAST_DAY(d) 返回给给定日期的那一月份的最后一天
13.MINUTE(t) 返回 t 中的分钟值
14.QUARTER(d) 返回日期d是第几季节，返回 1 到 4
15.SECOND(t) 返回 t 中的秒钟值
16.WEEK(d) 计算日期 d 是本年的第几个星期，范围是 0 到 52
17.WEEKOFYEAR(d) 计算日期 d 是本年的第几个星期，范围是 1 到 53
18.WEEKDAY(d) 日期 d 是星期几，0 表示星期一，1 表示星期二
19.ADDDATE(d ，INTERVAL n unit);
20.DATE_ADD(d INTERVAL n unit) 在指定日期基础上加上n个unit
21.DATEDIFF(d1,d2) 计算日期 d1->d2 之间相隔的天数
22.TIMEDIFF(time1, time2) 计算时间差值，前者减去后者
23.TIME(expression) 提取传入表达式的时间部分
24.DATE(expression) 提取传入表达式的日期部分
25.DATE_SUB(date,INTERVAL expr type)
26.SUBDATE(d,n) 函数从日期减去指定的时间间隔

#数字函数
1.ABS(x) 返回 x 的绝对值
2.MOD(x,y) 求余数
3.SQRT(x) 返回x的平方根
4.CEIL(x)和CEILING(x) 返回大于或等于 x 的最小整数
5.FLOOR(x) 返回小于或等于 x 的最大整数
6.RAND() 返回 0 到 1 的随机数包括0 不包括1
7.ROUND(x) ROUND(X,D) 返回离 x 最近的整数
8.ROUND(X,D) 保留D位小数
9.SIGN(x) 返回 x 的符号，x 是负数、0、正数分别返回 -1、0 和 1
10.TRUNCATE(x,y) 返回数值 x 保留到小数点后 y 位的值（与 ROUND 最大的区别是不会进行四舍五入）
11.POW(x,y) POWER(x,y) 返回 x 的 y 次方

#多表联查
1、内连接查询
*隐式内连接
1.1、如果我们要查询的数据来自有关联的两张表，可使用隐式内连接
1.2、语法:

select 列名...(若两张表中有相同的列名，要用点语法指明是哪个表的列明)
from 要取数据的多个表名
where 通用列的匹配（table1.列名 = table2.列名）；

*显示内连接
1、语法：

select 列名...(若两张表中有相同的列名，要用点语法指明是哪个表的列明)
from table1
[inner] join table2
on 通用列的匹配（table1.列名 = table2.列名）；

2、外连接查询（拿着主表中的所有数据去匹配另外一张表的数据，匹配不到返回null）

2.1、左外连接：table1中的所有数据都会被查出来，顺带把table2的主句查出来，如果匹配不到返回null
SELECT 列名...(若两张表中有相同的列名，要用点语法指明是哪个表的列明)
FROM table1(主表)
LEFT [outer] JOIN table2
ON 通用列的匹配（table1.列名 = table2.列名）；

2.2、右外连接：table2中的所有数据都会被查出来，顺带把table1的主句查出来，如果匹配不到返回null
SELECT 列名...(若两张表中有相同的列名，要用点语法指明是哪个表的列明)
FROM table1
RIGHT [outer] JOIN table2(主表)
ON 通用列的匹配（table1.列名 = table2.列名）；

2.3、三张及以三张以上的表关联查询
1、语法：
select 列名
from table1
inner join table2
on table1和table2的连接条件（table1.列名 = table2.列名)
inner join table3
on table1和table3的连接条件（table1.列名 = table3.列名)
....
left [outer] join tablen
on table1和tablen的连接条件（table1.列名 = tablen.列名);

**内外链接可以混合使用
**左右外连接可以相互转换
**外连接的查询结果条数一定大于等于内连接的结果条数

#子查询（在一个子查询中嵌套其他的查询，称为子查询）
查询1的结果，作为查询2的条件，那么就称查询1是查询2的子查询
情况1：子查询的结果是单行单列的
情况2：子查询的结果是多行单列的
情况3：子查询的结果是多行多列的（临时表）

**在where子句中的子查询
例子：查询工资比最低工资要高的员工
select ename as '员工姓名' , salary as '工资' from emp where salary > (select min(salary) from emp);

**在from子句中的子查询（可以将子查询查询出来的结果当成一张临时表）
例子：找出每个岗位的平均工资的薪资等级
select jobName , salaryGrade
from salary , (select avg(salary) from emp group by job) as temp
where temp.avg(salary) beteen salary.low and salary.high;

**在select子句中的子查询
例子：找出每个员工的部门名称，要求显示员工名，部门名
select
e.name,e.deptno,(select d.name from dept d where e.deptno=d.deptno) as dname
from emp , e;

！！！！！注意：对于select后面的子查询来说，这个子查询只能一次返回一条结果，多于一条就会报错、

#union的用法（合并查询结果集）
例子：查询工作名称是manager或saleman的员工
第一种方法：
select ename , job where job='manager' or job='saleman';
第二种方法：
select ename , job where job in('manager','saleman');
第三种方法：
select ename , job where job='manager';
union
select ename , job where job='saleman';

!!!第三种方法（使用union）效率最高，当有大量表的连接时，前两种方法效率会非常低
!!!union在进行合并时，要求两个结果集的列数要相同
!!!union在合并时，mysql列的数据类型可以不一样，但Oracle要求数据类型也要一样

#分页查询
## limit的使用
-- limit startIndex , length
-- startIndex是起始下标(从0开始) length是查出数据的长度
-- 完整写法：
select * from table order by number limit 2,2; //从第二条数据开始，取出两条数据来（取出第3、4条数据）
select * from table order by number limit 2; //取出前2条数据来

##分页
pageNum 页码
pagesize 每页显示的记录的条数

limit (pageNum -1) * pagesize , pagesize

## 完整查询语句
select 字段列表
from 表名
[where 查询条件]
[group by 进行分组的字段]
[having 对分组之后的数据的过滤条件]
[order by 升序或降序]
[limit 数据条数限制];

执行顺序 from -> where -> group by -> having -> select -> order by -> limit

#数据的增删改
1、增加数据
insert into table 列名... values(对应的值列表【要和前面一一对应】); //向表中的一些列添加数据
insert into table values(表中每一列对应的值列表); //向表中的每一列添加数据

insert into table values(表中每一列对应的值列表),(表中每一列对应的值列表),(表中每一列对应的值列表),.....; //一次性添加多列用,隔开

2、删除数据
delete from table where 条件； //条件一定要加，否则就是删除整个表的数据,但数据可以从日志文件中恢复（删除的效率较低）
truncate table 表名； //!!数据不可恢复!!，原理是将整个表删除再建立一个同样结构的表出来 ( 删除的效率高）

3、数据的更新
update 表名 set 列名1=修改后的值,列名2=修改后的值,列名3=修改后的值 where 条件

**跨行跨表更新数据
UPDATE JOIN语法：

UPDATE T1, T2,　　　　　　　　　　　　　　　　　　　　　　　　　　　　
[INNER JOIN | LEFT JOIN] T1 ON T1.C1 = T2. C1　　　　　　　　　
SET T1.C2 = T2.C2,
T2.C3 = expr
WHERE condition

第一，在UPDATE子句之后，指定主表(T1)和希望主表连接表(T2)。

第二，指定一种要使用的连接，即INNER JOIN或LEFT JOIN和连接条件。JOIN子句必须出现在UPDATE子句之后。

第三，要为要更新的T1和/或T2表中的列分配新值。

第四，WHERE语句中的条件用于指定要更新的行。

#利用外键关系实现级联更新和级联删除

CREATE TABLE stuclass(

id INT PRIMARY KEY,
classname VARCHAR(10)
);

CREATE TABLE stu(

id INT PRIMARY KEY,
username VARCHAR(10),
pass VARCHAR(20),
stuclass INT,
CONSTRAINT fk_1 FOREIGN KEY (stuclass)
REFERENCES stuclass(id)
ON DELETE CASCADE //实现级联删除
ON UPDATE CASCADE //实现级联更新
);

建了表之后只要一般的更新删除操作就可以实现级联更新和级联删除了

#数据库的设计（六大范式）
ER图：（实体关系图）
矩形框：实体
椭圆：属性
直线：实体与属性关系
菱形：实体与实体之间的关系

第一范式（1NF）：要求每一张表必须有主键，每一列都是不可分割的原子数据项

第二范式（2NF）：在第一范式的基础上，保证一张表只描述一件事情，即要求所有非主键字段完全依赖主键，不能产生部分依赖

第三范式（3NN）：在满足第二范式的基础上，要求所有非主键字段必须直接依赖主键，不能产生传递依赖

一对多：一对多，两张表，多的表加外键
多对多：多对多，三张表，关系表两个外键
一对一：按理来说，一对一一个表就可以，但在实际开发中，一张表可能数据量太大，要进行拆表分表，(可以用主键共享的方式保障数据的对应<用的少>)

**在实际开发中，为了满足客户的需求，有时候会拿冗余去换速度（因为在数据库中，表和表的连接次数越多，执行效率越低）

##约束概述

1、非空约束 not null
create table tablename(
name varchar(20) not null,
age int not null

);

2、唯一性约束 unique

create table tablename(
name varchar(20) unique,
age int not null unique //not null 和 unique字段可以联合，联合以后该字段自动变成主键字段（和Oracle不一样）

);

***联合唯一

create table tablename(
name varchar(20) ,
age int，
unique（naem , age） //联合唯一

);

*写在字段后面的是列级约束
*单独写在下面的是表级约束

3、主键约束 primary key

*什么是主键：主键是每一行记录的唯一标识，相当于人的身份证，一张表只能有一个主键
create table tablename(
name varchar(20) primary key , //使用列级约束
age int，
);

create table tablename(
name varchar(20),
age int，
primary key(name)；//使用表级约束
);

**复合主键
create table tablename(
id int,
name varchar(20),
age int，
primary key(id , name)；//使用表级约束复合主键
);
！！！！！！在实际开发中不建议使用复合主键，建议使用单一主键！！！！！！
！！！！！！在实际开发中，主键的类型建议用 int , bigint , char等类型，不建议使用varchar类型的

主键分自然主键(普通的1，2，类似这样) 和业务主键(和业务相关，比如银行卡号)
！！！在实际开发中，自然主键用的多，不建议使用业务主键，因为一旦业务发生改变，将会出现问题

4、外键约束 foreign key
create table tablename(
id int,
name varchar(20),
calss int，
foreign key(子表的字段) references 父表名(父表的字段)
);

建完表之后再修改
ALTER TABLE 子表名 ADD CONSTRAINT 外键名
FOREIGN KEY(子表的字段) REFERENCES 父表名(父表的字段);

！！！子表引用父表的某一个字段时，父表的那个字段不一定是主键，但至少要有unique约束
！！！外键值可以为null

#存储引擎

1、什么是存储引擎，有什么用呢？
存储引擎是MySQL中特有的一个术语，其它数据库中没有。（Oracle中有，但是不叫这个名字）
实际上存储引擎是一个表存储/组织数据的方式。
不同的存储引擎，表存储数据的方式不同。

2、怎么给表添加/指定“存储引擎”呢？
--查看某张表的存储引擎命令
show create table t_student;

--可以在建表的时候给表指定存储引擎。
CREATE TABLE `t_student` (
`no` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`cno` int(11) DEFAULT NULL,
PRIMARY KEY (`no`),
KEY `cno` (`cno`),
CONSTRAINT `t_student_ibfk_1` FOREIGN KEY (`cno`) REFERENCES `t_class` (`classno`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8

3、查看mysql支持哪些存储引擎
show engines \G

4、mysql常用的存储引擎

MyISAM存储引擎？
它管理的表具有以下特征：
使用三个文件表示每个表：
格式文件 — 存储表结构的定义（mytable.frm）
数据文件 — 存储表行的内容（mytable.MYD）
索引文件 — 存储表上索引（mytable.MYI）：索引是一本书的目录，缩小扫描范围，提高查询效率的一种机制。
可被转换为压缩、只读表来节省空间

提示一下：
对于一张表来说，只要是主键，
或者加有unique约束的字段上会自动创建索引。

MyISAM存储引擎特点：
可被转换为压缩、只读表来节省空间
这是这种存储引擎的优势！！！！

MyISAM不支持事务机制，安全性低。

InnoDB存储引擎？
这是mysql默认的存储引擎，同时也是一个重量级的存储引擎。
InnoDB支持事务，支持数据库崩溃后自动恢复机制。
InnoDB存储引擎最主要的特点是：非常安全。

它管理的表具有下列主要特征：
– 每个 InnoDB 表在数据库目录中以.frm 格式文件表示
– InnoDB 表空间 tablespace 被用于存储表的内容（表空间是一个逻辑名称。表空间存储数据+索引。）
– 提供一组用来记录事务性活动的日志文件
– 用 COMMIT(提交)、SAVEPOINT 及ROLLBACK(回滚)支持事务处理
– 提供全 ACID 兼容
– 在 MySQL 服务器崩溃后提供自动恢复
– 多版本（MVCC）和行级锁定
– 支持外键及引用的完整性，包括级联删除和更新

InnoDB最大的特点就是支持事务：
以保证数据的安全。效率不是很高，并且也不能压缩，不能转换为只读，
不能很好的节省存储空间。

MEMORY存储引擎？
使用 MEMORY 存储引擎的表，其数据存储在内存中，且行的长度固定，
这两个特点使得 MEMORY 存储引擎非常快。

MEMORY 存储引擎管理的表具有下列特征：
– 在数据库目录内，每个表均以.frm 格式的文件表示。
– 表数据及索引被存储在内存中。（目的就是快，查询快！）
– 表级锁机制。
– 不能包含 TEXT 或 BLOB 字段。

MEMORY 存储引擎以前被称为HEAP 引擎。

MEMORY引擎优点：查询效率是最高的。不需要和硬盘交互。
MEMORY引擎缺点：不安全，关机之后数据消失。因为数据和索引都是在内存当中。

#事务

1、概念：事务就是一个完整的业务逻辑，一个最小的工作单元，不可再分
例如：转账操作：A给B转账10000，
将A账户钱减少10000元，
将B账户钱增加10000元，
这个操作是一个最小的操作单元，要么同时成功，要么同时失败，才能保证业务正常

2、只有DML语句才有事务的概念（insert , delete , update）

--因为一些业务往往需要多个DML语句共同完成，所以才有事务存在的必要
--本质上，一个事物就是多条DML语句同时成功或者同时失败

3、事务如何做到同时成功或者同时失败？
--InnoDM存储引擎，提供一组用来记录事务活动的日志文件
事务开启：
update
delete
insert
delete
insert
事务结束：

--在事务执行的过程中，每一条DML语句操作都会记录到事务性活动的日志文件中
在事务的执行过程中，可以提交事务，也可以回滚事务
--提交事务：清空事务性活动的文件，将数据全部彻底持久化的提交到数据表中，提交事务标志这事务的结束，并且是一种成功的结束
--回滚事务：将之前所有的DML语句全部撤销，并且清空事务性活动的文件，回滚事务标志着事务的结束，并且是一种全部失败的结束

--提交事务： commit
--回滚事务： rollback

--mysql在默认情况下，是自动提交事务的（每执行一次，自动提交一次）
--取消自动提交语句： start transaction;

4、事务的4个特性
A：原子性
C：一致性
I：隔离性
D：持久性

5、重点研究一下事务的隔离性！！！

A教室和B教室中间有一道墙，这道墙可以很厚，也可以很薄。这就是事务的<隔离级别>。
这道墙越厚，表示隔离级别就越高。

事务和事务之间的隔离级别有哪些呢？4个级别

1、读未提交：read uncommitted（最低的隔离级别）《没有提交就读到了》
--事务A可以读取到事务B未提交的数据。
--这种隔离级别存在的问题就是：脏读现象！(Dirty Read)
--这种隔离级别一般都是理论上的，大多数的数据库隔离级别都是二档起步！

2、读已提交：read committed《提交之后才能读到》

--事务A只能读取到事务B提交之后的数据。
--这种隔离级别解决了脏读的现象
--这种隔离级别存在不可重复读取数据的问题
--不可重复读：在事务开启之后，第一次读到的数据是3条，当前事务还没有结束，可能第二次再读取的时候，读到的数据是4条
--这种隔离级别是比较真实的数据，每一次读到的数据是绝对的真实
--oracle数据库默认的隔离级别是：read committed

3、可重复读：repeatable read《提交之后也读不到，永远读取的都是刚开启事务时的数据》
--什么是可重复读取？
事务A开启之后，不管多久，每次在事务A中读取到的数据都是一致的。即使事务B将数据已经修改，并且提交了，事务A读取到的数据还是没有发生改变，这就是可重复读。
--可重复读解决了不可重复读取数据
--可重复读存在的问题：可能会出现幻影读（每一次读取到的数据都是幻象。不够真实）
--早晨9点开始开启了事务，只要当前事务不结束，到晚上9点，读到的数据还是那样！
--mysql中默认的事务隔离级别就是这个

4、序列化/串行化：serializable（最高的隔离级别）
--这是最高隔离级别，效率最低。解决了所有的问题。
--这种隔离级别表示事务排队，不能并发！
--有点像Java中synchronized，线程同步（事务同步）
--每一次读取到的数据都是最真实的，并且效率是最低的。

6、验证各种隔离级别

查看隔离级别：SELECT @@tx_isolation
设置全局的事务隔离级别：set global transaction isolation level [read uncommitted]/[read commited]/[repeatable read]/[serializable];

被测试的表t_user
1、验证：read uncommited
mysql> set global transaction isolation level read uncommitted; //设置全局的事务隔离级别
事务A 事务B
--------------------------------------------------------------------------------
use bjpowernode;
use bjpowernode;
start transaction;
select * from t_user;
start transaction;
insert into t_user values('zhangsan');
select * from t_user;

2、验证：read commited
mysql> set global transaction isolation level read committed;
事务A 事务B
--------------------------------------------------------------------------------
use bjpowernode;
use bjpowernode;
start transaction;
start transaction;
select * from t_user;
insert into t_user values('zhangsan');
select * from t_user;
commit;
select * from t_user;

3、验证：repeatable read
mysql> set global transaction isolation level repeatable read;
事务A 事务B
--------------------------------------------------------------------------------
use bjpowernode;
use bjpowernode;
start transaction;
start transaction;
select * from t_user;
insert into t_user values('lisi');
insert into t_user values('wangwu');
commit;
select * from t_user;

4、验证：serializable
mysql> set global transaction isolation level serializable;
事务A 事务B
--------------------------------------------------------------------------------
use bjpowernode;
use bjpowernode;
start transaction;
start transaction;
select * from t_user;
insert into t_user values('abc');

##索引的概述
1、索引是在数据库表的字段上添加的，是为了提高查询效率存在的一种机制。
一张表的一个字段可以添加一个索引，当然，多个字段联合起来也可以添加索引
对于一本字典来说，查找某个汉字有两种方式：
第一种方式：一页一页挨着找，直到找到为止，这种查找方式属于全字典扫描。
效率比较低。

第二种方式：先通过目录（索引）去定位一个大概的位置，然后直接定位到这个
位置，做局域性扫描，缩小扫描的范围，快速的查找。这种查找方式属于通过
索引检索，效率较高。

2、select * from t_user where name = 'jack';
--以上的这条SQL语句会去name字段上扫描，因为查询条件是：name='jack'
--如果name字段上没有添加索引（目录），或者说没有给name字段创建索引，
MySQL会进行全扫描，会将name字段上的每一个值都比对一遍。效率比较低。

3、MySQL在查询方面主要就是两种方式：
第一种方式：全表扫描
第二种方式：根据索引检索。

4、在mysql数据库当中索引也是需要排序的，并且这个索引的排序和TreeSet数据结构相同。TreeSet（TreeMap）底层是一个自平衡的二叉树！
在mysql当中索引是一个B-Tree数据结构。
遵循左小右大原则存放。采用中序遍历方式遍历取数据。

索引的实现原理？
假设有一张用户表：t_user

id(PK) name 每一行记录在硬盘上都有物理存储编号
----------------------------------------------------------------------------------
100 zhangsan 0x1111
120 lisi 0x2222
99 wangwu 0x8888
88 zhaoliu 0x9999
101 jack 0x6666
55 lucy 0x5555
130 tom 0x7777

注意：！！在任何数据库当中主键上都会自动添加索引对象，id字段上自动有索引，
！！因为id是PK。另外在mysql当中，一个字段上如果有unique约束的话，也会自动创建索引对象。
！！在任何数据库当中，任何一张表的任何一条记录在硬盘存储上都有一个硬盘的物理存储编号。
！！在mysql当中，索引是一个单独的对象，不同的存储引擎以不同的形式存在：
--在MyISAM存储引擎中，索引存储在一个.MYI文件中
--在InnoDB存储引擎中索引存储在一个逻辑名称叫做tablespace的当中
--在MEMORY存储引擎当中索引被存储在内存当中
--不管索引存储在哪里，索引在mysql当中都是一个树的形式存在。（自平衡二叉树：B-Tree）

5、什么条件下，我们会考虑给字段添加索引呢？
条件1：数据量庞大（因为每一个硬件环境不同，数据多大算庞大要根据具体具体的环境测试）
条件2：该字段经常出现在where的后面，以条件的形式存在，也就是说这个字段总是被扫描
条件3：该字段很少的DML(insert delete update)操作。（因为DML之后，索引需要重新排序）

--建议不要随意添加索引，因为索引也是需要维护的，太多的话反而会降低系统的性能。
--建议通过主键查询，建议通过unique约束的字段进行查询，效率是比较高的。

5.1、索引怎么创建？怎么删除？语法是什么？

创建索引语法：
create index 索引名(自定义) on 表名(字段名);

删除索引语法：
drop index 索引名 on 表名;

6、索引有失效的时候，什么时候索引失效呢？

失效的第1种情况：
select * from emp where ename like '%T';
--模糊匹配当中以“%”开头了
--尽量避免模糊查询的时候以“%”开始
--这是一种优化的手段/策略

失效的第2种情况：
--使用or的时候会失效，如果使用or那么要求or两边的条件字段都要有索引，才会走索引
--如果其中一边有一个字段没有索引，那么另一个字段上的索引也会失效
--所以这就是为什么不建议使用or的原因

失效的第3种情况：
使用复合索引的时候，没有使用左侧的列查找，索引失效
什么是复合索引？
两个字段，或者更多的字段联合起来添加一个索引，叫做复合索引。

create index emp_job_sal_index on emp(job,sal);
使用job查询会走索引，而使用sal查询不会走索引

失效的第4种情况：
在where当中索引列参加了运算，索引失效。
create index emp_sal_index on emp(sal);

explain select * from emp where sal = 800; //不会失效
explain select * from emp where sal+1 = 800; //会失效

失效的第5种情况：
在where当中索引列使用了函数
explain select * from emp where lower(ename) = 'smith';

失效的第6种情况：使用了强制类型转换时会失效
...

7、索引是各种数据库进行优化的重要手段。优化的时候优先考虑的因素就是索引。
索引在数据库当中分了很多类？
单一索引：一个字段上添加索引
复合索引：两个字段或者更多的字段上添加索引
主键索引：主键上添加索引。
唯一性索引：具有unique约束的字段上添加索引
.....
注意：!唯一性比较弱的字段上添加索引用处不大

#视图（view）

2、视图(view)

2.1、什么是视图？
view:站在不同的角度去看待同一份数据。

2.2、怎么创建视图对象？怎么删除视图对象？

表复制：
mysql> create table dept2 as select * from dept;

创建视图对象：
create view dept2_view as select * from dept2;

删除视图对象：
drop view dept2_view;

注意：只有DQL语句才能以view的形式创建。
create view view_name as 这里的语句必须是DQL语句;

2.3、用视图做什么？

--我们可以面向视图对象进行增删改查，对视图对象的增删改查，会导致原表被操作
--视图的特点：通过对视图的操作，会影响到原表数据

//面向视图查询
select * from 视图名;

// 面向视图插入
insert into 视图名(字段1,字段2,字段3...) values(值1,值2, 值3...);

// 面向视图删除
delete from 视图名;

// 创建视图对象
创建视图语法：
create view
视图名(自定义)
as 查询语句;

例：
create view
视图名(自定义)
as
select
e.ename,e.sal,d.dname
from
emp e
join
dept d
on
e.deptno = d.deptno;

// 面向视图更新
update 视图名 set 列名 = 值 where 条件;

##重点：视图对象在实际开发中到底有什么用？--《方便，简化开发，利于维护》
假设有一条非常复杂的SQL语句，而这条SQL语句需要在不同的位置上反复使用。
每一次使用这个sql语句的时候都需要重新编写，很长，很麻烦，怎么办？
--可以把这条复杂的SQL语句以视图对象的形式新建。
--在需要编写这条SQL语句的位置直接使用视图对象，可以大大简化开发。
--并且利于后期的维护，因为修改的时候也只需要修改一个位置就行，只需要修改视图对象所映射的SQL语句。

我们以后面向视图开发的时候，使用视图的时候可以像使用table一样。
可以对视图进行增删改查等操作。视图不是在内存当中，视图对象也是
存储在硬盘上的，不会消失。

注意：
！！视图对应的语句只能是DQL语句。
！！但是视图对象创建完成之后，可以对视图进行增删改查等操作。

3、DBA常用命令？

1、重点掌握：数据的导入和导出（数据的备份）

数据导出？
注意：在windows的dos命令窗口中：
mysqldump 库名>硬盘中的位置 -uroot -p123456
例：
mysqldump bjpowernode>D:\bjpowernode.sql -uroot -p123456

也可以导出指定的表
mysqldump 库名表名>硬盘中的位置 -uroot -p123456
mysqldump bjpowernode emp>D:\bjpowernode.sql -uroot -p123456

数据导入？
注意：需要先登录到mysql数据库服务器上。
然后创建数据库： create database bjpowernode;
使用数据库： use bjpowernode
然后初始化数据库：source D:\bjpowernode.sql

2、新建用户
CREATE USER username IDENTIFIED BY 'password';

3、授权
grant all privileges on dbname.tbname to 'username'@'login ip' identified by 'password' with grant option;
1)privileges 包括：
1) alter：修改数据库的表
2) create：创建新的数据库或表
3) delete：删除表数据
4) drop：删除数据库/表
5) index：创建/删除索引
6) insert：添加表数据
7) select：查询表数据
8) update：更新表数据
9) all：允许任何操作
10) usage：只允许登录
2) dbname=*表示所有数据库
3) tbname=*表示所有表
4) login ip=%表示任何 ip
5) password为空，表示不需要密码即可登录
6) with grant option; 表示该用户还可以授权给其他用户

--首先以 root 用户进入 mysql，然后键入命令：grant select,insert,update,delete on *.* to p361 @localhost Identified by "123";
--如果希望该用户能够在任何机器上登陆 mysql，则将 localhost 改为 "%" 。

4、回收权限
revoke privileges on dbname[.tbname] from username;

5、修改密码
update user set password = password('qwe') where user = 'p646';

刷新权限： flush privileges;

posted @ 2022-08-15 17:44 手可摘星陈1024 阅读(275) 评论(0) 编辑收藏举报

刷新页面返回顶部

手可摘星陈1024

mysql基础

公告