MySQL 增删改查基础
终端登录mysql：
mysql -u root -p
1111aaaa
创建数据库：
create DATABASE DBname;
删除数据库：
drop DATABASE DBname;
创建数据表：
create table student( `stu_id` INT UNSIGNED AUTO_INCREMENT,
`stu_name` VARCHAR(8) NOT NULL,
`stu_sex` CHAR(4),
`stu_brethday` DATE,
PRIMARY KEY(`stu_id`))
ENGINE = InnoDB DEFAULT CHARSET = utf8;
create table teacher_info (
`t_id` INT UNSIGNED AUTO_INCREMENT,
`t_name` varchar(8) NOT NULL,
`t_sex` CHAR(4),
`t_birthday` DATE,
PRIMARY KEY(`t_id`))
ENGINE = InnoDB DEFAULT CHARSET = utf8;
删除数据表：
drop table table_name;
— 新增
填写部分字段信息插入
INSERT INTO student(stu_name,stu_sex,stu_brethday)VALUES(‘张三’,’男’,now());
填写所有字段信息插入
INSERT INTO studentVALUES(‘5′,’张三’,’男’,now());
— 删除
delete from student where stu_sex is null;
— 修改
update student set stu_name = ‘张某某’ where stu_name = ‘张某’;
— 查询
指定字段查询
select stu_name,stu_brethday from student;
查询所有字段
select * from student;
where条件查询
select * from student where stu_sex = ‘男’;
select * from student where stu_sex = ‘男’ or stu_sex = ‘女’;
select * from student where stu_id between 1 and 3;
limit字段
select * from student limit 2;
嵌套查询
select * from student where stu_id in (select t_id from teacher_info);
ORDER BY 排序
select * from student order by stu_id;
select * from student order by stu_id desc;
union 结果集合并
select stu_name from student union select t_name from teacher_info;
group by 分组
select stu_name,sum(stu_id) from student group by stu_name;
多表连接查询
内连接
select * from student a inner join teacher_info b on a.stu_id=b.t_id;
左连接
select * from student a left join teacher_info b on a.stu_id=b.t_id;
右连接
select * from student a right join teacher_info b on a.stu_id=b.t_id;