SQL基础知识总结

在cmd中打开mysql-------------mysql -uroot -p
在cmd中创建数据库------------create database dbl;
在数据库中添加字段----------------alter table user add name varchar(50)
mysql中的不等于
可以是<> ，也可以是!=
基本查询语句：
注：了解一个DQL语言的执行顺序
1、先执行from子句：
查询时，一定是基于表操作，因此，第一步先执行from子句
2、再执行where子句
进行表确定后，可以使用WHERE子句，进行条件筛选或者条件过滤
3、然后执行group by子句
剩下的数据可以进行分组查询，进行每一组的统计数据
4、其次执行having子句
如果分组后，还想使用条件筛选或过滤某些组的数据，就使用having子句
5、之后执行select子句
这个时候，就要根据业务需求，选择需要的字段进行显示
6、再执行order by子句
对选择后的字段进行排序
7、最后执行limit子句
进行分页查询，或者是查询前n条记录

mysql中输入delete from user where name=null;不会报错，但是却无法执行，即无法删除记录
正确写法：delete from user where name is null
聚合函数：
count():统计指定列不为null的记录行数，count( * )写法不会忽略null值所在的行记录
max():计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算
min():计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算
sum():计算指定列的数值和，如果指定类型不是数值类型，那么计算结果为0
avg():计算指定列的平均值，如果指定类型不是数值类型，那么计算结果为0
注意：
所有的聚合函数，都会忽略字段为null的那条记录，count(*)写法，不会忽略null值所在的行记录，通常用于统计总行数
–查询部门平均工资大于1000部门号，平均工资
错误：select deptno,avg(sal) from emp where avg(sal)>1000 group by deptno
正确：select deptno,avg(sal) from emp
group by deptno
having avg(sal)>1000
----查询每种职位的最高工资大于1500的职位，最高工资，平均工资，平均奖金
select job,max(sal),avg(sal),avg(ifnull(comm,0))
from emp
group by job
having max(sal)>1500

HAVING和WHERE的区别：
1、HAVING后面可以写聚合函数，WHERE后面是不可以写聚合函数的
2、HAVING是对分组后的数据做的过滤。如果没有满足WHERE条件过滤的数据，将不参加加分组。
3、HAVING是分组后的数据做过滤

表的操作：
如何添加字段？ alter table tableName add colName type
如何修改字段的类型？ alter tableName modify colName newType
如何修改字段的名称？ alter tableName change oldCoName newColName type
如何删除某个字段的名称？ alter tableName drop colName
删除表对象？ drop table tableName

实体完整性：为了保证表中每条记录唯一
向表中添加主键（primary key):
alter table stu add primary key(sid)
选择主键约束的字段要求：
1、对业务需求没有意义的字段，比如序号
2、应该选择不会经常被修改的字段
唯一性约束：unique，简称，UK
如果字段设置了唯一性约束，那么在表中此字段的值不能重复，但是可以为null
主键和唯一性约束：
一个表只能有一个主键，但一个表可以有多个唯一性约束
主键上的值不能为null，唯一性可以为null

create table tb1(
id int PRIMARY key auto_increment,
name varchar(20) UNIQUE,
age int);
insert into tb1(name,age)values(‘lisi’,20)
insert into tb1(name,age)VALUES(‘tom’,20)
insert into tb1(name,age)VALUES(‘tom’,25)

域完整性：对表中每列上的数据的约束
1、-非空约束：not null,简称NN
2、-数据类型
3、-默认值DEFAULT
4、-检查约束

-建表写法：
create table tablename(
tid int,
tname varchar(20) not null,
gender char(1) default ‘M’
);

检查约束：check,简称ck
mysq不支持，语法通过，但是没有效果，可以使用枚举代替(新版本的mysql支持check)
建表语句：
create table tableName(
id int,
name varchar(20),
gender char(1) check(gender in(‘f’,‘m’));

create table tb4(
id int PRIMARY key auto_increment,
name varchar(25),
age int,
gender enum(‘m’,‘f’));

引用完整性：
字段A的值，依赖于字段B的值，这两个字段可以在同一张表中，字段A所在的表称为从表（副表），
字段B所在的表称之为主表（父表)，字段A的值可以为null，字段B必须为主键.
外键约束：foreign key,简称FK
create table stu_score(
id int PRIMARY key auto_increment,
stu_id char(6),
sccore int,
FOREIGN key(stu_id) REFERENCES stu(sid)
–constraint fk_sid foreign key(stu_id) references stu(sid)–);

alter table stu_score add foreign key(stu_id) references stu(sid)
alter table stu_score add constraint flk_sid foreign key(stu_sid) references stu(sid) //给外键起个名字

多表查询
最简单的关联查询(笛卡尔积)
select * from emp,dept
select * from emp join dept
条件筛选：
select * from emp,dept where emp.deptno=dept.deptno
select * from emp,dept on emp.deptno=dept.deptno(报错）
select * from emp join dept where emp.deptno=dept.deptno
select * from emp join dept on emp.deptno=dept.deptno
select sal from emp on empno=7499（报错）
select sal from emp where empno=7499

join是标准连接：内连接和外连接
1、内连接：[inner] join
2、外连接：(outer join):(引出一个驱动表的概念：驱动表里的数据全部显示）
左外连接：left [outer] join,左表是驱动表
右外连接：right [outer] join,右表是驱动表
全外连接：full [outer] join,mysql不支持，两张表里的数据全部显示出来

SELECT * FROM EMP RIGHT JOIN DEPT on emp.deptno=dept.deptno
SELECT * FROM EMP RIGHT JOIN DEPT where emp.deptno=dept.deptno (报错）

关键字：distinct
select distinct deptno from emp //查询不同部门
关键字：union（去重）/union all(不去重）
两个查询语句使用上述的关键字连接即可
注意：两个查询语句的字段名，字段个数，必须对应上
SELECT empno,ename,job,sal,deptno from emp where deptno in(10,20)
UNION
SELECT empno,ename,job,sal,deptno from emp where deptno in(20,30)

SELECT empno,ename,job,sal,deptno from emp where deptno in(10,20)
UNION all
SELECT empno,ename,job,sal,deptno from emp where deptno in(20,30)
这里的去重表示如果在第一条语句中查出了与第二条语句当中相同部门，只保留一条。

子查询：
where子查询
select * from emp where sal>(select sal from emp where empno=7499)
查询工资大于10号部门的平均工资的非10号部门的员工信息
select * from emp where sal>(select avg(ifnull(sal,0)) from emp where deptno=10)and deptno<>10
查询平均工资大于10号部门的平均工资的部门号，和平均工资
SELECT deptno,avg(ifnull(sal,0)) from emp group by deptno
having avg(ifnull(sal,0))>(select avg(IFNULL(sal,0)) from emp WHERE
deptno=10)
查询每个员工的姓名，工资，及其部门的平均工资，工资之和
SELECT ename,sal,(select avg(ifnull(sal,0)) from emp b where a.deptno=b.deptno) avg_sal,
(SELECT sum(ifNull(sal,0)) from emp c where c.deptno=a.deptno) sum_sal from emp a

CASE-WHEN语句
可以做一些简单的分支逻辑，类似于if-else和switch-else
语法：
case [colName] when 条件1 then 值1
when 条件2 then 值2
…
else 值n
end
查询员工的信息，及其工资等级（>=5000 第一等级 >=3000 第二等级 >=1000第三等级 其他）
SELECT *,CASE
when sal>=5000 then ‘第一等级’
when sal>=3000 then ‘第二等级’
when sal>=1000 then ‘第三等级’
else ‘其他’
end levels
from emp
查询员工表的姓名、部门（显示效果：10 第一部门 20 第二部门 30 第三部门 其他）
SELECT ename,CASE deptno
when 10 then ‘第一部门’
when 20 then ‘第二部门’
when 30 then ‘第三部门’
else ‘其他’
end ‘部门’
from emp

日期函数
1、获取当前系统时间的函数
curdate()/curtime()/now()/sysdate()/current_timestamp();
2、获取星期几的函数
dayofweek(date):1=星期天 1-7
weekday(date) 0=星期一
dayname(date) 0~6
3、获取第几天的函数
dayofmonth(date)
dayofyear(date)
monthname(date)
4、截取时间分量函数
year(date)/month(date)/day(date)
hour(date)/minute(date)/second(date)
5、日期格式化函数
date_format(date,format):更具format字符串格式化date值
%Y:年 %月 %d日 %h 时 %i 分 %s 秒 %p 上下午 %w 星期
SELECT CURDATE()
SELECT CURTIME()
SELECT NOW()
select SYSDATE()
SELECT CURRENT_TIMESTAMP()
SELECT DAYOFWEEK(‘2020-12-12’)//星期日是1
SELECT weekday(NOW())
select DAYNAME(NOW())
SELECT DAYOFMONTH(‘2020-12-18’)
select DAYOFYEAR(‘2020-12-31’)
select MONTHNAME(NOW())
SELECT YEAR(NOW()),MONTH(NOW()),DAY(NOW()),HOUR(NOW()),MINUTE(NOW()),SECOND(NOW())
select DATE_FORMAT(NOW(),’%y/%m/%d/ %h:%i:%s %p %a’)
select DATE_ADD(NOW(),INTERVAL 3 day) 在当前日期上加3天
select ADDDATE(NOW(),INTERVAL 3 day)
select DATE_SUB(NOW(),INTERVAL 3 day)
SELECT SUBDATE(NOW(),INTERVAL 3 MONTH)

字符串函数
把十进制的数字8转换为二进制
SELECT CONV(8,10,2)
SELECT CONCAT(‘hello ‘,‘world’)
SELECT LPAD(‘aaabbbcc’,12,‘k’),RPAD(‘aaabbcc’,12,‘g’)
SELECT LEFT(‘aaaabbbbcccdddd’,6),RIGHT(‘aaaabbbbcccdd’,5)
SELECT SUBSTR(‘helloworld’,6 ,3)
SELECT LENGTH(‘你好’),octet_length(‘大家好’)
select CHAR_LENGTH(‘你好’),CHARACTER_LENGTH(‘大家好’)
select LTRIM(’ hello’),RTRIM('world ‘),trim(’ good ')
select LCASE(‘ABC’),LOWER(‘BcD’),UCASE(‘abc’),UPPER(‘abD’)
select replace(‘hello world’,‘world’,‘hello’)
select INSERT(‘hello world’,7,3,‘aaa’)

数值函数
select POWER(2,3),POW(2,3)
select POW(2,3)
select sqrt(4)
select PI()
1、不带参数的RAND(),每次产生不同的随机数
2、带一个整数参数X,RAND(X)每次产生相同的随机数
SELECT RAND(),RAND(9)
SELECT TRUNCATE(123.456,2)
select LEAST(12,3,45,6,7),GREATEST(99,123,34,6)
select mod(9,4)
select CEILING(12.6),FLOOR(12.6)
select ROUND(23.72,1),ROUND(23.72)