mysql安装im,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  terminatedby "#"  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.*  fromroot@"172.40.7.42";