MySQL学习笔记（复习自用）

一、安装MySQL

什么是数据库？

数据库(DataBase，简称DB)

概念：长期存放在计算机内，有组织，可共享的大量数据的集合，是一个数据“仓库”。

作用：保存，并能安全管理数据(增删改查等)，减少冗余…

数据库总览

关系型数据库	非关系型数据库
MySQL,Oracle,SQL Server…	Redis,MongoDB…
关系型数据库通过外键关联来建立表与表之间的关系	非关系型数据库通常指数据以对象的形式存储在数据库中，而对象之间的关系通过每个对象自身的属性来决定

什么是DBMS？

数据管理系统(DataBase Management System)

数据库管理软件，科学组织和存储数据，高效地获取和维护数据…

SQL就是一个数据库管理系统

MySQL简介

概念：是现在流行的开源的，免费 的 关系型 数据库。

特点 :

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

安装MySQL

建议安装压缩版

安装步骤：

1. 下载得到zip压缩包

2. 解压到自己想要安装的目录

3. 添加环境变量：我的电脑->属性->高级->环境变量

   选择PATH,在其后面添加: 你的mysql 安装文件下面的bin文件夹
   

4. 编辑my.ini文件，注意替换路径位置

   [mysqld]
   basedir=D:\Program Files\mysql-5.7\
   datadir=D:\Program Files\mysql-5.7\data\
   port=3306
   skip-grant-tables
   

5. 启动管理员模式下的CMD，并将路径切换至mysql下的bin目录，然后输入mysqld –install (安装mysql)

   mysqld –install
   

6. 再输入 mysqld --initialize-insecure --user=mysql 初始化数据文件

   mysqld --initialize-insecure --user=musql
   

7. 然后再次启动mysql 然后用命令 mysql –u root –p 进入mysql管理界面（密码可为空）

8. 进入界面后更改root密码

   update mysql.user set authentication_string=password('123456') where user='root' and Host='localhost';
   

9. 刷新权限

   flush privileges;
   

10. 修改my.ini文件最后一句skip-grant-tables

11. 重启mysql即可正常使用

    net stop mysql
    net start mysql
    

SQLyog

管理SQL数据库的图形化软件工具

特点： 简洁易用

连接数据库

打开MySQL命令窗口

• 在DOS命令行窗口进入 安装目录\mysql\bin
• 可设置环境变量，设置了环境变量，可以在任意目录打开！

连接数据库语句：

mysql -h 服务器主机地址 -u 用户名 -p用户密码

-p后不能加空格！

几个基本的数据库操作命令

update user set password=password('123456') where user='root'; --修改密码
flush privileges; --刷新数据库
show dbname; --打开某个数据库
show tables; --显示数据库musql中所有的表
describe user; --显示表mysql数据库中user表的列信息
create database name; --创建数据库
use databasename; --选择数据库

exit; --退出MySQL
?命令关键词：--寻求帮助
--表示注释

二、数据库操作

2.1、结构化查询语句分类

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

2.2、数据库操作

命令行操作数据库

创建数据库：create database [if not exists] 数据库名;

删除数据库：drop database [if exists] 数据库名;

查看数据库：show database;

使用数据库：use 数据库名;

2.3、创建数据表

属于DDL的一种，语法：

create table [if not exists] '表名'(
	'字段名1' 列类型 [属性][索引][注释],
    '字段名2' 列类型 [属性][索引][注释],
    #...
    '字段名n' 列类型 [属性][索引][注释],
)[表类型][表字符集][注释]

2.4、数据值和列类型

列类型：规定数据库中该列存放的数据类型

数值类型

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

ps: decimal的取值范围为decimal(m, d)

字符串类型

类型	说明	最大长度
char[(M)]	固定长字符串，检索快但费空间，0 <=M<= 255	M字符
varcahr[(M)]	可变字符串0 <= M <= 65535	变长度
tinytext	微型文本串	2^8-1字节
text	文本串	2^16-1字节

日期和时间型数值类型

类型	说明
DATE	YYYY-MM-DD
TIME	Hh:mm:ss
DATETIME	YYYY-MM-DD Hh:mm:ss
TIMESTAMP	YYYYMMDDhhmmss格式表示的时间戳
YEAR	YYYY格式的年份值

NULL值

• 理解为“没有值”或“未知值”
• 不要用NULL进行算术运算，结果仍为NULL

2.5、数据字段属性

UnSigned

• 无符号的
• 声明该数据列不允许是负数

ZEROFILL

• 0填充的
• 不足为数的用0来填充，如int(3),5则为005

Auto_InCrement

• 自动增长的 , 每添加一条数据 , 自动在上一个记录数上加 1(默认)

• 通常用于设置主键 , 且为整数类型

• 可定义起始值和步长

• • 当前表设置步长(AUTO_INCREMENT=100) : 只影响当前表
  • SET @@auto_increment_increment=5 ; 影响所有使用自增的表(全局)

NULL和NOT NULL

• 默认为NULL , 即没有插入该列的数值
• 如果设置为NOT NULL , 则该列必须有值

DEFAULT

• 默认的
• 用于设置默认值
• 例如,性别字段,默认为"男" , 否则为 “女” ; 若无指定该列的值 , 则默认值为"男"的值

例子

-- 目标 : 创建一个school数据库
-- 创建学生表(列,字段)
-- 学号int 登录密码varchar(20) 姓名,性别varchar(2),出生日期(datatime),家庭住址,email
-- 创建表之前 , 一定要先选择数据库

CREATE TABLE IF NOT EXISTS `student` (
    `id` int(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
    `name` varchar(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
    `pwd` varchar(20) NOT NULL DEFAULT '123456' COMMENT '密码',
    `sex` varchar(2) NOT NULL DEFAULT '男' COMMENT '性别',
    `birthday` datetime DEFAULT NULL COMMENT '生日',
    `address` varchar(100) DEFAULT NULL COMMENT '地址',
    `email` varchar(50) DEFAULT NULL COMMENT '邮箱',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

--查看数据库的定义
SHOW CREATE DATABASE school;
--查看数据表的定义
SHOW CREATE TABLE student;
--显示表结构
DESC student; 
--设置严格检查模式（不能容错了）
SET sql_mode='STRICT_TRANS_TABLES';

2.6数据表的类型

设置数据表的类型

CREATE TABLE 表名(
   -- 省略一些代码
   -- Mysql注释
   -- 1. # 单行注释
   -- 2. /*...*/ 多行注释
)ENGINE = MyISAM (or InnoDB)

-- 查看mysql所支持的引擎类型 (表类型)
SHOW ENGINES;

MySQL的数据表的类型：MyISAM,InnoDB,HEAP,BOB,CSV…

MyISAM与InnoDB区别：

名称	MyISAM	InnoDB
事务处理	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间大小	较小	较大，约两倍！

适用场合：

• MyISAM：节约空间及响应速度
• InnoDB：安全性，事务处理及多用户操作数据表

数据表的存储位置

• MySQL数据表以文件方式存放在磁盘中
  • 包括表文件，数据文件，以及数据库的选项文件
  • 位置 : Mysql安装目录\data\下存放数据表 ，目录名对应数据库名 , 该目录下文件名对应数据表
• 注意
  • * . frm – 表结构定义文件
  • * . MYD – 数据文件 ( data )
  • * . MYI – 索引文件 ( index )
  • InnoDB类型数据表只有一个 *.frm文件 , 以及上一级目录的ibdata1文件

2.7修改数据库

修改表(ALTER TABLE)

修改表名：ALTER TABLE 旧表名 RENAME AS 新表名

添加字段：ALTER TABLE 表名 ADD 字段名 列属性[属性]

修改字段：

• ALTER TABLE 表名 MODIFY 字段名 列类型[属性]
• ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列属性[属性]

删除字段：ALTER TABLE 表名 DROP 字段名

删除数据表

语法：DROP TABLE [IF EXISTS] 表名

• IF EXISTS为可选，判断是否存在该数据表
• 如删除不存在的数据表会抛出错误

其他

1. 可用反引号（`）为标识符（库名、表名、字段名、索引、别名）包裹，以避免与关键字重名！中文也可以作为标识符！

2. 每个库目录存在一个保存当前数据库的选项文件db.opt。

3. 注