数据库基本操作

数据库基本操作

 

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>