MYSQL笔记

一 MySQL数据库的概述   1.1 数据库的概念       数据库是用来存储数据的一个仓库；       数据库的种类：层次式数据库，网络式数据库，关系型数据库，非关系型数据库       1.2 关系型数据库         使用关系模型来存储数据的数据库称为关系型数据库，能设计对应的字段，并且 根据字段来进行存储数据的；        常见的数据库：   商业数据库     Oracle SQLserver DB2 Sybase 开源数据库 MySQL SQLLite  二 数据库的安装和配置  2.1 数据库的安装         步骤1 ：安装的图解                  步骤2 : 注意的点 路径问题                       网络和严格的配置              字符集的配置             添加到windows的服务和全局配置环境           设置用户和密码及其远程访问               注意点： 安装的路径不要有中文或者空格 默认的端口式3306，不要去修改 使用命令窗口进行连接的命令格式：mysql -u 用户名 -p 密码 -h 主机地址，           像登陆： mysql -u root -p root -h localhost        4. 退出数据库 quit或者exit;     2.2 安装的时候出现问题       问题1：       在安装的过程中，MySQL server Configuration最后一步没有办法执行。                解决方案：       结束当前界面，找到Mysql安装目录下的bin目录，右键单击MySQLInstanceConfig.exe文件->管理员权限执行。          问题2：     报“Can't connect to MySQL server on 'localhost' (10061)“错误    解决方案：   在DOS下进入BIN目录C:\Program Files\MySQL\MySQL Server 5.4\bin然后，直接输入net start mysql    然后enter就可以了。   问题3：   删除服务 MySQL mysql删除服务 无法删除服务     解决方案：    选择管理员来运行CMD              使用命令 sc delete MySQL来解决服务被占用          问题4：    删除MYSQL 解决方法：   方案1：      使用MYSQL的安装程序来进行删除操作，最简单有效   方案2：      删除对应的文件夹，，查看后台进程，在用户了了吗查看MYSQL的进程，停止进程，然后找到对应的注册列表进行删除        步骤1：进入CMD，输入 regedit指令      步骤2：查找注册列表删除信息                                需要删除的内容                1、HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\Eventlog\Application\MySQL 目录删除      2、HKEY_LOCAL_MACHINE\SYSTEM\ControlSet002\Services\Eventlog\Application\MySQL 目录删除      3、HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application\MySQL 目录删除                可能存在一个文件夹里面存有信息；   三 数据库的原理 3.1  MySQL数据库服务器、数据库和表的关系     所谓安装数据库服务器，只是在机器上装了一个数据库管理程序，这个管理程序可以管理多个数据库，一般开发人员 会针对每一个应用创建一个数据库。为保存应用中实体的数据，一般会在数据库创建多个表，以保存程序中实体的数据。     数据库服务器、数据库和表的关系如图所示：        3.2  数据在数据库中的存储方式        数据库中表的结构，由行和列组成，其中行式用来存储对应的实际的数据称为记录，而是第一列是规定数据的存储的 数据的类型和对应数据的名称称为字段；       结构如下：               3.3 结构化数据库使用的语言SQL         SQL的全称是： Structured Query Language, 结构化查询语言          o    非过程性语言          o    美国国家标准局(ANSI）与国际标准化组织（ISO）已经制定了SQL标准          o    为加强SQL的语言能力，各厂商增强了过程性语言的特征                •    如Oracle的PL/SQL 过程性处理能力                •    SQL Server、Sybase的T-SQL          o    SQL是用来存取关系数据库的语言，具有查询、操纵、定义和控制关系型数据库的四方面功能。开源划分为4种类型：                  DQL 数据查询语言                  DML 数据操作语言                  TPL   事务处理语言                  DCL   数据控制语言     四 数据库的操作   4.1 有关库的操作  查看数据库的操作     查看所有的数据库的操作            命令： show databases; 查看指定的数据库            命令：show create database 指定的库名；                      查看当前使用的数据库                       命令： select database();                          创建数据库 创建使用默认的字符集的数据库                       命令：create database 库名；   创建指定字符集的数据库                                命令：create database 库名  character set 字符集（utf8/gbk）;                           创建数据库指定字符集并且带上校验规则                       命令： create database 库名 character set 字符集（utf8/gbk）collacte 校对规则（utf8_bin）                           使用数据库             命令： use 库名；                    删除数据库的操作            命令：drop database 库名；   修改数据库的操作           命令： alert dabase 库名 character set 字符集 collate 校对规则；   扩展：        1. 查看mysql存储位置 :            命令：show global variables like "%datadir%";                 4.2 有关表的操作 查看表的操作 查看所有的表                      命令：show tables; 查看指定的表信息                      命令：show create table 表名；                                 show create table 表名\G；                                查看指定表的结构                       命令：desc 表名；                          创建表的操作 创建带有默认字符集的表                       命令：create table 表名（字段（属性名，属性类型），字段）   创建设置字符集的表                      命令：create tbale 表名(字段（属性名，属性类型），字段) charset 字符类型 engine=数据引擎；                                      注意：创建表时，要根据需保存的数据创建相应的列，并根据数据的类型定义相应的列类型。                   修改表的操作 修改表的名字                        命令：alter table 表名字 rename to 新的表名字；   修改表的属性（字符集或者引擎）                        命令：alter table 表名 charset 字符集 engine=字符引擎；                           增加一个字段（字段名，字段类型，字段的位置）                        命令： aleter table 表名 add 字段名称 字段类型 字段位置（默认在最后）                            修改字段的类型                       命令：aleter table 表名 change 字段名  新字段  新字段类型 新字段位置                              修改字段的的顺序                             命令：alter table  表名 modify 字段 新字段 新字段的类型 新字段的位置       删除表的操作            命令：drop table 表名；   表格的字段约束            o    定义主键约束 •    primary key:不允许为空，不允许重复 •    删除主键：alter table tablename drop primary key ; •    主键自动增长 ：auto_increment         o    定义唯一约束 •      unique   •    例如：name varchar(20) unique         o    定义非空约束 •      not null   •      例如：salary double not null         o    外键约束        4.3 数据库的数据操作 插入数据 根据全部字段插入数据                      命令： insert into 表名 values(字段对应的数据) 根据全部字段插入多条数据                      命令： insert into 表名 values(字段对应的数据)，(字段对应的数据) 根据部分字段插入一条数据                      命令：insert into 表名（需要的字段名） values(字段对应的数据) 根据部分字段插入多条数据                      命令：insert into 表名（需要的字段名） values(字段对应的数据)，(字段对应的数据)      注意点： a.    插入的数据应与字段的数据类型相同。 b.    数据的大小应在列的规定范围内，例如：不能将一个长度为80的字符串加入到长度为40的列中。 c.    在values中列出的数据位置必须与被加入的列的排列位置相对应。 d.    字符和日期型数据应包含在单引号中。 e.    插入空值：不指定或insert into table value(null) f.    如果要插入所有字段可以省写列列表，直接按表中字段顺序写值列表   删除数据 命令：delete  from  表名 (where 条件)； 命令： turncat  table 表名；   修改数据               命令：update 表名 set 条件 where 条件；                 UPDATE语法可以用新值更新原有表行中的各列。    SET子句指示要修改哪些列和要给予哪些值。    WHERE子句指定应更新哪些行。如没有WHERE子句，则更新所有的行。   查询数据 基本查询                      命令：select */（指定列名） from 表名；                      注意点：                     select 指定查询哪些列的数据。 column指定列名。 *号代表查询所有列。 from指定查询哪张表。 DISTINCT可选，指显示结果时，是否剔除重复数据    查询中进行运算                       命令：select 列名加上运算 from 表名；   查询结果的列名取别名                        命令：select 列名 as 别名 from 表名；                         注意点：                                    其中的as开源进行省略；   带条件查询                       命令： select 列 from 表名字 where 条件；                       where中结合常见的运算符号：比较，bettwenn....and，in,like和逻辑运算符                                         注意点：                      Like语句中，% 代表零个或多个任意字符，_ 代表一个字符，例first_name like ‘_a%’;   使用高级查询 排序查询                                                                 注意点：                                  Order by 指定排序的列，排序的列既可是表中的列名，也可以是select 语句后指定的列名。                                  Asc 升序（默认）、Desc 降序                                  ORDER BY 子句应位于SELECT语句的结尾。        分组查询                                                                Having子句的使用：                                                                注意点：                                   where和having区别：where在分组前进行条件过滤，having在分组后进行条件过滤。使用where                               的地方都可以用having替换。但是having可以使用分组函数，而where后不可以使用。   聚合函数的查询                                       限制查询                              4.4 多表查询  多表设计——外键约束             定义外键约束             语法：foreign key(ordersid) references orders(id)             多表设计中常常伴随着外键约束的产生。           联系的属性及两个实体的主标识形成关系表。  多表设计中三种实体关系 M:N的联系引入关系表                      关系表的主键为两个实体主标识的组合。                                                      将关系的属性及非多方的主标识加入到多方表          多方表的外键是非多方实体的主标识            1:N 的联系可不引入关系表                                        1:1的联系不必引入关系表                     外键:          用来通知数据库表与表字段之间的对应关系, 并让数据库帮我们维护这样关系的键就叫做外键          外键作用: 维护数据的完整性 一致性          定义外键约束          命令： foreign key(ordersid) references orders(id)   外键的使用情景：             在不要求吞吐速度而对数据的正确性和安全性要求较高时，推荐使用外键。             如果面对高吞吐量，要求优先保证读取效率时，则不推荐使用外键。       案例: 新建部门表dept(id,name) 通过外键约束建立与员工表emp关系 步骤1：创建表格 create table dept( id int primary key auto_increment, name varchar(20) ); 步骤2：插入数据 insert into dept values(null, '财务部'); insert into dept values(null, '人事部'); insert into dept values(null, '科技部'); insert into dept values(null, '销售部'); 步骤3：创建从表 create table emp( id int primary key auto_increment, name varchar(20), dept_id int, foreign key(dept_id) references dept(id) ); 步骤4：插入数据   insert into emp values(null, '张三', 1); insert into emp values(null, '李四', 2); insert into emp values(null, '老王', 3); insert into emp values(null, '赵四', 4); insert into emp values(null, '刘能', 4);     多表查询 案例： create table dept( id int primary key auto_increment, name varchar(20) );   insert into dept values(null, '财务部'); insert into dept values(null, '人事部'); insert into dept values(null, '科技部'); insert into dept values(null, '销售部');   create table emp( id int primary key auto_increment, name varchar(20), dept_id int );   insert into emp values(null, '张三', 1); insert into emp values(null, '李四', 2); insert into emp values(null, '老王', 3); insert into emp values(null, '刘能', 5);     需求：查询出部门信息和部门所对应的员工信息。          select * from dept, emp; 笛卡尔积查询：            笛卡尔积查询概念：                  两张表相乘得出来的结果。如果左边表有m条记录，右边有n条记录，则查询出来的结果就是m*n条。            这些查询结果中包含大量错误的结果，通常不会使用这种查询。                           select * from dept, emp;           select * from dept, emp where emp.dept_id=dept.id;           select * from dept d, emp e where e.dept_id=d.id; 内连接查询：            内连接查询概念：左边表有且右边表也有的记录。                       select * from dept d inner join emp e on e.dept_id=d.id;   左、右外连接查询及全连接查询           左外连接查询：在内连接查询的基础上，加上左边表有而右边表没有的记录 **查询出部门所对应的员工信息, 同时列出那些没有员工的部门 select * from dept d left join emp e on e.dept_id=d.id;           右外连接查询：在内连接查询的基础上，加上右边表有而左边表没有的记录。 **查询出部门所对应的员工信息, 同时列出那些没有部门的员工     全外连接查询：在内连接查询的基础上，加上左边表有而右边表没有的记录 和 右边表有而左边表没有的记录。 **查询出部门所对应的员工信息,同时列出那些没有员工的部门及些没有部门的员工       select * from dept d full join emp e on e.dept_id=d.id; ##mysql不支持全外连接查询 **通过union关键字来模拟全外连接查询         select * from dept left join emp on emp.dept_id = dept.id union        select * from dept right join emp on emp.dept_id = dept.id;                 五 数据库的备份及其恢复 5.1.    备份数据库表中的数据 cmd> mysqldump -u 用户名 -p 数据库名 > 文件名.sql            mysqldump -uroot -p db_name > d:/1.sql 5.2.    恢复数据库 （注意：如果数据库已经删除，先创建数据库再恢复数据。） 方式一: 在cmd中: mysql –u 用户名 -p 数据库名 < 文件名.sql   mysql -uroot -p db_name < d:/1.sql mysql -uroot -p mydb3 < d:/1.sql 方式二: 在mysql客户端中 source 文件名.sql    source d:/1.sql 3.    练习 备份test库中的数据，并恢复        遇见问题：  问题1：编码问题                            解决方案：             mysql有六处使用了字符集，分别为：client 、connection、database、results、server 、system。 •  client是客户端使用的字符集。 •    connection是连接数据库的字符集设置类型，如果程序没有指明连接数据库使用的字符集类型就按照服务 器端默认的字符集设置。        •    database是数据库服务器中某个库使用的字符集设定，如果建库时没有指明，将使用服务器安装时指 定的字符集设置。     •    results是数据库给客户端返回时使用的字符集设定，如果没有指明，使用服务器默认的字符集。 •    server是服务器安装时指定的默认字符集设定。        •    system是数据库系统使用的字符集设定。（utf-8不可修改）---临时修改方案 show variables like'character%'; set names gbk;临时修改当前CMD窗口和mysql的通信编码字符集 •    通过修改my.ini 修改字符集编码 请到mysql安装目录下面找到 my.ini文件 修改default-character-set=utf8 为 default-character-set=gbk 有两个地方都要改 修改文件前，先停止mysql服务 ，等修改后再重新启动 使用dos命令  ：net stop mysql  来停止服务   net  start mysql  来启动       4.4 数据库的数据类型       MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。 数值类型：          日期和时间类型：        字符串类型：       注意点： 1、一个汉字占多少长度与编码有关：        UTF－8：一个汉字＝3个字节        GBK：一个汉字＝2个字节   2、varchar(n) 表示 n 个字符，无论汉字和英文，Mysql 都能存入 n 个字符，仅是实际字节长度有所区别   3、MySQL 检查长度，可用 SQL 语言来查看：        命令：select LENGTH(fieldname) from tablename           总结： mysql查询关键字执行顺序： 查询中用到的关键词主要包含六个，并且他们的顺序依次为 ： select--from--where--group by--having--order by 其中select和from是必须的，其他关键词是可选的。 这六个关键词的执行顺序，与sql语句的书写顺序并不是一样的，而是按照下面的顺序来执行 from--where--group by--having--select--order by。