第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<777;
SQL过滤练习:
排序:按照人口从少到多的顺序排列显示:
SELECT NAME,Population FROM city
WHERE CountryCode='chn' ORDER BY Population;
按照从多到少进行排序显示:
SELECT NAME,Population FROM city
WHERE CountryCode='chn' ORDER BY Population DESC;
显示人口排名最多的前10名:
SELECT NAME,Population FROM city
WHERE CountryCode='chn'
ORDER BY Population DESC LIMIT 10;
显示人口排名 第50行到第60行
SELECT NAME,Population FROM city
WHERE CountryCode='chn'
ORDER BY Population DESC LIMIT 50,10;
显示世界上人口小于100的城市:
SELECT NAME,Population
FROM city WHERE Population<1000;
显示PCN是那个国家:
SELECT NAME FROM city WHERE country='PCN';
显示世界上人口小于100的城市所在国家的国家名字
SELECT country.name FROM country,city
WHERE city.Population<100 AND country.Code=city.countrycode;
以上命令可以简写成:
SELECT co.name FROM city AS ci ,country AS co
WHERE
ci.population<100
AND co.code=ci.CountryCode;
MySQL用户管理及SQL入门
原文：http://blog.51cto.com/13520772/2094425
