操作Mysql数据库的基本流程

本文主要给大家简单讲讲操作Mysql数据库的基本流程，相关专业术语大家可以上网查查或者找一些相关书籍补充一下，这里就不涉猎了，直奔主题，希望可以给大家带来一些实际帮助。

一、MySql数据库表操作

1、操作数据库

创建数据库：create database 数据库名;

删除数据库：drop database 数据库名;

修改数据库编码格式：alter database 数据库名 character set 要修改的字符集名称;

查询数据库：show databases; 查看所有数据库名称

选择数据库：use 数据库名;

2、操作表

创建表：create table 表名(

列名1 数据类型1,

列名2 数据类型2,...

);

常用列的数据类型：int double char varchar(字节长度) date:yyyy-MM-dd time: hh:mm:ss datetime: yyyy-MM-dd hh:mm:ss

删除表：drop table 表名;

修改表：

添加列：alter table 表名 add 列名 数据类型;

修改列数据类型：alter table 表名 modify 列名 数据类型;

修改列名：alter table 表名 change 旧表名 新表名 数据类型;

删除列：alter table 表名 drop 列名;

修改表名称：alter table 旧表名 rename to 新表名;

查询表：

show tables; 查询数据库下所有的表的名称

desc 表名; 查询该表的表结构

3、操作表中数据

增加数据：

insert into 表名(列名1,列名2,...) values(值1,值2,....);

insert into 表名 values(按顺序给出所有列对应的值);

删除数据：

delete from 表名 where 限定条件;

delete from 表名; -- 删除全部无法删除自增长约束，可以回滚,而truncate table不可以回滚

truncate table 表名; -- 删除全部重置自增长约束，只能删除全表数据,不能删除单个数据

修改数据：

update 表名 set 列名1 = 值1 , 列名2 = 值2... where 限定条件;

基本查询数据：

select * from 表名; 查看所有列

select 列名1,列名2,...from 表名; 查看所需列

select 列名1 as '别名1',列名2 as '别名2',...from 表名; 查看所需列并给列起别名

select (列名1 + 列名2) as '别名' from 表名; 

-- 和并列的时候,两个列的字段要类型一致;null参与的运算，结果都为null;ifnull 字段名称 会将null替换为0

4、表数据查询

1)where限制条件查询

=、!=、<>(不等于)、<、<=、>、>=;

between...and 在什么范围之间

IN(set)；

is null 为空

is not null 不为空

and 并且

or 或者

not 非

2)模糊查询 like

通配符

_ 匹配单个任意字符

% 匹配对个任意字符

select * from 表名 where 列名 like '通配符组合'; 

3)去除重复记录 distinct

select distinct 列名 from 表名;

4)排序 order by ASC 默认升序排列 DESC 降序排列

select * from 表名 order by 列名 asc;

select * from 表名 order by 列名1 desc,列名2 desc;

5)聚合函数 ———— 纵向运算

count(): select count(* 或者不含null值的列名) from 表名;

max(): select max(列名) as '最大值' from 表名;

min(): select min(列名) as '最小值' from 表名;

sum(): select sum(列名) as '和' from 表名;

avg(): select avg(列名) as '平均值' from 表名;

6)分组查询 ———— 配合聚合函数使用 查出的数据才有意义

select 列名1 聚合函数 from 表名 where 限定条件 group by 列名1; 按列名1分组

select 列名1 聚合函数 from 表名 group by 列名1 having 限定条件;

where：在分组之前对条件进行限定 不满足条件，就不会参与分组

having：在分组之后，对结果集的筛选

7)分页查询

select * from 表名 limit 开始的记录索引,每一页显示的条数;

开始的记录索引 = (页码-1)*每一页显示的条数

* oracle：rownum 分页方言

* sqlserver：top 分页方言

5、约束

1)主键约束(primary key)

被修饰的字段唯一且非空;一张表只能有一个主键,这个主键可以包含多个字段

格式一：

create table 表名(

字段名称 字段类型 primary key

);

格式二：

create table 表名(

字段名称 字段类型;

primary key(字段名称)

);

格式三：

alter table 表名 add primary key(字段名1,字段名2..);

2)唯一约束(unique) ———— 被修饰过的字段唯一,对null不起作用

3)非空约束(not null) ———— 被修饰过的字段非空

4)外键约束(foreign key)

1)自增长约束 auto_increment

1.被修饰的字段类型支持自增. 一般int

2.被修饰的字段必须是一个key 一般是primary key

2)在多表的一方添加外键约束

alter table 外表名称 add foreign key(外键字段名称) references 主表名称(主键字段名称);

6、多表查询

1)内连接 a表，b表的连接

格式1:显式的内连接

select a.*,b.* from a [inner] join b on ab的连接条件;

格式2:隐式的内连接

select a.*,b.* from a,b where ab的连接条件;

2)外连接

格式1:左外连接

select a.*,b.* from a left [outer] join b on 连接条件;

先展示join左边的(a)表的所有数据,根据条件关联查询 join右边的表(b),符合条件则展示出来,不符合以null值展示.

格式2:右外连接

select a.*,b.* from b right [outer] join a on 连接条件;

先展示jion右边的表(a)表的所有数据,根据条件关联查询join左边的表(b),符合条件则展示出来,不符合以null值展示.

3)子查询 ———— 一个查询依赖另一个查询

例：SELECT emp_fname,emp_lname FROM employee WHERE emp_no = (SELECT emp_no FROM works_on WHERE enter_date = '1998-01-04 00:00:00');

select 列名 from 表名 where 列名 = (select 列名 from 表名 where 限定条件);

二、储存过程 ———— 带有逻辑的sql语句

1、特点

1)执行效率非常快，储存过程是在数据库的服务器端执行的

2)移植性很差，不同数据库的储存过程是不能移植的

2、语法

创建储存过程

delimiter $

create procedure test(in 输入参数名 数据类型，out 返回值名 数据类型) 

-- 也可以没有参数和返回值

begin

-- 多条需要执行的sql语句

if 输入参数名 = 值1 then

set 返回值名 = '返回值1';

if 输入参数名 = 值2 then

set 返回值名 = '返回值2';

else

set 返回值名 = '返回值3';

end if;

end $

调用存储过程：

call tset(输入参数值,@返回值名);

select @返回值名;

3、MySQL中的变量

1)全局变量(内置变量)：作用域整个链接

-- 查看所有全局变量；show variables;

-- 查看某个全局变量：select @@变量名;

-- 修改全局变量：set 

set @@character_set_client=gbk; 设置服务器端口接收的编码

set @@character_set_results=utf8; 设置显示的编码

2)会话变量：只存在当前一次连接中 如果这次链接断掉 会话变量就消失了

-- 定义会话变量：set @变量 = 值;

-- 查看会话变量：select @变量;

3)局部变量：比如存储过程中的变量 作用域在 begin和end之间

4、begin end间的逻辑sql语句

1)条件判断

if ..then ..set..;

else set..;

end if;

2)循环

whlie 变量名 判断符 值 do

set..;

set 变量名=变量名+1; -- 注意这块没有i++的说法

end whlie;

5、删除储存过程

drop procedure 储存过程名;

三、触发器 ———— 当一个操作发生时会触发另一个行为

创建触发器

create trigger 起个触发器名字 after增/改/删 on要增改删的表名 for each row

insert into 要记录的表名(字段名)values(‘增加了一条记录’);

//after insert on 添加

//after update on 修改

//after delete on 删除

例：

delimiter $

create trigger banji after insert on user for each row

begin

insert into loger values(NULL,'你增加了一条记录',NULL);

end $

insert into user values(NULL,'张三');

四、MySql中的函数

1、系统函数

直接调用即可.任何函数都有返回值,函数的调用是通过select调用.因为有返回值,所以只能用select 调用

1)截取字符串

substring(原字符串,起始位置,截取长度); mysql 中下标从1开始算

2)字符字节长度

set @username='你好世界';

char_length('@username') 字符长度  4 个

length('@username')  字节长度 12个

3)查找字符所在字符串的索引，没有返回0

select instr(@username,'好'); 返回2

4)按照指定长度填充指定字符串

lpad (@username,10,'要填充的字符') 左填充

例如: select lpad(@username,10,'欢迎'); 注意10个长度算上原来'你好世界'的这4个长度,也就是说欢迎填充了6个

rpad 右填充

5)insert 替换字符串

例如: select insert(@username,2,2,'哈哈'); 从第二个字符开始替换两个字符 替换成哈哈

6)strcmp(字符串1,字符串2) 比较两个字符串大小 按照字典顺序去比较

2、自定义函数

1)创建自定义函数

create function 函数名(参数列表可有可无) returns 数据类型  --规定要返回的数据类型

begin

函数体

返回值:return 返回类型(我们指定的类型);

end

2)使用函数

create function show() returns int

return 100;

-- 调用函数

select show();

-- 查看所有函数 show functions status;

-- 查看创建函数  show create function test;

-- 删除函数  drop function test;

3)例：

delimiter $

create function delById(uid int) returns int

begin

delete form user where id = uid;

return (select count(*) from user);

end $

select deById(7);

五、视图

1、概述：有结构的虚拟表，虚拟表的结构来源不是自己定义,而是从对应的基表中产生

视图可以更好的进行权限控制 比如对外隐藏我的一些基表的名称

2、创建视图

create view 视图名称 as select语句;

create view my_v1 as select * from student;

create view my_v2 as select a.字段名,b.字段名 from a,b where a.id=b.id;

3、查看视图

关于查询表的语句对于视图都是可以用的 除过show create view my_v1;

视图的执行:其实本质就是执行封装的select 语句

4、删除视图：drop view 视图名称

5、修改视图，其实是对原表的修改 alter view 视图名字 as 新的select语句

6、视图数据的操作

视图插入数据:

(1)多表视图不能插入数据

(2)单表视图中可以插入数据(如果视图中字段没有基表中不能为空的字段且没有默认值的字段,是插入不成功的)

(3)视图是可以向基表中插入数据的 (视图的操作是影响基表的)

视图删除数据

(1):多表视图不能删除数据

(2):单表视图可以删除数据,也会影响到基表

视图更新数据

单表视图,多表视图都可以更新数据

    更新限制:with check option

    例如:create view my_v1 as select * from student where age>30 with check option;

    表示视图数据的来源都是年龄大于30的,with check option 决定通过视图更新的时候,不能将已得到

    数据age>30的学生 改成age<30 的.

    那么:update  my_v1 set age=20 where id=1; 就会报错 不允许改 因为做了限制

操作Mysql数据库的基本流程就先给大家讲到这里，对于其它相关问题大家想要了解的可以持续关注我们的行业资讯。我们的板块内容每天都会捕捉一些行业新闻及专业知识分享给大家的。