1. 数据库基础1.1 什么是数据库文件保存数据的缺点1) 文件的安全性问题2) 文件不利于数据查询和管理3) 文件不利于存储海量数据4) 文件在程序中控制不方便数据库解决文件存储的缺陷更加高效管理数据数据库水平是衡量程序员水平的重要指标。数据库存储介质磁盘、内存1.2 主流数据库1. SQL Server微软产品.NET程序员常用适合中大型项目2. Oracle甲骨文适合大型项目、复杂业务逻辑并发强闭源收费3. MySQL世界最受欢迎开源数据库中小型互联网项目并发性能好4. PostgreSQL加州大学伯克利开发开源免费商用、科研均可5. SQLite轻量级嵌入式数据库不需要服务进程占用资源极小多用于嵌入式设备、移动端6. H2Java开发嵌入式数据库是一个类库可以直接嵌入Java应用1.3 MySQL基本使用1.3.1 MySQL安装CentOS6.5编译安装MySQL5.6.14CentOS7 yum安装MariaDBWindows安装MySQL5.71.3.2 连接服务器mysql -h 127.0.0.1 -P 3306 -u root -p-h主机地址不写默认127.0.0.1本地-P端口号不写默认3306-u用户名-p密码回车后输入密码成功登录提示Welcome to the MySQL monitor. Commands end with ; or \g.1.3.3 Windows服务器管理winr输入services.msc打开服务管理器可以停止、暂停、重启MySQL服务。1.3.4 服务器、数据库、表关系1. 数据库服务器安装MySQL是一套管理程序一台服务器可以管理多个数据库。2. 数据库DB一个项目一般对应一个数据库。3. 表Table一个数据库里面有多张表保存实体数据。4. 层级Client客户端 → MySQL服务 → 多个数据库DB → 每个DB多张表5. 表行记录、列字段。1.3.5 使用案例-- 创建数据库 create database helloworld; -- 使用数据库 use helloworld; -- 创建表 create table student( id int, name varchar(32), gender varchar(2) ); -- 插入数据 insert into student (id,name,gender) values (1,张三,男); insert into student (id,name,gender) values (2,李四,女); insert into student (id,name,gender) values (3,王五,男); -- 查询全部数据 select * from student;1.3.6 数据逻辑存储行row一条完整记录列column字段代表属性1.4 MySQL架构MySQL跨平台支持Linux、Windows、MacOS。分层1. Client Connectors各种语言驱动JDBC、PHP、Python等2. Connection Pool连接池、权限认证、安全3. SQL InterfaceSQL接口接收语句4. Parser语法解析器词法语法分析5. Optimizer查询优化器生成最优执行计划6. Caches查询缓存7. Pluggable Storage Engines 可插拔存储引擎真正负责读写数据InnoDB、MyISAM、Memory、Archive等8. File System底层磁盘文件系统日志文件redo、undo、binary log等1.5 SQL语句分类分类全称作用关键字DDLData Definition Language数据定义语言定义库、表结构create、drop、alterDMLData Manipulation Language数据操纵语言操作表里数据insert、delete、updateDQLData Query Language数据查询语言DML拆分出来selectDCLData Control Language数据控制语言权限、事务grant、revoke、commit1.6 存储引擎1.6.1 概念存储引擎MySQL如何存储数据、建立索引、更新查询数据的底层实现方式。MySQL支持可插拔多种存储引擎。1.6.2 查看存储引擎show engines;1.6.3 常用引擎对比1. InnoDBMySQL8.0默认✅支持事务、行锁、外键、MVCC适合增删改频繁业务互联网项目首选2. MyISAM❌不支持事务表锁查询速度快支持全文索引适合大量查询很少修改场景崩溃丢失数据3. Memory全部数据放内存断电丢失速度极快4. Archive只支持插入查询压缩存储5. NDB集群引擎2. 库的操作2.1 创建数据库语法CREATE DATABASE [IF NOT EXISTS] db_name [DEFAULT CHARACTER SET charset_name] [DEFAULT COLLATE collation_name];IF NOT EXISTS数据库不存在才创建防止报错CHARACTER SETcharset字符集COLLATE排序/校验规则2.2 案例--最简创建 create database db1; --指定字符集 create database db2 charsetutf8; --字符集collate create database db3 charsetutf8 collate utf8_general_ci;不指定字符集collate使用MySQL服务器默认。项目charset字符集collate排序规则核心作用定义字符的二进制存储编码定义字符串比较、排序的规则解决问题这个字怎么存到数据库字节是什么A和a算不算相等查询、order by怎么排示例值utf8mb4、utf8、latin1utf8mb4_general_ci、utf8mb4_bin从属关系一个charset可以对应多个collatecollate必须依附某个charset不能单独存在2.3 字符集和校验规则collate2.3.1 查看数据库字符集、排序规则show variables like character_set_database; show variables like collation_database;2.3.2 查看全部支持字符集show charset;2.3.3 查看全部collate排序规则show collation;2.3.4 collate对查询、排序的影响1. utf8_general_cicicase insensitive大小写不敏感create database test1 collate utf8_general_ci; use test1; create table person(name varchar(20)); insert into person values(a),(A),(b),(B); select * from person where namea; -- 结果a 和 A 两条都会查出来大小写视为相等2. utf8_bin二进制比较区分大小写create database test2 collate utf8_bin; use test2; create table person(name varchar(20)); insert into person values(a),(A),(b),(B); select * from person where namea; --只会匹配a不会匹配Aorder by排序也会受collate影响ci不区分大小写排序bin严格二进制排序。2.4 操纵数据库2.4.1 查看服务器所有数据库show databases;2.4.2 查看数据库创建语句show create database 数据库名;反引号 包裹库名防止库名和关键字冲突。/*!40100 ... */版本条件注释高版本MySQL才执行。2.4.3 修改数据库只能修改字符集、collate不能修改数据库名字ALTER DATABASE db_name [DEFAULT CHARACTER SET charset_name] [DEFAULT COLLATE collation_name];示例alter database mytest charsetgbk;⚠️只修改数据库设置不会自动修改已经存在的表。2.4.4 删除数据库DROP DATABASE [IF EXISTS] db_name;IF EXISTS存在才删除避免报错删除效果数据库消失对应磁盘文件夹被删除库里面所有表全部级联删除⚠️禁止随意删除数据库2.4.5 备份与恢复mysqldumpmysqldump是外部命令退出mysql终端执行不是sql语句。备份整个数据库mysqldump -P3306 -u root -p -B 数据库名 备份文件.sql示例mysqldump -P3306 -u root -p123456 -B mytest D:/mytest.sql导出的.sql里面保存全部建库、建表、插入数据SQL。恢复source命令mysql内部执行source D:/mysql-5.7.22/mytest.sql;其他备份用法1. 只备份库中几张表不带-Bmysqldump -u root -p 库名 表1 表2 xxx.sql2. 同时备份多个数据库mysqldump -u root -p -B db1 db2 all.sql不带-B参数备份恢复前要手动先create databaseuse数据库再source。2.4.6 查看数据库连接show processlist;作用1. 查看当前哪些用户正在连接MySQL2. 发现陌生连接判断是否被入侵3. 数据库慢的时候可以看连接状态定位问题输出字段Id、User、Host、db、Command、Time、State、Info考试高频易错总结1. charset字符集管文字怎么存collate排序规则管字符串比较、where匹配、order by排序。2. ci大小写不敏感bin二进制区分大小写。3. 修改数据库charset/collate不会更新已有表。4. InnoDB支持事务、行锁MyISAM表锁不支持事务。5. mysqldump是shell命令不是mysql内部sqlsource是mysql内部恢复命令。6. DDL定义结构(create/drop/alter)DML操作数据(insert/update/delete)DQL查询select。7. 删除数据库drop database级联删除全部表谨慎操作。8. MySQL的utf8不是完整utf‑8最多3字节不能存emoji生产优先utf8mb4。3. 表的操作3.1 创建表语法CREATE TABLE table_name ( field1 datatype, field2 datatype, field3 datatype ) character set 字符集 collate 校验规则 engine 存储引擎;参数说明field表的列名datatype列的数据类型character set字符集不指定则继承数据库字符集collate校验规则不指定则继承数据库校验规则engine指定存储引擎3.2 创建表案例create table users ( id int, name varchar(20) comment 用户名, password char(32) comment 密码是32位的md5值, birthday date comment 生日 ) character set utf8 engine MyISAM; MyISAM存储引擎文件说明使用MyISAM引擎建表磁盘会生成3个文件1 users.frm表结构文件2 users.MYD表数据文件3 users.MYI表索引文件对比InnoDB引擎只有 .frm 和 .ibd 文件数据和索引放在ibd文件中。3.3 查看表结构语法desc 表名;示例desc users;输出字段含义字段含义Field字段名字Type字段类型Null是否允许为空Key索引类型Default默认值Extra扩充属性3.4 修改表 ALTER TABLE开发中经常需要新增字段、修改字段类型、删除字段、重命名表、重命名字段。核心语法-- 添加字段 ALTER TABLE tablename ADD (column datatype [DEFAULT expr][,column datatype]...); -- 修改字段类型/长度 ALTER TABLE tablename MODIFY (column datatype [DEFAULT expr][,column datatype]...); -- 删除字段 ALTER TABLE tablename DROP (column); -- 修改表名 ALTER TABLE old_table RENAME [TO] new_table; -- 修改列名必须完整重写类型 ALTER TABLE 表名 CHANGE 旧列名 新列名 数据类型;实操案例1. 插入测试数据insert into users values(1,a,b,1982-01-04),(2,b,c,1984-01-04);2. 新增字段在birthday后面增加图片路径字段alter table users add assets varchar(100) comment 图片路径 after birthday;新增字段不会影响原有数据旧数据新增字段处值为NULL。3. 修改字段长度把name长度改为60alter table users modify name varchar(60);4. 删除字段 ⚠️危险字段和对应数据全部丢失alter table users drop password;5. 修改表名alter table users rename to employee;to关键字可以省略。6. 修改列名CHANGE语法新字段必须完整定义不能只写名字alter table employee change name xingming varchar(60);3.5 删除表语法DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ...IF EXISTS如果表不存在不会报错推荐写在生产脚本TEMPORARY只删除临时表示例drop table if exists t1;面试重点总结1. MyISAM 3个文件.frm结构、.MYD数据、.MYI索引InnoDB.frm、.ibd2. modify改字段类型长度change改列名必须重写类型3. add ... after 列名控制新增字段位置4. drop删除字段数据直接丢失不可恢复5. 删除表建议带上if exists避免脚本执行报错4. 数据类型4.1 数据类型总览分类类型说明数值类型BIT(M)位类型M位数1‑64TINYINT [UNSIGNED]1字节很小整数SMALLINT [UNSIGNED]2字节INT [UNSIGNED]4字节最常用BIGINT [UNSIGNED]8字节大整数FLOAT(M,D)单精度浮点数DOUBLE(M,D)双精度浮点数DECIMAL(M,D)定点数高精度财务推荐文本二进制CHAR(size)定长字符串VARCHAR(size)可变长字符串BLOB二进制原始数据TEXT大文本时间日期DATE日期 yyyy‑mm‑ddDATETIME日期时间 yyyy‑mm‑dd hh:mm:ssTIMESTAMP时间戳4字节自动更新字符串枚举ENUM单选枚举SET多选集合4.2 数值类型整数范围表类型字节有符号最小值有符号最大值无符号最小值无符号最大值TINYINT1-1281270255SMALLINT2-3276832767065535MEDIUMINT3-83886088388607016777215INT4-2147483648214748364704294967295BIGINT8-92233720368547758089223372036854775807018446744073709551615默认是有符号加上UNSIGNED变成无符号只能存非负数。⚠️生产建议尽量少用UNSIGNED数据存不下直接升级为BIGINT。TINYINT越界测试create table tt1(num tinyint); insert into tt1 values(1); insert into tt1 values(128); --越界报错 Out of range无符号示例create table tt2(num tinyint unsigned); insert into tt2 values(-1); --报错不能负数 insert into tt2 values(255); --合法4.2.1 BIT位类型语法bit(M)M范围1‑64默认M1select查询bit默认显示ASCII字符不直接显示数字。适合存储0/1状态。create table tt4(id int, a bit(8)); insert into tt4 values(10,10); select * from tt4; --bit字段显示字符看不到数字10 --bit(1)只存0或1节省空间性别、开关状态 create table tt5(gender bit(1)); insert into tt5 values(0); insert into tt5 values(1); insert into tt5 values(2); --越界报错4.2.2 小数类型FLOAT语法float(M,D) [unsigned]M总显示长度D小数位数占用4字节会四舍五入精度大约7位不适合财务。float(4,2)范围 -99.99 ~ 99.99unsigned则0‑99.99create table tt6(id int, salary float(4,2)); insert into tt6 values(100,-99.99); insert into tt6 values(101,-99.991); --四舍五入保存-99.99DECIMAL定点数财务必用语法decimal(M,D) [unsigned]高精度不会丢失精度金额、账单必须用decimal。decimal(5,2)总长度5位小数占2位范围 -999.99 ~999.99decimal 整数最大位数M为65支持小数最大位数D为30。如果D被省略默认为0。如果M被省略默认为10。create table tt8( id int, salary float(10,8), salary2 decimal(10,8) ); insert into tt8 values(100,23.12345612, 23.12345612); --float会丢失精度decimal保持准确float近似存储decimal精确存储。涉及钱一定用decimal4.3 字符串类型 char vs varcharCHAR(L) 定长字符串• 固定长度L最多255个字符• 数据不足L长度内存仍然占满L查询速度快浪费空间。适合身份证、手机号、md5密码长度固定数据。create table tt9(id int,name char(2)); insert into tt9 values(100,ab); insert into tt9 values(101,中国);VARCHAR(L) 可变长字符串• L最多字符数实际字节受字符集限制最大长度65535个字节utf8一个汉字占3字节。• 按需占用空间节省存储性能略低于char。适合姓名、地址长度变化的数据。create table tt10(id int,name varchar(6)); insert into tt10 values(100,hello); insert into tt10 values(100,我爱你中国);char与varchar对比总结情况char(4)varchar(4)存储abcd占4字符占41字节存储A占4字符占11字节✅选型1. 长度固定 → char手机号、md5效率高浪费空间无所谓2. 长度变化大 → varchar节省磁盘3. char最大255字符varchar最大受行大小限制。4.4 日期时间类型类型字节格式说明DATE3yyyy‑mm‑dd只存日期DATETIME8yyyy‑mm‑dd hh:mm:ss日期时间范围1000‑9999年TIMESTAMP4yyyy‑mm‑dd hh:mm:ss时间戳插入更新自动填充当前时间1970起始create table birthday(t1 date, t2 datetime, t3 timestamp); insert into birthday(t1,t2) values(1997‑7‑1,2008‑8‑8 12:1:1); --t3 timestamp 不赋值自动填入当前时间 update birthday set t12000‑1‑1; --更新行timestamp会自动刷新为当前时间业务小提示只需要日期用date完整时间用datetimetimestamp会自动更新适合记录修改时间。4.5 ENUM 与 SETENUM 单选枚举只能选给定列表其中一个值底层存储数字。enum(男,女);SET 多选集合可以选列表中0个、1个或者多个底层位图存储最多64个选项。set(登山,游泳,篮球,武术);案例create table votes( username varchar(30), hobby set(登山,游泳,篮球,武术), gender enum(男,女) ); insert into votes values(雷锋,登山,武术,男); insert into votes values(Juse,登山,武术,2); --enum数字2代表女⚠️注意where hobby登山 只能匹配只选登山的记录同时选登山武术查不出来。查询集合包含某一项使用find_in_set()函数--查询爱好包含登山的所有记录 select * from votes where find_in_set(登山, hobby);find_in_set(sub,str_list)找到返回下标找不到返回0。select find_in_set(a,a,b,c); --返回1 select find_in_set(a,b,a,b,c); --返回0,只能查找一项 select find_in_set(d,a,b,c); --返回0面试重点总结1. 整数类型tinyint(1字节) ~ bigint(8字节)unsigned无符号不推荐滥用。2. bit类型查询显示ASCII字符适合0/1开关。3. 金额绝对不能用float/double必须用decimal定点数4. char定长varchar变长char上限255字符。5. timestamp会自动更新时间datetime不会自动。6. enum单选set多选set查询包含某一项要用find_in_set()。5. 表的约束作用数据类型约束比较单一约束是额外校验规则从业务逻辑层面保证存入数据库的数据合法、正确。常见约束null/not null、default、comment、zerofill、primary key、auto_increment、unique key、foreign key5.1 空属性 NULL / NOT NULL知识点1. NULL允许为空系统默认该字段可以不填数据2. NOT NULL不为空该字段必须填入数据不能是NULL3. 运算大坑NULL参与任何数学运算结果永远为NULLselect 1null; -- 结果为NULL得不到14. 开发规范业务中尽量设置 NOT NULL原因空值无法正常参与运算、索引效率差业务上很多字段本来就不应该为空班级名、姓名示例代码-- 创建班级表班级名称、教室不能为空 create table myclass( class_name varchar(20) not null, class_room varchar(10) not null ); -- 查看表结构 desc myclass; -- 报错缺少class_room字段不允许为空 insert into myclass(class_name) values(class1); -- ERROR 1364 (HY000): Field class_room doesnt have a default value5.2 默认值 DEFAULT知识点1. 默认值插入数据不给该字段传值时自动填入预设的默认数据2. 只有设置了default的字段插入语句才可以省略该列3. 如果手动传入数值优先使用传入的值不会触发默认值示例代码create table tt10 ( name varchar(20) not null, age tinyint unsigned default 0, sex char(2) default 男 ); desc tt10; -- 只插入nameage、sex自动使用默认值 0、男 insert into tt10(name) values(zhangsan); select * from tt10;查询结果nameagesexzhangsan0男注意not null 和 default一般不同时写。有默认值就算不传字段也不会是空不需要not null。5.3 列注释 COMMENT知识点1. comment 不影响任何表逻辑仅用来给字段写中文说明给开发/DBA阅读2. desc 表名 看不到注释3. 使用show create table 表名\G才能完整查看 建表语句注释示例代码create table tt12 ( name varchar(20) not null comment 姓名, age tinyint unsigned default 0 comment 年龄, sex char(2) default 男 comment 性别 ); -- 完整查看建表语句显示注释 show create table tt12\G5.4 zerofill 零填充知识点1. 只作用于数字类型2. int(5)括号内数字本身没有意义只有搭配zerofill才生效3. 功能查询展示的时候数字前面补0补齐到设定长度⚠重点只是显示效果数据库底层存储仍然是原始数字不会改变存储的值4. 添加zerofill字段会自动带上unsigned无符号属性不能存负数示例代码-- 修改a字段5位长度零填充 alter table tt3 change a int(5) unsigned zerofill; insert into tt3 values(1,2); select * from tt3; -- 查询输出00001 , 2 -- 底层存储依旧是数字1hex(a)验证存储值不变 select a,hex(a) from tt3;5.5 主键 primary keyPRI知识点1. 主键约束2条硬性规则✅值不能重复唯一 ✅不能为NULL非空2. 一张表最多只能有1个主键3. 主键字段业务首选整数类型查询、关联性能更好4. 分类单字段主键、复合主键多字段联合主键复合主键多个字段合在一起作为主键组合整体不能重复单个字段可以重复①单主键示例-- 创建时直接指定主键 create table tt13 ( id int unsigned primary key comment 学号不能为空, name varchar(20) not null ); desc tt13; -- 重复主键插入直接报错 insert into tt13 values(1,aaa); insert into tt13 values(1,aaa); -- ERROR 1062 (23000): Duplicate entry 1 for key PRIMARY -- 表建好之后追加主键 alter table 表名 add primary key(字段列表); -- 删除主键不需要写字段名一张表只有一个主键 alter table tt13 drop primary key;②复合主键示例create table tt14( id int unsigned, course char(10) comment 课程代码, score tinyint unsigned default 60 comment 成绩, primary key(id,course) -- id课程 联合复合主键 ); desc tt14; insert into tt14 (id,course)values(1,123); -- 组合完全一样主键冲突报错 insert into tt14 (id,course)values(1,123);5.6 自增长 auto_increment知识点1. 作用插入数据不给值数据库自动生成一个1递增的整数2. 强制前提字段本身必须是索引一般搭配primary key主键字段类型必须是整数一张表最多只能设置1个自增长列3. 自增规则从当前表里已有最大ID1生成新ID4. 获取刚刚插入的自增IDselect last_insert_id();批量插入时返回第一条生成的自增id示例代码create table tt21( id int unsigned primary key auto_increment, name varchar(10) not null default ); -- 不给id自动自增 insert into tt21(name) values(a); insert into tt21(name) values(b); select * from tt21; -- id自动变成12 -- 获取上一次自增id select last_insert_id();5.7 唯一键 unique keyUNI知识点1. 作用保证字段业务不重复手机号、邮箱、身份证2. 和主键对比核心区别约束能否NULL一张表数量primary key❌不允许为空只能1个unique key✅允许NULLNULL之间不做重复校验可以多个业务经验主键用无业务含义自增ID唯一键用来约束业务字段不能重复邮箱、身份证示例代码create table student ( id char(10) unique comment 学号不能重复但可以为空, name varchar(10) ); insert into student(id,name) values(01,aaa); insert into student(id,name) values(01,bbb); -- 重复报错 insert into student(id,name) values(null,bbb); -- NULL可以多次插入 select * from student;5.8 外键 foreign key知识点1. 作用约束两张表的数据关联性保证从表数据一定在主表存在杜绝脏数据主表被引用的表班级表从表设置外键的表学生表2. 语法foreign key(从表字段) references 主表名(主表主键字段)3. 约束规则1从表外键的值要么等于主表已经存在的值2从表外键的值要么直接为NULL3主表被从表引用的数据不能随意删除4主表被引用列必须是主键或者唯一键开发提醒MySQL外键是数据库层校验大型互联网项目一般不在数据库建立外键业务代码层面做逻辑校验示例代码-- 1.先建【主表】班级表 create table myclass ( id int primary key, name varchar(30) not null comment 班级名 ); -- 2.再建【从表】学生表设置外键关联班级id create table stu ( id int primary key, name varchar(30) not null comment 学生名, class_id int, foreign key (class_id) references myclass(id) ); -- 主表插入班级 insert into myclass values(10,C大牛班),(20,java大神班); -- 合法班级10、20主表里存在 insert into stu values(100,张三,10),(101,李四,20); -- ❌报错班级30不存在外键约束拦截 insert into stu values(102,wangwu,30); -- ✅合法外键给NULL学生暂时没有分配班级 insert into stu values(102,wangwu,null);5.9 综合建表案例商店业务三张表业务说明商品表、客户表、购买订单表主外键关联约束需求清单1.每张表设置主键、自增2.客户姓名不能为空3.邮箱不能重复unique唯一键4.性别只能男 / 女enum枚举-- 创建数据库 create database if not exists bit32mall default character set utf8 ; use bit32mall; -- 商品表 goods create table if not exists goods ( goods_id int primary key auto_increment comment 商品编号, goods_name varchar(32) not null comment 商品名称, unitprice int not null default 0 comment 单价单位分, category varchar(12) comment 商品分类, provider varchar(64) not null comment 供应商名称 ); -- 客户表 customer create table if not exists customer ( customer_id int primary key auto_increment comment 客户编号, name varchar(32) not null comment 客户姓名, address varchar(256) comment 客户地址, email varchar(64) unique key comment 电子邮箱, sex enum(男,女) not null comment 性别, card_id char(18) unique key comment 身份证 ); -- 购买订单 purchase从表双外键 create table if not exists purchase ( order_id int primary key auto_increment comment 订单号, customer_id int comment 客户编号, goods_id int comment 商品编号, nums int default 0 comment 购买数量, foreign key (customer_id) references customer(customer_id), foreign key (goods_id) references goods(goods_id) );约束面试重点总结1. not null字段禁止为空default不传值自动填充预设内容2. zerofill仅查询显示补零存储数值不变自动unsigned无符号3. primary key主键非空唯一一张表只能1个主键支持复合主键4. auto_increment自增必须绑定整数索引主键单表只能1个5. unique key唯一键可以多个可以存NULL只约束业务字段不重复6. foreign key外键关联两张表从表数据必须在主表存在或者NULL管控数据完整性7. comment注释仅文档作用不参与任何校验逻辑
1. 数据库基础1.1 什么是数据库文件保存数据的缺点1) 文件的安全性问题2) 文件不利于数据查询和管理3) 文件不利于存储海量数据4) 文件在程序中控制不方便数据库解决文件存储的缺陷更加高效管理数据数据库水平是衡量程序员水平的重要指标。数据库存储介质磁盘、内存1.2 主流数据库1. SQL Server微软产品.NET程序员常用适合中大型项目2. Oracle甲骨文适合大型项目、复杂业务逻辑并发强闭源收费3. MySQL世界最受欢迎开源数据库中小型互联网项目并发性能好4. PostgreSQL加州大学伯克利开发开源免费商用、科研均可5. SQLite轻量级嵌入式数据库不需要服务进程占用资源极小多用于嵌入式设备、移动端6. H2Java开发嵌入式数据库是一个类库可以直接嵌入Java应用1.3 MySQL基本使用1.3.1 MySQL安装CentOS6.5编译安装MySQL5.6.14CentOS7 yum安装MariaDBWindows安装MySQL5.71.3.2 连接服务器mysql -h 127.0.0.1 -P 3306 -u root -p-h主机地址不写默认127.0.0.1本地-P端口号不写默认3306-u用户名-p密码回车后输入密码成功登录提示Welcome to the MySQL monitor. Commands end with ; or \g.1.3.3 Windows服务器管理winr输入services.msc打开服务管理器可以停止、暂停、重启MySQL服务。1.3.4 服务器、数据库、表关系1. 数据库服务器安装MySQL是一套管理程序一台服务器可以管理多个数据库。2. 数据库DB一个项目一般对应一个数据库。3. 表Table一个数据库里面有多张表保存实体数据。4. 层级Client客户端 → MySQL服务 → 多个数据库DB → 每个DB多张表5. 表行记录、列字段。1.3.5 使用案例-- 创建数据库 create database helloworld; -- 使用数据库 use helloworld; -- 创建表 create table student( id int, name varchar(32), gender varchar(2) ); -- 插入数据 insert into student (id,name,gender) values (1,张三,男); insert into student (id,name,gender) values (2,李四,女); insert into student (id,name,gender) values (3,王五,男); -- 查询全部数据 select * from student;1.3.6 数据逻辑存储行row一条完整记录列column字段代表属性1.4 MySQL架构MySQL跨平台支持Linux、Windows、MacOS。分层1. Client Connectors各种语言驱动JDBC、PHP、Python等2. Connection Pool连接池、权限认证、安全3. SQL InterfaceSQL接口接收语句4. Parser语法解析器词法语法分析5. Optimizer查询优化器生成最优执行计划6. Caches查询缓存7. Pluggable Storage Engines 可插拔存储引擎真正负责读写数据InnoDB、MyISAM、Memory、Archive等8. File System底层磁盘文件系统日志文件redo、undo、binary log等1.5 SQL语句分类分类全称作用关键字DDLData Definition Language数据定义语言定义库、表结构create、drop、alterDMLData Manipulation Language数据操纵语言操作表里数据insert、delete、updateDQLData Query Language数据查询语言DML拆分出来selectDCLData Control Language数据控制语言权限、事务grant、revoke、commit1.6 存储引擎1.6.1 概念存储引擎MySQL如何存储数据、建立索引、更新查询数据的底层实现方式。MySQL支持可插拔多种存储引擎。1.6.2 查看存储引擎show engines;1.6.3 常用引擎对比1. InnoDBMySQL8.0默认✅支持事务、行锁、外键、MVCC适合增删改频繁业务互联网项目首选2. MyISAM❌不支持事务表锁查询速度快支持全文索引适合大量查询很少修改场景崩溃丢失数据3. Memory全部数据放内存断电丢失速度极快4. Archive只支持插入查询压缩存储5. NDB集群引擎2. 库的操作2.1 创建数据库语法CREATE DATABASE [IF NOT EXISTS] db_name [DEFAULT CHARACTER SET charset_name] [DEFAULT COLLATE collation_name];IF NOT EXISTS数据库不存在才创建防止报错CHARACTER SETcharset字符集COLLATE排序/校验规则2.2 案例--最简创建 create database db1; --指定字符集 create database db2 charsetutf8; --字符集collate create database db3 charsetutf8 collate utf8_general_ci;不指定字符集collate使用MySQL服务器默认。项目charset字符集collate排序规则核心作用定义字符的二进制存储编码定义字符串比较、排序的规则解决问题这个字怎么存到数据库字节是什么A和a算不算相等查询、order by怎么排示例值utf8mb4、utf8、latin1utf8mb4_general_ci、utf8mb4_bin从属关系一个charset可以对应多个collatecollate必须依附某个charset不能单独存在2.3 字符集和校验规则collate2.3.1 查看数据库字符集、排序规则show variables like character_set_database; show variables like collation_database;2.3.2 查看全部支持字符集show charset;2.3.3 查看全部collate排序规则show collation;2.3.4 collate对查询、排序的影响1. utf8_general_cicicase insensitive大小写不敏感create database test1 collate utf8_general_ci; use test1; create table person(name varchar(20)); insert into person values(a),(A),(b),(B); select * from person where namea; -- 结果a 和 A 两条都会查出来大小写视为相等2. utf8_bin二进制比较区分大小写create database test2 collate utf8_bin; use test2; create table person(name varchar(20)); insert into person values(a),(A),(b),(B); select * from person where namea; --只会匹配a不会匹配Aorder by排序也会受collate影响ci不区分大小写排序bin严格二进制排序。2.4 操纵数据库2.4.1 查看服务器所有数据库show databases;2.4.2 查看数据库创建语句show create database 数据库名;反引号 包裹库名防止库名和关键字冲突。/*!40100 ... */版本条件注释高版本MySQL才执行。2.4.3 修改数据库只能修改字符集、collate不能修改数据库名字ALTER DATABASE db_name [DEFAULT CHARACTER SET charset_name] [DEFAULT COLLATE collation_name];示例alter database mytest charsetgbk;⚠️只修改数据库设置不会自动修改已经存在的表。2.4.4 删除数据库DROP DATABASE [IF EXISTS] db_name;IF EXISTS存在才删除避免报错删除效果数据库消失对应磁盘文件夹被删除库里面所有表全部级联删除⚠️禁止随意删除数据库2.4.5 备份与恢复mysqldumpmysqldump是外部命令退出mysql终端执行不是sql语句。备份整个数据库mysqldump -P3306 -u root -p -B 数据库名 备份文件.sql示例mysqldump -P3306 -u root -p123456 -B mytest D:/mytest.sql导出的.sql里面保存全部建库、建表、插入数据SQL。恢复source命令mysql内部执行source D:/mysql-5.7.22/mytest.sql;其他备份用法1. 只备份库中几张表不带-Bmysqldump -u root -p 库名 表1 表2 xxx.sql2. 同时备份多个数据库mysqldump -u root -p -B db1 db2 all.sql不带-B参数备份恢复前要手动先create databaseuse数据库再source。2.4.6 查看数据库连接show processlist;作用1. 查看当前哪些用户正在连接MySQL2. 发现陌生连接判断是否被入侵3. 数据库慢的时候可以看连接状态定位问题输出字段Id、User、Host、db、Command、Time、State、Info考试高频易错总结1. charset字符集管文字怎么存collate排序规则管字符串比较、where匹配、order by排序。2. ci大小写不敏感bin二进制区分大小写。3. 修改数据库charset/collate不会更新已有表。4. InnoDB支持事务、行锁MyISAM表锁不支持事务。5. mysqldump是shell命令不是mysql内部sqlsource是mysql内部恢复命令。6. DDL定义结构(create/drop/alter)DML操作数据(insert/update/delete)DQL查询select。7. 删除数据库drop database级联删除全部表谨慎操作。8. MySQL的utf8不是完整utf‑8最多3字节不能存emoji生产优先utf8mb4。3. 表的操作3.1 创建表语法CREATE TABLE table_name ( field1 datatype, field2 datatype, field3 datatype ) character set 字符集 collate 校验规则 engine 存储引擎;参数说明field表的列名datatype列的数据类型character set字符集不指定则继承数据库字符集collate校验规则不指定则继承数据库校验规则engine指定存储引擎3.2 创建表案例create table users ( id int, name varchar(20) comment 用户名, password char(32) comment 密码是32位的md5值, birthday date comment 生日 ) character set utf8 engine MyISAM; MyISAM存储引擎文件说明使用MyISAM引擎建表磁盘会生成3个文件1 users.frm表结构文件2 users.MYD表数据文件3 users.MYI表索引文件对比InnoDB引擎只有 .frm 和 .ibd 文件数据和索引放在ibd文件中。3.3 查看表结构语法desc 表名;示例desc users;输出字段含义字段含义Field字段名字Type字段类型Null是否允许为空Key索引类型Default默认值Extra扩充属性3.4 修改表 ALTER TABLE开发中经常需要新增字段、修改字段类型、删除字段、重命名表、重命名字段。核心语法-- 添加字段 ALTER TABLE tablename ADD (column datatype [DEFAULT expr][,column datatype]...); -- 修改字段类型/长度 ALTER TABLE tablename MODIFY (column datatype [DEFAULT expr][,column datatype]...); -- 删除字段 ALTER TABLE tablename DROP (column); -- 修改表名 ALTER TABLE old_table RENAME [TO] new_table; -- 修改列名必须完整重写类型 ALTER TABLE 表名 CHANGE 旧列名 新列名 数据类型;实操案例1. 插入测试数据insert into users values(1,a,b,1982-01-04),(2,b,c,1984-01-04);2. 新增字段在birthday后面增加图片路径字段alter table users add assets varchar(100) comment 图片路径 after birthday;新增字段不会影响原有数据旧数据新增字段处值为NULL。3. 修改字段长度把name长度改为60alter table users modify name varchar(60);4. 删除字段 ⚠️危险字段和对应数据全部丢失alter table users drop password;5. 修改表名alter table users rename to employee;to关键字可以省略。6. 修改列名CHANGE语法新字段必须完整定义不能只写名字alter table employee change name xingming varchar(60);3.5 删除表语法DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ...IF EXISTS如果表不存在不会报错推荐写在生产脚本TEMPORARY只删除临时表示例drop table if exists t1;面试重点总结1. MyISAM 3个文件.frm结构、.MYD数据、.MYI索引InnoDB.frm、.ibd2. modify改字段类型长度change改列名必须重写类型3. add ... after 列名控制新增字段位置4. drop删除字段数据直接丢失不可恢复5. 删除表建议带上if exists避免脚本执行报错4. 数据类型4.1 数据类型总览分类类型说明数值类型BIT(M)位类型M位数1‑64TINYINT [UNSIGNED]1字节很小整数SMALLINT [UNSIGNED]2字节INT [UNSIGNED]4字节最常用BIGINT [UNSIGNED]8字节大整数FLOAT(M,D)单精度浮点数DOUBLE(M,D)双精度浮点数DECIMAL(M,D)定点数高精度财务推荐文本二进制CHAR(size)定长字符串VARCHAR(size)可变长字符串BLOB二进制原始数据TEXT大文本时间日期DATE日期 yyyy‑mm‑ddDATETIME日期时间 yyyy‑mm‑dd hh:mm:ssTIMESTAMP时间戳4字节自动更新字符串枚举ENUM单选枚举SET多选集合4.2 数值类型整数范围表类型字节有符号最小值有符号最大值无符号最小值无符号最大值TINYINT1-1281270255SMALLINT2-3276832767065535MEDIUMINT3-83886088388607016777215INT4-2147483648214748364704294967295BIGINT8-92233720368547758089223372036854775807018446744073709551615默认是有符号加上UNSIGNED变成无符号只能存非负数。⚠️生产建议尽量少用UNSIGNED数据存不下直接升级为BIGINT。TINYINT越界测试create table tt1(num tinyint); insert into tt1 values(1); insert into tt1 values(128); --越界报错 Out of range无符号示例create table tt2(num tinyint unsigned); insert into tt2 values(-1); --报错不能负数 insert into tt2 values(255); --合法4.2.1 BIT位类型语法bit(M)M范围1‑64默认M1select查询bit默认显示ASCII字符不直接显示数字。适合存储0/1状态。create table tt4(id int, a bit(8)); insert into tt4 values(10,10); select * from tt4; --bit字段显示字符看不到数字10 --bit(1)只存0或1节省空间性别、开关状态 create table tt5(gender bit(1)); insert into tt5 values(0); insert into tt5 values(1); insert into tt5 values(2); --越界报错4.2.2 小数类型FLOAT语法float(M,D) [unsigned]M总显示长度D小数位数占用4字节会四舍五入精度大约7位不适合财务。float(4,2)范围 -99.99 ~ 99.99unsigned则0‑99.99create table tt6(id int, salary float(4,2)); insert into tt6 values(100,-99.99); insert into tt6 values(101,-99.991); --四舍五入保存-99.99DECIMAL定点数财务必用语法decimal(M,D) [unsigned]高精度不会丢失精度金额、账单必须用decimal。decimal(5,2)总长度5位小数占2位范围 -999.99 ~999.99decimal 整数最大位数M为65支持小数最大位数D为30。如果D被省略默认为0。如果M被省略默认为10。create table tt8( id int, salary float(10,8), salary2 decimal(10,8) ); insert into tt8 values(100,23.12345612, 23.12345612); --float会丢失精度decimal保持准确float近似存储decimal精确存储。涉及钱一定用decimal4.3 字符串类型 char vs varcharCHAR(L) 定长字符串• 固定长度L最多255个字符• 数据不足L长度内存仍然占满L查询速度快浪费空间。适合身份证、手机号、md5密码长度固定数据。create table tt9(id int,name char(2)); insert into tt9 values(100,ab); insert into tt9 values(101,中国);VARCHAR(L) 可变长字符串• L最多字符数实际字节受字符集限制最大长度65535个字节utf8一个汉字占3字节。• 按需占用空间节省存储性能略低于char。适合姓名、地址长度变化的数据。create table tt10(id int,name varchar(6)); insert into tt10 values(100,hello); insert into tt10 values(100,我爱你中国);char与varchar对比总结情况char(4)varchar(4)存储abcd占4字符占41字节存储A占4字符占11字节✅选型1. 长度固定 → char手机号、md5效率高浪费空间无所谓2. 长度变化大 → varchar节省磁盘3. char最大255字符varchar最大受行大小限制。4.4 日期时间类型类型字节格式说明DATE3yyyy‑mm‑dd只存日期DATETIME8yyyy‑mm‑dd hh:mm:ss日期时间范围1000‑9999年TIMESTAMP4yyyy‑mm‑dd hh:mm:ss时间戳插入更新自动填充当前时间1970起始create table birthday(t1 date, t2 datetime, t3 timestamp); insert into birthday(t1,t2) values(1997‑7‑1,2008‑8‑8 12:1:1); --t3 timestamp 不赋值自动填入当前时间 update birthday set t12000‑1‑1; --更新行timestamp会自动刷新为当前时间业务小提示只需要日期用date完整时间用datetimetimestamp会自动更新适合记录修改时间。4.5 ENUM 与 SETENUM 单选枚举只能选给定列表其中一个值底层存储数字。enum(男,女);SET 多选集合可以选列表中0个、1个或者多个底层位图存储最多64个选项。set(登山,游泳,篮球,武术);案例create table votes( username varchar(30), hobby set(登山,游泳,篮球,武术), gender enum(男,女) ); insert into votes values(雷锋,登山,武术,男); insert into votes values(Juse,登山,武术,2); --enum数字2代表女⚠️注意where hobby登山 只能匹配只选登山的记录同时选登山武术查不出来。查询集合包含某一项使用find_in_set()函数--查询爱好包含登山的所有记录 select * from votes where find_in_set(登山, hobby);find_in_set(sub,str_list)找到返回下标找不到返回0。select find_in_set(a,a,b,c); --返回1 select find_in_set(a,b,a,b,c); --返回0,只能查找一项 select find_in_set(d,a,b,c); --返回0面试重点总结1. 整数类型tinyint(1字节) ~ bigint(8字节)unsigned无符号不推荐滥用。2. bit类型查询显示ASCII字符适合0/1开关。3. 金额绝对不能用float/double必须用decimal定点数4. char定长varchar变长char上限255字符。5. timestamp会自动更新时间datetime不会自动。6. enum单选set多选set查询包含某一项要用find_in_set()。5. 表的约束作用数据类型约束比较单一约束是额外校验规则从业务逻辑层面保证存入数据库的数据合法、正确。常见约束null/not null、default、comment、zerofill、primary key、auto_increment、unique key、foreign key5.1 空属性 NULL / NOT NULL知识点1. NULL允许为空系统默认该字段可以不填数据2. NOT NULL不为空该字段必须填入数据不能是NULL3. 运算大坑NULL参与任何数学运算结果永远为NULLselect 1null; -- 结果为NULL得不到14. 开发规范业务中尽量设置 NOT NULL原因空值无法正常参与运算、索引效率差业务上很多字段本来就不应该为空班级名、姓名示例代码-- 创建班级表班级名称、教室不能为空 create table myclass( class_name varchar(20) not null, class_room varchar(10) not null ); -- 查看表结构 desc myclass; -- 报错缺少class_room字段不允许为空 insert into myclass(class_name) values(class1); -- ERROR 1364 (HY000): Field class_room doesnt have a default value5.2 默认值 DEFAULT知识点1. 默认值插入数据不给该字段传值时自动填入预设的默认数据2. 只有设置了default的字段插入语句才可以省略该列3. 如果手动传入数值优先使用传入的值不会触发默认值示例代码create table tt10 ( name varchar(20) not null, age tinyint unsigned default 0, sex char(2) default 男 ); desc tt10; -- 只插入nameage、sex自动使用默认值 0、男 insert into tt10(name) values(zhangsan); select * from tt10;查询结果nameagesexzhangsan0男注意not null 和 default一般不同时写。有默认值就算不传字段也不会是空不需要not null。5.3 列注释 COMMENT知识点1. comment 不影响任何表逻辑仅用来给字段写中文说明给开发/DBA阅读2. desc 表名 看不到注释3. 使用show create table 表名\G才能完整查看 建表语句注释示例代码create table tt12 ( name varchar(20) not null comment 姓名, age tinyint unsigned default 0 comment 年龄, sex char(2) default 男 comment 性别 ); -- 完整查看建表语句显示注释 show create table tt12\G5.4 zerofill 零填充知识点1. 只作用于数字类型2. int(5)括号内数字本身没有意义只有搭配zerofill才生效3. 功能查询展示的时候数字前面补0补齐到设定长度⚠重点只是显示效果数据库底层存储仍然是原始数字不会改变存储的值4. 添加zerofill字段会自动带上unsigned无符号属性不能存负数示例代码-- 修改a字段5位长度零填充 alter table tt3 change a int(5) unsigned zerofill; insert into tt3 values(1,2); select * from tt3; -- 查询输出00001 , 2 -- 底层存储依旧是数字1hex(a)验证存储值不变 select a,hex(a) from tt3;5.5 主键 primary keyPRI知识点1. 主键约束2条硬性规则✅值不能重复唯一 ✅不能为NULL非空2. 一张表最多只能有1个主键3. 主键字段业务首选整数类型查询、关联性能更好4. 分类单字段主键、复合主键多字段联合主键复合主键多个字段合在一起作为主键组合整体不能重复单个字段可以重复①单主键示例-- 创建时直接指定主键 create table tt13 ( id int unsigned primary key comment 学号不能为空, name varchar(20) not null ); desc tt13; -- 重复主键插入直接报错 insert into tt13 values(1,aaa); insert into tt13 values(1,aaa); -- ERROR 1062 (23000): Duplicate entry 1 for key PRIMARY -- 表建好之后追加主键 alter table 表名 add primary key(字段列表); -- 删除主键不需要写字段名一张表只有一个主键 alter table tt13 drop primary key;②复合主键示例create table tt14( id int unsigned, course char(10) comment 课程代码, score tinyint unsigned default 60 comment 成绩, primary key(id,course) -- id课程 联合复合主键 ); desc tt14; insert into tt14 (id,course)values(1,123); -- 组合完全一样主键冲突报错 insert into tt14 (id,course)values(1,123);5.6 自增长 auto_increment知识点1. 作用插入数据不给值数据库自动生成一个1递增的整数2. 强制前提字段本身必须是索引一般搭配primary key主键字段类型必须是整数一张表最多只能设置1个自增长列3. 自增规则从当前表里已有最大ID1生成新ID4. 获取刚刚插入的自增IDselect last_insert_id();批量插入时返回第一条生成的自增id示例代码create table tt21( id int unsigned primary key auto_increment, name varchar(10) not null default ); -- 不给id自动自增 insert into tt21(name) values(a); insert into tt21(name) values(b); select * from tt21; -- id自动变成12 -- 获取上一次自增id select last_insert_id();5.7 唯一键 unique keyUNI知识点1. 作用保证字段业务不重复手机号、邮箱、身份证2. 和主键对比核心区别约束能否NULL一张表数量primary key❌不允许为空只能1个unique key✅允许NULLNULL之间不做重复校验可以多个业务经验主键用无业务含义自增ID唯一键用来约束业务字段不能重复邮箱、身份证示例代码create table student ( id char(10) unique comment 学号不能重复但可以为空, name varchar(10) ); insert into student(id,name) values(01,aaa); insert into student(id,name) values(01,bbb); -- 重复报错 insert into student(id,name) values(null,bbb); -- NULL可以多次插入 select * from student;5.8 外键 foreign key知识点1. 作用约束两张表的数据关联性保证从表数据一定在主表存在杜绝脏数据主表被引用的表班级表从表设置外键的表学生表2. 语法foreign key(从表字段) references 主表名(主表主键字段)3. 约束规则1从表外键的值要么等于主表已经存在的值2从表外键的值要么直接为NULL3主表被从表引用的数据不能随意删除4主表被引用列必须是主键或者唯一键开发提醒MySQL外键是数据库层校验大型互联网项目一般不在数据库建立外键业务代码层面做逻辑校验示例代码-- 1.先建【主表】班级表 create table myclass ( id int primary key, name varchar(30) not null comment 班级名 ); -- 2.再建【从表】学生表设置外键关联班级id create table stu ( id int primary key, name varchar(30) not null comment 学生名, class_id int, foreign key (class_id) references myclass(id) ); -- 主表插入班级 insert into myclass values(10,C大牛班),(20,java大神班); -- 合法班级10、20主表里存在 insert into stu values(100,张三,10),(101,李四,20); -- ❌报错班级30不存在外键约束拦截 insert into stu values(102,wangwu,30); -- ✅合法外键给NULL学生暂时没有分配班级 insert into stu values(102,wangwu,null);5.9 综合建表案例商店业务三张表业务说明商品表、客户表、购买订单表主外键关联约束需求清单1.每张表设置主键、自增2.客户姓名不能为空3.邮箱不能重复unique唯一键4.性别只能男 / 女enum枚举-- 创建数据库 create database if not exists bit32mall default character set utf8 ; use bit32mall; -- 商品表 goods create table if not exists goods ( goods_id int primary key auto_increment comment 商品编号, goods_name varchar(32) not null comment 商品名称, unitprice int not null default 0 comment 单价单位分, category varchar(12) comment 商品分类, provider varchar(64) not null comment 供应商名称 ); -- 客户表 customer create table if not exists customer ( customer_id int primary key auto_increment comment 客户编号, name varchar(32) not null comment 客户姓名, address varchar(256) comment 客户地址, email varchar(64) unique key comment 电子邮箱, sex enum(男,女) not null comment 性别, card_id char(18) unique key comment 身份证 ); -- 购买订单 purchase从表双外键 create table if not exists purchase ( order_id int primary key auto_increment comment 订单号, customer_id int comment 客户编号, goods_id int comment 商品编号, nums int default 0 comment 购买数量, foreign key (customer_id) references customer(customer_id), foreign key (goods_id) references goods(goods_id) );约束面试重点总结1. not null字段禁止为空default不传值自动填充预设内容2. zerofill仅查询显示补零存储数值不变自动unsigned无符号3. primary key主键非空唯一一张表只能1个主键支持复合主键4. auto_increment自增必须绑定整数索引主键单表只能1个5. unique key唯一键可以多个可以存NULL只约束业务字段不重复6. foreign key外键关联两张表从表数据必须在主表存在或者NULL管控数据完整性7. comment注释仅文档作用不参与任何校验逻辑
