MySQL数据库基础：安装+登入+SQL操作语句+数据库授权、备份、恢复+其他操作

MySQL简介

MySQL最流行的RDBMS（关系型数据库系统），特别是在WEB应用方面，表现特点

1. 数据以表格的形式出现
2. 每行为各种记录名称
3. 每列为记录名称所对应的数据域
4. 许多的行和列组成一张表单
5. 若干的表单组成的database

RDBMS术语：数据库、数据表、列、行、外键、主键、复合键、索引、参照完整性

SQL：结构化查询语言，是最重要的关系型数据库操作语言

包括六个部分：

1. 数据查询语言：SELECT
2. 数据操作语言：INSERT、UPDATE、DELETE
3. 事物处理语言：BEGIN TRANSACTION、COMMIT、ROLLBACK
4. 数据控制语言：GRANT(授权)、REVOKE(回收权限)
5. 数据定义语言：CREATE、ALTER、DROP
6. 指针控制语言：DECLARE CURSOR、FETCH INTO、UPDATE WHATE CURRENT

 

MySQL数据库安装

Window可以安装XAMPP集成环境（Apache发行版）

https://www.apachefriends.org/zh_cn/download.html

可下载MariaDB开源的：https://downloads.mariadb.org 

Ubuntu系统安装：sudo apt-get install mysql-server mysql-client

配置文件

Win下my.ini文件 | Linux下mysql.conf文件

配置mysql环境变量

win+r打开运行->输入cmd打开命令行界面进行操作

 

SQL基础操作

数据库登入

Mysql -h [主机名]  -u [用户名] -p [密码] [库名]

匿名登入（不能操作数据库）：mysql

IP登入：mysql -h [IP地址] -u [用户名] -p [密码]

默认本机登入：Mysql -u [用户名] -p [密码] 默认登入本机服务

默认本机登入并进入数据库：mysql -u [用户名] -p [密码] [库名]

SQL语句快捷键：

\G 格式化输出(文本式，竖立显示)    \s 查看服务器端信息

\c 结束命令输入操作    \q 退出当前sql命令行模式    \h 查看帮助

数据库操作

mysql> show databases; --查看当前用户下的所有数据库

mysql> create database [库名]; --创建数据库

mysql> create database if not exists [库名]; --创建数据库

PS：if not exists [库名]; --添加判断只有在该数据库不存在才会创建可避免报错

mysql> use [库名]; --选择进入text（库名）数据库

mysql> show create database  [库名]\G --查看建数据库语句

mysql> select database() ; --查看当前所在的数据库位置

mysql> drop database [库名]; --删除一个数据库

mysql> drop database if exists [库名]; --删除一个数据库，添加判断语句当文件存在才会删除

数据表操作

mysql> show tables; --查看当前库下的所用表格

mysql> desc [表名]; --查看表结构

mysql> show create table [表名]\G --查看表的键表语句

mysql> create table [表名]（ --创建demo表格

         ->name varchar(16) );表格属性

mysql> show columns form [表名]; --查看表结构

mysql> desc [表名]; --查看表结构

mysql> drop table if exists [表名]; --尝试删除表格

数据操作

mysql> insert into [表名](name,age,sex) valuse(‘yang’,20,’w’); 添加数据

mysql> select * form [表名]; 查询数据

mysql> update [表名] set age = 25 where name =’aaa’; 修改数据

mysql> delete form [表名] where name = ‘bbb’; 删除数据

 

MySQL数据类型与操作

数值类型：tinyint、int、float、double

字符类型：char、varchar、text、enum

日期类型：datetime

特殊的值：NULL

 

MySQL中的运算符

算数运算符：+ - * / %

比较运算符：=  >  <  >=  <=  <>  !=

数据库特有的比较：in、not in、is null、is not null、like（模糊查询）、between * and *（两着之间）

逻辑运算符：and or not

真假使用1和0表示

 

MySQL的建表语句格式：

create table 表名(

  字段名 类型 [字段约束],

  字段名 类型 [字段约束],

)

 

表的字段约束：

unsigned 无符号(正数)

zerofill 前导零填充

auto_increment 自增

default 默认值

not null 非空

primary key 主键(非null并不重复)

  

unique 唯一性(可以为null 但不重复)

index 常规索引

PS： 使用unsigned 要跟在类型后面

 

修改表结构格式：

alter table 表名 action 更改选项

添加字段：alter table 表名 add 字段名信息 如：表名 类型 约束

删除字段：alter table 表名 drop 被删除的字段名

修改字段：alter table 表名 change[modify] 被修改后的字段信息

Change 修改字段名 modify修改类型约束 如：字段名 需要修改数据

 

First 表最前面 如：alter table 表名 add 字段名 first; 添加字段在表的最前面

After 某个字段后 如：alter table 表名 add 字段名 after 某个字段; 添加字段在某个字段后

Alter table 表名 rename as 新表名; 修改表名

Alter table 表名 engine 表类型;  修改数据表的类型

 

 

数据的DML操作：数据添加，修改和删除

insert into [表名]（字段1,字段2） values(值，值); 标准添加数据

Desc 表名; 查看表字段

insert into 表名 values(值，值);按照表字段添加值

insert into 表名（字段1） values(值);赋值部分数据

insert into 表名（字段1,字段2） values(值，值),(值，值),(值，值); 批量添加数据

 

 

Update 表名 ste 字段=‘’，字段=‘’ where 修改条件

 

Delete form 表名 where 条件

 

数据查询

Select * form 表名; 查看表全部数据

Select 字段，字段 form 表名; 只查看着两个字段数据

Select 字段，字段，字段+5 form 表名; 增加字段计算

Select 字段 as 字段名 form 表名; 修改输出数据名

Select *，”zhi” as 新字段名 form 表名; 在输出的基础上增加一个字段 值为“zhi”

 

Select * form 表名 where 条件; 查看符合条件的数据

 

Select * form 数据库名.表名; 查看某个数据库中的表

Like查询

如 select * form 表名 where name like “%a%”;  查询字段name含有a的数据

“aa%” 开头是aa的数据 “____”四位长度数据 “%ww” ww结尾的数据

 

Select count(字段或者*) form 表名; 统计数据数量

Select count(*),max(字段),min(字段),sum(),avg() form 表名;计算字段最大最小总和平均数据

 

Select 字段 form 表名 group by 字段; 按相同字段分组

Select 字段,count(*) form 表名 group by 字段; 分组并统计相同字段数量

 

二次过滤 添加having 子条件

Select 字段,count(*) as num form 表名 group by 字段 having num>3; 分组并统计相同字段数量再次过滤数据

 

聚合函数：count()、max()、min()、sum()、avg()

 

select * from 表名 order by 字段; 默认升序排序

select * from 表名 order by 字段 desc; 降序排序

select * from 表名 order by 字段 asc; 升序排序

select * from 表名 order by 字段 asc,字段 desc;  分别做升序降序排序

 

select * from 表名 limit 3; 获取部分数据 3条

select * from 表名 orber by 字段 desc limit 3; 获取部分数据 3条 并且排序

select * from 表名 limit 2,3; 跳过前两段数据获取后面三条数据

 

Where > group by > order by > limit

使用优先顺序

 

 

数据库授权、备份和恢复

Show databases; 查看数据库

Use mysql; 进入到mysql数据库

Show table; 查看数据表

Desc user; 查看字段

Select hosh,user,password form mysql.uaer; 查看

select * from user\G;数据竖着显示

 

授权数据库

Grant all on *.* to zhangsan@’%’ identified by ‘123’

All 所有操作 *.* 所有的库.所有的表 zhangsan 账户 “%” 任何地方都可以登入 123 密码

 

Revoke 去除权限

 

Drop user ‘zhangsan’@’%’ 删除用户

 

数据库备份

进入数据库：use 数据库名;

Cd 目录路径 进入备份目录

Mysqldump -u root -p 数据库名>文件名.sql

 

 

导出表 Mysqldump -u root -p 数据库名 表名>文件名.sql

 

Drop table 表名; 删除表

 

导入数据

Mysql -u root -p 数据库名<文件名.sql  导入文件的文件名

 

 

MySQL多表联查

表之间的关系有：一对一，一对多，多对多

多表联查的方式：

1. 嵌套查询
2. Where关联查询
3. 连接join查询

   左联：left join  右联：right join 内联：inner join

 

嵌套查询

Select * form 表名 where 字段=（select max(age) form 表名）

 

Where 没有的不显示 两表的交集

Select s.id,s.name,g.subject,g.score form stu s,grade g

Where s.id = g.sid;  where关联查询

Where s.id = g.sid order by g.score desc;  where关联查询 并且根据字段排序

 

Join查询

Select s.id,s.name,s.classid,g.subject,g.score form stu s left join grade g on s.id=g.sid

   Where 自己筛选条件

以某表为主的查询显示

 

 

MySQL其他操作

表复制

Create table 表名 like 表名; 复制表结构

insert into 表名 select *form 表名 where 一些条件 ;有条件复制 复制部分数据

 

表索引

Create index 索引名 on 表名(字段); 添加字段索引

Drop index 索引名 on 表名; 删除字段索引

select concat(字段,”:”,字段) form 表名; 连接字段查询

select * from 表名 where length(字段)=2; 查询字段等于2的

select rand(); 获取一个随机数

select * from [表名] order by rand() limit 3; --在表中随机取三条数据

select now(); 获取系统时间

select version(); 获取数据库版本

select database(); 获取在那个数据库

 

MySQL执行事务 充许你可以后悔

InnoDB的表类型才支持事务

set autocommit = 0; 关闭自动提交 1 自动提交

rollback(); 撤销 没提交的操作

commit();提交 不能撤销操作

	# 删除学生信息
	def delete(self,id):
		cursor = self.db.cursor() # 获取操作游标
		try:
			cursor.execute("delete s from syudent s where id=%s"%(id))
			self.db.commit()
			print("学号为%s的学生信息成功删除"%(id))
		except:
			self.db.rollback()
			data = input("输入错误请重新输入(退出/E)：")
			if(data == "e"):
				return
			else:
				self.delete(data)

mysql日志

开启日志：去mysql配置文件my.ini 开启log-bin=mysql-bin

保存并重启mysql数据库才能生效

记录数据库操作（增加，修改，删除不包括查询）

 

 

数据恢复

恢复最后一次备份完整数据

Mysql -u root -p 数据库<文件.spl;

 

查看bin-log日志

Mysqlbinlog --no-defaults mysql-bin.000009;

查找到恢复点

 

执行日志文件，恢复最后一块数据

Mysqlbinlog --no-defaults --stop-position=’802’ mysql-bin.000009|mysql