Mysql数据库的常用操作

一、sql语句介绍：

1.DDL数据定义

->1.创建数据库
    create database if not exists 数据库名
->2.删除数据库
    drop database 数据库名
->3.查看数据库
    show databases;
->4.定义数据库的字符集
    create database if not exists 数据库名 default charset=utf8;
->5进入到某个数据库
    use 数据库名

->1.创建表
    create table if not exists 表名(
    字段1 字段类型(字段的长度) 字段属性 字段约束,
    字段2 字段类型(字段的长度) 字段属性 字段约束,
    字段2 字段类型(字段的长度) 字段属性 字段约束,
    .....);

->2.查看表结构
    desc 表名
->3.查看数据表
    show tables
->4.删除表
    drop table if exists 表名
->5.修改表名
    alter table 旧表名 rename as 新表名

->6.修改字段名
    alter table 表名 change 旧字段名 新字段名 字段类型及属性

->7.修改字段类型
    alter table  表名 modify 字段名 字段类型属性

->8.添加字段
    alter table 表名 add 字段名  字段类型及属性
->9.删除字段
    alter table 表名 drop 字段名

->10.数据表字段的约束类型
    主   键：primary key
    唯  一：unique
    自增长：auto_increment
    外  键：foreign key

->11.添加主键约束
    create table if not exists `表名`(
    字段1 类型(长度) primary key)
->12.添加唯一约束
    create table if not exists `表名`(
    字段1 类型(长度) unique)
->13.添加自增长约束
    create table if not exists `表名`(
    字段1 类型(长度) auto_increment)

->14.添加外键约束
    -->01.建表时添加
        CREATE TABLE `表名` ( 
        `id` int(11) NOT NULL AUTO_INCREMENT, 
        `user_id` int(11) NOT NULL, PRIMARY KEY (`id`), 
        CONSTRAINT `外键名` FOREIGN KEY (`从表字段`) REFERENCES `主表名` (`主表字段`) ) 
        ENGINE=InnoDB DEFAULT CHARSET=utf8

    -->02.建表之后添加
        alter table 从表表名 add co nstraint 外键名称 
        foreign key(从表某字段) REFERENCES 主表(主表的某字段) 
    --03.注意事项
        (1)主表中的字段只能有主键约束时才能添加外键
        (2)需要添加外键约束的表的类型必须是InnoDB
->15.指定表的类型
    create table if not exists 表名(
    字段1 字段类型(字段的长度) 字段属性 字段约束,
    字段2 字段类型(字段的长度) 字段属性 字段约束,
    ...)engine=InnoDB/MyISAM

->16.字段注释和字段默认值
    create table if not exists 表名(
    字段1 字段类型(字段的长度) 字段属性 字段约束 commet "字段注释" default 默认值

->17.删除外键约束
    alter table 表名 drop foreign key '外键名'.

2.DML数据库管理语言(database management language)

->1.添加数据    
    01->指定字段名
        insert into 表名(字段1,字段2,字段三....) values(值1,值2,值3...)
    02->不指定字段名
        insert into 表名 values(值1,值2,值3...)  #values中给定数据的个数必须与字段数相同
    03->同时添加多条数据
        insert into 表名 values(值1,值2,值3...),(值1,值2,值3...).....

->2.删除数数据
    01->条件删除
        delete from 表名 where condition

    02->删除所有数据
        delete from 表名
        truncate [table] 表名

        区别：
        delete 删除数据后索引不会重置
        truncate 会将索引重置
->3.更新数据
    01->根据条件更新部分数据
        update 表名 set 字段1=value1,字段2=value2.... where condition
    02->更新所有的数据
        update 表名 set 字段1=value1,字段2=value2....
->4.查询数据
    01->无条件查询
        001->查询表中所有数据
            select * from 表名        # * 代表所有字段
        002->查询指定的字段
            select 字段1,字段2,字段3... from 表名
    02->条件查询
        001->语法
            select * from 表名 where condition
        002->查询条件
            (1)between...and    查询字段在某一区间内的所有记录
            (2)is null          查询字段值为null的所有记录
            (3)is not null      查询字段值不为null的所有记录
            (4)like             模糊匹配，查询字段中出现给定字符的所有记录
            (5)in               查询字段的值在某个集合内的所有记录 ("张三","李四")

            (6)条件1 and 条件2 and 条件三....条件n
                查询同时满足条件1，条件2，条件n的所有记录数
            (7)条件1 or 条件2 or 条件三....条件n
                查询满足条件1至条件n中某一个条件的所有记录

            (8)not 条件1
                查询不符合条件1所有记录
            (9)条件1 xor 条件2
                查询不同时满足条件1和条件2的所有记录

            (10)算术运算符
                >   <   >=  <=  =       (!=  <>)不等于


        003.分组查询
            select 字段1，字段2... from 表名 group by 字段x
            按照字段x对查询到的数据进行分组
            select c_id,c_name,c_madein from commodity group by c_madein
            将查询出的商品按照插地进行分组
            c_id：商品的id
            c_name:商品的名称
            c_madein:商品的产地
        004.分页查询(每页包含记录数pageNum=5)
                                            开始的索引位置，        每页包含的记录数
            select * from commodity limit (page-1)*pageNum,           pageNum
                                第一页：    0                           5
                                第二页：    5                           5
        005.排序
            select * from commodity order by c_inprice asc/desc
            对查询到的结果按照进价进行升序/降序排列
        006.子查询：已知数码产品的商品分类的ct_id为0,
                    根据该条件从商品表中查询出所有的数码产品
                select * from commodity where c_type=
                (select ct_id from commoditytype where ct_name="数码产品")

        007.链表查询
            表a数据 m行数据                   
                id  name
                1   a
                2   aa
                4   aaaa

            表b的数据   n行数据
                id  name
                1   b
                2   bb
                3   bbb
            001->左链表查询

                select * from a left join b on a.id=b.id
                结果:
                id  name    id  name
                1   a       1   b
                2   aa      2   bb
                4   aaaa
            002->内链表查询
                select * from a inner join b on a.id=b.id
                id  name    id  name
                1   a       1   b
                2   aa      2   bb

            003->右链表查询
                select * from a right join b on a.id=b.id
                id  name        id  name
                1   a           1   b
                2   aa          2   bb
                                3   bbb

            004->全链接
                select * from a full join b 
                结果：m*n行数据
    03.函数
        001->求和函数:sum()
            select sum(c_num) as "所有商品库存" from commodity
            查询所有商品进货量的总和
        002->求平均:avg()
            select avg(c_inprice) as "进价均值" from commodity
            查询所有商品进价的均值
        003->count()
            0001->查询表中所有的记录数
                select count(*) from 表名
            0002->查询某个字段不为null值的所有记录数
                select count(c_num) as "进货量不为null的商品种类数" from commodity
        004->max()/min
            select max(字段名) from 表名
            查询表中某个字段的最大值
            select min(字段名) from 表名
            查询表中某个字段的最小值

mysql数据引擎的区别

delete和truncate区别
delete from 表名 删除数据后索引不会重置
truncate table 表名 会将索引重置

二、python+mysql

1.pymysql的安装

pip install pymysql  

2.使用pymysql链接mysql数据库

#导入模块  
import pymysql  
#链接数据库  
conn = pymysql.connect(host='',user='',passwd='',db='',charset='utf8')

#获取游标
cur = conn.cursor()

3.执行sql语句

sql = "select * from tbale"
cur.execute(sql)
conn.commit() #如果更改数据，需要此步

4.处理结果集

a、获取结果集中所有数据
    all = cursor.fetchall()
b、获取结果集中一条数据
    one = cursor.fetchone()
c、获取结果集中的多条数据
    many = cursor.fetchmany()

5.如何获取结果集中的表的字段

fileds = cursor.description

6.关闭游标和连接

cur.close()
conn.close()
python 分页查询

# 分页获取
import pymysql

# 连接数据库
conn = pymysql.connect(host='', user='', passwd='', db='', charset='utf8')
cur = conn.cursor()
# 定义变量，保存当前的页码
currentpage = 1
# 定义变量保存每页显示的记录数
pagenum = 10
sql = "select * from jobinfo "
cur.execute(sql)
all_value = cur.fetchall()
len1 = len(all_value) // 10
for i in range(1, len1 + 2):
    sql = "select * from jobinfo limit %d,%d"
    startIndex = (i - 1) * pagenum
    cur.execute(sql % (startIndex, pagenum))
    print("********************************** 第{}页  ***************************************".format(i))
    for row in cur.fetchall():
        print(row)

应用：

– 要求:查询出2门及2门以上不及格者的平均成绩

DROP TABLE IF EXISTS `stu_score`;
CREATE TABLE IF NOT EXISTS `stu_score`(
`name` varchar(20),
`subject` varchar(20),
`score` int
);

INSERT INTO `stu_score` VALUES
('张三','数学',90),
('张三','语文',50),
('张三','地理',40),
('李四','语文',55),
('李四','政治',45),
('王五','政治',30);
select * from stu_score;
select  `name`,avg(score) from stu_score 
where `name` in 
(select `name` from stu_score where score <60 group by `name` having count(*) >1) 
group by `name`;
select `name`,avg(score) from stu_score  group by `name` having count(score<60 or NULL) >1;