MySql学习与总结（一）

再小的帆也能远航，不断学习，不断进步！
具体文档参考mysql官网文档，官方文档是学习的最好方式！
https://dev.mysql.com/doc/ref...

总结一下工作中常用的基本sql和命令

基本的命令行操作

命令行连接数据库
mysql -h地址 -u用户名 -P端口 -p密码 
查看当前数据库的版本
select version();
显示当前数据库中的所有表
show tables;
查看表结构
desc 表名;
查看建表语句
show create table 表名\G;

操作数据库语句

创建数据库
create database if not exists 数据库名;
删除数据库 
drop database if exists 数据库名;
使用数据库 
use `数据库名`;
查看数据库 
show databases;  查看所有的数据库
select database(); 查看当前位于哪个数据库
show create database 数据库名\G; 查看建库语句

数据库的列类型

数值
int 标准的整数
decimal 字符串形式的浮点数

字符串
varchar 可变字符串 0-65535 常用的变量
text 文本串 2^16-1 保存大文本

时间
datatime YYYY-MM-DD HH:mm:ss 最常用的时间格式
timestamp 时间戳 1970.1.1 到现在的毫秒数
year
年
Null
空值

操作数据表语句

创建表

CREATE TABLE IF NOT EXISTS `student`(
 `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
 `name` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT'姓名',
 `pwd` VARCHAR(30) NOT NULL DEFAULT '123456' COMMENT '密码',
 `sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
 `birthday` DATETIME DEFAULT NULL COMMENT'生日',
 `address` VARCHAR(100) DEFAULT NULL COMMENT 'j家庭地址',
  `email` VARCHAR(20) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

修改表

修改表名 
alter table `旧表名`  rename as `新表名`;
增加表的字段 
alter table `表名` add `字段名` 列属性;
修改表的字段
alter table `表名`  modify  `字段名` 新约束;    只能修改约束
alter table `表名`  change `旧字段名`  `新字段名` [新|旧约束]；   既可以修改字段名也可以修改约束
删除表的字段
alter table `表名` drop `字段名`;

删除表

drop table if exists `表名`;

查看表

查看表结构
desc `表名`;
查看建表语句
show create table `表名`\G;

MyISAM和INNODB的区别

	MyISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
全文索引	支持	不支持
外键索引	不支持	支持
表空间的大小	较小	较大，约为2倍

数据库级别的外键(物理外键)

DROP TABLE IF EXISTS `student`;
DROP TABLE IF EXISTS `grade`;
-- 创建年级表（主表）
CREATE TABLE IF NOT EXISTS `grade`(
 `grade_id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '年级id',
 `grade_name` VARCHAR(20) NOT NULL COMMENT '年级名称',
  PRIMARY KEY(`grade_id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 创建学生表，创建表的时候添加外键引用
-- 学生表（从表）的grade_id字段要去引用年级表（主表）的grade_id字段
CREATE TABLE IF NOT EXISTS `student`(
 `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
 `name` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT'姓名',
 `pwd` VARCHAR(30) NOT NULL DEFAULT '123456' COMMENT '密码',
 `sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
 `birthday` DATETIME DEFAULT NULL COMMENT'生日',
 `address` VARCHAR(100) DEFAULT NULL COMMENT 'j家庭地址',
  `email` VARCHAR(20) DEFAULT NULL COMMENT '邮箱',
  `grade_id` INT(4) NOT NULL  COMMENT '年级id',
  PRIMARY KEY(`id`),
  KEY `FK_id` (`grade_id`),   -- 定义学生表的grade_id为key
  -- 约束 定义的grade_id 为外键同时引用年级表的grade_id字段
  CONSTRAINT `FK_id` FOREIGN KEY (`grade_id`) REFERENCES `grade`(`grade_id`) 
  )ENGINE=INNODB DEFAULT CHARSET=utf8; -- constraint 约束  foreign key  外键  refrences 引用  
-- 删除有外键引用的表时，必须先删除从表再删除主表
DROP TABLE IF EXISTS `student`;
-- 创建表后添加外键引用
CREATE TABLE IF NOT EXISTS `student`(
 `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
 `name` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT'姓名',
 `pwd` VARCHAR(30) NOT NULL DEFAULT '123456' COMMENT '密码',
 `sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
 `birthday` DATETIME DEFAULT NULL COMMENT'生日',
 `address` VARCHAR(100) DEFAULT NULL COMMENT 'j家庭地址',
  `email` VARCHAR(20) DEFAULT NULL COMMENT '邮箱',
  `grade_id` INT(4) NOT NULL  COMMENT '年级id',
  PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
-- 添加外键约束
ALTER TABLE `student` 
ADD CONSTRAINT `FK_id` FOREIGN KEY (`grade_id`) REFERENCES `grade` (`grade_id`);
DESC `student`;

DML语言（数据操作语言）

insert语句

INSERT INTO `grade`(`grade_name`) VALUES('大一');
-- 插入的时候字段和值要一一对应，字段是自增的时候可以省略

update语句

-- update 修改 
UPDATE `grade` SET `grade_name`='大二' WHERE grade_id=1;
-- 需要修改多个字段，使用逗号隔开

delete语句

-- delete 删除 
DELETE FROM `grade` WHERE grade_id = 1;

delete和truncate的区别

• 相同点
  都能删除数据，都不会删除表结构

• 不同点

  • truncate重新设置自增列，计数器会归零
  • truncate 不会影响事务

DQL(数据查询语言)

基本的Select语句

-- 查询所有数据  
SELECT * FROM `student`;
-- 查询指定字段 
SELECT `name` ,`pwd` FROM `student`;
-- 起别名 ，结果、字段、表都可以起别名，可以使用as，也可以省略 
SELECT `name` AS 姓名,`pwd` AS 密码 FROM `student`;
SELECT s.name AS 姓名 , s.pwd AS 密码 FROM `student` AS s;
-- 函数concat(a,b)
SELECT CONCAT("学生姓名:",`name`) AS 新姓名  FROM `student`;
-- 去重  
SELECT DISTINCT `name` FROM `student`;

Where子句

算术运算符

算术运算符	含义
=	等于
>	大于
<	小于
>=	大于等于
<=	小于等于
!= <>	不等于
and	且
or	或
between ...and	在某个区间内

逻辑运算符

逻辑运算符	含义
&&	与
双竖线	或
not	非

模糊查询-比较运算符

比较运算符	含义
is null	null值
is not null	不为null值
like	匹配
in	集合

-- like 模糊查询  
-- like 结合 %（0-任意个字符） _(一个字符) 
SELECT `name` ,`pwd` FROM `student` WHERE `name` LIKE '刘%';
SELECT `name` ,`pwd` FROM `student` WHERE `name` LIKE '刘_';
-- in  集合  
SELECT `name`,`pwd` FROM `student` WHERE `id` IN (1,2,3);
-- is null   空  
SELECT `name` ,`id` FROM `student` WHERE `psd` IS NULL;
-- is not null  不为空 
SELECT `name` ,`id` FROM `student` WHERE `psd` IS NOT NULL;

联表查询

	操作	描述
inner join	等值联结	只显示符合条件的记录
left join	左联结	左表的记录会全部显示，右表只会显示符合搜索条件的记录
right join	右联结	右表的记录会全部显示，左表只会显示符合搜索条件的记录

1:分许查询的字段都来自哪些表
2：确定交叉点（两个表中的哪些数据是相同的）
3：找到判断的条件

分页和排序

order by 排序的字段    asc 升序  desc  降序
limit 起始值，页面大小    
起始值=（n-1）*页面大小   n为当前页  
数据总数/页面大小 = 总页数  

聚合函数

聚合函数	含义
count（）	统计
sum()	求和
max()	最大值
avg()	平均值
min()	最小值

三者的比较
count(*)	包括了所有列，相当于行数	统计结果不会忽略列值为null的
count(1)	忽略了所有列，用1代表代码行	统计结果不会忽略列值为null的
count(列名)	只包括列名那一列	统计结果会忽略列值为null的

分组函数
group by 有一个原则,就是select 后面的所有列中,没有使用聚合函数的列,必须出现在 group by 后面（重要）

区别
having	在分组之后过滤数据，条件中可以使用聚合函数
where	在分组之前过滤数据，条件中不能使用聚合函数