首先,创建一张学生表student
create table student(
sno int(6),-- 学号
name varchar(10),
sex char(1),
age int(2),
enterdate date,
classname varchar(10), -- 课程名称
email varchar(16)
);
desc student;-- 查看表的结构
show create table student; --查看建表语言
表结构如下:
1.DML(针对表中的记录,数据):insert,update,delete
(1)insert的几种操作:
insert into student values(1,"张三","男",16,"2001-10-10","java","gdgkms.com");
insert into student values(2,"李四",'男',23,'1996/12/12','javascript',"xkhuhu.com");-- 单双引号不区分,日期有多种写法
insert into student(name,age,email)values("赵六",23,"hhhsdliosa.com"); -- 注意:关键字,表名,字段名不区分大小写
一个insert也可以同时插入多条记录;
insert into student values(3,"李三",'女',3,'1996/10/12','javascript',"xhuhu.com"),(4,"李四",'男',23,'1990/09/12','javascript',"huhu.com");
(2)update操作:
update student set sex="女" where sno=2;
update student set sex="男",age=100,classname="python" where SNO=2;
(3)delete操作:
delete from student where sno=2;
2.DML(针对数据库对象:如表,索引,视图等…):create,alter,drop
(1)alter操作
eg:修改表的结构 增加列取名为score…
alter table student add score double(5,2);-- 5是添加数据的总位数,2是保留的小数位数
alter table student add score3 double (5,1) first;-- 在表的第一列插入新的一列score3
alter table student add score33 double(9,3) after classname;-- 在字段classname后添加新的一列 score33
eg:修改表的结构 修改列
update student set score=99.66 where sno=3;
alter table student modify score double(4,1);-- 修改之后保留一位小数
eg:删除列
alter table student drop score;
alter table student drop score 3;
alter table student drop score33;
(2)drop操作:删除表
drop table student;
(3)create操作:创建表
create table student;