mysql基本操作

MySQL 三种数据类型：

　　 1、数值格式有哪

数值型分为两种： 1. 整形 2. 浮点型	整形常用有5类	非常小的整形	1字节：-128~127；0~255	Tinyint
		较小的整形	2字节：-32768~32767	Smallint
		中等大小整形	3字节：0~2²⁴	Mediumint
		标准整形	4字节：0~2³²	int
		大整形	8字节：0~2⁶⁴	Bigint
	浮点型有3类	Float(H,D)	4字节
		Double(H,D)	8字节
		Decinal(H,D)定点数	H+2字节

　　2、字符串格式有哪些

字符型分为4类	Char(n)	255(固定长度)	常用于长度不变的（如：性名列）索引快，但浪费空间
	varchar	255(可变长度)	节省空间但索引慢
	text	2¹⁶-1	文本数据（文章）
	blog		二进制数据（相片）

　　3、日期型

Date（日期）	YYYY-MM-DD
TIME（时间）	hh:mm:ss
DATETIME（日期和时间）	YYYY-MM-DD hh:mm:ss
TIMESTAMP（时间戳）	YYYYMMDDhhmmss
YEAR（年）	YYYY

MySQL常用增删改查命令：

　　1.创建数据库和表

　　　 1. 创建数据库

　　　　　　　　mysql> show databases;
　　　　　　　　mysql> create database tomdb charset utf8; #创建数据库tomdb，使用utf-8做字符编码
　　　　　　　　mysql> use tomdb #选择使用tomdb数据库
　　　　　　　　mysql> drop database tomdb;
　　　　　　　　mysql> show create database tomdb; # 查看创建数据库的语法

　　　　2.创建表结构

　　　　　　　　mysql> show tables
　　　　　　　　mysql> desc student;
　　　　　　　　mysql> drop table student;

mysql> create table student(               #在tomdb数据库中创建表：student
    -> id int auto_increment,              #在表中创建第一个字段：“id”
    -> name char(32) not null,             #在表中创建第二个字段：“name”
    -> age int not null,                   #在表中创建第三个字段：“age”
    -> register_data date not null,        #在表中创建第四个字段：日期
    -> primary key (id));                  #将表的主键设置为字段：“id
Query OK, 0 rows affected (0.10 sec)       #这里是创建成功的提示

　　2.插入数据

　　　　mysql> desc student; # 查看student表所有字段
　　　　mysql> insert into student(name,age,register_data) values("zhangsan",100,"2016-06-20");
　　　　mysql> select * from student; #查看student表有哪些数据

　　3、常用查询语句

　　　　1. 最基本查询语句

　　　　　　　　mysql> select * from student limit 2; #仅查看student表中前两行数据
　　　　　　　　mysql> select * from student limit 5 offset 3; #从第三行开始查询，并且只显示5行数据

　　　　2. where; like; order by;使用

　　　　　　　　mysql> select * from student where id >3 and age >103;
　　　　　　　　mysql> select * from student where register_data like "2016-06%"; #查询所有在2016-06这一条新建的条目
　　　　　　　　mysql> select * from student order by id desc; #按主键降续
　　　　　　　　mysql> select * from student order by id asc; #按主键升续排序（默认升续）
　　　　　　　　mysql> select * from student where name like binary "%si" order by id desc; #查找名字以“si”结尾的所有条目，并且按照id降续排列

　　　　3. GROUP BY 语句：指定以什么分组（比如可以统计出有多少同名数据）

　　　　　　　　mysql> select name,count(*) from student group by name;

　　　　　　　　mysql> select coalesce(name,"Total age"),sum(age) from student group by name with rollup;

　　　　4、修改（update）

　　　　　　mysql> update student set name="lisi",age=22 where id=1; #将表中id=1的条目改成name=lisi,age=22
　　　　　　mysql> update student set name="lisi",age=22 where id>4; #上面仅仅修改一条，这里修改id>4的所有

　　　　5、删除（delete）

　　　　　　mysql> delete from student where name="zhangsan"; #删除student表中所有name=“zhangsan”

删除，添加或修改表字段：

　　1、添加一个字段（add）

　　　　　　mysql> alter table student add sex enum("M","F"); #添加一个字段sex，只能用M，或者F
　　　　　　mysql> insert into student(name,age,register_data,sex) values("zhangsan",100,"2016-06-20","M");

　　　　　　mysql> ALTER TABLE user MODIFY COLUMN NAME VARCHAR(50) default null; # 修改字段属性
　　　　　　mysql> ALTER TABLE user CHANGE name new_name char(32); # 修改字段名称

　　2、删除一个字段（drop）

　　　　　　mysql> alter table student drop age; #删除student表的age字段

　　3、仅能修改一个字段的属性（modify）

　　　　　　mysql> alter table student modify sex enum("F","M") not null; #修改刚创建的student表属性不能为空

　　4、把字段的名字也能改掉（change）

　　　　　　mysql> alter table student change sex gender char(32) not null default "x"; #将sex字段名由sex改成gender，设置不能为空，默认值为“x”

　　5、修改表名

　　　　　　mysql> alter table student rename to student_table; #将表名从student修改成student_table

　　6、删除有依赖关系的表

　　　　　　导入sql文件前，取消外键检查：set foreign_key_checks=0;
　　　　　　导入sql文件后，加入外键检查：set foreign_key_checks=1;

　　7、django中，在已有表添加新字段和新外键关系手动解决migrate失败问题

　　　　1. 添加普通字段

　　　　　　　　#1、将notify_notifybytagrelation表添加一个新字段max_times，为int类型，不为空，默认值为0
　　　　　　　　alter table notify_notifybytagrelation add column max_times int not null default 0;

　　　　2. 创建外键关联的表

　　　　　　create table notify_tagnotifygroup(
　　　　　　id int auto_increment,
　　　　　　name char(255) not null,
　　　　　　notify_interval int not null default 0,
　　　　　　max_times int not null default 0,
　　　　　　primary key (id));

MySQL外键关联（一对多）：

　　1、外键说明　　　　

　　　　1. 什么是外键？

　　　　　　　　1）表A中有字段（如身份证号）做了主键，表B中也有这个字段（身份证号），这里的身份证号一定来自表A同样字段里的内容，但再B表中身份证号对应id可以重复
　　　　　　　　2）那么表B的这个字段就叫表A字段的外键，也就是两个表以身份证号这个字段建立了联系。

　　　　2. 外键作用

　　　　　　　　1）为了一张表记录的数据不要太过冗余。
　　　　　　　　2）保持数据的一致性、完整性。

　　　　　　　　一致性：外键的作用就是可以让身份证号保证是来自表A中，也就是保证了数据的一致性；
　　　　　　　　完整性：如果要删除A表中的某个身份证号，那么首先要删除B表中同样的身份证号，这保证了数据的完整性

　　2、创建学生表(student), 和学生每天上课记录表(student_record)

　　　　1. 创建student和student_record表

　　　　#1、student表
create table student(
id int auto_increment,
name char(32) not null,
age int not null,
register_data date not null,
primary key (id)) 
engine=InnoDB  
;

　　　　#2、student_record表
create table study_record (
  id int(11) auto_increment,
  day int NOT NULL,
  status char(32) NOT NULL,
  stu_id int(11) NOT NULL,
  primary key (id),
  CONSTRAINT fk_student_key FOREIGN KEY (stu_id) REFERENCES student (id)
)
engine=InnoDB  
;

　　　　2. 在student表中创建两条记录

　　　　　　　　mysql> insert into student(name,age,register_data) values("zhangsan",100,"2016-06-20");
　　　　　　　　mysql> insert into student(name,age,register_data) values("lisi",101,"2016-06-21");

　　　　3. 在student_record表中创建与student表的关联记录（day,status,stu_id）

　　　　　　　　mysql> insert into study_record (day,status,stu_id) values(1,"yes",1); # student表id=1第一天到了
　　　　　　　　mysql> insert into study_record (day,status,stu_id) values(1,"yes",2); # student表id=2第一天到了
　　　　　　　　mysql> insert into study_record (day,status,stu_id) values(1,"yes",3); # 会报错，因为student没id=3

　　　　4. 如果有student表中有student_record表关联的数据，你是不能删除student表中的记录（报错）

　　　　　　　　mysql> delete from student where name='lisi';

　　　　5. 查看刚刚创建study_record表结构创建记录

　　　　　　　　mysql> show create table study_record;

　　　　6. 使用左连接查询student表中学生每天上课记录

　　　　　　　　mysql> select name,day,status from student left join study_record on student.id=study_record.stu_id;

　　3、django在model中添加一对多字段后migrate报错，手动解决冲突

　　　　1. 添加普通字段(django model中添加 max_times 字段)

　　　　　　　　alter table notify_notifybytagrelation add column max_times int not null;

　　　　2. 创建外键关联的表（django model添加了notify_tagnotifygroup表）

create table notify_tagnotifygroup(
id int auto_increment,
name char(255) not null,
notify_interval int not null,
max_times int not null,
primary key (id));

　　　　3. 添加外键（django model中已有表的group_notify字段关联了2中的表，一对多）

　　　　　　　　1）添加字段（这个字段作为本表外键）　　　　　　　　

　　　　　　　　　　alter table notify_notifybytagrelation add column group_notify_id int;

　　　　　　　　2）创建外键关系（将上面创建的 group_notify_id 外键添加外键关系）　　　　　　　　

　　　　　　　　　　# 说明：notify_notifybytagrelation 表中的group_notify_id作为外键关联notify_tagnotifygroup表的主键id

　　　　　　　　　　alter table notify_notifybytagrelation add foreign key(group_notify_id) references notify_tagnotifygroup(id);

　　4、mysql手动创建和删除外键约束

　　　　参考博客：https://blog.csdn.net/wyswlp/article/details/8881103

MySQL连接查询：两个表之间外键关联：

　　1. left join (左连接：两个表的差集)

　　　　　1、左连接where只影向右表,所以左表(student)中数据全部显示，右表study_record表中不符合where条件的数据不会显示
　　　　　2、select * from student left join study_record on student.id=study_record.stu_id;

　　2. right join (右连接：两个表的差集)

　　　　　1、右连接where只影向左表,所以左表(student)中不符合where条件的数据不会显示，右表study_record表内容全部显示
　　　　　2、select * from student right join study_record on student.id=study_record.stu_id;

　　3. inner join (内连接：两个表的交集)

　　　　　inner join：理解为“有效连接”，两张表中都有的数据才会显示left join
　　　　　select * from student inner join study_record on student.id=study_record.stu_id; # 等价于面这条语句
　　　　　select * from student,study_record where study_record.stu_id = student.id;

　　4. Full join（两个表的并集）

　　　　　select * from a FULL JOIN b on a.a = b.b; # MySQL不支持这个命令（可以使用下面语句代替，两行是一个语句）

　　　　　select * from student left join study_record on student.id=study_record.stu_id UNION
　　　　　select * from student right join study_record on student.id=study_record.stu_id;