1 数据类型
1.1 命名规则
l 字段名必须以字母开头,尽量不要使用拼音
l 长度不能超过30个字符(不同数据库,不同版本会有不同)
l 不能使用SQL的保留字,如where,order,group
l 只能使用如下字符az、AZ、0~9、$ 等
l Oracle习惯全大写:USER_NAME,mysql习惯全小写:user_name
l 多个单词用下划线隔开,而非java语言的驼峰规则
1.2 字符
l char长度固定,不足使用空格填充,最多容纳2000个字符,char(11)存储abc,占11位。查询速度极快但浪费空间
l varchar变长字符串,最多容纳4000个字符,varchar(11)存储abc,只占3位。查询稍慢,但节省空间。Oracle为varchar2
l 大文本: 大量文字(不推荐使用,尽量使用varchar替代)
以utf8编码计算的话,一个汉字在u8下占3个字节
注:不同数据库版本长度限制可能会有不同
1.3 数字
l tinyint,int整数类型
l float,double小数类型
l numberic(5,2) decimal(5,2)—也可以表示小数,表示总共5位,其中可以有两位小数
l decimal和numeric表示精确的整数数字
1.4 日期
l date 包含年月日
l time时分秒
l datetime包含年月日和时分秒
l timestamp时间戳,不是日期,而是从1970年1月1日到指定日期的毫秒数
1.5 图片
l blob 二进制数据,可以存放图片、声音,容量4g。早期有这样的设计。但其缺点非常明显,数据库庞大,备份缓慢,这些内容去备份多份价值不大。同时数据库迁移时过大,迁移时间过久。所以目前主流都不会直接存储这样的数据,而只存储其访问路径,文件则存放在磁盘上。
2 字段约束
2.1 主键约束
主键约束:如果为一个列添加了主键约束,那么这个列就是主键,主键的特点是唯一且不能为空。通常情况下,每张表都会有主键。
添加主键约束,例如将id设置为主键:
主键自增策略:当主键为数值类型时,为了方便维护,可以设置主键自增策略(auto_increment),设置了主键自增策略后,数据库会在表中保存一个AUTO_INCREMENT变量值,初始值为1,当需要id值,不需要我们指定值,由数据库负责从AUTO_INCREMENT获取一个id值,作为主键值插入到表中。而且每次用完AUTO_INCREMENT值,都会自增1. AUTO_INCREMENT=1
create table abc(
id int primary key auto_increment
);
insert into abc values(null);
insert into abc values(null);
insert into abc values(null);
select * from abc;
2.2 非空约束
非空约束:如果为一个列添加了非空约束,那么这个列的值就不能为空,但可以重复。
添加非空约束,例如为password添加非空约束:
create table user(
id int primary key auto_increment,
password varchar(50) not null
);
show tables;
insert into user values(null,null);//不符合非空约束
insert into user values(null,'123');//OK
2.3 唯一约束
唯一约束:如果为一个列添加了唯一约束,那么这个列的值就必须是唯一的(即不能重复),但可以为空。
添加唯一约束,例如为username添加唯一约束及非空约束:
create table test(
id int primary key auto_increment,
username varchar(50) unique--唯一约束
);
show tables;
insert into test values(null,'lisi');
insert into test values(null,'lisi');--username的值要唯一,重复会报错的
select * from test;
3 基础函数、条件查询
3.1 数据准备
2.1.1 部门表 dept
CREATE TABLE dept(
deptno int primary key auto_increment NOT NULL,
dname VARCHAR(20),
loc VARCHAR(13)
);
INSERT INTO dept VALUES(1,'accounting','一区');
INSERT INTO dept VALUES(2,'research','二区');
INSERT INTO dept VALUES(3,'operations','二区');
3.1.2 员工表 emp
CREATE TABLE emp(
empno int primary key auto_increment NOT NULL,
ename VARCHAR(10),
job VARCHAR(10),
mgr int,
hiredate DATE,
sal double,
comm NUMERIC(7,2),
deptno int
);
INSERT INTO emp VALUES(100,'jack','副总',NULL,'2002-05-03',90000,NULL,1);
INSERT INTO emp VALUES(200,'tony','总监',100,'2015-02-02',10000,2000,2);
INSERT INTO emp VALUES(300,'hana','经理',200,'2017-02-02',8000,1000,2);
INSERT INTO emp VALUES(400,'leo','员工',300,'2019-02-22',3000,200.12,2);
INSERT INTO emp VALUES(500,'liu','员工',300,'2019-03-19',3500,200.58,2);
3.2 基础函数
lower
SELECT 'ABC',LOWER('ABC') from dept; --数据转小写
upper
select upper(dname) from dept --数据转大写
length
select length(dname) from dept --数据的长度
substr
SELECT dname,SUBSTR(dname,1,3) FROM dept; --截取[1,3]
concat
select dname,concat(dname,'123') X from dept --拼接数据
replace
select dname,replace(dname,'a','666') X from dept --把a字符替换成666
infull
select ifnull(comm,10) comm from dept2 #判断,如果comm是null,用10替换
round & ceil & floor.
select comm,round(comm) from emp;--直接四舍五入取整
select comm,round(comm,1) from emp;--四舍五入并保留一位小数
select comm,ceil(comm) ,floor(comm) from emp;--ceil向上取整,floor向下取整
uuid
select uuid();--返回uuid:a08528ca-741c-11ea-a9a1-005056c00001
now
select now() -- 年与日 时分秒
select curdate() --年与日
select curtime() --时分秒
year & month & day
select now(),hour(now()),minute(now()),second(now()) from emp ; --hour()时 minute()分 second()秒
select now(),year(now()),month(now()),day(now()) from emp ;--year()年 month()月 day()日
转义字符
select 'ab'cd' -- 单引号是一个SQL语句的特殊字符
select 'ab\'cd' --数据中有单引号时,用一个\转义变成普通字符
3.3 条件查询
3.3.1 distinct(使用distinct关键字,去除重复的记录行)
SELECT loc FROM dept;
SELECT DISTINCT loc FROM dept;
3.3.2 where(注意:where中不能使用列别名!!)
select * from emp
select * from emp where 1=1 --类似没条件
select * from emp where 1=0 --条件不成立
select * from emp where empno=100 --唯一条件
select * from emp where ename='tony' and deptno=2 --相当于两个条件的&关系
select * from emp where ename='tony' or deptno=1 --相当于两个条件的|关系
select name, sal from emp where sal=1400 or sal=1600 or sal=1800;
-- 或
select name, sal from emp where sal in(1400,1600,1800);
select name, sal from emp where sal not in(1400,1600,1800);
3.3.3 like(通配符%代表0到n个字符,通配符下划线_代表1个字符)
select * from emp where ename like 'l%' --以l开头的
select * from emp where ename like '%a' --以a结束的
select * from emp where ename like '%a%' --中间包含a的
select * from emp where ename like 'l__' --l后面有两个字符的 _代表一个字符位置
3.3.4 null
select * from emp where mgr is null --过滤字段值为空的
select * from emp where mgr is not null --过滤字段值不为空的
3.3.5 between and
SELECT * FROM emp
select * from emp where sal>3000 and sal<10000
select * from emp where sal>=3000 and sal<=10000--等效
select * from emp where sal between 3000 and 10000--等效
3.3.6 limit
分数最高的记录:按分数排序后,limit n,返回前n条。Oracle做的很笨,实现繁琐,后期有介绍,而mysql做的很棒,语法简洁高效。在mysql中,通过limit进行分页查询:
select * from emp limit 2 --列出前两条
select * from emp limit 1,2 --从第二条开始,展示2条记录
select * from emp limit 0,3 --从第一条开始,展示3条记录--前三条
3.3.7 order by
SELECT * FROM emp order by sal #默认升序
SELECT * FROM emp order by sal desc #降序