数据库的分类
mysql5.7安装rpm
基础命令 创建删除库和表 插入数据 查看表结构 查看数据
数据类型 int char
完整性约束条
主键 多列联合主键 非空 唯一 默认 自增 外键
添加和删除约束条件
数据库存储引擎(发动机)
(一)数据库存储引擎是数据库底层软件组件,数据库管理系统(DBMS)使用数据引擎进行创建、查询、更新和删除数据操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能,使用不同的存储引擎,还可以获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySQL的核心就是存储引擎。
存储引擎说白了就是数据存储的格式,不同的存储引擎功能不同,占用的空间大小不同,读取性能也不同
数据库存储引擎是数据库底层软件组件,不同的存储引擎提供不同的存储机制
在 MySQL 中,不需要在整个服务器中使用同一种存储引擎,可以对每一个表使用不同的存储引擎
MySQL 支持多种存储引擎,如 InnoDB 、MyISAM 、Memory 、Merge 、Archive 、CSV 、Federated 等等
(二)MySQL存储引擎简介
MySQL提供了多个不同的存储引擎,包括处理事务安全表的引擎和处理非事务安全表的引擎。在MySQL中,不需要在整个服务器中使用一种引擎,针对具体要求可以对每一个表使用不同的存储引擎。MySQL5.5支持的存储引擎有:InnoDB、MyISAM、Memory等。查看引擎的命令用
SHOW ENGINES\G
mysql>SHOW ENGINES\G
*************************** 1. row ***************************
Engine: CSV
Support: YES
Comment: CSV storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 2. row ***************************
Engine: PERFORMANCE_SCHEMA
Support: YES
Comment: Performance Schema
Transactions: NO
XA: NO
Savepoints: NO
*************************** 3. row ***************************
Engine: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
*************************** 4. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 5. row ***************************
Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 6. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
6 rows in set (0.00 sec)
查看默认存储引擎:
mysql>SHOW VARIABLES LIKE 'default_storage_engine';
+----------------+--------+
| Variable_name | Value |
+----------------+--------+
| storage_engine | InnoDB |
+----------------+--------+
1 row in set (0.00 sec)
MyISAM 存储引擎特点:5.7 InnoDB
(1) MySQL 5.5 之前使用 MyISAM 引擎,MySQL 5.5 之后使用 InnoDB 引擎
(2) MyISAM 引擎读取速度较快,占用资源相对较少,不支持事务,不支持外键约束,但支持全文索引
(3) 读写互相阻塞,也就是说读数据的时候你就不能写数据,写数据的时候你就不能读数据
(4) MyISAM 引擎只能缓存索引,而不能缓存数据
MyISAM 适用场景:
(1) 不需要事务支持的业务,例如转账就不行
(2) 适用于读数据比较多的业务,不适用于读写频繁的业务
(3) 并发相对较低、数据修改相对较少的业务
(4) 硬件资源比较差的机器可以考虑使用 MyISAM 引擎
InnoDB 存储引擎特点:
(1) 事务型数据库的首选引擎,支持事务安全表,支持行锁定和外键,MySQL 5.5 版本之后,InnoDB 作为默认存储引擎
(2) 具有提交、回滚和崩溃恢复能力的事务安全存储引擎,能处理巨大数据量,性能及效率高,完全支持外键完整性约束
(3) 具有非常高效的缓存特性,能缓存索引也能缓存数据,对硬件要求比较高
(4) 使用 InnoDB 时,将在 MySQL 数据目录下创建一个名为 ibdata1 的 10MB 大小的自动扩展数据文件,以及两个名为 ib_logfile0 和 ib_logfile1 的 5MB 大小的日志文件
InnoDB 适用场景:
(1) 需要事务支持的业务、高并发的业务
(2) 数据更新较为频繁的场景,比如 BBS、SNS、微博等
(3) 数据一致性要求较高的业务,比如充值转账、银行卡转账
Memory 存储引擎特点:
(1) Memory 存储引擎将表中的数据存储到内存中,为查询和引用其他表数据提供快速访问
(2) Memory 存储引擎执行 HASH 和 BTREE 索引,不支持 BLOB 和 TEXT 列,支持 AUTO_INCREMENT 列和对可包含 NULL 值得列的索引
(3) 当不再需要 Memory 表的内容时,要释放被 Memory 表使用的内存,应该执行 DELETE FROM 或 TRUNCATE TABLE ,或者删除整个表
四、存储引擎的选择
(1) 如果要提供提交、回滚和崩溃恢复能力的事务安全能力,并要求实现并发控制,InnoDB 是个很好的选择
(2) 如果数据表主要用来插入和查询记录,则 MyISAM 引擎能提供较高的处理效率
(3) 如果只是临时存放数据,数据量不大,并且不需要较高的安全性,可以选择将数据保存在内存中的 Memory 引擎,MySQL 使用该引擎作为临时表,存放查询的中间结果
(4) 如果只有 INSERT 和 SELECT 操作,可以选择 Archive 引擎,支持高并发的插入操作,如记录日志信息可以使用 Archive 引擎
存储引擎:
常用的存储引擎的类型:
#myisam:
1.mysql5.5之前的版本默认使用的存储引擎,读性能好,支持全文索引和空间索引。
2.不支持事务,不支持外键。
3.读写阻塞。
4.只支持索引缓存,不支持数据缓存
#innodb:
1.mysql5.5及之后的版本默认使用的存储引擎,支持高并发,支持多种索引类型,但不支持全文索引和空间索引。
2.支持事务,支持外键。
3.读写不会相互阻塞。
4.即支持索引缓存,也支持数据缓存。
5.mysql5.7版本的innodb引擎支持全文索引
#memory:该引擎会将数据存储到内存中,读写性能好,数据稳定性差
mysql> use test;
mysql> show create table student\G #查看表中的信息
ENGINE=InnoDB
#创建表指定存储引擎
mysql> create table aa (
-> id int)engine=MyISAM;
Query OK, 0 rows affected (0.00 sec)
mysql> show create table aa\G
) ENGINE=MyISAM
修改存储引擎
alter table aa ENGINE = InnoDB;
mysql> show create table aa\G
查询:
首先是查询语句的构成
SELECT {* | <字段列表>} FROM <表1>, <表2>.... [ where <表达式> ][ group by <group by definition>] [ having <expr> ][ order by <..> ] [ limit <...> ]
上述是一个全面的查询语句的语法,方括号内的数据表示可以省略
查询不止可以查询一个表,可以同时查看多个表,
WHERE 作为条件判断表达式,可以写入运算符等符号进行查找符合条件的数据
ORDER BY:给指定的字段进行排序,可以选择由高到底进行排列,也可以选择由低到高进行排列
GROUP BY :分组,给某个字段进行分组过后,该字段重复的数据将被合在一条数据中,组成新的记录
HAVING :HAVING语句通常与GROUP BY语句联合使用,用来过滤由GROUP BY语句返回的记录集。
LIMIT :过滤输出的条目数,可以选择从表中的第几行开始输出,也可以指定输出的条目数量
mysql> drop table student;
mysql> drop table class;
学员表
mysql> create table students
-> (
-> s_id int primary key,
-> s_name char(10) not null,
-> s_age int not null,
-> s_sex char(1) not null,
-> c_id int not null,
-> s_grade int
-> );
Query OK, 0 rows affected (0.10 sec)
班级表
mysql> create table class
-> (
-> id int primary key,
-> name char(10) not null,
-> t_name char(10) not null,
-> s_num int not null
-> );
Query OK, 0 rows affected (0.01 sec)
学员表中的数据
mysql> insert into students values (1,"one",15,"m",1,95);
mysql> insert into students values (2,"two",13,"w",1,90);
mysql> insert into students values (3,"three",16,"m",1,85);
mysql> insert into students values (4,"four",17,"m",2,88);
mysql> insert into students values (5,"five",14,"w",2,91);
mysql> insert into students values (6,"six",15,"m",3,35);
mysql> insert into students values (7,"seven",14,"w",3,60);
mysql> insert into students values (8,"eight",17,"w",3,70);
mysql> insert into students values (9,"nine",16,"w",4,97);
mysql> insert into students values (10,"ten",17,"m",4,83);
班级表当中的数据
mysql> insert into class values (1,"A","zhangsan",3);
mysql> insert into class values (2,"B","lala",2);
mysql> insert into class values (3,"C","lisi",3);
mysql> insert into class values (4,"D","wangwu",2);
一 基本查询 查询指定数据表中的所有字段的数据或者某些字段的数据
mysql> select * from students; #不能在生产环境当中应用
mysql> select s_name from students;
mysql> select s_name,s_sex from students;
二: 条件查询 根据指定的条件返回查询的内容
select */字段名 from 表名 where 条件语句
- 根据比较运算符查询
> >= < <= = !=
查询成绩大于85的学生信息
mysql> select * from students where s_grade > 85;
查询班级编号为1的学生的姓名和成绩
mysql> select s_name,s_grade from students where c_id=1;
练习:查询所有的女生的姓名,年龄和班级编号
mysql> select s_name,s_age,c_id from students where s_sex="w";
- in 列表查询
查询班级编号为1和2的学生信息
mysql> select * from students where c_id in (1,2);
查询班级编号不是1和2的学生信息
mysql> select * from students where c_id not in (1,2);
练习:查询年龄为13 15 17的学生姓名和性别
mysql> select s_name,s_sex from students where s_age in (13,15,17);
- between and 范围查找
查询学生id为3到7的学生信息
mysql> select * from students where s_id between 3 and 7;
查询学生年龄为13-16的学生信息
mysql> select * from students where s_age between 13 and 16;
练习 查询成绩60-90之间学生的信息
mysql> select * from students where s_grade between 60 and 90;
- 空值判断 is null is not null
列出名字为空的学生信息
mysql> select * from students where s_name is null;
列出名字不为空的学生信息
mysql> select * from students where s_name is not null;
- 字符匹配
like 通配符 常用的字符只有%和_
% 匹配多个字符
_ 匹配任何一个字符
regexp 正则匹配表达式配置
.: 任何一个字符
*:一直重复前面的字符
^:匹配以。。开头的字符
$:匹配以。。结尾的字符
[]:匹配包含括号内的任意字符
通配符 可以针对查看变量和sql语句的使用 regexp不能针对查看变量
通配符:
mysql> show variables like "%password%";
mysql> show variables like "%pass____";
regexp 正则匹配
查询教师名当中包含a或者b或者c的班级信息
mysql> select * from class where t_name regexp "[abc]";
查询名字以t开头的学生信息
mysql> select * from students where s_name regexp "^t";
mysql> select * from students where s_name like "t%";
练习:查询名字以e结尾的学生信息 使用两种方法
mysql> select * from students where s_name like "%e";
mysql> select * from students where s_name regexp "e$";
- 多重条件查询
and && 连接多个条件 并且要求多个条件同时满足
or || 连接多个条件 只需要满足其中一个条件即可
查询年龄大于15的男生的成绩和名字
mysql> select s_name,s_grade from students where s_age > 15 and s_sex="m";
mysql> select s_name,s_grade from students where s_age > 15 && s_sex="m";
练习:
查询编号为4-9并且成绩大于85的学生信息
mysql> select * from students where s_id between 4 and 9 and s_grade > 85;
查询编号为4或9并且成绩大于85的学生信息
mysql> select * from students where s_id in (4,9) and s_grade > 85;
练习查询名字以e开头或者以e结尾的学生信息
mysql> select * from students where s_name regexp "^e" or s_name like "%e";
- 排序查询 根据指定的字段的值进行大小排序 查询结果
select */字段名 from 表名 order by 字段名 [desc]
desc 从高到低
按照成绩从高到低进行排序
mysql> select * from students order by s_grade desc;
mysql> select * from students order by s_grade;
排序可以同时满足两个条件 两个排序的情况下 会先满足第一个在满足第二个
两个排序条件用,隔开(满足第一个条件的结果去满足第二个条件)
练习先按照班级编号从高到低排序,在对每个班级的成绩从低到高排序,查询
学生的信息
mysql> select * from students order by c_id desc,s_grade;
四 分组查询 对指定的字段进行分组后返回查询的结果
select group_concat(字段名) from 表名 group by 字段名 [having 条件
语句]
按照班级的id分组查询出学生的名字
mysql> select group_concat(s_name),c_id from students group by c_id;
练习 按照性别分组查询出学生的名字
mysql> select group_concat(s_name),s_sex from students group by s_sex;
常用的聚合函数
max(字段名) :求当前的最大值
mysql> select max(s_grade) from students;
min(字段名):求当前的最小值
mysql> select min(s_grade) from students;
avg(字段名):求平均值
mysql> select avg(s_grade) from students;
sum(字段名):求和
mysql> select sum(s_grade) from students;
count(字段名):计数
查看女生有几个
mysql> select count(s_sex) from students where s_sex="w";
查看学生人数
mysql> select count(s_id) from students;
对于性别进行分类 分别统计出男和女的个数
mysql> select count(s_sex),s_sex from students group by s_sex;
对于性别进行分组 统计出女孩的人数和姓名
mysql> select count(s_sex),s_sex,group_concat(s_name) from
students group by s_sex having s_sex="w";
练习:按照班级进行分组,查询3班的平均分
mysql> select c_id,avg(s_grade) from students group by c_id having c_id=3;
五:分页查询 按照指定的偏移量和行数返回查询的结果
用法: select */字段名 表名 limit m offset n;
查询学生表的前三行
mysql> select * from students limit 3;
查询学生表中3-8行的内容
mysql> select * from students limit 6 offset 2;
mysql> select * from students limit 2,6;
练习:查询学生前三名的信息
mysql> select * from students order by s_grade desc limit 3;
mysql> select * from students order by s_grade desc limit 0,3;
练习:查询学生3-5名的学生信息
mysql> select * from students order by s_grade desc limit 2,3 ;
六:连接查询 可以实现多表查询 查询时需要指定多张表之间存在联系的字段
自然连接查询 只显示两个表中关联值相等的数据
select */字段名 from 表1 表2 where 表1字段名=表2字段名
内连接查询 只显示两张表中关联值相等的数据
select */字段名 from 表1 inner join 表2 on 表1字段名=表2字段名
查询每个学生的姓名 成绩 班级名称和老师姓名
mysql> select s_name,s_grade,name,t_name from students,class where students.c_id=class.id;
mysql> select s_name,s_grade,name,t_name from students inner join class on students.c_id=class.id;
mysql> insert into class values (5,"E","QWE",0);
mysql> select s_name,s_grade,name,t_name from students inner join class on students.c_id=class.id;
外连接查询
左连接查询 将左表中所有的数据列出并将右表中没有的数据使用null表示
mysql> insert into students values (11,"test",17,"m",6,83);
mysql> select s_name,s_grade,name,t_name from students left outer join class on students.c_id=class.id;
右连接查询 将右表中的所有数据列出并将左表中没有的数据使用null表示
mysql> select s_name,s_grade,name,t_name from students right outer join class on students.c_id=class.id;
练习 查询成绩大于70的女生id 名字 年龄 和老师的姓名
mysql> select s_id,s_name,s_age,t_name from students,class where students.c_id=class.id and s_grade > 70 and s_sex="w";
七: 嵌套查询(子查询) 查询语句中嵌套这查询语句
select * /字段名 from 表名 where 字段=|in (select *|字段 from表名 where| order by | limit| group by)
查询3班分数最高的学生的名字和年龄
mysql> select s_name,s_age from students where s_grade=(select max(s_grade) from students group by c_id having c_id=3);
查询成绩小于85分的学生的老师姓名
mysql> select t_name from class where id in (select c_id from students where s_grade < 85);
练习 查询班级当中包含女生的老师姓名
mysql> select t_name from class where id in (select c_id from students where s_sex = "w");
exists 关键字嵌套查询 判断子查询是否有结果 如果有结果执行父查询 如果没有则不执父查询
mysql> select * from class where exists (select * from class where id=1);
mysql> select * from class where exists (select * from class where id=10);
八:联合查询 将多条查询语句连接在一起输出查询的的结果 要求多条查询语句的字段数量必须一致
用法:
查询语句1 union 查询语句2 [union 查询语句n]
查询语句1 union all 查询语句2 [union all 查询语句n]
查询学生表中的学生成绩和id 查询班级表中的班级名字和班级id
mysql> select s_id,s_grade from students union select name,id from class;
查询学生表的成绩和id 查询学生表的成绩和id
mysql> select s_id,s_grade from students union select s_id,s_grade from students;
mysql> select s_id,s_grade from students union all select s_id,s_grade from students;