增:
①单条数据:INSERT INTO table_name(属性1,属性2...) VALUES(属性值1,属性值2...)
②【MYSQL写法】多条数据:INSERT INTO table_name(属性1,属性2...) VALUES(属性值1,属性值2...),
(属性值1,属性值2...),
...(最后一个的时候末尾不加逗号)
②【oracle写法】insert all
into test(teacher_id,teacher_name,sex,subject) values(1,'张1','男','语文')
into test(teacher_id,teacher_name,sex,subject) values(2,'张2','女','数学')
into test(teacher_id,teacher_name,sex,subject) values(3,'张3','女','英语')
into test(teacher_id,teacher_name,sex,subject) values(4,'张4','男','物理')
into test(teacher_id,teacher_name,sex,subject) values(5,'张5','女','化学')
into test(teacher_id,teacher_name,sex,subject) values(6,'张6','女','生物')
into test(teacher_id,teacher_name,sex,subject) values(7,'Jack','男','历史')
into test(teacher_id,teacher_name,sex,subject) values(8,'张8','男','地理')
into test(teacher_id,teacher_name,sex,subject) values(9,'张9','女','政治')
into test(teacher_id,teacher_name,sex,subject) values(10,'张10','女','语文')
select 1 from dual;
删:DELETE FROM table_name WHERE 属性值=?
改:UPDATE table_name SET 属性X=?[,属性X2=?] WHERE 属性Y=?
查:
1.模糊查询:SELECT * FROM table_name where 属性 like '_张%'
_(下划线):表示占一个字符
%(百分号):表示任意个字符
2.BETWEEN AND(包括临界值):
SELECT * FROM table_name WHERE 属性 BETWEEN a AND b;
3.IN关键字
SELECT * FROM table_name WHERE 属性 [NOT] IN(属性值1,属性值2...)
4.DISTINCT 关键字:去重
SELECT DISTINCT 属性 FROM table_name
5.函数(SUM,AVG,MAX,MIN)
AVG:求平均值
SELECT AVG(属性) FROM table_name
SUM:求和
SELECT SUM(属性) FROM table_name
MAX:求最大值
MIN:求最小值
SELECT MAX(属性) ,MIN(属性) FROM table_name
6.COUNT():求数量
SELECT COUNT(NOT NULL)FROM table_name
7.ORDER BY:排序
SELECT * FROM table_name ORDER BY 属性 DESC/ASC
DESC:降序
ASC:升序(默认升序,可以不写)
8.GROUP BY:分组
SELECT 属性1,属性2... FROM table_name GROUP BY 属性X
9.HAVING:筛选
SELECT 属性1,属性2... FROM table_name
GROUP BY 属性X
HAVING 属性>常量
10.LIMIT:限制
SELECT * FROM table_name
ORDER BY 属性 DESC
LIMIT X,Y(X:表示开始的索引 Y:表示显示数据的个数)
11.多表查询:
1.交叉表:
SELECT 属性1,属性2,属性3...
FROM table1,table2
WHERE table1.属性1=table2.属性1 [AND 属性=' ']
注意:查询的属性若两个都存在,需要指明是哪个的,随便指定,指定方式:table1.属性1
2.内连接表:INNER JOIN...ON
SELECT 属性1,属性2,属性3...
FROM table1 INNER JOIN table2
ON table1.属性1=table2.属性1
WHERE 属性=' '
注意:查询的属性若两个都存在,需要指明是哪个的,随便指定,指定方式:table1.属性1
3.内左,右连接:LTFT:左边是主表 RIGHT:右边是主表
SELECT 属性1,属性2,属性3...
FROM table1
lEFT/RIGHT table2
ON table1.属性1=table2.属性1
12.子查询:先执行内层查询 再执行外层查询,再将内层查询结果做为外层查询的条件
SELECT * FROM table1
WHERE 属性 '运算符'
(SELECT 属性 FROM table2 WHERE 属性=' ')
13.EXIST:
1.先取外层查询的第一条记录
2.拿第一条记录去和内层循环比较 ,如果存在记录 则返回true
如果不存在,则返回false
3.如果返回true,则保留当前记录存入到结果集中
4.最终结果都是从外层查询中得到,而内层查询仅仅是用来判断。
SELECT * FROM table1
WHERE EXISTS(
SELECT * FROM table2
WHERE table1.属性1=table2.属性1)