MySQL基础知识

最新推荐文章于 2024-04-26 17:09:36 发布

蚂蚁要上天

最新推荐文章于 2024-04-26 17:09:36 发布

阅读量549

点赞数 2

分类专栏： web sql 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/gooaaee/article/details/58589803

版权

web 同时被 2 个专栏收录

3 篇文章 0 订阅

订阅专栏

sql

1 篇文章 0 订阅

订阅专栏

对慕课网的课程《与MySQL的零距离接触》的随手总结，在此感谢平然老师的课程，附上链接：

与MySQL的零距离接触

一些常用的命令
初涉MySQL
数据类型与操作数据表
约束以及修改数据表
操作数据表中的记录
子查询与连接
运算符和函数
自定义函数
MySQL存储过程
MySQL存储引擎
MySQL图形化管理工具

一些常用的命令：

mysql -u root -p;
CREATE TABLE tb3(id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,username VARCHAR(30) NOT NULL);
打开数据库：USE 数据库名称;
显示打开的数据库：SELECT DATABASE();
查看数据表结构：SHOW COLUMNS FROM tb3;
查看表的记录：SELECT * FROM tb3;
SHOW TABLES;
SHOW TABLES FROM mysql;
创建数据库：CTEATE DATABASE //数据库名称;
查看数据库：SHOW DATABASES;
查询数据库编码方式：SHOW CREATE DATABASE //数据库名称;
新建数据库并设置编码方式：CREATE DATABASE IF NOT EXISTS t2 CHARACTER SET gbk;
修改数据库：ALTER DATABASE t2 CHARACTER SET utf8;
删除数据库：DROP DATABASE t1;
修改表的字段：5-7 ALTER
删除主键：ALTER TABLE tb DROP PRIMARY KEY;
清屏：cls
SET NAMES GBK;//只影响客户端的编码方式，不影响服务器端的编码方式

1 初涉MySQL

1-4 MySQL登录与退出

mysql登录退出 mysql -uroot -p -P3306 -h127.0.0.1
127.0.0.1是本机回环地址

1-5 修改MySQL提示符

mysql -uroot -p密码 –prompt 提示符

1-7 操作数据库

创建数据库：CTEATE DATABASE 数据库名称;
查看数据库：SHOW DATABASES;
查询数据库编码方式：SHOW CREATE DATABASE 数据库名称;
新建数据库并设置编码方式：CREATE DATABASE IF NOT EXISTS t2 CHARACTER SET gbk;
修改数据库：ALTER DATABASE t2 CHARACTER SET utf8;
删除数据库：DROP DATABASE t1;

2 数据类型与操作数据表

2-2 MySQL数据类型之整型

略

2-5 MySQL数据类型之字符型

字符型是定长的类型，如果写的没有达到定长，系统自动以空格补齐

2-6 创建数据表

打开数据库：USE 数据库名称;
显示打开的数据库：SELECT DATABASE();
加上IF NOT EXISTS不提示错误，最多提示警告
逗号是两个字段的分隔符
CREATE TABLE tb4(username VARCHAR(20),age TINYINT UNSIGNED,salary FLOAT(8.2) UNSIGNED); FLOAT(8,2)8位，两位是小数点后的。

2-7 MySQL查看数据表

SHOW TABLES;
SHOW TABLES FROM mysql;

2-8 MySQL查看数据表结构

查看数据表结构：SHOW COLUMNS FROM tb1;

2-9 MySQL记录的插入与查找

完整插入记录：INSERT tb1 VALUES(Tom,25,7863.25);
部分插入记录：INSERT tb1(username,salary) VALUES('John',4500.69);
星号是字段的过滤：SELECT * FROM tb1;

3 约束以及修改数据表

3-1 回顾和概述

主键约束PRIMARY KEY
唯一约束UNIQUE KEY
默认约束DEFAULT
非空约束NOT NULL

3-2 MySQL外键约束的要求解析

外键约束示例：

USE test;
CREATE TABLE provinces(id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,pname VARCHAR(20) NOT NULL);
SHOW CREATE TABLE provinces;
CREATE TABLE users(id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,username VARCHAR(10) NOT NULL,pid SMALLINT UNSIGNED,FOREIGN KEY (pid) REFERENCES provinces (id)); 
//pid字段必须和id字段一样，其中id为参照列，pid为外键列，provinces为父表，users为子表。
SHOW INDEXES FROM provinces;
SHOW INDEXES FROM provinces\G;

3-3 MySQL外键约束的参照操作

外键约束的参照CASCAD、SET NULL、RESTRICT、NO ACTION
CASCADE:从父表删除或更新且自动删除或更新子表中匹配的行
CASCADE使用示例：CREATE TABLE users1(id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,username VARCHAR(10) NOT NULL,pid SMALLINT UNSIGNED,FOREIGN KEY (pid) REFERENCES provinces (id) ON DELETE CASCADE);

外键参照了主键，不能创建没有主键值的外键。
删除id为3的那行信息：DELECT FROM provinces WHERE id=3

3-4 MySQL表级约束与列级约束

对一个数据列建立的约束，称为列级约束
对多个数据列建立的约束，称为表级约束
表级约束只能在列定义后声明

3-5 MySQL 修改数据表–添加/删除列

添加单列示例：ALTER TABLE users1(表名称) ADD password(要添加的列的名称) VARCHAR(32) NOT NULL AFTER username(添加到username列的后面); //添加到某数据库的某个数据表的某个列后
添加多列：ALTER TABLE tbl_name ADD (col_name column_definition,...);
删除列：ALTER TABLE tbl_name(表名称) DROP col_name(列名称);
展示表中的列：SHOW COLUMNS FROM users1(表名称);
删除多列示例：ALTER TABLE users1(表名称) DROP password(列名称),DROP age(列名称);

3-6 MySQL 修改数据表–添加约束

添加主键约束：ALTER TABLE users2(表名称) ADD CONSTRAINT PK_users2_id PRIMARY KEY (id(列名称));//SHOW COLUMNS FROM users2;
添加唯一约束：ALTER TABLE users2(表名称) ADD UNIQUE (username(列名称)); //SHOW CREATE TABLE users2;
添加外键约束：ALTER TABLE users2(表名称) ADD FOREIGN KEY (pid(表users2中的列的列名称)) REFERENCES provinces(表名称) (id(表provinces中的列的列名称));
添加默认约束：ALTER TABLE users2(表名称) ALTER age(列名称) SET DEFAULT 15;//把users2中age的默认值改为15
删除默认约束：ALTER TABLE users2(表名称) ALTER age(列名称) DROP DEFAULT;//把users2中age的默认值删除清空，变为NULL

3-7MySQL 修改数据表–删除约束

删除主键约束：ALTER TABLE users2 DROP PRIMARY KEY;
删除唯一约束：ALTER TABLE users2(表名称) DROP INDEX username;//用命令SHOW INDEXES FROM users2(表名称)\G;查看唯一约束的列名称
删除外键约束：ALTER TABLE users2 DROP FOREIGN KEY users2_ibfk_1;//用SHOW CREATE TABLE users2;查看外键约束

3-8 MySQL 修改数据表–修改列定义和更改数据表

修改列定义：ALTER TABLE users2 MODIFY id SMALLINT UNSIGNED NOT NULL FIRST;//修改id字段的数据类型和在表users2中所处的位置
修改列名称：ALTER TABLE users2 CHANGE pid(旧的字段名称) p_id(新的字段名称) TINYINT UNSIGNED NOT NULL;//修改pid字段，改为p_id字段
数据表更名：ALTER TABLE users2 RENAME users3;//把表名users2改为新的users3

4 操作数据表中的记录

4-2 MySQL插入记录INSERT

插入记录示例：

CREATE TABLE users( id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT ,
username VARCHAR(20) NOT NULL ,
password VARCHAR(32) NOT NULL ,
age TINYINT UNSIGNED NOT NULL DEFAULT 10 ,
sex BOOLEAN 
);//对表users的各个字段进行定义，字段即表的各个列
SELECT * FROM users;//显示的是表中的各个字段的值，字段既是列
SHOW COLUMNS FROM users;//显示的是表中各个字段的属性，即各个列的属性
INSERT users VALUES(NULL,'John','456',25,1);//按照表的字段定义进行插入记录操作
INSERT users VALUES(DEFAULT,'Tom','123',3*7-6,1);//可以对自动增加字段赋予默认值，即该例中有AUTO_INCREMENT的id字段
INSERT users VALUES(DEFAULT,'Tom','123',3*7-5,1),(NULL,'Rose',md5('123'),DEFAULT,0);//插入多个记录，md5('')是PHP中的转换哈希函数

4-3 MySQL插入记录INSERT SET-SELECT

略

4-4 MySQL单表更新记录

更新记录(单表更新):

UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1 = {expr1|DEFAULT} [, col_name2 = {expr2|DEFAULT}] ... [WHERE where_condition]

示例1：

//更新users中的age字段和sex字段。users是数据表
UPDATE users SET age =age - id , sex = 0 ;
//对表users的各个字段进行定义，字段即表的各个列
CREATE TABLE users( id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT ，
                    username VARCHAR(20) NOT NULL ,
                    password VARCHAR(32) NOT NULL ,
                    age TINYINT UNSIGNED NOT NULL DEFAULT 10 ,
                    sex BOOLEAN 
                    );

示例2：

//目的是实现表users中的所有id为偶数的age加10
UPDATE users SET age=age + 10 , WHERE id % 2 = 0 ;

4-5 mysql单表删除记录

删除记录(单表删除)：

DELETE FROM tbl_name [WHERE where_condition]

示例：

//删除表users中的id为6的记录
DELETE FROM users WHERE id = 6 ;

4-6 mysql查询表达式解析

查找记录：

SELECT select_expr[ , select_expr ...]
[
    FROM table_references
    [WHERE where_condition]
    [GROUP BY {col_name|position} [ASC|DESC] , ...]
    [HAVING where_condition]
    [ORDER BY { col_name|expr|position|} [ASC|DESC] , ...]
    [LIMIT {[offset ,] row_count|row_count OFFSET offset}]
]

示例1：

//只查询id和username两列。查询显示的列与查询输入的排序有关，结果集显示顺序是id、username。
SELECT id , username FROM users ;
//另外一种查询方式
SELECT users.id , users.username FROM users ;
//以字段别名方式显示结果集,结果集以userId、uname字段显示
SELECT id AS userId , username AS uname FROM users ;

4-7 mysql where语句进行条件

略

4-8 mysql group by语句对查询结果进行分组

查询结果分组：

//ASC是指升序，DESC是指降序
[GROUP BY {col_name|position} [ASC|DESC] , ...]

示例：

//用sex对数据表users进行分组，结果集显示各个sex组中在数据表users中第一个出现的sex的id
SELECT id FROM users GROUP BY sex ;

示例结果显示

4-9 having语句设置分组条件

分组条件：

//对满足该条件的记录进行分组操作
[HAVING where_condition]

示例：

//对满足id值大于等于1的记录进行分组
SELECT sex FROM users GROUP BY sex HAVING count(id) >= 1;

4-10 order by语句对查询结果排序

对查询结果进行排序

[ORDER BY {col_name|expr|position}[ASC|DESC], ... ]

示例：

//对数据表按照id降序排序
SELECT * FROM users ORDER BY id DESC ;
//对数据表按照age升序、id降序排序
SELECT * FROM users ORDER BY age,id DESC ;
//对数据表按照age降序、id降序排序
SELECT * FROM users ORDER BY age DESC,id DESC ;

select * from users

order by age,id desc

order by age desc,id desc

4-11 limit语句限制查询数量

限制查询结果返回的数量：

[LIMIT {[offset,] row_count|row_count OFFSET offset}]

示例：

//把数据表users中的age>=25的username字段插入给数据表test
CREATE TABLE test( id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
                   username VARCHAR(20)
                   );
INSERT test(username) SELECT username FROM users WHERE age>=25;

创建test数据表

把age>=25的username插入到test数据表中

结果集显示

5 子查询与连接

5-1 数据准备

//修改编码方式，只影响客户端的显示方式，并不影响真实的数据表中的数据
SET NAMES GBK ;

5-2mysql子查询简介

子查询(Subquery)是指出现在其他SQL语句内的SELECT子句。
子查询是指嵌套在查询内部，且必须始终出现在圆括号内。

SELECT * FROM t1 WHERE col1 = (SELECT col2 FROM t2);
其中SELECT * FROM t1，称为Outer Query/Outer Statement 
SELECT col2 FROM t2 ，称为SubQuery

5-3 由比较运算符引发的子查询

AVG是一个聚合函数，返回一个平均值

//查询表tdb_goods中goods_price的平均值
SELECT AVG(goods_price) FROM tdb_goods;
//查询表tdb_goods中goods_price的平均值，且只显示小数点后两位
SELECT ROUND(AVG(goods_price),2);
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price>=5636;
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price>=(SELECT ROUND (AVG(goods_price),2) FROM tdb_goods);
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price > ANY (SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本');

ANY、SOME、ALL

goods_price

goods_id goods_name goods_price

5-4 由[NOT] IN/EXISTS引发的子查询

示例：

//显示除去SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本';的3个的剩下的19个
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price != ALL (SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本');

5-5 使用INSERT … SELECT插入记录

INSERT…SELECT
将查询结果写入数据表

INSERT [INTO] tbl_name [(col_name,...)]
SELECT ...

示例准备：

CREATE TABLE IF NOT EXISTS tdb_goods_cates(cate_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
                                           cate_name VARCHAR(40) NOT NULL
                                           );
SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;

示例：

DESC tdb_goods_cates;
INSERT tdb_goods_cates(cate_name) SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;

这里写图片描述

5-6 多表更新

多表更新

UPDATE table_references SET col_name1={expr1|DEFAULT}[, col_name2={expr2|DEFAULT}]...[WHERE where_condition]

示例：

//数据表tdb_goods中所有goods_name与数据表tdb_goods_cates中cate_name相等，修改goods_cate为cate_id。参照数据表tdb_goods_cates修改数据表tdb_goods
UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate = cate_name SET goods_cate = cate_id;

goods_cate,cate_name

5-7 多表更新之一步到位

创建数据表同时将查询结果写入到数据表

CREATE TABLE [IF NOT EXISTS] tbl_name [(create_definiton,...)] select_statement

示例：

//创建表tdb_goods_brands并且写入记录，记录来自表tdb_goods的brand_name
CREATE TABLE tdb_goods_brands
(brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
 brand_name VARCHAR(40) NOT NULL
 )SELECT brand_name FROM tdb_goods GROUP BY brand_name; 
 SHOW TABLES;
 SELECT * FROM tdb_goods\G;
 SELECT * FROM tdb_goods_brands;
 SHOW COLUMNS FROM tdb_goods;
 SHOW COLUMNS FROM tdb_goods_brands;
 //对满足两个表的brand_name相等条件的记录进行更新，更新表tdb_goods中满足条件的记录的brand_name为表tdb_goods_brands中对应的brand_id
 UPDATE tdb_goods AS g INNER JOIN tdb_goods_brands AS b ON g.brand_name =b.brand_name SET g.brand_name =b.brand_id;
 SELECT * FROM tdb_goods\G;
 //修改表的结构,把字符型的goods_cate和brand_name修改为数据型的cate_id和brand_id
 ALTER TABLE tdb_goods 
 CHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL,
 CHANGE brand_name brand_id SMALLINT NOT NULL;

5-8 连接的语法结构

语法结构

table_reference
{[INNER|CROSS]JOIN|{LEFT|RIGHT}[OUTER]JOIN}
table_reference
ON conditional_expr

数据表参照

table_reference
tbl_name[[AS]alias] | table_subquery[AS] alias
数据表可以使用tbl_name AS alias_name或tbl_name alias_name赋予别名
table_subquery可以作为子查询使用在FROM子句中，这样的子查询必须为其赋予别名

5-9 内连接INNER JOIN

连接类型

INNER JOIN,内连接
在MySQL中，JOIN,CROSS JOIN和INNER JOIN 是等价的。
LEFT [OUTER] JOIN,左外连接
RIGHT [OUTER] JOIN，右外连接

连接条件

使用ON关键字来设定连接条件，也可以使用WHERE来代替
通常使用ON关键字来设定连接条件
使用WHERE关键字进行结果集记录的过滤。

示例：

SELECT goods_id,goods_name,cate_name FROM tdb_goods INNER JOIN tdb_goods_cates ON tdb_goods.cate_id=tdb_goods_cates.cate_id;

INNER JOIN

INNER JOIN tdb_goods tdb_goods_cates

5-10 外连接OUTER JOIN

示例：

//LEFT JOIN
SELECT goods_id,goods_name,cate_name FROM tdb_goods LEFT JOIN tdb_goods_cates ON tdb_goods.cate_id=tdb_goods_cates.cate_id;
//RIGHT JOIN

LEFT JOIN

用左连接,说白一点就是以左边那个表为标准，左边那表（表a）的所有记录必须得全部出现，例如：SELECT * FROM A LEFT JOIN B ON A.A_id=B.name_id

网上发现了一篇很好的总结，原文地址：

MySQL JOIN 总结参考借鉴

百度来的两张MySQL JOIN的图片：

SQL join2

5-11 多表连接

示例：

SELECT goods_id,goods_name,cate_name,goods_price FROM tdb_goods AS g 
INNER JOIN tdb_goods_cates AS c ON g.cate_id=c.cate_id 
INNER JOIN tdb_goods_brands AS b ON g.brand_id=b.brand_id;

5-12 关于连接的几点说明

略

5-13 无限级分类表设计

无限分类的数据表设计

CREATE TABLE tdb_goods_types(
 type_id   SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
 type_name VARCHAR(20) NOT NULL,
 parent_id SMALLINT UNSIGNED NOT NULL DEFAULT 0);

 INSERT tdb_goods_types(type_name,parent_id) VALUES('家用电器',DEFAULT);
 INSERT tdb_goods_types(type_name,parent_id) VALUES('家用电器',DEFAULT);
 INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑、办公',DEFAULT);
 INSERT tdb_goods_types(type_name,parent_id) VALUES('大家电',1);
 INSERT tdb_goods_types(type_name,parent_id) VALUES('生活电器',1);
 INSERT tdb_goods_types(type_name,parent_id) VALUES('平板电视',3);
 INSERT tdb_goods_types(type_name,parent_id) VALUES('空调',3);
 INSERT tdb_goods_types(type_name,parent_id) VALUES('电风扇',4);
 INSERT tdb_goods_types(type_name,parent_id) VALUES('饮水机',4);
 INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑整机',2);
 INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑配件',2);
 INSERT tdb_goods_types(type_name,parent_id) VALUES('笔记本',9);
 INSERT tdb_goods_types(type_name,parent_id) VALUES('超级本',9);
 INSERT tdb_goods_types(type_name,parent_id) VALUES('游戏本',9);
 INSERT tdb_goods_types(type_name,parent_id) VALUES('CPU',10);

上面输入命令后插入记录后的结果图：
tdb_goods_types

自身连接：同一个数据表对其自身进行连接。
下面为表tdb_goods_types的自身连接操作：

//s相当于上面的LEFT JOIN图中的A，p对应于图上的B。
SELECT s.type_id , s.type_name , p.type_name FROM tdb_goods_types AS s 
LEFT JOIN tdb_goods_types AS p
ON s.parent_id=p.type_id;

上面命令结果图：
自身连接结果图

第二种自身连接方法：

SELECT p.type_id,p.type_name,s.type_name FROM tdb_goods_types AS p
LEFT JOIN tdb_goods_types AS s
ON s.parent_id=p.type_id;
//另外一种方法，结果一样
SELECT s.type_id , s.type_name , p.type_name FROM tdb_goods_types AS s
LEFT JOIN tdb_goods_types AS p
ON p.parent_id=s.type_id;

上面命令结果图：
另外一种表示方法

//通过p.type_name分组
SELECT p.type_id,p.type_name,s.type_name FROM tdb_goods_types AS p
LEFT JOIN tdb_goods_types s ON s.parent_id=p.type_id 
GROUP BY p.type_name;

上面命令的结果：
分类结果

//通过 p.type_name分组,再通过p.type_id排序
SELECT p.type_id,p.type_name,s.type_name FROM tdb_goods_types AS p
LEFT JOIN tdb_goods_types s ON s.parent_id=p.type_id 
GROUP BY p.type_name ORDER BY p.type_id;
//通过p.type_id进行分组结果不变(ps:暂时很疑惑通过p.type_name分组显示的顺序和用p.type_id分组为什么会有这样的显示的顺序)
SELECT p.type_id,p.type_name,s.type_name FROM tdb_goods_types AS p
LEFT JOIN tdb_goods_types s ON s.parent_id=p.type_id 
GROUP BY p.type_id;

上面命令的结果：
重新按id排序排序结果

//显示每个记录对应的子类数目
SELECT p.type_id,p.type_name,count(s.type_name) child_count FROM tdb_goods_types AS p
LEFT JOIN tdb_goods_types s ON s.parent_id=p.type_id 
GROUP BY p.type_name ORDER BY p.type_id;

上面命令的结果：
显示子类数目

通过p.type_id分组后显示的结果：
通过p.type_id分组显示子类数目

5-14 多表删除

多表删除：

DELETE tbl_name[.*][,tbl_name[.*]] ...
FROM table_references
[WHERE where_condition]

示例：

//用goods_name分组并用goods_id排序
 SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name ORDER BY goods_id;

上面用goods_name分组后只显示每组的goods_name,重复的不显示出来，图中21，22与18，19重复，未显示。结果图如下：

//查找表中重复的记录并显示
SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name)>=2;

上面命令结果图：
只显示满足HAVING条件的重复的

//删除重复的且重复中goods_id大的记录
DELETE t1 FROM tdb_goods AS t1 LEFT JOIN 
(SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name)>=2) AS t2 
ON t1.goods_name=t2.goods_name WHERE t1.goods_id>t2.goods_id;

上面命令运行的结果
删除重复的记录

6 运算符和函数

6-1 回顾与概述

字符函数
数值运算符与函数
比较运算符与函数
日期时间函数
信息函数
聚合函数
加密函数

6-2 MySQL字符函数

字符函数：

字符函数

示例：

CREATE DATABASE imooc;
SELECT CONCAT('imooc','MySQL');
SELECT CONCAT('imooc','-','MySQL');

上面命令结果图：
CONCAT

SELECT CONCAT(first_name,last_name) AS fullname FROM test;

表test中的记录：
表test内容
上面命令的结果：
CONCAT

CONCAT_WS应用示例：

SELECT CONCAT_WS('|','A','B','C');
SELECT CONCAT_WS('-','imooc','MySQL','Functions');

FORMAT(),LOWER(),UPPER(), LEFT(), RIGHT()示例：

SELECT FORMAT(12560.75,2);               
SELECT LOWER('MySQL'); 
SELECT UPPER('mysql');
SELECT LEFT('MySQL',2);
SELECT LOWER(LEFT('MySQL',2));
SELECT RIGHT('MySQL',3);

LENGTH(),LTRIM(),RTRIM(),TRIM(),REPLACE(),SUBSTRING()函数示例：

SELECT LENGTH('My SQL');//空格也算，返回6
SELECT LTRIM('  MySQL   ');//返回删除前导空格后的字符串，即MySQL，共5+3个字符，其中3为3个空格
SELECT LENGTH(LTRIM('  MySQL   '));//返回8，即'MySQL   '
SELECT LENGTH(RTRIM('  MySQL   '));//返回7，即'  MySQL'
SELECT LENGTH(TRIM('  MySQL   '));//返回5，即'MySQL'
SELECT TRIM(LEADING '?' FROM '??MySQL???');//返回'MySQL???'
SELECT TRIM(TRAILING '?' FROM '??MySQL???');//返回'??MySQL'
SELECT TRIM(BOTH '?' FROM '??MySQL???');//返回'MySQL'
SELECT REPLACE('??My??SQL???','?','');//返回'MySQL'
SELECT SUBSTRING('MySQL',1,2);//返回'My'
SELECT SUBSTRING('MySQL',3);//返回'SQL'
SELECT SUBSTRING('MySQL',-1);//返回L

LIKE的用法：

SELECT 'MySQL' LIKE 'M%';//返回1。%(百分号)代表任意个字符，_(下划线)代表任意一个字符
SELECT * FROM test WHERE first_name LIKE '%O%';

上面命令的结果：
LIKE用法

SELECT * FROM test WHERE first_name LIKE '%%%';

上面用%%%命令的结果：
LIKE %%%

SELECT * FROM test WHERE first_name LIKE '%1%%' ESCAPE '1';//想查询tom%,由于%是通配符，代表任意个字符，所以用ESCAPE告诉系统1后面的%不是通配符。

上面用%1%% ESCAPE命令的结果：
LIKE %1%% ESCAPE

6-3 数值运算符和函数

数值运算符和函数：

示例：

SELECT 3+4;
SELECT CEIL(3.01);//向上取整,返回4
SELECT FLOOR(3.99);//向下取整,返回3
SELECT 3 DIV 4;//返回0
SELECT 3/4;//返回0.7500
SELECT 5 MOD 3;//返回2，和5%3结果一样
SELECT POWER(3,3);//返回27
SELECT ROUND(3.652,1);//保留小数点1位，即返回3.7
SELECT TRUNCATE(125.89,1);//截断小数点后1位，即返回125.8.TRUNCATE(125.89,-1)返回120

6-4 MySQL比较运算符和函数

比较运算符和函数：

示例：

SELECT 15 BETWEEN 1 AND 22;//返回真，即1，15在1到22之间
SELECT 13 IN(5,10,15,20);//返回假，即0，13不是(5，10，15，20)之中的值
SELECT * FROM test WHERE first_name IS NOT NULL;//显示表test中first_name不是空的记录

6-5 MySQL日期时间函数

日期时间函数：

示例：

SELECT NOW();//返回当前的日期和时间
SELECT CURDATE();//返回当前的日期
SELECT CURTIME();//返回当前的时间
SELECT DATE_ADD('2014-3-12',INTERVAL -365 DAY);//返回2013-3-12,含有WEEK,YEAR等
SELECT DATEDIFF('2013-3-12','2014-3-12');//返回-365
SELECT DATE_FORMAT('2014-3-12','%m/%d'/%Y);//返回03/02/2014

6-6 MySQL信息函数

信息函数：

示例：

SELECT CONNECTION_ID();
SELECT DATABASE();
DESC test;
ALTER TABLE test ADD id SMALLINT UNSIGNED KEY AUTO_INCREMENT FIRST;//为表test增加主键id
SELECT LAST_INSERT_ID();//显示最后写入的id号，如果同时写入多个记录，只返回同时写入的第一个记录的id

下图为上述写入多条记录的结果验证：
LAST_INSERT_ID()写入多条记录的验证

SELECT USER();//显示当前的用户
SELECT VERSION();//显示mysql版本号

6-7 MySQL聚合函数

聚合函数：返回结果只有一个

SELECT AVG(id) FROM test;//返回4.000
SELECT * FROM tdb_goods LIMIT 1;
SELECT ROUND(AVG(goods_price),2) AS avg_price FROM tdb_goods;//返回表goods_price中tdb_goods的平均值，小数点后保留两位
SELECT COUNT(goods_id) AS counts FROM tdb_goods;//统计表tdb_goods中goods_id的个数
SELECT MAX(goods_price) AS counts FROM tdb_goods;//求表tdb_goods中goods_price的最大值
SELECT SUM(goods_price) AS counts FROM tdb_goods;//对表tdb_goods中的goods_price求和

6-8 MySQL加密函数

加密函数：

示例：

SELECT MD5('admin');

7 自定义函数

7-1 回顾与概述

略

7-2 MySQL自定义函数简介

自定义函数：
用户自定义函数(user-defined function ,UDF)是一种对MySQL扩展的途径，其用法和内置函数相同
函数可以返回任意类型的值，同样可以接收这些类型的参数。
创建自定义函数：

CREATE FUNCTION function_name
RETURNS
{STRING|INTEGER|REAL|DECIMAL}
routine_body
//function_name为函数名
//routine_body为函数体
//函数体如果为复合结构则使用BEGIN...END语句
//复合结构可以包含声明，循环，控制结构

7-3 MySQL创建不带参数的自定义函数

示例：

SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日 %H点:%i分:%s秒');  

CREATE FUNCTION f1() RETURNS VARCHAR(30)
RETURN DATE_FORMAT(NOW(),'%Y年%m月%d日 %H点:%i分:%s秒');//定义为f1()函数，用SELECT f1()调用

7-4 MySQL创建带有参数的自定义函数

示例：

CREATE FUNCTION f2(num1 SMALLINT UNSIGNED,num2 SMALLINT UNSIGNED)
RETURNS FLOAT(10,2) UNSIGNED
RETURN (num1+num2)/2;//f2()计算两个数的平均值,用SELECT f2(num1,num2)调用

7-5 MySQL创建具有复合结构函数体的自定义函数

复合结构函数示例：

CREATE TABLE test1(
id TINYINT(3) UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20) DEFAULT NULL
);//创建表test1
INSERT test1(username) VALUES('John');//插入记录
INSERT test1(username) VALUES('111');//插入记录

DELIMITER // //用//(两条斜线)代替;(分号)表示语句结束
CREATE FUNCTION adduser(username VARCHAR(20))
RETURNS INT UNSIGNED
BEGIN
INSERT test1(username) VALUES(username);
RETURN LAST_INSERT_ID();
END
//  //代表语句结束，用自定义函数adduser()进行插入记录操作

DELIMITER示例结果：

上面的复合结构函数体命令的结果：
复合结构函数体的自定义函数

插入后查询

删除函数：

DROP FUNCTION [IF EXISTS] function_name

8 MySQL存储过程

8-2 MySQL存储过程简介

存储过程可以优化执行速度，可以把一些过程封装成存储过程来使MySQL执行它最精通的部分

8-3 MySQL存储过程语法结构解析

存储过程：

CREATE 
[DEFINER={user|CURRENT_USER}]
PROCEDUER sp_name([proc_parameter[,...]])
[characteristic ...]routine_body

proc_parameter:
[IN|OUT|INOUT]param_name type
//sp_name指的是存储过程的名字
//IN,表示该参数的值必须在调用存储过程时指定
//OUT,表示该参数的值可以被存储过程改变，并且可以返回
//INOUT,表示该参数的调用时指定，并且可以被改变和返回
//过程体由合法的SQL语句构成，过程体如果为复合结构则使用BEGIN...END

8-4 MySQL创建不带参数的存储过程

创建不带参数的存储过程sp1():

CREATE PROCEDURE sp1() SELECT VERSION();
CALL sp1;//调用不带参数的存储过程sp1()

8-5 MySQL创建带有IN类型参数的存储过程

/*创建一个存储过程removeUserById()*/
/*WHERE后面的第一个id指的是数据表中的字段，第二个id指的是要传递的参数*/
DELIMITER //
CREATE PROCEDURE removeUserById(IN p_id INT UNSIGNED)
BEGIN
DELETE FROM users WHERE id=p_id;
END
//
DELIMITER ;
/*修改存储过程*/
ALTER PROCEDURE sp_name[characteristic ...]
/*删除存储过程*/
DROP PROCEDURE [IF EXISTS] sp_name

上面命令的执行结果：
带IN的存储过程

8-6 MySQL创建带有IN和OUT类型参数的存储过程

/*创建存储过程removeUserAndReturnUserNums()*/
/*INTO代表的是把count(id)放入到userNums中*/
/*变量@nums是用户变量，是跟用户的客户端绑定的，只对用户的客户端生效，在客户端是有效的。在BEGIN...END语句块之间的变量是局部变量*/
DELIMITER //
CREATE PROCEDURE removeUserAndReturnUserNums(IN p_id INT UNSIGNED,OUT userNums INT UNSIGNED)
BEGIN
DELETE FROM users WHERE id=p_id;
SELECT count(id) FROM users INTO userNums;
END
//
DELIMITER ;
CALL removeUserAndReturnUserNums(4,@nums);
SELECT @nums;

上面命令的执行结果：
IN OUT @nums

8-7 MySQL创建带有多个OUT类型参数的存储过程

/*创建一个存储过程可以根据年龄删除记录。OUT参数一个是要返回删除的用户，一个返回剩余的用户*/
/*ROW_COUNT()是指的是被插入或被删除或被更新的记录的总数*/
DELIMITER //
CREATE PROCEDURE removeUserByAgeAndReturnInfos(
IN p_age SMALLINT UNSIGNED,
OUT deleteUsers SMALLINT UNSIGNED,
OUT userCounts SMALLINT UNSIGNED
)
BEGIN
DELETE FROM users WHERE age=p_age;
SELECT ROW_COUNT() INTO deleteUsers;
SELECT COUNT(id) FROM users INTO userCounts;
END
//
DELIMITER ;
CALL removeUserByAgeAndReturnInfos(25,@a,@b);
SELECT @a,@b;

上面命令的执行结果：
多个OUT参数

8-8 MySQL存储过程与自定义函数的区别

存储过程可以返回多个值，函数只能有一个返回值

9 MySQL存储引擎

9-2 MySQL存储引擎简介

存储引擎：
MySQL可以将数据以不同的技术存储在文件(内存)中，这种技术就称为存储引擎。
每一种存储引擎使用不同的存储机制、索引技巧、锁定水平、最终提供广泛且不同的功能。
MySQL支持的存储引擎：

MyISAM
InnoDB
Memory
CSV
Archive

9-3 MySQL相关知识点之并发处理

并发控制：
当多个连接对记录进行修改时保证数据的一致性和完整性
锁：
共享锁(读锁)：在同一时间段内，多个用户可以读取同一个资源，读取过程中数据不会发生任何变化。
排他锁(写锁)：在任何时候只能有一个用户写入资源，当进行写锁时会阻塞其他的读锁或者写锁操作。
锁颗粒：
表锁是一种开销最小的锁策略
行锁匙一种开销最大的锁策略

9-4 MySQL相关知识点之事务处理

事务：
事务用于保证数据库的完整性
事务的特性：

原子性(Atomicity)
一致性(Consistency)
隔离性(Isolation)
持久性(Durability)

9-5 MySQL相关知识点之外键和索引

外键：

是保证数据一致性的策略

索引：

是对数据表中一列或多列的值进行排序的一种结构

9-6 MySQL各个存储引擎特点

各种存储引擎的特点：
各个存储引擎特点
MyISAM:适用于事务的处理不多的情况。
InnoDB:适用于事务处理比较多，需要有外键支持的情况。

9-7 MySQL设置存储引擎

通过修改MySQL配置文件实现
default-storage-engine=engine
通过创建数据表命令实现
CREATE TABLE table_name(
…
…
)ENGINE=engine;
示例：

CREATE TABLE tp1(
s1 VARCHAR(10)
)ENGINE=MyISAM;
SHOW CREATE TABLE tp1;//通过创建数据表命令实现设置存储引擎
ALTER TABLE tp1 ENGINE=InnoDB;//直接修改存储引擎

10 MySQL图形化管理工具

10-1 课程介绍

管理工具：

PHPMyAdmin
Navicat
MySQL Workbench

10-2 MySQL图形管理工具之phpMyAdmin

网站：planet.phpmyadmin.net

10-3 MySQL图形管理工具之Navicat for MySQL

10-4 MySQL图形管理工具之MySQL Workbench

蚂蚁要上天

关注

2
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
MySQL基础知识

MySQL基础
复制链接

扫一扫

专栏目录