MYSQL回顾（基础）

数据库简介

什么是数据库（DataBase，简称DB）

数据库，又称为数据管理系统，简而言之可视为电子化的文件柜——存储电子文件的处所，用户可以对文件中的数据运行新增、截取、更新、删除等操作[1]。 --《维基百科》

数据库即存放数据的仓库，只不过这个仓库是在计算机存储设备上，而且数据是按一定的格式存放的。过去人们将数据存放在文件柜里，现在数据量庞大，已经不再适用。数据库是长期存放在计算机内、有组织、可共享的数据即可。数据库中的数据按一定的数据模型组织、描述和储存，具有较小的冗余度、较高的数据独立性和易扩展性，并可为各种用户共享。

本质上数据库就是存储在电脑上的一个文件夹，而数据库表就是文件夹下的文件。

什么是数据库管理系统（DataBase Management System 简称DBMS）

用于管理数据库的软件系统。DBMS解决了如何科学地组织和存储数据，如何高效获取和维护数据的问题。

常见的DBMS

参考文章

MYSQL就是一个数据库管理软件。又叫数据管理系统，即DBMS（dababase management system）。大型数据库通常运行于一个独立的数据库服务器上，客户端或者其他服务器通过网络访问数据库。这就用到了数据库管理系统。MYSQL就是数据库管理软件，而非数据库。但通常我们所谈论的数据库，其实是在谈论DBMS。

关系型数据库（管理系统）

MySQL数据库
Microsoft SQL Server数据库
Oracle数据库
Postgresql数据库
Sybase数据库
DB2数据库
Access数据库

非关系型数据库

Mongodb数据库
Redis数据库
Hbase数据库
Memcaced数据库
BigTable数据库

mysql是目前各大企业使用的主流数据库。它主要的优势就是开放源代码，因为开放源代码这个数据库是免费的，现在是甲骨文公司的产品。 oracle主要用于银行、铁路、飞机场等。该数据库功能强大，软件费用高。也是甲骨文公司的产品。 sql server是微软公司的产品，主要应用于大中型企业，如联想、方正等。

数据库管理工具

navicat
mysql workbench

理解数据库服务器、数据管理系统、数据库、表与记录的关系

记录（record）：1 小明男二班 9岁（多个字段的信息组成一条记录，即数据库表文件中的一行内容）表（table）：student，class，school（即文件）数据库（database）：oldboy_stu（即文件夹）数据库管理系统（DBMS）：如mysql（是一个软件）数据库服务器：一台计算机（对内存性能要求比较高）

总结：

数据库服务器-：运行数据库管理软件数据库管理软件：管理-数据库数据库：即文件夹，用来组织文件/表表：即文件，用来存放多行内容/多条记录

MySQL简介

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下公司。MySQL 是最流行的关系型数据库管理系统，在 WEB 应用方面MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。

代码语言：javascript复制

mysql就是一个基于socket编写的C/S架构的软件

客户端软件
　　mysql自带：如mysql命令，mysqldump命令等
　　python模块：如pymysql

MYSQL安装与管理

linux下安装

代码语言：javascript复制

#二进制rpm包安装

yum -y install mysql-server mysql

macOS下安装

代码语言：javascript复制

brew install mysql

查看mysql版本

代码语言：javascript复制

mysql --version

启动mysql

代码语言：javascript复制

mysql.server start

设置mysql密码

代码语言：javascript复制

mysqladmin -u root password "123456"

修改mysql密码

代码语言：javascript复制

 mysqladmin -u root -p12345678(新密码) password 123456(旧密码)

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456'

源码安装

You must reset your password using ALTER USER statement before executing this statement.

代码语言：javascript复制

安装完mysql 之后，登陆以后，不管运行任何命令，总是提示这个

mac mysql error You must reset your password using ALTER USER statement before executing this statement.

解决方法：

step 1: SET PASSWORD = PASSWORD('your new password');

step 2: ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;

step 3: flush privileges;

登录设置密码

代码语言：javascript复制

初始状态下，管理员root，密码为空，默认只允许从本机登录localhost

设置密码

# mysqladmin -uroot password "123"        设置初始密码 由于原密码为空，因此-p可以不用

# mysqladmin -uroot -p"123" password "456"        修改mysql密码,因为已经有密码了，所以必须输入原密码才能设置新密码

命令格式:

# mysql -h172.31.0.2 -uroot -p456

# mysql -uroot -p

# mysql                    以root用户登录本机，密码为空

linux下破解密码

linux平台下，破解密码的两种方式

方法一：删除授权库mysql，重新初始化

代码语言：javascript复制

# rm -rf /var/lib/mysql/mysql #所有授权信息全部丢失！！！

# systemctl restart mariadb

# mysql

方法二：启动时，跳过授权库

代码语言：javascript复制

# vim /etc/my.cnf    #mysql主配置文件

[mysqld]

skip-grant-table

# systemctl restart mariadb

# mysql

MariaDB [(none)]> update mysql.user set password=password("123") where user="root" and host="localhost";

MariaDB [(none)]> flush privileges;

MariaDB [(none)]> q

# #打开/etc/my.cnf去掉skip-grant-table,然后重启

# systemctl restart mariadb

# mysql -u root -p123 #以新密码登录

SQL 语句

mysql服务端软件即mysqld帮我们管理好文件夹以及文件，前提是作为使用者的我们，需要下载mysql的客户端，或者其他模块来连接到mysqld，然后使用mysql软件规定的语法格式去提交自己命令，实现对文件夹或文件的管理。该语法即sql（Structured Query Language 即结构化查询语言）

代码语言：javascript复制

SQL语言主要用于存取数据、查询数据、更新数据和管理关系数据库系统,SQL语言由IBM开发。SQL语言分为3种类型：

1、DDL语句    数据库定义语言： 数据库、表、视图、索引、存储过程，例如CREATE DROP ALTER
2、DML语句    数据库操纵语言： 插入数据INSERT、删除数据DELETE、更新数据UPDATE、查询数据SELECT
3、DCL语句    数据库控制语言： 例如控制用户的访问权限GRANT、REVOKE

初识SQL语句

代码语言：javascript复制

#1. 操作数据库

        增：create database db1 charset utf8;

        查：show databases;

        改：alter database db1 charset latin1;

        删除: drop database db1;

#2. 操作库表

    先切换到文件夹下：use db1

        增：create table t1(id int,name char);

        查：show tables

        改：alter table t1 modify name char(3);

              alter table t1 change name name1 char(2);

        删：drop table t1;

#3. 操作表中的记录

        增：insert into t1 values(1,'egon1'),(2,'egon2'),(3,'egon3');

        查：select * from t1;

        改：update t1 set name='sb' where id=2;

        删：delete from t1 where id=1;

数据库的操作

数据库命名规则

可以由字母、数字、下划线、＠、＃、＄

区分大小写

唯一性

不能使用关键字如 create select

不能单独使用数字

最长128位

数据库CRUD

代码语言：javascript复制

# charset可省略

CREATE DATABASE 数据库名 charset utf8;

# 查看数据库

show databases;

# 查看或创建（如果没有）数据库

show create database db1;

# 查看当前数据库

select database();

选择数据库

# 进入数据库

USE 数据库名

# 删除数据库

DROP DATABASE 数据库名;

# 修改数据库

alter database db1 charset utf8;

MySQL使用存储引擎

方法1：建表时指定

代码语言：javascript复制

> create table innodb_t1(id int,name char)engine=innodb;

> create table innodb_t2(id int)engine=innodb;

> show create table innodb_t1;

> show create table innodb_t2;

方法2：在配置文件中指定默认的存储引擎

代码语言：javascript复制

/etc/my.cnf

[mysqld]

default-storage-engine=INNODB

innodb_file_per_table=1

代码语言：javascript复制

练习：创建四个表，分别使用innodb，myisam，memory，blackhole存储引擎

> create table t1(id int)engine=innodb;

> create table t2(id int)engine=myisam;

> create table t3(id int)engine=memory;

> create table t4(id int)engine=blackhole;

> quit

> ls /var/lib/mysql/db1/ #发现后两种存储引擎只有表结构，无数据

db.opt  t1.frm  t1.ibd  t2.MYD  t2.MYI  t2.frm  t3.frm  t4.frm

#memory，在重启mysql或者重启机器后，表内数据清空

#blackhole，往表内插入任何数据，都相当于丢入黑洞，表内永远不存记录

表的操作

创建表

代码语言：javascript复制

create table 表名(

字段名1 类型[(宽度) 约束条件],

字段名2 类型[(宽度) 约束条件],

字段名3 类型[(宽度) 约束条件]

);

#注意：

1. 在同一张表中，字段名是不能相同

2. 宽度和约束条件可选

3. 字段名和类型是必须的

例:

代码语言：javascript复制

create table article(

  id int(10) not null auto_increment,

  img text default null comment '缩略图',

  title varchar(80) default null comment '文章标题',

  summary varchar(300) default null comment '文章简介',

  content text default null comment '文章内容',

  createTime timestamp default null comment '发布时间',

  primary key(id)

)engine=InnoDB AUTO_INCREMENT=1 comment '文章表';

代码语言：javascript复制

mysql> create table student(

    -> id int not null auto_increment,

    -> name text default null comment '名字',

    -> gender enum('male', 'female') default 'male' comment '性别',

    -> primary key(id)

    -> ) engine=InnoDB AUTO_INCREMENT=1 comment '学生表';

代码语言：javascript复制

show tables; #查看数据库下所有表名

查看表结构

代码语言：javascript复制

desc 表名;

查看表详细结构，可加G

代码语言：javascript复制

show create table t1G; #查看表详细结构，可加G

例：

代码语言：javascript复制

desc student; # 等价于 describe student;


 ------- ----------------------- ------ ----- --------- ------- 

| Field | Type                  | Null | Key | Default | Extra |

 ------- ----------------------- ------ ----- --------- ------- 

| id    | int(11)               | YES  |     | NULL    |       |

| name  | varchar(50)           | YES  |     | NULL    |       |

| sex   | enum('male','female') | YES  |     | NULL    |       |

| age   | int(3)                | YES  |     | NULL    |       |

 ------- ----------------------- ------ ----- --------- -------

查数据

代码语言：javascript复制

select id,name,sex,age from student;

select * from student;

插入数据

代码语言：javascript复制

# 只插入id

insert into article(id) values(7);

# 插入一条空记录

insert into article() value();

# 插入记录时可以省略into

Insert article(id, title) value(10, '操作系统');

# 插入多条记录

insert into student(name, gender) values('mark', 'male'), ('jenny', 'female');

代码语言：javascript复制

# 插入两条记录

insert into student values

    -> (1,'jack',18,'male'),

    -> (2,'mark',81,'female')

    -> ;

# 插入两条只有id的记录

insert into t1(id) values

    -> (3),

    -> (4);

查看表结构

代码语言：javascript复制

> describe student; #查看表结构，可简写为desc 表名

> show create table studentG; #查看表详细结构，可加G

 ------- ----------------------- ------ ----- --------- ------- 

| Field | Type                  | Null | Key | Default | Extra |

 ------- ----------------------- ------ ----- --------- ------- 

| id    | int(11)               | YES  |     | NULL    |       |

| name  | varchar(50)           | YES  |     | NULL    |       |

| sex   | enum('male','female') | YES  |     | NULL    |       |

| age   | int(3)                | YES  |     | NULL    |       |

 ------- ----------------------- ------ ----- --------- -------

修改表结构

代码语言：javascript复制

# 修改表名 student表修改成person表

mysql> alter table student rename person;

# 添加字段 给peron表添加address字段

mysql> alter table person add address varchar(100);

# 删除字段 删除person表的address字段

mysql> alter table person drop address;

# 修改字段类型 用 modify

mysql> alter table person modify name char(10);

# 修改字段名 用change

mysql> alter table person change gender sex enum('male', 'female');

# 建表之后设置 primary key

mysql> alter table employee modify id int primary key;

语法

代码语言：javascript复制

1. 修改表名

      ALTER TABLE 表名

                          RENAME 新表名;

2. 增加字段

      ALTER TABLE 表名

                          ADD 字段名  数据类型 [完整性约束条件…],

                          ADD 字段名  数据类型 [完整性约束条件…];

      ALTER TABLE 表名

                          ADD 字段名  数据类型 [完整性约束条件…]  FIRST;

      ALTER TABLE 表名

                          ADD 字段名  数据类型 [完整性约束条件…]  AFTER 字段名;

3. 删除字段

      ALTER TABLE 表名

                          DROP 字段名;

4. 修改字段

      ALTER TABLE 表名

                          MODIFY  字段名 数据类型 [完整性约束条件…];

      ALTER TABLE 表名

                          CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];

      ALTER TABLE 表名

                          CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];

示例：

代码语言：javascript复制

1. 修改存储引擎

mysql> alter table service

    -> engine=innodb;

2. 添加字段

mysql> alter table student10

    -> add name varchar(20) not null,

    -> add age int(3) not null default 22;

mysql> alter table student10

    -> add stu_num varchar(10) not null after name;                //添加name字段之后

mysql> alter table student10                       

    -> add sex enum('male','female') default 'male' first;          //添加到最前面

3. 删除字段

mysql> alter table student10

    -> drop sex;

mysql> alter table service

    -> drop mac;

4. 修改字段类型modify

mysql> alter table student10

    -> modify age int(3);

mysql> alter table student10

    -> modify id int(11) not null primary key auto_increment;    //修改为主键

5. 增加约束（针对已有的主键增加auto_increment）

mysql> alter table student10 modify id int(11) not null primary key auto_increment;

ERROR 1068 (42000): Multiple primary key defined

mysql> alter table student10 modify id int(11) not null auto_increment;

Query OK, 0 rows affected (0.01 sec)

Records: 0  Duplicates: 0  Warnings: 0

6. 对已经存在的表增加复合主键

mysql> alter table service2

    -> add primary key(host_ip,port);       

7. 增加主键

mysql> alter table student1

    -> modify name varchar(10) not null primary key;

8. 增加主键和自动增长

mysql> alter table student1

    -> modify id int not null primary key auto_increment;

9. 删除主键

a. 删除自增约束

mysql> alter table student10 modify id int(11) not null;

b. 删除主键

mysql> alter table student10                                

    -> drop primary key;

复制表

代码语言：javascript复制

复制表结构＋记录 （key不会复制: 主键、外键和索引）

mysql> create table new_service select * from service;

只复制表结构

mysql> select * from service where 1=2;        //条件为假，查不到任何记录

Empty set (0.00 sec)

mysql> create table new1_service select * from service where 1=2; 

Query OK, 0 rows affected (0.00 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> create table t4 like employees;

删除表

代码语言：javascript复制

DROP TABLE 表名;

云数据库SQLServer sqlserver linux 数据库 sql

0 人点赞