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