管理表

/*删除表*/
DROP TABLE t_year;




SELECT * FROM t_student;
/*复制表*/
CREATE TABLE t_copyStudent SELECT * FROM t_student;
SELECT * FROM t_copyStudent WHERE 1=0;
/*值复制表结构，不复制表的内容*/
CREATE TABLE t_cpoyStudent2 SELECT * FROM t_student 
WHERE 1=0;
SELECT * FROM t_cpoyStudent2;
CREATE TABLE t_cpoy3 LIKE t_student;


SELECT * FROM t_cpoy3;


/*修改表*/
SELECT *FROM t_student;
/*增加一列数据*/
ALTER TABLE t_student ADD phone BIGINT;
/*删除一列数据 column列*/
ALTER TABLE t_student DROP COLUMN phone;
/*修改列的名称*/
ALTER TABLE t_student CHANGE COLUMN phonenum phonenumber BIGINT;
ALTER TABLE t_student CHANGE COLUMN phonenumber phonenum BIGINT;
/*修改列的数据类型*/
ALTER TABLE t_student CHANGE phonenumber phonenumber BIGINT;
ALTER TABLE t_student MODIFY phonenumber VARCHAR(100);
/*修改表名*/
ALTER TABLE t_student RENAME t_stu;
SELECT * FROM t_stu;


/*创建索引*/
CREATE INDEX nameIndex ON t_stu(s_name);
SELECT * FROM t_stu WHERE s_name="houyuan";
SELECT * FROM t_stu WHERE s_name="%yuan";#索引不会起作用
SELECT * FROM t_stu WHERE s_name="hou%"


/*删除索引*/
ALTER TABLE t_stu DROP INDEX nameIndex;