Mysql数据库基础

数据库概论
文章目录
  1. 1. Mysql 启动和创建数据库
    1. 1.1. 1、ubuntu下打开Mysql服务，并使用root用户登录
    2. 1.2. 2、连接数据库
    3. 1.3. 3、 新建数据库
    4. 1.4. 4、 新建数据表
    5. 1.5. 5、数据类型
    6. 1.6. 6、插入数据
    7. 1.7. 主键
    8. 1.8. 默认值约束
    9. 1.9. 唯一约束
    10. 1.10. FOREIGN KEY
    11. 1.11. NOT NULL
  2. 2. 查询
    1. 2.1. 1.基本select语句
    2. 2.2. 2.AND与OR
    3. 2.3. 3. IN和NOT IN
    4. 2.4. 4. 通配符
    5. 2.5. 5. 对结果进行排序 DESC(降) ASC(升)
    6. 2.6. 6.sql内置函数和计算
    7. 2.7. 7.子查询
    8. 2.8. 8.连接查询
  3. 3. 修改和删除
    1. 3.1. 删除数据库
    2. 3.2. 重命名一张表
    3. 3.3. 删除一张表
    4. 3.4. 对一列的修改(既对表结构的修改)
    5. 3.5. 对表的内容进行修改
  4. 4. 索引
    1. 4.1. 视图
    2. 4.2. 备份
    3. 4.3. 使用备份文件恢复数据库
  5. 5. 参考链接
Mysql 启动和创建数据库


sql语句注释：
--表示单行注释
/**/表示多行注释
Markdown书写格式不对，注意缩进问题

1、ubuntu下打开Mysql服务，并使用root用户登录

sudo service mysql start(也可用来检测系统是否安装mysql)
mysql -u root

检测是否安装成功：
netstat -tap |grep mysql
这句话的意思就是查看监听服务进程号
`

2、连接数据库

use mysql_car
use+[数据库名称] 表示使用某个数据库

show tables;
显示该数据库的各个表名(如果有视图，也会显示)

3、 新建数据库

CREATE DATABASE company

虽然Mysql建表语句中不区分大小写，但是一般保留字(关键字)大写，变量小写

4、 新建数据表
  1. use company (连接数据库)
  2. 在数据库中新建一张表的格式为：
1
2
3
4
5
CREATE TABLE employee(
id int(10),
name char(20),
phone int(12)
);
5、数据类型
数据类型 大小(字节) 用途 格式
INT 4 整数
Float 4 单精度浮点数
DOUBLE 4 双精度浮点数
ENUM 单选 ENUM(‘a’,’b’,’c’)
SET 多选 SET(‘1’,’2’,’3’)
DATE 3 日期 YYYY-MM-DD
TIME 3 时间点或持续时间点 HH:MM:SS
YEAR 1 年份值 HH:MM:SS
CHAR 0~255 定长字符串
VARCHAR 0~255 变长字符串
TEXT 0~65535 长文本数据

CHAR和VARCHAR的区别: CHAR的长度是固定的，而VARCHAR的长度是可以变化的，比如，存储字符串“abc”，对于CHAR (10)，表示存储的字符将占10个字节(包括7个空字符)，而同样的VARCHAR(12)则只占用3个字节的长度，12只是最大值，当你存储的字符小于12时，按实际长度存储。

ENUM和SET的区别: ENUM类型的数据的值，必须是定义时枚举的值的其中之一，即单选，而SET类型的值则可以多选。

6、插入数据
1
2
INSERT INTO 表名（
列名a,列名b,列名c）VALUES (值1,值2,值3)
1
2
3
INSERT INTO employee(id,name,phone) VALUES(1,'周星',18570631121);
INSERT INTO employee(id,name) VALUES(2,'成都');
INSERT INTO employee VALUES(3,'昆哥',130229456311);

1,3语句相同，如果插入数据完整可以省略
2语句不全时该值为NULL
除INT，DOUBLE，FLOAT值不需要单引号，其他数据需要单引号括起

#####约束
约束：通过对表的行和列的数据做出限制，来保证表数据的完整性和唯一性
Mysql数据库的约束分类：

约束类型： 主键 默认值 唯一 外键 非空
关键字： PRIMARY KEY DEFAULT UNIQUE FOREIGN KEY NOT NULL
主键

  1. 列级定义主键

    1
    2
    3
    4
    5
    create table employee(
     id int(10) NOT NULL PRIMART KEY AUTO_INCREMENT,
     name  char(20),
     phone bigint(15)
      )

  2. 表级定义主键

    1
    2
    3
    4
    5
    6
    create table employee(
     id int(10) NOT NULL  AUTO_INCREMENT,
     name  char(20),
     phone bigint(15),
     CONSTRAINT emp_pk PRIMARY KEY(id)
      )

  3. 复合主键(由表中两列或多列共同标识)

    1
    2
    3
    4
    5
    6
    7
    create table employee(
     id int(10) NOT NULL AUTO_INCREMENT,
     name  char(20),
     phone bigint(15),
     CONSTRAINT emp_py PRIMARY KEY(id,phone)

      )

emp_py是主键名称(自定义的)

默认值约束

phone bigint(15) 15203768500

DEFAULT约束只会在使用INSERT语句时体现出来，
INSERT语句中，插入位置没有值，则填充默认值

唯一约束

UNIQUE比较简单，它规定一张表中指定的一列的值必须不能有重复值，即这一列每个值都是唯一的。

UNIQUE(phone)

如果在phone这一列插入相同的值则失败

FOREIGN KEY

FOREIGN KEY既能保证数据完整性，又能体现表之间的关系

1
2
3
4
5
CONSTRAINT emp_fk FOREIGN KEY(name) REFERENCE Em_info(em_name)
--name列为外键，参考列为Em_info表的em_name列
/*
这里是多行注释
*/

NOT NULL

非空约束：name char(20) NOT NULL

Mysql中违反非空约束，不会报错只有警告，值为0
没有非空约束：空插入，值为NULL

1
2
3
4
INSERT INTO employee(id,name,phone) VALUES(3,1586563241)
--警告 name被非空约束，值为空，表中显示0
INSERT INTO employee(id,name,phone) VALUES(3,'laiwei')
-- INSERT成功 phone为空，因为没有非空约束，表中显示NULL

查询
1.基本select语句
1
2
3
select * from 表名
select name,age from employee where name='zhoxing';
-- select 要查询的列名 from 表名 where 限制条件
2.AND与OR
1
2
3
4
5
SELECT name,age from employee WHERE age>20 AND age<30
 --筛选出age大于20，且age小于30

 SELECT name,age from employee WHERE age<20 AND age>30
  --筛选出age小于20或者age大于30

如果需要包含边界值既age>=20<=3

1
select name,age from employee WHERE age BETWEEN 20 AND 30`;

3. IN和NOT IN

IN 和NOT IN用来指定在不在一个某个范围内的结果，比如我们要查询在dpt3和dpt4中的结果：

1
select name,age,dpt from employee where dpt IN ('dpt3','dpt4');

NOT IN 查询不在这个序列中的：

1
select name,age,dpt from employee where dpt NOT IN ('dpt3','dpt4');

4. 通配符

SQL语句中 LIKE 关键字和通配符一起使用，
_ :表示未指定的 一个字符
%: 表示未指定的多个字符

查询电话号码前面为：43776的employee

1
select name,age from employee where phone LIKE '43776__';

查询以周姓开头的employee

1
select name,age from employee where name LIKE '周%';

结果可能为： 周星，周星星，周建波

5. 对结果进行排序 DESC(降) ASC(升)
1
select name,age,salary,phone from employee ORDER BY salary DESC;
6.sql内置函数和计算

sql允许对表中数据进行计算，有5个内置函数：

函数名： COUNT SUM AVG MAX MIN
作用： 计数 求和 平均值 最大值 最小值

查询salary最大最小值：

1
select MAX(salary) AS zuigao_gongzi, MIN(salary) from employee;

AS关键字对最高工资进行命名

7.子查询

想要知道名为”Tom”的员工所在部门做了几个工程。员工信息储存在employee表中，但工程信息储存在project表中

1
2
3
select apt_name,COUNT(proj_name) AS count_project from project
WHERE apt_name IN
(SELECT apt_name from employee where name='Tom');

子查询还可以扩展到3层、4层或更多层。

8.连接查询

在处理多个表时，子查询只有在结果来自一个表时才有用。但如果需要显示两个表或多个表中的数据，使用连接查询把多个表当做一个表使用。
各员工所在部门的人数，其中员工的id和name来自employee表，people_num来自department表：

1
2
3
4
select name,people_num
FROM employee,department
WHERE employee.in_dpt= department.dpt_name
ORDER BY id

修改和删除
删除数据库

DROP DATABASE test_01

重命名一张表
1
2
3
RENAME TABLE 原名 TO 新名
ALTER TABLE 原名 RENAME 新名
ALTER TABLE 原名 RENAME TO 新名

这三种方式都可以

删除一张表

DROP TABLE 表名;

对一列的修改(既对表结构的修改)

####### 1.增加一列

1
2
3
 ALTER TABLE 表名字 ADD COLUMN 列名字 数据类型 约束;

或： ALTER TABLE 表名字 ADD 列名字 数据类型 约束;

ex:
ALTER TABLE employee ADD Height int(10) DEFAULT 170;

我们新增一列 weight (体重)放置在 age (年龄)的后面：
ex:
ALTER TABLE employee ADD weight int(10) DEFAULT 120 AFTER age;

新增加一列，放在第一位
ex:
ALTER TABLE employee ADD test int(10) DEFAULT 11 FIRST;

####### 2. 删除一列

1
2
ALTER TABLE employee DROP test;
--ALTER TABLE 表名字 DROP 列名字

####### 3.重命名一列(对列的属性做修改)
修改数据类型必须小心，因为这可能会导致数据丢失。在尝试修改数据类型之前，请慎重考虑。

1
2
ALTER TABLE employee CHANGE age nianling int(50) NOT NULL;
--ALTER TABLE 表名字 CHANGE 原列名 新列名 数据类型 约束;

注意：这条重命名语句后面的 “数据类型” 不能省略，否则重命名失败。
当原列名和新列名相同的时候，指定新的数据类型或约束，就可以用于修改数据类型或约束。需要注意的是，修改数据类型可能会导致数据丢失，所以要慎重使用。

####### 4.改变数据类型

1
2
ALTER TABLE employee MODIFY age char(2);
--ALTER TABLE 表名字 MODIFY 列名 新数据类型;

对表的内容进行修改

####### 修改表中的某个值

1
2
UPDATE employee SET age=21, salary=3000 WHERE name='TOM';
--UPDATE 表名 SET 列1='值1' 列2='值2' where 条件

####### 删除一行记录

1
2
DELETE FROM employee where name ='TOM';
--DELETE FROM 表名 WHERE 条件;

索引

索引是一种与表有关结构，相当于书的目录，可以根据目录的页码快速找到所需内容。
当表中有大量内容，如果表中没有索引，进行全表搜索将记录全部取出，与查询条件对比，返回符合条件的记录。这样会消耗数据库的大量时间，造成大量磁盘IO操作。

如果表中以建立索引，在索引中查找符合条件的索引值，根据索引值快速找到表中数据，可大大加快查询速度

1
2
3
4
5
6
7
ALTER TABLE employee ADD INDEX idx_id (id);
--ALTER TABLE 表名 ADD INDEX 索引名 (列名)；先写索引名，在写列名
--在employee表的id列上建立名为idx_id的索引

CREATE INDEX idx_name ON employee (name);
--在employee表的name列建立名为idx_name的索引
--CREATE INDEX 索引名 ON 表名 (列名);

索引的目的是加快查询的速度，当数据较少时候不会体现

  1. SHOW INDEX FROM 表名 查看表中的索引
  2. SELECT语句查询时，WHERE里面的条件会自动判断有没有可用的索引
视图

视图是从一个表或者多个表导出来的表，是一个虚拟的表，它就像一个窗口，通过这个窗口可以看到系统专门提供的数据。这样，用户不用看到整个数据库中的数据，而只关心对自己有用的数据。

注意理解视图是一个虚拟的表：

  • 数据库中只存放了视图的定义，而没有存放视图中的数据，这些数据放在原来的表中
  • 使用视图查询数据时，数据库系统会从原来的表中取出对应的数据
  • 视图中的数据依赖于原来表中的数据，如果原来表中的数据发生改变，显示在视图中的数据也会发生改变
  • 在使用视图时，可以把它当做一张表

创建视图的语句格式为：

1
CREATE VIEW 视图名(列a,列b,列c) AS SELECT 列1,列2,列3 FROM 表名字;

可见创建视图的语句，后半句是一个SELECT查询语句，所以视图也可以建立在多张表上，只需在SELECT语句中使用子查询或连接查询

######导入和导出

导入操作，可以把一个文件里的数据保存进入一张表：

1
LOAD DATA INFILE '文件路径' INTO TABLE 表名字;

前提是文件中数据的格式要对：

导出操作，是将一个数据库表中的数据保存到一个文件之中：

1
SELECT 列1，列2 INTO OUTFILE '文件路径和文件名' FROM表名字;

注意：语句中 “文件路径” 之下不能已经有同名文件。

现在我们把整个employee表的数据导出到 /tmp 目录下，导出文件命名为 out.txt 具体语句为：

1
SELECT * INTO OUTFILE 'tmp/out.txt' FROM employee

备份

数据库中的数据十分重要，注意使用备份功能

备份与导出的区别：导出的文件只是保存数据库中的数据；而备份，则是把数据库的结构，包括数据、约束、索引、视图等全部另存为一个文件。

mysqldump是MySQL用于备份数据库的实用程序。它主要产生一个SQL脚本文件，其中包含从头重新创建数据库所必需的命令CREATE TABLE INSERT等。

使用mysqldump备份的语句：

1
2
3
4
mysqldump -u root 数据库名 > 备份文件名;
--备份整个数据库
mysqldump -u root 数据库名 表名字 > 备份文件名;
--备份整个表

我们尝试备份整个数据库 mysql_shiyan，将备份文件命名为 bak.sql，先 Ctrl+Z 退出MySQL，再使用语句：

1
mysqldump -u root mysql_shiyan > bak.sql

使用备份文件恢复数据库
1
source /tmp/SQL6/MySQL-06.sql

这就是一条恢复语句，它把MySQL-06.sql文件中保存的mysql_shiyan数据库恢复。

另一种方式：
1.CREATE DATABASE test;

  1. CTRL+Z退出mysql ,然后输入语句进行恢复，把刚才备份的 bak.sql 恢复到 test 数据库:
    mysql -u root test < bak.sql
  2. mysql -u root use test show tables;

结果：

参考链接

Mysql官方文档
完整的建表语句
Mysql菜鸟教程

分享到 评论