MySQL语法练习一

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)

 

posted @ 2014-03-30 16:37  zhangyongjian  阅读(300)  评论(0编辑  收藏  举报