【Mysql】基础知识

MySQL

四大基本MySQL语句

01. D Q L <数据查询语言>

01.基本形式

基本由 select + from + where组成
--> select 字段
--> from 表或视图名(就是来自哪)
--> where 条件
--> 最简单的全部查询
--> select * from `表名` 

02.条件查询_where

01.比较运算符

#     >，< , >= , <= , = , <>(不等于)

02.范围查询

between ... and ...(简化代码)
# 例如:查询80 -90之间的学生记录
select StudentResult
	from result 
		where StudentResult >= 80 and StudentResult <= 90
		
select StudentResult
	from result 
		where StudentResult between 80 and 90

03.in列表查询

# in (值列表) -- 包含括号中的所有数据
# 例: 查询科目id为1 , 2,3的成绩  in:在....里面
select * 
	from result
		where SubJectId in (1,2,3)

04.模糊查询

# like %(任意数量的任意字符) _(任意的一个字符)
# 例1:查询地址中带有京的
select *
	from student
	where address like "%京%" 
# 例2:查询姓徐的两个字的名字 的学生信息 _:匹配一个字符
select * 	
	from student
		where StudentName like "徐_"

05.null

#  is null:判断是否为'空' / is not null :判断不是空的值
# 例1:查询包含邮箱为null的字段
select *
		from student
			where Email is null
select *
		from student
			where Email = ''
# 例2:查询不为null 的字段
select *
		from student
			where Email is not null

06.逻辑运算符

#    and--&&(与) , or--||(或) , not--!(非)

07.排序

order by 字段名 asc(升序--默认可以不写) / desc(降序);

08.分页查询

 # 作用:限制查询的条数   (每页显示几条 ,第几页)
 # 格式: 起始下标,每页显示的条数
 # 起始下标 = (当前页 - 1) * 每页显示的条数 
 
 # 例题:查询c语言中前5的学号 姓名 成绩
select s.StudentNo '学号',s.StudentName '姓名',r.StudentResult'成绩'
		from student s
			inner join result r on r.StudentNo = s.StudentNO
				where r.SubjectNo = (
					select SubjectNo 
						from `subject`
							where SubjectName = 'C语言-1'
				)
				order by r.StudentResult  desc
				limit 5

09.分组

# group by 字段名[ , 字段2 , 字段3 ....]
# 补充: where having   分组后数据筛选

-- 例:分组查询
# 按照不同的课程分组，分别算出其平均分、最高分和最低分，对于低于60分平均分的不予显示
# 课程名 最高分 最低分 平均分
# 本来max 只会有一个结果 分组后 就会按照分组来求出多个结果
select s.SubjectName,s.SubjectNo, max(r.StudentResult),min(r.StudentResult),avg(r.StudentResult) avg
	from `subject` s
		inner join  result r on r.SubjectNo = s.SubjectNo
		 group by s.SubjectName
			 having avg > 60

03.补充

01.起别名 as

例如: from `Student` as 's' = from Student s(简化)

02.去重 distinct

# 去除返回结果中重复的字段
例如:select distinct StudentNo
	from result  # 默认全部显示(all)

03.查询MySQL版本

select VERSION() mysql_v,3.14*5 as result # 避免各种诡异的报错

04.笛卡尔积

# 笛卡尔积 :数据膨胀(只有在等值查询中会有出现,而非等值查询不会出现这种问题) -- 解决方法:连表查询
例:
#　笛卡尔积　（两张表中拥有相同数据）
select student.StudentNo,result.StudentNo,StudentName,StudentResult
	from student,result
# 解决方法 (关联两表)
select student.StudentNo,result.StudentNo,StudentName,StudentResult
	from student,result
			where student.StudentNo = result.StudentNo

02. D M L <数据操纵语言>

01.插入数据：insert into

insert into 表名(字段列表) values(值列表):值列表要跟字段列表要对应；
# 例:
insert into student(StudentNo,StudentName,sex,GradeId,Phone,Address,Email,IdentityCard) values(1011,'郭靖',1,1,'13500000001','北京海淀区中关村大街1号','[email protected]','450323198612111000')

02.更新/更改：update

update 表名 set 字段名 = 值(这里是要修改的值) [where 条件]
update 表名 set 字段1 = 值1, 字段2 = 值2 , 字段3 = 值3 [where 条件]
# 例:
update subject set  ClassHour = ( ClassHour - 10 ) where ClassHour>110 and GradeID = 1;
# 删除某一列中的某一数据,将其修改为null

03. 删除：delete from

delete from 表名 [where 条件]
# delete  from news_detail;
# truncate content;保持表结构
# where content = '23'这个删除的话只能删除一整行

03. D D L <数据定义语言>

01.定义

# 用于定义各种对象-->表、视图、索引、同义词、聚簇、数据库...

02.主要几个用法

# create table /view /index /syn /clustre /database
# 主键自增: primary key 与 auto_increment 相对应(前面记得写上数据类型,一般都是int类型)
# comment "默认值":选择默认值
# engine = innodb 与 MyIsam 推荐用前者,前者安全性更高.
# 选择字符集:CHARset=utf8
# 注意:DDL操作是隐性提交的！不能rollback

03.操作数据库

-- 创建数据库
create database[if not exists]数据库名；
-- 修改数据库名
alter table 原数据库名 rename as 修改后的数据库名;
-- 删除数据库
drop database[if exists] 数据库名；
-- 查看数据库
show database；
-- 使用/选择数据库
use 数据库名；

04.操作表格

-- 修改表名
alter table 旧表名 rename as新表名
-- 添加字段
alter table 表名 add 字段名列类型[属性]
-- 修改字段
alter table 表名 modify 字段名 列类型[属性]
alter table 表名 change 旧字段名 新字段名 列类型[属性]
-- 删除字段
alter table 表名 drop 字段名

04. D C L <数据控制语言>

01.定义

-- 数据控制语言DCL用来授予或回收访问数据库的某种特权，并控制
-- 数据库操纵事务发生的时间及效果，对数据库实行监视等。
-- BEGIN：开始事务 SAVE：保存

02.grant:授权

03.rollback:回滚

rollback [work] to [savepoint]：回退到某一点。# rollback:回滚
# 回滚命令使数据库状态回到上次最后提交的状态。其格式为：
SQL>rollback;

04.commit [work]：提交。

# 注意:在数据库的插入、删除和修改操作时，只有当事务在提交到数据
# 库时才算完成。在事务提交前，只有操作数据库的这个人才能有权看
# 到所做的事情，别人只有在最后提交完成后才可以看到。
# 提交数据有三种类型：

01.显式提交、

# 用commit命令直接完成的提交为显式提交。其格式为：
SQL>commit；

02. 隐式提交

# 用SQL命令间接完成的提交为隐式提交。这些命令是：
-- ALTER，AUDIT，COMMENT，CONNECT，CREATE，DISCONNECT，DROP，
-- EXIT，GRANT，NOAUDIT，QUIT，REVOKE，RENAME

03.自动提交

# 若把AUTOcommit设置为on，则在插入、修改、删除语句执行后，
# 系统将自动进行提交，这就是自动提交。其格式为：
SQL>set AUTOcommit on；

其他补充

01.DBMS

# (Database Management System)数据库管理系统

02.连接数据库语句

# mysql -h 服务器主机地址 -u 用户名 -p 用户密码(黑窗口)

03.索引(基本)

-- 主键就是索引
-- 唯一不能重复
-- 提升数据查询速度
-- 一张表必须要有一个索引

04. MyISAM 与 INNODB 的区别

MyISA: 支持全文索引 内存占用少
INNODB: 支持事务处理/外键约束 安全性高

05.delete 与 truncate 的区别

--> delete from 表名;
    truncate student; 
    不会清空表结构会清空自增计数器 
--> delete from student; 
    不会清空计数器

06.连接查询

# 01--> 内连接: 两张表中的数据必须匹配才能返回(输出共同拥有的)
格式: from 主表格
	 inner join 副表格 on 等值条件
例如:使用内连接查询科目表 java是那个年级的
内连查询
select s.SubjectName,g.GradeName
	from `subject` s
		inner join grade g on g.GradeId = s.GradeId
		where s.SubjectName = 'Java'
连表查询
select s.SubjectName,g.GradeName
	from `subject` s,grade g 
	  where g.GradeId = s.GradeId and s.SubjectName = 'Java'	  

# 02外连接 	
# 01--> 左外连接: 返回所有左表中的数据和匹配到的右表数据 没有匹配到的使用null填充(输出共同拥有的跟左边的).
格式:from 主表格 left join 副表格 on 等值条件
右外连接: 和左外相反
# 02--> 自连接: 在一张表中右父级字段和子级字段 相互有关联
例如:查询所有的动作游戏
select gameType
	from game 
		where parentId = 2;

07.子查询

形式:
# 例1:查询课程为《高等数学-2》且分数不小于80分的学生的学号和姓名
select StudentNo,StudentName 
from student 
where StudentNo in (
	select StudentNo 
		from result 
			where StudentResult >= 80
				and SubjectNo = (
				 select SubjectNo 
					from `subject` 
						where SubjectName = '高等数学-2'
			)
)

-- 三表联查的综合应用
# 例2:查询<<JAVA第一学年>>课程成绩前十且分数大于80的学生信息
select s.StudentNo '学号',s.StudentName'姓名',su.SubjectName'课程名',r.StudentResult '成绩'
# 三链接
from Student s 
inner join result r on r.StudentNo = s.StudentNo
inner join `subject` su on su.SubjectNo = r.SubjectNo 
where s.StudentNo in(
select s.StudentNo
from result
where r.StudentResult > 80
and SubjectName = "JAVA第一学年"
)
# 设置逆序排列,抽取前十成绩
order by r.StudentResult desc
limit 10
-- Ctrl+/:注释快捷键

08.函数的应用

# 聚合函数		
# 计数  总共有多少个学生  不建议使用*号效率低 1效率高
# 事实上表有索引 * 自动匹配索引
# count函数是统计此表中总共有多少函数
select count(1) from student

# 统计所有的学生一共考了多少分
select sum(StudentResult) from result 

# 平均分
select avg(StudentResult) from result 

# 最高分
select max(StudentResult) from result

# 最低分
select min(StudentResult) from result

# 字符串函数
# 连接   模糊查询 like  '%hello%'
select CONCAT('%','关键字','%')

# 字符串替换 8 - 3   
# 从1开始数 从几开始替换 替换几个
select INSERT('苏炳添以9.88的速度冲破了亚洲记录!',8,1,'3')

# 大小写转换
select LOWER("HelloWorld")
select upper("HelloWorld")
		
# 字符串截取 从几开始截取 截取几个
select SUBSTRING('美国和中国会晤,不支持台独',6,2)

# 日期函数
# 当前日期
select curdate()
# 当前时间
select curtime()
# 日期和时间
select now()
# 今天是一年中第几周
select week(now())

# 获取日期中的年份
select  year(BornDate) from student
# 计算学生有多大
select year(now()) - year(BornDate) from student

# 获取现在的小时
select hour(now())

# 获取现在的分钟
select minute(now())

# 计算两个时间的间隔
select DATEDIFF(now(),BornDate) / 365 from student

# 计算150之后是几月几号
select ADDDATE(now(),150)

#数学函数
# 向上取整
select ceil(3.14)
# 向下取整
select floor(3.14)
#随机数
select rand()

# 获取100 -125之间的随机数
select floor(rand()*(125-100)+100)

09.mysql常见报错

-- 1.ERROR 1064 (42000):You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'love China' at line 1
-- 语法错误
-- 
-- 2.ERROR 1007 (HY000): Can't create database 'ilovechina'; database exists
-- 数据库已存在
-- 
-- 3.ERROR 1008 (HY000): Can't drop database 'ilovachina';database doesn't exist
-- 数据库不存在
-- 
-- 4.ERROR 1049 (42000):Unknown database 'database'
-- 数据库不识别
-- 
-- 5.ERROR 1046 (3D000): No database selected
-- 没有选中数据库
-- 
-- 6.ERROR 1113 (42000):A table must have at least 1 column
-- 数据表至少要有一列
-- 
-- 7.ERROR 1146 (42S02): Table 'ishop1.cuetomer' doesn't exist
-- 数据表"cuetomer" 在数据库“ishop1"中不存在
-- 
-- 8.