MySQL基础

MySQL基础_第1张图片

MySQL基础

一、查询的SQL语句(DQL语言的一部分)

1、查询语句

1.1 基本语句

注意:sql语句对大小写不敏感,所以创建表时id int,Id varchar这样的不能同时出现。

查询可以查:常量、表达式、函数;

SELECT 100;

SELECT 100-92;

SELECT VERSION();

如果想查的字段包含重复的,自己想去重,那么可以使用distinct

#显示有哪些部门编号
SELECT DISTINCT department_id FROM employees;

+的作用:java中有拼接的功能,而mysql中不是,只有运算的功能;比如两个运算,如果直接能算,那么就计算,如果不能那么会试图把他们转换成数值型再计算,如果转换失败就会把它们转换为0,如果其中有一方为null,那么结果是null;

#查询员工姓和名,拼接成姓名    下面这个是错的
SELECT first_name+last_name`departments` '姓名' FROM employees;

#上面这个拼接可以用CONCAT,CONCAT(str1,str2......)函数
SELECT CONCAT(first_name,last_name) AS '姓名' FROM employees;

#这个也能计算成功,结果213
SELECT 123 + '90';

#结果是123
SELECT 123 + 'abv';

#结果是0
SELECT 'abc' + 'abv';

#结果为null
SELECT 90 + null;

如果某个字段中有null值,拼接会为null,那么就需要使用函数IFNULL()了。

IFNULL(`字段名`,0);    =>#这表示把某个字段的null值变为0

1.2 where——筛选

#语法
SELECT 查询的列表 FROM 表名 WHERE 筛选条件;
筛选条件分为几类
  • 按条件表达式筛选: =、<、>、<=、>=、<>
  • 按逻辑表达式筛选:&&(与)、||(或)、!(非) ===>and、or、not
  • 模糊查询:like、between...and、in、is null;like模糊查询其中%表示任意多个字符,_表示任意一个字符;between...and前后数不能调换,in中的值类型必须统一
  • is null和<=>:is null只能判断null值,<=>除了判断null值,还可以判断别的值,推荐使用is null判断null值,因为可读性高
#比如like的字段包含特殊字符需要转义,查询名第二个字符为_的
SELECT * FROM employees WHERE last_name LIKE '_\_%';

#判断某个列值为null的用户信息,可以使用is null,也可以用<=>,这里推荐使用is null,因为可读性高
SELECT last_name,job_id,commission_pct FROM employees WHERE commission_pct <=> NULL;

1.3 order by——排序

order by后面支持别名。下面这个查询完年薪起别名,可以按别名排序。

#查询所有员工信息、年薪,并按年薪高低排序
SELECT *,salary * 12 * (1 + IFNULL(commission_pct,0)) 年薪 FROM employees ORDER BY 年薪;

#当有两个及以上的列排序时,直接写即可
SELECT salary,employee_id FROM employees ORDER BY salary,employee_id DESC;

order by子句后面可以跟单个字段、多个字段、表达式、函数、别名。

order by子句一般放在查询语句最后面,limit子句除外。

1.4 常见函数——单行函数

程序帮我们封装了,这样我们就可以直接使用,提高了代码的可复用性。

分类:
  • 单行函数:concat、length、ifnull等
  • 分组函数:又称为统计函数、聚合函数、分组函数、组函数
(1)字符函数
  • length:表示获取参数值的字节数;

默认utf-8字符集:一个字母占1个字节、一个汉字占3个字节,不同的字符集占字节数不相同。

#如何查看当前字符集
SHOW VARIABLES LIKE '%char%';
#可以看到结果中有
Variable_name            Value
character_set_client     utf8

#那么如下结果
select length('张三丰loveyou');
#上面这个结果是:3个汉字3*3 = 9,7个字母为7,长度是9 + 7 = 16
  • concat:拼接字符串
#拼接可以用CONCAT,CONCAT(str1,str2......)函数
SELECT CONCAT(first_name,last_name) AS '姓名' FROM employees;
  • upper和lower:转换为大写和小写
SELECT UPPER('zhangSanfEng');
SELECT LOWER('zhangSanfEng');
  • substr、substring:从索引开始截取字符串,索引是从1开始的,和java不一样
#从下标为6的地方开始截取:结果为慕容复
SELECT SUBSTR('张三丰大战慕容复',6) 'winner';

#从下标为4的地方向后截取两位:结果为大战
SELECT SUBSTR('张三丰大战慕容复',4,2) 'winner';
综合练习:姓名首字母大写,其他字母小写
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),LOWER(SUBSTR(last_name,2))) FROM employees;
  • instr:查询某个字符串A在字符串B的索引位置,找不到返回0
#查询哈哈哈在张三丰哈哈哈的什么位置
SELECT INSTR('张三丰哈哈哈','哈哈哈');
  • trim:去掉前后的字符,下面是去掉前后的‘ ’空格
#结果为张三丰
SELECT LENGTH(TRIM(' ' FROM '    张三丰     '));

#结果为张三丰aaaaaaa张三疯
SELECT TRIM('a' FROM 'aaaaaaaaaa张三丰aaaaaaa张三疯aaaaaaaaaa');
  • lpad:左填充指定长度的指定字符;rpad同理:右填充
#这个表示初始字段是‘刘备’,然后用大萌萌填充到10个字符,所以最后结果是:大萌萌大萌萌大萌刘备
SELECT LPAD('刘备',10,'大萌萌');
  • replace:替换
#把玄德换成黄书,变成:刘黄书,我是刘黄书,刘黄书就是我,天下英雄只有刘黄书和曹操
SELECT REPLACE('刘玄德,我是刘玄德,刘玄德就是我,天下英雄只有刘玄德和曹操','玄德','黄书');
(2)数学函数
  • round:四舍五入
#可以放数值,这种是四舍五入
SELECT ROUND(-11.24);

#还可以放两个数,表示取到第几位
SELECT ROUND(122.237323,2);
  • ceil:向上取整;floor:向下取整
SELECT ceil(1.24);    #结果为2

SELECT floor(1.24);    #结果为1
  • truncate:截断
#从小数后1位截断,也就是1.6
SELECT TRUNCATE(1.65,1);
  • mod取余
SELECT MOD(10,3);    #结果为1
(3)日期函数
  • now:返回当前系统日期,含时间
SELECT NOW();
  • curdate:返回系统当前日期,不含时间
SELECT CURDATE();
  • curtime:返回系统当前时间,不含日期
SELECT CURTIME();
  • 获取指定的部分,年、月、日、小时、分钟、秒等
SELECT CONCAT(YEAR(NOW()),'年',MONTH(NOW()),'月',DAY(NOW()),'日',HOUR(NOW()),'时',MINUTE(NOW()),'分',SECOND(NOW()),'秒');
  • str_to_date:将日期格式的字符转换成指定格式的,格式不只有这一种
SELECT STR_TO_DATE('1994-10-10','%Y-%m-%d');
  • date_format:将日期转换成字符
SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') as out_put;
  • date_diff:计算日期相差多少天
SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) FROM employees;

SELECT DATEDIFF('2021-09-04','1998-10-02');
(4)其他函数
  • version:版本号
  • database:select database();
  • user:select user();
(5)流程控制函数
  • if:类似if else
#类似三元运算符
SELECT IF(10 < 5,'大','小');
  • case:第一种方式:switch case的效果
#看部门,不同的部门薪水的比重不一样
SELECT salary,department_id,
CASE department_id
    WHEN 30 THEN salary * 1.1
    WHEN 40 THEN salary * 1.2
    WHEN 50 THEN salary * 1.3
    ELSE salary
END AS '新工资'
FROM employees;

-------------------------------------------------
#另一个示例
select 
case 3
when 3 then '这是3'
WHEN 4 then '这是4'
WHEN 5 then '这是5'
else '这个我不认识'
end as '数字';

1.5 常见函数——分组函数

传一组值,变成一个值。

  • sum:求和
  • avg:平均数
  • max:最大值
  • min:最小值
  • count:计算个数

分组函数特点:

  1. sum和avg只支持数值型;
  2. count、max、min可用于处理任何类型;
  3. 所有分组函数均忽略null值,直接这一列就不计算,比如计算平均值,100个人有20个人有null值,那么计算avg除的是80;
  4. 所有分组函数都可以和distinct搭配实现去重运算;
  5. count(*)和count(列名),但是count(列名)如果为null不计算,SELECT COUNT(1) FROM employees;等于添加了一列1值,然后计算1的数量,等于计算了总个数,但是几乎不用;

在myisam存储引擎下,count(*)的效率最高,因为它里面包含了计数器,可以直接返回个数;

在innodb存储引擎下,count(*)和count(1)的效率差不多,但是比count(列名)高一些,因为count(列名)要进行一次null值判断

注意:和分组函数一起查询的字段是有限制的
SELECT AVG(salary),employee_id from employees;

上面一个查的是一行,一个查的是多行,所以查出来的是有问题的,肯定不对。

1.6 group by——分组

#查询每个工种的最高工资SELECT MAX(salary),job_id FROM employees GROUP BY job_id;

1.7 having——查询结果作为查询的条件

#查询部门的员工个数大于二的部门SELECT COUNT(*) AS 数量,department_id FROM employees GROUP BY department_id HAVING 数量 > 2;#查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资

自己理解:这个查询如果使用where 数量 > 2肯定是不行的,因为where子句只能查询当前表,但是当前表中查不到数量字段,那么想使用查询结果作为一张表查询的可以使用having字句。

having的分组条件

还可以按表达式或函数分组或别名(废话):

SELECT COUNT(*),LENGTH(last_name) len  FROM employees GROUP BY LENGTH(last_name) HAVING COUNT(*) > 5;SELECT COUNT(*)AS 数量,LENGTH(last_name) len  FROM employees GROUP BY LENGTH(last_name) HAVING 数量 > 5;

group by子句支持单个字段分组,也支持多个字段分组。

2、多表连接查询

2.1 连接条件及分类

从beauty的某女连接boys的某男,使用如下sql语句:

SELECT `name`,boyname FROM beauty,boys;

这样查询语法上是没毛病的,但是会产生笛卡尔积现象;A表的每条数据都要和B表达的每条数据进行连接,从而产生不是我们想要的数据结果。需要加上相应的查询条件才能避免笛卡尔积。

所以条件如下:

SELECT     `name`,boyname FROM     beauty,boys WHERE     boyfriend_id = boys.id;

添加有效的连接条件就可以避免,那么添加条件可以分为:

  1. sql92标准:仅支持内连接和外连接。
  2. sql99标准:内连接,外连接(左外 + 右外),交叉连接。
分类:
  • 内连接

    • 等值连接
    • 非等值连接
    • 自连接
  • 外连接

    • 左外连接
    • 右外连接
    • 全外连接
  • 交叉连接

2.2 sql92语法

(1)等值连接

多表等值连接为多表的交集部分,n表连接至少要n-1个连接条件。

#员工名和部门名对应查询SELECT     last_name,department_name FROM     employees,departments WHERE     employees.`department_id` = departments.`department_id`;    #查询员工名,工种号,工种名SELECT     last_name,employees.job_id,job_title #这里有歧义,用表名.字段名来表示FROM     employees,jobsWHERE    employees.`job_id` = jobs.`job_id`;

如果表连接字段总是重复,两表容易有歧义,所以可以使用别名。

SELECT     e.last_name,e.job_id,j.job_titleFROM     employees e,jobs jWHERE    e.`job_id` = j.`job_id`;
(2)非等值连接

也就是上面的等于条件变为不等于条件。

#查询员工的工资和工资等级SELECT     e.`salary`,j.`grade_level`FROM    employees e,job_grades jWHERE     e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`;
(3)自连接

连接的就是自己。

#查询员工名和上级员工名SELECT     e.`last_name`,f.`last_name`FROM     employees e,employees fWHERE     e.`manager_id` = f.`employee_id`;

2.3 sql99语法

#语法:SELECT     查询列表FROM     表1 别名join     表2 别名on     连接条件where    筛选条件;

上面可读性变高了,因为以前连接条件和筛选条件都是在一起的,而现在则分离了,比如以前的e.manager_id = f.employee_id;是连接条件却放在了where中。

以前学过了sql99包含很多种连接,每种的语法都不太一样,在join加上不同的说明即可:

内连接(inner)、左外连接(left 【outer】outer可省略)、右外连接(right【outer】outer可省略),全外连接(full【outer】outer可省略),交叉连接(cross)。

(1)内连接:等值、非等、自连接
#查询员工名和上级员工名SELECT     e.`last_name`,f.`last_name`FROM    employees eINNER JOIN     employees fON    e.`manager_id` = f.`employee_id`;
(2)左外连接和右外连接
#查询员工名和上级员工名#左外连接SELECT     e.`last_name`,f.`last_name`FROM    employees eLEFT JOIN     employees fON    e.`manager_id` = f.`employee_id`;    #右外连接    SELECT     e.`last_name`,f.`last_name`FROM    employees eRIGHT JOIN     employees fON    e.`manager_id` = f.`employee_id`;        

拿A匹配B,也就是B join A,那么A表为主表,B表为从表,A中所有的数据都会显示,从表中匹配不到的用null去匹配。

left join:左边的是主表,右边的是从表;

right join:右边的是主表,左边的是从表;

(3)全外连接

mysql这里不支持。只是演示一下语法。

SELECT     b.*,bo.*FROM     beauty b,FULL OUTER JOIN     boys boON    b.`boyfriend_id` = bo.id;

就是把两个表全部数据都查出来,查不到的用null填充。

(4)交叉连接
SELECT b.*,bo.*FROM beauty bCROSS JOIN boys bo;

有点类似不加条件的查询,包含笛卡尔乘积。

3、子查询

3.1 子查询的分类

嵌套查询,外部查询语句叫主查询或外查询,内部查询语句叫子查询或内查询。

分类

按照子查询出现的位置可以分为:

  • 在select后面:仅仅支持标量子查询
  • from后面:支持表子查询
  • where或having后面:支持标量子查询、列子查询、也支持行子查询(出现的较少)
  • exists后面(相关子查询):支持的是表子查询

按照结果集的行列数不同:

  • 标量子查询(结果集只有一行一列)
  • 列子查询(结果集有一列多行)
  • 行子查询(结果集有一行多列)
  • 表子查询(结果集有多行多列)

3.2 where或having后面

特点:都放在小括号内,都放在条件的右侧。

  • 标量子查询:一般搭配着单行操作符:>、<、=、>=、<=、!=
#谁的工资比阿贝尔高SELECT * FROM employees WHERE salary > (SELECT salary FROM employees WHERE last_name = 'Abel');
salary
11000.00

select子查询的结果如上,确实是一行一列。

  • 列子查询:一般搭配着多行操作符:IN、ANY、SOME、ALL

    a > any(10,20,30) ==> a > min(10,20,30)

    a > all(10,20,30) ==> a > max(10,20,30)

#返回location_id是1400或1700的部门中所有员工的姓名SELECT     last_nameFROM     employeesWHERE     department_id IN(        SELECT DISTINCT             department_id        FROM             departments        WHERE             location_id IN (1400,1700))
  • 行子查询:一行多列或多行多列
#查询员工编号最小且工资最高的员工信息#列子查询方式SELECT     *FROM    employeesWHERE    employee_id = (        SELECT             MIN(employee_id)        FROM            employees    )AND    salary = (        SELECT            MAX(salary)        FROM            employees    )    #行子查询方式SELECT     *    FROM    employeesWHERE    (employee_id,salary) =(        SELECT             MIN(employee_id),MAX(salary)        FROM             employees    )

3.3 select后面

#查询每个部门的员工个数SELECT d.*,(SELECT COUNT(*) FROM employees e WHERE e.department_id = d.`department_id`) '个数'FROM departments d;

3.4 from后面

#查询每个部门的平均工资的工资等级SELECT     ag_dep.*,g.`grade_level`FROM E    SELECT         AVG(salary) ag,department_id     FROM         employees     GROUP BY         department_id) ag_depINNER JOIN     job_grades gON    ag_dep.ag BETWEEN lowest_sal AND highest_sal;

3.5 exists后面(相关子查询)

SELECT EXISTS (SELECT employee_id from employees);

4、分页查询

#语法:SELECT     查询列表FROM     表#可能有连接【join 子句】#on连接条件WHERE     筛选条件GROUP BY     分组字段HAVING     分组后的筛选LIMIT offset,size;#offsize:要显示条目的起始索引(起始索引从0开始)#size:表示要显示的条目个数
#显示前五条员工信息SELECT * FROM employees LIMIT 0,5;

公式:要显示的页数为page:每页条目数为size

那么起始索引为:(page-1)×size

也就是:limit (page-1)×size,size

5、联合查询

将多个查询语句的结果合并为一个结果。

#查询部门编号>90或邮箱包含a的员工信息SELECT * FROM employees WHERE department_id > 90UNION(SELECT * FROM employees WHERE email LIKE '%a%');
#语法:查询语句1UNION查询语句2;

什么时候用呢?

比如查询的结果来自不同的表,而且这几个表没有联系,但是所查询的信息一致的时候,可以用连接查询;而且这两个查询的结果列数需要相同。不然会报错,为了满足查询结果符合自己需求,查询的两个结果集的列顺序应一致

union关键字默认是去重的,如果使用的是union all可以包含重复项

二、增删改的SQL语句(DML的一部分)

1、插入语句

基本插入语句:

#语法:INSERT INTO 表名 (列名,...) VALUES (列值,...);

对于像二进制类型等在sql中没法直接插入,需要使用jdbc等java技术转换类型才能插入的,可以设置字段类型为nullable,这样,就可以不插入了。

可为空的字段,不想插入值的话,要么值写null,要么字段都不加上。

插入值不太多的时候可以用下面这个语句:

#语法:INSERT INTO 表名 SET 列名=值,列名=值...;
两种方式对比
  • 第一种方式支持多行插入,第二种不支持;
  • 第一种方式支持子查询,第二种不支持;
INSERT INTO employees(employee_id,last_name,email) SELECT(100,'James','110101');#第一种方式

2、修改语句

修改单表中记录的语句:

#语法:UPDATE 表名SET 列=新值,列值=新值...WHERE 筛选条件;

修改多表中记录的语句(sql92语法):

UPDATE 表1 别名,表2 别名SET 列=值,...WHERE 连接条件AND 筛选条件;

修改多表中记录的语句(sql99语法):

UPDATE 表1 别名INNER|LEFT|RIGHT| JOIN 表2 别名ON 连接条件SET 列=值,...WHERE 筛选条件;

3、删除语句

  • delete删除(单表删除):
DELETE FROM 表名 WHERE 筛选条件;
  • delete删除(多表删除sql92语法):
DELETE 表1别名,表2别名FROM 表1 别名,表2 别名WHERE 连接条件AND 筛选条件;
  • delete删除(多表删除sql99语法):
DELETE 表1别名,表2别名FROM 表1别名INNER|LEFT|RIGHT| JOIN 表2 别名ON 连接条件WHERE 筛选条件;

这里需要知道多表删除就是想删哪张表就delete哪个表,另一张表join进来

不加筛选条件整张表都删掉了,需要慎重。

  • truncate清空数据:
TRUNCATE TABLE 表名;

这样就是整张表的数据记录都删了,不能恢复,需要非常慎重

delete和truncate
  • delete可以加条件,truncate是直接删除不保留
  • truncate效率高一点点
  • 假如说要删除的表中有自增长列,用delete删除后,再插入值,自增长列的值从断点开始;truncate从1开始(废话
  • truncate删除没有返回值,delete删除有返回值(几行受影响)
  • truncate删除不能回滚,delete删除可以回滚

三、库和表的SQL语句(DDL的一部分)

1、简单介绍

  • 创建:create
  • 修改:alter
  • 删除:drop

2、库的管理

2.1 库的创建

#语法:CREATE DATABASE 库名;#可加上条件CREATE DATABASE IF NOT EXISTS 库名;

2.2 库的修改

#修改库名RENAME DATABASE 库名 TO 新库名;#现在已经不支持了#更改库的字符集ALTER DATABASE 库名 CHARACTER SET 新字符集    #默认是utf8

2.3 库的删除

#语法:DROP DATABASE IF EXISTS 库名;

3、表的管理

3.1 表的创建

#语法:CREATE TABLE 数据库表名(    列名1 数据类型1 其他要求【约束】,    列名2 数据类型2 其他要求【约束】    ... ...);

3.2 表的删除

DROP TABLE 数据库表名;DROP TABLE IF EXISTS 数据库表名;

3.3 表的修改

#添加列ALTER TABLE 数据库表名 ADD 列名1 数据类型1 其他要求; #可以在其他要求的位置添加after来设置l#修改列的数据类型、尺寸默认值ALTER TABLE 数据库表名 MODIFY 列名1 数据类型1 其他要求;#删除列ALTER TABLE 数据库表名 DROP 列名1;#重命名列ALTER TABLE 数据库表名 CHANGE 旧列名 新列名;

3.4 表的复制

#仅仅复制表的结构CREATE TABLE 新表名 LIKE 旧表名;#复制表结构 +  数据CREATE TABLE 新表名 SELECT * FROM 旧表名;#只复制部分数据CREATE TABLE 新表名 SELECT 自己需要的部分;#只复制部分表结构CREATE TABLE 新表名 SELECT 自己需要的部分数据 WHERE 1 = 2;#部分结构和数据都复制过来,不想要数据,那就让where始终不满足即可

3.5 数据类型

怎么设置数据类型有无符号:默认是有符号的。设置成无符号,创建表时在表字段后面加上UNSIGNED

(1)整型

MySQL数据类型 含义(有符号如下,无符号的数据比下面大一倍)
tinyint(m) 1个字节 范围(-128~127)
smallint(m) 2个字节 范围(-32768~32767)
mediumint(m) 3个字节 范围(-8388608~8388607)
int(m) 4个字节 范围(-2147483648~2147483647)
bigint(m) 8个字节 范围(+-9.22*10的18次方)

(2)浮点型

默认数值精度会按照首次插入精度来确定。

MySQL数据类型 含义
float(m,d) 单精度浮点型 8位精度(4字节) m总个数,d小数位
double(m,d) 双精度浮点型 16位精度(8字节) m总个数,d小数位

(3)定点型

decimal(m,d) 参数m<65 是总个数,d<30且 dm=10,d=0。

(4)字符串

MySQL数据类型 含义
char(n),其中n表示字符数,n默认为1,可省略 固定长度,最多255个字符
varchar(n),其中n表示字符数,不可以省略n 固定长度,最多65535个字符
tinytext 可变长度,最多255个字符
text 可变长度,最多65535个字符
mediumtext 可变长度,最多2的24次方-1个字符
longtext 可变长度,最多2的32次方-1个字符

char比varchar耗费空间,但是效率更高一点。

(5)二进制数据

1._BLOB和_text存储方式不同,_TEXT以文本方式存储,英文存储区分大小写,而_Blob是以二进制方式存储,不分大小写。

2._BLOB存储的数据只能整体读出。

3._TEXT可以指定字符集,_BLO不用指定字符集。

(6)日期时间类型

MySQL数据类型 含义
date 日期 '2008-12-2'
time 时间 '12:25:36'
datetime 日期时间 '2008-12-2 22:06:44'
timestamp 自动存储记录修改时间
year 只记录年份

timestamp支持的范围比较小,也就到1970-2038年的某个时间,而datetime的取值可以从1000-9999年。

timestamp和实际的时区有关,更能反应出实际的时间,而datetime只能反映出当地时区。(建议这里可以自己测试一下

(7)枚举类型

又称为枚举类型哦,要求插入的值必须属于列表中指定的值之一。

如果列表成员为1~255,则需要1个字节存储;如果列表成员为255~65535,则需要2个字节存储,最多需要65535个成员!

(8)Set类型

和Enum类型类似,里面可以保存0~64个成员。和Enum类型最大的区别是:Set类型一次可以选取多个成员,而Enum只能选一个根据成员个数不同,存储所占的字节也不同。

成员数 字节数
1~8 1
9~16 2
17~24 3
25~32 4
33~64 8
CREATE TABLE table_set(    s1 SET('a','b','c','d')    );#可以像下面这样插入值,插入的不只一个INSERT INTO table_set VALUES('a');INSERT INTO table_set VALUES('a,b');INSERT INTO table_set VALUES('a,c,d');

3.6 显示表结构

DESCRIBE 表名;

image-20210902102020957

4、约束

4.1 简单介绍

一种限制,用于限制数据,保证其准确可靠。

一共有6大约束:

  • NOT NULL:非空约束,保证字段值不为空,比如姓名、学号等;
  • DEFAULT:默认约束,保证该字段有默认值,比如性别;
  • PRIMARY KEY:主键约束,保证该字段值的唯一性,并且非空,比如学号、员工编号等;
  • UNIQUE:唯一约束,保证该字段的值具有唯一性,但可以为空,比如座位号等;
  • CHECK:检查约束,【mysql中不支持,语法没错,但是mysql中不起作用】,比如年龄等,可以用检查约束限制范围;
  • FOREIGN KEY:外键约束,用于限制两个表的关系的,保证该字段值必须来自于主表的关联列的值,在从表中添加外键约束,用于引入主表的值,比如专业编号、员工部门编号等等;

约束添加的分类:

  • 列级约束:上面几个都可以写,语法都支持,但外键约束没有效果;
  • 表级约束:除了非空和默认,其他的都支持;
#列级约束和表级约束所在位置CREATE TABLE 表名(    字段名 字段类型 列级约束,    字段名 字段类型,    表级约束)

4.2 添加列级约束

#使用列级方式添加约束CREATE TABLE students(    id INT PRIMARY KEY,#主键    stuName VARCHAR(20) NOT NULL,#非空    gender CHAR(1) CHECK(gender='男'  OR gender='女'),#检查约束    seat INT UNIQUE,#唯一    age INT DEFAULT 18,#默认约束    majorId INT FOREIGN KEY REFERENCES major(id),#外键,当然这里是不支持的,上面说了列级约束外键没效果)CREATE TABLE major(    id INT PRIMARY KEY,#主键     majName VARCHAR(20) NOT NULL,#非空)

4.3 添加表级约束

#使用表级方式添加约束CREATE TABLE students(    id INT,    stuName VARCHAR(20),    gender CHAR(1),    seat INT,    age INT,    majorId INT,        CONSTRAINT pk PRIMARY KEY(id),#给id添加主键    CONSTRAINT uq UNIQUE(seat),#唯一键    CONSTRAINT ck CHECK(gender='男' OR gender='女'),#检查约束    CONSTRAINT fk_stuinfo_major FOREIGN KEY(major_id) REFERENCE major(id),#添加外键)
主键和唯一的对比:
保证唯一性 是否允许为空 一个表可以有几个 是否允许组合
主键 × 至多有多个 √,但表示两个字段组合成一个主键
唯一 √,不过只能有一个为null,多了还是算重复 可以有多个
外键的特点
  1. 要求在从表设置外键关系
  2. 从表的外键列的类型和主表的关联列的类型要求一致或兼容
  3. 主表中的关联列必须是一个key(一般是主键或唯一键)

4.4 修改表时添加约束

#添加列级约束ALTER TABLE 表名 MODIFY COLOMN 字段名,字段类型 新约束;#添加表级约束ALTER TABLE 表名 ADD [CONSTRAINT 约束名] 约束类型(字段名) [外键的引用];

4.5 修改表时删除约束

#删除非空约束ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 NULL;#删除默认约束ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型;#删除主键ALTER TABLE 表名 DROP PRIMARY KEY;#删除唯一约束ALTER TABLE 表名 DROP INDEX 字段名;#删除外键ALTER TABLE 表名 DROP FOREIGN KEY 字段名;

5、标识列

又称为自增长列,,可以不用手动插入值,系统提供默认的序列值。

一个表最多只能有一个标识列。

标识列必须和主键搭配吗,不一定,但是要求是一个key。

标识列的类型只能是数值型。

5.1 创建表时创建标识列

CREATE TABLE tab_identity(    id INT PRIMARY KEY AUTO_INCREMENT,#改为自增长    NAME VARCHAR(20),)

5.2 设置步长

#可以先查询一下步长SHOW VARIABLES LIKE '%auto_increment%';#设置步长SET auto_increment_increment = 3;

5.3 修改表时创建标识列

ALTER TABLE 表名 MODIFY COLUMN 列名 INT PRIMARY KEY AUTO_INCREMENT;

四、事务的SQL语句(TCL的一部分)

1、简单介绍

TCL:Transaction Control Language事务控制语言。

事务:一组sql语句要么都执行,要么都不执行(回滚)。

Innodb支持事务,Myisam、memory不支持事务。

#查看存储引擎SHOW ENGINES;

1.1 事务的ACID原则

  • 原子性(Atomicity):原子性是指事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生。
  • 一致性(Consistency):事务必须使数据库从一个一致性状态变换到另外一个一致性状态。
  • 隔离性(Isolation):事务的隔离性是指一个事务的执行不能被其他事务干扰,即一个事务内部的操作及使用的数据对并发的其他事务是隔离的,并发执行的各个事务之间不能互相干扰。
  • 持久性(Durability):持久性是指一个事务一旦被提交,它对数据库中数据的改变就是永久性的,接下来的其他操作和数据库故障不应该对其有任何影响。

2、事务的创建

  • 隐式事务: 没有明显的开启和结束的标记,比如delete、insert、update语句;每一条语句相当于一条事务;
  • 显式事务:有明显的开启和结束的标记,必须先设置上面这种自动提交功能为禁用,这样才能保证多条sql语句同时执行时,中途出现问题,不会一条一条提交;
#关闭自动提交SET AUTOCOMMIT = 0;

2.1 使用事务的步骤

#步骤1:开启事务SET AUTOCOMMIT = 0;START TRANSACTION;#这条语句不是必须的,因为关闭了自动提交功能就等同于开启了事务#步骤2:编写事务中的sql语句#语句1;#语句2;#步骤3:结束事务COMMIT;#提交事务ROLLBACK;#回滚事务;
delete和truncate事务的区别

delete删除可以回滚,truncate不可以,删了就没了。

3、并发情况下的事务问题

同时运行多个事务访问同一个数据资源时,可能会导致并发问题:

  • 脏读:两个事务T1、T2,T1修改了数据但未提交就被T2读取到了,如果T1回滚,T2读到的数据就是无效的了;(字段)
  • 不可重复读:两个事务T1、T2,T1读取了一个字段T2更新了,T1就读不到以前的那个值了;
  • 幻读:对两个事务T1、T2,T1从一个表中读取了一个字段,然后T2在该表中插入了新行,T1再读这个表会多出几行;(插入行)

3.1 数据库的事务隔离级别

  • Read uncommitted

读未提交,顾名思义,就是一个事务可以读取另一个未提交事务的数据。

事例:老板要给程序员发工资,程序员的工资是3.6万/月。但是发工资时老板不小心按错了数字,按成3.9万/月,该钱已经打到程序员的户口,但是事务还没有提交,就在这时,程序员去查看自己这个月的工资,发现比往常多了3千元,以为涨工资了非常高兴。但是老板及时发现了不对,马上回滚差点就提交了的事务,将数字改成3.6万再提交。

分析:实际程序员这个月的工资还是3.6万,但是程序员看到的是3.9万。他看到的是老板还没提交事务时的数据。这就是脏读。

  • Read committed

读提交,顾名思义,就是一个事务要等另一个事务提交后才能读取数据。

事例:程序员拿着信用卡去享受生活(卡里当然是只有3.6万),当他埋单时(程序员事务开启),收费系统事先检测到他的卡里有3.6万,就在这个时候!!程序员的妻子要把钱全部转出充当家用,并提交。当收费系统准备扣款时,再检测卡里的金额,发现已经没钱了(第二次检测金额当然要等待妻子转出金额事务提交完)。程序员就会很郁闷,明明卡里是有钱的…

分析:这就是读提交,若有事务对数据进行更新(UPDATE)操作时,读操作事务要等待这个更新操作事务提交后才能读取数据,可以解决脏读问题。但在这个事例中,出现了一个事务范围内两个相同的查询却返回了不同数据,这就是不可重复读。

  • Repeatable read

重复读,就是在开始读取数据(事务开启)时,不再允许修改操作

事例:程序员拿着信用卡去享受生活(卡里当然是只有3.6万),当他埋单时(事务开启,不允许其他事务的UPDATE修改操作),收费系统事先检测到他的卡里有3.6万。这个时候他的妻子不能转出金额了。接下来收费系统就可以扣款了。

分析:重复读可以解决不可重复读问题。写到这里,应该明白的一点就是,不可重复读对应的是修改,即UPDATE操作。但是可能还会有幻读问题。因为幻读问题对应的是插入INSERT操作,而不是UPDATE操作。

  • Serializable 序列化

Serializable 是最高的事务隔离级别,在该级别下,事务串行化顺序执行,可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下,比较耗数据库性能,一般不使用。

MySQL基础_第2张图片

3.2 默认事务隔离级别

  • Oracle 支持的 2 种事务隔离级别:Read committed, Serializable。 Oracle 默认的事务隔离级别为: Read committed
  • Mysql 支持 4 种事务隔离级别. Mysql 默认的事务隔离级别为:Repeatable read

3.3 实际操作

#查看当前事务隔离级别SELECT @@_tx——isoliation;#设置当前 mySQL 连接的隔离级别SET SESSION TRANSACTION ISOLATION LEVEL 【某个隔离级别】;#设置数据库系统的全局的隔离级别SET GLOBAL SESSION TRANSACTION ISOLATION LEVEL;

3.4 保存点

#语法SAVEPOINT 节点名;

演示如何使用savepoint;

SET AUTOCOMMIT = 0;START TRANSACTION;DELETE FROM account WHERE id = 25;#设置保存点SAVEPOINT a;DELETE FROM account WHERE id = 28;#回滚到a保存点ROLLBACK TO a;

五、视图

1、简单介绍

视图其实就是虚拟表,和普通表一样使用,MySQL5.1版本出现的新特性,是通过表动态生成的数据。

舞蹈班和普通班:其实没有舞蹈班,只有领导来了以后,才临时搭建的舞蹈班,领导走了以后自动解散,普通班不是这样的。

示例(没用视图之前):

#查询姓张的学生名和专业名SELECT stuname,majornameFROM stuname sINNER JOIN major mON s.`majorid` = m.`id`WHERE s.`stuname` LIKE '张%';

示例(有了视图):

#还是得先查询一遍,封装成视图v1CREATE VIEW v1ASSELECT stuname,majornameFROM stuname sINNER JOIN major mON s.`majorid` = m.`id`WHERE s.`stuname` LIKE '张%';#再次查询v1即可SELECT * FROM v1 WHERE stuname LIKE '张%';

2、视图的创建

#语法CREATE VIEW 视图名AS要查询的语句;

视图的好处:

  • 重用sql语句
  • 简化复杂的sql操作,不必知道它的查询细节
  • 保护数据,提高安全性

3、视图的修改

#方式一:CREATE OR REPLACE VIEW 视图名AS查询语句;
#方式二:ALTER VIEW 视图名AS查询语句;

4、视图的删除

DROP VIEW 视图名1,视图名2;

5、视图的查看

DESC 视图名;#这个也行,不过不推荐SHOW CREATE VIEW 视图名;

6、视图的更新

用于更改视图中的数据。其实和表的更新一样。

INSERT INTO 视图名 (...) VALUES (...);#其他也一样

不过有特殊情况:

  • 包含以下关键字的sql语句:分组函数、distinct、group by、having、union或者union all;
  • 常量视图;
  • Select中包含子查询;
  • join;
  • from一个不能更新的视图;
  • where子句的子查询引用了from子句中的表;
视图和表的区别
  • 一个是create view,一个是create table
  • 是否实际占用物理空间?视图只占用了一小部分,表占用了物理空间
  • 视图一般不能增删改(有限制),表可以增删改查

六、变量

1、变量的分类

  • 系统变量:

    • 全局变量
    • 会话变量
  • 自定义变量:

    • 用户变量
    • 局部变量

2、系统变量

由系统提供,不是用户定义,属于服务器层面的。

#查看所有的系统变量,什么都不写默认是SESSION,下面一样SHOW GLOBAL|【SESSION】 VARIABLES;#查看部分系统变量SHOW GLOBAL|【SESSION】 VARIABLES LIKE 查询条件;#查看某个系统变量SELECT @@GLOBAL|【SESSION】 系统变量名;#为某个系统变量赋值#方式一SET GLOBAL|【SESSION】系统变量名 = 值;#方式二SET @@GLOBAL|【SESSION】.系统变量名 = 值;

服务器每次启动都会为全局变量赋初始值,针对于所有的会话都有效,但不能跨重启,如果想真正重启也修改,就需要修改配置文件了。

会话变量的作用域针对于当前会话(连接),换一个连接就不一样了。

3、自定义变量

变量是自己定义的。

3.1 用户变量

作用域:针对于当前会话有效的,换一个连接就无效了,和Session的作用域一样。

使用在任何地方,begin end里面或外面都行。

#声明并初始化 用=或:=赋值SET @用户变量名=值;#另一种方式SET @用户变量名:=值;#另一种方式SELECT @用户变量名:=值;#更新用户变量的值#方式一:同上,既是声明又是更新赋值SET @用户变量名=值;#另一种方式SET @用户变量名:=值;#另一种方式SELECT @用户变量名:=值;#方式二:SELECT INTO来赋值SELECT 字段 INTO @变量名 FROM 表;#使用SELECT @用户变量名;

3.2 局部变量

作用域:仅仅在局部有效,也就是begin end里有效。

#声明DECLARE 变量名 类型;DECLARE 变量名 类型 DEFAULT 值;#赋值#方式一SET 局部变量名=值;#另一种方式SET 局部变量名:=值;#另一种方式SELECT @局部变量名:=值;#方式二:SELECT INTO来赋值SELECT 字段 INTO 变量名 FROM 表;#使用SELECT 局部变量名;

七、存储过程和函数

1、简单介绍

存储过程和函数类似Java的方法。

存储过程和函数定义:事先经过编译并存储在数据库中的一段sql语句的集合。

好处:

  • 简化应用开发人员的很多工作
  • 减少数据在数据库和应用服务器之间的传输
  • 提高了数据处理的效率

2、存储过程

2.1 创建存储过程

CREATE PROCEDURE 存储过程名(参数列表)BEGIN    存储过程体(一组SQL语句);END;

注意:

  • 参数列表包含三部分:参数模式、参数名、参数类型
#举例IN stuname VARCHAR(20);
  • 参数模式:

    • IN:该参数可以作为输入,也就是需要调用方传入值
    • OUT:该参数可以作为输出,也就是该参数可以作为返回值
    • INOUT:该参数既可以作为输入,又可以作为输出,既需要调用方传入值,又可以作为返回值
  • 如果存储过程体仅仅只有一句话,BEGIN END可以省略,存储过程体种的每条sql语句都需要加分号结尾,存储过程的结尾可以使用delimiter重新设置
#语法DELIMITER 结束标记#案例DELIMITER $

2.2 调用存储过程

#语法CALL 存储过程名(实参列表);

2.3 案例

有点难,案例记录一下!!

  • 普通类型参数的存储过程
#往admin表里插入五条记录#写存储过程DELIMITER $CREATE PROCEDURE myp1()BEGIN    INSERT INTO admin(username,`password`) VALUES         ('zhangsan1','001'),        ('zhangsan2','002'),        ('zhangsan3','003'),        ('zhangsan4','004'),        ('zhangsan5','005');END $#使用存储过程CALL myp1()$
  • 带in模式参数的存储过程
#根据女神名,查询对应的男神信息#写存储过程DELIMITER $CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))BEGIN     SELECT bo.*    FROM boys bo    RIGHT JOIN beauty b ON bo.id = b.boyfriend_id    WHERE b.name = beautyName;END $#使用存储过程CALL myp2('柳岩')$
#用户是否登录成功DELIMITER $CREATE PROCEDURE myp3(IN username VARCHAR(20),IN `password` VARCHAR(20))BEGIN    DECLARE result INT(20) DEFAULT '';#声明并初始化        SELECT count(*) INTO result #赋值    FROM admin    WHERE admin.username = username    AND admin.password = `password`;        SELECT IF(result>0,'成功','失败)';#判断是否成功END $#调用CALL myp3('张三','0001')$
  • 带out模式参数的存储过程
#根据女神名,查询对应的男神信息#写存储过程DELIMITER $CREATE PROCEDURE myp5(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))BEGIN     SELECT bo.boyName INTO boyName    FROM boys bo    RIGHT JOIN beauty b ON bo.id = b.boyfriend_id    WHERE b.name = beautyName;END $#使用存储过程SET @bName$CALL myp5('柳岩',@bName)$SELECT @bName$
  • 带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 $

2.4 删除存储过程

不能进行修改存储过程里面的内容,如果想修改,那就删掉原来的,再创建新的。

#语法DROP PROCEDURE 存储过程名;#一次只能删除一个

2.5 查看存储过程

#语法SHOW CREATE PROCEDURE 存储过程名;

3、函数

存储过程和函数的区别:

  • 函数只能有一个返回,存储过程可以又任意个,0个也行,多个也行
  • 增删改比较适合存储过程,查询一个值比较适合函数

3.1 创建函数

#创建语法CREATE FUNCTION 函数名(函数名) RETURNS 返回类型BEGIN    函数体END

参数列表包含两部分:参数名、参数类型;函数更接近于Java中的方法。

函数体:肯定有return语句,如果没有会报错,如果return语句没放在函数体最后也不报错,但是没任何意义。

如果存储过程体仅仅只有一句话,BEGIN END可以省略,存储过程的结尾可以使用delimiter

3.2 调用语法

SELECT 函数名(参数列表)

3.3 案例

  • 无参有返回的
#返回公司的员工个数#创建函数CREATE FUNCTION myf1() RETURNS INTBEGIN    DECLARE c INT DEFAULT 0;    SELECT COUNT(*) INTO c    FROM employees;    RETURN c;END $#调用函数SELECT myf1()$
  • 有参有返回的
#根据员工名返回它的工资CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLEBEGIN    SET @sal=0;#定义一个用户变量    SELECT salary INTO @sal#赋值    FROM employees    WHERE last_name = empName;        RETURN @sal;END $#调用函数SELECT myf2('k_ing');

3.3 查看函数

#语法SHOW CREATE FUNCTION 函数名;

3.4 删除函数

#语法DROP FUNCTION 函数名;

八、流程控制结构

1、结构分类

顺序结构:程序从上向下依次执行;

分支结构:程序可以从多条路径中选择一条去执行;

循环结构:程序在满足一定条件的基础上,重复执行一段代码;

2、分支结构

2.1 if函数

可以实现简单的双分支。可以应用在任何地方。

#语法IF(表达式1,表达式2,表达式3)#如果 表达式1成立,返回表达式2的值,否则返回表达式3的值(三目运算符)

2.2 case结构

情况1:类似于switch语句,一般用于等值判断

#语法CASE 变量|表达式|字段WHEN 要判断的值 THEN 返回的值1[或语句1;]WHEN 要判断的值 THEN 返回的值2[或语句2;]...ELSE 要返回的值n[或语句n;]END CASE;

情况2:类似于多重if,一般用于实现区间判断,看到底在哪个区间

#语法CASE WHEN 要判断的条件1 THEN 返回的值1[或语句1;]WHEN 要判断的条件2 THEN 返回的值2[或语句2;]...ELSE 要返回的值n[或语句N;]END CASE;

可作为表达式,嵌套在其他语句中使用,可以放在任何地方,begin end中或begin end的外面;也可以作为独立的语句中使用,只能放在begin end中。

2.3 if结构

实现多重分支。只能应用在begin end中。

#语法IF 条件1 THEN 语句1;ELSEIF 条件2 THEN 语句2;...【ELSE 语句n;】END IF;

3、循环结构

3.1 简单介绍

分类:while、loop、repeat。

循环控制:iterate类似于Java中的continue,结束当前循环,进行下一次循环;leave相当于Java中的break

3.2 while循环

#语法【标签:】WHILE 循环条件 DO     循环体;END WHILE 【标签】;

案例

#批量插入,插入到admin表多条记录CREATE PROCEDURE pro_while1(IN insertCount INT)BEGIN    DECLARE i INT DEFAULT 1;    a:WHILE i <= insertCount DO    INSERT INTO admin(username,`password`) VALUES(CONCAT('Jame'+i),'666');    SET i = i + 1;    END WHILE a;END $CALL pro_while1(100)$

含leave的语句

#批量插入,插入到admin表多条记录,插20条结束CREATE PROCEDURE pro_while1(IN insertCount INT)BEGIN    DECLARE i INT DEFAULT 1;    a:WHILE i <= insertCount DO        INSERT INTO admin(username,`password`) VALUES(CONCAT('Jame'+i),'666');        IF i>=20 THEN LEAVE a;        END IF;        SET i = i + 1;    END WHILE a;END $CALL pro_while1(100)$

3.3 loop循环

#语法【标签:】LOOP    循环体;END LOOP 【标签】;

可以用来模拟简单的死循环。

3.4 repeat循环

#语法【标签:】REPEAT 循环体; UNTIL 结束循环的条件END REPEAT【标签】;

你可能感兴趣的