Mysql基本操作

一、数据库基本操作

1.列出数据库：

show databases;

2.创建数据库：

create database state;

3.使用/切换数据库：

use state;

4.查看当前所在数据库：

select database();

5.删除数据库：

drop database state;

二、数据表基本操作

1.列出当前数据库的所有表：

show tables;

2.创建新表：

create table students(                                   #创建名为students的表格
      id int auto_increment primary key not null,     #id字段整数类型、自增、为主键、非空
      name varchar(10) not null,                   #name字段可变字符串（只显示前十位）、非空
      gender bit default 1,                        #gender字段布尔值、默认为1（男）
      birthday datetime,                           #birthday字段日期时间类型
      isdelete bit default 0                        #isdelete字段布尔值、默认0（不删）
)engine=InnoDB default charset=utf8;      #设置存储引擎和字符编码（默认）

3.查看创建表的语句：

show create table students;

4.查看表结构：

desc students;

| Field |Type |Null |Key |Default |Extra |
| --------|-------|-------|------|----- | ------ |----|
| id |int |NO |PRI | |auto_increment |
| name |varchar(10) |NO| | | |
| gender |bit(1) |YES | |b'1' | |
| birthday |datetime |YES | | |
| isdelete |bit(1) |YES | | b'0' | |

5.修改表：

alter table students add score tinyint(3);     #增添score字段、tinyint类型
alter table students modify score smallint(3);   #更改score字段、数据类型为smallint
... add/modify ... first/after ... ;       #在add/modify关键词后添加first/after关键词来设置增/改字段的相对位置
alter table students change score score int(3);  #更改score(前)字段名为score(后)，数据类型int
alter table students drop score;                #删除score字段
alter table students rename to student;    #更改表students名为student
rename table student to students;          #更改表student名为students

6.删除表：

drop table students;

三、数据录入

1.查询

select * from students;    #全部显示

2.增添

insert into students values(1,"张三",0,"1998-1-1",0);    #按全部字段添加
insert into students(id,name) values(2,"李四");       #按某些字段添加
insert into students(name) values("王五"),("赵六"),("钱七");    #按某些字段添加多条数据

3.更改

update students set birthday="1997-1-2" where id=2;   #将id字段为2的数据的birthday字段改为1997-1-2

4.删除（物理删除）

delete from students where id=5;    #删除id字段为5的数据

5.逻辑删除

update students set isdelete=1 where gender=1 and id >0;    #标记删除性别为男的数据（where语句须有主键参与）

四、数据查询

1.基本查询

select * from students;        #查看students表
select name students;          #查看students表的name字段
select distinct gender from students;    #查看students表的gender字段（只有两种值故只显示两个结果）

2.条件查询

#条件运算符  >,<,>=,<=,!=,=
select * from students where id >2;    #where用于对原数据表做查询
select * from students where isdelete !=1;
#若有区分大小写的需求可在where关键字后加binary关键字

3.多条件查询（逻辑运算符连接）

#逻辑运算符  and,or,not
select * from students where id >4 and gender=1;
select * from students where id<4 or gender=1;

4.模糊查询

#  %表示任意多字符   _表示一个任意字符
select * from students where name like "张_";        #查询叫张_的学生
select * from students where name like "张%";       #查询姓张的学生
select * from students where name like "%四%";      #查询姓名含四的学生
select * from students where name like "张%" or name like "李%";  #查询姓张或姓李的学生
... where name regexp "正则表达式";    #使用正则表达式模糊匹配

5.范围查询

select * from students where id in(1,3,5);    #查询id为1、3、5的学生（in表示离散范围）
select * from students where id between 2 and 5;    #查询id为2到5的学生（between...and...表示连续范围）
select * from students where id between 2 and 5 and gender=1；   #查询id为2到5的男生

6.空值判断

select * from students where birthday is null;     #查询未填birthday的学生
select * from students where birthday is not null;    #查询已填写birthday的学生
<=>用于判断两者是否都为空值

7.描述统计（聚合）

select count()/max()/min()/sum()/avg() from students where ... ;     #五种函数参数为字段名或*(全部)

8.分组与分组筛选

select gender as "性别", count(*) from students group by gender;       #查询男女生各总人数
select gender,count(*) from students group by gender having count(*)>2;      #查询大于两人的性别
select gender,count(*) as num from students group by gender having num >2;    #having用于对group by 的结果筛选
...group by ... with rollup;      #在分组筛选语句末尾添加 with rollup 可以实现汇总行
select coalesce(a,b,c) ... ;       #用于对分类汇总结果的重命名，主要通过coalesce(字段名，汇总行名)来修饰结果

9.排序

select * from students where gender=1 and isdelete=0 order by id desc;    #查询未标记删除的男生信息，按学号降序（asc为升序）
...order by convert(字段名 using 编码方式);       #若排序字段编码与默认不一致可使用convert函数进行转换

10.查询部分条数据

select * from students limt 2,1;      #从索引2开始查询1条数据（起始索引为0）

五、多表操作

1.联合多表结果

create table class_1(
id int auto_increment primary key not null,
name varchar(6) not null,
subject int not null);
create table class_2(
id int auto_increment primary key not null,
name varchar(6) not null,
subject int not null);
insert into class_1(name,subject) values("a",1),("b",1),("c",2),("d",2);
insert into class_2(name,subject) values("e",1),("f",2),("g",2),("h",3);
#创建两个班级的学号、姓名、学科表

select subject from class_1 union select subject from class_2 order by subject;
#union联合两表以上结果（去重）
select subject from class_1 union all select subject from class_2 order by subject;
#union all联合两表以上结果（不去重）

2.连接表

select ... from 表A   连接方式   表B   on 连接条件;    
连接方式包括：
内连接 inner join    依两表共有行为基准连接
左连接 left join     依左表行为基准连接
右连接 right join    依右表行为基准连接
全连接 full join    左连接与右连接的并集
自然连接 natural join      去重的内连接
笛卡尔积 cross join  交叉连接，两表乘积，一般默认可不写，使用逗号分隔表即可
连接条件： 关键词on之后跟随条件语句

3.创建班级表，与学生表关联，并创建视图（查询）

create table T_classes(
id int auto_increment primary key not null,
name varchar(10)
);
create table T_students(
id int auto_increment primary key not null,
name varchar(10),
classid int,
foreign key(classid) references T_classes(id)
);

create view V_stu_cls as 
select T_students.id,T_students.name,T_classes.name as title
from T_students inner join T_classes on T_classes.id=T_students.classid;
#创建视图V_stu_cls 
#选择T_students表的id字段、T_students表的name字段、其中T_classes表的name字段作为title字段  
#这来自T_classes表id字段与T_students表classid字段相等的T_students表与T_classes表内连表

4.创建分类表，自关联，并创建视图（查询）

create table T_type(
id int auto_increment primary key not null,
name varchar(10),
pid int,
foreign key(pid) references T_type(id)
);

create view V_type as
select son.* 
from T_type as father inner join T_type as son on father.id=son.pid;