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)
