2数据库之Mysql基本操作

                                                                                                                                    数据库基本类型、基本操作

一.数据库表基本操作

1.创建表

    create table tb1(

         `nid` int(10)  NOT NULL auto_increment primaey key,

          `name` varchar(11) DEFAULT NULL

    )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

   

    主键primaey key特殊的唯一索引，不能重复，不能为空,一个表只有一个主键

    联合主键primaey key(nid,name)，合起来唯一

 

2.数据类型：

    数字类型

        int整型

        tinyint小整型，有符号是-128至127；没符号0-255

        bigint大整型

        decimal小数类型

    字符串：

        char 字符类型，定长，char(10),不管输入内容多少都占10个字节，最长255

        varchar 字符类型，变长，varchar(10),输入超过10个字节会自己变长，最长255

        大长度字符串

        text

        mediutext

        longtext

    blob 二进制类型，存取二进制文件

    枚举：

        enum

 

        CREATE TABLE `db1` (

          `nid` int(10) DEFAULT NULL,

          `name` varchar(11) DEFAULT NULL,

          `money` decimal(5,2) DEFAULT NULL,

          `sex` enum('woman','man') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT 'man'

        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

 

    时间类型：

        date   YYYY-MM-DD（1000-01-01/9999-12-31）

        time    HH:MM:SS（'-838:59:59'/'838:59:59'）

        year    YYYY（1901/2155）

        datetime        YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59 Y）

        timestamp    YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某时）

    布尔值：Mysql无布尔类型，通过tinyint(1)构造的

 

3.删除表

    drop table 表名

 

4.清空表

    delete from 表名

    truncate table 表名

 

5.修改表：

    添加列：alter table 表名 add 列名 类型

删除列：alter table 表名 drop column 列名

修改列：

        alter table 表名 modify column 列名 类型;  -- 类型

        alter table 表名 change 原列名 新列名 类型; -- 列名，类型

添加主键：

        alter table 表名 add primary key(列名);

删除主键：

        alter table 表名 drop primary key;

        alter table 表名  modify  列名 int, drop primary key;

添加外键：alter table 从表 add constraint 外键名称（形如：FK_从表_主表） foreign key 从表(外键字段) references 主表(主键字段);

删除外键：alter table 表名 drop foreign key 外键名称

修改默认值：ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;

  删除默认值：ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;

 

二.数据库表内容基本操作

1.增加

     insert into 表 (列名,列名...) values (值,值,值...)

 insert into 表 (列名,列名...) values (值,值,值...),(值,值,值...)

   insert into 表 (列名,列名...) select (列名,列名...) from 表

2.修改

     update 表 set name ＝ 'alex' where id>1

3.删除

    delete from 表

  delete from 表 where id＝1 and name＝'alex'

4.查找

    select * from 表

select * from 表 where id > 1

  select nid,name,gender as gg from 表 where nid > 1

 

5.其他

    a、条件

    select * from 表 where id > 1 and name != 'alex' and num = 12;

 

    select * from 表 where id between 5 and 16;

 

    select * from 表 where id in (11,22,33)

    select * from 表 where id not in (11,22,33)

    select * from 表 where id in (select nid from 表)

 

b、通配符

    select * from 表 where name like 'ale%'  - ale开头的所有（多个字符串）

    select * from 表 where name like 'ale_'  - ale开头的所有（一个字符）

 

c、限制

    select * from 表 limit 5;            - 前5行

    select * from 表 limit 4,5;          - 从第4行开始的5行

    select * from 表 limit 5 offset 4    - 从第4行开始的5行

 

d、排序

    select * from 表 order by 列 asc              - 根据 “列” 从小到大排列

    select * from 表 order by 列 desc             - 根据 “列” 从大到小排列

    select * from 表 order by 列1 desc,列2 asc    - 根据 “列1” 从大到小排列，如果相同则按列2从小到大排序

 

e、分组

    select num from 表 group by num

    select num,nid from 表 group by num,nid

    select num,nid from 表  where nid > 10 group by num,nid order nid desc

    select num,nid,count(*),sum(score),max(score),min(score) from 表 group by num,nid

 

    select num from 表 group by num having max(id) > 10

 

    特别的：group by 必须在where之后，order by之前

 

f、连表

    无对应关系则不显示

    select A.num, A.name, B.name

    from A,B

    Where A.nid = B.nid

 

    无对应关系则不显示

    select A.num, A.name, B.name

    from A inner join B

    on A.nid = B.nid

 

    A表所有显示，如果B中无对应关系，则值为null

    select A.num, A.name, B.name

    from A left join B

    on A.nid = B.nid

 

    B表所有显示，如果B中无对应关系，则值为null

    select A.num, A.name, B.name

    from A right join B

    on A.nid = B.nid

 

g、组合

    组合，自动处理重合

    select nickname

    from A

    union

    select name

    from B

 

    组合，不处理重合

    select nickname

    from A

    union all

    select name from B

 

 

三.python pymysql进行数据库操作

1.基本操作

                                                                                                                                    数据库基本类型、基本操作
一.数据库表基本操作
1.创建表
    create table tb1(
         `nid` int(10)  NOT NULL auto_increment primaey key,
          `name` varchar(11) DEFAULT NULL
    )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
   
    主键primaey key特殊的唯一索引，不能重复，不能为空,一个表只有一个主键
    联合主键primaey key(nid,name)，合起来唯一

2.数据类型：
    数字类型
        int整型
        tinyint小整型，有符号是-128至127；没符号0-255
        bigint大整型
        decimal小数类型
    字符串：
        char 字符类型，定长，char(10),不管输入内容多少都占10个字节，最长255
        varchar 字符类型，变长，varchar(10),输入超过10个字节会自己变长，最长255
        大长度字符串
        text
        mediutext
        longtext
    blob 二进制类型，存取二进制文件
    枚举：
        enum

        CREATE TABLE `db1` (
          `nid` int(10) DEFAULT NULL,
          `name` varchar(11) DEFAULT NULL,
          `money` decimal(5,2) DEFAULT NULL,
          `sex` enum('woman','man') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT 'man'
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

    时间类型：
        date   YYYY-MM-DD（1000-01-01/9999-12-31）
        time    HH:MM:SS（'-838:59:59'/'838:59:59'）
        year    YYYY（1901/2155）
        datetime        YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59 Y）
        timestamp    YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某时）
    布尔值：Mysql无布尔类型，通过tinyint(1)构造的

3.删除表
    drop table 表名

4.清空表
    delete from 表名
    truncate table 表名

5.修改表：
    添加列：alter table 表名 add 列名 类型
删除列：alter table 表名 drop column 列名
修改列：
        alter table 表名 modify column 列名 类型;  -- 类型
        alter table 表名 change 原列名 新列名 类型; -- 列名，类型
添加主键：
        alter table 表名 add primary key(列名);
删除主键：
        alter table 表名 drop primary key;
        alter table 表名  modify  列名 int, drop primary key;
添加外键：alter table 从表 add constraint 外键名称（形如：FK_从表_主表） foreign key 从表(外键字段) references 主表(主键字段);
删除外键：alter table 表名 drop foreign key 外键名称
修改默认值：ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
  删除默认值：ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;

二.数据库表内容基本操作
1.增加
     insert into 表 (列名,列名...) values (值,值,值...)
 insert into 表 (列名,列名...) values (值,值,值...),(值,值,值...)
   insert into 表 (列名,列名...) select (列名,列名...) from 表
2.修改
     update 表 set name ＝ 'alex' where id>1
3.删除
    delete from 表
  delete from 表 where id＝1 and name＝'alex'
4.查找
    select * from 表
select * from 表 where id > 1
  select nid,name,gender as gg from 表 where nid > 1

5.其他
    a、条件
    select * from 表 where id > 1 and name != 'alex' and num = 12;
 
    select * from 表 where id between 5 and 16;
 
    select * from 表 where id in (11,22,33)
    select * from 表 where id not in (11,22,33)
    select * from 表 where id in (select nid from 表)
 
b、通配符
    select * from 表 where name like 'ale%'  - ale开头的所有（多个字符串）
    select * from 表 where name like 'ale_'  - ale开头的所有（一个字符）
 
c、限制
    select * from 表 limit 5;            - 前5行
    select * from 表 limit 4,5;          - 从第4行开始的5行
    select * from 表 limit 5 offset 4    - 从第4行开始的5行
 
d、排序
    select * from 表 order by 列 asc              - 根据 “列” 从小到大排列
    select * from 表 order by 列 desc             - 根据 “列” 从大到小排列
    select * from 表 order by 列1 desc,列2 asc    - 根据 “列1” 从大到小排列，如果相同则按列2从小到大排序
 
e、分组
    select num from 表 group by num
    select num,nid from 表 group by num,nid
    select num,nid from 表  where nid > 10 group by num,nid order nid desc
    select num,nid,count(*),sum(score),max(score),min(score) from 表 group by num,nid
 
    select num from 表 group by num having max(id) > 10
 
    特别的：group by 必须在where之后，order by之前
 
f、连表
    无对应关系则不显示
    select A.num, A.name, B.name
    from A,B
    Where A.nid = B.nid
 
    无对应关系则不显示
    select A.num, A.name, B.name
    from A inner join B
    on A.nid = B.nid
 
    A表所有显示，如果B中无对应关系，则值为null
    select A.num, A.name, B.name
    from A left join B
    on A.nid = B.nid
 
    B表所有显示，如果B中无对应关系，则值为null
    select A.num, A.name, B.name
    from A right join B
    on A.nid = B.nid
 
g、组合
    组合，自动处理重合
    select nickname
    from A
    union
    select name
    from B
 
    组合，不处理重合
    select nickname
    from A
    union all
    select name from B


三.python pymysql进行数据库操作
1.基本操作

import pymysql

# 连接数据库
conn=pymysql.connect(host="127.0.0.1",port=3306,user="root",password="123456",database="a",)

# 创建游标进行数据库操作以及获取数据
cursor=conn.cursor()

# 执行sql语句,并返回影响的行数
# cursor.execute("""insert into user(username,password,email) values(%s,%s,%s)""",("haha","7890","ssh@163.com",))
# 插入多条数据
row=cursor.executemany("""insert into user(username,password,email) values(%s,%s,%s)""",[("haha","7890","ssh@163.com",),("gg","7890","gg@163.com",)])
print(row)

print(cursor.lastrowid)
# 提交数据
conn.commit()

# 关闭游标
cursor.close()

# 关闭数据库连接
conn.close()
2.自动提交

import pymysql

# 连接数据库
conn=pymysql.connect(host="127.0.0.1",port=3306,user="root",password="123456",database="a",autocommit=True)

# 创建游标进行数据库操作以及获取数据
cursor=conn.cursor()

# 执行sql语句,并返回影响的行数
# cursor.execute("""insert into user(username,password,email) values(%s,%s,%s)""",("haha","7890","ssh@163.com",))
# 插入多条数据
row=cursor.executemany("""insert into user(username,password,email) values(%s,%s,%s)""",[("haha","7890","ssh@163.com",),("gg","7890","gg@163.com",)])
print(row)

print(cursor.lastrowid)
# 提交数据，如果创建数据库连接时autocommit=True会帮我们进行自动提交
# conn.commit()

# 关闭游标
cursor.close()

# 关闭数据库连接
conn.close()
3.查询数据

import pymysql

# 连接数据库
conn=pymysql.connect(host="127.0.0.1",port=3306,user="root",password="123456",database="a",autocommit=True)

# 创建游标进行数据库操作以及获取数据
cursor=conn.cursor()

# 执行sql语句,并返回影响的行数
# cursor.execute("""insert into user(username,password,email) values(%s,%s,%s)""",("haha","7890","ssh@163.com",))
# 插入多条数据
# row=cursor.executemany("""insert into user(username,password,email) values(%s,%s,%s)""",[("haha","7890","ssh@163.com",),("gg","7890","gg@163.com",)])

# 查询数据
cursor.execute("select * from user")
# fetchone返回一条数据,类型是元祖
# result=cursor.fetchone()
# print(type(result),result)

# fetchmany返回多条数据,类型是元祖，数据展示是元祖里面包元祖
result=cursor.fetchmany(3)
print(type(result),result)

# fetchmany返回多条数据,类型是元祖，数据展示是元祖里面包元祖
result=cursor.fetchall()
print(type(result),result)

# 提交数据
# conn.commit()

# 关闭游标
cursor.close()

# 关闭数据库连接
conn.close()
4.返回结果格式化显示cursor=pymysql.cursors.DictCursor，且返回的数据包含列名

import pymysql

# 连接数据库
conn=pymysql.connect(host="127.0.0.1",port=3306,user="root",password="123456",database="a",autocommit=True)

# 创建游标进行数据库操作以及获取数据
cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)

# 执行sql语句,并返回影响的行数
# cursor.execute("""insert into user(username,password,email) values(%s,%s,%s)""",("haha","7890","ssh@163.com",))
# 插入多条数据
# row=cursor.executemany("""insert into user(username,password,email) values(%s,%s,%s)""",[("haha","7890","ssh@163.com",),("gg","7890","gg@163.com",)])

# 查询数据
cursor.execute("select * from user")
# fetchone返回一条数据,类型是字典
result=cursor.fetchone()
print(type(result),result)

# fetchmany返回多条数据,数据展示是列表里面包含字典
result=cursor.fetchmany(3)
print(type(result),result)

# fetchmany返回多条数据,数据展示是列表里面包含字典
result=cursor.fetchall()
print(type(result),result)

# 提交数据
# conn.commit()

# 关闭游标
cursor.close()

# 关闭数据库连接
conn.clos

 

2.自动提交

import pymysql

 

# 连接数据库

conn=pymysql.connect(host="127.0.0.1",port=3306,user="root",password="123456",database="a",autocommit=True)

 

# 创建游标进行数据库操作以及获取数据

cursor=conn.cursor()

 

# 执行sql语句,并返回影响的行数

# cursor.execute("""insert into user(username,password,email) values(%s,%s,%s)""",("haha","7890","ssh@163.com",))

# 插入多条数据

row=cursor.executemany("""insert into user(username,password,email) values(%s,%s,%s)""",[("haha","7890","ssh@163.com",),("gg","7890","gg@163.com",)])

print(row)

 

print(cursor.lastrowid)

# 提交数据，如果创建数据库连接时autocommit=True会帮我们进行自动提交

# conn.commit()

 

# 关闭游标

cursor.close()

 

# 关闭数据库连接

conn.close()

3.查询数据

import pymysql

 

# 连接数据库

conn=pymysql.connect(host="127.0.0.1",port=3306,user="root",password="123456",database="a",autocommit=True)

 

# 创建游标进行数据库操作以及获取数据

cursor=conn.cursor()

 

# 执行sql语句,并返回影响的行数

# cursor.execute("""insert into user(username,password,email) values(%s,%s,%s)""",("haha","7890","ssh@163.com",))

# 插入多条数据

# row=cursor.executemany("""insert into user(username,password,email) values(%s,%s,%s)""",[("haha","7890","ssh@163.com",),("gg","7890","gg@163.com",)])

 

# 查询数据

cursor.execute("select * from user")

# fetchone返回一条数据,类型是元祖

# result=cursor.fetchone()

# print(type(result),result)

 

# fetchmany返回多条数据,类型是元祖，数据展示是元祖里面包元祖

result=cursor.fetchmany(3)

print(type(result),result)

 

# fetchmany返回多条数据,类型是元祖，数据展示是元祖里面包元祖

result=cursor.fetchall()

print(type(result),result)

 

# 提交数据

# conn.commit()

 

# 关闭游标

cursor.close()

 

# 关闭数据库连接

conn.close()

4.返回结果格式化显示cursor=pymysql.cursors.DictCursor，且返回的数据包含列名

import pymysql

 

# 连接数据库

conn=pymysql.connect(host="127.0.0.1",port=3306,user="root",password="123456",database="a",autocommit=True)

 

# 创建游标进行数据库操作以及获取数据

cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)

 

# 执行sql语句,并返回影响的行数

# cursor.execute("""insert into user(username,password,email) values(%s,%s,%s)""",("haha","7890","ssh@163.com",))

# 插入多条数据

# row=cursor.executemany("""insert into user(username,password,email) values(%s,%s,%s)""",[("haha","7890","ssh@163.com",),("gg","7890","gg@163.com",)])

 

# 查询数据

cursor.execute("select * from user")

# fetchone返回一条数据,类型是字典

result=cursor.fetchone()

print(type(result),result)

 

# fetchmany返回多条数据,数据展示是列表里面包含字典

result=cursor.fetchmany(3)

print(type(result),result)

 

# fetchmany返回多条数据,数据展示是列表里面包含字典

result=cursor.fetchall()

print(type(result),result)

 

# 提交数据

# conn.commit()

 

# 关闭游标

cursor.close()

 

# 关闭数据库连接

conn.clos