MySQL用户管理及SQL入门

mingche

　　
第1章 Mysql用户管理:
　　
1.1 用户的定义:  用户名+主机域
　　
mysql> select user,host,password from mysql.user;
　　
+------+-----------+----------+
　　
| user | host      | password |
　　
+------+-----------+----------+
　　
| root | localhost |          |
　　
| root | db01      |          |
　　
| root | 127.0.0.1 |          |
　　
1.2 用户的作用:
　　
1.      用来登录数据库
　　
2.      管理数据的对象(表和库)
　　
说明:类比:Linux中创建用户,更改某个目录或者文件的权限来对数据进行管理
　　
1.2.1 创建一个用户
　　
权限设定: grant 权限 on 权限范围 to 用户  identified by ‘密码’;
　　
1.2.2 权限管理:
　　
对数据库的读写操作等,权限可用来管理某个用户可以对数据库做什么
　　
（insert update、select、delete、drop、create等）
　　
1.2.3 角色:
　　
对数据库读,写等操作(insert;update;select)
　　
1.2.4 权限范围:
　　
1.      全库级别: *.*
　　
2.      单库级别: test.*
　　
3.      单表级别: test.table_name
　　
1.2.5 用户:
　　
    'clsn'@'localhost'  本地
　　
    'clsn'@'192.168.66.149'
　　
    'clsn'@'192.168.66.%'
　　
'clsn'@'192.168.66.14%'
　　
1.3 练习:按照要求创建一个用户
　　
用户只能通过10.0.0.0/24网段访问,用户名为jiang,密码为123
　　
jiang用户只能对jiang数据库下的对象进行增insert;create;改update;查select
　　
1.3.1 创建用户并授权
　　
grant insert,select,create,update on jiang.* to ‘jiang@10.0.0.%’ identified by ‘123’;
　　
1.3.2 查看用户的权限:
　　
mysql> show grants for root@'10.0.0.%'\G
　　
*************************** 1. row ***************************
　　
Grants for root@10.0.0.%: GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.0.0.%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257'
　　
1 row in set (0.01 sec)
　　
1.3.3 标准创建用户的方法:
　　
create user 'web'@'172.16.1.%' identified by 'web123';   这样创建的用户只有连接的权限
　　
1.3.4 删除用户:
　　
mysql> drop user root@'10.0.0.%';
　　
1.3.5 回收权限
　　
revoke insert on *.* from root@’localhos’;
　　
1.4 Mysql忘记密码的修改办法:
　　
1.      停掉数据库
　　
/etc/init.d/mysqld stop
　　
2.      停掉连接层的授权功能和远程登录动能,并启动
　　
cd /application/mysql/bin/
　　
mysqld_safe --skip-grant-table --user=mysql --skip-networking &
　　
说明:这种启动模式下,无密码登录,网络用户无法登录,只能本地登录,和授权有关的命令都无法执行
　　
3.      直接修改密码
　　
mysql> update mysql.user set password=password('123') where user='root' and host='localhost';
　　
mysql> flush privileges;
　　
MySQL5.7版本修改密码的修改字段为: authentication_string
　　
4.      退出,重启服务,正常restart就可以,我这里失败了,所以先停止在启动
　　
/etc/init.d/mysqld stop
　　
/etc/init.d/mysqld start
　　
5.      登录数据库进行验证
　　
mysql -uroot -p123
　　
第2章 Mysql客户端工具及SQL入门
　　
2.1 MySQL客户端命令有哪些?
　　
1.      mysql     ---用来连接数据库
　　
---将用户的SQL语句发送到服务端
　　
2.      mysqladmin---命令行管理工具
　　
3.      mysqldump---备份数据库和表的内容
　　
2.2 mysql命令功能详解:
　　
1.      用户连接数据库
　　
2.      用于管理数据库
　　
2.2.1 mysql命令接口自带功能:
　　
命令    命令说明
　　
\h 或 help 或 ?     获取帮助
　　
\G    格式化输出（行转列）
　　
\T 或 tee    记录操作日志  tee /tmp/mysql.log
　　
\c 或 CTRL+c    退出mysql
　　
\s 或 status    查看数据库状态信息
　　
\. 或 source    mysql> source   /tmp/world.sql
　　
\!    使用shell中的命令 mysql> \!   cat /etc/redhat-releaseCentOS   release 6.9 (Final)
　　
\u 或use       use  worldshow   databases  看当前所有数据库的名字show   tables   查看当前use到的数据库所有的表show  tables   from world   查看目标数据库下的表
　　
快捷键    上下翻页、TAB键、ctrl   +C 、ctrl +L
　　
2.2.2 mysql命令中help帮助说明:
　　
contents查看完整的sql类别列表帮助
　　
mysql> help contents
　　
查看特定sql类别或语句的帮助
　　
mysql> help Account Management;
　　
查看grant帮助
　　
mysql> help grant
　　
与状态相关的sql语句帮助
　　
mysql> help status
　　
mysqladmin命令说明:
　　
功能选项    说明
　　
mysqladmin -u用户 -p密码 ping    “强制回应 (Ping)”服务器。
　　
mysqladmin -u用户 -p密码 shutdown    关闭服务器。
　　
mysqladmin -u用户 -p密码 create   databasename    创建数据库。
　　
mysqladmin -u用户 -p密码drop   databasename    删除数据库
　　
mysqladmin -u用户 -p密码 version    显示服务器和版本信息
　　
mysqladmin -u用户 -p密码 status    显示或重置服务器状态变量
　　
mysqladmin -u用户 -p密码 password    设置口令
　　
mysqladmin -u用户 -p密码 flush-privileges    重新刷新授权表。
　　
mysqladmin -u用户 -p密码 flush-logs    刷新日志文件和高速缓存。
　　

　　
2.3 SQL语句入门:
　　
2.3.1 DDL:   数据定义语言
　　
定义范围:对库名和库的特性      对表名和表中的列
　　
查看数据库
　　
查看所有数据库
　　
mysql> show databases;
　　
+--------------------+
　　
| Database           |
　　
+--------------------+
　　
| information_schema |
　　
| mysql              |
　　
| performance_schema |
　　
| test               |
　　
| world              |
　　
| zabbix             |
　　
+--------------------+
　　
查看当前所在数据库
　　
mysql> select database();
　　
+------------+
　　
| database() |
　　
+------------+
　　
| zabbix     |
　　
+------------+
　　
1 row in set (0.00 sec)
　　
对数据库的操作
　　
创建一个库
　　
mysql> create database zabbix character set utf8;
　　
Query OK, 1 row affected (0.01 sec)
　　
查看库中的表
　　
mysql> show tables;
　　
+------------------+
　　
| Tables_in_zabbix |
　　
+------------------+
　　
| stu              |
　　
+------------------+
　　
1 row in set (0.00 sec)
　　
查看库的创建语句
　　
mysql> show create database zabbix;
　　
+----------+-----------------------------------------------------------------+
　　
| Database | Create Database                                                 |
　　
+----------+-----------------------------------------------------------------+
　　
| zabbix   | CREATE DATABASE `zabbix` /*!40100 DEFAULT CHARACTER SET utf8 */ |
　　
+----------+-----------------------------------------------------------------+
　　
1 row in set (0.00 sec)
　　
修改库的属性:只能修改字符集和校对规则
　　
mysql> alter database zabbix charset utf8mb4;
　　
删除一个库:
　　
mysql> drop database zabbix;
　　
切库
　　
mysql> use zabbix;
　　
对表的操作
　　
创建一个表
　　
mysql> create table stu (id int,name varchar(20),age int ,gender int);
　　
查看表的创建语句
　　
mysql> show create table stu;
　　
在表最后一列添加:
　　
mysql> alter table stu add addr varchar(20);
　　
在表的头部添加:
　　
mysql> alter table stu add stu_id int first;
　　
在某一列后面添加:
　　
mysql> alter table stu add qq int after name;
　　
在age后添加tel_num,在最后一行添加email
　　
mysql> alter table stu add tel_num int after age,add email varchar(20);
　　
删除某一列:
　　
mysql> alter table stu drop email;
　　
修改列名字:
　　
mysql> alter table stu change qq QQ int;
　　
修改列的数据类型:
　　
mysql> alter table stu modify gender varchar(20);
　　
创建一个表结构相同的空表
　　
mysql> create table stu_0 like stu;
　　
mysql> show tables;
　　
+------------------+
　　
| Tables_in_zabbix |
　　
+------------------+
　　
| stu              |
　　
| stu_0            |
　　
+------------------+
　　
2 rows in set (0.00 sec)
　　
创建一个表结构相同的备份表
　　
mysql> create table stu_0 as select * from stu;
　　
2.3.2 DCL      数据库控制语言
　　
用户授权
　　
GRANT ALL ON *.* TO 'jiang'@'localhost';
　　
SHOW GRANTS FOR 'jiang'@'localhost'\G
　　
创建用户的同时进行授权
　　
grant insert,select,create,update on jiang.* to jiang@'10.0.0.%' identified by '123';
　　
回收权限
　　
REVOKE INSERT ON *.* FROM jiang@localhost;
　　
2.3.3 DML      数据行操作语言(增删改)
　　
insert语句
　　
指定列插入
　　
mysql> insert into stu (stu_id,QQ) values(1,777);
　　
所有列插入
　　
mysql> insert into stu values(2,777,'j',56,'hao',2,'r','q',1);
　　
insert复制表结构及内容---表已经存在的情况下,在可以复制
　　
mysql> create table stu_1 like stu;
　　
mysql> insert into stu_1 select * from stu;
　　
修改和删除操作
　　
删除数据:
　　
mysql> delete from stu where stu_id=1        逻辑删除
　　
mysql> truncate table stu_3;                 物理删除
　　
修改数据,要加上where条件
　　
mysql> update stu_3 set QQ=5656 where age='hao';
　　
delete    删除之后可以用二进制日志反解insert命令把数据找回来
　　
truncate   物理删除,数据找不回来
　　
一般删除大表的时候,先truncate然后在drop整个表,效率会比较高
　　
使用update替代delete,伪删除
　　
mysql> alter table stu add state int default 1;
　　
Query OK, 0 rows affected (0.54 sec)
　　
Records: 0  Duplicates: 0  Warnings: 0
　　

　　
mysql> select * from stu;
　　
+--------+------+-------+-----------+------+---------+--------+------+-------+
　　
| stu_id | id   | name  | QQ        | age  | tel_num | gender | addr | state |
　　
+--------+------+-------+-----------+------+---------+--------+------+-------+
　　
|      1 |  123 | jiang |       222 | ni   |   15555 | boy    | hao  |     1 |
　　
|      1 |  123 | jiang |       222 | ni   |   15555 | boy    | hao  |     1 |
　　
|      1 |  123 | jiang |       222 | ni   |   15555 | boy    | hao  |     1 |
　　
|      7 | NULL | da ya | 850144102 | NULL |    NULL | NULL   | NULL |     1 |
　　
+--------+------+-------+-----------+------+---------+--------+------+-------+
　　
4 rows in set (0.00 sec)
　　
修改state值,等于0即为删除,
　　
mysql> update stu set state=0 where name='jiang';
　　
mysql> select * from stu where state=1;
　　
+--------+------+-------+-----------+------+---------+--------+------+-------+
　　
| stu_id | id   | name  | QQ        | age  | tel_num | gender | addr | state |
　　
+--------+------+-------+-----------+------+---------+--------+------+-------+
　　
|      7 | NULL | da ya | 850144102 | NULL |    NULL | NULL   | NULL |     1 |
　　
+--------+------+-------+-----------+------+---------+--------+------+-------+
　　
1 row in set (0.01 sec)
　　
防止误删除:
　　
  -U, --safe-updates  Only allow UPDATE and DELETE that uses keys.
　　
mysql命令加上-U后   在执行update和delete语句时,不加where条件不会执行
　　
2.3.4 DQL:数据行查询语言
　　
select查询语句
　　
查看stu表中所有信息
　　
mysql> select * from stu;
　　
查看某一列的信息
　　
mysql> select name from stu;     多个列用逗号分隔
　　
查看id号码为777的qq号码
　　
mysql> select QQ from stu where id=777;
　　
where子句使用: 将数据按照指定条件,进行过滤处理
　　
等值过滤:
　　
mysql> select stu_id from stu where QQ=777;
　　
比较过滤:
　　
mysql> select stu_id from stu where QQ