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,5sal 年终奖 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;

5. 从完整的年月日时分秒中提取时间分量
   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());
6. 日期格式化

• 格式：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秒’);

7. 把非标准时间格式转成标准时间

• 格式: 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;

2. 最大值max(字段名)

• 查询20号部门的最高工资
  select max(sal) from emp where deptno=20;

3. 最小值min(字段名)

• 查询有领导的员工最低工资是多少
  select min(sal) from emp where mgr is not null;

4. 求和sum(字段名)

• 查询20号部门的工资总和
  select sum(sal) from emp where deptno=20;
• 把100块钱以内的商品全买下来需要多少钱？
  select sum(price) from t_item where price<100;

5. 统计数量count(字段名)

• 查询30号部门有多少人
  select count(*) from emp where deptno=30;
  ###字符串相关

1. 字符串拼接 concat(s1,s2) s1s2
   select concat(“ab”,“mm”);

• 查询每个员工的姓名和工资 工资以元为单位
  select ename,concat(sal,‘元’) from emp;

2. 获取字符串长度 char_length(str);

• 查询每个员工的姓名和名字长度
  select ename,char_length(ename) from emp;

3. 获取字符串出现的位置 instr(str,substr)
   select instr(“abcdefg”,“d”);
4. 转大写和转小写 upper() lower()
   select upper(‘nba’),lower(‘ABC’);
5. 截取字符串
   • 从左边截取
     select left(“abcdefg”,2);
   • 从右边截取
     select right(“abcdefg”,2);
   • 自由截取substring(str,位置,长度)
     select substring(“abcdefg”,2,3);
6. 去空白trim()
   select trim(" a b ");
7. 重复repeat()
   select repeat(‘ab’,2);
8. 替换replace(‘abcdefg abc’,‘b’,‘m’);
   select replace(‘abcdefg abc’,‘b’,‘m’);
9. 反转reverse()
   select reverse(“abc”);