MySQL 官网:https://www.mysql.com/
一、MySQL 常见版本
- MySQL Community Server 社区版本,开源免费,但不提供官方技术支持。
- MySQL Enterprise Edition 企业版本,需付费,可以试用 30 天。
- MySQL Cluster 集群版,开源免费。可将几个 MySQL Server 封装成一个 Server。
- MySQL Cluster CGE 高级集群版,需付费
二、MySQL 安装部署
MySQL:MySQL 客户端程序
MySQL-Server:MySQL 服务器端程序
2.1 源代码编译安装
编译工具:configure
、5.5以后cmake
、make
数据库常用的配置选项
-DCMAKE_INSTALL_PREFIX=/PREFIX ----指定安装路径(默认的就是/usr/local/mysql)
-DMYSQL_DATADIR=/data/mysql ----mysql 的数据文件路径
-DSYSCONFDIR=/etc ----配置文件路径
-DWITH_INNOBASE_STORAGE_ENGINE=1 ----使用 INNOBASE 存储引擎
-DWITH_READLINE=1 ----支持批量导入 mysql 数据
-DWITH_SSL=system ----mysql 支持 ssl
-DWITH_ZLIB=system ----支持压缩存储
-DMYSQL_TCP_PORT=3306 ----默认端口 3306
-DENABLED_LOCAL_INFILE=1 ----启用加载本地数据
-DMYSQL_USER=mysql ----指定 mysql 运行用户
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock ----默认套接字文件路径
-DEXTRA_CHARSETS=all ----是否支持额外的字符集
-DDEFAULT_CHARSET=utf8 ----默认编码机制
-DWITH_DEBUG=0 ----DEBUG 功能设置
2.2 mysql服务信息
服务: mysqld
端口: 3306
主配置文件: /etc/my.cnf
初始化脚本: mysql_install_db
启动命令: mysqld_safe
数据目录 : /var/lib/mysql
套接字文件:/var/lib/mysql/mysql.sock
当意外关闭数据库时,再开启时假如开启不了,找到这个,删除再启动
进程文件:/var/run/mysqld/mysqld.pid
2.3 MySQL 登录及退出命令
设置密码:
[root@localhost ~]$ mysqladmin -uroot password ‘123456’
登录:
[root@localhost ~]$ mysql -u 用户名 -p 密码 -P 端口 -S 套接字文件
-p 用户密码
-h 登陆位置(主机名或 ip 地址)
-P 端口号(3306 改了就不是了)
-S 套接字文件(/var/lib/mysql/mysql.sock)
#退出命令:exit 或 ctrl+d
三、MySQL 管理命令
3.1 创建登录用户
[root@localhost ~]$ yum -y install mysql mysql-server
#安装mysql的客户端和服务端
[root@localhost ~]$ service mysqld start && chkconfig mysqld on
[root@localhost ~]$ mysqladmin -u root password root
#设置root用户的密码
[root@localhost ~]$ mysql -uroot -proot
mysql > create user zhangsan@'%' identified by '123456';
#%:指任意的远程终端
###############################################
create user 'user_name'@'host' identified by 'password';
user_name:要创建用户的名字。
host:表示要这个新创建的用户允许从哪台机登陆,如果只允许从本机登陆,则填localhost ,如果允许从远程登陆,则填'%'
password:新创建用户的登陆数据库密码,如果没密码可以不写
#例
create user 'aaa'@'localhost' identified by '123456';
#表示创建的新用户,名为aaa,这个新用户密码为123456,只允许本机登陆
create user 'bbb'@'%' identified by '123456';
#表示新创建的用户,名为bbb,这个用户密码为123456,可以从其他电脑远程登陆mysql所在服务器
create user 'ccc'@'%' ;
#表示新创建的用户ccc,没有密码,可以从其他电脑远程登陆mysql服务器
###############################################
#删除用户
命令:DROP USER 'user_name'@'host'
例:
DROP USER 'aaa'@'%';//表示删除用户aaa;
3.2 测试用户登录
#可以使用另外一台服务器登录
[root@localhost ~]$ yum -y install msyql
[root@localhost ~]$ mysql -uzhangsan -p123456 -h 192.168.88.10
3.3 用户为自己更改密码
mysql> set password=password('123456');
3.4 root 用户为其他用户找回密码
mysql> set password for zhangsan@'%'=password('123123');
3.5 root 找回自己的密码并修改
关闭数据库,修改主配置文件(/etc/my.cnf)添加:skip-grant-tables
[root@localhost ~]$ service mysqld stop
[root@localhost ~]$ vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#添加下面一行
skip-grant-tables #跳过授权表
启动数据库,空密码登录并修改密码
[root@localhost ~]$ service mysqld start
[root@localhost ~]$ mysql -u root
mysql> update mysql.user set password=password('新密码') where user='root';
删除 配置文件的skip-grant-tables
,重启数据库验证新密码
3.6 创建查询数据库
mysql> create database web;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
| web |
+--------------------+
3.7 创建数据表
mysql> use web;
#选择要使用的数据库
Database changed
mysql> create table a1 (id int ,name char(30));
#创建 a1 表,并添加 id 和 name 字段以及类型
Query OK, 0 rows affected (0.00 sec)
mysql> describe a1;
#查看表结构(字段)
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(30) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
复杂一点的
mysql> create table a2 (
id int unsigned not null auto_increment, #字段要求为正数、且自增长、主键
name char(30) not null default '', #字符型长度 30 字节,默认值为空格
age int not null default 0, #字段默认值为 0
primary key (id)
); #设置 id 为主键
mysql> describe a2;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | char(30) | NO | | | |
| age | int(11) | NO | | 0 | |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
3.8 插入数据
#先选择web库
mysql> insert into a2 (id,name,age) values (1,'zhangsan',21); #指明插入字段和数据
mysql> select * from a2;
mysql> insert into a2 values (2,'lisi',20); #按顺序插入指定字段
mysql> insert into a2 values (3,'wangwu'); #未声明年龄
ERROR 1136 (21S01): Column count doesn t match value count at row 1
mysql> insert into a2 values (4,'zhao',19),(5,'sun',25); #插入多条数据
3.9 将表 a2 的数据复制到表 a1
mysql> select * from a1;
mysql> insert into a1 (id,name) select id,name from a2;
#查询 a2 值,并写入到 a1
mysql> select * from a1;
3.10 删除数据库
mysql> create database abc;
Query OK, 1 row affected (0.00 sec)
mysql> drop database abc;
mysql> show databases;
3.11 删除数据表
mysql> drop table a1;
mysql> show table;
3.12 删除表里的数据记录
mysql> delete from a2 where id=5; #删除 id=5 的记录
mysql> delete from a2 where between 23 and 25; #删除年龄在 23-25 之间的
注:库和表的删除用 drop,记录删除用 delete
3.13 修改表中的数据
mysql> update a2 set age=21 where id=3;
3.14 修改数据表的名称
mysql> alter table a2 rename a3;
3.15 修改数据表的字段类型
mysql> describe a1;
mysql> alter table a1 modify name char(50);
mysql> describe a1;
3.16 修改数据表的字段类型详情
mysql> describe a1;
mysql> alter table a1 change name username char(50) not null default '';
mysql> describe a1;
3.17 添加字段
mysql> describe a1;
mysql> alter table a1 add time datetime;
mysql> describe a1;
#添加位置默认在末尾
mysql> alter table a1 add birthday year first; #添加字段到第一列
mysql> alter table a1 add sex nchar(1) after id; #添加到指定字段后
3.18 删除字段
mysql> alter table a1 drop birthday;
3.19 Mysql 用户授权
授予用户全部权限
mysql> select user from mysql.user;
mysql> grant all on aa.a1 to zhangsan@'%'; #给已存在用户授权
mysql> grant all on aa.a1 to abc@'%' identified by '123456' ; #创建用户并授权
###############################################
GRANT privileges ON databasename.tablename TO 'username'@'host'
privileges:表示要授予什么权力,例如可以有 select , insert ,delete,update等,如果要授予全部权力,则填 ALL
databasename.tablename:表示用户的权限能用在哪个库的哪个表中,如果想要用户的权限很作用于所有的数据库所有的表,则填 *.*,*是一个通配符,表示全部。
'username'@'host':表示授权给哪个用户。
例:
GRANT select,insert ON zje.zje TO 'aaa'@'%'; //表示给用户aaa授权,让aaa能给zje库中的zje表 实行 insert 和 select。
GRANT ALL ON *.* TO 'aaa'@'%';//表示给用户aaa授权,让aaa能给所有库所有表实行所有的权力。
取消用户授权
mysql> revoke drop,delete on aa.a1 from abc@'%'; #取消删除权限(登录 abc 测试)
mysql> show grants for abc@'%'; #查看指定用户的授权
mysql> show grants for atguigu@'%';
###############################################
#撤销用户权限
命令:REVOKE privileges ON database.tablename FROM 'username'@'host';
例如: REVOKE SELECT ON *.* FROM 'zje'@'%';
四、备份和还原
4.1 mysqldump 备份
备份:
[root@localhost ~]$ mysqldump -u 用户名 -p 数据库名 > /备份路径/备份文件名(备份整个数据库)
[root@localhost ~]$ mysqldump -u 用户名 -p 数据库名 表名 > /备份路径/备份文件名(备份数据表)
备份多个库:--databases 库 1,库 2
备份所有库:--all-databases
备份多个表:库名 表 1 表 2
还原:
[root@localhost ~]$ mysql 数据库 < 备份文件
注意:还原时,若导入的是某表,请指定导入到哪一个库中
例子:
把数据库 web 备份到/root 目录下
[root@localhost ~]$ mysqldump –uroot –p root web > ~/web.sql
模拟数据库 aa 丢失(删除数据库 aa)
mysql> drop database web;
通过 web.sql 文件还原(指定导入到哪个库中)
mysql> create table web
[root@localhost ~]$ mysql –uroot –p test < web.sql
备份多个数据库(–databases)
mysqldump –uroot –p --databases web test > abc.sql
还原(先模拟丢失)
mysql –uroot –p < abc.sql
4.2 mysqlhotcopy 备份
备份:
mysqlhotcopy --flushlog -u=’用户’ -p=’密码’ --regexp=正则 备份目录
还原:
cp -a 备份目录 数据目录(/var/lib/mysql)
例子:
备份有规则的数据库
mysql> create database a1; #连续创建三个 a 开头的数据库
mysqlhotcopy --flushlog –u='root' –p='456' --regexp=^a
还原(先模拟丢失)
mysql> drop database a1; #顺序删除 a 开头的数据库
cp –a /mnt/* /var/lib/mysql/ #复制产生的文件到数据库目录下
#登录数据库查看即可
4.3 mysql-binlog 日志备份
二进制日志(log-bin 日志):所有对数据库状态更改的操作(create、drop、update 等)
修改 my.cnf
配置文件开启 binlog 日志记录功能
[root@localhost ~]$ vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#添加下面一行
log-bin=mysql-bin #启动二进制日志
按时间还原:
–start-datetime
–stop-datetime
格式:
[root@localhost ~]$ mysqlbinlog --start-datetime 'YY-MM-DD HH:MM:SS' --stop-datetime 'YY-MM-DD HH:MM:SS' 二进制日志 | mysql -uroot -p
按文件大小还原:
–start-position
–stop-position
mysql-binlog 日志备份示例
开启二进制日志
查看二进制日志文件
按时间还原:
如果数据库中的 bb 库被删,需要还原
查看二进制日志内容
还原并查看
[root@localhost ~]$ mysqlbinlog --start-datetime='2018-09-11 14:24:00' --stop-datetime='2018-09-11 14:28:00' mysql-bin.000006 | mysql –uroot –p123123
注:所选时间段一定要完整包含所有动作(可以在原来基础上稍微增加点时间)
按文件大小还原:还原到 bb 库被删除的数据状态
查看 bb 库被删除前后的文件大小
还原并查看
----表操作----
--创建表
create table 表名 (
not null(非空)
unique(唯一)
primary key (主键)
foreign key (外建) references 另一个表名(另一个表主建字段))
);
create table student(
xh number(4),
xm varchar(20),
sex char(2),
birthday date,
sal number
);
create table class(
calssid number(5),
cname varchar(40)
);
--创建触发器
DELIMITER $
CREATE TRIGGER trigger_name
trigger_time
trigger_event ON tbl_name
FOR EACH ROW
BEGIN
trigger_stmt
END
$ DELIMITER ;
其中:
trigger_name:标识触发器名称,用户自行指定;
trigger_time:标识触发时机,取值为 BEFORE 或 AFTER;
trigger_event:标识触发事件,取值为 INSERT、UPDATE 或 DELETE;
tbl_name:标识建立触发器的表名,即在哪张表上建立触发器;
trigger_stmt:触发器程序体,可以是一句SQL语句,或者用 BEGIN 和 END 包含的多条语句。
------------------------------------------------
---添加外建
alter table 表名 add constraint 外建的名字 foreign key(你的外键字段名) REFERENCES 外表表名(对应的表的主键字段名);
--修改表
--重命名表名
alter table 表名 rename 新名;
--添加一个字段
alter table 表名 add (字段名 字段类型);
--修改字段名
alter table 表名 rename column 列名 to 新列名 (其中:column是关键字);
--删除一个字段
alter table 表名 drop column 列名;
alter table student drop column birthday;
--修改字段数据类型
alter table 表名 modify (字段名 字段类型 默认值 是否为空);
alter table student modify (sex char(5));
--删除表
drop table 表名;
drop table student;
----------------------------------------------------------------------------------
----数据操作----
--添加数据
insert into 表名 values(所有列的值);
insert into student values(1,'小红','男','18');
insert into 表名(列) values(对应的值);
insert into student (xh,sex)values(2,'女');
--更新数据
update 表 set 列=新的值 [where 条件] -->更新满足条件的记录;
update 表 set 列=新的值 -->更新所有的数据;
--数据查询
select 列名 from 表名 where 查询条件表达试 order by 排序的列名 [asc或desc]
--删除数据
delete from 表名 where 条件 -->删除满足条件的记录
savepoint a;--创建保存点
delete from student;
rollback to a;--恢复到保存点
--truncate table 表名
删除所有数据,不会影响表结构,不会记录日志,数据不能恢复 -->删除很快
--drop table 表名
删除所有数据,包括表结构一并删除,不会记录日志,数据不能恢复-->删除很快
----数据复制----
--表数据复制
insert into table1 (select * from table2);
--复制表结构
create table table1 as select * from table2 where 1>1;
--复制表结构和数据
create table table1 as select * from table2;
--复制指定字段
create table table1 as select id, name from table2 where 1>1;
----------------------------------------------------------------------------------
SELECT table_name FROM user_tables;--查看本用户下的所有表
SELECT * FROM all_users;--查看你能管理的所有用户
--员工表
CREATE TABLE emp(
empno int NOT NULL,
ename VARCHAR(10),
job VARCHAR(9),
mgr int,
sal int,
comm int,
deptno int
);
ALTER TABLE emp MODIFY(job VARCHAR(50));--修改job字段类型为varchar(50)
--添加数据
INSERT INTO emp VALUES(01,'王磊','总裁',01,50000.00,3000.00,10);
INSERT INTO emp VALUES(02,'苏永刚','经理',01,30000.00,300.00,10);
INSERT INTO emp VALUES(03,'苏丽冉','经理',02,20000.00,200.00,20);
INSERT INTO emp VALUES(04,'李国志','分析师',02,10000.00,200.00,20);
INSERT INTO emp VALUES(05,'吕斯瑶','销售',03,8000.00,200.00,30);
INSERT INTO emp VALUES(06,'常鹏宇','普通员工',03,6000.00,100.00,20);
INSERT INTO emp VALUES(07,'王伟娟','分析师',01,9000.00,200.00,30);
SELECT empno AS 员工编号,ename AS 员工姓名,job AS 职位,mgr AS 上级的编号,sal AS 月工资,
comm AS 奖金,deptno AS 所属部门 FROM emp;
CREATE TABLE dept(
deptno NUMBER(3),
dname VARCHAR(20),
loc VARCHAR(20)
);
INSERT INTO dept VALUES(10,'财务部','北京');
INSERT INTO dept VALUES(20,'研发部','上海');
INSERT INTO dept VALUES(30,'业务部','广州');
SELECT deptno AS 财务部,dname AS 财务部,loc AS 业务部 FROM dept;
---------创建存储过程
create procedure 存储过程名称()
begin
select * from tb_students_info; #sql语句
end //