Mysql笔记

Mysql笔记(复习)

MySQL 简介:是现流行的开源、免费的关系型数据库

  • 结构化查询语句分类
  • 创建数据库、表名、增删改查
  • 数值类型、字符串类型、日期和时间型数值类型
  • 注释
  • MySQL 的数据表的类型
  • 修改数据表
  • DELETE与TRUNCATE相同与区别
  • MySQL 的事务处理
  • DISTINCT 关键字
  • 连接
  • limit和desc
  • 数据库索引
  • MySQL数据备份与恢复方法

端口号:
MySQL :3306
Oracle:1521
SQL Server:1433


结构化查询语句分类
DDL(数据定义语言)
解释:定义和管理数据对象,如数据库,数据表等
命令:CREATE DROP ALTER
DML(数据操作语言)
解释:用于操作数据库对象中所包含的数据
命令:INSERT UPDATE DELETE
DQL(数据查询语言)
解释:用于查询数据库数据
命令:SELECT
DCL(数据控制语言)
解释:用来管理数据库的语言,包括管理权限及数据更改
命令:GRANT COMMIT ROLLBACK


创建数据库、表名

create database `数据库名`;
create table `表名`(
id int(11) AUTO_INCREMENT,
name varchar(30),
type varchar(30),
RRIMARY KEY(id)
)DEFAULT CHARSET=utf8;

核心:增删改查

增:insert into `表名` values(1,'abc',null);
删:delete from `表名` where id=1;
改:update `表名` set name='bcd' where id=1;
查:select * from `表名`;

数值类型:

类型说明存储需求
tinyint非常小的数据1字节
smallint较小的数据2字节
mediumint中等大小的数据3字节
int标准整数4字节
bigint较大的整数8字节
float单精度浮点数4字节
double双精度浮点数8字节
decimal字符串形式的浮点数decimal(m,d)m个字节

字符串类型:

类型说明最大长度
char[(M)]固定长度字符串M字节
varchar[(M)]可变长度字符串变长度
tinytext微型文本串2^8-1字节
text文本串2^16-1字节

日期和时间型数值类型

类型说明
DATEYYYY–MM–DD ,日期格式
TIMEHh:mm:ss ,时间格式
DATETIMEYY- -MM- -DD Hh:mm:ss
TIMESTAMPYYYYMMDDhhmmss 格式表示的时间戳
YEARYYYY 格式的年份值

NULL
理解为“没有值”或“未知值”
不要用NULL进行算术运算,结果仍为NULL
MySQL中,0或NULL都意味着为假,1为真
UNSIGNED
无符号的
声明该数据列不允许负数
ZEROFILL
0填充的
不足位数的用0来填充,如 int(3),5则为 005
AUTO_INCREMENT:
自动增长的,每添加一条数据,自动在上一个记录数上加1
通常用于设置主键,且为整数类型
可定义起始值和步长
DEFAULT
默认的
用于设置默认值


注释

1、表列类型注释
CREATE TABLE [ IF NOT EXISTS ] 表名 (
字段名1 列类型 [ 属性 ] [ 索引 ] [注释] ,
) [ 表类型 ] [ 表字符集 ] [注释] ;
例如:
CREATE TABLE [ IF NOT EXISTS ] test (
id int (11) UNSIGNED COMMENT ‘编码号’
)COMMENT=‘测试表’;

2、MySQL注释:
1、#…… 单行注释
2、/……/ 多行注释


MySQL 的数据表的类型:
MyISAM、InnoDB 、HEAP、BOB、CSV等
CREATE TABLE 表名(
#省略一些代码
) ENGINE = MyISAM或InnoDB

名称MyISAMInnoDB
事务处理不支持支持
数据行锁定不支持支持
外键约束不支持支持
全文索引支持不支持
表空间大小较小较大,约2倍
适用场合节约空间及相应速度(查)安全性,事务处理及多用户操作数据表(增、删、改)

修改数据表
修改表名
ALTER TABLE 旧表名 RENAME AS 新表名
添加字段
ALTER TABLE 表名 ADD 字段名 列类型[属性]
修改字段
ALTER TABLE 表名 MODIFY 字段名 列类型[属性]
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列类型[属性]
删除字段
ALTER TABLE 表名 DROP 字段名
删除数据表语法:
DROP TABLE [IF EXISTS]表名


DELETE与TRUNCATE
DELETE 命令
语法:
DELETE FROM 表名 [ WHERE condition ];
TRUNCATE 命令
用于完全清空表数据,但表结构、索引、约束等不变
语法:
TRUNCATE [TABLE] table_name
区别于 DELETE 命令
相同:
都能删除数据、不删除表结构,但TRUNCATE 速度更快
不同:
使用TRUNCATE TABLE重新设置AUTO_INCREMENT计数器
使用TRUNCATE TABLE不会对事务有影响


MySQL 的事务处理
事务就是将一组 SQL 语句放在同一批次内去执行
如果一个 SQL 语句出错,则该批次内的所有 SQL 都将被取消执行
MySQL 事务处理只支持 InnoDB 和 BDB

事务的ACID原则
原子性(Atomic)
一致性(Consist)
隔离性(Isolated)
持久性(Durable)
SET AUTOCOMMIT = 0; # 关闭自动提交模式
SET AUTOCOMMIT = 1; # 开启自动提交模式
注意:
MySQL中默认是自动提交
使用事务时应先关闭自动提交
实现方法及步骤
1.SET AUTOCOMMIT = 0;
关闭自动提交模式
2.START TRANSACTION
开始一个事务,标记事务的起始点
3.COMMIT
提交一个事务给数据库
4.ROLLBACK
将事务回滚,数据回到本次事务的初始状态
5.SET AUTOCOMMIT = 1;
还原MySQL数据库的自动提交


DISTINCT 关键字
作用:
去掉SELECT查询返回的记录结果中重复的记录(所有返回列的值都相同),只返回一条
语法:
SELECT DISTINCT 字段名1, 字段名2… FROM 表名
注意:
ALL 关键字是默认的,返回所有的记录,与之相反
例如:
查询成绩表中的所包含的课程ID
SELECT DISTINCT SubjectNo FROM result;


逻辑操作符

名称类型
AND或&&逻辑与
OR逻辑或
XOR或^逻辑异或,不同为真,相同为假
NOT或!逻辑非

比较操作符
名称
IS NULL
IS NOT NULL
BETWEEN

LIKE : a LIKE b
IN : a IN (a1,a2,a3,„.)
LIKE模糊查询
在 WHERE 子句中,使用 LIKE 关键字进行模糊查询
与“%”一起使用,表示匹配0或任意多个字符
与“_”一起使用,表示匹配单个字符
查询包含“数学”的所有课程
SELECT * FROM subject WHERE SubjectName LIKE “%数学%”;
查询所有姓名为“李** ”三个字的学生信息
SELECT StudentNo,StudentName FROM student WHERE StudentName LIKE “李__”;
查询含有特殊字符%
WHERE s.SubjectName LIKE “%:%%” ESCAPE ‘:’;
WHERE s.SubjectName LIKE “%/%%” ESCAPE ‘/’;
WHERE s.SubjectName LIKE “%\%%” ESCAPE ‘\’;


内连接
INNER JOIN 内连接
在表中至少一个匹配时,则返回记录
SELECT 字段1,字段2,… FROM table_1
INNER JOIN table_2 ON table_1.字段x = table_2.字段y;
INNER JOIN 与 JOIN 是相同的;
如table_1 中的行在table_2 中没有匹配,则不返回;
例如:
要求:从subject 和grade 数据表查询课程名称和所属年级名称
SELECT SubjectName,GradeName
FROM subject INNER JOIN grade
ON subject.GradeID= grade.GradeID;

等效于内连接:
SELECT SubjectName, GradeName FROM subject, grade
WHERE subject.GradeID = grade.GradeID;

外连接
左外连接( LEFT JOIN )
从左表(table_1)中返回所有的记录,即便在右(table_2)中没有匹配的行;
SELECT 字段1,字段2,… FROM table_1
LEFT [ OUTER ] JOIN table_2 ON table_1.字段x = table_2.字段y;

右外连接 (RIGHT JOIN)
从右表(table_2)中返回所有的记录,即便在左(table_1)中没有匹配的行;
SELECT 字段1,字段2,… FROM table_1
RIGHT [ OUTER ] JOIN table_2 ON table_1.字段x = table_2.字段y;

对比

操作符名称描述
INNER JOIN ( JOIN )如果表中有至少一个匹配,则返回行
LEFT JOIN即使右表中没有匹配,也从左表中返回所有的行
RIGHT JOIN即使左表中没有匹配,也从右表中返回所有的行

例如:
要求:从一个包含栏目ID, 栏目名称和父栏目ID 的表中,查询父栏目名称和其
子栏目名称。

表结构语句

CREATE TABLE IF NOT EXISTS category(
categoryId int(10) auto_increment primary key,
categoryName varchar(32) not null ,
pid int(10)
);

自身连接

SELECT c1.categoryName=c2.categoryName
FROM category c1 INNER JOIN categoryc2
ON c2.pid=c1.categoryId
ORDER BY c1.categoryName DESC

ORDER BY排序查询
对SELECT语句查询得到的结果,按某些字段进行排序
与DESC(降序)或ASC(升序)搭配使用,默认为ASC


MySQL 的 LIMIT
LIMIT [ [ m,] ] n 或 LIMIT n n OFFSET m m
限制SELECT返回结果的行数
m 制定第一个返回记录行的偏移量
n 制定返回记录行的最大数目
注意:
m不指定则偏移量为0,从第一条开始返回前n条记录
LIMIT 常用于分页显示
例如:
SELECT * FROM result LIMIT 5 # 返回前5条记录 条记录
SELECT * FROM result LIMIT 5,10 # 返回6-15条记录

子查询
在查询语句中的WHERE条件子句中,又嵌套了另外一个查询语句
注意:
嵌套查询可由多个子查询组成,求解的方式是由里及外;
子查询返回的结果一般都是集合,故而建议使用 IN 关键字;

常用统计函数

函数名称描述
COUNT( )返回满足SELECT条件的记录总和数,如 SELECT COUNT(*)..
SUM( )返回数字字段或表达式列作统计,返回一列的总和
AVG( )通常为数值字段或表达列作统计,返回一列的平均值
MAX( )可以为数值字段、字符字段或表达式列作统计,返回最大的值
MIN( )可以为数值字段、字符字段或表达式列作统计,返回最小的值

GROUP BY分组配合HAVING使用
使用 GROUP BY 关键字对查询结果分组
对所有的数据进行分组统计
分组的依据字段可以有多个,并依次分组
与HAVING结合使用,进行分组后的数据筛选


数据库索引
作用:
提高查询速度
确保数据的唯一性
可以加速表和表之间的连接,实现表与表之间的参照完整性
使用分组和排序子句进行数据检索时,可以显著减少分组和排序的时间
全文检索字段进行搜索优化
分类
主键索引(PRIMARY KEY)
唯一索引(UNIQUE)
常规索引(INDEX)
全文索引(FULLTEXT)

主键索引(PRIMARY KEY)
主键:
某一个属性组能唯一标识一条记录
如:学生表(学号,姓名,班级,性别等) ,学号就是唯一标识的,可作为主键
特点:
最常见的索引类型
确保数据记录的唯一性
确定特定数据记录在数据库中的位置

唯一索引( UNIQUE)
作用:避免同一个表中某数据列中的重复值
与主键索引的区别
主键索引只能有一个
唯一索引可以有多个
示例:
GradeName VARCHAR(32) UNIQUE
或 UNIQUE KEYGradeID(GradeID)

常规索引(KEY/INDEX)
作用:
快速定位特定数据
注意:
index和key关键字都可设置常规索引
应加在查找条件的字段
不宜添加太多常规索引,影响数据的插入、删除和修改操作
创建表时添加:
CREATE TABLE result (
// 省略一些代码
INDEX/KEY ind (studentNo, subjectNo)
)
创建后追加:
ALTER TABLE result ADD INDEX ind (studentNo, subjectNo);

全文索引(FULLTEXT)
作用:
快速定位特定数据
注意:
只能用于MyISAM类型的数据表
只能用于CHAR、VARCHAR、TEXT数据列类型
适合大型数据集
CREATE TABLE student (
省略一些SQL
FULLTEXT (StudentName)
)ENGINE=MYISAM;
ALTER TABLE employee ADD FULLTEXT (first_name);
还有一种写法:
SELECT * FROM test WHERE MATCH(studentname)AGAINST(‘key’);
注:key是关键字管理索引:
创建索引
创建表时添加
建表后追加:
语法: ALERT TABLE 表名 ADD 索引类型(数据列名)

删除索DROP INDEX 索引名 ON 表名
ALTER TABLE 表名 DROP INDEX 索引名
ALTER TABLE 表名 DROP PRIMARY KEY
查看索引
SHOW INDEX(或KEYS) FROM 表名

索引准则:
索引不是越多越好
不要对经常变动的数据加索引
小数据量的表建议不加索引
索引一般加在查找条件的字段


MySQL数据备份方法
mysqldump备份工具
数据库管理工具,如SQLyog
直接拷贝数据库文件和相关配置文件

mysqldump客户端
作用:
转储数据库
搜集数据库进行备份
将数据转移到另一个SQL服务器(不一定是MySQL服务器)
语法:
mysqldump -h 主机名 –u 用户名 –p [options] 数据库名 数据库名
[ table1 table2 table3 ] > path/filename.sql
示例:
备份myschool 数据库如:

mysqldump -u root -p myschool > d:/myschool.sql
EnterPassword: *****

mysqldump常用选项
符号名称
–add-drop-table
导出sql 脚本会加上 DROP TABLE IF EXISTS 语句默认是打开的 ,可以用 –skip-add-drop-table
–add-locks
该选项会在INSERT 语句中捆绑一个LOCK TABLE 和UNLOCK TABLE 语句
好处:防止记录被再次导入时,其他用户对表进行的操作,
-t或–no-create-info
忽略不写重新创建每个转储表的CREATE TABLE 语句
-c或–complete-insert
在每个INERT 语句的列上加上字段名在数据库导入另一个数据库时非常有用
-d 或 –no-data
不写表的任何行信息。对于只想转储表的结构很有用
–where “where-condition”, -w “where-condition”
只转储给定的WHERE 条件选择的记录
–opt
该选项是速记;等同于指定
–add-drop-tables
–add-locking
–create-option
–disable-keys–extended-insert
–lock-tables
–quick
–set-charset
注意:
可通过mysqldump –help


MySQL数据库的恢复
方法一:
用 SOURCE 语法
/path/是一个绝对路径,并且必须是mysql 运行用户有权限读取的文件
SOURCE 在MySQL命令行里执行
先创建或选择数据库,然后SOURCE /path/db_name.sql;
方法二:
用 mysql 客户端
mysql –u root –p dbname < /path/db_name.sql;
利用 SQL语句导出、导入数据库
语法:
SELECT * INTO OUTFILE ‘file_name’ FROM tbl_name
注意:输出文件不能现存在,否则报错

索引优化:

show  index  from sutdent;
alter table student add primary key(`StudentNo`);#主键索引
alter table student add unique index(IdentityCard);#唯一索引
alter table student add index (Email);#常规索引
先修改为MyISAM类型数据表
alter table student engine=MyISAM;#全文索引
alter table student add fulltext(StudentName);
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值