当前位置:   article > 正文

MySQL数据库---常用名词以及各数据类型及其方法_数据库数量统计用什么字段名

数据库数量统计用什么字段名

###主键约束

  • 主键:表示数据唯一性的字段称为主键
  • 约束: 创建表时给表字段添加的限制条件
  • 主键约束: 限制值唯一且非空
  • 格式: create table t1(id int primary key,name varchar(10));
    insert into t1 values(1,‘AAA’);
    insert into t1 values(1,‘BBB’);//报错 不能重复
    insert into t1 values(null,‘CCC’);//报错 不能为null
    ###主键约束+自增
  • 从历史最大值基础上+1 , 自增数值只增不减
  • 格式: create table t2(id int primary key auto_increment,name varchar(10));
    insert into t2 values(null,‘aaa’); //1
    insert into t2 values(2,‘bbb’); //2
    insert into t2 values(10,‘ccc’); //10
    insert into t2 values(null,‘ddd’); //11
    delete from t2 where id>=10;
    insert into t2 values(null,‘eee’); //12
    ###注释 comment
  • 格式: create table t3(id int primary key auto_increment comment ‘这是id啊’,name varchar(10) comment ‘名字’);
    查看: show create table t3;
    ### ` 的作用
  • 用于修饰表名和字段名 可以省略
    create table t4(id int,name varchar(10));
    ###冗余
  • 由于表设计不够合理导致出现的大量重复数据成为冗余。通过合理拆分表的形式解决冗余问题
    ###事务
  • 什么是事务: 事务是数据库中执行同一业务多条SQL语句的工作单元,事务可以保证多条SQL语句全部执行成功或全部执行失败。
    - 和事务相关的SQL:
  1. 开启事务: begin;
  2. 提交: commit; 将内存中的改动一次性提交到磁盘中
  3. 回滚: rollback; 将内存中的改动清除,回滚到正确的点
  • 验证转账:
    create table user(id int primary key auto_increment,name varchar(10),money int,status varchar(5));
    insert into user values(null,‘超人’,50,‘冻结’),(null,‘蝙蝠侠’,20000,‘正常’);
  • 转账SQL:
    update user set money=money-2000 where id=2 and status=‘正常’;
    update user set money=money+2000 where id=1 and status=‘正常’;
  • 有事务保护的情况下 回滚流程:
    1. 开启事务
      begin;
    2. 蝙蝠侠-2000
      update user set money=money-2000 where id=2 and status=‘正常’;
    3. 此时当前终端查询数据已经发生改变(因为查询到的是内存中的数据),开启另外一个终端查询会发现数据并未改变
    4. 超人+2000
      update user set money=money+2000 where id=1 and status=‘正常’;
    5. 当判断出有一条执行失败,则需要回滚
      rollback;
  • 有事务保护下提交流程:
    insert into user values(null,‘灭霸’,20,‘正常’);
    1. 开启事务
      begin;
    2. 蝙蝠侠-5000
      update user set money=money-5000 where id=2 and status=‘正常’;
    3. 灭霸+5000
      update user set money=money+5000 where id=3 and status=‘正常’;
    4. 当判断出两条全部执行成功则执行提交事务命令
      commit;
      - 保存回滚点
      begin;
      update user set money=200 where id=3;
      savepoint s1;
      update user set money=300 where id=3;
      savepoint s2;
      update user set money=400 where id=3;
      rollback to s2;
      ###SQL分类
      ####DDL Data Definition Language 数据定义语言
      -truncate:删除表并创建新表 自增数值清零
      truncate table 表名;
  • 包括: create 、 alter 、 drop 、 truncate
  • 不支持事务
    ####DML Data Manipulation Language 数据操作语言
  • 包括: insert 、 update 、 delete 、 select(DQL)
  • 支持事务
    ####DQL Data Query Language 数据查询语言
  • 只包括select
    ####TCL Transaction Control Language 事务控制语言
  • 包括: begin 、 commit 、rollback、 savepoint xxx 、 rollback to xxx;
    ####DCL Data Control Language 数据控制语言
  • 分配用户权限相关的SQL

###数据类型
1. 整数: int(m) 和 bigint(m) ,m代表显示长度,不足m长度时补零,需要结合zerofill关键字使用
create table t_int(id int,age int(10) zerofill);
insert into t_int values(1,20);
select * from t_int;
2. 浮点数: double(m,d) m代表总长度 d代表小数长度, 超高精度浮点数decimal(m,d) 当涉及超高精度运算时使用。
25.234 m=5 d=3
3. 字符串:

  • char(m) 固定长度,最大长度255 好处:执行效率高
  • varchar(m) 可变长度, 好处:节省资源, 最大长度65535 但是超高255建议使用text
  • text(m) 可变长度, 最大长度65535
    4. 日期
  • date:只能保存年月日
  • time:只能保存时分秒
  • datetime:年月日时分秒 最大值:9999-12-31 ,默认值为null
  • timestamp:时间戳 年月日时分秒 最大值:2038-1-19 ,默认值为当前系统时间
    create table t_date(t1 date,t2 time,t3 datetime,t4 timestamp);
    insert into t_date values(‘2019-05-17’,null,null,null);
    insert into t_date values
    (null,‘16:29:00’,‘2019-05-17 16:30:00’,null);
    5. 其它

###导入sql文件

  1. windows系统
    把文件放到d盘根目录,然后执行
    source d:/tables.sql;
  2. linux系统
    把文件放到桌面, 然后执行
    source /home/soft01/桌面/tables.sql;
  • 验证:
    show tables;
is null 和 is not null
  1. 查询没有领导的员工姓名和工资
    select ename,sal from emp where mgr is null;
  2. 查询有奖金的员工姓名,工资,奖金
    select ename,sal,comm from emp where comm is not null;
    ####别名
    select ename from emp;
    select ename as ‘姓名’ from emp;
    select ename ‘姓名’ from emp;
    select ename 姓名 from emp;
    ###比较运算符 > < >= <= = !=和<>
  3. 查询工资在2000以内的员工姓名和工资
    select ename,sal from emp where sal<2000;
  4. 查询工作job是manager的员工姓名、工资和工作
    select ename,sal,job from emp where job=‘manager’;

###去重 distinct

  • 把关键字distinct写在需要去重的字段前面
  1. 查询员工表有哪些职位job
    select distinct job from emp;
  2. 查询员工表中的部门编号有哪些?
    select distinct deptno from emp;

and 和 or

- 当需要多个条件同时满足时使用 and
- 当多个条件满足一个就行时使用 or

  1. 查询10号部门中工资低于2000的员工信息
    select * from emp where deptno=10 and sal<2000;
  2. 查询30号部门或工资大于3000的员工信息
    select * from emp where deptno=30 or sal>3000;
    ###模糊查询 like
    - %代表0或多个未知字符
    - _代表单个未知字符
    举例: 第一个字符是x的内容 x%
    最后一个字符是y %y
    第一个字符是x并且最后一个是y x%y
    包含x %x%
    第二个字符是x x%
    以x开头倒数第三个是y x%y
    _
  3. 查询名字中包含a的员工姓名
    select ename from emp where ename like ‘%a%’;
  4. 查询第二个字符是l的员工姓名和工资
    select ename,sal from emp where ename like ‘_l%’;

###between x and y 包含x和y

  1. 查询工资在2000到3000之间的员工姓名和工资(要求包含2000和3000)
    select ename,sal from emp where sal>=2000 and sal<=3000;
    select ename,sal from emp where sal between 2000 and 3000;

in

  1. 查询员工工资为800,1300,1500的员工信息
    select * from emp where sal=800 or sal=1300 or sal=1500;
    select * from emp where sal in(800,1300,1500);

###排序 order by 字段名
- order by 字段名 asc(升序)/desc(降序);

  • 默认升序排序
  1. 查询每个员工的姓名和工资 按照工资降序排序
    select ename,sal from emp order by sal desc;
  2. 查询10号部门每个员工的姓名和工资 按照工资升序排序
    select ename,sal from emp where deptno=10 order by sal;
  • 多字段排序 order by 字段1,字段2;
    select ename,sal,deptno from emp order by deptno,sal desc;
    ;
    ###分页查询
    - limit 跳过条数,请求条数
  1. 查询每个员工的姓名和工资,按照工资升序排序,请求第三页的两条数据
    select ename,sal from emp order by sal limit 4,2;
  2. 请求员工表中工资最高的前三名员工的信息
    select * from emp order by sal desc limit 0,3;

####数值计算 + - * / % 7%2 = mod(7,2)

  1. 查询员工姓名,工资和年终奖(年终奖=5月工资)
    select ename,sal,5
    sal 年终奖 from emp;

###日期相关

  1. SQL语言中的HelloWorld
    select ‘HelloWorld’;
  2. 获取当前系统时间 now()
    select now();
    create table t_date(name varchar(10),birthday datetime);
    insert into t_date values(‘刘德华’,now());
  3. 获取当前的年月日,当前的时分秒 current当前
    select curdate(),curtime();
  4. 从年月日时分秒中提取年月日 和 提取时分秒
    select date(now()), time(now());
  • 查询每个商品的创建时间(年月日)
    select created_time,date(created_time) from t_item;
    select created_time,time(created_time) from t_item;
  1. 从完整的年月日时分秒中提取时间分量
    select extract(year from now());
    select extract(month from now());
    select extract(day from now());
    select extract(hour from now());
    select extract(minute from now());
    select extract(second from now());
  2. 日期格式化
  • 格式:date_format(时间,格式)
  • %Y 四位年 %y 2位年
  • %m 2位月 %c 1位月
  • %d 日
  • %H 24小时 %h 12小时
  • %i 分钟
  • %s 秒
  • 测试:把now() 转成2019年5月20号 15点15分15秒
    select date_format(now(),’%Y年%c月%d号 %H点%i分%s秒’);
  1. 把非标准时间格式转成标准时间
  • 格式: str_to_date(时间,格式)
  • 测试: 把19.04.2019 15点36分20秒 转成标准时间
    select str_to_date(“19.04.2019 15点36分20秒”,"%d.%m.%Y %H点%i分%s秒");
    ###ifnull()
  • age = ifnull(x,y) 如果x值为null则age=y 不为null则age=x;
  • 测试: 修改奖金为null的值为0,不为null则不变
    update emp set comm=ifnull(comm,0);
    ###聚合函数
  • 对查询的多条数据进行统计:平均值、最大值、最小值、求和、计数
  1. 平均值avg(字段名)
  • 查询员工的平均工资
    select avg(sal) from emp;
  • 查询有奖金的员工的平均奖金
    select avg(comm) from emp where comm>0;
  1. 最大值max(字段名)
  • 查询20号部门的最高工资
    select max(sal) from emp where deptno=20;
  1. 最小值min(字段名)
  • 查询有领导的员工最低工资是多少
    select min(sal) from emp where mgr is not null;
  1. 求和sum(字段名)
  • 查询20号部门的工资总和
    select sum(sal) from emp where deptno=20;
  • 把100块钱以内的商品全买下来需要多少钱?
    select sum(price) from t_item where price<100;
  1. 统计数量count(字段名)
  • 查询30号部门有多少人
    select count(*) from emp where deptno=30;
    ###字符串相关
  1. 字符串拼接 concat(s1,s2) s1s2
    select concat(“ab”,“mm”);
  • 查询每个员工的姓名和工资 工资以元为单位
    select ename,concat(sal,‘元’) from emp;
  1. 获取字符串长度 char_length(str);
  • 查询每个员工的姓名和名字长度
    select ename,char_length(ename) from emp;
  1. 获取字符串出现的位置 instr(str,substr)
    select instr(“abcdefg”,“d”);
  2. 转大写和转小写 upper() lower()
    select upper(‘nba’),lower(‘ABC’);
  3. 截取字符串
    • 从左边截取
      select left(“abcdefg”,2);
    • 从右边截取
      select right(“abcdefg”,2);
    • 自由截取substring(str,位置,长度)
      select substring(“abcdefg”,2,3);
  4. 去空白trim()
    select trim(" a b ");
  5. 重复repeat()
    select repeat(‘ab’,2);
  6. 替换replace(‘abcdefg abc’,‘b’,‘m’);
    select replace(‘abcdefg abc’,‘b’,‘m’);
  7. 反转reverse()
    select reverse(“abc”);
声明:本文内容由网友自发贡献,转载请注明出处:【wpsshop博客】
推荐阅读
相关标签
  

闽ICP备14008679号