###主键约束
- 主键:表示数据唯一性的字段称为主键
- 约束: 创建表时给表字段添加的限制条件
- 主键约束: 限制值唯一且非空
- 格式: 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 tablet4
(id
int,name
varchar(10));
###冗余 - 由于表设计不够合理导致出现的大量重复数据成为冗余。通过合理拆分表的形式解决冗余问题
###事务 - 什么是事务: 事务是数据库中执行同一业务多条SQL语句的工作单元,事务可以保证多条SQL语句全部执行成功或全部执行失败。
- 和事务相关的SQL:
- 开启事务: begin;
- 提交: commit; 将内存中的改动一次性提交到磁盘中
- 回滚: 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=‘正常’; - 有事务保护的情况下 回滚流程:
- 开启事务
begin; - 蝙蝠侠-2000
update user set money=money-2000 where id=2 and status=‘正常’; - 此时当前终端查询数据已经发生改变(因为查询到的是内存中的数据),开启另外一个终端查询会发现数据并未改变
- 超人+2000
update user set money=money+2000 where id=1 and status=‘正常’; - 当判断出有一条执行失败,则需要回滚
rollback;
- 开启事务
- 有事务保护下提交流程:
insert into user values(null,‘灭霸’,20,‘正常’);- 开启事务
begin; - 蝙蝠侠-5000
update user set money=money-5000 where id=2 and status=‘正常’; - 灭霸+5000
update user set money=money+5000 where id=3 and status=‘正常’; - 当判断出两条全部执行成功则执行提交事务命令
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文件
- windows系统
把文件放到d盘根目录,然后执行
source d:/tables.sql; - linux系统
把文件放到桌面, 然后执行
source /home/soft01/桌面/tables.sql;
- 验证:
show tables;
is null 和 is not null
- 查询没有领导的员工姓名和工资
select ename,sal from emp where mgr is null; - 查询有奖金的员工姓名,工资,奖金
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;
###比较运算符 > < >= <= = !=和<> - 查询工资在2000以内的员工姓名和工资
select ename,sal from emp where sal<2000; - 查询工作job是manager的员工姓名、工资和工作
select ename,sal,job from emp where job=‘manager’;
###去重 distinct
- 把关键字distinct写在需要去重的字段前面
- 查询员工表有哪些职位job
select distinct job from emp; - 查询员工表中的部门编号有哪些?
select distinct deptno from emp;
and 和 or
- 当需要多个条件同时满足时使用 and
- 当多个条件满足一个就行时使用 or
- 查询10号部门中工资低于2000的员工信息
select * from emp where deptno=10 and sal<2000; - 查询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_ - 查询名字中包含a的员工姓名
select ename from emp where ename like ‘%a%’; - 查询第二个字符是l的员工姓名和工资
select ename,sal from emp where ename like ‘_l%’;
###between x and y 包含x和y
- 查询工资在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
- 查询员工工资为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(降序);
- 默认升序排序
- 查询每个员工的姓名和工资 按照工资降序排序
select ename,sal from emp order by sal desc; - 查询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 跳过条数,请求条数
- 查询每个员工的姓名和工资,按照工资升序排序,请求第三页的两条数据
select ename,sal from emp order by sal limit 4,2; - 请求员工表中工资最高的前三名员工的信息
select * from emp order by sal desc limit 0,3;
####数值计算 + - * / % 7%2 = mod(7,2)
- 查询员工姓名,工资和年终奖(年终奖=5月工资)
select ename,sal,5sal 年终奖 from emp;
###日期相关
- SQL语言中的HelloWorld
select ‘HelloWorld’; - 获取当前系统时间 now()
select now();
create table t_date(name varchar(10),birthday datetime);
insert into t_date values(‘刘德华’,now()); - 获取当前的年月日,当前的时分秒 current当前
select curdate(),curtime(); - 从年月日时分秒中提取年月日 和 提取时分秒
select date(now()), time(now());
- 查询每个商品的创建时间(年月日)
select created_time,date(created_time) from t_item;
select created_time,time(created_time) from t_item;
- 从完整的年月日时分秒中提取时间分量
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()); - 日期格式化
- 格式: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秒’);
- 把非标准时间格式转成标准时间
- 格式: 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);
###聚合函数 - 对查询的多条数据进行统计:平均值、最大值、最小值、求和、计数
- 平均值avg(字段名)
- 查询员工的平均工资
select avg(sal) from emp; - 查询有奖金的员工的平均奖金
select avg(comm) from emp where comm>0;
- 最大值max(字段名)
- 查询20号部门的最高工资
select max(sal) from emp where deptno=20;
- 最小值min(字段名)
- 查询有领导的员工最低工资是多少
select min(sal) from emp where mgr is not null;
- 求和sum(字段名)
- 查询20号部门的工资总和
select sum(sal) from emp where deptno=20; - 把100块钱以内的商品全买下来需要多少钱?
select sum(price) from t_item where price<100;
- 统计数量count(字段名)
- 查询30号部门有多少人
select count(*) from emp where deptno=30;
###字符串相关
- 字符串拼接 concat(s1,s2) s1s2
select concat(“ab”,“mm”);
- 查询每个员工的姓名和工资 工资以元为单位
select ename,concat(sal,‘元’) from emp;
- 获取字符串长度 char_length(str);
- 查询每个员工的姓名和名字长度
select ename,char_length(ename) from emp;
- 获取字符串出现的位置 instr(str,substr)
select instr(“abcdefg”,“d”); - 转大写和转小写 upper() lower()
select upper(‘nba’),lower(‘ABC’); - 截取字符串
- 从左边截取
select left(“abcdefg”,2); - 从右边截取
select right(“abcdefg”,2); - 自由截取substring(str,位置,长度)
select substring(“abcdefg”,2,3);
- 从左边截取
- 去空白trim()
select trim(" a b "); - 重复repeat()
select repeat(‘ab’,2); - 替换replace(‘abcdefg abc’,‘b’,‘m’);
select replace(‘abcdefg abc’,‘b’,‘m’); - 反转reverse()
select reverse(“abc”);