Mysql DDL DML

Mysql DDL(建表, 修改表结构的语句)      DML(insert update delete语句)

-- 可重复执行的创建库
create database if not exists test_db CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
 
usetest_db;
 
-- 可重复执行的创建表
CREATE TABLE IF NOT EXISTS user(
  `name` varchar(50) DEFAULT NULL
) ENGINE=InnoDB;

-- ROW_FORMAT = Dynamic不写mysql8 行记录格式默认Dynamic
-- 在 MySQL 中，如果在建表的列定义中没有指定默认值，并且也没有指定列的 NOT NULL 约束，
-- 则默认情况下该列将使用 DEFAULT NULL 作为默认值。因此，字段 DEFAULT NULL写不写, 都是默认 DEFAULT NULL
-- 虽然在某些情况下 DEFAULT NULL 是默认设置，但这并不意味着您可以忽略此行为。
-- 在建表时，您应该尽可能明确地指定列的默认值和 NULL 约束，以便能够清晰地表达数据模型中的含义。
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
`gender` VARCHAR(1) DEFAULT NULL COMMENT '性别',
`name` VARCHAR(50) DEFAULT NULL COMMENT '姓名',
`age` TINYINT DEFAULT NULL COMMENT '年龄',
`birthdate` date  DEFAULT NULL COMMENT '出生日期',
`user_card` VARCHAR(20) DEFAULT NULL COMMENT '身份证',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB ROW_FORMAT = Dynamic;

在MySQL中复制表结构和数据:

1.使用CREATE TABLE ... AS语句：  此方式速度很快的, 200w数据也是40s就搞定了

CREATE TABLE new_table AS SELECT * FROM old_table;     new_table是不存在的

2.使用INSERT INTO ... SELECT语句：  200w数据也是40s就搞定了

INSERT INTO new_table SELECT * FROM old_table;     前提是 new_table已经存在了

show create table user; 语句可以查询表的 DDL 语句

创建一个表结构一样的表

CREATE TABLE user1 like user;

DDL之修改表字段

-- 字段类型修改  注意修改表字段必须加``  (连同注释一并改了, 你可以不带COMMENT '姓名')
ALTER TABLE `user` MODIFY COLUMN `name` varchar(20) not NULL COMMENT '姓名';
ALTER TABLE `user` MODIFY COLUMN `delete_mark` tinyint not NULL DEFAULT(0) COMMENT '删除标志';
-- 列的重命名、列类型的变更
ALTER TABLE `user` CHANGE COLUMN `name` `name1` text  COMMENT '姓名1';

-- 删除一列
ALTER TABLE `user` DROP COLUMN `name`;


-- 在某个字段后加一个字段, 如果不指定after 字段, 默认就加到最后一列了
ALTER TABLE `user`ADD COLUMN `name` VARCHAR ( 50 ) DEFAULT 'zhangsan' NOT NULL COMMENT '姓名' AFTER `age`;

1、表连接不宜太多，一般5个以内

关联的表个数越多，编译的时间和开销也就越大
每次关联内存中都生成一个临时表
应该把连接表拆开成较小的几个执行，可读性更高
如果一定需要连接很多表才能得到数据，那么意味着这是个糟糕的设计了
阿里规范中，建议多表联查三张表以下

2、索引不宜太多，一般5个以内

索引并不是越多越好，虽其提高了查询的效率，但却会降低插入和更新的效率；
索引可以理解为一个就是一张表，其可以存储数据，其数据就要占空间；
索引表的数据是排序的，排序也是要花时间的；
insert或update时有可能会重建索引，如果数据量巨大，重建将进行记录的重新排序，所以建索引需要慎重考虑，视具体情况来定；
一个表的索引数最好不要超过5个，若太多需要考虑一些索引是否有存在的必要；

阿里巴巴索引命名规范：主键索引名为 pk_字段名；唯一索引名为 uk_字段名；普通索引名则为 idx_字段名 即 index的简称。。

说明：pk_即 primary key；uk_即 unique key；idx_即 index 的简称

MySQL会忽略为主键指定的名称, key_name就是PRIMARY

注意创建索引必须明确指定索引类型, 不需要指定 索引的数据结构

1.索引类型 普通索引（INDEX）唯一索引（UNIQUE INDEX）主键索引（PRIMARY KEY）全文索引（FULLTEXT INDEX）空间索引（SPATIAL INDEX）

2.索引的数据结构(B-Tree（平衡树） / B+ Tree（B-Tree的变种）/ HASH哈希索引 / 全文索引使用倒排索引（Inverted Index）来加速文本搜索/ 空间索引的R-Tree如地理信息) , 在一些可视化工具navicat里叫索引方法,navicat展示是BTREE也对(因为B+树是B树的一种变种)

 MySQL InnoDB创建普通索引 需要指定b+树吗?

在MySQL中，InnoDB 存储引擎使用 B+树 来实现索引，这是默认的索引结构。因此，当您使用 InnoDB 存储引擎创建普通索引时，不需要显式指定 B+树

CREATE INDEX idx_name USING BTREE ON user (name);

CREATE UNIQUE INDEX uk_code_name  ON user(code,name);

SHOW INDEX FROM user; 查询索引

-- 添加索引的另一种写法

ALTER TABLE user ADD INDEX idx_name(name) USING BTREE;

-- 删除索引

DROP INDEX 索引名 on my_table;  

回退脚本

DROP TABLE my_table;

DROP INDEX 索引名 on my_table;  

ALTER TABLE my_table DROP INDEX my_index;

ALTER TABLE my_table DROP name;

数据库弱关联关系的理解:        

 一般数据库建表时,比如一对多,多方表含有一方表的主键作为它的外键.但这种绑定关系在数据库最好不要绑定关联,这种就叫数据库的弱关联关系.这种方式建表的好处就是直接在数据库表里删数据时,不会级联删

mysql user表有create_time update_time字段, 新增user表 需要给这2个都赋值时间吗?

通常情况下， create_time（创建时间）：表示记录的创建时间，一般在插入新记录时赋予当前的时间戳。这个值在记录创建后一般不再改变。 update_time（更新时间）：表示记录最后一次更新的时间。在插入新记录时，你可以将update_time和create_time都设置为当前时间戳，表示这条记录是新创建的，然后在记录被更新时，只更新update_time字段。

阿里JAVA规范对is_grant这样的字段定义为无符号 tinyint 只能存 0-255