我的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*1038)
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 语言完成以下功能
- 建库、建表,要求添加主键
- 插入指定的数据
- 找出所有年龄小于19岁的学生学号、姓名、年龄。
- 学生张三转到化学系 111 班,请更新相关的表。
- 删除班级表的主键这一列。
- 将学生表改名为T_STD
- 为班级表添加字段CLS_COUNT人数字段
- 为班级表添加主键。
- 删除班级表
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必须与()内的查询结果之一相等。
上一篇: 安全蛋居然有这些功效,你还不来看看吗?
推荐阅读
-
javascript学习笔记(三)显示当时时间的代码_基础知识
-
我的derby学习笔记之一:derby开始准备 博客分类: 我的学习 DerbyEclipse嵌入式.net
-
我的derby学习笔记之二:嵌入式derby的JDBC驱动 博客分类: 我的学习 嵌入式DerbyJDBCJavaJDK
-
我的Python学习笔记_Day15 面向对象编程1
-
那些年,我还在学asp.net(一) 学习笔记
-
那些年,我还在学习asp.net(二) 学习笔记
-
那些年,我还在学asp.net(一) 学习笔记
-
那些年,我还在学习asp.net(二) 学习笔记
-
Spring学习笔记之bean的基础知识
-
我的php学习笔记(毕业设计)_PHP教程