mysql学习

安装数据库

brew install mysql@5.7

brew tap homebrew/services

brew services start mysql@5.7

//如果报错
brew update

brew services list

brew link mysql@5.7 --force

echo 'export PATH="/usr/local/opt/mysql@5.7/bin:$PATH"' >> ~/.zshrc
export LDFLAGS="-L/usr/local/opt/mysql@5.7/lib"
export CPPFLAGS="-I/usr/local/opt/mysql@5.7/include"

brew services start mysql@5.7
brew services stop  mysql@5.7

update user set authentication_string=password('Ssy198800!') where user='root';

操作数据库

显示所有的数据库
show databases;

如果 db1 数据库 存在，删除 db1；
drop database  if exists db1;

查询正在使用的数据库
select database();

使用数据库 db1
use db1;

修改db1 的字符集
alter database db1 character set utf8;

显示 db1 的创建信息
show create database db1;

如果 db1 不存在的话，创建 db1 字符集为 utf-8
create database if not exists db1 character set utf8;
quit

操作表

显示当前使用的数据库里面包含的表
show tables;

显示 user 表的表结构
desc user;

创建表的基本语法
create table user (
    id int,
    name varchar(32),
    age int,
    score double(5,2),  -- 最大值为 999.99
    birthday date, -- yyyy-MM-dd
    creatTime timestamp, -- yyyy-MM-dd HH:mm:ss 
);

复制表 把 Student 复制一份，命名Wie stu
create table stu like Student


删除 Student
drop table Student

如果 Student 表存在，删除 Student；
drop table  if exists Student;

查看表 stu2 的创建信息
show create table stu2;

修改表名称 把表  Stu1 修改名称为 Stu2
alter table Stu1 rename to Stu2
 
修改表字符集,把 stu2 表的字符集修改为 utf-8
alter table stu2 character set utf8;


添加一列,在 stu2 的表中 新增一列 名为 gender、类型为 int 、长度为 1 位的列。
alter table stu2 add gender int(1);

修改列名称 类型,将表 stu2中列名为  gender1 修改为 sex，类型修改为 字符串，长度为 20；
alter table stu2 change gender1 sex varchar(20);


只修改类的类型,将表 stu2中列名为 sex 列，类型修改为 int ，长度为 1；
alter table stu2 modify sex int(1);


删除列 删除 stu2 表中的 sex 列。
alter table stu2 drop sex;

操作表中的数据

添加数据

insert into 表名(列名1，列名2,列名3) values (值1,值2,值3);

insert into 表名 values (值1,值2,值3); 要对应表中所用的列的值。

eg :INSERT into stu2 values (2,"李三",25,88.5,"2015-05-11",null,1);

列名和值要一一对应，如果表后面没有添加列名，则默认给所有的列添加值。除了数字类型外，其他类型的值需要用引号包裹起来。

删除数据

delete from 表名 [where 条件];

eg : delete from stu2 where id=1;

如果不加条件，会删除表中的所有数据。delete from stu2;（不推荐用做删除表，因为这个操作是表中有多少数据就进行多少次删除操作。）

//删除 stu2 所有数据 truncate table stu2;

修改数据

update 表名 set 列名1=值1,列名2=值2， [where 条件] 如果不加任何条件，则会将表中所有记录全部修改。

查询表中的数据

select * from 表名

查询语句

基础查询

select name,age from stu2; 查询 name ，age 字段

select 
	name, -- 姓名
	age  -- 年龄
from 
	stu2; 

去重 DISTINCT select DISTINCT name from stu2;

结果相加 (+) 并且设置别名(as 可以用空格代替)，计算列的结果可以使用四则运算符，一般只计算数值型的值。 select age as "年纪",gender as "性别", ifnull(age,0)+ifnull(gender,0) as "虽然不合理但是我就是想这么加" from stu2;

条件查询 where

where 后面跟查询条件

运算符

>
<
<=
>=
=
<> 不等于,跟 != 一样的意义。
between...and 在什么什么之间
in(集合) 在集合内部的范围
like 模糊查询，占位符 - 单个任意字符，%多个任意字符
is null 值为空
is not null 值不为空
and / &&
or / ||
not / !

select * from stu2 where age >= 25; 
select * from stu2 where age = 25; 
select * from stu2 where age != 25; 
select * from stu2 where age <> 25; 
select * from stu2 where age >= 25 && age < 40; 
select * from stu2 where age >= 25 and age < 40; 
select * from stu2 where age BETWEEN 25 and 40; 
select * from stu2 where age = 25 || age = 28; 
select * from stu2 where age = 25 or age = 28; 
select * from stu2 where age in (25,28); 
select * from stu2 where score is null;
select * from stu2 where score is not null;
-- 姓名第一字为 王 的 数据
select * from stu2 where name like '王%';
-- 姓名第二字为 三 的 数据
select * from stu2 where name like '_三%';
-- 姓名为三个字的人
select * from stu2 where name like '___';
-- 姓名中包含王的人
select * from stu2 where name like '%王%';

排序查询

order by 子句 eg: select * from stu2 order by id;,select * from stu2 order by id DESC; 排序方式不写，默认升序排列（ASC）。

order by 排序字段1 排序方式1,排序字段2 排序方式2, ...

排序方式 1. 升序（ASC）默认 2. 降序（DESC）降序

按照 score 排序如果值一样，按照 id 排序;只有第一排序条件结果一样的时候，才采用后面的排序条件。 select * from stu2 order by score ASC,id ASC;

聚合函数

将一列数据作为一个整体，进行一个纵向的计算。

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

eg:

select count(name) from stu2; 计算 name 有多少个字段 ,不计算 null 值。 select count(ifnull(name,"")) from stu2; 计算 name 有多少个字段 ,如果值为 null，

分组查询

group by gender

分组之后查询的字段只能是分组的字段或者聚合函数。 select gender,avg(score),max(score),COUNT(id) from stu2 group by gender;

按照 gender 分组；查询平均值avg ，最大值max ，最小值min ，人数 COUNT 分组；score 值低于 80 的不参与分组。 select gender,avg(score),max(score),min(score),COUNT(id) from stu2 where score>80 group by gender;

– 按照 gender 分组；查询平均值avg ，最大值max ，最小值min ，人数 COUNT 分组；score 值低于 80 的不参与分组。 – 分组之后人数大于两个人 select gender,avg(score),max(score),min(score),COUNT(id) from stu2 where score>80 group by gender having count(id)>2;

为聚合函数取别名 select gender,avg(score),max(score),min(score),COUNT(id) as studentNumber from stu2 where score>80 group by gender having studentNumber>2; COUNT(id) as studentNumber，将聚合函数(COUNT(id) )设置为别名 studentNumber,当做后面的查询条件用。

where 在分组查询之间进行限定，不可进行聚合函数判断
having 在分组之后进行限定，可以进行聚合函数判断

分页查询

limit 开始的索引每页查询的条数

– 查询第1页，每页查询2个 select * from stu2 limit 0,2; – 查询第2页，每页查询2个 select * from stu2 limit 2,2; – 查询第3页，每页查询2个 select * from stu2 limit 4,2;

开始的索引 = (当前的页码 - 1) * 每页显示的个数

约束

对表中的数据进行限定，保证数据的正确性、有效性和完整性。

主键约束 primary key
非空约束 not null
唯一约束 unique
外键约束 foreign key

-- 约束
-- 非空约束
-- 创建表时添加非空约束
CREATE TABLE Person (
	id int,
	name VARCHAR(20) NOT NULL -- name 非空
);

-- 删除 非空的约束
ALTER TABLE `Person` MODIFY name VARCHAR(10);

-- 添加 非空的约束
ALTER TABLE `Person` MODIFY name VARCHAR(10) NOT NULL;


-- 唯一约束
-- 创建表时添加唯一约束
CREATE TABLE Person1 (
	id int UNIQUE, -- 添加唯一约束
	name VARCHAR(20)
);

-- 添加 唯一的约束
ALTER TABLE `Person1` MODIFY name VARCHAR(10) UNIQUE;

-- 删除 唯一的约束
ALTER TABLE `Person1` DROP INDEX name ;


-- 主键约束
-- 注意：
-- 含义：非空且唯一。
-- 一张表只能有一个字段为主键.

-- 主键约束
-- 创建表时添加主键约束
CREATE TABLE Person2 (
	id int PRIMARY KEY, -- 添加主键约束
	name VARCHAR(20)
);

-- 删除主键约束
ALTER TABLE Person2 DROP PRIMARY KEY;

-- 添加 主键约束
ALTER TABLE `Person2` MODIFY id int PRIMARY KEY;


-- 主键约束 自动增长 ：如果某一列是数值类型的，使用 auto_increment 可以完成值的自动增长。

-- 创建表时添加主键约束 并且完成自动增长
CREATE TABLE Person3 (
	id int PRIMARY KEY auto_increment, -- 添加主键约束
	name VARCHAR(20),
);

-- 删除 自动增长
ALTER TABLE `Person3` MODIFY id int;

-- 添加 自动增长
ALTER TABLE `Person3` MODIFY id int auto_increment;

select * from Person3;

INSERT INTO Person3 values(null, "李三");


-- 外键约束 foreign key

-- 创建时添加外键
CREATE TABLE Address (
	address_id INT PRIMARY KEY auto_increment, -- 添加主键约束
	name VARCHAR(20)
);

CREATE TABLE Person4 (
	id int PRIMARY KEY auto_increment, -- 添加主键约束
	name VARCHAR(20),
	address INT,
	CONSTRAINT person3_location foreign key (address) REFERENCES Address(address_id) -- 关联 Address 表中的  address_id 
);

-- 删除外键
ALTER TABLE Person4 DROP foreign key person3_location;

-- 创建表以后 添加外键 
ALTER TABLE Person4 ADD CONSTRAINT person3_location foreign key (address) REFERENCES Address(address_id);

INSERT INTO Address values(null, "南山");
INSERT INTO Address values(null, "福田");
INSERT INTO Address values(null, "前海");
select * from Address;

INSERT INTO Person4 values(null, "李三",1);

INSERT INTO Person4 values(null, "李四",2);

INSERT INTO Person4 values(null, "王五",3);


select * from Person4;
select * from Address;

-- 添加外键 设置 级联操作 ON UPDATE CASCADE 更新关联  ON  DELETE CASCADE 删除关联
ALTER TABLE Person4 ADD CONSTRAINT person3_location foreign key (address) REFERENCES Address(address_id) ON UPDATE CASCADE ON  DELETE CASCADE;

update stu2 set age=45 where id=1;
update address  set `address_id` = 5 where name="南山";
delete  from Address where address_id=2;

多表之间的关系

多表之间的关系
- 一对一
  1. 如人和身份证，一个人只能有一个身份证，一个身份证也只能对应一个人。
  2. 操作：可以在任意一方添加外键指向另一方的主键，并且保证外键值唯一（unique）。
- 一对多、多对一
  1. 如部门和员工的关系。一个部门有多个员工，一个员工只能对应一个部门。
  2. 操作：在多的一方（员工）建立外键指向一的一方（部门）的主键。（在员工表中建立外键指向部门表的主键）
- 多对多
  1. 学生和课程之间的关系。一个学生可以选择很多门课程，一门课程也能被许多学生选择。
  2. 操作：多对多关系的实现，需要借助第三张中间表，中间表至少包含两个字段，分别指向两张表的主键。这两个字段分别作为外键指向两张表的主键。这两个字段叫做联合主键。

范式

第一范式

第二范式

第三范式

数据库的备份和还原

多表查询

-- 内连接查询
-- 使用隐式内连接查询

SELECT * FROM Person4,Address WHERE Person4.`Address` = Address.`address_id`;

SELECT Person4.name ,Address.`name` FROM Person4,Address WHERE Person4.`Address` = Address.`address_id`;

SELECT 
		p.`name` "姓名",
		a.`name` "地址"
FROM
		Person4 p,Address a 
WHERE 
		p.`address` = a.`address_id`;




-- 显式内连接

-- 语法 ： SELECT 字段列表 FROM 表名1 INNER JOIN 表名2 ON 表名1.id = 表名2.id;
-- 语法 ： SELECT 字段列表 FROM 表名1 JOIN 表名2 ON 表名1.id = 表名2.id;


SELECT 
		p.`name` "姓名",
		a.`name` "地址"
FROM
		Person4 p INNER JOIN Address a 
ON 
		p.`address` = a.`address_id`;
		

SELECT 
		p.`name` "姓名",
		a.`name` "地址"
FROM
		Person4 p JOIN Address a 
ON 
		p.`address` = a.`address_id`;




-- 外连接查询

-- 左外连接 语法 ： SELECT 字段列表 FROM 表名1 LEFT OUTER JOIN 表名2 ON 表名1.id = 表名2.id;
-- 左外连接 语法 ： SELECT 字段列表 FROM 表名1 LEFT  JOIN 表名2 ON 表名1.id = 表名2.id;
-- 左外连接 查询的是左表所有的数据以及交集部分。


-- 右外连接
-- 右外连接 语法 ： SELECT 字段列表 FROM 表名1 RIGHT OUTER JOIN 表名2 ON 表名1.id = 表名2.id;
-- 右外连接 语法 ： SELECT 字段列表 FROM 表名1 RIGHT  JOIN 表名2 ON 表名1.id = 表名2.id;
-- 右外连接 查询的是右表所有的数据以及交集部分。

INSERT INTO Person4 values(null, "王老实",NULL);
INSERT INTO Address values(null, "宝安");

select * from Person4;
/*
1	李三	5
3	王五	3
5	李四	6
6	王老实	NULL
*/


select * from Address;
/*
3	前海
5	南山
6	后海
7	宝安
*/

SELECT 
		p.`name` "姓名",
		a.`name` "地址"
FROM
		Person4 p 
LEFT JOIN 
		Address a 
ON 
		p.`address` = a.`address_id`;
		
/*

王五	前海
李三	南山
李四	后海
王老实	NULL

*/
		
		
		
		
SELECT 
		p.`name` "姓名",
		a.`name` "地址"
FROM
		Person4 p 
RIGHT JOIN 
		Address a 
ON 
		p.`address` = a.`address_id`;
		
		
/*
李三	南山
王五	前海
李四	后海
NULL	宝安
*/
		

mysql学习

在不断填坑中前进。。

mysql学习

安装数据库

操作数据库

操作表

操作表中的数据

添加数据

删除数据

修改数据

查询表中的数据

查询语句

基础查询

条件查询 where

排序查询

聚合函数

分组查询

分页查询

约束

多表之间的关系

范式

第一范式

第二范式

第三范式

数据库的备份和还原

多表查询

CATALOG

FEATURED TAGS

FRIENDS