文章目录
数据库操作(一)
1、创建数据库
//1、创建数据库
create database db;
//2、查看创建好的数据库的定义
show create database db;
//显示如下
+----------+---------------------------------------------------------------+
| Database | Create Database |
+----------+---------------------------------------------------------------+
| db | CREATE DATABASE `db` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+---------------------------------------------------------------+
2、删除数据库
//1、删除数据库 如果指定的数据库不存在,则删除错误;
DROP DATABASE db;
//1008 - Can't drop database 'db'; database doesn't exist
3、数据库引擎
MySQL提供了多个不同的存储引擎,包括处理事务安全表的引擎和处理非事务安全表的引擎。在MySQL中,不需要再整个服务器中使用同一种存储引擎,针对具体要求,可以对每一个表使用不同的存储引擎。
MySQL 5.7支持的存储引擎有:InnoDB,MyISAM,Memory,Merge,Archive,Federated,CSV,BLACKHOLE等。
SHOW ENGINES; //显示引擎
3.1 InnoDB引擎
InnoDB是事务型数据库首选引擎,支持事务安全表(ACID),支持行锁定和外键,5.5版本之后默认引擎为InnoDB。
3.2 MyISAM引擎
MyISAM 基于ISAM的存储引擎,并对其进行了扩展。它是在Web、数据存储和其他应用环境下最常用的存储引擎之一。MyISAM拥有较高的插入、查询速度,但不支持事务。在MySQL5.5之前,MyISAM是默认引擎。
3.3 MEMORY引擎
MEMORY存储引擎将表中的数据存储到内存中,为查询和引用其他表数据提供快速访问。
3.4总结
如果要提供提交、回滚和崩溃恢复能力的事务安全(ACID兼容)能力,并要求实现并发控制,InnoDB是个很好的选择。如果数据表主要用于插入和查询记录,则MyISAM引擎能提供较高的处理效率;如果只是临时存储数据,数据量不大,并且不需要较高的数据安全性,可以选择将数据保存在内存中的MEMORY引擎中,MySQL中使用该引擎作为临时表。
4 数据表
4.1 创建表的种方式
4.1.1 创建表标明主键和自增
//1
CREATE TABLE `test`(
`id` int(16) not null auto_increment primary key,
`name` varchar(25)
);
//2
CREATE TABLE test(
id int(16) not null auto_increment,
name varchar(25),
primary key (id)
);
4.1.2 创建表使用外键约束
子表的外键必须关联父表的主键,且关联字段的数据
mysql> create table test_dept(
-> id int primary key,
-> name varchar(25),
-> deptId int,
-> constraint fk_test foreign key(deptId) references test(id)
-> );
4.1.3创建表使用唯一性约束
唯一性约束(Unique Constraint) 要求该列唯一
Unique 和 Primary 的区别: 一个表中可以有多个字段声明为Unique,但只能有一个Primary key的声明,声明为Primary key的列不允许有空值,但是声明为Unique的字段运行存在空值(NULL)。
CREATE TABLE test_unique(
id int(16) primary key,
name varchar(25) unique,
);
//或者
CREATE TABLE test_unique(
id int(16) primary key,
name varchar(25) ,
constraint c_name unique(name)
);
4.1.4创建表使用默认约束
默认约束(Default Constraint)指定某列的默认值。如果男性同学较多,性别就可以默认为‘男’。如果插入一条新的记录时没有为这个字段赋值,那么系统会自动为这个字段赋值为‘男’
CREATE TABLE test_default(
id int(16) primary key,
name varchar(25) default 'hu'
);
4.2 查看数据表结构
4.2.1 Describe 语句
Describe test_default;
//简写
desc test_default;
4.2.2查看表详细结构语句 show create table
show create table test_default;
4.3修改数据表
修改已存在的数据表结构,MySQL中使用关键词 alter table。
4.3.1 修改表名
//alter table <旧表名> rename to <新表名>;
alter table test_default rename to test_default_new;
4.3.2修改字段的数据类型
//alter table <表名> modify <字段名> <新数据类型>;
alter table test_default_new modify name varchar(255);
4.3.3修改字段的名
//alter table <表名> change <旧字段名> <新字段名> <新数据类型>;
alter table test_default_new change name namenew varchar(255);
4.3.4 添加字段
//alter table <表名> add <新字段名> <新数据类型>;
alter table test_default_new add nameadd varchar(255) unique;
//如果想在表的第一列添加 使用关键字first
alter table test_default_new add nameaddfirst varchar(255) first;
4.3.5 删除字段
//alter table <表名> drop <新字段名> ;
alter table test_default_new drop nameadd ;
4.3.6 更换表的存储引擎
//alter table <表名> engine = <新引擎名> ;
alter table test_default_new engine = MyISAM;
4.3.7 删除表的外键约束
//alter table <表名> drop foreign key <外键约束名> ;
alter table test_dept drop foreign key fk_test ;
4.3.8 添加约束
//添加外键约束
// alter table <表名> add constraint <外键约束名> foreign key(<字段名>) refrences <表名>(<字段名>)
alter table test_dept add constraint `fk_test` foreign key(deptId) references test(id) ;
4.4 删除表
4.4.1 删除没有关联的表
在MySQL中,使用Drop table 可以一次性删除一个或多个没有被其他表关联的数据表。
//drop table [if exists]表1,表2...
drop table if exists test;
4.4.2 删除被其他表关联的主表
数据表之间存在外键关联的情况下,如果直接删除父表,结果会显示失败。原因是直接删除,将破坏表的参照完整性。如果必须要删除,可以先删除与它关联的字表,再删除父表,只是这样同事删除了两个表中的数据。但有时需要保留子表,只需将关联的外键约束取消,然后删除父表;
4.5 案例
4.5.1创建office和employees表
office表结构
字段名 | 数据类型 | 主键 | 外键 | 非空 | 唯一 | 自增 |
---|---|---|---|---|---|---|
officeCode | int(10) | 是 | 否 | 是 | 是 | 否 |
city | varchar(50) | 否 | 否 | 是 | 否 | 否 |
address | varchar(50) | 否 | 否 | 否 | 否 | 否 |
country | varchar(50) | 否 | 否 | 是 | 否 | 否 |
postalCode | varchar(50) | 否 | 否 | 否 | 是 | 否 |
create table if not exists offices(
officeCode int(10) primary key not null unique,
city varchar(50) not null,
address varchar(50) ,
country varchar(50) not null,
postalCode varchar(15) unique)Engine = InnoDB default charset=utf8;
employees表结构
字段名 | 数据类型 | 主键 | 外键 | 非空 | 唯一 | 自增 |
---|---|---|---|---|---|---|
employeeNumber | int(11) | 是 | 否 | 是 | 是 | 是 |
lastName | varchar(50) | 否 | 否 | 是 | 否 | 否 |
firstName | varchar(50) | 否 | 否 | 是 | 否 | 否 |
mobile | varchar(25) | 否 | 否 | 否 | 是 | 否 |
officeCode | int(10) | 否 | 是 | 是 | 否 | 否 |
jobTitle | varchar(50) | 否 | 否 | 是 | 否 | 否 |
birth | datetime | 否 | 否 | 是 | 否 | 否 |
note | varchar(255) | 否 | 否 | 否 | 否 | 否 |
sex | varchar(5) | 否 | 否 | 否 | 否 | 否 |
create table if not exists employees(
employeeNumber int(11) primary key not null unique auto_increment,
lastName varchar(50) not null,
firstName varchar(50) not null,
mobile varchar(25) unique,
officeCode int(10) not null,
jobTitle varchar(50) not null,
birth datetime not null,
note varchar(255) ,
sex varchar(5) ,
constraint fk_offices foreign key(officeCode) references offices(officeCode)
)Engine = InnoDB default charset=utf8;
4.5.2修改表内容
//修改employees表中的birth字段为employee_birth
alter table employees change birth employee_birth datetime not null;
//修改sex字段的数据类型为varchar(1)
alter table employees modify sex varchar(1);
//删除字段note
alter table employees drop note;
//添加字段favorite_activity
alter table employees add favorite_activity varchar(100) after sex;
//删除外键
alter table employees drop foreign key fk_offices;
//修改引擎
alter table employees engine==myisam; //(需先删除外键,才能执行 delete or update)
//显示表的详细信息 除了desc 还可以
show create table employees;
5 MySQL数据类型和运算符
5.1数据类型
5.2 运算
5.2.1算术运算符
//创建运算表
create table temp(num int);
//插入值
insert int temp value (64);
//进行算术运算 + - * / %
select num , num + 1 ,num - 1, num * 10, num / 2,num % 2 from temp;
//如果运算没有意义 返回值为null;
select num / 0 from temp;
5.2.2 比较运算符
比较运算符
运算符 | 作用 |
---|---|
= | 等于(不能用于判断null null=null返回null) |
<=> | 安全等于(可用于比较null,两个null<=>null返回1) |
<>(!=) | 不等于(不能用于判断null null<>null返回null) |
<= | 小于等于(不能用于判断null null<=null返回null) |
>= | 大于等于(不能用于判断null null>=null返回null) |
> | 大于(不能用于判断null null>null返回null) |
is null | 是null |
is not null | 不是null |
least | 存在有两个或多个参数时返回最小值 |
greatest | 存在有两个或多个参数时返回最大值 |
between and | 判断一个值是否落在两个值之间 |
isnull | 于 is null相同 |
in | 判断一个值是in列表中的任意一个值 |
not in | 判断一个值不是in列表中的任意一个值 |
like | 通配符匹配 |
regexp | 正则表达式匹配 |
// num 等于 1 大于 1 小于 1 不等于1
select num = 1,num>1,num<1,num<>1 from temp;
//between and
select 2 between 1 and 66;
//least: least (值1,值2,值3....)
select least(1,2,3,4,5),least(4,2,3,4,5);
//greatest(v1,v2,v3...)
select greatest(1,2,3,4,5),greatest(4,2,3,4,5);
// in 和 not in
select 2 in (1,2,3,4), 4 not in (1,2);
//like
//'%' 匹配任何数目的字符,甚至包括零字符;
//'_' 只能匹配一个字符
select 'stud' like '%s%';
//正则表达式: regexp :
// '^'匹配以该字符后面的字符开头的字符串
// '$'匹配以该字符后面的字符结尾的字符串
// '.'匹配任何一个单字符
// '[...]'匹配在方括号内的任何字符 例如 [abc]匹配a、b或c
// '*'匹配零个或多个在它前面的字符 例如 x* 匹配 xxxx xx x
select 'ssky' regexp '^s';
select 'ssky' regexp '$y';
select 'ssky' regexp '.*';
select 'ssky' regexp '[a-z]*';
5.2.3 逻辑运算符
运算符 | 作用 |
---|---|
not 或者 ! | 逻辑非 |
and 或者 && | 逻辑与 |
or 或者 || | 逻辑或 |
XOR | 逻辑异或 |
select not 1>0, 1>0 and 2>0, 1<0 or 2>0, 1>0 xor 2>0;
5.2.4 位运算符
运算符 | 作用 |
---|---|
| | 位或 |
& | 位与 |
^ | 位异或 |
<< | 位左移 |
>> | 位右移 |
~ | 位取反,反转左右比特 |
select 1|1 ,1&1, 1^1, 1<<1,1>>1,~1;
6 MySQL 函数
6.1 数学函数
函数名 | 作用 |
---|---|
abs(x) | x绝对值 |
pi() | 返回 pi 默认 6位小数 |
sqrt(x) | 二次方根 |
mod(x,y) | x对y取余 |
ceil(x) | 返回不小于x的最小整数,返回值返回一个bigint |
ceiling(x) | 返回不小于x的最小整数 |
floor(x) | 返回不大于x的最大整数值,返回值返回一个bigint |
rand() | rand 返回一个0-1范围的随机数 |
rand(x) | x为种子,作用在同一个种子下的数据数相同 |
round(x) | 返回最接近于参数x的整数,对x进行四舍五入 |
round(x,y) | 返回接近于x的数,其保留到小数的y位,若y为负数,则保留x值到小数点左边y位 |
truncate(x,y) | 对x进行截取操作,操作结果保留小数点后面指定y位 |
sign(x) | 符号函数 |
pow(x,y),power(x,y),exp(x) | |
log(x),log10(x) | |
radians(x),degrees(x) | |
sin(X),asin(x),cos(x),acos(x) |
6.2 字符串函数
字符串函数主要用来处理数据库中的字符串数据。
函数名 | 作用 |
---|---|
char_length(str) | 返回字符数 |
concat(s1,s2,…) | 合并字符串,如果有任意一个字符串为null 则结果返回null |
concat_ws(wstr,s1,s2,…) | 合并字符串通过wstr字符串进行连接,如果有任意一个字符串为null 则结果返回null |
insert(s1,x,len,s2) | s1字符串 x位置到x+len位置被s2替换 |
lower(str),lcase(str) | 返回全部小写字符串 |
upper(str),ucase(str) | 返回全部大写字符串 |
6.3日期与时间函数
函数名 | 作用 |
---|---|
curdate(),current_date() | 'yyyy-mm-dd’返回日期 |
curtime(),current_time() | 'hh:mm:ss’返回时间 |
current_timestamp(),localtime(),now(),sysdate() | 当前日期和时间’yyyy-mm-dd’+‘hh:mm:ss’ |
unix_timestamp(date) | 时间戳函数 到秒级 |
from_unixtime(timestamp) | 时间戳转换为普通时间格式 |
dayname(date) | 返回 Wednesday。。。 |
dayofweek(date) | 返回 1表示周日 2周一 |
6.4 条件函数
- if (expr , v1 ,v2), 如果表达式 是true 则if 的返回值是 v1 否则是v2;
- ifnull(v1,v2) 如果v1是null 返回v2 如果v1,v2都不是null 返回v1;
- case expr when v1 then r1 [when v2 then r2] [else rn] end; //类似switch
- case 2 when 1 then ‘one’ when 2 then ‘two’ else ‘more’ end;
- case when 1< 0 then ‘true’ else ‘false’ end;
6.5系统信息函数
- version() 查看版本
- connection_id() 查看链接数
- show processlist; 查看当前用户的链接信息
6.6 加/解密函数
- 加密
- password(str) 加密不可逆
- md5(str)
- encode(pwd,str)
- 解密
- decode(encode(pwd,str),str)
7 查询数据
7.1基本查询
select [字段1 ,字段2 ,…] from [表或视图] where [查询条件]
7.2 单表查询
select * from temp;
select * from temp where num = 64;
//in 关键字
select * from temp where num in(1,9,64) order by num desc;
//between and 关键字
select * from temp where num between 1 and 63 order by num desc;
// like 关键字
select * from temp where num like '%6%' order by num desc;
// 查询null值
select * from temp where num is null
// and 多条件查询
select * from temp where num>1 and num>2 order by num desc;
// or 多条件查询
select * from temp where num>1 or num>2 order by num desc;
// distinct 消除重复
select distinct * from temp where num>1 or num>2 order by num desc;
// 多列排序 先进行num排序 再进行n排序
select * from temp where num>1 or num>2 order by num,n asc;
// 分组查询 [group by 字段] [having <条件表达式>]
select num ,count(*) as total from temp group by num;
+-----+-------+
| num | total |
+-----+-------+
| 6 | 2 |
| 64 | 1 |
+-----+-------+
select num ,group_concat(n) as n from temp group by num;
+-----+-----+
| num | n |
+-----+-----+
| 6 | 3,2 |
| 64 | 1 |
+-----+-----+
//having 过滤分组
select num ,group_concat(n) as n from temp group by num having count(n)>1;
//limit 限制查询结果
select num ,group_concat(n) as n from temp group by num having count(n)>0 limit 2;
//聚合函数查询 avg()平均数 count() 总行数 max()最大值 min()最小值 sum()某列求和
select count(*) as count, max(n) as max, min(num) as min ,sum(n) as sum from temp;
+-------+-----+-----+-----+
| count | max | min | sum |
+-------+-----+-----+-----+
| 3 | 3 | 6 | 6 |
+-------+-----+-----+-----+
7.2 连接查询
连接是关系数据库模型的主要特点,连接查询是关系数据中最主要的查询,主要包括内连接和外连接等,通过连接运算符可以实现多个表的查询。
7.2.1内连接查询
//普通方法
select temp.num , temp1.n from temp , temp1 where temp.num = temp1.num;
// inner join 语法
select temp.num , temp1.n from temp inner join temp1 on temp.num = temp1.num;
//as 使用表引用
select t.num , t1.n from temp as t inner join temp1 as t1 on t.num = t1.num;
7.2.2 外连接查询
外连接查询将查询多个表中相关联的行,内连接时,返回查询结果集合中的仅是符合查询条件和连接条件的行。但是有时候需要包含没有关联的行中数据,即返回查询结果集合中的不仅包含符合连接条件的行,而且还包括左表(左外连接或左连接)、右表(右外连接或右连接)或两个连接表(全外连接)中的所有行
- 左连接 left join : 返回包括左表中的所有记录和右表中连接字段相等的记录
- 右连接 right join : 返回包括右表中的所有记录和左表中连接字段相等的记录
//左外连接
select t.num , t1.n from temp as t left outer join temp1 as t1 on t.num = t1.num;
//右外连接
select t.num , t1.n from temp as t right outer join temp1 as t1 on t.num = t1.num;
7.3 子查询
子查询指的时一个查询语句嵌套在另一个查询语句内部的查询。
//any 关键字 只要大于任意一个子查询的结果 即为true
select * from temp where num> any(select num from temp1);
//all 关键字 需要大于子查询的所有结果才为 true
select * from temp where num> all(select num from temp1);
// exists
select * from temp where exists (select * from temp where n >6566)
7.4 合并查询
利用Union关键字 可以给出多条Select 语句,并将它们的结果组合成单个结果集。
//union 需要列数,和数据格式完全一致 否则报错 union [all] 加all是不去重 不加all会去重
select * from temp union all select * from temp1;
7.5 为表和字段设别名
//使用关键字 as 或者不用
select * from temp as tp;
7.6 使用正则表达式查询
// ^ 匹配特定字符或字符串开头的文本
select * from temp where num regexp '^6';
// $ 匹配特定字符或字符串结尾的文本
select * from temp where num regexp '6$';
// . 匹配任意一个字符
select * from temp where num regexp '6.';
// * 和 + 匹配多个字符
select * from temp where num regexp '6*'; ##*表示可以匹配 0次以上 包含0次
select * from temp where num regexp '6+'; ##+表示可以匹配 1次以上 包含1次
//匹配特定字符串 包含该字符则会返回
select * from temp where num regexp '6|3';
//匹配指定字符中的任意一个
select * from temp where num regexp '[6,3]';
select * from temp where num regexp '[a-z]';
select * from temp where num regexp '[0-9]';
//使用{n,} 或者 {n,m} 来指定字符连续出现的次数 {n,}表示至少n次 {n,m}表示至少n次自多m次
select * from temp where num regexp '6{1,}';
8 插入、更新、删除数据
8.1 插入数据
// insert into <表名>[(字段1,字段2...)] values (<值1>,<值2>)[(<值1>,<值2>)...];
insert into temp values (1,2),(2,3),(4,5);
//将查询的结果插入到表中
// insert into <表名> [<字段名>] select [<字段名>] from <表名> where <约束条件>
insert into temp1 select * from temp where temp.num = temp.n;
8.2 更新数据
// update <表名> set <字段1> = <值1>,<字段2> = <值2> where <约束条件>
update temp set num = 100 ,n = 100 where num =1;
8. 3 删除数据
// delete from <表名> where <约束条件>
delete from temp where num = 100;
9 索引
索引用于快速找出在某列中有一特定的行,不用索引,MySQL必须从第一条记录开始读完整个表,知道找到相关的行。表越大,查询数据所花费的事件越多。
9.1索引简介
索引是一个单独的、存储在磁盘上的数据库结构,它们包含着对数据表里所有记录的引用指针。使用索引用于快速查找出某个或多个列中有一定特征值得行,所有MySQL列类型都可以被索引,对相关列使用索引是提高查询操作速度的最佳途径。
索引是在存储引擎中实现的,因此没种存储引擎的索引都不一定完全相同,并且每种存储引擎也不一定支持所有索引类型。根据存储引擎定义每个表的最大索引数和最大索引长度。所有存储引擎支持每个表至少16个索引,总索引长度至少为256个字节。大多是存储引擎有更高的限制。MySQL中索引的存储类型有两种,BTREE和HASH,具体和表的存储引擎相关。MyISAM 和 InnoDB 存储引擎只支持BTREE索引; MEMORY和HEAP存储引擎可以支持BTREE和HASH。
索引的优点:
- 通过创建唯一索引,可以保证数据库表中每一行数据的唯一性。
- 可以大大加快数据的查询速度。
- 在实现数据的参考完整性方面,可以加速表和表之间的链接。
- 在使用分组和排序子句进行数据查询时,也可以显著减少查询中分组和排序的事时间。
索引的缺点
- 创建索引和维护索引需要耗费时间,并且随着数据量的增加所耗费的时间也会增加。
- 索引需要占磁盘空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果有大量的索引,索引文件可能比数据文件更快达到最大文件尺寸。
- 当对表中的数据进行,增、删、改的时候,索引也需要动态维护,这样降低了数据的维护速度
索引的分类
- 普通索引和唯一索引
- 普通索引是MySQL中基本的索引类型,运行在定义索引的列中插入重复值和空值。
- 唯一索引,索引列的值必须唯一,但运行有空值。
- 主键索引,是一种特殊的唯一索引,不运行有空值。
- 单列索引和组合索引
- 单列索引即一个索引只包含单个列,一个表可以有多个单列索引。
- 组合索引指在表的多个字段组合上创建索引,只有在查询条件中使用了这些字段的左边字段时,索引才会被使用。使用组合索引时遵循最左前缀集合。
- 全文索引
- 全文索引类型为FULLTEXT ,在索引的列上支持值得全文查找,运行在这些索引列中插入重复值=和空值。全文索引可以在 char varchar 或者 text类型上创建,MySQL中只有MyISAM支持全文索引。
- 空间索引
- 空间索引时对空间数据类型的字段建立索引,MySQL中的空间数据类型有4中,分别是GEOMETRY,POINT,LINESTRING,POLYGON。MySQL使用SPATIAL关键字进行扩展,是的能够用于创建正规索引类似的语法创建空间索引。创建空间索引的列必须声明为not null且空间索引只能在存储引擎MyISAM的表中创建。
索引的设计原则
- 索引并非越多越好
- 避免对经常更新的表进行过多的索引
- 数据量小的表最好不要使用索引
- 在条件表达式中经常用到的不同值较多的列上建立索引,在不同值很少的列上不要建立索引
- 当唯一索引是某种数据本身的特征时,指定唯一索引。使用唯一索引需确保定义的列的数据完整性,以提高查询速度。
- 在频繁进行排序或分组(group by 或 order by)的列上建立索引,如果待排序的列有多个,可以在这些列上建立组合索引。
9.2 创建普通索引
//在创建表的时候建立索引
// create table <table_name> [col_name data_type] [unique|fulltext|spatial][index|key][index_name](col_name [length])[asc|desc]
//1.创建普通索引
drop table if exists index_temp;
create table index_temp (
id int not null ,
name varchar(255) not null,
info int not null primary key auto_increment,
index (name)
)engine = innodb charset = utf8;
//插入数据
drop procedure if exists insert_data;
delimiter //
create procedure insert_data()
begin
declare n int default 0;
while n<1000000 do
insert into index_temp(id,name) values( n,concat('测试数据:',n));
set n = n+1;
end while;
end
//
call insert_data;
//使用关键字 explain 来分析索引是否建立
explain select * from index_temp where name = '测试数据:9999';
explain select * from index_temp where id = 9999;
explain select * from index_temp where info = 9999;
//
select * from index_temp where name = '测试数据:9999';
select * from index_temp where id = 9999;
select * from index_temp where info = 9999;
- select_type : simple 表示简单查询,即不适用union 或子查询。
- table : table名字
- type : 指定了本数据表与其他数据表之间的关联关系,可能的取值为system,const,eq_ref,ref,range,index,all;
- possible_keys: 给出了MySQL在搜索时可选的各个索引;
- key:表示MySQL实际选用的索引;
- key_len: 给出索引字节计算长度,key_len数值越小,表示越快;
- ref:给出了关联关系中另一个数据表里的数据列的名字;
- rows:是MySQL在执行这个查询时预计会从这个数据表里读出的数据行的个数;
- extra:行提供了与关联操作有关的信息;
9.3 创建其他索引
//1 、 unique 唯一索引,索引键值不能重复
create table temp_unique_index(
id int not null,
name char(30) not null,
unique index uniqidx(id)
)engine = innodb default charset = utf8;
// 2、 单列索引 索引长度20
create table temp_single_index(
id int not null,
name char(30) not null,
index singledx(name(20))
)engine = innodb default charset = utf8;
// 3、 组合索引 组合索引遵从最左前缀 , 搜索id ,或者id ,name 或者 id ,name ,age 时 会使用索引,其他不能
create table temp_multi_index(
id int not null,
name char(30) not null,
age int not null,
index multidx(id,name(20),age)
)engine = innodb default charset = utf8;
select * from temp_multi_index where id = 1;
select * from temp_multi_index where id = 1 and name = '111';
select * from temp_multi_index where id = 1 and name = '111' and age =15;
//3 、全文索引 fulltext 只有MyISAM引擎支持,并且只能为char varchar text创建全文索引
create table temp_fulltext_index(
id int not null,
name char(30) not null,
age int not null,
fulltext index fulltextidx(name)
)engine = myisam default charset = utf8;
9.4 添加索引
在已经存在的表中创建索引,可以使用 alter table 语句或者 create index 语句。
// alter table <表名> add index <索引名>(列名);
alter table temp add index name(name);
// unique index :alter table <表名> add unique index <索引名>(列名);
// create index <索引名> on <表名>(列名);
// create unique index <索引名> on <表名>(列名);
9.5 删除索引
// alter table <表名> drop index <索引名>;
alter table index_temp drop index name;
// drop index <索引名> on <表名>;
10 存储过程和函数
10.1 简介
存储程序可以分为存储过程和函数,MySQL中创建存储过程和函数使用的语句分别是: create procedure和create function。使用call语句来调用存储过程,只能用输出变量返回值。函数可以从语句外调用(即通过引用函数名),也能返回标量值。存储过程也可以调用其他存储过程。
10.2 创建存储过程
// delimiter 设置 以什么符号结尾 这里设置以//为结尾。后面还原为;
delimiter //
create procedure mypro()
begin
select * from temp ;
end //
delimiter ;
// 创建带参数的存储过程
//参数分为 三种 in out inout;
// in 参数的值必须在调用存储过程时指定,在存储过程中修改参数的值不能被返回;
// out 该值可在存储过程被改变,并可以返回
// inout 调用时指定 并且可被改变和返回;
delimiter //
create procedure bypin(in n int)
begin
declare i int ;
declare sum int ;
set i = 1;
set sum = 1;
while i<n do
set sum = sum +i;
set i = i+1;
end while;
select sum;
end //
delimiter ;
10.3 创建存储函数
create function f()
returns int
return (select * from temp where n = 1);