数据库基础信息
2019-03-05 本文已影响0人
蜗牛ICU
查看当前服务器的版本 :
select version();
清空表的语句：
truncate table 表名;
删除表:
drop table 表名称
基础查询:
① 查询
SELECT 字段 FROM 表名;
② 去重
SELECT DISTINCT 字段 FROM 表名
③ + 号的作用
案例 : 数据库中名和姓分开存的 first_name last_name 。
需求 : 将两个字段链接起来成为一个。
注意:如果两个连接的字段是数字类型 就会当做加法运算。
如果一方为字符型 数据库将会将字符型转化成数字进行相加，
但是这个时候会出现异常 数据库会默认将字符型的字段的值当做 0 来处理
比如 : lidong + 90 就会等于90
如果一方为null 结果就为null.
④ concat
使用concat 函数 就会将两个字符串拼接到一起,
案例: concat(first_name,last_name )
⑤ IFNULL
如果一个字符串为null 但是 我们有想给他一个值的话就用这个函数。
案例 : IFNULL( 字段名 , 0 );
条件查询:
① 安全等于
安全等于 <=> 是否等于 等于返回true 不等于返回false。
② 排序查询
语法 :
SELECT * FROM 表名 WHERE 筛选条件 ORDER BY 排序列表 DESC 者 ASC
描诉:
ASC 升序 从低到高
DESC 降序 从高到低
需求 : 先按员工编号排序 再按工资升序排序
SELECT * FROM 表名 ORDER BY 字段名 ASC , 字段名 DESC
注意 :
一般排序的字段都是在SQL语句的最后.
常见函数:
① 调用函数 :
语法:
SELECT 函数名 (实参数表) FROM 表明
② 单行函数:
1、字符函数 LENGTH
2、拼接字符串 CONCAT
3、upper lower 变成大小写
4、substr 后者 substring 注意 :数据库中 索引从1开始
5、instr 返回字段在字符串中的索引 如果没有返回null
6、trim 去掉空格
需求：
需要将字符串中的aaaaaa去掉，aaaaaaaaaaaaaaaaaa张dddd翠山aaaaaaaaaaaaa
语法 :
SELECT TRIM ( 'a' FROM 'aaaaaaaaaaaaaaaaaa张aaaaaaaaaaa翠山aaaaaaaaaaaaa') AS 字段名 FROM 表名；
最后 会将张aaaaaaaaaaaaaa翠山剩下 两边的a 去除。
7、lpad 用指定的字符填充 左填充的长度 RPAD 右填充
语法 :
SELECT LPAD ( '李栋' ,10 ,'*') FROM 表名；
结果 :
********李栋
8 、replace 替换
语法 :
SELECT REPLACE ('张三丰张三丰张三丰张三丰张三丰张三丰爱上睡觉' ,'张三丰' ,'李栋') FROM 表名;
9、 数学函数:
四舍五入: round
流程控制函数：
① IF 函数:
SELECT IF(10 >5 ,'大'，'小') FROM 表名 ;
返回 ：大
② case函数：
case 要判断的字段或者表达式
when 常量1 then 显示的值1
when 常量2 then 显示的值2
else 默认值3
end
注意：
需求1：
是这样的才可以这样写
如果某个值等于多少 然后显示的值用这种方式。
需求2：
如果工资大于 1000 显示a级别
如果工资大于 2000 显示b级别
如果工资大于 3000 显示c级别
否则显示D级别
sql :
select momery ,
case
when momery>1000 then 'a'
when momery>2000 then 'b'
when momery>3000 then 'c'
else 'd'
end as '工资级别'
form 表名；
③ 日期函数：
返回当前日期： 当前日期 + 时间
select now();
返回当前系统日期，不包含时间：
select curdate();
返回当前的时间 不包括日期：
select curtime();
获取指定的部分字段：
select YEAR(NOW()) as 年;
select month(now()) 月;
将日期格式的字符转换成指定的格式日期类型：
select str_to_date( '1998-3-2','%y-%c-%d') as data form 表名;
将日期类型的值变成字符串类型：
select date_format(字段 ，'%y年-%m月-%d日') from 表名;
分组函数：
分组函数主要用来统计。
sum:
select sun(字段) from 表名;
avg:
select avg(字段) from 表名;
min:
select min(字段) from 表名;
max:
select max(字段) from 表名;
count:
select count(字段) from 表名;
select count(*) from 表名;
select count(1) from 表名;
在mysql 5.5 之前 默认是MYISAM存储引擎 count(*) 效率最高。
innerdb 存储引擎的下 count(*) 和count(1) 差不多 比count(字段)的效率高,
因为count(字段) 需要判断 字段是否有null值。
注意： 分组函数都会把null值 剔除。不会参与计算。
去重 disinct:
select sum(disinct 字段 ) from 表名;
select count(disninct 字段) from 表名;
分组查询：
语法：
select 分组函数，列（ 要求出现在 group by 后面） from 表名
where [ 筛选条件] group by
[order by 子句];
需求：
查询那个部门下的员工>2
分析 1 ：查询每个部门的员工个数
select count(*) ,departemt_id from 表名
group by departemt_id;
分析2 ：
根据查询 1 的结果 进行筛选 ，查询那个部门的员工个数>2
select count(*) ,departemt_id from 表名
group by departemt_id
having count(*)>2;
总结：
分组查询中的筛选条件主要分为两类：
数据源 位置 关键字
分组前筛选： 原始表 group by 子句的前面 where
分组后筛选： 分组后的结果集 group by 子句的后面 having
注意： 分组函数一定会放在having中。
## 连接查询：（多表查询）：
1 等值连接：
select 字段1 ，字段 2 from 表名1, 表名2 where 表名1.id=表名2.id;
注意哦， 如果为表起了别名 就不要用表名显示字段。
2 非等值连接
案例：
select salary ,grade_level from 表名 as e job_grades as f
where salary between e.lowest_sal and e.higest_sal;
3 自连接
在同一张表中：
需求：
查询员工名和上级领导的名称
select e.employee_id ,e_last_name,m.employee_id,m.last_name
from employees e ,employees m
where e.manager_id=m.employee_id;x
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 连接条件 ;
inner join 不区分表的前后顺序。
自连接：
select e_last_name ,m.last_name from
employees e
inner join
employees m
on e.manager_id = m.employee_id;
上面学的都是 两张表中都有数据时才能用上面的查询方式（交集）， 如果查询的是两张表（差集）的数据上面的方式就不可以了。
外连接：
外连接分为主表和从表区分 ，
如果查询的话 主表的数据将全部显示出来，如果主表和从表有匹配的列 从表的数据也将显示出来，如果从表中没有和主表中匹配的列 ，从表将显示null.
外连接的查询结果数据 = 内连接查询的数据 + 主表有 但是从表没有的数据。
左外连接： left 左边的是主表
右外连接： right 右边的是主表
子查询：
在 where 或 having 之后
（标量子查询 单行子查询）：
需求：
谁的工资比李东高？
1. 先将李东的工资查询出来
select * from 表名 where username ='李东' ;
2. 查询出员工的信息大于1的结果
select * from 表名 where money >
(select * from 表名 where username ='李东' );
需求2 ：
返回job_id 和141 号员工相同 ，money 比 143号员工多的员工
1 查询141号员工的job_id
select job_id form 表名 where employee_id='141'
2 查询出143号员工的money
select money from 表名 where employee_id ='143'
3 查询出员工的工资 要求：job_id=1
并且 money >2
selet * from 表名 where job_id=
（select job_id form 表名 where employee_id='141'）
and monery> (select money from 表名 where employee_id ='143')
分页查询：
语法：
select 查询列表
from 表名
【join 表2
on 连接条件
where 晒选条件
group by 分组字段
having 分组后的筛选数据
order by 排序字段 】
limit offset ，size;
联合查询： union
需求：
查询部门编号 > 90 或者邮箱编号带有a 的员工信息
== 注意： 查询的数据来自两张表中 并且这两张表没有关系。==
select * from 表名 where email like '%a%'
union
select * from 表名 where dept_num > 90;
** 特点：
语句查询的列数一致**
** 如果不想去重的话 就用 union all**
DML语言：
多表修改：
语法：
update 表1 别名
inner | left | rigth | join 表2
on 连接条件
set 列=值
where 筛选条件；
也可以多表删除。
对表的修改：
1 修改列名
alert tabel 表名 change column 旧列名 新列名 加上字段的类型;
2 修改列的类型
alert table 表名 modify column 字段名 修改后的字段类型
3 添加新的列:
方式一、
alert table 表名 add column 字段名 加上字段类型；
方式二、
ALTER TABLE 表明 ADD COLUMN 列名 varchar(50) DEFAULT null comment '注释' ;
4 删除列
alert table 表名 drop column 字段名;
5 修改表名
alert table 表名 rename to 新的表名
表的复制：
1 仅仅复制表结构：
cretae tabel 新的表名 like 需要复制的表名；
2 复制表名和数据
cretae tabel 表名 select * from 旧 的表名；
3 只复制部分数据
cretae tabel 表名 select * from 旧 的表名 whre 筛选条件;
约束:
not null 非空约束
default 默认约束
primary key 主键约束
unique 用于保证唯一性 但是允许为空
foreign key 外键约束
事务
通过 show engines; 查看mysql 存储引擎。 innerdb 支持事务 其他的不支持。
数据库默认 没开启事务。
命令： 查看事务是否开启
show variables 'autocommit';
将事务开启：
set autocommit=0;
start transaction;
insert ...
commit;
rollback;
设置保存点
savepoint a；
rollback a;
视图
1 创建视图：
crete view 视图名 as
2 使用视图：
select * from 视图名；
3 视图的修改
方式1 :
说明 : 如果视图存在就替换 如果不存在就创建.
create or replace view 视图名 as 查询语句;
方式2 :
alert view 视图名 as 查询语句;
4 删除视图
语法:
drop view 视图名1 ,视图名2 ......
5 查看视图的结构
desc 视图名;
6 视图的更新
视图的插入:
语法:
insert 语句;
视图的修改:
update 语句;
视图的删除:
delete 语句;
视图添加权限:只能查询 不能对视图新增 修改 删除.
变量
系统变量:
使用语法:
1 查看所有的系统变量
全部变量:
show globle variables;
会话级变量:
show session variables;
2 查看符合条件的系统变量
show variables like '%char%';
3 查看指定的系统变量:
select @@系统变量名;
4 为系统变量赋值
set 系统变量名=值
存储过程
1 创建语法:
create procedure 存储过程名 (参数列表)
begin
sql语句
end
参数列表:
参数模式 参数名 参数类型
事例:
in username varchar(20)
参数模式:
in : 该参数的 在调用时需要传值.
out : 该参数可以作为返回值.
inout : 该参数可以传入值又可以返回值.
begin:
2 调用方式
call 存储过程(参数列表);
案例
1 空参列表
需求:插入表中数据.
创建存储过程:
delimiter $
create procedure 存储过程的名字()
begin
insert into 表名 ('username','password') value('3244','423');
insert into 表名 ('username','password') value('3244','423');
insert into 表名 ('username','password') value('3244','423');
end $
调用存储过程:
call 存储过程名() $;
2 带 in 模式的存储过程
根据女神名查询男神的信息:
delimiter $
create procedure 存储过程的名字(in gilrsusername varchar(20))
begin
select * from boys where username = gilrsusername
end $
調用存储过程:
call('asdasdf') $
验证用户是否登录成功:
delimiter $
create procedure 存储过程的名字(in username varchar(20),in password varchar(20))
begin
//声明变量作为返回值
declare result varchar(20) default '';
//into result 将查询出的值赋值给返回值
select count(*) into result from boys as b where b.username = username
and
b.password=password;
//打印
select result;
end $
調用存储过程:
call('asdasdf') $
3 创建一个带out的模式的参数
根据女神名 查询男生名
deimiter $
create procedure 存储过程名(in username varchar(23),out boyName varchar(23))
begin
select boyName into boyName from boys b where b.username=username;
end $
调用:
call('范冰',@bname) $;
select $bname;
4 带inout的模式
deimiter $
create procedure 存储过程名(inout username varchar(23),inout boyName varchar(23))
begin
username+'fdsadf';
boyName='wqerqwer';
end $
调用:
call(@username,@bname) $;
select @username, $bname;
5 删除存储过程
drop procedure 存储过程名称;
函数
语法:
create function 函数名( 参数列表) returns 加返回类型
begin
函数体
end
调用函数:
select 函数名(参数列表);
1 函数的创建
无参函数:
create function 函数名称 ( 参数列表 )
begin
end $
调用函数:
select 函数名称 ( 参数列表 )$
案例:
返回员工个数:
create function 函数名称 () returns int //返回值类型定义
begin
declare c int default 0 ; //定义返回值 设置默认值为 0
select count(*) into c from 表名 return c;
end $
有参有返回值:
需求 : 根据员工姓名 返回员工工资
create function 函数名称 ( username varchar(20) ) returns int
//定义返回值
declare c int default 0;
begin
select money into c from 表名 where username = username ;
return c;
end $
2 查看函数:
show create function 函数名称;
3 删除函数
drop function 函数名称;
流程控制解构
if 函数:
语法:
if( 表达式1 ,表达式2 ,表达式3 )
如果表达式1 成立 返回表达式2 的值 ,如果表达式1 不成立就返回表达式3 的值.
case 函数:
语法:
if结构:
语法:
if 条件1 then 语句1 ;
elseif 条件2 then 语句2 ;
.....
[else 语句N]
END IF;
只能在begin end 中;
循环结构:
while :先判断 后执行
loop : 没有循环条件的死循环
repeat:先执行 后判断
循环控制 :
iterate 结束本次循环 继续下次循环
leave 类似break 跳出本次循环.
while语法:
[标签1 :] while 循环条件 do
循环体;
end while [标签];
loop语法:
[标签 :] loop
循环体
end loop [标签];
repeat语法:
[标签: ] repeat
循环体
until 循环结束的条件
end repeat [标签];
创建外键
alter table t_core_organ （外键表） add constraint org_user（外键名称） FOREIGN KEY(org_user_id （外键表中的外键字段） ) REFERENCES t_core_user(id) 主表名（主表中的id）;
实践操作
现象: 在插入数据的过程中,数据造成乱码.
解决方案:
-
查询 mysql 数据库服务器编码格式:
命令:
show variables like 'character%'; -
查询完成之后 如果有不是 utf-8 编码格式的可以通过命令修改编码格式.
-
修改编码格式:
命令:
set character_set_database=utf8; -
上面的方式是仅仅限于当前会话设置编码格式:
windows 免安装版 mysql 修改编码格式:
免安装版是没有 my.ini 文件的, 只有一个 my-default.ini 文件 , 将 my-default.ini 文件复制一份 重命名成 my.ini 文件 ,在文件中设置成以下方式:
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
[mysql]
default_character_set=utf8
[mysqld]
character_set_server=utf8
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
