mysql中councat_认识MYSQL

一、认识MYSQL：

关系型数据库管理系统(RDBMS)来存储和管理大量的数据。

1、数据库？

数据库(Database)是按照 数据结构 来组织、存储和管理数据的仓库。

2、关系型数据库？

关系型数据库 是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。

3、关系型数据库的特点：

1、数据以表格的形式出现

2、每行为各种记录名称

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

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

5、若干的表单组成database

4、RDBMS术语：

数据库：数据库是一些关联表的集合

数据表：表是数据的矩阵(二维图标)。在一个数据库中的表看起来像一个简单的电子表格。

列：一列(数据元素)包含了相同的数据，例如邮政编码数据。

行：一行(也叫元祖或记录)是一组相关的数据，例如一个人的信息。

冗余：存储重复数据。

主键：主键是唯一的。一个数据表中只能包含一个主键。但是主键可能是一列，也可能的多列组合。

外键：外键用于关联两个表。

5、SQL语言的分类：

l 数据定义语言(DDL)

l 数据操作语言(DML)

l 数据控制语言(DCL)

l 事物控制语言(TCL)

Data Definition Language(DDL) 数据定义语言

DDL使我们有能力创建或删除表格。也可以定义索引(键)，规定表之间的链接，以及施加表间的约束。

CREATE DATABASE - 创建新数据库

ALTER DATABASE - 修改数据库

CREATE TABLE - 创建新表

ALTER TABLE - 变更(改变)数据库表

DROP TABLE - 删除表

CREATE INDEX - 创建索引(搜索键)

DROP INDEX - 删除索引

Data Manipulation Language(DML) 数据操作语言

供用户实现对数据的追加、删除、更新、查询等操作。

SELECT - 从数据库表中获取数据

UPDATE - 更新数据库表中的数据

DELETE - 从数据库表中删除数据

INSERT INTO - 向数据库表中插入数据

Data Control Language(DCL) 数据控制语言

GRANT - 授权

REVOKE - 撤销授权

DENY - 拒绝授权

Transaction Control Language(TCL) 事物控制语言

SAVEPOINT - 设置保存点

ROLLBACK - 回滚

COMMIT - 提交

6、时下流行的数据库：

Oracle

应用广泛、功能强大，分布式数据库系统

“关系-对象”型数据库

DB2

应用于大型应用系统，具有较好的可伸缩性

SQL Server

针对不同用户群体的五个特殊的版本

易用性好

MySQL

快捷、可靠

开源、免费、与PHP组成经典的LAMP组合

7、MYSQL常用的存储引擎：

MyISAM InnoDB Memory Archive MERGE

8、常用的数据库操作命令：

1、显示所有数据库

show databases

2、选择(使用)数据库 stu

use stu

3、显示所有数据表

show tables4

4、创建数据库 stu

create database stu

5、删除数据库 stu

drop database stu

6、显示stu表的结构

describe stu 或 desc stu

二、数据库和表的基本操作：

1、SQL语句注释：

#

-- 注意：--后面还有一个空格

/* */

2、建表三大范式：

1、表中的每一项都是不可再分的基本数据项

(列不可再分或一个列不能有多个值)

2、表中的每一行记录必须能被唯一的区分(主键)

3、表中不能包含其他表的非主键信息

例如将学员姓名和所属班级名称放在同一张表中是不科学的，因为学员依赖于班级，可将学员信息和班级信息单独存放

3、数据库6种约束：

定义约束可以防止将错误的数据插入表中

1.主键约束 数据唯一，并且不允许为空

2.唯一约束 唯一性，允许为空，但只能出现一个空值 unique

3.非空约束 字段的值不能为空

4.默认约束 该数据的默认值 default

5.外键约束 需要建立两表间的关系并引用主表的列

6.自增约束

4、mysql 的存储引擎介绍：

用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，获得额外的速度或者功能，从而改善你的应用的整体功能。

不同的技术以及配套的相关功能在 MySQL中被称作存储引擎(也称作表类型)

5、数据库存储引擎：

Archive存储引擎 适用于存放大量数据

InnoDB存储引擎 事务型数据库的首选引擎

MyISAM存储引擎 有较高的插入、查询速度，但不支持事务。

MEMORY存储引擎 数据存储在内存中，为查询和引用其它表数据提供快速访问方式。

show engines查看存储引擎

6、数据库命令操作：

创建数据库

CREATE DATABASE db_name(数据库名);

删除数据库

DROP DATABASE db_name(数据库名);

7、创建数据表：

create table class

(

id tinyint not null auto_increment comment '班级id',

name varchar(10) not null foreign key comment '班级名称',

primary key(id)

)

not null非空 auto_increment自增 primary key主键约束 foreign key外键约束 comment为注释

更改表结构操作 语法： alter table stu ……

添加字段 alter table stu add email varchar(30) not null

删除字段 alter table stu drop email

修改表名 alter table stu rename 【as】 stu2或 rename table stu to stu2

修改字段名 alter table stu change `qq` `qq2` varchar(11)

修改字段数据类型 alter table stu modify name char(8)

添加主键 alter table stu add primary key(id)

删除主键：

alter table stu change `id` `id` smallint 先清除主键列的自增

alter table stu drop primary key 再删除主键

添加主键：

alter table stu add primary key(id)

添加自增：

alter table `stu` change `id` `id` smallint( 6 ) not null auto_increment

三、数据类型和运算符

1、整型

数值型数据类型主要用来存储数字，只有它们存储的值的大小是不相同

tinyint (-128---127)

smallint (-32768---32767)

int (-231---231-1)

bigint用于大整数值

mediumint用于极大整数值

2、浮点型

注意：设一个字段定义为float(5,3)，如果插入一个数123.45678,实际数据库里存的是123.457，但总个数还以实际为准，即6位。

3、定点数类型

Decimal( p , s)

P表示精度，s 表示小数位数；

浮点型和定点数有什么不同(区别)？

浮点型在数据库中存放的是近似值，而定点类型在数据库中存放的是精确值。

4、日期与时间类型

MySQL中表示日期的数据类型。DATETIME、DATE、TIME

5、字符串类型

Char( ) 固定长度，范围是0~255，长度不足就用空格补足 性别 用 char

Varchar( )可变长度字符串，范围是0~65535，存储字符串实际长度，不浪费空间 姓名 用 varchar

经常变化的字段用varchar

知道固定长度的用char

Text 存储大文本数据,不能指定默认值

blob 存储二进制字符串

长度都可变化

6、运算符概述

算术运算符

+、 -、 *、 /、 %

比较运算符(结果总是1,0或者是NULL)

>、 =、 <=、!=

逻辑运算符(结果为TRUE、FALSE或NULL)

not或！、and或&&、or或||、异或xor

四、MySQL函数

1、数学函数

(1)ceil(x) 返回大于x的最小整数值，(进1取整)

Select ceil(1.2) 2

SELECT CEIL(-3.5) -3

(2)floor()返回小于x的最大整数(去掉小数取整)

Select floor(1.2) 1

(3) rand() 返回0到1内的随机值。

Select rand() ；

SELECT RAND(),RAND(),RAND()

没有参数的时候，每次执行，值都会变化。每个函数返回随机数不同

SELECT RAND(2),RAND(3),RAND(2)

Select rand(n)加上任意一个参数，则生成值后不再变化，参数相同，返回值相同。

(4)round(x,y)

返回参数x的四舍五入，有y位小数的值 Select round(4.567,2) 4.57

(5)truncate(x,y)

返回数字x截短为y位小数的结果

select truncate(1.234567,5) 1.23456 不四舍五入的

2、字符串函数

(1)concat() 字符串连接函数

select concat('编号',id) as 编号 from stu

select concat(id,user) from user

把两列连接起来显示

(2)left(str,x) 返回字符串str中最左边的x个字符

Select left(‘abcdef’,3) abc

left(str,x) str也可以为字段名

right(str,x) 返回字符串str中最右边的x个字符

Select right(phone,6) from stu

获取身份证字段的后6位，phone为字段名

(3)Trim()：去掉制定字符串

SELECT TRIM('19' FROM birthday) FROM stu

(4)Replace(str,str1,str2) 把str字符串中的str1替换为str2

Select(‘abcde’,’cd’,’11’) ab11e

(5)substring(str,x,y) 把str字符串从x位置截取y个

select substring('你好啊朋友',2,2) 好啊

SELECT SUBSTRING(phone,4,4) FROM stu

(6)mid() 获取字符串str从第x位置开始，y个字符长的字符串

Select mid(‘1234567’,2,3) 234

(7)reverse() 返回颠倒字符串的效果

Select reverse(‘abc’) cba

SELECT REVERSE(NAME) FROM stu

3、日期时间函数：

(1)Now()：获取当前日期时间

select now() 2018-03-06 23:02:29

SELECT SYSDATE();

(2)Month(): 获取月份

Select month(now()) 3

SELECT MONTH(birthday) FROM stu 生日的月份

Select month(‘2018-02-05’) 2

(3)Dayname():获取星期几

Select dayname(now())

输出今天是星期几

(4)Week(d):计算日期d是一年中的第几周

Select week(now()) 输出当前是一年中的第几周

Select week(‘2018-01-02’) 0

SELECT WEEK('2018-01-02',1)

(5)Dayofyear():获取某日期是一年中的第几天

Select dayofyear(‘2018-01-01’) 1

Select dayofyear(‘2018-01-02’) 2

可以继续尝试：dayofmonth()

五、查询数据(一)

1、查询所有数据

1．在SELECT语句中使用星号“*”通配符查询所有字段

2．在SELECT语句中指定所有字段

SELECT * FROM test;

-- test为表名

2、查询指定字段

1．查询单个字段

SELECT admin FROM test;

-- admin为要查询的字段名

2．查询多个字段

SELECT name，sex FROM test;

-- name，sex为要查询的字段名

最后一个字段不要加“，”

3、查询指定记录

Select * from stu where name=’张三’

Select * from stu where id=1 or id>3

Select * from stu where sex='男' and name='张'

加入where条件，根据要求进行筛选过滤

4、带IN关键字的查询

查询满足指定范围内的条件的记录

使用IN操作符，将所有检索条件用括号括起来，检索条件用逗号分隔开，只要满足条件范围内的一个值即为匹配项。

查询id为1或为4或为5的学生信息

Select * from stu where id in(1,4,5)

查询id不为1和4和5的学生信息

Select * from stu where id not in(1,4,5)

5、带BETWEEN AND的范围查询

查询某个范围内的值

该操作符需要两个参数，即范围开始和结束，如果字段值满足范围查询条件，则记录被返回。

查询学生id为1到3的学生信息(包含1和3)

Select * from stu where id between 1 and 3

查询学生id不在1到3的学生信息(包含1和3)

Select * from stu where id not between 1 and 3

6、带LIKE的字符匹配查询

百分号通配符‘%’，匹配任意长度的字符，甚至包括零字符

查询姓名中有小字的学生信息

select * from stu where name like '%小%'

查询姓名以小字结尾的数据

select * from stu where name like '%小'

查询姓名以小字开头的数据

select * from stu where name like '小%'

查询姓张的学生信息 (已姓开头)

select * from stu where name like '张%'

查询姓张或姓李的学生信息

select * from stu where name like '张%' or name like ‘李%’

查询姓赵，并且姓名是两个字的学生信息

select * from stu where name like '赵_'

7、下划线通配符‘_’，一次只能匹配任意一个字符

如果要匹配多个字符，则使用相同个数的‘ _ ’

查询姓王，并且姓名是两个字的学生信息

select * from stu where name like '王_'

查询1998年的学生信息(存储格式1998-02-02)

SELECT * FROM stu WHERE birthday LIKE '1998______'

8、带AND的多条件查询

使用AND连接两个甚至多个查询条件，多个条件表达式之间用AND分开。

查询id小于5的男生信息

Select * from stu where id<5 and sex=’男

查询id大于5成绩60以下所有男生名字

Select * from stu where id>5 and score<60 and sex=’男’

9、带OR的多条件查询

OR操作符，表示只需要满足其中一个条件的记录即可返回。OR也可以连接两个甚至多个查询条件，多个条件表达式之间用OR分开。

查询id小于5的和男生的信息

Select * from stu where id<5 or sex=’男’

查询数据--限定结果(二)

1、查询结果不重复

在SELECT语句中可以使用DISTINCE关键字指示MySQL消除重复的记录值。distinct只返回它的目标字段，无法返回其它字段。后面跟要去重的字段。

SELECT DISTINCT 字段名 FROM 表名;

SELECT DISTINCT id,NAME FROM stu

发现表中所有数据都查询出来了。原因在于只要id,name两列不重复的数据就可以查询出

2、对查询结果排序

MySQL中可以通过在SELECT使用order by对查询的结果进行排序。

1．单列排序(默认升序排列)

SELECT NAME FROM stu ORDER BY NAME

2．多列排序(第一列有重复第二列才会排序)

SELECT NAME,sex FROM stu ORDER BY NAME,sex

3．指定排序方向

(desc降序 asc升序,只能用于最靠近他们前面的字段)

SELECT NAME,sex FROM stu ORDER BY NAME DESC,sex ASC

3、分组查询

分组查询是对数据按照某个或多个字段进行分组。

1．创建分组

SELECT NAME FROM stu GROUP BY NAME

所有男生分组显示个数和名字

SELECT sex,COUNT(*) AS '个数'，GROUP_CONCAT(NAME) AS '名字' FROM stu WHERE sex='男' GROUP BY sex

GROUP_CONCAT(NAME)函数显示name列所有的记录

COUNT(*)统计个数

4、多字段分组

SELECT *,COUNT(*),GROUP_CONCAT(NAME) AS 'xingm',GROUP_CONCAT(sex) AS 'xingb' FROM stu GROUP BY NAME,sex

对多个字段分组，当第一个字段相同时，按照第二字段分组。

5、用LIMIT限制查询结果的数量

LIMIT关键字可以返回指定位置的记录。

LIMIT [位置偏移量,] 行数

位置偏移量从0开始计算，

limit 2,4表示从第三条开始，显示4条

SELECT NAME,id FROM stu LIMIT 2,4

SELECT NAME,count(name),group_councat(name),jianjie,c_name,sname FROM stu JOIN class

ON stu.cid=class.id

JOIN school

ON class.sid=school.id LIMIT 1,2

7、使用集合函数查询

COUNT()函数 统计包含的记录行总数

SELECT COUNT(name) FROM stu

SUM()函数 求总和的函数，返回指定列值的总和

AVG()函数 求指定列数据的平均值

MAX()函数 指定列中的最大值

MIN()函数 返回查询列中的最小值

聚合函数对一组值执行计算，并返回单个值。

查询数据--连接查询(三)

1、内连接查询

SELECT NAME,jianjie,c_name,sname FROM stu JOIN class

ON stu.cid=class.id

JOIN school

ON class.sid=school.id LIMIT 1,2

2、外连接查询

LEFT JOIN(左连接)

显示左表中的全部和右表中符合条件的信息。

SELECT s.id,s.NAME,c.id,c.c_name FROM stu as s LEFT JOIN class c

ON s.cid=c.id

RIGHT JOIN(右连接) 。

显示右表中的全部和左表中符合条件的信息。

学生报道了，学生信息录入到了学生表中，有的学生报道早，有的晚。早的学生已经分配好班级了，晚的还没有分配班级。我想查询所有学生的分班情况？如何查询？

已经分班的显示出班级名称，没有分班的班级显示为空

SELECT s.NAME,c.c_name FROM stu

LEFT JOIN class c

ON s.cid=c.id

3、内连接和外连接的区别：

我把两个表比作集合A、B，其中，内连接是集合A和集合B的交集，而交集的内容在两个表中都存在，即在每一个表的内部；而外连接则是除了交集外，还有另一个表中的内容(左、右、全)，所涉及的结果集是两个表中的内容。

4、子查询

1、带IN关键字的子查询

SELECT * FROM stu s WHERE cid IN(SELECT id FROM class WHERE c_name=1701)

SELECT * FROM stu JOIN class ON stu.cid=class.id WHERE c_name=1701;

IN关键字进行子查询时，内层查询语句仅仅返回一个数据列，这个数据列里的值将提供给外层查询语句进行操作。

2、带比较运算符的子查询

可以使用运算符 > < = >= <= !=

SELECT * FROM stu s

WHERE cid !=(SELECT id FROM class WHERE c_name=1701);

3、为表取别名

表名 [AS] 表别名

列名 [AS] 列别名

AS 可以省略。

SELECT s.id,s.NAME,c.id,c.c_name FROM stu s LEFT JOIN class c

ON s.cid=c.id