数据操作语言 (DML)
select
select 列名称 from 表名称
select * from 表名称
select LastName,FirstName from Personsselect * from Persons
select distinct
select distinct 列名称 from 表名称
select distinct * from 表名称
select distinct LastName,FirstName from Personsselect distinct * from Persons
update
update 表名称 set 列名称 = 新值 where 列名称 = 某值
update Person set Address = 'Zhongshan 23', City = 'Nanjing'where LastName = 'Wilson'
insert into
insert into 表名称 （列1，列2...） values （值1，值2...）
insert into Persons (LastName, Address) values ('Wilson', 'Champs-Elysees') delete
delete from 表名称 where 列名称 = 某值
delete * from 表名称
delete form Person where LastName = 'Wilson' delete * form Person
select * from Persons where City='Beijing'
and & or
select * from Persons where (FirstName='Thomas' or FirstName='William') and LastName='Carter'
order by
order by 语句默认按照升序对记录进行排序。如果您希望按照降序对记录进行排序，可以使用 desc 关键字。select Company, OrderNumber from Orders order by Company, OrderNumberselect Company, OrderNumber from Orders order by Company desc, OrderNumber asc
MySQL
mysql> show databases; // 查看当前所有的数据库mysql> use test; //作用与test数据库mysql> show tables; //查看test库下面的表
python进行数据库操作
host: 连接的数据库服务器主机名，默认为本地主机(localhost)
port:MySQL服务使用的TCP端口.默认是3306. user:数据库登陆名.默认是当前用户. passwd:数据库登陆的秘密.默认为空. db:要使用的数据库名.没有默认值.# -*- coding: utf-8 -*-import MySQLdb# 连接数据库conn= MySQLdb.connect( host='localhost', port = 3306, user='root', passwd='orochi123456', db ='test', )# 创建游标cur = conn.cursor() # 创建数据表#cur.execute("create table student(id int ,name varchar(20),class varchar(30),age varchar(10))")# 每次执行execute操作都会返回影响的数据条数# 插入数据# 插入一条数据inser = "insert into student (id, name, class, age) values(%s,%s,%s,%s)"cur.execute(inser ,('3','Huhu','2 year 1 class','7'))# 插入多条数据cur.executemany(inser,[ ('3','Tom','1 year 1 class','6'), ('3','Jack','2 year 1 class','7'), ('3','Yaheng','2 year 2 class','7'), ])# 更新数据cur.execute("update student set id='4', age = '10' where age='6'")# 删除数据cur.execute("delete from student where name='Yaheng'")# 选择数据# 选择符合条件的数据，print cur.execute("select * from student")# 选择符合条件且不重复的数据条数print cur.execute("select distinct * from student")# 下面两个都要紧跟在select之后# 逐个打印符合条件的数据print cur.fetchone() # 每次执行该语句的结果都不同# 打印符合条件的所有数据info = cur.fetchall()for ii in info: print ii# 关闭游标 cur.close()# 进行提交conn.commit()# 关闭数据库连接conn.close()
2015-05-18