MySQL学习笔记
索引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 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;
在删除表中的列时，该列也会从索引中删除；
如果索引中的列全部被删除，那么该索引也会被自动删除；
|
查询指令
查询
| 查询是数据库最重要的功能；可以查询数据、筛选数据、指定显示格式；
几何函数查询、连接查询、子查询、合并查询、取别名、正则表达式；
[table] | 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:>、=、=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 age1;分组→统计→筛选
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 agesome(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 num5 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 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;
[/td][/tr][tr][td=1,1,108]约束
[/td][td=1,1,782]定义列的同时指定约束，列定义完后为字段指定约束；
主键约束：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；
一个表只能有一个字段使用自增约束；
自增约束的字段必须是主键或主键的一部分；
[/td][/tr][tr][td=1,1,108]数据
[/td][td=1,1,782]■插入数据：
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 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;
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！ |
