第二章:MySLQ必知必会
02-数据库
MySLQ必知必会1--MYSQL 安装与基本语法
1、主要配置MySQL Router Configuration
Hostname:localhost
Port:3306
Management User:root
password:123456
2、MySQL-cmd命令测试mysql -u root -p #使用超级管理员登陆
h localhost -P 3366 -u root -p #访问本地主机
3、sql语言及其分类是一种特殊目的的编程语言,是一种数据库查询和程序设计语言,用于存取数据以及查询、更新和管理关系数据库系统;同时也是数据库脚本文件的扩展名。
3.1、DDL:Data Defintion Language(Create 、Alter、Drop、Show 、Desc)
3.1.1 Create:新建数据库、表
create databaseifnotexistsDBname;#创建一个新的数据库,DBname
useDBname;#进入数据库,DBname
show tables;#查看当前库内所有的表格
CreatetableifnotexistsTablename(#创建表
`字段名1`列类型(属性)约束,
`字段名2`列类型(属性)约束);
descTablename;#描述表结构
3.1.2 Alter:设计表
AltertableTablenameadd约束类型(表字段名);# 创建约束
AltertableTablenamedrop index约束名;#删除约束(不建议使用index)
AltertableTablenamedrop primary key;#删除约束
AltertableTablenamedrop foreign key外键名;#删除外键
Altertable旧表名renameas新表名;#修改表名
AltertableTablenameadd字段名列类型(属性);#添加字段
AltertableTablename**modify字段名列类型(属性)**;#修改字段类型
AltertableTablename**change旧字段名新字段名列类型(属性)**;#修改字段
AltertableTablenamedrop字段名;#删除字段
3.1.3 Drop:删除库、表、约束
Dropdatabase dbname;#删除库
DroptableTablename;#删除表
Dropindex约束名onTablename;#删除约束
3.1.4 Show:查看所有数据库、表及查看约束
show databases;#查看所有数据库
show tables;#查看库内所有表
Showindex(orkeys)fromTablename;#查看约束
3.2、DML:Data Management Language(Insert、Delete、Update、Select、Truncate)
3.2.1 Select:查询数据
语法:Selectfield1,field2..fieldnfromtablename[whereconditions];`#见-2`
3.2.2 Updata:更新数据
语法:update tablenameset字段名1=new_value1字段名2=new_value2whereconditions;
3.2.3 Insert:插入数据
InsertintoTablename[(field1,field2...)]values(value1,value2...);#有字段名插入,值与字段名一一对应
InsertintoTablenamevalues(value1,value2...);#无字段名插入,需包含所有的字段值
Insertintotablename[(feild..)]values(value..),(value..),...,(value..);#多个值的同时插入
sql语句中,对于自动增长的字段名在插入值时可以使用null或default代替,不可留空;
date类型的数据在插入时,用字符串即可
3.2.4 Delete:删除数据,
DeletefromTablenamewherecondition;#根据条件删除数据
DeletefromTablename;#如无条件将删除整张表
3.2.5 Truncate
Truncate[table]Tablename;#清除表数据,但表结构、索引、约束等不变
Delete与Truncate的比较:比较
delete
truncate
相同点
删除数据时,不删除表结构
删除数据时,不删除表结构
不同点
条件删除,不重置AUTO_INCREMENT计数
清除表所有数据,会重置AUTO_INCREMENT计数
3.3、DCL:Data Control Language数据库管理系统提供了数据控制语言实现对数据库的控制,包含数据完整性控制、数据安全性控制和数据库的恢复等。(一般用不到)
4、MySQL中数据类型数值类型
int(tinyint 、 smallint 、 mediumint 、 bigint)
float:4字节
double:8字节
decimal:字符型数字,涉及需要高精度计算时常用,比如金融领域
bit:1~8字节
字符串类型
char:一个字符,长度0~255
varchar:长度0~65535,常用字符串类型,上限根据给定长度,可伸缩;
text(tinytext 、mediumtext 、longtext)
日期类型
date:4字节 1000-01-01~9999-12-31
datetime:8字节 1000-01-01 00:00:00~9999-12-31 23:59:59
timestamp:4字节 19700101080001~2038年
time
year
5、MySQL中的约束与属性作用
提高查询速度,
确保数据的唯一性,
可以加速表和表之间的连接,实现表与表之间的参照完整性, #外键
使用分组和排序子句进行数据检索时,可以显著减少分组和排序的时间,
全文减速字段进行搜索优化
约束
primary key:主键约束:某一个属性组能唯一标识一条记录
1)、最常见的索引类型
2)、确保数据记录的唯一性
3)、确保特定数据记录在数据库中的位置
Unique:唯一约束
功能与主键相同,可以避免某数据列中值重复,可以有多个,但主键唯一
Foreign key:外键约束
1)在设计表时添加外键
Create table if not exists Tablename(filed1 int auto_increment primary key,field2 int(11) not null, constraint FK_name foreign key(field2) references othertable(字段名))engine=innoDB;
引擎有InnoDB和MyisAM两种,只有innoDB才支持外键
2)为已添加好的数据表添加外键
Alter table 需要添加外键的表 add constraint FK_name foreign key(需要添加外键的字段名) REFERENCES 作为外键的表(作为外键的字段名);
Auto_increment:自增长约束
1)、从1开始,不可更改
2)、对于主键和unique约束都可以使用auto_increment约束,无书写顺序要求
属性
Unsigned:无符号的,不允许为负数
Null和Not Null:默认为Null,设置为Not Null时必须有值
Default:用于设置默认值
Comment:用于添加注释 #字段名 列类型[(属性)] [约束] [注释],注意书写顺序
6、其他如果使用到mysql中的关键字作为表名,字段名,必须使用反引号来标注,例如:order
MySQL命令操作不区分大小写
SQL语句指定默认字符集:create table Tablename(…)default charset=utf8;
MySLQ必知必会2--MySQL查询语句
1、单表查询
1.1 函数
SelectCount(field)astotalfromtablename;#Count()函数,统计field有多少条记录
SelectAvg(feild)asfield_avgfromtablename;#Avg()函数,统计field所有记录的平均值
SelectSum(feild)asfield_sumfromtablename;#Sum()函数,统计field所有记录的和
SelectMax(feild)asfield_maxfromtablename;#Max()函数,统计field所有记录的最大值
SelectMin(feild)asfield_minfromtablename;#Min()函数,统计field所有记录的最小值
as后为别名,代替count(field)显示结果
count(*) -> null值将被统计;count(feild) -> null值将被忽略
1.2 查询语句
1.2.1 distinct关键字
Selectdistinct field1,feild2,..feildnfromtablename;#去掉需要查询的重复数据
1.2.2 condition语句支持+、-、*、/、>、
Between a and b([a,b]):SELECT 字段列1,字段2 ,…FROM 表名 WHERE 字段x BETWEEN 值1 AND 值2;
In:Select * from 表名 where fieldx in ( 值1,值2,值3…);
Like:使用LIKE关键字进行模糊查询 “%”表示匹配任意多个字符,“_”表示匹配单个字符
field in (condtion1、condtion2...) 等价于 field=condition1 or field=condition2 or ...
1.2.3 Group by 分组
Select[field1,]count(fieldx)fromtablenamegroupbyfieldy;#对查询到的数据按照fieldy进行分组
1.2.5 Order by 排序
selectfield1,field2fromtablenamegroupbyfieldn[ascordsec];#升序或者降序,默认升序
1.2.6 Limit 切片,分页查询
selectfield1,field2fromtablename limit[m,]n;# m为索引(从0开始),n为限制的个数
select*fromTablenamelimt(page-1)*pageNum,pageNum;#查询某一页的所有数据
Having与Where的区别:
selectfield1,field2fromtablename`where`condition1groupbyfieldn;
selectfield1,field2fromtablenamegroupbyfieldn`having`condtion1;比较
相同点
不同点
having
都可以配合各种conditions语句使用
只能用在group by之后,且可以对统计函数进行条件筛选
where
都可以配合各种conditions语句使用
可以用在group by和order by之前,不可对统计函数进行条件筛选
对统计函数的筛选:select count(field1) as num from tablename group by fieldn having num>2;
2、多表查询
2.1 子查询场景:已知数码产品的商品分类为ct_id为0,根据该条件从商品表中查询出所有的数码产品
select*fromcommoditywherec_type=
(selectct_idfromcommoditytypewherect_name="数码产品");
#将另一张表中的查询结果作为另一个表中的查询条件,c_type为ct_id的外键
select*fromgradewhere(level,mark)=(selectlevel,markfromgrade1wheremark=60);#多字段值查询
2.2 链表查询
数据:
# 表a数据 #表b数据:
id name id name
----------------------------------
1a1b
2aa2bb
4aaaa3bbb
2.2.1 左链表查询:Left join...on
selectid,namefroma left join b on a.id=b.id;
#结果:
id name id name
----------------------------------
1a1b
2aa2bb
4aaaanullnull
2.2.2 内链表查询:Inner join...on
selectid,namefroma inner join b on a.id=b.id;
#结果:
id name id name
----------------------------------
1a1b
2aa2bb
2.2.3 右链表查询:Right join...on
selectid,namefroma right join b on a.id=b.id;
#结果:
id name id name
----------------------------------
1a1b
2aa2bb
nullnull3bbb
2.2.3 全链表查询:Full join 或者Inner join #无on条件
selectid,namefroma full join b;
selectid,namefroma inner join b;
#结果:a的每一行与b的每一行进行组合,最后共有m*n行数据,m:a表的行数,n:b表的行数
多表链接:
#可以通过多次使用join语句来链接多张有关联表
selectsno,sname,avg(score),count(score<60ornull)as\`不及格门数\`from(**students**`right join`**score**on*students.snos=score.sno*)`left join`**course**on*score.cno=course.cno*GROUP BY sno having \`不及格门数\`in(2,3);
selecta.sno,b.sname(通过表名.字段名)from(selectsno,count(score<60ornull)askfromscore GROUP BY sno having k>=2)a inner join students b(students表的别名)on a.sno=b.sname;
'a'为前面括号内的别名,也可以直接给biao可以简化代码
通过表.字段名,可以避免两个字段名重复时出现的筛选是出现字段指代不明的错误