MySql命令符操作语句

Mysql控制台操作

命令提示符mysql操作

创建库
create database '数据库名' charset utf8; 

创建表
create table member(
    id int auto_increment primary key,
    member_name varchar(32) not null unique comment '注册帐号',    
    createtime datetime not null default current_timestamp comment'注册时间',
    logid int,
    foreign key(logid) references 表(id))    
    default character set utf8;

插入操作
insert into member(name)values('bill');

查询表结构
desc 表名;

删除表
drop table 数据表名；

删除库
DROP DATABASE repertory;

字段唯一约束
ALTER TABLE member_info ADD unique(member_id)；


添加字段
ALTER TABLE `member_info` ADD `nationality` VARCHAR(64) NOT NULL COMMENT '国家' AFTER `member_email`; 


查询库
show databases;

查询表
show tables;

使用库
use 库名

退出exit \c

修改字段类型
alter table 表名 modify column 字段名 类型 not null;

清空表
truncate table wp_comments;

查看数据库存放位置
show global variables like "%datadir%";

查看表外键
show global variables like 'port'; 

SQL语句

按年和月查找
select * from trading_log where date_format(trading_time,'%Y%m') = 202010;

格式日期
date_format(trading_time, '%Y-%m-%d')

创建省市类型关系表

CREATE TABLE province
( 
 proID INT PRIMARY KEY NOT NULL,
 proName VARCHAR(50) NOT NULL
)
go
insert into province(proID,proName) values(1,'北區')
insert into province(proID,proName) values(2,'東區')
insert into province(proID,proName) values(3,'葵青區')
insert into province(proID,proName) values(4,'荃灣區')
insert into province(proID,proName) values(5,'灣仔區')
insert into province(proID,proName) values(6,'中西區')
insert into province(proID,proName) values(7,'長洲區')
insert into province(proID,proName) values(8,'觀塘區')
insert into province(proID,proName) values(9,'南區')
insert into province(proID,proName) values(10,'沙田區')
insert into province(proID,proName) values(11,'西貢區')
insert into province(proID,proName) values(12,'大埔區')
insert into province(proID,proName) values(13,'黃大仙區')
insert into province(proID,proName) values(14,'南丫島區')
insert into province(proID,proName) values(15,'深水埗區')
insert into province(proID,proName) values(16,'油尖旺區')
insert into province(proID,proName) values(17,'大嶼山區')
insert into province(proID,proName) values(18,'九龍城區')
insert into province(proID,proName) values(19,'坪洲區')
insert into province(proID,proName) values(20,'屯門區')
insert into province(proID,proName) values(21,'元朗區')
go
CREATE TABLE city
( 
 cityID INT PRIMARY KEY IDENTITY(1,1)  NOT NULL,
 proID INT FOREIGN KEY REFERENCES province(proID),
 cityName VARCHAR(50) NOT NULL
)

insert into city(proID,cityName) values(10,'大圍')
insert into city(proID,cityName) values(10,'禾輋')
insert into city(proID,cityName) values(10,'火炭')
insert into city(proID,cityName) values(10,'九肚山')
insert into city(proID,cityName) values(10,'馬料水')
insert into city(proID,cityName) values(10,'石門')
insert into city(proID,cityName) values(10,'小瀝源')
insert into city(proID,cityName) values(16,'大角咀')
insert into city(proID,cityName) values(16,'尖沙咀')
insert into city(proID,cityName) values(16,'柯士甸')
insert into city(proID,cityName) values(16,'太子')
insert into city(proID,cityName) values(16,'旺角')
insert into city(proID,cityName) values(16,'油麻地')
insert into city(proID,cityName) values(16,'佐敦')