MySQL数据库基础与操作

目录1. 数据库基础1.1 基本使用1.1.1 连接服务器1.1.2 服务器管理1.1.3 使用案例1.1.4 数据逻辑存储1.2 MySQL架构1.3 SQL分类1.4 存储引擎1.4.1 查看存储引擎1.4.2 存储引擎对比2. 库的操作2.1 创建数据库2.1.1 创建案例2.3 字符集和校验规则2.3.1 查看系统默认字符集以及校验规则2.3.2 查看数据库支持的字符集2.3.3 查看数据库支持的字符集校验规则2.3.4 校验规则对数据库的影响2.4 操作数据库2.4.1 查看数据库2.4.2 显示创建语句2.4.3 修改数据库2.4.4 数据库删除2.4.5 备份和恢复2.4.6 查看连接情况正文开始1. 数据库基础1.1 基本使用1.1.1 连接服务器输入mysql -h 127.0.0.1 -P 3306 -u root -p输出Enter password: **** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.21-log MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type help; or \h for help. Type \c to clear the current input statement. mysql注意如果没有写 -h 127.0.0.1 默认是连接本地如果没有写 -P 3306默认是连接3306端口号1.1.2 服务器管理执行 winr 输入services.msc打开服务管理器通过下图左侧停止暂停重启动按钮进行服务管理所谓安装数据库服务器只是在机器上安装了一个数据库管理系统程序这个管理程序可以管理多个数据库一般开发人员会针对每一个应用创建一个数据库。为保存应用中实体的数据一般会在数据库中创建多个表以保存程序中实体的数据。数据库服务器、数据库和表的关系如下1.1.3 使用案例创建数据库create database db1;使用数据库use db1;创建数据库表create table student( id int, name varchar(32), gender varchar(2) );表中插入数据insert into student (id, name, gender) values (1, 张三, 男); insert into student (id, name, gender) values (2, 李四, 女); insert into student (id, name, gender) values (3, 王五, 男);查询表中数据select * from student;1.1.4 数据逻辑存储1.2 MySQL架构MySQL 是一个可移植的数据库几乎能在当前所有的操作系统上运行如Unix/Linux、Windows、Mac和Solaris。各种系统在底层实现方面各有不同但是MySQL基本上能保证在各个平台上的物理体 系结构的一致性。1.3 SQL分类DDL【data definition language】 数据定义语言用来维护存储数据的结构代表指令:create, drop, alterDML【data manipulation language】 数据操纵语言用来对数据进行操作代表指令 insertdeleteupdateDML中又单独分了一个DQL数据查询语言代表指令selectDCL【Data Control Language】 数据控制语言主要负责权限管理和事务代表指令 grantrevokecommit1.4 存储引擎存储引擎是数据库管理系统如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。MySQL的核心就是插件式存储引擎支持多种存储引擎。1.4.1 查看存储引擎show engines;1.4.2 存储引擎对比2. 库的操作2.1 创建数据库语法CREATE DATABASE [IF NOT EXISTS] db_name [create_specification [, create_specification] ...] create_specification: [DEFAULT] CHARACTER SET charset_name [DEFAULT] COLLATE collation_name说明大写的表示关键字[ ]是可选项CHARACTER SET:指定数据库采用的字符集COLLATE:指定数据库字符集的校验规则2.1.1创建案例创建名为db1的数据库create database db1;说明当我们创建数据库没有指定字符集和校验规则时系统使用默认字符集utf8校验规则是utf8_ general_ ci创建一个使用utf8字符集的db2数据库create database db2 charsetutf8;创建一个使用utf字符集并带校对规则的 db3 数据库create database db3 charsetutf8 collate utf8_general_ci;2.3 字符集和校验规则2.3.1查看系统默认字符集以及校验规则show variables like character_set_database; show variables like collation_database;2.3.2查看数据库支持的字符集show charset;字符集主要是控制用什么语言。比如utf8就可以使用中文。2.3.3查看数据库支持的字符集校验规则show collation;2.3.4 校验规则对数据库的影响1. 不区分大小写创建一个数据库校验规则使用utf8_ general_ ci[不区分大小写]create database test1 collate utf8_general_ci;use test1;create table person(name varchar(20));insert into person values(a); insert into person values(A); insert into person values(b); insert into person values(B);2. 区分大小写创建一个数据库校验规则使用utf8_ bin[区分大小写]create database test2 collate utf8_bin;use test2;create table person(name varchar(20));insert into person values(a); insert into person values(A); insert into person values(b); insert into person values(B);3. 进行查询不区分大小写的查询以及结果mysql use test1; mysql select * from person where namea; ------ | name | ------ | a | | A | ------ 2 rows in set (0.01 sec)区分大小写的查询以及结果mysql use test2; mysql select * from person where namea; ------ | name | ------ | a | ------ 2 rows in set (0.01 sec)4. 结果排序不区分大小写的查询以及结果mysql use test1; mysql select * from person order by name; ------ | name | ------ | a | | A | | b | | B | ------区分大小写的查询以及结果mysql use test2; mysql select * from person order by name; ------ | name | ------ | A | | B | | a | | b | ------2.4 操作数据库2.4.1 查看数据库show databases;2.4.2 显示创建语句show create database 数据库名示例mysql show create database mytest; -------------------------------------------------------------------------- | Database | Create Database | -------------------------------------------------------------------------- | mysql | CREATE DATABASE mytest /*!40100 DEFAULT CHARACTER SET utf8 */ | --------------------------------------------------------------------------说明MySQL 建议我们关键字使用大写但是不是必须的。数据库名字的反引号 是为了防止使用的数据库名刚好是关键字/*!40100 default.... */ 这个不是注释表示当前mysql版本大于4.01版本就执行这句话2.4.3 修改数据库语法ALTER DATABASE db_name [alter_spacification [,alter_spacification]...] alter_spacification: [DEFAULT] CHARACTER SET charset_name [DEFAULT] COLLATE collation_name说明对数据库的修改主要指的是修改数据库的字符集校验规则。实例将 mytest 数据库字符集改成gbkmysql alter database mytest charsetgbk; Query OK, 1 row affected (0.00 sec) mysql show create database mytest; -------------------------------------------------------------------------- | Database | Create Database | -------------------------------------------------------------------------- | mytest | CREATE DATABASE mytest /*!40100 DEFAULT CHARACTER SET gbk */ | --------------------------------------------------------------------------2.4.4 数据库删除DROP DATABASE [IF EXISTS] db_ name;执行删除之后的结果数据库内部看不到对应的数据库对应的数据库文件夹被删除级联删除里面的数据表全部被删注意不要随意删除数据库2.4.5 备份和恢复备份# mysqldump -P3306 -u root -p 密码 -B 数据库名 数据库备份存储的文件路径示例将mytest库备份到文件退出连接# mysqldump -P3306 -u root -p123456 -B mytest D:/mytest.sql这时可以打开看看mytest.sql文件里的内容其实把我们整个创建数据库建表导入数据的语句都装载这个文件中。还原mysql source D:/mysql-5.7.22/mytest.sql;注意事项如果备份的不是整个数据库而是其中的一张表怎么做mysqldump -u root -p 数据库名 表名1 表名2 D:/mytest.sql同时备份多个数据库mysqldump -u root -p -B 数据库名1 数据库名2 ... 数据库存放路径如果备份一个数据库时没有带上-B参数 在恢复数据库时需要先创建空数据库然后使用数据库再使用source来还原。2.4.6 查看连接情况语法show processlist示例mysql show processlist; ------------------------------------------------------------------- | Id | User | Host | db | Command | Time | State | Info | ------------------------------------------------------------------- | 2 | root | localhost | test | Sleep | 1386 | | NULL | | 3 | root | localhost | NULL | Query | 0 | NULL | show processlist | -------------------------------------------------------------------可以告诉我们当前有哪些用户连接到我们的MySQL如果查出某个用户不是你正常登陆的很有可能你的数据库被人入侵了。以后大家发现自己数据库比较慢时可以用这个指令来查看数据库连接情况。