03: MySQL基本操作

MySQL其他篇

1.1 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

1.2 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;

create table student(
id int auto_increment,
name char(32) not null,
age int not null,
register_data date not null,
primary key (id));

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)       #这里是创建成功的提示

创建表结构：例1

create table MyClass(
id int(4) not null primary key auto_increment,
name char(20) not null,
sex int(4) not null default '0',
degree double(16,2));

创建表结构：例2

　　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.3 删除，添加或修改表字段返回顶部

　　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));

notify_tagnotifygroup

1.4 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  
;

student和student_record表创建语法

　　　　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));

notify_tagnotifygroup

　　　　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

　　　　1. 创建student和student_record表

#1、student表
create table student(
id int auto_increment,
name char(32) not null,
age int 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,
  primary key (id))
engine=InnoDB  
;

创建student和student_record表，但不直接创建外键关联

　　　　2. 手动创建外键关联的字段和外键约束

　　　　　　　　alter table study_record add column stu_id int; # 创建stu_id作为外键关联字段

　　　　　　　　# 说明：创建外键约束study_record 表中的 stu_id 字段一对多外键关联 student 表的 id 字段
　　　　　　　　alter table study_record add foreign key(stu_id) references student(id);

　　　　3. 查看数据库表创建的sql语句

　　　　　　　　mysql> show create table study_record;

| study_record | CREATE TABLE `study_record` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `day` int(11) NOT NULL,
  `status` char(32) NOT NULL,
  `stu_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `stu_id` (`stu_id`),
  CONSTRAINT `study_record_ibfk_1` FOREIGN KEY (`stu_id`) REFERENCES `student` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

　　　　4. 解除外键约束

　　　　　　　　alter table study_record drop foreign key study_record_ibfk_1;

| study_record | CREATE TABLE `study_record` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `day` int(11) NOT NULL,
  `status` char(32) NOT NULL,
  `stu_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `stu_id` (`stu_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

mysql> show create table study_record;

　　　　5. 删除外键

　　　　　　mysql> alter table study_record drop stu_id;

| study_record | CREATE TABLE `study_record` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `day` int(11) NOT NULL,
  `status` char(32) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

mysql> show create table study_record;

　　5、手动创建django表关联关系

class NewFlowUserRoleActionConf(models.Model):
    flowconf = models.ForeignKey(FlowConf, verbose_name='流程审批名称')
    approvetype = models.CharField(max_length=32, verbose_name='审批类型')
    sequence = models.IntegerField(verbose_name='审批序号')
    approvetogroupuser = models.BooleanField(default=True, verbose_name='是否允许指派组内执行')
    approvetorole = models.BooleanField(default=False, verbose_name='是否角色组审批')

create table workflow_newflowuserroleactionconf (
  id int(11) auto_increment,
  flowconf_id int NOT NULL,
  approvetype char(64) NOT NULL,
  sequence int NOT NULL,
  approvetogroupuser int NOT NULL,
  approvetorole int NOT NULL,
  primary key (id),
  CONSTRAINT fk_workflow_flowconf_key FOREIGN KEY (flowconf_id) REFERENCES workflow_newflowuserroleactionconf (id)
)
engine=InnoDB  
;

手动创建django表关联关系

1.5 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;

1.6 mysql导入导出返回顶部

　　参考博客：https://www.cnblogs.com/chandb/p/7425680.html

　　1、首先linux 下查看mysql相关目录

　　　　　　[root@op-workorder bin]# whereis mysql
　　　　　　mysql: /usr/bin/mysql /usr/lib64/mysql /usr/include/mysql

　　2、导出数据库用mysqldump命令

　　　　cd /home/work/mysql/bin # 先cd到mysql的运行路径下，再执行一下命令　

　　　　1. 导出数据和表结构

　　　　　　　　./mysqldump -uroot -p bsp > bsp.sql

　　　　　　　　mysqldump -u用户名 -p密码数据库名 > 数据库名.sql

　　　　2. 只导出表结构

　　　　　　　　mysqldump -u用户名 -p密码 -d 数据库名 > 数据库名.sql

　　　　　　　　mysqldump -uroot -p -d dbname > dbname .sql

　　3、导入数据库

　　　　1、首先建空数据库
　　　　　　　　mysql>create database bsp charset utf8;
　　　　2、导入数据库（方法一）
　　　　　　（1）选择数据库

　　　　　　　　　　mysql>use bsp;

　　　　　　（2）导入数据（注意sql文件的路径）

　　　　　　　　　　mysql>source /home/work/project/bsp/bsp.sql

　　　　3、导入数据库（方法二）

　　　　　　　　　　mysql -u用户名 -p密码数据库名 < 数据库名.sql

　　4、执行导出数据库报错问题解决

　　　　　　报错：mysqldump: Couldn't execute 'SHOW VARIABLES LIKE 'gtid\_mode'': Table 'performance_schema.session_variables' doesn't exist (1146)

　　　　　　1）mysql_upgrade -u root -p --force #更新

　　　　　　2）service mysql restart #restart mysql service

　　　　　　3）mysqldump -u root -p test > test.sql #重新备份数据

posted @ 2018-02-02 10:27 不做大哥好多年阅读(638) 评论(0) 编辑收藏举报

刷新页面返回顶部

03: MySQL基本操作

MySQL其他篇

目录：

1.1 MySQL 三种数据类型（数值，字符串，日期）返回顶部

1.2 MySQL常用增删改查命令返回顶部

1.3 删除，添加或修改表字段返回顶部

1.4 MySQL外键关联（一对多）返回顶部

1.5 MySQL连接查询：两个表之间外键关联返回顶部

1.6 mysql导入导出返回顶部

公告

03: MySQL基本操作

MySQL其他篇

目录：

1.1 MySQL 三种数据类型（数值，字符串，日期） 返回顶部

1.2 MySQL常用增删改查命令 返回顶部

1.3 删除，添加或修改表字段 返回顶部

1.4 MySQL外键关联（一对多） 返回顶部

1.5 MySQL连接查询：两个表之间外键关联 返回顶部

1.6 mysql导入导出 返回顶部

公告

1.1 MySQL 三种数据类型（数值，字符串，日期）返回顶部

1.2 MySQL常用增删改查命令返回顶部

1.3 删除，添加或修改表字段返回顶部

1.4 MySQL外键关联（一对多）返回顶部

1.5 MySQL连接查询：两个表之间外键关联返回顶部

1.6 mysql导入导出返回顶部