MySQL安装与讲解

一：数据库讲解

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";