SQL基础语法

1 DDL:操作数据库

DDL(Data Definition Language): 数据定义语言，用来定义数据库对象：数据库，表，列等。

1.1 查询

# 查询所有的数据库
show databases;
#查看当前使用的数据库
select database();

1.2 创建数据库

#创建数据库students
create database if not exists students;

1.3 删除数据库

#如果存在数据库students删除数据库students
drop database if exists students;

1.4 使用数据库

#使用数据库
use students;

2 DDL:操作表

2.1 查询表

# 查询当前数据库下所有表名称
show tables;
# 查询表结构
desc stu_student;

2.2 创建表

create table if not exists stu_student(
    id int primary key auto_increment, #id添加主键/自动增长
    username varchar(20) unique not null,#username唯一/不能为空
    password varchar(20) not null default '123456'#password不能为空,默认为123456
);

2.4 删除表

#如果存在表stu_student删除表stu_student
drop table if exists stu_student;

2.5 修改表

#将表名stu_student修改为tb_student
alter table stu_student rename to tb_student;
#添加一列
#给tb_student添加一列status，该字段类型是int
alter table tb_student add status int;
#修改数据类型
#将tb_student表中的status字段的类型改为char(2)
alter table tb_student modify status char(2);
#修改类名和数据类型
alter table tb_student change status new_status varchar(3);
#删除列
#将tb_student表中的addr字段删除
alter table tb_student drop status;

3 DML对数据进行增删改操作

DML(Data Manipulation Language) 数据操作语言，用来对数据库中表的数据进行增删改

3.1 添加数据

#添加一条数据数据
insert into stu_student values(null,'张三','zhangsan');
#添加指定列数据
insert into stu_student(username) values ('李四');
#批量添加数据
insert into stu_student values (null,'王五','wangwu'),
                               (null,'赵六','zhaoliu');

3.2 修改数据

#修改id为4的学生的密码为'123456'
update stu_student set password='123456' where id=4;

3.3 删除数据

#删除id为4的学生的信息
delete from stu_student where id=4;

4 DQL数据查询语言

DQL(Data Query Language) 数据查询语言，用来查询数据库中表的记录(数据)
完整语法：

select
	字段列表
from
	表名
where
	条件
group by
	分组字段
having
	分组后条件
order by
	排序字段
limit
	分页限定

4.1 基础查询

#查询多个字段
select id,username,password from tb_student;
#查询所有数据
select * from tb_student;
#去除重复记录
select distinct username from tb_student;
#起别名
select username 用户名,password 密码 from tb_student;

4.2 条件查询

4.2.1 条件查询

#查询用户名是张三的用户信息
select * from tb_student where username = '张三';
#查询用户名是张三并且id等于1的用户信息
select * from tb_student where username = '张三' && id = 1;
#等价于
select * from tb_student where username = '张三' and id = 1;
#查询id大于等于1并且id小于等于3的用户信息
select * from tb_student where id>=1 && id<=3;
#等价于
select * from tb_student where id between 1 and 3;
#查询用户名不是张三的用户信息
select * from tb_student where username != '张三';
#等价于
select * from tb_student where username <> '张三';
#查询用户名是张三或者用户名是李四的用户信息
select * from tb_student where username='张三' || username='李四';
#等价于
select * from tb_student where username='张三' or username='李四';
#等价于
select * from tb_student where username in ('张三','李四');
#查询密码为null的用户信息
select * from tb_student where password is null;
#查询密码不为null的用户信息
select * from tb_student where password is not null;

4.2.2 模糊查询

#查询姓张的用户信息
select * from tb_student where username like '张%';
#查询第二个字是三的用户信息
select * from tb_student where username like '_三%';
#查询用户名中包含'三'的学员信息
select * from tb_student where username like '%三%';

4.3 排序查询

#查询用户信息，按照密码升序排列
select * from tb_student order by password asc;
#asc可以省略
select * from tb_student order by password;
#查询用户信息，按照id降序排列
select * from tb_student order by id desc;
#查询用户信息，按照密码升序排列，如果密码一样，再按照id降序排列
select * from tb_student order by password,id asc;

注意：如果有多个排序条件，当前边的条件值一样时，才会根据第二条件进行排序

4.4 聚合函数

#统计用户的个数
select count(*) from tb_student;
#查询id的最大值
select max(id) from tb_student;
#查询id的最小值
select min(id) from tb_student;
#查询id的总和
select sum(id) from tb_student;
#查询id的平均值
select avg(id) from tb_student;

注意：null 值不参与所有聚合函数运算

4.5 分组查询

#查询男同学和女同学各自的数学平均分
select sex,avg(math) from tb_student2 group by sex;
#查询男同学和女同学各自的数学平均分，以及各自人数
select sex,avg(math),count(*) from tb_student2 group by sex;
#查询男同学和女同学各自的数学平均分，以及各自人数，要求：分数低于70分的不参与分组
select sex,avg(math),count(*) from tb_student2 where math > 70 group by sex;
#查询男同学和女同学各自的数学平均分，以及各自人数，要求：分数低于70分的不参与分组，分组之后人数要大于等于2个
select sex,avg(math),count(*) from tb_student2 where math>70 group by sex having count(*) >= 2;

注意：分组之后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义
where 和 having 区别：

• 执行时机不一样：where 是分组之前进行限定，不满足where条件，则不参与分组，而having是分组之后对结果进行过滤。
• 可判断的条件不一样：where 不能对聚合函数进行判断，having 可以。

4.6 分页查询

select * from tb_student2;
#从0开始查询，查询3条数据
select * from tb_student2 limit 0,3;
#每页显示3条数据，查询第1页数据
select * from tb_student2 limit 0,3;
#每页显示3条数据，查询第2页数据
select * from tb_student2 limit 3,3;
#每页显示3条数据，查询第3页数据
select * from tb_student2 limit 6,3;

从上面的练习推导出起始索引计算公式：
起始索引 = (当前页码 - 1) * 每页显示的条数