目录
5.3索引分类 -- 重点(可以提升查询速度,面试中遇到的问题也多)
1. mysql的数据类型
1.1 整数类型、浮点数类型和定点数类型
1.1.1 整数类型
常用int
1.1.2浮点数据类型和定点数据类型
常用float、double
1.2 时间类型
Teimstamp与datetime的范围不一样。Timestamp从1970开始,datetime从1000开始。
1.3字符串类型
Char(10),定义长度为10,存入2个字符,占的空间还是10
Varchar(10),定义长度为10,存入2个字符,占的空间是2个字节,比Char更的弹性。
但char在搜索上比varchar更有优势。
TEXT,当新闻内容和论文内容时使用。
ENUM和SET基本上没什么使用。
1.4 二进制类型
常用BLOB,二进制是存些图片和视频。
一般来说我们将图片和视频等放在项目下,将其路径存入数据库。
除非图片和视频是具有机密性的,不能随便暴露的。
2.数据库的基本操作
1.mysql的注释符
# 注释一行
/*
* 注释多行
*/
2.mysql简介(关系型数据库管理系统)
MySQL是一个关系型数据库管理系统,由瑞典MySQL AB 公司开发,目前属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一,在 WEB 应用方面,MySQL是最好的 RDBMS (Relational Database Management System,关系数据库管理系统) 应用软件。
数据库(Database)是按照数据库结构来组织、存储和管理数据的仓库。
一个项目至少有一个数据库。
3.显示所有的数据库
Show databases;
在DOM界面也可以使用show databases查询所有的数据库
单纯显示是否存在数据库:
Show databases like ‘dbname’;
4. 创建数据库
Createa database 数据库名;
判断数据库是否存在:
SELECT * FROM information_schema.SCHEMATA where SCHEMA_NAME='dbname';
判断数据库存在,则删除:
drop database if exists dbname;
5. 删除数据库
Drop database 数据库名;
6. 显示所有的数据库
Show databases;
在DOM界面也可以使用show databases查询所有的数据库
单纯显示是否存在数据库:
Show databases like ‘dbname’;
7. 创建数据库
Createa database 数据库名;
判断数据库是否存在:
SELECT * FROM information_schema.SCHEMATA where SCHEMA_NAME='dbname';
判断数据库存在,则删除:
drop database if exists dbname;
8. 删除数据库
Drop database 数据库名;
3.数据库表操作
3.1创建表
表是数据库存储数据的基本单位。一个表包含若干字段或记录。
CREATE TABLE 表名(字段名 数据类型 [完整性约束条件],
字段名 数据类型 [完整性约束条件],
……
字段名 数据类型 [完整性约束条件]
)[engine = InnoDB Default Charset=utf8];
将表的时候可以选择添加或者不加[engine = InnoDB Default Charset=utf8];意思是设置mysql的引擎InnoDB 默认编号格式为utf8
完整性约束条件
1.直接写在字段名后面
Id int primary key auto_increment,
2.单独编写约束
uid int,
Constraint `fk` foreign key (`uid`) references `主表名`(`uid`)
自动增长(标识列):
AUTO_INCREMENT
3.2 查看表结构
查看基本表结构:describe 表名; 或 desc 表名;
查看表详细结构:show create table 表名;
4.查询数据
4.1单表查询
1.查询所有字段
Select * from 表名;
2.查询指定字段
Select 字段1,字段2,…… from 表名;
3.Where条件查询
Select 字段1,字段2,…… from 表名 where 条件表达式;
4.带IN关键字查询
Select 字段1,字段2,…… from 表名 where 字段 [not] in(值1,值2,……);
5.带between and的范围查询
Select 字段1,字段2,…… from 表名 where 字段 [not] between 值1 and 值2;
4.2 带like的模糊查询--只有_、%,其他的全没了
Select 字段1,字段2,…… from 表名 where 字段 [not] like ‘字符串’;
_代表单个字符
%代表任意字符
4.3 空值查询
Select 字段1,字段2,…… from 表名 where 字段 is [not] null
案例:select * from tb_ta where tname is not null 其中tname 为空
4.4 带and的多条件查询
Select 字段1,字段2,…… from 表名 where 条件表达式1 and 条件表达式2[…… and 条件表达式n]
4.5 带or的多条件查询
Select 字段1,字段2,…… from 表名 where 条件表达式1 or 条件表达式2[ ……or 条件表达式n]
4.6 Distinct去重复查询
Select Distinct 字段1 from 表名
4.7 对查询结果排序
Select 字段1,字段2,…… from 表名 order by 字段名 [asc|desc];
4.8 Group by 分组查询
Select 字段1,字段2,…… from 表名
Group by 字段名 [having 条件表达式][with rollup]
4.8.1 单独使用
4.8.2 与group_concat函数一起使用(将分组后的某列一起串起,比较常用)
4.8.3 与聚合函数一起使用
4.8.4 与having一起使用(限制输出的结果)
4.8.5 与with rollup一起使用(最后加入一个总和行)
4.8.6 Limit分页查询 Select 字段1,字段2,…… from 表名 limit 初始位置,记录数;
Select * from tb_a limit 5, 3;
4.2 使用聚合函数查询
4.2.1 COUNT()函数
统计记录的条数。常与GROUP BY一起使用,也可单独使用。
4.2.2 SUM()函数
求和。常与GROUP BY一起使用,也可单独使用。
4.2.3 AVG()函数
求平均值。常与GROUP BY一起使用,也可单独使用。
4.2.4 MAX()函数
求最大值。常与GROUP BY一起使用,也可单独使用。
4.2.5 MIN()函数
求最小值。常与GROUP BY一起使用,也可单独使用。
4.3 连接查询 -- 多表联查
连接查询是将两个或两个以上的表按照某个条件连接起来,从中选取需要的数据。
4.3.1. 内连接查询
内连接查询是一种最常见的连接查询。内连接查询可查询两个或两个以上的表共有的信息。
SELECT 字段列表 FROM 表名1,表名2 WHERE 表名1.字段名=表名2.字段名;
SELECT 字段列表 FROM 表名1 INNER JOIN 表名2 ON 表名1.字段名=表名2.字段名;
4.3.2. 外连接查询
SELECT 字段名列表 FROM 表名1 LEFT|RIGHT JOIN 表名2 ON 表名1.字段名=表名2.字段名;
4.3.2.1 左连接查询
可以查询出“表名1”的所有记录,而“表名2”中,只能查询出匹配的记录。
4.2.3.2 右连接查询
可以查询出“表名2”的所有记录,而“表名1”中,只能查询出匹配的记录。
4.4子查询
4.4.1 带比较运算符的子查询
子查询可以使用比较运算符。即子查询的结果做为父查询的条件,而该条件用比较运算符。
4.4.2 带IN关键字的子查询
子查询的结果返回多个时使用IN包含。
4.4.3 带EXISTS关键字的子查询
EXISTS表示是否存在,具有判断的意思。
子查询的结果返回一条或多条时,EXISTS子查询返回结果为TRUE;返之为FALSE。
[NOT EXISTS]
SELECT * FROM t_book WHERE EXISTS (SELECT * FROM t_booktype);
--如果t_booktype表存在,就查询t_book表。
4.4.4 带ANY关键字的子查询
表示满足其中任一条件。(找案例)
--查询价格便宜和贵的书(即满足其中一个就行)
SELECT * FROM t_book WHERE price >= ANY(SELECT price FROM t_pricelevel);
--SELECT price FROM t_pricelevel表示查询出所有的价格
-- WHERE price >= ANY() 表示满足所有价格中的任意一个都打印
4.5 合并查询结果
4.5.1 UNION
使用UNION关键字,数据库系统会将所有的查询结果合并到一起,然后去除掉相同的记录。
--合并两张表,相同的数据去除掉
SELECT id FROM t_book
UNION
SELECT id FROM t_booktype;
4.5.2 UNION ALL
使用UNION ALL,不会去除掉重复的记录。
5.索引
5.1索引引入
索引定义:索引是由数据库表中一列或多列组合而成,其作用是提高对表中数据的查询速度。
类似于图书的目录,方便快速定位,寻找指定的内容。
5.2索引的优缺点
优点:提高查询数据的速度(如果一次性要查询30%以上的数据时不推荐使用索引)。
缺点:创建和维护索引的时间增加了,同时占用硬盘空间。
5.3索引分类 -- 重点(可以提升查询速度,面试中遇到的问题也多)
5.3.1.普通索引:这类索引可以创建在任何数据类型中;
5.3.2.唯事索引:使用UNIQUE参数可以设置,在创建唯一索引时,限制该索引的值必须是唯一的。
5.3.3.全文索引:使用FULLTEXT参数可以设置,全文索引只能创建在CHAR,VARCHAR,TEXT类型的字段上。主要作用是提高查询较大字符串类型的速度;只有MyISAM引擎支持该索引,MySQL默认引擎不支持。(到innodb5.5的时候好像支持全部索引了)
5.3.4.单列索引:在表中可以给某个字段创建索引,单列索引可以是普通索引,也可以是唯一索引,还可以是全文索引。
5.3.5.多列索引:多列索引是在表的多个字段上创建一个索引。
5.3.6.空间索引:使用SPATIAL参数可以设置空间索引。空间索引只能建立在空间数据类型上, 这种可以提高系统获取空间数据的效率。
5.4创建索引
1.创建表时创建索引
Create table tb_index(
Iid int,
Index(id)
)ENGINE = InnoDB DEFAULT CHARSET = utf8;
2.创建普通索引
create table tb_a(
aid int,
aname varchar(210),
key aid(aid)
)ENGINE = InnoDB DEFAULT CHARSET = utf8;
3.创建唯一索引
Create table tb_test(
aid int,
aname varchar(210),
unique key aid(aid)
)ENGINE = InnoDB DEFAULT CHARSET = utf8;
4.创建全文索引
Create table tb_test(
Aid int,
Aname varchar(210),
FULLTEXT key aid(aid)
)ENGINE = MYISAM DEFAULT CHARSET = utf8;
5.创建单列索引
create table tb_a(
aid int,
aname varchar(210),
key index_aname(aname)
)ENGINE = InnoDB DEFAULT CHARSET = utf8;
6.创建多列索引
create table tb_a(
aid int,
aname varchar(210),
key index_aname(aname,aid)
)ENGINE = InnoDB DEFAULT CHARSET = utf8;
7.创建空间索引
create table tb_a(
Aid int,
aname varchar(210),
SPATLIAL key index_aname(aname,aid)
)ENGINE = MYISAM DEFAULT CHARSET = utf8;
5.2 在已经存在的表上创建索引
Alter table tb_test add index index_tid(tid)
修改表tb_test 添加一个index(索引)名为index_tid(添加到tid)
5.3 用ALTER TABLE语句来创建索引
ALTER TABLE 表名 ADD[UNIQUE | FULLTEXT | SPATIAL] INDEX
索引名(字段名 [(长度)][ASC | DESC]);
5.6删除索引
DROP INDEX 索引名 ON 表名;
6.视图
6.1. 视图的引入
6.1.1 视图是一种虚拟的表,是从数据库中一个或多个表中导出来的表。
6.1.2 数据库中存放了视图的定义,而并没有存放视图中的数据,这些数据存放在原来的表中。
6.1..3 使用视图查询数据时,数据库系统会从原来的表中取出对应的数据。
6.2 视图的作用
6.2.1 使操作简便化。
6.2.2 增加数据的安全性。
6.2.3 提高表的逻辑独立性。
6.3 创建视图
CREATE VIEW v1 AS SELECT * FROM t_book;
6.4 查看视图
6.4.1 DESCRIBE 语句查看视图基本信息
6.4.2 SHOW TABLE STATUS 语句查看视图基本信息
6.4.3 SHOW CREATE VIEW 语句查看视图详细信息
6.4.4 在views表中查看视图详细信息
6.5 修改视图
修改视图的定义。
1.CREATE OR REPLACE VIEW 语句修改视图
CREATE OR REPLACE [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE} ]
VIEW 视图名 [(字段清单)]
AS SELECT 语句
[WITH [CASCADED | LOCAL ] CHECK OPTION];
2.ALTER 语句修改视图
ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE} ]
VIEW 视图名 [(字段清单)]
AS SELECT 语句
[WITH [CASCADED | LOCAL ] CHECK OPTION];
第1种具有创建和修改功能,第2种只具有修改功能。
6.6 更新视图
更新视图是指通过视图来插入(INSERT)、更新(UPDATE)和删除(INSERT)表中的数据。因为视图是一个虚拟的表,其中没有数据。通过视图更新时,都是转换基本表来更新。更新视图时,只能更新权限范围内的数据。超出了范围,就不能更新。
与表操作是一样的,即将视图的操作变成了对基表的操作:
1.插入(INSERT)
INSERT INTO b1 VALUES(‘A’,’B’,’C’);
2.更新(UPDATE)
UPDATE b1 SET v=’X’ WHERE v=’A’;
3.删除(DELETE)
DELETE FROM b1 WHERE v=’X’;
6.7 删除视图
删除视图是指删除数据库中已存在的视图。
删除视图时,只能删除视图的定义,不会删除数据。
DROP VIEW [IF EXISTS] 视图名列表 [RESTRICT | CASCADE]
7.触发器
触发器是一个特殊的存储过程
触发程序是与表有关的命名数据库对象,当表上出现特定事件时,将激活该对象
7.1 创建触发器
CREATE TRIGGER trigger_name
trigger_time trigger_event
ON tbname
FOR EACH ROW
trigger_stmt
说明:
tbl_name必须引用永久性表。不能将触发程序与临时表表或视图关联起来。
trigger_time是触发程序的动作时间。它可以是BEFORE或AFTER,以指明触发程序是在激活它的语句之前或之后触发。
trigger_event指明了激活触发程序的语句的类型。trigger_event可以是下述值之一:
·INSERT:将新行插入表时激活触发程序,例如,通过INSERT、LOAD DATA和REPLACE语句。
·UPDATE:更改某一行时激活触发程序,例如,通过UPDATE语句。
·DELETE:从表中删除某一行时激活触发程序,例如,通过DELETE和REPLACE语句。
请注意,trigger_event与以表操作方式激活触发程序的SQL语句并不很类似,这点很重要。
创建一个单执行语句的触发器:
#创建表
CREATE TABLE account(
acct_num INT ,
amount DECIMAL(10,2)
);
#创建触发器
CREATE TRIGGER ins_sum
BEFORE INSERT
ON tbname
FOR EACH ROW
SET @SUM=@SUM+new.amount;
创建有多个执行语句的触发器,语法如下:
DELIMITER $$
CREATE TRIGGER testref
BEFORE INSERT
ON tbname
FOR EACH ROW BEGIN
INSERT INTO tbname SET a2 = NEW.a1;
DELETE FROM tbname WHERE a3 = NEW.a1;
UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
END $$
7.2查看触发器
SHOW TRIGGERS
在TRIGGERS 表中查看触发器信息:
information_schema数据库的TRIGGERS 表中,可以通过查询查看触发器信息
SELECT * FROM `information_schema`.`TRIGGERS` WHERE `TRIGGER_NAME`='ins_sum'
7.3删除触发器
案例:删除ins_sum触发器
DROP TRIGGER `school`.`ins_sum`
#删除对应的表也会删除该表的触发器
8.常用函数
8.1日期和时间函数
1.CURDATE() 返回当前日期;
2.CURTIME() 返回当前时间;
3.MONTH(d) 返回日期d中的月份值,范围是1~12;
4.DAY() 返回日期的天(日);
5.YEAR() 返回日期的年
6.MONTH() 返回日期的月
7.SYSDATE() 返回当前系统日期
8.DATEDIFF() 返回两个DATE值之间的天数
9.DATE_ADD() 将时间值添加到日期值
10.NOW() 返回当前日期和时间
11.TIMEDIFF() 计算两个TIME或DATETIME值之间的差值
12.WEEK() 返回一个日期的星期数值
8.2字符串函数
1.CHAR_LENGTH(s) 计算字符串s的字符数;
2.UPPER(s) 把所有字母变成大写字母;
3.LOWER(s) 把所有字母变成小写字母;
4.CONCAT() 将两个或多个字符串组成一个字符串;
5.LEFT() 获取指定长度的字符串的左边部分;
6.REPLACE() 搜索并替换字符串中的子字符串;
7.SUBSTRING() 从具有特定长度的位置开始的最一个子字符串;
8.TRIM() 从字符串中删除不需要的字符;
9.LPAD()/RPAD() 左填充与右填充
10.FIND_IN_SET(n, h) 在逗号分隔的字符串列表中找到一个字符串;
N是要查找的字符串;H是要搜索的逗号分隔的字符串列表
SELECT FIND_IN_SET(‘y’, ‘x,y,z’);
--返回:2 表示在第二个位置
SELECT FIND_IN_SET(‘a’, ‘x,y,z’);
--返回:0
11.FORMAT(n, d, locale) 格式化具有特定区域设置的数字,舍入到小数位数。
N是要格式化的数字;D是要舍入的小数位数;locale可选,用于确定千个分隔符和分隔符之间的分组(不常用)。
SELECT FROMAT(14500.2018, 2);
--返回:14,500.20
SELECT FROMAT(14500.2018, 0);
--返回:14,500
8.3数学函数
1.ABS(x) 求绝对值;
2.SQRT(x) 求平方根;
3.MOD(x,y) 求余。
8.4 加密函数
1.PASSWORD(str) 一般对用户的密码加密,不可逆;
2.MD5(str) 普通加密,不可逆;--在添加数据的时候MD5(data)
3.ENCODE(str, pswd str) 加密函数,结果是一个二进制数,必须使用BLOB类型 的字段来保存它。
4.DECODE(crypt_str, pswd_str) 解密函数。
8.5比较函数
1.Coalesce() 返回一个非NULL参数,适用于将值替换为NULL;
2.GREATEST() 使用N个参数,分别返回n个参数的最大值和最小值。
3.ISNULL() 如果参数为NULL,则返回1否则返回0。
8.6转换函数
1.CAST() 将任何类型的值转换为具有指定类型的值。
CAST(expression AS TYPE);
SELECT CAST(‘123’ AS INT);
9.存储过程和函数
优点(为什么要用存储过程?):
(1)将重复性很高的一些操作,封装到一个存储过程中,简化了对这些SQL的调用。
(2)批量处理:SQL+循环,减少流量,也就是“跑批”。
(3)统一接口,确保数据的安全。
相对于Oracle数据库来说,MySQL的存储过程相对功能较弱,使用较少。
9.1创建存储过程和函数
1.创建存储过程
CREATE PROCEDURE sp_name([proc_parameter[,……]])
[characteristic……] routine_body
Sp_name参数是存储过程的名称;
Proc_parameter表示存储过程的参数列表;
Characteristic参数指定存储过程的特性;
Routine_body参数是SQL代码的内容,可以是BEGIN……END来标志SQL代码的开始和结束。
示例:创建一个存储过程,删除给定球员参加的所有比赛
delimiter $$ #将语句的结束符号从分号;临时改为两个$$(可以是自定义)
CREATE PROCEDURE delete_matches(p_playerno INT)
BEGIN
DELETE FROM matches
WHERE playerno = p_playerno;
END$$
delimiter ; #将语句的结束符号恢复为分号
9.2调用存储过程
call delete_matches(57);
带IN的存储过程:
delimiter $$
create procedure in_param(in p_in int)
begin
select p_in;
set p_in=2;
select P_in;
end$$
delimiter ;
set @p_in=1;
call in_param(@p_in);
Select @p_in;
带OUT的存储过程:
delimiter //
create procedure out_param(out p_out int)
begin
select p_out;
set p_out=2;
select p_out;
end
//
delimiter ;
set @p_out=1;
call out_param(@p_out);
#因为out是向调用者输出参数,不接收输入的参数,所以存储过程里的p_out为null
select @p_out;
#调用了out_param存储过程,输出参数,改变了p_out变量的值
带INOUT的存储过程:
delimiter $$
create procedure inout_param(inout p_inout int)
begin
select p_inout;
set p_inout=2;
select p_inout;
end
$$
delimiter ;
set @p_inout=1;
call inout_param(@p_inout);
select @p_inout;
#调用了inout_param存储过程,接受了输入的参数,也输出参数,改变了变量
10 帐户管理
10.1 mysql命令的常用参数
-h:主机名或ip,默认是localhost,最好指定-h参数
-u:用户名
-p:密码,注意:该参数后面的字符串和-p不能有空格
-P:端口号,默认为3306
数据库名:可以在命令最后指定数据库名
-e:执行SQL语句,如果指定该参数,将在登录后执行-e后面的命令或sql语句并退出
10.2创建用户
CREATE USER user [IDENTIFIED BY [PASSWORD] 'password']
[, user [IDENTIFIED BY [PASSWORD] 'password']]
1.新建普通用户
CREATE USER 'jeffrey'@'localhost' identified BY 'mypass';
用户名部分为“jeffrey”,主机名默认为“%”(即对所有主机开放权限)
如果指定用户登录不需要密码,则可以省略identified BY部分
2.使用GRANT语句创建新用户
GRANT USER语句可以用来创建帐户,通过该语句可以在user表中添加一条新记录
比起CREATE USER语句创建的新用户,还需要使用GRANT语句赋予用户权限
使用GRANT语句创建新用户时必须有GRANT权限。
使用GRANT语句创建一个新用户testUser,密码为testpwd,并授予用户对所有数据表的SELECT和UPDATE权限
GRANT SELECT ,UPDATE ON *.* TO 'testUser'@'localhost' identified BY 'testpwd'
SELECT `Host` ,`User` ,`Select_priv` ,`Update_priv` FROM mysql.user WHERE `User` ='testUser';
10.3直接操作MYSQL用户表
不管是CREATE USER还是GRANT USER,在创建用户时,实际上都是在user表中添加一条新记录。
使用INSERT语句向mysql.user表INSERT一条记录来创建一个新用户
插入的时候必须要有INSERT权限
INSERT INTO mysql.user(host,user,password,[privilegelist])
VALUES ('host','username',password('password'),privilegevaluelist)
使用INSERT创建一个新用户,其用户名称为customer1,主机名为localhost,密码为customer1
INSERT INTO mysql.user(host,user,password)
VALUES ('localhost','customer1',password('customer1'))
10.4 删除普通用户
使用DROP USER语句删除用户,也可以直接通过DELETE从mysql.user表中删除对应的记录来删除用户
DROP USER语句用于删除一个或多个MYSQL帐户。要使用DROP USER,必须拥有MYSQL数据库的全局
CREATE USER 权限或DELETE权限。
删除testUser这个用户
DROP user 'testUser'@'localhost';
使用delete语句删除用户
DELETE FROM mysql.user WHERE `Host`='localhost' and `User`='testUser'
有疑问或优化操作可以添加QQ俩系:304808680
如果看这个不习惯的话可以去下载本人上传的文档。。之前本人是习惯性写文档,没有写博客的习惯,最近突然喜欢上写博客了
积分多的可以选择下载文档