mysql數據庫學習——3,表的創建,刪除和變更
表創建 create table mytbl( id int auto_increment not null, name char(10) not null, birth date not null, wight int, sex enum('f','m') ); 指定數據庫引擎的表創建 create table mytbl( id int auto_increment not null, name char(10) not null, birth date not null, wight int, sex enum('f','m') )engine=memory; 創建臨時表 create temporary table mytbl( id int auto_increment not null, name char(10) not null, birth date not null, wight int, sex enum('f','m') );
復制數據表結構 (也可以這樣創建一個臨時表作副本create temporary table tblnew like tblold) create table tblnew like tblold 同時復制數據 insert into tblnew select * from tblold 從查詢結果創建表 create table tblnew select * from tblold
刪除數據表
drop table tbl_name drop table tbl_name ,tbl2_name drop table if exists tbl_name drop temporary table tbl_name
修改數據表
修改數據列的數據類型 alter table mytbl modify i mediumint unsigned alter table mytbl chang i i mediumint unsigned 修改數據列的數據類型和字符集 alter table mytbl modify i mediumint character set ucs2 修改數據表的存儲引擎 alter table mytbl engine=engine_name
重命名數據表 alter table tbl_name rename to new_tbl_name rename talbe tbl_name to new_tbl_name 重命名多個數據表 rename talbe tbl_name to new_tbl_name,t1 to t2 ,t3 to t4 移動數據表到另一個數據庫 alter table db1.tbl_name rename to db2.new_tbl_name rename talbe db1.tbl_name to db2.new_tbl_name