MySQL介绍，SQL入门及表结构分析 |
您所在的位置：网站首页 › 简单结构是什么结构 › MySQL介绍，SQL入门及表结构分析 |
MySQL介绍，SQL入门及表结构分析
|
数据库分类：
关系型数据库（SQL）：通过表与表之间，行与列之间的关系去存储数据。如MySQL、Oracle（两者本质都是DBMS，数据库管理系统） 非关系型数据库（No SQL意为Not only SQL）：通过对象自身属性去存储。如Redis。 MySQL是什么：先简单解释一下数据库是什么，数据库就是数据的仓库，也是一种文件系统，需要使用SQL来进行操作。 MySQL则是关系型数据库管理系统（库中的数据存在实体与实体的关系）。 常见的关系型数据库（Java开发中，MySQL和Oracle最为常见）： Oracle：大型的数据库，收费的。 MySQL：小型的数据库，免费开源的。被Oracle收购了（6.x版本后开始收费）。 SQLServer：微软的数据库。 DB2：IBM公司产品，大型的数据库，收费的。 数据库的三级模式和二级映像：三级模式对应数据库的三层：外部层，概念层，内部层。每层各对应一个模式，分别是外模式，概念模式，内模式。 三级模式是数据的三个抽象级别，每个级别关心的重点不同。 外模式面向用户，展示数据（如视图，可以向不同的人展示不同的数据）； 概念模式面向设计人员，描述数据，如表的字段设置，类似于一个表的列标题； 内模式面向数据库本身，存储数据。 二级映像是一种规则，规定了三个模式的联系和转换。 外模式/概念模式映像，保证数据逻辑独立性，概念模式变了，修改外模式/概念模式映像即可，不会影响应用程序。 概念模式/内模式映像，保证数据物理独立性，内模式变了，修改概念模式/内模式映像就好，概念模式、应用程序不受影响。 MySQL结构图：
mysql install（安装服务） mysqld --initialize（初始化操作，生成data目录） net start mysql（启动服务） mysql -u root -p（登录） show databases;（查看所有数据库） use 数据库名称;（切换数据库） show create database 数据库名称;（查询数据库的创建信息） select database(); （查询当前正在使用的数据库 ） drop database 数据库名称;（删除数据库） alter database 数据库名称 character set ‘gbk’ collate ‘校对规则’;（修改数据库） show tables（查看该库所有数据表） show create table 表名称;（查询表的创建信息） desc 表名(查看表结构) MySQL常用数据类型：字符串型：varchar（长度可变0-65535）、char（长度不可变0-255，剩余字符用空格补齐）、tinytext、text 数值型：tinyint（1字节） 、smallint（2字节）、mediumint（3字节）、int（4字节）、bigint（8字节）、float（4字节）、double（8字节） 日期型：date（包含年月日）、time（包含时分秒）、datetime（包含年月日时分秒，插入数据时如果为空，字段值则为空）、timestamp（时间戳，插入数据时如果为空，获取当前系统日期到1970.1.1的毫秒数，存入字段值） 注意：空字符串不是null。 InnoDB和MyISAM引擎的区别： InnoDBMyISAM事务支持支持不支持外键约束支持不支持全文索引不支持支持锁支持行锁和表锁只支持表锁表空间大小较大，约为MyISAM两倍较小物理文件.frm文件、.idb文件.frm文件、.myd文件、.myi文件delete后自增重启后，自增量从1开始（存在内存中）重启后，从上个自增量开始（存在文件中）总的来说：InnoDB支持事务，安全性高，多表多用户操作；MyISAM节约空间，速度较快，但不支持事务。 SQL概述及分类：SQL（Structured Query Language）意为结构化查询语言，是非过程性的语言（一条语句，就对应一个返回结果），用于操作数据库的通用语言。 DDL（Data Definition Language）数据定义语言：creat（创建）、alter（修改）、drop（删除），用于数据库、表、视图、索引上。 DML（Data Manipulation Language）数据操作语言：insert（插入）、update（修改）、delete（删除），用于增，删，改表内数据 DQL（Data QueryLanguage）数据查询语言：select（查询），用于查询表内数据。 DCL（Data Control Language）数据控制语言：crant、revoke，主要应用在数据访问权的控制。（用的相当少了） SQL基本语法：有关数据库的创建、修改、删除： 创建：create database 数据库名称; 修改：alter database 数据库名称; 删除：drop databse [if exitsts] 数据库名称;（防止数据库不存在，进行删除而报错添加：if exitsts）有关表的创建、修改、删除： 1、表名称后跟的括号，要以分号结束。 2、字段要以逗号结束，最后一个字段不能加逗号。 3、约束、注释可以不写。 4、类型如果为字符数据，必须指定长度。 5、primary key(字段名)：设置主键 创建： create table [if not exists]表名称( `字段名1` 类型(长度) [约束] [注释], `字段名2` 类型(长度) [约束] [注释], `字段名3` 类型(长度) [约束] [注释] )engine=InnoDB default charset=utf8; 修改： alter table 表名 rename 新表名称;（修改表的名称） alter table 表名 add 新列名称 类型(长度) 约束;（添加列） alter table 表名 drop 列名称;（删除列） alter table 表名 modify 列名称 类型(长度) 约束;（修改列的类型或约束，不能修改名称） alter table 表名 change 旧名称 新列名 类型(长度) 约束;（修改列的名称,不能修改类型和约束） 删除 drop table[if exitsts] 表名称; （防止表不存在，进行删除而报错添加：if exitsts）有关数据的插入、修改、删除和查询（重点）： 插入： insert into 表名称 values(值1,值2,值3);（为所有字段插入值） insert into 表名称 (字段1,字段2) values(值1, 值2);（为指定字段插入对应值） 修改： update 表名称 set 字段1=值1,字段2=值2,... where 条件;（修改条件为...的指定字段，没有where条件则修改字段全部数据） 删除： delete from 表名称 where 条件;（删除指定条件的数据，没有where则删除表中所有数据） truncate 表名称;（也可以删除全部数据，但方式是先把表删除，然后创建一种表结构相同的空表，不支持事务操作） 查询（重点）： select * from 表名称;（最基本select语句，查询所有列全部数据） select 字段1,字段2 from 表名称;（查询指定字段的数据） select distinct 字段 from 表名称;（查询并去重指定字段数据，必须加字段，用*表示不起作用） select * from 表名称 where 条件; 其中条件可以是运算符：> < = != >= = 16 and age < 19; select * from 表名称 order by 列名 升序asc/降序desc;（对查询的结果进行排序，默认为升序，总是放在select语句最后） select * from 表名称 limit 起始下标[(n-1)*pageSize],页面大小(pageSize);（n:当前页数，分页查询） 注意：总页数=数据总数/页面大小 select count(字段) from 表名称;（聚集函数-计数） select sum(字段) from 表名称;（聚集函数-求和） select max(字段) from 表名称;（聚集函数-求最大值） select min(字段) from 表名称;（聚集函数-求最小值） 一些字符串操作的函数： concat(A,B)：连接字符串A和B substring(A,1,3)：截取A字符串中的第1位到第3位字符串 一些日期操作的函数： year(date)：获得指定日期的年份 month(date)：获得指定日期的月份 select 字段, 聚集函数 from 表名称 group by 字段 having 过滤条件;（根据字段进行分组，分组一般和聚集函数一起使用，求每组某个字段的总和，having和where均可实现过滤，但在having可以使用聚集函数,where不能使用聚集函数，也不能作用在group后） 例子（为了好理解，在各个字段后加上别名）: select product 商品名, sum(price) 价格总和 from orders where price > 800 GROUP BY product having 价格总和 > 200 ORDER BY 价格总和 desc; 这句SQL的含义：从orders表中，查询商品名和价格总和（聚集函数sum计算出来的），且prive必须大于800，按商品名分组后，过滤出价格总价大于200的数据，并按价格总和降序排序 select语句顺序：select…distinct… from… join… where… group by… having… order by… limit 去重＞连接＞筛选＞分组＞过滤＞排序＞分页 执行效果上，count(1)和count(*)相同。 但count(1)忽略所有列，用1代表代码行，在统计结果时，不会忽略列值为NULL。 而count(*)包括所有列，相当于行数，在统计结果时，不会忽略列值为NULL。 count(字段)只包括列名那一列，在统计结果时，会忽略列值为NULL的计数不统计 执行效率上，count(主键)效率最高；若不选主键，count(1)比count(字段)效率高；若表中只有一个字段，count(*)效率最高。 事务管理：事务管理原则：ACID原则。 原子性A（Atomicity）：事务内操作不可分离，要么都成功，要么都失败。 一致性C（Consistency）：事务前后数据要保持一致，不可冲突。 隔离性I（Isolation）：多个用户并发访问数据库时，数据库为每一用户开启事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。 持久性D（Durability）：事务一旦提交便不可逆，被持久到数据库中。（服务器发生宕机断电，如果已经提交，不会回滚） 事务的隔离性会产生一些问题，为了解决这些问题，有了事务的隔离性。 脏读：一个事务读取并操作了另一事务未提交的数据。（两事务操作同一数据，一事务还未提交，另一事务操作了未提交数据） 不可重复读（不一定是错误）：在一个事务内，多次读取同一行数据结果不同。（查询时，有人修改了数据） 虚读（幻读）：一个事务内读取到了别的事务新增的数据，导致前后读取数量不一致。（查询时，有人新增了数据） mysql常规操作事务语句如下： 一对一表结构 一对多表结构 值得注意的是，一对多和多对一结构是相对的，一对多重点在对多，多对一重点在对一。
primary key（声明字段为主键，唯一、非空、被引用） auto_increment（声明字段自增长） unique （声明字段唯一） not null（声明字段不为空） 创建一对多表结构时，多表的约束：多表约束其实就是外键约束，为了数据完整性而存在。 上面所说的部门表和员工表，在一对多表结构中，如果不加外键，我们可以删除两个部门的数据，此时部门数据已经不存在了，但是员工表中还是存在部门的外键，显然这是不合理的。 即便这样，也不建议直接在数据库层面直接使用外键，应放到业务层面处理。 creat table order( oid int primary key auto_increment, oname varchar(50), oprice doubble, uid int, primary key(id) 建议这样设置主键，方便查看 foreign key order(uid) references user(uid) 这行表示声明uid为外键并执行user用户主键 ); 笛卡尔积当联合查询两张表时，两张表有关联，并通过外键关联，会出现笛卡尔积，如下： 我们理想的情况，应该是查出6条数据才对，但却查出了24条（相当于两张表相乘了），想要解决笛卡尔积，可以采用联表查询 联表查询1、分析查询的数据分别来自哪些表字段 2、确定联查方式（内连接查询、左连接查询、右连接查询） 3、找出交叉点，判断条件（表中共有字段） 普通内连接（参照上图两表）： select * from dept inner(可省略) join emp on dept.did = emp.dno; 隐式内连接： select * from dept d,emp e where d.did = e.dno; 左外连接： select * from dept left join emp on dept.did = emp.dno; 右外连接： select * from dept right join emp on dept.did = emp.dno; 注意：哪个表先写在from后，默认先展示哪个表的字段。join关键词左边的是左表，右边的是右表 对于普通内连接：join可以和where连用,也可以和on连用 对于隐式内连接：join只能和where连用 对于左,右连接：join只能和on连用 上面四种连接，在没有多余数据的情况下（都有对应的主外键关联），查询出的是数据完全相同。 当存在多余数据时： 内连接默认不查询，只查询出有两表中有关联的数据; 左连接先把左表所有的数据全部都查询出来，再把两个表中有关联的数据查询出，再将无关联的查询出来; 右连接先把右表所有的数据全部都查询出来，再把两个表中有关联的数据查询出，再将无关联的查询出来;
在增删改查语句中，其筛选条件是另一条select语句，就是子查询，子查询最常用于select语句的where子句中。（可嵌套） 在这里用具体问题去演示子查询，参考三张表： |
用文字描述：一个数据库管理系统（如MySQL）中包含多个数据库，一个数据库中有多张表，一个表中包含多个字段（字段和JavaBean的属性对应），表中存放多行数据，一行数据和一个JavaBean实体对象对应。
多对多表结构 
观察三张表数据，试着去解决这样一个问题：查询科目高等数学-1的同学信息（学号，姓名，分数）【本文地址】
今日新闻 |
推荐新闻 |