MySQL学习笔记

触发器:由事件触发的程序段

定义

1.触发器也是一段程序,相当于事件处理程序EventHandler;

2.触发器和存储过程一样,都是嵌入到MySQL中的一段程序;

3.触发器由事件来触发,触发事件包括insert,update,delete;

4.触发器基于某个表,当该表进行特定操作时,就会激活该触发器;

5.触发器不需要call来调用,而是由系统自动调用;

创建

create trigger triggerName triggerTime triggerEvent

on tableName for each row triggerBody;

triggerName 触发器名称,用户指定;

triggerTime 触发时机,可以指定为before或after;

triggerEvent 触发事件,包括insert、update、delete;

tableName 建立触发器的表名,即在哪张表上建立触发器;

triggerBody触发程序体;

触发器可以包含多条语句,用begin和end标识;

create talbe account(num int,amount decimal(10,2));

create trigger myTrigger before insert on account for each row set@sum=@sum+new.amount;

create trigger triggerName triggerTime triggerEvent

on tableName for each row triggerBody;

delimiter//

create trigger myTrigger before insert on t1

for each row begin

insert into t2 set a2=new.a1;

delete from t3 where a3=new.a1;

update t4 set set b4=b4+1 where a4 =new.a1;

end

delimiter;

自定义语句结束符为//

创建触发器

语句块开始,相当于{

.

.语句块

.

.

//语句块结束,相当于}

恢复默认语句结束符;

1

查看

show triggers;

select*from information_schema.triggers where condition;

select*from infromation_schema.triggers where trigger_name=’myTrigger’\g;

使用

当满足触发条件时,系统自动激活触发器,执行相关代码;

删除

drop trigger [dbName.]triggerName;

如果不指定数据库名称,则在当前数据库执行删除操作;

drop trigger test.myTrigger;

视图:一个虚拟表;

概述

1.视图中的数据是对现有表数据的引用;

2.视图是从一个表或者多个表中导出的;

3.视图中可以使用:select,insert,update,delete,,

4.视图可以从现有视图中创建,视图一经定义便存储于数据库中;

5.通过视图看到的数据只是基本表中的数据;

6.视图中数据修改,基本表中数据也同时修改;

7.基本表中的数据修改,视图中的数据也同时修改;

创建

视图创建基于select语句和基本表数据;

create [or replace] [algorithm={undefined|merge|templateble}]

view viewName[{columnList}]

as select statement

[with [cascaded|local] check option]

create 创建新视图,replace替换已有视图;

algorithm选择的算法;

viewName视图名称 ;columnList属性列;select statement选择语句;

[with [cascaded|local] check option]视图更新时保证在视图的权限范围内;

lundefined自动选择算法|

lmerge将使用的视图语句和视图定义结合起来,使得视图定义的某一部分取代语句对应的部分|

ltemplateble将视图结果存入临时表,然后用临时表来执行语句;

Øcascaded更新视图时需要满足所有相关视图和表的条件;(满足所有条件)

Ølocal表示可更新视图时满足该视图本身定义的条件即可;(满足自身条件)

要求具有create view权限和select权限,如果replace要求drop视图权限;

视图属于数据库,默认在当前数据库创建;

要在指定数据库创建视图,需要使用完全限定名daName.viewName;

create view myView as select quantity,price,quantity*price from t;

create view myView(quantity,price,total) as select quantity,price,quantity*price from t;

create view class(id,name,class) as select student.id,student.name,stuInfo.class from student,stuInfo where student.id=stuInfo.id;

查看

describe viewName;

show table statuslike’viewName’;show table status like ‘viewT’\g;

show create viewviewName;show create view myView;

select*from information_schema.views;

修改

create or replaceview myView as select*from t;

可以用create or replace修改,语法同创建;

alter view myView as select quantity from t;

更新

update view myView set quantity=5;

insert into t values(3,5);更新表,视图会跟随更新;

delete frommyView where price=5;

视图中的删除是通过删除基本表中的数据实现的;

删除

drop view [if exists]viewName[,…] [restrict|cascade]

可以同时删除多个视图,逗号隔开;必须要有drop权限;

drop view if exists myView;

存储程序=存储过程+存储函数

创建

create procedure create function

■■■存储过程

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

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

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

特性:characteristics

①语言特性language sql ②结果是否确定[not]deterministic

③使用SQL语句的限制:{constrains sql|no sql|reads sql data|modifies sql data}

lconstrains sql表明子程序包含SQL语句,但是不包含读写数据的语句;(默认)

lno sql表明子程序不包含SQL语句;

lreads sql data子程序包含读写数据的语句;

lmodidies sql data包含读写数据的语句;

④指明谁拥有权限来执行sql security{definer|invoker}

ldefiner表示只有定义者才能执行;(默认)

linvoker表示拥有权限的调用者可以执行;

⑤comment ‘string’注释信息,用来描述存储过程或函数;

示例

create prodedure avgAge()

begin

select avg(age) as avgAge from student;

end;

delimiter //

create procedure proc()

begin

select*from student;

end//

delimiter;

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

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

create procedure CountProc (out count int)

begin

select count(*)into count from student;

end;

create funtion myFunc() returns char(50)

return (select name from student where age=22);

■■■存储函数

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 fruitName char(50);

declare fruitPrice decimal(8,2);

select name,price into fruitName,fruitPrice

from fruits where id=’a1’;

异常

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

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

declare conditionName condition for [conditionType]

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

lconditionName 条件名称conditionType条件类型

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

lmysqlErrorCode错误代码-数值类型

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

lcontinue表示不处理错误,继续执行|

lexit表示遇到错误马上推出|

lundo表示遇到错误后,撤回之前的操作,MySQL中断暂时不支持这样的操作;

conditionValue表示错误类型,可以取值如下:

lsqlstate[value]sqlstateValue包含5个字符的错误字符串;

lconditionName表示declare condition定义的错误条件名称;

lsqlWarning匹配所有以01开头的sqlstate错误代码;

lnotFound匹配所有以02开头的sqlstate错误代码;

lsqlException匹配所有未被sqlWarning和notFound匹配的错误代码;

lmySqlErrorCode匹配数值型错误代码;

① 捕获字符串错误码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),可以嵌套;

if exprCondition then statement;

[elseif exprCondition then statement2]…;

[else statementN];

end if;

if val is null

then select ‘val is null’;

else select ‘val is not null’;

end if;

case expr

when value1 then statement1

[when value2 then statement2]…

[else statementN]

end case;

case val

when 1 then select’val is 1’;

when 2 then select’val is 2’;

else select ‘val is not 1 or 2’;

end case;

case

when expr then statement

[when expr2 then statement2]…

[else statementN]

end case;

case

when val is null then select’val is null’;

when val<0 then select’val is less than 0’;

when val>0 then select’val is greater than 0’;

else select’val is 0’;

end case;

[loopLabel:]loop

statement

end loop [loopLabel]

declare id int default 0;

addLoop:loop

set id=id+1;

if id>=10 then leave addLoop;

end if;

end loop addLoop;

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

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

[repeatLabel:]repeat

statement

until expr

end repeat[repeatLabel]

declare id int default 0;

repeat

set id=id+1;

until id>=10

end repeat;

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

[whileLabel:]while expr do

statement

end while[whileLabel]

declare i int default 0;

while i<10 do

set i=i+1;

end while;

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

调用

调用存储过程

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

call CountProc(101,@num);

select CountFunc(101);

查看

查看存储过程和函数状态

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

show procedure statuslike ‘c%’\g;

show create {procedure|function}name;

show create functiontest.CountFunc \g;

select*from information_schema.routineswhere routine_name=’sp_name’;

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

修改

修改存储过程和函数

alter {procedure|function} name [characteristic…]

alter procedureCountProc modifies sql data sql security invoker;

alter functioncountFunc reads sql data comment ‘find name’;

删除

删除存储过程和函数

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

drop prodedurecountProc;

drop functioncountFunc;

索引index、key

创建索引

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

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 indexmyindex(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 indexmyindex (g))engine=myisam ;

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

■■使用alter table创建

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

alter table student add indexmyIndex(name(10));

索引的查看方式:

lshow indexfrom tableName;查看表中索引的详细信息;(这里不能用show key)

lshow create tabletableName;查看创建表信息,包含创建索引信息;

lexplain 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 keymultiKey(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 {*|columnList}

from tableList

[where condition]

[group by field1]

[order by field2]

[limit [offset,] rowCount]

选择查询字段(1-n个)

指定查询数据源(1-n个)

筛选条件

按照某个字段分组

按照某个字段排序

显示的行数

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&&xb,则返回空;

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 byfield1;

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

having

分组之前进行过滤

分组之后进行过滤

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.idsupplyer.name fruits.name fruits.price

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

select suplliers.idsupplyer.name fruits.name fruits.price

from suppliers inner joinfruits 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 joinorders on customers.id=orders.id;

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

select customers.id,orders.num from customers inner joinorders 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 wherenum>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 allselect num from tb2 where num>5;

select num from tb1 union select age from stu;

select num from tb1 union allselect 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

^

位置符,字符串开始,位于第一个字符之前

‘^b’表示b开头,匹配bag,barry

$

位置符,字符串结束,位于最后一个字符之后

‘y$’表示y结尾,匹配berry,sorry

.

通配符,表示任意一个字符

‘b.t’匹配bit,bat,bot,b&t

*

数量符,表示字符个数0-n,

+

数量符,表示字符个数1-n,

‘^ba+’b开头后面紧跟至少一个a,匹配bat,banana,baad

[] [^]

字符集,字符集的补集

{m,}{m,n}

数量符,至少出现m次,出现m-n次

字符串

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]’);mokeydonkey 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;

create table name(

字段名1 数据类型[列约束条件][默认值],

字段名2 数据类型[列约束条件][默认值],

字段名3 数据类型[列约束条件][默认值],

[表约束条件]

);

use database mydb;

create table students(

id int(11)primary key;

name varchar(25),

age int(8),

class int(8)

);

use database mydb;

create table students(

id int(11);

name varchar(25),

age int(8),

class int(8),

primary key(id)

);

use database mydb;

create table students(

id int(11);

name varchar(25),

age int(8),

class int(8),

primary key(id,name)

);

显示所有表:show tables;

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

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

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

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

show tables

show create table

describe table

当前数据库中所有表

创建语句、存储引擎、字符编码

字段名称、数据类型、约束信息

修改表名: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;

rename [to]

modify table

change table

修改表名

修改字段数据类型

修改字段相对位置

修改字段名、修改数据类型

同时修改字段名、数据类型

添加字段: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 keyfk_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 aleft joinb_table bona.a_id = b.b_id;

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

左连接

左外连接

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

语句:select * from a_table aleft joinb_table bona.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 joinb_table b ona.a_id = b.b_id;

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

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

  • MySQL语句中,字符串应当用单引号表示,而不是双引号,例如’Tom’;
  • 表中字段区分大小写,name和Name是不同的字段;
  • 命令不区分大小写,DROP和drop是相同的命令;
  • 表名称不区分大消息,Student和student被认为是同名的;
  • 命令行后面必须跟分号;

通过MySQL命令行修改:(编码可选)

mysql> set character_set_client=utf8;

mysql> set character_set_connection=utf8;

mysql> set character_set_database=utf8;

mysql> set character_set_results=utf8;

mysql> set character_set_server=utf8;

mysql> set character_set_system=utf8;

mysql> set collation_connection=utf8;

mysql> set collation_database=utf8;

mysql> set collation_server=utf8;