数据库

数据库
- MySQL数据库

数据库

本质: 一套C/S socket 软件

分类

关系型数据库:

数据与数据建立关联关系

SQLServe,仅支持Windows

MySQL, 免费开源性能不差先后被sun 和Oracle 收购目前属于Oracle公司

mariaDB mysql创始人自己又自己开发的一款数据库

Oracle, 目前较好的数据库收费高

DB2, IBM 收费

非关系型数据库: MongoDB,Redis,memcache

MySQL数据库

安装

windows版本

1、下载：MySQL Community Server 5.7.16
http://dev.mysql.com/downloads/mysql/

2、解压
如果想要让MySQL安装在指定目录，那么就将解压后的文件夹移动到指定目录，如：C:mysql-5.7.16-winx64

3、添加环境变量
【右键计算机】--》【属性】--》【高级系统设置】--》【高级】--》【环境变量】--》【在第二个内容框中找到变量名为Path 的一行，双击】 --> 【将MySQL的bin目录路径追加到变值值中，用；分割】

4、初始化
mysqld --initialize-insecure

5、启动MySQL服务
mysqld # 启动MySQL服务

6、启动MySQL客户端并连接MySQL服务
mysql -u root -p # 连接MySQL服务器

上一步解决了一些问题，但不够彻底，因为在执行【mysqd】启动MySQL服务器时，当前终端会被hang住，那么做一下设置即可解决此问题：

注意：--install前，必须用mysql启动命令的绝对路径

制作MySQL的Windows服务，在终端执行此命令：

"c:mysql-5.7.16-winx64inmysqld" --install

移除MySQL的Windows服务，在终端执行此命令：

"c:mysql-5.7.16-winx64inmysqld" --remove

注册成服务之后，以后再启动和关闭MySQL服务时，仅需执行如下命令：

启动MySQL服务

net start mysql

关闭MySQL服务

net stop mysql

其他操作

设置密码
mysqladmin.exe 用于设置密码
mysqladmin -uroot -p旧密码 password 新密码

破解密码
可以在启动服务器时告诉他让他不要读取密码文件
1.mysqld --skip-grant-tables
2.无密码登录服务器
3.使用update 来更新你的密码在msyql.user表中
在客户端 update mysql.user set password = password("321") where user = "root" and host="localhost";
4.重启服务验证新密码

安装mysql得到两个程序
1.服务器程序
存取数据时服务器的工作
2.客户端程序
命令服务器来完成存取工作
客户端将命令发送给服务器服务器解析执行命令返回客户端要的数据

服务器为了保证数据安全需要进行用户验证
默认有一个root 账户默认没有密码不安全
需要修改密码:
在服务器的终端中执行
mysqladmin.exe -u要改的用户名 -p旧密码 password 新密码
在服务器上执行命令时如果看到Install/Remove of the Service Denied! 说明权限不够
得用管理员身份运行
如果忘记密码要破解也需要在服务器终端启动mysql服务并且需要添加跳过授权表的参数

mysql -uroot -padmin 用mysql自带的客户端登录服务器
-u 用户名 -p 密码 -h 服务器的ip -P端口号在本机上执行客户端不需要指的 -h -P

基础SQL语句

库相关

创建
create database 数据库名 charset 编码名称
修改
alter database 数据库名 charset gbk
查
show databases; 查看所有数据
show create database 数据库名查看建库语句
删除
drop database 数据库名

表相关

创建
        create table t1(id int,name char);
    修改
        改结构
        alter table 表名  add | drop | modify | change
        add 后面跟 字段名 数据类型
        drop 后面跟 字段名
        modify 后面跟 字段名  新类型
        change 后面跟 旧字段名 新的字段名 新类型
        该属性
            编码
                alter table t1 charset gbk;
            表名

                rename table 旧表名 to 新表名
    查
        show tables;
        show create table 表名 查看建表语句
        desc 表名  查看表结构(字段)
    删除
        drop table 表名
        truncate table 表名,重建表(清空数据)

记录相关

添加
insert into 表名 values(值1...),(值1....)....
插入时注意值得顺序和类型必须与表结构一致
修改
update 表名 set 字段名=新的值 where 条件
可以同时修改多行用逗号来隔开字段
没有条件就全改
查
select *|字段名 from 表名 *表示所有字段
删除
delete from 表名 where条件
没有条件就全删

创建表的完整语法
create table 表名(
字段名称数据类型[(长度) 约束条件],
字段名称数据类型[(长度) 约束条件]
)

数据存储引擎
什么是引擎 ? 一个功能的核心部分
引擎可以被分类
需求场景的不同催生了不同的引擎类别

mysql
核心功能是存储数据涉及到存储数据的代码就称之为存储引擎
根据不同的需求也有着不同的引擎分类

不同的引擎测试
创建表时在最后指定引擎名称 engine = xxx
create table t1(id int)engine=innodb
create table t1(id int not null)engine=CVS
create table t1(id int)engine=memory
create table t1(id int)engine=blackhole

总结: innodb是默认的引擎因为它是永久存储 并且 支持事务,行锁,外键

数据类型

存在不同数据类型好处

1.描述事物更加准确
2.描述起来更方便
3.节省内存空间

mysql支持的数据类型:

整型

tinyint smallint mediumint int bigint
字节数: 1 2 3 4 8
默认情况下整型是有符号的需要用一个二进制位存储符号
给整型加上约束 unsigned来表示无符号
如果数据超出范围就尽可能保存最大的例如在无符号下保存256 其实存的255
如果有符号例如 tinyint 保存-1280 其实存的是 -128 是最小值

浮点型

分类: float double decimal
字节数: 4 8 不确定(手动指定)

给浮点设置宽度限制
float(m,d)
double(m,d)
decimal(m,d)

长度说明
m表示这个浮点数整体的长度
d表示小数部分的长度
例如: float(5,3) 最大值: 99.999

异同
相同点: 小数部分最大长度都是30
float和double的最大长度为255
不同点: decimal的整体最大长度65
精度不同
double 比 float 精度高
decimal 是准确的不会丢失精度

字符型

分类
char 定长字符
varchar 变长字符

char类型的长度是固定无论你存储的数据有多长占用的容量都一样
char(3) 存储的数据为 "a" 在硬盘保存的数据还是占3字符长度实际保存的是"a "
varchar 长度是可变的存储的数据有多长就占用多长
varchar(3) 存储的数据为 "a" 在硬盘保存的数据还是占1字符长度实际保存的是"a"
yxx exx lxx zxx cx wxx char(3)
(1bytes+yx)(1bytes+exx)(1bytes+lx)(1bytes+zxx) varchar(3)
如果是可变长度则有问题不知道数据从哪里开始到哪里结束所以需要有一个位置保存数据的长度
vharchar 能支持的最大长度是65535 用于保存数据长度的数据最长两个bytes

如果是char类型如果你的数据不足指定长度就在后面用空格补全

验证:
使用一个 char_length的函数可以查看字符的长度
create table t18(a char(4),b varchar(4));
insert into t18 value("x","x");
select char_length(a),char_length(b) from t18;
两个字段的长度都为1

结论:
这是因为 mysql在存储时自动加上的空格对使用者而言是没有意义的所以mysql自动帮你处理掉空格了
我们可以设置sql模式来让它现出原形
set global sql_mode = "PAD_CHAR_TO_FULL_LENGTH,STRICT_TRANS_TABLES"
设置完成后重启msyql 再次查询长度

注意:

当你在执行这样的查询语句时 mysql会自动将参数末尾的空格去除
select *from t19 where name = "yh"
当你在使用模糊搜索时要注意定长字符后面可能会有空格所以最好在后面加上百分号 %
select *from t19 where name like "yh";
% 任意个数的任意字符
_ 1个任意字符

如何选择
char
存取效率高
浪费存储空间
varchar
存取效率低于char
节省存储空间
使用起来感受不到区别通常用的是char

char和varchar 长度都比较小最大就是65535

大文本类型:

TEXT系列 TINYTEXT TEXT MEDIUMTEXT LONGTEXT 文本是带有编码
BLOB 系列 TINYBLOB BLOB MEDIUMBLOB LONGBLOB 也是字符数据但是不带编码

二进制类型: 用于存储多媒体数据比如视频但是我们一般不会将多媒体数据存到数据而是存储文件路径地址
BINARY系列 BINARY VARBINARY 存储二进制数据

时间类型

分类
time 时分秒 HH:MM:SS
year 年份
date 日期年月日
datetime 日期加时间年月日时分秒年份最大是9999
timestamp 时间戳从1970-1-1开始算年份最大是2037

共同点: 时间的存取通过字符串类型
都可以使用now()函数来插入当前时间

datetime 和时间戳都能够表示日期和时间
不同之处是: 年份最大范围不同
时间戳可以为空代表当前时间
时间戳在你更新记录时会自动更新为当前时间

枚举

用于描述一个已知范围的数据例如性别: 只有男女或其他
enum("man","woman","other")

总结: 枚举中只能是字符串类型
添加的数据只能是已经出现在枚举中的值
你的值只能是其中的一个
你也可以使用枚举值的序号来插入值从1开始
多选1

集合
用于描述一堆数据比如你的兴趣爱好
set("watch movie","listen music","play game")

总结:集合中的数据只能是字符串
添加的数据只能是已经出现在集合中的值
你的值可以是其中的任意几个
你也可以使用枚举值的序号来插入值从1开始但是只能给一个序号
多选多

约束条件

除了数据类型以外额外添加的约束
为了保证数据的合法性完整性

not null

非空约束数据不能为空

 create table student (id ind,
                       name char(10) not null);

default

默认值约束可以指定字段的默认值

create table user (id ind,name char(10) not null,
                 sex char(1) default "woman");

unique

唯一性约束该字段的值不能重复

是一种索引可以加速查找

单列唯一:

create table t5(idcard char(18) unique);

联合唯一:

create table t6(idcard char(18),
                phonenumber char(11),
                unique(idcard,phonenumber));

primary key

主键约束

是一种索引必然存在硬盘上的某个文件中是物理层面

等价于not null 加unique约束

create table stu (stuid int primary key,name char(3));

多列联合主键

 create table t8(idcard char(18),
                 phonenumber char(11),
                 primary key(idcard,phonenumber));

有主键和没有主键的区别？
1.无法区分两个相同记录
2.有主键则意味有这索引效率更高
3.可以建立关联关系

auto_increment
自动增长
通常搭配主键字段使用可以自动为你的数据分配逐渐

create table t9(id int primary key auto_increment,
                name char(3));

修改自动增长的起始位置

alter table t9 auto_increment = 7;

注意: 自动增长只能用于整型

	如果主键是自动增长  你可以跳过这个字段 也可以为它插入null  都可以

foreign key

表与表之间建立联系

表与表之间的关系

一对一

多对一:

将'多' 一方设为从表加上外键

多对多:

创建第三张关联表

语法

 create table dept(id int primary key auto_increment,
                   name char(10),
                   manager char(10));

create table emp(id int primary key auto_increment,
                 name char(10),dept_id int,
                 foreign key(dept_id) references dept(id));

添加外键约束时:
被关联的表需要先被创建
部门数据(主表)应该先插入员工数据(从表)后插入
在删除部门数据前(主表)前要保证该部门的员工数据都删除了
在更新部门编号前要先保证没有员工关联到这个部门

注:外键加上以后主表中的数据删除和更新时都受到限制
解决的方案是为外键添加 级联操作

级联操作
指的是就是同步更新和删除
语法:在创建外键时在后面添加 on update cascade 同步更新
on delete cascade 同步删除

create table class(id int primary key auto_increment,
                   name char(10));
create table student(
    id int primary key auto_increment,
    name char(10),
    c_id int,
    foreign key(c_id) references class(id)
    on update cascade
    on delete cascade
);

查询语句

完整查询语句

select [distinct] {* | 字段名 | 聚合函数 | 表达式} from 表名
[where 条件
group by 字段名
having 条件
order by 字段名
limit 显示的条数]

关键字执行顺序

def select()
from() 打开文件
where() 读取每一行并判断是否满足条件
group() 对数据进行分组
having() 再分组之后进行过滤 having不单独出现仅用于分组之后进行过滤
distinct() 去重
order() 用于对筛选后的数据进行排序
limit() 限制显示的条数
最后根据select后制定的字段来显示数据

一、where过滤
where字句中可以使用：

比较运算符：> < >= <= <> != #不等于用！= 不用 <>
```
select id,name from db39.emp where id >= 3 and id <= 6
```

between 80 and 100

select *  from db39.emp where id between 3 and 6;  # >=3 and <=6

in(80,90,100) 值是80或90或100

select * from emp where salary in (20000,18000,17000); # select * from emp where salary = 20000 or salary = 18000 or salary = 17000;

like 'egon%', pattern可以是%或_, %表示任意多字符, _表示一个字符

select name,salary from db39.emp where name like '%i%'  #要求：查询员工姓名中包含i字母的员工姓名与其薪资
select name,salary from db39.emp where name like '____';  #要求：查询员工姓名是由四个字符组成的的员工姓名与其薪资
select name,salary from db39.emp where char_length(name) = 4;   #结果与上一条一致

逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not

select *  from db39.emp where id not between 3 and 6;
select * from emp where salary not in (20000,18000,17000);

针对NULL必须用is，不能用=

select name,post from db39.emp where post_comment is NULL;

二、group by分组

如果不设置成only_full_group_by模式，分完组后用*默认取出的是组内的第一个人的数据。但分完组后单独取组内的某个元素是没有意义的，所以，分组前，一般会对模式做如下处理
#设置sql_mode为only_full_group_by，意味着以后但凡分组，只能取到分组的依据

```Python

set global sql_mode="strict_trans_tables,only_full_group_by";
```

聚合函数: max(), min(), avg(), sum(),count()

group_concat:（分组之后用）把想要用的信息取出；字符串拼接操作

as :为字段或表取别名

#聚合函数 group function（一般与分组连用）
select post,max(salary) from emp group by post; #取不出组内的元素name, age..，只能取组名（分组依据）或用聚合函数

select post,min(salary) from emp group by post;

select post,avg(salary) from emp group by post;

select post,sum(salary) from emp group by post;

select post,count(id) from emp group by post;

#group_concat（分组之后用）：把想要用的信息取出；字符串拼接操作
select post,group_concat(name) from emp group by post;
select post,group_concat(name,"_SB") from emp group by post;
select post,group_concat(name,": ",salary) from emp group by post;
select post,group_concat(salary) from emp group by post;

# 补充concat（不分组时用）：字符串拼接操作
select concat("NAME: ",name) as 姓名,concat("SAL: ",salary) as 薪资 from emp;

# 补充as语法：为字段或表取别名
select name as 姓名,salary as 薪资 from emp;  # as可省略
mysql> select emp.id,emp.name from emp as t1; # 报错
mysql> select t1.id,t1.name from emp as t1;  # 同 mysql> select id,name from emp as t1;

三、having过滤 (一定要用组名（分组依据）或聚合函数）
having的语法格式与where一模一样，只不过having是在分组之后进行的进一步过滤
即where不能用聚合函数，而having是可以用聚合函数，这也是他们俩最大的区别

#统计各部门年龄在30岁以上的员工平均工资，并且保留平均工资大于10000的部门
select post,avg(salary) from emp where age >= 30 group by post having avg(salary) > 10000;

#强调：having必须在group by后面使用 （不认默认分组）
select * from emp having avg(salary) > 10000; #报错

四、distinct去重（在having之后执行，和post，name等属于同一执行级别）
select distinct post,avg(salary) from emp where age >= 30 group by post having avg(salary) > 10000;

五、order by 排序（默认升序）

select * from emp order by salary asc; #默认升序排
select * from emp order by salary desc; #降序排
select * from emp order by age desc,salary asc; #先按照age降序排，再按照薪资升序排
# 统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序
select post,avg(salary) from emp where age > 10 group by post having avg(salary) > 1000 order by avg(salary);

六、limit 限制显示条数；分页

select * from emp limit 3;
select * from emp order by salary desc limit 1;  #显示薪资最高人的信息
select * from emp limit 0,5; #分页， 从0开始，取5条（1-5）
select * from emp limit 5,5; #分页， 从5开始，取5条（6-10）

七、正则表达式

select * from emp where name regexp '^jin.*(n|g)$';  #调正则；正则表达式通用

多表连接查询

关键字 on

在多表查询时使用后边条件 = 号两边全是主键

一、笛卡尔积
from emp,dep,dep2,...

二、内连接：把两张表有对应关系的记录连接成一张虚拟表

select * from emp inner join dep on emp.dep_id = dep.id;

#应用：
    select * from emp,dep where emp.dep_id = dep.id and dep.name = "技术"; # 不推荐；不要用where做连表的活
    select * from emp inner join dep on emp.dep_id = dep.id where dep.name = "技术";   #逻辑与上一条一致

三、左连接：在内连接的基础上，保留左边没有对应关系的记录

select * from emp left join dep on emp.dep_id = dep.id;

四、右连接：在内连接的基础上，保留右边没有对应关系的记录

select * from emp right join dep on emp.dep_id = dep.id;

五、全连接：在内连接的基础上，保留左、右边没有对应关系的记录

select * from emp left join dep on emp.dep_id = dep.id
union   #去重
select * from emp right join dep on emp.dep_id = dep.id;

六、多表连接可以是单表不断地与虚拟表连接

#查找各部门最高工资
select t1.* from emp as t1 
inner join
(select post,max(salary) as ms from emp group by post) as t2  #把虚拟表提成t2
on t1.post = t2.post
where t1.salary = t2.ms
;

select t1.* from emp as t1 
inner join
(select post,max(salary) as ms from emp group by post) as t2
on t1.salary = t2.ms
;

子查询（一个问题一个问题解决）
把一个查询语句用括号括起来，当做另外一条查询语句的条件去用，称为子查询

select name from emp where dep_id = (select id from dep where name="技术");  #子查询
select emp.name from emp inner join dep on emp.dep_id = dep.id where 	dep.name="技术";  #链表

#查询平均年龄在25岁以上的部门名
select name from dep where id in (select dep_id from emp group by dep_id having avg(age) > 25);   #子查询
select dep.name from emp inner join dep on emp.dep_id = dep.id group by dep.name having avg(age) > 25;  #链表

#查看不足2人的部门名(子查询得到的是有人的部门id)
select * from emp where exists (select id from dep where id > 3); #exists用法，当（）返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询（empty set）

#查询每个部门最新入职的那位员工
select t1.id,t1.name,t1.post,t1.hire_date,t2.post,t2.max_date from emp 	as t1 inner join (select post,max(hire_date) as max_date from emp group by post) as t2 on t1.post = t2.post where t1.hire_date = t2.max_date;

权限管理

mysql 用户管理
MYSQL 是一个tcp 服务器用于操作服务器上的文件数据,
接收用户端发送的指令, 接收指令时需要考虑安全问题

在mysql自带的mysql数据库中有4个表用于用户管理的
分别是: 优先级从高到低
user -> db -> tables_priv -> columns_priv

#1.创建用户的语句
    create user  用户名@"主机地址" identified by "密码";

    create user  scote@"127.0.0.1" identified by "123";
    此处的主机地址 不是服务器地址 而是表示 这个账户可以在那台电脑上登录

#2. 授权的语句  
    语法: grant [权限的名称 select insert.... | all ] on 数据库.表名  to 用户名@主机地址;
    # 授予 scote 这个用户所有权限 在所有数据库所有表中
    grant all on *.* to scote@"localhost"; 可以访问 所有库和表
    grant all on day41.* to scote@"localhost";  可以访问day41库的所有表
    grant all on day41.stu to scote@"localhost"; 可以访问day41库的stu表
    grant select(id,name),insert(id,name) on day41.stu to scote@"localhost";
# 仅能查看和添加 day41库的stu表中的 id和name字段
 
# 3.grant [权限的名称 select insert.... | all ] on 数据库.表名  to 用户名@主机地址 with grant option;
    with grant option 这个用户可以将他有的权限授予别的账户
    特点: 如果授权时  用户不存在 直接自动创建用户
 grant all on mydb1.* to testDBA@"%"  identified by "123";
# 4.删除权限
   revoke 权限的名称 on 数据库.表名  from 用户名@"主机名" ;
   revoke all on *.* from scote@"localhost";

   update mysql.user set  Grant_priv = "N" where user ="scote" and host = "localhost";

# *.刷新权限表
    flush privileges;

# 5.删除用户
    drop user 用户名@"主机地址";