mariadb数据库增删改查

1.常用数据类型

1）整数:int, bit

2）小数:decimal 　　    #decimal(5,2)表示共有五位数，保留两位小数

3）字符串:varchar, char　　                      

4）日期时间:date, time, datetime

5）枚举类型(enum)

2.约束

1）主键primary key：物理上存储的顺序

2）非空not null：此字段不能为空

3）唯一unique：此字段不允许重复

4）默认default：当不填写此值时会使用默认值，如果填写则已填写为准

5）外键foreign key：对关系字段进行约束，当为关系字段填写值时，会到关联的表中查询此值是否存在，如果存在则填写成功，如果不存在则填写失败并抛出异常

数值类型
类型	字节大小	有符号范围	无符号范围
TINYINT	1	-127~127	0~255
SMALLINT	2	-32768~32767	0~65535
MEDIUMINT	3	-8388608~8388607	0~16777215
INT/INTEGER	4	-2147483648~2147483647	0~4294967265
BIGINT	8	-9223372036854775808~-9223372036854775807	0~18446744073709551615
字符串
类型	字节大小	示例
CHAR	0-255	char(3)不管输入几个字节都会占3个字节
VARCHAR	0-255	varchar(3)输入比三小的字节会占用实际字节大小
TEXT	0-65535	大文本
日期时间类型
DATE	4	‘2020-01-01’
TIME	3	’12:05:34’
DATETIME	8	‘2020-01-01  12:05:34’
YEAR	1	‘2019’
TIMESTAMP	4	‘1970-01-01  00:00:01’UTC~‘2038-01-01  00:00:01’UTC

3.sql语句alter

显示当前时间

select now();

创建classes表(id, name)

create table zzzz(

    id int primary key not null auto_increment,

    name varchar(20)，

    age int

);

查看表结构

desc zzzz

1）创建students表(id, name, age, high, gender, cls_id)

create table students (

    id int unsigned not null auto_increment primary key,

    name varchar(20),

    age tinyint unsigned default 0,

    high decimal(5,2),

    gender enum('男', '女', '中性', '保密') default '保密',

    cls_id int unsigned

);

创建classes表(id, name)

create table classes(

    id int unsigned not null auto_increment primary key,

    name varchar(20)

);

2）修改表属性

修改表-添加字段

alter table 表名 add 列名 类型;

alter table students add birthday datetime;

修改表-修改字段:不重命名版

 alter table 表名 modify 列名 类型及约束;

alter table students modify birthday date;

修改表-修改字段:重命名版

alter table 表名 change 原名 新名 类型及约束;

alter table students change birthday birth date;

3）修改表-删除字段

alter table 表名 drop 列名;

alter table students drop birthday;

4) 删除表

drop table 表名;

drop table students;

4.sql语句增加insert

1）全列插入

insert into 表名 values(..)    主键字段 可以用0 null default 来站位

向students表里插入 一个学生信息

insert into students values (0,'小明',19,188.999,'男', 1);

             

2）部分插入

        insert into students(id, name, age) values (0,'绿帽子',19);

        部分插入(多条记录)

        insert into students(id, name, age) values (0,'绿帽子',19),(0,'小跳蚤',21);

       

5.sql语句修改update

update 表名 set 列1=值1, 列2=值2... where 条件;

update students set age=100 where id=1;

update students set age=100,cls_id=77 where id=1;

6.sql语句删除delete与truncate

1）物理删除

delete from 表名 where 条件

delete from students where cls_id=88;   

2）逻辑删除

用一条字段来表示 这条信息是否已经不能在使用了

给students表添加一个is_delete字段 bit 类型

alter table students add is_delete bit default 0;

update students set is_delete=1 where id=6;

3）truncate

清空表，连同id字段自增重置为1，重新插入的数据id默认会从1开始，用truncate删除的数据无法恢复

truncate students

7.sql语句查看select

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

1）查询所有列

select * from 表名

select * from students;

一定条件查询(where)

select * from where id=5;

   

查询制定列

select id,name from students;

   

使用as给字段起别名

select id,name as '姓名', age, high, gender from students;

   

通过表名字段查询

select students.name from students;

   

给表起别名查询

select s.id,s.name,s.age from students as s;

   

消除重复行

distinct

select distinct age from students;

2）条件查询

查询年纪大于18岁的信息

select * from students where age > 18;

       

18岁到28岁之间(and)

select * from students where age >= 18 and age =< 28;    允许使用&&

select * from students where age between 18 and 28

 

在18岁以上或者身高180以上的人(or)

select * from students where age > 18 or high > 180;    允许使用||

3）模糊查询like

% 替代1个或者多个甚至是没有

查询姓名中有‘小’的所有名字

select * from students where name like '%小%';

 

查询两个字人的名字

select * from students where name like '__';    两个下划线

 

查询至少有2个字的名字

select * from students where name like '%__%';

4）范围查询

in (1,3,8)表示在一个非连续的范围内

查询年纪为18和34的人

select * from students where age in (18, 34);

 

查询 年龄在17岁到34岁之间的信息

select * from students where age between 17 and 34;

 

查询 年纪不在18到34岁的信息

select * from students where age not between 17 and 34;

5）空判断

判断is null

查询身高为空的人的信息

select * from students where high is null;

6）排序order by

asc从小到大排列,即升序

desc从大到小排序，即降序

order by支持多字段

 

查询年纪在18到34岁之间的男性，按照年纪从小到大

select * from students where gender=1 and age between 18 and 34 order by age;

 

查询年纪在18到34岁之间的女性，身高从高到矮

select * from students where gender=2 and age between 18 and 34 order by high desc;

 

查询年纪在18到34岁的男性，身高从高到矮排序，如果身高相同的情况下按照年纪从小到大排序，如果年龄也相等那么按照id从小到大排序；

select * from students where age between 18 and 34 and gender=1 order by high desc,age,id;

       

8.聚合函数

1）总数count

查询男性有多少人

select count(*) from students where gender=1;

2）最大值max

查询最大的年纪

select max(age) from students;

 

查询女性的最高身高

select max(high) from students where gender=2;

3）最小值 min

select min(high) from students;

4）求和sum

计算所有人的年龄总和

select sum(age) from students;

5）平均值avg

计算平均年纪 sum(age)/count(*)

select sum(age)/count(*) from students;

select avg(age),2 from students;

 

保留2位小数

select round(avg(age),2) from students;

   

6）分组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 group by gender;    group_concat()按照拼接后的字符串显示

7）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 students group by gender having count(*)>4;

8）分页limit

显示5页

select * from students limit 5;

 

分页显示，每页显示2条数据

select * from students limit 0, 2;

 

按照身高从高到矮排序，查找出所有女性，并且分页显示，每页显示2条数据

select * from students where gender=2 order by high desc limit 0,2;