计算机知识

当前位置:澳门新葡萄京 > 计算机知识 > (2) 通过查询现存的表创建

(2) 通过查询现存的表创建

来源:http://www.hhmtch.com 作者:澳门新葡萄京 时间:2019-12-06 20:11

mysql DDL&DML 语言,mysqlddl

DDL:数据定义语言

  CREATE, ALTER, DROP

CREATE相关的常用命令:

CREATE DATABASE
CREATE EVENT
CREATE FUNCTION
CREATE FUNCTION UDF
CREATE INDEX
CREATE PROCEDURE
CREATE SERVER
CREATE TABLE
CREATE TABLESPACE
CREATE TRIGGER
CREATE USER
CREATE VIEW

创建表:CREATE TABLE
(1) 直接创建;

CREATE TABLE tablename(id INT(11) NOT NULL,name VARCHAR(256) NOT NULL);  

(2) 通过查询现存的表创建;新表会被直接插入查询而来的数据;

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
[table_options]
[partition_options]
select_statement
eg:
CREATE TABLE MYTAB2 select a.StuID,a.Name from hellodb.students as a;

(3) 通过复制现存的表的表结构创建;不复制数据;

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
{ LIKE old_tbl_name | (LIKE old_tbl_name) }

mysql> CREATE TABLE lala123 like hellodb.teachers;

(4) 查看表结构:
DESCRIBE tbl_name;

mysql> desc teachers;
 -------- ---------------------- ------ ----- --------- ---------------- 
| Field  | Type                 | Null | Key | Default | Extra          |
 -------- ---------------------- ------ ----- --------- ---------------- 
| TID    | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| Name   | varchar(100)         | NO   |     | NULL    |                |
| Age    | tinyint(3) unsigned  | NO   |     | NULL    |                |
| Gender | enum('F','M')        | YES  |     | NULL    |                |
 -------- ---------------------- ------ ----- --------- ---------------- 
4 rows in set (0.00 sec)

mysql> show create table teachersG
*************************** 1. row ***************************
Table: teachers
Create Table: CREATE TABLE `teachers` (
`TID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`Name` varchar(100) NOT NULL,
`Age` tinyint(3) unsigned NOT NULL,
`Gender` enum('F','M') DEFAULT NULL,
PRIMARY KEY (`TID`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

(5) 查看表状态信息:
SHOW [FULL] TABLES [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr]

mysql> show tables;
 ------------------- 
| Tables_in_hellodb |
 ------------------- 
| classes           |
| coc               |
| courses           |
| lala123           |
| scores            |
| students          |
| teachers          |
| toc               |
 ------------------- 
8 rows in set (0.00 sec)

修改表:ALTER TABLE

ALTER TABLE tablename MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]

(1)增加表字段:

ALTER TABLE tablename ADD [COLUMN] column_definition [FIRST | AFTER col_name];  
如给上表添加age字段:  
ALTER TABLE tablename ADD age INT(11) NOT NULL;   

(2)删除表字段:

ALTER TABLE tablename DROP [COLUMN] col_name;  
如删除上表age字段:  
ALTER TABLE tablename DROP age;  

(3)修改字段:

ALTER TABLE tablename CHANGE [COLUMN] old_col_name new_col_definition [FIRST | AFTER col_name];  
如修改上表字段name名为uname:  
ALTER TABLE tablename CHANGE name uname CHAR(128);  

CHANGE和MODIFY都可以修改表字段定义,不同的是CHANGE写两次字段名,但是CHANGE可以修改列名,而MODIFY则不能。 

(4)修改字段顺序:

在上表中添加birth字段,并放到列id后面:  
ALTER TABLE tablename ADD birth DATETIME AFTER id;  
再次修改,把它放到uname表后面:  
ALTER TABLE tablename MODIFY birth DATETIME NOT NULL AFTER uname;  

(5)修改表明:

ALTER TABLE tablename RENAME [TO] new_tablename;  
如把上面表名tablename改成test:  
ALTER TABLE tablename RENAME test;

 

删除表:DROP TABLE

mysql> show tables;
 ---------------- 
| Tables_in_mydb |
 ---------------- 
| mytb1          |
| mytb2          |
| mytb3          |
| mytb4          |
 ---------------- 
4 rows in set (0.00 sec)

mysql> drop table mytb4;
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
 ---------------- 
| Tables_in_mydb |
 ---------------- 
| mytb1          |
| mytb2          |
| mytb3          |
 ---------------- 
3 rows in set (0.00 sec)

删除表信息 DELETE  注:一定要添加条件,否则整表信息被删除。

delete from tableName where ......
eg:
mysql> SELECT * FROM mydb.mytb4;
 ----- --------------- ----- -------- 
| TID | Name          | Age | Gender |
 ----- --------------- ----- -------- 
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
 ----- --------------- ----- -------- 
4 rows in set (0.00 sec)

mysql> DELETE FROM mytb4 WHERE TID = 3;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM mydb.mytb4;
 ----- --------------- ----- -------- 
| TID | Name          | Age | Gender |
 ----- --------------- ----- -------- 
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   4 | Lin Chaoying  |  93 | F      |
 ----- --------------- ----- -------- 
3 rows in set (0.00 sec)

 sql语句在mysql客户端中可以使用help名称查看语句语法。

DDLDML 语言,mysqlddl DDL:数据定义语言 CREATE, ALTER, DROP CREATE相关的常用命令: CREATE DATABASE CREATE EVENT CREATE FUNCTION CREATE FUNCTION UDF CREATE I...

本文由澳门新葡萄京发布于计算机知识,转载请注明出处:(2) 通过查询现存的表创建

关键词:

上一篇:今天博主有一个Xcode7上传app的ERROR的需求

下一篇:没有了