记录生活中的点点滴滴

0%

MySQL基础

记录一下 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
UPDATE1inner】 john 表2 onSET 列名1= 新值1,列名2 =新值2where 筛选条件】

删除数据

单表删除语法:

语法一:

1
DELETE FROM 表名 [WHERE 条件]

例如:

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

语法二:

1
TRUNCATE TABLE 表名

例如:

1
truncate table student;
  • 虽然 TRUNCATEDELETE 都可以删除表的所有记录,但有原理不同。DELETE 的效率没有 TRUNCATE 高!

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

多表删除语法:

1
DELETE FROM1 别名1 INNER JOIN2 别名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

  • 结束事务:commitrollback

在执行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;