MySQL中常用的sql语句（增删改查）

增

存放数据： 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的名字
多表联查