13518219792
一：数据库讲解
为台江等地区用户提供了全套网页设计制作服务，及台江网站建设行业解决方案。主营业务为成都网站设计、成都网站建设、台江网站设计，以传统方式定制建设网站，并提供域名空间备案等一条龙服务，秉承以专业、用心的态度为用户提供真诚的服务。我们深信只要达到每一位用户的要求，就会得到认可，从而选择与我们长期合作。这样，我们也可以走得更远！
1 5.6之前的源码安装
源码格式 ./configure 选项
make make install
2 yum安装
6 yum -y install MySQL-server mysql
service mysqld start ；chkconfig mysqld on
7 yum -y groupinstall mariadb mariadb-client
Systemctl enable/restart mariadb
3 基础
端口号 3306
进程名 mysqld
进程所有者/组 mysql/mysql
数据传输协议 tcp
主配置文件 /etc/my.cnf
数据库目录 /var/lib/mysql/
错误日志文件 /var/log/mysqld.log
进程pid号文件/var/run/mysqld/mysqld.pid
每条sql命令必须以;结尾
sql命令不区分字母大小写
\c 结束sql命令
查看已有的库 show databases;
information_schema 虚拟库
存储当前数据库服务器上已有库和表的信息/数据存储在物理内存里
mysql 授权库
存储用户登录信息和权限信息/占用物理存储空间
performance_schema
存储当前数据库服务运行的参数信息
占用物理存储空间
mysql体系结构 （8）
连接池
sql接口
解析器
优化器
缓存区 service mysql start /etc/my.cnf 8M
存储引擎
文件系统（硬盘 /var/lib/mysql）
管理工具
4 数据库连接
mysql -h数据库服务器Ip地址 -u用户名 -p密码 [库名]
mysql -uroot -p123456 -e "show databases;" 非交互
5 设置密码
1 mysqladmin -hlocalhost -uroot password "abc123"
2 mysql> set password for root@"localhost"=password("999");
修改数据库管理员密码
1 [root@www ~]# mysqladmin -hlocalhost -uroot -p password "999"
Enter password: 旧密码
2 恢复数据库管理员密码
#service mysql stop
#service mysql start --skip-grant-table
#mysql
mysql> update mysql.user
-> set
-> password=password("123")
-> where
-> host="localhost";
mysql> flush privileges;
#service mysql stop
#service mysql start
6 升级
安装下载高版本的软件包提供mysql数据库服务
service mysqld stop
rpm -e --nodeps mysql-server mysql
rm -rf /etc/my.cnf
rm -rf /var/lib/mysql/*
tar -xvf MySQL-5.6.rpm.tar
rpm -Uvh MySQL-*.rpm
rpm -qa | grep -i mysql
service mysql start ;chkconfig mysql on
cat /root/.mysql_secret
mysql -hlocalhost -uroot -pNlUDn9Wn
mysql>
mysql> set password for root@"localhost"=password("999");
mysql>quit
mysql -hlocalhost -uroot -p999
mysql> show databases;
二 数据库操作
1 部分SQL命令（库）
查看当前登录的用户信息
select user();
查看当前所在库的名字
select database();
创建库
create database 库名；
删除库
drop database 库名
切换库
use 库名；
数据库名的命名规则？
可以使用数字/字母/下划线，但不能纯数字
区分大小写，具有唯一性
不可使用指令关键字、特殊字符。
2 部分SQL命令（表）
查看里已有的表
show tables；
查看表结构
desc 表名;
查看表记录
select * from 表名;
select 字段名1，字段名2，字段名N from 表名；
select 字段名列表 from 库名.表名where 条件；
向表中添加记录
insert into stuinfo(name)values("j"),("t");
表的使用（表必须存储在库里）
建表
create table 表名(
字段名1 字段类型(宽度) 字段约束，
字段名2 字段类型(宽度) 字段约束，
字段名3 字段类型(宽度) 字段约束，
.......
);
复制表
create table 表名 SQL查询命令;
create table user2 select * from user;
create table user3 select id,name,uid from user limit 3;
create table user4 select * from user where 1 = 2;
删除表的所有记录
delete from 表名；
insert into bjb values(1,"zzz");
insert into bjb values(4,"bob");
update 表名 set 字段名=值 where 条件；
update jfb set jfb_id=8 where jfb_id=2;
delete from 表名 where 条件；
delete from jfb where jfb_id=3;
delete from user where name regexp '[0-9]';
3 修改表结构 (受表中已有记录的限制)
alter table 表名 动作；
add 添加新字段
add 字段名 类型（宽度） 约束条件；
alter table t26
add mail varchar(30) not null default "plj@tarena.com",
add qq varchar(11);
alter table t26 add stu_id int(2) first;
alter table t26 add age tinyint(2) unsigned not null default "21" after name;
drop 删除已有字段
drop 字段名；
drop 字段名,drop字段名;
4 modify 修改已有字段的类型
modify 字段名 类型（宽度） 约束条件；
alter table t26 modify sex enum("boy","girl") default "girl";
change 修改字段名
change 源字段名 新字段名 类型（宽度） 约束条件；
alter table t26 change email mail varchar(10);
修改表名
alter table 源表名 renam [to] 新表名；
三 数据库字段类型（表）
1 字符型
char （255） 定长
varchar （65532） 变长
blob
text
create table stuinfo(name char（10）);
create table stuinfo2(name varchar(3) );
2 数值型
微小整型 小整型 中整型 大整型 极大整型
有符号 无符号
create table t9(name char(5),age tinyint unsigned);
create table t10(name char(5),age int unsigned);
create table t11(name char(5),age tinyint unsigned，pay int(2));
浮点型 float 单精度 4字节
double 双精度 8字节
float(N,M)
double(N,M)
N 数值的总位数
M 小数位位数
create table t11(
name char(5),
age tinyint unsigned,
pay float(7,2)
3枚举型
set(值1，值2，值N) 选择一个或多个
enum(值1，值2，值N）只能选择一个
create table studb.t25(
name varchar(10),
sex enum("boy","girl"),
love set("game","film","music","girl"),
class set("network","system","server","shell")
);
4日期类型
年 year（YYYY）
两位自动补规则
01~69 2001~2069
70~99 1970~1999
create table t23(
name char(10),
s_year year
);
insert into t23 values("jim",01);
insert into t23 values("bob",80);
insert into t23 values("jerry",00);
日期 date（YYYYMMDD）
时间 time (HHMMSS)
日期时间
datetime
1000-01-01 00:00:00.000000
9999-12-31 23:59:59.999999
timestamp
1970-01-01 00:00:00.000000
2038-01-19 03:14:07.999999
create table t22(
time1 datetime,
time2 timestamp
);
insert into t22 values(20151211094418,20151211094418);
insert into t22(time2)values(20160214000000);
insert into t22(time1)values(20170214200000);
与日期相关函数
now()
year()
month()
day()
time()
select time(now());
select day(now());
select now();
四： 字段约束条件的设置(限制如何给字段赋值)
1 是否允许给字段赋空null/NULL值 默认允许为NULL值
not null
字段默认值 向表中添加新记录时，不给字段赋值时，使用默认值给字段赋值，若没有
设置过默认值的值， 默认值的值是null值
2 default 值
create table t26(
name varchar(10) not null,
sex enum("boy","girl") not null default "boy",
love set("game","film","music","girl") default "game,film",
class set("network","system","server","shell") default "shell"
);
insert into t26(name)values("jerry");
insert into t26 values("lucy","girl","game","network");
insert into t26 values(null,null,null,null)
3 非零zerofill
五：索引
1 索引好处
索引优点 ： 加快查询的速度
索引缺点 ： 占用物理存储空间
减慢 update insert delete
2 索引查看
show index from 表名;
3 index索引
一个表中可以有多个INDEX字段
对应的字段值允许有重复
可以赋null 值
把经常做查询条件的字段设置为INDEX字段
INDEX字段的KEY标志是MUL
方式一
create index name on tt26(name);
create index sex on tt26(sex);
方式二
create table t27(
name varchar(10) not null,
sex enum("boy","girl") not null default "boy",
love set("game","film","music","girl") default "game,film",
class set("network","system","server","shell") default "shell",
index(name),
index(sex)
);
索引名 表名
drop index name on tt26;
4 unique索引
一个表中可以有多个UNIQUE字段
对应的字段值不允许有重复,但可以赋NULL值；
UNIQUE字段的值允许为NULL,将其修改为不允许为NULL，则此字段限制与主键相同
UNIQUE字段的KEY标志是UNI
方式一
create unique index stu_id on tt26(stu_id);
方式二
create table t28(
stu_id char(4) ,
iphone char(11),
name varchar(10),
unique(stu_id),
unique(iphone)
);
drop index stu_id on tt26;
5 主键（primary key）
一个表中只能有一个PRIMARY字段
对应的字段值不允许重复且不允许为null
主键字段的KEY标志是PRI
如果有多个字段都作为PRIMARY KEY，称为复合主键，必须在建表时一起创建.
通常与 AUTO_INCREMENT 连用
让字段的值自动增长 ++
数值类型
字段必须是primay key 字段
把表中能够唯一定位一条记录的字段设置为主键字段
create table t211(
name varchar(10) primary key ,
age tinyint(2)
);
alter table t211 drop primary key;
alter table t211 add primary key(age);
create table t222(
id int(2) primary key auto_increment,
name varchar(10) not null,
age tinyint(2) unsigned,
index(name)
);
insert into t222 (name,age) values("bob",23);
alter table t222 modify id int(2) not null;
--------------------------------------------------------------------
alter table t222
add
id int(2) primary key auto_increment first ;
create table test3(
id1 int(3) zerofill,
level int zerofill
);
数值类型的宽度是 显示宽度 ，不能够控制给字段赋值的大小。字段值的大小由字段类型决定。
insert into test3 values(9,9);
insert into test3 values(27,27);
------------------------------------------------------------------
create table sertab(
cip varchar(15) ,
sername varchar(20),
serport smallint(2) ,
status enum("deny","allow") not null default "deny",
primary key (cip,serport)
);
6 外键
1 表的存储引擎必须是innodb
2 字段的类型要一致
3 被参考字段必须是一个key(主键)
create table 表名(字段名列表)engine=存储引擎名 DEFAULT CHARSET=字符集；
创建外键
foreign key(字段名) references 表名（字段名） on delete cascade on update cascade
缴费表
create table jfb(
jfb_id tinyint(2) primary key auto_increment,
class char(7) not null default "nsd1509",
name varchar(15),
xf float(7,2) default "17800"
)engine=innodb;
insert into jfb(name)values("zzz");
insert into jfb(name)values("mda");
insert into jfb(name)values("jack");
班级表
create table bjb(
bjb_id tinyint(2),
name varchar(15),
foreign key(bjb_id) references jfb(jfb_id) on delete cascade on update cascade
)engine=innodb;
删除表字段的外键属性
mysql> alter table bjb drop foreign key bjb_ibfk_1;
六：存储引擎
1查看当前数据库服务器支持哪些存储引擎
show engines;
2 存储引擎MyISAM与InnoDB
MyISAM
不支持事务、事务回滚 、 外键
支持表级锁
独享表空间 bt.frm 表结构
bt.MYD 表记录
bt.MYI 表索引
表级锁
InnoDB
Supports transactions, row-level locking, and foreign keys
事务 ？ 一次SQL操作从开始到结束的过程
事务回滚 ： 当操作没有正确完成时，把数据恢复到操作之前的 状态。
事务日志文件
ib_logfile0
ib_logfile1
ibdata1
共享表空间 at.frm 表结构
at.ibd 表记录+表索引
行级锁
读锁 （ 共享锁）
写锁 （互斥锁 排它锁
3 设置事务状态
mysql> show variables like "%commit%";
mysql> set autocommit=off； 事务自动提交关闭
start transaction; 开启事务
4 存储引擎选择
编辑操作多的表 使用行级锁 (innodb)
查询操作多的表 使用表级锁 (myisam)
5 存储引擎设置
修改表的存储引擎
alter table 表名 engine=存储引擎；
修改数据库服务默认使用的存储引擎
service mysql stop
vim /etc/my.cnf
[mysqld]
default-storage-engine=myisam
:wq
service mysql start
七：文件与数据库的转换（导入与导出）
1 文件到数据库（导入），，，先有表结构
mysql> LOAD DATA INFILE "系统文件名"
INTO TABLE 表名
FIELDS TERMINATED BY "分隔符"
LINES TERMINATED BY "\n";
例子
把当前系统用户信息保存到数据库 studb库的user表里。
/etc/passwd studb.user
create table studb.user(
name varchar(25),
password char(1),
uid int(2),
gid int(2),
comments varchar(50),
homedir varchar(50),
shell varchar(20),
index(name)
);
load data infile "/etc/passwd" into table user fields terminated by ":" lines terminated by "\n";
alter table user add id int(2) primary key auto_increment first;
2 数据库到文件（导出）
l库下user表的所有记录保存到系统/mydata目录下user.txt文件里。
mkdir /mydata
chown mysql /mydata
select * from mysql.user into outfile "/mydata/user.txt";
SELECT查询命令
INTO OUTFILE “文件名” fields terminated by "#" lines terminated by "!!!"；
例子
select name,uid,gid from user into outfile "user4.txt" fields terminated by "#" lines terminated by "!!!" ;
导出的内容由SQL查询语句决定
若不指定路径，默认会放在执行导出命令时所在库对应的数据库目录下。
应确保mysql用户对目标文件夹有写权限。
八： 查询条件
查询表记录时条件的表示方式
1、字符比较
相等 =
不相等 !=
select id,name from user where name!="zzz";
select name from user where shell="/sbin/nologin";
2、数值比较
相等 不相等 大于 大于等于 小于 小于等于
= != > >= < <=
select uid from user where uid=500;
3 范围内查找
字段名 in (值列表) 在....里
字段名 not in (值列表) 不在....里
字段名 between 值1 and 值2 在...之间
select name,uid from user where name in ("sync","plj","bob");
select name,uid from user where uid in (500,2000,65535);
select name,uid from user where uid not in (500,2000,65535);
select * from user where uid between 500 and 510;
4 匹配空
is null
select id,name from user where name is null;
5 匹配非空
is not null
select id,name from user where name is not null;
6 逻辑比较 （多个查询条件时）
逻辑与 and 多个条件同时成立
逻辑或 or 多个条件有一个条件成立就可以
逻辑非 ! 取反
select * from user where name in ("root","bin");
select * from user where name="root" or uid=10 or uid=20;
select name,uid from user where name in ("root","bin") or uid<=5;
select name,uid from user where uid=2000 and name="zzz";
7 DISTINCT 不显示字段的重复的值
select distinct shell from user;
8 查询时可以做四则运算 + - * / %
字段的类型必须是数值类型
alter table user add age tinyint(2) unsigned not null default 23 after name;
alter table user add services int(2) not null default "60",add mysql int(2) not null default 60;
select name,2015-age as s_year from user where name="root"
mysql> select name,mysql,services,mysql+services as zcj from user where name="root";
select name,mysql,services,mysql+services as zcj , (mysql+services)/2 as pjcj from user where name="root";
9 常用的统计函数avg()：字段的平均值
sum()：字段求和
min()：字段的最小值
max()：字段的最大值
count()：字段记录的个数
select avg(mysql) from user;
select count(id) from user;
select count(id),count(name) from user;
10 like 模糊查询
where 字段名 like '表达式';
_ 匹配任意一个字符
% 匹配任意零个或多个字符
insert into user(name)values("a");
insert into user(name)values("");
select id,name from user where name="";
select name from user where name like '___';
select name from user where name like '%';
select name from user where name like 'a%';
having 条件 在查询结果里过数据
select * from user where uid<=10 having name="sync";
11 使用正则表达式做查询条件
where 字段名 regexp '正则表达式'；
^ $ . [ ]
把名字包含数字用户名全显示出来
insert into user(name)values("jim3");
insert into user(name)values("ji7m");
insert into user(name)values("j0im");
insert into user(name)values("9jim");
insert into user(name)values("jim");
select name from user where name regexp '[0-9]';
select name from user where name like '%jim%';
select name from user where name regexp '^a.*m$';
select name from user where name regexp '^a' and name regexp 'm$';
12 分组查询
group by 字段名;
select sex from user group by sex;
select shell from user where uid>=500 group by shell;
13 给查询结果排序 asc/ desc（倒序）
order by 字段名 排序的方式
select name,uid from user order by uid;
select name,uid from user where uid>=500 order by uid desc;
14 limit 限制显示查询记录的条目数
limit N,M
N 从第几条记录开始显示
第一条记录的编号是零
M 共显示几条记录
select * from user limit 0,1 ;
select * from user order by uid desc limit 1；
select * from user order by uid desc limit 2,5；
15 嵌套查询
把内层查询结果作为外层查询的查询条件
select 字段名列表 from表名where 条件 (sql查询)；
select name,system from user where system > (select avg(system) from user);
select name from user where name = (select name from user2 where uid =3);
select name from user where name in (select name from user2 where uid >=3);
16 多表查询
select 字段名列表from 表1，表2，表N where条件;
*
表1.字段名
表2.字段名
select * from atab,btab; 迪卡尔集
select atab.name,btab.shell from atab,btab where 条件;
select atab.name from atab,btab where atab.name = btab.name;
18 左连接查询（以左表纪录为主 显示）
select 字段名列表from 表a LEFT JOIN 表b ON 条件表达式；
右连接查询（以右表纪录为主 显示）select 字段名列表from 表a RIGHT JOIN 表b ON 条件表达式；
mysql> select atab.name,btab.shell from atab right join btab on atab.uid =btab.uid;
select * from atab left join btab on atab.uid!=btab.uid;
select name from atab where uid not in (select uid from btab);
九: 数据库授权与撤销
1 查询授权
show grants; 连接数据库服务器的用户查看自己的权限信息
查看数据库服务器上有哪些授权用户？
select user,host from mysq.user
2 权限列表
命令 权限
all 所有权限
usage 无权限
SELECT 查询表记录
INSERT 插入表记录
UPDATE 更新表记录
DELETE 删除表记录
CREATE 创建库、表
DROP 删除库、表
RELOAD 有重新载入授权 必须拥有reload权限，才可以执行flush [tables | logs | privileges]
SHUTDOWN 允许关闭mysql服务 使用mysqladmin shutdown来关闭mysql
PROCESS 允许查看用户登录数据库服务器的进程 （ show processlist; ）
FILE 导入、导出数据
REFERENCES 创建外键
INDEX 创建索引
ALTER 修改表结构
SHOW DATABASES 查看库
SUPER 关闭属于任何用户的线程
CREATE TEMPORARY TABLES 允许在create table语句中使用 TEMPORARY关键字
LOCK TABLES 允许使用 LOCK TABLES 语句
EXECUTE 执行存在的Functions,Procedures
REPLICATION SLAVE 从主服务器读取二进制日志
REPLICATION CLIENT 允许在主/从数据库服务器上使用show status命令
CREATE VIEW 创建视图
SHOW VIEW 查看视图
CREATE ROUTINE 创建存储过程
ALTER ROUTINE 修改存储过程
CREATE USER 创建用户
EVENT 有操作事件的权限
TRIGGER, 有操作触发器的权限
CREATE TABLESPACE 有创建表空间的权限
3 客户端地址的表示方式？
172.40.7.213 固定Ip地址
192.168.1.% 网段
% 所有地址
pc10.tarena.com 主机名
%.tarena.com 区域
4 授权的信息存储与库和表
user 存储授权用户权限信息 *.*
db 库的权限信息 库名.*
tables_priv 表的权限信息 库名.表名
columns_priv 字段的权限信息 update(name,sex)
5用户授权
用户有授权权限要具备2个条件
1 有授权权限 with grant option
2 对授权库要有写入权限
3 给其他用户授权时，权限不能大过自己本身的权限
grant 权限列表 on 数据库名 to 用户名@"客户端地址" identified by "密码"；
grant 权限列表 on 数据库名 to 用户名@"客户端地址" identified by "密码" with grant option;
with grant option 设置授权用户有授权权限
管理员可重设授权用户的密码
SET PASSWORD
FOR 用户名@'客户端地址'=PASSWORD('新密码');
6 权限撤销
* 有过授权才可以撤销权限。
* revoke 撤销的是权限
revoke 权限列表 on数据库名 from用户名@"客户端地址"
grant all on *.* to root@"172.40.7.42" identified by "123456" with grant option;
revoke grant option on *.* from root@"172.40.7.42";
revoke delete on *.* from root@"172.40.7.42";
revoke all on studb.* from root@"172.40.7.42";
