目录
一、介绍数据库
数据库的好处:1.实现数据持久化2.使用完整的管理系统统一管理,易于查询
数据库的特点:
SQL的版本:社区版(免费)、企业版(收费)
二、常见语句
①查看数据库:show databases;
②打开指定的库:use 库名;
③创建表:creat table 表名(id int,name varchar(15));
④查看结构:desc 表名;
⑤查看数据:select * from 表名;
⑥插入数据:insert into 表名(id,name) values (1,'john');
⑦修改数据:update 表名 set name='lilei' where id=1;
⑧删除数据:delete from 表名 where id=1;
⑨查看版本:select version();
⑩单行注释:#注释文字;多行注释:/*注释文字*/
三、DQL语言
查询:
select 查询列表 from 表名 【where 筛选条件】;
8.去重:select distinct department_id from employees;
使用concat实现连接:select concat(‘a’,‘b’,‘c’)as 结果;
⑴ 模糊查询:like
通配符:%任意多个字符,包含0个字符;_任意单个字符
转义字符:$,\
关键字:ESCAPE
⑵模糊查询:between and
select * from employees where employees_id between 100 and 120;
⑶模糊查询:in
⑷模糊查询:is null
⑸模糊查询:安全等于<=>
排序查询:
select 查询列表 from 表 【where 筛选条件】order by 排序列表 【asc|desc】asc为升序,desc为降序,不写默认为升序
常见函数:
select 函数名(实参列表)【from 表名】;
Ⅰ、单行函数:
⑴字符函数:
- length:获取参数值的字节个数。例:select length("张三丰hahaha"); 15 一个汉字占三个字节
- concat:拼接字符串。例:select concat(last_name,'_',first_name) 姓名 from employees
- upper,lower:变为大写,变为小写。例:select upper('john'); select lower('joHn');
- substr、substring:例:1、select substr("小龙女",2) 输出; 龙女 注意:索引从1开始,截取从指定索引处后面所有字符. 2、select substr("小龙女和",1,3) 输出; 小龙女 截取从指定索引处指定字符长度的字符
- instr:返回字串第一次出现的索引,如果找不到返回0。 例:select instr ('杨不悔爱上了殷六侠','殷六侠') as out_put; 7
- trim:去前后空格。 例:1、select trim(' 张翠翠 ') as out_put; 张翠翠 2、select trim('a' from 'aaaaaaa张aaaaaaa翠翠aaaaaaaaaaaa') as out_put; 张aaaaaaa翠翠
- lpad:用指定的字符实现左填充指定长度。例:select lpad('白素素',10,'*') out_put; *******白素素
- rpad :用指定的字符实现右填充指定长度。例:select rpad('白素素',10,'o') out_put; 白素素ooooooo
- replace:替换。例:select replace('张无忌爱上了周芷若','周芷若','赵敏') as out_put; 张无忌爱上了赵敏
⑵数学函数:
- round:四舍五入。 例:1、select round(1.65); 2 2、select round(1.567,2); 1.57 小数点后保留两位
- ceil:向上取整,返回>=该参数的最小整数。例:1、select ceil(1.65); 2 2、select ceil(-1.25); -1
- floor:向下取整,返回<=该参数的最大整数。例:1、select floor(9.99); 9 2、select floor(-9.99); -10
- truncate:截断。例:select truncate(1.69,1); 1.6,小数点后一位截断
- mod:取余。例:select mod(10,3); 1,结果与10的符号相同 mod(a,b):a-a/b*b
⑶日期函数:
- now:返回当前系统日期+时间。例:select now();
- curdate:返回当前系统日期,不包含时间。例:select curdate();
- curtime:返回当前系统时间,不包含日期。例:select curtime();
- 可以获取指定的部分,年、月、日。例:select year(now());
⑷其他函数:
- select version();
- select database();
- select user();
⑸流程控制函数:
- if函数:select if(5>10,'大','小');
- case函数:case 要判断的字段或表达式 when 常量1 then 要显示的值1或语句1 when 常量2 then 要显示的值2或语句2 ... else 要显示的值n或语句n end
- case函数:case when 条件1 then 要显示的值1或语句1 when 条件2 then 要显示的值2或语句2 ... else 要显示的值n或语句n end
Ⅱ、分组函数:
⑴分类:sum 、avg、 max、 min 、count、datediff(差值)。
例:1、select sum(salary) from employees;
2、select sum(salary) 和,avg(salary) 平均 from employees;
3、select datediff('2022-9-26','2022-9-18'); 8 用前面-后面
特点:1、sum,avg一般用于处理数值型,max,min,count可以处理任何类型。
2、以上分组函数都忽略null值。
3、可以和distinct搭配实现去重的运算。select sum(distinct salary) from employees;
4、一般使用count(*)统计行数。
5、和分组函数一同查询的字段要求是group by后的字段。
(2)分组查询:
select 分组函数,列(要求出现在group by的后面)from 表名 【where 筛选条件】 group by 分组的列表 【order by 字句】
1.简单使用
2.多个字段
3.添加筛选条件
4.添加复杂筛选条件
5.特点:
6.添加排序
(3)连接查询:又称多表查询
1.分类:
2.sql92等值连接:
为表起别名:
筛选:
分组:
排序:
实现三表连接:
3. sql92非等值连接:
4.sql92自连接:
5.sql99:
语法:select 查询列表 from 表1 别名 【连接类型】join 表2 别名 on 连接条件 【where 筛选条件】【group by 分组】【having 筛选条件】【order by 排序列表 】
【连接类型】:内连接:inner;左外:left 【outer】;右外:right 【outer】;全外:full 【outer】;交叉连接:cross
(一)内连接
语法:select 查询列表 from 表1 别名 inner join 表2 别名 on 连接条件;
分类:等值,非等值,自连接
1.等值连接
特点:1.添加排序、分组、筛选。2.inner可以省略
2.非等值连接
3.自连接
(二)外连接
4.全外连接=内连接结果+表1中有但表2没有的+表2中有但表1没有的
(三)交叉连接
总结:
(4)子查询
含义:出现在其他语句中的select语句,称为子查询或内查询;外部的查询语句,称为主查询或外查询。
分类:按子查询出现的位置:select后面:仅仅支持标量子查询、from后面:支持表子查询、where或having后面:标量子查询(单行)、列子查询 (多行)、行子查询、exists后面(相关子查询):表子查询
按结果集的行列数不同:标号子查询(结果集只有一行一列)、列子查询(结果集只有一列含行)、行子查询(结果集有一行多列)、表子查询(结果集一般为多行多列)
一、where或having后面
1、标量子查询(单行子查询)
2、列子查询(多行子查询)
3、行子查询(多列多行)
特点:
(1)子查询放在小括号内
(2)子查询一般放在条件的右侧
(3)标量子查询,一般搭配着单行操作符使用><><=<>
列子查询,一般搭配着多行操作符使用 in,any/some,all
(4)子查询的执行优先于主查询执行,主查询的条件用到了子查询的结果。
例:标量子查询 :
列子查询:
二、select后面
(5)分页查询
(6)联合查询
语法:查询语句1 union 查询语句2 union ......
应用场景:查询的结果来自多个表,表没有直接的联系关系,但查询的信息一致
特点:1.要求多条查谢语句的查询列数是一致的 2.要求多条查询语句的查询的每一列的类型和顺序最好一致 3.union关键字默认去重,如果使用union all可以包含重复项
四、DML语言
1.插入语句:
insert into 表名(列名,......)values(值1,......);或insert into 表名 set 列名=值,列名=值,...... 方式一支持插入多行,子查询,方式二不支持
2.修改单表数据:
update 表名 set 列名=新值,列名=新值,......where 筛选条件;
修改多表数据:sql92语法----update 表1 别名,表2 别名 set 列名=新值,列名=新值,......where 连接条件 and 筛选条件;
修改多表数据:sql99语法------update 表1 别名 inner|lefe|right join 表2 别名 on 连接条件 set 列名=新值,......where 筛选条件;
3.删除语句:
(1)单表delete from 表名 where 筛选条件;
sql92语法----delete 表1的别名,表2的别名 from 表1 别名,表2 别名 where 连接条件 and 筛选条件;
sql99语法------delete 表1的别名,表2的别名 from 表1 别名 inner|lefe|right join 表2 别名 on 连接条件 where 筛选条件;
(2)truncate table 表名;
区别:1.delete 可以加where 条件,truncate不能加。2.truncate删除,效率高一丢丢。 3.假如要删除的表中有自增长列,如果用delete删除后,再插入数据,自增长列的值从断点开始,而truncate删除后,再插入数据,自增长列的值从1开始。4.truncate删除没有返回值,delete删除有返回值。
5.truncate删除不能回滚,delete删除可以回滚
五、DDL语言
库管理:
- 创建库:create database 库名;
- 修改库:rename database 库名 to 新库名;
- 更改库的字符集:alter database 库名 character set gbk;
- 库的删除:drop database 库名;
表管理:
- 创建表:creat table 表名(列名 列的类型【(长度)约束】);
- 修改表:alter table 表名 add\drop\modify\change column 列名 【列的类型 约束】;
①修改列名:alter table book change column publishdate pubdate datetime;
②修改列的类型或约束: alter table book modify column pubdate timestamp;
③添加新列 :alter table author add columnannual double;
④删除列:alter table author drop column annual;
⑤修改表名 - 删除表:drop table 表名;
- 复制表:1.仅仅复制表的结构 CREATE TABLE COPY LIKE author 2,复制表的结构+数据 CREATE TABLE COPY2 SELECT FROM author; 3.只复制部分数据 CREATE TABLE COPY3 SELECT id,au_name FROM author WHERE nation='中国';
约束:
创建表时添加列级约束:在字段名和类型后面追加约束类型,支持默认、非空、主键、唯一
创建表时添加表级约束:在各个字段的最下面 【constraint 约束名】 约束类型(字段名)
主键和唯一的对比:
修改表时添加列级约束:alter table 表名 modify column 字段名 字段类型 新约束;
修改表时添加表级约束:alter table 表名 add【constraint 约束名】 约束类型(字段名) 【外键的引用;
修改表时删除约束:
标识列:可以不用手动的插入值,系统提供默认的序列值
特点:
1、标识列必须和主键搭配吗?不一定,但要求是一个key
2、 一个表可以有几个标识列?至多一个!
3、标识列的类型只能是数值型
4、标识列可以通过 SET auto increment_increment=3;设置步长可以通过手动插入值,设置起始值
六、TCL语言
1.事务:一个或一组SQL语句组成一个执行单元,这个执行单元要不全部执行,要不全部不执行。
2.事务的ACID属性:
- 原子性:事务不可再分割;
- 一致性:事务执行会使数据从一个一致状态切换到另一个一致状态;
- 隔离性:事务的执行不受其他事务干扰;
- 持久性:事务一旦提交,则会永久的改变数据库的数据。
3.事务的创建:
隐式事务:事务没有明显的开启和结束的标记。比如insert、update、delete语句
显式事务:事务具有明显的开启和结束的标记。前提:必须先设置自动提交功能为禁用
4.数据库的隔离级别
七、视图
1.创建视图:create view 视图名 as 查询语句;
2.修改视图:方式一:create or replace view 视图名 as 查询语句;方式二:alter view 视图名 as 查询语句;
3.删除视图:drop view 视图名 ,视图名.....;
4.查看视图:desc 视图名;
八、存储过程
(1)创建
create procedure 存储过程名(参数模式 参数名 参数类型)
begin
存储过程体
end
注意:1.参数模式:in、out、inout,其中in可以省略.2.存储过程体的每一条sql语句都需要用分号结尾
(2)调用
call 存储过程名(实参列表)
举例:调用in模式的参数:call sp1('值’);
调用out模式的参数:set @name; call sp1(@nane);
调用inout模式的参数:set @name=值;call sp1(@name); select @name;
(3)查看:show create procedure 存储过程名
(4)删除:drop procedure 存储过程名
九、函数
(1)创建
create function 函数名(参数名 参数类型) returns 返回类型
begin
函数体
end
注意:函数体中肯定需要有return语句。
(2)调用
select 函数名(实参列表)
(3)查看:show create function 函数名;
(4)删除:drop function 函数名;
十、控制结构