MySQL数据库基本命令操作

MySQL数据库 MySQL基础
发布时间 :
阅读 :
  1. 操作MySQL数据库
    1. 创建一个新的数据库
    2. 删除指定的数据库数据库
    3. 数据库的列类型
    4. 数据库的字段属性
    5. 表的基本操作
  2. MYISIAM、INNODB 引擎
  3. MySQL 数据库管理
    1. 添加外键
      1. 创建表的同时添加外键
      2. 创建表后单独添加外键
    2. DML语言
      1. 添加（insert 语句）
      2. 修改（updata 语句）
      3. 删除（delete 语句）
  4. DQL查询数据
    1. select from
    2. 连表查询
      1. 内连接 inner join
      2. 左连接 left join
      3. 右连接
      4. 多表查询（两个以上）
    3. 自链接
    4. 分页和排序
      1. 排序 order by
      2. 分页limit
    5. 子查询

注意：cmd 操作数据库时每条命令后必须加分号，否则会认为命令没有结束不会执行

操作MySQL数据库

创建一个新的数据库

1
create database 库名;

删除指定的数据库数据库

1
drop database 库名;
MySQL数据库基本命令行操作
mysql -u root -p密码连接数据库
flush privileges;刷新数据库
show databases;查看所有的数据库
user 数据库名;切换数据库
show tables;查看数据库中所有的表
describe 表名;显示数据库中的所有的表的信息
desc 表名;查看表的结构
exit;退出MySQL的连接

数据库的列类型

数据库的字段属性

  1. unsigened: 无符号的整数，声明该列时不能为负数;
  2. zerofill: 0填充的，不足的位数由0来填充;
  3. 自增: 通常理解为自增，自动在上一条记录上的基础上+1（默认），通常用来设计唯一的额主键“index”，必须时 整数类型，可以自定义设计主键自增的起始值和步长。

表的基本操作

  1. 表的常用的修改、删除命令：
    常用的修改命令
    修改表名alter table 旧表名 rename as 新表名;
    增加表的字段alter table 表名 add 字段名 列属性[]
    修改表的字段（修改约束）alter table 表名 modify 字段名 列属性[]
    修改表的字段（字段重命名）alter table 表名 change 旧名字 新名字 列属性[]
    常用的删除命令
    删除表的字段alter table 表名 drop 字段名
    删除表（存在的表）drop table if exists 表名
  2. 创建一个新表：
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    -- IF NOT EXISTS 判断 student 这个表在数据库中是否存在同名的表，如果不存在，就创建：
    -- PRIMARY KEY(`id`) 将id设为主键;
    -- PRIMARY KEY 主键,一般一个表只有唯一的一个主键;
    -- ENGINE=INNODB 设置引擎为INNODB;
    -- CHARSET=utf8 设置默认字符集为utf8;
    CREATE TABLE IF NOT EXISTS `student` (
    id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
    `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
    `pwd` VARCHAR(20) NOT NULL DEFAULT '000000' COMMENT '密码',
    `sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
    `birthday` DATETIME  DEFAULT NULL COMMENT '出生日期',
    `address` VARCHAR(100) NOT NULL DEFAULT '月球' COMMENT '家庭住址',
    `email` VARCHAR(50)  DEFAULT NULL COMMENT '邮箱',
    PRIMARY KEY(`id`)    
    )ENGINE=INNODB DEFAULT CHARSET=utf8

MYISIAM、INNODB 引擎

  1. 所有的数据文件都存在data目录下，一个文件就对应一个数据库。
  2. 数据库的本质就是文件的存储。
  3. MySQL 引擎的物理文件上的区别：
    ·INNODB 在数据表中只有 .fram文件，以及上级目录下的 ibdata1文件
    ·MYISAM 对应文件有 .frm 表结构的定音文件,.MYD 数据文件（data）, .MYI 索引文件（index）。
4.myisam、innodb对比
MYISAMINNODB
事务支持不支持支持
数据行锁定不支持支持
外键约束不支持支持
全文索引支持不支持
表空间大小较小较大，约为两倍
常规使用操作：
·节约空间，速度加快；
·安全性高，事物的处理，多表多用户操作；

MySQL 数据库管理

添加外键

注意：删除由外键关系的表时，必须先删除引用该表（主表）的所有表（从表），在删除该表，否则会报错（删除失败）

创建表的同时添加外键

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- 创建 grade 表
CREATE TABLE `grade`(
    `gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级',
    `gradename` VARCHAR(50) NOT NULL COMMENT '年级名',
    PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 创建 student 表
CREATE TABLE IF NOT EXISTS `student` (
    `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
    `NAME` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
    `pwd` VARCHAR(20) NOT NULL DEFAULT '000000' COMMENT '密码',
    `sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
    `birthday` DATETIME  DEFAULT NULL COMMENT '出生日期',
    `address` VARCHAR(100) NOT NULL DEFAULT '月球' COMMENT '家庭住址',
    `email` VARCHAR(50)  DEFAULT NULL COMMENT '邮箱',
    `gradeid` INT(10) NOT NULL COMMENT '年级',
    PRIMARY KEY(`id`),
    -- 定义外键 KEY ,FK_将要设置外键的名称（固定）,
    KEY `FK_gradeid` (`gradeid`),
    -- 给外键添加约束（执行引用）,REFERENCES `grade`(`gradeid`);REFERENCES引用grade表的gradeid,
    CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

创建表后单独添加外键

1
2
3
4
5
6
7
格式：
ALTER TABLE `表名`
ADD CONSTRAINT `约束名` FOREIGN KEY (`作为外键的列`) REFERENCES `引用表`(`引用表的字段`)

eg:
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)

DML语言

添加（insert 语句）

  1. 单个插入
    1
    2
    3
    4
    5
    格式：
    INSERT INTO `表名` (`添加字段名`) VALUES ('值');

    eg：
    INSERT INTO `student` (`name`) VALUES ('夏淼淼');
  2. 一行多列插入
    1
    2
    3
    4
    5
    6
    7
    格式：        
    INSERT INTO `表名` (`添加字段名1`,`添加字段名2`,`添加字段名3`)
    VALUES ('值1','值2','值3');

    eg:
    INSERT INTO `student` (`name`,`pwd`,`sex`,`birthday`,`address`,`email`)
     VALUES ('jake','123456','男','1999-10-26','爪洼','666666');
  3. 多行多列插入（每一行插入相同的字段的字段数相等）
    1
    2
    3
    4
    5
    6
    7
    格式:
    INSERT INTO `表名`(`添加字段名1`,`添加字段名2`,`添加字段名3`) 
    VALUES ('值1','值2','值3'),('值1','值2','值3'),...;

    eg:
    INSERT INTO `student`(`name`,`pwd`,`sex`)
     VALUES ('tom','222222','男'),('jake','333333','男');
  4. 省略字段，给定行号（id）,前提是值与表中的字段是一一对应的顺序不可错！
    1
    2
    3
    4
    5
    格式：
    INSERT INTO `表名` VALUES (行号,'值1','值2','值3',...)

            eg:
            INSERT INTO `student` VALUES (4,'夏淼淼','333333','女','2020-8-25','火星','159753')

    5.注意事项：

    （1）字段和字段之间，值和值之间用英文状态的“逗号”隔开，字段用 ` ` 引上，值用英文状态的 '' 引上。 （2）字段可以省略，但后面的值必须要一一对应，不能少。 （3）可以同时插入多条数据,values后面的值,需要使用英文状态的逗号隔开,数据组用括号括起来，values(数据组),(数据组),...

修改（updata 语句）

  1. 单个修改指定属性（如果不添加where判断那么指定列的所有数据都会被修改成将要修改的数据）
    1
    2
    3
    4
    格式：
    UPDATE `表名` SET `列名` ='jice' WHERE 主键名=行号
    eg:
    UPDATE `student` SET `name` ='jice' WHERE id=1
  2. 修改多个属性
    1
    2
    3
    4
    格式：
    UPDATE `表名` SET `列名1`='夏淼淼',`列名2`='123@qq.com',... WHERE id=1
    eg:
    UPDATE `student` SET `name`='夏淼淼',`email`='123@qq.com' WHERE id=1
  3. 运算符
    操作符操作的返回值是布尔值！
    条件where 子句常用运算符
    操作符含义范围结果
    =等于1=2false
    <>或！=不等于1<>2true
    > 大于1>2false
    < 小于1<2true
    <= 小于等于1<=2true
    >= 大于等于1>=2false
    and &&1>2&&1<2false
    or ||1>2&&1<2true
    between...and... 在某个范围内[1,2]

删除（delete 语句）

1.delete

1
格式： delete from `表名`

2.truncate

1
格式： truncate table `表名`

3.delete、truncate的共同点
delete、truncate 都是删除数据的命令，都不会删除表结构。
4.delete、truncate的区别：
delete 删除数据不改变自增数(需要重启数据库才会使自增数归零。原因：innobd 数据存在内存当中，相当于断电即使。myisam 存在文件中不会丢失。
)。
truncate 删除数据自增数归零。

DQL查询数据

DQL：Data Query Language:数据查询语言。
接下来测试命令时会使用的school数据库的创建和数据的添加命令都在这个文件里，提取码iti3：
https://pan.baidu.com/s/1mA0ghsGKmau0ByF0e891kg

select from

  1. 查询指定字段:
    1
    2
    select `字段` frame 表
    * 表示表的所有字段
  2. 给字段起别名:
    1
    select  `字段` as 别名 from 表
  3. 给表起别名:
    1
    select  * from 表 as 别名;
  4. 筛选 select 查询出来的结果中重复的数据,重复的数据只显示一条:
    1
    select distinct `字段` from 表

连表查询

关于链表查询的理解：链表查询首先要确定要查询的字段都是来自那些表中，然后考虑相关表中的交叉数据的字段，通过这些交叉数据，作为判断依据进行查询。
join 查询时，左右的判断为在编写命令时,表名在join的左右的那一侧，类似于：from 左表 left join 右表

内连接 inner join

inner join 是指交叉数据内容的交际

1
2
3
4
SELECT a.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` AS a
INNER JOIN `result` AS b
WHERE a.`studentno`=b.`studentno`

左连接 left join

left join 是指交叉数据内容的交际和坐左表全部的内容，如果右表没有则显示空

1
2
3
4
SELECT a.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` AS a
LEFT JOIN `result` AS b
ON a.`studentno`=b.`studentno`

右连接

right join 是指交叉数据内容的交际和坐右表全部的内容，如果左表没有则显示空

1
2
3
4
SELECT a.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` AS a
RIGHT JOIN `result` AS b
ON a.`studentno`=b.`studentno`

多表查询（两个以上）

当使用两个以上的表进行查询时，应该两两查询，用每次两个表的结果当作一个新表连接另一个表，层层查询
下面是是一个三表查询：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
    -- 查询学生的学号，姓名，科目名，科目成绩，查询结果根据成绩排序
    -- 分析：学号，姓名来自student表，科目名来自subject表，科目成绩来自result表
    SELECT a.`studentno`,`studentname`,`subjectname`,`studentresult`
    -- 第一步，获取所有已经有成绩的所有同学的是所有成绩名单
    FROM `student` AS a
    INNER JOIN `result` AS b
    ON a.studentno=b.studentno
    -- 第二步，通过上面的结果在连接subject表获得相应学课代号的学课名
    INNER JOIN`subject` AS c
    ON b.`subjectno`=  c.`subjectno`
    -- 筛选指定科目
    WHERE c.`subjectname`='高等数学-1'
-- 查询结果根据成绩排序    
    order by `studentresult`

自链接

自连接的理解：就是和自己进行连接查询，给一张表取两个不同的别名，然后附上连接条件。

1
2
3
SELECT a.`categoryName` AS '父栏',b.`categoryName` AS '子栏'
FROM `category` AS a,`category` AS b
WHERE a.`pid`=b.`categoryid`

分页和排序

排序 order by

order by 用来设置排序依据

注意：如果使用where判断需要将where判断写在order by 之前

1. desc 降序排序 
1
ORDER BY `studentresult` desc
2. asc 升序排序 
1
ORDER BY `studentresult` asc

分页limit

为什么要分页？
分页能够缓解数据库压力，并且可以给人更好的体验

1
2
3
4
格式：
limit 开始位置，每页的显示数据条数
-- 从第一条开始显示，显示条数据
 LIMIT 0,5

子查询

子查询是一种由内向外的查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
    -- 查询 高等数学-1的所有考试结果（学号，科目编号，成绩），升序排序
    SELECT `studentno`,`subjectno`,`studentresult`
    FROM `result`
    WHERE `subjectno`=(
        SELECT `subjectno`FROM `subject`
        WHERE `subjectName`  = '高等数学-1'
    )
    ORDER BY `studentresult` DESC
``` 

## MySQL 函数
官网：https://dev.mysql.com/doc/refman/5.7/en/sql-function-reference.html
## 事务 ACID
关于 事务 的了解：
所谓事务，它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。
ACID，是指数据库管理系统（DBMS）
在写入或更新资料的过程中，为保证事务（transaction）是正确可靠的，所必须具备的四个特性：原子性（atomicity，或称不可分割性）,一致性（consistency）
隔离性（isolation，又称独立性）,持久性（durability）。
### ACID的四个特性
https://blog.csdn.net/qq_32690999/article/details/78007621
#### 原子性（atomicity，或称不可分割性）

#### 一致性（consistency）

#### 隔离性（isolation，又称独立性）

#### 持久性（durability）

### 事务之间的相互影响

#### 脏读

#### 不可重复读

#### 幻读（虚读）

#### 丢失更新

### 事务的执行
 事务默认是自动提交的，但是也可以根据需求自己设置事物是自动提交还是手动提交。
    set autocommit=0  /*自动提交关闭状态*/
    set autocommit=1  /*自动提交开启状态*/
如果不自己设置事务提交状态，那么会使用默认状态自动提交，如果要手动设置事物提交需要先将默认提交状态设置为关闭然后编写sql，从事物自动提交关闭到事物自动提交开启，这之间的sql会认为是在一个事务中的，以为事务的“一致性”所以处在同一事务中的sql命令要么全部执行提交成功，要么全部失败。
### 手动设置事务提交
```sql
-- 1.手动关闭事务自动提交
    SET autocommit=0
    -- 2.标志一个事务的开启
    START TRANSACTION 
    /*sql命令*/
    -- 3. 提交事务
    COMMIT
    -- 4.设置回滚，如果提交失败就恢复到原来的样子
    ROLLBACK
    -- 5.事务结束,开启事务提交
    SET autocommit=1
转载请注明来源，欢迎对文章中的引用来源进行考证，欢迎指出任何有错误或不够清晰的表达。可以在下面评论区评论，也可以邮件至 jaytp@qq.com

赏

©2016-2020 Yelog

Built with Hexo and 3-hexo theme

×

喜欢就点赞,疼爱就打赏