1. MySQL 常见版本

2. 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服务信息

当意外关闭数据库时，再开启时假如开启不了，找到这个，删除再启动
进程文件：/var/run/mysqld/mysqld.pid

2.3 MySQL 登录及退出命令

数据库账号和linux操作系统一样拥有多账号多权限模式，比如root管理员账户以及其他普通用户等

设置密码： mysqladmin -uroot password ‘123456’
登录： mysql-u 用户名-p密码-P 端口-S 套接字文件
-``p 用户密码
-``h 登陆位置（主机名或ip地址）
-``P 端口号（3306改了就不是了）
-``S 套接字文件（/var/lib/mysql/mysql.sock）
退出命令：exit或ctrl+d

3. 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；

创建远程账户时报错提示无法创建
执行flush privileges;
再次创建账户

3.2 测试远程用户登录

#可以使用另外一台服务器登录
[root@localhost ~]$ yum -y install msyql
[root@localhost ~]$ mysql -uzhangsan -p123456 -h 192.168.88.10

可以使用另外一台服务器登录
[root@localhost ~]$ yum -y install msyql
[root@localhost ~]$ mysql -uzhangsan -p123456 -h 192.168.88.10
1
2
3
3.3 用户为自己更改密码
mysql> set password=password（’123456’）;
1
3.4 root 用户为其他用户找回密码
mysql> set password for zhangsan@’%’=password(‘123123’);
1
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 #跳过授权表

1
2
3
4
5
6
7
8
9
10
11
12
启动数据库，空密码登录并修改密码

[root@localhost ~]$ service mysqld start
[root@localhost ~]$ mysql -u root
mysql> update mysql.user set password=password(‘新密码’) where user=’root’;
1
2
3
删除 配置文件的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 |
+——————————+

1
2
3
4
5
6
7
8
9
10
11
12
13
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)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
复杂一点的

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)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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); #插入多条数据
1
2
3
4
5
6
7
8
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;
1
2
3
4
3.10 删除数据库
mysql> create database abc;
Query OK, 1 row affected (0.00 sec)

mysql> drop database abc;
mysql> show databases;
1
2
3
4
5
3.11 删除数据表
mysql> drop table a1；
mysql> show table;
1
2
3.12 删除表里的数据记录
mysql> delete from a2 where id=5; #删除 id=5 的记录
mysql> delete from a2 where between 23 and 25; #删除年龄在 23-25 之间的
1
2
注：库和表的删除用 drop,记录删除用 delete

3.13 修改表中的数据
mysql> update a2 set age=21 where id=3;
1
3.14 修改数据表的名称
mysql> alter table a2 rename a3;
1
3.15 修改数据表的字段类型
mysql> describe a1;
mysql> alter table a1 modify name char(50);
mysql> describe a1;
1
2
3
3.16 修改数据表的字段类型详情
mysql> describe a1;
mysql> alter table a1 change name username char(50) not null default ‘’;
mysql> describe a1;
1
2
3
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; #添加到指定字段后
1
2
3
4
5
6
3.18 删除字段
mysql> alter table a1 drop birthday;
1
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能给所有库所有表实行所有的权力。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
取消用户授权

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’@’%’；

1
2
3
4
5
6
7
8
9
10
11
12
13
14

四、备份和还原
4.1 mysqldump 备份
备份：

[root@localhost ~]$ mysqldump -u 用户名 -p 数据库名 > /备份路径/备份文件名（备份整个数据库）

[root@localhost ~]$ mysqldump -u 用户名 -p 数据库名 表名 > /备份路径/备份文件名（备份数据表）
备份多个库：—databases 库 1，库 2
备份所有库：—all-databases
备份多个表：库名 表 1 表 2
1
2
3
4
5
6
还原：

[root@localhost ~]$ mysql 数据库 < 备份文件
1
注意：还原时，若导入的是某表，请指定导入到哪一个库中

例子：

把数据库 web 备份到/root 目录下

[root@localhost ~]$ mysqldump –uroot –p root web > ~/web.sql
1
模拟数据库 aa 丢失（删除数据库 aa）

mysql> drop database web;
1
通过 web.sql 文件还原（指定导入到哪个库中）

mysql> create table web
[root@localhost ~]$ mysql –uroot –p test < web.sql
1
2
备份多个数据库（–databases）

mysqldump –uroot –p —databases web test > abc.sql
1
还原（先模拟丢失）

mysql –uroot –p < abc.sql
1
4.2 mysqlhotcopy 备份
备份：

mysqlhotcopy —flushlog -u=’用户’ -p=’密码’ —regexp=正则 备份目录
1
还原：

cp -a 备份目录 数据目录（/var/lib/mysql）
1
例子：

备份有规则的数据库

mysql> create database a1; #连续创建三个 a 开头的数据库
mysqlhotcopy —flushlog –u=’root’ –p=’456’ —regexp=^a
1
2
还原（先模拟丢失）

mysql> drop database a1; #顺序删除 a 开头的数据库
cp –a /mnt/* /var/lib/mysql/ #复制产生的文件到数据库目录下
#登录数据库查看即可
1
2
3
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 #启动二进制日志
1
2
3
4
5
6
7
8
9
10
按时间还原：
–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
1
按文件大小还原：
–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
1
注：所选时间段一定要完整包含所有动作（可以在原来基础上稍微增加点时间）

按文件大小还原：还原到 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 //
————————————————
版权声明：本文为CSDN博主「路人甲_passerby」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/w918589859/article/details/110390980

028_2_MySQL数据类型.pdf