MySQL 基础操作

图形化管理工具SQLyog操作数据库体验

使用工具SQLyog，下载地址：

https://github.com/webyog/sqlyog-community/wiki/Downloads

根据自身系统，下载相应版本，并安装好软件。

首先通过管理员权限的命令行，开启MySQL服务。

net start mysql

登录MySQL

然后使用SQLyog连接，输入主机地址、用户名、密码、端口登录。

sqlalchemy创建数据库连接 sqlyog新建连接数据库_数据

创建数据库

登录后，在主界面左侧看到默认的数据库文件，默认有4个数据库，不要去操作。

sqlalchemy创建数据库连接 sqlyog新建连接数据库_MySQL_02

鼠标右键点击root@localhost，选择创建数据库。

sqlalchemy创建数据库连接 sqlyog新建连接数据库_MySQL_03

按要求填写数据库名称、基字符集、数据库排序规则。为了使中文不出现乱码，通常使用utf-8字符集，而数据库排序规则可以选择utf_general_ci。

sqlalchemy创建数据库连接 sqlyog新建连接数据库_MySQL_04

点击创建后即可完成新数据库的创建。

创建表

展开school数据库，鼠标右键点击表，选择创建表

sqlalchemy创建数据库连接 sqlyog新建连接数据库_数据_05

设置表名称、引擎选择：InnoDB、字符串utf-8，并配置数据库的列名、数据类型、长度，注意勾选ID为主键。配置参考如下：

sqlalchemy创建数据库连接 sqlyog新建连接数据库_数据_06

点击保存后，完成表的创建。

输入表数据

鼠标右键点击刚刚创建的student表，选择打开表

sqlalchemy创建数据库连接 sqlyog新建连接数据库_数据_07

在界面右下方显示表结构，类似于Excel，将信息填入，参考如下：

sqlalchemy创建数据库连接 sqlyog新建连接数据库_sqlalchemy创建数据库连接_08

注意改完后数据是没有保存的，右下方有提示 数据已修改但没有存储，鼠标右键空白处，点击保存更改即可。

sqlalchemy创建数据库连接 sqlyog新建连接数据库_数据库_09

至此，简单的登录MySQL->创建数据库->创建表->创建数据即完成。

但学习数据库，不是只用图形界面，还是要学习数据库语句和命令，点开历史记录，刚才我们所有的操作均以语句的形式展示在这里。接下来就要学习基本数据库语句。

sqlalchemy创建数据库连接 sqlyog新建连接数据库_数据库_10

MySQL基础指令

命令行连接数据库

--命令回顾,记得分号 ; 结尾
mysql -uroot -p123456  --连接数据库
update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost'; --修改用户密码
flush privileges; --刷新权限

sqlalchemy创建数据库连接 sqlyog新建连接数据库_MySQL_11

查看数据库

show databases; --查看所有的数据库

sqlalchemy创建数据库连接 sqlyog新建连接数据库_MySQL_12

选择（切换）数据库

use school; --切换数据库    use [数据库名]

sqlalchemy创建数据库连接 sqlyog新建连接数据库_数据_13

查看数据库中的表

show tables; --查看该数据库中所有的表

sqlalchemy创建数据库连接 sqlyog新建连接数据库_数据_14

显示表中的数据

describe student; --查看school表中的信息 describe [表名称]

sqlalchemy创建数据库连接 sqlyog新建连接数据库_数据库_15

创建一个数据库

create database class;  --创建一个数据库class  create database [数据库名称]

sqlalchemy创建数据库连接 sqlyog新建连接数据库_数据_16

注释

--  这个单行注释
/*
      这是块注释，和java类似
*/

MySQL语法分类

DDL Data Definition Language：数据定义语言

DML Data Manipulation Language：数据操作语言

DQL Data Query Language：数据查询语言

DCL Data Control Language：数据控制语言

后续敲命令均使用SQLyog来操作，尽量不使用cmd。

右侧为语句输入，左上箭头为执行语句，右下为结果（成功、错误、警告）

sqlalchemy创建数据库连接 sqlyog新建连接数据库_sqlalchemy创建数据库连接_17

MySQL DML 操作数据库

操作数据库->操作数据库中的表->操作数据库中表中的数据

MySQL关键字不区分大小写，但一般用大写表示。

  • 创建数据库
CREATE DATABASE [ IF NOT EXISTS ] newschool; --【如果不存在就】创建一个newschool的数据库
  • 删除数据库
DROP DATABASE [ IF EXISTS ] newschool;--【如果存在就】删除一个newschool的数据库
  • 使用数据库
USE school; --使用数据库 school
  • 查看数据库
SHOW DATABASES; --查看所有的数据库

使用语句，同时学会对比SQLyog可视化操作，不会的语句使用可视化操作，然后查看历史记录。

MySQL数据库类型

数据的类型

数值

类型

解释

大小

tinyint

小数据

1 Byte

smallint

较小的数据

2 Byte

mediumint

中等大小的数据

3 Byte

int

标准的数据

4 Byte

bigint

较大的数据

8 Byte

float

浮点数

4 Byte （精度问题）

double

较大的浮点数

8 Byte

decimal

字符串形式的浮点数

金融计算较多使用

字符串

类型

解释

大小

char

字符串固定大小

0-255

varchar

可变字符串

0-65535 常用 String

tinytext

微型文本

2^8-1

text

文本串

2^16-1 保存大文本

时间日期

类型

解释

备注

date

YYYY-MM-DD

日期格式

time

HH:mm:ss

时间格式

datetime

YYYY-MM-DD HH:mm:ss

最常用格式

timestamp

时间戳

**1970.1.1到现在毫秒数 **常用

year

年份表示

null

  • 没有值，未知
  • 注意不要用null进行运算，结果为null

字段属性

Unsigned

  • 无符号的整数
  • 不能声明为负数

zerofill

  • 0填充
  • 不足的位数使用0来填充，例：int(3) 11 =011

自增

  • 自增，通常在上一条的记录基础上+1（默认）
  • 通常用来设计唯一主键，必须是整数类型
  • 可以自定义设计主键自增的起始值和步长

非空

  • 假如设置为非空，如果不给赋值就会报错（必填项）
  • NULL 如果不填值，则默认为NULL

默认

  • 设置默认的值
  • sex 默认男，如果不指定该列，则默认值为男

常用，每个表，都必须存在以下五个字段！！！表示一个记录存在的意义！开发准则。

  • id 主键
  • 'version' 乐观锁
  • is_delete 伪删除
  • gmt_create 创建时间
  • gmt_update 修改时间

操作示例

要求

--创建一个school 数据库
--创建学生表（列，字段）使用SQL创建
--学号int 登录密码varchar（20）姓名，性别varchar(2),出生日期（datetime）,家庭地址，E-mail
--注意点，使用英文（），表名称和字段使用''括起来
--使用自增
--字符串使用单引号括起来
--所有语句后面加,最后一个不用加
--最后配置主键，一个表一般只有一个主键

代码记录

CREATE DATABASE IF NOT EXISTS school;	
CREATE TABLE IF NOT EXISTS `student`(
    `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
    `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
    `password` 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 '地址',
    `E-mail` VARCHAR(50) DEFAULT NULL COMMENT '邮箱地址', 
    PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

sqlalchemy创建数据库连接 sqlyog新建连接数据库_数据_18

格式

CREATE TABLE [IF NOT EXISTS] `表名`(
    `字段名` 列类型 [属性] [索引] [注释],
    `字段名` 列类型 [属性] [索引] [注释],
    ...
    `字段名` 列类型 [属性] [索引] [注释],
    PRIMARY KEY(`字段名`)
)[表类型][字符集设置][注释]

数据表的类型

INNODB 默认使用

MyISAM 早些年使用

MYISAM

INNODB

事务支持

不支持

支持

数据行级锁定

不支持

支持

外键约束

不支持

支持

全文索引

支持

不支持（5.7后支持）

表空间大小

较小

较大 约为2倍

常规使用操作：

  • MYISAM 节约空间，速度较快
  • INNODB 安全性较高，支持事务的处理，多表多用户操作

在物理空间存在的位置

所有的数据库文件都存在data目录下，本质是文件的存储，一个文件夹对应一个数据库。

MySQL 数据表类型在物理文件上的区别。

  • InnoDB 在数据库中只有一个*.frm的文件 以及上级目录下的ibdata1文件
  • MYISAM 在数据库中有
  • *.frm 表结构定义的文件
  • *.myd 数据文件（data）
  • *.myi 索引文件（index）

设置数据库表的字符集编码

DEFAULT CHARSET=utf8

MySQL默认的字符集编码不支持中文，默认为Latin1不支持中文。

也可以在my.ini中修改,添加character-set-server=utf8（不建议，通用性低了）代码考到别人计算机可能就不能用了。

查询配置命令

SHOW CREATE DATABASE school --查看创建数据库的语句
SHOW CREATE TABLE student	--查看student数据表的定义语句
DESC student --显示表结构

修改和删除

修改表

-- 表重命名
ALTER TABLE student RENAME AS student1 -- 表重命名
ALTER TABLE student1 ADD age INT(11) -- 表新增列
ALTER TABLE student1 MODIFY age VARCHAR(11) --表修改约束
ALTER TABLE student1 CHANGE age age1 INT(3) --表修改名称
ALTER TABLE student1 DROP age1 --删除列

删除表

DROP TABLE IF EXISTS student1 --删除表