学习笔记：MySQL-1

mysql基本操作命令汇总

一、基本操作

1.关于数据库

–创建数据库
create database h_test;
–查看数据库
show databases;
–查看数据库信息
show create database h_test;
–修改数据库的编码，可使用上一条语句查看是否修改成功
alter database h_test default character set gbk collate gbk_bin;
–删除数据库
drop database h_test;

2. 关于数据表

–首先选定操作的数据库
use h_test;
–创建表student
create table student(
id int(11),
name varchar(20),
age int(11)
);
–查看数据表
show tables;
–查看数据表信息，后面加上参数/G可使结果更加美观
show create table student;
–查看表的的字段信息
desc student;
–修改表名
alter table student rename [to] h_student;
–修改字段名
alter table h_student change name stu_name varchar(20);
–修改字段的数据类型
alter table h_student modify id int(20);
–添加字段
alter table h_student add grade float;
–删除字段
alter table h_student drop grade;
–修改字段的位置
alter table h_student modify stu_name varchar(20) first;
alter table h_student modify id int(11) after age;
–删除数据表
drop table h_student;

3.表的约束

4.索引

索引的作用是提高表中数据的查询速度。
分类有：

• 普通索引
• 唯一性索引
• 全文索引
• 单列索引
• 多列索引
• 空间索引

创建索引

• 一.创建表的时候创建索引
  create table 表名(
字段名 数据类型[完整性约束条件],
...
字段名 数据类型,
[UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY
);
  –1.创建普通索引
  create table test1(
id INT,
name VARCHAR(20),
age INT,
INDEX (id)
);
  –可以插入一条数据,查看索引是否被使用
  explain select * from test1 where id=1 \G; --2.创建唯一性索引create table test2(` id INT,
name VARCHAR(20),
age INT,
UNIQUE INDEX unique_id(id asc)
);
  –3.创建全文索引
  create table test3(
id INT,
name VARCHAR(20),
age INT,
FULLTEXT INDEX fulltext_name(name)
)ENGINE=MyISAM;
  –4.创建单列索引
  create table test4(
id INT,
name VARCHAR(20),
age INT,
INDEX single_name(name(20))
);
  –5.创建多列索引
  create table test5(
id INT,
name VARCHAR(20),
age INT,
INDEX multi(id,name(20))
);
  –6.创建空间索引
  create table test6(
id INT,
space GEOMETRY NOT NULL,
SPATIAL INDEX sp(space)
)ENGINE=MyISAM;

• 二.使用create index语句在已经存在的表上创建索引

–首先新建一个表,这个表没有索引
create table student(
id int,
age int,
name varchar(20),
intro varchar(40),
g GEOMETRY NOT NULL
)ENGINE=MyISAM;
–1.创建普通索引
create index index_id on student(id);
–2.创建唯一性索引
create unique index uniqueidx on student(id);
–3.创建单列索引
create index singleidx on student(age);
–4.创建多列索引
create index mulitidx on student(name(20),intro(40));
–5.创建全文索引
create fulltext index fulltextidx on student(name);
–6.创建空间索引
create spatial index spatidx on student(g);

• 三.使用alter table语句在已经存在的表上创建索引

–删除student表，重新创建
drop table student;
create table student(
id int,
age int,
name varchar(20),
intro varchar(40),
space GEOMETRY NOT NULL
)ENGINE=MyISAM;
–1.创建普通索引
alter table student add index index_id(id);
–2.创建唯一性索引
alter table student add unique uniqueidx(id);
–3.创建单列索引
alter table student add index singleidx (age);
–4.创建多列索引
alter table student add index multidx(name(20),intro(40));
–5.创建全文索引
alter table student add fulltext index fulltextidx(name);
–6.创建空间索引
alter table student add spatial index spatidx(space);

• 四.删除索引

–1.使用alter table删除索引fulltextidx
alter table student drop index fulltextidx;
–2.使用drop index删除索引spatidx
drop index spatidx on student;

5.添加数据

–重新建立表student
drop table student;`` create table student(` id int,
name varchar(20) not null,
grade float
);
–插入一条数据，也可以少某个字段的同时也少对应的数据
insert into student(id,name,grade) values(1,'howie',70);
–也可以不指定字段名，但要注意顺序
insert into student values(2,'howie',80);
insert into student set id=3,name="howie",grade=90;
–同时添加多条数据
insert into student values
(4,'howie',80),
(5,'howie',80),
(6,'howie',80);

6.更新数据

–更新id=1的数据
update student set name="howie1",grade=60 where id=1;
–批量更新,如果没有where子句，会更新表中所有对应数据
update student set grade=100 where id<4;

7.删除数据

–删除id=6的数据
delete from student where id=6;
–批量删除数据
delete from student where id>3;
–删除所有数据
delete from student;

二、单表查询和多表操作

单表查询：如何从数据库中获取你需要的数据
多表查询：实际开发中，需要进行2张表以上进行操作

1.单表查询

–建立表student
create table student(
id int not null auto_increment,
name varchar(20) not null,
grade float,
primary key(id)
);
–插入数据
insert into student (name,grade) values
("howie1",40),
("howie1",50),
("howie2",50),
("howie3",60),
("howie4",70),
("howie5",80),
("howie6",null);
–查询全部
select * from student;
–查询某个字段
select name from student;
–条件查询,查询id=2学生的信息
select * from student where id=2;
–in关键字查询,也可以使用not in
select * from student where id IN(1,2,3);
–between and关键字查询
select * from student where id between 2 and 5;
–空值(NULL)查询，使用IS NULL来判断
select * from student where grade is null;
–distinct关键字查询
select distinct name from student;
–like关键字查询,查询以h开头，e结尾的数据
select * from student where name like "h%e";
–and关键字多条件查询,or关键字的使用也是类似
select * from student where id>5 and grade>60;

2.高级查询

–聚合函数
–count()函数,sum()函数,avg()函数,max()函数,min()函数
select count(*) from student;
select sum(grade) from student;
select avg(grade) from student;
select max(grade) from student;
select min(grade) from student;
–对查询结果进行排序
select * from student order by grade;
–分组查询
–1.单独使用group by分组
select * from student group by grade;
–2.和聚合函数一起使用
select count(*),grade from student group by grade;
–3.和having关键字一起使用
select sum(grade),name from student group by grade having sum(grade) >100;
–使用limit限制查询结果的数量
select * from student limit 5; select * from student limit 2,2; select * from student order by grade desc limit 2,2;
–函数.mysql提供了许多函数
select concat(id,':',name,':',grade) from student;
–为表取别名
select * from student as stu where stu.name="howie";
–为字段取别名,as关键字也可以不写
select name as stu_name,grade stu_grade from student;

3.多表操作

外键是指引用另一个表中的一列或者多列，被引用的列应该具有主键约束或者唯一性约束，用于建立和加强两个数据表之间的连接。

–创建表class,student
create table class(
id int not null primary key,
classname varchar(20) not null
)ENGINE=InnoDB;
create table student(
stu_id int not null primary key,
stu_name varchar(20) not null,
cid int not null
)ENGINE=InnoDB;
–添加外键约束
alter table student add constraint FK_ID foreign key(cid) references class(id);
–删除外键约束
alter table student drop foreign key FK_ID;

4.操作关联表

–数据表有三种关联关系，多对一、多对多、一对一
–学生(student)和班级(class)是多对一关系，添加数据
–首选添加外键约束
alter table student add constraint FK_ID foreign key(cid) references class(id);
–添加数据,这两个表便有了关联若插入中文在终端显示空白，可设置set names ‘gbk’;
insert into class values(1,"软件一班"),(2,"软件二班");
insert into student values(1,"howie",1),(2,"howie1",2),(3,"howie2",1),(4,"howie3",2);
–交叉连接
select * from student cross join class;
–内连接，该功能也可以使用where语句实现
select student.stu_name,class.classname from student join class on class.id=student.cid;
–外连接,首先在student,class表中插入数据
insert into class values(3,"软件三班");
–左连接，右连接
select s.stu_id,s.stu_name,c.classname from student s left join class c on c.id=s.cid;
select s.stu_id,s.stu_name,c.classname from student s right join class c on c.id=s.cid;
–复合条件连接查询就是添加过滤条件
–子查询
–in关键字子查询跟上面的in关键字查询类似
select * from student where cid in(select id from class where id=2);
–exists关键字查询,相当于测试，不产生数据，只返回true或者false，只有返回true，外层才会执行
select * from student where exists(select id from class where id=12); -- 外层不会执行
select * from student where exists(select id from class where id=1); -- 外层会执行
–any关键字查询
select * from student where cid>any(select id from class);
–all关键字查询
select * from student where cid=any(select id from class);

• 选择：select * from table1 where 范围
• 插入：insert into table1(field1,field2) values(value1,value2)
• 删除：delete from table1 where 范围
• 更新：update table1 set field1=value1 where 范围
• 查找：select * from table1 where field1 like ‘%value1%’
• 排序：select * from table1 order by field1,field2 [desc]
• 总数：select count as totalcount from table1
• 求和：select sum(field1) as sumvalue from table1
• 平均：select avg(field1) as avgvalue from table1
• 最大：select max(field1) as maxvalue from table1
• 最小：select min(field1) as minvalue from table1
• 高级查询：A： UNION 运算符 UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。
B： EXCEPT 运算符 EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。
C： INTERSECT 运算符 INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL)，不消除重复行。
• 使用外连接：A、left （outer） join： 左外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所有行。 SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
B：right （outer） join: 右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。
C：full/cross （outer） join： 全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。