sql常用语法

sql常用语法

1.增删改查

建表示例

CREATE TABLE Persons
(
Id_P int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

增删改查

INSERT INTO [TABLE_NAME] (column1,...columnN) VALUES (value1,...valueN);
DELETE FROM [table_name] WHERE [condition];
UPDATE [table_name] SET column1 = value1, column2 = value2...., columnN = valueN;
SELECT column1, column2, columnN FROM table_name WHERE [condition];

2.模糊查询

SELECT * FROM tab WHERE name LIKE '%A%';

匹配模式符“%”可以匹配任意0到多个字符，“_”表示匹配任意1个字符，该语句是查询name字段中包含’A’的记录。

SELECT * FROM tab WHERE name LIKE 'A%';
SELECT * FROM tab WHERE name LIKE '%A';

分别输出name字段中首字母是A和尾字母是A的记录。

SELECT * FROM tab WHERE name LIKE '_A%';

输出name字段中第二个字母是A的记录。

SELECT * FROM tab WHERE  instr(a, 'xx') > 0 or instr(b, 'yy') > 0 or instr(c, 'zz') > 0

多字段模糊查询。

2.in

SELECT * FROM tab WHERE name IN ('Adams','Carter')

in的上限为1000.

3.oracle日期转换格式

select * from dual where time between to_date('2012-06-18 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2012-06-18 23:59:59','yyyy-mm-dd hh24:mi:ss');
select * from dual where to_char(time,'yyyy-mm-dd')='2012-06-18';
select * from dual where trunc(time)=to_date('2012-06-18','yyyy-mm-dd');

4.mapper中转义字符

<![CDATA[ SELECT * FROM  user WHERE  age  <= 30 AND age >= 18 ]]>