MySQL基础

什么是MySQL？

MySQL?ˋ( ° ▽、° ) 当然是没压岁钱了啊( M(没)y(压)S(岁)Q(钱)L(了) 谐音梗扣钱ψ(._. )>

• MySQL是关系型数据库管理系统
• 数据管理工具

MySQL 是如何管理数据的？

MySQL原理图示

MySQL不是直接管理数据的，是通过对表的管理，来管理数据的。

(所以说MySQL是数据库的是错误的哦=￣ω￣=）

什么是数据库?

数据库（DataBase 简称DB ）是按照数据结构来组织，存储，管理数据的仓库。

数据库的发展

手工管理

特点：

• 数据不在计算机中长期保存
• 没有专门的数据管理软件，数据包需要应用程序自己掌握
• 不同程序之间无法共享数据
• 数据不具有独立性完全依赖应用程序

文件管理

OS, Operating System

特点：

• 数据在计算机的外村设备上长期保存，可以对数据反复操作
• 利用文件系统，可以管理和存取
• 一定程度上实现了数据的独立性和共享性，但是非常薄弱

数据库管理

DBMS, Database Management System

• 数据结构化
• 数据共享
• 数据独立性高
• 数据统一管理与控制

关系型数据库

Oracle , SQL Server , DB2 , MySQL 等.

关系型数据库的二维表关系

二维表中每一行数据称之为一个元组，也叫记录

属性: 二维表中的列称为属性，上图里的“编号”， “姓名” ， “年龄” 等就是属性

域是指属性的取值范围，有些属性会存在。（像喜好这样的属性是没有域的╰(￣ω￣ｏ)）

主键(Primary Key) ：可以理解成关键字，具有唯一性，不可以为空。

外键(Foreign Key) : 用于关联两个表。

一般主键存在于父表，外键存在于子表

值 (Value) : 不唯一，可为空值。

如何理解主键和外键?

表一

表二

这里表一和表二相比，表一物品名的键对应的值多，并且关联两个表，表一的物品名为外键，而表二的物品名唯一，为主键。

非关系型数据库

Redis

MongoDB

SQL 语言

值得注意的是这些操作针对的是整个数据库，或者数据库的对象

标准语言是SQL (英文全称：Structured Query Language)，结构化查询语言是关系数据库的标准语言。

SQL分为四个部分:

• 数据定义语言（DDL）:定义数据库，表等

CREATE语句创建数据库，删除表(对象)等

• 数据操作语言(DML) ：用于对数据库进行添加，修改和删除等操作。

INSERT语句对数据库进行添加修改删除命令, UPDATE语句用于修改数据，DALETE语句用于删除数据。

• 数据查询语言(DQl):用于查询数据。

SELECT语句查询数据库中一条数据或者多条数据。

• 数据控制语言(DCL):用于控制用户。

GRANT 语句控制用户权限等

值得注意的是这些操作针对的是整个数据库，或者数据库的对象。（对象=>表）

MySQL的安装

安装教程可以根据这个帖子来安装：

MySQL安装教程

开启/关闭 MySQL 服务

使用管理员身份打开CMD，输入

1

net start mysql 

可以打开MySQL服务，

输入

1

net stop mysql

可以关闭MySQL服务

MySQL基本操作

数据库基本操作

创建数据库

使用 Create database + 库名 来创建

1

Create database T1 ; #创建一个名为"T1"的数据库

创建成功会显示这个：

使用参数 if not exists 可以在创建数据库时,出现相同数据库的时候不报错(一般情况下创建相同名称数据库会报错)。

1

Create database if not exists T1 ; #创建一个名为"T1"的数据库

当然也是可以设置数据库字符集。

创建字符集为gb2312的数据库

1

Create database if not exists T1 default character set gb2312 ; #创建名为“T1”的数据库，字符集设定为gb2312

排序规则（校对规则）: 是指对指定字符集下不同字符的比较规则。

查看可用的排序规则

1

show collsation ; #查看可用的排序规则

创建数据库T1，修改字符集为 utf8，更改排序规则为utf8_unicode_ci

1

Create database T1 default character set utf8 default collate utf8_unicode_ci ; #创建数据库T1，修改字符集为 utf8，更改排序规则为utf8_unicode_ci

查看数据库

查看数据库定义（字符集和排序规则）

show create database + 空格+库名

1

show create database T1 ; #查询T1数据库定义

使用数据库

use +库名

1

use T1 ; #使用T1数据库

修改数据库

alter database + 库名 default character set +新字符集 default collate + 新排序规则

1

alter database T1 default character set gbk ;#将T1数据库的字符集改为gbk

删除数据库

drop database +库名来删除数据库(此命令不可以用来删除数据库中的表)

1

drop database T1 ; #删除T1数据库

数据库的一些其他指令

MySQL默认结束符为分号 (" ; “)

修改结束符:

delimiter + 空格 + 新结束符

修改结束符只能单次使用，重启后将转为默认

1

delimiter ！ #将结束符换为 "!"

可以用指令验证

show databases + 新结束符：展示数据库

1

show databases ! #展示数据库

退出MySQL：

1
2
3

quit #退出数据库

exit

查看服务器信息:

Status + 空格 +结束符

1

Status ; #显示服务器信息

数据表基本操作

数据类型

整型

int 占用四个字节

bigint 占用八个字节

Tinyint 占用一个字节

smallint 占用两个字节

mediumint 占用三个字节

适用于 ：有数值，有大小比较的。

浮点型

有小数点的数值

Float (m,d)

double(m,d)

其中:

m表示该数值有几位整数，

d表示小数点后面有几位

float (3,2) ; -9.99 — +9.99

字符串

字母，汉字，数字符号，特殊符号构成的数据对象

适用于:身份证号，编号，电话号码。联系地址，名称等

char：定长字符串,长度为 0 - 255。保存char时，在右边填充空格来达到指定长度

varchar ：可变字符串, 长度为 0 - 65535。只保存需要的字符数，另外再加一个字节来记录

如果超出规定长度，char 和 varchar 会对值裁剪以适应，如果裁剪的字符不是空格，会发出警告。

如果超出规定长度，char 和 varchar 会对值裁剪以适应，如果裁剪的字符不是空格，会发出警告。

blob 和text类型

(非二进制字符串）

存储声音，视频，头像等设置

时间和日期类型

date , time 和 datatime

固定的格式数据，存储的是日期和时间

date类型

存储格式 ： yyyy - mm - dd

支持范围 ： 1000 - 01 - 01 ~~9999 - 12 - 31

time类型

存储格式 : hh : mm : ss

支持范围 ; -838 : 59 : 59 ~~ 838 : 59 : 59

datetime类型

表示日期时间

存储格式 : yyyy - mm - dd hh : mm : ss

支持范围 ：1000 - 01 - 01 00 : 00 : 00 ~~ 9999 - 12 -31 23 : 59 :59

timestamp类型

客户端插入的时间从当前时区转化为UTC（世界标准时间）进行存储。查询时，将其又转化为客户端当前时区进行返回

创建数据表

在数据库中新建数据表

1

create table t1 ; #创建名为 t1 数据表 

创建数据表,规定长度

创建前记得使用数据库

1

use t1 ;  //使用t1数据库

1
2
3
4
5
6
7
8
9

create table t0 
(name char(10),
logintime date,
mail varchar(20),
score float(5,2),
list int(5),
photo text comment"头像");

 #创建名为 t0 数据表

comment ： 解释。一般放在数据类型后面，表示对于字段名的解释,可以理解成为注释。

1

comment "解释内容"  #comment使用方法

创建临时表

1
2
3

create temporary table (N1) ;

create temporary table () ; //创建临时数据表

N1包含两个字段：id 和 name

查看数据表

查找数据表

XX为需要查找名

1
2
3
4

show table like "XX%" ; 

//可以查找多个字符,只要指定包含查找字段就会被查找到
//如果查找 "X%",则会出现 XXX,XYX,XXYXY,XXYXYX

1
2
3

show table like"XX_" ;
//只能查找单个字符
//如果查找"X_",则会出现XX,

查看数据表的列

1
2
3

//desc + 表名 +列名 ;
//查看teacher表中的teachearID列
desc teacher teacherID ;

查看数据表状况

1

show table status ;  //查看数据表

但是如果数据表太多会难以查找，需要在末尾加个\G，变为横向排列

1

show table status \G

如果加了 \G ，就不需要结束符了，否则会报错

查看存在数据表的名称

1

show table + 表名 ; #查看存在数据表的名称

查看数据表结构

1

desc/describe + 表名 ; #查看数据表结构

查看表的定义语句

查看表里有什么

1
2
3

//show create table + 表名 ;

show create table score ;

查看表的结构

使用describe可查看字段定义，键的信息，默认值等

1
2

 //desc / describe + 表名 ;
describe score ;

根据指定条配件来查询

消除结果集中的重复行

使用 Distinct 关键字

1

SELECT DISTINCT [字段名1，字段名2] FROM 表名 ;

多个字段名的话，只有两个字段完全相同。DISTINCT关键字才会同时消除

实现有条件筛选

1
2
3
4

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M

多条件查询

修改数据表

alter table + 表名

1

 alter table + 表名 ；

[warning]注意，以下修改数据表所有操作都要加alter table + 表名。[/warning]

添加新字段

1

alter table 表名 add  字段名 + 字段名定义 ；

可以在末尾加first 或者 aftear 来在指定字段前新增字段

添加默认值和删除默认值

1

alter table 表名 alter  字段名 set default + 默认值 ; //修改默认值

1

alter table 表名 alter  字段名 drop default ; // 删除默认值 

修改字段名

1

alter table 表名 change  旧字段名 + 新字段名 + 字段 ;

修改字段定义

1

alter table 表名 modify  + 字段名 + 字段的新定义  ;

1

alter table student modify  studentmark text ;

删除字段

1

alter table 表名 drop + 字段名 ;

删除多个字段

1

alter table 表名 drop 字段1 drop 字段2 ;

为表重命名

1

alter table 表名 rename to + 新表名 ;

实例

为表添加备注字段，字段名为studentremark,数据类型为varcgar(100)，放置在stuname字段之后

1

alter table score add studentremark varchar(100) after stuname ;

为score添加一个字段，字段名为classid 数据类型为varchar(10)，默认值为10

1

alter table score add classid varchar(10) set default 10 after studentremark ;

验证

1
2

#desc + 表名 ;
desc score ;

为字段增加内容

1

insert into 表名 values(内容1，内容2);

分字段批量增加内容

1

insert into 表名(字段名1，字段名2)values(内容1,内容2) ;

添加多组内容

1

insert into 表名 values(内容1,内容2)(内容3,内容4) ;

将指定字段导入到新表中

1
2

//insert into 目标表(字段1，字段2) select (字段1，字段2) from 来源表
insert into student(Name，StudentID) select (Name，StudentID) from StuentClass

update修改表的数据

1

//update 表名 set 字段名1 = 值1，字段名2 = 值2 where 条件

[warning]update修改的是表中数据，不是字段[/warning]

多表数据修改

1

update 表名列表 set 表名.字段名1 =  值1, 表名.字段名2 = 值2 where 表名.条件 ；

表名.字段名 表示表中的某个字段

按条件删除数据

delete , truncate来删除

使用delete 删除

删除表中++所有数据：

1

//delete from 表名 ;

删除表中的部分数据

1

delete from 表名 where 条件 ；

区别

Delete: 删除部分数据或者所有数据，执行速度较慢，删除后日志中可找回
truncate：删除表中所有数据，删除速度较快，不能使用where子句，无法找回

复制数据表

create [if not exists]新表名

[like 参照表名]

[al {selsct 语句}]

like 像：创建一个和草诏表名结果相同的新表，不复制内容，其他的都复制

as 完全复制表的内容，单索引和完整性拘束不会复制，selsct语句是一个表达式，可以是一个selsc语句

1
2
3

use bookstore;
create table book like book_copy1;
create table book_copy2 as(select * from book);

删除数据表

dorp table [if not exists] + 表名

1

dorp table if not exists student ;

其他

存储引擎：对不同表类型的处理器

默认:innoDB

Engine = innoDB

1

create table (字段名 数据类型)engine = innoDB ;

让其识别中文字符

1

set names gbk ;

约束

何为约束?

对字段添加限制，称为约束。

约束的增加

[collapse title=“约束关键字以及定义语句”]主体部分，由于对于字段取值的各种不同的限定，因此，表中的约束也分为若干种，每种约束的关键字不同。约束的定义语句也不同。[/collapse]

1

constraint 约束名  

创建表的时候，直接为字段定义约束

1

create table 表名(字段 数据类型 [约束定义语句])；

创建表后，修改字段定义增加约束

1

alter table 表名 add[constraint + 约束名]+约束的定义语句

主键约束(PRIMARY KEY)

确定表中的标识列（主键字段不能为空，必须唯一）

定义主键约束

1
2
3

create table DDD(
DDDid char(10) primary key,
DDDname varchar(10));//创建名为DDD的表。创建时，将DDDid约束为主键

1
2
3
4

create table DDD(
DDDid char(10),
DDDname varchar(10),
primary key(DDDid));//创建表后，约束主键

为约束命名

1
2
3
4
5

create table DDD(
DDDid char(10),
DDDname varchar(10),
primary key(DDDid),
constraint pk_id primary key(DDDid))；

组合主键

表中主键字段需要多个字段组合起来充当，来满足主键约束的要求

特征 ： 每个字段允许有重复值，但是组合在一起不许重复，而组合中每个字段都不可以取空值，

格式 :

1

[constraint 约束名]primary key[(字段名1，字段名2)];

注意：由于组合主键萨河及多个字段因此，组合主键只能定义表级约束，定义在表的最后或者被约束字段完成之后。

唯一约束(UNIQUE)

不可重复，可以有空值。

格式:

1
2
3
4
5
6

--建表时添加约束
CREATE TABLE 表名(
字段名 int(10) UNIQUE);  --定义唯一约束

--建表后添加约束
ALTER TABLE 表名 ADD CONSTRAINT 唯一约束名 UNIQUE 字段名 ;

1

unique index_name [字段名]; --索引命名

非空约束（NOT NULL）

不允许空值

如果省略关键字，默认值为null

not null 只能放在字符后面

1
2
3
4
5
6

--建表时添加
Create table Student(
Name varchar(100) not null )； --定义非空约束

--建表后添加
ALTER TABLE student MODIFY Name varchar(100) NOT NULL ; --MODIFY 添加非空约束

检查约束（CHECK）

控制特定列中的值的完整性约束

使用场景：

订购册数 ：（1-100），出版时间等。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

--建表时添加约束
create table s2(
学号 char(10) not null,
性别 char(10) not null,
check ( 学号 in ( select 学号 from student)));


--建表后添加约束
ALTER TABLE s2 ADD CONSRAINT 检查约束名
 CHECK ( 学号 in ( select 学号 from student));

外键约束(FOREIGN KEY)

foreign key 定义在子表中。

一个表中的某一个字段是参考另一个表中某个字段的取值，被称为外键约束。

受约束的称为子表，约束子表的表称为父表。

四个限制

1. 子表中的外键字段必须和父表中的参考字段的数据类型一致。
2. 父表中的参考字段必须被主键约束或者唯一键约束，才能约束子表中的外键约束
3. 父表中的主键值一旦被子表参照，那么这些值就不能随意修改和删除。
4. 子表的外键字段的值可以为空值，但是如果有值必须是在父表的参照列的取值范围内

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

--建表时定义
CREATE TABLE 表名(
字段名  int(10),
CONSTRAINT 外键约束名 
FOREIGN KEY 字段名 
REFERENCES 主表名[主表字段1,主表字段2] ;


--建表后定义
ALTER TABLE 表名 
ADD CONSTRAINT 外键约束名 
FOREIGN KEY 字段名 REFERENCES 主表名[主表字段1,主表字段2] ;

默认约束(DEFAULT)

如果该行没有指定数据，那么系统将默认值赋给该列，如果我们不设置默认值，系统默认为NULL。

定义默认约束

1
2
3
4
5
6

--建表时定义
CREATE TABLE 表名(
字段名 int(10) default 10);  --10为默认值

--建表后定义
ALTER TABLE 表名 ADD DEFAULT 10 FOR 字段名 ;

为表添加数据

1
2

//
insert into s1(Name,ID) selest(Name,ID)from student；