操作Mysql数据库的基本步骤

发布时间：2023-09-06 14:21:58 所属栏目：MySql教程来源：

导读：一、MySql数据库表操作

1、操作数据库

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

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

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

一、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 新表名;

查询表：

操作Mysql数据库的基本流程

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; 就会报错不允许改因为做了限制

（编辑：聊城站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!