MySQL学习笔记

1. DBMS ：

DataBaseManagementSystem，数据库管理系统。常见的DBMS有：Oracle、MySQL、SQLServer、DB2等

2. SQL：

Structured Query Language，结构化查询语言

3. 数据库分类：

(1) 关系型数据库：经过数学理论验证可以保存实现的生活中的关系，以表为单位保存数据
(2) 非关系型数据可：一般以键值对的形式保存数据，可以应用在数据缓存，比如：Redis

4. 常见的关系型数据库

(1) MySQL：开源数据库，市场占有率第一
(2) Oracle：性能最高，价格最贵，闭源数据库
(3) SQLServer：微软公司闭源数据库，应用在微软嵌套解决方案中（操作系统、WebServer、开发软件、数据库软件等
(4) DB2：IBM公司闭源数据库，应用在IBM整套解决方案中，安全性高
(5) Sqlite：轻量级数据库，应用于嵌入式设备或者移动设备上

5. 连接数据库的语句：

mysql -uroot -p 敲回车
exit 退出

6. 数据库SQL

(1) 查询所有数据库：
show database;
(2) 创建数据库：
create datebase 新建数据库名称；
(3) 查看数据库详情：
show create database 查看数据库名称；
(4) 创建数据库并制定字符集：
create database 新建数据库名称 characterset utf8/gbk;
(5) 删除数据库：
drop 删除数据库名称；
(6) 切换/使用数据库
use 数据库名称；

7. 表相关数SQL

(01) 查看所有表：
show tables;
(02) 查看表详情：
show create table 表名；
(03) 创建表：
create table 表名(字段名称 字段类型,字段名称 字段类型,…)；
创建表主键约束：
create table 表名(字段名称 字段类型 primary key,字段类型 字段名称,…);
创建表自增主键：
create table 表名(字段名称 字段类 primary key auto_increment,字段类型 字段名称,…);
创建表加注释：
create table 表名(字段名称 字段类型 comment ‘注释’,字段名称 字段类型 comment ‘注释’,…)；
(04) 创建表并制定引擎和字符集
create table 表名(字段名 字段类型,字段名 字段类型…) engine=innodb/myisam charset=utf8/gbk;
* 表引擎说明：
innodb(默认)：支持数据库的高级操作（外键，事务等）
myisam：不支持数据的高级操作，只支持基本的数据增删改查操作
(05) 查看表字段：
desc 表名;
(06) 删除表：
drop table 表名;
truncate table 表名; 删除表并创建新表,自增主键值会清零
(07) 修改表名：
rename table 原名 to 新表名;
(08) 修改表引擎和字符集
alter table 表名 engine=innodb/myisam charset=utf8/gbk;
(09) 添加字段
添加到最后：
alter table 表名 add 字段名称 字段类型
添加到最前：
alter table 表名 add 字段名称 字段类型
添加到某个字段后面
alter table 表名 add 字段名称 字段类型 after 字段名称
(10) 删除表字段
alter table 表名 drop 字段名
(11) 修改字段类型和位置
alter table 表名 modify 字段名 新类型 first/after xx
(12) 约束
非空约束：not null
唯一约束：unique
默认约束：default
主键约束：primary key
外键约束：值可以为null，可以重复，但不能是关系表中不存在的数据，如果建立好关系后 被依赖的数据不能先删除，被依赖的表不能先删除
constraint 约束名 foreign key(外键字段名) references 被依赖的表名(被依赖的字段名)
create table 表名1(id int primary key auto_increment,name varchar(10));
create table 表名2(id int primary key auto_increment,name varchar(10),constraint 约束名 foreign key(deptid) references 表名1(id));

8. 数据相关SQL

(1) 添加数据
insert into 表名 values(字段值,字段值,…);
insert into 表名 (字段名1,字段名2,…) values(值1,值2,…);
批量添加数据：
insert into emp values(字段值,字段值,…),(字段值,字段值,…),(字段值,字段值,…)…;
insert into emp (字段名) values(字段值),(字段值),(字段值)…;
(2) 修改数据
update 表名 set 字段名=值 where 条件;
(3) 删除数据
delete from 表名 where 条件;
(4) 查询数据
select 字段信息 from 表名 where 条件;

9. 数据相关SQL进阶

(1) is null 和 is not null
空和非空
(2) and 和 or
and相当于&&，or相当于||
(3) between x and y
xy两者之间，包含xy
(4) in (x,y,z)
是x、y、z其中之一
(5) 比较运算符 >, <, >= ,<=, != 和 <>
<> 相当于 != 不等于的意思
(6) 模糊查询
% ：代表0或多个未知字符
_ ：代表1个未知字符
(7) distinct 字段名
去重，放在字段名前面
(8) order by 字段名 asc/desc;
排序，asc：升序，desc：降序
(9) limit 跳过的条数,请求的条数
分页查询,limit (页数-1)*条数,条数,查到的是分页后的页数
(10) 数值计算 + - * / % 和 mod(x,y)
mod(x,y)也是取余，x%y
(11) 分组查询
group by
(12) having
后面可以添加条件用于查询，不能使用聚合函数
(13) 各个关键字的顺序 select … from … where … group by … having … order by … limit …;

10. 关联查询

(1) 查询方式有三种：等值连接、内连接、外连接
如果查询的数据是两张表的交集数据使用等值或内连接(推荐)
如果查询的数据是一张表的全部数据和另外一张表的交集数据使用外连接
(2) 等值连接： select * from A,B where A.x=B.x and A.age=18;
(3) 内连接：select * from A [inner] join B on A.x=B.x where A.age=18;
(4) 外连接：select * from A left/right [outer] join B on A.x=B.x where A.age=18;

11. 数据类型

(1) 整数类型：
int(n)、bigint(n) n代表的是显示长度可以使用zerofill限制是否需要在前面补上0
* zerofill说明：
字段的值的长度小于定义的长度时，是否在前面补上0，默认为int(10)
使用zerofill 时，默认是unsigned（无符号）的，数值范围是原值的2倍，例如，有符号为-128~+127，无符号为0 ~ 256。
(2) 浮点数：
double(n,c) n代表总长度c代表小数长度 78.386 n=5 c=3
decimal(n,c) 超高精度浮点数 当超高精度运算时使用
(3) 字符串：
char(m) 不可变长度 最大长度255 ,varchar(m) 可变长度 节省资源 最大长度为65535 长度超过255建议使用text text(m)可变长度 最大长度65535
(4) 日期
date：只能保存年月日
time：只能保存时分秒
datetime：年月日时分秒，默认值为null，最大值9999-12-31
timestamp：时间戳，年月日时分秒，默认值为当前系统时间，最大时间2038-1-19

12. 事务

开启事务：begin
提交事务：commit
回滚事务：rollback
保存回滚点：savepoint xxx;
回滚到指定回滚点：rollback to xxx;

13. 函数

(1) 聚合函数
avg(字段名): 平均值
max(字段名): 最大值
min(字段名): 最小值
sum(字段名): 求和
count(字段名): 统计数量

(2) select now();
获取当前的系统时间（年月日时分秒）

(3) select curdate();
获取当前的年月日

(4) select curtime();
获取当前的时分秒

(5) select date(now()); select time(now());
从年月日时分秒中提取年月日 和 提取时分秒
举例：
select time(birthday) from user;
查询商品表中每个商品上传年月日 select date(createdtime) from titem;
查询商品表中每个商品上传时分秒 select time(createdtime) from titem;
提取时间分量extract: select extract(year from now()); select extract(month from now()); select extract(day from now()); select extract(hour from now()); select extract(minute from now()); select extract(second from now());
查询每个员工入职的年份 select extract(year from hiredate) from emp;

(6) date_format(时间,格式)
日期格式化
%Y: 四位年 2008
%y: 2位年 08
%m: 两位月 01
%c: 1位月 1
%d: 日
%H: 24小时
%h: 12小时
%i: 分钟
%s: 秒
把now()转换成转成 XXXX年XX月XX号 XX点XX分XX秒 select date_format(now(),’%Y年%m月%d号 %H点%i分%s秒’);
把非标准时间格式转成标准格式 strtodate(非标准字符串时间,格式)
把 XX.XX.XXXX XX:XX:XX 转成标准时间格式 select strtodate(“XX.XX.XXXX XX:XX:XX”,"%d.%m.%Y %H:%i:%s");

(7) ifnull()函数
age = ifnull(x,y) 如果x值为null则age=y 不为null age=x
把员工表奖金为null的修改为0 update emp set comm=ifnull(comm,0);

(8) 字符串相关函数
concat(s1,s2)：字符串拼接
查询id在前面加上id： select concat(‘id:’,id) from 表名;

char_length(str)：获取字符串的字符长度
查询name和name长度 select name,char_length(name) from 表名;

instr(str,substr) select instr(‘abcdefg’,‘d’);
获取字符串在另外一个字符串中出现的位置

locate(substr,str) select locate(‘d’,‘abcdefg’);
获取字符串在另外一个字符串中出现的位置

select upper(‘nba’),lower(‘AbC’);
转大写 转小写

left(str,count); select left(“abcdefg”,2);
字符串截取，左边截取

right(str,count); select right(“abcdefg”,2);
字符串截取，右边截取：

substring(str,start,length?); select substring(“abcdefg”,2); select substring(“abcdefg”,2,3);
字符串截取，自由截取：

trim(str) select trim(" a b ");
去空白：

repeat(str,count) select repeat(‘ab’,2);
重复

replace(str,old,new) select replace(‘abc abc’,‘c’,‘m’);
替换

reverse(str) select reverse(“abc”);
反转

(9) 数字相关函数
floor(num) select floor(3.8);
向下取整
round(num) select round(23.8);
四舍五入
round(num,m) m代表小数位数 select round(23.879,2);
四舍五入
truncate(num,m) m代表小数位数 select truncate(23.879,2);
非四舍五入
rand() 0-1随机数 select rand();
随机数
3-5的随机整数 3，4，5 0-2+2 select floor(rand()*3+3);

(10) group_concat() 组连接
group_concat(字段1,’-’,字段2)
select 字段名,group_concat(字段1,’-’,字段2) from 表名;

14. 视图view

(1) 什么是视图：数据库中表和视图都是其内部的对象，视图可以理解成一张虚拟的表，数据来自原表，视图本质其实就是取代了一段SQL查询语句
(2) 视图的优势：视图可以起到SQL重用的作用，提高开发效率。可以隐藏敏感信息
(3) 视图创建方式： create view 视图名 as (子查询)
创建id>10的视图 create view 视图名 as (select * from 表名where id>10);
(4) 修改视图： create or replace view 视图名 as (子查询);
(5) 删除视图：drop view 视图名;

15. 索引

(1) 在数据量大的时候创建索引可以加快查询效率；只针对创建索引的字段查询有效；索引也需要占用内存，所以一般常用的字段才创建索引
(2) 创建索引： create index 索引名 on 表名(字段(字段长度));
(3) 查看索引 show index from 表名
(4) 删除索引 drop index 索引名 on 表名;

16. 其他

(1) ` 符号的作用
用于修饰表名和字段名,可以省略
(2) SQL语句中中文报错,原因是终端的编码的字符集和数据库服务器解码的字符集不一致
set names gbk;
(3) 别名
放在表名或者字段名后面
(4) 导入sql文件命令
source path/*.sql