一、数据库与MySQL的基本概念:
数据库(database)的定义:
保存有组织的数据的容器(一个文件或者一组文件)。
误区:
人们经常用数据库这个词来代表他们所使用的的软件,例如MySQL、Redis等,这是不正确的,确切地说,数据库软件应该称为 DBMS(数据库管理系统)。
数据库是通过数据库软件来创建和操作的容器(一个文件或者一组文件)。我们并不直接访问数据库,而是通过数据库软件来访问数据库。
MySQL是数据库管理软件,而不是数据库本身。
什么是SQL:
SQL = Structure Queury Language,结构化查询语言。
SQL是一种专门用来与数据库通信的语言(类似C语言)。
SQL与C、Java这些语言不同的地方在于,SQL由很少的语句构成,只提供增删改查等一些基本操作。
几乎所有的DBMS(数据库软件)都支持SQL语言,但事实上任意两个DBMS实现的SQL都不完全相同。
MySQL被广泛使用主要是因为以下几个原因:
(1)成本:MySQL是开源的,一般可以免费使用、免费修改;
(2)性能:MySQL执行非常快;
(3)可信赖:很多著名网站、公司都在使用MySQL;
(4)简单:容易安装,容易使用。
性能快,成本低,易使用,可信赖
共享型的DBMS 与 C/S型的DBMS:
DBMS(数据库软件)从使用方式的区别上可分为两类:
(1)基于共享文件系统的DBMS:
例如Microsoft Access,FileMaker,这种DBMS用于桌面用途,通常不用于高端或者更关键的应用(Samba服务器应该也属于这种类型);
(2)基于 服务器-客户机 的DBMS:
与数据打交道的只有服务器。
关于数据的添加、删除、更新都是由服务器来完成,而这些请求来自于客户机。
为进行数据库交互,客户机需要与服务器进行通信。
数据文件对于客户机是隐藏的。
(由此可见,共享文件系统形式的DBMS适合于少量用户、数据公开类型的业务,当用户增多时很难管理。所有MySQL这类数据库(隐藏数据文件,只由服务器直接处理数据,客户机的请求需要现到达服务器进行排队处理)是更高效更安全更强大的形式。)
二、关系数据库(RDBMS)的几个常用术语:
数据库:
一些关联表的集合。
表:
相当于是文件柜(database)中的一个文件。MySQL可以同时管理多个database,一个database中又可以存在多个table。
每个表用于存储同一种类型的数据,不应该将不同类型的数据存在在一张表中,否则会导致检索非常困难。
表具有一些特性,这些特性定义了数据在表中如何存储,例如可以存储什么样的数据、数据如何分解、各部分信息如何命名等等。描述表的这组信息就是表的模式(schema)。
列:
表由列组成。所有表都有一个列或多个列组成。可以这样理解:
表是一个网格(类似于excel),每一列存储着特定的信息,例如在顾客表中,一列存储顾客编号,一列存储姓名,一列存储地址,等等,这就是列。
行:
表中纵向为列,横向为行,例如上面的例子中一行就可以表示一个顾客的完整信息。
冗余:
存储两倍数据,冗余降低了性能,但提高了数据的安全性。
主键:
每一个`行`的`唯一标识`。例如上面的例子中适合选用顾客编号作为主键。
(1)任意两行都不能具有相同的主键值;
(2)每行都只能有一列做为主键(也可以是选用多列共同组成主键);
(3)应尽量避免修改主键的值
外键:
用于关联两个表。(待补充)
复合键:
(待补充)
索引:
(待补充)
参照完整性:
(待补充)
简单理解:把数据库想象成一个文件夹,表就是其中的多个excel,一个excel文件就是一个表,excel中的列、行跟表中的列、行意义相同。
关系数据库的特点:
RDBMS = Relational Database Management System
(1)数据以表格的形式出现;
(2)每行为各种记录名称;
(3)每列为记录名称所对应的数据域;
(4)许多的行和列组成一张表;
(5)若干的表组成database。
简单理解:跟Excel的特点一样
Tips:
-
如何为MySQL添加用户: (非root的普通用户)
为MySQL添加用户 -
如何配置MySQL的端口和最大连接数:
通过修改配置文件:/etc/my.cnf -
如何修改MySQL的用户密码:
《linux下忘记密码怎么办》
三、MySQL的基本操作:
0. 配置MySQL:
安装MySQL:
sudo apt-get install mysql-server
yum -y install mysql
查看MySQL所使用的配置文件:
mysql --help | grep 'my.cnf'
order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf
my.cnf 是 MySQL启动时加载的配置文件,一般会放在MySQL的安装目录下,安装MySQL后,系统中会有多个my.cnf文件,有些是用于测试的。
通过上述命令,可以查看到 MySQL在启动时会默认搜寻的my.cnf的目录,顺序排前的优先。
在上面的例子中,MySQL会优先去 /etc/ 目录下寻找 my.cnf 配置文件,如果没有找到,则会转到 /etc/mysql/ 目录下查找 my.cnf 配置文件,如果仍未找到,最后转到 ~/. 目录下查找 my.cnf配置文件。
以Ubuntu系统安装MySQL 5.7 版本为例,/etc/my.cnf 配置文件并不存在,MySQL在启动时会使用 /etc/mysql/my.cnf配置文件,其文件内容如下:
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
!includedir /etc/mysql/conf.d
!includedir /etc/mysql/mysql.conf.d
说明 /etc/mysql/my.cnf 只是个中转文件,需要继续包含 /etc/mysql/mysql.conf.d 和 /etc/mysql/conf.d 中的配置文件。
打开 /etc/mysql/mysql.conf.d/mysqld.cnf 配置文件,内容如下:
#
# The MySQL database server configuration file.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
[mysqld]
#
# * Basic Settings
#
user = mysql
bind_address = 127.0.0.1
mysqlx-bind-address = 127.0.0.1
#
# * Fine Tuning
#
key_buffer_size = 16M
myisam-recover-options = BACKUP
#
# * Logging and Replication
#
log_error = /var/log/mysql/error.log
# slow_query_log = 1
# slow_query_log_file = /var/log/mysql/mysql-slow.log
# long_query_time = 2
# log-queries-not-using-indexes
# The following can be used as easy to replay backup logs or for replication.
# note: if you ar setting up a replication slave, see README.Debian about
# other settings you may need to change.
#
# server-id = 1
# log_bin = /var/log/mysql/mysql-bin.log
# binlog_expire_logs_seconds = 2592000
max_binlog_size = 100M
# ------>
# * My Custom Add:
innodb_lock_wait_timeout = 30
使用命令:
mysql --help --print-defaults
可以查看MySQL中各个配置项的当前默认值。
在配置文件中修改配置文件中的配置项后, 需重启MySQL以使新的配置项默认值生效,以 innodb_lock_wait_timeout 配置项(死锁等待超时时间)为例,MySQL的默认值为 50,将其修改为30后重启MySQL并查询新的默认值:
root@linux: service mysql restart //重启mysql
mysql> show global variables like "innodb_lock_wait_timeout";
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 30 |
+--------------------------+-------+
1 row in set (0.10 sec)
在MySQL官网中可以查看到 关于MySQL-server的配置项列表:
(PS:修改链接中的“5.7”为对应的版本号可转到其他MySQL版本说明)
https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html
关于InnoDB的配置项列表:
https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html
1. 连接:(-u)
[root@linux] mysql -u root -p
Enter password:********
mysql> exit
Bye
这是一种简单的登录方式,而在现实中,管理登录受到密切保护。
首次登录可使用空密码,直接回车。
登录后修改密码:
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456'
2. 创建数据库:(create database *)
CREATE DATABASE <数据库名>;
//举例:
mysql> create database runoob_20201115;
//这样就创建了一个名为 runoob_20201115 的数据库
3. 删除数据库:(drop database *)
DROP DATABASE <数据库名>;
//举例:
mysql> drop database runoob_20201115;
//删除名为 runoob_1115 的数据库
4. 选择数据库:(use *)
USE <数据库名>;
//举例:
mysql> ues runoob;
//选择使用runoob这个数据库
5. 数据类型:(数值、日期/时间、字符/字符串)
MySQL中定义数据字段的类型对数据库的优化是非常重要的。
MySQL支持多种类型数据,大致可分以上三种。
6. 创建数据表:(create table *)
CREATE TABLE <table_name> (column_name column_type);
//举例:
mysql> create table runoob_20201115 (
-> runoob_id INT NOT NULL AUTO_INCREMENT, //column_name, column_type)
-> runoob_title VARCHAR(100) NOT NULL,
-> runoob_author VARCHAR(40) NOT NULL,
-> submission_date DATE,
-> PRIMARY KEY (runoob_id)
-> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affeceted (0.16 sec)
mysql>
解析:
几个column_type(列的属性)解析:
(1)AUTO_INCREMENT
定义列为自增
的属性,一般用于主键,数值会自动加1;
(2)PRIMARY KEY
用于定义一个列为主键(可定义多个列为主键,逗号隔开);
(3)EGINE
设置存储引擎;
(4)CHARSET
设置编码
ps:
如果用换行符 ->,一行写下来,形式是这样的:
mysql> create table runoob_20201115 (runoob_id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(runoob_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
如果再次创建同名的table,会提示错误:already exists
7. 删除数据表:(drop table *)
DROP TABLE <table_name表名>;
//举例:
mysql> drop table runoob_20201115;
//删除名为 runoob_20201115 的表
8. 向表中插入一行数据:(insert into …)
INSERT INTO <table_name> (field1, field2, ... fieldN)
VALUES
(value1, value2, ... valueN);
//举例:
mysql> insert into runoob_tb1
-> (runoob_title, runoob_author, submission_date)
-> values
-> ("学习", "菜鸟教程", NOW());
Query OK, 1 rows affected, 1 warning (0.02 sec)
mysql> select * from runoob_tb1;
//显示出runoob_tb1表中的所有内容
9. 查询表中数据:(select … from …)
SELECT <column_name1, column_name2> FROM <table_name>
[WHERE clause]
[LIMIT N]
[OFFSET M]
[ORDER BY <column_name>];
//举例:
mysql> select * from runoob_tb1; //显示表中所有内容
mysql> select runoob_title from runoob_tb1; //只显示runoob_title这一列
mysql> select runoob_title, runoob_author from runoob_tb1; //显示两行
mysql> select * from runoob_tb1 where runoob_title=2;//按runoob_title=2的条件进行过滤
mysql> select * from runoob_tb1 where runoob_title=2 OR runoob_author="嘿嘿";
mysql> select * from runoob_tb1 limit 3; //只显示前3行
mysql> select * from runoob_tb1 offset 4; //从第4行开始显示
mysql> select * from runoob_tb1 limit 3 offset 4;
mysql> select * from runoob_tb1 order by runoob_title; //按runoob_title进行排序
Tips:
- WHERE : 表示的是查询的过滤条件,它是一个表达式,支持操作符 =, <>, !=, <, >, >=, <=, BETWEEN,同时支持 AND 、OR 的过滤条件组合;
- LIMIT: 用于限定返回的记录数;
- OFFSET: 开始查询的数据偏移量,默认为0;
- ORDER BY: 将查询数据排序后再返回数据;
10. 修改表中数据:(update … set …)
UPDATE <table_name> SET <field1 = new_value1, field2 = new_value2> [WHERE Clause]
//举例:
mysql> update runoob_tb1 set runoob_title = "学习20201115" where runoob_id=1; //修改runoob_id=1这一行的runoob_title的值为新值
mysql> update runoob_tb1 set runoob_title = "学习20201115"; //修改所有行的runoob_title列的值都为新值
11. 删除表中的数据:(delete from …)(删除某一行元素)
DELETE FROM <table_name> [WHERE Clause]
//举例:
mysql> delete from runoob_tb1 where runoob_author="呵呵"; //删除runoob_author这一列值为“呵呵”的行,如果有多个行的这一列值都是“呵呵”,则全部删除
mysql> delete from runoob_tb1; //删除表中所有内容
12. 模糊搜索:(like *)
SELECT <field1> FROM <table_name> WHERE <field1> LIKE <condi>
//举例:
mysql> select * from runoob_tb1 where runoob_author="计算机原理"; //这是使用where的严格匹配,必须“=”完全相等
mysql> select * from runoob_tb1 where runoob_author like "%原理";
mysql> select * from runoob_tb1 where runoob_author like "计算机%";
Tips:
- % 相当于 * ,表示通配。
13. 修改表的列字段或者表名:(alter table …)
(alter:v. (使)改变,改动。(键盘的ALT即为alter的缩写))
//总结:
ALTER TABLE ... DROP ... //删除列
ALTER TABLE ... ADD ... //添加列
ALTER TABLE ... MODIFY ... //修改列的数据类型
ALTER TABLE ... CHANGE ... //
ALTER TABLE ... RENAME TO ... //修改表名
//插入、删除一列:
ALTER TABLE <table_name> DROP <column_name>; //删除column_name这一列
ALTER TABLE <table_name> ADD <column_name> [TYPE]; //在表的最后面添加一个名为column_name的列。
//注意TYPE字段必须指定,否则会报错。类似INT/CHAR/DATE,创建时系统需要知道要分配多少内存。
//NOT NULL字段如不指定,默认为NULL
//指定新添加的列所插入的位置:
ALTER TABLE <table_name> ADD <column_name> FIRST; //插入到第一列
ALTER TABLE <table_name> ADD <column_name> AFTER <column_name_2>; //插入到column_name_2这一列的后面
//修改列的名称:
ALTER TABLE <table_name> MODIFY <column_name> [CHAR(10)]; //修改column_name这一列的类型为CHAR(10)
//修改表名:
ALTER TABLE <table_name> RENAME TO <new_table_name>;
//举例:
//1. 添加:
//朴素添加:
mysql> alter table runoob_tb3 add runoob_column_1 INT NOT NULL; //在表runoob中添加runoob_column_1列,类型为INT且非空,此时默认值为0
//指定默认值的添加:
mysql> alter table runoob_tb3 add runoob_column_2 INT default 20; //设这一列的默认值是20
//指定位置的添加:
mysql> alter table runoob_tb3 add runoob_column_3 INT NOT NULL FIRST; //添加到第一列
mysql> alter table runoob_tb3 add runoob_column_4 INT NOT NULL AFTER runoob_column_2; //添加到 runoob_column_2 这列的后面
//2. 删除:
mysql> alter table runoob_tb3 drop runoob_column_1; //删除 runoob_column_1 这一列
mysql> alter table runoob_tb3 drop runoob_column_2, drop runoob_column_3; //同时删除多列
mysql> alter table runoob_tb3 drop runoob_column_4, add runoob_column_20; //同时既删且添
//3. 修改:
// MODIFY 修改列属性:(TYPE)
mysql> alter table runoob_tb3 modify runoob_column_5 char(10); //把 runoob_column_5 这列的属性改为char(10)
// CHANGE 修改列名+列属性:
mysql> alter table runoob_tb3 change runoob_column_6 runoob_column_20 char(20); //同时修改runoob_column_6的名字和类型
mysql> alter table runoob_tb3 modify runoob_column_5 INT NOT NULL DEFAULT 50; //修改类型+默认值
//修改表名:
mysql> alter table runoob_tb3 rename to runoob_alter_test; //修改runoob_tb3的表名为runoob_alter_test
Tips:
-
是否可以仿照 ALTER TABLE … RENAME TO …的方式修改库的名字?
不能。在MySQL 5.1.23之前的旧版本中,可以使用 RENAME DATABASE 来重命名数据库,
但此后的版本,因为安全考虑,删除了这一条命令。
MySQL新版本中如何修改库名?
无法直接修改,只能间接的达到修改库名的效果:先导出数据,新建库,再导入数据
。 -
如果需要修改列中的值,使用update命令,而不是alter:
mysql> UPDATE runoob_tb3 SET runoob_title="更新的值";
mysql> UPDATE runoob_tb3 SET runoob_title="更新的值" WHERE runoob_id=1;
由此可见,alter命令用于修改列的属性(ADD/DROP/MODIFY/CHAGE/RENAME),而update命令用于修改列中的数值(SET)。