mysql基础知识速记

第一章 初识MySQL
数据库：长期存储在计算机内有组织、有共享、统一管理的数据集合，主要包含数据和数据管理的技术
表：二维数据的集合，行是记录，列是字段
数据类型：数据在计算机中的存储格式，整型、浮点、日期等类型
主键：唯一标识表的的字段，要求不能重复
数据库系统：数据库+数据库管理系统+数据库应用程序
SQL语言：DDL+DML+DQL+DCL
数据库访问接口：ODBC+JDBC+ADO.NET+PDO 等
MySQL
小型关系型数据库管理系统：规模小，功能有限，但是速度快，成本低，功能足够
优势：速度、价格免费、容易使用、可移植性强、丰富的接口、支持查询语言、安全性和连接性
MySQL工具比较多，包括客户端和服务端两大类，下面很多个小工具
MySQL workbench是可视化数据库设计软件
学习MySQL的方法：兴趣+夯实基础+新知识+实践

第二章 MySQL的安装配置
官网下载对应的版本
https://www.mysql.com/
一般我们下载msi格式的就可以了
安装配置一键式就可以搞定
启动服务并登陆MySQL的方式：
命令提示符中输入services.msc
找到服务，右键启动即可
登陆数据库：可以采用命令式和mysql安装后自带的command line client两种
命令式：mysql -h hostname -u username -p 其中-h后面的是服务器主机地址本地可以输入localhost或者107.0.0.1，-u后面是登陆数据库的用户名，一般是root，-p后面是登陆密码，出现mysql>样式就表示登陆成功
有时候会弹窗闪一下就退出了，这个时候你需要查看对应的my.ini文件是否存在，右击command line client -属性-找到文件位置
配置path变量
就是需要把bin目录添加到系统的环境变量里，这个比较简单

linux安装mysql后面在讲

第三章 数据库基本操作
创建数据库

通过show databases 可以看到内置有6个数据库
创建数据库命令
create database test_create;

如果想看数据库的定义：
show create database test_create\G

删除数据库
drop database test_create;
show create database test_create\G

第一条命令就是删除数据库的，第二条是是查看数据库的定义，发现会报error，表明数据库已经不存在了。

drop命令要慎重

数据库存储引擎
数据库存储引擎是数据库底层软件组件，DBMS使用数据引擎进行创建、查询、更新和删除数据操作
MySQL的存储引擎：
可以使用show engines查看系统支持的引擎类型
show engines\G

可以看到支持很多种类型的引擎
主要关注support，yes表示支持使用，no不能使用，default表示当前默认存储引擎
常用的是InnoDB、MyISAM、Memory
引擎比较：
功能 MyISAM InnoDB Memory Archive
存储限制 256TB 64TB RAM None
支持事务 no yes no no
支持全文索引 yes no no no
支持数索引 yes yes yes no
支持哈希索引 no no yes no
支持数据缓存 no yes N/A no
支持外键 no yes no no
选择的策略：
提供提交、回滚和崩溃恢复的事物安全（ACID）能力，实现并发，选择InnoDB；
主要用来插入和查询记录，处理效率要求比较高，选择MyISAM；
临时数据存放，数据量不大，不需要较高的安全性，选择Memory；
只有insert和select，如日志信息可以选择Archive；

如何查看默认存储引擎？
除了之前的一种show engines\G
更直接的方法：show variables like’%storage_engine%';
show variables like ‘%storage_engine%’;

第四章 数据表
创建表
在创建表之前，确认是在哪个数据库中进行，使用use +数据库名

创建表，使用create tabel +表名
查看表 show tables;

主键唯一标识表的一条记录，primary key
可以放在定义列的同时，也可以在定义完所有列之后

多字段联合主键就是在id后面加字段即可，
primary key(id,deptId)

外键约束
外键是用来在两个表之间建立联系的
使用foreign key，在定义完列之后
constraint +外键名 foreign key (字段) references +主表名(id)

如

非空约束
not null
唯一性约束
unique
可以定义在列中或者列后
name varchar(25) unique;
neme varchar(25),
constraint s unique(namprie)

unique可以有多个，primary key只能有一个
primary key的列不允许空值，unique的可以
默认约束
default +默认值
sex varchar(25) default ‘男’，

设置表的属性值自动增加
比如每次希望id每新增一个记录自动增加 ,auto_increment只能跟主键
id int(11) primary key auto_increment;

查看表结构
describe +表名，或者desc+表名

显示表详细结构
show create table +表名\G

修改表
当我们觉得创建的表有需要改动的时候，这个部分的作用就发挥出来了
修改表名
alter table tb_emp rename tb_emp1;

修改字段的数据类型
alter table tb_emp modify name varchar(30);

修改字段名
alter table tb_dept1 change location loc varchar(60);

添加字段，first和after参数表示新字段可放置的位置
alter table tb_dept1 add managerId int(10); #添加无约束条件的字段
alter table tb_dept1 add column1 varchar(12) not null; #添加带有约束条件的字段
alter table tb_dept1 add column2 int(11) first;#在表的第一列添加一个字段
alter table tb_dept1 add column3 int(11) after name;#在表的name列后添加一个字段

删除字段
alter table tb_dept1 drop column2; #使用drop删除字段

修改字段的排列位置
alter table tb_dept1 modify column1 varchar(12) first;#把column1放在第一列
alter table tb_dept1 modify column3 int(11) after location;#放在location之后

更改表的存储引擎
先要看看看支持哪些引擎
show engines;

查看表当前的存储引擎
show create table tp_dept1\G

修改引擎
alter table tb_dept1 engine=MyISAM;

如果有异常，查看异常原因，修改对应即可

删除表的外键约束
alter table tb_dept1 drop foreign key fk_emp_dept;

删除数据表
删除没有被关联的表
drop table if exists tb_dept1;#其中if exists表示在删除之前判断表是否存在

删除有关联的表
如果直接删除主表，结果会失败，是因为会破坏表的参照完整性，所以需要先删除关联的字表，在删除父表，但有时候需要保存子表，这个时候只需把外键约束条件删除，在删父表就可以了
alter table tb_emp drop foreign key fk_emp_dept;#先删除外键约束
drop table tb_dept1;#再删除主表
show tables;#查看数据表列表情况

表删除操作需谨慎
无论是删除还是修改，最好提前都备份
外键约束不能跨引擎使用，如果有外键约束，引擎必须相同

第五章 数据类型和运算符
数据类型
数值类型
整数类型Tinyint、smallint、mediumint、int、bigint
浮点型 float、double、decimal

浮点型的三种，超出范围前两个不会警告，decimal会发出警告，通过show warnings;查看，
日期/时间类型
year、time、date、datetime、timestamp

字符串类型
文本字符串和二进制字符串：char、varchar、binary、varbinary、blob、text、enum、set

current_date()表示当前日期
now()表示当前时间
timestamp和datetime的区别除了储存字节和支持的范围不同，最大区别是datetime在存储日期数据时，按实际输入的格式存储，与时区无关，timestamp的存储是以UTC格式保存的

选择数据类型
整数存储，选择整型,高精度小数选择double
如果精度要求比较高的可以选择decimal，比如货币定义，
存储引擎选择：MyISAM最好使用固定长度的数据列代替可变长数据列
InnoDB使用可变长度
枚举可以使用enum，取单值比较合适，如性别
set可取多值，如兴趣爱好
blob主要存储图片，音频
text存储纯文本文件

运算符
算术运算符、比较运算符、逻辑运算符、位运算符
<=>安全的等于，可以用来判断null是否相同
like 用来匹配字符串，可以使用%匹配任何数目字符，_匹配一个字符
Regexp 正则表达式匹配机制，^ 匹配以该字符后面的字符开头的字符串，$匹配以该字符后面的字符结尾的字符串，. 匹配任意一个单字符，[]匹配在括号内的任意字符，* 匹配零个或多个在它前面的字符，

MySQL中特殊字符，必须转义才能
MySQL默认是不区分大小写的，如果要区分，在前面加上Binary即可

第六章 函数
数学函数+字符串函数+日期时间函数+条件判断函数+系统信息函数+加密函数等
数学函数
abs(2)#绝对值函数
pi()#圆周率函数,默认返回六位小数
sqrt(9)#平方根函数
mod(31,8)#求余函数
ceil(-3.35)、ceiling(3.35)#这两个意义相同，都是返回不小于参数的最小整数
floor(4.3)#返回不大于参数值得最大整数值，就是4
rand(),rand(x)#返回值在0-1之间，带x的参数相同时返回的随机数也一样，不带x，随机数每次不一样
round(x)#返回最接近x的整数，四舍五入
round(x,y)#返回最接近参数x的数，保留小数点后y位，y若为负值，则是保留到小数点左边y位
truncate(x,y)#表示截取小数点后y位，负数反过来
sign(x)#返回值的符号，负数为-1，0就是0，整数是1
pow(x,y)/power(x,y)#返回x的y次方
exp(x)#返回的是e的x次方
log(x)#返回x的自然对数
log10(x)#返回x的基数为10的对数。x为负数的话，返回值是null
radians(x)#将角度转换为弧度
degrees(x)#将弧度转换为角度
sin(x)/asin(x)#正弦和反正弦
cos(x)/acos(x)#余弦和反余弦，还有其他的略

字符串函数
char_length(s) #返回字符串字符个数
length(s) #计算字符串字节长度，一个汉字算3个字节，一个数字和字母算一个字节
concat(“s”,“l”)#连接参数产生的字符串，若有null字符串，则最后的结果就是null
concat_ws(“-”,“s”,“l”)#返回带分隔符-的字符串，第一个参数是分隔符，若含有null，则忽略，除非分隔符就是null
insert(s1,x,len,s2)#返回字符串s1，起始位置x，被字符串s2替换长度为len，替换字符串用
lower(s)/lcase(s)#将s字母字符全部转换为小写字母
upper(s)/ucase(s)#全部转换为大写字母
left(s,n)#返回s开始的最左边n个字符
right(s,n)#返回s最右边n个字符
lpad(s1,len,s2)#左填充，如果s1大于len，只截取len长字符串，没有则用s2左边填充
rpad(s1,len,s2)#与lpad类似
ltrim(s)/rtrim(s)/trim(s)#删除空格
trim(s1 from s)#删除字符串s1指定的s2字符串，只删除两端的，中间不删除
repeat(s,n)#返回n组s字符串组合的字符串
space(n)#返回n个空格组成的字符串
replace(s,s1,s2)#使用s2替换s中所有的s1
strcmp(s1,s2)#比较字符串是否相同，相同返回0，s1>s2返回-1，其他情况1
substring(s,n,len)#获取子串
mid(s,n,len)#与substring相同，n代表起始位置
locate(s1,s2)/position(s1 in s2)/instr(s1,s2)#作用相同，返回s2在s1的开始位置
reverse(s)#翻转字符串
elt(n,s1,s2,s…)#返回第n个位置的字符串，没有就返回null
field(s,s1,s2,…)#返回s出现的位置，没有就返回0

日期和时间函数
curdate()/current_date()#返回当前日期，年月日
curdate()+0 #会将日期值转换为数值型
curtime()/current_time#返回当前时间，用法跟日期相似
current_timestamp()/localtime()/now()/sysdate()#均返回当前日期和时间
unix_timestamp(date)#若无参数，则返回unix时间戳，若带date则返回秒数的形式
from_unixtime(date)#将unix时间戳转换为普通格式时间
utc_date()#返回utc日期
utc_time()#返回utc时间
month(date)#获取月份，显示地是数字
monthname(date)#获取月份，显示地是全英文
dayname(date)#获取全英文工作日
dayofweek(date)#返回日期对应的周几索引，1代表周日，2代表周一
weekday(date)#返回日期对应的工作日索引，0代表周一，6代表周日
week(date)#返回一年中的第几周，后面可跟参数代表第一天，0-7，偶数代表周日，奇数为周一
weekofyear(date)#一天位于一年的第几周，一周的第一天是按周一开始的
dayofyear(date)/dayofmonth(date)#获取天数，一年/一月中的第几天
year(date)/quarter(date)/minute(date)/second(date)#年份、季度、分钟、秒数
extract(type from date)#从date中提取type对应的值
time_to_sec(time)#时间转换为秒
sec_to_time(sec)#秒转换为时间
date_add()/adddate()/date_sub()/subdate()/addtime()/subtime()/date_diff()#日期时间的加减
date_format(date,format)#根据format格式化date，参数比较多，用的时候查

条件判断函数
if(exp,v1,v2)#第一个参数是判断式，true则为v1，false为v2
ifnull(v1,v2)#v1不为null则返回v1，否则返回v2
case value when v1 then v2 #vaule 匹配到后面的那个v1值就返回那个对应的v2

系统信息函数
select version();#查看MySQL的版本号
select connection_id();#查看当前用户的连接数
show processlist; #输出当前用户的连接信息
select user(),current_user(),system_user();#这三个都是显示用户名的函数
select last_insert_id();#会显示最后插入记录的id值，比如最后插入的记录id为5，则返回5

加解密函数
对数据进行加密和界面处理，保证重要数据不被别人获取，提升数据库安全性
select password(‘newpd’);#返回的是一行字符串，加密后的密码保存到了用户权限表
select MD5(‘mypwd’);#使用MD5函数加密字符串，输出的是32位十六进制数字的二进制字符串
select encode(‘secret’,‘cry’);#使用cry作为密码，加密secret
select decode(encode(‘secret’,‘cry’),‘cry’);#使用decode进行解码

其他函数
select format(1234.123456,4);#将数字格式化，四舍五入的方式保存4位小数
select conv(16,10,2);#将16从10进制转为2进制
select inet_aton(‘127.0.0.1’);#将网络地址转换为数值网络地址，即127256的3次方+0256的2次方+0*256的1次方+1
select inet_ntoa(3520061480);#将数值网络地址转换为网络地址
get_lock(str,timeout) #使用str字符串得到一个锁，持续时间timeout秒，成功得到锁返回1，超时返回0，发生错误返回null
release_lock(str) #解锁，解开返回1，线程未创建锁返回0，锁不存在返回null
is_free_lock(str) #检查str的锁是否可以使用
is_used_lock(str) #检查str的锁是否正在被使用
select charset(convert(‘string’ using latin1));#使用convert函数将str改为latin1形式的字符集
select cast(100 as char(2)),convert(‘2020-1-1’,time);#cast和convert函数将一个类型的值转换为另一种类型的值

第七章 查询数据
查询数据不仅仅是简单的查询，还需要筛选数据，确定数据以什么样的格式进行显示
基本查询语句
select * from tb_dept1;#查询表所有数据
select id,name from tb_dept1;#查询id，name相关的记录
group by #表示按照指定的字段分组
order by #表示排序，默认升序asc，降序desc
limit #告诉MySQL每次显示查询出来的数据条数

单表查询
select * from fruits; #查询所有字段
select f_id,s_id,f_name from fruits;#查询指定所有字段
select name from fruits; #只查询name字段的数据
select id,name from fruits; #查询多个字段的数据
select f_name,f_price from fruits where f_price = 10.2; #查询指定记录
select s_id,f_name,f_price from fruits where s_id IN(101,102) ORDER BY f_name;#IN相当于一个检索范围，满足条件的会被检索出来
NOT IN（）于IN恰恰相反，表示在检索范围
select f_name,f_price from fruits WHERE f_price BETWEEN 2.00 AND 12.00;价格位于2-12之间，可使用between and
SELECT f_id,f_name FROM fruits WHERE f_name LIKE ‘b%’; #like的通配符%，可以匹配任意长度的字符
SELECT f_id,f_name FROM fruits WHERE f_name LIKE 'y’;#like的通配符，只能匹配一个字符
select f_id,f_name from fruits where f_name is NULL; #查询f_name为空值的记录
select f_id,f_name from fruits where s_id=‘101’ and f_price>=5;#使用and进行多条件查询
类似的还有or，and优先级高于or，可以一起使用
select distinct s_id from fruits;# 使用distinct消除重复的记录值
排序使用order by，如
select f_name,f_price from fruits order by f_name desc;#按照f_name降序排列
分组，group by通常跟集合函数一起使用。如
select s_id,count() as total from fruits group by s_id;
如果要查看分组后的水果名称，可以使用group_concat函数
select s_id,group_concat(f_name) as names from fruits group by s_id;
group by可以和having一起限定显示记录所需满足的条件
如，显示水果种类大于1的分组
select s_id,group_concat(f_name) as names from fruits group by s_id having count(f_name)>1;
select s_id,count()as total from fruits group by s_id with rollup;# with rollup会帮助在结果后面加上一行统计结果