1.查询
查询所有内容:select * from table_name
查询选中列的所有内容:select col_name from table_name
查询选中列的不重复数量:select count(distinct col_name) from table_name
固定条件查询:select * from table_name where col_name='value'
模糊条件查询:select * from table_name where col_name like '%value%' order by col_name
连表查询
内连接:至少有一个匹配才返回行
- SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons, Orders WHERE Persons.Id_P = Orders.Id_P
- SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons INNER JOIN Orders ON Persons.Id_P = Orders.Id_P
左连接:即使右表中没有匹配,也从左表返回所有的行
SELECT column_name(s) FROM table_name1 LEFT JOIN table_name2 ON table_name1.column_name=table_name2.column_name
右连接:即使左表中没有匹配,也从右表返回所有的行
SELECT column_name(s) FROM table_name1 RIGHT JOIN table_name2 ON table_name1.column_name=table_name2.column_name
全连接:返回左表和右表的所有行
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons FULL JOIN Orders ON Persons.Id_P=Orders.Id_P
2.更新
update table_name set col_name='new_value' where col_name='old_value'
3.插入
- insert into table_name values (value1, value2, ...)
-insert into table_name (col1, col2, col3) values(value1, value2, value3)
4.删除
delete from table_name where col_name='value'
网友评论