MySQL数据库-知识笔记

命名规则:字母, 数字, 下划线 (命名不区分大小写)

 dir :查询当前磁盘目录  ;exit : 结束sql服务

SQL语句根据其功能被分为四大类：DDL(定义)、DML(操作,即增删改查)、DQL(查询)、DCL(控制)

一.数据库设计-DDL(定义)

1. 数据库操作

1.1 MySQL 登陆

 mysql -u root -p 

1.2 查询数据库

 show databases;

1.3创建数据库

 create database 数据库名;

 create database if not exists 数据库名;
 [if not exists] : 数据库不存在,则创建该数据库; 如果存在则不创建

1.4 使用数据库

use 数据库名;

1.5 删除数据库

 drop database 数据库名;

 drop database if exists 数据库名;
 [if exists] : 数据库存在,则执行删除;如果不存在,不执行

2. 表操作

表结构的操作也是包含四个部分：创建表、查询表、修改表、删除表。

2.1建表

create table 表名 (
   字段1 字段1类型 [约束] [comment 注释],
   字段2 字段2类型 [约束] [comment 注释],
   ...
   字段n 字段n类型 [约束] [comment 注释]
 );
 [comment] : 后边跟字段注释


示例:

  create table xx (
      id int comment '编号',
      name varchar(20) comment '姓名',
      age int comment '年龄'
     );

2.2 约束

约束	描述	关键字
非空约束	限制该字段值不能为null	not null
唯一约束	保证字段的所有数据都是唯一、不重复的	unique
主键约束	主键是一行数据的唯一标识，要求非空且唯一	primary key
默认约束	保存数据时，如果未指定该字段值，则采用默认值	default
外键约束	让两张表的数据建立连接，保证数据的一致性和完整性	foreign key

示例:

primary key
-- 1.主键不能重复
 insert into t1 values (1,'许一');
 报错: [23000][1062] Duplicate entry '1' for key 'PRIMARY' 
   
-- 2.主键不能为空
 insert into t1(name) values ('许影');
 报错: [HY000][1364] Field 'id' doesn't have a default value  

-- 3.主键字符类型不能为字符串类型
 报错: [42000][1063] Incorrect column specifier for column 'id' 

 auto_increment(主键自增,需要搭配主键约束使用)

 1.指定字段进行插入
 insert into t2(name) values ('许影');

 2.插入整条记录, 给主键一个null
 insert into t2 values (null,'张三')

 unique 唯一约束
 报错: [23000][1062] Duplicate entry '许一' for key 'name'

删除表数据 (delete from 和 truncate 的区别 )

 1.delete from 表名; (删除数据后新建表主键id依旧按照上一张表的最大值往下排序)
 ​
 2.truncate 表名; (清空数据,新建表主键id重新开始, 相当于进行了 drop + create)

2.3 数据类型

常用数据类型

tinyint
int
decimal(M,D)	示例:`height decimal(5,2) comment '身高',即最大可显示999.99`
datetime :  年月日时分秒	示例:`1000-01-01 00:00:00 至 9999-12-31 23:59:59`
char : 固定长度字符串
varchar : 变长类型字符串
text : 变长类型字符串
enum : 枚举类型,只能有一个值	示例: `gender enum('男','女','未知') comment '性别'`

注: char 与 varchar 都可以描述字符串，char是定长字符串，指定长度多长，就占用多少个字符，和字段值的长度无关 。而varchar是变长字符串，指定的长度为最大占用长度 。相对来说，char的性能会更高些。

示例:

 用户名 username ---长度不定, 最长不会超过50
 username varchar(50)
   
 手机号 phone ---固定长度为11
 phone char(11)

大致分为三类:数值类型 , 日期/时间类型 , 字符串类型

数值类型

类型	大小	有符号(SIGNED)范围	无符号(UNSIGNED)范围	描述
tinyint	1byte	(-128，127)	(0，255)	小整数值
DECIMAL		依赖于M(精度)和D(标度)的值	依赖于M(精度)和D(标度)的值	小数值(精确定点数)
int/integer	4bytes	(-2147483648，2147483647)	(0，4294967295)	大整数值
bigint	8bytes	(-2^63，2^63-1)	(0，2^64-1)	极大整数值
float	4bytes	(-3.402823466 E+38，3.402823466351 E+38)	0 和 (1.175494351 E-38，3.402823466 E+38)	单精度浮点数值
double	8bytes	(-1.7976931348623157 E+308，1.7976931348623157 E+308)	0 和 (2.2250738585072014 E-308，1.7976931348623157 E+308)	双精度浮点数值

日期/时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

类型	大小	范围	格式	描述
date	3	1000-01-01 至 9999-12-31	YYYY-MM-DD	日期值
time	3	-838:59:59 至 838:59:59	HH:MM:SS	时间值或持续时间
year	1	1901 至 2155	YYYY	年份值
datetime	8	1000-01-01 00:00:00 至 9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
timestamp	4	1970-01-01 00:00:01 至 2038-01-19 03:14:07	YYYY-MM-DD HH:MM:SS	混合日期和时间值，时间戳

示例 :

 生日字段 birthday ----年月日  
 birthday date

字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET

类型	大小	用途
char	0-255 bytes	定长字符串
varchar	0-65535 bytes	变长字符串
text	0-65 535 bytes	长文本数据
tinytext	0-255 bytes	短文本字符串

2.4 删除数据表

 drop table 表名;

2.5 修改数据表

2.5.1 修改数据类型

 alter table 表名 modify  字段名  新数据类型(长度);
 alter table 表名 change  旧字段名  新字段名  类型(长度)  [comment 注释]  [约束];

案例：修改qq字段的字段类型，将其长度由11修改为13

 alter table tb_emp modify qq varchar(13) comment 'QQ号码';

案例：修改qq字段名为 qq_num，字段类型varchar(13)

 alter table tb_emp change qq qq_num varchar(13) comment 'QQ号码';

2.5.2 删除字段

 alter table 表名 drop 字段名;

案例：删除tb_emp表中的qq_num字段

 alter table tb_emp drop qq_num;

2.5.3 修改表名

 rename table 表名 to  新表名;

案例：将当前的tb_emp表的表名修改为emp

 rename table tb_emp to emp;

2.6 查看数据表

查看表结构 :

 desc 表名;

查看建表语句 :

  show create table 表名;

取消当前命令

 \c
 ctrl + c 

非负,无符号

 unsigned ,防止出现负数
 示例: id int unsigned

二.数据库操作-DML(操作)

1.增加操作

向指定字段添加数据

 insert into 表名 (字段名1, 字段名2) values (值1, 值2);

全部字段添加数据

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

批量添加数据（指定字段）

 insert into 表名 (字段名1, 字段名2) values (值1, 值2), (值1, 值2);

批量添加数据（全部字段）

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

示例:

 -- 1.添加整条数据
 insert into t values (2,'李四',20,'女','2001-01-01',175);
 -- 2.添加指定字段
 insert into t(id, name) values (3,'王五');
 -- 3.添加多条数据
 insert into t(id,name,age) values (4,'马六',20),(5,'田七',21),(6,'许一',23);

2.修改操作

 update 表名 set 字段名1 = 值1 , 字段名2 = 值2 , .... [where 条件] ;

示例:

 -- 1.修改单个字段
 update t set age = 30 where id = 1;
 -- 2.修改多个字段
 update t set age = 18, gender = '男',birthday = '2002-02-02', height = 180 where id = 3;

3.删除操作

 delete from 表名  [where  条件] ;

示例:

 删除单条信息(注意添加条件)
 delete from t where id = 4;

三.数据库操作-DQL(查询)

1.基本查询

查询多个字段

 select 字段1, 字段2, 字段3  from  表名;
 示例:
 select id,name from t;

查询所有字段（通配符）

 select *  from  表名;

设置别名

 select 字段1 [ as 别名1 ] , 字段2 [ as 别名2 ]  from  表名;
 示例:
 select id as '编号',name as '姓名' from t;

去除重复记录

 select distinct 字段列表 from  表名;
 示例:
 select distinct * from t;

2.条件查询

 select  字段列表  from   表名   where   条件列表 ; -- 条件列表：意味着可以有多个条件

常用的比较运算符

比较运算符	功能
>, >=, <, <=, =	比较大小
<> 或 !	不等于
between ... and ...	在某个范围之内(含最小、最大值)
in(...)	在in之后的列表中的值，多选一
like 占位符	模糊匹配(_匹配单个字符, %匹配任意个字符)
is null	是null

常用的逻辑运算符

逻辑运算符	功能
and 或 &&	并且 (多个条件同时成立)
or 或 ||	或者 (多个条件任意一个成立)
not 或 !	非 , 不是

示例：
 -- 1.> 大于
 select * from t5 where id > 2 ;
 -- 2.>= 大于等于
 select * from t5 where id >= 2 ;
 -- 3.< 小于
 select * from t5 where id < 2 ;
 -- 4.<= 小于等于
 select * from t5 where id <= 2 ;
 -- 5.= 等于
 select * from t5 where id = 2 ;
 -- 6.<> or !=  不等于
 select * from t5 where id != 2 ;
 -- 7.between...and...  在某个范围内
 select * from t5  where id between 2 and 4 ;
 -- 8.in(...)
 select * from t5 where id in (2,3,5);
 -- 9.like 占位符 模糊匹配
 -- %不限位数
 select * from t5 where username like '%三';
 -- _限一位数
 select * from t5 where username like '_三_';
 -- 10. is null / is not null
 select  * from t5 where age is null;

3.聚合函数

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

函数	功能
count	统计数量 : 按照列去统计有多少行数据。
max	最大值 : 算指定列的最大值
min	最小值 :计算指定列的最小值
avg	平均值 : 计算指定列的平均值
sum	求和 : 计算指定列的数值和，如果不是数值类型，那么计算结果为0

注意: avg( )函数计算平均值时,如果一个字段值为空 ,则这个字段不参与计算

 示例:
 alter table t5 add column asset int default 0  comment '资产（元）' after age;
 -- 1、统计函数 count（）统计有多少行数据
 select count(*) from t5;
 -- 2、求和函数 sum() 计算指定列的数值和，如果不是数值类型，那么计算结果为0
 select sum(asset) as 总资产 from t5;
 -- 3、求平均值函数 avg() 计算指定列的平均值 但如果一个字段值为空 ,则这个字段不参与计算
 select avg(asset)  from t5;
 -- 4、最值函数 max() min() 最大值、最小值
 select max(age) as 最大年龄 from t5;
 select min(age) as 最小年龄 from t5;
 ​

4.分组查询

分组： 按照某一列或者某几列，把相同的数据进行合并输出。

 select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件]
 示例:
 select  gender 性别, max(age) 最大年龄 ,min(age) 最小年龄, avg(age) 平均年龄 from t5 group by gender;

where与having区别（面试题）

1. 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。

2. 判断条件不同：where不能对聚合函数进行判断，而having可以。

5.排序查询

 select  字段列表  
 from   表名   
 [where  条件列表] 
 [group by  分组字段 ] 
 order  by  字段1  ASC(升序) , 字段2  DESC(降序) … ;
 示例：
 select * from t5 order by age asc; 
 select * from t5 order by age desc;

6.分页查询

 -- 分页 limit start(省略表示从0开始),length;
 select * from student limit 开始，每页的条数；
 ​
 示例:select * from t5 limit 0,3; 后面只能跟明确数字

7.多表查询

7.1 内连接

 select  字段列表   from   表1  [ inner ]  join 表2  on  连接条件 ... ;

7.2 外连接

外连接分为两种：左外连接 和 右外连接。

左外连接语法结构：

 select  字段列表   from   表1  left  [ outer ]  join 表2  on  连接条件 ... ;

左外连接相当于查询表1(左表)的所有数据，当然也包含表1和表2交集部分的数据。

右外连接语法结构：

 select  字段列表   from   表1  right  [ outer ]  join 表2  on  连接条件 ... ;

右外连接相当于查询表2(右表)的所有数据，当然也包含表1和表2交集部分的数据。

示例：
 create table goods(
     id int primary key auto_increment,
     name varchar(20),
     good_id tinyint unsigned
 );
 create table goods_fl(
     id int primary key auto_increment,
     good_name varchar(20)
 );
 -- 1.内连接
 select g.id , g.name, g.good_id ,g2.good_name from goods g inner join goods_fl g2 on g.good_id = g2.id;
 -- 2.左连接
 select g.id , g.name, g.good_id ,g2.good_name from goods g left join goods_fl g2 on g.good_id = g2.id;
 -- 3.右连接
 select g2.good_name, g.id , g.name, g.good_id from goods g right join goods_fl g2 on g.good_id = g2.id;
 ​

7.3 子查询

SQL语句中嵌套select语句，称为嵌套查询，又称子查询。

 SELECT  *  FROM   t1   WHERE  column1 =  ( SELECT  column1  FROM  t2 ... );

未完待续。。。。