mysql 插查改删语句 – 开源达人博客

发表于2010年12月29日2019年12月29日作者 jiukeshuju

多表查询内连接外连接

内连接: 只连接匹配的行

左外连接: 包含左边表的全部行（不管右边的表中是否存在与它们匹配的行），以及右边表中全部匹配的行
右外连接: 包含右边表的全部行（不管左边的表中是否存在与它们匹配的行），以及左边表中全部匹配的行
全外连接: 包含左、右两个表的全部行，不管另外一边的表中是否存在与它们匹配的行。

交叉连接生成笛卡尔积－它不使用任何匹配或者选取条件，而是直接将一个数据源中的每个行与另一个数据源的每个行都一一匹配

举个例子吧。

表A 表B
id name id address A_id
1 张 1 北京 1
2 李 2 上海 3

3 王 3 南京 10

表B
id address A_id
1 北京 1
2 上海 3
3 南京 10

/*
Navicat MySQL Data Transfer

Source Server : localhost_3306
Source Server Version : 50153
Source Host : localhost:3306
Source Database : lianjie

Target Server Type : MYSQL
Target Server Version : 50153
File Encoding : 65001

Date: 2015-08-12 22:18:59
*/

SET FOREIGN_KEY_CHECKS=0;

— Table structure for tp_aaa

DROP TABLE IF EXISTS tp_aaa;
CREATE TABLE tp_aaa (
id int(10) NOT NULL AUTO_INCREMENT,
name varchar(30) CHARACTER SET utf8 DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

— Records of tp_aaa

INSERT INTO tp_aaa VALUES (‘1’, ‘张’);
INSERT INTO tp_aaa VALUES (‘2’, ‘李’);
INSERT INTO tp_aaa VALUES (‘3’, ‘王’);

— Table structure for tp_bbb

DROP TABLE IF EXISTS tp_bbb;
CREATE TABLE tp_bbb (
id int(10) NOT NULL AUTO_INCREMENT,
address varchar(30) CHARACTER SET utf8 DEFAULT NULL,
a_id int(10) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

— Records of tp_bbb

INSERT INTO tp_bbb VALUES (‘1’, ‘北京’, ‘1’);
INSERT INTO tp_bbb VALUES (‘2’, ‘上海’, ‘3’);
INSERT INTO tp_bbb VALUES (‘3’, ‘南京’, ’10’);
INSERT INTO tp_bbb VALUES (‘4’, null, null);

/* left join 左连接左表是全的**/

包容性: tp_aaa 表包容 tp_bbb 表,左连接左表是全的.(left join 或 left outer join )
SQL语句如下：

SELECT tp_aaa.name, tp_bbb.address
FROM tp_aaa
LEFT JOIN tp_bbb
ON tp_aaa.id=tp_bbb.a_id

SELECT tp_aaa.name, tp_bbb.address //查询 tp_aaa表.name字段,tp_bbb表address字段
FROM tp_aaa //从 tp_aaa表
LEFT JOIN tp_bbb //连接 tp_bbb表
ON tp_aaa.id=tp_bbb.a_id //条件 tp_aaa.id=tp_bbb.a_id

根据tp_aaa表中的id 查询tp_bbb表中的a_id 输出内容!

查询结果为：
name address
张北京
李 null
王上海

注: 左连接左表是全的

/* right join 右连接右表是全的**/

包容性:B表包容A表,右连接右表是全的.(right join 或 right outer join )

SQL语句如下：

SELECT tp_aaa.name, tp_bbb.address
FROM tp_aaa
RIGHT JOIN tp_bbb
ON tp_aaa.id=tp_bbb.a_id

查询结果为：

name address
张北京
王上海
null 南京
null null

注：右连接右表是全的.

/* inner join 内连接排他性两表的交集**/

排他性:A,B表中至少有1个匹配时，才返回行。两表的交集

SQL语句如下：

SELECT tp_aaa.name, tp_bbb.address
FROM tp_aaa
inner join tp_bbb
ON tp_aaa.id=tp_bbb.a_id

等价

SELECT tp_aaa.name, tp_bbb.address
FROM tp_aaa, tp_bbb
WHERE tp_aaa.id = tp_bbb.A_id

查询结果为：
name address
张北京
王上海

注: 排他性:A,B表中至少有1个匹配时，才返回行。两表的交集

/* full join 全连接**/
注释:全外连接返回参与连接的两个数据集合中的全部数据，无论它们是否具有与之相匹配的行。在功能上，它等价于

对这两个数据集合分别进行左外连接和右外连接，然后再使用消去重复行的并操作将上述两个结果集合并为一个结果集。

(full join 或 full outer join )

SQL语句如下：

select * from tp_aaa
full join tp_bbb

查询结果为：
id name id address A_id
1 张 1 北京 1
2 李 1 北京 1
3 王 1 北京 1
1 张 2 上海 3
2 李 2 上海 3
3 王 2 上海 3
1 张 3 南京 10
2 李 3 南京 10
3 王 3 南京 10
1 张 4 null null
2 李 4 null null
3 王 4 null null

/* CROSS JOIN(不带条件where…)****/

注释：返回3*3=9条记录，即笛卡尔积

SQL语句如下：

select * from tp_aaa
CROSS JOIN tp_bbb

select a.name,a.sex,b.name,b.age
from a
cross join b
on a.name=b.name

SELECT jiuke_portal_category.id,jiuke_portal_post.id
FROM jiuke_portal_category
CROSS JOIN jiuke_portal_post
ON jiuke_portal_category.id=jiuke_portal_post.id

CROSS JOIN等价于: select * from tp_aaa,tp_bbb

注意:

on A.id = B.id 等同于 using(id)//这里字段名要相同
当 MySQL 在从一个表中检索信息时，你可以提示它选择了哪一个索引。

如果 EXPLAIN 显示 MySQL 使用了可能的索引列表中错误的索引，这个特性将是很有用的。

通过指定 USE INDEX (key_list)，你可以告诉 MySQL 使用可能的索引中最合适的一个索引在表中查找记录行。

可选的二选一句法 IGNORE INDEX (key_list) 可被用于告诉 MySQL 不使用特定的索引。

效率问题:
1.inner join比left join快
注:inner join 内连接等价于下面的sql: SELECT A.name, B.address FROM A, B WHERE A.id = B.A_id
所以一般要用一般的连接就可以了.
2.连接字段建索引

三张表 sql多对多关系的两表如何联合查询出所有的结果？

1 project表 (projectId和projectName两个字段)
2 contract表 (contractId和contractName)
3 contract_project中间表 (记录每个projectId和contractId之间的关系)

现在根据projectName或者projectId查询出所有的projectId,projectName,contractId,contractName信息，sql语句该怎么写？

project表 (projectId和projectName两个字段) —> b
contract表 (contractId和contractName) —> c
contract_project中间表 (记录每个projectId和contractId之间的关系) —> a

select *from contract_project a

left join project b
on a.projectId = b.projectId

left join contract c
on a.contractId = c.contractId

select *from contract_project(中间表) a(别名)

left join project(b表) b(别名)
on a表.projectId = b表.projectId

left join contract(c表) c(别名)
on a表.contractId = c表.contractId

=============================================================================================================

==== 1 sql左连接左表是全的: left join 左连接左表是全的
包容性: tp_aaa 表包容 tp_bbb 表,左连接左表是全的.(left join 或 left outer join )

SELECT jiuke_portal_category.id, jiuke_portal_category_post.category_id
FROM jiuke_portal_category
LEFT JOIN jiuke_portal_category_post
ON jiuke_portal_category.id=jiuke_portal_category_post.category_id;

==== 2 sql右连接右表是全的: right join 右连接右表是全的
包容性:B表包容A表,右连接右表是全的.(right join 或 right outer join )

SELECT jiuke_portal_category.id,jiuke_portal_category_post.category_id
FROM jiuke_portal_category
RIGHT JOIN jiuke_portal_category_post
ON jiuke_portal_category.id=jiuke_portal_category_post.category_id

==== 3 sql内连接: inner join 内连接排他性两表的交集
排他性:A,B表中至少有1个匹配时，才返回行。两表的交集

SELECT jiuke_portal_category.id,jiuke_portal_category_post.post_id
FROM jiuke_portal_category
INNER JOIN jiuke_portal_category_post
ON jiuke_portal_category.id=jiuke_portal_category_post.post_id

等价

SELECT tp_aaa.name, tp_bbb.address
FROM tp_aaa, tp_bbb
WHERE tp_aaa.id = tp_bbb.A_id

==== 4 sql全连接: full join 全连接 (全所有字段都连接在一起)
全外连接返回参与连接的两个数据集合中的全部数据，无论它们是否具有与之相匹配的行。在功能上，它等价于
对这两个数据集合分别进行左外连接和右外连接，然后再使用消去重复行的并操作将上述两个结果集合并为一个结果集

select a.name,a.sex,b.name,b.age
from a
cross join b
on a.name=b.name

SELECT jiuke_portal_category.id,jiuke_portal_post.id
FROM jiuke_portal_category
CROSS JOIN jiuke_portal_post
ON jiuke_portal_category.id=jiuke_portal_post.id

==== 5 三张表 sql多对多关系的两表如何联合查询出所有的结果？

select *from contract_project(中间表) a(别名)

left join project(b表) b(别名)
on a表.projectId = b表.projectId

left join contract(c表) c(别名)
on a表.contractId = c表.contractId

SELECT * FROM jiuke_portal_category_post a

LEFT JOIN jiuke_portal_category b
ON a.category_id=b.id

LEFT JOIN jiuke_portal_post c
ON a.post_id=c.id

==== 6 sql 增删改插
增加： INSERT INTO ‘tb_name’ (字段)values(值);
删除： DELETE FROM ‘tb_name’ WHERE id=’1′;
DELETE FROM ‘tb_name’ ORDER BY ‘id’ desc或asc LIMIT 5;
修改: UPDATE ‘tb_name’ SET 字段=’值’ WHERE id=’1′;
UPDATE ‘tb_name’ SET 字段=’值’ ORDER BY ‘id’ desc或asc limit 5;

UPDATE wpg3_posts SET post_content = ‘1’ WHERE ID = 126 AND ID < 143;

SELECT * FROM wpxa_posts WHERE post_content LIKE ‘%<script’

插入：insert into ‘tb_name'(字段)values(值);
修改：update ‘tb_name’ set 字段=’值’ where id=’1′;
update ‘tb_name’ set 字段=’值’ order by ‘id’ desc或asc limit 5;
删除：delete from ‘tb_name’ where id=’1′;

delete from ‘tb_name’ order by ‘id’ desc或asc limit 5;

查询：
1 查询所有字段
select * from ‘tb_name’;
2 查询特定字段
select ‘字段1′,’字段2′,’字段3’ from ‘tb_name’;
3 查询as别名
select ‘字段1’ as ‘别名1′,’字段2’ as ‘别名2′,’字段3’ as ‘别名3’ from ‘tb_name’;
4 查询distinct重复数据
select distinct ‘字段’ from ‘tb_name’;
5 查询表达式列查询
select id ‘数号’,title ‘标题’,price0.8 ‘价格’ from ‘tb_name’; 6 查询条件查询 select id,title,price from ‘tb_name’ where price >=10; 7 查询is null或is not null查询 select id,title,price from ‘tb_name’ where title is not null; 8 查询比较查询 select id,title,price from ‘tb_name’ where price >= 10 and price <= 30; 9 查询in()或not in()范围 select id from ‘tb_name’ where id in(‘1’); 10 查询模糊like select id,title from ‘tb_name’ where title like’%高%’; 11 多表查询 select c.id ‘数号’, b.title ‘标题’ from cate c, name b; select c.id ‘数号’, b.title ‘标题’ from cate c, name b where c.id=b.id; select c1.catname ‘父类’,c2.catname ‘子类’ from catname c1, catname c2 where c2.id=c2.id;//自身连接 select id,name,price from ‘tb_name’ where name in(select bookname from ‘cates’ where userid=’1′); //嵌套查询 12 查询排序 desc降序 asc升序 select id,name,price from ‘book’ order by id desc; 13 查询limit 限定行数 select id,name,price from ‘book’ order by desc limit 0,5; 14 查询统计数量 count()总条数 sum()总和 avg()平均值 max()最大值 min()最小值 select count() ‘记录行数’ from ‘tb_name’;
15 查询分组
select avg(*) ‘平均值’,sum(price) ‘总价格’ from ‘book’;

增删改查：

一.操作数据表中的数据记录
1,使用INSERT语句[添加]
语法格式：
insert into 表名([字段列表]) values(值列表1),(值列表2),(值列表3),(值列表4)

INSERT INTO cats(pid,catname,catdsn)values(‘0′,’英语类’,’存放’);

2,使用UPDATE语句[更新]
update 表名 set 字段=’值’,字段=’值’,字段=’值’,字段=’值’ [where条件] [order by字段] [limit 行数]
语法格式：
update 表名 set 字段=’值’,字段=’值’,字段=’值’,字段=’值’ [where 条件] [order by 字段] [limit 行数]

UPDATE books SET catid=’3′ ORDER BY bookid DESC LIMIT 5;
UPDATE books SET price=price*0.8 WHERE catid=’1′;
UPDATE books SET catid=2, bookname=’RedHat Linux’, author=’高某某’, price=’50.00′ WHERE bookid=’3′;
update 表名 set num=num+1 where id=27; 数据库执行+1
update 表 set num=77 where id in(5,10,15,25);

3,使用DELETE语句[删除]
语法格式：
delete from 表名 [where条件] [order by字段] [limit 行数]

DELETE FROM books WHERE bookid=’2′;
delete from 表 where id in(5,10,15,25);

4,使用SELECT语句[查询]
语法格式：
select ALL或DISTINCT 关键字所有和不重复

或 table.* 表.* 或 table.字段或 as name别名或字段
FROM 表名
WHERE 条件
GROUP BY 分组
HAVING 分组给条件
ORDER BY 按什么排序
LIMIT coun 取多少个

select * from 表名;
select 表名.* from 字段;
select name,price from 表名;
select price as bookprice, name as bookname from 表名；
select price bookprice, name bookname from 表名;
select distinct关键字不重复 price bookprice, name bookname from 表名;
select distinct关键字不重复 price from 表名; 只查价格
select 98; select name,price,price0.8 dazprice from 表;
select name,price,price0.8 dazprice from 表 where id>4 && id<5; select version<>,1.2310;
select version() mysql_version,1.23*10 as expression;
select * from 表 where price <100 ; select * from 表 where price <100 and >50;
select * from 表 where id=10;
select * from 表 where id!=10;
select * from 表 where desn is null;
select * from 表 where desn not null;
select * from 表 where desn <=>’good’; 和等号一样。可以用于空值比较；
select * from 表 where id>10 && id<20;
select * from 表 where id between 10 and 20;
select * from 表 where id not between 10 and 20;
select * from 表 where id in(5,10,15,25);
select * from 表 where name like ‘java__’; 单一字符
select * from 表 where name like ‘java____’;
select * from 表 where name like ‘j%’; 多个字符
select * from 表 where name like ‘%java%’;多个字符
select * from 表 wehre name not like ‘%two%’; 不包括
select * from 表 where name=’a’;
select * from 表 where name regexp ‘^j’; 以j开头

5,多表查询，连接查询；非等值和等值两种。
select pid,price字段 from 表1,表2,表3;
select pid,price,cats.name,producs.name,products.price from 表;
select c.name表别名.字段名,c.desn,p.name,p.price from cats c,products as p;

select * from cats;
select * from products where pid in(select id from cats wherer name like ‘j%’);嵌套查询子查询

select * from products表;
select * from 表 order by id asc ; 正序不写
select * from 表 order by name desc 从高到低倒序;
select * from 表 where id>5 order by name desc;

select * from 表 where id<10 order by id desc limit 5; select * from 表 where id=14; select * from 表 where id>15 order by id asc limit 0,1; 上一篇
select * from 表 where id<14 order by id desc limit 0,1;下一篇 select * from 表 where id>20 order by id desc limit 0,1;下一篇

count()总数 sum()总合 avg()平均值 max()最大值 min()最小值
select count(*) from 表;
select count(price),sum(price),avg(price),max(price),mix(price) from 表;
select count(price),sum(price),avg(price),max(price),mix(price) from 表 group by cid;
select count(price),sum(price),avg(price),max(price),mix(price) from 表 group by cid having avg(price)>50; having 分组后的从句

mysql中的内置系统函数：字符串函数，数值函数，日期函数 , 流程控制函数
用在select语句及子句where order by having 中 update delete

字符串函数:
1 concat(s1,s2,s3….) 把传入的参数连接成字符串
2 insert(str,x,y,insert)将字符串从x位置开始,y个字符串长度替换为insert
3 lower(str) upper(str) 将小写转为大写。
4 left(str,x) right(str,x) 分别返回最左X个字符，最右边的X个字符
5 lpad(str,n,pad),rpad(str,n,pad) 用字符串PAD对str最左边和最右边进行填充，直到N个字符长度为止。
6 trim(str) ltrim(str) rtrim(str) 去掉字符串，两边，左边，右边空格。
7 replace(str,a,b) 用字符串，b替换str中的所有a;
8 strcmp(str1,str2) 如果str1比str2小，返回-1,如果相反，则返回+1，如果str1=str2 返回0
9 substring(str,x,y) 返回字符串中的第x位置起的y个字符串长度的字符串。截取。

数值函数:
ABS(X); 返回X 的绝对值
ceil(x); 返回大于x的最小整数 2.1 2.5 2.9 返回3
floor(x); 返回小于x的最大整数 2.1 2.5 2.9 返回2
mod(x,y);返回x/y;
rand() 0-1之间的随机.
round(x,y) 返回参数x的4舍五入的有y位小数的值
truncate(x,y) 返回数字X截断为y位小数的结果

日期函数：
cordate();返回当前的日期。
curtimae();
now();
unix_timestamp(date);
from_unixtime
week();
year();
hour();
minute();

流程控制函数;
if(value,t f)
ifnull(value1,value2)如果value1不为空，则返value2
case wher [value1] then[result1] … else[default]end;
case when …then

其它函数;
database() 数据库名
version()版本
user() 用户名
inet_aton(ip)返回IP
inet_ntoa()
password()

md5();

select 1+1;
select concat(‘abc’,”xyz”); 连接在一起。
select concat(“acb”,”xyt”,”ccc”,”adsfa”);
select concat(name,”age is”,age) from users; 整列连接

select insert(“abcdefg”,2,3,’hello’);
select insert(name,3,2,’oo’) from users;

select lower(“HELLO”),upper(“hello”); 大写转小写。小写转大写。
select * from users where upper(name)=’AAA’;

select left(“aaaaaaaa”,3),right(“abcdefg”,3),left(“aaaaaaaa”,NULL);

select name,lpad(name,10,”#”),rpad(name,10,”@”) from users; 填补

select trim(” abc “),ltrim(” abc “), rtrim(” abc “);
select concat(‘1′,trim(” abc “),’2’),concat(‘1′,ltrim(” abc “),’2’), concat(‘1′,rtrim(” abc “),’2’) \G; \G横起来

select replace(“abadslfjdsajfkjkldsaf”,”d”,”xx”); 替换

select strcmp(“a”,”b”),strcmp(“b”,”a”),strcmp(“a”,”a”);比较

select substring(“abcdejfakdkfsjf”,3,5); 从第3个，到第5个取。

select substring(name,1,2) from user;从第3个，到第5个取。

select abs(10),abs(-10);
select abs(ags) from 表;

select ceilsmarty
↑
MYTPL ->改版smarty 初始化
↑
Action ->常用操作页面跳转操作 login.class.php login extends Action
↑
Common ->权限验证共用操作
↑

[index user aritile等]

//http://www.brophp.com/入口文件/模块名/操作名/参数1/值1 #URL统一解析和调度的PATHINFO模式

主控制器调用模块的方法：
login.class.php
function login(){
$user=D(“user”);
$user->pub_login(); //调用模块下的 user.class.php 中的 function pub_login(){}
}
//$album=D(“album”);
//$this->assign(“select”, $album->formselect()); //模块中类的方法formselect()

//$this->display();

admin/controls

common.class.php
redirect(“login/index”);
}
$this->assign(‘session’,$_SESSION);
}
}

?>

class login extends Action {

function index() {
    $this->display();
}
function pro(){
    if($_POST['username'] == '' || $_POST['password']

2024年12月
一	二	三	四	五	六	日
« 11月
						1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28	29
30	31

举个例子吧。

3 王 3 南京 10

delete from ‘tb_name’ order by ‘id’ desc或asc limit 5;

md5();

select substring(name,1,2) from user;从第3个，到第5个取。

//$this->display();

?>

发表评论 取消回复

发表评论取消回复