查看數據庫
show databases;
使用數據庫
use [數據庫名]
如:use mysql
創建數據庫
CREATE DATABASE bruce DEFAULT CHARACTER SET utf8
刪除數據庫
drop database bruce
查詢mysql版本
select version();
查詢當前用戶
select user();
顯示當前庫的所有表
show tables;
創建用戶
create user ysb
賦予權限
GRANT ALL PRIVILEGES ON *.* TO 'ysb'@'%' IDENTIFIED BY 'ysb' WITH GRANT OPTION;
創建表
CREATE TABLE `cobar_test` ( `user_id` bigint(20) NOT NULL AUTO_INCREMENT, `user_name` varchar(100) DEFAULT NULL, `password` varchar(100) DEFAULT NULL, PRIMARY KEY (`user_id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
查看表結構
DESCRIBE goods
刪除表
drop table goods; drop table goods CASCADE CONSTRAINTS;//級聯刪除約束
語法:ALTER TABLE 【表名】ADD CONSTRAINT 【約束名】PRIMARY KEY(列名)
ALTER TABLE goods ADD CONSTRAINT goodsType_pk PRIMARY KEY (id);
刪除主鍵約束
ALTER table goods DROP PRIMARY KEY
增加外鍵約束
ALTER TABLE goods ADD CONSTRAINT goods_fk foreign KEY (goodsType) references goodsType(id) ;
刪除外鍵約束
ALTER table goods DROP FOREIGN KEY goods_fk
增加唯一性約束
ALTER TABLE itemDetail ADD CONSTRAINT itemDetail_uk UNIQUE (goodsId,orderId);
刪除唯一約束
ALTER TABLE itemDetail DROP INDEX itemDetail_uk;
增加列
alter table file_info add convert_status varchar(50);
刪除列
alter table file_info drop column convert_status;
修改列
alter table file_info change convert_status convert_status2 int(11);