MySQL数据库从入门到实战（三）

nics

　　第一部分：补充
　　（1）前章补充：
　　MySQL在启动时：
　　1、启动后台守护进程（mysqld），并生成工作线程(io 、w、 r)
　　2、预分配内存结构供MySQL处理数据使用
　　实例是什么？
　　MySQL的后台进程+线程+预分配的内存结构。
　　mysql数据库管理系统：实例+数据组成
　　（2）mysql启动方式：
　　mysql.server---->mysqld_safe---->mysqld
　　通过网络连接串
　　mysql -uroot -poldboy123 -h 10.0.0.52
　　通过套接字文件
　　mysql -uroot -poldboy123 -S /application/mysql/tmp/mysql.sock
　　（3）mysql关闭方式：
　　mysqladmin -uroot -poldboy123 shutdown
　　/etc/init.d/mysqld stop
　　（4）mysql连接管理mysql命令
　　-u   用户名
　　-p   密码
　　-h   mysql，IP地址
　　-S   套接字
　　-P   端口
　　-e   非交互式访问数据库并执行命令
　　（5）错误日志（很重要）
　　默认存放在数据目录下的"hostname.err"，结合日志分析工具使用
　　常见的报错整理：perror命令查看
　　http://oldboy.blog.51cto.com/2561410/1728380
　　（6）mysqld 启动配置选择顺序：
　　1、预编译（Cmake）
　　2、启动时在命令行直接指定（这种优先级是最高的）
　　./mysqld_safe --log-error=/var/log/mysql.log --socket=/tmp/mysql.sock --port=3310 &
　　（7）/etc/my.cnf可以影响什么？
　　mysqld启动：mysqld，mysqld_safe，
　　[server]模块
　　影响客户端链接    mysql，mysqladmin，mysqldump
　　[client]模块
　　(2)配置案例
　　[server]
　　basedir=/application/mysql
　　datadir=/application/mysql/data
　　socket=/tmp/mysql.sock
　　log-error=/var/log/mysql.log
　　log_bin=/data/mysql/mysql-bin
　　binlog_format=row
　　skip_name_resolve=1
　　server_id=3306
　　[client]
　　socket=/tmp/mysql.sock
　　——————————————————————————————————
　　第二部分：mysql实际操作
　　（1）MySQL用户管理
　　————————————————————————————————————
　　用户的作用：
　　（1）登录
　　（2）管理数据库对象（库、表等对象）
　　用户的定义：
　　（1）用户名（2）主机域：谁能来访问Mysql
　　'10.0.0.200'   ---->只允许200地址访问我mysql
　　'10.0.0.%'     -----》允许这个网段的所有机器都能访问我
　　'10.0.0.5%'    ----》 允许50-59
　　'%'         ----> 允许所有人
　　创建用户：

　　create user oldboy@'10.0.0.%'>　　select user,host,password from mysql.user;             ----查看用户表
　　drop user oldboy@'10.0.0.%';                                  ----删除用户
　　————————————————————————————————————
　　（2）权限管理
　　————————————————————————————————————
　　1.权限的作用
　　用户                           对象                     做什么
　　oldboy@'10.0.0.%'    库、表                  增、删、改、查
　　2.权限设定

　　grant 权限 on   对象（范围）  to 用户  >
　　grant all on . to oldboy@'10.0.0.%'>　　3.对象说明：
　　对象：在grant语句中是个范围定义
　　oldboy.t1    单表级别
　　oldboy.     单库级别
　　.*          全库级别
　　4.权限说明：
　　思考一下如果在test.，设置了select,insert,update,delete
　　在oldboy.t1设置了select，那么用户在t1表的最终权限应该是什么。
　　回收权限的时候又该怎么做？

　　grant select on test. to oldboy@'10.0.0.%'>
　　grant all on test.t1  to oldboy@'10.0.0.%'>　　oldboy用户最后对test.t1权限为all
　　结论：如果，库级别和表级别都设置了权限，那么对表操作时，权限是叠加。
　　告诉大家的是，对同一个用户，不要多级别设置权限。
　　 5.最后总结
　　grant INSERT,SELECT, UPDATE, DELETE, CREATE, DROP on oldboy.  to app@'10.0.0.%'>
　　revoke INSERT,SELECT, UPDATE, DELETE, CREATE, DROP on oldboy.  from app@'10.0.0.%';          ----权限回收
　　show grants for   app@'10.0.0.%';
　　select user,host,password from mysql.user;    ---查看用户
　　————————————————————————————————————
　　（3）mysql忘记密码
　　————————————————————————————————————
　　1.停止数据库
　　2.停止授权功能，停止远程网络登录
　　mysqld_safe --skip-grant-table  --skip-networking &
　　3.更改用户表中root密码
　　update mysql.user set password=PASSWORD('123') where user='root' and host='localhost';
　　4.重启数据库
　　/etc/init.d/mysqld restart
　　补充说明：
　　5.7密码字段不再是password了，被替换为了authentication_strings
　　update mysql.user set authentication_strings=PASSWORD('123') where user='root' and host='localhost';
　　——————————————————————————————————————
　　help帮助-----很有用
　　\c
　　\G
　　source  恢复备份，执行sql脚本
　　system   执行操作系统命令
　　use world
　　status
　　例子：mysql> help create database;
　　———————————————————————————————————————
　　（3）库，表，列，行操作   -----DDL：数据库中对象定义语言（库、表）
　　———————————————————————————————————————
　　库操作：
　　create schema zabbix character set utf8;   ---建库
　　show create database zabbix;         ---看库
　　drop database zabbix                      ---删库
　　alter database zabbix charset utf8mb4;     ---修改库属性
　　show create database zabbix;
　　列操作：
　　create table stu (id int,name varchar(20),age int ,gender int);  ---创建一个表
　　show create table stu;    ----查看表内容信息
　　desc stu;  ---查看表结构
　　drop table stu;   ---删除表
　　alter table  stu rename  to  student;   ----给表重命名
　　1、在表最后加一列
　　alter table student add addr varchar(20);
　　2、在表头部加一列
　　alter table student add stu_id int first;
　　3、  在name列后加一列
　　alter table student add qq int after name;
　　4、在age后加tel_num,在最后一行加email
　　alter table student add tel_num int after age,add email varchar(20);
　　5、删除某一列

　　alter table student drop>　　6、  修改列名字
　　alter table student change name stu_name varchar(20);
　　7、修改列数据类型
　　alter table student modify  gender varchar(20);
　　8.使用update替代delete，伪删除。
　　alter table student_0 add state int default 1;   ---最后一列新加一列
　　update student_0 set state=0 where stu_name='zhang3';   ---修改
　　select from student_0 where state=1;     ---按条件查看
　　9.补充的：
　　创建一个表结构与student表相同的空表。
　　create table student_0 like stundet;
　　创建一个和t1相同的备份表
　　create table t1_1 as select  from  t1;
　　——————————————————————————————————————
　　DCL：数据库控制语言（权限grant revoke）
　　——————————————————————————————————————
　　grant INSERT,SELECT, UPDATE, DELETE, CREATE, DROP on oldboy.  to app@'10.0.0.%'>
　　revoke INSERT,SELECT, UPDATE, DELETE, CREATE, DROP on oldboy.  from app@'10.0.0.%';
　　——————————————————————————————————-————
　　DML：数据行操作语言（增、删、改）
　　——————————————————————————————————————
　　操作的是什么？
　　表中的数据行，常规的“事务”语句
　　插入数据：
　　插入一行
　　insert student values(1,'zhang3',123,20,110,'male','bj','123@qq.com');
　　insert into student values(1,'zhang3',123,20,110,'male','bj','123@qq.com');
　　指定列进行插入
　　insert into student(stu_id,stu_name,qq) values(2,'li4',456);
　　一次性插入多行数据（多行同时同时插入，效率更高，推荐使用，但是不能）：
　　insert into student values(1,'zhang3',123,20,110,'male','bj','123@qq.com'),(5,'zz',12322,202,1102,'female','bj','12322@qq.com');
　　插入student数据到student_0
　　insert into student_0 select from student;
　　修改数据：
　　update student set stu_name='wang5' where stu_id=5;
　　删除数据
　　delete from student where stu_name='zhang3';
　　————————————
　　truncate table student;
　　一般是在需要删除整个大表的时候，为了加速
　　例如   drop table  oss_base;  -----》1000w 行
　　先：
　　truncate table oss_base;
　　后
　　drop table oss_base;
　　————————————
　　使用update替代delete伪删除。
　　alter table student_0 add state int default 1;
　　update student_0 set state=0 where stu_name='zhang3';
　　select  from student_0 where state=1;
　　——————————————————————————————————
　　DQL: 数据行查询语言（select show）
　　——————————————————————————————————
　　1、where子句使用
　　用途：将数据按照指定条件，进行过滤处理显示
　　1.1 等值过滤
　　-- CHN 国家，所有城市的名字。
　　SELECT  NAME   FROM city WHERE CountryCode='CHN';
　　1.2 比较过滤（范围查询）

　　>=  <  500w的
　　SELECT  NAME,population
　　FROM city
　　WHERE CountryCode='CHN' AND  population>5000000;
　　-- 全世界范围内，小于100人的城市。
　　SELECT NAME,Population,countrycode FROM city
　　WHERE Population