一. 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;

创建表结构：例1

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

创建表结构：例2

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、插入数据

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. 创建外键关联的表

notify_tagnotifygroup:

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表

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表）

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手动创建和删除外键约束

1. 创建student和student_record表

创建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  
;

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;

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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

5. 删除外键

mysql> alter table study_record drop stu_id;

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,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

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

手动创建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  
;

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

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

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

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

右连接where只影向左表,所以左表(student)中不符合where条件的数据不会显示，右表study_record表内容全部显示
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 onstudent.id=study_record.stu_id;

五. mysql导入导出

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

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

2、导出数据库用mysql dump命令

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 #重新备份数据

原文链接：https://blog.csdn.net/longlong6682/article/details/104971233