增
存放数据: create table tb_student();
增: insert into tb_student value(‘余’,20,‘文理’,‘13891716424’,‘计算机’);
eg:create table tb_student(name char(32),age int,c_name varchar(32),
-> phoneNum varchar(32),major varchar(32));
增:insert into tb_student(name) value (‘大校’);
批量增:insert into tb_student value(‘刘倩’,21,‘文理’,‘13891716424’,‘计算 机’),
-> (‘妙仔’,20,‘高新’,‘13891716424’,‘电气’);
创主键
create table tb_学生(id int primary key auto_increment,
-> name varchar(32) not null,
-> gender varchar(2) not null default ‘男’);
增加值
insert into tb_学生(id,name,gender) value(‘aaa’,‘张三’,‘女’);
关系表
create table r_student1_subject(id int auto_increment prinement primary key,sid int,cid int,score int);
删
全部删:delete from tb_student;
有事物过程
全部删:truncate tb_student;
无事物过程
drop
直接讲表结构也删了
有条件删:delete from tb_student where name=‘余’;
*等于号可以改变,为删除的条件要满足的东西
改
改:update tb_student set age = 19; 改年龄
有条件改:update tb_student set age = 20 where name = ‘妙仔’ ; 改名字为妙仔的年龄
该多个字段:update tb_student set age = 20 ,phoneNum = ‘13891716424’,major = ’ 机械’ where name = ‘妙仔’;
查
查询是尽量不要再select后跟 * ,因为 * 会屏蔽优化器,写 * 叫通表查询
查询表:show tables;
查: select * from tb_student;
迪卡尔机
select * from tb_student1 , tb_subject;
select * from tb_student1 , tb_subject,r_student1_subject;
查出来u所有的的结果,且满足两个id的相等才是结果
select * from tb_student1 , tb_subject,r_student1_subject,where tb_student1.id = r_student_subject.sid and tb_subject.id = r_student_subject.cid;
查出来想要的而结果,筛选后的结果,用重命名查(as可省略),称表别名,
select a.name ‘姓名’, b.name ‘课程’ from tb_student1 a,tb_subject b,r_student_subject c where a.id = c.id and b.id = c.id;
字段查:select name from tb_student where age = 20; 查询年龄是20的名字
多表联查