MariaDB基本知识点总结01--介绍+语句
一.概念
1.数据库介绍：
简单的说，数据库就是一个存放数据的仓库，这个仓库是按照一定的数据结构（数据结构是指数据的组织形式或数据之间的联系）来组织，存储的，我们可以通过数据库提供的多种方法来管理数据库里的数据。
2.关系型数据库VS非关系型数据库
(1).关系型数据库:关系型数据库模型是把复杂的数据结构归结为简单的二元关系（既二维格式表）如：oracle，db2，sqlserver，mysql，mariadb
优点：
易于维护：都是使用表结构，格式一致；
使用方便：SQL语言通用，可用于复杂查询；
复杂操作：支持SQL，可用于一个表以及多个表之间非常复杂的查询。
缺点：
读写性能比较差，尤其是海量数据的高效率读写；
固定的表结构，灵活度稍欠；
高并发读写需求，传统关系型数据库来说，硬盘I/O是一个很大的瓶颈。
(2).关系型数据库：非关系型数据库也称之为nosql数据库，请注意，nosql的本意是“not only SQL”，指的是非关系型数据库，而不是“no SQL”的意思，因此nosql的产生并不是要彻底否定关系型数据库，而是作为传统关系型数据库的一个有效补充，NoSQL数据库在特定的场景下可以发挥出难以想象的高效率和高性能。如：mongodb，redias
优点：
格式灵活：存储数据的格式可以是key,value形式、文档形式、图片形式等等，文档形式、图片形式等等，使用灵活，应用场景广泛，而关系型数据库则只支持基础类型。
速度快：nosql可以使用硬盘或者随机存储器作为载体，而关系型数据库只能使用硬盘；
高扩展性；
成本低：nosql数据库部署简单，基本都是开源软件。
缺点：
不提供sql支持，学习和使用成本较高；
无事务处理；
数据结构相对复杂，复杂查询方面稍欠。
二.关系型数据库MariaDB操作（SQL语句）
1.SQL语句分类
DQL：数据查询语言，用于对数据进行查询，如select
DML：数据库操作语言，对数据库进行增删改查，如：insert，update，delete
TPL：事物处理语言，对事物进行处理，包括begin，transaction，commit，rollback
DCL：数据控制语言，如grant，revoke
DDL：数据定义语言：进行数据库，表的管理等，如create，drop
CCL：指针控制语言，通过控制指针完成表的操作，如declare cursor
2.SQL语句操作01
忘记mysql的root密码 vim /etc/my.conf.d/server.conf 添加skip-grant-tables mysql -uroot | mysql -u root -p 查看数据库 show databases; 查看创建数据库状态 show create database testdb; 使用哪个库 use testdb 修改数据库 create database test03 创建数据库 create database testdb character set utf8; 删除数据库 drop database testdb; 修改数据库默认字符集 alter database 库名 default character set utf8; 查看当前用户 select user(); 给aaa添加查询，创建，更新，删除的权限（在任意主机上） grant select,create,update,delete on test03.grade to aaa@'%'; 给aaa赋予test03数据库的grade表最高权限（在任意主机上） grant all privileges on test03.grade to aaa@'%'; 查看aaa用户的权限（root用户） show grants for 'aaa'; 收回aaa在任意数据库的任意表的最高权限 revoke all on *.* from aaa@'%';
查看自己权限 show grants
3.SQL语句操作02（增删改）
(1).常用数据类型
常用的数据类型
整数:int, bit
小数:decimal #decimal(5,2)
字符串:varchar, char
日期时间:date, time, datetime
枚举类型(enum)
约束
主键primary key：物理上存储的顺序
非空not null：此字段不能为空
唯一unique：此字段不允许重复
默认default：当不填写此值时会使用默认值，如果填写则已填写为准
外键foreign key：对关系字段进行约束，当为关系字段填写值时，会到关联的表中查询此值是否存在，如果存在则填写成功，如果不存在则填写失败并抛出异常
一、创建数据表（table)
1、show databases > use testdb > show tables > create table students 2、create table students (id tinyint unsigned primary key , > name varchar(20),age tinyint unsigned, > high decimal(5,2),gender enum('男','女','保密'), > ); (创建表结构)
3、show tables （查看表是否创建成功) 4、desc students (查看students表结构) 5、show create table students （查看创建表的结构信息） 6、-- 数据库中用-- 来注释
二、改变表结构：(alter) 1、alter table students add birthday datetime (增加字段，后也可加类型) modify birthday date (修改字段后的类型) change birthday birth (修改字段) drop birthday （删除字段）
三、对表内容进行增删改:(insert into,delete,update) 1、增： insert into students values(1,'周超',24,177.50,'男')； insert into students (name,age,high) values ('周超',24,177.50); 2、删： 物理删除 delete from 表名 where 条件 delete from students where name='周超'; 逻辑删除 (此类删除常用于游戏等数据型公司，用来保留用信息及及时跟进激活不活跃用户) 用一条字段来表示 这条信息是否已经不能在使用了 给students表添加一个is_delete字段 bit 类型 alter table students add is_delete bit default 0; update students set is_delete=1 where id=6; 3、改： 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;
4.SQL语句操作03（查询）
--查询基本使用(条件，排序，聚合函数，分组，分页) --创建学生表 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 ); --创建班级表 create table classes( id int unsigned auto_increment primary key not null, name varchar(20) not null ); 一、查询 1.查询所有列 select * from 2.查询制定列 select id,name from students; 3.使用as给字段起别名 select id,name as '姓名', age, high, gender from students; 4.通过表名字段查询 select students.name from students; 5. 给表起别名查询 select s.id,s.name,s.age from students as s; 6.消除重复行(distinct) select distinct age from students; 二、条件查询 1.-比较运算符： 查询年纪大于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; 2.模糊查询(like) %替代1个或者多个甚至是没有 查询姓名中有‘小’的所有名字 select * from students where name like '%小%'; 查询两个字人的名字 select * from students where name like '__'; 查询至少有2个字的名字 select * from students where name like '%__%'; 3.范围查询 查询 年纪为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; 4.空判断(is null) 查询身高为空的信息 select * from students where high is null; 判断非空is not null select * from students where high is not null; 三、排序 order by 字段 asc从小到大排列,即升序(默认) desc从大到小排序，即降序 1.查询年纪在18到34岁之间的男性，按照年纪从小到大 select * from students where gender=1 and age between 18 and 34 order by age; 2.查询年纪在18到34岁之间的女性，身高从高到矮 select * from students where gender=2 and age between 18 and 34 order by high desc; order by 多字段 1. 查询年纪在18到34岁的男性，身高从高到矮排序，如果身高相同的情况下按照年纪从小到大排序，如果年龄也相等那么按照id从小到大排序； select * from students where age between 18 and 34 and gender=1 order by high desc, age, id desc; 四、聚合函数 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) 计算平均年纪 select avg(age)from students; select sum(age)/count(*) from students; 保留2位小数 select round(avg(age),2) from students; 五、分组（group by） 1.按照性别分组，查询所有的性别 select gender from students group by gender; 2.计算每组性别的人数 select gender, count(*) from students group by gender; 3.查询男性组中的姓名 group_concat select gender,group_concat(name) from students where gender=1 group by gender; having 4.查询每个性别平均年纪超过30岁的性别，以及姓名 having avg(age) > 30 select gender, group_concat(name) from students group by gender having avg(age) > 30; 5.查询每种性别中的人数多于4个的组的信息 select gender,group_concat(name) from students group by gender having count(*)>4; 六、分页（limit） 1.显示5页 select * from students limit 5; 2.分页显示，每页显示2条数据 select * from students limit 0, 2; 3. 按照身高从高到矮排序，查找出所有女性，并且分页显示，每页显示2条数据 select * from students where gender=2 order by high desc limit 0,2;
5.SQL语句操作04（连接查询）
--创建学生表 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 ); --创建班级表 create table classes( id int unsigned auto_increment primary key not null, name varchar(20) not null ); --往students表里插入数据 insert into students values (0,'小明',18,180.00,2,1,0), (0,'小月月',19,180.00,2,2,0), (0,'彭于晏',28,185.00,1,1,0), (0,'刘德华',58,175.00,1,2,0), (0,'黄蓉',108,160.00,2,1,0), (0,'凤姐',44,150.00,4,2,1), (0,'王祖贤',52,170.00,2,1,1), (0,'周杰伦儿',34,null,1,1,0), (0,'程坤',44,181.00,1,2,0), (0,'和珅',55,166.00,2,2,0), (0,'刘亦菲',29,162.00,3,3,0), (0,'金星',45,180.00,2,4,0), (0,'静香',18,170.00,1,4,0), (0,'郭静',22,167.00,2,5,0), (0,'周杰',33,178.00,1,1,0), (0,'钱小豪',56,178.00,1,1,0), (0,'谢霆锋',38,175.00,1,1,0); --向classes表里插入数据 insert into classes values (0, '云唯_01期'),(0, '云唯_02期'); -- 连接查询（内关联） -- inner join ... on -- 两个表连接查询 select * from students inner join classes -- 查询能够对应班级的学生以及班级信息 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.name, c.name from students as s inner join classes as c on s.cls_id=c.id; -- 查询 有能够对应班级的学生以及班级信息，显示学生的所有信息，只显示班级名称 select students.*, classes.name from students inner join classes on students.cls_id=classes.id; -- 在以上查询中将班级姓名显示在第一列 select classes.name,students.* from students inner join classes on students.cls_id=classes.id; -- 查询有能够对应班级的学生以及班级信息，按照班级进行排序 select classes.id, students.* from students inner join classes on students.cls_id=classes.id order by classes.id; -- 当是同一个班级的时候，按照学生的id从小到大 select classes.id, students.* from students inner join classes on students.cls_id=classes.id order by classes.id, students.id; --连接查询（左关联，右关联） --左关联，以左边表为基准，条件对应不上的显示null select * from students left join classes on students.cls_id=classes.id; --右关联，以后边为基准，条件对应不上的显示null select * from classes left join students on students.cls_id=classes.id; -- 自关联 create table areas( aid int primary key auto_increment, name varchar(20), pid int ); -- 查询出河北省所有市 select * from areas as province inner join areas as city on province.aid=city.pid having name='河北省'; select province.name,city.name from areas as province inner join areas as city on province.aid=city.pid having province.name='河北省'; -- 子查询 -- 标量子查询 -- 查询出北京市所有区的信息 select * from areas where pid=(select aid from areas where name='北京市'); select * from areas where pid in (select aid from areas where name='北京市');
6.SQL语句操作05（视图，事务，索引，外键）
一.视图 对于复杂的查询，在多个地方被使用，如果需求发生了改变，需要更改sql语句，则需要在多个地方进行修改，维护起来非常麻烦； 视图本质就是对查询的封装。 1.创建视图： create view 视图名称 as select语句 create view kkk as select * from students inner join classes on students.cls_id = classes.uid; (注意：在连接查询时，两个表不要有相同的字段名) 2.查看视图：查看表会将所有的视图也列出来 show tables; 3.删除视图： drop view 视图名称; 4.使用：视图的用途就是查询: select * from 视图名称;
二.外键 如果一个实体的某个字段指向另一个实体的主键，就称为外键。被指向的实体，称之为主实体（主表），也叫父实体（父表）。 负责指向的实体，称之为从实体（从表），也叫子实体（子表）。 对关系字段进行约束，当为从表中的关系字段填写值时，会到关联的主表中查询此值是否存在，如果存在则填写成功，如果不存在则填写失败并报错。 1.添加外键： alter table students add constraint fk foreign key(cls_id) references classes(uid) ; 在创建外键时，父table的主键和子table的外键的类型必须一致。 alter table students add constraint fk foreign key(cls_id) references classes(uid) on delete cascade; 用这种方法创建的外键，可以通过删除父table的主键来删除子table的数据； 2.查看外键： show create table 表名 3.删除外键： alter table students drop foreign key 外键名字;
三.索引 索引可以让在大量数据查询中效率更高； 1.创建索引: create index 索引名称 on 表名(字段名称(长度)) 如：create index age_index on create_index(age); 如果指定字段是字符串，需要指定长度，建议长度与定义字段时的长度一致 字段类型如果不是字符串，可以不填写长度部分 2.查看索引： show index from 表名; 3.删除索引： drop index 索引名称 on 表名; 4、拓展：测试时间 set profiling=1;#打开sql语句执行时间 show profiles;#查看sql执行时间
四.事务 事务具有ACID特性：原子性(A,atomicity)、一致性(C,consistency)、隔离性(I,isolation)、持久性(D,durabulity) 原子性：事务内的所有操作要么都执行，要么都不执行，它是一个不可分割的工作单位； 一致性：数据库总是从一个一致性的状态转换到另一个一致性的状态（在前面的例子当中，一致性确保了，即使在执行第三、四条语句之间系统崩溃， 支票账户也不会损失200美元，因为事物最终没有被提交，所以事物中所做的修改也不会保存到数据库中）； 隔离性：一个事物所做的修改在最终提交之前，对其他事物是不可见的（在前面的例子中，当执行完第三条语句、第四条语句还未开始时， 此时有另外一个账户汇总程序开始运行，则其看到的支票账户的余额并没有被减去200美元。）； 持久性：事务完成后，该事务内涉及的数据必须持久性的写入磁盘保证其持久性。当然，这是从事务的角度来考虑的的持久性， 从操作系统故障或硬件故障来说，这是不一定的。 事务命令： 要求：表的引擎类型必须是innodb类型才可以使用事务，这是mysql表的默认引擎；查看表的创建语句，可以看到engine=innodb。 1.开启事务： 开启事务后执行修改命令，变更会维护到本地缓存中，而不维护到物理表中 begin; ......commands;(delete/update/insert。。等) 2.提交事务： 将缓存中的数据变更维护到物理表中 commit; 3.回滚事务： 放弃缓存中变更的数据(也就是以rollback命令结束事务，数据没有改变，可以恢复) rollback;
