DESC t_dept ALTER TABLE t_dept ADD descri VARCHAR(20) ALTER TABLE t_dept ADD decribe VARCHAR(20) FIRST ALTER TABLE t_dept ADD descr VARCHAR(100) AFTER deptno ALTER TABLE t_dept DROP descri ALTER TABLE t_dept DROP decribe DESCRIBE t_dept ALTER TABLE t_dept MODIFY descr INT DESC t_dept ALTER TABLE t_dept CHANGE descr me INT DESC t_dept ALTER TABLE t_dept CHANGE me he VARCHAR(100) DESC t_dept ALTER TABLE t_dept MODIFY he VARCHAR(100) FIRST DESC t_dept ALTER TABLE t_dept MODIFY he VARCHAR(100) AFTER loc DESC t_dept DROP TABLE t_dept CREATE TABLE t_dept( deptno INT NOT NULL, dname VARCHAR(20), loc VARCHAR(40) ) DESC t_dept DROP DATABASE test1 CREATE DATABASE company; USE company; CREATE TABLE t_dept( deptno INT NOT NULL, dname VARCHAR(20) DEFAULT 'cjgong', loc VARCHAR(40) ) DROP DATABASE company CREATE DATABASE company; USE company CREATE DATABASE t_dept( deptno INT, dname VARCHAR(20) UNIQUE, loc VARCHAR(40) ) DESC t_dept; CREATE TABLE t_dept( deptno INT, dname VARCHAR(20), loc VARCHAR(40), CONSTRAINT uk_name UNIQUE(dname) ); CREATE TABLE t_dept( deptno INT, dname VARCHAR(20), loc VARCHAR(40), CONSTRAINT pk_name PRIMARY KEY(dname) ) CREATE DATABASE company; USE company CREATE TABLE t_dept( deptno INT, dname VARCHAR(20), loc VARCHAR(40), CONSTRAINT pk_dname_deptno PRIMARY KEY(deptno,dname) ) CREATE DATABASE company; USE company; CREATE TABLE t_dept( deptno INT PRIMARY KEY AUTO_INCREMENT, dname VARCHAR(20), loc VARCHAR(40) ) ALTER TABLE t_dept ADD deptno1 INT; CREATE TABLE t_employee( empno INT PRIMARY KEY, ename VARCHAR(20), job VARCHAR(40), MGR INT, Hiredate DATE, sal DOUBLE(10,2), comm DOUBLE(10,2), deptno INT, CONSTRAINT fk_deptno FOREIGN KEY(deptno) REFERENCES t_dept(deptno) ); DESC t_employee DESC t_dept
1 DESC t_dept 2 3 ALTER TABLE t_dept 4 ADD descri VARCHAR(20) 5 6 ALTER TABLE t_dept 7 ADD decribe VARCHAR(20) FIRST 8 9 ALTER TABLE t_dept 10 ADD descr VARCHAR(100) 11 AFTER deptno 12 13 ALTER TABLE t_dept 14 DROP descri 15 16 ALTER TABLE t_dept 17 DROP decribe 18 19 DESCRIBE t_dept 20 21 ALTER TABLE t_dept 22 MODIFY descr INT 23 24 DESC t_dept 25 26 ALTER TABLE t_dept 27 CHANGE descr me INT 28 29 DESC t_dept 30 31 ALTER TABLE t_dept 32 CHANGE me he VARCHAR(100) 33 34 DESC t_dept 35 36 ALTER TABLE t_dept 37 MODIFY he VARCHAR(100) FIRST 38 DESC t_dept 39 40 41 ALTER TABLE t_dept 42 MODIFY he VARCHAR(100) 43 AFTER loc 44 45 DESC t_dept 46 47 DROP TABLE t_dept 48 49 CREATE TABLE t_dept( 50 deptno INT NOT NULL, 51 dname VARCHAR(20), 52 loc VARCHAR(40) 53 ) 54 55 DESC t_dept 56 57 DROP DATABASE test1 58 59 CREATE DATABASE company; 60 USE company; 61 CREATE TABLE t_dept( 62 deptno INT NOT NULL, 63 dname VARCHAR(20) DEFAULT 'cjgong', 64 loc VARCHAR(40) 65 ) 66 67 DROP DATABASE company 68 CREATE DATABASE company; 69 USE company 70 71 CREATE DATABASE t_dept( 72 deptno INT, 73 dname VARCHAR(20) UNIQUE, 74 loc VARCHAR(40) 75 ) 76 77 DESC t_dept; 78 79 CREATE TABLE t_dept( 80 deptno INT, 81 dname VARCHAR(20), 82 loc VARCHAR(40), 83 CONSTRAINT uk_name UNIQUE(dname) 84 ); 85 86 CREATE TABLE t_dept( 87 deptno INT, 88 dname VARCHAR(20), 89 loc VARCHAR(40), 90 CONSTRAINT pk_name PRIMARY KEY(dname) 91 ) 92 CREATE DATABASE company; 93 USE company 94 95 CREATE TABLE t_dept( 96 deptno INT, 97 dname VARCHAR(20), 98 loc VARCHAR(40), 99 CONSTRAINT pk_dname_deptno PRIMARY KEY(deptno,dname) 100 ) 101 102 CREATE DATABASE company; 103 USE company; 104 105 CREATE TABLE t_dept( 106 deptno INT PRIMARY KEY AUTO_INCREMENT, 107 dname VARCHAR(20), 108 loc VARCHAR(40) 109 ) 110 ALTER TABLE t_dept 111 ADD deptno1 INT; 112 113 CREATE TABLE t_employee( 114 empno INT PRIMARY KEY, 115 ename VARCHAR(20), 116 job VARCHAR(40), 117 MGR INT, 118 Hiredate DATE, 119 sal DOUBLE(10,2), 120 comm DOUBLE(10,2), 121 deptno INT, 122 CONSTRAINT fk_deptno FOREIGN KEY(deptno) 123 REFERENCES t_dept(deptno) 124 ); 125 126 DESC t_employee 127 128 DESC t_dept 129 DROP TABLE t_employee 130 DROP TABLE t_dept 131 132 USE company 133 134 CREATE TABLE t_dept( 135 deptno INT, 136 dname VARCHAR(20), 137 loc VARCHAR(40), 138 INDEX index_deptno(deptno) 139 ) 140 SHOW CREATE TABLE t_dept 141 142 EXPLAIN 143 SELECT *FROM t_dept WHERE deptno=1 144 145 CREATE INDEX index_deptno 146 ON t_dept (deptno) 147 148 SHOW CREATE TABLE t_dept 149 150 ALTER TABLE t_dept 151 ADD INDEX index_deptno(deptno) 152 153 SHOW CREATE TABLE t_dept 154 155 CREATE TABLE t_dept( 156 deptno INT UNIQUE, 157 dname VARCHAR(20), 158 loc VARCHAR(40), 159 UNIQUE INDEX index_deptno(deptno) 160 ) 161 162 163 EXPLAIN 164 SELECT *FROM t_dept WHERE deptno=10 165 166 #mysql -h localhost -u root -p 167 168 CREATE UNIQUE INDEX index_deptno 169 ON t_dept(deptno) 170 171 EXPLAIN 172 SELECT *FROM t_dept WHERE dname='cjgong' 173 174 CREATE FULLTEXT INDEX index_loc 175 ON t_dept(loc) 176 177 SHOW CREATE TABLE 178 179 ALTER TABLE t_dept 180 ADD FULLTEXT INDEX index_loc(loc)