数据库基础操作

Ubuntu安装MySQL服务

安装服务端: sudo apt-get install mysql-server
安装客户端: sudo apt-get install mysql-client

配置文件：/etc/mysql
命令集： /usr/bin
数据库存储目录 ：/var/lib/mysql

目录

• 1. 启动/连接/关闭mysql
• 2. 数据库操作
• 2. 表的操作
• 3. 数据的操作:
• 4. 字段的操作(表结构的修改)
• 5. 时间类型
• 6. 高级查询
• 7. 导出/恢复数据库
• 8. Python操作MySQL数据库
• 写操作
• 读操作

1. 启动/连接/关闭mysql

• 启动/关闭/重启服务端: sudo /etc/init.d/mysql status | start | stop | restart
• 客户端连接服务端: mysql -hlocalhost -uroot -p123456
• 关闭连接: ctrl + d exit

2. 数据库操作

show databases; --> 查看数据库存储目录：/var/lib/mysql下有那些数据库

create database db_name character set utf8;
create database db_name charset=utf8;

show create database db_name;

select database();

use db_name;

drop database db_name;

2. 表的操作

create table t_name (col_name1 dt opt1 opt2, col_name2, dt opt1 opt2, ...);

unsigned not null default primary_key auto_increment

show tables;

show create table t_name;

desc t_name;

drop table t_name;

3. 数据的操作:

insert into t_name values (v1, v2, ...);
insert into t_name (col1, col2, ...) values (v1, v2, ...);

select * from t_name;
select * from t_name where ...;
select col1, col2, ... from t_name where ...;

update t_name set vol1 = v1, vol2 = v2, ... where ...;

delete from t_name; --> 清空表中的全部记录
delete from t_name where...;

4. 字段的操作(表结构的修改)

alter table t_name add col1 dt opt1 opt2 ..., ...;
alter table t_name add col1 dt opt1 opt2 ..., ... first;
alter table t_name add col1 dt opt1 opt2 ..., ... after col;

alter table t_name drop col;

alter table t_name modify col1 dt opt1 opt2 ..., col2 dt opt1 opt2 ..., ...;

alter table t_name change old_col new_col dt opt;

alter table t_name rename new_t_name;

5. 时间类型

date time year datetime timestamp

now() curdate() curtime()

创建表时,给一个时间类型字段设置默认值:
create table test_time (id int, time datetime default now());

select * from t_name where ...;

select * from t_name where col 运算符 (时间 - interval 时间间隔单位)
+ / - interval
时间间隔单位: 2 hour | 1 minute | 2 second | 2 year | 3 month | 1 day

time('2:30:00') 得到指定的时间
date('2019-8-13') 得到指定的日期
主要用于时间运算

6. 高级查询

select * from t_name where col like '...';
% 任意0或多个字符
_ 任意单个字符

select * from t_name where col regexp '...';

select * from t_name where ... order by col1 col2 [asc [desc]];

limit 1
select update delete

select ... union select ...;
all --> 返回所有结果集, 包括重复的数据.
两个查询语句的字段可以不同,但是数量必须保持相同.

7. 导出/恢复数据库

mysqldump -uroot -p stu > stu.sql

mysql -uroot -p student < stu.sql

8. Python操作MySQL数据库

sudo pip3 install pymysql --> 安装模块pymysql

db = pymysql.connect(...)
cur = db.cursor()
cur.execute('insert ...;')
db.commit() / db.fetchall()
cur.close() --> 不能使用这个游标了
db.close() --> 关闭与数据库的连接

db = pymysql.connect(host, port, user, password, database, charset)
cur = db.cursor()
db.commit()
db.rollback()
db.close()

cur.execute(sql)
cur.fetchone()
cur.fetchmany(n)
cur.fetchall()
cur.close()

写操作

当向数据库commit时, 如果前面的execute中有错误的,这时数据库是不允许错误的execute提交,但是会让正确的execute提交.
我们希望一次提交的内容中, 有一个有错误, 那所有的execute都不提交, 即回滚到上一次提交后的状态.
所以需要使用try.

try:
    db.commit()
except:
    db.rollback()    # 如果提交异常, 则回到提交前的状态

try:
    cur = db.cursor()

    name = input("Name: ")
    age = input("Age: ")
    gender = input("Gender: ")
    score = input("Score: ")

    # 提前把语句列出来 sql = ""

    # 1. 直接构建sql语句
    sql = "insert into class_1 (name, age, gender, score) " 
          "values ('%s', %s, '%s', %s)" % (name, age, gender, score)
    cur.execute(sql)

    # 2. 通过execute第二个参数列表构建sql语句  通常用作传字段值 例如不可用于传表名
    sql = "insert into class_1 (name, age, gender, score) " 
          "values (%s, %s, %s, %s)"
    cur.execute(sql, [name, age, gender, score])

    db.commit()
except Exception as e:
    db.rollback()       # 如果提交异常, 则回到提交之前的状态  

读操作

不需要db.commit()

# 数据库操作
sql = "select name, age from class_1 where sex='m';"
cur.execute(sql)

# 获取查询结果
one_row = cur.fetchone()          # 元组 / None
many_rows = cur.fetchmany(3)      # 元组中嵌套元组 / None
all_rows = cur.fetchall()         # 元组嵌套元组 / None