巩固MySql基本语法

创建数据库
create database dbname;

显示数据库
show database;

使用数据库
user dbname;

显示该数据库中的表
show tables;

删除数据库
drop database dbname;

创建表
create table tablename(column_name1 column_type1 constrains,
column_name2 column_type2 constrains,
column_name3 column_type3 constrains
)

查看表定义
desc tablename

查看创建表的sql
show create table tablename \G;

删除表
drop table tablename;

修改表
alert table tablename modify column_definition [first|after col_name];
eg: alert table emp modify ename varchar(20);

增加表字段
alter table tablename add column_definition [first|after col_name]
eg: alter table emp add age int(3);

删除字段
alert table table name drop col_name;
eg:alter table amp drop age;

字段改名
alert table table name change old_col_name new_col_name column_definition [first | after col_name]
eg:alter table emp change age age1 int(4);

更改表名
alter table tablename rename new_tablename

插入记录
insert into table (field1,field2,…) values (value1,value2,…)
eg:insert into emp (enama,hiredate,sal,depto) values (‘zzx1’,2000-01-01,’3000’,2)
可依次插入多条字段

更新记录
update tablename set field1=value1,field2=value2,… [where condition]
可同时更新多个表的数据
update t1,t2,…,tn set t1.field1=value1,t2.field2=value2,… [where condition]

删除记录
delete from tablename [where condition]
eg: delete from emp where ename=‘done’
可以依次删除多个表的记录
delete t1,t2,…,tn from t1,t2,…,tn [where condition]
eg:delete a,b from amp a,dept b where a.deptno=b.deptno and a.deptno =3