MySQL相关笔记

(这里我只是记录了部分自己不太熟的容易忘记的内容的笔记，方便以后使用的时候查看)

命令行登录数据库

mysql -u 用户名 -p 密码
mysql -h 主机IP -u 用户名 -p 密码

MySQL存储引擎

Mysql5.1版本之前默认的存储引擎是MyISAM，之后的版本，默认的引擎是InnoDB

InnoDB

1. 事务处理、回滚、崩溃修复能力和多版本并发控制
2. 自增长AUTO_INCREMENT
3. 外键（FOREIGN KEY）

InnoDB的优势在于提供了良好的事务处理、崩溃修复能力和并发控制。缺点是读写效率较差，占用的数据空间相对较大。

MyISAM：

MyISAM的优势在于占用空间小，处理速度快。缺点是不支持事物的完整性和并发性。

Memory：

1. 数据全部放在内存中
2. 哈希索引

Memory用到的很少，因为它把数据存到内存中，如果内存出现异常就会影响数据。如果重启或者关机，所有数据都会消失。因此，基于memory的表的生命周期很短，一般是一次性的。

MySQL数据类型

DDL语句

create database;  --创建数据库

create database 数据库名 character set 字符集;  --创建数据库并指定字符集

CREATE DATABASE db1_1 CHARACTER SET utf8;

use 数据库;  --切换数据库
select database();  --查看当前正在使用的数据库
show databases;  --查看Mysql中 都有哪些数据库
show create database 数据库名;  --查看一个数据库的定义信息

alter database 数据库名 character set 字符集;  --修改数据库的字符集
drop database 数据库名;  -- 删除数据库

--创建表
CREATE TABLE test1 (
cid INT,
cname VARCHAR(20)
);
--复制表结构
create table test2 like test1 ;

show tables; --查看当前数据库中的所有表名
desc 表名;  -- 查看数据表的结构

drop table 表名;  -- 删除表（从数据库中永久删除某一张表）
drop table if exists 表名;  --判断表是否存在， 存在的话就删除,不存在就不执行删除

rename table 旧表名 to 新表名; -- 修改表名
alter table 表名 add 字段名称 字段类型;  --添加字段
alter table 表名 modify 字段名称 字段类型;  --修改数据类型
alter table 表名 change 旧列名 新列名 类型(长度);  --修改列名称
alter table 表名 drop 列名; --删除列名

DML语句

--insert
INSERT INTO student (sid,sname,age,sex,address) VALUES(1,'孙悟空',20,'男','花果山');
或
INSERT INTO student VALUES(2,'孙悟饭',10,'男','地球');

--update
update 表名 set 列名 = 值 [where 条件表达式：字段名 = 值 ]

--delete
delete from 表名 [where 字段名 = 值]

limit关键字

用于限制返回的查询结果的行数 (可以通过limit指定查询多少行数据)，多用来完成分页，类似于oracle中的rownum。

SELECT 字段1,字段2… FROM 表名 LIMIT offset , length;

- offset 起始行数, 从0开始记数, 如果省略 则默认为 0

- length 返回的行数

– 分页操作 每页显示3条数据（关于分页这里，我还要再看看书）

SELECT * FROM emp LIMIT 0,3; -- 第1页
SELECT * FROM emp LIMIT 3,3; -- 第2页 2-1=1 1*3=3
SELECT * FROM emp LIMIT 6,3; -- 第三页

主键约束

用来唯一标识数据表中的一条记录

-- 删除主键约束
ALTER TABLE emp2 DROP PRIMARY KEY;
DESC emp2;
-- 创建主键自增的表，关键字 AUTO_INCREMENT,主键类型必须是整数类型
CREATE TABLE emp2 (
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
sex CHAR(1)
);

DELETE和TRUNCATE对自增长的影响：

- DELETE 只是删除表中所有数据,对自增没有影响
- TRUNCATE 将整个表删除掉,然后创建一个新的表自增的主键,重新从 1开始

主键约束与唯一约束的区别:

- 主键约束 唯一且不能够为空（主键约束=非空约束+唯一约束）
- 唯一约束,唯一 但是可以为空
- 一个表中只能有一个主键 , 但是可以有多个唯一约束

默认值

-- 创建带有默认值的表
CREATE TABLE emp4(
eid INT PRIMARY KEY AUTO_INCREMENT,
-- 为ename 字段添加默认值
ename VARCHAR(20) DEFAULT '女',
sex CHAR(1)
);

-- 添加数据 使用默认值
INSERT INTO emp4(ename,sex) VALUES(DEFAULT,'男');
INSERT INTO emp4(sex) VALUES('女');

外键约束

主表：主键所在的表
从表：外键所在的表

[CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)  --新建表时添加外键

-- 新创建 employee表,添加外键约束
CREATE TABLE employee(
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
age INT,
dept_id INT,  -- 外键字段类型要和主表的主键字段类型保持一致
-- 添加外键约束
CONSTRAINT emp_dept_fk FOREIGN KEY(dept_id) REFERENCES department(id)
);

--给已创建的表添加外键
ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主 键字段名); 

添加外键约束，就会产生强制性的外键数据检查，从而保证了数据的完整性和一致性。

--删除外键约束
alter table 从表 drop foreign key 外键约束名称

注意事项：

- 从表外键类型必须与主表主键类型一致，否则创建失败
- 添加数据时，应该先添加主表中的数据
- 删除数据时，应该先删除从表中的数据

级联删除操作(了解)

如果想实现删除主表数据的同时,也删除掉从表数据,可以使用级联删除操作
ON DELETE CASCADE --级联删除

CREATE TABLE employee(
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
age INT,
dept_id INT,
CONSTRAINT emp_dept_fk FOREIGN KEY(dept_id) REFERENCES department(id)
-- 添加级联删除
ON DELETE CASCADE
);

UNION和UNION ALL的区别：

- 重复值：UNION在进行表连接后会筛选掉重复的记录，而Union All不会去除重复记录。
- UNION ALL只是简单的将两个结果合并后就返回，不会去重。
- 在执行效率上，UNION ALL 要比UNION快很多，因此，若可以确认合并的两个结果集中不包含重复数据，那么就使用UNION ALL。但是具体用哪个还要根据业务的需要来看。

MySQL函数

数学函数、字符串函数、日期时间函数、条件判断函数、系统信息函数
下面只列出一部分比较常见的函数

数学函数

ABS(x) 返回x的绝对值
FLOOR(x) 向下取整
CEIL(X) 向上取整，返回不大于x的最大整数值
RAND() 返回0～1的随机数
PI() 返回圆周率的值
MOD(x,y) 返回x除以y以后的余数

字符串函数

CONCAT(s1,s2…) 将字符串拼接，连接为一个字符串
LEFT(s,n) 返回从字符串s开始的n最左字符
TRIM(s) 移除掉字串s中的字头或字尾处空格
REPLACE(s,s1,s2) 用字符串s2替代字符串s重的字符串s1
SUBSTRING(s,n,len) 截取字符串s中第n个位置开始，长度为len的字符串
MID(s,n.len) 同SUBSTRING(s,n,len)
REVERSE(s) 将字符串s的顺序翻转过来

日期和时间函数

CURDATE() 返回当前日期
CURTIME() 返回当前时间
NOW() 返回当前日期和时间
MONTH(d) 返回月份
YEAR(d) 返回年份

条件判断函数

IF(expo,v1,v2) 如果表达式成立，则执行v1，否则执行v2
CASE WHEN 用于计算条件列表并返回多个可能结果表达式之一，可翻译为：当。。。则结果为。。。

系统信息函数

VERSION() 返回数据库的版本号
DATABASES() 返回当前数据库名
USER() 返回当前用户名