MySQL安装与讲解

cqlinx

　　一：数据库讲解
　　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>>　　六：存储引擎
　　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、数值比较
　　相等  不相等  大于   大于等于    小于    小于等于
　　=       !=      >          >=          <          =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";