MYSQL总结一_1. recharge date*2. order number*required fields:3-CSDN博客

本文链接：https://blog.csdn.net/m0_37111373/article/details/109689769

MySQL

MYSQL 简章

MYSQL 简章

简单了解表的组成部分

表名，表字段名，定义每个表的字段
表名：是数据库中表的名字
表字段名：就是每一个字段的名字
表字段定义：如何定义一个表字段首先
1.整型的基本类型：
TINYINT： 小整数型一般用来定义 0,1等开关类和版本类的字段，大小为 1 byte，范围（-128.127）
SMALLINT： 大整数型，大小为 2 bytes，范围（-32768,32767）
MEDIUMINT： 大整数型，大小为 3 bytes，范围（-8388608,8388607）
INT或INTEGER： 大整数型，大小为 4 bytes
BIGINT： 大整数型，大小为 8 bytes
FLOAT： 单精度浮点数值一般用来定义带小数点的值，大小为 4 bytes
DOUBLE： 大小为 8 bytes 双精度浮点数值
DECIMAL：
2.日期和时间类型
DATE： 日期值
TIME： 时间值或持续时间
YEAR： 年份值
DATETIME： 混合日期值
TIMESTAMP： 混合日期或者时间戳，时间戳（时间戳是指格林威治时间1970年01月01日bai00时00分00秒(北京时间1970年01月01日08时00分00秒)起至现在的总秒数。通俗的讲，时间戳是一份能够表示一份数据在一个特定时间点已经存在的完整的可验证的数据）。
3.字符串类型
CHAR： 大小为 0 - 255 bytes，定长字符串
VARCHAR： 大小为 0 - 65535 bytes，变长字符串
TINYBLOB： 大小为 0 - 255 bytes，不超过255个字符的二进制字符串
TINYTEXT： 大小为 0 - 255 bytes，短文本字符串
BLOB： 大小为 0 - 65535 bytes，二进制形式的长文本数据
TEXT： 大小为 0 -65535 bytes，长文本数据
MEDIUMBLOB： 二进制形式的中等长度文本数据
MEDIUMTEXT： 中等长度文本数据
LONGBLOB： 二进制形式的极大文本数据
LONGTEXT： 极大的文本数据

表的创建

分析案例：
表创建的语句：

CREATE TABLE `app_user_info` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `haitang_number` int(20) NOT NULL COMMENT '号',
  `user_name` varchar(32) DEFAULT NULL COMMENT '账户名',
  `pass_word` varchar(32) NOT NULL COMMENT '密码',
  `telephone_number` varchar(32) NOT NULL COMMENT '手机号码',
  `user_image_url` varchar(256) DEFAULT 'demo.jpg' COMMENT '用户头像url',
  `user_level` bigint(4) DEFAULT '0' COMMENT '用户等级',
  `vip_level` int(4) DEFAULT '0' COMMENT '0:普通  1:至尊',
  `nick_name` varchar(32) DEFAULT NULL COMMENT '昵称',
  `role_id` varchar(32) DEFAULT NULL,
  `status` bigint(4) NOT NULL COMMENT '账号状态(1.启用 2.关闭 3.模拟)',
  `login_time` datetime DEFAULT NULL COMMENT '登陆时间',
  `is_login` bigint(4) DEFAULT NULL COMMENT '登陆状态',
  `login_number` bigint(4) NOT NULL DEFAULT '0' COMMENT '用户登陆次数',
  `logout_time` datetime DEFAULT NULL COMMENT '登出时间',
  `recharge_amount` decimal(18,2) NOT NULL DEFAULT '0.00' COMMENT '充值金额(糖果)',
  `earnings_amount` decimal(18,2) NOT NULL DEFAULT '0.00' COMMENT '收益金额(海棠币)',
  `balance_amount` decimal(18,2) NOT NULL DEFAULT '0.00' COMMENT '账号金额',
  `accumulation_recharge_amount` decimal(18,2) NOT NULL DEFAULT '0.00' COMMENT '累计充值',
  `accumulation_consumer_amount` decimal(18,2) NOT NULL DEFAULT '0.00' COMMENT '累计消费',
  `fans_num` varchar(32) DEFAULT NULL COMMENT '粉丝数量',
  `like_number` varchar(12) DEFAULT NULL COMMENT '点赞数',
  `equipment_id` bigint(20) DEFAULT NULL COMMENT '设备id',
  `equipment_name` varchar(20) DEFAULT NULL COMMENT '设备名称',
  `equipment_code` varchar(32) DEFAULT NULL COMMENT '设备码',
  `gender` varchar(4) NOT NULL DEFAULT '0' COMMENT '性别(1:女 2:男)',
  `birthday` date DEFAULT NULL COMMENT '生日',
  `user_signature` varchar(32) DEFAULT NULL COMMENT '用户签名',
  `constellation` varchar(12) DEFAULT NULL COMMENT '星座',
  `register_to_adress` varchar(32) DEFAULT NULL COMMENT '注册地址',
  `belong_to_adress` varchar(32) DEFAULT NULL COMMENT '归属地',
  `is_delete` bigint(3) DEFAULT NULL,
  `create_time` datetime NOT NULL,
  `updatetime` datetime DEFAULT NULL,
  `update_id` varchar(32) DEFAULT NULL,
  `remark` varchar(128) DEFAULT NULL,
  `lng` varchar(200) DEFAULT '0' COMMENT '经度',
  `lat` varchar(200) DEFAULT '0' COMMENT '纬度',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=54 DEFAULT CHARSET=utf8mb4;

这是一个很基本的创建表的语句
1）字段上如果不想字段为NULL可以设置字段的属性为NOT NULL，操作数据库的时候如果该字段的数据要是为NULL的话就会报错
2）自增的定义，用 AUTO_INCREMENT进行声明，一般用于主键上，可以实现效果主键的数值自增+1。
3）用关键字 PRIMARY KEY (id) 用来定义字段为主键
4）ENGINE设置存储引擎，一般InnoDB，提供了事务的控制能力功能，他确保一组命令全部执行成功，或者当任何一个命令出现错误时所有命令的结果都被回退。支持COMMIT，ROLLBACK和其他事务特性。
5）CHARSET：utf8mb4，字符的编码，字符编码的区别
链接: https://baike.baidu.com/item/%E5%AD%97%E7%AC%A6%E7%BC%96%E7%A0%81/8446880?fr=aladdin.
6）COMMENT 可以给字段添加上一些描述，能让字段的意思更加直观清晰
7）针对特殊的值也可以进行默认值的设定 DEFAULT ‘0’

表的修改ALTER

DROP 比如删除表中字段：
ALTER TABLE app_user_info DROP belong_to_adress;
相当于删除了表中归属地的字段
ADD 添加表中的字段：
ALTER TABLE app_user_info ADD belong_to_adress varchar(32) NULL DEFAULT COMMENT ‘归属地’;
把删除的字段进行添加回来
MODIFY 修改表中字段类型及名称：
ALTER TABLE app_user_info MODIFY belong_to_adress bigint(20) DEFAULT NULL COMMENT ‘归属地INT型’;
再修改回来
ALTER TABLE app_user_info MODIFY belong_to_adress varchar(32) DEFAULT NULL COMMENT ‘归属地’;

表中数据的添加为后续进行准备

INSERT INTO `app_user_info` (`id`, `haitang_number`, `user_name`, `pass_word`, `telephone_number`, `user_image_url`, `user_level`, `vip_level`, `nick_name`, `role_id`, `status`, `login_time`, `is_login`, `login_number`, `logout_time`, `recharge_amount`, `earnings_amount`, `balance_amount`, `accumulation_recharge_amount`, `accumulation_consumer_amount`, `fans_num`, `like_number`, `equipment_id`, `equipment_name`, `equipment_code`, `gender`, `birthday`, `user_signature`, `constellation`, `register_to_adress`, `belong_to_adress`, `is_delete`, `create_time`, `updatetime`, `update_id`, `remark`, `lng`, `lat`, `channel`) VALUES ('48', '906414', '18510628557', '207acd61a3c1bd506d7e9a4535359f8a', '18510628593', 'http://model.chillivision.com//15770904622203045a053-5916-471b-a4a1-f3dcd6d98286.jpg', '1201', '0', '哈哈哈', NULL, '1', NULL, NULL, '92', NULL, '0.00', '19.93', '9487.93', '0.00', '0.00', NULL, NULL, NULL, '123456', '123456', '1', '2019-12-29', '1234567890', '摩羯座', NULL, '北京', '1', '2019-12-23 16:39:18', NULL, NULL, NULL, '116.553798583037', '39.90256918595045', '');
INSERT INTO `app_user_info` (`id`, `haitang_number`, `user_name`, `pass_word`, `telephone_number`, `user_image_url`, `user_level`, `vip_level`, `nick_name`, `role_id`, `status`, `login_time`, `is_login`, `login_number`, `logout_time`, `recharge_amount`, `earnings_amount`, `balance_amount`, `accumulation_recharge_amount`, `accumulation_consumer_amount`, `fans_num`, `like_number`, `equipment_id`, `equipment_name`, `equipment_code`, `gender`, `birthday`, `user_signature`, `constellation`, `register_to_adress`, `belong_to_adress`, `is_delete`, `create_time`, `updatetime`, `update_id`, `remark`, `lng`, `lat`, `channel`) VALUES ('49', '831078', '17858252589', '207acd61a3c1bd506d7e9a4535359f8a', '17858252505', 'http://model.chillivision.com/17777777777/1578048423594089a515e-093e-41bf-887b-5a038f90a7c2.jpg', '1801', '0', '张三', NULL, '1', NULL, NULL, '51', NULL, '0.00', '181.00', '1.00', '0.00', '0.00', NULL, NULL, NULL, '123456', '123456', '1', '2020-01-01', '哈哈哈', '摩羯座', NULL, '重庆', '1', '2019-12-27 17:02:38', NULL, NULL, NULL, '116.5536855422386', '39.90254055000641', '');
INSERT INTO `app_user_info` (`id`, `haitang_number`, `user_name`, `pass_word`, `telephone_number`, `user_image_url`, `user_level`, `vip_level`, `nick_name`, `role_id`, `status`, `login_time`, `is_login`, `login_number`, `logout_time`, `recharge_amount`, `earnings_amount`, `balance_amount`, `accumulation_recharge_amount`, `accumulation_consumer_amount`, `fans_num`, `like_number`, `equipment_id`, `equipment_name`, `equipment_code`, `gender`, `birthday`, `user_signature`, `constellation`, `register_to_adress`, `belong_to_adress`, `is_delete`, `create_time`, `updatetime`, `update_id`, `remark`, `lng`, `lat`, `channel`) VALUES ('50', '145118', '18888888811', '207acd61a3c1bd506d7e9a4535359f8a', '18888888888', 'http://model.chillivision.com/18888888888/1579162838921b82e5494-bdcd-4f58-b556-665ba405693d.jpg', '1601', '0', '56801', NULL, '1', NULL, NULL, '22', NULL, '0.00', '206.00', '46.00', '0.00', '0.00', NULL, NULL, NULL, '123456', '123456', '1', '2020-01-01', '', '摩羯座', NULL, '566', '1', '2020-01-08 14:13:22', NULL, NULL, NULL, '118.8161284123429', '35.58943871427938', '');

可以成功添加三条属于app_user_info 的基本数据

SELECT,UPDATE,INSERT,DELETE语句

SELECT
例如：
场景：要查询一张表（app_user_info）的数据
可以使用SELECT进行数据的查询
SELECT * FROM app_user_info；
这是一个全数据查询的的语句
会直接查询出来全部的数据，假如一张极大数据量的表就要尽量避免全数据的查询，因为在没有使用索引的情况下，大数据量得表全查询是极度消耗性能的
此时我们可以进行WHERE的条件筛选这样就可以适当减少查询消耗的性能，也可以不要进行 * 的查询，用到哪个字段就查哪个字段，列如我们现在只需要，用户表中的手机信息和账号信息，此时我们可以进行如下操作，
SELECT haitang_number,telephone_number FROM app_user_info;
这样查询消耗的性能就比全查询消耗的性能低很多，此时可以加上条件筛选，比如知道用户手机号
SELECT haitang_number,telephone_number FROM app_user_info WHERE telephone_number = 18888888811;
这样我们就可以根据手机号来找到与手机号匹配的账号了
UPDATE
例如：
场景：要修改一张表（table）的数据
UPDATE app_user_info SET haitang_number = “test1” WHERE telephone_number = 18888888811;
要是不使用WHERE语句就会导致符合条件的所有数据都被进行了批量修改。
INSERT
先执行下面的DELETE进行尝试练习然后在进行插入数据操作
INSERT INTO app_user_info (id, haitang_number, user_name, pass_word, telephone_number, user_image_url, user_level, vip_level, nick_name, role_id, status, login_time, is_login, login_number, logout_time, recharge_amount, earnings_amount, balance_amount, accumulation_recharge_amount, accumulation_consumer_amount, fans_num, like_number, equipment_id, equipment_name, equipment_code, gender, birthday, user_signature, constellation, register_to_adress, belong_to_adress, is_delete, create_time, updatetime, update_id, remark, lng, lat, channel) VALUES (‘50’, ‘145118’, ‘18888888811’, ‘207acd61a3c1bd506d7e9a4535359f8a’, ‘18888888888’, ‘http://model.chillivision.com/18888888888/1579162838921b82e5494-bdcd-4f58-b556-665ba405693d.jpg’, ‘1601’, ‘0’, ‘56801’, NULL, ‘1’, NULL, NULL, ‘22’, NULL, ‘0.00’, ‘206.00’, ‘46.00’, ‘0.00’, ‘0.00’, NULL, NULL, NULL, ‘123456’, ‘123456’, ‘1’, ‘2020-01-01’, ‘’, ‘摩羯座’, NULL, ‘566’, ‘1’, ‘2020-01-08 14:13:22’, NULL, NULL, NULL, ‘118.8161284123429’, ‘35.58943871427938’, ‘’);
DELETE
DELETE FROM app_user_info WHERE telephone_number = 18888888811;

LIKE

一般在WHERE子句中使用 = 来进行指定获取数据的条件，但是当有时候要一个匹配规则的时候，我们就需要使用LIKE
SELECT haitang_number,telephone_number FROM app_user_info WHERE telephone_number like "%18%"
LIKE 子句中使用百分号 %字符来表示任意字符

细谈WHERE

再WHERE子句中可以使用多个条件查询，也可以使用排序等等语句加上函数
列子1：
SELECT haitang_number,telephone_number FROM app_user_info WHERE telephone_number = 18888888888；
代表查询手机号为：18888888888 的数据集

列子2：
asc是指定列按升序排列，desc则是指定列按降序排列
SELECT haitang_number,telephone_number,create_time FROM app_user_info WHERE telephone_number = 18888888888 ORDER BY create_time DESC;
代表查询手机号为：18888888888 的数据集，并且查询出来的结果集按照 create_time 的降序排列

列子3：
SELECT count(1) FROM app_user_info GROUP BY telephone_number;
统计分组好以后，数据具有相同手机号的条数
GROUP BY： GROUP BY 语句根据一个或多个列对结果集进行分组。在分组的列上我们可以使用 COUNT, SUM, AVG,等函数

细谈表连接查询

UNION

UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。
针对的就是结果集
例子：

SELECT id,haitang_number FROM app_user_info UNION SELECT haitang_number,telephone_number FROM app_user_info;

首先前面的 SELECT id,haitang_number FROM app_user_info 代表，定义了其中一列的名字id，另一列的名字 haitang_number，相当于这是一个 n行2列的一个结果集。
因为 UNION代表连接两个以上的 SELECT 语句的结果组合到一个结果集合中，又因为前一个结果为 n行2列，所以后面的结果集也必须是j行2列，两个结果集的合并必须是相当的列数，行数可以不同

INNER JOIN

两张表数据的交集

列子：
SELECT * FROM app_user_info app_a INNER JOIN app_user_info app_b ON app_a .id = app_b .id;

LEFT JOIN

左边表所有的数据加上右边和左边表相交的数据

SELECT * FROM app_user_info app_a LEFTJOIN app_user_info app_b ON app_a .id = app_b .id;

RIGHTJOIN

RIGHT JOIN 会读取右边数据表的全部数据，即便左边边表无对应数据

SELECT * FROM app_user_info app_a RIGHT JOIN app_user_info app_b ON app_a .id = app_b .id;

细谈索引

优点：

索引的效果相当于，牺牲了空间换取了时间，大大减小了服务器执行命令的时候需要扫描的数据量
索引还可以把随机IO变成顺序IO
可以帮助服务器避免排序和临时表

缺点：

大大提升了存储空间的使用
在更新表的同时，不仅需要保存数据，还要取更新索引文件

索引的类型区分

简述每种索引的区别

普通索引

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
创建索引：
CREATE INDEX suoyinName ON app_user_info (telephone_number);
修改表结构添加索引：
ALTER table app_user_info ADD INDEX suoyinNameAdd (haitang_number);
删除索引：
DROP suoyinName [telephone_number] ON app_user_info ;
DROP suoyinNameAdd [haitang_number] ON app_user_info ;

唯一索引

索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
创建索引：
**CREATE UNIQUE INDEX suoyinName ON app_user_info (telephone_number(20)) **
修改表结构添加索引：
ALTER table app_user_info ADD UNIQUE [suoyinNameAdd ] (haitang_number(20))
删除索引：
DROP suoyinName [telephone_number] ON app_user_info ;
DROP suoyinNameAdd [haitang_number] ON app_user_info ;