MySQL 库表操作学习笔记

SQL 语句基础SQL 简介SQL结构化查询语言是关系型数据库标准语言可以改库结构、设置权限、增删改查数据、维护数据库。SQL 四大分类DDL 数据定义语言操作库、表、索引对象。CREATE、DROP、ALTER、RENAME、TRUNCATEDML 数据操作语言操作表里记录。INSERT、DELETE、UPDATE、SELECTDCL 数据控制语言权限管理。GRANT、REVOKE事务控制COMMIT提交、ROLLBACK回滚、SAVEPOINT保存点SQL 书写规范SQL 关键字不区分大小写建议大写字符串内容区分大小写语句以分号;结尾可以单行 / 多行写关键字不能换行拆分注释多行/* */单行--、#数据库操作MySQL 登录与退出先启动服务systemctl start mysqld不启动会报 socket 连接错误登录命令mysql -u用户名 -h主机地址 -p密码 # 推荐写法密码回车后输入更安全 mysql -uroot -p-h本地填localhost-p和密码之间不要空格直接命令行写密码会有安全警告退出exit/quit/\q常用查看命令登录 mysql 内部执行select now(); --查看当前时间 select version(); --查看mysql版本 select user(); --查看当前登录用户查看数据库show databases; --查看全部数据库注意带s show databases like s%; --%匹配任意字符_匹配单个字符MySQL 自带 4 个系统库系统库说明information_schema数据字典存库、表、列、权限元信息performance_schema服务器性能统计mysql存放账号、权限信息sys简化性能查询视图5.7 新增创建数据库create database 库名; show create database 库名\G; --查看建库语句\G格式化垂直输出库已存在直接创建会报错关键字做库名要用反引号包裹create database database;MySQL8.0 默认字符集utf8mb4支持 emoji 表情utf8mb4_0900_ai_ci不区分重音、不区分大小写切换数据库select database(); --查看当前正在使用的库没选库返回NULL use 库名; --切换数据库删除数据库drop database 库名;数据库操作速记create database 库名; --新建 use 库名; --切换 select database(); --看当前库 show databases; --列出所有库 drop database 库名; --删除MySQL 字符集字符集字符编码存储规则字符序 (collation)字符排序、比较规则版本差异MySQL5.7 默认latin1MySQL8.0 默认utf8mb4⚠️ mysql 里面utf8实际是utf8mb3最多 3 字节不能存 emoji尽量不要用统一使用 utf8mb4。后缀含义ci大小写不敏感bin二进制ai口音不敏感查看命令show character set; --查看全部字符集 show collation; --查看全部校对规则 show variables like character_set%; --查看系统字符集变量数据库对象对象作用表核心存储行是记录列是字段约束校验数据保证完整性数据字典系统表存元数据视图一张 / 多张表的逻辑展示索引提升查询速度命名规范小写下划线分隔不能用保留关键字字母开头生产规范表类型命名示例普通表t_模块_表名例t_user_eduinfo临时表temp_xxx_日期备份表bak_xxx_日期表的基本操作创建表前必须use 数据库名否则报错No database selected常用数据类型整型类型字节有符号范围无符号 unsignedtinyint1-128~1270‑255smallint2-32768~327670‑65535int4-21 亿21 亿0‑42 亿bigint8超大超大int(10)只是显示宽度不改变存储 4 字节8.0.17 不推荐写法。浮点与定点类型说明float (4 字节)存在精度丢失存近似值double (8 字节)存在精度丢失存近似值decimal(M,D)定点数字符串存储无精度丢失财务金额必须使用 decimal字符串类型特点适用场景char(n)固定长度不足补空格最多 255手机号、身份证varchar(n)可变长度最多 255普通短文本text大文本文章、评论blob二进制字节图片二进制不推荐文件存磁盘enum枚举只能选给定值性别等有限选项set多选集合可以存多个选项标签多选时间日期类型字节格式date3yyyy‑MM‑dd日期time3HH:mm:ss时间datetime8日期 时间timestamp4时间戳日期时间范围有限创建表create table 表名( 列名1 数据类型 [约束], 列名2 数据类型 [约束] );查看表show tables; --查看当前库所有表 show tables from 其他库名; --看别的库的表 desc 表名; --查看表字段结构 show create table 表名\G; --查看完整建表语句 show columns from 表名; --等价descdesc 输出字段说明Field字段名Type类型Null是否允许空Key索引Default默认值Extra额外属性删除表drop table if exists 表名; --if exists防止表不存在时报错修改表 ALTER TABLE修改表名alter table 旧表 rename 新表名; rename table 旧表 to 新表名;新增列alter table 表 add 列名 类型; alter table 表 add 列名 类型 after 某列; --加到某列后面 alter table 表 add 列名 类型 first; --放到第一列删除列alter table 表 drop 列名;change改列名 类型alter table 表 change 旧列 新列 新类型;modify只修改列类型 / 约束不改名字alter table 表 modify 列名 新类型; alter table 表 modify 列名 新类型 after xx; --移动列位置只复制表结构不带数据create table 新表 like 源表;复制结构 全部数据create table 新表 select * from 源表;只复制数据到已有空表insert into 新表 select * from 源表;数据字典 information_schema系统库information_schema保存全部元数据描述数据的数据系统表作用tables所有表信息schemata所有数据库信息columns所有字段信息可以查询这几张系统表获取数据库运行元信息用于运维统计。表的约束约束目的保障数据完整性四类完整性实体完整性、域完整性、引用完整性、自定义完整性。约束分两种列级约束写在字段定义后面表级约束所有字段写完之后再写约束适合联合主键、外键NOT NULL 非空约束字段不能存 NULL空字符串和数字0不等于 NULL。--建表时 name varchar(20) not null --后期修改 alter table 表 modify name varchar(20) not null; --删除非空约束 alter table 表 modify name varchar(20) null;DEFAULT 默认值约束插入不给值自动填充默认值sex char(2) default 女 alter table 表 modify sex char(2) default 女;COMMENT 字段注释只做说明不限制数据在建表语句保存name varchar(20) comment 用户姓名;PRIMARY KEY 主键约束特点非空、不能重复一张表最多 1 个主键自动创建索引复合主键多个字段联合做主键组合不能重复单个可以重复--列级 id int primary key --表级复合主键 primary key(ip,port) --后期添加主键 alter table 表 add primary key(id); --删除主键 alter table 表 drop primary key;AUTO_INCREMENT 主键自增必须是整数必须是主键 / 唯一索引一张表只能有一个自增列不给该字段赋值自动取当前最大值 1可以手动插入指定数值id int primary key auto_increment alter table 表 modify id int auto_increment; --建表指定起始值 create table t(id int primary key auto_increment) auto_increment100;UNIQUE 唯一约束字段不能重复可以为 NULL一张表可以多个唯一键。主键不能 null只能 1 个唯一键允许 null可以多个name varchar(20) unique --删除唯一约束 alter table 表 drop index 约束名;FOREIGN KEY 外键约束引用完整性主表父表被引用从表子表引用别人外键写在从表主表被引用字段必须是主键 / 唯一键外键值要么是主表已存在值要么 NULL。foreign key(从表字段) references 主表(主表主键字段) --删除外键 alter table 表 drop foreign key 外键约束名;CHECK 检查约束限制字段取值范围MySQL8.0.16 之后真正生效之前写了不校验。check(salary0) alter table 表 add constraint 约束名 check(id0); alter table 表 drop check 约束名;约束删除速查表约束删除方式NOT NULL / DEFAULTmodify 列 类型去掉约束PRIMARY KEYdrop primary keyUNIQUEdrop index 约束名FOREIGN KEYdrop foreign key 约束名CHECKdrop check 约束名存储引擎存储引擎是 MySQL 底层读写数据的模块。引擎特点InnoDBMySQL8 默认支持事务、行锁、外键适合业务系统MyISAM查询插入快不支持事务、外键MEMORY全部放内存重启数据丢失速度极快查看引擎show engines\G建表指定引擎与字符集create table 表(...) engineInnoDB default charsetutf8mb4;MySQL 账户及授权密码策略MySQL8.0 默认认证插件caching_sha2_password5.7 是mysql_native_password密码强度插件component_validate_passwordLOW仅校验长度MEDIUM长度 数字大小写特殊字符STRONG再加字典文件修改全局密码策略属于临时生效永久生效修改/etc/my.cnf配置文件。用户管理MySQL8先创建用户再授权用户格式用户名登录主机localhost仅本地登录%代表允许任意远程 IP 登录--创建用户 create user testlocalhost identified by 123456; --修改密码 alter user testlocalhost identified by 新密码; --删除用户 drop user testlocalhost;用户信息保存在系统库mysql.user表。授权 GRANTgrant 权限列表 on 库.表 to 用户主机; -- *.* 代表所有库所有表mydb.*代表mydb库全部表常用权限select insert update delete create dropall全部权限不包含给别人授权的权限。with grant option允许该用户给其它账号授权。查看权限show grants for testlocalhost;回收权限 REVOKErevoke 权限列表 on 库.表 from 用户主机; revoke all on *.* from test%; --回收全部权限权限最佳实践root 账号禁止使用%任意远程访问账号权限最小化业务账号尽量只给 select 查询业务账号、管理员账号分开定期清理废弃账号