1.登录数据库:
importMySQLdb
conn= MySQLdb.connect(host='127.0.0.1',user='root',passwd='',db='',port=3306) #连接数据库
cursor = db.cursor() #使用cursor()方法获取操作游标
cursor.execute("执行操作内容") #使用execute方法执行SQL语句
data = cursor.fetchone() #使用 fetchone() 方法获取一条数据库
db.close() #关闭数据库连接
修改数据库root密码:
mysqladmin -u root -p password 新密码#或者:
mysql>use mysql;
mysql>update user set Password=password('root123') where User='root'; #修改root密码
mysql>flush privileges;
2.查看数据库:
mysql> show databases;
3.创建数据库:
mysql> create database test;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.01 sec)
4.使用数据库:
mysql>use test;
Database changed
5.查看表:
show tables;
6.创建表:
语法:create table 表名称(列声明); 使用关键词IF NOT EXISTS可以防止发生错误
mysql> create table basicinfo (id int primary key auto_increment, name varchar(30) not null, age int notnull);
mysql>show tables;+----------------+
| Tables_in_test |
+----------------+
| basicinfo |
+----------------+
1 row in set (0.00 sec)
#在一个表的基础上创建表:
mysql> create table new_tbl select * from orig_tbl;
7.插入数据:
语法:insert [into] 表名 [(列名1, 列名2, 列名3, ...)] values (值1, 值2, 值3, ...);
mysql> insert into basicinfo(id, name, age) values('1','echo','30');
mysql> insert into basicinfo(id, name, age) values('2','pingy','25');
mysql> insert into basicinfo(id, name, age) values('3','telnet','18');
8.查询数据:
语法:select 列名称 from 表名称 [查询条件];
mysql> select * from basicinfo ; #查询所有数据内容
+----+--------+-----+
| id | name | age |
+----+--------+-----+
| 1 | echo | 30 |
| 2 | pingy | 25 |
| 3 | telnet | 18 |
+----+--------+-----+
3 rows in set (0.00sec)
mysql> select id from basicinfo ; #只查看id项的数据内容,同理可以把id换成name或age,只查看name,age项
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
+----+
3 rows in set (0.00sec)
9.修改数据:
语法:update 表名称 set 列名称=新值 where 更新条件;
mysql> update basicinfo set name = 'ssh' where id=1 ; #修改id为1的name为:ssh
mysql> select * frombasicinfo ;+----+--------+-----+
| id | name | age |
+----+--------+-----+
| 1 | ssh | 30 | #由echo变为ssh了
| 2 | pingy | 25 |
| 3 | telnet | 18 |
+----+--------+-----+
3 rows in set (0.00 sec)
10.删除数据:
语法:delete from 表名称 where 删除条件;
mysql> delete from basicinfo where id =3; #删除id为3的项
Query OK, 1 row affected (0.00sec)
mysql> select * frombasicinfo ;+----+-------+-----+
| id | name | age |
+----+-------+-----+
| 1 | ssh | 30 |
| 2 | pingy | 25 |
+----+-------+-----+
2 rows in set (0.00 sec)
11.删除表:
语法:drop table table_name; 或者 drop table if exists table_name;
12.删除数据库:
语法:drop database 数据库名;
13.数据回滚:
cur.rollback() #回滚
例:
#打开数据库连接
db = MySQLdb.connect("127.0.0.1","root","root123","mydb")#使用cursor()方法获取操作游标
cursor =db.cursor()#SQL 更新语句
sql = "UPDATE EMPLOYEE SET AGE = AGE + 1
WHERE SEX = '%c'"% ('M')
try:#执行SQL语句
cursor.execute(sql)#提交到数据库执行
db.commit()except:#发生错误时回滚
db.rollback()#关闭数据库连接
db.close()
实例1:
mysql> select * from basicinfo; #先查看现有表数据内容
+----+--------+-----+
| id | name | age |
+----+--------+-----+
| 1 | echo | 30 |
| 2 | pingy | 25 |
| 3 | telnet | 18 |
+----+--------+-----+
3 rows in set (0.00 sec)
连接数据库并操作:
cur=conn.cursor()
conn.select_db('mydb')
res= cur.execute("update basicinfo set name = 'john',age = 20 where id=1 ;") #修改ID为1的内容:名字为john,年龄为20
conn.commit()
cur.close()
conn.close()print(res)
再次查询:
mysql> select * frombasicinfo;+----+--------+-----+
| id | name | age |
+----+--------+-----+
| 1 | john | 20 | #内容被修改了
| 2 | pingy | 25 |
| 3 | telnet | 18 |
+----+--------+-----+
3 rows in set (0.00 sec)
实例2:
importMySQLdb
conn= MySQLdb.connect(host='127.0.0.1',user='root',passwd='',db='',port=3306) #连接数据库
cur =conn.cursor()
conn.select_db('newdb')
res= cur.execute("select * from info;")print(cur.fetchone()) #返回第一条数据内容
res1 = cur.execute("insert into info(id, name, age) values('2','pingy','25');")#cur.executemany() #执行多条语句
conn.commit() #提交
cur.close() #关闭连接
conn.close() #关闭操作
print(res)
输出结果:
(1L, 'pule', 30L)1