|
|
登录MySQL方法
单实例MySQL登录的方法
mysql #刚装完系统无密码情况登录方式，不需要密码 mysql -u root #刚装完系统无密码的情况登录 mysql -uroot -p #这里标准的dba命令行登录 mysql -uroot -poldboy #非脚本里一般不这样用，密码明文会泄露密码适合多实例防止密码泄露的方法
设置变量HISTCONTROL=ignorespace mysql -uroot -poldboy -S /data/3306/mysql.sock前面加空格将不记录登录信息 2、给启动脚本以及备份脚本等加700权限，用户和组改为root。
chmod 700 /data/3306/mysqlchmod 700 /server/scripts/bak.sh 3、删除命令行记录
history -d 历史命令序号history -c 清除所有清除之后在root家目录里面还会有记录cat ~/.bash.history 多实例MySQL本地登录
mysql -uroot -p -S /data/3306/mysql.sockmysql -uroot -p -S /data/3307/mysql.sock提示： 多实例通过mysql的-S 命令指定不同的sock文件登录不同的服务中 注意：多实例的远程连接无需自定sock路径：
mysql -uroot -p -h 127.0.0.1 -P3307-h 指定IP地址，-P 指定端口号 登录后默认提示符是：mysql > 这个提示符可以更改，就像linux命令行提示符一样
mysql
>
为了防止误操作，可以把提示符标记为测试环境，也可以写入配置永久生效
1） 在命令行修改登录提示
mysql> prompt \u@oldboy \r:\m:\s->PROMPT set to '\u@oldboy \r:\m:\s->'root@oldboy 05:03:09->root@oldboy 05:03:11->root@oldboy 05:03:12-> 2） 配置文件修改登录提示符
在my.cnf配置中【mysql】模块下添加如下内容（注意，不是【mysqld】）保存后，无需重启mysql，退出当前session，重新登录即可
[mysql]prompt=\\u@oldboy \\r:\\m:\\s-> MySQL help帮助Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> SHOW DATABASES LIKE '%MY%'; 模糊匹配mysql> show databases like 'd3306';mysql> show databases like '%3306';mysql> show databases like '%33%';更多参数help show 强制关闭数据库的方法（慎用）
killall mysqldpkill mysqldkillall -9 mysqldmysqld: no process killedkill -9 pid 最好使用优雅停止
mysqladmin -uroot -poldboy123 shutdown 野蛮粗鲁杀死数据库导致故障企业案例：
http://oldboy.blog.51cto.com/2561410/1431161http://oldboy.blog.51cto.com/2561410/1431172 企业实战题7：
开发mysql多实例启动脚本：
已知mysql多实例启动命令为：mysqld_safe–defaults-file=/data/3306/my.cnf
&
停止命令为：mysqladmin
-u root -poldboy123 -S /data/3306/mysql.sockshutdown
请完成mysql多实例启动启动脚本的编写
要求：用函数，case语句、if语句等实现。
MySQL数据库安全策略介绍
为root设置比较复杂的密码删除无用的mysql库内的用户账号，只留root@localhost删除默认的test数据库删除用户的时候，授权的权限尽量最小，允许访问的主机范围最小化针对mysql数据库的用户处理，还有更严格的做法，例如删除root用户，添加新的管理员用户 truncalt table test和delete from test；区别
1.truncate table test；速度更快。直接清空对应数据的物理文件。
2、delete from test；速度慢，逻辑清除，按行删
为管理员root用户设置密码方法
mysqladmin -u root password ‘oldboy’ #没有密码的情况下mysqladmin -uroot -p oldboy password oldboy123 -S /data/3306/mysql.sock 适合多实例更改密码，强调，以上命令的是命令行执行，而不是进入root
修改管理员root密码法一：linux命令修改法
mysqladmin -uroot -p oldboy password ‘oldboy123’ #原密码，新密码mysqladmin -uroot -p oldboy password oldboy456 -S /data/3306/mysql.sock适合多实例 修改管理员root密码法二：****sql
语句修改法
mysql> update mysql.user set password=oldboy456 where user='root' and host='localhost'; 这样设置是不可以登录的 这样设置的密码不可以使用，需要加密
mysql> update mysql.user set password=password('oldboy456') where user='root' and host='localhost'; 结果： 如果不使用password这个变量 下面的密码将会是明文，明文我们无法登录
mysql> select user,host,password from mysql.user;+------+-----------+-------------------------------------------+| user | host | password |+------+-----------+-------------------------------------------+| root | localhost | *7DB922C59F217871B8165D72BEC8ED731A0EFFA1 || root | db01 | || root | 127.0.0.1 | || root | ::1 | || | localhost | || | db01 | |+------+-----------+-------------------------------------------+6 rows in set (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0需要查看是否成功，出现Changed代表成功 设置完成之后我们需要刷新才可以登录
mysql> flush privileges; 修改管理员root（所有）密码法三
set password=password('oldboy123'); MySQL密码丢失如何找回？
单实例
a、/etc/init.d/mysqld stopb、mysqld_safe --skip-grant-tables --user=mysql &c、mysqld、修改完密码重启e、/etc/init.d/mysqld restart 多实例
1./data/3306/mysql stop 无法停止2.killall mysqld3.mysqld_safe --defaults-file=/data/3306/my.cnf --skip-grant-tables --user=mysql &4.update mysql.user set password=password('oldboy456') where user='root' and host='localhost';5.flush privileges;6.mysql 登录7.mysqladmin -uroot -poldboy shutdown8./etc/init.d/mysqld start 5.1 启动与关闭MYSQL命令及工作原理
5.2 登陆MYSQL方法
5.4 善用MYSQL的帮助命令HELP
5.5 退出MYSQL数据库方法
5.6 设置及修改MYSQL ROOT用户密码
5.6.1 MySQL数据库用户安全策略介绍
5.6.2 为管理员root用户设置密码方法
5.7 找回丢失的MYSQL ROOT用户密码
SQL结构化查询语言
什么是SQL？
SQL，英文全称Structured Query Language，中文意思是结构化查询语言，它是一种对关系型数据库中的数据进行定义和操作的语言方法，是大多数关系数据库管理系统所支持的工业标准语言。
结构化查询语言SQL是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统，同时用sql作为MySQL逻辑备份文件的扩展名。结构化查询语言是高级的非过程化编程语言，允许用户在高层数据结构上工作。他不要求用户指定对数据存放方法，也不需要用户了解具体的数据存放方式。
小结：SQL语句最常见的分类一般就是3类
DDL（Data
Definition Language）——数据定义语言（CREATE，ALTER，DROP）
管理基础数据，例如：库，表
DCL （Data
Control Language）——数据控制语言（GRANT，REVOKE，COMMIT，ROLLBACK）
用户授权，权限回收，数据提交回滚等
DML（Data
Manipulation Language）——数据操作语言（SELECT，INSERT，DELETE,UPDATE）针对数据库里的表里的数据进行操作，记录
命令讲解
创建数据库
mysql> create database oldboy; 查看创建库的语句
show create database oldboy;+----------+-----------------------------------------------------------------+| Database | Create Database|+----------+-----------------------------------------------------------------+| oldboy | CREATE DATABASE `oldboy` /*!40100 DEFAULT CHARACTER SET utf8 */ |+----------+-----------------------------------------------------------------+1 row in set (0.00 sec) 编译的时候指定了utf8 所以这里显示utf8
1.网站程序字符集2.客户端的字符集3.服务器端字符集4.linux客户端字符集5.以上都要统一，否则会出现中文乱码 创建不同字符集格式的数据库命令
mysql> create database oldboy; #默认数据库配置，相当于创建拉丁字符集数据库mysql> create database oldboy_gbk character set gbk collate gbk_chinese_ci;创建gbk格式文件mysql> help create databasemysql> show character set; #查看字符集mysql> show create database oldboy_gbk;+------------+--------------------------------------------------------------------+| Database| Create Database|+------------+--------------------------------------------------------------------+| oldboy_gbk | CREATE DATABASE `oldboy_gbk` /*!40100 DEFAULT CHARACTER SET gbk */ |+------------+--------------------------------------------------------------------+1 row in set (0.00 sec) 如果编译的时候指定了特定的字符集，则以后创建对应字符集的数据库就不需要指定字符集
-DDEFAULT_CHARSET=utf8 \-DDEFAULT_COLLATION=utf8_general_ci \-DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii \提示：二进制软件包，安装的数据库字符集默认latinl 查看数据库
show databases;select database(); #相当于pwdselect user(); #查看当前用户select version(); #查看当前版本()可以说是函数 当前数据库包含的表信息
use oldboy #相当于cdshow tables #查看表orshow tables from wordpress #查看wordpress库下的表文件 删除用户
drop user 'root'@'::1';如果drop删除不了（一般reshuffle符号或大写）可以用下面方式删除（以root，用户为例）delete from mysql.user where user=’root’ and host=’oldboy’;flush privileges;创建MySQL用户及赋予用户权限
1、通过在mysql中输入“helpe grant”得到如下信息
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';GRANT ALL ON db1.* TO 'jeffrey'@'localhost';GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost';GRANT USAGE ON *.* TO 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90; 2、通过grant命令创建用户并授权
a、grant命令简单语法如下：grant all privileges on dbname.* to username@localhost identified by ‘passwd’; b、列表说明如下：
grant all
on dbname.*
to username@localhost
identified by‘passwd’
授权命令
对应权限
目标：库和表
用户名和客户端主机
用户密码
说明：上述命令是授权localhost主机上通过用户username管理dbname数据库的所有权限，密码为passwd，其中username，dbname，passwd可根据业务的情况修改
对于web连接用户授权尽量采用最小化原则，很多开源软件都是web界面安装，因此在安装期间除了select,insert,update,delete 4个权限外，还需要create,drop等比较危险的权限。
grant select,insert,update,create,drop on blog.* to blog@localhost identified by ‘123’ 常规情况下授权select,insert,update,delete，4个权限即可，有的源软件，例如discuz,bbs还需要create,drop等比较危险的权限。生成数据库表后，要收回create，drop授权。
生产环境针对主库（写入主读为辅）用户的授权；
普通环境：
本机：lnmp，lamp环境数据库授权grant all privileges ON blog.* to blog@localhost identified by ‘123456’应用服务器和数据库服务器不在一个主机上授权；grant all privileges ON blog.* to blog@10.0.0.% identified by ‘123’严格的授权：重视安全，忽略了方便；grant select,insert,update,delete ON blog.* to blog@10.0.0.% identified by ‘123’生产环境从库（只读）用户的授权；grant select ON blog.* to blog@10.0.0.% identified by ‘123’查看授权用户oldboy的具体的授权权限show grants for ‘oldboy’@’localhost’; 3、案例1.创建oldboy用户，对test库具备所有权限，允许从localhost主机登录，密码是oldboy123
grant all on test.* to oldboy@localhost identified by ‘oldboy123’; 第一种：授权用户
grant all on test.* to oldboy@127.0.0.% identified by ‘oldboy123’show grants for oldboy@’127.0.0.%’; 查看授权用户+-------------------------------------------------------------------------------------------------------------+| Grants for root@127.0.0.1|+-------------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'root'@'127.0.0.1' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' || GRANT ALL PRIVILEGES ON `test`.* TO 'root'@'127.0.0.1' |+-------------------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec) 第二种：授权方法
create user bbs@'172.16.1.1/255.255.255.0' identified by '123456'；先授权可以登录的mysql> show grants for bbs@'172.16.1.1/255.255.255.0';mysql> grant select on wordpress.* to bbs@'172.16.1.1/255.255.255.0';授权局域网主机连接远程数据库
a.一条命令百分号匹配法
grant all on *.* to‘test@10.0.0.%’identified by ‘test123’; b、一条命令子网掩码配置法
grant all on *.* to test@’10.0.0.0/255.255.255.0’ identified by ‘test123’; c、两条命令实现
先创建用户并设置密码；
create user test@’10.0.0.%’ identified by ‘test123’; 再对用户授权指定权限和管理库表
grant all on *.* to test@10.0.0.0/255.255.255.0 最后记得上述每条grant命令都要刷新权限
flush privilege 数据库远程登录
mysql -uwordpress -poldboy123 -h 172.16.1.51 -P3306-h指定IP地址，-P指定服务端口号 创建类似于root系列的管理员用户，可以创建下级用户的用户
grant all privileges on *.* to root@'127.0.0.1' identified by 'oldboy123' with grant option;只需要在最后输入with grant option 回收用户权限
REVOKE INSERT ON *.* FROM 'jeffrey'@'localhost'; 显示库的信息
mysql> show create database oldboy\G MySQL包含关系
数据库服务器>数据库（多个实例）>多个库>多个表>多个字段行列（数据）
创建表
建表的基本命令语法：
create table （);提示：其中create table是关键字，不能更改，但是大小写可以变化 建表语句
下面是人工写法设计的建表语句例子，表名student
create table student(id int(4) not null,name char(20) not null,age tinyint(2) NOT NULL default '0',dept varchar(16) default NULL); 查看创建表的语句
mysql> show create table student;create table student( #create table表示创建表的固定关键字,student为表名id int(4) not null, #学号列,数字类型，长度为4，不能为空值name char(20) not null, #名字列，定长字符类型，长度20，不能为空age tinyint(2) NOT NULL default '0', #年龄列，很小的数字类型，长度为2，不能为空，默认为0值dept varchar(16) default NULL #系别列，变长字符类型，长度16，默认为空。ENGINE=lnnoDB DEFAULT CHARSET=latinl #引擎和字符集，引擎默认为InnoDB，字符集，继承库的latinl); student表的直观显示，可以用下面表格显示。
create table student(id int(4) not null,name char(20) not null,age tinyint(2) NOT NULL default '0',dept varchar(16) default NULL)ENGINE=InnoDB DEFAULT CHARSET=latinl; 需要注意的事：MySQL5.1和MySQL5.5
环境的默认建表语句中的引擎的不同，如果希望控制引擎，就要在建表语句里显示的指定引擎建表；
MySQL5.1以及默认引擎为MyISAM，MySQL5.5
以后默认引擎为InnoDB
MySQL表的字段类型
1） 数字类型
2）日期和时间类型（DATE 日期类型：支持的范围是1000-01-01到9999-12-31。MySQL以YYYY-MM-DD格式来显示DATE值，但是允许你使用字符串或数字把值赋给DATE列）
3）字符串类型
1、 INT[(M)]型：正常大小整数类型
2、 CHAR(M)型：定长字符串类型，当存储时，总是用空格填满右边到指定的长度
3、 VARCHAR型：变长字符串类型
有关MySQL字段类型详细内容，可以参考MySQL手册
1． INT[(M)]型：正常大小整数类型
2． DOUBLE[M,D] [ZEROFILL]型：正常大小（双精密）浮点数字类型
3． DATE 日期类型：支持的范围是1000-01-01到9999-12-31.MySQL以YYY-MM-DD格式来显示DATE值，但是允许你使用字符串给数字把值赋给DATE列
4． CHAR(M)型：定长字符串类型，当存储时，总是是用空格填满右边到指定的长度
5． BLOB TEXT类型，最大长度65535（2^16-1）个字符
6． VARCHAR型：变长字符串类型
下面的图说明了CHAR和VARCHAR之间的差别：
解释：例如，VARCHAR(10)列可以容纳最大长度为10的字符串。实际存储需求是字符串（L）的长度，加上一个记录字符串长度的字节。对于字符串’abcd’，L是4，存储需要5个字节。
小结
1、 char定长，不够的用空格补全，浪费存储空间，查询速度快，多数系统表字段都是定长
2、 varchar变长，查询速度慢
例子：mysql.user用的就是定长
`Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '', user表用的是CHAR
生产场景案例：
某sns产品生产正式建表语句
use sns;set names gbk;CREATE TABLE `subject_comment_manager` ( `subject_comment_manager_id` bigint(12) NOT NULL auto_increment COMMENT '主键', `subject_type` tinyint(2) NOT NULL COMMENT '素材类型', `subject_primary_key` varchar(255) NOT NULL COMMENT '素材的主键', `subject_title` varchar(255) NOT NULL COMMENT '素材的名称', `edit_user_nick` varchar(64) default NULL COMMENT '修改人', `edit_user_time` timestamp NULL default NULL COMMENT '修改时间', `edit_comment` varchar(255) default NULL COMMENT '修改的理由', `state` tinyint(1) NOT NULL default '1' COMMENT '0代表关闭，1代表正常', PRIMARY KEY (`subject_comment_manager_id`), KEY `IDX_PRIMARYKEY` (`subject_primary_key`(32)), # create index ind_name_dept on student(name,dept); 联合索引作用，查询更快
mysql> show index from student\G*************************** 1. row ***************************Table: studentNon_unique: 0Key_name: PRIMARY Seq_in_index: 1Column_name: idCollation: ACardinality: 0Sub_part: NULLPacked: NULLNull:Index_type: BTREEComment:Index_comment:*************************** 2. row ***************************Table: studentNon_unique: 1Key_name: index_name Seq_in_index: 1Column_name: nameCollation: ACardinality: 0Sub_part: NULLPacked: NULLNull:Index_type: BTREEComment:Index_comment:*************************** 3. row ***************************Table: studentNon_unique: 1Key_name: index_age Seq_in_index: 1Column_name: nameCollation: ACardinality: 0Sub_part: 8 Packed: NULLNull:Index_type: BTREEComment:Index_comment:*************************** 4. row ***************************Table: studentNon_unique: 1 Key_name: ind_name_dept Seq_in_index: 1Column_name: nameCollation: ACardinality: 0Sub_part: NULLPacked: NULLNull:Index_type: BTREEComment:Index_comment:*************************** 5. row ***************************Table: studentNon_unique: 1 Key_name: ind_name_dept Seq_in_index: 2Column_name: deptCollation: ACardinality: 0Sub_part: NULLPacked: NULLNull: YESIndex_type: BTREEComment:Index_comment:5 rows in set (0.00 sec)创建联合索引，并指定值大小
create index ind_name_dept on student(name(8),dept(10));name 前8个字符，dept 前10个字符*************************** 2. row ***************************Table: studentNon_unique: 1 Key_name: index_dept Seq_in_index: 1Column_name: nameCollation: ACardinality: 0 Sub_part: 8Packed: NULLNull:Index_type: BTREEComment:Index_comment:*************************** 3. row ***************************Table: studentNon_unique: 1 Key_name: index_dept Seq_in_index: 2Column_name: deptCollation: ACardinality: 0 Sub_part: 10Packed: NULLNull: YESIndex_type: BTREEComment:Index_comment:3 rows in set (0.00 sec)
提示：尽量在唯一值多的大表上建立索引。
什么时候创建联合索引？
只有程序用这两个条件查询，采用联合索引，这个主要是看开发。
提示：按条件列查询数据时，联合索引是由前缀生效特性的
创建唯一索引（非主键）
create unique index uni_ind_name on student(name);
索引小结：
创建主键索引alter table student chage id id int primary key auto_increment;删除主键索引（主键列不能自增）alter table student drop primary key;创建普通索引alter table student add index index_dept(dept);根据的前n个字符创建索引create index index_dept on student(dept(8));根据多个列创建联合索引create index index_name_dept on student(name,dept);创建唯一索引create unique index uni_ind_name on student(name);删除普通索引与唯一索引alter table student drop index index_dept;drop index index_dept on student;索引列的创建及生效条件
问题1、既然索引可以加快查询速度，那么就给所有的列加索引吧？
解答：因为索引不但占用系统空间，而且更新数据时还需要维护索引数据的，因此索引是一把双刃剑，并不是越多越好，例如：数十到几百行的小表上无需建立索引，插入更新频繁，读取比较少的需要少建立索引
问题2、需要在哪些列上创建索引才能加快查询速度呢？
select user,host from mysql.user where
password=…..,索引一定要创建在where后的条件列上，而不是select后的选择数据的列上。另外，我们要尽量选择在唯一值多的大表上的列建立索引，例如，男女生性别列唯一值少，不适合建立索引。
查看唯一值数量
select count(distinct user) from mysql.user; 唯一值就是相同的数量，例如查询user那么相同的user就是唯一值
mysql> select count(distinct user) from mysql.user;+----------------------+| count(distinct user) |+----------------------+| 7 |+----------------------+1 row in set (0.07 sec) 用户列表，根据上放进行解释
mysql> select user,host from mysql.user;+-----------+---------------------------+| user| host |+-----------+---------------------------+| cyh| 10.1.1.% || root| 127.0.0.1 || bbs| 172.16.1.1/255.255.255.0 || wordpress | 192.168.1.% || oldboy| 192.168.1.%/255.255.255.0 || abc| localhost || blog| localhost || oldboy| localhost || root| localhost |+-----------+---------------------------+9 rows in set (0.00 sec) 创建索引的基本知识小结：
- 索引类似书籍的目录，会加快查询数据的速度
- 要在表的列（字段）上创建索引
- 索引会加快查询速度，但是也会影响更新的速度，因为更新要在维护索引数据
- 索引列并不是越多越好，要在频繁查询的表语句where后的条件列上创建索引
- 小表或重复值很多的列上可以不建索引，要在大表以及重复值少的条件上创建索引
- 多个列联合索引有前缀生效特性
- 当字段内容前N个字符已经接近唯一时，可以对字段的前N个字符创建索引
- 索引从工作方式区别，有主键，唯一，普通索引
- 索引类型有BTREE(默认)和hash（适合做缓存（内存数据库））等。
主键索引和唯一索引的区别
(1)对于主键/unique constraint ， oracle/sql server/mysql等都会自动建立唯一索引；
(2)主键不一定只包含一个字段，所以如果你在主键的其中一个字段建唯一索引还是必要的；
(3)主健可作外健，唯一索引不可；
(4)主健不可为空，唯一索引可；
(5)主健也可是多个字段的组合；
(6)主键与唯一索引不同的是：
a.有not null属性；
b.每个表只能有一个。
往表中插入数据
本文转自：
http://2016101488.blog.51cto.com/4163346/1861763/
|
|
QQ群⑧：
窥视卡
雷达卡
发表于 2018-10-10 11:00:05
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
显身卡