一、MySQL数据库操作指令
1.端口号:3306;启动:mysql -u -root -p
2.查询版本:select version();
3.查询当前时间:select now();
4.查询所有数据库:show databases;
5.删除数据库:drop database XXX(库名字);
6.切换数据库:use XXX(库名字);
7.查看数据库中的表:show tables;
8.查看表中内容:select * from XXX;
9.创建数据库:create database XXX(名字) charset=utf8(必须指定,数据库的格式);
10.查看当前在哪个数据库:select database();
二、MySQL表操作指令
1.查看所有的表:show tables;
2.创建表:create table tablename(字段1 类型 是否为空 是否是主键 默认值,
字段2,
字段3
);
3.修改表:①alter table tablename;②alter table tablename add 字段4 类型 是否为空 是否有默认值
4…删除表格:drop table tablename;
5.删表中的字段:alter table tablename 字段;
6.更新表的名字:rename table tablename1 to tablename2;
7.查看表的创建语句:show create table tablename;
8.查看表结构:desc tablename
三、MySQL数据操作指令
1.增加数据:①单条全列插入:insert into dog values(字段1对应内容,“字段2对应内容”,字段3对应内容);②缺省插入:insert into dog(字段2) values(“字段2对应的内容”);
2.查看数据:select * from tablename;
3.修改数据:update tablename set 字段=“XXX” where id(字段)=XXX;
4.删除数据:①物理删除:delete from tablename where id (字段)= XXX;②逻辑删除:在表中增加一个字段:alter table tablename add isdelete bit default 0;如果是逻辑删除,update修改isdelete=1:update tablename set isdelete=1 where id=1;
四、MySQL查询操作指令
1.条件运算(>,<,=,!):①select 字段 from tablename where 字段=XXX;②select * from tablename where 字段=XXX;③select * from tablename where 字段>XXX;④select 字段1,字段2 from tablename where 字段!=XXX;
2.逻辑运算(and,or,not):select * from tablename where 字段1>XXX and(or…) 字段2=XXX;
3.模糊查询(“%”表示任意多个字符;“”表示任意一个字符):①select * from tablename where 字段 like “模糊值”;(%代表模糊符号:如①%+模糊值;②模糊值+%;③%+模糊值+%;);②select * from tablename where 字段1 like “模糊值” or 字段2 like “模糊值”;③select * from tablename where 字段 like “X”;
4.范围查询:①in:select * from tablename where 字段1 in(X,X,X,X);②not in:select * from tablename where 字段1 not in(X,X,X,X);③between…and…:select * from tablename where 字段1 between 范围1 and 范围2;④null:select * from tablename where 字段2 is null;
5.查询符号的优先级:()>not>条件运算(>,=)>逻辑运算(and,or;且and>or);
6.聚合查询:①统计个数:select count() from tablename; select count() from tablename where 字段=XXX;②求最大值:select max(字段) from tablename;③求和:select sum(字段) from tablenamewhere 字段=XXX;④求平均值:select avg(字段) from tablename where 字段=XXX;
7.分组查询:①select 字段 as 别名,count(*) from tablename group by 字段;(group by—以XXX为分组依据)
8.排序:oder by 字段(asc—升序;desc—降序)①select * from tablename where 字段1=XXX order by 字段2 desc;
9.分页:(limit start,count ;实际开发中涉及的是计算问题):select * from tablename limit start,count;
10.去重列中的内容:distinct:select distinct 字段 from tablename;
五、Python和MySQL的交互
交互代码如下:
#安装pymsql pip install PyMySQL
import pymysql
try:
#1.链接 数据库 链接对象 connection()
conn = pymysql.Connect(
host = "localhost",
port = 3306,
db = "animal",
user = "root",
passwd = "123456",
charset = 'utf8'
)
#2. 建库 建表
#3.创建游标对象 cursor()
cur = conn.cursor()
#4.mysql操作
# ①-------------增加一条数据
# insert_one = 'insert into stu values(0,"张飞","918-02-9",0,0,"河南",95)' #mysql的语句在这里填写
#result = cur.execute(update_stu)
#注意:
# mysql错误:Column count doesn't match value count at row 1
# 这是由于类似 INSERT INTO table_name(col_name1, col_name2, col_name3) VALUES('value1', 'value2');语句中,前后列数不等造成的,
#②---------------修改一条数据
#update_stu = 'update stu set name="张沁瑶" where name = "沁瑶"'
#result = cur.execute(update_stu)
# ③---------------删除一条数据
#delete = 'delete from stu where name = "刘能"'
#result = cur.execute(delete)
# ④--------------查询多组数据
# reach = 'select * from stu'
# cur.execute(reach)
# result = cur.fetchall()#输出的都是元组
# print(result)
# ⑤-------------查询一条数据
reach = 'select * from stu where id = 1'
cur.execute(reach)
result = cur.fetchone()#输出的都是元组
print(result)
#提交事务
conn.commit()
#关闭游标
cur.close()
#关闭链接,节省资源
conn.close()
except Exception as e:
print(e)
六、MySQL的备份和恢复
1.备份
mysqldump -u root -p database_name tablename > 备份文件的绝对路径
(路径中斜线为反斜线“\”)
2.恢复
mysql -u root -p db_name < 备份文件的绝对路径
(路径中斜线为反斜线“\”)