php mysql笔记_MYSQL笔记

Myth丶恋晨 2022-10-26 14:25 216阅读 0赞

数据库基本概念

命令行链接mysql-hlocalhost-P3306-u'username'-p'password'host

\-h

主机

port

\-P

端口号

user

\-u

用户名

Password

\-p

密码

\-h链接本地可以省略，-P默认端口可以省略

退出

exit

quit

\\q

数据库、表相关数据库：数据库中存放的是表，一个数据库可以存放多个表

表：表是用来存放数据的

关系： 两个表的公共字段

行：也称记录，也称实体

列：也称字段，也称属性

就表结构而言，表分为行和列。就数据而言，表分为记录和字段。面向对象而言，一个记录就是一个实体，一个字段就是一个属性。

数据相关数据冗余：相同的数据冗余只能减少，不能杜绝

冗余减少的方法就是分表，同时降低查询速度数据完整性

正确性+完整性

![1c21bb426c38eca70d06298836b14513.png][]

正确性：数据类型正确

准确性：数据范围要准确数据库执行执行过程

![b8f5159b220329c5266082dae6ac3570.png][]

操作数据库显示数据库show databases;

![5ef6c67a8006707ac74f0ad8fe8adb79.png][]

创建数据库create database\[ifnotexists\]数据名\[选项\]

![e7538931a7135eb482a153bd3197b33c.png][]

\#特殊字符创建数据库需要用反引号包括

\#创建数据库时指定使用的字符编码

\#如果不指定字符编码，数据库默认使用安装数据库时的指定编码

create database\`emp\`charset=gbk;

删除数据库

语法：

drop database\[ifexists\]\`database-name\`;

显示创建数据库的语句

语法：

show create database database-name;

修改数据库

语法：

\#修改数据库的字符编码

alter database database-name charest=utf8;

选择数据库

语法：

usedatabase-name;

表操作创建表

语法：

create table\[ifnotexists\]\`表名\`(

\`字段名\`数据类型\[null|nonull\]\[default\]\[auto\_incremment\]\[primary key\]\[comment\],

\`字段名\`数据类型\[null|nonull\]\[default\]\[auto\_incremment\]\[primary key\]\[comment\]

)\[engine=\]\[charset=utf8\]

null|nonull是否为空

default默认值

auto\_incremment自动增长

primary key主键值不能为空，每个表只有一个主键

comment备注

engine引擎决定了数据的储存和查找myisam、innodb

\#设置客户端和服务器通讯的编码

MariaDB\[data\]>setnames gbk;

QueryOK,0rows affected(0.000sec)

\#创建简单的表

MariaDB\[data\]>create table stu1(

\->idintauto\_increment primary key,

\->bane varchar(20)notnull

\->)engine=innodb;

QueryOK,0rows affected(0.008sec)

\#创建复杂的表

MariaDB\[data\]>create table stu2(

\->idintauto\_increment primary key comment'主键',

\->name varchar(20)notnullcomment'姓名',

\->scoreintdefault'0'comment'成绩可以为空'

\->)engine=innodb;

QueryOK,0rows affected(0.007sec)

小结：

如果不指定引擎，默认是innodb

如果不指定编码，默认和数据库一样

innodb 一个表对应一个表结构，innodb的所有表的数据都保存在ibdatak文件中，如果数据量很大，会自动创建ibdata2、ibdata3···

| 引擎 | |

| ——— | —————————————————————————————— |

| myisam | 查询速度快、容易产生碎片、不能约束数据 |

| innodb | 以前没有myisam速度快，现在已经提速了、不产生碎片、可以约束数据 |

推荐使用innodb。

显示创建表的语句

语法：

show create table;--结果横着排列

show create table stu2\\G;--结果竖着排列

查看表结构

语法:

desc\[ribe\]stu2;

MariaDB\[data\]>decsribe stu2;

ERROR1064(42000):Youhave an errorinyour SQL syntax;check the manual that corresponds to yourMariaDBserver versionforthe right syntax tousenear'decsribe stu2'at line1

MariaDB\[data\]>describe stu2;

\+-------+-------------+------+-----+---------+----------------+

\+-------+-------------+------+-----+---------+----------------+

|id|int(11)|NO|PRI|NULL|auto\_increment|

|name|varchar(20)|NO||NULL||

|score|int(11)|YES||0||

\+-------+-------------+------+-----+---------+----------------+

3rowsinset(0.002sec)

删除表

语法:

drop table\[ifexists\]stu4,stu3,stu2;

\#删除一个表

MariaDB\[data\]>drop tableifexists sut4;

QueryOK,0rows affected,1warning(0.000sec)

\#删除多个表

MariaDB\[data\]>drop tableifexists sut4,stu3;

QueryOK,0rows affected,2warnings(0.000sec)

复制表

语法一：

create table stu3selectid form stu2;

\#不能复制主键，只能复制数据

语法二：

create table stu4 like stu2;

\#只能复制表结构，不能复制数据

小结：\*代表所有数据

修改表

语法：

alter table stu2;

添加字段：alter table 表名add \[column\] 字段名 数据类型 \[位置\]

\`\`mysql

MariaDB \[data\]> alter table stu addadd\` varchar(20); — 默认添加的字段放在末尾

Query OK, 0 rows affected (0.007 sec)

Records: 0 Duplicates: 0 Warnings: 0

MariaDB \[data\]> alter table stu add sex char(1) after name; — 放在某个之后

Query OK, 0 rows affected (0.006 sec)

Records: 0 Duplicates: 0 Warnings: 0

MariaDB \[data\]> alter table stu add age int first; — 放在最前面

Query OK, 0 rows affected (0.006 sec)

Records: 0 Duplicates: 0 Warnings: 0

2.删除字段：alter table表名drop\[column\]字段名

\`\`\`mysql

MariaDB \[data\]> desc stu;

\+-------+-------------+------+-----+---------+-------+

\+-------+-------------+------+-----+---------+-------+

| age | int(11) | YES | | NULL | |

| id | int(11) | YES | | NULL | |

| sex | char(1) | YES | | NULL | |

| add | varchar(20) | YES | | NULL | |

\+-------+-------------+------+-----+---------+-------+

5 rows in set (0.001 sec)

MariaDB \[data\]> alter table stu drop age; -- 删除字段

Query OK, 0 rows affected (0.010 sec)

Records: 0 Duplicates: 0 Warnings: 0

MariaDB \[data\]> desc stu;

\+-------+-------------+------+-----+---------+-------+

\+-------+-------------+------+-----+---------+-------+

| id | int(11) | YES | | NULL | |

| sex | char(1) | YES | | NULL | |

| add | varchar(20) | YES | | NULL | |

\+-------+-------------+------+-----+---------+-------+

4 rows in set (0.001 sec)修改字段(改名):alter table 表change\[column\]原字段名 新字段名 数据类型

\`\`\`mysql

— 将name字段改为stuname

MariaDB \[data\]> alter table stu change name stuname varchar(20);

Query OK, 0 rows affected (0.007 sec)

Records: 0 Duplicates: 0 Warnings: 0

MariaDB \[data\]> desc stu

\->;

\+————-+——————-+———+——-+————-+———-+

\+————-+——————-+———+——-+————-+———-+

| id | int(11) | YES | | NULL | |

| sex | char(1) | YES | | NULL | |

| add | varchar(20) | YES | | NULL | |

\+————-+——————-+———+——-+————-+———-+

4 rows in set (0.001 sec)

4.修改字段：alter table表modify字段名字段属性·

\`\`\`mysql

\-- 将sex数据类型更改为varchar(20)

MariaDB \[data\]> alter table stu modify sex varchar(20);

Query OK, 0 rows affected (0.015 sec)

Records: 0 Duplicates: 0 Warnings: 0

MariaDB \[data\]> desc stu;

\+---------+-------------+------+-----+---------+-------+

\+---------+-------------+------+-----+---------+-------+

| id | int(11) | YES | | NULL | |

| sex | varchar(20) | YES | | NULL | |

| add | varchar(20) | YES | | NULL | |

\+---------+-------------+------+-----+---------+-------+

4 rows in set (0.001 sec)

\-- 将add数据类型更改为varchar(20)默认值‘where？’

MariaDB \[data\]> alter table stu modify \`add\` varchar(20) default 'where?';

Query OK, 0 rows affected (0.006 sec)

Records: 0 Duplicates: 0 Warnings: 0

MariaDB \[data\]> desc stu;

\+---------+-------------+------+-----+---------+-------+

\+---------+-------------+------+-----+---------+-------+

| id | int(11) | YES | | NULL | |

| sex | varchar(20) | YES | | NULL | |

| add | varchar(20) | YES | | where? | |

\+---------+-------------+------+-----+---------+-------+

4 rows in set (0.001 sec)修改引擎：later table 表名engine= 引擎名

\--修改表的引擎

MariaDB\[data\]>alter table stu engine=myisam;

QueryOK,0rows affected(0.008sec)

Records:0Duplicates:0Warnings:0

修改表名 alter table 表名 rename 新表名

\--将表名改为新表名

MariaDB\[data\]>alter table stu rename stu1;

QueryOK,0rows affected(0.000sec)

MariaDB\[data\]>show tables;

\+----------------+

|Tables\_in\_data|

\+----------------+

|stu1|

\+----------------+

1rowinset(0.000sec)

将表移动到其他数据库

\--将当前数据库的表移动到目标数据库

MariaDB\[data\]>alter table stu rename to php.stu;

QueryOK,0rows affected(0.001sec)

数据操作插入数据

语法：insert into

\--插入数据，null为yes时可以为空

MariaDB\[php\]>insertintostu(id,stuname,sex,\`add\`)values(1,'tom','man','beijing');

QueryOK,1row affected(0.004sec)

\--插入控值为null

\--插入默认值为default

MariaDB\[php\]>insertintostu values(5,'jake',null,default);

QueryOK,1row affected(0.000sec)

MariaDB\[php\]>select\*fromstu;

\+------+---------+-------+-----------+

|id|stuname|sex|add|

\+------+---------+-------+-----------+

|1|tom|man|beijing|

|3|ros|woman|chongqing|

\+------+---------+-------+-----------+

4rowsinset(0.000sec)

小结：

插入字段名的顺序和表中的顺序可以不一致

插入值的个数、顺序必须和插入字段名的个数、顺序要一致更新数据

语法：update 表名 set 字段=值\[where条件\]

\--将tom的性别给为woman

\--多个链接

MariaDB\[php\]>update stusetsex='woman'wherestuname='tom';

QueryOK,1row affected(0.000sec)

Rowsmatched:1Changed:1Warnings:0

MariaDB\[php\]>select\*fromstu;

\+------+---------+-------+-----------+

|id|stuname|sex|add|

\+------+---------+-------+-----------+

|1|tom|woman|beijing|

|3|ros|woman|chongqing|

|6|李白|man|hunan|

|7|杜甫|man|wuhan|

\+------+---------+-------+-----------+

6rowsinset(0.000sec)

删除数据

语法：delete from 表名\[where条件\]

\--删除stuname为tom的数据

MariaDB\[php\]>deletefromstuwherestuname='tom';

QueryOK,1row affected(0.000sec)

MariaDB\[php\]>select\*fromstu;

\+------+---------+-------+-----------+

|id|stuname|sex|add|

\+------+---------+-------+-----------+

|3|ros|woman|chongqing|

|6|李白|man|hunan|

|7|杜甫|man|wuhan|

\+------+---------+-------+-----------+

5rowsinset(0.000sec)

技巧：

deletefrom表:遍历记录，一条一条删除

truncate table表:删除表在重新建立表结构(效率更高)

查询数据

语法：

select列名from表名

\--从stu查询id数据

MariaDB\[php\]>selectid,stunamefromstu;

Emptyset(0.000sec)

数据传输是使用的

发现：插入数据的时候，如果有中文会报错或者无法插入

可用set names utf8；直接更改client、connection、result

设置什么编码取决于客户端的编码

查看客户端发送的编码

查看服务器接受，返回的编码

MariaDB\[(none)\]>show variables like'character\_set\_%';

\--更改编码

MariaDB\[(none)\]>setcharacter\_set\_client=utf8;

QueryOK,0rows affected(0.000sec)

校对集collate=校对集\#创建表时可加入

校对集规则

\_bin 区分大小写

\_ci 不区分大小写

补充

严格模式

sql-mode="...,STRICT\_TRANS\_TABLES"

插入数据时主键冲突

\--语法一

replaceintostu values(id,'name')

\# 原理：如果插入的主键不重复就直接插入，如果主键重复就替换(删除原来的记录，插入新记录)

\--语法二

on duplicate key update\#当插入的值与主键冲突，执行 update操作

\# 插入的数据和主键或唯一键起冲突，更新字段内容

delimiter// -- 改变定界符

数据类型

数值型

mysql中的数据类型是强类型

整数整型

占用字节

范围

无符号

tinyint

\-128～127

0-256

samllint

\-32768～32767

mwdiumint

\-8388608～8388607

int

\-247483648～2147483647

bigint

\-9223372036854775808～9223372036854775807

例题：

\--数据类型：选择范围尽量的小，占用的资源就小

MariaDB\[learn\]>create table stu;

ERROR1113(42000):A table must have at least1column

MariaDB\[learn\]>create table stu(

\->id tinyint,

\->name varchar(20)

\->);

QueryOK,0rows affected(0.112sec)

无符号整型(unsigned) 无符号整型就是没有负数，是整数的两倍

整型支持显示宽度，显示宽度是最小的显示位数，如int(11)表示最小占用11位表示，可用zerofill填充

MariaDB\[learn\]>create table stu1(

\->id tinyintunsigned,

\->numint(5)zerofill

\->);

QueryOK,0rows affected(0.006sec)

MariaDB\[learn\]>insertintostu1 values(12,12);

QueryOK,1row affected(0.001sec)

MariaDB\[learn\]>select\*fromstu1;

\+------+-------+

|id|num|

\+------+-------+

|12|00012|

\+------+-------+

1rowinset(0.000sec)

单词

意思

nity

微小的

medium

中间的

big

大的

unsigned

无符号的

浮点型浮点型

float

double

浮点型的声明：float(m,d)、 double(m,d)

m:总位数d：小数位数--mysql支持科学计数法

MariaDB\[learn\]>insertintostu2 values(5E10),(7E-6);

QueryOK,2rows affected(0.001sec)

Records:2Duplicates:0Warnings:0

MariaDB\[learn\]>select\*fromstu2;

\+-------------+

|id|

\+-------------+

|50000000000|

|0.000007|

\+-------------+

2rowsinset(0.000sec)

\--浮动数精度会丢失

MariaDB\[learn\]>insertintostu2 values(99.9999999999);

QueryOK,1row affected(0.000sec)

MariaDB\[learn\]>select\*fromstu2;

\+------+

|id|

\+------+

|100|

\+------+

1rowinset(0.000sec)

定点数(小数)

原理：将小数部分和整数部分分开存储

语法：

decimal(m,d)

例题：

MariaDB\[learn\]>alter table stu3 modify numdecimal(20,11);

QueryOK,0rows affected(0.013sec)

Records:0Duplicates:0Warnings:0

MariaDB\[learn\]>insertintostu3 values(12.99999999999);

QueryOK,1row affected(0.001sec)

MariaDB\[learn\]>select\*fromstu3;

\+----------------+

|num|

\+----------------+

|12.99999999999|

\+----------------+

1rowinset(0.000sec)

deciaml是变长的，m最大为65，d最大时30，默认为(10，2)

定点数和浮点数都支持无符号和zerofill

字符型

在数据库中没有字符串概念，只有字符

数据类型

描述

char

定长字符，最大可到255

varchar

可变长字符， 最大可到65535

tinytext

8个字节

text

16个字节

mediumtext

24个字节

longtext

32个字节

变长字符的剩余空间会自动回收一条整个记录的所有字段的总长度不能超过65535text系列的类型在表中储存的是地址，占用大约10个字节

枚举(enum)

从集合中选择一个值作为数据(单选)

MariaDB\[learn\]>create table stu4(

\->name varchar(20),

\->sexenum('男','女','保密')\#枚举

\->);

QueryOK,0rows affected(0.006sec)

MariaDB\[learn\]>insertintostu4 values('tom','男');

\--插入值部位每句集合中的数据会报错

QueryOK,1row affected(0.001sec)

MariaDB\[learn\]>select\*fromstu4;

\+------+------+

|name|sex|

\+------+------+

|tom|男|

\+------+------+

1rowinset(0.000sec)

枚举值是通过整型数字来管理的MariaDB\[learn\]>insertintostu4 values('berry',2);

QueryOK,1row affected(0.000sec)

MariaDB\[learn\]>select\*fromstu4;

\+-------+------+

|name|sex|

\+-------+------+

|tom|男|

|berry|女|

\+-------+------+

2rowsinset(0.000sec)

限制值

节省空间

运行速度快思考：枚举占用两个字节，所以枚举最多有65536,范围是(0-65535)，由于枚举从1开始，枚举最多65535个

集合(set)

从集合中选择一个值作为数据(多选)

MariaDB\[learn\]>alter table stu3 add hobbyset('爬山','游泳','睡觉','吃饭');

QueryOK,0rows affected(0.006sec)

Records:0Duplicates:0Warnings:0

MariaDB\[learn\]>insertintostu3 values(12.00,'admin','游泳,爬山');

QueryOK,1row affected(0.000sec)

MariaDB\[learn\]>select\*fromstu3;

\+----------------+-------+---------------+

|num|name|hobby|

\+----------------+-------+---------------+

|12.00000000000|admin|爬山,游泳|

\+----------------+-------+---------------+

1rowinset(0.000sec)

MariaDB\[learn\]>

集合和枚举一样，为每个集合属性分配一个固定值，分配方式是从前往后按2的0、1、2、、、次方，转换为二进制的只有一位是1，其他都是0。'爬山','游泳','睡觉','吃饭'

MariaDB\[learn\]>select\*fromstu3;

\+----------------+-------+---------------+

|num|name|hobby|

\+----------------+-------+---------------+

|12.00000000000|admin|爬山,游泳|

\+----------------+-------+---------------+

1rowinset(0.000sec)

MariaDB\[learn\]>selecthobby+0fromstu3;

\+---------+

|hobby+0|

\+---------+

|3|

\+---------+

1rowinset(0.000sec)

集合占8个字节，最多可以有64个选项

日期时间型数据类型

占用

datetime

日期时间占用8个字节

date

日期占用3个字节

time

时间占用3个字节

year

年份占用1个字节

timestamp

时间戳占用4个字节

booleanmysql不支持布尔型，存储为1和0

boolean型在mysql中对应tinyint型

列属性是否为空(null｜not null)null表示字段值可以为空

notnull表示字段值不能为空默认值(default)可以用default关键字，替代默认值。自动增长(auto\_increment)字段值从1开始，每次递增1，自动增长不会有重复，适合用来生成唯一的id。在mysql中只要是自动增长列必须为主键。主键(primary key)唯一标示表中的记录的一个或一组列称为主键

特点：

不能重复、不能为空

一个表只能有一个主键

作用：

保证数据完整性

加快查询速度

选择主键的原则

最少性：尽量选单个键作为主键

稳定性：尽量选择数值更新少的列作为主键--创建主键方法一

MariaDB\[learn\]>create table stu3(

\->idintauto\_increment primary key,

\->name varchar(20)

\->);

QueryOK,0rows affected(0.006sec)

\--创建主键的方法二

MariaDB\[learn\]>create table stue(

\->idint,

\->name varchar(20),

\->primary key(id)

\->);

QueryOK,0rows affected(0.006sec)

\--组合键创建主键方法

MariaDB\[learn\]>create table stu3(

\->classnamechar,

\->name varchar(20),

\->primary key(classname,name)

\->);

QueryOK,0rows affected(0.006sec)

MariaDB\[learn\]>desc stu3;

\+-----------+-------------+------+-----+---------+-------+

\+-----------+-------------+------+-----+---------+-------+

|classname|char(1)|NO|PRI|NULL||

|name|varchar(20)|NO|PRI|NULL||

\+-----------+-------------+------+-----+---------+-------+

2rowsinset(0.002sec)

\#添加主键

MariaDB\[learn\]>alter table sut3 add primary key(id);

QueryOK,0rows affected(0.008sec)

Records:0Duplicates:0Warnings:0

MariaDB\[learn\]>desc sut3;

\+-------+-------------+------+-----+---------+-------+

\+-------+-------------+------+-----+---------+-------+

|id|int(11)|NO|PRI|NULL||

|name|varchar(20)|NO||NULL||

\+-------+-------------+------+-----+---------+-------+

2rowsinset(0.001sec)

\#删除主键

MariaDB\[learn\]>alter table sut3 drop primary key;

QueryOK,0rows affected(0.021sec)

Records:0Duplicates:0Warnings:0

MariaDB\[learn\]>desc sut3;

\+-------+-------------+------+-----+---------+-------+

\+-------+-------------+------+-----+---------+-------+

|id|int(11)|NO||NULL||

|name|varchar(20)|NO||NULL||

\+-------+-------------+------+-----+---------+-------+

2rowsinset(0.001sec)

小结：

1.只要是auto\_increment必须是主键，但是主键不一定auto\_increment

2.主键的特点是不能重复不能为空

3.一个表只能有一个主键，但是一个主键可以有多个字段组成

4.自动增长列通过插入null值让其递增

5.自动增长列的数据被删除，默认不再重复使用。truncate table删除数据就会从1开始。唯一键(unique)键

区别

主键

不能重复，不能为空  
一个表只能有一个主键

唯一键

不能重复，可以为空  
一个表可以有多个唯一键

\--创建唯一键

MariaDB\[learn\]>create table stu3(

\->idintauto\_increment primary key,

\->name varchar(20)unique--唯一键

\->);

QueryOK,0rows affected(0.006sec)

\--方法二

MariaDB\[learn\]>create table stu3(

\->idint,

\->name varchar(20)

\->,unique(name)

\->);

QueryOK,0rows affected(0.007sec)

\--修改表添加唯一键

\--添加多个用，分开

MariaDB\[learn\]>alter table stu3 add unique(id);

QueryOK,0rows affected(0.007sec)

Records:0Duplicates:0Warnings:0

\--查看唯一键的名字

MariaDB\[learn\]>show create table stu3\\G;

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*1.row\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

Table:stu3

CreateTable:CREATE TABLE\`stu3\`(

\`id\`int(11)DEFAULT NULL,

\`name\`varchar(20)DEFAULT NULL,

UNIQUE KEY\`id\`(\`id\`)

)ENGINE=InnoDBDEFAULT CHARSET=utf8mb4

1rowinset(0.000sec)

\--通过唯一键的名字删除唯一键

MariaDB\[learn\]>alter table stu3 drop index name;

QueryOK,0rows affected(0.007sec)

Records:0Duplicates:0Warnings:0

备注(comment)说明性文本

属于SQL代码的一部分

SQL注释MariaDB\[learn\]>create table stu4(

\->idintprimary key,--主键

\->name varchar(20)notnull,\#姓名

\->sex tinyint

\->)

\->/\*

/\*> 这是一个学生表

/\*> \*/

数据完整性实体完整性主键约束

唯一约束

标识符域完整性数据类型约束

非空约束

默认值约束引用完整性主外键约束自定义完整性储存过程

触发器主表和从表主表中没有的记录，从表不允许插入

从表中有的记录，主表不允许删除

外键(foreign key)从表中的公共字段。

alter table从表foreign从表公共字段references主表公共字段

alter table从表drop foreign key外键的名字

小结：

只有innodb才能支持外键

公共字段的名字可以不一样，但数据类型要一样。

外键操作严格限制

置空操作(set null )

foreign从表公共字段references主表公共字段ondeletesetnull

级联操作(cascade)

foreign从表公共字段references主表公共字段on update cascade

小结：

置空、级联操作中外键不能是从表的主键

实体之间的关系

一对多(多对一)主表中的一条记录对应从表中的多条记录

实现一对多的关系：主键和非主键建立关系

例子：

班主任表--学生表

品牌表--商品表

![7ababdcdfccaa051f3fa4e61307c243e.png][]

一对一主表中的一条记录对应从表中的一条记录

实现一对一的关系：主键和主键键了关系

一个表为什么分成两个表：

![b1fd3fb2c432dce3e6c3dc18bd7dd991.png][]

多对多非主键和非主键之间的关系

实现：引入第三方关系表

![93ffcecffe4e4dded8a7f8488717f5df.png][]

数据库设计

数据库设计的步骤收集信息：充分理解数据库需要完成的任务

标识对象：标识数据库要管理的关键对象或实体

标识每个实体的属性

标识对象之间的关系

将模型转换成数据库

规范化

绘制E-R实体关系图

![1c237ffe14c5d1c973e40676f157262c.png][]

![image-20200603154854318.png][]

将E-R实体关系表转换为表实体对应表

属性对应字段

如果没有合适的主键，可以添加一个自增长键。

数据规范化

第一范式确保每列的原子性，一个字段表示一个信息

第二范式非主键要依赖于主键

第三范式非主键之间不能有效应关系

反3NF性能比规范化更重要

查询语句语法：select\[选项\]列名\[from表名\]\[where条件\]\[groupby分组\]\[orderby排序\]\[having条件\]\[limit限制\]

字段表达式--可以直接输出内容

MariaDB\[book\]>select10\*10;

\+-------+

|10\*10|

\+-------+

|100|

\+-------+

1rowinset(0.000sec)

\--可以输出表达式

MariaDB\[book\]>selectch,math,ch+mathfromstu;

\+------+------+---------+

|ch|math|ch+math|

\+------+------+---------+

|80|NULL|NULL|

|77|76|153|

|55|82|137|

|NULL|74|NULL|

|72|56|128|

|86|92|178|

|74|67|141|

|65|67|132|

|88|77|165|

\+------+------+---------+

9rowsinset(0.000sec)

\--表达式部分可以用函数

MariaDB\[book\]>selectrand();

\+--------------------+

|rand()|

\+--------------------+

|0.3300995624904517|

\+--------------------+

1rowinset(0.000sec)

通过as区别名MariaDB\[book\]>selectch,math,ch+mathas'总分'fromstu;

\+------+------+--------+

|ch|math|总分|

\+------+------+--------+

|80|NULL|NULL|

|77|76|153|

|55|82|137|

|NULL|74|NULL|

|72|56|128|

|86|92|178|

|74|67|141|

|65|67|132|

|88|77|165|

\+------+------+--------+

9rowsinset(0.000sec)

\--as可以省略的

MariaDB\[book\]>selectch,math,ch+math'总分'fromstu;

\+------+------+--------+

|ch|math|总分|

\+------+------+--------+

|80|NULL|NULL|

|77|76|153|

|55|82|137|

|NULL|74|NULL|

|72|56|128|

|86|92|178|

|74|67|141|

|65|67|132|

|88|77|165|

\+------+------+--------+

9rowsinset(0.000sec)

from子句

from：来自，from后面跟的原可以有多个源，返回笛卡尔积。

dual伪表

dual伪表是为乐保持语句完整性

where子句

跟条件，在数据源中进行筛选

\--查询大于等于

MariaDB\[book\]>select\*fromstuwherech>=60;

\+--------+--------------+--------+--------+---------+------------+------+------+

\+--------+--------------+--------+--------+---------+------------+------+------+

|s25301|张秋丽|男|18|1|北京|80|NULL|

|s25302|李文才|男|31|3|上海|77|76|

|s25305|诸葛丽丽|女|23|7|河南|72|56|

|s25318|争青小子|男|26|6|天津|86|92|

|s25319|梅超风|女|23|5|河北|74|67|

|s25320|Tom|男|24|8|北京|65|67|

|s25321|Tabm|女|23|9|河北|88|77|

\+--------+--------------+--------+--------+---------+------------+------+------+

7rowsinset(0.001sec)

\--查询大于等于

MariaDB\[book\]>select\*fromstuwherech>=60andmath>=60;

\+--------+--------------+--------+--------+---------+------------+------+------+

\+--------+--------------+--------+--------+---------+------------+------+------+

|s25302|李文才|男|31|3|上海|77|76|

|s25318|争青小子|男|26|6|天津|86|92|

|s25319|梅超风|女|23|5|河北|74|67|

|s25320|Tom|男|24|8|北京|65|67|

|s25321|Tabm|女|23|9|河北|88|77|

\+--------+--------------+--------+--------+---------+------------+------+------+

5rowsinset(0.000sec)

\--查询需要或语句

MariaDB\[book\]>select\*fromstuwherech<60ormath<60;

\+--------+--------------+--------+--------+---------+------------+------+------+

\+--------+--------------+--------+--------+---------+------------+------+------+

|s25303|李斯文|女|22|2|北京|55|82|

|s25305|诸葛丽丽|女|23|7|河南|72|56|

\+--------+--------------+--------+--------+---------+------------+------+------+

2rowsinset(0.000sec)

group by「分组查询」

将查询的结果分组，分组查询目的在于统计数据

MariaDB\[book\]>selectstusex,avg(ch)'平均分'fromstugroupbystusex;

\+--------+-----------+

|stusex|平均分|

\+--------+-----------+

|女|72.2500|

|男|77.0000|

\+--------+-----------+

2rowsinset(0.001sec)

\----------

MariaDB\[book\]>selectstuaddress,count(\*)fromstugroupbystuaddress;

\+------------+----------+

|stuaddress|count(\*)|

\+------------+----------+

|上海|1|

|北京|3|

|天津|2|

|河北|2|

|河南|1|

\+------------+----------+

5rowsinset(0.000sec)

通过group\_concat()函数将同一组的值连接起来

MariaDB\[book\]>selectgroup\_concat(stuname),stusex,avg(math)fromstugroupbystusex;

\+---------------------------------------------------+--------+-----------+

|group\_concat(stuname)|stusex|avg(math)|

\+---------------------------------------------------+--------+-----------+

|李斯文,诸葛丽丽,梅超风,Tabm|女|70.5000|

|张秋丽,李文才,欧阳俊雄,争青小子,Tom|男|77.2500|

\+---------------------------------------------------+--------+-----------+

2rowsinset(0.000sec)

如果是分组字段，查询字段必须是分组字段和聚合函数

查询字段是普通字段，只取第一个值

多列分组

MariaDB\[book\]>selectstuaddress,stusex,avg(math)fromstugroupbystusex,stuaddress;

\+------------+--------+-----------+

|stuaddress|stusex|avg(math)|

\+------------+--------+-----------+

|北京|女|82.0000|

|河北|女|72.0000|

|河南|女|56.0000|

|上海|男|76.0000|

|北京|男|67.0000|

|天津|男|83.0000|

\+------------+--------+-----------+

6rowsinset(0.000sec)

order by排序

asc：升序

desc：降序

默认为升序--拿年龄的升序排序

MariaDB\[book\]>select\*fromstu orderbystuage asc;

\+--------+--------------+--------+--------+---------+------------+------+------+

\+--------+--------------+--------+--------+---------+------------+------+------+

|s25301|张秋丽|男|18|1|北京|80|NULL|

|s25303|李斯文|女|22|2|北京|55|82|

|s25305|诸葛丽丽|女|23|7|河南|72|56|

|s25319|梅超风|女|23|5|河北|74|67|

|s25321|Tabm|女|23|9|河北|88|77|

|s25320|Tom|男|24|8|北京|65|67|

|s25318|争青小子|男|26|6|天津|86|92|

|s25304|欧阳俊雄|男|28|4|天津|NULL|74|

|s25302|李文才|男|31|3|上海|77|76|

\+--------+--------------+--------+--------+---------+------------+------+------+

9rowsinset(0.000sec)

\--按总分排序

MariaDB\[book\]>select\*,ch+math'总分'fromstu orderbych+math desc;

\+--------+--------------+--------+--------+---------+------------+------+------+--------+

\+--------+--------------+--------+--------+---------+------------+------+------+--------+

|s25318|争青小子|男|26|6|天津|86|92|178|

|s25321|Tabm|女|23|9|河北|88|77|165|

|s25302|李文才|男|31|3|上海|77|76|153|

|s25319|梅超风|女|23|5|河北|74|67|141|

|s25303|李斯文|女|22|2|北京|55|82|137|

|s25320|Tom|男|24|8|北京|65|67|132|

|s25305|诸葛丽丽|女|23|7|河南|72|56|128|

|s25301|张秋丽|男|18|1|北京|80|NULL|NULL|

|s25304|欧阳俊雄|男|28|4|天津|NULL|74|NULL|

\+--------+--------------+--------+--------+---------+------------+------+------+--------+

9rowsinset(0.000sec)

\--多列

MariaDB\[book\]>select\*fromstu orderbystuage asc,ch desc;

\+--------+--------------+--------+--------+---------+------------+------+------+

\+--------+--------------+--------+--------+---------+------------+------+------+

|s25301|张秋丽|男|18|1|北京|80|NULL|

|s25303|李斯文|女|22|2|北京|55|82|

|s25321|Tabm|女|23|9|河北|88|77|

|s25319|梅超风|女|23|5|河北|74|67|

|s25305|诸葛丽丽|女|23|7|河南|72|56|

|s25320|Tom|男|24|8|北京|65|67|

|s25318|争青小子|男|26|6|天津|86|92|

|s25304|欧阳俊雄|男|28|4|天津|NULL|74|

|s25302|李文才|男|31|3|上海|77|76|

\+--------+--------------+--------+--------+---------+------------+------+------+

having条件结果集中进行条件筛选MariaDB\[book\]>select\*fromstuwherestusex='女';

\+--------+--------------+--------+--------+---------+------------+------+------+

\+--------+--------------+--------+--------+---------+------------+------+------+

|s25303|李斯文|女|22|2|北京|55|82|

|s25305|诸葛丽丽|女|23|7|河南|72|56|

|s25319|梅超风|女|23|5|河北|74|67|

|s25321|Tabm|女|23|9|河北|88|77|

\+--------+--------------+--------+--------+---------+------------+------+------+

4rowsinset(0.001sec)

\--------

MariaDB\[book\]>select\*fromstu having stusex='女';

\+--------+--------------+--------+--------+---------+------------+------+------+

\+--------+--------------+--------+--------+---------+------------+------+------+

|s25303|李斯文|女|22|2|北京|55|82|

|s25305|诸葛丽丽|女|23|7|河南|72|56|

|s25319|梅超风|女|23|5|河北|74|67|

|s25321|Tabm|女|23|9|河北|88|77|

\+--------+--------------+--------+--------+---------+------------+------+------+

4rowsinset(0.000sec)

limit语法：limit起始位置，显示长度MariaDB\[book\]>select\*fromstu limit0,3;

\+--------+-----------+--------+--------+---------+------------+------+------+

\+--------+-----------+--------+--------+---------+------------+------+------+

|s25301|张秋丽|男|18|1|北京|80|NULL|

|s25302|李文才|男|31|3|上海|77|76|

|s25303|李斯文|女|22|2|北京|55|82|

\+--------+-----------+--------+--------+---------+------------+------+------+

3rowsinset(0.001sec)

limit在update和delete语句中使用。MariaDB\[book\]>update stusetch=ch+1orderbych+math desc limit3;

QueryOK,3rows affected(0.000sec)

Rowsmatched:3Changed:3Warnings:0

查询语句的选项all：显示所有数据

distinct：去除重复数据

聚合函数sum()求和

avg()求平均值

max()求最大值

min()求最小值

count()求记录数

模糊查询

通配符\_代表任意一个字符

%代表任意字符

模糊查询(like)MariaDB\[book\]>select\*fromstuwherestuname like'T\_m';

\+--------+---------+--------+--------+---------+------------+------+------+

\+--------+---------+--------+--------+---------+------------+------+------+

|s25320|Tom|男|24|8|北京|65|67|

\+--------+---------+--------+--------+---------+------------+------+------+

1rowinset(0.000sec)

MariaDB\[book\]>select\*fromstuwherestuname like'张%';

\+--------+-----------+--------+--------+---------+------------+------+------+

\+--------+-----------+--------+--------+---------+------------+------+------+

|s25301|张秋丽|男|18|1|北京|80|NULL|

\+--------+-----------+--------+--------+---------+------------+------+------+

1rowinset(0.000sec)

union(联合)语法：select语句union\[选项\]select语句union\[选项\]select语句

将多个select的语句的结果集纵向连接起来

all：显示所有数据

discinct：去除重复的数据(默认)

结果默认去除重复union两边的select语句的字段个数必须一致

union两边的select语句的字段名可以不一样，按第一个select语句的字段名

union两边的select语句中的数据类型可以不一致

多表查询

内连接规则：返回两个表的公共记录--语法一

select\*from表1inner join表2on表1.公共字段=表2.公共字段

\--语法二

select\*from表1，表2where表1.公共字段=表2.公共字段

\--inner join

MariaDB\[book\]>select\*fromstuinfo inner join stumarks on stuinfo.stuno=stumarks.stuno;

\+--------+--------------+--------+--------+---------+------------+---------+--------+-------------+---------+

\+--------+--------------+--------+--------+---------+------------+---------+--------+-------------+---------+

|s25303|李斯文|女|22|2|北京|s271811|s25303|80|58|

|s25302|李文才|男|31|3|上海|s271813|s25302|50|90|

|s25304|欧阳俊雄|男|28|4|天津|s271815|s25304|65|50|

|s25301|张秋丽|男|18|1|北京|s271816|s25301|77|82|

|s25318|争青小子|男|26|6|天津|s271819|s25318|56|48|

\+--------+--------------+--------+--------+---------+------------+---------+--------+-------------+---------+

5rowsinset(0.002sec)

\--where联合查询

MariaDB\[book\]>select\*fromstuinfo,stumarkswherestuinfo.stuno=stumarks.stuno;

\+--------+--------------+--------+--------+---------+------------+---------+--------+-------------+---------+

\+--------+--------------+--------+--------+---------+------------+---------+--------+-------------+---------+

|s25303|李斯文|女|22|2|北京|s271811|s25303|80|58|

|s25302|李文才|男|31|3|上海|s271813|s25302|50|90|

|s25304|欧阳俊雄|男|28|4|天津|s271815|s25304|65|50|

|s25301|张秋丽|男|18|1|北京|s271816|s25301|77|82|

|s25318|争青小子|男|26|6|天津|s271819|s25318|56|48|

\+--------+--------------+--------+--------+---------+------------+---------+--------+-------------+---------+

5rowsinset(0.000sec)

\--取消重复显示方法

MariaDB\[book\]>selectstuinfo.stuno,stuname,stusex,stuage,stuseat,writtenexam,labexamfromstuinfo,stumarkswherestuinfo.stuno=stumarks.stuno;

\+--------+--------------+--------+--------+---------+-------------+---------+

\+--------+--------------+--------+--------+---------+-------------+---------+

|s25303|李斯文|女|22|2|80|58|

|s25302|李文才|男|31|3|50|90|

|s25304|欧阳俊雄|男|28|4|65|50|

|s25301|张秋丽|男|18|1|77|82|

|s25318|争青小子|男|26|6|56|48|

\+--------+--------------+--------+--------+---------+-------------+---------+

5rowsinset(0.001sec)

内联之中inner可以省略

select\*from表1inner join表2on表1.公共字段=表2.公共字段

MariaDB\[book\]>select\*fromstuinfo join stumarks on stuinfo.stuno=stumarks.stuno;

\+--------+--------------+--------+--------+---------+------------+---------+--------+-------------+---------+

\+--------+--------------+--------+--------+---------+------------+---------+--------+-------------+---------+

|s25303|李斯文|女|22|2|北京|s271811|s25303|80|58|

|s25302|李文才|男|31|3|上海|s271813|s25302|50|90|

|s25304|欧阳俊雄|男|28|4|天津|s271815|s25304|65|50|

|s25301|张秋丽|男|18|1|北京|s271816|s25301|77|82|

|s25318|争青小子|男|26|6|天津|s271819|s25318|56|48|

\+--------+--------------+--------+--------+---------+------------+---------+--------+-------------+---------+

5rowsinset(0.000sec)

\--表连接越多，效率越低

\--如何实现三表查询

select\*from表1inner join表2on表1.公共字段=表2.公共字段inner join表3on表2.公共字段=表3.公共字段

左外连接

规则：以左边的表为准，右边如果没有对应的记录用null显示

语法：select\*from表1left join表2on表1.公共字段=表2.公共字段MariaDB\[book\]>selectstuname,writtenexam,labexamfromstuinfo left join stumarks on stuinfo.stuno=stumarks.stuno;

\+--------------+-------------+---------+

|stuname|writtenexam|labexam|

\+--------------+-------------+---------+

|李斯文|80|58|

|李文才|50|90|

|欧阳俊雄|65|50|

|张秋丽|77|82|

|争青小子|56|48|

|诸葛丽丽|NULL|NULL|

|梅超风|NULL|NULL|

\+--------------+-------------+---------+

7rowsinset(0.005sec)

select\*from表1left join表2on表1.公共字段=表2.公共字段

和

select\*from表2left join表1on表1.公共字段=表2.公共字段一样吗？

答：不一样，第一个SQL以表1为准，第二个SQL以表2为准。

右外连接

规则：以右边的表为准，左边如果没有对应的记录用null显示

语法：select\*from表1right join表2on表1.公共字段=表2.公共字段MariaDB\[book\]>selectstuinfo.stuname,writtenexam,labexamfromstuinfo right join stumarks on stuinfo.stuno=stumarks.stuno;

\+--------------+-------------+---------+

|stuname|writtenexam|labexam|

\+--------------+-------------+---------+

|李斯文|80|58|

|李文才|50|90|

|欧阳俊雄|65|50|

|张秋丽|77|82|

|争青小子|56|48|

|NULL|66|77|

\+--------------+-------------+---------+

6rowsinset(0.000sec)

交叉连接

语法，返回笛卡尔积

语法：select\*from表1cross join表2--交叉连接

mysql>select\*fromstuinfo cross join stumarks;

\--交叉连接有连接表达式与内连接是一样的

mysql>select\*fromstuinfo cross join stumarks on stuinfo.stuno=stumarks.stuno;

\+--------+----------+--------+--------+---------+------------+---------+--------+-------------+---------+

\+--------+----------+--------+--------+---------+------------+---------+--------+-------------+---------+

|s25303|李斯文|女|22|2|北京|s271811|s25303|80|58|

|s25302|李文才|男|31|3|上海|s271813|s25302|50|90|

|s25304|欧阳俊雄|男|28|4|天津|s271815|s25304|65|50|

|s25301|张秋丽|男|18|1|北京|s271816|s25301|77|82|

|s25318|争青小子|男|26|6|天津|s271819|s25318|56|48|

\+--------+----------+--------+--------+---------+------------+---------+--------+-------------+---------+

5rowsinset(0.00sec)小结

1、交叉连接如果没有连接条件返回笛卡尔积

2、如果有连接条件和内连接是一样的。

自然连接自动判断条件连接，判断的条件是依据同名字段

如果没同名字段返回笛卡尔积

同名字段只显示一个，并自动放在最前面自然内连接natural joinMariaDB\[book\]>select\*fromstuinfo natural join stumarks;

\+--------+--------------+--------+--------+---------+------------+---------+-------------+---------+

\+--------+--------------+--------+--------+---------+------------+---------+-------------+---------+

|s25303|李斯文|女|22|2|北京|s271811|80|58|

|s25302|李文才|男|31|3|上海|s271813|50|90|

|s25304|欧阳俊雄|男|28|4|天津|s271815|65|50|

|s25301|张秋丽|男|18|1|北京|s271816|77|82|

|s25318|争青小子|男|26|6|天津|s271819|56|48|

\+--------+--------------+--------+--------+---------+------------+---------+-------------+---------+

5rowsinset(0.000sec)

自然左外连接natural left joinMariaDB\[book\]>select\*fromstuinfo natural left join stumarks;

\+--------+--------------+--------+--------+---------+------------+---------+-------------+---------+

\+--------+--------------+--------+--------+---------+------------+---------+-------------+---------+

|s25303|李斯文|女|22|2|北京|s271811|80|58|

|s25302|李文才|男|31|3|上海|s271813|50|90|

|s25304|欧阳俊雄|男|28|4|天津|s271815|65|50|

|s25301|张秋丽|男|18|1|北京|s271816|77|82|

|s25318|争青小子|男|26|6|天津|s271819|56|48|

|s25305|诸葛丽丽|女|23|7|河南|NULL|NULL|NULL|

|s25319|梅超风|女|23|5|河北|NULL|NULL|NULL|

\+--------+--------------+--------+--------+---------+------------+---------+-------------+---------+

7rowsinset(0.000sec)

自然右外连接natural right joinMariaDB\[book\]>select\*fromstuinfo natural right join stumarks;

\+--------+---------+-------------+---------+--------------+--------+--------+---------+------------+

\+--------+---------+-------------+---------+--------------+--------+--------+---------+------------+

|s25303|s271811|80|58|李斯文|女|22|2|北京|

|s25302|s271813|50|90|李文才|男|31|3|上海|

|s25304|s271815|65|50|欧阳俊雄|男|28|4|天津|

|s25301|s271816|77|82|张秋丽|男|18|1|北京|

|s25318|s271819|56|48|争青小子|男|26|6|天津|

|s25320|s271820|66|77|NULL|NULL|NULL|NULL|NULL|

\+--------+---------+-------------+---------+--------------+--------+--------+---------+------------+

6rowsinset(0.000sec)

using

用来指定连接字段

MariaDB\[book\]>select\*fromstuinfo inner join stumarksusing(stuno);

\+--------+--------------+--------+--------+---------+------------+---------+-------------+---------+

\+--------+--------------+--------+--------+---------+------------+---------+-------------+---------+

|s25303|李斯文|女|22|2|北京|s271811|80|58|

|s25302|李文才|男|31|3|上海|s271813|50|90|

|s25304|欧阳俊雄|男|28|4|天津|s271815|65|50|

|s25301|张秋丽|男|18|1|北京|s271816|77|82|

|s25318|争青小子|男|26|6|天津|s271819|56|48|

\+--------+--------------+--------+--------+---------+------------+---------+-------------+---------+

5rowsinset(0.000sec)

using的结果也会对公共字段进行优化，优化的规则和自然连接一样

子查询语法：select\*from表1where(子查询)

外面的查询称为父查询

子查询为父查询提供查询条件标量子查询MariaDB\[book\]>select\*fromstuinfowherestuno=(selectstunofromstumarkswherewrittenexam=80);

\+--------+-----------+--------+--------+---------+------------+

\+--------+-----------+--------+--------+---------+------------+

|s25303|李斯文|女|22|2|北京|

\+--------+-----------+--------+--------+---------+------------+

1rowinset(0.002sec)

MariaDB\[book\]>select\*fromstuinfowherestuno=(selectstunofromstumarkswherewrittenexam=(selectmax(writtenexam)fromstumarks));

\+--------+-----------+--------+--------+---------+------------+

\+--------+-----------+--------+--------+---------+------------+

|s25303|李斯文|女|22|2|北京|

\+--------+-----------+--------+--------+---------+------------+

1rowinset(0.001sec)

列子查询特点：返回值的是一列

如果子查询的结果为多条记录，用in或notin行子查询特点：返回值为一行表子查询特点：将子查询作为表名

如果将子查询当成一个表来看，必须给结果集取别名exists子查询作用：提高查询效率

视图

概述视图是一张虚拟表，它表示一张表的部分数据或多张表的综合数据，其结构和数据是建立在对表的查询基础上

视图中并不存放数据，而是存放在视图所引用的原始表(基表)中

同一张原始表，根据不同用户的不同需求，可以创建不同的视图

作用筛选表中的行

防止未经许可的用户访问敏感数据

隐藏数据表的结构

降低数据表的复杂程度

创建视图语法：

\--创建

create view试图名

select语句；

\--查询视图

select列名from视图

修改视图--语法

alter view视图名

select语句；

删除视图语法

drop view\[ifexists\]视图1，视图2

查看视图信息--查看所有的表和视图

show tables；

\--查看视图

selecttable\_namefrominformation\_schema.views\#查看表和视图的详细状态信息

show table statuswherecomment='view'\\G;\#只查看视图信息

\--查看视图的结构信息

desc view1；

\--查看视图的创建语句

show create view view1;

视图算法视图的算法和表的算法不一样

merge：合并算法

temptable：临时表算法

undefined：未定义算法(用哪种算法由mysql决定，这是默认算法，视图一般会选merge算法)

重新通过视图

createorreplace algorithm=tmptable view view3

select\*fromstu orderbystusex;

事务

概述事务(TRANSACTION)是一个整体，要么一起执行，要么一起不执行

事务特性原子性：事务是一个完整的操作。事务的各步操作是不可分的(原子的)；要么都执行，要么都不执行

一致性：当事务完成时，数据必须处于一致状态

隔离性：对数据进行修改的所有并发事务是彼此隔离的。

永久性：事务完成后，它对数据库的修改被永久保持。

事务处理--开启事务

start transaction或begin\[work\]

\--回滚事务

rollback

\--提交事务

commit

\--设置事务的回滚点

savepoint

\--自动提交事务

事务是事务开始的时候开始

提交事务、回滚事务后事务结束

只有innodb支持事务

每个SQL语句都是一个事务

索引

概述优点：

加快查询速度

缺点：

带索引的表在数据库中需要更多的存储空间

增、删、改命令需要更长的处理时间，因为他们需要对索引进行更新

创建索引适合创建索引的列：

该列用于频繁搜索

该列用于对数据进行排序

在where\\join出现的列

不适合创建索引的列：

列中仅包含几个值

表中仅包含几行，数据量很少主键索引创建了主键就会自动的创建主键索引

唯一索引

创建唯一键就创建了唯一索引

create unique index ix\_name on t5(name)普通索引--创建表的时候添加普通索引

删除索引

drop index索引名on表名

函数

数字类

\--rand()获取随机数

MariaDB\[book\]>select\*fromstuinfo orderbyrand()limit1;

\+--------+-----------+--------+--------+---------+------------+

\+--------+-----------+--------+--------+---------+------------+

|s25301|张秋丽|男|18|1|北京|

\+--------+-----------+--------+--------+---------+------------+

1rowinset(0.000sec)

\--round()四舍五入

MariaDB\[book\]>selectround(3.1415926,3);

\+--------------------+

|round(3.1415926,3)|

\+--------------------+

|3.142|

\+--------------------+

1rowinset(0.000sec)

\--truncate()截取数据

MariaDB\[book\]>selecttruncate(3.1415926,3)

\->;

\+-----------------------+

|truncate(3.1415926,3)|

\+-----------------------+

|3.141|

\+-----------------------+

1rowinset(0.000sec)

\--ceil()向上取整

MariaDB\[book\]>selectceil(3.1415926)

\->;

\+-----------------+

|ceil(3.1415926)|

\+-----------------+

|4|

\+-----------------+

1rowinset(0.000sec)

\--floor向下取整

MariaDB\[book\]>selectfloor(3.9)

\->;

\+------------+

|floor(3.9)|

\+------------+

|3|

\+------------+

1rowinset(0.000sec)

字符类--大小写转换

mysql>selectucase('i name is tom')'转成大写',lcase('My Name IS TOM')'转成小写';

\+---------------+----------------+

|转成大写|转成小写|

\+---------------+----------------+

|I NAME IS TOM|mynameistom|

\+---------------+----------------+

1rowinset(0.00sec)

\--截取字符串

mysql>selectleft('abcdef',3)'从左边截取',right('abcdef',3)'从右边截取',substring('abcdef',2,3)'字符串';

\+------------+------------+--------+

|从左边截取|从右边截取|字符串|

\+------------+------------+--------+

|abc|def|bcd|

\+------------+------------+--------+

1rowinset(0.00sec)

\--字符串相连

mysql>selectconcat('中国','北京','顺义')'地址';

\+--------------+

|地址|

\+--------------+

|中国北京顺义|

\+--------------+

1rowinset(0.00sec)

mysql>selectconcat(stuname,'-',stusex)信息fromstuinfo;

\+-------------+

|信息|

\+-------------+

|张秋丽-男|

|李文才-男|

|李斯文-女|

|欧阳俊雄-男|

|诸葛丽丽-女|

|争青小子-男|

|梅超风-女|

\+-------------+

7rowsinset(0.00sec)

\--coalesce(str1,str2):str1有值显示str1,如果str1为空就显示str2

\--将成绩为空的显示为缺考

mysql>selectstuname,coalesce(writtenexam,'缺考'),coalesce(labexam,'缺考')fromstuinfo natural left join stumarks;

\+----------+------------------------------+--------------------------+

|stuname|coalesce(writtenexam,'缺考')|coalesce(labexam,'缺考')|

\+----------+------------------------------+--------------------------+

|张秋丽|77|82|

|李文才|50|90|

|李斯文|80|58|

|欧阳俊雄|65|50|

|诸葛丽丽|缺考|缺考|

|争青小子|56|48|

|梅超风|缺考|缺考|

\+----------+------------------------------+--------------------------+

7rowsinset(0.02sec)

\--length():字节长度，char\_length()：字符长度

mysql>selectlength('锄禾日当午')字节,char\_length('锄禾日当午')字符;

\+------+------+

|字节|字符|

\+------+------+

|10|5|

\+------+------+

1rowinset(0.00sec)

时间类--时间戳

MariaDB\[book\]>selectunix\_timestamp();

\+------------------+

|unix\_timestamp()|

\+------------------+

|1591236240|

\+------------------+

1rowinset(0.000sec)

\--格式化时间戳

MariaDB\[book\]>selectfrom\_unixtime(unix\_timestamp());

\+---------------------------------+

|from\_unixtime(unix\_timestamp())|

\+---------------------------------+

|2020-06-0410:05:22|

\+---------------------------------+

1rowinset(0.000sec)

\--获取当前格式化时间

MariaDB\[book\]>selectnow();

\+---------------------+

|now()|

\+---------------------+

|2020-06-0410:05:47|

\+---------------------+

1rowinset(0.000sec)

\--获取年，月，日，小时，分钟，秒

mysql>selectyear(now())年,month(now())月,day(now())日,hour(now())小时,minute(now())分钟,second(now())秒;

\+------+------+------+------+------+------+

|年|月|日|小时|分钟|秒|

\+------+------+------+------+------+------+

|2019|6|12|17|10|48|

\+------+------+------+------+------+------+

1rowinset(0.00sec)

\--星期，本年第几天;

mysql>selectdayname(now())星期,dayofyear(now())本年第几天;

\+-----------+------------+

|星期|本年第几天|

\+-----------+------------+

|Wednesday|163|

\+-----------+------------+

1rowinset(0.00sec)

MariaDB\[book\]>selectmonthname(now())

\->;

\+------------------+

|monthname(now())|

\+------------------+

|June|

\+------------------+

1rowinset(0.000sec)

\--日期相减

mysql>selectdatediff(now(),'2010-08-08')相距天数;

\+----------+

|相距天数|

\+----------+

|3230|

\+----------+

1rowinset(0.00sec)

加密函数--md5加密

mysql>selectmd5('aa');

\+----------------------------------+

|md5('aa')|

\+----------------------------------+

|4124bc0a9335c27f086f24ba207a4912|

\+----------------------------------+

1rowinset(0.00sec)

\--sha加密

mysql>selectsha('aa');

\+------------------------------------------+

|sha('aa')|

\+------------------------------------------+

|e0c9035898dd52fc65c41454cec9c4d2611bfb37|

\+------------------------------------------+

1rowinset(0.00sec)

预处理

每个代码的段的执行都要经历：词法分析——语法分析——编译——执行

预编译一次，可以多次执行。用来解决一条SQL语句频繁执行的问题。

预处理语句：prepare预处理名字from‘sql语句’

执行预处理：execute预处理名字\[using变量\]不带参数的预处理--创建预处理

mysql>prepare stmtfrom'select \* from stuinfo';

QueryOK,0rows affected(0.06sec)

Statementprepared

\--执行预处理

mysql>execute stmt;

\+--------+----------+--------+--------+---------+------------+

\+--------+----------+--------+--------+---------+------------+

|s25301|张秋丽|男|18|1|北京|

|s25302|李文才|男|31|3|上海|

|s25303|李斯文|女|22|2|北京|

|s25304|欧阳俊雄|男|28|4|天津|

|s25305|诸葛丽丽|女|23|7|河南|

|s25318|争青小子|男|26|6|天津|

|s25319|梅超风|女|23|5|河北|

\+--------+----------+--------+--------+---------+------------+

7rowsinset(0.00sec)带参数的预处理--创建带有位置占位符的预处理语句

mysql>prepare stmtfrom'select \* from stuinfo where stuno=?';

QueryOK,0rows affected(0.00sec)

Statementprepared

\--调用预处理，并传参数

mysql>delimiter//

mysql>set@id='s25301';

\->execute stmtusing@id//

QueryOK,0rows affected(0.00sec)

\+--------+---------+--------+--------+---------+------------+

\+--------+---------+--------+--------+---------+------------+

|s25301|张秋丽|男|18|1|北京|

\+--------+---------+--------+--------+---------+------------+

1rowinset(0.00sec)传递多个参数mysql>prepare stmtfrom'select \* from stuinfo where stuage>? and stusex=?'//

QueryOK,0rows affected(0.00sec)

Statementprepared

mysql>set@age=20;

\->set@sex='男';

\->execute stmtusing@age,@sex//

QueryOK,0rows affected(0.00sec)

\+--------+----------+--------+--------+---------+------------+

\+--------+----------+--------+--------+---------+------------+

|s25302|李文才|男|31|3|上海|

|s25304|欧阳俊雄|男|28|4|天津|

|s25318|争青小子|男|26|6|天津|

\+--------+----------+--------+--------+---------+------------+

3rowsinset(0.00sec)小结：

1、MySQL中变量以@开头

2、通过set给变量赋值

3、？是位置占位符

数据库备份与还原数据库中的数据需要定期备份

数据备份mysqldump数据库连接数据库>SQL文件备份地址--将data数据库中所有的表导出到data.sql中

F:\\wamp\\PHPTutorial\\MySQL\\bin>mysqldump-uroot-proot data>c:\\data.sql

\--将data数据库中的stuinfo、stumarks表

F:\\wamp\\PHPTutorial\\MySQL\\bin>mysqldump-uroot-proot data stuinfo stumarks>c:\\data.sql

\--导出data数据库，导出的语句中带有创建数据库的语法

F:\\wamp\\PHPTutorial\\MySQL\\bin>mysqldump-uroot-proot-B data>c:\\data1.sql

数据还原mysql>source c:/data.sql;

注意：地址分隔符用斜线，不能用反斜线

mysql连接数据库导入的数据库名

mysql-uroot-proot data1

[1c21bb426c38eca70d06298836b14513.png]: /images/20221024/1b5062458abc47c790bb906eee1a369b.png
[b8f5159b220329c5266082dae6ac3570.png]: /images/20221024/12a7be3150634f34b83f25d4973d005a.png
[5ef6c67a8006707ac74f0ad8fe8adb79.png]: /images/20221024/347bec46f41740a9bf54d3a26c7df737.png
[e7538931a7135eb482a153bd3197b33c.png]: /images/20221024/f37f1954312942678e766131031a82ea.png
[7ababdcdfccaa051f3fa4e61307c243e.png]: /images/20221024/476bfb31cb58434f841aca272d1862f5.png
[b1fd3fb2c432dce3e6c3dc18bd7dd991.png]: /images/20221024/d44e8c1bd0d8486c9f89af4ae9986858.png
[93ffcecffe4e4dded8a7f8488717f5df.png]: /images/20221024/2e603bc065ba42cbb1a6bb24291f3d8a.png
[1c237ffe14c5d1c973e40676f157262c.png]: /images/20221024/4a7c65a97e4b4a3f9a71c0592fc35b16.png
[image-20200603154854318.png]: https://www.php.cn/blog/detail/mysql.assets/image-20200603154854318.png