MySQL数据库-操作基础

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 //