MySQL基础学习2
常见的数据类型
一、数值型:
分类:
1.整形
2.小数:
定点数
浮点数
3.字符型:
较短的文本:char、varchar
较长的文本:text、blob(较长的二进制数据)
4.日期型:
特点:
1.如果不设置无符号还是有符号,默认是有符号,如果想设置无符号,需要添加unsigned关键字
2.不设置长度,会有默认长度
3.长度代表了显示的最大宽度,如果不够会用0在左边填充,但必须搭配zerofill使用
1.整形
分类:
1.Tinyint 1字节 -128~127
2.Smallint 2字节 -32768-32767
3.Mediumint 3字节
4.int、Integer 4字节
5.Bigint 5字节
1.如何设置成无符号和有符号?
#默认有符号
CREATE TABLE tab_int(
t1 INT, #默认有符号
t2 INT UNSIGNED #默认无符号
);
DESC tab_int #查看表的基本信息
SELECT * FROM tab_int; #查看表的数据
#zerofill关键字(默认unsigned),会填充0直到宽度位置
二、小数
1.浮点型:float(m,d) double(m,d)
4字节 8字节
2.定点型:dec(m,d)
m+2字节 精确度较高
特点:
①M和D:
D表示小数点后保留几位
M表示整数位+小数点位总共位数
②M和D都可以省略
如果是decimal,则M默认为10,D默认为0
如果是float和double,则会根据插入的数值的精度来决定精度
③定点型的精确度较高,如果要求插入数值的精度要求较高,比如货币运算,就用定点型
CREATE TABLE tab_float(
f1 FLOAT(5,2),
f2 DOUBLE(5,2),
f3 DEC(5,2)
);
INSERT INTO tab_float VALUES (11.11,12.22,13.33);
INSERT INTO tab_float VALUES (11.1111,12.2222,13.3333);
INSERT INTO tab_float VALUES (111.11,122.22,133.33);
SELECT * FROM tab_float;
原则:所选择的类型越简单越好,能保存数值的类型越少越好
三、字符型
较短的文本:
char
varchar
其他:
binary和varbinary用于保存较短的二进制
enum用于保存枚举
set用于保存集合
较长的文本:
text
blob(较大的二进制)
特点:
写法 M的意思 特点 空间的耗费 效率
char(M) 最大的字符数,可以省略 固定长度的字符 比较耗费 高
varchar(M) 最大的字符数,不可以省略 可变长度的字符 比较节省 低
CREATE TABLE tab_char(
c1 ENUM('a','b','c')
);
INSERT INTO tab_char VALUES('a');
INSERT INTO tab_char VALUES('b');
四、日期型
分类:
data只保存日期
time只保存时间
year只保存年
datetime保存日期+时间
timestamp保存日期+时间
特点:
字节 范围 时区等的影响
datetime 8 1000-9999 不受
timestamp 4 1970-2038 受
CREATE TABLE tab_date(
t1 DATETIME,
t2 TIMESTAMP
);
INSERT INTO tab_date VALUES(NOW(),NOW());
SELECT * FROM tab_date;
SHOW VARIABLES LIKE ‘time_zone’; #显示时区
SET time_zone =’+9:00’; #设置时区
常见约束
含义:一种限制,用于限制表中的数据,为了保证表中的数据的准确和可靠性
分类:六大约束
not null:非空约束,用于保证该字段的值不能为空(姓名,性别..)
default:默认约束,用于保证该字段的值有默认值
primary key:主键,用于保证该字段的值具有唯一性,并且非空(学号)
unique:唯一,用于保证该字段的值具有唯一性,可以为空(电话)
check:检查约束,【mysql中不支持】,类似于添加条件
foreign key:外键,用于限制两个表的关系,用于保证该字段的值必须来自于主表的关联列的值,
在从表添加外键约束,用于引用主表中某列的值
添加约束的时机:
1.创建表时
2.修改表时
约束的添加分类:
列级约束:六大约束语法上都支持,但外键约束没有效果
表级约束:除了非空和默认其他都支持
主键和唯一的大对比:
保证唯一性 是否允许为空 一个表中可以有多少个 是否允许组合
主键 √ × 至少有1个 允许,但不推荐
唯一 √ √ 可以有多个 允许,但不推荐
外键:
1.要求在从表设置外键关系
2.从表的外键列的类型和主表的关联列的类型要求一致或兼容,名称无要求
3.主表的关联列必须是一个KEY(一般是主键或唯一)
4.插入数据时,先插入主表,再插入从表
删除数据是,先删除从表,再删除主表
语法:
CREATE TABLE 表名(
字段名 字段类型 列级约束
字段名 字段类型
表级约束
);
一、创建表时添加约束
1.添加列级约束
语法:
直接在字段名和类型后面追加约束类型即可
支持类型:默认、非空、主键、唯一
CREATE DATABASE students; #创建一个数据库
CREATE TABLE stuinfo(
id INT PRIMARY KEY, #主键
stuName VARCHAR(20) NOT NULL, #非空
gender CHAR(1) CHECK(gender='男' OR gender='女'),#检查约束,mysql不支持
seat INT UNIQUE, #唯一约束
age INT DEFAULT 18, #默认约束
majorId INT REFERENCES major(id) #外键约束
);
DESC stuinfo; #查看表基本信息
2.添加表级约束
语法:在各个字段最下面
[constraint 约束名] 约束类型(字段名)
DROP TABLE IF EXISTS stuinfo; #把刚刚那个删了
通用的写法:除了外键其他都可以列级约束
CREATE TABLE IF NOT EXISTS stuinfo(
id INT PRIMARY KEY,
stuname VARCHAR(20) NOT NULL,
sex CHAR(1),
age INT DEFAULT 18,
seat INT UNIQUE,
majorid INT,
#一般表级约束是用来写外键的
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
);
二、修改表时添加约束
1.添加列级约束
alter table 表名 modify column 字段名 字段类型 新约束;
2.添加表级约束
alter table 表名 add [constraint 约束名] 约束类型(字段名) 【外键的引用】;
案例:
#1.添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;
#2.添加默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
#3.添加主键
#①列级约束
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
#②表级约束
ALTER TABLE stuinfo ADD PRIMARY KEY(id);
#4.添加唯一
#①列级约束
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
#②表级约束
ALTER TABLE stuinfo ADD UNIQUE(seat);
#5.添加外键
ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid)
REFERENCES major(id);
三、修改表时删除约束
#1.删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;
#2.删除默认约束
ALTER TABLE stuinfo MODIFY COLUMN stuname age INT;
#3.删除主键
ALTER TABLE stuinfo DROP PRIMARY KEY;
#4.删除唯一
ALTER TABLE stuinfo DROP INDEX seat;
#5.删除外键、
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;
标识列
又称为自增长列
含义:可以不用手动的插入值,系统提供默认的序列值
特点:
1、标识列必须和主键搭配吗?
不一定,但要求是一个key(主键,外键,唯一)
2、一个表至多一个标识列
3、标识列的类型
只能是数值型
4、标识列可以通过 set auto_increment_increment=3来设置步长
可以通过手动插入值来设置初始值
一、创建表时设置标识列
CREATE DATABASE test; #创建数据库
CREATE TABLE tab_identity( #创建表
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);
TRUNCATE TABLE tab_identity; #删除表中数据
INSERT INTO tab_identity VALUES(NULL,'john'); #往表中添加数据
SELECT * FROM tab_identity;
SET auto_increment_increment=3;#设置步长为3
#二、修改表时设置标识列
ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;
#三、修改表时删除标识列
ALTER TABLE tab_identity MODIFY COLUMN id INT;
TCL
Transaction Control Language 事务控制语言
事务:
一个或一组sql语句组成一个执行单元,这个执行单元要么全部执行,要么全部不执行
ACID属性
1.原子性(atomicity):指事务是一个不可分割的工作单位,
事务中的操作要么都发生,要么都不发生
2.一致性(consistency):事务必须使数据库从一个一致性状态变换到另一个一致性状态
3.隔离性(isolation):事务的隔离性是指一个事务的执行不能被其他事务干扰
4.持久性(durability):事务一旦被提交,它对数据库中的数据的改变就是永久性
事务的创建类型:
隐式事务:事务没有明显的开启和结束的标记
比如insert、update、delete语句
显示事务:事务具有明显的开启和结束的标记
设置事务的前提:必须先设置自动提交功能为禁用
set autocommit=0;
步骤一、开启事务:
set autocommit=0;
start transaction; #可选的
步骤二:编写事务中的sql语句(select,insert,update,delete)
语句一;
语句二;
。。。
步骤三:结果事务
commit;提交事务
rollback;回滚事务
savepoint 节点名;设置保存点
事务隔离级别:
脏读 不可重复读 幻读
read uncommitted: √ √ √
read committed: × √ √
repeatable read: × × √
serializable: × × ×
mysql中默认第三个隔离级别
oracle中默认第二个隔离级别
查看隔离级别
select @@tx_isolation;
设置隔离级别
set session|global transaction isolation level 隔离级别;
#查询存储引擎
SHOW ENGINES;
#演示事务的使用步骤(转账)
CREATE TABLE account(
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20),
balance DOUBLE
);
INSERT INTO account(username,balance)
VALUES('张无忌',1000),('赵敏',1000);
#开始事务
SET autocommit=0;
START TRANSACTION;
#编写一组事务的语句
UPDATE account SET balance=1000 WHERE username='张无忌';
UPDATE account SET balance=1000 WHERE username='赵敏';
#结束事务
#rollback; 回滚
COMMIT;
2、delete和truncate在事务中使用的区别
delete可以回滚,truncate不能回滚
3、演示savepoint 的使用
SET autocommit=0;
START TRANSACTION;
DELETE FROM account WHERE id=2;
SAVEPOINT a;#设置保存点
DELETE FROM account WHERE id=1;
ROLLBACK TO a;
视图
含义:虚拟表,和普通表一样使用
mysql5.1版本出现的新特性,是通过表动态生成的数据
类似于函数封装
创建表和创建视图的区别
关键字 是否占用空间 使用
创建表 create table 保存了数据 增删改查
创建视图 create view 只是保存了sql逻辑 增删改查,一般不能用增删改
一、创建视图
语法:
create view 视图名
as
查询语句
#案例:查询姓张的学生名和专业名
以前用的方法:
SELECT stuname,majorname
FROM stuinfo s
INNER JOIN major m ON s.`majorId`=m.`id`
WHERE s.`stuname` LIKE '张%';
#创建视图
CREATE VIEW v1
AS
SELECT stuname,majorname
FROM stuinfo s
INNER JOIN major m ON s.`majorId`=m.`id`
SELECT * FROM v1 WHERE stuname LIKE'张%';#调用视图
二、视图的修改
#方式一
create or replace view 视图名
as
查询语句;
案例:
CREATE OR REPLACE VIEW myv3
AS
SELECT AVG(Salary),job_id
FROM employees
GROUP BY job_id;
#方式二
语法:
alter view 视图名
as
查询语句;
ALTER VIEW myv3
AS
SELECT * FROM employees;
三、删除视图
语法:
drop view 视图名,视图名,…;
DROP VIEW myv3;
四、查看视图
DESC myv3;#视图的约束信息
SHOW CREATE VIEW myv3; #更详细的信息。。(不知道怎么说)
#案例:创建视图emp_v1,要求查询电话号码以’011’开头的员工姓名和工资、邮箱
CREATE OR REPLACE VIEW emp_v1
AS
SELECT last_name,Salary,email
FROM employees
WHERE phone_number LIKE '011%';
SELECT * FROM emp_v1;#查询视图数据
五、视图的更新
基本视图是不能更新的,也不建议更新
可以对视图增删改查,但是会同时更改原表
CREATE OR REPLACE VIEW myv5
AS
SELECT last_name,email,Salary*12*(1+IFNULL(commission_pct,0)) as "annual salary"
FROM employees
INSERT INTO myv5 VALUES('张飞','zf@qq.com',100000)
变量
分类:
系统变量:
全局变量
会话变量
自定义变量:
用户变量
局部变量
一、系统变量
说明:变量由系统提供,不是用户定义,属于服务器层面
使用的语法:
1、查看所有的系统变量(默认session)
SHOW GLOGAL VARIABLES;
2、查看满足条件的部分系统变量
SHOW GLOBAL(全局)VARIABLES LIKE '%char%';
3、查看指定的某个系统变量的值
SELECT @@global .系统变量名
4、为某个系统变量赋值
方式一:
SET GLOBAL 系统变量名=值;
方式二:
SET @@global .系统变量名=值;
注意:
如果是全局级别,需要加global
如果是会话级别,为session,默认session
1.全局变量
作用域:服务器每次启动将为所有的全局变量赋予初始值,可以跨会话,不能跨重启
#1.查看全局变量
SHOW GLOBAL VARIABLES;
#2.查看部分全局变量
SHOW GLOBAL VARIABLES LIKE '%char%';
#3.查看指定的全局变量值
SELECT @@global.autocommit;
SELECT @@tx_isolation;
#4.为某个指定的全局变量赋值
SET @@global.autocommit=0;
2.会话变量
作用域:仅仅针对于当前会话(连接)有效
#①查看所有的会话变量
SHOW VARIABLES;
②查看部分的会话变量
SHOW VARIABLES LIKE '%char%';
#③查看指定的某个会话变量
SELECT @@tx_isolation;
#④为某个会话变量赋值
方式一:
SET @@session.tx_isolation='read-uncommitted';
方式二:
SET SESSION tx_isolation='read-committed';
自定义变量
说明:变量是用户自定义的,不是由系统定义的
1.用户变量
作用域:针对于当前会话(连接)有效,同于会话变量的作用域
赋值的操作符: =或:=
#①声明并初始化
SET @用户变量名=值;
SET @用户变量名:=值;
SELECT @用户变量名:=值;
#②赋值
方式一:通过set或select
SET @用户变量名=值;
SET @用户变量名:=值;
SELECT @用户变量名:=值;
方式二:通过select INTO
SELECT 字段 INTO 变量名
FROM 表
#案例:
SET @name=‘john’;
SET @name=100; #重新赋值了
2.局部变量
作用域:仅仅在定义它的begin end中有效
应用在begin end中的第一句话
#①声明
DECLARE 变量名 类型
DECLARE 变量名 类型 DEFAULT 值;
#②赋值
方式一:通过set或select
SET 局部变量名=值;
SET 局部变量名:=值;
SELECT @局部变量名:=值;
方式二:通过select INTO
SELECT 字段 INTO 局部变量名
FROM 表
#③使用
SELECT 局部变量名;
#对比下用户变量和局部变量:
作用域 定义和使用的位置 语法
用户变量 当前会话 会话中的任何地方 必须加@符号,不用限定类型
局部变量 begin end中 只能在begin end中且为第一句话 一般不用加@符号,要限定类型
#案例:声明两个变量并附初始值,求和,打印
#1.用户变量
SET @m=1;
SET @n=2;
SET @sum=@m+@n;
SELECT @sum;
#2.局部变量(要在begin end中)
DECLARE a INT DEFAULT 1;
DECLARE b INT DEFAULT 2;
DECLARE SUM INT;
SET SUM=a+b;
SELECT SUM;
存储过程和函数
存储过程和函数,类似于java中的方法
好处:
1.提高代码的重用性
2.简化操作
3.减少了编译次数并且减少了和数据库服务器的连接次数,提高了效率
存储过程
含义:一组预先编译好的sql语句的集合,理解成批处理语句
一、创建语法
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
存储过程体
END;
注意:
1、参数列表包含三部分
参数模式
参数名
参数类型
参数模式:
in:该参数可以作为输入,也就是该参数需要调用方传入值
out:该参数可以作为输出,也就是该参数可以作为返回值
inout:该参数既可以作为输入又可以作为输出,也就是该参数既需要传入值,又可以返回值
注意:
如果存储过程体仅仅只有一句话, begin end 可以省略
存储过程体中的每条sql语句的结尾要求必须加分号
存储过程的结尾可以使用 delimiter 重新设置
语法:
delimiter 结束标记
案例:
delimiter $
二、调用语法
CALL 存储过程名(实参列表)
#1.空参列表
#案例:插入到admin表中五条记录
CREATE PROCEDURE myp1()
BEGIN
INSERT INTO admin(username,`password`)
VALUES ('john1','0000'),('lily','0000'),('rose','0000'),('jack','0000'),('tom','0000');
END $
#调用
CALL myp1()$
#2.创建一个带in模式参数的存储过程
#案例:创建存储过程实现,用户是否登录成功
CREATE PROCEDURE myp4(IN username VARCHAR(20),IN PASSWORD VARCHAR(20))
BEGIN
DECLARE result INT DEFAULT 0;
SELECT COUNT(*) INTO result
FROM admin
WHERE admin.username=username
AND admin.`password`=PASSWORD;
SELECT IF(result>0,'成功','失败');
END $
#调用
CALL myp3('张飞','8888')$
#3.创建带out模式的存储过程
#案例:根据女神名,返回对应的男神名
CREATE PROCEDURE myp5(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))
BEGIN
SELECT bo.boyName INTO boyName
FROM boys bo
INNER JOIN beauty b ON bo.id = b.boyfriend_id
WHERE b.name=beautyName;
END $
#调用
SET @bName$
CALL myp5('小昭',@bName)$
SELECT @bName$
#4.创建带inout模式参数的存储过程
#案例:传入a和b两个值,最终a和b都翻倍并返回
CREATE PROCEDURE myp8(INOUT a INT,INOUT b INT)
BEGIN
SET a=a*2;
SET b=b*2;
END $
#调用
SET @m=10$
SET @n=20$
CALL myp8(@m,@n)$
SELECT @m,@n$
#二、删除存储过程
语法:drop procedure 存储过程名
DROP PROCEDURE p1
#三、查看存储过程的信息
SHOW CREATE PROCEDURE myp2;
Tips:存储过程不能修改,想修改就删了重新写
#--------------------------------------------------------------------------------
函数
含义:一组预先编译好的sql语句的集合,理解成批处理语句
函数和存储过程的区别:
存储过程:可以有0个返回,也可以有多个返回,适合做批量插入、批量更新
函数:有且仅有1个返回,适合做处理数据后返回一个结果
一、创建语法
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
函数体
END
注意:
参数列表:参数名AND参数类型
函数体:肯定会有return语句,如果没有会报错
return 值;
3.函数体中仅有一句话,则可以省略bgin end
二、调用语法
SELECT 函数名(参数列表)
1.无参有返回
#案例:返回公司的员工个数
CREATE FUNCTION myf1() RETURNS INT
BEGIN
DECLARE c INT DEFAULT 0;
SELECT COUNT(*) INTO c
FROM employees;
RETURN c;
END $
SELECT myf1()$
#2.有参有返回
#案例;根据员工名返回工资
CREATE FUNCTION myf2(empName VARCHAR(20)) RETURN DOUBLE
BEGIN
SET @sal=0; #定义用户变量
SELECT Salary INTO @sal
FROM employees
WHERE last_name = empName;
RETURN @sal;
END$
SELECT myf2('kochhar')$
三、查看函数
SHOW CREATE FUNCTION myp3;$
四、删除函数
DROP FUNCTION myp3;
#案例:创建函数:实现传入两个float,返回二者之和
CREATE FUNCTION test_fun1(num1 FLOAT,num2 FLOAT) RETURNS FLOAT
BEGIN
DECLARE SUM FLOAT DEFAULT 0;
SET SUM=num1+num2;
RETURN SUM;
END $
SELECT test_fun1(1,2);
流程控制结构
分类:
顺序结构:程序从上往下依次执行
分支结构:程序从两条或多条路径中选择一条去执行
循环结构:程序在满足一定条件的基础上,重复执行一段代码
一、分支结构
1.if函数
功能:实现简单的双分支
语法:
IF(表达式1,表达式2,表达式3)
执行顺序:
如果表达式1成立,则if函数返回表达式2的值,否则返回表达式三的值
应用:任何地方
2.case结构
情况1:类似于java中的switch语句,一般用于实现等值判断
语法:
CASE 变量|表达式|字段
WHEN 要判断的值 THEN 返回的值1【或语句1;】
WHEN 要判断的值 THEN 返回的值2【或语句2;】
。。。
ELSE 要返回的值n【或语句n;】
END 【case】;
情况2:类似于java中的多重if语句,一般用于实现区间判断
语法:
CASE
WHEN 要判断的条件1 THEN 返回的值1【或语句1;】
WHEN 要判断的条件2 THEN 返回的值2【或语句2;】
。。。
ELSE 要返回的值nn【或语句n;】
END 【case】;
特点:
①可以作为表达式,嵌套在其他语句中使用,可以放在任何地方,begin end中或begin end的外面
可以作为独立的语句去使用,只能放在begin end中
②如果when中的值满足或条件成立,则执行对应的then后面的语句,并且结束case
如果都不满足,则执行else中的语句或值
③else可以省略,如果省略了,并且所有when条件都不满足,则返回null
#案例
#创建存储过程,根据传入的成绩,来显示登记,比如传入的成绩:90-100,显示A,80-90显示B,60-80显示C,否则显示D
CREATE PROCEDURE test_case(IN score INT)
BEGIN
CASE
WHEN score>=90 AND score<=100 THEN SELECT 'A';
WHEN score>=80 THEN SELECT 'B';
WHEN score>=60 THEN SELECT 'C';
ELSE SELECT 'D';
END CASE;
END$
CALL test_case(67)$
3.if结构
功能:实现多重分支
语法:
if 条件1 then 语句1;
elseif 条件2 then 语句2;
。。
【else 语句n;】
end if;
应用场合:
只能在begin end中
#案例:根据传入的成绩,来显示登记,比如传入的成绩:90-100返回A,80-90返回B,60-80返回C,否则返回D
CREATE FUNCTION test_if(score INT) RETURNS CHAR
BEGIN
IF score>=90 AND score<=100 THEN RETURN 'A';
ELSEIF score>=80 THEN RETURN 'B';
ELSEIF score>=60 THEN RETURN 'C';
ELSE RETURN 'D';
END IF;
END $
SET @m=76$
SELECT test_if(@m)$
二、循环结构
分类:
while、loop、repeat
循环控制:
iterate类似于continue,继续,结束本次循环,继续下一次
leave 类似于 break,跳出,结束当前所在的循环
1.while
语法:
[标签:]while 循环条件 do
循环体
end while[标签];
2.loop
语法:
[标签:]loop
循环体;
end loop[标签];
可以用来模拟简单的死循环
#3.repeat
语法:
[标签:]repeat
循环体;
until 结束循环的条件
end repeat[循环];
类似于do while
#while语句
#案例:批量插入,根据次数插入到admin表中多条记录
CREATE PROCEDURE pro_while(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i<=insertCount DO
INSERT INTO admin(username,`password`)
VALUES(CONCAT('rose',i),'666');
SET i=i+1;
END WHILE;
END $
CALL pro_while(100)$
#添加leave语句
#案例:批量插入,根据次数插入到admin表中多条数据,如果次数>20则停止
TRUNCATE TABLE admin$
CREATE PROCEDURE test_while1(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
a:WHILE i<insertCount DO
INSERT INTO admin(username,`password`)VALUES(CONCAT('xiaohua',i),'0000');
IF i>20 THEN LEAVE a;
END IF;
SET i=i+1;
END WHILE a;
END $
CALL test_while1(100)$