mysql 语句

规范：

         关键字与函数名称全部大写

         数据库名称，表名称，字段名称全部小写

         sql语句必须以分号结尾

mysql手册参考地址：

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format

SELECT VERSION()     : 当前使用版本

SELECT USER() ： 当前用户

SELECT NOW() ： 当前时间

SHOW WARNINGS ： 报错信息

SHOW CREATE DATABASE mydatabasename ： 显示创建数据库信息

truncate table tablename：清空表数据保留表结构

时间戳转date格式分组查询：

select DATE_FORMAT(FROM_UNIXTIME(create_time),'%Y-%m-%d') as new_time from cmf_user group by new_time

 

//以下是常用sql  自己写的典型的仅供和博主同段位的小白参观  没有注释的以后会加

创建：create database if not exists mydatabasename character set gbk/utf8;

修改：alter database mydatabasename character set = utf8/gbk;

删除：drop database dbname

create table if not exists table_name (

              id int(11) primary key auto_increment

              username varchar(20) not null,

)

show tables  //+ or 不+ from tablename

show indexes from provinces

show COLUMNS from tb1    ===    desc tb1

//主键约束

create table tb1 (
id smallint unsigned AUTO_INCREMENT primary key,
name varchar(20) not null,
pwd varchar(35) not null
)

//默认约束
create table tb(
id smallint unsigned auto_increment primary key,
name varchar(30) default 'lxp'
)

 

//外键实例

create table provinces(
id smallint unsigned primary key auto_increment,
pname varchar(30) not null
)

create table users(
id smallint unsigned primary key auto_increment,
username varchar(10) not null,
pid smallint unsigned,
foreign key (pid) references provinces (id)
)

create table users1(
id smallint unsigned primary key auto_increment,
username varchar(10) not null,
pid smallint unsigned,
foreign key (pid) references provinces (id) on delete cascade
)

 

//添加单列  after  在某列后  first最前  忽略默认最后

alter table table_name add age tinyint unsigned not null default 0;

alter table table_name add passsword varchar(35) not null after column_name;

alter table table_name add truename varchar(20) not null first;

 

//删除列

alter table users drop truename

alter table users drop passsword,drop age

 

//添加约束

create table users2(
username varchar(10) not null,
pid smallint unsigned
)

alter table users2 add id smallint unsigned

alter table users2 add constraint PK_users2_id primary key (id)

alter table users2 add unique (username)

alter table users2 add foreign key (pid) references provinces (id)

 

alter table users add age tinyint unsigned not null

alter table users2 alter age set default 15

alter table users2 alter age drop default

alter table users2 drop primary key

alter table users2 drop index username

alter table users2 drop foreign key users2_ibfk_1

 

//修改列定义

alter table users2 modify id smallint unsigned not null first

alter table users2 change pid p_id tinyint unsigned not null

 

//修改数据表

alter table user2 rename [as/to/null] users2

rename table users2 to user2 [,users2 to ......]