MySQL数据库常用SQL语句

//登入和退出数据库
1:mysql -u root -p//输入密码
2:exit

//数据库的创建，删除和进入数据库
3:Create Database 'dataBase1';
4:Drop Database 'dataBase1';
5:Use dataBase1;

//表的创建和删除，创建表的同时设置属性
6:Create Table table1(
	id int primary key auto_increment,
	name varchar(32) unique,
	age int not null,
	sex boolean
);

//查看表
7:desc table1;
8:show create table table1;

//修改表
//修改表名
9:alter table table1 rename table2;
//修改表属性数据类型
10:Create table t1(
	id int primary key auto_increment,
	age varchar(20),
	name varchar(20)
);
11:alter table t1 modify id varchar(20);
12:alter table t1 modify age int;
//增加字段
13:alter table t1 add age int not null after id;desc t1;
//删除字段
14:alter table t1 drop age;desc t1;
//修改属性排列顺序
15:alter table t1 modify no int after name;desc t1;

//索引

//创建带索引的数据库表
16:Create table t1(
	id int,
	name varchar(20),
	age int,
	index index1(id)
);
//唯一索引
17:Create table t1(
	id int,
	name varchar(20),
	age int,
	unique index index1(id)
);
//全文索引
18:Create table t1(
	id int,
	name varchar(20),
	age int,
	fulltext index index1(id)
);

//Alter语句添加索引
19:create table t1(id int);
20:alter table t1 add index index1(id);
21:create table t2 (id int,age int,name varchar(20),sex boolean,info varchar(50));
22:alter table t2 add unique index index3 (sex);

//删除索引
23:drop index index1 on tt2;
24:show create table tt2;
25:drop table t1,t2;