记录生活中的点点滴滴

0%

MySQL基础

发表于 Valine：

记录一下 mysql 学习（复习）的过程，方便以后查阅。

MySQL的使用

启动和停止服务

方式一：图形化界面

win键 + R键，输入 services.msc 找到 mysql 的服务，手动启动或者关闭

方式二：命令行

1
2
net  start  MySQL服务名
net  stop  MySQL服务名

客户端登录

方式一：MySQL自带客户端

“开始菜单”–>MySQL–>MySQL Server 5.7 –> MySQL 5.7 Command Line Client

方式二：命令行

mysql -h 主机名 -P 端口号 -u 用户名 -p密码

例如：mysql -h localhost -P 3306 -u root -proot

注意：

  • -p 和密码之间不能加空格，其他参数名与参数值之间可以有空格也可以没有空格

  • 推荐直接 -p，先不输入密码，然后会提示你输入密码，这样密码就不会被看到了

  • 如果是连本机：-hlocalhost就可以省略，如果端口号没有修改：-P3306也可以省略

    简写成：mysql -uroot -p ，然后输入密码就登录进去了

SQL语句

SQL概述

SQL（Structured Query Language）是“结构化查询语言”，它是对关系型数据库的操作语言。它可以应用到所有关系型数据库中，例如：MySQL、Oracle、SQL Server等。

SQL要求：

  • SQL语句可以单行或多行书写，以分号结尾；

  • 可以用空格和缩进来来增强语句的可读性；

  • 关键字不区别大小写，建议使用大写；

分类

  • DDL（Data Definition Language）：数据定义语言，用来定义数据库对象：库、表、列等；

  • DML（Data Manipulation Language）：数据操作语言，用来定义数据库记录（数据）；

  • DCL（Data Control Language）：数据控制语言，用来定义访问权限和安全级别；

  • DQL（Data Query Language）：数据查询语言，用来查询记录（数据）。

DQL

DQL就是数据查询语言，数据库执行DQL语句不会对数据进行改变，而是让数据库发送结果集给客户端。

语法：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT 
	selection_list /*要查询的列名称*/
FROM 
	table_list /*要查询的表名称*/
WHERE 
	condition /*行条件*/
GROUP BY 
	grouping_columns /*对结果分组*/
HAVING 
	condition /*分组后的行条件*/
ORDER BY
	sorting_columns /*对结果分组*/
LIMIT 
	offset_start, row_count /*结果限定*/

为了下面我们的学习 ，我们先在mysql命令行下执行下面命令创建数据库和表。

  • 创建我们的数据库并进入

    1
    2
    CREATE database mydatabase;
    USE mydatabase;

  • 创建学生表 stu

字段名称 字段类型 说明
sid char(6) 学生学号
sname varchar(50) 学生姓名
age int 学生年龄
gender varchar(50) 学生性别 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE TABLE stu (
	sid	CHAR(6),
	sname		VARCHAR(50),
	age		INT,
	gender	VARCHAR(50)
);
INSERT INTO stu VALUES('S_1001', 'liuYi', 35, 'male');
INSERT INTO stu VALUES('S_1002', 'chenEr', 15, 'female');
INSERT INTO stu VALUES('S_1003', 'zhangSan', 95, 'male');
INSERT INTO stu VALUES('S_1004', 'liSi', 65, 'female');
INSERT INTO stu VALUES('S_1005', 'wangWu', 55, 'male');
INSERT INTO stu VALUES('S_1006', 'zhaoLiu', 75, 'female');
INSERT INTO stu VALUES('S_1007', 'sunQi', 25, 'male');
INSERT INTO stu VALUES('S_1008', 'zhouBa', 45, 'female');
INSERT INTO stu VALUES('S_1009', 'wuJiu', 85, 'male');
INSERT INTO stu VALUES('S_1010', 'zhengShi', 5, 'female');
INSERT INTO stu VALUES('S_1011', 'xxx', NULL, NULL);
  • 创建雇员表 emp
字段名称 字段类型 说明
empno int 员工编号
ename varchar(50) 员工姓名
job varchar(50) 员工工种
mgr int 领导编号
hiredate date 入职日期
sal decimal(7,2) 月薪
comm decimal(7,2) 奖金
deptno int 部门编号 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
CREATE TABLE emp(
	empno		INT,
	ename		VARCHAR(50),
	job		VARCHAR(50),
	mgr		INT,
	hiredate	DATE,
	sal		DECIMAL(7,2),
	comm		decimal(7,2),
	deptno		INT
) ;
INSERT INTO emp values(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp values(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp values(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp values(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp values(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp values(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp values(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO emp values(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp values(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp values(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO emp values(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp values(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp values(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);
  • 创建部门表 dept
字段名称 字段类型 说明
deptno int 部门编号
dname varchar(50) 部分名称
loc varchar(50) 部门所在地点 
1
2
3
4
5
6
7
8
9
CREATE TABLE dept(
	deptno		INT,
	dname		varchar(14),
	loc		varchar(13)
);
INSERT INTO dept values(10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept values(20, 'RESEARCH', 'DALLAS');
INSERT INTO dept values(30, 'SALES', 'CHICAGO');
INSERT INTO dept values(40, 'OPERATIONS', 'BOSTON');

OK，这些准备工作已经做好了，下面我们就开始学习DQL吧！

基础查询

  • 查询学生表中的所有列
1
select * from stu;
  • 查询指定列
1
select sname,age from stu;

条件查询

条件查询就是在查询时给出WHERE子句，在WHERE子句中可以使用如下运算符及关键字：

  • =、!=、<>、<、<=、>、>=；

  • AND、OR、NOT

  • BETWEEN…AND、IN(set)、IS NULL

案例

案例1：查询工资>2000的员工信息

1
select * from emp where sal > 2000;

案例2：查询 job不是’CLERK’的员工姓名ename、job、sal

1
select ename,job,sal from emp where job != 'CLERK';

案例3：查询工资>1000并且小于2000的员工姓名、工资、部门编号

1
select ename,sal,deptno from emp where sal > 1000 and sal < 2000;

案例4：查询部门编号不在10到20之间的并且工资>1500的员工信息

1
select ename,sal,deptno from emp where sal > 1500 and deptno not between 10 and 20;

模糊查询

like

当想查询姓名中包含a字母的学生时就需要使用模糊查询了。模糊查询需要使用关键字LIKE。

  • 最常用的通配符是百分号（%），它表示任何字符出现任意次数。

  • 另一个有用的通配符是下划线（_），用途与%一样，但下划线只匹配单个字符而不是多个字符。

#案例1：查询员工信息表中姓名第一个字符为a的员工信息

1
select * from emp where ename like 'a%';

#案例2：查询员工信息表中姓名第二个字符为a，第四个字符为e的员工信息

1
select * from emp where ename like '_a_e%';
between and

特点：

①用于筛选某个字段或表达式是否在指定的区间范围

②等价于 使用逻辑表达式的效果，只是语法上更加简单

③两个区间值不能调换顺序

两个区间值包含

>=左区间 and <=右区间

in

#案例1：查询工种编号是 CLERK 或是 MANAGER 的员工信息

1
select * from emp where job in ('clerk','manager');
is null

#案例1：查询哪个员工没有奖金

1
select * from emp where comm not is null;

#案例2：查询哪个员工有奖金

1
select * from emp where comm is not null;

字段控制查询

去除重复记录

去除重复记录（两行或两行以上记录中系列的上的数据都相同），例如emp表中sal字段就存在相同的记录。当只查询emp表的sal字段时，那么会出现重复记录，那么想去除重复记录，需要使用DISTINCT：

1
select distinct sal from emp ;
查看雇员的月薪与佣金之和(IFNULL函数)

因为sal和comm两列的类型都是数值类型，所以可以做加运算。如果sal或comm中有一个字段不是数值类型，那么会出错。

comm列有很多记录的值为NULL，因为任何东西与NULL相加结果还是NULL，所以结算结果可能会出现NULL。下面使用了把NULL转换成数值0的函数IFNULL：

1
select sal + ifnull(comm,0) from emp;
给列名添加别名

方式一：

select 列名 as 别名 from 表名;

1
select sal+ifnull(comm,0) as sum from emp;

方式二：

select 列名 别名 from 表名;

1
select sal+ifnull(comm,0) sum from emp;

排序 order by

1）按单个字段进行升序 ★

案例1：按工资从低到高排序

1
select * from emp order by sal;

案例2：按工资从高到低排序

1
elect * from emp order by sal desc;

2）按多个字段排序

案例1：部门编号>10的按工资从高到低排序，如果一样，再按ename升序

1
select * from emp where deptno > 10 order by sal desc,ename;

常用函数

文本处理函数

查询员工表中的所有员工的ename，但是我们要小写的ename

1
select LOWER(ename) from emp;

这就用到了 LOWER() 函数，常用的文本处理函数如下：

CHAR_LENGTH(s) 返回字符串s的字符数
LENGTH(s) 返回字符串s的字节数，和字符集有关
INSERT(str, index , len, instr) 将字符串str从第index位置开始，len个字符长的子串替换为字符串instr
UPPER(s) 或 UCASE(s) 将字符串s的所有字母转成大写字母
LOWER(s) 或LCASE(s) 将字符串s的所有字母转成小写字母
LEFT(s,n) 返回字符串s最左边的n个字符
RIGHT(s,n) 返回字符串s最右边的n个字符
LPAD(str, len, pad) 用字符串pad对str最左边进行填充，直到str的长度为len个字符
RPAD(str ,len, pad) 用字符串pad对str最右边进行填充，直到str的长度为len个字符
LTRIM(s) 去掉字符串s左侧的空格
RTRIM(s) 去掉字符串s右侧的空格
SUBSTRING(s,index,len) 返回从字符串s的index位置其len个字符
SOUNDEX(s) 返回串的SOUNDEX值

SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。有的时候我们要匹配所有发音类似于 Lie 的姓名 select * from emp where soundex(ename) = soundex('Lie');，比如有一个员工叫Lee，它们读音相同，那么就可以匹配上。

日期和时间处理函数
CURDATE() 或 CURRENT_DATE() 返回当前日期
CURTIME() 或 CURRENT_TIME() 返回当前时间
NOW() 返回当前系统日期时间
Date(date) 返回日期时间的日期部分
Time(date) 返回日期时间的时间部分
YEAR(date) MONTH(date) DAY(date) HOUR(time) MINUTE(time) SECOND(time) 返回具体的时间值
WEEK(date) WEEKOFYEAR(date) 返回一年中的第几周
DAYOFWEEK() 返回周几，注意：周日是1，周一是2，。。。周六是7
WEEKDAY(date) 返回周几，注意，周1是0，周2是1，。。。周日是6
DAYNAME(date) 返回星期：MONDAY,TUESDAY…..SUNDAY
MONTHNAME(date) 返回月份：January，。。。。。
DATEDIFF(date1,date2) TIMEDIFF(time1, time2) 返回date1 - date2的日期间隔 返回time1 - time2的时间间隔
DATE_ADD(datetime, INTERVALE expr type) 返回与给定日期时间相差INTERVAL时间段的日期时间
DATE_FORMAT(datetime ,fmt) 按照字符串fmt格式化日期datetime值
STR_TO_DATE(str, fmt) 按照字符串fmt对str进行解析，解析为一个日期
数值处理函数
ABS（x） 返回x的绝对值
CEIL（x） 返回大于x的最小整数值
FLOOR（x） 返回大于x的最大整数值
MOD(x,y) 返回x/y的模
RAND(x) 返回0~1的随机值
ROUND(x,y) 返回参数x的四舍五入的有y位的小数的值
TRUNCATE(x,y) 返回数字x截断为y位小数的结果
SQRT(x) 返回x的平方根
POW(x,y) 返回x的y次方

聚合函数

聚合函数是用来做纵向运算的函数：

  • COUNT()：统计指定列不为NULL的记录行数；

  • MAX()：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；

  • MIN()：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；

  • SUM()：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；

  • AVG()：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

COUNT： 当需要纵向统计时可以使用COUNT()。

1）查询emp表中记录数：

1
select count(*) from emp;

2）查询emp表中有佣金的人数：

注意，因为count()函数中给出的是comm列，那么只统计comm列非NULL的行数。

1
select count(comm) from emp;

3）查询emp表中月薪大于2500的人数：

1
select count(*) from emp where sal > 2500;

SUM和AVG：当需要纵向求和时使用SUM()函数。

1）查询所有雇员月薪和：

1
select sum(sal) from emp;

4）统计所有员工平均工资：

1
select avg(sal) from emp;

MAX和MIN

1）查询最高工资和最低工资：

1
select max(sal),min(sal) from emp;

分组查询

当需要分组查询时需要使用 GROUP BY 子句，例如查询每个部门的工资和，这说明要使用部分来分组。

MySQL还允许过滤分组，提供了 HAVING 子句。

它非常类似于 WHERE，WHERE过滤行，而HAVING过滤分组，或者可以这样理解：WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。

案例：

1）简单的分组

案例：查询每个工种的员工平均工资

1
select avg(sal) from emp group by job;

2）可以实现分组前的筛选

案例：查询姓名中包含a字符的 每个部门的最高工资

1
select max(sal) from emp  where ename like '%a%' group by job;

3）可以实现分组后的筛选

案例：查询哪个部门的员工个数>3

1
select job from emp group by job having count(job) > 3;

4）按多个字段分组

案例：查询每个工种每个部门的最低工资,并按最低工资降序

1
select job,deptno,min(sal) from emp group by job,deptno;

连接查询

我们的员工表和部门表，他们两个之间通过 deptno 部门编号能联系到一起，我想查到这两个表，根据相同的部门编号输出员工信息和部门信息。

1
select * from emp,dept;

这样会产生笛卡尔积，我们要通过 WHERE 设置条件来得到我们想要的结果。

那么多表查询产生这样的结果并不是我们想要的，那么怎么去除重复的，不想要的记录呢，当然是通过条件过滤。通常要查询的多个表之间都存在关联关系，那么就通过关联关系去除笛卡尔积。

上面我们的需求的代码如下，增加WHERE，让这两个表的部门编号相等来去除笛卡尔积：

1
select * from emp as e,dept as p where e.deptno = p.deptno;
内连接

上面的连接语句就是内连接，但它不是SQL标准中的查询方式，可以理解为方言！SQL标准的内连接为：

1
SELECT * FROM emp e INNER JOIN dept d ON e.deptno = d.deptno;

注意：inner可以省略、on是连接条件

内连接的特点：查询结果必须满足条件。

外连接（左连接、右连接）

外连接的特点：查询出的结果存在不满足条件的可能。

左连接：

1
SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.deptno=d.deptno;

注意：OUTER可以省略

左连接是先查询出左表（即以左表为主），然后查询右表，右表中满足条件的显示出来，不满足条件的显示NULL。

右连接

右连接就是先把右表中所有记录都查询出来，然后左表满足条件的显示，不满足显示NULL。例如在dept表中的40部门并不存在员工，但在右连接中，如果dept表为右表，那么还是会查出40部门，但相应的员工信息为NULL。

1
SELECT * FROM emp e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno;
自然连接（不常用）

大家也都知道，连接查询会产生无用笛卡尔积，我们通常使用主外键关系等式来去除它。而自然连接无需你去给出主外键等式，它会自动找到这一等式：

两张连接的表中名称和类型完成一致的列作为条件，例如emp和dept表都存在deptno列，并且类型一致，所以会被自然连接找到！

当然自然连接还有其他的查找条件的方式，但其他方式都可能存在问题！

1
2
3
SELECT * FROM emp NATURAL JOIN dept;
SELECT * FROM emp NATURAL LEFT JOIN dept;
SELECT * FROM emp NATURAL RIGHT JOIN dept;

子查询

概念：

出现在其他语句的内部的select语句，称为子查询或内查询

里面嵌套其他select语句的查询语句，称为主查询或外查询

子查询不一定必须出现在select语句内部，只是出现在select语句内部的时候较多！

实例：

1
select ename from emp where sal >= (select sal from emp where ename = 'SCOTT');

说明：

​ 1、子查询语句需要放在小括号内，提高代码的阅读性

​ 2、子查询先于主查询执行，一般来讲，主查询会用到子查询的结果

​ 3、如果子查询放在条件中，一般来讲，子查询需要放在条件的右侧

​ 示例：where job_id>(子查询)

​ 不能写成：where (子查询)<job_id

​ 4、单行子查询对应的使用单行操作符：> < >= <= = <>

​ 多行子查询对应的使用多行操作符:in 、any 、all 、not in

LIMIT用来限定查询结果的起始行，以及总行数。

分页查询

语法：

1
2
3
4
select 查询列表
from 表
【where 条件】
limit 【起始条目索引,】查询的条目数;

例1：查询 emp 表前五条员工信息

1
select * from emp limit 5;

例2：查询 emp 表第6-10条员工信息

1
select * from emp limit 5,5;

DDL

基本操作

  • 查看所有数据库名称：SHOW DATABASES;

  • 切换数据库：USE mydb1，切换到 mydb1 数据库；

操作数据库

  • 创建数据库：CREATE DATABASE [IF NOT EXISTS] mydb1;

    创建数据库，例如：CREATE DATABASE mydb1;，创建一个名为 mydb1 的数据库。如果这个数据已经存在，那么会报错。例如 CREATE DATABASE IF NOT EXISTS mydb1;，在名为 mydb1 的数据库不存在时创建该库，这样可以避免报错。

  • 删除数据库：DROP DATABASE [IF EXISTS] mydb1;

    删除数据库，例如：DROP DATABASE mydb1;，删除名为 mydb1 的数据库。如果这个数据库不存在，那么会报错。DROP DATABASE IF EXISTS mydb1;，就算 mydb1 不存在，也不会的报错。

数据类型

MySQL 与 Java 一样，也有数据类型。MySQL中数据类型主要应用在列上。

常用类型：

  • int：整型

  • double/float：浮点型，例如double(5,2)表示最多5位，其中必须有2位小数，即最大值为999.99；

  • decimal：浮点型，在表示钱方面使用该类型，因为不会出现精度缺失问题；

  • char：固定长度字符串类型；char(4) 范围是0-255

  • varchar：可变长度字符串类型；

  • text：字符串类型；表示存储较长文本

意思 格式 n的解释 特点 效率
Char 固定长度字符 Char(n) 最大的字符个数，可选 默认：1 不管实际存储，开辟的空间都是n个字符 高
Varchar 可变长度字符 Varchar(n) 最大的字符个数，必选 根据实际存储决定开辟的空间 低

  • blob：字节类型；//jpg mp3 avi

  • date：日期类型，格式为：yyyy-MM-dd；

  • time：时间类型，格式为：hh:mm:ss

  • timestamp/datetime：时间戳类型；日期+时间 yyyyMMdd hhmmss

保存范围 所占字节
Datetime 1900-1-1~xxxx年 8
Timestamp 1970-1-1~2038-12-31 4

操作表

  • 创建表：
1
2
3
4
5
CREATE TABLE 表名(
 字段名 字段类型 【字段约束】,
 字段名 字段类型 【字段约束】,
 ......
);

例如：

1
2
3
4
5
6
create table stus(
	sid char(6),
    sname varchar(20),
    age int,
    gender varchar(10)
);
  • 查看当前数据库中所有表名称：
1
SHOW TABLES;
  • 查看指定表的创建语句：
1
SHOW CREATE TABLE  stus;
  • 查看表结构：
1
DESC stus;
  • 删除表：
1
DROP TABLE stus;

  • 修改表结构

    • 添加列：给stus表添加classname列

      1
      alter table stus add (classname varchar(100));

    • 修改列类型：修改stus表的gender列类型为CHAR(2)

      1
      alter table stus modify char(2);

    • 修改列名：修改stus表的gender列名为sex

      1
      alter table stus change gender sex char(2);

    • 删除列：删除stus表的classname列

      1
      alter table stus drop classname;

    • 修改表名称：修改stus表名称为student

      1
      alter table stus rename to student;

DML

插入数据

语法：

1
INSERT INTO 表名(列名1,列名2, …) VALUES(值1, 值2)

例如：

1
insert into student(sid,sname,age,sex) values('1001','张三',20,'男');

语法：

1
INSERT INTO 表名 VALUES(值1,值2,…)

因为没有指定要插入的列，表示按创建表时列的顺序插入所有列的值：

注意：所有字符串数据必须使用单引号

例如：

1
insert into student values('1002','李四',30,'女');

修改数据

单表修改语法：

1
UPDATE 表名 SET 列名1=值1, … 列名n=值n [WHERE 条件]

例如：

1
2
update student set sname = '王五' where sid = '1001';
update student set age = age + 1 where sname = '李四';

多表修改语法：

1
UPDATE 表1 【inner】 john 表2 on 表 SET 列名1= 新值1,列名2 =新值2 【where 筛选条件】

删除数据

单表删除语法：

语法一：

1
DELETE FROM 表名 [WHERE 条件]

例如：

1
2
delete from student where sid = '1001';
delete from student;

语法二：

1
TRUNCATE TABLE 表名

例如：

1
truncate table student;

  • 虽然 TRUNCATE 和 DELETE 都可以删除表的所有记录，但有原理不同。DELETE 的效率没有 TRUNCATE 高！

  • TRUNCATE 其实属性DDL语句，因为它是先 DROP TABLE ，再 CREATE TABLE 。而且 TRUNCATE 删除的记录是无法回滚的，但 DELETE 删除的记录是可以回滚的。

多表删除语法：

1
DELETE FROM 表1 别名1 INNER JOIN 表2 别名2 on 连接条件 【AND 筛选条件】

完整性约束

完整性约束是为了表的数据的正确性！如果数据不正确，那么一开始就不能添加到表中。

主键

当某一列添加了主键约束后，那么这一列的数据就不能重复出现。这样每行记录中其主键列的值就是这一行的唯一标识。例如学生的学号可以用来做唯一标识，而学生的姓名是不能做唯一标识的，因为学生有可能同名。

主键列的值不能为NULL，也不能重复！

指定主键约束使用 PRIMARY KEY 关键字。

  • 创建表：定义时指定主键

    1
    2
    3
    4
    5
    6
    CREATE TABLE stu(
    		sid	    CHAR(6) PRIMARY KEY,
    		sname	VARCHAR(20),
    		age		INT,
    		gender	VARCHAR(10) 
    );

  • 创建表：定义列之后独立指定主键

    1
    2
    3
    4
    5
    6
    7
    CREATE TABLE stu(
    		sid	    CHAR(6),
    		sname	VARCHAR(20),
    		age		INT,
    		gender	VARCHAR(10),
    		PRIMARY KEY(sid)
    );

  • 修改表时指定主键

    1
    ALTER TABLE stu ADD PRIMARY KEY(sid);

  • 删除主键（只是删除主键约束，而不会删除主键列）

    1
    ALTER TABLE stu DROP PRIMARY KEY;

主键自增长

MySQL提供了主键自动增长的功能！这样用户就不用再为是否有主键是否重复而烦恼了。当主键设置为自动增长后，在没有给出主键值时，主键的值会自动生成，而且是最大主键值+1，也就不会出现重复主键的可能了。

  • 创建表时设置主键自增长（主键必须是整型才可以自增长）

    1
    2
    3
    4
    5
    6
    CREATE TABLE stu(
    		sid INT PRIMARY KEY AUTO_INCREMENT,
    		sname	VARCHAR(20),
    		age		INT,
    		gender	VARCHAR(10)
    );

  • 修改表时设置主键自增长

    1
    ALTER TABLE stu CHANGE sid sid INT AUTO_INCREMENT;

  • 修改表时删除主键自增长

    1
    ALTER TABLE stu CHANGE sid sid INT;

非空

指定非空约束的列不能没有值，也就是说在插入记录时，对添加了非空约束的列一定要给值；在修改记录时，不能把非空列的值设置为NULL。

1
2
3
4
5
6
CREATE TABLE stu(
		sid INT PRIMARY KEY AUTO_INCREMENT,
		sname VARCHAR(10) NOT NULL,
		age		INT,
		gender	VARCHAR(10)
);

唯一

还可以为字段指定唯一约束！当为字段指定唯一约束后，那么字段的值必须是唯一的。这一点与主键相似！例如给stu表的sname字段指定唯一约束。

1
2
3
4
CREATE TABLE tab_ab(
	sid INT PRIMARY KEY AUTO_INCREMENT,
	sname VARCHAR(10) UNIQUE
);

外键

主外键是构成表与表关联的唯一途径！

外键是另一张表的主键！例如员工表与部门表之间就存在关联关系，其中员工表中的部门编号字段就是外键，是相对部门表的外键。

  • 创建dept表，指定deptno为主键列：

    1
    2
    3
    4
    5
    CREATE TABLE dept(
    	deptno		INT PRIMARY KEY,
    	dname		varchar(14),
    	loc		varchar(13)
    );

  • 创建t_section表，指定sid为主键列，u_id为相对t_user表的uid列的外键：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    CREATE TABLE emp(
    	empno		INT,
    	ename		VARCHAR(50),
    	job		VARCHAR(50),
    	mgr		INT,
    	hiredate	DATE,
    	sal		DECIMAL(7,2),
    	comm		decimal(7,2),
    	deptno		INT,
    	CONSTRAINT fk_e_d FOREIGN KEY(deptno) REFERENCES dept(deptno)
    );

表与表之间的关系

  • 一对一：例如 t_person 表和 t_card 表，即人和身份证。这种情况需要找出主从关系，即谁是主表，谁是从表。人可以没有身份证，但身份证必须要有人才行，所以人是主表，而身份证是从表。设计从表可以有两种方案：

    • 在 t_card 表中添加外键列（相对 t_user 表），并且给外键添加唯一约束；
    • 给 t_card 表的主键添加外键约束（相对 t_user 表），即 t_card 表的主键也是外键。

  • 一对多（多对一）：最为常见的就是一对多！一对多和多对一，这是从哪个角度去看得出来的。t_user 和 t_section 的关系，从 t_user 来看就是一对多，而从 t_section 的角度来看就是多对一！这种情况都是在多方创建外键！

  • 多对多：例如 t_stu 和 t_teacher 表，即一个学生可以有多个老师，而一个老师也可以有多个学生。这种情况通常需要创建中间表来处理多对多关系。例如再创建一张表 t_stu_tea 表，给出两个外键，一个相对 t_stu 表的外键，另一个相对 t_teacher 表的外键。

事务

事务属于 TCL 控制语言（Transaction Control Language ）。

事务概述

什么是事务？为什么要用事务？

一个事务是由一条或者多条 sql 语句构成，这一条或者多条 sql 语句要么全部执行成功，要么全部执行失败！

默认情况下，每条单独的 sql 语句就是一个单独的事务！

举例：

银行转账！张三转10000块到李四的账户，这其实需要两条SQL语句：

  • 给张三的账户减去10000元；

  • 给李四的账户加上10000元。

事务中的多个操作，要么完全成功，要么完全失败！不可能存在成功一半的情况！

事务的四大特性（ACID）

  • 原子性（Atomicity）：事务中所有操作是不可再分割的原子单位。事务中所有操作要么全部执行成功，要么全部执行失败。

  • 一致性（Consistency）：事务执行后，数据库状态与其它业务规则保持一致。如转账业务，无论事务执行成功与否，参与转账的两个账号余额之和应该是不变的。

  • 隔离性（Isolation）：隔离性是指在并发操作中，不同事务之间应该隔离开来，使每个并发中的事务不会相互干扰。

  • 持久性（Durability）：一旦事务提交成功，事务中所有的数据操作都必须被持久化到数据库中，即使提交事务后，数据库马上崩溃，在数据库重启时，也必须能保证通过某种机制恢复数据。

MySQL中的事务

在默认情况下，MySQL每执行一条SQL语句，都是一个单独的事务。如果需要在一个事务中包含多条SQL语句，那么需要开启事务和结束事务。

  • 开启事务：start transaction；

  • 结束事务：commit或rollback。

在执行SQL语句之前，先执行 strat transaction，这就开启了一个事务（事务的起点），然后可以去执行多条SQL语句，最后要结束事务，commit表示提交，即事务中的多条SQL语句所做出的影响会持久化到数据库中。或者rollback，表示回滚，即回滚到事务的起点，之前做的所有操作都被撤消了！

演示事务：

事先要准备的表和数据库内容：

1
2
3
4
5
6
7
8
9
10
11
12
13
# 创建表
CREATE TABLE account(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(30),
	balance NUMERIC(10.2)
);

-- 插入数据
INSERT INTO account(NAME,balance) VALUES('张三', 1000);
INSERT INTO account(NAME,balance) VALUES('李四', 1000);

-- 查看表
SELECT * FROM account;

  • 演示事务执行失败（他们的余额仍还为1000）

    • 案例一：回滚事务 执行失败！

      1
      2
      3
      4
      START TRANSACTION;
      UPDATE account SET balance = balance - 100 WHERE id=1;
      UPDATE account SET balance = balance + 100 WHERE id=2;
      ROLLBACK;

    • 案例二：退出mysql客户端 事务执行失败！

      1
      2
      3
      START TRANSACTION;
      UPDATE account SET balance = balance - 100 WHERE id=1;
      UPDATE account SET balance = balance + 100 WHERE id=2;

  • 演示事务执行成功（之后张三为900，李四为1100）

    1
    2
    3
    4
    START TRANSACTION;
    UPDATE account SET balance = balance - 100 WHERE id=1;
    UPDATE account SET balance = balance + 100 WHERE id=2;
    COMMIT;