MySql学习

仅供个人参考

关系模型

主键

选取主键的基本原则：
– 不适用任何业务相关的字段作为主键（如：身份证号等）

常见可作为id字段的类型有：
1、自增整数类型，int自增类型，可以记录21亿条，bigint类型可以记录最多922亿亿条。
2、全局唯一GUID类型。GUID算法通过网卡MAC地址、时间戳和随机数保证任意计算机在任意时间生成的字符串都是不同的，可以自己预算出主键。

没有必要的情况下，尽量不适用联合主键，他会给关系表带来复杂度的上升。

外键

把数据与另一张表关联起来的，这种列称为外键
foreign key（）

删除外键：
ALTER TABLE 表名
DROP FOREIGN KEY 外键

注意：删除外键约束并没有删除外键这一列。
删除列：DROP COLUMN ...

小结：
关系数据库通过外键可以实现一对多、多对多和一对一的关系。
外键既可以通过数据库来约束，也可以不设置约束，仅依靠应用程序的逻辑来保证。

索引

索引时关系数据库中对某一列或多个列的值进行预排序的数据结构。
通过使用索引，可以让数据库系统不必扫描整个表，而是直接定位到符合条件的记录，可以大大加快查询速度。

使用：
ALTER TABLE 表名
ADD INDEX idx_score(score)；

索引名称是任意的，索引如果有多列，可以在括号里依次写上。

索引优点：
提高查询效率
缺点：
在插入、更新和删除记录时，需要同时修改索引，因此，索引越多，插入、更新和删除记录的速度就越慢。

唯一索引
ALTER TABLE 表名
ADD UNIQUE INDEX idx_score(score)；
使用UNQUE关键字就添加了一个唯一索引，可以保证某一列的值具有唯一性。

查询数据

1、基本查询：
--表示注释
select * from <表名>
或
select 1 --用于测试数据库连接

select查询的结果是一个二维表

2、条件查询
SELECT * FROM <表名> WHERE <条件表达式>

条件表达式：> , < , =等

<条件1> or <条件2> 表示满足条件1或者满足条件2
<条件1> and <条件2> 满足条件1和条件2
not <条件> 不符合该条件

优先级：NOT > AND > OR

注：between and 是包括边界值的。

3、投影查询

使用：
select 列1 <别名1>， 列2<别名2>， 列3<别名3> from <表>
返回结果集只包含了我们指定的列

4、排序
使用ORDER BY语句来对查找的结果进行排序。

select id,name,gender,score from students order by score;

可以在后面加上 desc表示“倒序”

默认排序规则是ASC：升序，可以省略

5、分页查询

分页实际上就是从结果集中“截取”出第M~N条记录。

语句：
limit <n-m> offset <m>

分页查询的关键在于：
确定每页需要显示的结果数量pageSize
然后根据当前页的索引pageIndex(从1开始)，确定linit和offset应该设定的值:

• LIMIT总是设定未pageSize；
• OFFSET计算公式为pageSize*（pageIndex-1）

OFFSET超过了查询的最大数量并不会报错，而是得到一个空的结果集。

如：limit 3 offset 0 表示，对结果集从0号记录开始，最多取3条。

6、聚合查询

函数	说明
COUNT()	查询列的行数
SUM()	计算某一列的合计值，该列必须为数值类型
AVG()	计算某一列的平均值，该列必须为数值类型
MAX()	计算某一列的最大值
MIN()	计算某一列的最小值

对于max和min，如果对象是字符类型，则会返回排序最后和排序最前的字符。

分组

select count(*) num from students group by class_id;

7、多表查询
select * from <表1> <表2>

查询结果为两表的“乘积”。
表1的每一行与表2的每一行都凉凉拼在一起返回。
结果集的列数是表1和表2的列数之和，行数是表1和表2的行数之积。

给表或是列起别名

select s.id sid,
		s.name,
		s.gender,
		s.score,
		c.id cid,
		c.name cname
		from students s, classes c;

8、连接查询

连接查询对多个表进行join运算.
先确定一个主表作为结果集,然后,把其他表的行有选择性地"连接"在主表结果集上.

inner join(内连接)
–选出所有学生,同时返回班级名称

select s.id, s.name,s.class_id,c.name class_name,s.gender,s.score from students s
inner join classes c on s.class_id = c.id

inner join 查询写法:
1.确定主表,from <表1>
2.确定需要连接地表,inner join <表2>
3.确定连接条件,使用on <条件...>
4.可选:加上where子句,oder by等子句

内连接:返回同时存在与两张表的行数据
right outer join : 返回右表都存在的行数据,不够就以NULL填充
left outer join : 返回左表都存在的行数据
full outer join : 返回两张表的所有记录全部选中出来.

select ... from <table1> inner join <table2> on <conditions...>

修改数据

增删改查: create , retrieve, update, delete

insert 插入数据

语法:
insert into <tableName> (字段1,字段2,...) values(值1,值2,...);

值的顺序必须和字段顺序一致.

update更新数据

语法:
update <tableName> set 字段1=值1,字段2=值2,...where...;

注:当没有where条件时,整个表的记录都会被更新.

delete数据

语法:
delete from <tableName> where ...

同update一样,没有条件时,可能会删除整个表的数据.

管理MySQL

进入数据库：
以管理员的身份打开cmd，输入net start mysql
mysql数据库启动后，输入mysql -uroot -p 回车，然后输入密码即可

列出所有数据库:
show databases;
其中,information_schema, mysql , performance_schema 和sys是系统库,不要去改动他们.

数据库
创建数据库:create database 数据库名称;
删除数据库: drop database 数据库名称
使用改数据库:use 数据库名称

表
列出当前数据库的所有表: show tables;
查看一个表的结构:desc student;
查看创建表的SQL语句:show create table students;
创建表: create table;
删除表:drop table

增加一列birth: alter table students add column birth varchar(10) not null;

修改birth列: alter table students change column birth birthday varchar(20) not null;

退出Mysql: exit

使用sql语句

插入或替换(replace)

插入一条新记录,若记录存在,则先删除原数据,再插入新纪录
若没有记录相同,则直接插入新记录

replace into students (id,class_id,name,gender,score) values(1,1,'小明','F',99);

插入或更新

插入数据，记录存在，则更新，否则直接插入新记录。

insert into ... on duplicate key update...

插入或忽略

插入数据，若记录存在，则忽略，任何事都不干。否则插入新数据

insert ignore into ...

快照

快照即复制一份当前表的数据到一个新表。

结合create table 和select

create table students_of_class1 select * from students where class_id = 1;
新创建的表结构和select使用的表结构完全一致。

事务

把多条语句作为一个整体进行操作的功能，称为数据库事务

数据库事务具有ACID这4个特性：

• A：atiomic，原子性，将所有sql作为原子工作单元执行，要么全部执行，要么全部不执行。
• C：consistent，一致性，事务完成后，所有的数据状态都是一致的。
• I：isolation，隔离性，如果有多个事务并发执行，每隔事务做出的修改必须与其他事务隔离；
• D：duration，持久性，即事务完成后，对数据库的修改被持久存储。

显示事务: begin开启一个事务，commit提交一个事务

begin
update accounts set balance = balance -100 where id = 1;
update accounts set balance = balance + 100 where id = 2;
commit;

commit语句执行失败，则整个事务也会失败。

使用rollback回滚事务，整个事务会失败，将commit改为rollback即可。

数据库操作

常用数据库操作命令

• 查看所有数据库：show databases; ----一定要有";"，不然会报错
• 使用数据库：use 数据库名;
• 查看当前使用的数据库：select database();
• 创建数据库：create database 数据库名 charset=utf8；
• 删除数据库：drop database 数据库名；
• 注释：“-- 被注释的内容”，–后面有个空格 快捷键 ctrl + /
• 运行快捷键：ctrl + 回车键 ，运行时，鼠标要放在需要运行的语句位置
• 输入命令不区分大小写

数据库数据类型

• 整数 int
• 小数 decimal(几位数，几位小数)
• 字符串 varchar(几位)
• 日期 datetime

常见的约束及其作用

• 主键： primary key，只能有1个主键
• 外键：维护多表的关联
• 非空
• 默认值
• 唯一：有多个唯一，手机号唯一，银行卡唯一等

数据表的操作

1. 查看当前数据库的所有表：show tables;
2. 查看表结构 ：desc 表名;
3. 查看表的创建语句：show create table 表名;
4. 创建数据表：create table 表名(
   字段名1 类型 可选约束，
   字段名2 类型 可选约束，
   … … …，);
   Unsigned ----> 无符号，没有负数
   decimal(5,2) ----> 5个数字，2位小数
   主键： id int unsigned primary key auto_increment
5. 删除表
   （1）方法一：drop table 表名;
   限制：表存在，才会删除，不存在，会报错
   （2）方法二：drop table if exisit 表名;
   限制：表存在，才会删除，不存在，不删除
6. 插入数据
   主键说明：主键自增长，可以用0或null代替
   方式一：Insert into 表名 values(…) ----# value后加不加s都一样
   方式二：insert into 表名 (字段1，字段2，…) value(值1，值2，…) ----# value后加不加s都一样
   方式三：添加多行数据
   Insert into 表名 values(…),(…),…,(…);
   Insert into 表名(字段1，字段2，…) value(值1，值2，…) ,value(值1，值2，…),value(值1，值2，…);
7. 查找所有数据：Select * from 表名; #其中"*" 代表所有
8. 修改数据
   Update 表名 set 字段名1=值1，字段名2=值2… Where 条件
9. 删除数据
   Delete from 表名 where 条件
   eg：delete from students where id=6 or id=7;
   物理删除，真删，删除后无法找回
   若未添加过滤条件，则会删除整个表
   逻辑删除：假删，通过设定一个字段来标识当前记录已经删除
   is_delete字段来标识，1代表删除，0代表未删除
   1代表删除，不给用户看，用户看不到，以为删除
   0表示未删除，给用户看，用户看得到，以为数据在
   查看：select * from 表 where is_delete = 0;
   truncate用法： truncate table 表名;
   drop 用法 ：drop table if exist 表名;
   

数据查询操作

1. 查询某个字段：select 字段1 as 别名1，字段2 as 别名2，… from 表名
   注：取别名只能当前语句生效

2. 查询某个字段且去重：select distinct 字段1 as 别名1，字段2 as 别名2，… from 表名

3. 条件查询，比较运算符：
   等于 =
   大于 >
   小于 <
   不等于 != 或 <>
   Select 字段 from 表名 where 字段 = '值 ';
   Select * from 表名 where 字段 < '值 ';

4. 条件查询，逻辑运算符：
   

5. 条件查询，模糊查询：
   关键词：like
   匹配任意多个字符 ：%
   匹配一个任意字符：_
   eg:
   select * from 表名 where 字段 like ‘值%’;
   select * from 表名 where 字段 like ‘值_’;
   select * from 表名 where 字段 like ‘%值_’;
   select * from 表名 where 字段 like ‘%值%’;

6. 条件查询，范围查询
   In 表示在一个非连续的范围内
   Eg：查询家乡是北京或者上海或广东的学生
   Between … And … 表示在一个连续的范围内
   Eg：查询年龄为18至20的学生

7. 条件查询：空判断 is null

8. 条件查询：非空判断 is not null
   在mysql中，null表示空，’ '表示空字符

9. 排序 order
   语法： select * from 表名 order by 字段1 asc|desc,字段2 asc|desc,…
   说明：
   将行数据按照字段1进行排序，如果某些字段1的值相同时，则按照字段2排序，以此类推：
   默认按照列值从小到大排序
   asc从小到大排序，也就是升序
   desc从大到小排序，也就是降序

10. 聚合函数
    使用聚合函数方便进行数据统计，一般配合分组使用，聚合函数不能作为where条件(只能查找出字段名，无法查找出其他字段)

(1)count(*|字段名): 查询总记录数
	eg：select count(*) from students;
(2)max(字段名):查询最大值
	eg：select max(age) from students where sex='女';
(3)min(字段名):查询最小值
	eg：select min(age) from students where sex='男';
(4)sum(字段名)：求和
	eg：select sum(age) from students where home='北京';
(5)avg(字段名)：求平均数

分组查询

分组

• 按照字段进行分组，此字段相同的数据回被放到一个组中
• 分组的目的是对每一组的数据进行统计(使用聚合函数)
• 语法格式：
  select 字段1，字段2，聚合函数… from 表名 group by 字段1，字段2，…
  eg：
  1.查看各种性别的人数
    注意：若分组的目的是对每一组数据进行统计，而不会显示，如：select * from students group by sex; ---- 查找到后，只会显示个分组的第一个学生的全部信息
    使用：select sex from students group by sex; ---- 会显示分组的类别，例如此处只会显示 男和女
  所以查看各种性别的人数还需要count()函数来进行统计：
    使用：select sex,count( * ) from students group by sex;

2.查询各种性别的人数，每组最大年龄，每组最小年龄
 语句：select sex,count(*) max(age),min(age) from students group by sex;

3.查询每个班级中各种性别的人数，各种性别的最小年龄
 —每个班级：按班级分组，各种性别：再按照性别分组
 —人数：count(),最小年龄min(age)
语句：select class,sex,count(),min(age) from students group by class,sex;

分组后的数据筛选

语法：select 字段1，字段2，聚合… from 表明 字段1，字段2，字段3，…,having
说明：关键字having候选的条件运算符与where的相同
eg：
1.查询男生总人数(使用了分组)
  —分组过滤，group by后面的语句不能用where，需要用having
语句：select sex, count() from students group by sex having sex=‘男’;
或者将where放在group by 前面，既可以使用where
语句：select sex,count() from students where sex =‘男’ group by sex;
总结：分组后用having，分组前用where

2.查询男生总人数(不适用分组)
语句：select sex,count(*) from students where sex=‘男’;

3.查询各个班级学生的平均年龄、最大年龄、最小年龄
 —各个班级：已班级分组
 平均年龄avg(age)，最大年龄max(age),最小年龄min(age)
语句：select class,avg(age),max(age),min(age) from students group by class;

4.查询1班除外，其他班级学生的平均年龄，最大年龄，最小年龄
 --除1班外，其他班级：以班级分组，过滤不要1班
语句：select class,avg(age),max(age),min(age) from students group by class having class !=‘1班’;
或者：select class,avg(age),max(age),min(age) from students where class !=‘1班’ group by class;