SQL:(Structured Query Language)结构化查询语句
( 针对于不同的数据库来说还是有微小的差异的 )
在mysql中,字符串类型和日期类型都要用单引号括起来。'tom' '2015-09-04',空值:null
且区分大小写,一切都以 ; 作为结束标志
SQL的分类:
1. DDL:(Data Definition Language) 数据定义语言, 用来定义数据库的 库,表,列 等结构的
关键字: create(创建) drop(删除) alter(修改) truncate(截断)
2.DML: (Data Manipution Language) 数据操控语言,用来操控数据库中的数据(记录)
关键字: insert into 表名 (列1,列2...) values(值1,值2...)
delete from 表名 where 条件;
update 表名 set 列名=修改值 where 条件;
select 列名... from 表名 where 条件;
3.DCL: (Data Control Language) 数据控制语言,用来定义数据库的访问权限和安全级别的
关键字: grant: 授权 revoke 回收授权
4.TCL: (Transcation Control Language)事务控制语言,用来对事务进行管理
关键字: start transcation 开始事务(也可以用 begin) commit 提交事务 rollback 回滚事务
总结: DDL-数据对象; DML-数据; DCL-权限; TCL-事务
DDL:
创建 ( create)
create database mydb1;
create database mydb2 character set gbk;
create database mydb3 character set gbk COLLATE gbk_chinese_ci;
查询: 查看当前数据库服务器中的所有数据库
show databases;
查看前面创建的mydb2数据库的定义信息
show create database mydb2;
修改: (alter) 查看服务器中的数据库,并把mydb2的字符集修改为utf8;
alter database mydb2 character set utf8;
删除:( drop ) 删除前面创建的mydb3数据库
drop database mydb3;
截流( truncate ):删除表中的数据
truncate table 表名;
********* truncate 和 delete 的区别:
1) delete 如果和事务sql结合使用,是有机会恢复,而 truncate 没法和事务结合,不能恢复,效率更高
2)delete语句在删除记录的时候可以有选择的删除某些数据(使用where子句),当然,如果不添加where子句,就是删除所有记录,而trancete语句则是清空表中所有数据,没有其他选择说某些东西不删,某些东西删了。
3)当表中的主键是自动增长(auto_increment)时,用delete删除完数据之后,再往表中插入数据,当数据的主键为空,其自动增长时,并不是从1开始,也就是说,delete虽然删除了数据,但是,他并没有将主键自增(auto_increment)重新设为1。而truncate则做了这件事,它不仅将数据全部清空,还将主键自增的值初始为1。
总结: delete只删数据,不清痕迹;trancate既删数据,又清痕迹。
DELETE 删除表中的数据,表结构还在;删除后的数据可以找回
TRUNCATE 删除是把表直接DROP掉,然后再创建一个同样的新表,删除的数据不能找回,执行速度比DELETE快
如图
第一次测试用delete删除所有数据,然后在主键为空插入数据
然后用truncate删除数据,在进行主键为空插入
DML:
1)insert: 插入
格式 : insert into 表名 (列1,列2...) values(列值1,列值2...);
mysql独有的: 一次插入多行数据
insert into 表(多个列...) values (多个值), (多个值), (多个值) ...;
标准语法:用insert插入另一张表的数据(列的个数和类型要一致)
insert into 表1(列1,列2...) select 列1,列2... from 表2;
例:insert into s2(sid,sname) select sid,sname from student;
注意:
列名与列值的类型、个数、顺序要一一对应
可以把列名当做java中的形参,把列值当做实参
值不要超出列定义的长度
如果插入空值,请使用null
插入的日期和字符一样,都使用引号括起来
例:
标准SQL :insert into student values(null,'张三','1990-10-10','男');
insert into student values(null,'李四','1981-10-10','男');
insert into student values(null,'王五','1981-11-10','女');
Mysql特有: insert into student values(null,'张三','1990-10-10','男'),
(null,'李四','1981-10-10','男'),
(null,'王五','1981-11-10','女');
2)update :修改表中的数据 注意:主键列主要用于查询条件,一般不会更新
格式: update 表名 set 列名=修改值 where 条件;
例:
将所有员工薪水修改为5000元。
UPDATE emp SET salary=5000;
将姓名为’zs’的员工薪水修改为3000元。
UPDATE emp SET salary=3000 WHERE name=’ zs’;
将姓名为’aaa’的员工薪水修改为4000元,job改为ccc。
UPDATE emp SET salary=4000,job='ccc' WHERE name='aaa';
3) delete : 删除表中的数据
格式: delete from 表; 全部删除
delete from 表 where 条件; 删除符合条件的记录
例:
-- 删除成功,因为40号部门没有员工引用
delete from dept where deptno=40;
-- 删除不能成功,因为员工表中有记录引用了10号部门
delete from dept where deptno=10;
主表中删除的记录,如果被从表引用,那么会删除失败,违反外键约束(外键约束默认行为)
( 主键所在表可以称为`主表`,而外界所在表称为`从表` )
解决办法: 级联删除,在定义外键时加上 on datele cascade
以员工和部门为例:foreign key(deptno) references dept(deptno) on delete cascade
例如: 删除10号部门,不仅会删除10号部门,而且会被从表(emp)中的10部门下所有员工也一块删除
4) select: 查询表中的数据,并将查询到的数据以表(虚拟表)的形式显示出来,查询语句不会对表中的数据进行修改
格式: select 列名1,列名2... from 表名 where 条件 ;
select * from 表名 ;查出表中的所有列的数据
4.1)条件查询: where 条件
1: =、!=、<>、<、<=、>、>=;
2:BETWEEN ... AND ... ; 1) 必须按 由小到大 顺序 2) between 包含等于条件
3:IN(set);求这一列是否在一个值列表内
4:IS NULL; IS NOT NULL
5:AND(与);OR(或);NOT(非);
4.2)模糊查询: like
通配符 % 表示匹配0~多个任意的字符
通配符 _ 表示匹配一个任意字符
例: 查询以 S 开头的名字 select name from student like 'S%' ;
4.3) null 值:数据库中的null ,比较特殊,不能使用=来比较, 必须用 is null
例如:查询佣金为null的员工
select * from emp where comm is null;
例如:查询佣金不为null的员工
select * from emp where comm is not null;
4.4)limit 限制返回结果数 注意: 起始行从0开始,即第一行开始!
1) limit n; n表示最多返回几条记录
2) limit m, n; m代表偏移量(下标) 注意偏移量从0开始
例:SELECT * FROM emp LIMIT 0, 5;
4.5)order by : 用来给结果排序
order by 列名 desc ; 该列按 降序 排列
order by 列名 asc; 该列按 升序 排列(默认)
例: 如果多列排序,如果第一列取值相同,再按照第二列进行排序,
先按工资排序,工资相同的,再按姓名排序:
select * from emp order by sal asc, ename asc; /*如果升序,两个asc都可以省略*/
4.6)group by 列名 having 条件:当需要分组查询时需要使用GROUP BY子句
group by 后的列取值相同的会被分为一组
注意: 1) select,order by 中的列必须和 group by 中的列一样
2) 其它没有包含在 group by 语句中的列,必须和组函数一起用
凡和组函数同时出现的列名,一定要写在group by 之后
3)语法顺序 select ... from ... where ... group by ... having ... order by ... limit;
执行从先到后 :where 进行一遍条件过滤 , 再执行 group by 分组, 再执行 having中的条件, 再执行select, 再执行order by, 执行limit
***********having与where的区别:
(1)having是在分组后对数据进行过滤
where是在分组前对数据进行过滤
(2)having后面可以使用组函数(统计函数)
where后面不可以使用组函数
(3) WHERE是对分组前记录的条件,如果某行记录没有满足WHERE子句的条件,那么这行记录不会参加分组
而HAVING是对分组后数据的约束
4.7)distinct: 去除重复值
例: select distinct job from emp; /*去除重复后的职位有哪些*/
select count(distinct job) from emp; /*有几种职位*/
4.8)组函数:
最大值 max(列)
最小值 min(列)
和 sum(列)
个数 count(列) 会排除null值
count(*) 针对这次查询,看看一共有多少行
平均值 avg(列)
4.9)常用的函数:
date_add(原始时间,时间间隔)【(datetime, interval 值 时间单位) 】
例: select date_add(now(), interval 1 day );
extract(时间部分 from 原始时间)
例:要获取1981年入职的员工
select * from emp where extract(year from hiredate)=1981;
cast() 类型转换
例: select cast('11' as signed); // 将字符串 转化为 数字
select cast('12.55555' as decimal(5,2)); // 将字符串转化为小数类型
concat() 拼接字符串
concat(值1, 值2, ... 值n)
例: select concat('a', 'b', 'c');
select concat('a', 18, 'c'); /*可以把其它类型当做字符串拼接*/
lower() 函数把字符变成小写
upper()函数把字符变成大写
求长度的函数
char_length() 按字符为单位统计长度
length() 按字节为单位统计长度
utf8mb4编码下,汉字一个字符占三个字节,英文一个字符占一个字节
标准sql:整个case...end 最后返回一个值
case
when 条件1 then 值1
when 条件2 then 值2
...
else 值n
end
例 : select sal,
case
when sal>2000 then '高工资'
when sal between 1000 and 2000 then '中等'
else '低工资'
end from emp;