MySQL

 

一.概述

数据库（Database,简称DB），类似于文件夹；

数据库表（table），类似于文件，用来存储数据的；每张表是由行和列组成；字段名也是就列名；

数据（data），存储在表中的信息就叫做数据；

数据库系统有3个主要的组成部分

　　　1.数据库（Database System）：用于存储数据的地方。

　　　2.数据库管理系统（Database Management System，DBMS）：用户管理数据库的软件。

　　　3.数据库应用程序（Database Application）：为了提高数据库系统的处理能力所使用的管理数据库的软件补充。

二.数据库的特点

⑴ 实现数据共享

　　　数据共享包含所有用户可同时存取数据库中的数据，也包括用户可以用各种方式通过接口使用数据库，并提供数据共享。

 

⑵ 减少数据的冗余度

　　　同文件系统相比，由于数据库实现了数据共享，从而避免了用户各自建立应用文件。减少了大量重复数据，减少了数据冗余，维护了数据的一致性。

 

⑶ 数据一致性和可维护性，以确保数据的安全性和可靠性

　　　主要包括：①安全性控制：以防止数据丢失、错误更新和越权使用；

　　　　　　　　②完整性控制：保证数据的正确性、有效性和相容性；

　　　　　　　　③并发控制：使在同一时间周期内，允许对数据实现多路存取，又能防止用户之间的不正常交互作用。

 

⑷ 故障恢复

　　　 由数据库管理系统提供一套方法，可及时发现故障和修复故障，从而防止数据被破坏。数据库系统能尽快恢复数据库系统运行时出现的故障，可能是物理上或是逻辑上的错误。比如对系统的误操作造成的数据错误等。

 

 三.数据库分类

最常用的数据库模型主要是两种，即 关系型数据库 和 非关系型数据库；

关系型数据库：是把复杂的数据结构归结为简单的二元关系，即二维表格形式；

主流的关系型数据库为MySQL,Oracle,DB2;

非关系型数据库：也被称为 NoSQL数据库， NoSQL 的含义为Not Only SQL ， 是对传统关系型数据库的一个有效的补充；

传统的关系型数据库IO瓶颈、性能瓶颈都难以有效突破，于是出现了大批针对特定场景，以高性能和使用便利为目的功能特异化的数据库产品。

NOSQL 为了高性能，高并发而生，典型产品为redis等；

为什么选择MySQL数据库?

　　　（1） MySQL性能卓越、服务稳定，很少出现异常宕机 
　　　（2） MySQL开放源代码且无版权制约，自主性及使用成本低 
   　　（3） MySQL历史悠久，社区及用户活跃，遇到问题可以解决 
   　　（4） MySQL软件体积小，安装使用简单，并且易于维护，安装及维护成本低 
   　　（5） MySQL品牌口碑效应，使得企业无需考虑就直接用 
   　　（6） MySQL支持多用操作系统，提供多种API接口，支持多用开发语言，特别对流行的语言有很好的支持

SQL语言:是关系型数据库的标准语言, 其主要用于存取数据，查询数据，更新数据和管理数据库系统 等操作。

 

 

MySQL之库的操作：

1.创建数据库：

create database 数据库的名字 CHARSET utf8; # 当前库的编码集为utf8

2.查看数据库：

#查询当前用户下所有数据库
show databases;

#查看创建数据库的信息
show create database db_name;

#查询当前操作所在的数据库名称
select database();

3.删除数据库

DROP DATABASE db5;

4.进入数据库

use databasename

5.查看数据库里面的表

show tables;

　　　　　

四.用户权限

1.用户管理

创建用户
    create user '用户名'@'IP地址' identified by '密码';
删除用户
    drop user '用户名'@'IP地址';
修改用户
    rename user '用户名'@'IP地址'; to '新用户名'@'IP地址';

　　

2.授权管理

show grants for '用户'@'IP地址'                  -- 查看权限
grant  权限 on 数据库.表 to   '用户'@'IP地址'      -- 授权
revoke 权限 on 数据库.表 from '用户'@'IP地址'      -- 取消权限

　　

MySQL之表的操作：

create database db5 CHARSET utf8;
DROP DATABASE db5;

-- CREATE table info(id int not null auto_increment PRIMARY key,
-- name VARCHAR(50) not null,
-- sex CHAR(2) null)  创建表

-- 查看表
-- select * from info
-- 修改表
-- ALTER table info add age int not null; # 添加一个字段，字段后面必须要添加 字段的属性；
-- ALTER table info CHANGE name names VARCHAR(50) not null;修改表里面的字段；
-- rename table info to user_info 修改表名；
-- INSERT user_info VALUES(30,'曾辉','男',23)  #插入所有的字段。
-- INSERT user_info(id,NAMES) VALUES(2,'ZZZ'); 插入固定的字段。 
-- insert into user_info(id,NAMES) values(3,'aa'), (4,'aaaa'),(5,'aaaaa');在表中添加多个数据；
-- 操作数据
-- update user_info set sex ='人妖',names = '小强' where id =12;修改
-- select names,sex as '性别' from user_info; as 是别名；便于理解；
-- delete from user_info where id in('1','3','5')
-- truncate user_info; #清空表中的数据，并且也把自增主键也同时删去了；

　　

 MySQL之数据的操作：

-- select name,salary+500 as salary from person ;  #查询的时候数字可以进行四则运算；
-- select distinct age,name from person; 按照后面的多个数据相加后去重，而不是单看一个了。
-- select name,sex from person where age >=20 and age <=30;
-- select name,sex from person where age between 20 and 30; 区间
-- select name,sex from person where age <> 20; 不等于，相当于 ！=
-- select * from person where dept_id is null; #这是数据为空值 (null)的用is ;
-- select * from person where name = '';  #这是数据为空，即为空字符( )的用 = ;
-- select name from person where not age>20; #非
-- select * from person where id in (1,3,5);#in 为集合，
-- select * from person where name like '%e'; #最后结尾是e的 ;模糊查询
-- select * from person where name like 'e%';#开头是e的
-- select * from person where name like '%e%';#只要是包含e的
-- select * from person where name like '___';#_表示一个站位符； 名字是3位的；
-- select * from person where name like '__' ORDER BY salary DESC;# ORDER BY 排序，默认asc正序；desc倒序；
 # 条件加在排序的前面；
-- select * from person ORDER BY CONVERT(name USING gbk); #按照名字的ascil码
-- select max(age),min(age),avg(age) from person; #聚合函数
-- select avg(salary),dept_id,GROUP_CONCAT(name) from person GROUP BY dept_id;#查看每个部门平均工资，并且查看人员；
#GROUP_CONCAT（值）将聚合分开，取每个值组合成字段，然后用逗号分隔。
-- select avg(salary)as salary,dept_id,GROUP_CONCAT(name) from person GROUP BY dept_id HAVING salary >100000;#HAVING就跟where一样是条件。
-- select * from person LIMIT 0,4;  #LIMIT 为分页，第一个参数为 起始的数，第二个参数为页数。

　

truncate 和delete 都是删除表中的数据，但是两者有区别：

1.truncate 在各种表上无论是大的还是小的 删除都是非常快的，但是 delete  会被表中的数据量的大小影响其执行的效率；

2.truncate  不能触发 触发器，而 delete 会触发 触发器；

3.truncate  清空表中的数据，并且也把自增主键也同时删去了；而 delete 不会把自增和主键同时删去；

SQL语句关键字的执行顺序：

select name, max(salary)  #5
from person   #1
where name is not null   #2  
group by name  #3   
having max(salary) > 5000 #4  
order by max(salary) #6 
limit 0,5 #7

　　

以上是单表的查询方式；下面是多表的查询方式：

-- select * from person p,dept d where p.dept_id = d.did;#多表查询

-- SELECT * from person p LEFT JOIN dept d on p.dept_id = d.did;#左连接，即左边中的数据全部显示出来；
-- SELECT * from person p RIGHT JOIN dept d on p.dept_id = d.did;#右连接，即右边中的数据全部显示出来；
-- SELECT * from person p INNER JOIN dept d on p.dept_id = d.did;
#内连接和多表查询是一样的，只显示出两个表共同的条件数据；
-- SELECT * FROM person LEFT JOIN dept ON person.dept_id = dept.did
-- UNION ALL#全连接，只是 不去重， -- UNION  #全连接，并且 去重，
-- SELECT * FROM person RIGHT JOIN dept ON person.dept_id = dept.did;
-- 查询出 教学部 年龄大于20岁,并且工资小于40000的员工,按工资倒序排列
#内连接把两个表结合在一起；
-- select * from person p INNER JOIN dept d on p.dept_id = d.did 
-- and d.dname ='教学部'
-- and age >20
-- and salary < 40000
-- ORDER BY salary DESC;

-- select did from dept where dname = '教学部';
-- select * from person where person.dept_id = (select did from dept where dname = '教学部') 
and age>20
-- and salary >40000 ORDER BY salary DESC; #()是代表优先级，优先执行;
#查询每个部门中最高工资和最低工资是多少,显示部门名称
-- -- select * from person p INNER JOIN dept d on p.dept_id = d.did ;
-- select max(salary),min(salary),dname from(select * from person p INNER JOIN dept d 
on p.dept_id = d.did)as n
-- GROUP BY dname;

#person p INNER JOIN dept d on p.dept_id = d.did 结合的表。
-- select max(salary),min(salary),dname from person p INNER JOIN dept d on p.dept_id = d.did
-- GROUP BY dname;

#多表连接相当于一个组合的表；

#1.查询平均年龄在20岁以上的部门名.
 select avg(age),dname from (select * from person p INNER JOIN dept d where p.dept_id = d.did)
 as a GROUP BY dname HAVING avg(age)>20 ;
#2.查询教学部 下的员工信息.
-- select * from person p INNER JOIN dept d ON p.dept_id = d.did AND dname = '教学部';
-- select * from (select * from person p INNER JOIN dept d ON p.dept_id = d.did AND dname = '教学部')as n;
#3.查询大于所有人平均工资的人员的姓名与年龄
-- SELECT avg(salary) from person;
-- select name,age from person where salary > (SELECT avg(salary) from person)

#子语句
#  子语句就相当于一个 元组；
#1.查询平均年龄在20岁以上的部门名.
-- select dname from dept where did in 
(select dept_id from person GROUP BY dept_id HAVING avg(age) >20);
#2.查询教学部 下的员工信息.
-- select * from person where dept_id in (select did from dept where dname='教学部')
#3.查询大于所有人平均工资的人员的姓名与年龄
-- select name,age from person where salary >(select avg(salary) from person)
#查询高于本部门平均工资的本部门的人员；
-- select avg(salary) from person GROUP BY dept_id;
-- select * from person p1,(select dept_id,avg(salary)as 'avg_salary' from person GROUP BY dept_id)as p2 
-- where p1.dept_id = p2.dept_id AND p1.salary > p2.avg_salary;
#根据工资高低,将人员划分为两个级别,分别为 高端人群和低端人群。显示效果:姓名,年龄,性别,工资,级别 IF;
-- SELECT p1.*,
-- IF(p1.salary >10000,'高端人群','低端人群') as '级别'  #自己创建一个字段；
-- from person p1;
#需求2: 根据工资高低,统计每个部门人员收入情况,划分为 富人,小资,平民,吊丝 四个级别, 要求统计四个级别分别有多少人
-- select * from person p LEFT JOIN dept d on p.dept_id = d.did 
-- select dname,SUM((CASE WHEN salary >50000 THEN 1 ELSE 0 END)) as '富人',
-- SUM((CASE WHEN salary BETWEEN 29000 and 50000 THEN 1 ELSE 0 END)) as '小资',
-- SUM((CASE WHEN salary BETWEEN 10000 and 29000 THEN 1 ELSE 0 END)) as '平民',
-- SUM((CASE WHEN salary <10000 THEN 1 ELSE 0 END)) as '屌丝'
-- from (select * from person p LEFT JOIN dept d on p.dept_id = d.did )as n GROUP BY dept_id

#count(*)相当于没有分组前的行数
#查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
-- SELECT course.c_name,avg(num)  
FROM score,course where score.c_id = course.c_id GROUP BY score.c_id 
ORDER BY avg(num) ASC , score.c_id DESC
#按条件排序，可以按照多个条件，如果第一个条件不满足，则按照第二个条件；即 平均成绩相同时，按课程号降序排列.

　　

在多表查询中的SQL语句的执行的顺序：

#mysql 语句关键字的执行顺序：
-- SELECT DISTINCT <select_list> #7
-- FROM <left_table> #1
-- <join_type> JOIN <right_table> #3
-- ON <join_condition>  #2
-- WHERE <where_condition> #4
-- GROUP BY <group_by_list>  #5
-- HAVING <having_condition>  #6
-- ORDER BY <order_by_condition> #8
-- LIMIT <limit_number> #9

　　

 约束：

约束是一种限制，它通过对表的行或列的数据做出限制，来确保表的数据的完整性、唯一性

1.外键约束：

CREATE TABLE IF NOT EXISTS person(
    id int not null auto_increment PRIMARY KEY,
    name VARCHAR(50) not null,
    age TINYINT(4) null DEFAULT 0,
    sex enum('男','女','人妖') NOT NULL DEFAULT '人妖',
    salary decimal(10,2) NULL DEFAULT '250.00',
    hire_date date NOT NULL,
    dept_id int(11) DEFAULT NULL,
　  CONSTRAINT fk_did FOREIGN KEY(dept_id) REFERENCES dept(did) -- 添加外键约束
               #外键的名字     约束创建表中的字段    与另外一个表中的字段 ；
																			   #两者进行约束
)ENGINE = INNODB DEFAULT charset utf8;
#在已经创建表后，在追加外键约束；
ALTER TABLE pesron add constraint fk_did foreign key (dept_id) references dept(did)
#删除外键约束 
ALTER TABLE person DROP foreign key  fk_did;

　

定义外键的条件：　

（1）外键对应的字段数据类型保持一致,且被关联的字段(即references指定的另外一个表的字段)，必须保证唯一

（2）所有tables的存储引擎必须是InnoDB类型.

（3）外键的约束4种类型: 1.RESTRICT 2. NO ACTION 3.CASCADE 4.SET NULL

（4）建议:1.如果需要外键约束,最好创建表同时创建外键约束.

　　　　   2.如果需要设置级联关系,删除时最好设置为 SET NULL.

references（参考） 后面是 主表的数据；

注意：插入数据时，先插入主表中的数据，再插入从表中的数据；

　　　删除数据时，先删除从表的数据，再删除主表中的数据；

2.非空的约束

关键字：NOT NULL ，表示 不可空，用来约束表中的字段列；

3.主键约束

primary key

主键这一行的数据不能重复，并且不能为空；

复合主键，主键不仅可以是表中的一列，也可以由表中的两列或多列来共同标识

create table t3(
    id int(10) not null,
    name varchar(100) ,
    primary key(id,name)
);

4.唯一约束　

关键字: UNIQUE, 比较简单，它规定一张表中指定的一列的值必须不能有重复值，即这一列每个值都是唯一的。

create table t4(
    id int(10) not null,
    name varchar(255) ,
    unique id_name(id,name)
);
//添加唯一约束
alter table t4 add unique id_name(id,name);  #id和name 一起不能有重复；
//删除唯一约束
alter table t4 drop index id_name;

5.默认值约束

关键字为：default

create table t5(
    id int(10) not null primary key,
    name varchar(255) default '张三'   
);
#插入数据
INSERT into t5(id) VALUES(1),(2);

　注意: INSERT语句执行时.，如果被DEFAULT约束的位置没有值，那么这个位置将会被DEFAULT的值填充。　

 

表与表之间的关系:

表的关系总体可以分为三类： 一对一；一对多；多对多；

数据库三范式-详解