MySQL笔记..

03_MySQL

Day01

一、数据库(DataBase DB)

1、数据库：存储数据的仓库

2、数据库分类:

(1)关系型数据库:二维表存储数据(Mysql,SQLServer,Orcale,DB2等)

(2)非关系型数据库（Nosql）:MongDB等

3、Mysql安装

(1)window:配置文件my.ini

进入命令行开始--运行--cmd

(2)Linux：配置文件:/etc/https://www.360docs.net/doc/1a9518952.html,f

终端

mysql -uroot -p

说明: Mysql 数据库的存储位置

/var/lib/mysql

4、Mysql登录

mysql -u用户名-p密码[-P3306]

[-D数据库名称]

5、Mysql退出

quit

exit

\q

6、Mysql注释

-- 注释

# 注释

7、修改提示符(Prompt)

（1）进入MySQL的命令

prompt 提示符名称

（2）登录MySQL写的命令

mysql -uroot -p --prompt=提示符名称

说明:

a.恢复提示符prompt mysql>

b.特殊符号

\h 主机名称

\D 系统时间日期

\d 选择数据库名称

8、SQL(Structured Query Language)结构化查询语句

（1）DDL(数据创建语言):创建数据库，创建表、创建视图等

CREATE(创建)，DROP(删除)，ALTER(修改)

（2）DML(数据操作语言):对数据库的数据的增，删，改

INSERT（添加）,DELETE(删除)，UPDATE（改）

（3）DQL(数据查询语言):对数据库的数据查询

SELECT（查询）

（4）DCL(数据库控制语言):分配(GRANT)用户权限

及权限(REVOKE)撤销

9、SQL命令行规范

a.系统命令大写，名称小写

b.SQL命令行以分号结束或\g

说明:结束分隔符可以用delimiter 符号名称

c.SQL命令行支持折行，但不能在函数,名称，引号中不能折行

d.SQL命令中的名称不能用关键字和保留字，如果使用必须用反引号``括起来，例如`user`

10、创建数据库(DDL)

（1）查看数据库

SHOW DATABASES;

（2）创建数据库

CREATE DATABASE [IF NOT EXISTS]

数据库名称[[DEFAULT] CHARACTER

SET [=] 编码];

（3）查看创建数据库命令

SHOW CREATE DATABASE 数据库名称;

（4）删除数据库

DROP DATABASE [IF EXISTS] 数据库名称;

（5）修改数据库的编码

ALTER DATABASE 数据库名称

[DEFAULT] CHARACTER SET [=]编码;

（6）选择数据库

USE 数据库名称;

（7）查看当前选择的数据库

SELECT DATABASE();

11、创建数据库中的表

（1）查看表

SHOW TABLES;

（2）创建表结构

CREATE TABLE [IF NOT EXISTS] 表名(

字段(Field)名称|列名称数据类型[字段属性|约束条件]，

字段(Field)名称|列名称数据类型[字段属性|约束条件]

...

)[ENGINE=InnoDB DEFAULT CHARSET=utf8];

说明:数据库最小单位是表，一定是先有表结构

再添加数据

（3）查看表结构

DESC 表名;

DESCRIBE 表名;

SHOW COLUMNS FROM 表名;

（4）查看创建表结构命令

SHOW CREATE TABLE 表名;

12、数据操作（DML DQL）

(1)插入数据

INSERT 表名(字段1,字段2...)

VALUE[S]

(值,值...),

(值,值...)...;

(2)查询数据：

SELECT * FROM 表名;

SELECT 字段1,字段2,... FROM 表名; 13、MySQL数据类型

（1）整型

TINYINT([M]) [UNSIGNED|ZEROFILL]：

范围:大小1字节(Byte 2^8)

有符号位-128～127

无符号0～255

SMALLINT([M]) [UNSIGNED|ZEROFILL]：范围:大小2字节(Byte 2^16)

有符号位-32768～32767

无符号0～65535

MEDIUMINT([M]) [UNSIGNED|ZEROFILL]：范围:大小3字节(Byte 2^24)

有符号位-8388608～8388607

无符号0～16777215

INT([M]) [UNSIGNED|ZEROFILL]：

范围:大小4字节(Byte 2^32)

有符号位-2147483648～2147483647

无符号0～4294967295

BIGINT([M]) [UNSIGNED|ZEROFILL]：

范围:大小8字节(Byte 2^64)

说明:

字段属性:

UNSIGNED：无符号位(正数) ZEROFILL: 当显示的长度小于指定长度用0填补，会自动添加UNSIGNED （2）浮点

a.单精度浮点

FLOAT([M,D]) [UNSIGNED|ZEROFILL]

范围:

-3.40e38 ~3.40e38

b.双精度浮点

DOUBLE([M,D]) [UNSIGNED|ZEROFILL]

-1.79e308 ~1.79e308

c.定长浮点

DECIMAL([M,D])[UNSIGNED|ZEROFILL]

说明:

当指定D小点位数，FLOAT，DOUBLE超出指定位数会四舍五入存近似值，DECIMAL超出指定位数，报警告错误，DECIMAL存精确值

（3）字符型

a.CHAR([M])：定长字符（0-255字节一个字母占一个字节）

b.VARCHAR([M]):变长字符（0-65535字节）

VARCHAR存储长度受下面情况影响:

(a)一个字段最大65535字节

(b)编码

gbk:至多占2个字节

utf8:至多占3个字节65532/3 =21844

(c)多个字符型字段中总的长度不能超过

65535（21844）

c.TINYTEXT：0-255字节

d.TEXT:0-65535字节

e.MEDIUMTEXT:0-16777215字节

f.LONGTEXT:0-4294967295字节

说明: CHAR效率高但不省空间，VARCHAR效率低但省空间,TEXT效率最低

（4）布尔型

BOOLEAN

TINYINT(1)

（5）枚举类型

ENUM：范围:0-65535字节

例如:

sex ENUM('man','woman','secret');

14、约束条件(1)

NOT NULL：不为空

DEFAULT：默认值

[PRIMARY] KEY：主键，一个表中只能有一个主键，并且是主键的字段一定自动添加NOT NULL 和不重复(UNIQUE)

UNIQUE [KEY]：不重复，一个表中可以有多个不重复(例如:用户名不重复，邮箱不重复)

AUTO_INCREMENT：自动增长，字段如果是编号当添加记录(行)时，编号自增，并且

AUTO_INCREMENT的字段一定是主键。

Day02

一、表结构的修改(DDL)

1、添加表结构的字段

a.ALTER TABLE 表名

ADD 字段名数据类型[字段属性|约束条件]

[FIRST|AFTER 字段名]；

b. 添加多个字段

ALTER TABLE 表名

ADD 字段名数据类型[字段属性|约束条件]

[FIRST|AFTER 字段名]，

ADD 字段名数据类型[字段属性|约束条件]

[FIRST|AFTER 字段名]...；

2、删除字段

ALTER TABLE 表名

DROP 字段名,

DROP 字段名...;

3、修改字段

ALTER TABLE 表名

MODIFY 字段名数据类型[字段属性|约束条件]

[FIRST|AFTER 字段名]；

4、修改字段名称

ALTER TABLE 表名

CHANGE 旧字段名新字段名数据类型[字段属性|约束条件]

[FIRST|AFTER 字段名]；

5、添加默认值

ALTER TABLE 表名

ALTER 字段名SET DEFAULT 值;

6、删除默认值

ALTER TABLE 表名

ALTER 字段名DROP DEFAULT;

7、添加主键

ALTER TABLE 表名

ADD PRIMARY KEY(字段名称);

8、删除主键

ALTER TABLE KEY|INDE表名

DROP PRIMARY KEY;

说明:如果有AUTO_INCRMENT属性，一定要先删除

AUTO_INCRMENT才能删除主键

9、添加唯一性

a.ALTER TABLE 表名

ADD UNIQUE KEY|INDEX(字段名);

b.a.ALTER TABLE 表名

ADD [CONSTRAINT 约束名称] UNIQUE KEY|INDEX(字段名);

10、删除唯一性

ALTER TABLE 表名

DROP KEY|INDEX 唯一性约束名称;

说明:如果添加唯一性没有设置唯一性约束名称，默认为字段名称

11、修改表名称

a. ALTER TABLE 表名

RENAME [AS|TO] 新表名;

b. RENAME TABLE 旧表名TO 新表名;

12、重置自动增长

ALTER TABLE 表名

AUTO_INCREMENT=1;

二、帮助

? 命令

help 命令

\h 命令

三、表中对数据的操作(DML)

（1）添加(INSERT)

a.不列出表字段的添加（从第一字段到最后一个字段都赋值）INSERT [INTO] 表名VALUE[S] (值,值,值...);

b.不省略字段添加(添加一条记录)

INSERT [INTO] 表名(字段名,字段名...) VALUE[S] (值,值...);

c.不省略字段添加(添加多记录)

INSERT [INTO] 表名(字段名,字段名...)

VALUE[S]

(值,值...)，

(值,值...)...;

d.INSERT...SET （只能添加一条记录）

INSERT [INTO] 表名SET 字段名=值,

字段名=值...;

（2）更新(UPDATE)

UPDATE 表名SET 字段名=值,

字段名=值... [WHERE 条件];

说明:

如果更新没有WHERE 条件，表中字段值都相同的值记录

(3)删除(DELETE)

a.DELETE FROM 表名[WHERE 条件]；

说明:如果删除没有WHERE 条件,将会清空表中所有数据，AUTO_INCREMENT 不重置

b. TRUNCATE 表名；

说明: 清空表中所有数据，并且AUTO_INCREMENT 重置

四、表中对数据的查询(DQL)

1、查询所有字段（效率底）

SELECT * FROM 表名;

2、列出字段查询

SELECT 字段名|expr，字段名|expr...

FROM 表名;

3、给字段起别名

SELECT 字段名[AS]别名

FROM 表名;

4、给表起别名

SELECT 字段名

FROM 表名[AS]别名;

5、表名（别名).字段名(字段来自哪个表)

SELECT 表名（别名).字段名

FROM 表名[AS]别名;

6、数据库名.表名(表来自哪个数据库)

SELECT 字段名

FROM 数据库名.表名；

7、

SELECT 字段名|expr，字段名|expr...

FROM 表名

[WHERE 条件]

[GROUP BY 字段]

[HAVING 条件]

[ORDER BY 字段]

[LIMIT [$offset,]$length];

7.1

WHERE 条件：条件过滤

（1）比较运算符

> >= < <= =(等于) != <>

<=>(判断NULL)

（2）IS [NOT] NULL (判断NULL)

（3）[NOT] BETWEEN ...AND 范围值

（4）[NOT] IN(值,值...) 某个值

（5）逻辑运算符实现条件过滤

!非

&& AND 与

|| OR 或

(6) LIKE 实现模糊查询

[NOT] LIKE '字符串'

关键字

（1）%：包含任意多个字符(0个，1个，多个)

（2）_: 包含任意一个字符

7.2 GROUP BY 字段：分组，将字段中相同的值分为一组

说明:

a.分组值显示一个结果，如果多个值显示小编号的结果,一般显示分组的字段

b. 分组结合聚合函数使用

COUNT(*):获得每组中的个数，包含null

COUNT(字段) :不包含null值

AVG（字段）:获得每组中的平均值

MAX（字段）:获得每组中的最大值

MIN（字段）：获得每组中的最小值

SUM（字段）:获得每组中的和

7.3 HAVING 条件：二次条件过滤

说明:

a. WHERE 对字段的条件过滤

b. HAVING对一个运行结果的条件过滤，结合分组使用

7.4 ORDER BY 字段:对字段排序

说明:

ORDER BY 字段[ASC升序|DESC降序]

7.5

LIMIT [$offset,]$length:显示n条记录

说明：

a.$offset：偏移量，起始编号，编号从0开始

b.$length:显示记录(行)长度

c.实现web程序的分页原理

计算$offset =（当前页-1）*显示长度

$offset = （$curpage-1）*$pagesize

Day03

一、对表中的数据查询

SELECT 字段名,字段名...

FROM 表名

[WHERE 条件]

[GROUP BY 字段]

[HAVING 条件]

[ORDER BY 字段]

[LIMIT [$offset,]$length]

计算$offset = (当前页-1)*显示条数

实现web页面分页原理

二、MySQL运算符和函数

说明:用SELECT 语句输出显示

1、算术运算符及函数

a. + - * / % ，MOD 求余数

b.POW POWER 求幂次

c. SQRT 开平方

d.FLOOR 向下取整

e.CEIL 向上取整

f.ROUND 四舍五入

g.RAND:随机小数[0,1)

例如: [0-100]整数

ROUND( RAND()*100)

2、字符函数

a.LOWER：转为小写字母

b.UPPER：转为大写字母

c.SUBSTR, SUBSTRING:字符串截取,编号从1开始

d.CONCAT:CONCAT(str,str,str);字符串连接

e.CONCAT_WS:CONCAT_WS(delimiter,str,str,str)

用分隔符将字符串连接

f.TRIM: 清除两边空格

g.RTRIM:清除右边空格

h.LTRIM:清除左边空格

i.REPLACE: 字符串替换

REPLACE($str,$reseach,$replacement)

j.LENGTH ：字符串长度

3、比较运算符

a. > >= < <= = != <> <=>判断NULL值

b. [NOT] BETWEEN ... AND

c. [NOT] IN(值，值...)

d. IS [NOT] NULL 判断NULL值

4、逻辑运算符

! (非)

&& AND (与并且)

|| OR (或)

5、信息函数

a. DATABASE() :显示当前选择数据库

b. NOW():获得系统时间日期函数

c. VERSION()获得MYSQL版本

d. USER()：获得当前使用MYSQL的用户

6、聚合函数

COUNT(*): 获得每组中的个数，包含null值COUNT(字段):不包含null值

AVG(字段):获得每组中的平均值

MAX(字段):获得每组中的最大值

MIN(字段):获得每组中的最小值

SUM(字段):获得每组中的和

7、加密函数

MD5：返回32位的散列值

PASSWORD

三、多表操作

1、表复制

INSERT 表(字段，字段...)

SELECT ...

2、多表更新（根据一个表更新另一个字段）UPDATE 表1

INNER JOIN 表2

ON 两个表的逻辑关系

SET 字段=值[WHERE 条件]；

3、多表联合查询

SELECT 字段名,字段名... FROM 表1

连接类型表2

ON 两个表的逻辑关系

连接类型表3

ON 两个表的逻辑关系

(1)连接类型

a.内连接（INNER JOIN）：查找两个表符合条件的信息

b.外连接：

左外连接（LEFT [OUTER] JOIN）显示左表中的所有信息和右表中符合条件的信息，如果左表中的信息右表没有，用null 填补

右外连接（RIGHT [OUTER] JOIN）显示右表中的所有信息和左表中符合条件的信息，如果右表中的信息左表没有，用null 填补

4、多表删除（根据一个表删除另一个表的信息）

DELETE 删除表FROM 表1

INNER JOIN 表2

ON 两个表的逻辑关系

[WHERE 条件]

5、子查询(嵌套查询)

1.子查询：在SQL语句中嵌套SELECT语句

2.特点:

(1)子查询是嵌套SELECT语句

(2) 子查询的SELECT语句要用括

号括起来并起别名

(3)一般结合WHERE 和GROUP BY 使用

3.使用

(1)WHERE后使用

a. IN

b. 比较运算符：

说明:比较运算符只能比较一个结果，当结果不唯一时,会报错，要ALL 或SOME/ANY 来解决

> >= ALL 大于最大值

< <= ALL 小于最小值

> >= SOME/ANY 大于最小值

< <= SOME/ANY 小于最大值

= SOME/ANY IN

(2)FROM 后使用:产生新表用括号括起来，并且起别名，例如联合查询产生新表

4. INSERT...SET:可以实现子查询

四、约束条件（Constraint）

1、约束：对字段实现非空，唯一，完整性等约束。

2、约束种类

a.NOT NULL 非空

b.DEFAULT 默认值

c.[PRIMARY] KEY 主键

d.UNIQUE [KEY] 唯一性

e. FOREIGN KEY 外键

3、约束分类

(1)列约束:写在字段后叫列约束

(2)表约束：可以对一个字段以上实现约束

说明：

a. NOT NULL ,DEFAULT 必须是列约束

b. 两个字段同时为主键，或外键，必须是表约束

4、约束格式

（1）列约束

CREATE TABLE test(

id SMALLINT UNSIGNED KEY AUTO_INCREMENT,

name NOT NULL UNIQUE KEY

);

（2）表约束

CREATE TABLE test(

id SMALLINT UNSIGNED AUTO_INCREMENT,

name NOT NULL,

PRIMARY KEY(id),

UNIQUE KEY(name)

);

5、FOREIGN KEY 外键

（1）外键：对两个表实现的完整性约束

(2)格式

CREATE TABLE test(

....,

[CONSTRAINT 约束名称] FOREIGN KEY(外键列)

REFERENCES 参考表(字段名称)

);

说明:

(1)一定是表约束

(2)一定先有参考表(父表)，在有外键表(子表)

(3)数据类型必须相同

例如:

a.如果是整型，UNSIGNED也必须相同

b.如果是字符型，长度可以不一样，但

编码必须一致

(4) 创建外键表，MySQL引擎必须是InnoDB

(5)外键表(子表)中的外键列只能添加参考表(父表)对应字段的相应信息值

(6) 如果创建外键列没定义约束名称，那会自动产生索引外键约束名称Day04

1、外键（FOREIGN KEY）

(1) 外键:对两个表的完整性和一致性的约束

(2)

CREATE TABLE test(

...

FOREIGN KEY(外键列)

REFERENCES 参考表(字段名)

[ON UPDATE CASCADE|RESTRICT|SET NULL|NO ACTION] [ON DELETE CASCADE|RESTRICT|SET NULL|NO ACTION] );

2、逻辑外键:父表和子表是一个表

举例: 无限级分类菜单

3、数据库的备份和恢复

(1)数据库的导出

mysqldump uroot -p 数据库名称[表名]

> 位置文件名称(.sql)

（2）数据库的导入

mysql> USE 数据库名称

mysql> source 位置文件名称(.sql)

4、可视化工具操作MySQL

(1) (C/S)：Client/Server

MySQL-Front Navicat等

(2)（B/S）:Browser/Server

phpmyAdmin

(3) phpmyAdmin 安装

设置配置php.ini

加载动态链接库文件

a.

;extension = php_mysql.dll

;extension = php_mysqli.dll

;extension = php_mbstring.dll

b. 指定动态链接库文件夹位置

extension_dir = E:\psd1603\php5.4\ext

将前面的分号去掉,重启apache生效