字节与字符的区别 一个字节等于8比特(b) 字符是文字或符号的统称 在不同的编码环境下一个字符所占的字节可能不一样 ascll 一个字符等于一个字节
utf-8 一个英文字符等于一个字节,一个中文字符(含繁体)等于三个字节。
unicode 一个英文字符等于两个字节,一个中文字符(含繁体)等于两个字节。
英文标点占一个字节,中文标点占两个字节。
utf-16 一个英文字母字符或一个汉字字符存储都需要2个字节
oracl存在的varchar2
1.varchar2把所有字符都占两字节处理(一般情况下),varchar只对汉字和全角等字符占两字节,数字,英文字符等都是一个字节;
2.VARCHAR2把空串等同于null处理,而varchar仍按照空串处理;
3.VARCHAR2字符要用几个字节存储,要看数据库使用的字符集,
大部分情况下建议使用varchar2类型,可以保证更好的兼容性。
explain
explain select * from city where city ='Fuzhou' \G
在 SELECT 语句之前增加 explain 关键字,MySQL 会在查询上设置一个标记,从而在执行查询时,会返回执行计划的信息,而不是执行这条 SQL 。
约束:在表的字段上面加上约束,用来保证数据的有效性和完整性
非空约束not null:字段不能为空
唯一性约束unique:该字段的内容不能够重复,可以为空 删除唯一性约束ALTER TABLE t_user DROP INDEX user_id;
默认约束 default
检查约束 check
主键约束primary key:不能为空,也不能够重复,类似非空约束和唯一性约束的结合。
外键约束foreign key:该字段的取值范围是固定的,一类与另一个字段的取值。
外键约束的创建
CONSTRAINT fk_emp_dept_id FOREIGN KEY (dept_id) REFERENCES dept (id) dept是主表
alter table emp add constraint fk_emp_dept_id FOREIGN KEY (dept_id) REFERENCES dept(id);
删除外键
alter table emp drop foreign key fk_emp_dept_id;
约束还可分为列级约束(约束加在字段后面)与表级约束(约束另起一行可以联合多个字段,只有多个字段都相同才可认为重复)
添加约束的方式
在创建表时在其类型后面添加
或者
alter table emp add constraint fk_emp_dept_id FOREIGN KEY (dept_id) REFERENCES dept(id);
在任何数据库当中主键上都会自动添加索引对象
在MySQL当中,一个字段上如果有unique约束的话,也会自动创建索引
desc emp 查看表的结构,但是不容易看
show create TABLE emp; 更加清晰
修改表类型
alter table emp modify ename varchar(20);
增加表字段
alter table emp add column age int(3);
删除表字段
alter table emp drop column age;
字段改名
alter table emp change age age1 int(4) ;
change和modify都可以修改表的定义,不同的是change后面需要写两次列名,不方便。但是change的优点是可以修改列名称,modify则不能。
add,change,modify 都有一个可选项first after column
add默认增加到最后,change,modify不变
样例alter table emp add birth date after ename;
更改表名
ALTER TABLE tablename RENAME [TO]new_tablename
DDL一般都是对表的结构进行的操作,例如create,drop,alter
DML 操作是指对数据库中表记录的操作,主要包括表记录的插入( insert )、更新(update)、删除(delete)和查询(select)
更新表中的数据通过update
update emp set xxx=xxx where ...
删除表中的记录可以通过delete来进行
delete from emp where xxx=xxx 如果没有加where将输出表中的所有数据
truncate table xxx 删除表的速度最快 本质删除表然后新建一个表,其结构不变 drop table xxx删除表信息包括结构
select * from emp order by sal DESC和ASC是排序顺序关键字,DESC表示按照字段进行降序排列,ASC则表示升序排列,如果不写此关键字默认是升序排列。
在检索的时候,CHAR列删除了尾部的空格,而VARCHAR则保留这些空格。 保存较大的数据文本时使用text或blob。二者之间的主要差别是BLOB能用来保存二进制数据,比如照片;而TEXT只能保存字符数据
有关字符串的函数
concat,left,right,lpad(str,n,pad),rpad(用字符串pad填充str直到长度为n),ltrim,rtrim 去除字符串左边和右边的空格 trim repeat('mysql',3) 其结果为 mysqlmysqlmysql replace strcmp(s1,s2) 比较其ascll值 ,s1小为-1 相等为0 s1大为1 substring(str,x,y) 返回从字符串str中的第x位置起y个字符长度的字串。
数值函数 abs 返回绝对值 ceil 返回大于x的最小整数 floor 返回小于x的最大整数 mod(x,y) 等价与x%y模数和被模数任何一个为NULL结果都为NULL rand() 返回0-1的随机数 ceil(100*rand()) 0-100 round(x,y) 返回参数x的四舍五入的有y位小数的值(以0补位)。 truncate(x,y) 返回参数x的四舍五入的有y位小数的值。
日期时间类型
date 表示年月日 datetime 表示年月日时分秒 time 表示时分秒
日期时间函数 now()=curdate()+curtime() unix_timestamp(now()) 返回时间戳 FROM_UNIXTIME(unixtime) 返回时间戳对应的日期值 WEEK(DATE)和 YEAR(DATE)函数:前者返回所给的日期是一年中的第几周,后者返回所给的日期是哪一年。 datediff()计算两个日期之间相差的天数
浮点数与定点数(float,decimal) 存入的浮点数会根据要求进行四舍五入运算,另外浮点数可能存在精度缺失的问题,而定点数则不存在
索引
索引优缺点:提高查询效率,保证数据的唯一性(唯一索引) 减少分组排序的时间,被索引的列会自动进行分组排序
占用磁盘空间 降低更新表的效率
创建索引 create index cityname on city (city(10));要为city表创建10个字节的前缀索引 alter table city add index cityname(city);
删除索引 drop index cityname on city;
对于InnoDB存储引擎的表,记录默认会按照一定的顺序保存,如果有明确定义的主键,则按照主键顺序保存。如果没有主键,但是有唯一索引,那么就是按照唯一索引的顺序保存。如果既没有主键又没有唯一索引,那么表中会自动生成一个内部列,按照这个列的顺序保存。
视图
注意事项 在from关键字后面不能包含子查询
创建视图 CREATE OR REPLACE VIEW staff_list_view AS select * from emp;
删除视图 drop view staff_list
查看视图的方式为show tables
show table status like ‘staff_list’ 查看视图信息
show create view staff_list 查看视图的定义
视图定义中引用的表或视图必须存在。但是,创建完视图后,可以删除定义引用的表或视图。可使用 CHECK TABLE 语句检查视图定义是否存在这类问题。
视图定义中允许使用 ORDER BY 语句,但是若从特定视图进行选择,而该视图使用了自己的 ORDER BY 语句,则视图定义中的 ORDER BY 将被忽略。
存储过程与函数
创建存储过程或者函数需要 CREATE ROUTINE权限,修改或者删除存储过程或者函数需要ALTER ROUTINE权限,执行存储过程或者函数需要EXECUTE权限。
MySQL开启了bin-log, 我们就必须指定我们的函数是否是
1、DETERMINISTIC 不确定的
2、NO SQL 没有SQl语句,当然也不会修改数据
3、READS SQL DATA 只是读取数据,当然也不会修改数据
4、MODIFIES SQL DATA 要修改数据
5、CONTAINS SQL 包含了SQL语句
-- 创建存储过程
delimiter $$
create procedure proc01()
begin
select empno,ename from emp;
end $$
delimiter ;
-- 调用存储过程
call proc01;
在begin与end之间定义变量的方式 declare nickname varchar(20) 该定义的变量在外面不能使用
用户变量在当前会话有效例如 @name 不需要提前声明 可以在外面使用
查看全局变量 show global variables
查看某全局变量 select @@global.auto_increment_increment;
修改全局变量 set global sort_buffer =xxx set @@global.sort_buffer=xxx
对于传入参数的格式:
delimiter $$
create procedure proc06(in param_empno int)
begin
select*from emp where empno = param_empno
end $$
delimiter ;
call proc06(1001);
在存储过程中的if语句
if score<60 then select '不及格';
elseif score>=60 and score<80 then select '及格';
elseif score>=80 and score<90 then select '良好';
elseif score>=90 and score<=100 then select '优秀';
else select '成绩错误'
end if;
case有两种格式:
case pay_type
when 1 then select '微信支付';
when 2 then select '支付宝支付';
when 3 then select '银行卡支付';
else select '其他方式支付';
end case;
case
when score<60 then select '不及格';
when score>=60 and score<80 then select '及格';
when score>=80 and score<90 then select '良好';
when score>=90 and score<=100 then select '优秀';
else select '成绩错误'
end case;
sql语言中的循环分为 while repeat loop 对于循环的控制有leave 类似于break iteater类似于continue
while循环:
【标签】:while 条件 do ... end while 【标签】
label:while xxx do xxx end while label;
while的do后面的内容可以加入if判断语句来控制循环的进行与结束 if xx=xx then leave label;
repeat循环:
【标签】:repeat 内容 until 条件 end repeat 【标签】;
loop循环:(如果不添加条件则会是死循环)
【标签】:loop 内容 if 条件 then leave 【标签】;end if; end loop;
游标 游标包含声明,打开,取值,关闭
declare my_cursor cursor for select a,b,c from xx where xx=xx;
open my_cursor;
fetch my_cursor into xx,xx,xx;(这一步一般放入loop循环中,my_cursor中没有数据loop循环自动结束)
close my_cursor
异常(handler)
declare handler_action handler for condition_value(为什么异常处理) statement(当没有异常发生怎么办)
handler_action:{ continue exit undo(不支持)
}
condition_value:{
mysql_error_code 错误码
condition 条件名
SQLWARNING SQL警告
NOT FOUND 数据没有发现
SQLEXCEPTION SQL异常
}
declare continue handler for 1329 set flag=0 ;
**注意其先后顺序 变量声明 游标声明 handler声明
删除存储过程:drop procedure 【if exists】 xxx
查看存储过程的状态 show procedure status like ‘xxx’
查看存储过程的定义 show create procedure xxx
触发器
触发器的创建:DELIMITER $$
CREATE TRIGGER ins_filmAFTER INSERT ON film FOR EACH ROW
BEGIN INSERT INTO film_text (film_id, title,description)VALUES (new.film_id, new.title,new.description);END;
$$
delimiter ;
删除触发器:drop trigger ins_film