图片 7

MySQL语句总结(一)

其他类型

  1) 位类型 bit

    使用形式: bit[(M)]       其中M是1-64的数字。
表示使用多少二进制数字来存储数据

    插入形式:  b ‘value’   如 b ‘101’

  2) 序列类型 serial

    实质是  bigint  unsigned not null auto_increment unique
的一个别名

  3) 布尔类型 bool

    实质是 tinyint(1) 的一个同义词。 其值为0表示false, 否则表示true

删除表

数据表的创建和键的修改:

CREATE TABLE tb1(
  username VARCHAR(20),
  age TINYINT UNSIGNED,
  money FLOAT (8,2)UNSIGNED
);

创建有三个键的表:

  • 字符20个的username
  • 无符号TINYINT型的 age
  • 无符号型的FLOAT 8位小数点前,2位小数点后

SHOW TABLES FROM mysql;
显示某数据库下的数据表列表

SHOW COLUMNS FROM tb1;

显示某张表所有的键?

INSERT tb1 VALUES ('tom',30,1000.1);

默认插入所有数据

INSERT tb1 (username,money ) VALUES ('jack',1000.1);

插入指定列的值

CREATE TABLE tb2 (
  name VARCHAR(20)      NOT NULL,
  age  TINYINT UNSIGNED NOT NULL
);

创建键不为空值的表

CREATE TABLE tb3(
  sid  INT  PRIMARY KEY AUTO_INCREMENT,
  sname VARCHAR(20) NOT NULL ,
  sinfo VARCHAR(255)
);

创建主键为sid的数据表,且sid为自增

在主键没有设置自增属性的情况下,不允许出现同主键的一行数据

CREATE TABLE tb4(
  sid  INT  PRIMARY KEY ,
  sname VARCHAR(20) NOT NULL UNIQUE KEY,
  sinfo VARCHAR(255)
);

创建主键为sid的ta4 sname为不为空的且UNIQUE
KEY,此表中sid不可重复,情况下sname也不可相同。

UNIQUE KEY 作用的key和主键一同约束,保证数据唯一性

CREATE TABLE tb5(
  sid  INT  PRIMARY KEY AUTO_INCREMENT ,
  sname VARCHAR(20) NOT NULL UNIQUE KEY,
  sinfo VARCHAR(255) DEFAULT 'no summary'
);

创建tb5在sinfo没有赋值的情况下,默认会置为no summary字符串

DEFAULT 对字段进行默认初始赋值

约束分为列级约束(约束一个字段)表级约束(约束多个字段)约束包括:

  • NOT NULL(非空约束)
  • PRIMARY KEY(主键约束)
  • UNIQUE KEY (唯一约束)
  • DEFAULT (默认约束)
  • FOREIGN KEY(外键约束)

关于外键的约束要求:

  1. 父表和子表必须使用相同的存储引擎,且禁止使用临时表
  2. 数据表的存储引擎只能是InnoDB
  3. 外键列和参照列必须有相似的数据类型,其中数字的长度或是否有符号位必须相同,而字符的长度可以不同
  4. 外键列和参照列必须创建索引。如果外键列不存在索引的话,mysql将自动创建索引。

外键约束的参照操作:

  1. CASCADE 从父列表删除或更新且自动删除或更新子表中匹配的行
  2. SET NULL
    从父表删除或更新行,并设置子表中的外键列为NUL。如果使用该选项,必须保证子表列没有指定的NOT
    NULL
  3. RESTRICT :拒绝对父表的删除或更新操作。
  4. NO ACTION 标准的SQL的关键字,在Mysql中与RETRICT相同

修改数据表:
ALTER TABLE person ADD info VARCHAR(20) DEFAULT 'no summary'
添加一列
ALTER TABLE person DROP info
删除某一列

ALTER TABLE person DROP PRIMARY KEY

删除person表的主键的约束

ALTER TABLE person ADD FOREIGN KEY (info) REFERENCES test2 (id)

person表中info添加test2中id外键

ALTER TABLE person ALTER pname SET DEFAULT 'fuck'

修改person表中pname字段的默认值

ALTER TABLE person ADD UNIQUE (pname);

给person表中 pname添加UNIQUE属性

ALTER TABLE person DROP FOREIGN KEY person_ibfk_1

删除person的名为person_ibfk_1的外键
在外键建立的时候,系统会为主键指定一个名字(通过SHOW CREATE TABLE person
语句查看建表外键名字),删除时使用即可删除外键约束

ALTER TABLE person MODIFY pid SMALLINT

修改person表中的pid的数据类型 ,(后面的语句都可修改)

ALTER TABLE person MODIFY pid SMALLINT AFTER pname

修改person的pid属性在表中的位置(在pname属性后)

ALTER TABLE person CHANGE pid p_id INT FIRST

修改person表中pid 的名称(p_id)和属性 (INT) 以及在表中的位置(FIRST)

ALTER TABLE person RENAME per

修改perosn在表的名字为(per)

RENAME TABLE per TO person

改名字的另一种方式将(per)(改为peroson)

图片 1

97092E47-D5C4-4895-A04B-55B95FBB4D1E.png

主要:

修改表的基本形式
alter table 表名 修改语句;

MySQL的数据类型:

字段类型

  1) 形式:

字段名  类型名 [(M)] [unsigned] [zerofill]

     M:显示长度 与zerofil结合使用 
不够长度左侧自动补0
                设置zerofill, 则表示该字段同时具备unsigned。

  2) 主要分3类

    数值型,字符型,时间型

关系数据库

SQL: Struct Query
Language(结构化查询语言)
,它是一个”国际化标准”。各种关系数据库都是实现该标准的一种产品。

Mysql数据库基础操作:

  • SHOW DATABASES; 显示所有数据库

  • CREATE DATABASE name;创建name数据库

  • CROP DATABASE name; 删除数据库

  • SHOW WARNINGS; 显示警告信息

  • SELECT DATABASE(); 显示当前所在的数据库

  • CREATE TABLE name; 创建name表


创建表

基本形式:
create table [if not exists] 表名(字段列表,[约束或索引列表]) [表选项列表]
字段列表如果是多个,中间用逗号隔开
字段基本形式:字段名 类型[字段修饰属性]

表选项:
表选项就是对一个表的有关属性的设定,通常都不需要。如果不设定,有默认值。

有以下几个可用:
comment = '表的注释';

charset = 字符编码名称;//跟数据的字段编码设定一个意思。
字符编码设定的范围及继承关系:

  1. 系统级设定:安装时确定了
  2. 库级设定:建库时设定
  3. 表级设定:charset就是表级设定
  4. 字段级设定:作为字段属性出现

auto_increment = 起始整数;//自增长类型值初值,默认是1

engine = "表的存储引擎名";

  1. 存储引擎就是将数据存入硬盘的方式方法
![](https://upload-images.jianshu.io/upload_images/3022339-5c4a20216d143001.png)

存储引擎对比

字段属性:

not null:不为空,表示不能为null,不写默认为空
auto_increment:int类型字段的值可以”自增长”,即其值无须”写入”,而会自动获得并添加
primary
key:主键,一个表只能有一个主键,是唯一键的强化,不能重复,并且不能使用null,并且可以作为任意一行数据的”关键值”,大多数表使用id并自增长类型作为主键
unique key:唯一键,该字段所有行行的值不可以重复
default:一个字段在没有插入数据的时候自动使用值
comment:字段注释

//范例
create table tab1(
id int auto_increment primary key not null,
f1 float unique,
f2 decimal(20,5) default 12.3,
f4 varchar(20) comment'这是一个注释'
);

图片 2

执行结果

索引:
索引是一个”内置表”,该表的数据是对某个真是表的某个字段的数据做了”排序”之后的存储形式。
作用:极大提高表查找速度,效率类似二分查找,但会降低增删改的速度,对创建表来说,建立索引非常简单:key(字段1,字段2····)unique key(字段1,字段2····)primary key(字段1,字段2····)fulltext(字段1,字段2····)

//范例
create table tab2(
id int auto_increment not null,
f1 float,
f2 decimal(20,5) default 12.3,
f4 varchar(20) comment'这是一个注释',
primary key(id),/*其实和写在字段上作为属性意义一样,但是出现在这里叫做主键索引,出现的场合不同叫法不同而已*/
unique key(f1),/*同上*/
key(f2)
);

约束:
约束是一种限定数据以符合某种要求的形式(机制)

约束主要有:
primary key:主键索引,主键属性,主键约束其实作用一样
unique key:唯一索引,唯一属性,唯一索引其实作用一样
foreign
key:外键约束,此值其实是其他表的主键,所以不能修改。foreign key (字段名1,字段名2,···)references 表名2(字段名1,字段名2,···)

图片 3

外键图示

TEXT和BLOB 类型:

对于字段长度要求超过 255 个的情况下,MySQL 提供了 TEXT 和 BLOB
两种类型。根据存储数据的大小,它们都有不同的子类型。这些大型的数据用于存储文本块或图像、声音文件等二进制数据类型。

TEXT 和 BLOB 类型在分类和比较上存在区别。BLOB 类型区分大小写,而 TEXT
不区分大小写。大小修饰符不用于各种 BLOB 和 TEXT
子类型。比指定类型支持的最大范围大的值将被自动截短。

图片 4

删除数据库

drop database 数据库名;

字符串类型

字符串类型 字节大小 描述及存储需求
CHAR 0-255字节 定长字符串
VARCHAR 0-255字节 变长字符串
TINYBLOB 0-255字节 不超过 255 个字符的二进制字符串
TINYTEXT 0-255字节 短文本字符串
BLOB 0-65535字节 二进制形式的长文本数据
TEXT 0-65535字节 长文本数据
MEDIUMBLOB 0-16 777 215字节 二进制形式的中等长度文本数据
MEDIUMTEXT 0-16 777 215字节 中等长度文本数据
LOGNGBLOB 0-4 294 967 295字节 二进制形式的极大文本数据
LONGTEXT 0-4 294 967 295字节 极大文本数据
VARBINARY(M) 允许长度0-M个字节的定长字节符串,值的长度+1个字节
BINARY(M) M 允许长度0-M个字节的定长字节符串


创建数据库

creat database [if not exists] 数据库名 [charset 字符集] [collate 字符排序规则];
说明:

  1. if not exists:用于判断是否存在该数据库名,如果存在则不执行该语句。
  2. 字符集:数据存储到数据库中时用的编码名称
  3. 字符排序规则通常不设置,即使用默认排序规则

INT 类型:

整数类型 字节 范围(有符号) 范围(无符号) 用途
TINYINT 1字节 (-128,127) (0,255) 小整数值
SMALLINT 2字节 (-32 768,32 767) (0,65 535) 大整数值
MEDIUMINT 3字节 (-8 388 608,8 388 607) (0,16 777 215) 大整数值
INT或INTEGER 4字节 (-2 147 483 648,2 147 483 647) (0,4 294 967 295) 大整数值
BIGINT 8字节 (-9 233 372 036 854 775 808,9 223 372 036 854 775 807) (0,18 446 744 073 709 551 615) 极大整数值
FLOAT 4字节 (-3.402 823 466 E+38,1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38) 0, (1.175 494 351 E-38,3.402 823 466 E+38) 单精度浮点数值
DOUBLE 8字节 (1.797 693 134 862 315 7 E+308,2.225 073 858 507 201 4 E-308),0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) 0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) 双精度浮点数值
DECIMAL 对DECIMAL(M,D) ,如果M>D,为M+2否则为D+2 依赖于M和D的值 依赖于M和D的值 小数值

在 MySQL 中支持的 5 个主要整数类型是

  • TINYINT
  • SMALLINT
  • MEDIUMINT,
  • INT
  • BIGINT

这些类型在很大程度上是相同的,只有它们存储的值的大小是不相同的。
MySQL 以一个可选的显示宽度指示器的形式对 SQL
标准进行扩展,这样当从数据库检索一个值时,可以把这个值加长到指定的长度。例如,指定一个字段的类型为
INT(6),就可以保证所包含数字少于 6
个的值从数据库中检索出来时能够自动地用空格填充。需要注意的是,使用一个宽度指示器不会影响字段的大小和它可以存储的值的范围。
万一我们需要对一个字段存储一个超出许可范围的数字,MySQL
会根据允许范围最接近它的一端截短后再进行存储。还有一个比较特别的地方是,MySQL
会在不合规定的值插入表前自动修改为 0。

  • UNSIGNED
    修饰符规定字段只保存正值。因为不需要保存数字的正、负符号,可以在储时节约一个“位”的空间。从而增大这个字段可以存储的值的范围。
  • ZEROFILL 修饰符规定
    0(不是空格)可以用来真补输出的值。使用这个修饰符可以阻止 MySQL
    数据库存储负值。
  • FLOAT、DOUBLE 和 DECIMAL 类型MySQL 支持的三个浮点类型是
    FLOAT、DOUBLE 和 DECIMAL 类型。FLOAT
    数值类型用于表示单精度浮点数值,而 DOUBLE
    数值类型用于表示双精度浮点数值。与整数一样,这些类型也带有附加参数:一个显示宽度指示器和一个小数点指示器。比如语句
    FLOAT(7,3) 规定显示的值不会超过 7 位数字,小数点后面带有 3
    位数字。对于小数点后面的位数超过允许范围的值,MySQL
    会自动将它四舍五入为最接近它的值,再插入它。DECIMAL
    数据类型用于精度要求非常高的计算中,这种类型允许指定数值的精度和计数方法作为选择参数。精度在这里指为这个值保存的有效数字的总个数,而计数方法表示小数点后数字的位数。比如语句
    DECIMAL(7,3) 规定了存储的值不会超过 7 位数字,并且小数点后不超过 3
    位。忽略 DECIMAL 数据类型的精度和计数方法修饰符将会使 MySQL
    数据库把所有标识为这个数据类型的字段精度设置为 10,计算方法设置为
    0。UNSIGNED 和 ZEROFILL 修饰符也可以被 FLOAT、DOUBLE 和 DECIMAL
    数据类型使用。并且效果与 INT 数据类型相同。

  text 类型

  概念: 长文本字符类型。其中存储的数据不占表格中的数据容量限制

  其他同类型: smalltext  tinytext  longtext

php中操作数据库的基本代码和流程

CHAR和VARCHAR类型
  • CHAR
    类型用于定长字符串,并且必须在圆括号内用一个大小修饰符来定义。这个大小修饰符的范围从
    0-255。比指定长度大的值将被截短,而比指定长度小的值将会用空格作填补。CHAR
    类型可以使用 BINARY 修饰符。当用于比较运算时,这个修饰符使 CHAR
    以二进制方式参于运算,而不是以传统的区分大小写的方式。

  • VARCHAR 类型的一个变体是 VARCHAR
    类型。它是一种可变长度的字符串类型,并且也必须带有一个范围在 0-255
    之间的指示器。CHAR 和 VARCHGAR 不同之处在于 MYSQL
    数据库处理这个指示器的方式:CHAR
    把这个大小视为值的大小,不长度不足的情况下就用空格补足。而 VARCHAR
    类型把它视为最大值并且只使用存储字符串实际需要的长度(增加一个额外字节来存储字符串本身的长度)来存储值。所以短于指示器长度的
    VARCHAR 类型不会被空格填补,但长于指示器的值仍然会被截短。因为
    VARCHAR
    类型可以根据实际内容动态改变存储值的长度,所以在不能确定字段需要多少字符时使用
    VARCHAR 类型可以大大地节约磁盘空间、提高存储效率。
    VARCHAR 类型在使用 BINARY 修饰符时与 CHAR 类型完全相同。

  set 类型

    概念: 多选项字符串数据类型, 使用时,需要给定
固定的几个选项。 存储的时候,存储其中若干个值

    形式: set(“选项1”,
“选项2”,….)

    实际存储: 字符串选项值对应数字
1,2,4,8,16… 最多64个选项

    写入数据形式: 可以用选项字符串并用逗号隔开,
也可以使用对应的“数字的和”

    适用于: 存储表单界面中的 “多选项值”

图片 5图片 6

-- enum  set 使用Demo
create table enum_set(
   id int unsigned auto_increment primary key,
   gender enum('男','女'),
   fav set('篮球','足球','跑步','读书','写字')   
);

insert into enum_set (id, gender, fav) values(null,'男','篮球'),(null,1,1); -- 两行enum字段一致

insert into enum_set (id, gender, fav) values(null, '男', '篮球,跑步,写字'),(null,1,19); -- 两行set字段一致

--  篮球=1 跑步=2 写字=16  ==> 对应的和19

select * from enum_set;

【点击查看】set与enum操作Demo

图片 7

数据库本质和表现

本质:将大量的”数据值”通过数据库管理系统进行复杂但逻辑结构清晰有序的存储,效率高的惊人。
表现:存储在指定文件夹中。

  备份与恢复数据

      通常是用于数据库的“迁移”:将某台数据库服务器中的某个库,“搬迁”到另一个数据库服务器中去

      也常用于数据的“安全备份”:就是数据库实际运行中,防止数据库系统崩溃,防止数据库被黑客入侵

 

图片 8图片 9

备份 【管理员身份】: 
    mysqldump -h 服务器地址 -u 登录名 -P 端口号 -p 数据库名>完整路径的文件名

恢复: 
    未登录恢复: 
        mysql -h 服务器地址  -u 登录名 -P 端口号 -p  数据库名<完整路径的sql文件名

    登录后恢复:
        source '备份数据文件的完整路径'        

【点击查看】备份与恢复形式

视图创建形式
/**创建*/
create view 视图名[(列名1,列名2,···)] as 一条复杂的select语句;
/*修改*/
alter view 视图名 [(列名1,列名2,···)] as select 语句;
/*删除*/
drop view [if exists] 视图名;

基础

视图的使用

其实就是当做一个查询表来用(通常只用于select)

select * from 视图名 where 条件 order by···

  基本概念

  1) 数据库分类

    层次数据库,网状数据库,关系数据库

    常见:SQL Server, Oracle,infomix,sybase,ibmDB2,Mysql

  2)数据库存储的本质和表现:

    存储本质: 将 数据值 通过数据管理系统进行有序的存储

    存储表现:

      每个数据库对应一个文件夹

      每个数据表对应一个或多个文件
  3)mysql数据库的各层次关系:

    1个数据库系统包含有多个数据库

    1个数据库可以有多张数据表

     一个数据表可以有多个字段(数据项)和多行数据(记录)

  4)基本概念

    sql :是关系数据库的 语言标准。 Struct Query Language
结构化查询语言

    数据库database: 存储各种数据data

    数据库管理系统dbms: Database Management system

    数据表table,字段field,列column,行row,记录record

基本流程
  1. 连接数据库:$mylink = mysqli_connect("localhost",'root','root',"tempdb3");
    坑:mysql_connect在 PHP 5.5.0 起已废弃,并在将来会被移除。应使用
    MySQLi 或 PDO_MySQL 扩展来替换之。
  2. 执行sql命令:$result = $mylink->query("几乎任何sql语句")
    返回的结果通常分两种情况进行处理
    2.1 如果无返回数据的语句
    2.1.1 如果$result为true,表示执行成功
    2.1.2 如果$result为false,表示执行失败
    2.2 如果是有返回数据的语句
    2.2.1 如果$result为false,表示执行失败
    2.2.2 否则,执行成功,需要继续从结果中取出数据并显示出来