Mysql 基础知识

数据和数据库

数据：客观事物的符号表示。

数据分类：

结构化数据: 通过统一模式(方式)进行描述和管理。
非结构化数据:数据结构不规则或不完整，没有预定义的数据模型，不方便用数据库二维逻辑表来表现的数据。
半结构化数据:介于两者之间(xml)。

数据库(database DB): 按照数据结构存储数据的仓库

数据库管理系统(database management system: DBMS):操纵和管理数据库的软件。

数据库管理系统分类：

关系型数据库管理系统(RDBMS: relationship):关系型:二维表格(关系模式)

oracle: oracle公司,大型分布式关系型数据库管理系统。   
mysql: oracle公司,开源免费,GPL    
sql server:microsoft公司,中小型    db2: ibm公司,中型

非关系型数据库管理系统(Nosql: not only sql):

redis
hbase
mongodb
neo4j(图)

什么是mysql？

开源免费的关系型数据库管理系统。
采用关系模式来存储和管理数据(表)。
采用分库分表来进行灵活管理。
1996年,由mysql ab公司开发。
2000年,开源 2008年,被sun以10亿美元收购 2009年,sun被oracle以74亿美元收购。

下载和安装

下载: oracle.com/mysql.com
安装: mysql server
mysql data: 目录: Data
数据存放目录。数据库对应一个目录;表对应多个文件。 文件: my.ini 配置文件。

常见使用

登录数据库：mysql -uroot -p密码;
展示数据库：show databases;
切换数据库：use mysql;
展示表：show tables;

sql

概念：

tructure query language:结构化查询语言。
关系型数据库通用语言。 脚本后缀名称。
对大小写不敏感,句尾使用;结束标识。

sql分类：

1、ddl:数据定义语言(实现是数据库对象结构操作) create / alter / drop
2、dml:数据操纵语言(数据的增删改) insert / delete / update
3、dql:数据查询语言(数据查询) select
4、tcl:事务控制语言(事务操作) commit / rollback
5、dcl:数据控制语言(授权) grant / revoke

什么是表？

table,二维关系模型存储和管理数据。 行称为记录,列称为字段。

表的数据类型：

整型数据: tinyint     1个字节  
		 samllint      2个字节
		 mediumint      3个字节
		 int      4个字节
		 bigint      8个字节
 浮点型:float(m,n): m代表长度,n代表精度  
		double(m,n): m长度 n代表精度
		decimal(m,n):金额(不允许出现精度损失)
 字符串类型:char(n): 定长字符串
		   varchar(n): 可变长字符串
 日期类型: date
   		  time
   		  datetime:年月日时分秒
   		   timestamp:时间戳
  其他类型: text: 文本
  		   blob:二进制数据
  		   enum:枚举类型
  		   set:集合类型
  		   json:json类型

表对象结构操作：

create / alter / drop
创建表：create table [ if not exists ] jasperlee;
销毁表：drop table [if exists ] jasperlee;
表的修改(字段名称,字段类型,长度,位置,约束) :alter
1、添加字段
alter table tname add 字段(字段名称 字段类型 约束 位置)
追加：alter table student add tel varchar(11);
添加到首列：alter table student add sid int first;
添加到指定位置：alter table student add email varchar(50) after sid;
2、字段修改:
alter table tname modify 字段名字 字段类型 约束 first|after
alter table tname change 旧字段 新字段名字 字段类型 约束 first|after

alter table tname change 旧字段 新字段(名称，类型…)
alter table tname modify 字段信息(名称,类型…)

3、修改类型(长度) 20->50
alter table student modify name varchar(50);
alter table student change name name varchar(20);
4、修改字段名称
alter table student change name sname varchar(20);
5、修改字段位置
alter table student modify age int after sex;
6、删除字段:
alter table tname drop field;
7、重命名:
rename table tname to newname;

数据库设计

三个范式

1NF: 所有域必须是原子的。 字段应该是独立的，是不可分割的。
2NF: 每一列都与主键相关,而不是与主键部分相关(联合主键)。每张表保存一类数据。
3NF: 每列必须与主键直接相关,而不能间接相关。

数据库设计步骤

 需求分析
 概念模型抽取(e-r图)
 关系模型(表)
 物理模型设计(索引，存取等)
 测试
 上线维护

er图(实体关系)

矩形框:表示实体
菱形框:表示联系
椭圆形框:表示属性
连线

实体之间联系

一对一: 个人信息表和档案表

1. 外键添加唯一约束
2. 主键做外键
   一对多: 部门表和雇员表
   多对多: 学生表和课程表
   需要创建关系表
   关系表设置联合主键

pd工具(er 生成表)

运算符

1、算术运算符：+ - * / %
2、比较运算符：> >= < <= = != <> true 1 false 0
3、逻辑运算符：and or ! select 1>2 and 2>1;select 1>2 or 2>1;
4、位运算符：select 3&2; #2select 3|2; #3select 3^2; #1

数据完整性

1. 什么是数据完整性

数据的准确性和可靠性。

2. 完整性约束

实体完整性：

实体:记录
记录是不重复的,是可靠的准确的

主键约束:

主键约束: primary key(唯一而且不能为空)
基本语法:
创建表时创建:
create table student(
sid int primary key,
sname varchar(20),
age int );
create table student(
sid int, sname
varchar(20),
age int,
primary key(sid) );
alter table student add constraint PK_SID primary key(sid);

主键自增: 1 +1 auto_increment
alter table student modify sid int auto_increment;

主键约束说明:
有且只能有一个主键,但是可以有联合主键(多个字段整体作为主键)
唯一确定记录的字段
选择无意义字段
创建表一定要设置主键

唯一约束: 唯一

alter table student add CONSTRAINT UQ_CARD unique(card);

域完整性

域:字段
类型约束：非空约束: not null
默认值: default
alter table student modify sname varchar(20) not null;
alter table student modify sex varchar(20) default ‘男’;

引用完整性

外键约束: foreign key
alter table student add constraint FK_CID foreign key(cid) references classroom(cid);
外键说明:
1.外键字段和被参照表的主键字段名称可以不一致,但是类型必须一致
2.参照表中外键取值必须比被参照表主键取值范围小或者相等。

关联查询

1. 内连接 inner join … on…

select * from emp,dept where emp.deptno = dept.deptno; select * from
emp inner join dept on emp.deptno = dept.deptno; select * from emp
inner join dept using(deptno);

多张表记录进行匹配,只有都存在的记录出现在结果集。 内连接结果与连接顺序无关。

等值连接: 不等值连接:

2. 自然连接(自然连接是等值连接,等值连接不一定是自然连接)

select * from emp natural join dept;

3. 外连接

左外连接: left [outer] join …on…
右外连接: right [outer] join …on…

4. 自连接

select e1.ename,e2.ename boss from emp e1 left join emp e2 on e1.mgr =
e2.empno;

5. 嵌套查询(子查询)

1、单行子查询 子查询返回结果一条记录。
select dname from dept where deptno = (select deptno from emp where empno = 7788);
2、多行子查询:
in
any/all
=any: 相当于in >any:大于最小值 <any:小于最大值
all:大于最大值 <all:小于最小值

6. 联合查询

将多个结果集联合展示,结果集的字段信息必须一致。
union: 去重
union all: 不去重

事务

存储引擎(show engines;)

mysql核心就是存储引擎。dbms的底层软件组织,数据增删改查,存储,锁等都由存储引擎控制。mysql5.5后默认采用innodb。

1. 什么是事务

一组DML操作,要么同时成功，要么同时失败。

2. 事务四个特性(ACID)

原子性(Atomicity): 一组dml必须作为整体执行。
一致性(Consistency): 事务执行前后整体状态不变。
隔离性(Isolation): 并发事务不能互相产生干扰。
持久性(Durability): 事务提交后数据将持久化。

3. 并发事务产生的问题

脏读: 一个事务读到另一事务未提交的数据。
不可重复读: 一个事务在读取范围内多次读取数据不一致;另一事务对数据进行更改并提交。
幻读(虚读): 一个事务在读取范围内读取数据发现不一致;另一个事务添加或者除数据并提交。

事务隔离级别

读未提交: 不能解决任何问题。
读已提交: 解决脏读问题。
可重复读: 解决脏读和不可重复读问题。
串行化: 解决所有问题。

什么是存储程序 ？

存储和执行于服务器端程序(可重用代码块)。

存储程序优点和缺点

优点:执行效率高  ，简化开发
缺点:占用服务器端资源，迁移较繁琐

分类

1. 存储过程: 有输入和输出参数,内部执行一组sql指令。

delimiter //;
create procedure sel_ename(eno int)
begin
select ename from emp where empno = eno;
end //;
call sel_ename(7788);

参数三种模式: in: 默认,输入类型参数 out: 输出类型参数 inout: 输入输出类型参数

#根据员工编号查询员工姓名 ：
delimiter //;
create procedure sel_name(eno int,out name varchar(20))
begin
select ename into name from emp whereempno = eno;
end //;
call sel_name(7788,@v_name);
select @v_name;
#根据名称查职位
delimiter //;
create procedure sel_job(inout name_job varchar(20))
begin
select job into name_job from emp where ename= name_job;
end //;
set @name_job = ‘SCOTT’;
call sel_job(@name_job);
select @name_job;
#根据成绩分级 >=80 A >=60 B C
delimiter //;
create procedure score(score int)
begin #声明变量
declare v_level varchar(20);
if score >= 80 then set v_level=‘A’;
elseif score >= 60 then set v_level = ‘B’;
else set v_level = ‘C’;
end if;
select v_level;
end //;
call score(75);

循环：
delimiter //;
create procedure calc()
begin
declare sum int;
declare i int;
set sum=0;
set i=1;
while i<=100
do
set sum=sum+i;
set i=i+1;
end while;
select sum;
end //;
call calc();

2. 存储函数: 有返回值,内部执行一组sql

delimiter //;
create function func_ename(eno int)
returns varchar(20)
DETERMINISTIC
begin
declare v_name varchar(20);
select ename into v_name from emp where empno=eno;
return v_name;
end //;

过程和函数区别:
关键字不同
过程通过传出参数返回值;函数有return进行数据返回
过程可以独立运行；函数只能作为sql语句一部分运行

3. 触发器: 由事件(insert/update/delete)驱动,自动调用，不能传递参数。

CREATE
[DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW(行级触发器)
[trigger_order]
trigger_body
trigger_time: { BEFORE | AFTER } trigger_event: { INSERT | UPDATE |
DELETE } trigger_order: { FOLLOWS | PRECEDES } other_trigger_name

delimiter //;
create trigger tri_del_stu after delete on student for
each row begin #old new insert into stu_bak
values(old.sid,old.sname,old.cid);
end //;

视图和索引

1. 什么是视图 ？

虚拟表,本质是sql的映射。视图内部不能保存数据,数据需要依赖于基表,基表数据发生变化，视图也会发生变化。
用户可以通过视图修改基表的数据,强烈不建议对视图进行dml操作。
操作和表一致。

2. 语法：

create view view_name as 查询sql
with check option; 检查约束视图创建条件字段不允许修改
drop view view_name; 销毁视图

3. 视图优点:

   简化开发(复杂sql存放在视图中)
   安全性(隐藏某些字段)
   定制化数据
   

索引

提升查询效率所创建数据结构。 mysql索引: b+tree(和存储引擎有关 myisam/inndb) (hash,位图等)

基本语法:

create index index_name on tname(field1,field2…) 或者
alter table tname add index tname(field1,field2…)

唯一索引:

create unique index index_name on tname(field1,field2…)

删除索引:

drop index on tname;

查看表所有索引信息：

show index from tname;

索引添加场景:
索引需要单独进行维护,需要花费(索引不能添加过多)
索引适合添加在数据量较大的表
频繁进行增删改的表不适合添加
选择高基数列

数据库优化

1. 避免使用*
2. 几种索引失效场景:

1.索引列不能使用null/not null
2.索引列不要使用函数,包含
3.索引列不能计算
4.索引列不能使用!/!=/<>
5.索引列不能使用or(union代替)

3. in和exists

exists适合在主查询比较少，子查询结果较多的情况下；
in适合子查询结果较少，主查询结果较多。

not exists代替not in

备份和还原

备份: mysqldump -uroot -p 数据库 表 > d:/a.txt
还原: source *.sql / mysql -uroot -p < d:/a.sql

远程登录

1、修改mysql数据库的user表
host(绑定的ip) %
update user set host=’%’ where user=’root’;
#刷新权限
flush privileges;
2、授权远程访问
#授权远程访问
grant all privileges on . to ‘root’@’%’ identified by ‘root’ with grant option;
#刷新权限
flush privileges;