第 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;
来源: http://www.bubuko.com/infodetail-2548763.html