mysql 进阶方向_MYSQL进阶

-- 视图

CREATE VIEW V_Employee

AS

SELECT A.EmpID,A.EmpName,A.Gender,B.DeptName

FROM employee A LEFT JOIN dept B ON A.DeptId=B.DeptId;

SELECT * FROM V_Employee

SELECT * FROM V_Employee WHERE DeptName='开发部';

-- 更改view

ALTER VIEW V_Employee

AS

SELECT A.EmpID,A.EmpName,A.Gender,B.DeptName

FROM employee A LEFT JOIN dept B ON A.DeptId=B.DeptId LIMIT 3;

SELECT * FROM V_Employee

-- 字符串类 函数

SELECT CONCAT('A','B','C')

SELECT * FROM employee

SELECT EmpName,INSERT(EmpName,'u') FROM employee;

SELECT deptName,LENGTH(deptName) FROM dept;

SELECT * FROM employee;

SELECT EmpName,REPLACE(EmpName,'i','c') FROM employee;

SELECT SUBSTRING(EmpName,1,3) FROM employee;

SELECT EmpName,concat(SUBSTRING(EmpName,1,3),'***') from employee

-- 数学类

select abs(-1)

select pow(2,4)

select CEILING(2.8)

select CEILING(1.1)

select floor(1.1)

select floor(1.9)

select format(2.8148,2)

select format(2.8158,2)

select bin(10)

select hex(10)

select hex(12)

-- 日期时间类

select now()

select year(NOW());

select month(NOW());

select day(NOW());

select date(NOW());

select time(NOW());

select dayofweek(NOW());

select dayofyear(NOW());

-- 10.1还有几天？

-- DATEDIFF (date1 ,date2 )

select DATEDIFF('2019/10/1',date(NOW()))-1

-- 存储过程 (Stored Procedure)

-- (1) 根据一个参数：工号empId=101，显示 这个人的姓名  (输入参数 “101”,输出参数“empName”)

select empName from employee where empId='1001'

CREATE PROCEDURE Proc_GetEmpNameById(in _empId char(10),out _empName varchar(20) )

BEGIN

select empName into _empName from employee where empId=_empId;

END;

set @empName='';

call Proc_GetEmpNameById('101',@empName);

select @empName;

-- (2)  根据一个参数：工号empId=101，显示 这个人的所有信息(输入参数，得至一个多行多列的结果集)

select * from employee where empId='101'

select * from employee where gender='女'

CREATE PROCEDURE Proc_GetEmpByGender(in _gender char(10) )

BEGIN

select  *  from employee where gender=_gender;

END;

***********************************(1)***********************************

一句话说明: 获取字符串长度的mysql内置函数

length是mysql的一个用来来获取字符串长度的内置函数方法,

同样的获取字符串长度的还有char_length. length: 是计算字段的长度, utf8编码下,一个汉字是算三个字符,一个数字或字母算一个字符。其他编码下,一个汉字算两个字符, 一个数字或字母算一个字符。

mysql:

utf-8 :length()一个汉字3个长度

gbk : length()一个汉字2个长度

select * from employee

show session variables like '%character%'

insert into employee (empid,gender,deptId) values(108,'男男',1)

insert into employee (empid,gender,deptId) values(110,'AA',1)

insert into employee (empid,gender,deptId) values(110,'A',1)

insert into employee (empid,gender,deptId) values(113,'A ',1)

> enum,SET

> char(2) 2个字符

select gender,length(gender)  from employee

select length('的') as l, char_length('的') as cl;

select gender,length(gender) as l, char_length(gender) from employee

select gender,length(gender) as l, BIT_LENGTH(gender) from employee