Mysql (二)Mysql 数据库表增删改查

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用系列 2核4GB
简介:

一、表操作DDL

1、【增加】

create table DBname.table_name(id int, namevarchar(255))engine=MyISAM charset=gbk;

 

2、【删除】

删除表

drop table table_name;


3、【修改】

修改表选项

alter table table_name engine=myisamcharset=utf8;//修改字符集

rename table Old_tablename to new_tablename;//修改表名


4、【查询】

show tables like '%_name'; //查看表

show create table table_name //查看建表语句

desc table_name //查看表结构 describe

show variables like 'character_set%';//展示以character_set开头变量

 

二、表字段操作

1、【增加】

增加表字段

alter table table_name add column 字段定义 //增加字段

alter table table_name add column heightint after name;//增加一个字height字段在name之后

alter table table_name add column snvarchar(10) first;//在最形始增加sn字段、使用关键字first

 

2、【删除】

删除字段

alter table table_name drop columncolumn_name;

alter table table_name drop columnheight;//删除表table_nameheight字段

 

3、【修改】

 修改已有字段(修改字段定义)

alter table table_name modify columncolumn_name 新的定义;

alter table table_name modify column sn intafter name; //修改sn字段

 

修改字段

alter table table_name change column 原字段名   新字段名  新字段定义;

alter table table_name change column snnew_sn varchar(30) after age;//修改字段

 

4、【查询】

查看表和表结构

show tables like '%_name'; //查看表

show create table table_name //查看建表语句

desc table_name //查看表结构 describe




三、表数据操作DML

 

1、【增加】

insert into 表名(字段列表) values(与字段相对的值列表);

insert into table_name('name','age','height')values(ssw,22,180);

 

2、【删除】

delete from 表名 where 条件;(删除是不可逆的)

delete 时、支持order by  limit来限制删除数据记录

delete from table_name where id>2;

delete from tb_student order  by height  desc  limit 3;

truncate tb_student;//清空表

 

3、【修改】

update 表名 set 字段=新值,字段n=新值n  where 条件;(也可用order by limit限制)

update table_name  set name='php' where id=3;

 

 

4、【查询】

select [字段表达式列表]  from 表名 [where 子句] [group by子句] [having 子句] [order by子句] [limit 子句]

select 字段列表 from 表名 [where 条件表达式] (*表示所有字段)

select * from table_name where id>6;

select name from table_name where id>2;

 

select 加法逻辑关系别名(as)//select 运算

select 1+1; select 10>20; select 1 or 0;select 1+1 as a;

select * from tb1,tb2;//多表查询

select tb1.id as s_id, tb2.* from tb1,tb2;//字段别名多表查询

select tb1.id  as  s_ id, s.class_name,  c.*  from tb1  as  s, tb2 as  c;//表别名

where子句、查询条件子句:

关系:> >= < <= != =

Like:模糊查询、like  ‘模式匹配符%_’;(%任意字符,_表示一个字符)

Notlike: like取反

Betweennum1 and num2: 在某个区间、闭区间。

In(元素列表): 在某个集合之内  

Notin(元素列表): 不在某个集合之内

Null判断、is null 或者is not null

事例:selsect * from tb1where class_name like ‘%22’;

selsect * from tb1 where class_name like ‘_22’;

select * from  tb1 where  id  between 11  and  40;

select * from  tb1 where  id >=11  and id<=40;

select * from  tb1 where  id  not  in(11,30);

select * from  tb1 where  id  in (11,20);

 

group  by 字段:分组查询

对查询结果(已经通过where子句过滤之后的数据),按照某个字段,进行分组!

 

合计函数:

count(): 统计记录数、可以是*和字段名

sum(字段表达式): 统让和、对某个字段求和、

avg(): 平均值

max(字段表达式):最大值

min(字段表达式):最小值

group_concat(字段表达式):组内连接字符串

select count(*),id  from  tb1 where   1  groub by   id;

select sum(money),class_id   from   tb1  groub  by   class_id;

select avg(money),class_id   from   tb1  groub  by   class_id;

select concat(‘It’ ,’is’,’test’);

 

分组排序:

ASC: 升序

DESC:降序

Select  count(*),class_id   from   tb1 group  by  class_id;

 

多字段分组:

Select   count(*),class_id,class_name  from   tb1   groub by  class_id,class_name;

Having子句:条件子句、功能和where类似

Select *  from  tb1 where  money>300;

Select *  from  tb1 having  money>300;

Havingwhere区别having的结果一定是 where 已经过滤之后的结果!having对结果进行二次处理

Select avg(age),class_id  from  tb1 where  1  group by class_id  having  avg(age)>16;

Order by 排序子句

Order by  字段名 [asc|desc],[字段名[asc|desc],]//对结果进行排序的语句!可对多个字段排序

Select * from  tb1 order  by  class_id desc,  age  asc;

原则是,先按照第一个字段进行排序,如果字段值相同,则采用第二个,以此类推

Select class_id  from  tb1 group  by  class_id asc  order  by class_id  desc;

Limit 子句:限制结果记录的子句、limit  start (起始位置), size(记录数);

Select *  from  tb1 limit  1,3;

执行顺序:字段表达式,from子句,where子句,group by子句,having子句,order by子句,limit子句

 

子查询语句:

select max(height)  from  tb1;

select *  from  tb1 where  height=170;

select *  from  tb1 where  height=(select  max(height) from  tb1);

 

子查询的两种分类依据:

依据子查询出现的位置!

where型子查询, 出现在where子句内!

from 型子查询, 出现在from子句内!

 

依据子查询的返回数据的格式!

标量子查询,返回值是一个数据,称之为标量子查询!

列子查询,返回一个列,

行子查询,返回一个行,

表子查询,返回的是一个二维表

Select * from tb1 where height=(selectmax(height) from tb1); //where

需要,先用一个查询,得到身高排序结果,再将该结果分组

Select* from (select * from tb1 order byheight desc) group by class_id;//from

Select* from (select * from tb1 order byheight desc) as tmp  group by class_id;

 

列子查询:

Select * from  tb1 where sex=’girl’  and  class_id in(select  class_id  from tb1  where sex=’body’ group byclass_id); //找到班级内有女同学的男学生信息

Select * from  tb1 where (height,money)=(selectmax(height),max(money)  from tb1);

//找到,高富,最高并且最有钱!

 

exists型子查询:

select * from tb1 where  exists(select * from tb2  where tb1.class_id = tb2.id);

 

连接查询,join:

连接多个表记录之间的连接!from 表名1  join        表名2  on 连接条件

Select  name,class_id,age  from  tb_xue join  tb_ban  on tb_xue.class_id=tb_ban.id;

需要不单从 学生表获取数据,还需要从 班级表获得数据

 

内连接,inner  join

mysql默认的连接就是 inner join

select stu_name,class_id,class_name fromselect_student inner join select_class onselect_student.class_id=select_class.id;(可省略inner)

 

外连接,left joinright join

Select stu_name,class_id,class_name from select_student left join select_classon select_student.class_id = select_class.id;

join关键字前面的(左边的)左表,join关键字后边的(右边的)右表!

左外:如果出现左表记录连接不上右表记录的,左表记录会出现正在最终的连接结果内!而右表记录相应设置成NULL

右外:如果出现右表记录连接不上左表记录的,右表记录会出现正在最终的连接结果内!而左表记录相应设置成NULL

交叉连接,cross join相当于 没有条件的内连接

自然连接,natural join: mysql,自动判断连接条件,帮助我们完成连接!

Select stu_name,class_name  from  select_class natural  join  select_student;

 

而自然连接也分内连接与外连接!

自然内连接:natural join

自然左外:natural left join

自然右外:natual right join

总结:

最终的效果只有:内,左外,右外!

交叉,特殊的内!

自然,相当于自动判断连接条件,完成内,左外,右外!

 

连接条件,onusing:

on,后面使用一个连接条件表达式!

using(连接字段),要求使用同名字段进行连接!

Select class_name,stu_name  from  tb_class inner  join  tb_student on tb_class.class_id=tb_student.class_id;

Select class_name,stu_name  from  tb_class inner  join tb_student  using(class_id);

 

union查询,联合查询:

多个查询的结果,并列到一个结果集合内!

(select stu_name,height  from  tb_student where  sex=’girl’  order by height asc limit  1000) union(select  stu_name ,height  from tb_student  where  sex=’box’ order by height  desc  limit 1000 );

union 的连接的两个子句,不要求实同表,只要求,列的数量相同!

union会在联合时:主动去掉相同的记录:此时,可以使用 all关键字加以修正:

select 1=1 union  all select 2;

select语句的选项:

distinct,取消相同的记录

select class_id  from  tb_student;

select all  class_id  from tb_student;

select distinct  class_id  from tb_student;

 

 

四、编码

1、建库,建表,建字段 设置(数据库中的数据的编码)

2PHP作为mysql服务器的客户端,设置的客户端编码和连接编码(set names gbk/utf8

3、设置php返回给浏览器数据的编码,(Content-Type,header(),<meta>

4PHP文件本身保存的编码(文件编码,通过文本编辑器设置)

 

 

五、视图

视图:就是通过一条查询语句得到一个张虚拟表!因此,视图就是 select语句的结果

作用:简化查询的业务逻辑,隐藏真实的表结构。

语法:create   view  视图名字  as   查询语句

Create view  view_name  as select  *  from tb_student  as  s left  join  tb_class using(class_id);

Select * from   view_name  where id=22;

//取得每个班级最高的学生信息

Create view  view_student  as select  *  from view_student  order  by height desc;

Select * from  view_student  group by  class_id;

 

六、事务

事务:一组 SQL 的集合,要不集体都执行成功,要不集体都失败,

语法:

开启事务:start  transaction (可简写begin)

提交:commit  (如果sql成功、则提交、将sql的执行结果保存到数据库里)

回滚:rollback (如果sql失败、则回滚、将sql的执行结果退到事务开始之前)

注:无论回滚还是提交,都会关闭该事务!(需要再次开启,才能使用)事务,只针对当前的连接生效!

事例:

Start transaction;

Update tb_student  set  money=money+1000  where id=10;

Commit;

 

 

七、数据库备份

1、备份单个表

select 字段列表into outfile文件地址 from 表名 where 其他的select子句

select *  into  outfile ‘d:/b.txt’ 

fields terminated  by ‘,’ 

lines terminated  by  ‘\r\n’

from tb_student;

还原:load  data  infile filename  into  tb_name;

 

2、备份一个库

Mysqldump -h127.0.0.1  -P3306  -uroot -p  > d:/data_back.sql;//备份库

Mysqldump -h127.0.0.1  -P3306 -uroot -p data_name  tb_studnet >d:/tb_back.sql;//备份表

 

3、还原数据库

Mysql -h127.0.0.1  -P3306  -uroot -p  data_name <d:/data_back.sql;

Mysql>source  d:/data_back.sql;(登录后还原)

 

3、忘记root密码

mysqld服务器程序,有一个选项,跳过权限认证选项!客户端登陆不用密码

第一步:

重新开启mysqld服务、以跳过权限认证的模式

Mysql/bin/mysqld.exe  --skip-grant-tables

第二步:

直接登录不用用户密码

Cmd>mysql

第三步:

更新mysql.userroot用户的password字段

Update mysql.user  set  password=password(‘123456’)  where user=’root’;

第四步:

重启mysqld

 

 

 

 

八、PHP操作mysql服务器

 

php作为mysql服务器的客服端

php--链接认认证--发送sql--执行sql、生成结果--处理结果--关闭连接

 

1、【连接认证】

$_host = 'localhost';

$_port = '3306';

$_user = 'root';

$_pass = 'rootpass';

if(!$_link = mysql_connect("$_host:$_port",$_user, $_pass)){

         die('连接失败!');

}

 

2、【向mysql服务器发送sql

$_sql = 'show databases';

if(!$_result = mysql_query($_sql, $_link)){

         echo'sql执行失败';

}

 

3、【处理返回的数据】

 

$_row = mysql_fetch_assoc($_result);

var_dump($_row);

 

结果集取出来的函数:

mysql_fetch_assoc() 函数从结果集中取得一行作为关联数组。

mysql_fetch_row() 函数从结果集中取得一行作为索引数组。

mysql_fetch_array() 函数从结果集中取得一行作为关联数组,或数字数组,或二者兼有

mysql_num_rows()函数返回结果集中行的数目。

 

注:任何有结果的sql操作,返回的都是结果集!结果集,就是一个二维表的结构!是一行行的记录组成!

 

 

4、【释放释源】

mysql_free_result($_result);结果集

mysql_close($_link);连接资源




本文转自 sswqzx 51CTO博客,原文链接:http://blog.51cto.com/sswqzx/1965991
相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
8天前
|
SQL 关系型数据库 MySQL
mysql 简单的sql语句,入门级增删改查
介绍MySQL中的基本SQL语句,包括数据的增删改查操作,使用示例和简单的数据表进行演示。
mysql 简单的sql语句,入门级增删改查
|
18天前
|
NoSQL 关系型数据库 MySQL
微服务架构下的数据库选择:MySQL、PostgreSQL 还是 NoSQL?
在微服务架构中,数据库的选择至关重要。不同类型的数据库适用于不同的需求和场景。在本文章中,我们将深入探讨传统的关系型数据库(如 MySQL 和 PostgreSQL)与现代 NoSQL 数据库的优劣势,并分析在微服务架构下的最佳实践。
|
20天前
|
存储 SQL 关系型数据库
使用MySQL Workbench进行数据库备份
【9月更文挑战第13天】以下是使用MySQL Workbench进行数据库备份的步骤:启动软件后,通过“Database”菜单中的“管理连接”选项配置并选择要备份的数据库。随后,选择“数据导出”,确认导出的数据库及格式(推荐SQL格式),设置存储路径,点击“开始导出”。完成后,可在指定路径找到备份文件,建议定期备份并存储于安全位置。
160 11
|
15天前
|
存储 SQL 关系型数据库
MySQL的安装&数据库的简单操作
本文介绍了数据库的基本概念及MySQL的安装配置。首先解释了数据库、数据库管理系统和SQL的概念,接着详细描述了MySQL的安装步骤及其全局配置文件my.ini的调整方法。文章还介绍了如何启动MySQL服务,包括配置环境变量和使用命令行的方法。最后,详细说明了数据库的各种操作,如创建、选择和删除数据库的SQL语句,并提供了实际操作示例。
58 13
MySQL的安装&数据库的简单操作
|
21天前
|
存储 SQL 关系型数据库
一篇文章搞懂MySQL的分库分表,从拆分场景、目标评估、拆分方案、不停机迁移、一致性补偿等方面详细阐述MySQL数据库的分库分表方案
MySQL如何进行分库分表、数据迁移?从相关概念、使用场景、拆分方式、分表字段选择、数据一致性校验等角度阐述MySQL数据库的分库分表方案。
一篇文章搞懂MySQL的分库分表,从拆分场景、目标评估、拆分方案、不停机迁移、一致性补偿等方面详细阐述MySQL数据库的分库分表方案
|
2天前
|
关系型数据库 Unix MySQL
MySQL是一种关系型数据库管理系统
MySQL是一种关系型数据库管理系统
10 2
|
5天前
|
Oracle NoSQL 关系型数据库
主流数据库对比:MySQL、PostgreSQL、Oracle和Redis的优缺点分析
主流数据库对比:MySQL、PostgreSQL、Oracle和Redis的优缺点分析
16 2
|
10天前
|
SQL 关系型数据库 MySQL
创建包含MySQL和SQLServer数据库所有字段类型的表的方法
创建一个既包含MySQL又包含SQL Server所有字段类型的表是一个复杂的任务,需要仔细地比较和转换数据类型。通过上述方法,可以在两个数据库系统之间建立起相互兼容的数据结构,为数据迁移和同步提供便利。这一过程不仅要考虑数据类型的直接对应,还要注意特定数据类型在不同系统中的表现差异,确保数据的一致性和完整性。
22 4
|
8天前
|
SQL 关系型数据库 MySQL
ThinkPHP6 连接使用数据库,增删改查,find,select,save,insert,insertAll,insertGetId,delete,update方法的用法
本文介绍了在ThinkPHP6框架中如何连接和使用数据库进行增删改查操作。内容包括配置数据库连接信息、使用Db类进行原生MySQL查询、find方法查询单个数据、select方法查询数据集、save方法添加数据、insertAll方法批量添加数据、insertGetId方法添加数据并返回自增主键、delete方法删除数据和update方法更新数据。此外,还说明了如何通过数据库配置文件进行数据库连接信息的配置,并强调了在使用Db类时需要先将其引入。
ThinkPHP6 连接使用数据库,增删改查,find,select,save,insert,insertAll,insertGetId,delete,update方法的用法
|
19天前
|
SQL 监控 关系型数据库
MySQL数据库中如何检查一条SQL语句是否被回滚
检查MySQL中的SQL语句是否被回滚需要综合使用日志分析、事务状态监控和事务控制语句。理解和应用这些工具和命令,可以有效地管理和验证数据库事务的执行情况,确保数据的一致性和系统的稳定性。此外,熟悉事务的ACID属性和正确设置事务隔离级别对于预防数据问题和解决事务冲突同样重要。
30 2

热门文章

最新文章

下一篇
无影云桌面