MySql技术概要

yacon 2018-05-06

166

概念

什么叫数据库系统

存储数据的系统成为数据库系统

什么叫关系型数据库系统

关系型数据库中的数据都是存储在表中的，表与表之间存在某种关系，这种关系可以提高数据的有效性，支持这种表关系的数据库系统成为关系型数据库系统

什么叫数据库管理系统

管理数据库的系统成为数据库管理系统，可以通过数据库管理系统对数据库进行权限、数据等操作

什么叫SQL

结构化查询语句

SQL语句分类

名称	解释	明令
DDL(数据定义语言)	定义和管理数据对象，如数据库，数据表等	CREATE、DROP、ALTER
DML(数据操作语言)	用于操作数据库对象中所包含的数据	INSERT、UPDATE、DELETE
DQL(数据查询语言)	用于查询数据库数据	SELECT
DCL(数据控制语言)	用来管理数据的语言，包括管理权限及数据更改	GRANT、COMMIT、ROLLBACK

MySql的特点

免费、开源
小巧、功能齐全
使用便捷
可运行与Windows或Linux
适用于中小型甚至大型网站应用
关系型数据库系统

MySql服务启动与关闭

通过控制台启动与关闭

net start mysql #启动服务

net stop mysql #停止服务

如果出现拒绝访问
，就使用管理员模式
打开CMD

通过服务管理器启动与关闭

点击微软键+R
，打开运行窗体
输入services.msc
，打开服务管理器
在服务中找到MySql
，并进行启动或停止服务操作

控制台操作MySql数据库

登陆本地MySql数据库

语法mysql -u用户名 -p密码

mysql -uroot -proot

语法mysql -u 用户名 -p

mysql -u root -p

语法mysql -h 服务器地址 -u 用户名 -p密码

mysql -h localhost -u root -proot

如果出现'mysql' 不是内部或外部命令，也不是可运行的程序或批处理文件。
，就将MySql安装目录下的bin目录配置到系统环境变量中，配置成功后，要记得重启以下CMD，否则不会生效

显示数据库的版本号

select version();

退出MySql

exit;

查看数据库中有哪些表

show tables;

查询当前数据库用户信息

use mysql;select * from user\G;

修改用户密码

#选择mysql库
use mysql;
#将root用户的密码修改为123
update user set password=password('123') where user='root';
#刷新权限
flush privileges;

查看帮助

语法? 关键字;

? show;

语法命令 --help

mysqldump 
--help|more;

语法help 命令

help show;

DDL

创建数据库

语法

CREATE DATABASE [IF NOT EXISTS] 数据库名称[CHARACTER SET [=] 字符集COLLATE [=] 校对规则]

示例

CREATE DATABASE school CHARACTER SET utf8 COLLATE utf8_general_ci;

删除数据库

语法DROP DATABASE [IF EXISTS] 数据库名称;

DROP DATABASE IF EXISTS

查看数据库

SHOW DATABASES;

选择要操作的数据库

语法USE 数据库名;

USE mysql;

创建表

语法

CREATE TABLE [IF NOT EXISTS] 表名(
    列名 数据类型(数据长度) [NOT NULL | NULL] [DEFAULT 默认值] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT '列注释'],
    ...,
    列名 数据类型(数据长度) [NOT NULL | NULL] [DEFAULT 默认值] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT '列注释']
)[ENGINE [=] 表引擎 CHARACTER SET [=] 字符集 COMMENT [=] '表注释' AUTO_INCREMENT [=] 自增列初始值]

示例

CREATE TABLE IF NOT EXISTS `student` (
  `id` INT(11) DEFAULT NULL,
  `name` CHAR(1) DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8

查询数据库的定义

语法SHOW CREATE DATABASE 数据库名称;

SHOW CREATE DATABASE school;

查看数据表的定义

语法SHOW CREATE TABLE 表名

SHOW CREATE TABLE student;

查看表结构

语法DESC 表名;

DESC student;

设置SQL检查模式

语法SET sql_mode='检查模式'

#设置严格检查模式
SET sql_mode='strict_trans_tables'

设置表中自增列的步长

语法SET @@auto_increment_increment = 步长

SET @@auto_increment_increment = 5

查看支持的数据库引擎

SHOW ENGINES;

查看默认的数据库引擎

SHOW VARIABLES LIKE 'storage_engine';

修改表

修改表名

语法 ALERT TABLE 旧表名 RENAME AS 新表名;

ALERT TABLE sutdent 
RENAME AS stu;

添加字段

语法ALERT TABLE 表名 ADD 字段名数据类型(数据长度) [列属性];

ALERT TABLE student ADD address varchar(100);

修改字段

语法ALERT TABLE 表名 MODIFY 字段名数据类型(数据长度) [列属性];

ALERT TABLE student MODIFY address varchar(500);

语法ALERT TABLE 表名 CHANGE 旧字段名新字段名数据类型(数据长度) [列属性];

ALERT TABLE student CHANGE address addr varchar(500);

删除字段

语法ALERT TABLE 表名 DROP 字段名;

ALERT TABLE student DROP address;

删除表

语法DROP TABLE IF EXISTS 表名;

DROP TABLE IF EXISTS student;

创建外键

语法

CREATE TABLE 从表名(
    字段定义，
    CONSTRAINT 外键名称 FOREIGN KEY(外键列名) REFERENCES 主表名称(主键列名)
)

示例

/* 主键表 */
CREATE TABLE IF NOT EXISTS grade(
	id INT(4) NOT NULL PRIMARY KEY AUTO_INCREMENT,
	grade_name VARCHAR(20) NOT NULL);/* 外键表*/CREATE TABLE IF NOT EXISTS student(	id INT(4) NOT NULL PRIMARY KEY AUTO_INCREMENT,	NAME VARCHAR(20) NOT NULL DEFAULT '匿名',
	grade_id INT(4) NOT NULL,	
	CONSTRAINT Fk_gradeid FOREIGN KEY(grade_id) REFERENCES grade(id)
);

语法

ALTER TABLE 从表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键列名) REFERENCES 主表名称(主键列名)

示例

ALTER TABLE student ADD CONSTRAINT Fk_gradeid FOREIGN KEY(grade_id) REFERENCES grade(id)

删除外键

语法

/*删除外键*/
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
/*删除外键索引*/
ALTER TABLE 表名 DROP INDEX 索引名称;

示例

ALTER TABLE student DROP FOREIGN KEY Fk_gradeid;ALTER TABLE 表名 DROP INDEX Fk_gradeid;

修改表的存储引擎

ALTER TABLE 表名 ENGINE 
= MYISAM:

DML

插入数据

语法INSERT INTO 表名 [(字段1,...,字段n)] VALUES (值1,...,值n);

INSERT INTO grade(gradename) VALUES ('S1');

语法

INSERT INTO 表名 [(字段1,...,字段n)] 
VALUES (值1,...,值n),...,(值1,...,值n);

示例

INSERT INTO grade(gradename) VALUES ('S1'),('S2');

插入默认值

INSERT INTO student VALUES (1000,'张三',DEFAULT,1)

插入空值

INSERT INTO student VALUES (1000,'张三',NULL)

修改数据

语法UPDATE 表名 SET 列明=值[,...,列明=值] [WHERE 修改条件]

UPDATE student SET sex = 0 WHERE id=10001

删除数据

语法DELETE FROM 表名 [WHERE 删除条件];

DELETE FROM student WHERE id = 10001;

不会清空自增列；当表类型为INNODB，重启服务后会清空自增列

语法TRUNCATE TABLE 表名;

TRUNCATE TABLE student;

会清空自增列

DQL

语法

SELECT 字段列表FROM 表名 [AS 表别名]
[LEFT|RIGHT|INNER JOIN 表名]
[WHERE 查询条件]
[GROUP BY 分组字段列表[HAVING 组后筛选条件]]
[ORDER BY 排序字段列表]
[LIMIT [偏移量,]行数]

示例

/*查询所有学生信息*/
SELECT * FROM student;

/*查询指定列*/
SELECT studentno,studentname FROM student;

/*给列添加别名*/
SELECT studentno AS 学号,studentname AS 姓名 FROM student;
SELECT studentno 学号,studentname 姓名 FROM student;

/*给表添加别名*/
SELECT studentno,studentname FROM student AS s;
SELECT studentno,studentname FROM student s;

/*去除重复值*/
SELECT DISTINCT studentno FROM result;

/*查询自增列步长*/
SELECT @@auto_increment_increment;

/*查询版本号*/
SELECT VERSION();

/*直接查询表达式*/
SELECT 100*3-1 AS 计算结果;

/*根据条件查询*/
SELECT * FROM student WHERE id=10001

/*查询性李的学员*/
SELECT * FROM student WHERE studentname LIKE '李%';

/*查询地址中包含%号的学员信息*/
SELECT * FROM student WHERE address LIKE '%\%%';

/*使用自定义转义符*/
SELECT * FROM student WHERE address LIKE '%:%%' ESCAPE ':';

/*查询指定范围的学员信息*/
SELECT * FROM student WHERE studentno IN(1000,1001,1002);

/*空值查询*/
SELECT * FROM student WHERE email IS NULL;

/*非空值查询*/
SELECT * FROM student WHERE email IS NOT NULL;

/*对查询结果进行排序*/
SELECT * FROM result ORDER BY studentresult DESC

/*分组查询*/
SELECT sex,COUNT(1) FROM studentGROUP BY sexHAVING COUNT(1) > 10

/*返回指定条目数*/
SELECT * FROM student LIMIT 10SELECT * FROM student LIMIT 5,10

/*内连接查询*/
SELECT student.studentno,studentname,subjectno,studentresult 
FROM student INNER JOIN result ON result.studentno=student.studentno

/*内连接查询（等值连接查询）*/
SELECT student.studentno,studentname,subjectno,studentresult
FROM student,result WHERE result.studentno=student.studentno

/*左外连接查询*/
SELECT student.studentno,studentname,subjectno,studentresult
FROM student LEFT JOIN result ON result.studentno=student.studentno

/*右外连接查询*/
SELECT student.studentno,studentname,subjectno,studentresult
FROM student RIGHT JOIN result ON result.studentno=student.studentno

/*自连接查询*/
SELECT a.categoryName AS '父栏目',b.categoryName AS '子栏目'
FROM category AS a,category AS bWHERE a.categoryId = b.pid

/*子查询*/
SELECT studentno,subjectno,studentresult FROM result
WHERE subjectno=(SELECT subjectno FROM subject WHERE subjectname='数据库结构')

事务处理

什么是事务

将一组SQL语句放在同一批次内去执行，如果一个SQL出错，则该批次内的所有SQL都将被取消执行。

事务的特点（ACID原则）

原子性（Atomic）
一致性（Consist）
隔离性（Isolated）
持久性（Durable）

事务的实现

/*关闭自动提交模式*/
SET AUTOCOMMIT = 0;

/*开启事务*/
START TRANSACTION;

/*要执行的SQL语句组*/
UPDATE account SET cash=cash-500 WHERE name='a';
UPDATE account SET cash=cash+500 WHERE name='b';

/*提交事务*/
COMMIT;

/*回滚事务*/
ROLLBACK;

/*开启自动提交模式*/
SET AUTOCOMMIT = 1;

索引

什么是索引

可以将索引看作是数据表的目录

索引的所用

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

索引分类

主键索引（PRIMARY KEY）
唯一索引（UNIQUE）
常规索引（INDEX）
全文索引（FULLTEXT）

主键索引

主键索引特点

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

创建主键索引

/*方式一*/
CREATE TABLE 表名(
    id int(11) AUTO_INCREMENT PRIMARY KEY,
    ...
);
    
/*方式二*/
CREATE TABLE 表名(
    id int(11) AUTO_INCREMENT,
    ...,
    PRIMARY KEY(`id`)
);

/*方式三*/
ALTER TABLE 表名 ADD PREMARY KEY(`id`);

唯一索引

唯一索引作用

避免同一个表中某列数据重复

与主键索引的区别

主键索引只能右一个，唯一索引可以右多个
主键索引不能为NULL值，唯一索引可以为NULL值

创建唯一索引

/*方式一*/
CREATE TABLE 表名(
    name int(11) AUTO_INCREMENT UNIQUE,
    ...
);
/*方式二*/
CREATE TABLE 表名(
    name int(11) AUTO_INCREMENT,
    ...,
    UNIQUE KEY `name`(`name`)
);
/*方式三*/
ALTER TABLE 表名 ADD UNIQUE KEY(`name`);

常规索引

常规索引作用

快速定位特定数据

常规索引注意事项

index和key关键都可以设置常规索引
应该加在查找条件的字段
不宜添加太多常规索引，影响数据的插入、删除和修改操作

创建常规索引

/*方式一*/
CREATE TABLE 表名(
    ...,
    INDEX 'ind'(`studentno`,`subjectno`)
);
/*方式二*/
CREATE TABLE 表名(
    ...,
    KEY 'ind'(`studentno`,`subjectno`)
);
/*方式三*/
ALTER TABLE `表名` ADD INDEX `ind`(`studentno`,`subjectno`);

全文索引

全文索引作用

快速定位特定数据

全文索引注意事项

只能用于MyISAM类型的数据表
只能用于CHAR、VARCHAR、TEXT数据列类型
适合大型数据集

创建全文索引

/*方式一*/
CREATE TABLE 表名(
    ...,
    FULLTEXT(`content`)
);
/*方式二*/
ALTER TABLE `表名` ADD FULLTEXT (`content`);

使用全文索引

全文索引无法直接使用，需要使用如下SQL才可使用

SELECT * FROM student WHERE MATCH(StudentName) AGAINST('李')

查看执行查询语句的性能

EXPLAIN SELECT * FROM student WHERE studentno='1001';

显示表中的索引信息

SHOW INDEX FROM 表名;

删除索引

/*方式一*/
DROP INDEX 索引名称 ON 表名;
/*方式二*/
ALTER TABLE 表名 DROP INDEX 索引名称;
/*删除主键索引*/
ALTER TABLE 表名 DROP PRIMARY KEY;

索引使用准则

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

数据库备份与还原

使用`mysqldump`
备份

语法

mysqldump -h 主机名 -u用户名 -p密码 [其它参数] 数据库名[表1 表2 表3] > 文件路径.sql

示例

/*备份整个库*/
mysqldump -uroot -proot myschool > d:/myschool.sql

/*备份库中指定的表*/
mysqldump -uroot -proot myschool grade student > d:/myschool.sql

/*备份时跳过drop table语句*/
mysqldump -uroot -proot --skip-add-drop-table myschool > d:/myschool.sql

/*备份时跳过drop table语句，插入时显示列名*/

mysqldump -uroot -proot --skip-add-drop-table -c myschool > d:/myschool.sql

在控制台使用
在未登录的情况下使用

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	在每个INSERT语句的列上加上字段名在数据库导入另一个数据库时非常有用
-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

数据库的恢复

用`SOURCE`
恢复

SOURCE /path/db_name.sql

在控制台使用
登陆后使用

用`mysql`
恢复

mysql -uroot -proot 数据库名 < /path/db_name.sql;