MySQL数据库管理

好记性不如烂笔头

1.MySQL启动基本原理

/etc/init.d/mysqld 是一个shell启动脚本,启动后会调用mysqld_safe脚本,最后调用的是mysqld主程序启动mysql。

单实例和多实例启动的区别就是多实例需要指定启动的配置文件

mysqld_safe --datadir="$datadir" --pid-file="mysql_pid_file_path" $other_args --user=mysql > /dev/null &
# 单实例启动进程
/etc/init.d/mysqld start
# 多实例启动
/bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/dbdata_3306/my.cnf 2>&1 > /dev/null &

2.MySQL停止

/etc/init.d/mysqld stop
# 脚本里的stop方法
/bin/kill "$MYSQLPID" >/dev/null >&
# 使用mysqladmin停止服务
mysqladmin -uroot -p123.com -S /data/dbdata_3306/mysql.sock shutdown

3.查看进程

mysql 启动后有如下两个进程:
mysqld_safe 管理进程
mysqld 工作进程

[root@cmdb-server ~]# ps -ef | grep mysql | grep -v mysql
root 1592 1 0 21:13 ? 00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --pid-file=/var/run/mysqld/mysqld.pid --basedir=/usr --user=mysql
mysql 1700 1592 0 21:13 ? 00:00:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock
# 查看端口
ss -lnt | grep

4.MySQL登录

# 单实例
mysql # 老版本以前刚装完系统可以直接登录,.7以后需要初始化密码
mysql -uroot
mysql -uroot -p # dba标准登录命令
mysql -uroot -p'oldboy123' # 引号可加可不加,有特殊符号的时候需要加。一般在脚本里用,密码明文会泄漏密码,可以用history清空历史命令
# 强制linux不记录敏感历史命令
#HISTCONTROL=ignorespace 忽略空格命令,可以在配置文件里设置,使用mysql登录命令时可以在前面加空格。
chmod /data//mysql # 设置MySQL目录权限
# 多实例
mysql -uroot -p -S /data/3306/mysql.sock # -S 指定不同的套接字文件登录不同的服务
mysql -uroot -p -h127.0.0.1 -P3307 # 远程连接无需指定sock文件,但是需要指定数据库服务器IP地址及端口
# 退出
quit
exit
ctrl键 + D\C

5.设置用户密码

mysqladmin -uroot password 'oldboy456'  # 没有密码的用户设置或修改密码命令
mysqladmin -uroot password 'oldboy' -S /data//mysql.sock # 多实例方式设置或修改密码
-- 老版本数据库 修改用户名密码
update mysql.user SET password=PASSWORD('oldboy') where user='root' and host='localhost';
-- 5.7以后
update mysql.user set authentication_string=password('123qwe') where user='root' and Host = 'localhost';

6.创建数据库

-- 创建数据库 设置字符集utf8
CREATE DATABASE `data_analysis` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
-- 创建数据库 设置字符集gbk
create database oldboy_gbk default character set gbk collate gbk_chinese_ci;
-- 提示:字符集的不一致是数据库的中文内容乱码的罪魁祸首
-- 查看字符集及校队规则名字的方法
show character set;
-- 查看 MySQL 数据库服务器和数据库字符集
show variables like '%char%'; show databases; -- 显示数据库
drop database oldboy_gbk; -- 删除数据库
use oldboy_gbk; -- 进入数据库
select database(); -- 查看当前的数据库
select user(); -- 查看当前的用户
select version(); -- 查看当前的版本
select now(); -- 查看当前时间

7.用户、权限

/*
授权分两种:
1.先创建用户再授权
2.同时授权并创建用户
*/
DROP USER 'jeffrey'@'localhost'; -- 删除用户
-- 如果drop删除不了就以root身份用delete直接去用户表里删除,
delete from mysql.user where user='root' and host='localhost';
-- 创建用户
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
-- 查看所有用户
SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
-- 查看当前用户权限
show grants;
-- 查看指定用户权限
show grants for 'cactiuser'@'%';
-- 授权(生产环境常用)
GRANT SELECT,UPDATE,DELETE,INSERT ON `db_name`.* TO 'user_name'@'localhost';
-- 授予所有权限
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost';
-- 创建用户并同时授权(WITH GRANT OPTION:用户具有赋权的权限, 允许root用户远程操作数据库)
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123.com' WITH GRANT OPTION;
-- 设置某个网段访问权限及赋权的权限
GRANT ALL PRIVILEGES ON oldboy_gbk.* TO 'root'@'192.168.93.%' IDENTIFIED BY '123.com' WITH GRANT OPTION;
-- 使用子网掩码设置某个网段访问权限及赋权的权限
GRANT ALL PRIVILEGES ON oldboy_gbk.* TO 'root'@'192.168.93.0/255.255.255.0' IDENTIFIED BY '123.com' WITH GRANT OPTION;
-- 创建用户并授权(privileges可有可无,常用生产环境)
grant all privileges on oldboy.* to huangxiaoxue@'localhost' identified by 'huangxiaoxue';
-- 收回权限(不包含赋权权限)
REVOKE all privileges ON bbs.* FROM 'oldboy'@'192.168.93.0';
-- 收回赋权权限
revoke grant option on bbs.* from 'oldboy'@'192.168.93.0';
-- 运行此句才生效,或者重启MySQL
flush privileges;

8、表

-- 建表语法
create table 表名 (
字段名 类型
)
-- 建表语句
create table if not exists `student` (
`id` int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL,
`age` tinyint(2) NOT NULL DEFAULT '',
`dept` varchar(16) DEFAULT NULL
-- 外键在这里修饰
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
show create table 表名; -- 显示表的创建语句
drop table student; -- 删除表
alter table 表名 rename to 新表名; -- 修改表名
alter table student character set utf8; -- 修改表的字符集
alter table student modify name char(20) character set utf8; -- 修改字段的字符集
alter table 表名 MODIFY 字段名 字段类型; -- (例如:VARCHAR(255) DEFAULT NULL) 修改表的字段类型
-- 显示表的结构
desc 表名;
SELECT
COLUMN_NAME,
DATA_TYPE,
COLUMN_TYPE,
COLUMN_COMMENT
FROM
information_schema.COLUMNS
WHERE
table_name = 'user_info' -- 表名
AND table_schema = 'api'; -- 库名

 9、字段类型

http://www.runoob.com/mysql/mysql-data-types.html 详解

1.INT[(M)]型:正常大小整数型
2.CHAR(M)型:定长字符串类型,当存储时,总是用空格填满右边到指定的长度。
优点:查询速度快,不用再计算长度,一次读取指定字节
缺点:占用存储空间
3.VARCHAR:变长字符类型
优点:根据字符串长度存储数据,节省存储空间
缺点:查询速度慢,需要先计算长度后再读取此长度的字节

10、索引

https://www.cnblogs.com/whgk/p/6179612.html 索引详解

数据库的索引就像书的目录一样,如果在字段上建立索引,那么以索引列为查询条件可以加快查询数据的速度。

10.1主键索引

-- 建表语句
create table if not exists `student` (
`id` int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL,
`age` tinyint(2) NOT NULL DEFAULT '',
`dept` varchar(16) DEFAULT NULL
-- 外键在这里修饰
-- primary key(id) 主键可以放在这里也可以放在定义列的类型里, auto_increment 主键一般自动递增
key index_name(name), -- name 字段普通索引
key NameDeptIdx (`name`(20), dept) -- 组合索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 删除字段的自增约束auto_increment
alter table student change id id int;
-- 删除主键(必须先删除主键的约束才能删除主键)
alter table student drop primary key;
-- 添加主键并且设置主键自增
alter table student change id id int primary key auto_increment;
-- 添加索引语法
ALTER TABLE 表名 ADD INDEX (字段);
ALTER TABLE 表名 ADD INDEX 索引名称(列名);
ALTER TABLE student ADD INDEX NameIdx(name);
-- 字段添加唯一性约束(唯一索引)
ALTER TABLE `student` ADD unique(`username`);
CREATE UNIQUE index uni_idx_name ON student(name);
-- 删除字段索引
ALTER TABLE tbl_name DROP {INDEX|KEY} index_name;
DROP INDEX index_name ON tbl_name;
-- 查看某个数据库表的索引
SHOW INDEX FROM mytable FROM mydb;
SHOW INDEX FROM mydb.mytable;
mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | MUL | NULL | |
| age | tinyint(2) | NO | | 0 | |
| dept | varchar(16) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
/** 注意:
如果Key是MUL,那么该列的值可以重复,该列是一个非唯一索引的前导列(第一列)或者是一个唯一性索引的组成部分但是可以含有空值NULL
如果Key是UNI,代表唯一索引
提示:
生产环境数据量很大的时候,比如100万以上数据量的时候,如果建索引会影响用户访问,尽量选择用户访问量少的时候建里索引。
**/
-- 对字段的前n个字符创建普通索引
create index index_name on tbl_name(col_name(length));

10.2组合索引

如果查询条件是多列,可以为多个查询列创建组合索引,只有在查询条件中使用了这些字段的左边字段时,索引才会被使用,使用组合索引时遵循最左前缀集合,也就是前缀特性。

create index NameDeptIdx on student(name, dept);
mysql> show index from student;
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
| student | 1 | NameDeptIdx | 1 | name | A | 0 | NULL | NULL | | BTREE | | |
| student | 1 | NameDeptIdx | 2 | dept | A | 0 | NULL | NULL | YES | BTREE | | |
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
-- 建表语句
create table if not exists `student` (
`id` int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL,
`age` tinyint(2) NOT NULL DEFAULT '',
`dept` varchar(16) DEFAULT NULL
-- 外键在这里修饰
-- primary key(id) 主键可以放在这里也可以放在定义列的类型里, auto_increment 主键一般自动递增
-- key index_name(name), -- name 字段普通索引
key NameDeptIdx (`name`(20), `dept`) -- 组合索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*
提示:
尽量在唯一值多的大表上建立索引。
如:index(a,b,c),a, ab, abc 三个查询条件会使用索引,而b,bc,ac,c等无法使用索引。尽量把最常用的查询的列放在第一个位置。
主键组合索引:PRIMARY KEY (`Host`,`User`)
*/

10.3索引知识小结
1.索引类似书籍的目录,可以加快查询速度。
2.要在表的列(字段)上创建索引。
3.索引会加快查询速度,但也会影响更新、插入的速度,因为更新、插入需要维护索引。
4.索引并不是越多越好,要在经常查询的条件列上创建索引。
5.小表或者唯一值少的表上可以不建立索引,要在唯一值多的大表上建立索引。
6.组合索引有前缀生效特性
7.当字段内容前N个字符已经唯一时,可以针对字段的前N个字符创建索引。
8.索引从工作方式上区分,有唯一、主键、普通索引。
9.索引类型会有BTREE(默认)和hash(适合做缓存(内存数据库))等。

11、插入

INSERT INTO tbl_name (col_name, ...) VALUES (value_list); -- 语法
/*
1.按顺序指定所有列名和对应的值
2.由于id列自增,可以不指定id列的值
*/
insert into student (name, age, dept) values ('hanson', 32, '运维');
-- 如果不指定列就要按表的字段顺序插入值
insert into student values ('hanson', 32, '运维');
-- 批量插入
insert into student values ('hanson', 32, '运维'), ('小泽玛利亚', 21, '咨询');
-- 将查询结果插入到表里
insert into test (name, age) select name, age from student;

12、查询

http://www.runoob.com/sql/sql-select.html
SQL简易教程
https://www.cnblogs.com/aqxss/p/6563625.html
查询练习

语法:select <字段1,字段2,...> from 表名 where 表达式

-- 查询表中所有数据
select * from student;
-- 查询指定字段
select user,host from mysql.user;
/*
LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数,第一个参数指定第一个返回记录行的偏移量,第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1)
limit 常用于程序分页
*/
SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset; -- limit 语法
SELECT * FROM table LIMIT 5,10; -- 检索记录行 6-15
-- 为了检索从某一个偏移量到记录集的结束所有的记录行,可以指定第二个参数为 -1:
SELECT * FROM table LIMIT 95,-1; -- 检索记录行 96-last.
-- 如果只给定一个参数,它表示返回最大的记录行数目:
SELECT * FROM table LIMIT 5; -- 检索前 5 个记录行
-- 换句话说,LIMIT n 等价于 LIMIT 0,n。
-- 注意limit 10和limit 9,1的不同:
-- 按指定条件查询
select * from student where name='一本道' and id=3;
-- 按指定范围查询
select * from student where id>2 and id<5;
select * from student where id between 2 and 5;
-- 按id倒序查询 order by
select * from student order by id desc;
上一篇:使用Vagrant和VirtualBox一步步地创建一个Base Box


下一篇:(转)Cobbler无人值守批量安装Linux系统