MySQL基础

1. 数据库介绍

  • 说明：

    • 数据库(Database，DB)是按照数据结构来组织、存储和管理数据的,并且是建立在计算机存储设备上的仓库

  • 特点：

    • 数据库指的是以一定方式储存在一起、能为多个用户共享、具有尽可能小的冗余度、与应用程序彼此独立的数据集合。简单来说可视为电子化的文件柜——存储电子文件的处所，用户可以对文件中的数据运行新增、截取、更新、删除等操作

  • 数据库系统的组成部分：

    • 数据库（Database System）：用于存储数据的地方

    • 数据库管理系统（Database Management System，DBMS）：用户管理数据库的软件

    • 数据库应用程序（Database Application）：为了提高数据库系统的处理能力所使用的管理数据库的软件补充

2. SQL语言

  • 说明：

    • SQL(Structured Query Language)即结构化查询语言，数据库管理系统专门通过SQL语言来管理数据库中的数据，与数据库通信

  •  优点：

    • SQL不是某个特定数据库供应商专有的语言。几乎所有重要的DBMS都支持SQL，学习此语言使你几乎能与所有数据库打交道

    • SQL简单易学。它的语句全都是由描述性很强的英语单词组成， 而且这些单词的数目不多

    • SQL尽管看上去很简单，但它实际上是一种强有力的语言，灵活 使用其语言元素，可以进行非常复杂和高级的数据库操作

  • DBMS专用的SQL：

    • SQL不是一种专利语言，而且存在一个标准委员会，他们试图定义可供所有DBMS使用的SQL语法，但 事实上任意两个DBMS实现的SQL都不完全相同

  • SQL语言是一种数据库查询和程序设计语言，其主要用于存取数据，查询数据，更新数据和管理数据库系统。SQL分为4个部分：

    • 数据定义语言（Data Definition Language，DDL）：

      • 数据库定义语言。主要用于定义数据库，表，视图，索引和触发器等。

      • CREATE语句：主要用于创建数据库，创建表，创建视图；

      • ALTER语句：  主要用于修改表的定义，修改视图的定义；

      • DROP语句：    主要用于删除数据库，删除表和删除视图等

    • 数据操作语言（Data Manipulation Language，DML）：

      • INSERT、UPDATE、DELETE语句；数据库操作语言。主要用于插入数据，更新数据，删除数据。

        • INSERT语句用于插入数据；

        • UPDATE语句用于更新数据；

        • DELETE语句用于删除数据

    • 数据查询语言（Data Query Language，DQL）：

      • SELECT语句。主要用于查询数据

    • 数据控制语言（Data Control Language ，DCL）语句：

      • 数据库控制语言。主要用于控制用户的访问权限。

        • GRANT语句用于给用户增加权限；

        • REVOKE语句用于收回用户的权限

3. 数据库分类

  • 说明：

    • 数据库的分类有很多种，具体的可分为两种：关系型数据库和非关系型数据库。

  • 关系型数据库（Relational database）：

    • 是创建在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。现实世界中的各种实体以及实体之间的各种联系均用关系模型来表示。关系模型是由埃德加·科德于1970年首先提出的，并配合“科德十二定律”。现如今虽然对此模型有一些批评意见，但它还是数据存储的传统标准。标准数据查询语言SQL就是一种基于关系数据库的语言，这种语言执行对关系数据库中数据的检索和操作。常见的关系型数据库有：MySQL、Oracle、SQL Server、Sybase、DB2、sqllite

  • 非关系型数据库（NoSQL）：

    • NoSQL一词最早出现于1998年，是Carlo Strozzi开发的一个轻量、开源、不提供SQL功能的关系数据库。当代典型的关系数据库在一些数据敏感的应用中表现了糟糕的性能，例如为巨量文档创建索引、高流量网站的网页服务，以及发送流式媒体。关系型数据库的典型实现主要被调整用于执行规模小而读写频繁，或者大批量极少写访问的事务。常见的非关系型数据库有：Redis、MongoDB、MemCache

  • RDBMS:关系数据库管理系统(Relational Database Management System)的特点：

    • 数据以表格的形式出现

    • 每行为各种记录名称

    • 每列为记录名称所对应的数据域

    • 许多的行和列组成一张表单

    • 若干的表单组成database

    • 数据库: 数据库是一些关联表的集合。
数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
列: 一列(数据元素) 包含了相同的数据, 例如邮政编码的数据。
行：一行（=元组，或记录）是一组相关的数据，例如一条用户订阅的数据。
冗余：存储两倍数据，冗余可以使系统速度更快。(表的规范化程度越高，表与表之间的关系就越多；查询时可能经常需要在多个表之间进行连接查询；而进行连接操作会降低查询速度。例如，学生的信息存储在student表中，院系信息存储在department表中。通过student表中的dept_id字段与department表建立关联关系。如果要查询一个学生所在系的名称，必须从student表中查找学生所在院系的编号（dept_id），然后根据这个编号去department查找系的名称。如果经常需要进行这个操作时，连接查询会浪费很多的时间。因此可以在student表中增加一个冗余字段dept_name，该字段用来存储学生所在院系的名称。这样就不用每次都进行连接操作了。)
主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
外键：外键用于关联两个表。
复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。
索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。
      RDBMS 术语

  • 所有的数据库管理系统都配备了一个开放式数据库连接（ODBC）驱动程序，令各个数据库之间得以互相集成

  • 数据库的概念：

    • 关系数据库没有数据表，关键字、主键、索引等也就无从谈起，数据表是关系数据库中一个非常重要的对象，是其它对象的基础，也是一系列二维数组的集合，用来存储、操作数据的逻辑结构。根据信息的分类情况。一个数据库中可能包含若干个数据表，每张表是由行和列组成，记录一条数据数据表就增加一行，每一列是由字段名和字段数据集合组成，列被称之为字段，每一列还有自己的多个属性，例如是否允许为空、默认值、长度、类型、存储编码、注释等

  • 综合区分理解：

    • 关系型数据库需要有表结构

    • 非关系型数据库是key-value存储的，没有表结构

4. MySQL介绍

  • 介绍：

    • MySQL是一个关系型数据库管理系统，由瑞典MySQL AB公司开发，目前属于Oracle旗下公司。MySQL由于性能高、成本低、可靠性好，已经成为最流行的开源数据库，因此被广泛地应用在Internet上的中小型网站中。是最流行的关系型数据库管理系统，在WEB应用方面MySQL是最好的RDBMS(Relational Database Management System：关系数据库管理系统)应用软件之一

  • 优点：

    • 使用C和C++编写，并使用了多种编译器进行测试，保证源代码的可移植性
    • 支持AIX、BSDi、FreeBSD、HP-UX、Linux、Mac OS、Novell NetWare、NetBSD、OpenBSD、OS/2 Wrap、Solaris、Windows等多种操作系统
    • 为多种編程语言提供了API。这些編程语言包括C、C++、C#、VB.NET、Delphi、Eiffel、Java、Perl、PHP、Python、Ruby和Tcl等
    • 支持多线程，充分利用CPU资源，支持多用户
    • 优化的SQL查询算法，有效地提高查询速度
    • 既能够作为一个单独的应用程序在客户端服务器网络环境中运行，也能够作为一个程序库而嵌入到其他的软件中
    • 提供多语言支持，常见的编码如中文的GB2312、BIG5 UTF-8，日文的Shift JIS等都可以用作数据表名和数据列名
    • 提供TCP/IP、ODBC和JDBC等多种数据库连接途径
    • 提供用于管理、检查、优化数据库操作的管理工具
    • 可以处理拥有上千万条记录的大型数据库;

  • 数据库作用介绍：

    • information_schema： ----  虚拟库，不占用磁盘空间，存储的是数据库启动后的一些参数，如用户表信息、列信息、权限信息、字符信息等
    • performance_schema： -- MySQL 5.5开始新增一个数据库：主要用于收集数据库服务器性能参数，记录处理查询请求时发生的各种事件、锁等现象 
    • mysql： ------------------------- 授权库，主要存储系统用户的权限信息 
    • test： ---------------------------  MySQL数据库系统自动创建的测试数据库 
    • sys： ---------------------------  MySQL5.7之后新增的默认数据库，存储系统的元数据信息 
    • sakila： ------------------------ MySQL5.7之后新增的默认数据库，MySQL样本数据库 
    • world： ------------------------- MySQL5.7之后新增的默认数据库，作用暂无介绍

  • 命名规则：

    • 可以由字母、数字、下划线、@、#、$ 
    • 区分大小写 
    • 唯一性 
    • 不能使用关键字 
    • 不能单独使用数字 
    • 最长128位

5. MySQL数据库操作

  • Linux安装：

    • yum install mysql-server
    • 1.解压tar包
cd /software
tar -xzvf mysql-5.6.21-linux-glibc2.5-x86_64.tar.gz
mv mysql-5.6.21-linux-glibc2.5-x86_64 mysql-5.6.21

2.添加用户与组
groupadd mysql
useradd -r -g mysql mysql
chown -R mysql:mysql mysql-5.6.21

3.安装数据库
su mysql
cd mysql-5.6.21/scripts
./mysql_install_db --user=mysql --basedir=/software/mysql-5.6.21 --datadir=/software/mysql-5.6.21/data

4.配置文件
cd /software/mysql-5.6.21/support-files
cp my-default.cnf /etc/my.cnf
cp mysql.server /etc/init.d/mysql
vim /etc/init.d/mysql   #若mysql的安装目录是/usr/local/mysql,则可省略此步
修改文件中的两个变更值
basedir=/software/mysql-5.6.21
datadir=/software/mysql-5.6.21/data

5.配置环境变量
vim /etc/profile
export MYSQL_HOME="/software/mysql-5.6.21"
export PATH="$PATH:$MYSQL_HOME/bin"
source /etc/profile

6.添加自启动服务
chkconfig --add mysql
chkconfig mysql on

7.启动mysql
service mysql start

8.登录mysql及改密码与配置远程访问
mysqladmin -u root password 'your_password'     #修改root用户密码
mysql -u root -p     #登录mysql,需要输入密码
mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'your_password' WITH GRANT OPTION;     #允许root用户远程访问
mysql>FLUSH PRIVILEGES;     #刷新权限
      源码安装mysql
      1. 解压
tar zxvf  mariadb-5.5.31-linux-x86_64.tar.gz   
mv mariadb-5.5.31-linux-x86_64 /usr/local/mysql //必需这样，很多脚本或可执行程序都会直接访问这个目录

2. 权限
groupadd mysql             //增加 mysql 属组 
useradd -g mysql mysql     //增加 mysql 用户 并归于mysql 属组 
chown mysql:mysql -Rf  /usr/local/mysql    // 设置 mysql 目录的用户及用户组归属。 
chmod +x -Rf /usr/local/mysql    //赐予可执行权限 

3. 拷贝配置文件
cp /usr/local/mysql/support-files/my-medium.cnf /etc/my.cnf     //复制默认mysql配置 文件到/etc目录 

4. 初始化
/usr/local/mysql/scripts/mysql_install_db --user=mysql          //初始化数据库 
cp  /usr/local/mysql/support-files/mysql.server    /etc/init.d/mysql    //复制mysql服务程序 到系统目录 
chkconfig  mysql on     //添加mysql 至系统服务并设置为开机启动 
service  mysql  start  //启动mysql

5. 环境变量配置
vim /etc/profile   //编辑profile,将mysql的可执行路径加入系统PATH
export PATH=/usr/local/mysql/bin:$PATH 
source /etc/profile  //使PATH生效。

6. 账号密码
mysqladmin -u root password 'yourpassword' //设定root账号及密码
mysql -u root -p  //使用root用户登录mysql
use mysql  //切换至mysql数据库。
select user,host,password from user; //查看系统权限
drop user ''@'localhost'; //删除不安全的账户
drop user root@'::1';
drop user root@127.0.0.1;
select user,host,password from user; //再次查看系统权限，确保不安全的账户均被删除。
flush privileges;  //刷新权限

7. 一些必要的初始配置
1）修改字符集为UTF8
vi /etc/my.cnf
在[client]下面添加 default-character-set = utf8
在[mysqld]下面添加 character_set_server = utf8
2）增加错误日志
vi /etc/my.cnf
在[mysqld]下面添加：
log-error = /usr/local/mysql/log/error.log
general-log-file = /usr/local/mysql/log/mysql.log
3) 设置为不区分大小写，linux下默认会区分大小写。
vi /etc/my.cnf
在[mysqld]下面添加：
lower_case_table_name=1

修改完重启：#service  mysql  restart
      源码安装mariadb

  • 服务端启动：

    • mysql.server start
    • [root@admin ~]# systemctl start mariadb #启动
[root@admin ~]# systemctl enable mariadb #设置开机自启动
Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.
[root@admin ~]# ps aux |grep mysqld |grep -v grep #查看进程，mysqld_safe为启动mysql的脚本文件，内部调用mysqld命令
mysql     3329  0.0  0.0 113252  1592 ?        Ss   16:19   0:00 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
mysql     3488  0.0  2.3 839276 90380 ?        Sl   16:19   0:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql 
                                                           --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/log/mariadb/mariadb.log 
                                                           --pid-file=/var/run/mariadb/mariadb.pid --socket=/var/lib/mysql/mysql.sock
[root@admin ~]# netstat -an |grep 3306 #查看端口
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN  
[root@admin ~]# ll -d /var/lib/mysql #权限不对，启动不成功，注意user和group
drwxr-xr-x 5 mysql mysql 4096 Jul 20 16:28 /var/lib/mysql
      linux平台下查看

  • 客户端登录：

    • 连接：
    mysql -h host -u user -p
 
    #常见错误：
        ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2), 
      　　     it means that the MySQL server daemon (Unix) or service (Windows) is not running.
退出：
    QUIT 或者 Control+D
    • 安装完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;
      You must reset your password using ALTER USER statement before executing this statement.
      初始状态下，管理员root，密码为空，默认只允许从本机登录localhost
设置密码
[root@admin~]# mysqladmin -uroot password "123"                设置初始密码 由于原密码为空，因此-p可以不用
[root@admin~]# mysqladmin -uroot -p"123" password "456"        修改mysql密码,因为已经有密码了，所以必须输入原密码才能设置新密码

命令格式:
[root@admin~]# mysql -h172.31.0.2 -uroot -p456
[root@admin~]# mysql -uroot -p
[root@admin~]# mysql                    以root用户登录本机，密码为空
      初始化密码

  • 库操作：

    • 创建数据库：
    # utf-8
    create database 数据库名称 default charset utf8 collate utf8_general_ci;
    # gbk
    create database 数据库名称 default character set gbk collate gbk_chinese_ci;
查看数据库：
    show databases;
    --显示所有数据库
    show create database dbname;
    -- 显示数据库字符编码
    show tables;
    -- 显示所有表
使用数据库：
    use db_name；
删除数据库：
    drop database db_name；
修改数据库名：
          -- 数据库名修改建议使用导入导出的方式比较安全

  • 用户管理：

    • 创建用户
    create user '用户名'@'IP地址' identified by '密码';
    create user 'admin'@'192.168.1.1' identified by '123';    #账户名admin，ip地址192.168.1.1，密码123可以使用该用户
    create user 'admin'@'192.168.1.%' identified by '123';    # %代表任意
    create user 'admin'@'%' identified by '123';              # 允许所有远程用户登录
删除用户
    drop user '用户名'@'IP地址';
修改用户
    rename user '用户名'@'IP地址'; to '新用户名'@'IP地址';;
修改密码
    set password for '用户名'@'IP地址' = Password('新密码')
   
PS：用户权限相关数据保存在mysql数据库的user表中，所以也可以直接对其进行操作（不建议）

  •   授权管理：

    • show grants for '用户'@'IP地址'                  -- 查看权限
grant  权限 on 数据库.表 to   '用户'@'IP地址'      -- 授权
revoke 权限 on 数据库.表 from '用户'@'IP地址'      -- 取消权限
flush privileges                                -- 将数据读取到内存中，从而立即生效。
    • all privileges  除grant外的所有权限
            select          仅查权限
            select,insert   查和插入权限
            ...
            usage                   无访问权限
            alter                   使用alter table
            alter routine           使用alter procedure和drop procedure
            create                  使用create table
            create routine          使用create procedure
            create temporary tables 使用create temporary tables
            create user             使用create user、drop user、rename user和revoke  all privileges
            create view             使用create view
            delete                  使用delete
            drop                    使用drop table
            execute                 使用call和存储过程
            file                    使用select into outfile 和 load data infile
            grant option            使用grant 和 revoke
            index                   使用index
            insert                  使用insert
            lock tables             使用lock table
            process                 使用show full processlist
            select                  使用select
            show databases          使用show databases
            show view               使用show view
            update                  使用update
            reload                  使用flush
            shutdown                使用mysqladmin shutdown(关闭MySQL)
            super                   使用change master、kill、logs、purge、master和set global。还允许mysqladmin调试登陆
            replication client      服务器位置的访问
            replication slave       由复制从属使用
      对于权限
    •  对于目标数据库以及内部其他：
            数据库名.*           数据库中的所有
            数据库名.表          指定数据库中的某张表
            数据库名.存储过程     指定数据库中的存储过程
            *.*                所有数据库
      对于数据库
    •             用户名@IP地址         用户只能在改IP下才能访问
            用户名@192.168.1.%   用户只能在改IP段下才能访问(通配符%表示任意)
            用户名@%             用户可以再任意IP下访问(默认IP地址为%)
-- 实例：
            grant all privileges on db1.tb1 TO '用户名'@'IP'

            grant select on db1.* TO '用户名'@'IP'

            grant select,insert on *.* TO '用户名'@'IP'

            revoke select on db1.tb1 from '用户名'@'IP'
      对于用户
      # 启动免授权服务端：
#可在配置文件[mysqld]中写入 或 启动参数中使用
mysqld --skip-grant-tables

# 客户端
mysql -u root -p

# 修改用户名密码
update mysql.user set authentication_string=password('666') where user='root';
flush privileges;
      忘记密码
    • #外键绑定两个主键
create table db1(
    cid int not null auto_increment,
    id1 int not null,
    id2 int,
    primary key(cid,id1)
    )engine=innodb default charset=utf8;


create table db2(
    sid int not null auto_increment primary key,
    ic1 int,
    ic2 int,
    constraint db2_db1 foreign key(ic1,ic2) references db1(cid,id1)
    )engine=innodb default charset=utf8;



#外键 唯一 一对一演示
create table user_info(
    uid int not null auto_increment primary key,
    name varchar(32) not null,
    usertype int not null
    )engine=innodb default charset=utf8;


create table admain_info(
    id int not null auto_increment primary key,
    user_id int not null,
    unique admin_user (user_id),
    constraint admin_user foreign key(user_id) references user_info(uid)
    )engine=innodb default charset=utf8;



insert into user_info(name,usertype) values("alex",1),("egon",2),("tom",3);

insert into admain_info(user_id) values(1),(2),(3);




#外键 唯一 一对多演示

create table user(
    uid int not null auto_increment primary key,
    name varchar(32) not null,
    gender ENUM("男","女") not null
    )engine=innodb default charset=utf8;


create table host(
    hid int not null auto_increment primary key,
    name varchar(32) not null
    )engine=innodb default charset=utf8;


create table user_host(
    id int not null auto_increment primary key,
    uid int not null,
    hid int not null,
    unique uid_hid (uid,hid),
    constraint user_host_user foreign key(uid) references user(uid),
    constraint user_host_host foreign key(hid) references host(hid)
    )engine=innodb default charset=utf8;


insert into user(name,gender) values("alex","男"),("egon","男"),("tom","男");

insert into host(name) values("host1"),("host2"),("host3");


insert into user_host(uid,hid) values(1,1),(1,2),(1,3);


insert into user_host(uid,hid) values(2,1),(2,2),(2,3);

insert into user_host(uid,hid) values(3,1),(3,2),(3,3);




#外键 一对多演示
create table user_info(
    uid int not null auto_increment primary key,
    name varchar(32) not null,
    usertype int not null
    )engine=innodb default charset=utf8;


create table admin_info(
    id int not null auto_increment primary key,
    user_id int not null,
    constraint admin_user foreign key(user_id) references user_info(uid)
    )engine=innodb default charset=utf8;



insert into user_info(name,usertype) values("alex",1),("egon",2),("tom",3);

insert into admin_info(user_id) values(1),(2),(3);
      外键补充（需要回顾）
      #1. 修改配置文件
[mysqld]
default-character-set=utf8 
[client]
default-character-set=utf8 
[mysql]
default-character-set=utf8

#mysql5.5以上：修改方式有所改动
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8

#2. 重启服务
#3. 查看修改结果：
\s
show variables like '%char%'
      字符编码
posted @ 2017-12-13 19:17  焦国峰的随笔日记  阅读(422)  评论(0)    收藏  举报
// ############################### // ##############################