Orders 表:
Company | OrderNumber |
---|---|
IBM | 3532 |
W3School | 2356 |
Apple | 4698 |
W3School | 6953 |
distinct
SELECT DISTINCT 列名称 FROM 表名称
eg:select distinct company from orders
order by
eg:以逆字母顺序显示公司名称,并以数字顺序显示顺序号:
SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC, OrderNumber ASC
================================================================
创建数据库:create database database_name
删除数据库:drop database database_name
创建表:create table demo
(
id int(11) primary key,
name varchar(24)
)
删除表:drop delete truncate
delete from demo where id=3 //删除表中部分数据
TRUNCATE TABLE DEMO//删除表中全部数据
drop table demo//删除表和表中全部数据
TRUNCATE TABLE 不能用于参与了索引视图的表
在速度上,一般来说,drop> truncate > delete
查看表结构 :desc table_name
在mysql中 自增是从1开始的,
插入:insert into demo (id,name,phone) values(1,"小明","151451756932"),(2,"小兰","15636985694")
更新:update demo
set name=value,phone=value
where id=value
================================================================
CREATE TABLE `guojinlong` (
`id` int(11) DEFAULT NULL,
`namee` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
删除重复数据:DELETE a from guojinlong a JOIN (SELECT namee,COUNT(*),MAX(id) as id from guojinlong GROUP BY namee HAVING COUNT(*)>1 ) b on a.namee=b.namee WHERE a.id<b.id
查询以名字分组的薪水前三名:
SELECT name,salary
from user3 u1
WHERE
( SELECT COUNT(1)
from user3 u2
where u1.`name`=u2.name AND u2.salary>=u1.salary
)<=3
ORDER BY name,salary DESC
oracle Mybatis 批量插入
<insert id="insertCartoOracle2" parameterType="java.util.List" useGeneratedKeys="false">
INSERT INTO YW_JC_BENSHENGCL( bianhao,chepaihm,chepaiysbm,suoshudqbm,pipeiztbm )
SELECT
seq_benshengcl.nextval AS bianhao, A.*
FROM
(
<foreach collection ="list" item="item" index= "index" separator="union all">
(select #{item.chepaihm} chepaihm,#{item.chepaiysbm} chepaiysbm,
#{item.suoshudqbm} suoshudqbm,#{item.pipeiztbm} pipeiztbm
from dual
)
</foreach >
) A
</insert>