MySql关联查询
1、交叉连接(笛卡尔积)–cross join
语法:select * from t1 cross join t2 [on][where];
用法:如果t1和t2都有10条数据,则查询结果为10*10=100条数据。
2、内连接–inner join
语法:select * from t1 (inner) join t2 [on];
用法:inner可以省略,不加on时用法和交叉连接一样,加on过后对数据进行筛选。内连接是取左连接和右连接的交集。
内部有联系,使用内连接
格式: [inner] join ----inner可省
结果:
从左表中取出每一条记录,去右表中与所有的记录进行匹配;
匹配必须是某个条件是左表中与右表中相同,才会保留结果,否则不保留;
基本语法:
左表 [inner] join 右表
on 左表.字段 = 右表.字段;
注意事项:
1、on 表示连接条件;条件字段代表相同的业务含义
(如 stu.cid 和 cla.id)
学生stu表中的班级cid 和 班级cla表中的id
2、内连接可以没有连接条件,没有on之后的内容,
这时候系统会保留所有结果(笛卡尔集)
3、内连接可以使用where 代替 on
通常不用where,因为where没有on效率高
on指匹配到第一条成功的就结束,其他不匹配;
若没有,不进行匹配
而where会一直匹配,进行判断
4、通常使用字段别名 表别名
在查询数据的时候,不同表有同名字段,
这时候需要加上表名才能区分,
而若表名太长,使用表别名
若不想使用默认的字段名 可以通过[as] 设置字段别名
3、外连接–left/right join on
语法:select * from t1 left/right join t2 [on][where];
用法:左连接以左表为基准,on对右表进行筛选,没有数据与之匹配时用null填充。同理,右连接以右表为基准,on对左边的表进行筛选,不满足条件用null填充。where中的条件对所有表进行筛选。
4、自连接查询
语法:select a.name,b.name from t1 a left join t1 b on a.id=b.id;
用法:参考文章
5、联合查询–union
语法:
1、select * from t1 union select * from t2;
2、select * from t1 union all select * from t2;
用法:将多次查询(多条select语句), 在记录上进行拼接(字段不会增加),联合查询只要求字段一样,跟数据类型无关。union all:保留所有(不管重复),union:去重(整个重复)默认的。
联合查询意义:
- 查询同一张表,但是需求不同: 如查询学生信息, 男生身高升序, 女生身高降序.
- 多表查询: 多张表的结构是完全一样的,保存的数据(结构)也是一样的.
order by在联合查询中的使用:
语法:
(select * from t1 where sex=‘男’ order by age limit 99)
union (select * from t1 where sex=‘女’ order by age desc limit 99);
用法:在联合查询中: order by不能直接使用,需要对查询语句使用括号才行;另外,要order by生效: 必须搭配limit: limit使用限定的最大数即可.
6、子查询
两篇很详细的文章:
重点这篇:mysql的查询、子查询及连接查询
Mysql更新
1、update set
1、update set与where搭配使用,变更某些记录:
update + 表名 + set + 变更后的信息 + where子句;
例:update t1 set name='cdn',age='19' where id=5;
注意:如果变更信息后面没有加where子句指定其变更的内容,
那么update set语句就会把同字段中的所有信息全部更新,修改。
2、limit在update中的使用
mysql的update语句只支持更新前多少行,不支持从某行到另一行。
例:
(1) update t1 set name='cdn',age='20' order by id limit 10;
这条语句没有问题,按id的升序更新前10条数据中的name和age。
(2) update t1 set name='cdn',age='20' order by id desc limit 10,10;
这条语句有问题,不能这样写,该语句的作用是按id的降序更新从第10条开始10条数据中的name和age。
(3) update t1 set name='cdn',age='20' where id in(select id from (select * from t1 order by id desc limit 10,10)as t);
这条语句是正确的,采用子查询的方式,能实现更新表中根据id降序排序的第10条到第20条数据的name和age的内容。
2、alter
1、alter语句也具有修改的功能。而且alter的功能远远要比update set多,它不仅仅可以修改记录,还可以修改表名和字段属性。
2、修改表名
alter table+旧表名+rename to 新表名;
例:alter table t1 rename to t2;
将t1表的名字改为t2.
3、修改字段名及属性
alter table + 表名 + change +原字段名+新字段名+TYPE(字符长度);
例:alter table t1 change id uid int(10);
将t1表中的id字段改名为uid字段,类型为int,长度为10.
4、修改字段属性
alter table + 表名 + change +原字段名+原字段名+TYPE(字符长度);
例:alter table t1 change id id varchar(10);
将t1表中的id字段改为varchar类型.
5、将字段改为表的主键
alter table t1 change id id int primary key;
Mysql动态创建表
<update id="createGroupTable" parameterType="String">
create table ${tableName}(
message_id int(32) not null,
sender_id int(32) not null,
g_message varchar(10000) default null,
g_time date not null,
week_message varchar(10000) default null,
read_user varchar(1024) default null,
PRIMARY KEY (message_id)//主键
)ENGINE=InnoDB DEFAULT CHARSET=utf8//utf8编码
</update>
Mysql表字段的增删改
增:alter table 表名 add 字段名 数据类型(大小);
删:alter table 表名 drop 字段名;
改:alter table 表名 modify 字段名 数据类型(大小);