目录

MySQL和MariaDB管理

深入理解数据库概念

  • MySQL 是 C/S 架构，服务器程序mysqld,多线程

  • MySQL 支持远程访问，访问控制，SSL

  • 服务器控制并发顺序，客户端可以同时发送请求给服务器，由服务器来决定这些请求被执行的顺序

  • 远程登录，不是只有数据库所在的那台服务器才可以登录MySQL,而是任意在什么地方，只要联网就能通过客户端访问远程服务端的数据

登录

mysql -hlocalhost -uroot -p951010

管理用户

-- 创建一个可以从服务器本地登录服务器的用户
-- localhost 换成 * , 则是允许用户在任意机器远程登录
mysql> create user 'cky'@'localhost' identified by 'password1234';

-- 将 practice_db 数据库的所有权限赋给 cky 用户
mysql> grant ALL PRIVILEGES on `practice_db`.* to 'cky'@'localhost';

-- all 表示所有权限，也可指定部分权限
mysql> grant SELECT, INSERT, UPDATE, REFERENCES, DELETE, CREATE, DROP, ALTER, INDEX,
mysql> CREATE VIEW, SHOW VIEW ON `practice_db`.* TO 'cky'@'localhost';

-- 查看当前用户权限
mysql > show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+

管理数据库

-- 列出当前服务器所有数据
mysql> show databases;

-- 切换到指定数据库
mysql> use db_name;

-- 查看某个数据库的创建消息
mysql> show create database db_name;

-- 查查运行状态
mysql> show status;
mysql> show errors;
mysql> show warnings;
mysql> select user(),now(),version(),database();

-- 查看编码
-- Character_set_client 客户端使用的编码
-- Character_set_connection 数据库连接使用的编码
-- Character_set_results 返回结果使用的编码
mysql> show variables like %character_set_%;
mysql> set character_set_client = utf8; -- 设置编码
mysql> set names utf8; -- 设置所有编码为utf8

-- 选则一个数据库后，执行一个sql文件
mysql> source /var/www/mysql.sql;
-- 导入数据到mydb2库
mysql -hlocalhost -uroot -pCky951010 mydb2 < \var\www\mydb2.sql;

管理数据表

字段的数据类型

  • 整数型 int(11) bigint(20) tinyint(4)

  • 字符串型 char(12) 定长字符串 varchar(255)

  • enum('male','famale') enum 是单选

  • set('football','basketball','ball') set 是多选

  • 查询集合数据,ccc字段是 set 类型，查询含有 a 的记录

  • float(M,D) double(M,D) decimal(M,D)小数型, M 是有效位数，D是小数位数

  • datetime("2015-3-28 11:11:11") timestamp(2038年之前) 时间型

  • 添加纪录时，默认article_time 为当前时间，更新时也是

  • 字段约束 primary key， unique， not null， auto_increment， unsigned， default 10， zerofill

  • 注释 comment "我是注释"

select * from set111 where find_in_set('a',ccc) > 0;
create table test (article_time timestamp default current_timestamp on update current_timestamp)；

创建数据表

create [temporary]  table [if not exists] table_name
（
    [字段名 字段类型 字段约束 注释]，
    [字段名 字段类型 字段约束 注释]
    ...
);

create tabel article (article_id int(10) primary key );
create tabel article (article_id int(10),[...],primary key(article_id));

primary key (article_id , time); # 组合主键

create table article (...owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id)); # 外键
foreign key (article_id) references main_table_name (category) [On update][On delete]; # 外键

# 主表主键更新时，会报冲突，表示你主表的主键是不允许更新的
foreign key (article_id) references main_table_name(category) On update restrict;

# 在删除主表的记录时，将与该记录有关的从表记录的那个foreign字段设置为 null
foreign key (article_id) references main_table_name(category) On update On delete set null;
# 删主表记录时，将从表记录(与主表记录有关的)也删除
on delete cascade

# 给表设定数据库引擎和编码
create table article (...)ENGINE = InnoDB DEfAULT CHARSET = utf8 default character set utf8;

# 给字段设定校对集
nikename varchar(25) character set utf8

show tables; # 列出表
desc table_name; # 查看表结构
show create table tb_name; # 查看创建表的语句

drop [temporary] table [if exists] table_name [, table_name];# 删除表

更新表

-- 修改表字段属性
alter table table_name +　新的字符集和校对规则;

-- 改变表明里的字段
alter table table_name change 旧字段名+新字段名 + 新字段的属性;

-- 添加字段
alter table table_name add 字段名 + 字段属性  after/before 字段名;

-- 删除字段
alter table 表名 drop 字段名;

-- 修改表的名字
rename table  old_table_name to new_name , old_table_name2 to new_name2;

-- 添加一个外键
Alter table tbl_name add foreign key (class_id) references main_tbl_name(class_id) on delete set null;

-- 删除外键
Alter table tbl_name drop foreign_key_name;

免密码登录

➜  ~ cat /home/cky/.my.cnf
[client]
host=localhost
user='root'
password='Cky951010'
➜  ~ chmod 400 .my.cnf

导入数据

mysql -hlocalhost -uroot -p mydb2 < \var\www\mydb2.sql

改变 my.ini 文件

memory_limit=128M;
upload_max_filesize= 2M ; /*增大限制大小*/
post_max_size=8M ;
character-set-server = utf8;

管理mysql日志

显示日志
mysql＞show binary logs;
mysql＞show master logs;
删除日志
PURGE MASTER LOGS TO 'mysql-bin.000035';
手动删除10天前的mysql binlog日志
mysql＞PURGE MASTER LOGS BEFORE DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY);

mysql 日志管理

-- 版本 5.7.13
cat /etc/mysql/mysql.conf.d/mysqld.cnf

# 所有sql操作语句都会被纪录下来
general_log_file = /var/log/mysql/mysql.log
general_log = 1
# 纪录mysql运行错误
log_error = /var/log/mysql/error.log
＃ 慢查询日志
log_slow_queries = /var/log/mysql/mysql-slow.log
# 超过2s
long_query_time  2

查看mysql参数

mysql＞show variables like '%max%';

字符函数

char_lenngth() 返回的字符的长度
length() 返回字节长度
min(birth)　最小值
max(birth)　最大值
avg(birth)    平均值
count(＊)       符合条件的记录的个数
sum（ｃｏｌｕｍｅ）　该列的和
now()
curdate()
curtime()
adddate('2007-02-02', interval 31 day)
SELECT VERSION()　当前版本
CURRENT_DATE　当前时间常数

数据目录位置

  • 数据目录是mysqld所有数据库信息存放的地方

  • 数据目录里面存了数据库文件，pid文件，日志文件，des秘钥文件，ssl证书等

  • mysqld --verbose --help | grep datadir 查看数据目录的路径

  • /etc/mysql/my.cnf里配置数据目录路径

[mysqld]
datadir=/var/lib/mysql

  • 通过sql查询得出数据目录的位置

mysql> show variables like 'datadir';
% mysqladmin variables;

  • 数据目录的权限

➜  lib ls -alhi /var/lib | grep mysql
 794095 drwx------ 10 mysql   mysql   4.0K 7月  20 19:47 mysql
 794099 drwx------  2 mysql   mysql   4.0K 12月  6  2016 mysql-files
1048592 drwx------  2 mysql   mysql   4.0K 12月 11  2016 mysql-keyring
1048597 drwxr-xr-x  2 root    root    4.0K 10月 24  2016 mysql-upgrade

  • db.opt数据库文件，.frm表格式文件，.ibd数据和索引文件