自学MySQL数据库笔记整理

MySQL数据库##

DDL操作数据库和表

1、操作数据库：CRUD

1、C（Create）：创建

​ 创建数据库：

​ Create database 数据库名称;

​ 创建数据库，判断不存在，在创建

​ Create database if not exists 数据库名称;

​ 创建数据库，并指定字符集

​ Create database 数据库名称 character set 字符集名称;

2、R （Retrieve）：查询

​ 查询所有数据库的名称：

​ show databases;

​ 查询某个数据库的字符集： 查询某个数据库的创建语句

​ show Create database 数据库名称；

3、U（Update）：修改

​ 修改数据库字符集：

​ alter database 数据库名称 character set 字符集名称;

4、D（Delete）： 删除

​ 删除数据库：

​ drop database 数据库名称;

​ 判断数据库存在，存在再删除

​ drop database if exists 数据库名称;

5、使用数据库

​ 查询当前正在使用的数据库名称：

​ select database();

​ 使用数据库：

​ use 数据库名称;

2、操作表

1、C（Create）：创建

​ 语法

​ Create table 表名(

​ 列名1 数据类型1,

​ 列名2 数据类型2,

​ ....

​ 列名n 数据类型n

​ );

​ 最后一行不需要（,）

​ 数据库类型：

​ 1、int : 整数类型

​ 2、double : 小数类型

​ 3、date ： 日期，只包含年月日的日期，y y y y-MM-dd

​ 4、datetime： 日期，包含年月日时分秒 yyyy-MM-dd HH:mm:ss

​ 5、timestamp : 时间戳类型 包含年月日时分秒 yyyy-MM-dd HH:mm:ss

1，TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP  
在创建新记录和修改现有记录的时候都对这个数据列刷新

2，TIMESTAMP DEFAULT CURRENT_TIMESTAMP  

在创建新记录的时候把这个字段设置为当前时间，但以后修改时，不再刷新它

3，TIMESTAMP ON UPDATE CURRENT_TIMESTAMP  

在创建新记录的时候把这个字段设置为0，以后修改时刷新它 

4，TIMESTAMP DEFAULT ‘yyyy-mm-dd hh:mm:ss’ ON UPDATE CURRENT_TIMESTAMP  
在创建新记录的时候把这个字段设置为给定值，以后修改时刷新它

​ 6、varchar ：字符串

​ name varchar(20):姓名最大为20个字符

​ zhangsan 8个字符。 张三 2个字符

​ *创建表：

create table student(
    -> id int,
    -> name varchar(32),
    -> age int,
    -> score double(4,1),
    -> birthday date,
    -> insert_time timestamp
    -> );

​ 复制表

​ Create table 表名称 like 要复制的表名称;

2、R （Retrieve）：查询

​ 查询某个数据库中所有的表的名称：

​ show tables;

​ 查询表结构：

​ desc 表名;

3、U（Update）：修改

​ 修改表名：

​ alter table 表名称 rename to 新名称;

​ 修改字符集：

​ alter table 表名称 character set 字符集名称;

​ 添加列：

​ alter table 表名称 add 列名称 数据类型;

​ 修改列名：

​ alter table 表名称 change 列名称 新列名称 新数据类型;

​ alter table 表名称 modify 列名称 新数据类型;

​ 删除列：

​ alter table 表名称 drop 列名称;

4、D（Delete）： 删除

​ 删除表

​ drop table 表名称;

​ drop table if exists 表名称;

DML：增删改表中数据

1、添加数据：

语法：

​ insert into 表名(列名1，列名2，....列名n)values(值1,值2,....值n);

​ 注意：

​ 1、列名要和值一一对应。

​ 2、如果表明后，不定义列名，则默认给所有列添加值

​ insert into 表名 values(值1, 值2,....值n);

​ 3、除了数字类型，其他类型需要使用引号(单双都可以)引起来

2、删除数据：

语法：

​ delete from 表名[where 条件];

​ 注意：

​ 如果不加条件，则删除表中所有的记录，并且有多少次记录删除多少次，直到删除干净。

​ truncate table 表名; --先删除表，然后创建一个一模一样的表。

3、修改数据：

语法：

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

DDL：查询表中记录

​ select * from 表名;

1、语法：

​ select 字段列表 from 表名列表 where 条件列表 group by 分组字段 having 分组之后的条件 order by 排序 limit 分页限定 ;

2、基础查询：

1、多个字段查询

​ select 字段名1, 字段名2....from 表名;

​ 注意：

​ 如果查询所有字段，则可以使用*来代替字段列表。

2、去重复

​ distinct

3、计算列

​ 一般可以使用四则运算计算一些列的值。（一般只会进行数值型的计算）

​ ifnull(表达式1, 表达式2) ：null参与运算，计算的结果都为null

​ 表达式1:哪个字段需要判断是否为null

​ 表达式2:如果该字段为null 则替换为该参数

4、起别名

​ as：as也可以忽略

3、条件查询：

1、where句子后面跟条件

2、运算符：

​ > , < , <=, >=, = ,<>(不等于)

​ BETWEEN....AND

​ IN(在一个集合中)

​ LIKE：模糊查询：

​ 占位符：

​ _:单个人意字符

​ %:多个任意字符

​ IS NULL

​ AND 或 &&

​ OR 或 ||

​ NOT 或 !

DQL：查询语句

1、排序查询

语法： order by 句子

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

排序方式：

​ ASC：生序，默认的。

​ DESC：降序。

​ 注意： 如果有多个排序条件，则当前面的条件一样时，才会执行第二个条件。

2、聚合函数

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

1、count ：计算个数

​ 1、一般选择非空的列：主键

​ 2、count(*)：计算该列所有个数

2、max：计算最大值

3、min：计算最小值

4、sum：计算和

5、avg：计算平均值

​ 注意：聚合函数的计算，排除null值。

​ 解决方案：

​ 1、选择不包含空（null）的列进行计算。

​ 2、IFNULL函数

3、分组查询

1、语法：group by 分组字段;

​ 注意：

​ 1、分组之后查询的字段：分组字段、聚合函数（其他字段无意义）

​ 2、where和having的区别：where在分组前进行限定，如果不满足条件，不参与分组。having在分组后进行限定，如果不满足结果则不会被查询出来。where后面不可以跟聚合函数、having可以进行聚合函数的判断。

例如：

​ 按照性别分组查询男女的平均分：
SELECT sex, AVG(math) FROM student3 GROUP BY sex;

​ 按照性别分组。分别查询男、女同学的平均分,人数:
SELECT sex, AVG(math), COUNT(id) AS 人数 FROM student3 GROUP BY sex;

4、分页查询 MySQL独有方言

1、语法：limit 开始的索引, 每页查询的条数 ;

2、公式：开始的索引 = （当前页码数 - 1）*每页显示的条数; ---- (n - 1) *条目数

约束

概念：

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

分类：

1、主键约束：primary key

1、注意：
		1、含义：非空且唯一
		2、一张表只能有一个字段为主键
		3、主键就是表中记录的唯一标识
2、在创建表时，添加主键约束
		CREATE TABLE 表名称(
      id int primary key, -- 给id添加主键约束
      name varchar(20)
    );
3、删除主键
		ALTER TABLE 表名称 DROP PRIMARY KEY; -- 因为主键唯一，所以不需要字段名 并且不能有自增约束
4、创建表后，添加主键
		ALTER TABLE 表名称 MODIFY 字段名称 字段类型 PRIMARY KEY;
5、自动增长
		1、如果某一列是数值类型的，使用 AUTO_INCREMENT 可以来完成值的自动增长

2、非空约束：not null 值不能为空

1、创建表时添加约束
		CREATE TABLE 表名称(
      id INT,
      name VARCHAR(20) NOT NULL -- name不为空
    );
2、创建表后，添加非空约束
		ALTER TABLE 表名称 MODIFY 字段名称 字段类型 NOT NULL;
3、删除非空约束
		ALTER TABLE 表名称 MODIFY 字段名称 字段类型;

3、唯一约束：unique，值不能重复

1、创建表时，添加唯一约束
		CREATE TABLE 表名称(
      id INT,
      phone_number VARCHAR(20) UNIQUE -- 添加了唯一约束 但是列可以有多个NULL
    );
2、删除唯一约束
		ALTER TABLE 表名称 DROP INDEX 字段名;
3、在创建表后，添加唯一约束
		ALTER TABLE 表名称 MODIFY 字段名 字段类型 UNIQUE;

4、外键约束： foreign key，让表与表产生关系，从而保证数据的准确性。

1、创建表时添加外键
		CREATE TABLE 表名称(
      ....
      外键列,
      CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称)
    );
2、删除外键
 		ALTER TABLE 表名称 DROP FOREIGN KEY 外键名称;
3、创建表后添加外键
 		ALTER TABLE 表名称 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称 (主表列名);
4、级联操作
	1、添加级联操作
			ALTER TABLE 表名称 ADD CONSTRAINT 外键名称
				FOREIGN KEY (外键字段名称) REFERENCES 主表名称 (主表列名) ON UPDATE CASCADE ON DELETE CASCADE;
	级联更新：ON UPDATE CASCADE
	级联删除：ON DELETE CASCADE

数据库的设计

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

数据库备份及还原

1、 命令行：
		备份： mysqldump -u用户名 -p密码 数据库名称 > 保存的路径
		还原：
			1、 登录数据库
			2、 创建数据库
			3、 使用数据库
			4、 执行文件。source 文件路径