我的ASP.NET学习笔记08MySQL基础知识

mysql数据库基础知识

什么是数据库

数据库顾名思义，就是用来存储数据的工具，用一个比例形象的例子来比喻，就是Excel，一个Excel文件就可以看成是一个数据库。

关系型数据库

就是以行与列构成的二维数据表的形式，用来存储数据的，并且将多张数据表存储在一个单元中的存储形式，就称为关系型数据库。

数据表

由行与列构成的二维结构的存储数据的表格。

记录

就是数据表中行，一横行数据我们就称为一条数据记录。

通常而言，每条数据记录都有一个ID号，我们可以把这个id理解成是excel中的行号，用来对每条记录进行区分与标记。

主键

很多时候我们将id称为主键，主键这是指这张表的排列顺序的依据。

字段

数据字段，就是数据表中的列。

每一个字段都需要指定一个名称，用来说明该列数据的作用，就叫作字段名。

常用的数据库的类型

Mysql 性能强悍，可以免费使用。

MSSQL server 微软公司的数据库软件，通常用于搭配微软体系的编程语言。

Access是office的办公套件之一。

Oracel 性能非强悍，也非常昂贵。

安装与使用mysql

安装mysql的方式很多，我们使用phpstudy这个软件来安装php + mysql的运行环境。安装之后，我们就拥有了mysql的服务。而且还有两款mysql客户端。

 

Mysql分为客户端与服务端。服务端仅仅用于对软件程序提供数据通信，普通用户无法直接看到其中的数据内容。

 

客户端用来将服务端的数据，以用户可以理解的方式展示在其界面上。

 

phpMyAdmin

phpMyAdmin是最常用的mysql客户端，它是基于php语言的。

在桌面右下角的phpstudy图标上右击弹出菜单中选择phpMyAdmin就可以启动进入了。启动之后，需要输入用户与密码进行登陆，默认的用户名密码都是root。

 

新建数据库

1、 要指定数据库的名称。

2、 通常选择utf8_unicode_ci字符集，在只有中文的情况下，可以选择gbk_chinese_ic或gb2312_chinese_ci 。

 

新建数据表

1、 选中数据库之后，点击右侧的“新建数据表”，填写“数据表名”。

2、 指定数据表的字段，也就是列。在指定字段的过程中我们要对字段添加字段名、数据类型、数据长度、是否是主键、是否自增。（多数情况下，主键是名为id的整数类型，而且是自增的。）

3、 为字段指定这一列的数据类型。

自增auto_increment

自增字段中通常存放的是整数类型的数据，用于表示数据库中的记录的序号。

自增字段的值不需要手动输入，其中的编号是自动产生的，每当向这表里面添加一条新记录的时候，自增字段会自动取出上一行字段值，然后加一，作为新记录的主键值。（主键绝对不会发生重复，即使上一条记录被删除了）。

如何设置一个字段为自增

1、在创建表或在修改字段结构的时候，设置字段的数据类型为int，然后选中auto_increment上的勾。

2、当插入一条数据时，不要手动填写主键字段。

3、主键生成的数字绝对不会重复，即使记录被删除。

如何设置主键

1、在创建表或在修改字段结构的时候，在主键字段的”索引”下拉菜单中选择“primary”。（一张表只允许设置一个主键，通常它是int自增的。）

 

小练习：

1、 新建一个产品product数据库

2、 在数据库中添加产品product表，并建立字段pId （主键）、pName（产品名称）、pModel（产品型号）、pPrice（产品价格）

3、 添加5条产品信息

4、 在数据库中再添加一个产品入库表（saveInLib）,并建立字段：sid（主键）、pName（产品名称）、saveInTime（入库时间）、saveInCount（入库数量）。

5、 添加5条产品入库记录。

 

mySQL中的数据类型

在mySQL中每个字段，都必须明确它存放的数据的类型，一旦指定了类型，该字段（列）中的数据都必须符合这个类型的范围，否则就会引起错误。

 

并且字符等类型需要指定内容的最大长度。

 

int整数类型

只能存放整数

varchar字符类型

可以存储任一字符，包括符号、数字、字母，但是他们都会被当成字符为处理。

Date日期

用于存储日期和时间

数值类型

TinyInt 非常小的整数存储格式，1字节（8位的二进制数），它的取值范围是：不带符号时0 ~ 255，带正负号时-128 ~ 127

SmallInt 小整数，2字节，不带符号时0~65535，带正负号时-32768~32767

MediumInt 中等整数，3字节，不带符号时0~16777215，带正负号时-8388608 ~ 8388607

Int 标准整数，4字节，不带符号时0 ~ 4294967295 ，带正负号时-2147483648 ~ 2147483647

BigInt 大整数，8字节，不带符号时0~18446744073709551615

 

Float 单精度浮点数，4字节，最小值，正负1.175494351E-58（1.175494351*10^-58）最大值，正负3.102823466E+38(3.102823466E*10³⁸)

Double 双精度浮点数，8字节，最小值，正负2.22507385072014E-308，最大值：正负1.17976931348623157E+308。

 

Decimal 以字符串形式存储浮点数。它的取值范围不是固定的，取决于用户指定的长度。

数值类型的字段可以设置的参数：

unsigned属性

“整理”菜单中的unsigned选项，代表无符号，代表这一字段中的数值不能为负数，因为无符号就是代表没有正负号。如果“整理”菜单留空，什么都不选的话，就是代表有符号，可以为正数也可以为负数。

unsigned zerofill属性

当数据的宽度小于类型的最大宽度时，则在数据前面自动填充0

auto_increment

自增

默认à定义

这个选项代表，填充数据时如果该字段为空值时，所使用的默认值。

字符串数据类型

字符类型可以存储任何值，甚至是二进制形式的图像、声音、视频。

CHAR[M] 代表M字节的字符。

varChar 常用的字符存储格式，使用时需要指定最大内容长度。

存储大容量的文本

TinyBlob ，blob，TinyText，text

前两个是代表二进制字符串，后两个是代表非二进制字符串，都可以存储大量的文本信息。

MediumBlob，MediumText

LongBlob，LongText

枚举类型

ENUM / SET 类型

设置其默认值为(‘值1’, ‘值2’, ‘值3’,…)，由用户指定多个可选值，字段中的值必须是其中之一，最多只能有65535个可选值。

 

日期时间类型

Date 1000-01-01 ~ 9999-12-31

TIME -838:59:59~838:59:59

DateTime 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59

TimeStmp 1970-01-01 00:00:00 到2037年的之间的某一个时刻

Year 存储1901~2155年的一个年份。

 

小练习：

1、 创建一张员工数据表，employee，包含字段：eId(标准整数，主键、自增)、姓名eName(varchar,50)、年龄eAge（tinyInt，无符号）、工号eNum（smallInt，不足的位数用0填充）、学历（枚举：初中~研究生）、性别（枚举：男，女）、出生日期（DateTime）、基本工资（Float，默认:1300）、自我介绍text。然后输入五名员工的信息。

 

 

T-SQL语言基础之增删改查

SQL语句

是一门专门用于数据库操作的语言，SQL语句的语法不仅仅适用于mysql数据库，同时也适用于几乎所有的主流数据库。当然不同公司出口的数据库在语法细节上还是有些差异。

 

select语句

查询语句，专门用于在数据表中按照用户指定的条件进行查询。

简要语法描述：

SELECT <字段1,字段2，字段3…..> FROM <数据表名> [WHERE 筛选条件]

其中字段部分代表你想要从这张数据表中查询哪些字段（列），如果要查询所有字段，可以 * 号表示。

示例：

SELECT pName FROM product

//从产品表中查询所有产品名称

SELECT pName,pPrice FROM product

//从产品表中查询所有产品名称和价格

SELECT * FROM product

//从产品表中查询中所有产品的字段

SELECT pName,pPrice-30 FROM product

//在查询时给所有产品减30元

SELECT pName as '产品名',pPrice as '价格' FROM product

//在查询时修改结果集中的字段名

SELECT 3.1415926 *12 *12

//用select语句来进行数学运算

 

 

where子句

定义一个查询条件，然后在查询过程中用这个条件来筛选符合条件的记录。

 

SELECT * FROM product WHERE pPrice >=1000

查询价格大于等于1000的产品

SELECT * FROM product WHERE pPrice <>3000

SELECT * FROM product WHERE pPrice !=3000

查询价格不等于3000的产品

SELECT * FROM product WHERE pName = '中兴z954'

 

查询产品名称是中兴z954的产品信息（在mysql中双引号与单引号都表示字符串，但是推荐使用单引号，因为我们将来要学习的mssql server 中字符串用单引号表示）

逻辑与and

SELECT * FROM product
WHERE pPrice <800 AND pId >6 AND pName ='Nokia v998'

 

逻辑或or

SELECT * FROM product

WHERE pPrice <800 OR pId >6

 

逻辑非not

SELECT * FROM product

WHERE not pName = '中兴z954'

 

 

insert插入数据

向指定的数据表的指定字段插入一条记录。

语法：

INSERT INTO 表名(字段1,字段2，字段3，……) values(值1，值2，值3，….)

其中，字段的位置与值的位置必须一一对应。

示例：

INSERT INTO userinfo(uName,uPhone,uIDCard)

VALUES('江小白','010-89562321','5001234567814541X')

 

向数据表中插入一条用户信息

 

INSERT INTO userinfo(uName) VALUES('张小强')

插入一条用户信息，但是只有姓名，其他字段自动留空或使用默认值（如果其中某个字段设置为不允许为null，而没有默认值的话，就必须要给这个字段赋值了）。

 

INSERT INTO userinfo VALUES

(3,'王小虎','010-89562321','5001234567814541X')

 

可以省略字段名，但是所有的字段都必须按照顺序来依次赋值。包含主键ID（要按照顺序来填写主键值）

 

一次性插入多条语句

insert into student(sName,sAge,sSex,sPhone)

values('小黑',20,'女','010-89562314'),

('小强',20,'男','020-89562314'),

('小红',20,'女','021-89562314'),

('小黄',20,'男','019-89562314'),

('小李',20,'女','022-89562314')

 

 

Update修改数据

根据指定的条件确定需要修改的行，然后修改指定字段的数据。

语法：

update 数据表名 set 字段1=值1，字段2=值2，……..WHERE 条件

虽然where是一个可选参数，但是通常情况下都需要写where条件，如果不写就是修改整张表的所有行。

示例：

UPDATE userinfo SET uName = '江老怪' WHERE uid = 1

 

修改第1条记录的姓名为江老怪

UPDATE userinfo SET uName = '江老怪'

 

修改所有行的用户名为江老怪

 

UPDATE userinfo

SET uName = '小二黑',uPhone='111111',uIDCard='11111111' WHERE uid = 3

 

 

delete删除

根据条件删除指定的记录

语法：

delete from 表名 where 条件

虽然where是一个可选参数，但是通常情况下都需要写where条件，如果不写就是删除整张表的所有行。

示例：

DELETE FROM userinfo WHERE uid = 2

 

 

小练习：

1、 记忆背诵增删改查指定的语法。

2、 手动新建一个数据库product，在其中新建数据表product，这个表中的字段：pid（主键）、pName（产品名）、pModel（型号）、pIntro（简介）、pFrom(产地)、pPrice（价格） 。

3、 用insert语句添加10条不同的产品数据

4、 查询

a) 查询所有产品数据

b) 查询所有产品的产品名和价格

c) 查询所有价格在2000以上的产品

d) 查询所有产品为重庆并且价格大于3000的产品

e) 查询所有产地不是重庆的产品

5、 将第5条记录的产品号修改为T1000

6、 删除所有价格小于5元的产品。

 

将第三题之后的sql语句复制到一个word文档中，然后组长检查组员，班长检查组长。

3、5、6每题20分。

第4题中的每个小题8分共40分

 

命令行与高级查询

Mysql命令行

1、 在phpstudy的右键菜单中，mysql工具 à mysql命令行

2、 弹出的命令框中提示”enter password”，输入默认密码root

3、 如果看到”you mysql connection”就说明你登陆成功了。

4、 在命令行中，每一条指令都必须以分号;结束，否则系统会认为你这条指令还没有完成，始终要求你继续输入。

 

查看数据库列表

show Databases

选择数据库use

格式：

use 数据库名

选定指定数据库为当前默认被操作的数据库。

在使用操作数据表中的数据之前，必须要选定一个当前数据库。

 

罗列出当前数据库中的数据表

show tables

罗列出数据表中的字段

格式：show columns from 表名

或

Describe 数据表名

 

数据库与数据操作

创建一个新的数据库

命令格式：create database 数据库名

在当前数据库中创建数据表

注意，在创建之前必须选择当前数据库。

格式：create table 表名(字段1 数据类型,字段2 数据类型，……)

示例：

create table stdInfo(

sName varchar(20),

sAge int,

sSex varchar(5)

);

 

创建包含自增主键的数据表

示例：创建客户表：

create table customers(

 id int not null auto_increment,

 name varchar(20) not null,

 age int not null,

 address varchar(100) not null default 'empty',

 primary key(id)

);

 

说明：

not null 代表该字段不允许出现空值，就是说当你使用insert语句插入数据记录的时候，必须向这个字段赋值，否则数据操作将发生错误。

auto_increment 自增

Default 默认值

Primary key 指定数据表的主键

删除数据表

格式：drop table 数据表名

删除数据库

格式：drop database 数据库名

更改表结构：增加字段

添加一个字段

alter table student add sScore float null;

 

其中：

add 代表增加一列

Null 代表这个字段允许空值。

student 是数据表名

sScore 是新增的字段名，后面是数据类型。

更改表结构：删除字段

alter table student drop column sScore;

 

其中：

drop column 代表删除字段操作

sScore 要被删除的字段名

student 是表名

重命名数据表

rename table student to sInfo;

 

注意：to前面是修改前的原表名，to后面是修改后的新表名。

更改表结构：添加自增主键

alter table product

 add pid int

 not null

 primary key

 auto_increment

 first;

 

其中

primary key 设置新字段为主键

first 将新字段放在其他字段的前面，处于第一位。

 

小练习：

请同学把所有SQL都写到一个SQL文件中，按“班级-姓名(可以是拼音).SQL”的方式保存

某学校的学生管理数据库中有学生表(T_STUDENT)、班级表(T_CLASS)，表结构及存储的数据如下表所示：

学生表(T_STUDENT)：

STU_ID (int, 主键,学号)	STU_NAME (nvarchar(10),姓名)	STU_AGE (int,年龄)	STU_CID (int,外键,班级号)
1	张三	18	1
2	钱四	16	2
3	王玲	17	3
5	李飞	19	4
9	赵四	18	5
10	李可	20	6
11	张飞	18	7
12	周瑜	16	8
13	王亮	17	7
14	董庆	19	1
15	赵龙	18	2
16	李丽	20	3

班级表(T_CLASS)：

CLS_ID (int, 主键, 班级号)	CLS_JOB (nvarchar(50), 专业名)	CLS_DEPART (nvarchar(50), 系名)	CLS_DATE (int, 入学年份)
1	软件	计算机	2013
2	微电子	计算机	2013
3	无机化学	化学	2014
4	高分子化学	化学	2012
5	统计数学	数学	2015
6	现代语言	中文	2016
7	国际贸易	经济	2013
8	国际金融	经济	2014

 

用 SQL 语言完成以下功能

1. 建库、建表，要求添加主键
2. 插入指定的数据
3. 找出所有年龄小于19岁的学生学号、姓名、年龄。
4. 学生张三转到化学系 111 班，请更新相关的表。
5. 删除班级表的主键这一列。
6. 将学生表改名为T_STD
7. 为班级表添加字段CLS_COUNT人数字段
8. 为班级表添加主键。
9. 删除班级表

 

 

mysql_4_聚合函数

mysql数据库的备份与恢复

备份

在phpmyAdmin中使用“导出”功能将数据表的结构与数据保存为一个.sql文件（保存格式选择SQL）

恢复

在phpmyAdmin中点击“导入”，选择.sql文件的路径（格式选择SQL），点击执行。

 

mysql中的聚合函数

就是对指定字段中的一列数据进行统计和运算的函数。

GROUP BY分组

在指定字段中将数据内容重复的记录，聚合为一组。剔除重复的值。

示例：

SELECT * FROM student GROUP BY sSex

SELECT sName FROM student GROUP BY sAge

 

COUNT 统计个数

用于统计（按条件）查询出聚合后的记录或查询的结果一共有多少条。示例：

SELECT COUNT(*) FROM student //统计表中一共有多少条记录

SELECT COUNT(1) FROM student //同上，性能更强。

SELECT COUNT(1) FROM student WHERE sAge>17 //统计符合条件的记录总数

SELECT COUNT(1),sSex FROM student GROUP BY sSex

//将GROUP BY与COUNT两个函数结合起来使用，按性别进行分组统计。

 

AVG求平均值

格式：AVG(字段名)

对指定的字段中(一列中) 的数据值进行求平均值的运算。

SELECT AVG(sAge) FROM student

SELECT AVG(sScore) FROM student WHERE sAge<=18

SELECT AVG(sScore),sSex FROM student GROUP BY sSex

 

 

GROUP_CONCAT分组连接

将一张表中的多行记录中的指定的字段值，连接成一个字符串。每个值之间以逗号进行分隔。通常用于得到聚合后的每个分组中包谷的成员。

SELECT GROUP_CONCAT( sName ) FROM student

//连接所有学生的姓名

SELECT GROUP_CONCAT( sName ) FROM student WHERE sAge>18

//连接所有18岁以上的学生姓名

SELECT GROUP_CONCAT(sName),sSex FROM student GROUP BY sSex

//得到所有男生和女生的名单

 

 

ORDER BY排序

按照指定的字段的值的大小的次序，来排列查询的结果。

SELECT * FROM student ORDER BY sScore

//通过成绩来排列学生。默认为升序（从小到大）

SELECT * FROM student ORDER BY sScore DESC

//通过成绩来排列学生。添加DESC就是降序（从大到小）

 

 

最大值最小值MAX()、MIN()

在查询结果中的指定字段中找到最大的值或最小的值。

SELECT MAX(sScore) FROM student

//得到成绩最高分数

SELECT MIN(sScore),sSex FROM student GROUP BY sSex

//分别得到男生和女生最低分

 

 

求和sum()

在查询结果中对指定字段的值求和。

SELECT SUM(sSCore) FROM student

//全班总分

SELECT SUM(sSCore),sSex FROM student GROUP BY sSex

//查看男生总分和女生总分

 

 

小练习：

1、用Create指令创建一个产品销售记录数据表，包含字段：产品名、销售地区、销售数量、销售金额。(10分)

2、假设本公司出售的产品只有三种，销售地区也只有三个。用insert语句插入十条产品销售记录。（产品与销售地区肯定有重复值。）(15分)

3、统计每种产品的销售总额(10分)

4、统计每种产品的平均销售额。(10分)

5、统计每种产品的销售总数量。(10分)

6、在每个地区销售的每种产品的名称，连接成一个以逗号分隔的字符串。(15分)

7、同时统计每种产品的最大销售额。(10分)

8、统计产品在每个地区的最低销售额。(10分)

9、得到总销售额。(10分)

 

 

 

 

 

 

 

mysql_高级查询

数据过滤通配符

通配符，就是指能够通用的匹配其他字符的符号。

%通配符

%代表任意个数的任一字符，它通常是用在select语句中与LIKE关键配合使用的。

示例：

SELECT * FROM student WHERE sName LIKE '王%'

 

查找所有姓王的学生

LIKE关键字在这里代表模糊查询，不是像=那样必须完全匹配。

 

SELECT * FROM student WHERE sName LIKE '%五'

 

查找以“五”字结尾的数据

 

SELECT * FROM student WHERE sName LIKE '%老%'

 

查找中间包含“老”字的字符。

注意：即使数据的开头或结尾为空，依然可以匹配到数据，因为%不但代表任意个数的任意字符，它同样也可以代表没有字符。

注意事项：

1、%不但代表任意个数的任意字符，它也可以空字符。

2、数据尾部的空格可能会干扰通配符的搜索，比如：现有数据’abc  ’，如果它最后有一个或多个空格的话，则 %abc将不会查找到该数据，因为后面多余的空格也是字符。解决方法是前后都加上%。

3、%不能匹配null值。

 

_(下划线)通配符

_ 通配符的作用与%类似，但是它只能匹配单个任意字符。

 

示例：

SELECT * FROM student WHERE sName LIKE '小_'

SELECT * FROM student WHERE sName LIKE '小__'

 

查找以“小”开头的之后任意两个字符的数据，而且必须是两个字。

 

注意：下划线与%不同的是前者不匹配字符，必须得有一个字符才能匹配。

 

SELECT * FROM student WHERE sName LIKE '_老_'

 

 

SELECT 查询中的关键字

in关键字

在一个集合中进行匹配，只要数据与集合中的任意一项相同，就以为数据满足条件。

 

SELECT * FROM student WHERE sAddress IN('北京','西安','天津','山东')

 

查找地址是 北京,西安,天津 或 山东的学生信息

 

limit关键字

limite后面需要跟两个数字，代表从指定的记录开始（使用数据记录在物理上的次序而不是ID），查找多少条记录出来。

注意：

1、 次序是从0开始的，也就是说第一条记录的序号是0

2、 这里的序号不等于ID，它仅仅是代表排列次序。

3、 LIMIT关键字是mysql所独有的，比如：mssql和Oracle中就没有Limit关键字。

 

SELECT * FROM student LIMIT 3,2

 

从第4条记录开始，获取之后的两条记录。

 

join关键字 – 链表

join关键字用于在数据库中同时查询多张存在关联关系的数据表。

 

as关键字

用于对字段段取一个别名

SELECT sName as '姓名',sAge as '年龄',sAddress as '地址' FROM student

 

 

多表查询

内连接

就是指仅仅查询两张表中有关联关系的数据，而没有关联关系的数据是不会被查询出来的。

 

 

方式一：select同时from多张多

在select的from语句后同时写入多张表的名字，然后在where条件语句中写入多表之间的连接条件。

 

示例：

SELECT * FROM student,score WHERE student.sid = score.sid

 

注意：内连接之中，要分主表和附表，附表只是用于对主表所缺少的内容进行补充，比如上例中，主表是学生成绩，student学生只是在补充主表中缺少的学生姓名等数据。

SELECT

student.sName as '姓名',

   student.sAge as '年龄',

   score.sProject as '科目',

   score.score+10 as '成绩',

   score.sid as '编号'

FROM student,score

WHERE student.sid = score.sid

 

 

在多表查询的时候，为了避免由于字段名重复而产生错误，我们可以在字段名前面加上表名，以示区分。

 

方法二：inner join

使用inner join同样可以完成上例中的功能，而且主表与附表、连接条件一目了然。INNER JOIN子句的前面是主表，后面是附表，ON后面是表连接的条件。

 

SELECT

   student.sName as '姓名',

   student.sAge as '年龄',

   score.sProject as '科目',

   score.score as '成绩',

   score.sid as '编号'

FROM score INNER JOIN student

ON score.sid = student.sid

 

 

注意：省略INNER直接写JOIN关键字，也是内部连接。

 

左连接

在两张表联合查询的时候，我要显示主表中包括没有关联关系的数据在内的所有数据。

 

SELECT * FROM score

LEFT JOIN student

ON score.sid = student.sid

 

 

右连接

查询附表(JOIN关键字之后的表)中包含没有关联关系的数据在内的所有数据。

SELECT * FROM score

RIGHT JOIN student

ON score.sid = student.sid

 

多张表连接

示例：

SELECT * FROM score

INNER JOIN student ON score.sid = student.sid

INNER JOIN class ON student.cid = class.cid

 

通过关键字IN来进行关联查询

查询所有数学成绩在60分以上的学生的信息。

Select * from student where

sid in

(

    SELECT sid FROM score

    WHERE score >60 And sProject = '数学'

)

 

IN在此处表示，sid必须与()内的查询结果之一相等。