MYSQL操作のDDL
, : 、 、 ;
:CREATE、 ALTER、DROP
. (DDL)
1.
:CREATE DATABASE
:
CREATE DATABASE mydb1;( )
CREATE DATABASE mydb2 CHARACTER SET utf8; --
CREATE DATABASE mydb3 CHARACTER SET gbk COLLATE gbk_chinese_ci ; --
2.
SHOW DATABASES;
mydb2
SHOW CREATE DATABASE mydb2;
mydb3
DROP DATABASE mydb3;
3.
, mydb2 gbk;
ALTER DATABASE mydb2 CHARACTER SET gbk;
4.
DROP DATABASE mydb2;
5.
SELECT DATABASE();
USE mydb1;
. (DDL)
1.
:
create table (
1 ,
2 ,
...
n
);
:
int:
double: , double(5,2) 5 , 2 , 999.99;
char: ; char(10) 'abc '
varchar: ;varchar(10) 'abc'
text: ;
blob: ;
date: , :yyyy-MM-dd;
time: , :hh:mm:ss
timestamp: yyyy-MM-dd hh:mm:ss
datetime: yyyy-MM-dd hh:mm:ss
create table student(
id int,
name varchar(20),
chinese float,
english float,
math float
);
2.
:insert into [()] values ()
, , 。
insert into student(id,name,chinese,english,math) values(1,' ',89,78,90);
insert into student(id,name,chinese,english,math) values(2,' ',67,98,56);
insert into student(id,name,chinese,english,math) values(3,' ',87,78,77);
insert into student(id,name,chinese,english,math) values(4,' ',88,98,90);
insert into student(id,name,chinese,english,math) values(5,' ',82,84,67);
insert into student(id,name,chinese,english,math) values(6,' ',55,85,45);
insert into student(id,name,chinese,english,math) values(7,' ',75,65,30);
insert into student(id,name,chinese,english,math)
values(8,' ',89,78,90),
(9,' ',96,75,95),
(10,' ',81,52,78),
(11,' ',85,72,68);
3.
show tables;
4.
desc student;
alter :
alter table
// ,
[add [ ]]|[add]
// , 。 ,
[alter column ]
//
[drop column ]
// ,
[drop constraint ]
5. , sex, varchar
alter table student add age varchar(10);
6. age , int。
alter table student modify age int;
7. age , 。
alter table student drop age;
8. user。
rename table student to user;
9.
show create table user;
10. gbk
alter table user character set gbk;
11. name username
alter table user change name username varchar(50);
12. sid
alter table stu add CONSTRAINT PRIMARY key(sid);//
13.
drop table user;
間違いがあれば、訂正してくださいね~