对数据操作新增 修改 删除-----DML（重点）
-- 新增 这种写法 数据的顺序和数据库中字段顺序保持一致
insert into student values(1,"张三","男",12,5.2,"2020-09-09","java01","123@qq");
-- 日期可以'yyyy-MM-dd' 'yyyy/MM/dd' 'yyyy.MM.dd'
insert into student values(3,'张三','男',12,52.2,"2020-09-09","java01","123@qq");
-- sysdate() CURRENT_DATE() now() 可以获得当前时间
insert into student values(3,'张三','男',12,52.2,CURRENT_DATE(),"java01","123@qq");
-- 批量添加内容
insert into student3 values (5,'tianqi','男'),(6,'zhaoliu','女'), (7,'lisi','男')
-- 添加部分数据
insert into student3 set sno = 8,name = 'lisi2' ;
-- 给指定的列添加数据
insert into student(name) values('张三');
insert into student(sno,name) values(2,'张三');
-- 修改操作 把 sno=1 的学生年龄 修改成18岁
update student set age=18 where sno=1
-- 把sno=2且性别为男的学生 的年龄修改成60 出生年月修改成2000-09-09
update student set age=60,dtdate='2000-09-09' where sno=2 and sex='男';
-- 把sno=3 的学生的年龄修成18岁 只要满足条件都会修改
update student set age=18 where sno=3
-- 删除 把sno=2的学生信息删除
delete from student where sno=2;
-- 表中所有数据都会删除
delete from student
-- 删除表中所有数据 注意 使用事务没有办法回滚
truncate table student3
约束
PRIMARY KEY--主键乐视
NOT NULL-- 非空约束
UNIQUE--唯一约束
CHECK -- 检查约束
DEFAULT--默认约束
AUTO_INCREMENT--自增约束
FOREIGN KEY--外键
-- 列级约束 ----------------------DDL
create table student(
sno int(4) primary key auto_increment,
name varchar(12) not null,
sex char(1) default '男' check (sex ='男' or sex ='女'),
age int(3) check (age>18 and age<30),
enterdate date, classname varchar(10),
email varchar(20) unique
);
-- 表级约束 ----------------------DDL
create table student(
sno int(10),
name varchar(12) not null,
sex char(2) default '男',
age int(2),
enterdate date,
classname varchar(20),
email varchar(20),
constraint pk_stu primary key(sno) ,
constraint uk_stu_email unique(email),
constraint ck_stu_sex check (sex ='男'or sex='女'),
constraint ck_stu_age check(age>18 and age< 30)
);
-- 创建好的表可以添加约束 ----------------------DDL
alter table student add constraint ck_stu_age check(age>18 and age< 30)
alter table student add constraint ck_stu_sex check (sex ='男'or sex='女'),
alter table student add constraint pk_stu primary key(sno)
