● 数据库概念和Mysql使用
一、数据库相关概念
Data、Database(DB)、DBMS、DBS、DBA
二、SQL: 结构化查询语言
SQL分类:
数据定义语言 :DDL(针对数据库对象):create-创建、alter-修改、drop-删除
数据操纵语言 :DML(针对数据信息):insert-插入、update-更新、delete-删除
数据查询语言 :DQL(针对数据信息):select-查询
数据控制语言 :DCL(针对用户):grant-授权、revoke-回收
三、服务及连接操作
1、 启动/关闭服务
启动指定服务:net start 服务名
关闭指定服务:net stop 服务名
2、 连接服务
mysql -u 用户名 -p -h -P
user password host port
默认用户名为:root,用户名为mysql数据库user表中user、host字段的组合,如果host字段值为%,则不需要书写,其他则如test@172.168.0.4
3、 查看所有数据库
show databases;
4、 使用数据库(表示后期操作都针对该数据库进行)
use 数据库名;
5、 查看指定数据库下边所有数据表
show tables;
四、用户管理
1、 创建用户
法一:创建用户(同时设置密码)
语法:create user 用户名称 [identified by ‘密码’];
法二:创建用户同时分配权限
语法:grant 权限 on 范围 to 用户 identified by ‘密码’;
2、 删除用户
语法:drop user 用户名;
说明:
A、ceshi@192.168.1.2表示“ceshi”用户可通过IP为“192.168.1.2”的地址访问指定数据库服务器
B、ceshi@192.168.1.%表示“ceshi”用户可通过IP为“192.168.1.0”至“192.168.1.255”之间的地址访问指定数据库服务器
C、create user c identified by ‘111’;
上边语句创建用户c对应的host地址为“%”
3、 更改密码
A、更改指定用户密码
语法:set password for 用户名=password(‘密码’);
B、更改自己的登录密码
语法:set password=password(‘密码’);
五、权限管理
1、 授权
语法:grant 权限 on 范围 to 用户 [with grant option];
说明:如果在授权语句中出现with grant option,代表被授权的用户可将自己的权限授予给别人
2、 回收
语法:revoke 权限 on 范围 from 用户;
3、 刷新权限机制
flush privileges;
A、权限作用范围及分类
① 全局权限Global Privileges
它是管理权限,作用到服务器上的所有数据库。要授予全局权限,使用 ON *.的语法,.*表示所有数据库的所有表(对象,视图等)中。MySQL 把全局权限保存在 mysql.user 表中
② 数据库权限Database Privileges
作用到某个特定数据库的所有对象上。要授予数据库权限,使用 ON db_name.* 的语法,db_name.*表示db_name数据库下的所有表(对象,视图等)。 MySQL 把数据库权限保存在 mysql.db 表中
③ 表权限Table Privileges
应用到某个特定表的所有列上。要授予表权限,使用 ON db_name.tbl_name 的语法。 MySQL 把表权限保存在 mysql.tables_priv 表中
④ 列权限
权限操作分类: 使用逗号分隔
◆ 列权限
作用在特定表的特定列上,在权限操作分类中体现列权限 select(col_name),记录到mysql.columns_prive表中 field
◆ 存储过程权限
作用在存储过程和函数上,记录到mysql.procs_prive表中 procedure
◆ 代理用户权限
作用是使一个用户成为另一个用户的代理,记录到mysql.proxies_priv表中 proxy
B、查看用户当前权限
语法:show grants for 用户名;
六、建库建表
注意:在SQL语法中,[]表示可有可无
1、 创建数据库语法:create database 数据库名;
2、 创建表语法:create table 表名(
字段名 字段类型(长度) …
);
3、 MySQL数据类型
①数值型
◆整型(tinyint,smallint,mediumint, int,bigint)
◆ 浮点型(float,double)
◆ 定点型(decimal)
② 字符型(char,varcharl blob,textl enum(多选一),set(多选多))
③ 日期和时间类型(date,time,datetime,timestamp,year)
4、 字段属性unsigned、zerofill的用法!!!
① unsigned 为非负数,用此类型可以增加存储数值范围!
② zerofill属性,在数字长度不够的数据前面填充0,以达到设定的长度
六、插入语句
语法:inset into 表名[(字段1[,字段2,…])] values(值1,值2,…);
注意:
1、 如果针对表中所有字段添加数据,可省略字段列表
2、 字段列表不是表中所有字段时,非空、无默认值的字段必须出现
3、 值列表与字段列表必须一一对应(数量、数据类型)
4、 对于字段列表中有允许为空字段,其值可使用default或null替代
5、 对于字段列表中有默认值字段,其值可使用default替代
强调:针对字符、日期和时间类型对应的数据值,在SQL语句中须使用英文单引号括起来!
七、更新语句
语法:update 表名 set字段1=值1[,字段2=值2,…] [where 条件表达式];
注意:在update语句中,如果没有出现where子句,则表示针对表中所有数据进行更新操作;如果表中出现where子句,则必须先根据条件表达式筛选数据,再行更新操作
八、注释
1、SQL标准注释方式:
◆ 单行注释:-- 注释内容(提醒:在第二个中横线后打一空格,再添加注释内容方有效)
◆ 多行注释:/注释内容/
2、MySQL注释方式:
◆ #注释内容
●数据库增删改操作
一、自动递增(auto_increment)
◆ auto_increment是数据列的一种属性,只适用于数字类型
◆ auto_increment对应数据列必须具备not null
◆ 一个表只能有一个字段使用auto_increment
◆ 将字段设置成auto_increment之后,是需要将其设置成主键/或者主键的一部分
◆ 用户添加数据时,该字段的值可不用添加
◆ 可在建表时可用auto_increment =n选项来指定一个自增的初始值
◆ 可用alter table 表名 auto_increment =n命令来重设自增的起始值,默认的起始值、步长均是1
二、enun、set的应用
1、 enum:枚举字符串(多选一)
◆ 将可能出现的数据列举出来,实际存储的数据只能是列举出来的数据
◆ 使用枚举有利于统一数据,以及节省存储空间(枚举只是存储数值,系统转换成字符串)
◆ 枚举元素实际上按定义的顺序标号,从1开始,所以可以直接插入枚举元素对应的数值
◆ enum的选项对应一个数字,依次是1,2,3,4,5…,最多有65535个选项
如:enum(‘a’,’b’,’c’),如果某行的对应值为2,则选中了“b”
2、 set:集合字符串(多选多)
◆ 定义了集合之后,不能插入集合元素之外的字符串,(可以插入多个符合条件的字符串)
◆ 集合字符串实际上也是仅仅存储数值,系统自动转换成对应的字符串
◆ 集合中每一个元素对应一个二进制位,被选中的为1,没选中的为0,左边为二进制中的低位,右边为高位,从右到左得到二进制结果
◆ set的每个选项值对应一个数字,依次是1,2,4,8,16…,最多有64个选项
如:set(‘a’,’b’,’c’),如果某行的对应值为5,二进制表示为:101(22+0+20=5),则选中了“a,c”
三、另类的insert
1、 一条语句插入多条数据值
语法:insert into 表名(字段列表) values(值列表1),(值列表2),…
2、 insert…select…(须先创建目标表)
语法:insert into 表名[(字段列表)] select 字段列表 from 表名;
注意:两处字段列表数量须一致,且数据类型能兼容
3、 create…select…(插入数据前自动根据源表所选字段构建目标表)
语法:create table 表名 select 字段列表 from 表名;
四、数据表的更改
删除外键约束:alter table b drop foreign key fk_no_id;
删除主键:alter table drop primary key;
删除unique:alter table drop index username;
添加字段: alter table add userpassword varchar(20) not null;
删除字段:alter table a drop userpassword;
更改字段属性:alter table a modify userpassword varchar(30) not null;
五、约束
1、数据完整性:是指存储在数据库中的数据的一致性和正确性。
2、约束(constraint)是通过限制列中的数据、行中的数据和表之间数据来保证数据完整性的非常有效的方法。
完整性类型 | 约束类型 | 描 述 |
---|---|---|
域完整性 (针对表中字段进行约束) | not null(非空) | 指定某列不为空 |
default(缺省) | 指定某一个列中的默认数值 | |
实体完整性 (针对表中记录/数据进行约束) | primary key(主键) | 每一行的惟一标识符,确保用户不能输入 冗余值和确保创建索引,提高性能,不允 许空值 |
unique(唯一) | 防止出现冗余值,并且确保创建索引,提 高性能;允许空值,但字段中的数据值不能出现重复 | |
引用完整性 (针对表与表之间的约束) | foreign key(外键) | 定义一列或者几列,其值与本表或者另外 一个表的主键/唯一键值匹配 |
3、约束示例
◆ 创建表时创建约束
◆ 表存在后创建约束——实质是对表结构进行修改
六、删除语句
使用delete语句删除表中的记录(数据),可以删除指定的行或同时删除所有的行。
语法:delete from 表名 [where 条件表达式];
逻辑运算符:与(and)、或(or)、非(not)
not(非) | 如果原来条件返回true,在其之前使用not之后则返回false。如果原来条件返回false,在其之前使用not之后则返回true。 |
---|---|
and(与) | and运算符可以连接两个或两个以上的条件,只有当and连接的条件都为true(真)时,and返回的结果才是true(真)。如果其中有一个条件为false(假),and返回的值就是false(假)。 |
or(或) | or运算符连接的条件中只要有一个条件为true时,or返回的结果就是true。当然两个条件均为true时,or返回的结果当然是true。 |
语法:truncate table 表名;
补充:delete与truncate的区别
◆ delete会记录日志,意味着删除后的数据还可以恢复,但是效率低。
◆ truncate不会记录日志,删除后的数据不能恢复,但是效率高;truncate不能用于有外键约束引用的表。
truncate应用场景:如果是开发一个应用程序,开始可能使用的是测试数据,当应用开发成功后,需要将真正的数据导入到数据表中,为了使自增主键从1开始,使用truncate操作数据表比较方便。
**注意:**在表中的操作绝大部分都是记录在案的(日志),数据库实质是通过文件来存储(数据文件、日志文件)
如果删除数据涉及外键引用则须注意:当主表的记录被从表参照时,主表的记录将不允许删除,如果要删除数据,需要先删除从表中依赖该记录的数据, 然后才可以删除主表的数据。(设置级联操作除外
补充:
◆ 语法:desc 表名;– 查看指定表结构
desc A;#语句执行结果如下所示
◆ show create table 表名;– 查看指定表定义语句
● 数据查询操作
1 、检索表中所有数据信息
select * from 表名;# *表示表中所有字段
2、检索表中部分字段信息
select 字段1,字段2,… from 表名;
3、列重命名
select 字段 [as] ‘别名’ from 表名;-- 建议保留as关键字
select cName as ‘课程名称’ from Courses;
select cName ‘课程名称’ from Courses;
4、排序(order by)
select 字段列表 from 表名
order by 字段1 [asc]|desc[,字段2 [asc]|desc,…];
说明:
A、排序方式包含asc(升序,由小到大,默认值)、desc(降序,由大到小)
B、可根据多个字段排序,但是每个字段有其自身排序方式,如果出现多个字
段排序,后者是在前者出现重复值的情况下有意义)
C、order by子句出现在where子句的后边(如果有where子句的话)
D、聚合函数可以作为排序依据,如order by count(字段) desc
select * from elogs order by elScore desc;
分析下边语句的作用
select * from Teachers order by tAge desc,tNo desc;
select * from Courses order by cName;#因为编码问题可能达不到想要的结果
#因为当前数据库语言设置为utf8,使用中文字段排序则须进行编码更改
select * from Courses order by convert(cName using gbk);
5、取多少条数据(limit)
#select 字段列表 from 表名 limit [m,]n;
注意:
1、limit关键字后只有一个数字n时,表示取前n条数据(此时m=0)
2、limit关键字后有两个数m,n时,表示从m+1行开始读取n条数据(常用于分页数据读取)
查询学生信息表中前五条数据
select * from Students limit 5;
select * from Students limit 0,5;
select * from Students limit 5,5;#6~10
任务:检索年龄最小的五位男老师信息
select * from Teachers where tGender=‘男’ order by tAge limit 5;
查找年龄最大的老师?—年龄最大的不止一位老师的时候!!!
思路:
第一步:找到教师信息表中最大的年龄值
第二步:在教师信息表中找到与年龄最大值相同年龄的老师信息
法一:
select * from Teachers where tAge=(
select tAge from Teachers order by tAge desc limit 1
);
法二:
select * from Teachers where tAge=(
select max(tage) from Teachers – max(字段名):获取该字段数据中的最大值
);
重点提醒:聚合函数不能直接出现在where子句中!!!
select * from Teachers where tAge=max(tage);#错误的
6、聚合函数
max(字段名):返回指定字段的最大值
min(字段名):返回指定字段的最小值
avg(字段名):返回指定字段的平均值,只能针对数值型
sum(字段名):返回指定字段的和值,只能针对数值型
count():返回结果集的信息条数
count(*)
count(1)
count(主键字段)
count(非主键字段)
注意:
1、max(字段)、min(字段)、avg(字段)、sum(字段)、count(非主键字段)在统计时
自动排除字段值中的null值
2、count(*)、count(1)、count(主键字段)返回表中所有符合条件的信息数目
3、avg(字段)、sum(字段)在统计字符字段时,返回值为0
4、聚合函数不能直接嵌套使用
5、聚合函数不能直接出现在where子句中
统计选修记录信息表中,记录信息的数量及考试成绩的平均分
select count(*),count(1),count(elno),count(elscore),avg(elscore) from elogs;
select avg(cName),sum(cName),max(cName),min(cName) from Courses;
#select max(count(cid)) from elogs;-- 错误的
7、取消重复值(distinct)
#结果集中重复的数据值只会出现一次
select distinct tage from teachers;
练习:
查询选修考试成绩中,课程编号为2的前十名同学学号及成绩信息
select sid,elscore from elogs where cid=‘2’
order by elscore desc limit 10;
统计课程编号为00002的考试有多少学生未参考(考试成绩为空表示未参考)
select count(*) from elogs where elscore is null and cid=‘00002’;
select count(*)-count(elscore) from elogs where cid=‘00002’;
select * from elogs;
查看课程编号为00002,考试成绩为60、70、80、90、100的学生学号
select sid from elogs where elscore in (60,70,80,90,100)
and cid=‘00002’;
查询参与了选修的同学学号有哪些?
select distinct sid from elogs;
查询参与了选修的同学姓名
explain select sName from Students where sno in (
select sid from elogs
);
desc select sName from Students as S where exists(
select sid from elogs where sid=S.sno
);
8、子查询
子查询是指一个查询语句嵌套在另一个查询语句内部的查询
子查询(内层)结果作为外层SQL操作的过滤条件
子查询可以添加到select、update、delete语句中
子查询可进行多次嵌套
子查询类型
◆ 带比较运算符的子查询
◆ 带in关键字的子查询
◆ 带exists关键字的子查询
◆ 带any/some关键字的子查询
◆ 带all关键字的子查询
外部数据大于内部数据,使用in
内部数据大于外部数据,使用exists
in子查询:
select 字段列表 from 表A where 表A.字段 in(
select 表B.字段 from 表B
);
exits子查询:
select 字段列表 from 表A where exists(
select 主键字段 from 表B where 表B.字段=表A.字段
);
注意:
1、表A.字段与表B.字段有主外关联为佳
2、exists子查询中内部语句的where条件表达式是两表对应字段
#exists根据子句返回是否有结果(有数据返回,则exists返回真;否则返回假),以确定其前面的select语句是否需要执行
select * from Students where exists(select * from elogs);
select * from Students where exists(select * from elogs where cid=‘1’);
9、合并结果集union
默认将结果集中的重复值取消,如果不想取消重复值,则在union的后边增加all
select tName from Teachers
union all
select cName from Courses;
10、连接技术
连接技术:通过方法将两个及以上的表关联在一起(逻辑上看成一个表来操作)
内连接:inner join
语法:select 字段列表 from 表1 inner join 表2 on 表1.主键字段=表2.外键字段 [where 条件表达式];-- 建议
或
select 字段列表 from 表1,表2[,…] where 表1.主键字段=表2.外键字段 [and 条件表达式]
说明:呈现多表中能匹配的数据(有条件表达式存在则再筛选)
外连接:outer join
左外连接:left outer join/left join
语法:select 字段列表 from 表1 left join 表2 on 表1.主键字段=表2.外键字段 [where 条件表达式];
说明:以左表(left join左边的表)为基表呈现数据,右表中无对应匹配的数据则以null呈现(有条件表达式存在则再筛选)
右外连接:right outer join/right join
语法:select 字段列表 from 表1 right join 表2 on 表1.主键字段=表2.外键字段 [where 条件表达式];
说明:以右表(right join右边的表)为基表呈现数据,左表中无对应匹配的数据则以null呈现(有条件表达式存在则再筛选)
问题:统计《计算机网络基础》这门课程缺考的同学有多少
select count(*) from ELogs where elScore is null and cID=?
select cNo from Courses where cName=‘计算机网络基础’;
内连接(两表连接)
select count(*) from ELogs as E inner join Courses as C on C.cNo=E.cID where cName=‘计算机网络基础’;
子查询
select count(sID) from eLogs where elScore is null and cID =(select cNo from Courses where cName=‘计算机网络基础’);
问题:老张老师上了哪些课
分析:牵涉到两个表,分别是教师信息表(Teachers)、课程信息表(Courses)
Teachers、Courses存在主外关联(Courses.tID------>Teachers.tNo)
本次查询的是课程名称(cName)
本次涉及的条件是:老张老师参与授课(tName=‘老张’)
select cName from Courses inner join Teachers on tNo=tID where TName=‘老张’;
问题:查看选修了《C语言》这门课程的学生姓名
分析:牵涉到三个表,分别是学生信息表(Students)、课程信息表(Courses)、选修记录信息表(ELogs)
Students、ELogs存在主外关联(ELogs.sID------>Students.sNo)
Courses、ELogs存在主外关联(ELogs.cID------>Courses.cNo)
本次要查询的是:学生姓名(sName)
本次涉及的条件是:选修过《C语言》这门课程(cName=‘C语言’)
内连接(三表连接)
select sName from Students inner join ELogs inner join Courses where sNO=sID and cNo=cID and cName=‘C语言’;
问题:查看未进行选修的学生姓名
分析:未选修则表示:选修记录信息表中无这些学生学号的存在
学生姓名在学生信息表中,而选修涉及选修记录信息表
但此题并不是多表之间匹配数据的涉及,故不使用内连接解决
可考虑使用子查询 或 外连接技术实现
子查询
select sName from Students where sNo not in(
select distinct sID from ELogs
);
– 左外连接
select sName from Students left join ELogs on sNo=sID where elNO is null;
– 右外连接
select sName from ELogs right join Students on sNo=sID where elNO is null;
11、分组技术
#语法:select 字段列表 from 表名 [where 条件表达式] group by 字段1[,字段2,…] [having 条件表达式] [order by 字段列表]
统计在选修记录信息表中,已登记同学每人选修课程门数
select * from ELogs
select count(*) from ELogs group by sID;
查询已登记的每位学生姓名及其选修课程数目
select sID,count(*) from ELogs group by sID;
连接技术实现
– select * from ELogs inner join Students on sNo=sID;
select sName,count(*) from ELogs inner join Students on sNo=sID group by sID;
子查询技术实现
select (select sName from Students where sNo=sID),count(*) from ELogs group by sID;
应用group_concat()函数将指定字段(课程编号)以列表形式呈现在结果集内(就是所选课程的编号)
select sName,count(*),group_concat(cID) from ELogs inner join Students on sNo=sID group by sID;
select sName,count(*),group_concat(cName) from ELogs inner join Students on sNo=sID
inner join Courses on cNO=cID group by sID;
查询已登记的张姓同学的姓名及其选修课程数目
– 建议使用where进行数据的先行筛选再分组
select sName,count(*),group_concat(cName) from ELogs inner join Students on sNo=sID
inner join Courses on cNO=cID where sName like ‘张%’ group by sID;
select sName,count(*),group_concat(cName) from ELogs inner join Students on sNo=sID
inner join Courses on cNO=cID group by sID having sName like ‘张%’;
查询已登记的张姓同学的姓名及其选修课程数目,且选修课程门数必须大于三门才能呈现
select sName,count(*),group_concat(cName) from ELogs inner join Students on sNo=sID
inner join Courses on cNO=cID where sName like ‘张%’ group by sID having count(*)>3;