MySQL：增删改查（实操+代码+注释）

1.数据库的基本操作

#显示所有数据库
show databases;
#创建数据库语句
create database  if not exists student;
#语句后面最好加上字符编码，实际操作中基本上都是使用utf8字母编码，（但是在控制台中---默认编码是简体中文格式GBK）
create database if not exists student charset=gbk
#删除数据库语句
drop database  if exists student;
#显示如何创建数据库
show create database student;
#更新数据库的字符编码
alter database student charset=gbk;

2.数据表的基本操作

#使用数据库
use student;
#显示该库的所有表
show tables;
#创建表  主键且不为负自动增长的，字段不能为空，default是默认值
create table if not exists classone(id int unsigned auto_increment primary key comment '主键id',name varchar(30) not null comment '姓名',address varchar(100) default '暂时未知' comment'住址')engine=innodb;
#创建表的简写
create table classtwo(id int primary key,name varchar(30) ,age int,gender varchar(5));
#查看表结构
desc classone;
#删除表
drop table if exists classone;
#向表中添加字段
alter table classone add phone varchar(20) comment '电话号码';
#向表中删除字段
alter table classone drop phone;
#决定添加数据的位置
alter table classone add gender varchar(1) after namer;
alter table classone add gender varchar(1) first;
#修改表中的字段类型
alter table classone change phone tellphone int(11);
alter table classone change modify phone varchar(13);
#重命名表中的名字，
alter table classone rename to class;

3.数据的基本操作

#插入数据，若按序插入数据，前面的字段名可省略
insert into classone (id, name, address) values(1,'张三','wuhan' );
#id是自动增长的可用null直接代替，将address对应值填上default，则会显示暂时未知
insert into classone values(null,'李四',default);
#插入多条数据
insert into classtwo values(1,'宋七',13,'男'),(2,'黄八',17,'女'),(3,'胡九',21,'男'),(4,'庞二',19,'女');
#删除某条数据
delete from classone where id = 2;
#删除表中所有数据，相对于delete来说truncate效率更高，delete就是遍历删除，truncate是直接干掉
truncate table classone;
#更新数据
update classone set name='赵六',address='XiangYang' where id=1 or id=2;

4.单表查询语句操作

#查询表中所有数据,as后面接的是别名
select * from as date classone;
#select可以做计算,dual为默认的尾表可省略
select 2+6 from dual;
#where条件筛选
select * from classone where address = 'wuhan';
#在条件筛选中可以使用in关键字,not in就反过来
select * from classone where address in('wuhan','XiangYang');
#(not) between and筛选条件
select * from classtwo where age between 14 and 18;
#查询为空的数据,不是空就在is后面加上not
select * from classtwo where age is null;
#聚合函数,例如平均值,总和,最大小值，次数
select avg(age) from classtwo;
select sum(age) from classtwo;
select count(name) from classtwo;
#like模糊查询,%只查所有姓李的数据,如果加一个下划线就只能查两个字的名字
select * from classone where name like '李%';
select * from classone where name like '李_';
#排序查询,按年龄升降序,asc是升序,desc是降序
select * from classtwo order by age asc;
select * from classtwo order by age desc;
#分组查询，分别统计男女的平均年龄,可以用gruop_concat()将数据聚合
select avg(age) as '年龄',gender as'性别' from classtwo group by gender;
select group_concat(name),gender from classtwo group by gender;
#having筛选,where是从表中进行筛选,但是having可以对结果进行筛选,列如对分组之后的结果筛选
select avg(age) as 'age' ,gender  from classtwo group by gender having age>17;
#limit筛选,从0开始查两个数据
select * from classtwo limit 0,2;
#distinct去重,去掉重复的住址
select count(distinct address) from classone; 

5.多表查询语句操作

#union联合查询,默认将第一张表的查询字段作为查询内容,distinct去除两张表的重复字段
select name,gender from classtwo UNION distinct select name,address from classone;
#inner内连接查询,方便代码演示创建一个socre表,通过两张表的字段连接就能查出各表的数据关系
create table score(
id int primary key,
stuid int not null,
score double(5,2) 
);
insert into score values(1,1,89),(2,3,75),(3,4,76);
select name,score from classtwo inner join score on classtwo.id=score.stuid;
#左连接查询,以左表为基准,能查询classtwo的所有字段，而inner join是左右连接的交集
select name,score from classtwo left join score on classtwo.id=score.stuid;
#右连接查询，反之同理
select name,score from classtwo right join score on classtwo.id=score.stuid;
#cross join交叉查询,来回组合返回一个笛卡尔积,方便代码演示创建两个简单表
create table t1(
id int primary key,
name varchar(30)
);
create table t2(
id int primary key,
age int
);
insert into t1 values(1,'黄哈哈'),(2,'马四连'),(3,'张窟窿'),(4,'关过江'),(5,'曹盖饭');
insert into t2 values(1,45),(2,25),(3,28),(4,38),(5,29);
select * from t1 cross join t2;
#natural join自然连接,公众字段名相同时出现自然内连接,同理也会有自然左连接自然右连接
#如果没有公共字段就会返回一个笛卡尔积
#(扩展)如果有多个公共字段,连接直接为空,此时要在语句后面加上using(id)确定公共字段,指定谁是连接字段
select * from t1 natural join t2 ;
select * from t1 natural right join t2;
select * from t1 natural left join t2;

6.子查询语句操作

#子查询的基本语法,查询classtwo中成绩大于75的学生信息,多个结果使用in,反之not in
select * from classtwo where id in(select stuid from score where score>75);
select * from classtwo where id not in(select stuid from score where score>75);
#exists,如果存在条件,就将表中所有数据查询出来,不存在用not exists
select * from classtwo where exists(select stuid from score where score>75);