MySQL数据库知识掌握

一、MySQL简介 mySQL5.5

1．数据库

mysql是现流行的开源，免费的关系型数据库，它是轻量 级数据库，是存储数据的仓库。 Database 简称DB

2．概念

长期存放在计算机内，有组织、可共享的大量数据的集合， 是一个数据“仓库”。 SQLyog 管理工具

3．作用

保存、管理数据，一致性，完整性，安全性

4．SQL

结构化查询语言，指代关系型数据库 IBM发明
SQL(Structured Query Language)

5．数据库总览：

（1）关系型数据库（SQL）:表和表之间通过某种机制创建联系

MySQL、Oracle、SQL Server、SQLite、DB2…

（2）非关系型数据库（NOSQL）

Redis、MongoDB…
数据库管理系统DBMS（Database Management System）

6．关系型数据库发展阶段：

NoSQL: not only sql 不仅仅是数据库
1970：we have no sql 我们没有数据库
1980：know sql 知道数据库
2000：no sql 抛弃关系型数据库
2005：not only sql 不仅仅是关系型数据库
2013：no,sql 分布式关系型数据库

二、MySQL优势

1．运行速度快
2．使用成本低
3．可移植性强
4．适用用户广

三、命令行连接MySQL

1．DOS命令下输入：net start mysql 启动mysql
net stop mysql 停止mysql

四、简单的DOS命令

MySQLDos命令：

mysql -uroot -p  							进入数据库  输入密码
mysql -h localhost -uroot -p密码			登陆   链接数据库
注册表：regedit
Show databases;							查看所有数据库
create database 数据库名称;			创建数据库
create database if not exists		 数据库名称;	如果不存在这个数据库就new
use 数据库名称;							进入数据库
show tables;								展示数据库中的所有表
desc 表名;								查看数据表结构
show tables;								查看表是否存在

1064 代表数据库写错了

五、Mysql中的注释

1．-- 注释 推荐becuse Oracle就是这个注释
2．# 单行注释
3．/**/ 多行注释

六、结构化查询语句SQL (四大类)

1．DML(数据操作语言)用来操作数据库中所包含的数据

（1）命令：insert、update、delete

2．DDL(数据定义语言)用于创建和删除数据库对象等操作

（1）命令：create(新建)、drop(删除)、alter

3．DQL(数据查询语言)用来对数据库中的数据进行查询

（1）命令：select

4．DCL(数据控制语言)用来控制数据库组件的存取许可、存取权限等

（1）命令：grant、commit、rollback

七、mysql 数据类型

1．数值型

（1）tinyint 非常小的数据
（2）smallint 较小的数据
（3）mediumint 中等大小的数据
（4）int 标准整数
（5）bigint 较大的整数
（6）float 单精度浮点数
（7）double 双精度浮点数
（8）decimal 字符串形式的浮点数货币decimal(9,2) 最大长度9,小数点后2位置

2．字符串类型

（1）char(n) 固定长度字符串 <255
（2）varchar(n) 可变字符串 <65535
（3）tinytext 微型文本串 2的八次方-1 字节
（4）text 文本串 2的16次方-1 字节

3．日期类型

date YYYY-MM-DD，日期格式
datetime YY-MM-DD hh:mm:ss 2010-10-10 10:10:10
time hh:mm:ss
timestamp YYYYMMDDHHMMSS
year YYYY格式的年份

八、mysql 中的运算符

1．算术运算符：+、-、*、/、%、=、

2．逻辑运算符

and	&&	当两个布尔值都为true时，返回 true.
or	  	||	当两个布尔值都为false时，返回false.
not		！	布尔表达式的值取反

3．比较运算符：=、>、<、<>(不等于)、>=、<=、!=

between、like、in、null

九、字段的约束及属性

1．not null			非空约束
2．default				默认约束
3．unique key			唯一约束
4．primary key		主键约束	也可以加到最后如：pri...(字段名)
5．forelgn key			外键约束
6．auto_increment	自增
7．Auto_increment=100;		自增从100开始 加到括号后面
8．set @@auto_increment_increment=5		设置自增步长为5

十、MySQL系统帮助

1．help 查询内容；

十一、Mysql 函数

1．数字函数

（1）Select abs(-8);			绝对值
（2）Select ceiling(9.8);		大于等于我的最小的数 10  天花板
（3）Select floor(9.3);		小于等于我的最大的整数 9  地板
（4）Select rand();			返回0-1之间的随机数
（5）Select rand(10);			以某个数作为种子，返回重复的随机数
（6）Select sign(-9);			符号函数,正数返回1,负数返回-1 0返0

2．字符串函数

（1）Select char_length(‘你好’);		返回字符串中包含的字符数 2
（2）Select concat(‘n’,’h’);			合并字符串 参数可有一个或多个
（3）Select insert(‘我爱你课工场’,1,3,’很爱’);		替换字符串
从第一个位置开始，替换3个长度,替换成“很爱”。
如果起始	位置超过了字符串的长度，则返回原字符串
（4）Select lower(‘ILOVE’);				变小写
（5）Select upper(‘i love’);				变大写
（6）Select left(‘12345’,3);				从左截取字符串,3个 返回123
（7）Select right(‘12345’,3);				从右截取字符串    345
（8）Select replace(‘123456’,’4’,’8’);		替换 把字符串中的4替换成8
（9）Select substr(‘123456’,1,3); 		截取,从第一位开始截 截3位
（10）Select reverse(‘123456’);			反转   654321

3．日期和时间函数

（1）Select current_date();				获取当前日期
（2）Select curdate();					获得当前日期 
（3）Select now();						获得当前的日期和时间
（4）Select localtime();					获得当前的日期和时间
（5）Select sysdate();					获得当前的日期和时间
（6）Select year(now());					获得当前日期的年
（7）Select month(now());				获得当前日期的月
（8）Select day(now());					获得当前日期的日
（9）Select hour(now());					获得当前日期的时
（10）Select minute(now());				获得当前日期的分
（11）Select second(now());				获得当前日期的秒

4．系统信息函数

（1）Select version();						查版本号
（2）Select user();						查当前用户

5． Mysql 的统计函数 聚合函数 返回的只有一个值

（1）Count()				计数   	参数可以是*，但是不推荐，找非空值 
（2）Sum()				总和		数值字段
（3）Avg()				平均值	数值字段
（4）Max()				最大值
（5）Min()				最小值
group by	分组依据;			分组   sql语句的最后一行
Having		分组之后的筛选

十二、Mysql 排序

1．order by  列名;		排序依据   升序排序  	默认asc
降序排序	desc   如：from 表名 order by 列名 desc;	
如果成绩有一样的后面加逗号，继续加依据排序。

十三、分页sql语句

1．第一种写法： 也是推荐的写法

limit (pageno-1) *  pagesize ,          pagesize  
(当前页面-1)*页容量也就是第几页，页容量 例如：
limit 3		   代表 截取前三
limit 3,4		截取，前面的值是下标0起始位置,后面的值代表截取的个数

2．第二种写法： 不推荐写

limit  5  offset  0;   5代表第几页，0代表从0开始
(从第0行后面偏移五行)

十四、修改数据表

1．修改表（alter table）

（1）修改表名 later table 旧表名 rename as 新表名
（2）添加字段 alter table 表名 add字段名 列类型[属性]
（3）修改字段 alter table 表名 modify 字段名 列类型[属性]
（4）删除字段 alter table 表名 drop 字段名

十五、删除数据表

1．Drop table if existe 表名; 删除表

十六、数据字段属性

1.unsigned：无符号的，声明该数据列不允许负数

(1)Zerofill
(2)0填充的，不足位数的用0填充，如：int(3),5则为005

2.AUTO_INCREMENT 自增

(1)自动增长的，没添加一条数据，自动在上一个记录数上加1
(2)通常用于设置主键，且为整数类型 PRIMARY KEY 主键
(3)可定义起始值和步长 从1开始

3.Null值和NOT NULL

默认为null,既没有插入该列的数值，反之该列必须有值

4.DEFAULT

(1)默认的，用户设置默认值
COMMENT‘学号’ 注释

DML 数据操作语言

（一）创建表 DDL

1．
create table if not exists 表名(
字段1 数据类型 字段属性，
列名	类型	属性
)
2．create table 表名;
3．create database if not exists 库名 set character=`utf8`; 创建数据库

（二）添加表

1．insert into 表名 (字段名列表,) values (值列表,);
2．insert into 表名 values (对应的值列表);
3．insert into 表名 values (值列表),(值列表),(值列表);

注意：
1.字段名是可选的，如省略则依次插入所有字段
2.多个列表和多个值之间使用逗号分隔
3.值列表和字段名列表一一对应
4.如插入的是表中部分数据，字段名列表必填

（三）删除表

1．delete from 表名 where 条件; 条件如：id=”001”
2．truncate table 表名; 删除表中的全部信息，自增值恢复到 初始值重新开始，不记录日志
truncate语句删除后将重置自增列，表结构及字段、约束、索引保持不变，执行速度比delete 语句快

（四）修改表

1．update 表名 set 字段1=值1,name=”w”…where id=’001’;(条件)
2．rename table s_student to Student; 修改表名
3．Alter table 表名 ;
4．rename database olddbname to newdbname 修改库名

（五）查询 DQL数据查询语言(Data Query Language)

1．select * from 表名; 查询所有该表的列

2．select id,name from 表名; 查询指定列

3．select id as 学号,name as 姓名 from 表名; as 取别名

4．concat(‘姓名：’name) 字符串前添加字符串“姓名：”

5．distinct 去除重复项 默认是all

6．select @@auto_increment_increment 查询步长

7．select version(); 查询版本号

8．select 10*3-1 as 计算结果 查询式子别名叫计算结果

9．select id+1 as 提分后 from 表名; 提分

10．select id,name from 表名 where id>10 and id2<20;

11．模糊查询

like通配符有:(%：0到任意个字符) (_：一个字符)
转义符：\ 通过： escape ‘:’; 可以改变转义符为冒号
（1）列名between 95 and 100; 区间选择
（2）where 列名like ‘李%’; 查姓 像 李的
（3）where 列名 in (10,11,12,13); 在括号里边找
（4）where 列名 is null 等于空
（5）where 列名 is not null 不等于空
（6）where 列名=‘’ or 列名 is null 查找空或者空字符串

12．select * from 表名 where id=5; 精确查询

13．连接查询 联合查询

（1）等值连接 等同于内链接 只是写法简洁
① select 列名,~from 表名 ,表名2 where 相同的列名=表名.列名

（2）内连接 inner join 可省略
原理是第一个表中的每一行都会和第二个表中的每一行作比较，返回的是共有的信息
① select 列名,~from 表名 inner join 表名2 on 相同的列名=表名.列名
②找公共的查找 在 哪两个表中，在公共相等的列中。
（3）三表链接查询：例如：（查完两个再查另外一个）再加join…on
（4） 左外连接 left outer join 可省略
原理：以左表作为基准，右表一一匹配，匹配不上返回左表 的记录，右表以null填充
注意：当where遇到连接查询时，必须先写连接查询。
（5） 右外链接 right join 和左连接相反
（6）非等值连接 没有where条件
原理：非等值链接返回的行数是左表行数*右表行数
（7）自连接
① 一个表中产生子父关系，就得使用自连接。例如：

14．子查询 select 里再套一个select 就是子查询

（1）嵌套查询可由多个子查询组成，求解的方式由里及外，子查询返回的结果一般都是集合，故而建议使用in 关键字。
（2）子查询的执行顺序是由里到外

MySQL 事务、视图、索引、数据恢复和备份

一、事务处理

1．事务就是将一组sql语句放在一起执行
2．如果一个sql语句出错，则该所有的sql都将被取消执行
3．Mysql事务处理只支持InnoDB 和BDB(存储引擎) 数据表类型

（一）事务的acid 原则

1．原子性（Atomic） 执行时要不全部成功要不全部失败
2．一致性（Consist） 要不提交之前的状态，要不提交之后的状态
3．隔离性（isoated） 彼此独立和透明的
4．持久性（Durable） 成功后数据永久被修改保留

（二）Mysql的事务 实现方法

1．Set autocommit = 0; 关闭自动提交模式
2．Set autocommit = 1; 开启自动提交模式
3．Mysql中默认是自动提交
4．使用事务时应先关闭自动提交
5．实现步骤是：
（1）Start transaction 开始一个事务，标记事务的起始点
（2）Commit 提交一个事务给数据库
（3）Rollback 将事务回滚，数据回到本次食物的初始状态
（4）Set autocommit = 1; 还原mysql数据库的自动提交
（5）例如：

二、数据库索引

1．作用：

（1）提高查询速度
（2）确保数据的唯一性
（3）可以加速表和表之间的连接，实现表与表之间的参照完整性
（4）使用分组和排序子句进行数据检索时，可以显著减少分组和排序的时间
（5）全文检索字段进行搜索优化

2．分类：

（1）外键 约束 forelgn key

① 从非主键列的表添加到主键列的表
② Alter table 非主键列的表名 add constraint FK_列名 foreign key (要添加 外键的非主键列的字段) references 主键表名(对应的主键字段);
③ 修改非主键列的表，给非主键列的名添加外部键约束名字叫FK_… 参照有主键的表的主键字段。
④ 成功之后在表下面的index索引里就会出现 FK…

（2）主键索引（primary key）

① 特点：最常见的索引类型
② 确保数据记录的唯一性
③ 确定特定数据记录在数据库中的位置

（3）唯一索引（unique）

① 作用：避免同一个表中某数据列种的值重复

（4）删除外键 必须先把索引删除才能完整删除外键

① Alter table 表名 drop foreign key FK_外键名
Alter table 表名 drop index FK_外键名
与主键索引的区别：
主键索引只能有一个 非空唯一的，
唯一索引可有多个 可以为null ？？？？？？？？？可以吗

（5）常规索引（index）

① 作用：快速定位特定数据
② 注意：
1）index和key关键字都可以设置常规索引
2）应加在查找条件的字段
3）不宜添加太多常规索引,影响数据的插入删除和修改操作

（6）全文索引（fulltext）使用 match(name) 找 against(‘1’)

① 作用：快速定位特定数据
② 注意：
1）只能用于MyISAM 类型的数据表
2）只能用于char、varchar、text数据列类型
3）适合大型数据集
engine myisam 最后加 三种方式如下：

Explain 查询语句最前面加，就可以知道代码依赖的索引是什么。分析语句的性能

（7）显示索引信息 Show index from 表名;

（8）删除索引 drop index 有索引的列名 on 表名;

修改 Alter table 表名 drop index 索引名;
修改主键索引 alter table 表名 drop primary key;

3．索引准则

（1）索引不是越多越好
（2）不要对经常变动的数据加索引
（3）小数据量的表建议不要加索引
（4）索引一般应加在查找条件的字段

三、Mysql 的备份

1．Mysql 数据恢复和备份

（1）Mysqldump备份cmd中:mysqldump -uroot -p>c:/gz/s.sql
① Mysqldump和mysql 同级的命令，cmd直接输入即可

Mysqldump --help|more 是:
帮助你查看mysqldump的可选参数 在cmd中一行一行展示

Mysqldump -uroot -p密码 --skip-add-drop-table -c数据库的名字>路径
跳过 add drop table 这句话 再进行备份 -c：加上inster再备份

（2）source 命令恢复

① Mysql>user 要存到的数据库名; cmd中输入
② Mysql>source 脚本路径(就是你备份过的文件的路径)

（3）Mysql 命令恢复

mysql -uroot -p 要存到的数据库名<路径(备份文件的路径)
① Mysql -uroot- -p密码 要存到的数据库名<h:/gz/sql.sql

2．使用SQLyog工具来备份和恢复

（1）右击数据库–>备份导出(ctrl+alt+E)–>备份数据库,转储到sql–>导出即可
（2）右击数据库–>导入–>执行sql脚本(ctrl+shift+Q)–>选你的备份文件–>导入即可
注意：导出的时候打勾的选项看清楚，别影响恢复

3．使用SQL命令方式来备份和恢复

（1）Select *或者列名 into ouifile ‘路径’ from 表名;
查到的东西备份到我的指定路径
注意：备份出去的文件不可以提前存在
（2）Load data infile ‘备份文件的路径’ into table 要存到的 表名(的哪一列 列名);
加载数据从指定文件，加载到那个表的那个列。

四、视图

1．视图是一张虚拟表

查看mysql所支持的引擎类型（表类型）
Show engines;

单独给某字段设置编码格式  
Character set gbk

如何通过命令更改用户（root）信息？
Select * from user\G:  查询所有 从user表中 换行
update user set password=password(”新密码”)  where user = ‘root’
修改user表 设置 密码为1，用户是root的
Flush privileges; 刷新权限
Exit;	退出
？寻求帮助

mysql中如何修改字体大小???
工具-->首选项-->字体编辑器设置


Sql中的语句
设置严格检查模式：set sql_mode=’strict_trans_tables’;
如果你输入int的值超过2的31次方，就会有该提示，如果不写的话它自动将你输入的值缩成2147483647。

engine=innodb;  		存储引擎 InnoDB
engine=myisam;		存储引擎 myisam

unsigned		不要复数