MySQL基本操作

连接数据库

mysql -u username -p

数据库操作

显示存在的数据库：show databases;

创建数据库：create database name;

删除数据库：drop database name;

选择数据库：use name;

数据操作

创建数据表：create table tbl_name(key_name1 type1, name2 type2);

删除数据表：drop table tbl_name;

插入数据：insert into tbl_name (key_name1, key_name2) values ("data1", "data2");

查询数据：

select */[key_name1, key_name2]
	from tbl_name
	[where condition1 or/and condition2]
	[limit num]
	[offset off];

condition的模糊条件(like)： key_name like '%ant';

结合多个select：

select key_name from tbl_name1 [where conditions]
	union [all / distinct]
	select key_name from tbl_name1 [where conditions]

更新：

update tbl_name set nolumn_name1=updated_val1, nolumn_name2=updated_val2;

删除：

delete from tbl_name [where conditions];

排序（默认升序）：

select key_name1, key_name2 from tbl_name1, tbl_name2
order by key_name1 [asc/sesc], key_name2 [asc/sesc]

分组：

select key_name1, function(key_name2) [as key_name] from tbl_name
group by key_name3 [with rollup];

结合两个表：

select a.key_name1, b.key_name2
from tbl_name1 a 
left/inner/right join tbl_name2 b on condition;

修改表的内容（key ）：

• 添加：alter table tbl_name add key_name type;
• 删除：alter table tbl_name drop key_name;
• 修改：
  • key类型：alter table tbl_name modify type;
  • key名称和类型：alter table tbl_name change old_key_name new_name type;
  • 表格的名称：alter table tbl_name rename new_name;

利用python操作

利用mysql-connector。

import mysql.connector
# connection
mydb = mysql.connector.connect(host="address", user="username", passwd="pass_word")
# execute commands
cursor = mydb.cursor()
cursor.execute("mysql_command")
# if insert multi records
command = "insert into tbl_name (key_name1, key_name2) values (%s, %s)"
vals = [(data1, data2), (data3, data4)]
cursor.executemany(command, vals)
mydb.commit() #after table is changed
# result
result = cursor.fetchall() #a list
result = cursor.fetchone() #only get one result