MySQL学习笔记

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

tinyint[(m)] [unsigned] [zerofill]

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

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

int

int[(m)][unsigned][zerofill]

整数，数据类型用于保存一些范围的整数数值范围：
有符号： -2147483648 ～ 2147483647
无符号：4294967295

bigint

bigint[(m)][unsigned][zerofill]

大整数，数据类型用于保存一些范围的整数数值范围：
有符号：-9223372036854775808 ～ 9223372036854775807
无符号：18446744073709551615
        
作用：存储年龄，等级，id，各种号码等

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

小数型

decimal

decimal[(m[,d])] [unsigned] [zerofill]

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

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

FLOAT

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

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

char (m)

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

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

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

varchar

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

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

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');

日期/时间类型

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 <数据库名称>;

use zykj;

查看当前选择的数据库

1	select database();

MySQL之表操作

什么是表

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

查看当前数据库中所有表

1	格式：show tables;

创建表

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

示例：

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(...),(...),...;

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的区别？[面试题]

TRUNCATE 在各种表上无论是大的还是小的都非常快。而DELETE 操作会被表中数据量的大小影响其执行效率.
TRUNCATE是一个DDL语言而DELETE是DML语句，向其他所有的DDL语言一样，他将被隐式提交，不能对TRUNCATE使用ROLLBACK命令。
TRUNCATE不能触发触发器，DELETE会触发触发器。
当表被清空后表和表的索引和自增主键将重新设置成初始大小，而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 关键字对简单查询的结果集进行过滤

比较运算符: > < >= <= = <> (!=)
null 关键字: is null ,not null
逻辑运算符: 与 and 或 or (多个条件时,需要使用逻辑运算符进行连接)

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

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

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;

逻辑运算符: 与 and 或 or

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 % : 任意多个字符, _ :单个字符

示例

#查询姓名以"张"字开头的
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

排序查询格式

select 字段|* from 表名 [where 条件过滤] [order by 字段[ASC][DESC]]

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

示例

#按人员工资正序排列,注意:此处可以省略 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 以外，都会忽略空值

COUNT：统计指定列不为NULL的记录行数；
SUM：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
MAX：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
MIN：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
AVG：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

格式

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

示例

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

分组查询

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

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

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

公司每个部门有多少人
公司中有多少男员工和多少女员工.

分组查询格式

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

示例

#查询每个部门的平均薪资
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

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

分页查询

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

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

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

联合查询

union 联合、合并

语法：

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

特点：

多条查询语句的查询的列数必须是一致的
多条查询语句的查询的列的类型几乎相同
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”。

## ^  匹配 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之多表查询

多表联合查询

数据

#创建部门
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));

插入一些数据

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;

关联查询

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、表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(唯一约束)

添加唯一约束

建表时直接添加

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语句

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(主键约束)

添加主键约束

建表时直接添加

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语句

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)ALTER TABLE t_user DROP PRIMARY KEY;

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

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

Multiple primary key defined!!!

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

FOREIGN KEY(外键约束)

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

首先创建两张表: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(检查约束)

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;

删除自增长

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;

删除零填充

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;

删除无符号

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
insert、update、delete语句本身就是一个事务
显式事务，具有明显的开启和结束事务的标志
1. 开启事务
  取消自动提交事务的功能
2. 编写事务的一组逻辑操作单元（多条sql语句）
  insert
  update
  delete
3. 提交事务或回滚事务

使用到的关键字

set autocommit=0;
start transaction;
commit;
rollback;

savepoint  断点
commit to 断点
rollback to 断点

事务的隔离级别:

事务并发问题如何发生？

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

事务的并发问题有哪些？

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

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

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

设置隔离级别：

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

查看隔离级别：

1	select @@tx_isolation;

视图

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

视图和表的区别：

使用方式	占用物理空间

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

表	完全相同	占用

视图的好处：

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

视图的创建

语法：
CREATE VIEW  视图名
AS
查询语句;

视图的增删改查

查看视图的数据

1 2	SELECT * FROM my_v4; SELECT * FROM my_v1 WHERE last_name='Partners';

插入视图的数据

1	INSERT INTO my_v4(last_name,department_id) VALUES('虚竹',90);

修改视图的数据

1	UPDATE my_v4 SET last_name ='梦姑' WHERE last_name='虚竹';

删除视图的数据
1
DELETE FROM my_v4;

某些视图不能更新

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

视图逻辑的更新

#方式一：
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、仅仅带in类型，无返回有参
3、仅仅带out类型，有返回无参
4、既带in又带out，有返回有参
5、带inout，有返回有参
注意：in、out、inout都可以在一个存储过程中带多个

创建存储过程

语法：

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

end

类似于方法：

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

	方法体;
}

注意

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等
语法：

CREATE FUNCTION 函数名(参数名 参数类型,...) RETURNS 返回类型
BEGIN
	函数体

END

调用函数

1	SELECT 函数名（实参列表）

函数和存储过程的区别

1
2
3

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

流程控制结构

系统变量

一、全局变量

作用域：针对于所有会话（连接）有效，但不能跨重启

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

二、会话变量

作用域：针对于当前会话（连接）有效

查看所有会话变量
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 @变量名:=值;

赋值：

方式一：一般用于赋简单的值
SET 变量名=值;
SET 变量名:=值;
SELECT 变量名:=值;

1
2
3

方式二：一般用于赋表 中的字段值
SELECT 字段名或表达式 INTO 变量
FROM 表;

使用：

1	select @变量名;

二、局部变量

声明：

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

赋值：

方式一：一般用于赋简单的值
SET 变量名=值;
SET 变量名:=值;
SELECT 变量名:=值;

1
2
3

方式二：一般用于赋表 中的字段值
SELECT 字段名或表达式 INTO 变量
FROM 表;

使用：

1	select 变量名

二者的区别：

1
2
3

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

分支

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

二、case语句

语法：

情况一：类似于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语句

语法：

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

特点：
只能用在begin end中！！！！！！！！！！！！！！！

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

循环

语法：

1
2
3

【标签：】WHILE 循环条件  DO
	循环体
END WHILE 【标签】;

特点：

只能放在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;

MySQL学习

MySQL的连接

启动服务

停止服务

连接数据库

断开连接

查看服务器的版本

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

远程连接

MySQL数据类型和基本操作

数据类型

二进制类型

整数类型

小数型

字符型

枚举类型(了解)

集合类型(了解)

日期/时间类型

基本操作

创建数据库

删除数据库

切换数据库

查看当前选择的数据库

MySQL之表操作

什么是表

查看当前数据库中所有表

创建表

删除表

查看表结构

查看建表语句

重命名表名

修改表结构

复制表

数据操作(增 删 改 查)

增 insert

全列插入

缺省插入

同时插入多条数据

删 delete

改 update

查 select

查

MySQL之单表查询

简单查询

条件查询

区间查询

集合查询

模糊查询

排序查询

聚合函数

分组查询

分页查询

联合查询

正则表达式

MySQL之多表查询

多表联合查询

关联

约束

NOT NULL(非空约束)

添加非空约束

删除非空约束

UNIQUE(唯一约束)

添加唯一约束

建表时直接添加

通过ALTER语句

删除唯一性约束

PRIMARY KEY(主键约束)

添加主键约束

建表时直接添加

通过ALTER语句

删除主键约束

FOREIGN KEY(外键约束)

CHECK(检查约束)

其他

AUTO_INCREMENT(自增长)

添加自增长

删除自增长

ZEROFILL(零填充)

添加零填充

删除零填充

数据操作(增删改查)