• 欢迎光临~

mysql-增删改查

开发技术 开发技术 2022-10-20 次浏览

mysql-增删改查

# ### mysql
ctrl + l 清屏
ctrl + c 终止
[linux]
service mysql start   启动mysql
service mysql stop    停止mysql
service mysql restart 重启mysql

[windows] 在超级管理员权限下才能使用
net start mysql  启动mysql
net stop  mysql  停止mysql

# ### part1
mysql -u用户名 -p密码 -hip地址

# (1) 登录到本地mysql 默认root 是最高权限账户
mysql -uroot -p123456
localhost => 127.0.0.1 本地IP地址
# (2) 退出mysql
exit 或者 q  退出
# (3) 远程连接mysql 服务器
mysql -uroot -p -h192.168.80.135

# ### part2
# 查询当前登录用户是谁
select user()
# 设置密码
set password = password("123456");
# 去除密码
set password = password("");


# ### part3
# mysql 创建用户 192.168.80.135
create user "ceshi01"@"192.168.111.222" identified by "111"; # 给具体某个ip设置账户(一般是公网ip)
create user "ceshi02"@"192.168.111.%"   identified by "222"; # 给具体某个网段下的所有ip设置一个账户
create user "ceshi03"@"%";                                   # 所有ip都能登录,不需要密码

# 通过windows链接linux的mysql ,要通过vnet8 nat模式下的ip创建用户才可使用
create user "ceshi04"@"192.168.80.1" identified by "333";

# 查看用户权限
show grants for "ceshi03"@"%";
# GRANT USAGE ON *.* TO 'ceshi03'@'%' | USAGE 无任何权限
grant 权限 on 数据库.表名 to "用户名"%"ip地址" identified by "密码";
"""
# 权限:
select  查询数据库的权限
insert  插入数据库的权限
update  更新数据库的权限
delete  删除数据库的权限
*  代表所有
G 代表垂直分布查看
"""

grant select on *.* to "ceshi03"@"%" identified by "222"
# all 代表所有的权限
grant all on *.* to "ceshi03"@"%" identified by "222"
# 查看所有数据库
show databases
# 移除权限
revoke select on *.* from "ceshi03"@"%"
# 删除账户
drop user "ceshi03"@"%";
# 刷新权限
flush privileges

# ### part4
"""
linux 
sudo find / -name db1
sudo su root 切换到root用户才可以
cd /var/lib/mysql
能看到对应的数据库
"""
# (1) 操作[数据库] (文件夹)
# 创建数据库
    create database db1 charset utf8;
查
    # 查看数据库
    show databases
    # 查看数据库的建库语句
    show create database db1
改
    # alter 更改数据库的字符集
    alter database db1 charset gbk
删
    # 删除数据库db1
    drop database db1;

# (2) 操作[数据表] (文件)
"""选择使用哪个数据库创建表 use 数据库名称 """# int 整型 char字符
    # 字段名1 类型1 , 字段名2 类型2 ... 
    create table t1(id int , name char)
查
    # 查询所有数据表
    show tables
    # 查看建表语句
    show create table t1;
"""
CREATE TABLE `t2` (
  `id` int(11) DEFAULT NULL,
  `name` char(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
"""
    # 查看表结构
    desc t1
改
    # modify 只能改变数据类型
    alter table t1 modify name char(5)
    # change 连带字段名和数据类型一起改变
    alter table t1 change name NAME char(4)
    alter table t1 change NAME name char(5)
    # add    添加字段
    alter table t1 add age int;
    # drop   删除字段 column 列
    alter table t1 drop column age;
    # rename 更改表名 
    alter table t1 rename t1_1;
    
删
    # 删除表t1_1
    drop table t1_1

# (3) 操作记录 (文件内容)
"""mysql null 相当于 python None"""
增:
    # 一次插入一条数据
    insert into t1(id,name) values(1,"xboy1")
    # 一次插入多条数据
    insert into t1(id,name) values(2,"xboy2"),(3,"xboy3"),(4,"xboy4")
    # 不指定具体字段,默认把所有字段值插入一遍
    insert into t1 values(5,"xboy5")
    # 可以具体指定某个字段进行插入
    insert into t1(name) values("xboy6")
    
查:
    # * 代表所有
    select * from t1;
    select id,name from t1;
    
改:
    # update 表名 set 字段=值 where 条件
    update t1 set name = "王文" where id = 1
    # 如果不加条件,所有数据都改了
    update t1 set name = "王文"
    
删:
    # 删除id为1的这条数据
    delete from t1 where id = 2
    delete from t1 
    # 重置数据表 (包括重置id)
    truncate table t1;


# ### part5 常用数据类型

# 整型 
tinyint   1个字节 有符号(-128~127)  无符号(unsigned) (0~255) 小整型值
int       4个字节 有符号(-21亿 ~ 21亿 左右 ) 无符号 (0 ~ 42亿 左右) 大整型值,精度范围更广
    
    create table t1(id int , sex tinyint);
    insert into t1 values(2200000000,128);
    insert into t1 values(2100000000,127);

# 浮点型
float(255,30)   单精度
double(255,30)  双精度
decimal(65,30)  金钱类型,使用字符串的形式保存小数

    """float 小数点默认保留5位 , double 小数点默认保留16位 decimal 保留个整数 存在四舍五入"""
    create table t2(  f1 float(5,2) , f2 double(5,2) , f3 decimal(5,2) );
    insert into t2 values(1.55555555,1.55555555,1.55555555) # 存在四舍五入
    
    create table t3( f1 float , f2 double , f3 decimal )
    insert into t3 values(1.8288888888888888888888888888888888,1.8288888888888888888888888888888888,1.8288888888888888888888888888888888)

    create table t3_3(f1 float(5,2));
    insert into t3_3 values(12.5678); #  12.57

# 字符串 char(255) varchar(255)
char(11)       定长: 固定开辟11个长度的空间(手机号,身份证),  速度比较快
varchar(11)    变长: 最大开启字符长度为11的空间(文章评论,0~255字符,短信), 相比较于char类型,速度稍慢
text           文本类型,针对于 文章,论文,小说
    create table t4( c char(11) ,v varchar(11) , t text )
    insert into t4 values("1111","地方","sldfjsdkfjlskdfjksdfksdjklfsjklf")
    select concat(c,":",v,":",t) from t4


# ### part1. 数据类型 - 时间
date YYYY-MM-DD 年月日 (纪念日)
time HH:MM:SS   时分秒 (体育竞赛)
year YYYY       年份值 (酒的年份,82年拉菲)
datetime  YYYY-MM-DD HH:MM:SS 年月日 时分秒 (登录时间,下单时间)

    create table t5(d date,t time , y year , dt datetime);
    insert into t5 values("2019-11-21","09:30:30","2019","2019-11-21 09:30:30")
    insert into t5 values(now(),now(),now(),now())
    
timestamp YYYYMMDDHHMMSS 自动更新时间(不需要手动写入,修改数据时候,自动更新,记录最后一次修改时间)
    create table t6(dt datetime , ts timestamp);
    insert into t6 values(null,null)
    insert into t6 values(20191121093728,20380101050505)
    insert into t6 values(20191121093728,20390101050505) error # 时间戳最多到2038年的某一天
    
# mysql 内置函数
    now    获取当前时间
    concat 拼接各个参数
    user() 获取当前登录的用户


# mysql 
select user()
concat 拼接 concat(参数1,参数2,参数3 ... ) 把所有参数拼接在一起

# 枚举 和 集合
    enum  枚举: 从一组数据中选一个(性别)
    set   集合: 从一组数据中选多个(自动去重)

    create table t5(
    id int,name char(10),
    money float(6,2),
    sex enum("man","woman"),
    hobby set("eat","drink","piao","du","chou")
    )

    # 正常写法
    insert into t5(id,name,money,sex,hobby) values(1,"zhangsan",9.9,"woman","piao,du,chou")
    # 自动去重
    insert into t5(id,name,money,sex,hobby) values(1,"zhangsan",9.9,"woman","chou,drink,drink,drink,drink,drink")

# ### 配置linux 中 utf8编码集
# (1) find / -name my.cnf 
# (2) 找到之后,发现是链接,打开mysql.cnf 找到其中真正引入的路径
    !includedir /etc/mysql/conf.d/       (客户端配置)
    !includedir /etc/mysql/mysql.conf.d/ (服务端配置)
# (3) nano /etc/mysql/conf.d/mysql.cnf  default-character-set=utf8 添加到文件中
#     nano /etc/mysql/mysql.conf.d/mysql.cnf  character-set-server=utf8添加到文件中

# (4) 重启 service mysql restart 




# ### part2 约束

# 关于约束的添加和删除
# 1 添加/删除 约束 not null
    #alter table 表名 modify 字段名 类型
    alter table t1 modify id int not null
    alter table t1 modify id int

# 2 添加/删除 unique 唯一索引
    # alter table 表名 add unique(id)
    alter table t1 add unique(id)
    alter table t1 drop index id
    
# 3 添加/删除 primary key
    # alter table 表名 add primary key(id);
    alter table t1 add primary key(id);
    alter table t1 drop primary key;
    
# 4 添加/删除 foreign key 外键
    alter table student1 drop foreign key student1_ibfk_1; #删除
    alter table student1 add foreign key(classid) references class1(id) #添加

# 约束 : 对插入的数据进行限制,不满足约束的条件就直接报错
unsigned    无符号
not null    不为空
default     设置默认值
unique      唯一约束,数据唯一不能重复
primary key 主键,唯一不为空的值,表达这条数据的唯一性
auto_increment 自增加一,(一般针对于主键 或者 unique 进行自增)
zerofill    零填充 , int(6) , 位数不够6为,拿0来填充
foreign key 外键,把多张表通过一个字段联合在一起

# unsigned    无符号
    create table t7(id int unsigned)
    insert into t7 values(5)
    insert into t7 values(-1000) error
    
# not null    不为空
    create table t8(id int not null , name varchar(255));
    insert into t8 values(1,"tianqi")
    insert into t8 values(null,"tianqi") error

# default     设置默认值
    create table t9(id int not null , name varchar(255) default "常远" );
    insert into t9 values(1,null)
    insert into t9(id) values(2)
    
# unique      唯一约束,数据唯一不能重复 [默认创建索引,通过索引可以加快查询速度,相当于字典的目录]
    """默认允许插入多个null空值 UNI """
    create table t10(id int unique , name char(10) default "张龙");
    insert into t10 values(1,null)
    insert into t10(id) values(1) error
    insert into t10(id) values(2)
    insert into t10(id) values(null)
    insert into t10(id) values(null)

# primary key 主键,唯一不为空的值,表达这条数据的唯一性
    """在一个表中,只能有一个字段标记成主键,一般标记id"""
    # 原型 PRI 
    create table t11(id int not null unique ,name char(15) default "周永玲")
    insert into t11 values(1,"你好")
    insert into t11 values(null,"你好")

    # primary key 创建主键
    create table t12(id int primary key , name char(15) default "周永玲")
    insert into t12 values(1,"你好")

    # 两者同时存在 , 优先显示primary key 作为主键,
    create table t12_2(id int primary key , name char(15) not null unique);
    
    # 一个表只能让一个字段变成主键
    create table t12_3(id int primary key , name char(15) primary key); error

# auto_increment 自增加一,(一般针对于主键 或者 unique 进行自增)
    create table t13(id int primary key auto_increment , name char(15) default "尹家平");
    insert into t13 values(null,"李四")
    insert into t13 values(100,"张三")
    insert into t13(id) values(null)
    insert into t13(name) values("王二麻子")
    # 使用默认值进行插入;
    insert into t13 values();

# zerofill    零填充 , int(6) , 位数不够6为,拿0来填充
    create table ceshi111(id int(6) zerofill );
    insert into ceshi111 values(2)
    insert into ceshi111 values(222222222)

# 删除:
    # (1) delete from 表 where 条件 (删除数据,保留id)
    delete from t13 where id = 1
    delete from t13 ;
    insert into t13(id,name) values(null,"王文")
    
    # (2) truncate table 表名 (删除数据,重置id ,重置表)
    truncate table t13
    insert into t13(id,name) values(null,"王文")

# ### part3
# 1.联合唯一约束 : unique(字段1,字段2,..... ) 把多个字段拼在一起表达一个唯一的数据
# (1) 联合唯一索引 (在非空的情况,显示为主键 PRI)
    create table t1_server(id int , server_name char(10) not null,ip char(15) not null , port int not null , unique(ip,port));
    insert into t1_server values(1,"aa","192.168.111.15",3306);
    insert into t1_server values(1,"aa","192.168.111.15",3307);
    insert into t1_server values(1,"aa","192.168.111.16",3306);
    insert into t1_server values(1,"aa",null,null); error 
    
# (2) 联合唯一索引 (在为空的情况,显示索引 MUL 代表普通索引)
    create table t2_server(id int , server_name char(10) not null,ip char(15)  , port int  , unique(ip,port));    
    insert into t2_server values(1,"aa","192.168.111.15",3306);
    insert into t2_server values(1,"aa","192.168.111.15",3306); error
    insert into t2_server values(1,"aa","192.168.111.17",3306);
    insert into t2_server values(1,"aa",null,null); # 允许插入多个控制,推荐使用第一种

    | id   | server_name | ip             | port |
    +------+-------------+----------------+------+
    |    1 | aa          | 192.168.111.15 | 3306 |
    |    1 | aa          | 192.168.111.17 | 3306 |
    |    1 | aa          | NULL           | NULL |
    |    1 | aa          | NULL           | NULL |

# (3) 联合唯一索引 和 主键 是否可以同时存在呢?可以同时存在 primary key 是真正的主键,联合唯一索引恢复成MUL索引状态
    # 方法一
    create table t3_server(id int , server_name char(10) not null,ip char(15) not null  , port int not null  , unique(ip,port))
    alter table t3_server add primary key(id);
    
    # 方法二
    create table t4_server(id int primary key , server_name char(10) not null,ip char(15) not null  , port int not null  , unique(ip,port))

    # (了解) unique(ip,port) 联合唯一索引 , primary key(ip,port) 联合唯一主键 用法一样,区别在于后者不能在继续添加主键了


# foreign key 外键,把多张表通过一个字段联合在一起
    """外键的要求: 主动关联的这张表设置外键,要求被关联的表字段必须具有唯一属性 (unique 或者 primary key)"""
    student:
        id       name       age   classname   ....   address
        1     changyuan      81   python8              世外桃源 ... 
        2     zhouyongling   7    python8              世外桃源  ...
        3     wangwen        18   python9              富丽华冠冕堂皇大酒店 ... 
 

    # 为了避免出现过多的字段,可以采取分表的形式,来减少冗余数据,提升查询的效率;
    student1:
        id     name     age   classid
        1   changyuan    81     1
        2   zhouyongling 7      1
        3   wangwen      18     2
        
    class1:
        id  classname
        1   python8
        2   python9


    # 创建class1表
    create table class1(id int , classname varchar(255));
    
    # 设置classid 为主键或者唯一索引
    alter table class1 add unique(id);
    
    # 创建student1表
    create table student1(
    id int primary key  auto_increment,
    name varchar(255) not null,
    age int not null,
    classid int,
    foreign key(classid) references class1(id)    
    );

    # 插入数据
    insert into class1 values(1,"python8")
    insert into class1 values(2,"python9")
    insert into student1 values(null,"changyuan",81,2);
    insert into student1 values(null,"zhouyongling",7,1);
    insert into student1 values(null,"wangwen",7,2);


    # 删除class1 如果这条数据在多张表中被使用,直接删除会报错,因为有外键关联
    delete from class1 where id = 1
    # 把关联的数据删掉之后,才可以
    delete from student1 where id = 2;
    delete from class1 where id = 1;

    # 联级删除 联级更新 (谨慎操作)
    """
    联级删除  on delete cascade
    联级更新  on update cascade
    """
    
    # 创建class2
    create table class2(id int unique ,  classname varchar(255));
    # 创建student2
    create table student2(
    id int primary key  auto_increment,
    name varchar(255) not null,
    age int not null,
    classid int,
    foreign key(classid) references class2(id) on delete cascade on update cascade     
    );

    # 插入数据
    insert into class2 values(1,"python8");
    insert into class2 values(2,"python9");
    insert into student2 values(null,"changyuan",81,2);
    insert into student2 values(null,"zhouyongling",7,1);
    insert into student2 values(null,"wangwen",18,2);

    # 联级删除class2数据
    delete from class2 where id = 2
    # 联级更新
    update class2 set id = 100   where classname = "python8"

    # 表和表之间的关系
    (1) 一对一  表1:id m1 m2 m3 m4(表2的id数据)  表2: id m5 m6 m7
    (2) 一对多 或多对一 : 1个班级 可以对应多个学生 把学生作为主动关联的表,其中设置一个外键,去关联那个唯一的数据
    (3) 多对多 : 一个学生可以对应多个学科,一个学科可以多个学生学习,
                 一本书可以对应多个作者,一个作者可以写多本书
                 
    xueke (表1)
    id   name 
    1    math
    2    english
    3    huaxue
    

    student (表2)
    id   name
    1    wangwen
    2    changyuan
    3    zhouyongling

    relation (表3) 把 xid 和 sid 设置成外键 关联xueke 的 id  和 student 的 id
    
    xid  sid
    1     1
    1     2
    1     3
    2     1
    2     2
    2     3
    3     1
    3     2
    3     3


# 存储引擎 : 存储数据的方法
"""
show engines
"""
# 概念理解:
表级锁: 如果有人修改这张表,就直接上锁,其他人无法修改,速度慢,不能并发 (MyISAM)
行级锁: 如果有人修改这个表中的一个记录,当前这条记录会上锁,其他数据可以进行修改,允许更大的并发和更快的速度 (InnoDB)
事务处理 : 如果执行sql语句,在全部执行成功之后,在选择提交,如果操作时,有一条失败,直接回滚,恢复成初始状态
    begin :  开启事务
    commit:  提交数据
    rollback:回滚数据

# 存储引擎:
MyISAM :  是5.6版本之前,默认的存储引擎,支持表级锁
InnoDB :  是5.6版本之后,默认的存储引擎,支持行级锁,能够抗住更大的并发
BLACKHOLE : 黑洞,用来同步数据的,场景发生在服务器集群,用在:主从数据库 [主:查询 ,从:增删改]
MEMORY : 把数据存储在内存当中,也可以作为缓存

    create table myisam1(id int , name char(10)) engine = myisam;
    myisam1.frm  表结构
    myisam1.MYD  表数据
    myisam1.MYI  表索引

    create table innodb1(id int , name char(10)) engine = innodb;
    innodb1.frm  表结构
    innodb1.ibd  表数据 表索引

    create table memory1(id int , name char(10)) engine = memory;
    memory1.frm  只有一个表结构,数据在内存中
    
    create table blackhole1(id int , name char(10)) engine = blackhole;
    blackhole1.frm 


# 基础查询

# ### part1 单表查询
# sql 查询语句的完整语法 
''' select .. from .. where .. group by .. having .. order by .. limit .. '''

# 一.where 条件的使用
    """功能:对表中的数据进行筛选过滤"""

    """
    语法:
        1.判断的符号:
        = > < >= <= != <> 不等于
        2.拼接条件的关键字
        and or not
        3.查询的区间范围值 between
        between 小值 and 大值 [小值,大值] 查询两者之间这个范围的所有数据
        4.查询具体某个值的范围 in
        in(1,-9,-10,"a") 指定范围
        5.模糊查询 like "%" 通配符
            like "%a"  匹配以a结尾的任意长度的字符串
            like "a%"  匹配以a开头的任意长度的字符串
            like "%a%" 匹配含有a字母的任意长度字符串
            like "_a"  个数一共2个字符,必须以a结尾,前面这个字符随意
            like "a__" 个数一共3个字符,必须以a开头,后面这个两字符随意
    """
    # (1) 单条件的查询
    # 查询部门是sale的所有员工姓名:
    select emp_name from employee where post = "sale";
    
    # (2) 多条件的查询
    # 部门是teacher,收入大于10000的所有数据
    select * from employee where post = "teacher" and  salary > 10000;

    # (3) 关键字between .. and 
    # 收入在1万到2万之间的所有员工姓名和收入
    select emp_name,salary from employee where  salary between 10000 and 20000;
    # 收入不在1万到2万之间的所有员工姓名和收入
    select emp_name,salary from employee where  salary not between 10000 and 20000;

    # (4) null关键字 在搜索的时候,要用is进行判定,不能用=
    # 查询 post_comment 是空的NULL 所有数据
    select * from employee where post_comment = NULL 数据是空,搜索不到
    select * from employee where post_comment is NULL
    select * from employee where post_comment is not NULL

    update employee set post_comment = "" where id = 1
    select * from employee where post_comment = '';

    # (5) 关键字 in 的查询
    # 查询收入是 3000 或 5000 或者 4000 或者 8000  所有员工姓名和收入
    select emp_name,salary from employee where salary=3500 or salary=5000 or salary=8300 or salary=4000;
    # 用in优化,在小括号里面写上可能的值
    select emp_name,salary from employee where salary in (3500,5000,8300,4000);
    # 不在括号中的值,搜索出来
    select emp_name,salary from employee where salary not in (3500,5000,8300,4000);

    # (6) 关键字 like 模糊查询
    # (1) % 通配符
    select emp_name,age,post from employee where emp_name like "%on";
    # (2) _ 通配符
    select emp_name,age,post from employee where emp_name like "a_e_";

    # (7) concat 
    select concat("姓名:",emp_name,"薪资:",salary) as aaa from employee;
    # concat_ws(拼接的符号,参数1,参数2,参数3 ... )
    select concat_ws(" : ",emp_name,salary) as bbb from employee;
    # 可以在sql中使用四则运算(+ - * /)
    select concat_ws(" : ",emp_name, salary * 12 ) as bbb from employee;

# 二.group by 子句 分组,分类
    """group by 对数据进行分类, by 后面接的字段,就是select要搜索的字段"""
    select sex from employee group by sex;
    select post from employee group by post;
    # group_concat 按照分组形式进行字段的拼接
    select group_concat(emp_name),post from employee where id>1 group by post;

    # 聚合函数
        # 统计总数 count   *所有
        select count(*) from employee
        # 统计最大值 max 
        select max(salary) from employee
        # 统计最小值 min
        select min(salary) from employee
        # 统计平均值 avg
        select avg(salary) from employee
        # 统计总和 sum
        select sum(salary) from employee

    # 一般来说 使用时 分组 + 聚合函数 配合使用
        # 1. 查询部门名以及各部门的平均薪资
        select post , avg(salary) from employee group by post;
        # 2. 查询部门名以及各部门的最高薪资
        select post , max(salary) from employee group by post;
        # 3. 查询部门名以及各部门的最低薪资
        select post , min(salary) from employee group by post;
        # 4. 查询公司内男员工和女员工的个数
        select sex,count(*) from employee group by sex
        # 5. 查询部门名以及部门包含的所有员工名字
        select group_concat(emp_name) , post from employee group by post
        select emp_name,post from employee group by post,emp_name

# 三.having 查询数据之后在进行过滤,一般是配合group by使用, 主要用分组后过滤
    # 找出各部门的平均薪资,并且大于10000以上的所有部门
    select post,avg(salary) from employee group by post having avg(salary) > 10000;
    # 1.查询各岗位内包含的员工个数小于2的岗位名,员工名,个数
    select post,group_concat(emp_name),count(*) from employee group by post having count(*) < 2
    # 2.查询各岗位平均薪资小于10000的岗位名、平均工资
    select post,avg(salary) from employee group by post having avg(salary) < 10000
    # 3.查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资
    select post,avg(salary) from employee group by post having avg(salary) between 10000 and  20000
    select post,avg(salary) from employee group by post having avg(salary) > 10000 and avg(salary) < 20000

# 四.order by 排序 , 按照什么字段进行排序
    # 默认值asc  升序排序
    # 按照desc   降序排序
    select * from employee order by age  (默认升序)
    select * from employee order by age desc (降序)

    # 1. 查询所有员工信息,先按照age升序排序,如果age相同则按照hire_date降序排序
    select emp_name,sex,age,hire_date,post from employee order by age,hire_date desc
    # 2. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列
    select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) desc
    # 3. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列
    select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) asc

# 五.limit 限制查询的条数 (数据分页)
    limit m,n m代表从第几条开始查询,n代表查询几条  m=0 代表的是第一条
    select * from employee limit 0,5 从第一条开始查,查5条
    select * from employee limit 5,5 从第六条开始查,查5条
    # 只查询一条数据
    select * from employee limit 1
    # 想要瞬间得到数据表中,最后一条数据
    select * from employee order by id desc limit 1
    # 拿到最后三条数据
    select * from employee order by id desc limit 3

# 六.(了解) 可以使用正则表达式查询数据 (不推荐使用,不好用效率不高)
    select * from employee where emp_name regexp ".*on$" # .*? 的?号不识别
    select * from employee where emp_name regexp "^程";
    select * from employee where emp_name regexp "^程.*金";


# ### part2 多表查询
    # 内连接:(内联查询 inner join ) : 两表或者多表满足条件的所有数据查询出来[两个表之间共同具有的数据]
    """
    # 两表查询
    select  字段 from  表1 inner join 表2 on 条件
    # 多表查询
    select  字段 from  表1 inner join 表2 on 条件 inner join 表3 on 条件
    
    
    """
    
    # 基本语法 inner join on 接的表与表之间的必要连接条件
    select * from employee inner join department on employee.dep_id = department.id
    # 用as 起别名 (推荐)
    select * from employee as e inner join department as d on e.dep_id = d.id
    # 可以省略as
    select * from employee e inner join department d on e.dep_id = d.id
    
    # where 实现的就是内联查询
    select * from employee,department where employee.dep_id = department.id
    select * from employee as e,department as d where e.dep_id = d.id
    
    # 外连接
        # (1) 左连接 (左联查询 left join ) : 以左表为主,右表为辅,完整查询左表所有数据,右表没有的数据补NULL
        """ select 字段 from 表1 left join 表2 on 条件 """
        select * from employee left join department on employee.dep_id = department.id
        
        # (2) 右连接 (右联查询 right join) : 以右表为主,左表为辅,完整查询右表所有数据,左表没有的数据补NULL
        """ select 字段 from 表1 right join 表2 on 条件 """
        select * from employee right join department on employee.dep_id = department.id
        # (3) 全连接 (union) 所有数据全都合并起来
        select * from employee left join department on employee.dep_id = department.id
        union
        select * from employee right join department on employee.dep_id = department.id
        
# ### part3 子查询
    """
    子查询: 嵌套查询
        (1) 子查询是查询的语句当中又嵌套的另外一条sql语句,用括号()抱起来,表达一个整体
        (2) 一般应用在from 子句后面表达一张表,或者 where 子句后面表达一个条件
        (3) 速度从快到慢 单表查询速度最快  ->  联表查询 ->  子查询
    """
    
    # (1)找出平均年龄大于25岁以上的部门
    # 普通的where 相当于内联查询
    select 
        d.id,d.name
    from 
        employee e,department d
    where
        e.dep_id = d.id
    group by 
        d.id,d.name
    having 
        avg(e.age) > 25;

    # (2) inner join 
    select 
        d.id,d.name
    from 
        employee e inner join  department d on e.dep_id = d.id
    group by 
        d.id,d.name
    having 
        avg(e.age) > 25;

    # (3) 子查询
    # 1.先选出平均年龄大于25岁的部门id
    select dep_id from employee group by dep_id having avg(age) > 25;
    # 2.通过部门id,找部门名字
    select name from department where id in (201,202)
    # 3.综合拼接:
    select id,name from department where id in (select dep_id from employee group by dep_id having avg(age) > 25)


    # (2)查看技术部门员工姓名
    # 1.普通where查询

    select 
        e.name
    from 
        employee e ,department d
    where     
        e.dep_id = d.id  and d.name = "技术"
        


    # 2.inner join 实现
    select 
        e.name
    from 
        employee e inner join department d on e.dep_id = d.id 
    where     
          d.name = "技术"

    # 3.子查询
    # 1.找技术部门对应id
    select id from department where name = "技术"
    
    # 2.通过id找员工姓名
    select name from employee where employee.dep_id = ?
    
    # 3.综合拼接
    select name from employee where employee.dep_id = (select id from department where name = "技术")

    # (3)查看哪个部门没员工

    # 联表写法
    select 
        d.id,d.name
    from 
        employee e right join department d on e.dep_id = d.id
    where
        e.dep_id is NULL
    
    # 子查询
    # 1.先查询,员工都在哪些部门
    select dep_id from employee group by dep_id => (200,201,202,204)
    # 2.把不在部门列表中的数据找出来
    select from department where id not in (1)
    # 3.综合拼接
    select id,name  from department where id not in (select dep_id from employee group by dep_id)
    

    # (4)查询大于平均年龄的员工名与年龄
    # 假设平均年龄是18岁
    select name,age from employee where  age > ?
    # 找平均年龄
    select avg(age) from employee
    # 综合拼装
    select name,age from employee where  age > (select avg(age) from employee)

    # (5)把大于其本部门平均年龄的员工名和姓名查出来
# employee
+----+------------+--------+------+--------+
| id | name       | sex    | age  | dep_id || dep_id | avg(age) |
+----+------------+--------+------+--------+
|  1 | egon       | male   |   18 |    200 |
|  2 | alex       | female |   48 |    201 |
|  3 | wupeiqi    | male   |   38 |    201 |
|  4 | yuanhao    | female |   28 |    202 |
|  5 | liwenzhou  | male   |   18 |    200 |
|  6 | jingliyang | female |   18 |    204 |
+----+------------+--------+------+--------+
# department
+------+--------------+
| id   | name         |
+------+--------------+
|  200 | 技术         |
|  201 | 人力资源     |
|  202 | 销售         |
|  203 | 运营         |
+------+--------------+
    # 1.先计算平均年龄
    select  dep_id,avg(age) from employee group by dep_id
+--------+----------+
| dep_id | avg(age) |
+--------+----------+
|    200 |  18.0000 |
|    201 |  43.0000 |
|    202 |  28.0000 |
|    204 |  18.0000 |
+--------+----------+
    # 2.把子查询查出来的数据和employee作拼接,联合成一张更大的表,做一次单表查询;
    select 
        *
    from 
        employee as t1 inner join (1) as t2 on t1.dep_id = t2.dep_id

    # 3.综合拼接
    select
        *
    from 
        employee as t1 inner join (select  dep_id,avg(age) as avg_age from employee group by dep_id) as t2 on t1.dep_id = t2.dep_id
    
    # 4.把额外的比较的条件加进去
    select
        *
    from 
        employee as t1 inner join (select  dep_id,avg(age) as avg_age from employee group by dep_id) as t2 on t1.dep_id = t2.dep_id
    where 
        t1.age > t2.avg_age

    # (6)查询每个部门最新入职的那位员工  # 利用上一套数据表进行查询;
    # 1.找每个部门最大的入职时间
    select post,max(hire_date) as max_date  from employee group by post
    
    # 2.把子查询查出来的数据和employee联合成一张更大的表,做一次单表查询
    select 
    from 
        employee as t1 inner join (1) as t2 on t1.post = t2.post
    where
        t1.hire_date = t2.max_date

    # 3.综合拼接
    select 
        t1.emp_name,t1.hire_date
    from 
        employee as t1 inner join (select post,max(hire_date) as max_date  from employee group by post) as t2 on t1.post = t2.post
    where
        t1.hire_date = t2.max_date


    # (7)带EXISTS关键字的子查询
    """
    exists 关键字表达存在
        如果内层sql 能够查到数据, 返回True , 外层sql执行查询语句
        如果内层sql 不能查到数据, 返回False, 外层sql不执行查询语句
    """
    select * from employee where exists (select * from employee where id = 1)
    
        
    
    
    """
    子查询总结:
        子查询可以单独作为一个子句,也可以作为一个表或者某个字段
        一般用在from where select 子句后面
        通过查询出来的临时表,可以跟任意的表重新拼接,组成更大的表,在通过筛选达成自己的目的
    """

 

程序员灯塔
转载请注明原文链接:mysql-增删改查
喜欢 (0)