单表单查询总结

最新推荐文章于 2024-05-06 21:49:15 发布

weixin_30526593

最新推荐文章于 2024-05-06 21:49:15 发布

阅读量255

点赞数

原文链接：http://www.cnblogs.com/saoqiang/p/11377505.html

版权

存储引擎 :

数据的存储方式 -- 存储引擎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

weixin_30526593

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
单表单查询总结

存储引擎 :数据的存储方式 -- 存储引擎engines innodb常用 myisam memory 使用不同的存储引擎,数据是以不同的方式存储的show engines; 查看存储引擎innodb 2个文件 mysql5.6以上默认的存储方式 transaction 事务保证数据安全数据的完整性而设置的概念 row-le...
复制链接

扫一扫