MySQL基础

最新推荐文章于 2024-06-28 15:42:05 发布

北京-大白

最新推荐文章于 2024-06-28 15:42:05 发布

阅读量200

点赞数

分类专栏：数据库文章标签：基础

本文链接：https://blog.csdn.net/robothj/article/details/79776143

版权

数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

1. 安装MySQL

sudo service mysql start #检查是否已安装
sudo apt-get install mysql-server #安装 MySQL 服务端、核心程序
sudo apt-get install mysql-client #安装 MySQL 客户端
sudo netstat -tap | grep mysql #测试是否安装成功
sudo gedit /etc/mysql/my.cnf #修改配置文件
sudo service mysql start #启动 MySQL 服务
sudo service mysql restart #重启
mysql -u root #使用 root 用户登录
mysql -u用户名 -p密码 #fa:fa登录
mysql -V #查看版本

2. 常用命令

show databases; #查看数据库
create database 数据库名称; #新建数据库
drop database 数据库名称; #删除数据库
use 数据库名称; #使用数据库
show tables; #查表
create table 表名（字段参数）; 
create table if not exists 表名（字段参数）； #建立新表
drop table 表名；#删除表 
drop table if exists 表名； #删除旧表
desc 表名; #查看表属性内容
select * from 表名; #查看表数据
truncate table 表名; #清空表数据
quit 或 exit #退出

注意：安装完后，默认已有三个数据库，分别是 “information-schema”、“mysql”、“performance-schema”。

3. 新建数据库

CREATE DATABASE <数据库名字>; #eg:CREATE DATABASE mysql_shiyan
SHOW DATABASES;
use mysql_shiyan; #连接数据库
show tables; #可以查看当前数据库里有几张表

MySQL大小写说明：
在大多数系统中，SQL 语句都是不区分大小写的，因此以下语句都是合法的。但是出于严谨，而且便于区分保留字（保留字(reserved word)：指在高级语言中已经定义过的字，使用者不能再将这些字作为变量名或过程名使用。）和变量名，我们把保留字大写，把变量和数据小写。

CREATE DATABASE name1;
create database name2;
CREATE database name3;
create DAtabaSE name4;

4. 数据表

数据表（table）简称表，它是数据库最重要的组成部分之一。数据库只是一个框架，表才是实质内容。而一个数据库中一般会有多张表，这些各自独立的表通过建立关系被联接起来，才成为可以交叉查阅、一目了然的数据库。

5. 新建表

CREATE TABLE 表的名字
(
列名a 数据类型(数据长度),
列名b 数据类型(数据长度)，
列名c 数据类型(数据长度)
);

eg：在mysql_shiyan中新建一张表 employee，包含姓名，ID 和电话信息，所以语句为

CREATE TABLE employee (id int(10),name char(20),phone int(12));

然后再创建一张表 department，包含名称和电话信息，想让命令看起来更整洁，可以这样输入命令

CREATE TABLE department
{
dpt_name CHAR(20),
dpt_phone INT(12)
};

6. 查看表中的内容

SELECT * FROM employee;  #查看表中的内容

7. 插入数据

INSERT INTO 表的名字(列名a,列名b,列名c) VALUES(值1,值2,值3);

eg：向 employee 中加入 Tom、Jack 和 Rose

INSERT INTO employee(id,name,phone) VALUES(01,'Tom',110110110);
INSERT INTO employee VALUES(02,'Jack',119119119);
INSERT INTO employee(id,name) VALUES(03,'Rose');
SELECT * FROM employee;  #查看表中的内容

说明：有的数据需要用单引号括起来，比如 Tom、Jack、Rose 的名字，这是由于它们的数据类型是 CHAR 型。此外 VARCHAR,TEXT,DATE,TIME,ENUM 等类型的数据也需要单引号修饰，而 INT,FLOAT,DOUBLE等则不需要。

8. 常用数据类型

数据类型	大小(字节)	用途	格式
INT	4	整数
FLOAT	4	单精度浮点数
DOUBLE	8	双精度浮点数

ENUM		单选,比如性别	ENUM(‘a’,’b’,’c’)
SET		多选	SET(‘1’,’2’,’3’)

DATE	3	日期	YYYY-MM-DD
TIME	3	时间点或持续时间	HH:MM:SS
YEAR	1	年份值	YYYY

CHAR	0~255	定长字符串
VARCHAR	0~255	变长字符串
TEXT	0~65535	长文本数据

整数除了 INT 外，还有 TINYINT、SMALLINT、MEDIUMINT、BIGINT。

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

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

9. SQL约束

约束是一种限制，它通过对表的行或列的数据做出限制，来确保表的数据的完整性、唯一性。

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

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

建立含约束的表

mysql_shiyan.sql 脚本

DROP DATABASE mysql_shiyan; #删除数据表
CREATE DATABASE mysql_shiyan;
use mysql_shiyan;

CREATE TABLE department
(
  dpt_name   CHAR(20) NOT NULL,
  people_num INT(10) DEFAULT '10', #默认约束
  CONSTRAINT dpt_pk PRIMARY KEY (dpt_name) #dpt_pk主键名
 );

CREATE TABLE employee
(
  id      INT(10) PRIMARY KEY, #主键
  name    CHAR(20),
  age     INT(10),
  salary  INT(10) NOT NULL, #非空约束
  phone   INT(12) NOT NULL,
  in_dpt  CHAR(20) NOT NULL,
  UNIQUE  (phone), #唯一约束
  CONSTRAINT emp_fk FOREIGN KEY (in_dpt) REFERENCES department(dpt_name) #外键约束
 );

CREATE TABLE project
(
  proj_num   INT(10) NOT NULL,
  proj_name  CHAR(20) NOT NULL,
  start_date DATE NOT NULL,
  end_date   DATE DEFAULT '2015-04-01',
  of_dpt     CHAR(20) REFERENCES department(dpt_name),
  CONSTRAINT proj_pk PRIMARY KEY (proj_num,proj_name) #proj_pk主键名 (proj_num,proj_name)复合主键
 );

运行脚本

source /home/fa/Desktop/mysql_shiyan.sql;

主键

主键 (PRIMARY KEY)是用于约束表中的一行，作为这一行的唯一标识符，在一张表中通过主键就能准确定位到一行，因此主键十分重要。主键不能有重复且不能为空。

默认约束

默认值约束 (DEFAULT) 规定，当有 DEFAULT 约束的列，插入数据为空时，将使用默认值。

# 正常插入数据
INSERT INTO department(dpt_name,people_num) VALUES('dpt1',11);

#插入新的数据，people_num 为空，使用默认值
INSERT INTO department(dpt_name) VALUES('dpt2');

唯一约束

唯一约束 (UNIQUE) 它规定一张表中指定的一列的值必须不能有重复值，即这一列每个值都是唯一的。当 INSERT 语句新插入的数据和已有数据重复的时候，如果有 UNIQUE约束，则 INSERT 失败。

外键约束

外键 (FOREIGN KEY) 既能确保数据完整性，也能表现表之间的关系。一个表可以有多个外键，每个外键必须 REFERENCES (参考) 另一个表的主键，被外键约束的列，取值必须在它参考的列中有对应值。

CONSTRAINT emp_fk FOREIGN KEY (in_dpt) REFERENCES department(dpt_name) #外键约束
#emp_fk 外键名
#in_dpt 外键
#dpt_name 参考列为department表中的dpt_name列

在 INSERT 时，如果被外键约束的值没有在参考列中有对应，比如以下命令，参考列 (department 表的 dpt_name) 中没有dpt3，则INSERT 失败

INSERT INTO employee VALUES(02,'Jack',30,3500,114114,'dpt3');

非空约束

非空约束 (NOT NULL)听名字就能理解，被非空约束的列，在插入值时必须非空。在MySQL中违反非空约束，不会报错，只会有警告，比如以下语句：

#INSERT 成功 age 为空，因为没有非空约束，表中显示 NULL
INSERT INTO employee(id,name,salary,phone,in_dpt) VALUES(03,'Jim',3400,119119,'dpt2'); 

#警告 salary 被非空约束，值为空，表中显示0
INSERT INTO employee(id,name,age,phone,in_dpt) VALUES(04,'Bob',23,123456,'dpt1');

结果警告，但还是插入数据成功，5.6 版本以上的 MySQL 会报错，禁止插入不符合非空约束的数据

10. SELECT语句

创建示例

CREATE DATABASE mysql_shiyan;

use mysql_shiyan;

CREATE TABLE department
(
  dpt_name   CHAR(20) NOT NULL,
  people_num INT(10) DEFAULT '10',
  CONSTRAINT dpt_pk PRIMARY KEY (dpt_name)
 );

CREATE TABLE employee
(
  id      INT(10) PRIMARY KEY,
  name    CHAR(20),
  age     INT(10),
  salary  INT(10) NOT NULL,
  phone   INT(12) NOT NULL,
  in_dpt  CHAR(20) NOT NULL,
  UNIQUE  (phone),
  CONSTRAINT emp_fk FOREIGN KEY (in_dpt) REFERENCES department(dpt_name)
 );

CREATE TABLE project
(
  proj_num   INT(10) NOT NULL,
  proj_name  CHAR(20) NOT NULL,
  start_date DATE NOT NULL,
  end_date   DATE DEFAULT '2015-04-01',
  of_dpt     CHAR(20) REFERENCES department(dpt_name),
  CONSTRAINT proj_pk PRIMARY KEY (proj_num,proj_name)
 );

#INSERT INTO department(dpt_name,people_num) VALUES('部门',人数);

INSERT INTO department(dpt_name,people_num) VALUES('dpt1',11);
INSERT INTO department(dpt_name,people_num) VALUES('dpt2',12);
INSERT INTO department(dpt_name,people_num) VALUES('dpt3',10);
INSERT INTO department(dpt_name,people_num) VALUES('dpt4',15);

#INSERT INTO employee(id,name,age,salary,phone,in_dpt) VALUES(编号,'名字',年龄,工资,电话,'部门');

INSERT INTO employee(id,name,age,salary,phone,in_dpt) VALUES(01,'Tom',26,2500,119119,'dpt4');
INSERT INTO employee(id,name,age,salary,phone,in_dpt) VALUES(02,'Jack',24,2500,120120,'dpt2');
INSERT INTO employee(id,name,age,salary,phone,in_dpt) VALUES(03,'Rose',22,2800,114114,'dpt3');
INSERT INTO employee(id,name,age,salary,phone,in_dpt) VALUES(04,'Jim',35,3000,100861,'dpt1');
INSERT INTO employee(id,name,age,salary,phone,in_dpt) VALUES(05,'Mary',21,3000,100101,'dpt2');
INSERT INTO employee(id,name,age,salary,phone,in_dpt) VALUES(06,'Alex',26,3000,123456,'dpt1');
INSERT INTO employee(id,name,age,salary,phone,in_dpt) VALUES(07,'Ken',27,3500,654321,'dpt1');
INSERT INTO employee(id,name,age,salary,phone,in_dpt) VALUES(08,'Rick',24,3500,987654,'dpt3');
INSERT INTO employee(id,name,age,salary,phone,in_dpt) VALUES(09,'Joe',31,3600,110129,'dpt2');
INSERT INTO employee(id,name,age,salary,phone,in_dpt) VALUES(10,'Mike',23,3400,110110,'dpt4');
INSERT INTO employee(id,name,salary,phone,in_dpt) VALUES(11,'Jobs',3600,019283,'dpt2');
INSERT INTO employee(id,name,salary,phone,in_dpt) VALUES(12,'Tony',3400,102938,'dpt3');

#INSERT INTO project(proj_num,proj_name,start_date,end_date,of_dpt) VALUES(编号,'工程名','开始时间','结束时间','部门名');

INSERT INTO project(proj_num,proj_name,start_date,end_date,of_dpt) VALUES(01,'proj_a','2015-01-15','2015-01-31','dpt2');
INSERT INTO project(proj_num,proj_name,start_date,end_date,of_dpt) VALUES(02,'proj_b','2015-01-15','2015-02-15','dpt1');
INSERT INTO project(proj_num,proj_name,start_date,end_date,of_dpt) VALUES(03,'proj_c','2015-02-01','2015-03-01','dpt4');
INSERT INTO project(proj_num,proj_name,start_date,end_date,of_dpt) VALUES(04,'proj_d','2015-02-15','2015-04-01','dpt3');
INSERT INTO project(proj_num,proj_name,start_date,end_date,of_dpt) VALUES(05,'proj_e','2015-02-25','2015-03-01','dpt4');
INSERT INTO project(proj_num,proj_name,start_date,end_date,of_dpt) VALUES(06,'proj_f','2015-02-26','2015-03-01','dpt2');

基本的SELECT语句

SELECT 要查询的列名 FROM 表名字 WHERE 限制条件;

如果要查询表的所有内容，则把 要查询的列名 用一个星号 * 号表示，代表要查询表中所有的列。而大多数情况，我们只需要查看某个表的指定的列，比如要查看employee 表的 name 和 age：

SELECT name,age FROM employee;

数学符号条件

SELECT 语句常常会有 WHERE 限制条件，用于达到更加精确的查询。WHERE限制条件可以有数学符号 (=,<,>,>=,<=) ，刚才我们查询了 name 和 age，现在稍作修改：

SELECT name,age FROM employee WHERE age>25;

或者查找一个名字为 Mary 的员工的 name,age 和 phone：

SELECT name,age,phone FROM employee WHERE name='Mary';

AND与OR

从这两个单词就能够理解它们的作用。WHERE 后面可以有不止一条限制，而根据条件之间的逻辑关系，可以用 OR(或) 和 AND(且)连接：

#筛选出 age 小于 25，或 age 大于 30
SELECT name,age FROM employee WHERE age<25 OR age>30;

IN 和 NOT IN

关键词IN和NOT IN的作用和它们的名字一样明显，用于筛选“在”或“不在”某个范围内的结果，比如说要查询在dpt3或dpt4的人:

SELECT name,age,phone,in_dpt FROM employee WHERE in_dpt IN ('dpt3','dpt4');

通配符

关键字 LIKE在SQL语句中和通配符一起使用，通配符代表未知字符。SQL中的通配符是 _ 和 % 。其中 _ 代表一个未指定字符，%代表不定个未指定字符。比如，要只记得电话号码前四位数为1101，而后两位忘记了，则可以用两个 _ 通配符代替：

SELECT name,age,phone FROM employee WHERE phone LIKE '1101__';

对结果排序

为了使查询结果看起来更顺眼，我们可能需要对结果按某一列来排序，这就要用到 ORDER BY排序关键词。默认情况下，ORDER BY的结果是升序排列，而使用关键词ASC和DESC可指定升序或降序排序。比如，按salary降序排列，SQL语句为：

SELECT name,age,salary,phone FROM employee ORDER BY salary DESC;

SQL 内置函数和计算

SQL 允许对表中的数据进行计算。对此，SQL 有 5 个内置函数，这些函数都对 SELECT 的结果做操作：

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

其中 COUNT 函数可用于任何数据类型(因为它只是计数)，而 SUM 、AVG 函数都只能对数字类数据类型做计算，MAX 和 MIN 可用于数值、字符串或是日期时间数据类型。

比如计算出salary的最大、最小值，用这样的一条语句：

SELECT MAX(salary) AS max_salary,MIN(salary) FROM employee;

使用AS关键词可以给值重命名，比如最大值被命名为了max_salary

子查询

上面讨论的 SELECT 语句都仅涉及一个表中的数据，然而有时必须处理多个表才能获得所需的信息。例如：想要知道名为 “Tom” 的员工所在部门做了几个工程。员工信息储存在 employee 表中，但工程信息储存在project 表中。对于这样的情况，可以用子查询：

SELECT of_dpt,COUNT(proj_name) AS count_project FROM project
WHERE of_dpt IN
(SELECT in_dpt FROM employee WHERE name='Tom');

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

连接查询

在处理多个表时，子查询只有在结果来自一个表时才有用。但如果需要显示两个表或多个表中的数据，这时就必须使用连接 (join) 操作。连接的基本思想是把两个或多个表当作一个新的表来操作，如下：

SELECT id,name,people_num
FROM employee,department
WHERE employee.in_dpt = department.dpt_name
ORDER BY id;

这条语句查询出的是，各员工所在部门的人数，其中员工的 id 和 name 来自 employee 表，people_num 来自 department 表；另一个连接语句格式是使用 JOIN ON 语法，刚才的语句等同于：

SELECT id,name,people_num
FROM employee JOIN department
ON employee.in_dpt = department.dpt_name
ORDER BY id;

11. 其他

创建示例

CREATE DATABASE mysql_shiyan;

use mysql_shiyan;

CREATE TABLE department
(
  dpt_name   CHAR(20) NOT NULL,
  people_num INT(10) DEFAULT '10',
  CONSTRAINT dpt_pk PRIMARY KEY (dpt_name)
 );

CREATE TABLE employee
(
  id      INT(10) PRIMARY KEY,
  name    CHAR(20),
  age     INT(10),
  salary  INT(10) NOT NULL,
  phone   INT(12) NOT NULL,
  in_dpt  CHAR(20) NOT NULL,
  UNIQUE  (phone),
  CONSTRAINT emp_fk FOREIGN KEY (in_dpt) REFERENCES department(dpt_name)
 );

CREATE TABLE project
(
  proj_num   INT(10) NOT NULL,
  proj_name  CHAR(20) NOT NULL,
  start_date DATE NOT NULL,
  end_date   DATE DEFAULT '2015-04-01',
  of_dpt     CHAR(20) REFERENCES department(dpt_name),
  CONSTRAINT proj_pk PRIMARY KEY (proj_num,proj_name)
 );

CREATE TABLE table_1
(
l_1 INT(10) PRIMARY KEY,
l_2 INT(10),
l_3 INT(10)
 );

#INSERT INTO department(dpt_name,people_num) VALUES('部门',人数);

INSERT INTO department(dpt_name,people_num) VALUES('dpt1',11);
INSERT INTO department(dpt_name,people_num) VALUES('dpt2',12);
INSERT INTO department(dpt_name,people_num) VALUES('dpt3',10);
INSERT INTO department(dpt_name,people_num) VALUES('dpt4',15);

#INSERT INTO employee(id,name,age,salary,phone,in_dpt) VALUES(编号,'名字',年龄,工资,电话,'部门');

INSERT INTO employee(id,name,age,salary,phone,in_dpt) VALUES(01,'Tom',26,2500,119119,'dpt4');
INSERT INTO employee(id,name,age,salary,phone,in_dpt) VALUES(02,'Jack',24,2500,120120,'dpt2');
INSERT INTO employee(id,name,salary,phone,in_dpt) VALUES(03,'Jobs',3600,019283,'dpt2');
INSERT INTO employee(id,name,salary,phone,in_dpt) VALUES(04,'Tony',3400,102938,'dpt3');
INSERT INTO employee(id,name,age,salary,phone,in_dpt) VALUES(05,'Rose',22,2800,114114,'dpt3');

#INSERT INTO project(proj_num,proj_name,start_date,end_date,of_dpt) VALUES(编号,'工程名','开始时间','结束时间','部门名');

INSERT INTO project(proj_num,proj_name,start_date,end_date,of_dpt) VALUES(01,'proj_a','2015-01-15','2015-01-31','dpt2');
INSERT INTO project(proj_num,proj_name,start_date,end_date,of_dpt) VALUES(02,'proj_b','2015-01-15','2015-02-15','dpt1');
INSERT INTO project(proj_num,proj_name,start_date,end_date,of_dpt) VALUES(03,'proj_c','2015-02-01','2015-03-01','dpt4');
INSERT INTO project(proj_num,proj_name,start_date,end_date,of_dpt) VALUES(04,'proj_d','2015-02-15','2015-04-01','dpt3');
INSERT INTO project(proj_num,proj_name,start_date,end_date,of_dpt) VALUES(05,'proj_e','2015-02-25','2015-03-01','dpt4');
INSERT INTO project(proj_num,proj_name,start_date,end_date,of_dpt) VALUES(06,'proj_f','2015-02-26','2015-03-01','dpt2');

索引

目的：加快查询速率

ALTER TABLE employee ADD INDEX idx_id (id);  #在employee表的id列上建立名为idx_id的索引
CREATE INDEX idx_name ON employee (name);   #在employee表的name列上建立名为idx_name的索引
SHOW INDEX FROM employee; #查看索引

视图

目的：创建一张虚拟表，只选择自己关心的数据

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

导入

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

eg:
6   Alex    26  3000    123456  dpt1
7   Ken 27  3500    654321  dpt1
8   Rick    24  3500    987654  dpt3
9   Joe 31  3600    100129  dpt2
10  Mike    23  3400    110110  dpt1
11  Jim 35  3000    100861  dpt4
12  Mary    21  3000    100101  dpt2

注：导入文件时出现如下错误ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement，这个原因其实很简单，是因为在安装MySQL的时候限制了导入与导出的目录权限，只能在规定的目录下才能导入

show variables like '%secure%'; #查看 secure-file-priv 当前值

导出

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

备份

mysqldump -u root 数据库名>备份文件名;   #备份整个数据库
mysqldump -u root 数据库名 表名字>备份文件名;  #备份整个表

参考学习网站: 实验楼