mysql、表操作：表的增删改查、表的拷贝和简单移动（注只能移动到当前mysql下的另一数据库）、自动生成列generated alw；列操作：列的增删改查；

/*查看MySQL储存引擎*/
show engines;
/*查看mysql系统相关的字符集*/
show character set ;
/*查看当前mysql系统所支持的字符集*/
show variables like 'char%';
/*查看表*/
show tables;/*查看当前数据库下的所有表*/
show tables from db2;/*查看db2数据库的表*/
show table status from db2;/*查看db数据库下的所有表的信息*/
desc t9;/*查看表结构*/
show tables like 't%';/*查看开头有t的表名*/

/**********************************************************************************************************************/

/*创建表一,极简*/
create table t10(i int);/*当前数据库中建表*/
create table db2.t10(i int);/*db2数据库中建表*/
insert into t10 values (1),(2),(3);
select * from t10;

/*创建表二，有主键*/
create table if not exists t11(
    id int unsigned not null auto_increment,
    uname varchar(50) not null,
    gendre enum('男','女','妖') default '妖',
    birth date,
    primary key (id)
);
select * from t11;
insert into t11 values(null,'wzt','男','1998-10-12'),(null,'yaozeng','妖','1922-02-18');

/*创建表三,有主键、有字符集、有存储引擎*/
create table if not exists t12(
    id int unsigned not null auto_increment,
    uname varchar(50) not null,
    gendre enum('男','女','妖') default '妖',
    birth date,
    primary key (id)
)engine=InnoDb default charset=utf8mb4 auto_increment=2022;
select * from t12;
insert into t12 values(null,'wzt','男','1998-10-12'),(null,'yaozeng','妖','1922-02-18');

/*创建表四，更具所查内容建立一个表，这样的表没有相关约束不推荐使用*/
create table if not exists t0 as select 20,'jaca';
select * from t0;
desc t0;

/**********************************************************************************************************************/

/*generated always 生成列*/
create table if not exists t13(
    id int unsigned not null auto_increment,
    uname varchar(20) not null,
    gender enum('男','女') default '男',
    sfzh varchar(20) not null,
    brith varchar(20) generated always as (concat(mid(sfzh,7,4),'-',mid(sfzh,11,2),'-',mid(sfzh,13,2))),
    st1 varchar(10) not null,
    st2 varchar(10) not null,
    ywcj int not null,
    sxcj int not null,
    yycj int not null,
    zcj int generated always as (ywcj+sxcj+yycj),
    zst varchar(30) generated always as (concat(st1,' ',st2)),
    zgender varchar(10) generated always as (if(gender='男','先生','女士')),
    primary key(id)
)engine=InnoDb default charset=utf8mb4 auto_increment=2022021800;
drop table t13;
select * from t13;
insert into t13(uname,gender,sfzh,st1,st2,ywcj,sxcj,yycj) value('wzt','女','452525199810126666','hellow','world',1,2,3);

/**********************************************************************************************************************/

/*drop删除表*/
create table s1(id int);
create table s2(id int);
create table s3(id int);
create table s4(id int);
create table s5(id int);
create table db2.s5(id int);
drop table if exists s1;/*删除s1*/
drop table db2.s5;/*删除dba.s5*/
drop table s2,s3,s4,s5;/*删除s2,s3,s4,s5*/

/**********************************************************************************************************************/

/*修改表*/
show tables;
create table if not exists tt(id int);
create table s1(id int);
create table s2(id int);
create table s3(id int);
/*修改表名*/
rename table tt to db_tt;/*将表名修改为db_tt,注意：数据库不区分大小学不能将名字改为TT、Tt、tT*/
rename table s1 to v1,s2 to v2,s3 to v3;/*修改多个表名*/
rename table db_tt to db2.db_tt;/*将数据库转移到db2数据库中*/
alter table tt rename db_tt;/*将表名修改为db_tt,注意：数据库不区分大小学不能将名字改为TT、Tt、tT*/

/**********************************************************************************************************************/

/*添加、删除、修改字段（列）*/
desc db_tt;
alter table db_tt add sname varchar(20) not null;
alter table db_tt add age tinyint unsigned default '18';
alter table db_tt add gender enum('男','女') default '男';
alter table db_tt add address varchar(255);
alter table db_tt add str1 int;
alter table db_tt add str2 varchar(255) first;/*first代表把该字段放在首列*/
alter table db_tt add chenghu char(2) generated always as (if(gender='男','先生','女士'));
alter table db_tt drop str2;/*删除str2字段*/
/*修改列*/
alter table db_tt rename column str1 to sfzh;/*column表示列，修改列名*/
alter table db_tt modify id int unsigned not null auto_increment primary key ;/*修改列的属性，将id改为主键*/
alter table db_tt modify sfzh varchar(255) after gender;/*modify表示调整，修改列的类型（可以保持不便但是必须写），位置*/
alter table db_tt modify sfzh varchar(20);/*修改精度为20*/
insert into db_tt(sname, age, gender, sfzh, address) values('wzt','24','男','452525199810126666','河南鹿邑'),('yz','23','女','452525199810126666','河南郑州'),('李四','23','女','452525199810126666','河南郑州');
select * from db_tt;

/**********************************************************************************************************************/

/*复制表结构并建立表*/
create table db_ttcp like db_tt;/*拷贝db_tt,生成空表db_ttcp*/
desc db_ttcp;
select * from db_ttcp;
create table db_ttcp2 as select id,db_tt.sname from db_tt;/*跟具需求复制一个没有约束的空表*/
desc db_ttcp2;
select * from db_ttcp2;
create table db_ttcp3 as select id,db_tt.sname,db_tt.address from db_tt where db_tt.sname like '%z%';/*跟具需求复制名字中含’z‘的数据并一个没有约束的表*/
desc db_ttcp3;
select * from db_ttcp3;