MySQL和sql语句学习记录

数据库基本操作命令

#查看所有数据库；
show databases;

#查看当前使用的数据库；
select database();

#创建数据库：
create database 数据库名 charset=utf8；

#使用数据库：
use 数据库名；

#删除数据库：
drop database 数据库名；

查看表：

#查看当前数据库中的所有表：
格式：show tables;

# 查看创建表的命令：
格式：show create table 表名；

# 查看表结构： 
格式：desc 表名；

创建表：

#格式：
create table 表名 (
    字段1 类型 [约束],
    字段2 类型 [约束]
);

#举例1：
create table mytable(
    id int unsigned primary key auto_increment not null,
    name varchar(20)
);

#举例2：
create table student(
    id int unsigned primary key auto_increment not null,
    name varchar(20) not null,
    age int unsigned default 0,
    high decimal(5,2),
    gender enum('男','女'),
    cls_id int unsigned
);

修改表：

#添加字段：
alter table 表名 add 列名 类型；

举例：alter table  student add addr varchar(30);

#重命名字段：
alter table 表名 change 原名 新名 类型及约束；

举例：alter table student change addr dizhi varchar(30);

#修改字段类型：
alter table 表名 modify 列名 类型及约束；

举例：alter table student modify addr char(100);

#删除字段；
alter table 表名 drop 列名；

举例：alter table student drop addr;

#删除表：
alter table 表名

添加数据：

#全列插入，值的顺序与表结构字段的顺序完全一一对应
insert into 表名 values(...);

举例：insert into student values(0,'张三',12,143.11,'男',12222222);
# 这里的0表示占位，主键自增长

#部分列插入，值的顺序与给出的列顺序对应
insert into    表名 (列1，列2...) values(值1，值2...);

举例：insert into student(name,age) values('小红',18);

#一次性插入多行数据
insert into 表名 values(...),(...)...;

举例：insert into student values(0,'李四',16,115,'男',133333),(0,'小花',19,160,'女',100000);

#部分列多行插入；
insert into 表名(列1...) values(值1...)(值1...)...;

举例：insert into student(name,age) values('小小1',16),('小小2',18);

修改查询数据：

#查看所有列数据
select * from 表名；

#查看指定列数据
select 列1，列2...   from 表名；

举例：select name,gender from student;

#指定条件查询
select * from 表名 条件；

举例：select * from student where id=3;

​
#使用as为列火表指定别名
select 列1 as 别名1，列2 as 别名2 from 表名；

举例：select name as '名字',gender as '性别' from student;

#调换字段的顺序

select age as '年龄',name as '名字'  from student;

​
#修改数据：
update 表名 set 列1=值1,列2=值2 where 条件；

#全部修改
update student set gender='男';

#指定条件修改
update student set gender='男' where id=1,3;

as 语法

as 可以为字段起别名
as 也可以给表起别名，用来区分有相同字段的不同表
语法：
select 表别名.字段名 from 表名 as 表别名；
举例：select aaa.name  from student as aaa;

去重查询select distinct 
#语法：
select distinct 字段名称 from 表名 ;
#举例
select distinct age from student ;

where 比较运算查询

​ 使用 where 字句对表中的数据进行筛选，结果为true会出现在结果集中；

常见的比较运算符:
    等于 =
    大于 >
    大于等于 >=  
    小于 <
    小于等于 <=
    不等于 != 或 <>

举例：
1、查询年龄大于18岁的信息；
select * from student where age >18;
2、查询小于等于16岁的信息
select * from student where age <= 16;
3、查询不等于18岁的信息
select * from student where age != 18;
select * from student where age <> 18;

where逻辑运算符

and 表示有多个条件时，多个条件必须同时成立
or  表示有多个条件时，满足任意一个条件即成立
not 表示取反操作

举例：
1、查询15-18岁之间的信息
select * from student where age > 15 and  age < 18;
2、查询大于15岁的女性
select * from student where gender='女' and age > 15;
3、查询不在18岁以上的信息
select * from student where not  (age >= 18);
注意：
使用() 运算符优先级问题

where模糊查询

关键字like
like后跟：
    % 表示任意多个任意字符
    _ 表示一个任意字符

​
举例：
1、查询姓名中已 小 字开头的信息
select * from student where name like ‘小%’;
2、查询名字有2个字的名称
select * from student where name like ‘__’;
3、查询姓名中包含 小 字的名字；
select * from student where name like ‘%小%’;

where范围查询

知道between-and 和 in 的区别

连续范围查询和 非连续范围查询
in：表示在一个非连续的范围内
between a and b 表示在一个连续范围内（包含a和b）

​
举例：
1、查询年龄在15-18范围内的信息
select * from student where age between 15 and 18;
2、查询年龄不在15-18范围内的信息
select * from student where age not between 15 and 18;
3、查询年龄在 (12,13,37) 范围内的信息
select * from student where age in (12,13,37);
4、查询年龄不在 (12,13,37) 范围内的信息
select * from student where age not in (12,13,37);

where空值判断

判断为空：is null
判断非空：is not null

举例：
1、查询为空的信息
select * from student where cls_id is null;
2、查询不为空的信息
select * from student where cls_id is not null;

order排序查询

​ 关键字 order by

语法：
select * from 表名 order by 列1 [asc|desc] [,列2 asc|desc,...]

注意：
将行数据按照列1进行排序，如果某些行列1的值相同，则按照列2 排序，以此类推
asc从小到大，升序，默认
desc从大到小，降序

举例：
1、按年龄升序查询
select * from student order by age;
2、安年龄降序查询
select * from student order by age desc;
3、查询年龄大于15 的女性，按年龄降序
select * from student where (age > 15 and gender='女') order by age desc;

聚合函数

​ 作用：会把当前表当做一个组进行统计

特点：
每个组函数接受一个参数，一般为字段名或者表达式；
统计结果中默认忽略字段为null 的记录
不允许出现嵌套，比如sum(max(xx))

count(字段)    ：计算总数
max(字段)    ：求最大值
min(字段)    ：求最小值
sum(字段)    ：求字段之和
avg(字段)    ：求此字段平均值

​
举例：
1、查看男性总数
select count(*) from student where gender=1;

2、查看最大的年龄数
select max(age) from student;
3、平均年龄
select avg(age) from student;

# 四舍五入，保留几位小数
语法：round(字段,保留位数)
select round(avg(age),1) from student;
举例：
四舍五入展示身高这一列
select name,round(high,1) from student ;

group分组查询

所谓的分组就是将一个数据集  划分成若干个小区域，然后针对这个若干个 小区域 进行数据处理

使用特点
    group by 将查询结果按照1个 或多个字段进行分组 ，字段值相同的为一组
    group by 可用于单个字段分组，也可用于多个字段分组

举例：
按性别分组，统计每个性别有多少人
select gender,count(*) from student group by gender;

group by + group_concat()作用：根据分组结果，使用grou_concat来放置每一个分组中某字段的集合

举例：
按性别分区，将每个性别里面的人名列出
select gender,group_concat(name) from student group by gender;
select gender as 性别,group_concat(name) as 人员列表 from student group by gender;

group by + 聚合函数
作用：聚合函数在和group by 结合使用的时候 统计的对象是每个分组
举例：
查看每个性别中年龄最大的是多大
select gender,max(age) from student group by gender;

group by + having 
作用：对每个分组中的数据进行处理
和where类似，但是having只能用于grou by对分组后的每组数据进行过滤，而where是用来过滤表数据
例如：
查询每个性别中年龄超过10岁的性别以及姓名
select group_concat(name),gender from student group by gender having avg(age) > 10;

group by + with rollup
作用：在数据表的字后新增一行，来记录当前表中该字段对应的操作结果，一般是汇总结果

举例：
统计每个年龄的数量，最后一行汇总
select gender,count(*) from student group by gender with rollup;

limit限制擦和讯
可以使用limit 限制取出记录的数量，但limit要卸载sql语句的最后

语法：
limit 起始记录，记录数

起始记录是指从第几条开始取，第一条记录的下标是0
记录数是指从起始记录开始向后依次取得记录数

分页查询技巧：
起始记录=（当前页数-1）x 每页个数

连接查询：

内连接：

查询的结果为两个表符合条件的匹配到的数据

    注意：
    1、根据连接条件取出两个表的交集
    2、on 是连接条件，where是连接后筛选条件

    语法：
    select 字段 from 表1 inner join 表2 on 表1.字段1=表2.字段2

    举例：
    1、查询每个学员对应的班级信息
    select * from student inner join class on student.cls_id=class.id;

    2、查询每个学员对应的班级信息，且仅显示姓名和班级信息；
    select student.name,class.name from student inner join class on student.cls_id=class.id;

​
​

外连接：

左连接：
主表 left join 从表 on 连接条件
左外连接查询：查询的结果为两个表匹配到的数据和左表特有的数据
右连接：
从表 right join 主表 on 连接条件
右外连接查询：查询的结果为两个表匹配到的数据和右表特有的数据

自连接：

使用自连接查询只需要一个表就可以，可以加快查询速度，减少数据表占用空间
自连接查询本质还是连接查询

子查询：

把一个查询的结果当做另一个查询的条件

三类：
    标量子查询：子查询返回的结果是一个数据（一行一列）
    列子查询：返回的结果是一例（一列多行）
    行子查询：返回的结果是一行（一行多列）

​
举例：
1、查询平均身高
select avg(age) from student;
2、查询大于平均身高的信息
select * from student where age > (select avg(age) from student);