发布：2021/10/8 14:09:02作者：管理员 来源：本站 浏览次数：731

一、基本操作

   对数据库以及表的一些基本操作

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

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

1-3表的约束

约束条件 说明

PRIMARY KEY 主键约束，用于唯一标识对应的记录

FOREIGN KEY 外键约束

NOT NULL 非空约束

UNIQUE 唯一性约束

DEFAULT 默认值约束，用于设置字段的默认值

1-4索引

   作用：提高表中数据的查询速度

   1.普通索引

   2.唯一性索引

   3.全文索引

   4.单列索引

   5.多列索引

   6.空间索引

//创建索引

//一.创建表的时候创建索引

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

//下图是第二种方法创建索引演示后的所有索引

index2

//三.使用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;

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

//下图演示结果

index3

//删除索引，有下面两种方式

//1.使用alter table删除索引fulltextidx

alter table student drop index fulltextidx;

//2.使用drop index删除索引spatidx

drop index spatidx on student;

//下图可看到删除成功

dropIndex

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

1-6.更新数据

//更新id=1的数据

update student set name="howie1",grade=60 where id=1;

//批量更新,如果没有where子句，会更新表中所有对应数据

update student set grade=100 where id<4;

1-7.删除数据

//删除id=6的数据

delete from student where id=6;

//批量删除数据

delete from student where id>3;

//删除所有数据,DDL(数据定义语言)语句 truncate table student也可以删除表内所有数据

delete from student;

二 、单表查询和多表操作

   单表查询：如何从数据库中获取你需要的数据

   多表查询：实际开发中，需要进行2张表以上进行操作

2-1-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-1-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;

2-2.多表操作

   1.了解外键

   2.了解关联关系

   3.了解各种连接查询多表的数据

   4.了解子查询，会使用各种关键字以及比较运算符查询多表中的数据

2-2-1.外键

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

//创建表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      -- 表示班级id，它就是class表的外键

)ENGINE=InnoDB;

//添加外键约束

alter table student add constraint FK_ID foreign key(cid) references class(id);

//删除外键约束

alter table student drop foreign key FK_ID;

看下图可知外键添加成功：

foreign key

2-2-2.操作关联表

//数据表有三种关联关系，多对一、多对多、一对一

//学生(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);

具体结果请看下图：

交叉连接

内连接

left/right join on

in

exists

any all

三 、事务与存储过程

   事务的概念，会开启、提交和回滚事务

   事务的四种隔离级别

   创建存储过程

   调用、查看、修改和删除存储过程

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

3-2 存储过程

//创建查看student表的存储过程

//创建student表

create table student(

 id int not null primary key auto_increment,

 name varchar(4),

 grade float

)ENGINE=InnoDB default character set utf8;

delimiter //  -- 将mysql的结束符设置为//

create procedure Proc()

 begin

 select * from student;

 end //

delimiter ;   -- 将mysql的结束符设置为;

call Proc();  -- 这样就可以调用该存储过程

//变量的使用,mysql中变量不用事前申明，在用的时候直接用“@变量名”使用就可以

set @number=100; -- 或set @num:=1;

//定义条件和处理程序

//光标的使用

//1.声明光标

DECLARE * cursor_name* CURSOR FOR select_statement

2. 光标OPEN语句

OPEN cursor_name

3. 光标FETCH语句

FETCH cursor_name INTO var_name [, var_name] ...

4. 光标CLOSE语句

CLOSE cursor_name

//流程控制的使用  不做介绍

3-3 调用存储过程

//定义存储过程

delimiter //

create procedure proc1(in name varchar(4),out num int)

begin

select count(*) into num from student where name=name;

end//

delimiter ;

//调用存储过程

call proc1("tom",@num) -- 查找名为tom学生人数

//查看结果

select @num;  -- 看下图

call proc1

//查看存储过程

show procedure status like 'p%' \G -- 获得以p开头的存储过程信息

//修改存储过程

alter {procedure|function} sp_name[characteristic...]

//删除存储过程

drop procedure proc1;

四、视图

   如何创建视图

   查看、修改、更新、删除视图

4-1、视图的基本操作

//在单表上创建视图,重新创建student表，插入数据

create table student(

 id int not null primary key auto_increment,

 name varchar(10) not null,

 math float,

 chinese float

);

insert into student(name,math,chinese) values

('howie1',66,77),

('howie2',66,77),

('howie3',66,77);

//开始创建视图

create view stu_view as select math,chinese,math+chinese from student;  -- 下图可看出创建成功

//也可以创建自定义字段名称的视图

create view stu_view2(math,chin,sum) as select math,chinese,math+chinese from student;

stu_view

stu_view2

//在多表上创建视图，创建表stu_info，插入数据

create table stu_info(

 id int not null primary key auto_increment,

 class varchar(10) not null,

 addr varchar(100)

);

insert into stu_info(class,addr) values

('1','anhui'),

('2','fujian'),

('3','guangdong');

//创建视图stu_class

create view stu_class(id,name,class) as

select student.id,student.name,stu_info.class from

student,stu_info where student.id=stu_info.id;

//查看视图

desc stu_class;

show table status like 'stu_class'\G

show create view stu_class\G

//修改视图

create or replace view stu_view as select * from student;

alter view stu_view as select chinese from student;

//更新视图

update stu_view set chinese=100;

insert into student values(null,'haha',100,100);

delete from stu_view2 where math=100;

//删除视图

drop view if exists stu_view2;

stu_class

五、总结

   笔记参考《MySql数据库入门》

   基本命令就这么多，仍需多多敲写巩固

   以上命令本人全部敲过，若有错误，敬请指出，希望有帮助，谢谢。