数据库基本知识以及MySql语句
1.主键约束:
保证数据的完整性。
一个表只能有一个主键;主键列不允许为空。
2.外键约束:主表关联从表
MySql命令行连接MySql:
mysql-h服务器主机地址(本机可省略)-u用户名-p密码(可省略,回车继续输入密码)
mysql -u root -p root
SQL结构化查询语言简介:
数据操作语言DML:操作数据。
INSERT;UPDATE;DELETE。
数据定义语言DDL:创建删除数据库对象。
CREATE;DROP;ALTER。
数据查询语言DQL:数据库查询。
SELECT
数据控制语言DCL:控制数据库组件许可权限。
GRANT;REVOKE;commit;rollback。
SQL运算符:
+ - * / % = > < >= <=
<>:不等于
AND:两个表达式都为true返回true
OR:两个表达式都为false返回false
NOT:取反操作。
DDL操作数据库:
创建数据库:
CREATE DATABASE 【IF NOT EXISTS】数据库名;
查看数据库:
SHOW databases;
选择数据库:
USE 数据库名;
删除数据库:
DROP DATABASE 数据库名;
DDL操作数据库表:
数值类型:
TINYINT:非常小的数据
INT:标准整数
DOUBLE:双精度浮点数
CHAR:定长字符串
VARCHAR:可变字符串
DATATIME:年月日时分秒
TIMESTAMP:时间戳。年月日时分秒连在一起。
设置当前时间时用到
创建表:
CREATE TABLE [IF NOT EXISTS] 表名
( 字段 数据类型 [字段属性 约束] [索引] [注释]
)[表类型][表字符集][注释]; #(注释符号)例如创建学生表:
CREATE TABLE `student`(
`studentNo`INT(4) PRIMARY KEY,
`name` CHAR(10),
.....
);comment='';
属性 约束:
NOT NULL:
非空约束。
default:
默认约束。
unique KEY:
唯一约束。
primary KEY:
主键约束。
foreign KEY:
外键约束。
auto_increment/auto_increment=10:
自动增长/从10自动增长约束。通常用于主建。
set *** auto_increment_increment=5;
所有自动增长以+5方式。
unsigned属性:
标识为无符号属性值。不可取值为负数。相当于给 正数位数扩大一倍。
zerofill属性:
宽度(位数)不足时以0填充。
若指定了ZEROFILL属性,将自动添加UNSIGNED。
set sql_mode='strict_trans_tables';
严格模式。
不能插入多于指定长度的列类型。
注释:
comment'注释内容'
表字符集设置:
charset=字符集名称;
数据库字符集设置
character set 字符集名称;
校对规则:
collate 校对规则;
查看表:
SHOW tables;
查看表定义:
describe 表名;
避免DOS窗口乱码:
SET NAMES GBK;
删除表:
DROP TABLE [IF EXISTS]表名;
删除表数据:
delete from 表名;
截断表数据:
truncate 表名;
MySql系统帮助:
HELP 查询内容;
修改表名:
alter table 旧表名 rename as 新表名;
添加字段:
alter table 表名 add 字段名 列类型【属性】
修改字段:
alter table 表名 modify 字段名 列类型 【属性】
修改列名:
alter table 表名 change 旧字段名 新字段名 列类型【属性】
添加主键;
alter table 表名 add constraint 主键名 primary key(字段名);
添加外键;
alter table 表名 add constraint 外键名 foreign key(字段名)references 外表(外表主键);
删除字段:
alter table 表名 drop 字段名
暂时关闭外键约束:
SET FOREIGN_KEY_CHECKS=0;
引入sql文件:
source sql文件路径。路径用/隔开。
MySql存储引擎:
SHOW engines;
列出当前版本支持引擎。
MyISAM:
不支持事务处理;不支持数据行锁定;
不支持外键约束;支持全文索引。较小。 不需事务,空间小,以查询访问为主。
InnoDB:
支持事务处理;支持数据锁定;
支持外键约束;不支持全文索引。较大。
多删除更新操作,安全性高,事务处理及并发控制。
设置表的表类型引擎:
CREATE TABLE 表名( …)engine=存储引擎;
MyISAM类型表文件:
以.frm表结构定义文件 以.MYD数据文件;一个表对应一个文件 以.MYI索引文件
InnoDB类型文件:
以.frm表结构定义文件 以ibdata1数据文件;多个表对应一个文件
DML语句:
插入单条记录:
insert into 表名 (字段名) values 值;
插入多条数据:
insert into 表名 (字段名,字段名,字段名)values(值,值,值),(值,值,值),...;
插入具有单独条件的数据表记录:
insert into 表名 (字段名) values(字段名的值);
将查询到的结果集插入新表:
create table 新表 (select 字段1,...from 原表);
更新数据记录:
update 表名 set 字段1=值1,...where 条件;
删除数据:
delete from 表名 where条件;自增列不会重置。推荐,有事务数据安全机制。
truncate table 表名;自增列重置。截断表。不推荐。不受事安全机制的影响。
DQL语句:
查询语法:
select 列名|表达式|函数|常量|关键字 (列)
from 表名
where 查询条件|表达式 (行)
order by 排序的列名 默认ASC升序排 DESC降序排列 ;
查询全部行和列:
select * from `表名`;
查询部分列:
select `列名`
from`表名`
where 条件;
使用AS命名列:
select `studentNo`AS 学生编号
from `student`
where`address`<>'河南';
distinct关键字:
去掉select查询返回的记录结果中重复的记录。所偶有返回列都相同的情况下值返回一条。
查询null行:
select `列名`from `表名`where`列名` is null;
查询被清空的列名is null 换成:where `列名`='';
如何使用常量列:
select `studentName` AS 姓名,`address`AS 地址,
`北京信息中心`AS 学校名称
from`student`;
模糊查询行:
like关键字:
select * from student where studentname like '张'
like关键字若不带任何通配转义字符,则是严格查询。
任意字符%:
select *from student where studentname lie '张%'
占位任意字符_:
select *from student where studentname like '张__'
可以连用:
select *from student wher studengname like '_飞%'
指定转义字符的escape关键字、转移字符:
select *from student where studentname like '\%' escape '\\'
in关键字:
select*from student where studentresult in (80,90,100);
匹配到指定数值就返回
between关键字:
selecrt * from result where studentResult between 80 and 90;
在指定范围匹配到数值就返回
常用函数:
聚合函数:常与group by 一起用
avg(字段名)查询某个字段平均值;
count(字段名)查询行数;
max(字段名)查询最大值;
min(字段名)查询最小值
sum(字段名)查询和;
字符串函数:
concat(str1,str2,...)字符串连接。
insert(str,pos,len,newstr)字符串替换。
lower(str)字符串转化为小写。
upper(str)字符串转化为大写。
substring(str,num,len)字符串截取。
日期函数:
curdate()获取当前日期。
curtime()获取当前时间。
now()获取当前日期和时间。
week(date)获取date为一年中的第几周。
year(date)获取date的年份。
hour(date)获取date的小时值。
minute(date)获取date的分钟数。
datediff(date1,date2)获取相隔天数。
adddate(date,n)计算date加上n天后的日期。
数学函数:
seil(x)大于或等于x的最小整数。
floor(x)小于或等于x的最大整数。
rand()随机数。
通用函数:
select version();返回mysql版本号
select 100*3f;返回计算结果
order by子句:
order by子句实现按一定顺序显示查询结果,默认为升序,desc为降序。
把成绩都降低10%加5分后查询及格成绩从高到低显示
select`studentNo`as 学生编号,
(studentResult*0.9+5)as 综合成绩
from `result`
where(`studentResult`*0.9+5)>=60
order by studentResult desc;
limit子句:
limit[位置偏移量][行数];
从位置偏移量开始显示指定行数数据
简单子查询: 不允许查询多个记录
先执行括号里面的子查询语句再执行外围的父查询语句
括号内子查询结果必须为一。
显示比李思文同学小的学生信息,并显示。
#父查询语句
select`studentNo`,`studentName`,`sex`
from`student`
where`bornDate`>
#子查询语句
(select`bornDate`
from`student`
where`studentName`='李思文');
查询”java”课程至少一次考试刚好等于60分的学生。
表连接实现: 适合多表数据
select`studentName`from`student`stu
inner join `result`r on stu.studentNo=r.studentNo
inner join `subject`subon
r.subjectNo=sub.subjectNo
where `studentResult`=60 and `subjectName`='java'
子查询实现: 将查询嵌套到查询中适合单表查询
select`studentName`from`student`
where`studentNo`=
(select `studentNo`from `result`
inner join`subject`on result.subjectNo=
subject.subjectNo
where `studentResult`=60 and
`subjectName`='java');
in子查询 not in子查询: 允许查询多个记录
查询"java"课程至少一次考试刚好等于60分的学生。
将where后的等号换为in即可。
子查询实现: 将查询嵌套到查询中适合单表查询
select`studentName`from`student`
where`studentNo` in
(select `studentNo`from `result`
inner join`subject`on result.subjectNo =
subject.subjectNo
where `studentResult`=60 and
`subjectName`='java');
也可以写为:
select`studentName`from`student`
where`studentNo` in(
select`studentNo`from `result`
where `subjectNo`=(
select`subjectNo`from`subject`
where`subjectName`='java'
)and`studentResult`=60
);
查询参加’java’课程最近一次考试的在读学生。
#查询出参加'java'课程最近一次考试的在读学生名单
select`studentNo`,`studentName`from`student`
where`studetnNO` in(
#获取参加'java'课程最近一次考试的学生学号
select`studentNo`from`result`
where`subjectNo`in(
#获取'java'课程的课程编号
select`subjectNo`from`subject`
where`subjectName`='java'
)and `examDate`=(
#获取'java'课程最近一次考试日期
select max(`examDate`)from`result`
where`subjectNo`=(
#再次获取'java'课程的课程编号
select`subjectNo`from`subject`
where`subjectName`='java')));
exists子查询:
如何用sql语句检测temp表已经创建:
drop table if exists temp;
exists用法:
select 列表 from 表名 where exists(子查询);
如果子查询为true执行外部循环
如果子查询为false不执行外部循环
检查’java’课程最近一次考试;
如果有80分以上成绩则显示排在前5名的学员学号分数。
#java课程的课程编号
select subjectNo from subject where subjectName='java';
#获取java最近一次考试日期
select max(examData) from result where subjectNo=(
select subjectNo from subject where
subjectName='java');
#找到成绩达到80分的纪录
select studentNo,studentResult
from result
where subjectNo=(
select subjectNo from subject where subjectName='java')
and examData=(
select max(examData) from result where subjectNo=(
select subjectNo from subject where
subjectName='java'))
and seudentResult>80;
#显示java最近一次考试前五名学生信息
select studentNo,studentResult
from result
where exists(
select studentNo,studentResult
from result
where subjectNo=(
select subjectNo from subject where subjectName='java')
and examData=(
select max(examData) from result where subjectNo=(
select subjectNo from subject where
subjectName='java'))
and seudentResult>80;)
and subjectNo=(
select subjectNo from subject where subjectName='java')
and examData=(
select max(examData) from result where subjectNo=(
select subjectNo from subject where
subjectName='java'))
order by studentResult desc
limit 5;
not exists子查询:
检查java最近一次考试
如果全部未通过考试,则本次考试偏难,计算该次平 均分加五分
#java课程的课程编号
select subjectNo from subject where subjectName='java';
#获取java最近一次考试日期
select max(examData) from result where subjectNo=(
select subjectNo from subject where
subjectName='java');
#找到成绩达到60分的纪录
select studentNo,studentResult
from result
where subjectNo=(
select subjectNo from subject where subjectName='java')
and examData=(
select max(examData) from result where subjectNo=(
select subjectNo from subject where
subjectName='java'))
and seudentResult>=60;
#计算平均分并增加5分
select avg(studentResult)+5 as 平均分
from result
where not exists(
select studentNo,studentResult
from result
where subjectNo=(
select subjectNo from subject where subjectName='java')
and examData=(
select max(examData) from result where subjectNo=(
select subjectNo from subject where
subjectName='java'))
and seudentResult>=60)
and subjectNo=(
select subjectNo from subject where subjectName='java')
and examData=(
select max(examData) from result where subjectNo=(
select subjectNo from subject where
subjectName='java'));
分组查询;
group by分组子句查询:
统计每门课程的平均分显示
select subjectNo,avg(studentResult) as 成绩
from result
group by subjectNo;
having分组子句:
统计每门课程的平均分只显示平均分及格的信息
select subjectNo,avg(studentResult) as 成绩
from result
group by subjectNo
having avg(studentResult)>=60;
where子句来筛选from子句中指定的操作筛选。
group by子句来分组where子句。
having子句用来筛选group by的结果 。
多表连接查询:
内连接查询inner join on
:
内连接使用比较运算符根据表中的通用列匹配表中的行。
select 需要显示的字段
from 需关联的表1
innerjoin需关联的表2
on(表一.相同字段=表二.相同字段);
inner join 表三
on (表n.相同字段=表n.相同字段);
外链接查询:
左外连接查询left join on:
left join左边的表为主表,右边为从表
左表显示全部信息,右表中没有左表的信息则为null
右外连接right join on:
right join左边的表为从表,右边的表为主表
右表显示全部信息,左表中没有右表的信息则为null
事务transation:
向工作单元执行一系列操作来防止出现故障数据不一致。
要么都执行要不都不执行。
必须具备以下ACID属性:
1.原子性Atomicity:事务各部操作不可分割。
2.一致性Consistency:事务完成后数据必须一致。
3.隔离性Isolation:各种并发事务会处于隔离状态。
4.持久性Durability:事务完成后修改被永久保持。
操作事务:
关闭自动提交功能;
开始事务:
begin;或者start transation;
提交事务:
commit;
回滚(撤销)事务:
rollback;
开启自动提交功能。
从张三的账户转出500元,存入李四的账户中。
begin;
update `bank` set `currentMoney`=`currentMoney`-500
where `customerName`='张三'
update`bank` set `currentMoney`=`currentMoney`+500
where `customerName`='李四'
commit;
关闭/开启自动提交事务:
set autocommit=0|1;
关闭后需要用commit或者rollback结束该事务。
关闭后提交事务后最好再次开启自动提交。
视图:
虚拟表。筛选表中的行。
防止未经许可的用户访问敏感数据
降低操作数据库的复杂程度
将多个物理数据库抽象为一个逻辑数据库
可以相互嵌套
语法:
创建视图:
create view 视图名
as
查询语句
删除视图:
drop view if exists 视图名
查看视图:
select 字段一,字段二...from视图名
查看所有视图 :
use information-schema;
select*from views
索引:
有效组合数据的方式,快速查找到指定记录。
分类:
B-树索引。
哈希索引。
常用索引:
普通索引:允许索引列中插入重复值和空值。
唯一索引unique:数据不重复,允许有一个空值。
主键索引:主键列中非空唯一。主键自动创键。
复合索引:将多个列组合作为索引。
全文索引fulltext:支持值的全文查找允许重复和空值,用于char varchar text
只有MyIsAm类型数据表才支持
全文索引要用
where match(列名)against(要检索的关键字);
空间索引spatial:空间数据类型的列建立的索引。
创建索引:
在创建表示在字段后边添加
key`普通索引名 `(`字段一`,`字段二`),
index`普通索引名`(`字段一`),
或者创建表后追加
create 索引类型 index 索引名
on 表名(索引字段)
删除索引:
drop index 索引名 on 表名
删除全部索引
例子:
创建学校学生名字索引
use myschool;
create index `index_student_studentName`
on`student`(`studentName`);
查看索引:
show index from 表名\G(按列显示)
创建索引的原则:
频繁搜索,用作查询,经常排序,分组,连接的列
不要仅包含几个不同值,仅包含几行就创建索引
数据库备份:
MySQLdump命令–备份工具:将create和insert into语句保存到文本文件。 为dos命令。mysql命令行无法执行
mysqldump 选项参数 -u 用户名 -h 登录名 -p 密码
需备份的数据库名 需备份的数据库表名 > 备份后的文件名称
省略数据库名和表名则备份全部。
选项参数:
-add-drop-table:在每个create table前添加drop table。
默认是打开 ,可以使用-skip-add-drop-table取消。
--add-locks:锁定表。防止导入时其他用户再进行表操作。
默认是打开的
-t或-nocreate-info:只导出数据不添加create table语句。
-c或--complete-insert:在每个insert之前加上列名。
-d或--no-data:不写表的数据,只存储表结构。
数据库恢复:
方法一:mysql -u 用户名 -p 恢复后数据库名 < 文件名。
此方法为DOS命令。数据库必须存在。
方法二:source 文件名;
此方法为mysql命令行下使用。必须登录mysql。数据库必须存在并且选择。
导入导出数据:
导出数据语法:
select*from 导出表的表名
where 条件
into outfile '导出的文件' 参数(option:使导出文件具有更好可读性。) 导出文件路径为/分割。
导入数据语法:
load data infile 文件名 into table 表名 参数(option:使导出文件具有更好可读性。)