每一个不曾起舞的日子都是对生命的辜负。
–创建数据库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;
–首先选定操作的数据库use h_test;
–创建表studentcreate 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;
约束条件 | 说明 |
---|---|
PRIMARY KEY | 主键约束,用于唯一表示对应的记录 |
FOREIGN KEY | 外键约束 |
NOT NULL | 非空约束 |
UNIQUE | 唯一性约束 |
DEFAULT | 默认值约束,用于设置字段的默认值 |
索引的作用是提高表中数据的查询速度。
分类有:
创建索引
一.创建表的时候创建索引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);
–删除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删除索引fulltextidxalter table student drop index fulltextidx;
–2.使用drop index删除索引spatidxdrop index spatidx on student;
–重新建立表studentdrop 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);
–更新id=1的数据update student set name="howie1",grade=60 where id=1;
–批量更新,如果没有where子句,会更新表中所有对应数据update student set grade=100 where id<4;
–删除id=6的数据delete from student where id=6;
–批量删除数据delete from student where id>3;
–删除所有数据delete from student;
单表查询:如何从数据库中获取你需要的数据
多表查询:实际开发中,需要进行2张表以上进行操作
–建立表studentcreate 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 inselect * 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;
–聚合函数
–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;
外键是指引用另一个表中的一列或者多列,被引用的列应该具有主键约束或者唯一性约束,用于建立和加强两个数据表之间的连接。
–创建表class,studentcreate 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;
–数据表有三种关联关系,多对一、多对多、一对一
–学生(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);
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:
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。