MySQL学习笔记

创建

create procedure create function

■■■存储过程

create procedure pName([pParameter])[characteristics...]routineBody;

create procedure 过程名([参数])[特性...]过程体;

参数：[in|out|inout]pParameter输入、输出、可入可出

特性：characteristics

示例

delimiter//将MySQL结束符设置为//，保证procedure的完整性，完事后，再用delimiter；将结束符设置为默认值分号；delimiter也可以指定其他符号作为结束符；

delimiter应该避免使用反斜杠\\,因为\\是SQL的转义字符；

■■■存储函数

create function funcName([parameter]) returns type [characteristic...]routineBody

create function funcName([参数]) returns type [特性]函数体

如果定义的返回值类型与实际返回值类型不同，系统会自动转型；

只能对procedure指定参数类型in/out/inout;

不能对function指定参数类型in/out/inout，function参数默认in;

变量

■■■变量

声明语句：declare varName[,varName2]...dataType[default value];

declare  变量名 dataType[default 默认值];

declear count int default 0;

默认值可以指定为一个常量，也可以指定为一个表达式；

如果不指定默认值，初始值就是null；

赋值语句：set varName=expr[,varName2=expr2]...;

declare a,b,c int;set a=1,b=2;set c=a+b;

select colName[,...]into varName[,...] tableExpr;

异常

■■■异常条件：将一个名字和一个错误条件相关联

意义：保证过程/函数在遇到警告或错误时能够继续运行，避免程序异常停止；

declare conditionName condition for [conditionType]

l [conditionType]:sqlstate[value]sqlstateValue|mysqlErrorCode

l conditionName 条件名称conditionType条件类型

l sqlstateValue错误代码-长度为5的字符串

l mysqlErrorCode错误代码-数值类型

declare command_not_allowed condition for sqlstate’42000’;

declare command_not_allowed condition for 1148;

■■■定义异常处理程序：

declare handlerType handler for conditionValue[,...] spStatement

handlerType:continue|exit|undo

conditionValue:sqlstate[value]sqlstateValue

|conditionName|sqlWarning|notFound|sqlException|mySqlErrorCode

handlerType错误处理方式，参数可取3个值continue|exit|undo

l continue表示不处理错误，继续执行|

l exit表示遇到错误马上推出|

l undo表示遇到错误后，撤回之前的操作，MySQL中断暂时不支持这样的操作；

conditionValue表示错误类型，可以取值如下：

l sqlstate[value]sqlstateValue包含5个字符的错误字符串；

l conditionName表示declare condition定义的错误条件名称；

l sqlWarning匹配所有以01开头的sqlstate错误代码；

l notFound匹配所有以02开头的sqlstate错误代码；

l sqlException匹配所有未被sqlWarning和notFound匹配的错误代码；

l mySqlErrorCode匹配数值型错误代码；

①　捕获字符串错误码sqlStateValue

declare continue handler for sqlstate’42s02’set@info=’no_such_table’;

②　捕获数字错误码mysqlErrorCode

declare continue handler for 1146 set@info’no_such_table’;

③　捕获自定义错误码

declare no_such_table conditon for 1146;

delare continu handler for no_such_table set@info=’no_such_table’;

④　捕获警告错误码sqlWarning

declare exit handler for sqlwarning set@info=’error’;

⑤　捕获未找到错误码notFound

declare exit handler for not found set@info=’no_such_table’;

⑥　捕获数据库异常错误码sqlException

declare exit handler for sqlexception set @info=’error’;

光标

光标：用来逐条读取大量数据

顺序：声明条件和变量→声明光标→声明处理程序

顺序：声明declare→打开open→使用fetch→关闭close

declare cursorName cursor for selectStatement

declare cursorFruit sursor for select name,price from fruits;

open cursorName;open cursorFruit;

fetch cursorName into varName[,varName2...]

fetch cursorFruit into name ,price;

close cursorName;close cursorFruit;

流程

流程控制：条件转移语句

if,case,loop,while,leave,iterate,repeat,,,

每个流程控制，可以包含单个语句，可以包含复合语句（begin end），可以嵌套；

leave用来退出任何流程控制构造；leave label;

iterate用来跳转到开头处；可以放在loop,while,repeate内，表示再次循环；iterate label;

until后面是循环结束的条件，如果条件为真，则结束循环，条件为假，继续循环；

while后面是进行循环的条件，条件为真，继续循环，条件为假，结束循环；

调用

调用存储过程

call pName([parameter[,...]]);

call CountProc(101,@num);

select CountFunc(101);

查看

查看存储过程和函数状态

show {procedure|function}statuss [like’pattern’]

show procedure status like ‘c%’\\g;

show create {procedure|function}name;

show create function test.CountFunc \\g;

select*from information_schema.routines where routine_name=’sp_name’;

select*from information_schema.routines where routine_name=’countFunc’ and routine_type=’function’\\g’;

修改

修改存储过程和函数

alter {procedure|function} name [characteristic...]

alter procedure CountProc modifies sql data sql security invoker;

alter function countFunc reads sql data comment ‘find name’;

删除

删除存储过程和函数

drop {procedure|function} [if exists] name;

drop prodedure countProc;

drop function countFunc;

创建索引

一、创建表的同时创建索引

create table name [colName dataType] [unique|fulltext|spatial] [index|key] [indexName](colName[length]) [asc|desc]

create table teacher (id int(11),name varchar(25),salary int(11),index(id));普通索引

create table t1(id int(11),name varchar(25),unique index myindex(id));唯一索引

create table t2(id int(11) not null,name varchar(30) not null,

index singleIndex(name(20)));单列索引，只能在string类型上创建，并且索引长度不得大于字段长度；

 create table t3(id int(11) not null,name varchar(30)not null,index(id,name(30)));

多列索引；索引长度不能超过string数据长度；

全文索引，只支持MyISAM引擎，创建表时，必须显示指明引擎类型，否则报错；

create table t4(id int not null,name char(30) not null,fulltext index(name));×

ERROR 1214 (HY000): The used table type doesn\'t support FULLTEXT indexes

create table t4(id int not null,name char(30) not null,fulltext index(name))engine=myisam;√

create table t5(g geometry not null ,spatial index myindex (g))engine=myisam ;

二、在已经存在的表上创建索引

■■使用alter table创建

alter table name add [unique|fulltext|spatial] [index|key] [indexName](colName[length],...)[asc|desc]

alter table student add index myIndex(name(10));

索引的查看方式：

l show index from tableName;查看表中索引的详细信息；（这里不能用show key）

l show create table tableName;查看创建表信息，包含创建索引信息；

l explain select *from student where name=\'cat\';可以查看该筛选所用的索引；

alter table student add unique key(age);

创建唯一索引，要保证该列的数据唯一性，否则报错；

ERROR 1062 (23000): Duplicate entry \'22\' for key \'age\'

关键字key和index是等价的；

alter table student add key multiKey(name,age);

alter table t1 engine=myisam;先修改引擎为MyISAM，然后再添加fulltext索引；

alter table t1 add fulltext key(name);

■■用create index创建

create [unique|fulltext|spatial] index indexName on tableName(colName[lenght],...) [asc|desc]

create index idIndex on t2(id);普通索引

create unique index nameIndex on t2(name);唯一索引

create index multiIndex on t2(id,name);多列索引

alter table t3 engine=myisam;修改引擎后，再创建全文索引；

create index fullIndex on t3(name);

删除索引

■用alter table删除索引

alter table tableName drop index indexName;编辑表 删除索引；

show create table tableName;首先查看包含的索引名称，然后再针对性删除；

 alter table t3 drop index fullIndex;

■用drop index删除索引

drop index indexName on tableName;删除某个表上的某个索引；

drop index name on t3;

在删除表中的列时，该列也会从索引中删除；

如果索引中的列全部被删除，那么该索引也会被自动删除；

查询

查询是数据库最重要的功能；可以查询数据、筛选数据、指定显示格式；

几何函数查询、连接查询、子查询、合并查询、取别名、正则表达式；

select name from stu;

select name,age from stu order by age;

单表查询：所有字段、指定字段、指定记录、空值、多条件查询、结果排序；

指定字段：列举所需查询的字段即可，*表示所有字段；

select*from stu where age>10;

where:>、<、>=、<=、=、<>、!=、between and、in、like、is null、and、or、distinct

select*from student where age between 18 and 28;

between a and b等价于x>=a&&x<=b,双闭区间，如果a>b,则返回空；

select*from student where age in(55,66,77,88);

in(a,b,c)表示是（）中的任意一个数据，等价于x=a||x=b||x=c；

like通配符%表示任意个任意字符，字符个数0-n个，字符任意；

’b%’’%b’’%b%’’a%b’分别表示b开头，b结尾，包含b，a开头且b结尾；

下划线通配符_，表示任意一个字符，字符个数为1，字符任意；

多个逻辑条件查询：逻辑与and逻辑或or；

select*from student where name like \'a%\';

select*from student where name like \'%o%\';

select*from student where name like \'%o%y\';

select*from student where name like \'___\';

select*from student where name like \'____y\';

select*from student where name is null;

select*from student where name like \'m%\' and age>10;

select*from student where name like\'%y\' or age<10;

select distinct field from tableName;

select distinct age from student;

排序：

order by field;按照某个字段进行排序，默认升序；降序排序用desc关键字；

select*from student where name like \'%o%\' order by age;

order by filed1,field2;多列排序，首先第一列必须有相同的值，否则不会按照第二例进行排序；

select*from student order by name ,age ;

select *from student order  by age desc;

select*from student order by name desc,age desc;

分组：■■■

group by通常和集合函数一起使用，min,max,avg,count,sum,,

select field count(*) as total from table group by field;

select name count(*) as total from student group by name;

select field1 group_concat(field2) as newField from table group by field1;

select name ,group_concat(age) as ages from student group by name;

select fiedl1, group_concat(field2) as newField from table group by field1 having count(field2)>1;分组→统计→筛选

select name,group_concat(age) as ages from student group by name having count(age)>1;

where→group by→having

with rollup表示统计数据；

select name,count(*) as total from student group by name with rollup;

select*from table gruop by field1,field2,field3;

多字段分组，先按照第一个字段分组（相同项合并），然后在第一个字段相同的记录中，按照第二个字段分组，以此类推；

select field from table limit count;

限制查询结果数量：limit[,位置偏移量]行数

 select*from student order by name limit 4;

select*from student limit 3,4;

从偏移量3开始共显示4行；偏移量3表示第四行，第一行的偏移量为0；

集合函数/聚合函数查询count,sum,avg,max,min,,,

count(*)计算表中的总行数，不管是否为空；

count(字段名)计算某字段的行数，为空不计入；

 select count(*) as rowCount from student;

select count(name) as nameCount from student;

 select age,count(name) as nameCount from student group by age;

select sum(age) as sumAge from student where age<10;

select avg(age) as avgAge from student;

 select min(age)from student;

select max(name)from student;字符串按照字码顺序进行排序；

连接查询■

连接是关系型数据库的主要特点；

连接查询包括：内连接、外连接、复核连接；

通过连接运算符实现多个表的查询；

内连接查询inner join

select suplliers.id supplyer.name fruits.name fruits.price

from suppliers,fruits where suplliers.id=fruits.id;

select suplliers.id supplyer.name fruits.name fruits.price

from suppliers inner join fruits on suplliers.id=fruits.id;

对于两个表中都有的字段，需要使用完全限定名，用.连接符号，即表名.字段名；

从供应商表选择两个列，id和name，从水果表选择两个列，name和price，共同组成一个新的表；条件是二者的id相同；

自连接查询（自己连接自己）

select s1.name,s2.age from student as s1, student as s2 where s1.age=s2.age and s1.age<10;

如果在一个连接查询中，涉及到的两个表是同一个表，这种查询称为自连接查询；

自连接是一种特殊的内连接，两个相互连接的表在物理上为同一张表，但可以在逻辑上分为两张表；

为了避免同一个表名称相同导致的二义性，这里对两个表分别重命名，也叫别名；

外连接分为 左外连接，右外连接；

left join 左连接：返回包括左表中所有记录和右表中连接字段相等的记录；

right join 右连接：返回包括右表中的所有记录和左表中连接字段相等的记录；

select customer.id,orders.num from customers left outer join orders on customers.id=orders.id;

select customer.id,orders.num from customer right outer join orders on customer.id=orders.id;

select customers.id,orders.num from customers inner join orders on customers.id=orders.id and customers.id=10001;

子查询也叫嵌套查询，是一个查询嵌套在另一个查询内；

先执行子查询，将子查询的结果作为外层查询的入参，进行第二层查询过滤；

子查询常用操作符：any,some,all,in,exists,<,<=,>,>=,=,!=,in,,,,

子查询可以添加到select,upodate,delete语句中，并且可以多层嵌套；

select num from tb1 where num>any(select num from tb2);

select num from tb1 where num>some(select num from tb2);

any等价于some，表示大于集合中的任意一个，就算是满足条件；相当于逻辑或；也就是大于集合中的最小值即可；

select num from tb1 where num >all(select num from tb2);

all表示大于集合中的所有，相当于逻辑与；也就是大于集合中的最大值；

select num from tb1 where  exists(select num from tb2 where num<0);

子查询是否存在，也就是子查询是否为空，如果为空，则不再进行外层查询，如果不为空，也就是子查询结果存在，就进行外层查询；

select num from tb1 where num in(select num from tb2);

in表示是否属于集合的元素，也就是集合中是否有一个元素与该值相等；相当于交集；

select num from tb1 where num not in(select*from tb2);

non in表示不在集合中，相当于差集；

合并查询结果

union[all]可以将两个查询结果合并为一个结果集，前提是两个查询的列数和数据类型必须相同；

select num from tb1 where num>5 union all select num from tb2 where num>5;

select num from tb1 union select age from stu;

select num from tb1 union all select num from tb2;

select num from tb1 union  select num from tb2;

union all包含重复行；union会自动删除重复行；union all效率更高；

表和字段的别名

表名 [as] 表别名；字段名 [as] 字段别名；其中as可以省略；

select*from student as s where s.age>10;使用as

select*from student s where s.age>10;省略as

在为表取别名时，不能与已存在的表名冲突；

select s.name n,s.age a from student s where s.age<10;

字段别名主要是为了显示的需要；

正则表达式查询regexp

select*from student where name regexp(\'^c\');匹配cat Charles Clark

select*from student where name regexp(\'y$\');匹配mokey donkey Marry Lily

select*from student where name regexp(\'o.*[stuvwxyz]\');mokey donkey Colorful

select*from student where name regexp(\'ap\'); apple

select*from student where name regexp(\'co|ca\');cat Colorful

MySQL指令

举例

数据库

创建：create database name;create database mydatabase;

删除：drop database name;drop database mydatabase;

查看定义：show create database name;show create database mydatabase;

显示：show databases;显示数据库清单（所有数据库）

引擎

显示所有存储引擎：show engines;

显示默认引擎：show variables like \'storage_engine\';

MySQL存储引擎：InnoDB,MyISAM,Memory,Merge,Archive,Fdedrated,CSV,BLACKHOLE；

数据表

选择数据库→在该数据库内创建数据表；

use database mydatabase;create table mytable;

显示所有表：show tables;

查看数据表结构：describe tablename;describe student;desc student;

可以查看：字段名称、数据类型、约束信息；

查看创建表信息：show create table tablename;show create table mytable;

可以查看：创建表的语句、存储引擎、字符编码；

修改表名：alter table 旧名称rename  [to] 新名称;

alter table student rename to stu;alter table stu rename student;

修改数据类型：alter table student modify name varchar(50);

修改字段名称：必须指定数据类型，不可省略；

alter table 表名change 原字段名 新字段名 数据类型；

alter table tablename change name fullname varchar(50);

change可以同时修改字段名称和数据类型，若只想修改名称，则把数据类型写成原先的类型；若只想修改数据类型，则把名称写成原名称，此时change等效于modify；

添加字段：alter table 表名 add 字段名 数据类型 [约束条件][first|after 已有字段名];

alter table student add sex varchar(10) default ‘male’ after name;

alter table student add id int(11) not null first;

alter table dogs add sex varchar(11) default \'male\' after name;

添加位置有2中选择：开头或者某个字段后面；

删除字段：alter table student drop age;

修改位置：alter table student modify age int(11) after name;

修改存储引擎：alter table student engine=myISAM;(引擎名称不区分大小写)

MySQL存储引擎：InnoDB,MyISAM,Memory,Merge,Archive,Fdedrated,CSV,BLACKHOLE；

删除无关联数据表：删除没有关联的一个或者多个数据表；

drop table [if exists] name1 [,name2,name3...];

drop table if exists stu;

删除关联父表：先删除子表再删父表；先取消子表的外键约束，再删除父表；

主键和外键

主键：列数据必须唯一且非空；主键与记录一一对应，主键是记录的唯一标识；

主键分为单字段主键和多字段联合主键；

[constraint 约束名] primary key(字段名);

primary key (id);id int(11)primary key;primary key(id,name);

外键：

用来在两个表的数据之间建立链接；

l 外键可以使一列或者多列，一个表可以有一个或多个外键；

l 外键可空，若不为空时，必须是另一个表中主键的某个值；

l 外键是一个字段，可以不是本表的主键，但一定是另外一个表的主键；

l 外键主要用来保证数据引用的完整性，定义外键后，关联表中的行不得删除；

主表（父表）：关联字段中主键所在的表称为主表；

从表（子表）：关联字段中外键所在的表称为从表；

[constraint 外键名]foreign key 字段名 references主表名 主键列;

[constraint 外键名]foreign key 字段名[,字段名2,...] references主表名 主键列[,主键列2,...];

constraint fk_name foreign key(students)reference class(id)

关联：指的是关系型数据库中，相关表之间的联系。

子表的外键必须关联父表的主键，并且关联字段的数据类型必须匹配；

建表以后添加外键：
语法：alter table 表名称 add foreign key (列名称) references 主表名 主键列;

创建表的同时添加外键

-> errorType varchar(25),

-> constraint fk_errorType foreign key(errorType) references errorType(name)

表创建好之后再添加外键：

 alter table res2 add foreign key(errorType) references errorType(name);

删除外键约束：alter talbe name drop foreign key fkname;

 alter table res3 drop foreign key fk_errorType;

约束

定义列的同时指定约束，列定义完后为字段指定约束；

主键约束：id int(11)primary key;constraint pk primary key(id);

非空约束：name varchar(25)not null;constraint nnull not null(name);

唯一约束：name varchar(25)unique;constraint uni unique(name);

默认值约束：departId int(11) default(1111);

自增约束：id int(11) primary key auto_increment;

默认初始值1，每增加一条记录，字段值加1；

一个表只能有一个字段使用自增约束；

自增约束的字段必须是主键或主键的一部分；

数据

■插入数据：

insert into tablename (field1,field2,field3)values(v1,v2,v3);

insert into student (name,age) value(\'Tome\',33);

insert into student value(\'Joe\',88);

insert into student values(\'Trump\',77);

关键词，用value或者value均可；

两种插入方式：指定字段和不指定字段；

如果不指定字段名称，则插入数据的个数和顺序，必须和所有的字段一一对应；

insert into tablename (columnList)values(valueList1),(valueList2)...;

insert into student(name,age)values(\'dog\',3),(\'cat\',4);

insert into student values(\'mokey\',11),(\'donkey\',22);

将查询结果插入到表中

insert into tableName1(columnList)select (columnList)from tableName2 where condition;

insert into stu(name,age)select name,age from student where age<20;

■修改数据：

update 表名 set 字段名1=值1，字段名2=值2，字段名3=值3 where 条件；

update stu set name=\'monkey\' where name=\'mokey\';

update stu set name=\'dongkey\',age=22 where name=\'monkey\';

如果忽略where子句，则更新表中的所有数据；

■删除数据：

delete from tableName [where condition];

如果不指定where条件，则删除表中的所有数据；

delete from student where age=33;

delete from student where age between 10 and 90;

修改和删除，如果不指定where条件，则会对所有的行进行操作；

一般应先用select语句进行查看，再进行修改和删除操作；

内连接

关键字：left join on / left outer join on

语句：select * from a_table a left join b_table b on a.a_id = b.b_id;

组合两个表中的记录，返回关联字段相符的记录，也就是返回两个表的交集（阴影）部分

左连接

左外连接

关键字：left join on / left outer join on

语句：select * from a_table a left join b_table b on a.a_id = b.b_id;

left join 是left outer join的简写，它的全称是左外连接，是外连接中的一种。

左(外)连接，左表(a_table)的记录将会全部表示出来，而右表(b_table)只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。

右连接

右外连接

关键字：right join on / right outer join on

语句：select * from a_table a  right outer join b_table b  on a.a_id = b.b_id;

right join是right outer join的简写，它的全称是右外连接，是外连接中的一种。

与左(外)连接相反，右(外)连接，左表(a_table)只会显示符合搜索条件的记录，而右表(b_table)的记录将会全部表示出来。左表记录不足的地方均为NULL。