MySQL學習筆記

創建索引

一、創建表的同時創建索引

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，