MySQL

1.SQL语句

DML：数据操纵语句，用于添加、删除、修改、查询数据库记录，并检查数据完整性。insert、update、delete、select

DDL：数据定义语句，用于库和表的创建、修改、删除。create、alter、drop、create index、drop index

DCL：数据控制语句，用于定义用户的访问权限和安全级别。grant、revoke、commit、rollback、savepoint、lock

2.MySQL服务端的登录与退出

登录：mysql -h 主机名 -u用户名 -p密码

退出：exit

查看数据库：show databases

使用数据库：use database 数据库名

新建数据库：create database 数据库名

查看表：show tables

建表：create table customer(

           id varchar(10),

           ......

           );

*注：插入varchar或date型的数据要用单引号引起来，在MySQL里一个汉字占一个varchar长度

3.组函数（聚集函数），配合group by使用

AVG(),COUNT(),MAX(),MIN(),SUM()

当这些组合函数作为查询条件时，不能放在where后面，应该在having后面

*注：使用表的别名的时候，select查询的列名前缀也必须使用别名

4.join连接

内连接：(inner) join on

外链接：left (outer) join on、right (outer) join on

select * from table1 t1

inner join table2 t2

on t1.id = t2.id

join table3 t3

on t1.name = t3.name;

5.常见函数：字符函数、数学函数、日期函数、其他。。。

字符函数：

字符小写：LOWER(' SQL Course')------>sql course

字符大写：UPPER('sql Course')------>SQL COURSE

字符拼接：CONCAT('Hello', 'World')------>HelloWorld

字符截取：SUBSTR('HelloWorld', 1, 5)------>Hello

计算字符长度：LENGTH('HelloWorld')------>10

某个字符的位置：INSRT('HelloWorld', 'W')------>6

设置字符长度，并用*左补全：LPAD(salary, 10, '*')------>*****24000

设置字符长度，并用*右补全：RPAD(salary, 10, '*')------>24000*****

去掉特定字符：TRIM('H' FROM 'HelloWorld')------>elloWorld

替换特定字符：REPLACE('abcde', 'b', 'm')------>amcde

数字函数：

ROUND()：四舍五入  ROUND(45.926, 2)------>45.93

TRUNCATE():截断  TRUNCATE(45.926, 2)------>45.92

MOD():求余  MOD(1600, 300)------>100

日期函数：

now():获取当前日期

str_to_date():将日期格式的字符转换成制定格式的日期  STR_TO_DATE('9-13-1999', '%m-%d-%Y')------>1999-09-13

date_format():将日期转换成字符  DATE_FORMAT('2018/6/6', '%Y年/%m月/%d日')------>2018年06月06日

其他：

select id,

CASE job WHEN '普通员工' THEN salary*1.01

                 WHEN '经理' THEN salary*1.2

ELSE        salary

END

form employees;

6.MySQL自动提交，如果要回滚，设置SET AUTOCOMMIT = FALSE;

7.alter table语句：

alter table dept add job varchar(20);

alter table dept modify (last_name varchar(30) defalut 'kobe');

alter table dept drop column job;

alter table dept change department_name dept_name varchar(15);

*注：改表名：alter table dept rename to detail;

8.几种删除表的区别：

一：用法和区别

drop：drop table 表名

      删除内容和定义，并释放空间。执行drop语句，将使此表的结构一起删除。

truncate (清空表中的数据)：truncate table 表名

      删除内容、释放空间但不删除定义(也就是保留表的数据结构)。与drop不同的是,只是清空表数据而已。

      truncate不能删除行数据，虽然只删除数据，但是比delete彻底，它只删除表数据。

delete：delete from 表名 （where 列名 = 值）

       与truncate类似，delete也只删除内容、释放空间但不删除定义；但是delete即可以对行数据进行删除，也可以对整表数据进行删除。

二：注意

      1.delete语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存，以便进行进行回滚操作。

      2.执行速度一般来说：drop>truncate>delete

      3.delete语句是数据库操作语言(dml)，这个操作会放到 rollback segement 中，事务提交之后才生效；如果有相应的 trigger，执行的时候将被触发。

      4.truncate、drop 是数据库定义语言(ddl)，操作立即生效，原数据不放到 rollback segment 中，不能回滚，操作不触发trigger。

      5.truncate语句执行以后，id标识列还是按顺序排列，保持连续；而delete语句执行后，ID标识列不连续

9.约束

为了保证数据的一致性和完整性，约束是表级的强制规定，

NOT NULL非空  UNIQUE唯一  PRIMARY KEY主键  FOREIGN KEY外键  CHECK检查  DEFAULT默认

*注：MySQL不支持check约束，可以使用，但没有任何效果

10.分页查询

MySQL使用limit进行分页

select * from dept limit 0,10;前10条

select * from dept limit 10,20;第11至20条

11.查询语句执行顺序

语句形式为：select--from--where--group by--having--order by--

顺序：1.from--2.where--3.group by--4.having--5.order by

12.存储引擎

myisam：不支持事务，不支持外键，访问速度快，对事物没要求或以select、insert为主的应用可以使用

InnoDB：健壮的事务型存储引擎。需要事务支持，并且有较高的并发读取速率，可以选择

memory：响应速度快，采用的存储介质是系统内存

merge

archive

13.事务

ACID：原子性、一致性、隔离性、持久性

不考虑事务的隔离性会出现的问题：

1.脏读：一个事务读取了另一个还没有提交的事务处理后的数据，结果另一个事务没有提交，那么之前读的数据就是无效的

2.不可重复读：一个事务读取了数据，另一个事务修改了数据，这个事务又来读同一数据，发现和之前读的数据不一样

3.幻读：一个事务对数据进行操作以后，另一个事务又操作了数据，如添加了几行，之前的事务再次查询，发现多了几行，好像出现了幻觉

事务的隔离级别：

① Serializable (串行化)：可避免脏读、不可重复读、幻读的发生。

② Repeatable read (可重复读)：可避免脏读、不可重复读的发生。

③ Read committed (读已提交)：可避免脏读的发生。

④ Read uncommitted (读未提交)：最低级别，任何情况都无法保证。

*注：在MySQL数据库中，支持上面四种隔离级别，默认的为Repeatable read (可重复读)；而在Oracle数据库中，只支持Serializable (串行化)级别和Read committed (读已提交)这两种级别，其中默认的为Read committed级别。

14.数据库添加索引为什么能加快速度？

     DB在执行一条Sql语句的时候，默认的方式是根据搜索条件进行全表扫描，遇到匹配条件的就加入搜索结果集合。如果我们对某一字段增加索引，查询时就会先去索引列表中一次定位到特定值的行数，大大减少遍历匹配的行数，所以能明显增加查询的速度。

*注：B树和B+树

 

如图所示,区别有以下两点:

1. B+树中只有叶子节点会带有指向数据记录的指针(ROWID),而B树则所有节点都带有,在内部节点出现的索引项不会再出现在叶子节点中。

2. B+树中所有叶子节点都是通过指针连接在一起,而B树不会。

 

B+树的优点:

1. 非叶子节点不会带上ROWID,这样,一个块中可以容纳更多的索引项,一是可以降低树的高度。二是一个内部节点可以定位更多的叶子节点。

2. 叶子节点之间通过指针来连接,范围扫描将十分简单,而对于B树来说,则需要在叶子节点和内部节点不停的往返移动。

 

B树的优点:

对于在内部节点的数据,可直接得到,不必根据叶子节点来定位。

15.存储过程