240
我爱毛绒绒~~~~~~~

数据库 - MySql语句

库的增删改查

  • create database testdb1;  #创建数据库testdb1
    
    create database testdb2 charset='gbk';   #创建指定编码的库testdb2
    
  • drop database testdb2; #删除testdb2
    
  • alter database testdb2 charset='utf8'; #修改testdb2编码为utf-8
    
  • show databases; #查看所有数据库
    
    show create database testdb2; # 查看单个数据库testdb2
    

表的增删改查

  • 前置

    use testdb1;  #切换库
    
    select database();  #查看所在库的名称
    
  • create table t1(id int,name char); #创建一个id是int格式,name是char格式的t1
    
    create table t1(id int,name char); #创建一个id是int格式,name是char格式,且长度是4的t1
    
  • drop table t2;
    

    1. 删除字段名称

    alter table t2 drop hobbies; #1.删除字段
    

    2. 删除字段属性

    alter table t1 modify id int not null; #1.删除自增
    
    alter table t1 drop primary key; #2.删除字段主键
    
  • alter table t1 modify name char(16); 
    

    1. 修改表名

    alter table t1 rename t11;
    

    2. 修改字段名称及属性

    alter table t11 modify department varchar(6) unique; #修改字段属性
    
    alter table t11 change  describle varchar(12) default '无' ; #修改字段名称,不能修改属性
    
  • show tables;  #查看所有表
    show create table t2; 
    describe t2; 
    desc t2; #简写
    

数据的增删改查

  • insert into t1 value(1,'甲'); #单独增加一条数据
    
    insert into t1 value(2,'乙'),(3,'丙'),(4,'丁'); #一次性增加多个
    
  • delete from t1 where id=1; #删除id=1的数据
    
    delete from t1; #清空t1表中所有数据
    
  • update t1 set name='ABCD' where id=4; #修改id=4的数据
    
  • select * from t1; #查看表中所有数据
    
    select name from t1;  #查看表中单独字段的数据
    
    select * from pre_ucenter_members \G;
    

约束条件

  • default默认

      create table t1(id int,name varchar(6),gender enum('female','male') default 'male' );
      insert t1(name,id) values('jack',1);
    
  • unique唯一
    1.单列唯一

    create table t2(id int unique,name varchar(6),gender enum('male','female') default 'male'); # 默认值要加字段名
    

    2.联合唯一

    create table t3(id int,ip char(16),port int,unique(ip,port));
    insert t3 values(1,'127.0.0.1',8080),(2,'127.0.0.1',8081),(3,'127.0.0.1',8082);
    insert t3 values(3,'127.0.0.2',8080),(2,'127.0.0.12',8081),(3,'127.0.0.12',8082);
    
  • primary key主键
    1、单单从约束效果上来看primary key等价于not null + unique,非空且唯一!!!
    2、它除了有约束效果之外 它还是Innodb存储引擎组织数据的依据,Innodb存储引擎在创建表的时候必须要有primary key,因为它类似于书的目录 能够帮助提示查询效率并且也是建表的依据

    create table t4(id int primary key);
    insert t4 values(1);
    insert t4 values(null); #报错:Column 'id' cannot be null
    insert t4 values(1); #报错:Duplicate entry '1' for key 'PRIMARY'
    

    一张表中有且只有一个主键 如果你没有设置主键 那么会从上往下搜索直到遇到一个非空且唯一的字段将它自动升级为主键

    create table t5(id int not null unique,name varchar(6),age int);
    

    如果表中没有主键也没有其他任何的非空且唯一字段 那么Innodb会采用自己内部提供的一个隐藏字段作为主键,隐藏意味着你无法使用到它 就无法提示查询速度
    一张表中通常都应该有一个主键字段 并且通常将id/uid/sid字段作为主键

    • 单个字段主键
    create table t6(id int primary key,name varchar(6));
    
    • 联合主键(多个字段联合起来作为表的主键 本质还是一个主键)
    create table t7 (id int,name varchar(6),phone int,primary key(id,phone));
    
  • auto_increment自增
    注意auto_increment通常都是加在主键上的 不能给普通字段加
    以后在创建表的id(数据的唯一标识id、uid、sid)字段的时候
    id int primary key auto_increment

    #不能给非主键加自增
    create table t9(id int  auto_increment,name varchar(6),age int);
    # 报错:Incorrect table definition; there can be only one auto column and it must be defined as a key
    
    create table t8(id int primary key auto_increment,name varchar(6),age int);
    insert t8 values('rose',13); #报错:Column count doesn't match value count at row 1
    insert t8(name,age) values('rose',13);
    

    补充

    删除表中数据后 主键的自增不会停止; 清空表数据并且重置主键

    truncate t8;
    

表关系

外键就是用来帮助我们建立表与表之间关系的:foreign key

一对多
    外键字段建在多的一方
多对多
    自己开设第三张存储
一对一
    建在任意一方都可以 但是推荐你建在查询频率较高的表中
1、一对多表关系   外键字段建在多的一方
2、在创建表的时候 一定要先建被关联表
3、在录入数据的时候 也必须先录入被关联表
  • 表关系例子

    create table grades(cid int primary key auto_increment,caption varchar(16) not null);
    create table teachers(tid int primary key auto_increment,tname varchar(6) not null);
    create table students( sid int primary key auto_increment, sname varchar(6) not null, gender enum('male','female') default 'male', class_id int,  foreign key((class_id) references grades(cid) on update cascade on delete cascade);
    create table courses( cid int primary key, cname varchar(6), teacher_id int, foreign key(cid) references grades(cid) on update cascade on delete cascade, foreeign key(teacher_id) references teachers(tid) on update cascade on delete cascade );
    create table results(
        rid int primary key auto_increment,
        student_id int,
        course_id int,
        number int not null,
        foreign key(student_id) references students(sid)
        on update cascade  on delete cascade,
        foreign key(course_id) references courses(cid)
        on update cascade on delete cascade);
    

    级联操作

    on update cascade 
    on delete cascade
    
    • 复制表
      1.不能复制主键,外键
      create table t3 select * from t1;
      
      2.指定复制
      create table t4 select * from t1 where id >3;
      

关键字查询

  • where

    作用:是对整体数据的一个筛选操作

    1. 精确查找

    #1.查找uid是7的数据
    select uid,username,email from pre_ucenter_members where uid >7;
    
    #2.查找uid在3和7之间的数据
    select uid,username,email from pre_ucenter_members where uid >=3 and uid <=7;
    select uid,username,email from pre_ucenter_members where uid between 3 and  7;
    
    #3.查找regip是'127.0.0.1','113.4.146.14','27.189.221.197'的数据
    select username from pre_ucenter_members where regip='127.0.0.1' or regip='113.4.146.14' or regip='27.189.221.197';
    select username from pre_ucenter_members where regip in ('127.0.0.1','113.4.146.14','27.189.221.197');
    
    #4.查看值为null
    select department from t11 where describle is null;
    
    #5.查找uid不在3和9之间的数据
    select username,email from pre_ucenter_members where uid not between 3 and 9;
    
    #6.查找email不在'qianfan1@126.com','qianfan2@126.com','qianfan3@126.com'的数据
    select username,email from pre_ucenter_members where email not in('qianfan1@126.com','qianfan2@126.com','qianfan3@126.com');
    

    2. 模糊匹配

    模糊查询
        like
            %  匹配任意多个字符
            _  匹配任意单个字符
    
    #1.查看email中含有qianfan字符的数据
    select username from pre_ucenter_members where email like '%qianfan%';
    
    #2.查看username中是五个字符的数据
    select username,email from pre_ucenter_members where username like '_____';
    
  • group by

    分组之后 最小可操作单位应该是组 还不再是组内的单个数据
    没有严格模式:
        返回的是分组之后 每个组的第一条数据
        但是这不符合分组的规范:分组之后不应该考虑单个数据 而应该以组为操作单位(分组之后 没办法直接获取组内单个数据)
    有严格模式:报错
    

    1. 基本命令

    #1.获取每个部门最大薪资
    select post,max(salary) from emp group by post;
    
    #2.获取每个部门最低薪资,as作为别称,可加可不加
    select post as '部门',min(salary) '最高薪资' from emp group by post;
    
    #3.获取各部门平均值
    select post,avg(salary) from emp group by post;
    
    #4.获取各部门总值
    select post,sum(salary) from emp group by post;
    
    #5.统计各部门人数
    select post,count(name) from emp group by post; #可以
    select post,count(id) from emp group by post; #可以
    select post,count(post_comment) from emp group by post; #不可以,null
    

    2. group_concat

    group_concat不单单可以支持你获取分组之后的其他字段值 还支持拼接操作

    #1.查询分组之后的部门名称和每个部门下所有的员工姓名
    select post,group_concat(name) from emp group by post;
    
    #2.查询分组之后的部门名称和每个部门下所有的员工姓名和薪资
    select post,group_concat(name,':',salary) from emp group by post;
    

    3. 分组注意事项

    关键字where和group by同时出现的时候group by必须在where的后面
    where先对整体数据进行过滤之后再分组操作
    where筛选条件不能使用聚合函数
    
    # 报错:Invalid use of group function
    select name,age from emp where max(salary) >10000;
    
    #不分组 默认整体就是一组
    select max(salary) from emp;
    
    #统计各部门年龄在30岁以上的员工平均薪资
    select post,avg(salary) from emp where age>30 group by post;
    
  • having

    having的语法根where是一致的
    只不过having是在分组之后进行的过滤操作
    即having是可以直接使用聚合函数的
    
    #1.统计各部门年龄在30岁以上的员工平均工资并且保留平均薪资大于10000的部门
    select post,avg(salary) from emp where age >30 group by post having avg(salary)>1000;
    
  • distinct
    一定要注意 必须是完全一样的数据才可以去重!!!

    #会去重
    select distinct age from emp; 
    
    #不会去掉重复的年龄
    select distinct id,age from emp;
    
  • order by

    order by默认是升序asc,可以省略不写;也可以修改为降序:desc

    #1.薪资升序排列
    select * from emp order by salary;
    
    #2.查询薪资降序,相同薪资的年龄升序
    select * from emp order by salary desc, age;
    
    #3.统计各部门年龄在10岁以上的员工平均工资并且保留平均薪资大于1000的部门,然后对平均工资降序排序
    select post,avg(salary) from emp 
      where age>10 group by post 
      having avg(salary) > 1000
      order by avg(salary) desc;  
    
  • limit

    #1.输出五条数据
    select * from emp limit 5;
    
    #2.输出从1开始的五条数据
    select * from emp limit 0,5;
    
    #3.输出从8开始的五条数据
    select * from emp limit 7,5;
    
  • 正则

    正则是一门独立的语言
    在python中如果你想使用正则需要借助于re模块
      面试题
          1.re模块中常用的方法
              findall:分组优先展示
                  ^j.*(n|y)$
                  不会展示所有正则表达式匹配到的内容
                  而仅仅展示括号内正则表达式匹配到的内容
              match:从头匹配
              search:从整体匹配
          2.贪婪匹配与非贪婪匹配
              正则表达式默认都是贪婪匹配的
              将贪婪变成非贪婪只需要在正则表达式后面加?
              .*  贪婪
              .*? 非贪婪
    
    select * from emp where name regexp '^j.*(n|y)$';
    

多表操作

  • 表查询
    1.inner join,内连接

    只拼接两张表中公有的数据部分

    select * from emp1 inner join  dep on emp1.dep_id=dep.id;
    

    2.left join,左连接

    左表所有的数据都展示出来 没有对应的项就用NULL

    select * from emp1 left join dep on emp1.dep_id=dep.id;
    

    3.right join,右连接

    右表所有的数据都展示出来 没有对应的项就用NULL

    select * from emp1 right join dep on emp1.dep_id =dep_id;  
    

    4.union,全连接
    左右两表所有的数据都展示出来

    select * from emp1 left join dep on emp1.dep_id=dep_id 
    union select * from emp1 right join  dep on emp1.dep_id=dep_id;
    
posted @   水开白  阅读(19)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· 百万级群聊的设计实践
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
· 永远不要相信用户的输入:从 SQL 注入攻防看输入验证的重要性
点击右上角即可分享
微信分享提示