单表单查询总结

缺乏、安全感 2021-10-26 13:34 361阅读 0赞

# 存储引擎 : #

数据的存储方式 -- 存储引擎engines   innodb常用  myisam   memory
        使用不同的存储引擎,数据是以不同的方式存储的
    show engines; 查看存储引擎
    
    innodb 2个文件
        mysql5.6以上 默认的存储方式
        transaction 事务   保证数据安全 数据的完整性而设置的概念
        row-level locking 行级锁
        table-level locking 表级锁
        foreign keys 外键约束
        树tree - 加速查询 (树形结构(数据+树) + 表结构)
    myisam 3个文件
        mysql5.5以下 默认的存储方式
        table-level locking 表级锁（保护数据安全）
        树tree - 加速查询 (树形结构 + 数据 + 表结构)
    memory 1个文件#内存级别
        基于hash
    外键约束 保护了数据间关联的数据安全 不能随意修改
    show create table staff;#查看表的结构,
    create table myisam_t (id int,name char(18)) engine=myisam;#修改了存储方式
    create table memory_t (id int,name char(18)) engine=memory;
    
    两个表之间的关系
    
    创建一个库
    创建三张表 : myisam innodb memory
        向三张表中写数据
        重启server端
        重新登陆 查看你写进去的三条数据还在不在

# 操作文件夹（库） #

# 操作文件夹（库）
    # # 增：create database db1;   #create database 文件夹名;
    # # 查：show databases;  #查看所有文件夹名
    # # 改：alter database db1;  #官方没有直接修改数据库名称的命令
    # # 删除: drop database db1; #drop database 库名;
    
    #查看当前库中有多少张表 先切换
    # show tables;
    
    先切换到文件夹下：use db1

# 表操作 #

## 查看表结构 
    desc 表   show create table 表
    # 查看
    # select * from user;
    # select * from 表名;
    删除
    drop table 表名

## 创建 crate table 键表 与写入 ##

id name hire_date gender spec salary
    
    create table empl452oyee(
    id int not null unique auto_increment,
    emp_name varchar(20) not null,
    sex enum('male','female') not null default 'male', #大部分是男的
    age int(3) unsigned not null default 28,
    hire_date date not null,
    post varchar(50),
    post_comment varchar(100),
    salary double(15,2),
    office int, #一个部门一个屋子
    depart_id int
    );
    
    
    增加内容
    insert into empl452oyee(emp_name,sex,age,hire_date,post,salary,office,depart_id) values
    ('egon','male',18,'20170301','老男孩驻沙河办事处外交大使',7300.33,401,1), #以下是教学部
    ('alex','male',78,'20150302','teacher',1000000.31,401,1),
    ('wupeiqi','male',81,'20130305','teacher',8300,401,1),
    ('yuanhao','male',73,'20140701','teacher',3500,401,1);

### 约束 ###

unsigned 非负数
    not null 不可以为空
    default 默认值
    unique 唯一
        联合唯一 unique(id,name)
    auto_increment（自增） int 自带not null 前提unique
    primary key （设置主键）
        # 联合主键 primary key(id,name)
    foreign key （外键关联）关联的外表字段必须是unique

### 数据类型 ###

#### tinyint int ####

create table int_t (
              ti tinyint,     # 有符号****(-128，127)
              i  int,        # 有符号*****(-2 147 483 648，2 147 483 647)
              tiun tinyint unsigned,  #无符号(0，255)
              iun int unsigned #无符号(0，4 294 967 295)
        );
        
    写超过范围的 最大不过范围
    create table fd_t(
            f float,      # 精度问题 小数点后5位 # *****单精度
            d double,     # 精度更高但也不准确
            f2 float(5,2),# 在精确位四舍五入输入12345 确是999.99 126是126.00 3.125 是3.12
            d2 double(5,2)# 在精确位四舍五入
    )
    单精度 与双精度
    insert into a1 values(123456);
    select * from a1;
    
    create table dec_t(
        dec1 decimal,#默认取整
        dec2 decimal(30,20)#精确后20位
    )

#### 字符串 ####

char    0-255    定长字符串 定长存储 存储速度更快 占用更多的空间  *****
        char(12)
        alex --> 'alex        ' --> 12的长度
    varchar 0-65535  变长字符串 变长存储 存储速度慢  占用的空间小  ****
        varchar(12)
        'alex' --> 'alex4' --> 5的长度
    
    
    
    手机号码/身份证号码 : char
    用户名/密码 : 有一定范围弹性 char
    评论 : varchar
    
    时间和空间
        时间换空间
        空间换时间
    
        create table ch_t(
            c char,
            c2 char(5),
            vc2 varchar(5)
        )

#### enum和set ####

单选题,只能从有限的条件中选择
    create table enum_t(
        id int,
        name char(12),
        gender enum('男','女','不详')
    )
    
    gender性别(男 女 不详)
    
    多选题,从有限的条件中选
    create table set_t(
        id int,
        name char(12),
        hobby set('抽烟','喝酒','烫头','搓脚')
    )

#### 时间 ####

内置函数  now()   当前时间
    datetime  打卡时间/***** 年月日时分秒
    date      员工生日/   *****年月日
    time      上课时间（每天固定） 时间
    year 年
    timestamp  2038年由于表示范围的问题,导致用的少了
    create table time_t2(
        dt datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,   # 表示的范围更大,还能拥有timestamp的特点
        d  date,
        t  time,
        y  year,
        ts timestamp    # 不能为空,默认值是当前时间,在修改的时候同时更新时间
    )

## 删除 drop table ##

## 修改表 ##

# id age name varchar(255)
    # alter table 表名 rename 新表明
    # alter table 表名 add 新字段 类型(宽度) 约束;
                    # add 新字段 类型(宽度) 约束 after id
                    # add 新字段 类型(宽度) 约束 first
    # alter table 表名 drop 字段名;
    # alter table 表名 change 旧字段 新字段 类型(宽度) 约束;
                    # change name username char(12) not null
                    # change name name char(12) not null
                    # change name name varchar(255) after id;
    # alter table 表名 modify 存在的字段 新类型(新宽度) 新约束;
                    # modify name char(12) unique;
                    # modify name char(12) unique after id;

## 查看表结构 ##

**desc 表 show create table 表**

# 关联表 #

一对一 客户 和学生 先键客户
    多对多 产生第3张表
    多对一 先键1
    # 3. 两张表关联
    '''
    
    staff
    id  name    gender  post
    1   张三      男       1
    2   李四      男       1
    3   太亮      女       2
    4   alex    不详      3
    5   wusir   男       2
    6   小攀      男       2
    '''
    '''
    post
    id  post    door_num    phone_num
    1   销售部 402          80800088
    2   教质部 403          80800090
    3   CEO     401          80800089
    '''
    
    # create table post(
    #     id int primary key,
    #     post char(12) not null unique,
    #     door_num int,
    #     phone_num char(12)
    # );
    
    # create table staff(
    #     id int primary key auto_increment,
    #     name char(12) not null,
    #     gender enum('男','女','不详'),
    #     post int,
    #     foreign key(post) references post(id) on update cascade
    # );
    
    # 表与表之间的关系
        # 一对多  foreign key
        # 多对多  出现第三张表 :有两个foreign key
        # 一对一  foreign + unique

# 数据操作 #

## 增加 ##

# id name gender
    # 增加
        #增insert. into表名(字段1,字段2) values(值1,值2);
    
        # insert into 表名 value (1,'alex','female')
        # insert into 表名 values (1,'alex','female'),(1,'alex','female');
        # insert into 表名(name,gender) values ('alex','female');             *****
        # insert into 表名(name,gender) select (username,sex) from day38.表2;

## 删除 ##

delete from 表名 where 条件

## 修改 ##

# update 表名 set 字段=值1 where 条件
    # update 表名 set 字段1=值1,字段2=值2 where 条件

## 单表查询 ##

# 查看
    # select * from user;
    # select * from 表名;
    
    select 字段 from 表名 where 约束条件
    # select name,salary from staff;
    # select * from staff where gender = 'female';
    # select * from staff where gender = 'male' and salary between 10000 and 20000;
    
        # select:
            # select 函数/算数表达式
            # select */列名/其他表达式 from 表名
            # select distinct */列名/其他表达式 from 表名
            # 重命名 : select 字段1 as 新名1,字段名 as 新名字 from 表名
            # 重命名 : select 字段名 新名字 from 表名
        # where:
            # 范围
                # > < = <= >= !=/<>
                # between and
                # in (a,b,c) not in 
            # 模糊查询
                # like
                    # %任意长度任意内容   _一个长度任意内容
                # regexp  正则
                    # 放正则
            # 身份运算符 -->null
                # is , is not
            # 逻辑运算符
                # and or not
                
    
    1.找到表:from
    2.拿着where指定的约束条件，去文件/表中取出一条条记录
    3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组
    4.执行select（去重）
    5.将分组的结果进行having过滤 asc（升序从小到大）desc(降序 从大到小)
    7.限制结果的显示条数

##### 列题 #####

1. 查看岗位是teacher的员工姓名、年龄
    select emp_name,age from employee where post = 'teacher';
    select 姓名,年龄 from 表名 where 查看的条件
    
    2. 查看岗位是teacher且年龄大于30岁的员工姓名、年龄
    select emp_name,age from employee where post='teacher' and age > 30; 
    
    3. 查看岗位是teacher且薪资在9000-10000范围内的员工姓名、年龄、薪资
    select emp_name,age,salary from employee where post='teacher' and salary between 9000 and 10000;
    
    4. 查看岗位描述不为NULL的员工信息（*）
    select * from employee where post_comment is not null;
    
    5. 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资
    select emp_name,age,salary from employee where post='teacher' and salary in (10000,9000,30000);
    
    6. 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资
    select emp_name,age,salary from employee where post='teacher' and salary not in (10000,9000,30000);
    
    7. 查看岗位是teacher且名字是jin开头的员工姓名、年薪
    select emp_name,salary*12 from employee where post='teacher' and emp_name like 'jin%';

### 分组 group by 与聚合函数 ###

# select * from 表 group by 字段名
    #     分组 group by（生成一个新表）
    #     根据某个重复率比较高的字段进行的
    #     这个字段有多少种可能就分成多少个组
    #         根据性别分组 : 男的一组 女的一组
    #         根据部门分组 : 销售一组 教学一组 ...
    #     去重
    #     一旦分组了就不能对具体某一条数据进行操作了
    #         永远都是考虑 这组 xxx
    #     group_concat : 只用来做最终的显示,不能结果操作其他数据（属于这个组的所有人）
    
    与聚合 联系 聚合函数
    #     99.99%的情况都是和分组一起用的
    #     如果没有和分组一起用,默认一整张表是一组
    #     count(id)  / count(*) 计数 :每个组对应几条数据
    #     max 求最大值: 这个组中某字段的最大值
    #     min 求最大值: 这个组中某字段的最小值
    #     avg 求平均值
    #     sum 求和值
    #
    #     select min(hire_date) from employee
    #         求employee中所有人里最早入职的时间
    #     select min(hire_date) from employee group by post
    #         求每个部门中入职最早的时间
    
    分组只会显示第一个
    select * from book group by 作者;

###### group列题 ######

1. 查询岗位名以及岗位包含的所有员工名字
    select post,group_concat(emp_name) from employee group by post;
    select 显示(岗位)，字段（姓名） from 表名 group by 查询的岗位;
    
    2. 查询岗位名以及各岗位内包含的员工个数
    select post,count(id) from employee group by post;
    
    3. 查询公司内男员工和女员工的个数
    
    4. 查询岗位名以及各岗位的平均薪资
    
    5. 查询岗位名以及各岗位的最高薪资
    
    6. 查询岗位名以及各岗位的最低薪资
    
    7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
    
    
    # 2.找出最贵的图书的价格
    # select max(price) from book;
    # select price,book_name from book order by price desc limit 1;
    
    # 3.求所有图书的均价
    # select avg(price) from book;
    
    # 4.将所有图书按照出版日期排序
    # select * from book order by pub_date;
    
    # 5.查询alex写的所有书的平均价格
    # select avg(price) from book where author = 'alex'
    
    # 扩展: 求所有人自己出版的图书的平均价格
    # select author,avg(price) from book group by author
    # 扩展: 求所有人自己出版的图书的平均价格>30的所有人
    # select author from book group by author having  avg(price)>30
    
    # 6.查询人民音乐不好听出版社出版的所有图书
    # select * from book where press = '人民音乐不好听出版社';
    
    # 7.查询人民音乐出版社出版的alex写的所有图书和价格
    # select * from book where press = '人民音乐不好听出版社' and author = 'alex';
    
    # 8.找出出版图书均价最高的作者
    # select author,avg(price) as avg_p from book group by author order by avg_p desc limit 1;
    
    # 9.找出最新出版的图书的作者和出版社
    # select author,press from book order by pub_date desc limit 1;
    
    # 10.显示各出版社出版的所有图书
    # select press,group_concat(book_name) from book group by press;
    
    # 11.查找价格最高的图书，并将它的价格修改为50元
    # select max(price) from book;
    # update book set price = 50 where price = 70
    
    # 12.删除价格最低的那本书对应的数据
    # select min(price) from book;
    # delete from book where price = 5;
    
    # 13.将所有alex写的书作者修改成alexsb
    # update book set author = 'alexsb' where author = 'alex';
    
    # 14.select year(publish_date) from book
    # 自己研究上面sql语句中的year函数的功能，完成需求：
    # 将所有2017年出版的图书从数据库中删除
    # delete from book where year(publish_date) = 2017;

### having 对组过滤 必须先分组 在组的后面 ###

就是一个对组进行筛选的条件
    
    要部门人数大于3个人的部门 count(id)>3
    select post from employee group by post having count(id)>3;
    
    having的问题 不建议你用
    select id,emp_name,age from employee having age>20;
    select id,emp_name from employee group by age having age>20;

### order by 排序 ###

#     order by 字段 (默认升序)
    #     order by 字段 asc（升序从小到大）
    #     order by 字段 desc(降序  从大到小)
    #
    #     order by 字段1,字段2
    #     order by 字段 asc,字段2 desc
    #     order by 字段 desc,字段2 asc
    #     order by 字段 desc,字段2 desc

### limit 取一个区间 ###

1.显示分页
    limit m,n(耗内存)
          表示从m+1开始,取n条
          limit 0,6 表示从1开始取6条
          limit 6,6 表示从7开始取6条
          limit 12,6 表示从13开始取6条
          limit 18,6 表示从19开始取6条
    2.取前n名
        limit n   m默认为0
        跟order by一起用
    limit n offset m :从m+1开始,取n条

# python操作数据库 #

先安装pymysql模块
    查询
    # import pymysql
    # conn = pymysql.Connection(host='127.0.0.1', user='root', password="123",
    #                  database='day40')
    # cur = conn.cursor()  # 游标 数据库操作符
    # sql = 'select emp_name,salary from employee where age = %s'
    # cur.execute(sql,(80,))
    # # 获取结果
    # ret1 = cur.fetchone()#查一条
    # ret2 = cur.fetchmany(2)#指点多少条
    # ret3 = cur.fetchall()#查所有
    # print(ret1)#会记录位置
    # print(ret2)
    # print(ret3)
    # cur.close()#关闭游标
    # conn.close()#关闭连接
    
    
    # insert语句 写入
    # import pymysql
    # conn = pymysql.Connection(host='127.0.0.1', user='root', password="123",
    #                  database='day40')
    # cur = conn.cursor()  # 游标 数据库操作符
    # username = '太亮'
    # sql = 'insert into employee(id,emp_name,sex,age,hire_date) values(%s,%s,"female",88,20170707)'
    # cur.execute(sql,(21,username,))
    # conn.commit()#提交 没提交没用
    # cur.close()
    # conn.close()
    
    
    # 文件操作数据库
    # conn = pymysql.Connection(host='127.0.0.1', user='root', password="123",
    #                database='day40')
    # cur = conn.cursor()  # 游标 数据库操作符
    # cur.excute(sql,(值1,))
        # 查询
            # fetchone fethmany(10) fetchaLL
        # 插入\更新\删除
            # cur.execute(里面学数据库语句)
            # conn.commit()#
    # cur.close()
    # conn.close()

转载于:https://www.cnblogs.com/saoqiang/p/11377505.html