Sql数据库基本操作
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;
//综上,可以直接创建数据库且设置编码方式
CREATE DATABASE h_test DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
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、创建索引
索引采用B+树结构,找到键之后再把键对应的值查询出来,底层以链表的形式连接
包含:普通索引、唯一索引和全文索引
索引在创建表时就可以添加,或者在创建表之后添加索引(create index XX on XX)
//创建索引
//一.创建表的时候创建索引
create table 表名(
字段名 数据类型[完整性约束条件],
...
字段名 数据类型,
[UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY
);
//1-1.创建普通索引
create table test1(
id INT,
name VARCHAR(20),
age INT,
INDEX (id)
);
//可以插入一条数据,查看索引是否被使用
explain select * from test1 where id=1 \G;
//1-2.创建唯一性索引
create table test2(
id INT,
name VARCHAR(20),
age INT,
UNIQUE INDEX unique_id(id asc)
);
//1-3.创建全文索引
create table test3(
id INT,
name VARCHAR(20),
age INT,
FULLTEXT INDEX fulltext_name(name)
)ENGINE=MyISAM;
//1-4.创建单列索引
create table test4(
id INT,
name VARCHAR(20),
age INT,
INDEX single_name(name(20))
);
//1-5.创建多列索引
create table test5(
id INT,
name VARCHAR(20),
age INT,
INDEX multi(id,name(20))
);
//1-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;
//2-1.创建普通索引
create index index_id on student(id);
//2-2.创建唯一性索引
create unique index uniqueidx on student(id);
//2-3.创建单列索引
create index singleidx on student(age);
//2-4.创建多列索引
create index mulitidx on student(name(20),intro(40));
//2-5.创建全文索引
create fulltext index fulltextidx on student(name);
//2-6.创建空间索引
create spatial index spatidx on student(g);
//下图是第二种方法创建索引演示后的所有索引
---------------------------------------------------
//三.使用alter table tname add语句在已经存在的表上创建索引
//删除student表,重新创建
drop table student;
create table student(
id int,
age int,
name varchar(20),
intro varchar(40),
space GEOMETRY NOT NULL
)ENGINE=MyISAM;
//3-1.创建普通索引
alter table student add index index_id(id);
//3-2.创建唯一性索引
alter table student add unique uniqueidx(id);
//3-3.创建单列索引
alter table student add index singleidx (age);
//3-4.创建多列索引
alter table student add index multidx(name(20),intro(40));
//3-5.创建全文索引
alter table student add fulltext index fulltextidx(name);
//3-6.创建空间索引
alter table student add spatial index spatidx(space);
//下图演示结果
---------------------------------------------------
//四.删除索引,有下面两种方式
//1.使用alter table tName drop删除索引fulltextidx
alter table student drop index fulltextidx;
//2.使用drop index删除索引spatidx
drop index spatidx on student;
//下图可看到删除成功
5、数据的增删改
5-1 添加数据
//重新建立表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);
5-2 更新数据
//更新id=1的数据
update student set name="howie1",grade=60 where id=1;
//批量更新,如果没有where子句,会更新表中所有对应数据
update student set grade=100 where id<4;
5-3 删除数据
//删除id=6的数据
delete from student where id=6;
//批量删除数据
delete from student where id>3;
//删除所有数据,DDL(数据定义语言)语句 truncate table student也可以删除表内所有数据
delete from student;
6、数据的查询
//聚合函数
//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;
7、数据库的事务操作
● 事务的概念,会开启、提交和回滚事务
● 事务的四种隔离级别
● 创建存储过程
● 调用、查看、修改和删除存储过程
7-1 事务管理
start transaction; -- 开启事务
commit; -- 提交事务
rollback; -- 取消事务(回滚)
//创建表account,插入数据
create table account(
id int primary key auto_increment,
name varchar(40),
money float
);
insert into account(name,money) values('a',1000),('b',2000),('c',3000);
//利用事务实现转账功能,首先开启事务,然后执行语句,提交事务
start transaction;
update account set money=money-100 where name='a';
update account set money=money+100 where name='b';
commit;
//事务的提交,通过这个命令查看mysql提交方式
select @@autocommit; -- 若为1,表示自动提交,为0,就要手动提交
//若事务的提交方式为手动提交
set @@autocommit = 0; -- 设置为手动提交
start transaction;
update account set money=money+100 where name='a';
update account set money=money-100 where name='b';
//现在执行select * from account 可以看到转账成功,若此时退出数据库重新登录,会看到各账户余额没有改变,所以一定要用commit语句提交事务,否则会失败
//事务的回滚,别忘记设置为手动提交的模式
start transaction;
update account set money=money-100 where name='a';
update account set money=money+100 where name='b';
//若此时a不想转账给b,可以使用事务的回滚
rollback;
//事务的隔离级别
read uncommitted;
read committed;
repeatable read;
serializable;