MySQL学习

MySQL的连接

启动服务

通过管理员身份运行

格式:net start 服务名(启动服务)

1
2
3
net start mysql57

在windows服务中找到MYSQL57将其打开

停止服务

net stop 服务名(停止服务)

1
2
3
net stop mysql57

在windows服务中找到MYSQL57将其关闭

连接数据库

mysql -u 用户名 -p

1
mysql -u root -p

断开连接

1
exit 或 ctrl+C

查看服务器的版本

  • 方式一:登录到mysql服务端

    1
    select version();
  • 方式二:没有登录到mysql服务端

    1
    mysql --version 或 mysql --V

显示时间-登录到MySQL服务端之后

1
select now();

远程连接

1
mysql -h IP地址 -u 用户名 -p 对方mysql密码

MySQL数据类型和基本操作

数据类型

二进制类型

bit

1
2
bit[(M)]
二进制位(101001),m表示二进制位的长度(1-64),默认m=1
整数类型

tinyint

1
2
3
4
5
6
7
tinyint[(m)] [unsigned] [zerofill]

小整数,数据类型用于保存一些范围的整数数值范围:
有符号:-128 ~ 127.
无符号:255

特别的: MySQL中无布尔值,使用tinyint(1)构造。

int

1
2
3
4
5
int[(m)][unsigned][zerofill]

整数,数据类型用于保存一些范围的整数数值范围:
有符号: -2147483648 ~ 2147483647
无符号:4294967295

bigint

1
2
3
4
5
6
7
8
9
10
11
bigint[(m)][unsigned][zerofill]

大整数,数据类型用于保存一些范围的整数数值范围:
有符号:-9223372036854775808 ~ 9223372036854775807
无符号:18446744073709551615

作用:存储年龄,等级,id,各种号码等

注意:为该类型指定宽度时,仅仅只是指定查询结果的显示宽度,与存储范围无关,所以我们使用默认的就可以了
有符号和无符号的最大数字需要的显示宽度均为10,
而针对有符号的最小值则需要11位才能显示完全,所以int类型默认的显示宽度为11是非常合理的
小数型

decimal

1
2
3
4
5
6
decimal[(m[,d])] [unsigned] [zerofill]

准确的小数值,m是数字总个数(负号不算),d是小数点后个数。 m最大值为65,d最大值为30。

特别的:对于精确数值计算时需要用此类型
decaimal能够存储精确值的原因在于其内部按照字符串存储。

FLOAT

1
2
3
4
5
6
7
FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]

单精度浮点数(非准确小数值),m是数字总个数,d是小数点后个数。
有符号:(-3.402823466E+38 to -1.175494351E-38),0,(1.175494351E-38 to 3.402823466E+38)
无符号:0,(1.175 494 351 E-38,3.402 823 466 E+38)

数值越大,越不准确

DOUBLE

1
2
3
4
5
6
7
8
DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]

双精度浮点数(非准确小数值),m是数字总个数,d是小数点后个数。
有符号:(-1.797 693 134 862 315 7 E+308,-2.225 073 858 507 201 4 E-308),0,
(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308)
无符号:0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308)

数值越大,越不准确

作用:存储薪资、身高、体重、体质参数等

字符型

char

1
2
3
4
5
6
7
char (m)

char数据类型用于表示固定长度的字符串,可以包含最多达255个字符。其中m代表字符串的长度。

PS: 即使数据小于m长度,也会占用m长度,但是在查询时,查出的结果会自动删除尾部的空格

特点:定长,简单粗暴,浪费空间,存取速度快

varchar

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
varchar(m)

varchar 数据类型用于变长的字符串,可以包含最多达65535个字符(理论上可以,但是实际上在超出21845长度后,mysql会自动帮您转换数据类型为文本类型)。
其中m代表该数据类型所允许保存的字符串的最大长度,只要长度小于该最大值的字符串都可以被保存在该数据类型中。

PS: varchar类型存储数据的真实内容,例如:如果'ab ',尾部的空格也会被存起来
强调:varchar类型会在真实数据前加1-2Bytes的前缀,该前缀用来表示真实数据的bytes字节数(1-2Bytes最大表示65535个数字,正好符合mysql对row的最大字节限制,即已经足够使用)
如果真实的数据<255bytes则需要1Bytes的前缀(1Bytes=8bit 2**8最大表示的数字为255)
如果真实的数据>255bytes则需要2Bytes的前缀(2Bytes=16bit 2**16最大表示的数字为65535)
特点:变长,精准,节省空间,存取速度慢

sql优化:创建表时,定长的类型往前放,变长的往后放:比如性别, 比如地址或描述信息

PS:虽然varchar使用起来较为灵活,但是从整个系统的性能角度来说,char数据类型的处理速度更快,有时甚至可以超出varchar处理速度的50%。
因此,用户在设计数据库时应当综合考虑各方面的因素,以求达到最佳的平衡

text

1
2
3
text

text数据类型用于保存变长的大字符串,可以最多到65535 (2**16 − 1)个字符。
枚举类型(了解)

enum

1
2
3
4
5
6
7
8
9
10
11
12
enum

An ENUM column can have a maximum of 65,535 distinct elements. (The practical limit is less than 3000.)

示例:
CREATE TABLE user (
name VARCHAR(40),
sex ENUM('男', '女', '未知')
);
INSERT INTO user (name, sex) VALUES ('人1','男'), ('人2','女'),
('人3','未知'),('人4','人妖');
PS:人4会插入成功吗??
集合类型(了解)

set

1
2
3
4
5
6
7
set

A SET column can have a maximum of 64 distinct members.
示例:
CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));

INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
日期/时间类型
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
DATE 日期值
YYYY-MM-DD(1000-01-01/9999-12-31)

TIME 时间值或持续时间
HH:MM:SS('-838:59:59'/'838:59:59')

YEAR 年份值
YYYY(1901/2155)

DATETIME 混合日期和时间值(占用8字节)
YYYY-MM-DD HH:MM:SS(1000-01-01 00:00:00/9999-12-31 23:59:59)

TIMESTAMP 时间戳(占用4字节)
YYYYMMDD HHMMSS(1970-01-01 00:00:00/2037 年某时)

作用:存储用户注册时间,文章发布时间,员工入职时间,出生时间,过期时间等数据类型

基本操作

创建数据库

create database <数据库名称> charset=utf8;

1
create database skx charset=utf8;
删除数据库

drop database <数据库名称>;

1
drop database skx;
切换数据库

use <数据库名称>;

1
use zykj;
查看当前选择的数据库
1
select database();

MySQL之表操作

什么是表

表(TABLE) 是一种结构化的文件,可用来存储某种特定类型的数据。表中的一条记录有对应的标题,标题称之为表的字段。

表

查看当前数据库中所有表

1
格式:show tables;

创建表

格式:
create table 表名( 字段名1 类型[(宽度) 约束条件], 字段名2 类型[(宽度) 约束条件], 字段名3 类型[(宽度) 约束条件] )engine=innodb default charset utf8;

示例:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
create table student(
id int auto_increment primary key,
name varchar(20) not null,
age int not null,
gender bit default 1,
address varchar(20),
isDelete bit default 0
);

create table info1(
id int not null auto_increment primary key,
name varchar(50) not null,
sex char(2) not null
)

注意:

  • not null :表示此列不能为空
  • auto_increment :表示自增长,默认每次增长+1
  • 自增长只能添加在主键或者唯一索引字段上
  • primary key :表示主键(唯一且不为空)
  • engine = innodb :表示指定当前表的存储引擎
  • default charset utf8 :设置表的默认编码集

删除表

drop table 表名;

1
drop table student; 

查看表结构

desc 表名;

1
desc student;

查看建表语句

show create table 表名;

1
show create table student;

重命名表名

netame table <变更前表名> to <变更后表名>

1
netame table car to newCar;

修改表结构

alter table 表名 add|change|drop 列名 类型;

1
2
alter table newCar add isDelete bit default 0;
alter info1 alter sex default '男';

复制表

create table 新表名 select * from 原表名

1
create table newinfo select * from oldinfo;

ps:主键自增/索引/触发器/外键 不会 被复制

  • 选择性插入

    1
    create table newinfo select * from oldinfo where id=1;
  • 复制表结构

    1
    create table newinfo like oldinfo;
  • ps: 数据/触发器/外键 不会被复制

  • create table newinfo select * from oldinfo where 1!=1;

数据操作(增 删 改 查)

增 insert

全列插入

insert into 表名 values(...);

主键列是自动增长,但是全列插入时需要占位,通常使用0,插入成功一行以实际数据为准

1
insert into student values(0,"tom",19,1,"北京",0);
缺省插入

insert into 表名(列1,列2,...) values(值1,值2,...)

1
2
insert into student(name,age,address) values("lilei",19,"上海");
insert into(id,name) values(2,'张三');
同时插入多条数据

insert into 表名 values(...),(...),...;

1
2
3
4
5
insert into student values(0,"hanmeimei",18,0,"北京",0),(0,"poi",22,1,"海南",0),(0,"wenli",20,0,"石家庄",0);

insert into(id,name) values(2,'哈哈'),(3,'呵呵'),(4,'哦哦');

insert into(name,sex) select 'name',sex from info;

删 delete

delete from 表名 where 条件;

1
delete from student where id=4;

注意:

  • delete from student; – 删除整张表中的所有数据(一条一条数据的删除)
  • truncate student; – 清空整张表(整个删,效率高)

truncate和delete的区别?[面试题]

  1. TRUNCATE 在各种表上无论是大的还是小的都非常快。而DELETE 操作会被表中数据量的大小影响其执行效率.
  2. TRUNCATE是一个DDL语言而DELETE是DML语句,向其他所有的DDL语言一样,他将被隐式提交,不能对TRUNCATE使用ROLLBACK命令。
  3. TRUNCATE不能触发触发器,DELETE会触发触发器。
  4. 当表被清空后表和表的索引和自增主键将重新设置成初始大小,而delete则不能。

改 update

update 表名 set 列1=值1,列2=值2,......where 条件;

1
update student set age=16 where id = 7;

注意:如果没有条件是全部列都修改

查 select

1
2
3
说明:查询表名中的全部数据
格式:select * from 表名;
示例:select * from student;

MySQL之单表查询

简单查询
  • 查询语法

    select [distinct]*(所有)|字段名,...字段名 from 表名;

  • 查询所有字段信息

    1
    select * from person;
  • 查询指定字段信息

    1
    select id,name,age,sex,salary from person;
  • 别名查询,使用的as关键字,as可以省略的

    1
    select name,age as'年龄',salary '工资' from person;
  • 直接对列进行运算,查询出所有人工资,并每人增加100块

    1
    2
    select (5/2);
    select name, salary+100 from person;
  • 剔除重复查询 distinct

    1
    select distinct age from person;
条件查询

使用 WHERE 关键字 对简单查询的结果集 进行过滤

  1. 比较运算符: > < >= <= = <> (!=)

  2. null 关键字: is null ,not null

  3. 逻辑运算符: 与 andor (多个条件时,需要使用逻辑运算符进行连接)

  • 查询格式

    select [distinct]*(所有)|字段名,...字段名 from 表名 [where 条件过滤]

  • 比较运算符: > < >= <= = <> (!=) , is null 是否为null

    1
    2
    3
    4
    select * from person where age = 23;
    select * from person where age <> 23;
    select * from person where age is null;
    select * from person where age is not null;
  • 逻辑运算符: 与 andor

1
2
select * from person where age = 23 and salary = 29000;
select * from person where age = 23 or salary = 29000;
区间查询

关键字 between 10 and 20 :表示 获得10 到 20 区间的内容

  • 使用 between…and 进行区间 查询

    1
    select * from person where salary between 4000 and 8000;
  • between…and 前后包含所指定的值
    等价于 select * from person where salary >= 4000 and salary <= 8000;

集合查询

关键字: in, not null

  • 使用 in 集合(多个字段)查询

    1
    2
    select * from person where age in(23,32,18);
    等价于: select * from person where age =23 or age = 32 or age =18;
  • 使用 in 集合 排除指定值查询

    1
    select * from person where age not in(23,32,18);
模糊查询

关键字 like , not like

  • %: 任意多个字符
  • _ : 只能是单个字符

  • 模糊查询 like % : 任意多个字符, _ :单个字符

  • 示例

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    #查询姓名以"张"字开头的
    select * from person where name like '张%';

    #查询姓名以"张"字结尾的
    select * from person where name like '%张';

    #查询姓名中含有"张"字的
    select * from person where name like '%张%';

    #查询 name 名称 是四个字符的人
    select * from person where name like '____';

    #查询 name 名称 的第二个字符是 'l'的人
    select * from person where name like '_l%';

    #排除名字带 a 的学生
    select * from student where name not like 'a%'
排序查询

ORDER BY 字段1 DESC, 字段2 ASC

  • 排序查询格式

    1
    2
    3
    4
    5
    select 字段|* from 表名 [where 条件过滤] [order by 字段[ASC][DESC]]

    升序:ASC 默认为升序
    降序:DESC
    PS:排序order by 要写在select语句末尾
  • 示例

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    #按人员工资正序排列,注意:此处可以省略 ASC关键字
    select * from person order by salary ASC;
    select * from person order by salary;

    #工资大于5000的人,按工资倒序排列
    select * from person where salary >5000 order by salary DESC;

    #按中文排序
    select * from person order by name;

    #强制中文排序
    select * from person order by CONVERT(name USING gbk);
    ps:UTF8 默认校对集是 utf8_general_ci , 它不是按照中文来的。你需要强制让MySQL按中文来排序
聚合函数
  • 聚合: 将分散的聚集到一起.

  • 聚合函数: 对列进行操作,返回的结果是一个单一的值,除了 COUNT 以外,都会忽略空值

    1
    2
    3
    4
    5
    COUNT:统计指定列不为NULL的记录行数;
    SUM:计算指定列的数值和,如果指定列类型不是数值类型,那么计算结果为0;
    MAX:计算指定列的最大值,如果指定列是字符串类型,那么使用字符串排序运算;
    MIN:计算指定列的最小值,如果指定列是字符串类型,那么使用字符串排序运算;
    AVG:计算指定列的平均值,如果指定列类型不是数值类型,那么计算结果为0;
  • 格式

    1
    select 聚合函数(字段) from 表名;
  • 示例

    1
    2
    #统计人员中最大年龄、最小年龄,平均年龄分别是多少
    select max(age),min(age),avg(age) from person;
分组查询

分组的含义: 将一些具有相同特征的数据 进行归类.比如:性别,部门,岗位等等

怎么区分什么时候需要分组呢?

套路: 遇到 “每” 字,一般需要进行分组操作
例如:

  1. 公司每个部门有多少人
  2. 公司中有 多少男员工 和 多少女员工.
  • 分组查询格式

    1
    2
    select 被分组的字段 from 表名 group by 分组字段 [having 条件字段]
    ps: 分组查询可以与 聚合函数 组合使用
  • 示例

    1
    2
    3
    4
    5
    6
    7
    8
    9
    #查询每个部门的平均薪资
    select avg(salary),dept from person GROUP BY dept;

    #查询每个部门的平均薪资 并且看看这个部门的员工都有谁?
    select avg(salary),dept,GROUP_CONCAT(name) from person GROUP BY dept;
    #GROUP_CONCAT(expr):按照分组,将expr字符串按逗号分隔,组合起来

    #查询平均薪资大于10000的部门, 并且看看这个部门的员工都有谁?
    select avg(salary),dept,GROUP_CONCAT(name) from person GROUP BY dept; having avg(salary)>10000;

where 与 having区别:
执行优先级从高到低:where > group by > having

  1. Where 发生在分组group by之前,因而Where中可以有任意字段,但是绝对不能使用聚合函数。
  2. Having发生在分组group by之后,因而Having中可以使用分组的字段,无法直接取到其他字段,可以使用聚合函数
分页查询

好处:限制查询数据条数,提高查询效率

limit (起始条数),(查询多少条数);

1
2
3
4
5
6
7
8
#查询前5条数据
select * from person limit 5;

#查询第5条到第10条数据
select * from person limit 5,5;

#查询第10条到第15条数据
select * from person limit 10,5;
联合查询

union 联合、合并

语法:

1
2
3
4
5
select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】
select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】
select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】
.....
select 字段|常量|表达式|函数 【from 表】 【where 条件】

特点:

  1. 多条查询语句的查询的列数必须是一致的
  2. 多条查询语句的查询的列的类型几乎相同
  3. union代表去重,union all代表不去重
正则表达式

MySQL中使用 REGEXP 操作符来进行正则表达式匹配。

模式描述
^匹配输入字符串的开始位置。
$匹配输入字符串的结束位置。
.匹配任何字符(包括回车和新行)
[…]字符集合。匹配所包含的任意一个字符。例如, ‘[abc]’ 可以匹配 “plain” 中的 ‘a’。
[^…]负值字符集合。匹配未包含的任意字符。例如, ‘[^abc]’ 可以匹配 “plain” 中的’p’。
p1|p2|p3匹配 p1 或 p2 或 p3。例如,’z|food’ 能匹配 “z” 或 “food”。’(z|f)ood’ 则匹配 “zood” 或 “food”。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
## ^  匹配 name 名称 以 "e" 开头的数据
select * from person where name REGEXP '^e';

## $ 匹配 name 名称 以 "n" 结尾的数据
select * from person where name REGEXP 'n$';

## . 匹配 name 名称 第二位后包含"x"的人员 "."表示任意字符
select * from person where name REGEXP '.x';

## [abci] 匹配 name 名称中含有指定集合内容的人员
select * from person where name REGEXP '[abci]';

## [^alex] 匹配 不符合集合中条件的内容 , ^表示取反
select * from person where name REGEXP '[^alex]';

#注意1:^只有在[]内才是取反的意思,在别的地方都是表示开始处匹配
#注意2 : 简单理解 name REGEXP '[^alex]' 等价于 name != 'alex'

## 'a|x' 匹配 条件中的任意值
select * from person where name REGEXP 'a|x';  

## 查询以w开头以i结尾的数据
select * from person where name regexp '^w.*i$';

## 注意:^w 表示w开头, .*表示中间可以有任意多个字符, i$表示以 i结尾

MySQL之多表查询

多表联合查询

数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
#创建部门
CREATE TABLE IF NOT EXISTS dept (
did int not null auto_increment PRIMARY KEY,
dname VARCHAR(50) not null COMMENT '部门名称'
)ENGINE=INNODB DEFAULT charset utf8;


#添加部门数据
INSERT INTO `dept` VALUES ('1', '教学部');
INSERT INTO `dept` VALUES ('2', '销售部');
INSERT INTO `dept` VALUES ('3', '市场部');
INSERT INTO `dept` VALUES ('4', '人事部');
INSERT INTO `dept` VALUES ('5', '鼓励部');

-- 创建人员
DROP TABLE IF EXISTS `person`;
CREATE TABLE `person` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`age` tinyint(4) DEFAULT '0',
`sex` enum('男','女','人妖') NOT NULL DEFAULT '人妖',
`salary` decimal(10,2) NOT NULL DEFAULT '250.00',
`hire_date` date NOT NULL,
`dept_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

-- 添加人员数据

-- 教学部
INSERT INTO `person` VALUES ('1', 'alex', '28', '人妖', '53000.00', '2010-06-21', '1');
INSERT INTO `person` VALUES ('2', 'wupeiqi', '23', '男', '8000.00', '2011-02-21', '1');
INSERT INTO `person` VALUES ('3', 'egon', '30', '男', '6500.00', '2015-06-21', '1');
INSERT INTO `person` VALUES ('4', 'jingnvshen', '18', '女', '6680.00', '2014-06-21', '1');

-- 销售部
INSERT INTO `person` VALUES ('5', '歪歪', '20', '女', '3000.00', '2015-02-21', '2');
INSERT INTO `person` VALUES ('6', '星星', '20', '女', '2000.00', '2018-01-30', '2');
INSERT INTO `person` VALUES ('7', '格格', '20', '女', '2000.00', '2018-02-27', '2');
INSERT INTO `person` VALUES ('8', '周周', '20', '女', '2000.00', '2015-06-21', '2');

-- 市场部
INSERT INTO `person` VALUES ('9', '月月', '21', '女', '4000.00', '2014-07-21', '3');
INSERT INTO `person` VALUES ('10', '安琪', '22', '女', '4000.00', '2015-07-15', '3');

-- 人事部
INSERT INTO `person` VALUES ('11', '周明月', '17', '女', '5000.00', '2014-06-21', '4');

-- 鼓励部
INSERT INTO `person` VALUES ('12', '苍老师', '33', '女', '1000000.00', '2018-02-21', null);

多表查询语法

1
select  字段1,字段2... from 表1,表2... [where 条件]

注意: 如果不加条件直接进行查询,则会出现以下效果,这种结果我们称之为 笛卡尔乘积

1
2
#查询人员和部门所有信息
select * from person,dept 

笛卡尔乘积公式 : A表中数据条数 * B表中数据条数 = 笛卡尔乘积.

关联

建表语句

1
2
3
create table class(id int auto_increment primary key,name varchar(20) not null,stuNum int not null);

create table students(id int auto_increment primary key,name varchar(20) not null,gender bit default 1,classid int not null,foreign key(classid) refenetces class(id));

插入一些数据

1
2
3
4
5
6
7
8
9
10
11
insert into class values(0,"python01",55),(0,"python02",50),(0,"python03",60),(0,"python04",60);
insert into students values(0,"tom",1,1);
insert into students values(0,"jack",1,2);
insert into students values(0,"jack1",1,2);
insert into students values(0,"jack12",1,3);
insert into students values(0,"jack14",1,3);
insert into students values(0,"jack145",1,3);
insert into students values(0,"jack1456",1,2);
insert into students values(0,"lilei",1,10); ## 错误,没有键为10的班级

select * from students;

关联查询

1
2
3
4
5
select students.name,class.name from class inner join students on class.id=students.classid;

select students.name,class.name from class left join students on class.id=students.classid;

select students.name,class.name from class right join students on class.id=students.classid;

分类

1
2
3
4
5
6
1、表A inner join 表B:	
表A与表B匹配的行会出现在结果集中
2、表A left join 表B:
表A与表B匹配的行会出现在结果集中,外加表A中独有的数据,未对应的数据使用null填充
3、表A right join 表B:
表A与表B匹配的行会出现在结果集中,外加表B中独有的数据,未对应的数据使用null填充

约束

MySQL中的约束,添加约束,删除约束,以及其他的一些修饰:

NOT NULL(非空约束)

添加非空约束
  • 建表时直接添加

    1
    CREATE TABLE t_user(user_id INT(10) NOT NULL);
  • 通过ALTER 语句

    1
    2
    3
    ALTER TABLE t_user MODIFY user_id INT(10) NOT NULL;

    ALTER TABLE t_user CHANGE user_id user_id INT(10) NOT NULL;
删除非空约束
1
2
3
ALTER TABLE t_user MODIFY user_id INT(10);

ALTER TABLE t_user CHANGE user_id user_id INT(10);

UNIQUE(唯一约束)

添加唯一约束
建表时直接添加
1
2
3
4
5
6
7
8
9
10
CREATE TABLE t_user(user_id INT(10) UNIQUE);
CREATE TABLE t_user(
user_id INT(10),
user_name VARCHAR(30),
CONSTRAINT UN_PHONE_EMAIL UNIQUE(user_id,user_name)#复合约束
);
CREATE TABLE t_user(
user_id INT(10),
UNIQUE KEY(user_id)
);
通过ALTER语句
1
2
3
4
5
6
7
8
9
10
11
ALTER TABLE t_user MODIFY user_id INT(10) UNIQUE;

ALTER TABLE t_user CHANGE user_id user_id INT(10) UNIQUE;

ALTER TABLE t_user ADD UNIQUE(user_id);

ALTER TABLE t_user ADD UNIQUE KEY(user_id);

ALTER TABLE t_user ADD CONSTRAINT UN_ID UNIQUE(user_id);

ALTER TABLE t_user ADD CONSTRAINT UN_ID UNIQUE KEY(user_id);
删除唯一性约束

ALTER TABLE t_user DROP INDEX user_id;
注:唯一但是可以为空(空和空不相等)

PRIMARY KEY(主键约束)

添加主键约束
建表时直接添加
1
2
3
4
5
6
7
8
9
10
CREATE TABLE t_user(user_id INT(10) PRIMARY KEY);
CREATE TABLE t_user(
user_id INT(10),
user_name VARCHAR(30),
CONSTRAINT PK_ID_NAME PRIMARY KEY(user_id,user_name)#复合约束
);
CREATE TABLE t_user(
user_id INT(10),
PRIMARY KEY(user_id)
);
通过ALTER语句
1
2
3
4
5
6
7
ALTER TABLE t_user MODIFY user_id INT(10) PRIMARY KEY;

ALTER TABLE t_user CHANGE user_id user_id INT(10) PRIMARY KEY;

ALTER TABLE t_user ADD PRIMARY KEY(user_id);

ALTER TABLE t_user ADD CONSTRAINT PK_ID PRIMARY KEY(user_id);
删除主键约束
1
2
3
4
5
6
7
8
9
1)ALTER TABLE t_user DROP PRIMARY KEY;

注:主键约束相当于(唯一约束+非空约束)

一张表中最多有一个主键约束,如果设置多个主键,就会出现如下提示:

Multiple primary key defined!!!

删除主键约束前,如果有自增长需要先删除自增长,如果不删除自增长就无法删除主键约束

FOREIGN KEY(外键约束)

对应的字段只能是主键或者唯一约束修饰的字段

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
首先创建两张表:class,students

主表:
CREATE TABLE class(
cla_id INT(6) AUTO_INCREMENT PRIMARY KEY,
cla_name VARCHAR(30) NOT NULL UNIQUE
);

从表:
CREATE TABLE students(
stu_id INT(10) AUTO_INCREMENT PRIMARY KEY,
stu_name VARCHAR(30) NOT NULL,
stu_score FLOAT(5,2) DEFAULT 0.0,
cla_id INT(10),
CONSTRAINT FK_CLA_ID FOREIGN KEY(cla_id) REFEnetCES class(cla_id)#添加外键约束
);

也可以这样添加:
ALTER TABLE students ADD CONSTRAINT FK_CLA_ID FROEIGN KEY(cla_id) REFEnetCES class(cla_id);

删除外键约束
ALTER TABLE students DROP FOREIGN KEY FK_CLA_ID;

#外键中的级联关系有以下几种情况:
#ON DELETE CASCADE 删除主表中的数据时,从表中的数据随之删除
#ON UPDATE CASCADE 更新主表中的数据时,从表中的数据随之更新
#ON DELETE SET NULL 删除主表中的数据时,从表中的数据置为空

#默认 删除主表中的数据前需先删除从表中的数据,否则主表数据不会被删除

CREATE TABLE students(
stu_id INT(10) AUTO_INCREMENT PRIMARY KEY,
stu_name VARCHAR(30) NOT NULL,
stu_score FLOAT(5,2) DEFAULT 0.0,
cla_id INT(10),
CONSTRAINT FK_CLA_ID FOREIGN KEY(cla_id) REFEnetCES class(cla_id) ON DELETE CASCADE
);

CREATE TABLE students(
stu_id INT(10) AUTO_INCREMENT PRIMARY KEY,
stu_name VARCHAR(30) NOT NULL,
stu_score FLOAT(5,2) DEFAULT 0.0,
cla_id INT(10),
CONSTRAINT FK_CLA_ID FOREIGN KEY(cla_id) REFEnetCES class(cla_id) ON UPDATE CASCADE
);

CREATE TABLE students(
stu_id INT(10) AUTO_INCREMENT PRIMARY KEY,
stu_name VARCHAR(30) NOT NULL,
stu_score FLOAT(5,2) DEFAULT 0.0,
cla_id INT(10),
CONSTRAINT FK_CLA_ID FOREIGN KEY(cla_id) REFEnetCES class(cla_id) ON DELETE SET NULL
);

注:插入数据时,先插入主表中的数据,再插入从表中的数据。

删除数据时,先删除从表中的数据,再删除主表中的数据。

CHECK(检查约束)

1
2
3
4
5
6
7
CREATE TABLE class(
cla_id INT(6) AUTO_INCREMENT PRIMARY KEY,
cla_name VARCHAR(30) NOT NULL UNIQUE,
CHECK(cla_id>0)
);

注:mysql不支持检查约束,但是写上检查约束不会报错

其他

AUTO_INCREMENT(自增长)

添加自增长
  • 在创建表的时候添加

    1
    CREATE TABLE t_user(user_id INT(10) AUTO_INCREMENT PRIMARY KEY);
  • 通过ALTER语句

    1
    2
    3
    ALTER TABLE t_user MODIFY user_id INT(10) AUTO_INCREMENT;

    ALTER TABLE t_user CHANGE user_id user_id INT(10) AUTO_INCREMENT;
删除自增长
1
2
3
4
5
6
7
8
9
ALTER TABLE t_user MODIFY user_id INT(10);

ALTER TABLE t_user CHANGE user_id user_id INT(10);

注:There can be only one auto column and it must be defined as a key.

一张表只能有一个自增长列,并且该列必须定义了约束(可以是主键约束,也可以是唯一约束,也可以是外键约束,但是不可以是非空和检查约束)

不过自增长一般配合主键使用,并且只能在数字类型中使用

ZEROFILL(零填充)

添加零填充
  • 在创建表的时候添加

    1
    CREATE TABLE t_user(user_id INT(10) ZEROFILL);
  • 通过ALTER语句

    1
    2
    3
    ALTER TABLE t_user MODIFY user_id INT(10) ZEROFILL;

    ALTER TABLE t_user CHANGE user_id user_id INT(10) ZEROFILL;
删除零填充
1
2
3
4
5
6
7
ALTER TABLE t_user MODIFY user_id INT(10);

ALTER TABLE t_user CHANGE user_id user_id INT(10);

注:零填充会将未将有效位以外的位用零来显示,比如某字段数据类型为INT(5),而插入的值为2,那么零填充会显示00002

但是,这个效果在Navicat for MySQL中显示不出来,只有在DOS窗口下才能显示

DEFAULT(默认)

添加默认约束
  • 在创建表的时候添加

    1
    CREATE TABLE t_user(user_id INT(10) DEFAULT  3);
  • 通过ALTER语句

    1
    2
    3
    ALTER TABLE t_user MODIFY user_id INT(10) DEFAULT  2;

    ALTER TABLE t_user CHANGE user_id user_id INT(10) DEFAULT 2;
删除默认约束
1
2
3
ALTER TABLE t_user MODIFY user_id INT(10);

ALTER TABLE t_user CHANGE user_id user_id INT(10);

UNSIGNED(无符号位)

添加无符号
  • 在创建表的时候添加

    1
    CREATE TABLE t_user(user_id INT(10) UNSIGNED);
  • 通过ALTER语句

    1
    2
    3
    ALTER TABLE t_user MODIFY user_id INT(10) UNSIGNED;

    ALTER TABLE t_user CHANGE user_id user_id INT(10) UNSIGNED;
删除无符号
1
2
3
4
5
6
7
8
9
ALTER TABLE t_user MODIFY user_id INT(10);
ALTER TABLE t_user CHANGE user_id user_id INT(10);

注:无符号作用于数值类型

#从查询information_schema中查询指定表中的约束

USE INFORMATION_SCHEMA;
SELECT CONSTRAINT_NAME FROM TABLE_CONSTRAINTS WHERE TABLE_NAME='student';

数据库事务

含义

通过一组逻辑操作单元(一组DML——sql语句),将数据从一种状态切换到另外一种状态

特点

(ACID)

  • 原子性:要么都执行,要么都回滚
  • 一致性:保证数据的状态操作前和操作后保持一致
  • 隔离性:多个事务同时操作相同数据库的同一个数据时,一个事务的执行不受另外一个事务的干扰
  • 持久性:一个事务一旦提交,则数据将持久化到本地,除非其他事务对其进行修改

相关步骤:

  1. 开启事务
  2. 编写事务的一组逻辑操作单元(多条sql语句)
  3. 提交事务或回滚事务

事务的分类

  • 隐式事务,没有明显的开启和结束事务的标志

    1
    insert、update、delete语句本身就是一个事务
  • 显式事务,具有明显的开启和结束事务的标志

    1. 开启事务
      取消自动提交事务的功能

    2. 编写事务的一组逻辑操作单元(多条sql语句)
      insert
      update
      delete

    3. 提交事务或回滚事务

使用到的关键字

1
2
3
4
5
6
7
8
set autocommit=0;
start transaction;
commit;
rollback;

savepoint 断点
commit to 断点
rollback to 断点

事务的隔离级别:

事务并发问题如何发生?

  • 当多个事务同时操作同一个数据库的相同数据时

事务的并发问题有哪些?

  • 脏读:一个事务读取到了另外一个事务未提交的数据
  • 不可重复读:同一个事务中,多次读取到的数据不一致
  • 幻读:一个事务读取数据时,另外一个事务进行更新,导致第一个事务读取到了没有更新的数据

如何避免事务的并发问题?

1
2
3
4
5
通过设置事务的隔离级别
1、READ UNCOMMITTED
2、READ COMMITTED 可以避免脏读
3、REPEATABLE READ 可以避免脏读、不可重复读和一部分幻读
4、SERIALIZABLE可以避免脏读、不可重复读和幻读

设置隔离级别:

1
set session|global  transaction isolation level 隔离级别名;

查看隔离级别:

1
select @@tx_isolation;

视图

含义:理解成一张虚拟的表

视图和表的区别:

1
2
3
4
5
使用方式	占用物理空间

视图 完全相同 不占用,仅仅保存的是sql逻辑

表 完全相同 占用

视图的好处:

1
2
1、sql语句提高重用性,效率高
2、和表实现了分离,提高了安全性

视图的创建

1
2
3
4
语法:
CREATE VIEW 视图名
AS
查询语句;

视图的增删改查

  1. 查看视图的数据

    1
    2
    SELECT * FROM my_v4;
    SELECT * FROM my_v1 WHERE last_name='Partners';
  2. 插入视图的数据

    1
    INSERT INTO my_v4(last_name,department_id) VALUES('虚竹',90);
  3. 修改视图的数据

    1
    UPDATE my_v4 SET last_name ='梦姑' WHERE last_name='虚竹';
  4. 删除视图的数据

    1
    DELETE FROM my_v4;

某些视图不能更新

1
2
3
4
5
6
包含以下关键字的sql语句:分组函数、distinct、group  by、having、union或者union all
常量视图
Select中包含子查询
join
from一个不能更新的视图
where子句的子查询引用了from子句中的表

视图逻辑的更新

1
2
3
4
5
6
7
8
9
10
11
12
#方式一:
CREATE OR REPLACE VIEW test_v7
AS
SELECT last_name FROM employees
WHERE employee_id>100;

#方式二:
ALTER VIEW test_v7
AS
SELECT employee_id FROM employees;

SELECT * FROM test_v7;

视图的删除

1
DROP VIEW test_v1,test_v2,test_v3;

视图结构的查看

1
2
DESC test_v7;
SHOW CREATE VIEW test_v7;

存储过程

含义:一组经过预先编译的sql语句的集合
好处:

1
2
3
1、提高了sql语句的重用性,减少了开发程序员的压力
2、提高了效率
3、减少了传输次数

分类:

1
2
3
4
5
6
1、无返回无参
2、仅仅带in类型,无返回有参
3、仅仅带out类型,有返回无参
4、既带in又带out,有返回有参
5、带inout,有返回有参
注意:in、out、inout都可以在一个存储过程中带多个

创建存储过程

语法:

1
2
3
4
5
create procedure 存储过程名(in|out|inout 参数名  参数类型,...)
begin
存储过程体

end

类似于方法:

1
2
3
4
修饰符 返回类型 方法名(参数类型 参数名,...){

方法体;
}

注意

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
1、需要设置新的结束标记
delimiter 新的结束标记
示例:
delimiter $

CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...)
BEGIN
sql语句1;
sql语句2;

END $

2、存储过程体中可以有多条sql语句,如果仅仅一条sql语句,则可以省略begin end

3、参数前面的符号的意思
in:该参数只能作为输入 (该参数不能做返回值)
out:该参数只能作为输出(该参数只能做返回值)
inout:既能做输入又能做输出

调用存储过程

1
call 存储过程名(实参列表)

函数

创建函数

学过的函数:LENGTH、SUBSTR、CONCAT等
语法:

1
2
3
4
5
CREATE FUNCTION 函数名(参数名 参数类型,...) RETURNS 返回类型
BEGIN
函数体

END

调用函数

1
SELECT 函数名(实参列表)

函数和存储过程的区别

1
2
3
关键字		调用语法	返回值			应用场景
函数 FUNCTION SELECT 函数() 只能是一个 一般用于查询结果为一个值并返回时,当有返回值而且仅仅一个
存储过程 PROCEDURE CALL 存储过程() 可以有0个或多个 一般用于更新

流程控制结构

系统变量

一、全局变量

作用域:针对于所有会话(连接)有效,但不能跨重启

1
2
3
4
5
6
7
8
9
查看所有全局变量
SHOW GLOBAL VARIABLES;
查看满足条件的部分系统变量
SHOW GLOBAL VARIABLES LIKE '%char%';
查看指定的系统变量的值
SELECT @@global.autocommit;
为某个系统变量赋值
SET @@global.autocommit=0;
SET GLOBAL autocommit=0;

二、会话变量

作用域:针对于当前会话(连接)有效

1
2
3
4
5
6
7
8
9
10
查看所有会话变量
SHOW SESSION VARIABLES;
查看满足条件的部分会话变量
SHOW SESSION VARIABLES LIKE '%char%';
查看指定的会话变量的值
SELECT @@autocommit;
SELECT @@session.tx_isolation;
为某个会话变量赋值
SET @@session.tx_isolation='read-uncommitted';
SET SESSION tx_isolation='read-committed';

自定义变量

一、用户变量

声明并初始化:

1
2
3
SET @变量名=值;
SET @变量名:=值;
SELECT @变量名:=值;

赋值:

1
2
3
4
方式一:一般用于赋简单的值
SET 变量名=值;
SET 变量名:=值;
SELECT 变量名:=值;
1
2
3
方式二:一般用于赋表 中的字段值
SELECT 字段名或表达式 INTO 变量
FROM 表;

使用:

1
select @变量名;

二、局部变量

声明:

1
declare 变量名 类型 【default 值】;

赋值:

1
2
3
4
方式一:一般用于赋简单的值
SET 变量名=值;
SET 变量名:=值;
SELECT 变量名:=值;
1
2
3
方式二:一般用于赋表 中的字段值
SELECT 字段名或表达式 INTO 变量
FROM 表;

使用:

1
select 变量名

二者的区别:

1
2
3
作用域			定义位置		语法
用户变量 当前会话 会话的任何地方 加@符号,不用指定类型
局部变量 定义它的BEGIN END中 BEGIN END的第一句话 一般不用加@,需要指定类型

分支

一、if函数
语法:if(条件,值1,值2)
特点:可以用在任何位置

二、case语句

语法:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
情况一:类似于switch
case 表达式
when 值1 then 结果1或语句1(如果是语句,需要加分号)
when 值2 then 结果2或语句2(如果是语句,需要加分号)
...
else 结果n或语句n(如果是语句,需要加分号)
end 【case】(如果是放在begin end中需要加上case,如果放在select后面不需要)

情况二:类似于多重if
case
when 条件1 then 结果1或语句1(如果是语句,需要加分号)
when 条件2 then 结果2或语句2(如果是语句,需要加分号)
...
else 结果n或语句n(如果是语句,需要加分号)
end 【case】(如果是放在begin end中需要加上case,如果放在select后面不需要)

特点:
可以用在任何位置

三、if elseif语句

语法:

1
2
3
4
5
if 情况1 then 语句1;
elseif 情况2 then 语句2;
...
else 语句n;
end if;

特点:
只能用在begin end中!!!!!!!!!!!!!!!

三者比较:
应用场合
if函数 简单双分支
case结构 等值判断 的多分支
if结构 区间判断 的多分支

循环

语法:

1
2
3
【标签:】WHILE 循环条件  DO
循环体
END WHILE 【标签】;

特点:

1
2
3
4
5
只能放在BEGIN END里面

如果要搭配leave跳转语句,需要使用标签,否则可以不用标签

leave类似于java中的break语句,跳出所在循环!!!

数据库引擎

查看数据库支持的引擎

1
show engines;

查看数据库当前默认引擎

1
show variables like '%storage_engine%';

查看数据表使用的引擎

1
show create table table_name;

修改表的存储引擎

1
alter table table_name engine=engine_name;

问题

Navicat for mysql连接远程数据库(1130错误解决方法)

  • 进入mysql控制台:

  • 执行命令1

    1
    GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '您的数据库密码' WITH GRANT OPTION;
  • 执行命令2

    1
    flush privileges;