MySQL学习笔记（一）

-----------MySQL命令---------------
1.登录数据库
>mysql -h localhost -u root -p

2.查看所有数据库列表
show databases;

3.选择需要使用的数据库
use [数据库名];

4.显示当前数据库下的所有表
show tables；

5.查看详细的数据表结构
show create table [数据表名]

6.查看表结构
desc [表名]；

7.清空当前输入行的数据
\c

8.SQL结束标记
；或\g 使用\G结束主要是为了让数据显示得美观一些。

9. 显示MYSQL帮助命令
\h 或者\?

-----------------------------------
-----------数据表操作--------------
1.重命名字段
alter table [数据表名] change [旧字段名称]［新字段］[数据类型];

2.修改字段的位置
alter table [数据表名] modify [字段名] [数据类型] after [ 前一个字段];

3.修改字段名
alter table [数据表名] change [字段名] score [数据类型]);

4.查询表中的主键和外键
select constraint_name from information_schema.key_column_usage where table_schema=[数据表名]; 或者
select * from information_schema.key_column_usage where table_schema=[数据表名];

5.删除字段的外键约束
alter table [数据表名] drop foreign key [外键名];

6.删除字段
alter table [数据表名] drop [字段名];

7.增加字段
alter table [数据表名]add [字段名] [数据类型];

8.修改表名
alter table [数据表名]rename to [字段名];

9.删除表
drop table [数据表名];

10.修改字段类型
alter table [数据表名] Modify [字段名] [数据类型]

11.更改数据表的引擎
alter table [数据表名] ENGINE=[存储引擎名]

---------------创建索引-----------------
语法：
create table [数据表名]
(
字段名 数据类型 [完整性越俗条件],
字段名 数据类型 [完整性越俗条件],
字段名 数据类型 [完整性越俗条件],
……
字段名 数据类型
[UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY [索引名](字段名[(长度)]|[ASC|DESC])
);

12.创建普通索引，使用关键字index或者Key，二选其一
create table sys_user
(
userID int primary key not null auto_increment,
userName varchar(60) not null,
userPwd varchar(60) not null,
Email varchar(50),
index [索引名称] (userID) ---对UserID创建普通索引
);

---插入测试数据
insert into Sys_User(userName,userPwd,Email)values('Jack','123456','jack@admi
n.com');
insert into Sys_User(userName,userPwd,Email)values('Tom','123456','Tom@admin.
com');
insert into Sys_User(userName,userPwd,Email)values('Peeter','123456','Peeter@
admin.com');
insert into Sys_User(userName,userPwd,Email)values('Harry','123456','Harry@ad
min.com');

13.查看索引是否被使用
explain select * from sys_user where Id=1 \G
possible_keys和 key都在存在，说明索引存在并且开始说使用了。

14.创建唯一索引
create table Index_Demo
(
id int primary key not null auto_increment,
name varchar(45) not null,
unique index index_id(id) ---使用unique指定唯一索引
);

15.创建全文索引（只有MYISAM引擎支持全文索引）
create table Index_Full
(
id int primary key not null auto_increment,
name varchar(45) not null,
fulltext index index_full_id(name)----使用fulltext指定为全文索引，全文索引只能是（char，varchar，text类型的字段）
)engine=MYISAM;

16.创建单列索引（可以给索引字段设置长度）
create table Index_alone
(
id int primary key not null auto_increment,
name varchar(45),
index index_id(name(10)) -设置单列索引字段的长度为10，可以提高查询速度
);

17.创建多列索引(在一个表的多个字段上创建索引)
create table index_Many
(
id int primary key auto_increment not null,
varno varchar(12) not null,
varname varchar(45) not null,
index index_name(varno,varname)
);
使用多列索引时应该注意：只有使用了索引字段中的第一个索引时，才会触发索引，如果没有使用到多列索引中的第一个索引的话，则不会触发索引。
一次优化索引的时候，可以首先考虑到优化多列索引。

18.创建空间索引（SPATIAL关键字.1.存储引擎必须是MYISAM；2.必须有非空约束）
create table index_space
(
id int primary key auto_increment not null,
space geometry not null,
spatial index index_space_id(space)
)engine=MyISAM;
补充：空间数据类型
GEMOETRY,POINT,LINESTRING,POLYGON,平常很少用到

-----------------------------------

-----------在已经存在的表上创建索引---------------
语法
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX [索引名]
ON [表名] ([字段名] [(长度)] [ASC|DESC]);

1.创建普通索引--使用表sys_user和sys_role为例
create index index_userid on sys_user(userID);

2.创建唯一索引
create unique index index_name on sys_user(userName);

3.创建全文索引（注意只有MyISAM存储引擎支持全文索引）
--修改存储引擎
alter table student engine=MyISAM;
--创建测试索引
create fulltext index index_full on student(name (10));

4.创建多列索引
create index index_n on grade(course,s_num);

5.创建空间上索引
--创建测试表
create table space
(
id int primary key auto_increment not null,
address geometry not null
);
--修改存储引擎
alter table space engine=MyISAM;
--创建全文索引
create spatial index index_add on space(address);

-----------使用alert Table创建索引--------------
语法
ALTER TABLE [表名] ADD [UNIQUE|FULLTEXT|SATIAL] INDEX [索引名]
([字段名] [(长度)] [ASC|DESC]);

1.创建普通索引--使用表sys_user和sys_role为例
alter table student add index index_name(name);

2.创建唯一索引
alter table student add unique index inex_unique(num);

3.创建全文索引（注意只有MyISAM存储引擎支持全文索引）
--修改存储引擎
alter table student engine=MyISAM;

--创建测试索引
alter table student add fulltext index index_full(address);

4.创建多列索引
alter table grade add index index_many(course,s_num);

5创建空间索引
alter table grade add spatial index index_space(course);

6.删除索引

drop index [索引名] on [表名]；

-----------------------------------

练习实例代码

create table Sys_Role(
  RoleID int primary key auto_increment not null,
  RoleName varchar(60) not null,
  Remark varchar(120) null
 
 );
 insert into Sys_Role ( RoleName,Remark)values ('系统管理员',Null);
 insert into Sys_Role ( RoleName,Remark)values ('管理员',Null);
 insert into Sys_Role ( RoleName,Remark)values ('前台用户',Null); 
 
 create table Sys_User(
 userID int auto_increment primary key not null ,
 RoleID int , 
 constraint U_R_fk foreign key (RoleID) references Sys_Role (RoleID),
 userName varchar(30),
 userPwd varchar(32),
 Email varchar(50)
 );  
 insert into Sys_User(userName,userPwd,Email,RoleID)values('Jack','123456','jack@admi
 n.com',1);
 insert into Sys_User(userName,userPwd,Email,RoleID)values('Tom','123456','Tom@admin.
 com',2);
 insert into Sys_User(userName,userPwd,Email,RoleID)values('Peeter','123456','Peeter@
 admin.com',3);
 insert into Sys_User(userName,userPwd,Email,RoleID)values('Harry','123456','Harry@ad
 min.com',2);
 
 
 
 ------- 实例练习
 create table Student 
 (
  num int (10) primary key not null unique,
  name varchar(20) not null,
  sex varchar(4) not null,
  birthday datetime,
  address varchar(50)
 );
 create table Grade
 (
  id int(10) primary key not null unique,
  course varchar(10) not null,
  s_num int(10) not null,
  constraint S_G_fk foreign key (s_num) references Student (num),
  grade varchar(4)
 );
 
 ---创建普通索引
 create table sys_user
 (
  userID int not null ,
  userName varchar(60) not null,
  userPwd varchar(60) not null,
  Email varchar(50),
  index index_user_id (userID) 
 );
 ---插入测试数据
 insert into Sys_User(userID,userName,userPwd,Email)values(1,'Jack','123456','jack@admi
 n.com');
 insert into Sys_User(userID,userName,userPwd,Email)values(3,'Tom','123456','Tom@admin.
 com');
 insert into Sys_User(userID,userName,userPwd,Email)values(2,'Peeter','123456','Peeter@
 admin.com');
 insert into Sys_User(userID,userName,userPwd,Email)values(4,'Harry','123456','Harry@ad
 min.com');
 
 ----创建唯一索引
 create table Index_Demo
 (
  id int not null ,
  name varchar(45) not null,
  unique index index_id(id) 
 ); 
 
 ---创建全文索引
 create table Index_Full
 (
  id int not null,
  name varchar(45) not null,
  fulltext index index_full_id(name)
 )engine=MYISAM; 
 
 ---创建单列索引
 create table Index_alone
 (
  id int  not null,
  name varchar(45),
  index index_id(name(10))
 );       
 ----创建多列索引
 create table index_Many
 (
  id int not null,
  varno varchar(12) not null,
  varname varchar(45) not null,
  index index_name(varno,varname)
 );
 ---插入测试数据
 insert into index_Many (id,varno,varname) values(1,'01010022','harry');
 insert into index_Many (id,varno,varname) values(2,'01010023','Jack');
 insert into index_Many (id,varno,varname) values(3,'01010024','Tom');
 insert into index_Many (id,varno,varname) values(4,'01010025','Pattery');
 insert into index_Many (id,varno,varname) values(5,'01010026','Jamson');
 insert into index_Many (id,varno,varname) values(6,'01010027','Lucy');       
 
 创建空间索引
 create table index_space
 (
  id int primary key auto_increment not null,
  space geometry not null,
  spatial index index_space_id(space)
 )engine=MyISAM;
 
 在已经存在的表上创建索引
 --创建普通索引
 create index index_userid on sys_user(userID);
 --创建唯一索引
 create unique index index_name on sys_user(userName);
 
 --新建测试表
 ------- 实例练习
 create table Student 
 (
  num int (10) primary key not null unique,
  name varchar(20) not null,
  sex varchar(4) not null,
  birthday datetime,
  address varchar(50)
 );
 create table Grade
 (
  id int(10) primary key not null unique,
  course varchar(10) not null,
  s_num int(10) not null,
  grade varchar(4)
 );
 --创建全文索引
 create fulltext index index_full on student(name (10));
 
 --使用alter table创建索引
 --创建普通索引
 alter table student add index index_name(name);
 --创建唯一索引
 alter table student add unique index inex_unique(num);