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 文件路径