mariadb数据库（2）增删改与 单表查询

一、数据类型

MariaDB数据类型可以分为数字，日期和时间以及字符串值。

使用数据类型的原则：够用就行，　尽量使用范围小的，而不用大的

• 常用的数据类型

1. 整数:int, bit
2. 小数:decimal 　　                                  #decimal(5,2)
3. 字符串:varchar, char　　                       
4. 日期时间:date, time, datetime
5. 枚举类型(enum)

• 约束

1. 主键primary key：物理上存储的顺序（不能重复）
2. 非空not null：此字段不能为空
3. 唯一unique：此字段不允许重复
4. 默认default：当不填写此值时会使用默认值，如果填写则已填写为准
5. 外键foreign key：对关系字段进行约束，当为关系字段填写值时，会到关联的表中查询此值是否存在，如果存在则填写成功，如果不存在则填写失败并抛出异常。

二、增删改

枚举类型的数据可以索引取值

主键null/default/0默认自增

decimal 小数会四舍五入

年龄写成字符串会默认为整型

自增不会回退（好马不吃回头草）

MariaDB [jam]> desc classes;      #查看表结构
+-----------+---------------------+------+-----+---------+----------------+
| Field     | Type                | Null | Key | Default | Extra          |
+-----------+---------------------+------+-----+---------+----------------+
| id        | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
| name      | varchar(4)          | YES  |     | NULL    |                |
| age       | tinyint(3) unsigned | YES  |     | NULL    |                |
| high      | decimal(5,2)        | YES  |     | NULL    |                |
| gender    | enum('男','女')     | YES  |     | 女      |                |
| cls_id    | int(10) unsigned    | YES  |     | NULL    |                |
| date      | bit(1)              | YES  |     | NULL    |                |
| is_delete | bit(1)              | YES  |     | NULL    |                |
+-----------+---------------------+------+-----+---------+----------------+
8 rows in set (0.01 sec)

MariaDB [jam]> select * from classes;     #查看表里的内容
Empty set (0.01 sec)

#查看时间
MariaDB [jam]> select now();               
#增字段 alter table 表名 add 字段名 数据类型；
MariaDB [jam]> alter table classes add bithday datetime;
#修改数据类型   alter table 表名 modify 字段名 新的数据类型；
MariaDB [jam]> alter table classes modify bithday date;   
#修改字段名，数据类型   alter table 表名 change 旧字段名 新字段名 数据类型
MariaDB [jam]> alter table classes change bithday birth datetime;
#删除字段名（drop）
MariaDB [jam]> alter table classes drop birth;  

#插入一个新的数据
MariaDB [jam]> insert into classes values (1,'萧敬腾',3,171.2,'男',1,1,1);
#部分插入，可以多条插入
MariaDB [jam]>  insert into classes (name,age,high) values ('jam',32,171.2),('hsiao',32,172);

#将hsiao修改为田馥甄
MariaDB [jam]> update classes set name='田馥甄' where name='hsiao';
#修改特定的某一条的name，用id指定
MariaDB [jam]> update classes set name='张惠妹' where id=20;
#修改多个字段用‘，’隔开
MariaDB [jam]> update classes set name='阿密特',age=35 where id=20;

#全部删除（危险操作，一定要注意）
MariaDB [jam]> delete from classes;
Query OK, 8 rows affected (0.00 sec)

MariaDB [jam]> select * from classes;
Empty set (0.00 sec)
#物理删除，按主键删除最保险
MariaDB [jam]> delete from classes where id=23;
#编辑一个is_de字段，默认值为0，bit（1）表示1，0
MariaDB [jam]> alter table classes add is_de bit(1) default 0;
#逻辑删除is_de;
MariaDB [jam]> alter table classes drop is_de;
#查看is_delete=1的行
MariaDB [jam]> select * from classes where is_delete=1;
+----+-----------+------+--------+--------+--------+------+-----------+
| id | name      | age  | high   | gender | cls_id | date | is_delete |
+----+-----------+------+--------+--------+--------+------+-----------+
|  1 | 萧敬腾    |    3 | 171.00 | 男     |   NULL |     |          |
|  2 | 白敬亭    |    5 | 183.00 | 男     |   NULL |     |          |
| 21 | 1         |    0 |   3.00 |        |      1 |     |          |
| 22 | 2         |    0 |   5.00 |        |      1 |     |          |
+----+-----------+------+--------+--------+--------+------+-----------+
4 rows in set (0.00 sec)
#查看is_delete=0的行
MariaDB [jam]> select * from classes where is_delete=0;
+----+-----------+------+--------+--------+--------+------+-----------+
| id | name      | age  | high   | gender | cls_id | date | is_delete |
+----+-----------+------+--------+--------+--------+------+-----------+
|  3 | 王嘉尔    |    3 | 172.00 | 男     |   NULL |     |           |
|  4 | 阿密特    |    6 | 160.00 | 女     |   NULL |     |           |
| 24 | 4         |    0 |   6.00 |        |      2 |     |           |
+----+-----------+------+--------+--------+--------+------+-----------+
3 rows in set (0.00 sec)

三、单表查询

查询基本使用(条件，排序，聚合函数，分组，分页)

 

#创建studens表
MariaDB [jam]> create table students (
    -> id int unsigned not null auto_increment primary key,
    -> name varchar(20) default '',
    -> age tinyint unsigned default 0,
    -> high decimal(5,2),
    -> gender enum('男', '女', '中性', '保密') default '保密',
    -> cls_id int unsigned default 0,
    -> is_delete bit default 0）；
#创建class表
MariaDB [jam]> create table class(
    -> id int unsigned auto_increment primary key not null,
    -> name varchar(20) not null）；
#插入行
MariaDB [jam]> insert into students (name,age,high,gender,cls_id,is_delete) values('猪',12,150,1,1,1),('狗',13,180,1,1,1),('鸡',14,183,2,1,0),('马',3,120,2,1,1),('鼠',20,150,1,1,1);
MariaDB [jam]> select * from students;
+----+------+------+--------+--------+--------+-----------+
| id | name | age  | high   | gender | cls_id | is_delete |
+----+------+------+--------+--------+--------+-----------+
|  1 | 猪   |   12 | 150.00 | 男     |      1 |          |
|  2 | 狗   |   13 | 180.00 | 男     |      1 |          |
|  3 | 鸡   |   14 | 183.00 | 女     |      1 |           |
|  4 | 马   |    3 | 120.00 | 女     |      1 |          |
|  5 | 鼠   |   20 | 150.00 | 男     |      1 |          |
+----+------+------+--------+--------+--------+-----------+

#给字段取别名
MariaDB [jam]> select name as '姓名',age as '年纪' from students;
+--------+--------+
| 姓名   | 年纪   |
+--------+--------+
| 猪     |     12 |
| 狗     |     13 |
| 鸡     |     14 |
| 马     |      3 |
| 鼠     |     20 |
+--------+--------+
5 rows in set (0.00 sec)
#给表取别名（多表查询特别有用）
MariaDB [jam]> select s.name,s.age from students as s;
+------+------+
| name | age  |
+------+------+
| 猪   |   12 |
| 狗   |   13 |
| 鸡   |   14 |
| 马   |    3 |
| 鼠   |   20 |
+------+------+
5 rows in set (0.00 sec)
#全部查询（危险操作，数据大时会承受不了）
MariaDB [jam]> select * from students;

MariaDB [jam]> select distinct age from students;

#查询年纪大于10的行
MariaDB [jam]> select * from students where age >10;
#查询年纪在19到30之间的行（上下等价）
MariaDB [jam]> select * from students where age >19 && age <30;
MariaDB [jam]> select * from students where age >19 and age <30;
#查询年纪小于14或大于20的行（同上）
MariaDB [jam]> select * from students where age <14 or age >20;
MariaDB [jam]> select * from students where age <14 || age >20;

%表示零个或任意多个字符
#查询students表的age列中包含1的行
MariaDB [jam]> select * from students where age like '%1%'; 
#查询students表的age列中开头为1的行
MariaDB [jam]> select * from students where age like '1%';
#查询students表的age列中末尾为1的行 
MariaDB [jam]> select * from students where age like '%1'; 
#查询students表的age列中只有一个字符的行
MariaDB [jam]> select * from students where age like '_'; 
#查询students表的age列中只有两个字符的行
MariaDB [jam]> select * from students where high like '__';
#查询students表的age列中至少有两个字符的行 
MariaDB [jam]> select * from students where high like '__%'; 

#查询年纪在12，14，20的人
MariaDB [jam]> select * from students where age in (12,14,20);
#查询年纪在10到20之间的人，包含10和20
MariaDB [jam]> select * from students where age between 10 and 20;
#查询年纪不在10和20之间的人
MariaDB [jam]> select * from students where age not between 10 and 20;

#查询年纪为空的行
MariaDB [jam]> select * from students where age is null;
#查询年纪不为空的行
MariaDB [jam]> select * from students where age is not null;

#按年纪从小到大排序asc（默认从小到大）
MariaDB [jam]> select * from students order by age asc;
+----+------+------+--------+--------+--------+-----------+
| id | name | age  | high   | gender | cls_id | is_delete |
+----+------+------+--------+--------+--------+-----------+
|  4 | 马   |    3 | 120.00 | 女     |      1 |          |
|  1 | 猪   |   12 | 150.00 | 男     |      1 |          |
|  2 | 狗   |   13 | 180.00 | 男     |      1 |          |
|  3 | 鸡   |   14 | 183.00 | 女     |      1 |           |
|  5 | 鼠   |   20 | 150.00 | 男     |      1 |          |
+----+------+------+--------+--------+--------+-----------+
5 rows in set (0.00 sec)

MariaDB [jam]> select * from students order by age;
+----+------+------+--------+--------+--------+-----------+
| id | name | age  | high   | gender | cls_id | is_delete |
+----+------+------+--------+--------+--------+-----------+
|  4 | 马   |    3 | 120.00 | 女     |      1 |          |
|  1 | 猪   |   12 | 150.00 | 男     |      1 |          |
|  2 | 狗   |   13 | 180.00 | 男     |      1 |          |
|  3 | 鸡   |   14 | 183.00 | 女     |      1 |           |
|  5 | 鼠   |   20 | 150.00 | 男     |      1 |          |
+----+------+------+--------+--------+--------+-----------+
5 rows in set (0.00 sec)
#按年级从大到小排序（desc）
MariaDB [jam]> select * from students order by age desc;
+----+------+------+--------+--------+--------+-----------+
| id | name | age  | high   | gender | cls_id | is_delete |
+----+------+------+--------+--------+--------+-----------+
|  5 | 鼠   |   20 | 150.00 | 男     |      1 |          |
|  3 | 鸡   |   14 | 183.00 | 女     |      1 |           |
|  2 | 狗   |   13 | 180.00 | 男     |      1 |          |
|  1 | 猪   |   12 | 150.00 | 男     |      1 |          |
|  4 | 马   |    3 | 120.00 | 女     |      1 |          |
+----+------+------+--------+--------+--------+-----------+
5 rows in set (0.00 sec)
#-- 查询年纪在18到34岁之间的男性，按照年纪从小到大
MariaDB [jam]> select * from students where (age between 18 and 34) and gender=1 order by age; 
#-- 查询年纪在18到34岁之间的女性，身高从高到矮
MariaDB [jam]>  select * from students where (age between 18 and 34) and gender=2 order by high desc;
#    -- 查询年纪在10到20岁的女性，身高从高到矮排序，如果身高相同的情况下按照年纪从小到大排序
MariaDB [jam]>  select * from students where (age between 10 and 20) and gender=2 order by high desc, age;
#-- 查询年纪在18到44岁的男性，身高从高到矮排序，如果身高相同的情况下按照年纪从小到大排序，如果年龄也相等那么按照id从小到大排序；
MariaDB [jam]> select * from students where (age between 18 and 44) and gender=1 order by high desc,age,id;

-- 总数
    -- count 
    -- 查询男性有多少人
    select count(*) from students where gender=1;
    
    
    -- 最大值
    -- max
    -- 查询最大的年纪
    select max(age) as '最大值' from students;
    
    
    -- 查询女性的最高 身高
    select max(high) from students where gender=2;
    
    -- 最小值
    -- min
    select min(age) as '最小值' from students;

    
    -- 求和
    -- sum
    -- 计算所有人的年龄总和
    select sum(age) form students;
    
    -- 平均值
    -- avg
    -- 计算平均年纪
    -- 计算平均年纪 sum(age)/count(*)
    select avg(age) from students;
    select sum(age)/count(*) form students;  #除去空值，比较准确
    -- 保留2位小数
    select round(avg(age),2) 

-- 总数
    -- count 
    -- 查询男性有多少人
    select count(*) from students where gender=1;
    
    
    -- 最大值
    -- max
    -- 查询最大的年纪
    select max(age) as '最大值' from students;
    
    
    -- 查询女性的最高 身高
    select max(high) from students where gender=2;
    
    -- 最小值
    -- min
    select min(age) as '最小值' from students;

    
    -- 求和
    -- sum
    -- 计算所有人的年龄总和
    select sum(age) form students;
    
    -- 平均值
    -- avg
    -- 计算平均年纪
    -- 计算平均年纪 sum(age)/count(*)
    select avg(age) from students;
    select sum(age)/count(*) form students;  #除去空值，比较准确
    -- 保留2位小数
    select round(avg(avg),2) from studen

-- 总数
    -- count 
    -- 查询男性有多少人
    select count(*) from students where gender=1;
    
    
    -- 最大值
    -- max
    -- 查询最大的年纪
    select max(age) as '最大值' from students;
    
    
    -- 查询女性的最高 身高
    select max(high) from students where gender=2;
    
    -- 最小值
    -- min
    select min(age) as '最小值' from students;

    
    -- 求和
    -- sum
    -- 计算所有人的年龄总和
    select sum(age) form students;
    
    -- 平均值
    -- avg
    -- 计算平均年纪
    -- 计算平均年纪 sum(age)/count(*)
    select avg(age) from students;
    select sum(age)/count(*) form students;  #除去空值，比较准确
    -- 保留2位小数
    select round(avg(age),2) from students; 

-- group by
    -- 按照性别分组，查询所有的性别
    select gender from students group by gender;
    
    
    -- 计算每组性别的人数
    select gender,count(*) from students group by gender;

    
    -- 查询男性组中的姓名 group_concat
    select gender,group_concat(name) from students where gender=1;
    --查询以性别分组的人    
    select gender,group_concat(name) from students group by gender;

    -- having
    -- 查询每个性别平均年纪超过30岁的性别，以及姓名 having avg(age) > 30
        select gender,group_concat(name) from students group by gender having avg(age) > 30;
    
    
    -- 查询每种性别中的人数多于4个的组的信息
        select gender,group_concat(name) from classes group by gender having count(*) > 4;

-- 分页显示，每页显示2条数据（以1开头，如果limit3，2以4开头）
        select * from classes limit 0,2;


    -- 按照身高从高到矮排序，查找出所有女性，并且分页显示，每页显示2条数据
        select * from students where gender=2 order by high desc limit 0,2