数据库 mysql

数据库 mysql

1. mysql基础

2. 数据库的简单操作

2-1. mysql的连接与退出

1. 链接数据库：

mysql -uroot -p
mysql -h 云数据库连接 -p 端口号 -u 用户名 -p

2. 退出数据库
   exit / quit

2-2. mysql的一些查询数据库信息的命令

1… 显示数据库版本

select version();

2. 显示时间

select now();

3. 数据库/表的操作

3-1. 数据库的操作

1. 创建数据库

create database name;(name=新建的数据库名)
create database name charset=utf8;(后面指明了编码)

2. 查看创建数据库的语句

show create detabase name;

3. 查看当前使用的数据库

select database();

4. 使用数据库

use name;

5. 查看当前数据库中所有表

show tables; 

3-2. 数据表的操作

3-2-1. 基础操作

1. 查看当前数据库中所有表

show tables;

2. 创建数据表

create table 表名 （id int,name varchar(30)）; #1
create table demo2(
        id int primary key not null auto_increment,
        name varchar(30)  #2
    );

示例1：创建一个students表(id、name、age、high、gender、cls_id)

create table students(
        id int not null primary key auto_increment,
        name varchar(30),
        age tinyint unsigned default 18,
        high decimal(5,2),
        gender enum('男','女','保密')  default '保密',        -- 存数据的时候 只能存 男或者女
        cls_id int 
    ); 

示例2：创建一个classes表（id,name）
create table classes( id int primary key not null auto_increment, name varchar(30) );

3. 查看表结构

desc 表名;

4. 查看表的创建语句

show create table 表名;

5. 修改表
   添加字段 add：

alter table 表名 add 列名 类型;
alter table students add birthday date;# 示例

修改字段 modify:

alter table 表名 modify 列名 类型及约束;
alter table students modify birthday date default '1990-1-1'; # 示例

修改字段：重命名 change:

alter table 表名 change 原名 新名 类型及约束;
alter table students change birthday birth date default '1990-1-1'; # 示例

删除字段 drop：

alter table 表名 drop 列名;
alter table students drop high; # 示例

3-2-2. 增删改查（curd）:

3-2-2-1. 增加

格式：

insert into 表名 values(插入的信息)

3-2-2-1-1. 全列插入

示例：向students表插入 一个学生信息
表的结构如下：

+--------+-------------------------------+------+-----+------------+----------------+
        | Field  | Type                          | Null | Key | Default    | Extra          |
        +--------+-------------------------------+------+-----+------------+----------------+
        | id     | int(11)                       | NO   | PRI | NULL       | auto_increment |
        | name   | varchar(30)                   | YES  |     | NULL       |                |
        | age    | tinyint(3) unsigned           | YES  |     | 0          |                |
        | gender | enum('男','女','中性','保密')  | YES  |     | 保密       |                |
        | cls_id | int(10) unsigned              | YES  |     | NULL       |                |
        | birth  | date                          | YES  |     | 1997-01-01 |                |
        +--------+-------------------------------+------+-----+------------+----------------+

1. 主键字段 0 null default 可以用来占位

insert into students values(0,'xx',18,'男',1,'1990-1-1');  
insert into students values(null,'xx',19,'男',1,'1990-1-1');  
insert into students values(default,'xxx',19,'男',1,'1990-1-1');  

2. 枚举类型插入

insert into students values(default,'xxxx',19,1,1,'1990-1-1');

3-2-2-1-2. 部分插入

insert into 表名(列1,...) values(值1,...);

示例基于上面部分:

insert into students(`gender`) values(2);
insert into students(`name`,`gender`) values('xxxx',2);

3-2-2-1-3. 多行插入

insert into students values
        (default,'xxx',19,'男',1,'1990-1-1'),
        (default,'666',20,'男',1,'1990-1-1')
        ;  

3-2-2-2. 删除

delete from 表名 where 条件;

3-2-2-2-1. 物理删除

delete from 表名; # 删除表中的数据
delete from 表名 where 条件   #删除的是满足条件的

3-2-2-2-2. 逻辑删除

id_delete 表示  是否删除  1删除  0未删除

-- is_delete 表示  是否删除  1 删除   0 未删除
-- 查找 select * from students where is_delete=0;
-- 修改
update students set is_delete = 1 where id=6; 

3-2-3. 查询

3-2-3-1. 基本使用

select 去重选项 字段列表[as 字段名] from 数据表;
where [group by 字句] [having字句] [order by 字句] [limit字句];

3-2-3-1-1. 查询所有列;

select * from 表名;

3-2-3-1-2. 去除重复字段的查询 distinct 整个查询的行是否重复

distinct 字段可消除重复行

select sidtinct name from students;

3-2-3-1-3. 查询指定列

select 列1,列2,列3... from 表名;

3-2-3-1-4. 使用as指定别名或表

select 别名.字段 .... from 表名 as 别名;
select 列1 as n,age,gender from students_abc_demoas_asdasda  where n='juran';
select 列1 as n,gender from students where name='xxx';
select 列1 as 姓名,gender from students where name='xx';

3-2-3-2. 条件查询

select ... from 表名 where ...

3-2-3-2-1. 比较运算符

示例：基于上表

-- 查询 大于18岁 的信息
select * from 表名 where age > 18;

-- <
-- 查询小于18岁的信息
select * from 表名 where age < 18;

-- >=
-- <=
-- 查询小于或者等于18岁的信息
select * from 表名 where age <= 18;
-- =
-- 查询年龄为18岁的所有学生的名字
select * from 表名 where age = 18;

-- != 或者 <>  不等于 
-- 查询姓名不是xx的
select * from 表名 where name != 'xx';

3-2-3-2-2. 逻辑运算符

示例：基于上表

-- and
-- 18到28之间的所有学生信息
select * from 表名 where age >18 and age < 28;
-- select * from 表名 where 18 < age < 28;  错误的写法
-- select * from 表名 where age >18 and  < 28;


-- 18岁以上的女性
select * from 表名 where age > 18 and gender='女';

-- or
-- 查询编号小于4或没被删除的学生
select * from 表名 where id < 4 or is_delete = 0;

-- not 取反
-- 查询年龄不是18岁的   女性 这个范围内的信息
select * from 表名 where not (age = 18 and gender = '女'); -- 可读性 更高
select * from 表名 where age != 18 and gender = '女';

-- 年龄不是小于或者等于18 并且是女性
select * from 表名 where (not age <= 18)  and gender = 2;

-- select * from 表名 where age > 18  and gender = 2;
-- MySQL数据库中优先级：not>and>or 

3-2-3-3. 模糊查询(搜索) like

示例：基于上表

-- 查询姓名中 以 "小" 开始的名字 
 select * from 表名 where name like '小%';

-- 查询姓名中 有 "小" 所有的名字
select * from 表名 name like '%小%;

-- 查询有2个字的名字
select * from 表名 name like '__';

-- 查询至少有2个字的名字
select * from 表名 name like '__%;

-- rlike 正则
-- 查询以 周开始的姓名
select * from 表名 where name rlike "^周.*";

-- 查询以 周开始、伦结尾的姓名
select * from 表名 where name rlike "^周.*伦$";

3-2-3-4. 范围查询

示例：基于上表
in (1,3,8)表示在一个非连续的范围内

-- 查询 年龄为18、34的姓名
select name from 表名 where age in (18,34);

--查询 姓名为xx,xxx的
select * from 表名 where name in ('xx','xxx');

--not in 不非连续的范围值内
--年龄 不是 18、34岁之间的信息
select name from 表名 where age not in (18,34);

--between ... and ... 表示在一个连续的范围内
--查询 id是3到8之间的信息
select * from 表名 where id between 3 and 8;

查询 id是3到8的男生
select * from 表名 where (id between 3 and 8) and gender=1;

--not between ... and ... 表示不在一个连续的范围内
-- 查询年龄不在18到34之间的信息
select name from 表名 where age not between 18 and 34;

3-2-3-5. 空判断

-- 判空is null  不能用 = 
-- 查询身高为空的信息
select * from students where name is null;

-- 判非空is not null
select * from students where name is not null;

3-2-3-6. 聚合函数/常用计算

1. 总数(count)
   示例:

--查询学生总数
select count(*) from 表名 ;
-- 查询男生，、女生分别多少;
select count(*) as 男性人数 from 表名 where gender = 1;
select count(*) as 女性人数 from 表名 where gender = 2;

2. 最大值(max）
   示例

-- 查询最大的年龄
select max(age) from 表名;

-- 查询女性最大编号
select max(id) from 表名 where gender = 2;

-- 查询最大年龄的女性ID
select max(age) from 表名 where gender = 2;

3. 最小值(min)
   示例

-- 查询未删除的学生最小编号
select min(id) from 表名 where is_delete = 0;

4. 求和(sum)
   示例

-- 查询男生的年龄和
select sum(age) from 表名 wherer gender = 1;
-- tips: 求和字段， 是varchar 0 如果字段内容是数字的话会进行相加

5. 平均值（avg)
   示例

-- 查询为删除女生的年龄的平均值
select avg(age) from 表名 where gender = 2 and is_delets = 0;
-- tips: 默认保留4为小数

6. 计算平均年龄(sum(age)/count(*)
   示例

-- 四舍五入 round(123.45,1) 保留1为小数
-- 计算所有人的平均年龄，保留两位小数
select round(avg(age),2) from 表名;
select round(sum(age)/count(*),2) from 表名;

3-2-3-7. 分组(group by)

3-2-3-7-1. 用法及命令

select ... from 表名 group by 列名

示例

-- 按照性别分组,查询所有的性别   # students = 表名
select gender from students group by gender;
	
-- 计算男生和女生中的人数
select count(*) from students group by gender;
select gender as 性别,count(*) from students group by gender;

select gender as 性别,count(*) from students where gender = 1;
select count(*) from students where gender = 2;

-- 男女同学最大年龄
select gender as 性别,max(age) from students group by gender;

-- group_concat(...)   查看组内的信息
-- 查询同种性别中的姓名
select gender as 性别,group_concat(name) from students group by gender;

-- 查询组内年龄,姓名  'a' + b + 'c'  b 变量
select gender as 性别,group_concat('姓名:',name,'-','年龄:',age) from students group by gender;

3-2-3-7-2. 筛选(分组之后的筛选) having

-- 查询男生女生总数大于2
select gender,count(*) from 表名 group by gender having count(*)>2;

-- 查询男生女生总数大于2的姓名
select gender,count(*),group_concat(name) from 表名 group by gender having count(*)>2;

-- 平均年龄超过18岁的性别，以及姓名 having avg(age) >18
select gender.group_concat(name),avg(age) from students group by gender having avg(age) >18;

3-2-3-8. 排序 order by

order by 字段
asc 从小到大排列，即升序 默认
desc 从大到小排序，即降序
示例，

select ... from 表名 where 条件 order by 列 降序/升序;

-- 查询年龄在18到26岁之间的男同学，按照年龄从小到大排序
select * from 表名 where (age between 18 and 26) and gender=1;
select * from 表名 where (age between 18 and 26) and gender=1 order by age;
select * from 表名 where (age between 18 and 26) and gender=1 order by age asc;

-- 查询年龄在18到26岁之间的女同学，身高从高到矮排序
select * from 表名 where (age between 18 and 26) and gender=2 order by id desc

-- order by 多个字段
# 示例
-- 查询年龄在18到28岁之间的男性，年龄从高到矮排序, 如果身高相同的情况下按照年龄从小到大排序
-- 排序的字段 相同了 
-- 先按照 年龄排序  如果年龄相同  在按照身高进行排序
select * from 表名 where (age between 18 and 28) and gender=1 order by age desc,hiht asc

3-2-3-9. 分页(limit)

limil start, count 起始的位置(从0开始), 个数
示例

3-2-3-9-1. 分页常用

-- 限制查询出来的个数
select * from 表名 limit 2;

-- 查询前5个数据
select * from 表名 limit 5;

-- 查询id 6-10(包含)的数据
select * from 表名 limit 5,5;

3-2-3-9-2. 制作分页

示例

-- 每页显示2个，第1个页面
select * from 表名 limit 0,2;

-- 每页显示2个，第二个页面
select * from 表名 limit 2,2;

-- 每页显示2个，第三个页面
select * from 表名 limit 4,2;

-- 每页显示2个，第四个页面
select * from 表名 limit 6,2;

3-2-3-10. 连接查询

3-2-3-10-1. 介绍

inner join ... on 

3-2-3-10-2. 示例

-- select ... from 表A inner join 表B;
select * from students inner join classers;

-- 查询 有能够对应班级的学生以及班级信息
select * from students inner join classes on students.cls_id = classes.id;

-- 按照要求显示姓名、班级
select students.*,classes.name from students inner join classes on students.cls_id = classes.id;

-- 给数据表起名字
select s.*,c.name from students as s inner join classes as c on s.cls_id = c.id;

-- 查询 有能够对应班级的学生以及班级信息，显示学生的所有信息，只显示班级名称
select s.*,c.name from students as s inner join classes as c on s.cls_id = c.id;

-- 在以上的查询中，将班级姓名显示在第一列
select c.name, s.* from students as s inner join classes as c on s.cls_id = c.id;

-- 查询 有能够对应班级的学生以及班级信息, 按照班级进行排序
select c.name, s.* from students as s inner join classes as c on s.cls_id = c.id order by c.name;

-- 当同一个班级的时候，按照学生的id进行从小到大排序
select c.name, s.* from students as s inner join classes as c on s.cls_id = c.id order by c.name,s.id asc;

-- left join
-- 查询每位学生对应的班级信息
select * from students left join classes on students.cls_id = classes.id;
select * from classes left join students on students.cls_id = classes.id

--  查询没有对应班级信息的学生
-- select ... from xxx as s left join xxx as c on ... having ......
-- select * from students as s left join classes as c on s.cls_id = c.id where c.id is null;
select * from students as s left join classes as c on s.cls_id = c.id having c.id is null;

-- right jin   on 
-- 将数据表名字互换位置，用left join完成

3-2-3-11. 子查询

示例参考：

-- 查询最高男生的身高信息
select * from students where higt = 170;
select max(higt) from students where gender = '男';
select * from students where higt = (select max(higt) from students where gender = '男');
-- select * from students where higt = 170.4;
select * from students where higt = (select max(higt) from students) and gender = '男';
-- select * from students where higt = 175.4;
select * from students where higt = (select max(higt) from students where gender = '男') and gender = '男';

-- 查询高出平均身高的信息
select * from students where higt > (select avg(higt) from students);

-- 列级子查询
-- 查询学生的班级号能够对应学生的信息
select * from students where cls_id in (select id from classes);