MySql数据库学习总结(MySQL入门到精通)

2017.1.24-2.3日（在大兴实验室）

 

1.数据库存储引擎：

（1）MyISAM: 访问速度快，对事物完整性没要求，并以访问为主的适合这个

（2）InnoDB: 更占磁盘空间，需要进行频繁的更新、删除操作，对事物完整性要求比较高，需要实现并发控制时适合。

（3）MEMORY：内存存储数据，访问比较快，但是安全无保障，数据小的时候适合这个。

 

2.创建视图：

create view view—name as +sql语句  即封装sql语句，利于复用。

create view view_pro as SELECT name from p_fruit

create or replace view view_pro as select name,price from p_fruit

select * from view_pro

3.触发器：

create trigger trigger_diarytime before insert

on t_dept for each row

insert into t_diary values(null,'t_dept',now());

 

多条语句

delimiter $$

create trigger trigger_diarytime2 after insert on t_dept for each ROW

BEGIN

insert into t_diary values(null,'t_dept',now());

insert into t_diary values(1,'t_ad',now());

END

$$

delimiter;     

 

删除drop trigger trigger_diarytime

 

4.插入语句：

搜索其他表的结果来插入：

insert into t_dept(deptno,dname,loc)

select id,name,loc from t_loader 

 

5.更新语句：

（1）更新特定记录：

update  t_dept  set  dname='xiaoming', loc='shanghai'  where  deptno=1;

（2）更新所有数据记录：

update t_dept set dname='xiaoming',loc='shanghai'  where deptno<12;

where后的条件要满足所有，或者干脆没有这个条件。

 

6.删除语句：

（1）删除特定 delete from t_dept where deptno=1;

（2）删除所有delete from t_dept

where后的条件要满足所有，或者干脆没有这个条件。

 

7.单表查询

（1）避免重复的查询：

select  distinct  job,deptno  from t_employee

 

（2）四则运算+

显示修改：

select  ename, sal*10 （as） yearsalary  from t_employee

 

(3)concat拼接字符串

select concat(ename, '雇员的年薪为：', sal*12)  yearsalary  from  t_employee

 

(4)多条件

select ename from t_employee where job='clerk' && sal>2000

（5）between... and...判断字段数值是否在指定范围内

select  ename,MGR,sal  from  t_employee  where  sal  between  300 and 800;

范围为：(300,800]

 

不在范围内：

select  ename,MGR,sal  from  t_employee  where  sal  not between 300 and 800;

范围为 ：（--，300） +（800，++）

 

（6）非（为）空查询

为空：select  ename,sal  from t_employee  where comm is NULL;

非空：select ename,sal from t_employee where comm is not NULL;

 

（7）集合中的数据记录查询in

在：select ename,sal,MGR from t_employee where MGR in (7698,7839,7566,7696)

不在：

select ename,sal,MGR from t_employee where MGR not in (7698,7839,7566,7696)

或者：

select ename,sal,MGR from t_employee where not MGR  in (7698,7839,7566,7696)

 

如果in中存在null，如果是in查询则无影响，

select ename,sal,MGR from t_employee where MGR  in (7698,7839,7566,7696,null)

如果是not in则查询结果为空

select ename,sal,MGR from t_employee where not MGR in (7839,7566,7696, null)

 

 

 

 

（8）模糊查询like（字符串必须加单引号或者双引号）

1.  _匹配一个字符select ename,sal from t_employee where ename like 'a_';

2.  %匹配所有字符select ename,sal from t_employee where ename like 'a%';

3.查询第二个字符不是a的所有字符：

select  ename, sal  from  t_employee  where  ename  not  like  '_a%';

4.查询所有字符：'%%'

 

(9)排序查询

单字段：select * from t_employee  where sal>300  order  by  sal  asc|desc

多字段：

select * from t_employee where sal>300 order by  sal  asc, Hiredate  DESC;

结果显示，先按 sal升序排列，遇到sal值相同的按Hiredate进行降序排列。

 

（10）限制查询数量limit

1.不设置初始偏移量，查询输入行数的条数，默认从0开始，

SELECT * from t_employee  where  comm  is  not  null  limit  4;

 

2.限制初始偏移量

select * from t_employee where comm is not null order by hiredate limit 0,5

 

select * from t_employee where comm is not null order by hiredate limit 5,5

从第六条语句开始查询。

 

(11)统计函数与分组查询：

1. select  count(sal)  total  from t_employee

2. select  avg(sal)  average  from t_employee

3. select  sum(sal)  sum  from t_employee

4. select  min(sal)  min  from t_employee

5.select  max(sal)  max  from t_employee

注意点：如果表中没有任何数据，则count函数返回0，其他函数返回null

 

（12）分组查询：当数据值存在重复的时候才去分组)，使用关键字group by:

1.如：select * from t_employee GROUP BY  deptno 只显示deptno中的随机一个10,20,30编号的行，重复的不显示。

2.上述显示存在问题，可以用group_concat()来显示每个分组中指定字段的值

select  deptno, group_concat(ename)  enames  from t_employee group by deptno;

3.显示每组ename的数量

select  deptno, group_concat(ename)  enames,  count(ename) number  from t_employee  group by deptno;

4.多组分组

select deptno,hiredate from t_employee GROUP BY deptno,hiredate

先按照第一个分组，针对前一组的每一组来对第二个进行分组。

 

显示每个分组中雇员名称和雇员个数：

select deptno,hiredate,GROUP_CONCAT(ename) enames,COUNT(ename) from t_employee GROUP BY deptno,hiredate

 

（12）分组查询中的having，对分组进行条件限制。 

select deptno,avg(sal) average,GROUP_CONCAT(ename) enames,COUNT(ename) number from t_employee  GROUP BY deptno having avg(sal)>2000

 

8.多表查询

 t_deptt_employee

 

（1）内连接为笛卡尔积数据记录中保留表关系中所有匹配的数据记录，舍弃不匹配的数据记录。按照匹配条件可以分为自然连接（去重字段）、等值连接（不去重字段）和不等值连接。

特殊等值连接形式，自连接：（先把两个相同表按条件串在一起，再去选择某些列，这些列依然由t1，t2控制）。查询雇员姓名、职位、领导。

select t1.ename employee,t1.job,t2.ename boss from t_employee t1 INNER JOIN t_employee t2 on t1.MGR=t2.empno;

 

一般的等值连接，查询雇员编号、姓名、职位、部门名称、位置。

select t1.empno,t1.ename,t1.job,t2.dname,t2.loc location from t_employee t1 INNER JOIN t_dept t2 on t1.deptno=t2.deptno;

 

等值连接查三张表：

select t1.empno,t1.ename,t1.job,t1.MGR,t2.dname,t2.loc location, t3.ename boss from t_employee t1

INNER JOIN t_dept t2 on t1.deptno=t2.deptno

INNER JOIN t_employee t3 on t3.empno=t1.MGR;

不等连接查询除去等号条件之外还要加一个不等的条件用and衔接。

select t1.ename employee,t1.job,t2.ename boss from t_employee t1 INNER JOIN t_employee t2 on t1.MGR=t2.empno and t1.empno>t2.empno;

 

（2）外链接为笛卡尔积数据记录中不仅保留表关系中所有匹配的数据记录，而且还会保留部分不匹配的数据记录。按照保留不匹配条件数据记录来源可以分为左外链接、右外连接和全外连接。（字段数为两张表的和不去重复）

左外连接：以左表为基准，右表没有的就以空值null来代替。

select e.ename,e.job,l.ename boss from t_employee e

LEFT JOIN t_employee l on e.MGR=l.empno

即可以显示所有。

 

右外连接：以右表为基准，左表没有的就以空值null来代替。

全外连接：为左右两边匹配的数据+不匹配的数据记录。

 

9.合并查询：

这个是单纯的把两个表合并在一起：

select * from t_computer union all select * from t_music;

 

这个是把相同的合并了：

select * from t_computer  union  select * from t_music;

 

10.子查询（替代连接查询）

通过子查询可以实现多表查询，该查询语句中可能包含in、any、all、exists等关键字，除此之外还可能包含比较运算符。子查询主要出现在where和from子句中。

       where子句中的子查询：该位置处的子查询一般返回

1.单行单列：通常包含>、<、=、!=等运算符号；

select * from t_employee where sal>(select sal from t_employee where ename='smith')

2.多行单列：经常包含in(not in)、any、all、exists等关键字和>、<、=、!=等；

select * from t_employee e where e.deptno in (SELECT deptno from t_dept)

select * from t_employee e where e.sal>=any(select sal from t_employee e where e.job='manager')

select * from t_employee e where e.sal>all(select sal from t_employee e where e.job='manager')

select * from t_dept c where  EXISTS(SELECT * from t_employee where deptno=c.deptno)（前者的查询出来的记录在条件之中则显示）

3.单行多列：很少出现。

select ename,sal,job from t_employee where (sal,job)=(select sal,job from t_employee where ename='smith');

 

 

       from子句中的子查询：该位置处的子查询一般返回多行多列数据记录，相当于一个临时表。

select d.deptno,d.dname,d.loc,e.number,e.average from t_dept d INNER JOIN (select deptno dno,COUNT(empno) number,avg(sal) average from t_employee GROUP BY deptno desc) e on e.dno=d.deptno

 

11.特殊的比较运算符，即正则表达式。匹配成功返回1，否返回0；

（1）以^开头表示匹配开头的字符串或字符

select 'cjong' regexp '^c' 特定字符,

'cjgongcads' regexp '^cjgong' 特定字符串

（2）以$结尾，表示匹配以特定字符结尾的，

select 'cjong' regexp 'ong$' 特定字符,

'cjgongcads' regexp 'cads$' 特定字符串

（3）.匹配任意一个特殊字符。

select 'cjong' regexp 'c...g$' 特定字符

（4）执行带有[]或[^]的模式字符的SQL语句select，可以实现比较是否包含指定字符中任意一个和指定字符外任意一个。

       select  'cjgong' regexp '[ab]' 指定字符中字符,  0

       'cjgong' regexp '[a-zA-Z]'指定字符中的集合区间, 1

  'cjgong' regexp '[^abcjgong]' 指定字符外字符,    0

   'cjong' regexp '[^a-zA-Z0-9]' 指定字符外集合区间 0

 

（5）*和+用于比较是否包含多个指定字符，前者可表示0个或任意个，后者至少表示一个。

select 'cjgong' regexp 'a*g', 返回1

       'cjong' regexp 'a+g'  返回0

表示g之前是否有多个a。

（6）执行带有“|”模式字符可以实现比较是否包含指定字符串中任意一个字符串。

select 'cjgong' regexp 'cgk',

'cjgong' regexp 'cjc|cjg'

 

（7）执行带有{M}或{M，N}模式字符的SQL语句。

 

12.日期和时间函数：

（1）获取当前日期和时间select now(),current_timestamp(),localtime(),sysdate()

（2）获取当前日期select curdate(),current_date()

（3）获取当前时间select curtime(),current_time()

(4) select ow(),UNIX_TIMESTAMP(),FROM_UNIXTIME(unix_timestamp(now()))

返回1971年1月1日起经过的毫秒值。

（5）UTC时间 select UTC_DATE(),utc_time()

（6）获取日期时间的各部分值

select now() 当前日期时间,

year(now()) 年,

quarter(now()) 季度,

month(now()) 月,

week(now()) 星期,

dayofmonth(now()) 天,

hour(now()) 小时,

minute(now()) 分,

second(now()) 秒

 

月的函数select month (now()), monthname (now())

 

星期的函数select week(now()),weekofyear(now()),dayname(now()),dayofweek(now()),weekday(now())

 

天的函数：select dayofyear(now()),dayofmonth(now())

 

获取指定值的extract()函数

select now(),

extract(year from now()),

extract(month from now()),

extract(day from now()),

extract(hour from now()),

extract(minute from now()),

extract(second from now())

 

(7)计算时间和日期的函数

1.to_days(date)计算date与0000年1月1日相隔天数。

2.from_days(number)函数：该函数计算从默认日期和时间0000年1月1日开始经历number天后的日期：

select to_days(now()),   from_days(116774);

3.计算两个日期之间间隔天数用datedief(date1,date2)

select DATEDIFF(now(),'2019-10-10') 前面减后面的天数。

 

（8）与指定日期和时间操作

1.adddate(date,n)  date+n天后的日期，sundate(date,n) date+n天前的日期

select adddate(curdate(),5),subdate(curdate(),5)

还有addtime与subtime来操作时间和日期

 

2.两年三月后、前

select ADDDATE(CURDATE(),INTERVAL '2,3' year_month)

select SUBDATE(CURDATE(),INTERVAL '2,3' year_month)

 

13.储存过程和函数

（1）创建存储过程

delimiter $$

create PROCEDURE procedure_employee_sal()

COMMENT '查询员工工资'

BEGIN

              select sal from t_employee;

end $$

delimiter;

创建函数：

delimiter $$

create function function_employee_sal (empno int(11))

          returns double(10,2)

COMMENT '查询某个员工的工资'

BEGIN

return (select sal from t_employee where t_employee.empno=empno);

END$$

delimiter;

 

存储过程和函数为一条或多条SQL语句的集合，优点如下：（简单、高性能）

1.允许标准组件式编程，提高了SQL语句的重用性、共享性和可移植性。

2.实现较快的执行速度，能够减少网络流量。

3.可以被作为一种安全机制来利用。

 

14.事务处理--针对并发访问保证数据库记录更改的一致性。innodb支持

（1）事物的特性：

1.原子性（atomicity）：所有事物操作均可视为一个原子单元，即对事物所进行的数据修改的操作只能是完全提交或者完全回滚。

2.一致性（consistency）：事物在完成时，必须使所有的数据从一种一致性状态变更为另外一种一致性状态，所有的变更都必须应用于事物的修改，以确保数据的完整性。

3.隔离性（isolation）一个事物中的操作语句所做的修改必须与其他事物所做的修改相隔离。在进行事物查看数据时数据所处的状态，要么是被另一并发事物修改之前的状态，要么是被另一并发事物修改之后的状态，即当前事务不会查看另一个并发事务正在修改的数据。这种特性通过锁机制来实现。

4.持久性（durability）事物完成后，所作修改对数据的影响是永久的，即使系统重启或者出现故障数据也可以恢复。

 

(2)REDO日志和UNDO日志来实现。

将REDO的更新操作写到日志缓冲区，commit提交之后将缓冲区的内容刷新到磁盘。

UNDO主要用于事物异常时的数据回滚，具体内容就是复制事物前的数据库内容到UNDO缓冲区，然后在合适的时间将内容刷新到磁盘。

 

（3）采用begin来开启事物，然后进行一个更新操作SQL语句，如果不commit提交，则rollback直接回到更新之前的状态，提交之后rollback就无法滚回之前的状态。

 

（4）SQL标准定义了四种事务隔离级别

1.未提交读：（容易导致读脏数据）

set global transaction isolation level read uncommitted

所有事物都可以看到其他未提交的执行结果，读未提交的数据为脏读

 

2.提交读（大多数数据库默认的）：一个事物在提交前所做的任何改变都会是不可见的，只能看见已提交的改变。此级别也支持不可重复读，即同一查询可能返回不同结果。

set global transaction isolation level read committed

 

3.可重复读（mysql默认，确保同一事物在多个实例并发操作读取数据时，会看到同样的数据行。理论上可能会导致“幻读”即先前一次读的数据行数row，此间进行了数据插入操作，之后接下来的查询中就会发现有几列数据是它先前没有的。 InnoDB存储引擎采用MVCC机制来解决）

set global transaction isolation level repeatable read

 

4.可串行化（通过强制事物顺序实现，在每个读的数据行加上共享锁实现。但可能会导致超时现象和锁竞争）

set global transaction isolation level serializable

 

（5）InnoDB锁机制

1.共享锁S，锁粒度为行或多行。一个事物获取共享锁之后可以对锁定范围内的数据进行读操作。

2.排它锁X（exclusive），锁粒度为行或多行。一个事物获取排他锁之后可以对锁定范围内的数据进行写操作。

3.意向锁，为表锁，锁粒度为整张表，分为意向共享锁IS和意向排它锁IX。

 

4.锁粒度分为表锁和行锁