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