MySQL基本操作

MySQL基本操作

一、结构化查询语言

1、SQL

SQL的含义是结构化查询语句（Structured Query Languate），是对数据库进行查询和修改操作的语言。

2、T-SQL

T-SQL ：Transact-SQL

T-SQL是SQL的增强版，对功能进行了扩充：如变量说明、流程控制、功能函数。

3、SQL的组成

二、MySQL常用数据类型

1、数值类型

数值类型的属性：

UNSIGNED

标识为无符号数

ZEROFILL

宽度（位数）不足，以0填充

示例：

create database `school`;
use `school`;
create table `student`(
	`sid` INT(4) ZEROFILL
);
# 查看表结构
desc student;
# 插入数据
insert into `student` VALUE(12),(123),(1234);

数据显示如下：

mysql> select * from `student`;
+------+
| sid  |
+------+
| 0012 |
| 0123 |
| 1234 |
+------+
3 rows in set (0.00 sec)

2、字符串类型

3、日期类型

若某日期字段默认值为当前日期，一般设置为TIMESTAMP类型

三、MySQL建库、建表

1、创建数据库

创建数据库是在系统磁盘上划分⼀块区域用于数据的存储和管理，如果管理员在设置权限的时候为用户创建了数据库，则可以直接使用，否则，需要自己创建数据库。

语法格式：

CREATE DATABASE [IF NOT EXISTS]  数据库名

示例：

# 创建myschool数据库
create database myschool;

IF NOT EXISTS：在创建数据库之前进行判断，只有该数据库目前尚不存在时才能执行操作。

此选项可以用来避免数据库已经存在而重复创建的错误。

2、创建表

语法格式：

CREATE TABLE [IF NOT EXISTS]  表名 (
    字段1 数据类型 [字段属性|约束][索引][注释],
      ……
     字段n 数据类型 [字段属性|约束][索引][注释]
)[表类型][表字符集][注释];

示例：

#创建学生表
CREATE TABLE `student`（
    `studentNo` INT(4)  PRIMARY KEY,
     ` name` CHAR(10),
     ……）;

注意：

多字段使用逗号分隔
保留字用撇号括起来
单行注释：#……
多行注释：/*……*/

（1）字段的约束及属性

主键

CREATE TABLE student（
      `studentNo` INT(4)  PRIMARY KEY,
      ……）;

注释

CREATE TABLE test (
    `id` int(11) UNSIGNED COMMENT ‘编号’
)COMMENT='测试表’ ;

设置字符集编码

CREATE TABLE [IF NOT EXISTS] 表名（
     #省略代码
）CHARSET = 字符集名;

（2）在myschool数据库中创建学生表

示例：

use myschool;
create table student(
	`studentNo` int(4) not null comment '学号' primary key,
	`loginPwd` varchar(20) not null comment '密码',
	`studentName` varchar(50) not null comment '姓名',
	`sex` char(2) not null default '男' comment '性别',
	`gradeID` int(4) unsigned comment '年级编号',
	`phone` varchar(50) comment '电话',
	`address` varchar(255) default '地址不详' comment '地址',
	`bornDate` datetime comment '出生日期',
	`email` varchar(50) comment '邮件账号',
	`identityCard` varchar(18) comment '身份证号' unique key
)charset='utf8' comment='学生表';

3、查看表

（1）查看表是否存在

use myschool;
show tables;

（2）查看表定义

语法格式：

desc 表名;
或
describe 表名;

示例：

use myschool;
desc `student`;

4、删除表

语法格式：

drop table [if exists] 表名;

示例：

use myschool;
drop table if exists `student`;

在删除表之前，先使用if exists语句验证表是否存在。

5、删除数据库

删除数据库是将已经存在的数据库从磁盘空间上清除，清除之后，数据库中的所有数据也将一起被删除。

删除数据库语句和创建数据库的命令相似，MySQL中删除数据库的基本语法格式为：

drop database if exists 数据库名;

示例：

drop database if exists myschool;

6、上机练习

（1）myschool数据库中创建科目表(subject)

（2）myschool数据库中创建成绩表（result）

四、MySQL的存储引擎

1、存储引擎简介

数据库存储引擎是数据库底层软件组件，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能。使用不同的存储引擎，还可以获得特定的功能。

现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySQL的核心就是存储引擎。

2、存储引擎的类型

mysql有多种存储引擎，它们分别为：

MyISAM
InnoDB
MERGE
MEMORY
EXAMPLE
FEDERATED
ARCHIVE
CSV
BLACKHOLE

3、存储引擎的主要区别

（1）MyISAM 存储引擎特点

MySQL 5.5 之前使用 MyISAM 引擎，MySQL 5.5 之后使用 InnoDB 引擎
MyISAM 引擎读取速度较快，占用资源相对较少，不支持事务，不支持外键约束，但支持全文索引
读写互相阻塞，也就是说读数据的时候你就不能写数据，写数据的时候你就不能读数据
MyISAM 引擎只能缓存索引，而不能缓存数据

（2）InnoDB 存储引擎特点

事务型数据库的首选引擎，支持事务安全表，支持行锁定和外键，MySQL5.5.5 版本之后，InnoDB作为默认存储引擎
具有提交、回滚和崩溃恢复能力的事务安全存储引擎，能处理巨大数据量，性能及效率高，完全支持外键完整性约束
具有非常高效的缓存特性，能缓存索引也能缓存数据，对硬件要求比较高
使用InnoDB时，将在MySQL数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据⽂文件，以及两个名为 ib_logfile0 和 ib_logfile1 的 5MB ⼤大⼩小的日志⽂文件

（3）Memory 存储引擎特点

Memory存储引擎将表中的数据存储到内存中，为查询和引用其他表数据提供快速访问
Memory存储引擎执行 HASH 和 BTREE 索引，不支持 BLOB 和 TEXT 列，支持AUTO_INCREMENT 列和对可包含 NULL 值得列的索引
当不再需要 Memory 表的内容时，要释放被 Memory 表使用的内存，应该执行DELETE FROM 或 TRUNCATE TABLE ，或者删除整个表

4、存储引擎适用场合

（1）MyISAM 适⽤用场景

不需要事务支持的业务，例如：转账就不行
适用于读数据比较多的业务，不适用于读写频繁的业务
并发相对较低、数据修改相对较少的业务
硬件资源比较差的机器可以考虑使用 MyISAM 引擎

（2）InnoDB 适⽤用场景

需要事务⽀持的业务、⾼并发的业务
数据更新较为频繁的场景，⽐如：BBS、SNS、微博等
数据⼀致性要求较⾼的业务，⽐如：充值转账、银⾏卡转账

（3）总结

使用MyISAM: 不需事务，空间小，以查询访问为主
使用InnoDB: 多删除、更新操作，安全性高，事务处理及并发控制

5、查看当前默认存储引擎

mysql> show variables like '%storage_engine';
+----------------------------------+--------+
| Variable_name                    | Value  |
+----------------------------------+--------+
| default_storage_engine           | InnoDB |
| default_tmp_storage_engine       | InnoDB |
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+
3 rows in set, 1 warning (0.02 sec)

6、修改默认存储引擎

（1）MySQL 5.5

修改my.ini配置文件

default_storage_engine=InnoDB

（2）MySQL 5.7

最简单的方法，就是通过命令直接修改表的存储引擎，如下所示：

alter table 表名 ENGINE = 引擎名;

示例：

ALTER TABLE student ENGINE = InnoDB;

7、设置表的存储引擎

语法格式：

CREATE TABLE 表名(
    #省略代码
)ENGINE=存储引擎;

示例：

CREATE TABLE `myisam` (
   id  INT(4)
)ENGINE=MyISAM;

五、MySQL补充知识

在mysql中，每个数据库最多可创建20亿个表，一个表允许定义1024列，每行的最大长度为8092字节（不包括⽂本和图像类型的长度）。

当表中定义有varchar、nvarchar或varbinary类型列时，如果向表中插入的数据行超过8092字节时，将导致语句失败，并产生错误信息。

SQL Server对每个表中行的数量没有直接限制，但它受数据库存储空间的限制。每个数据库的最大空间1048516TB，所以一个表可用的最大空间为1048516TB减去数据库类系统表和其它数据库对象所占用的空间。理论上无限大，就看你硬盘够不够大，大多数情况是你的硬盘不够。