MySQL语法笔记1

MySQL语法笔记

第一部分数据表的创建、备份和导入
1) 表查看show

Show variables like “%charset%”; 1查看库的字符集
Show create table home; 2查看表的字符集
Show full columns from home; 3查看字段字符集
Show tables; 4 查看表有多少张表
Select * from table; 5 查看表的所有信息
Show warnings; 6查看警告
desc home; 7 查看表的字段类型
Select * from home; 8查看表的字段信息

---

表修改modify=modify=rename to =drop=add

Alter database laishijin charset utf8; 9更改库的字符集
Alter table home charset gbk; 10更改表的字符集
Alter table home modify name varchar(10) charset gbk; 11更改字段字符集
Alter table home modify name varchar(10) charset gbk; 12更改数据类型
Alter table home change home newhome varchar(10); 13更改字段名
Alter table home rename to “名字”; 14更改新表名
Alter table home add hobby varchar(10) first; 15添加字段放在第一位
Alter table home add 收入 varchar(10) after Phone; 16 添加字段方在谁字段的后面;
Alter table drop primary key 17删除主键
Alter table add primary key(Name,Id); 18添加双主键
Name varchar(10) primary key(Name ,Id) 19创表时添加双主键

---

字段增 insert into 表

Insert into home 20字段的插入的格式
(Name,Age) 如果不写这一段就默认是全部的字段
Values
(“雨落”20);

字段的改==update

 Update home 22字段的修改
Set 字段名=值, 字段名2=值2
Where 字段名=值;

 Update 表名
Set 字段名=值+10;
例如：update home
set Age=Age-10;

字段内容的删除detele

Detele from 表名 where 字段名=值; 23字段内容的删除格式
Detele from 表名 24删除表的内容 慎用
Drop table 表明 这才是删除表
===表的拷贝带筛选的=
Create table 新表名（select * from 原表）; 24实现整个表达拷贝
25 实现一个字段的拷贝
Create table 新表名 (select 字段名1，字段名2，字段名3 from 旧表名 where 字段名=值)
26实现模糊拷贝
Create table 新表名 (select * from 原表where rlike “^赖”)

数据表还原

1首先要使用库 27
2 source 文件多在的地址
例如 source F:/MySQL/teacher.sql ;

数据表的导出

1要换路劲 d: 28
2要选择数据库bin下的目录进行路径选
3进行数据表的到出。Mysqldump -u root -p 库 表名>地址；
4：cd \Wampserver\wamp\bin\mysql\mysql5.6.12\bin>F:/MySQL/home.sq
29 set 和enum的用法 set可以取多个例如兴趣 而enum只能取一个例如性别
表的数据类型：
1数值形：严格数值形 int decimal 近视似数值形float double
2字符串形：char varchar text longtext
3时间类型的：data datetime(YYYY-MM-DD)区别 timestamp(YYYYMMDD)
4、InnoDB引擎是目前唯一可提供外键实现支持的引擎
5、约束.主键约束primary key ,2外键约束foreign key ,
3默认值约束deaflult ,4唯一约束unqiune
5非空约束not null

第二部分数据表的查询

where、group by、having、order by、limit

select * from 表名; 1、查询所有的字段
select 字段1,字段2 from 表名; 2查询想查的字段
select 字段1 as 别名, 字段2 as 别名 from 表名; 3给查询字段起表名
select * from 表名 where 字段名=值 ; 4带条件的查询
select * from 表名 limit ; 5带限定记录的查询

带有字段函数名的查询

Avg() ,count() , max() , min () , sum () 6常用函数
例题：select age(degree) as 班级平均分,max(degree) as 班级最高分, min(degree ) as班级最低分 ,sum(degree) as 班级总分 from sc where cno=“c02”;
Select sum(cno) as 总科目 from sc; 错误写法 7区分sum*()和count()函数
Select count(cno) as 总科目 from sc ; 8、count返回某字段的行数（计数）
Sum()返回某字段的和
Select * from sc limit 2,14; 从第一行开始查询到14行
Select * from sc limit 2,14 从第二行开始查询14行的记录

去从查询

Distinct (字段名) 备注：distinct往往结合count一块查询
例如：select distinct(cno) from sc; 9去重查询sc表达课程
Select count(distinct(cno)) from sc; 10对课程去重并且统计个数

日期函数查询 出生日期查询

1year, 返回年份 2month 月 3day 天 year(字段名);
2date_format(字段名,format) date_format=日期格式
Select 字段名,date_format(字段名,”%Y%M%d%d”) from 表名; 11日期函数查询
例如：select sname ,date_format(sbirathday,(“%Y%M”)) from student ;
查询学生表的出生年月
select sname ,date_format(sbirathday,(“%Y%M”)) from student where year(sbirthday)!=1988
查询学生表里面出生年不为1988的学生，并且以字段名显示出来
3、timestampdiff(单位,字段名,结束时间)
例如：student sname,timestampdiff(year,sbirthday,now()) from student;
4、Date_format和timestampdiff 的区分 12、Date_format和timestampdiff 的区分
Select sname,2020-year(sbirthday) from studemt; 第一种查询
Select sname,2020-date_format(sbirthday,”%Y”) from student 第二种查询
Select sname,timestampdiff(year,sbirthday,now()) from student; 第三种查询
Year<date_format<timestampdiff 关系链

单表条件的查询

比较运算符：>= <= != !< !>
逻辑运算符： not , and , or

单表范围查询

1、between and 2、not between and 13、下面涉及到日期函数的使用
范围查询什么时候用：求该字段的值在这范围里面或者不在这范围里面
例如:查询stuent表学生出生年在1985到1990年之间的学生的年份
Select sname,year(sbirthday) from student where year(sbirthday) between 1985 and 1990;
Select sname,year(sbirthday) from student where year(sbirthday)not between 1985 and 1990;
语法；select * from 表名 where 字段 not between 值1 and 值2；

单表的列表查询

列表查询什么时候用：求该值的该字段的值的相关信息 有in 就肯定有not in
Select * from 表名 where 字段名 in(值1,值2); 14列表查询可以说是指定的范围返回相关信息
例如 14列表查询
select sname,year(sbirthday) from student where date_format(sbirthday,”%Y”) in(1988,1999);、
select sname,year(sbirthday) from student where date_format(sbirthday,”%Y”)not in(1988,1999);、
select * from 表名 字段名 not in (值1,值2,值3);

单表的模糊查询

模糊查询分为SQL模式like()和正则模式rlike() 15模糊查询
SQL regexp对象
1、表示以值开头的 格式：like”值%” 例如 “黄%”
2、表示以值结尾的 格式；like”%值” 例如 “%值”
3、表示含有的值 格式；like”%值%” 例如 “%值%”
语法select * from 表名 where 字段名 like”%”;
2、表示以指定值开头的 值 值 值
4、表示以指定值开头的 格式：like”值” 例如 “黄”
5、表示以指定值结尾的 格式；like”值_” 例如 “_值”
6、表示指定有的值 格式；like”值” 例如 “值”
语法 select * from 表名 where 字段名 like”_值”
二着相互结合使用的格式：
例如：查询student表sphone 的第三个号码数字为3的同学的相关信息
Select * from student where Sphone like”__3%”;
注意,在命令Mysql console 里面不区分大小写查询

正则表式 rlike(regexp)
1、表示已值开头的 格式；”^值” 例如；rlike”^值”
2、表示以值结尾的 格式；“值$”例如;rlike“值$”
3、表示含有值的 格式 rlike”赖” 例如；rlike “赖”
4、表示指定位置出生的 格式rlike”.{n}”
4说明：.表式空格 {n} 表示第几个空格 省是要找的值
例如：select * from student where saddress rlike".{2}省" ;
例如：select * from student where sno rlike “^.{3}7” limit 10; 第三个空是7
5、正则表达式进行or匹配
Select * from 表名 where 字段名 rlike “值|值”;
例如：select * from student where sname rlike"^张|王";

单分组查询 group by

Group by 分组 having 分组后筛选
备注：可以和函数结合使用
Select * from student group by dept; 主角是dept
select sdept,count(sno) from student group by sdept;

多分组查询group by

Select 字段, 字段函数,from 表名 group by 字段1,字段2;
例如：查询每个系男女的个数
Select sdept ,count(*) from student group by sdept,ssex;

单表排序 ordery by

升序：asc 默认是asc; 排序注要针对数值行的排序
降序：desc
Select * from sc order by 字段名 desc
例如对学生成绩进行降序排序
Select sname,degree from sc order by degree desc;
select count(*) from sc group by cno; 说明：分组是能重复的

having 用法

跟在group by 后面
例如：查询student 表里每个系年龄不超过49岁的学生人数,并且按人数降序排序
Select sdept,count() from student where timestampdiff(year,sbirthday,now())<=40 group by sdept order by count() desc;

子查询\难点

子查询返回的字段
1、字段><=()
2、字段 in()
3、字段 >all
4、字段 >any
1带比较运算符的子查询
例如：查询和于田田在一个系的学生的姓名
Select snmae sdept from student where sdept=(select sdept from student where sname=”于田田”);
例如：查询a03课程成绩比2007010104这位同学a03成绩高的同学的学号以及对应的成绩
Select sno ,degree from sc degree>=(select degree from sc where sno=”2007010104” and cno=”a03”) and cno=”a03”;
(select degree from sc where sno=”2007010104” and cno=”a03”) 2007010104这位同cno成绩
难点：区分 degree = 和 degee In 的区别
例如：查询电子工程系已经选修课程的学生的学号,选修的课程以及成绩(涉及表student sc)
Select sno,cno,degee from sc where sno in(select sno from student where =”电子工程系”);
备注：sno返回的是多个字段
Degee= 返回一个值
Degree in 返回多个值
什么时候用 字段 In
1、 内层查询只有一个字段
2、 且该字段返回不止一个值

子查询的多表查询

分析：正向思维和逆向思维
1首先要找两张表或者多张表的关系字段
2要用到字段 in() 查询
例如：查询大学英语和软件工程授课老师的名字 涉及表couse teaching teacher
Select tno from teacher tno in(select tno from teaching cno in(select cno from course where cname=”大学英语” or cname=”软件工程”));
1 select cno from course where cname="大学英语"or cname=“软件工程”;
2 select tno from teaching where cno in(select cno from course where cname="大学英语"or cname=“软件工程”);
3 select tname from teacher where tno in (select tno from teaching where cno in(select cno from course where cname="大学英语"or cname=“软件工程”));
 带字段名 >all() 关键字的子查询
 带字段名 >any()关键字的子查询
All 解析：要符号所有的条件
Any 解析： 符号其中一个需求就可以
例如：查询出生日期比信息工程系所有学生的出生日期都打的学生信息
Select * from student where sbirthday > from student where sdept =”信息工程系”);
注意：

例如2；查询成绩有高于学号位2007010104这位同学的任意一门成绩的学生的学号，课程成绩
Select * from sc where degree>any(select degree from sc where sno=2007010104);

子查询的from 查询

注意：使用from()子查询的时候必须起别名；
语法格式select 字段名 from (selelct * from 表名 where 字段名=值) as a;
查询于田田所在的系别
Select * from (select * from student where sname=“于田田”) as a;

多表连接查询

多表连接分为内连接和外连接
需求：多表连接可以找出 在自己表里面没有的字段内容
内连接语法1：select 字段名 from 表1,表2 where 表1.关系字段=表2.关系字段
内连接语法2：select 字段名 from 表1 join 表2 on 表1.关系字段=表2.关系字段
特点：
1满足左右的连接条件才会出现在结构里面
2.其实相当于左右拼接表
注意点：
1要找到关联的关系字段
2要是有相同的关系字段，必须明确来自那一张表
例题1
查询”大学英语”和”软件工程”授课老师的名称以及授课的课程(course ,teaching,teacher)
Select teacher.tname,course.cname from teaching,course,teacher where teacher.tno
=teaching.tno and Teaching.cno=course.cno and( cname=”大学英语” or cname= “软件工程”);
解法2：
Select teacher.tname,course.cname from teaching,course,teacher where teacher.tno
=teaching.tno and Teaching.cno=course.cno and cname in(“大学英语”,“软件工程”);
注意： 还可以起别名 方便操作

内连接的自连接

什么时候用自连接 答：发生在同一列
注意：自连接和for()子查询一样必须起别名
例题：
查询c01的课程比c04课程成绩高的学生的学号以及c01和c04对应的课程成绩
Select a.sno, a.degree ,b.degree from sc as a, sc as b where a.sno=b.sno and a.cno=”c01”
and b.cno=”c04” and a.degree>b.degree;

外连接

外连接又分为 左连接和右连接
语法格式： from 表1 left join on表2 on 表1.关系字段=表2.关系字段 where 表2.关系=null
语法格式2：from 表1 right join on表2 on 表1.关系字段=表2.关系字段 where 表1.关系=null
定义：外连接是表的包含关关系 student >sc sc表有的student一定有而student有的sc表不一定有
例题1
查询没有选课的学生的名字
左查询：Select sname,cno from student left join sc on student.sno=sc.sno where cno is null ;
右查询; select student.sno,sc.cno from sc right join student on student.sno =sc.sno where sc.cno is null ;

外连接左右联合使用

第三部分设置外键
设置外键的好处
设置外键的步骤
1创建一张表，这张表是从表 拿 sc来说把
2然后查看主表的唯一字段 例如primary key unqiue defeault
3建立从表和主表一样的数据类型 1字段的主键 2 字段的字符集 3 表的字符集 4表的引擎
4建立外键的语法
Alter table 从表 add constraint 别名 forign key(从表字段名) references主表名(从表字段名)
Alter table sc add constraint sc_teacher forign key (sno) references teacher(sno);
建立完外键表以后
1从表不能插入主表没有的字段
2主表不能删除从表有的字段
Alter table 表名 drop 字段名;
第四部分库表的导出入与写入

5月16日csv表导入数据库步骤

show variables like “%secure%”; 查询导入路径的限制
导入csv表的前提条件：1把格式.Excel另存为.csv格式, 2以笔记本形式打开并改字符集为utf8格式
1.建库
mysql>create database tabledaoru;
2.使用库
mysql> use tabledaoru;
3.建表
mysql> create table score(
-> 学号 varchar(20) primary key,
-> 年级 varchar(20) ,
-> 性别 char(2) ,
-> 姓名 varchar(10),
-> 数学 int(4),
-> 语文 int(4),
-> 英语 int(4),
-> 地理 int(4),
-> 历史 int(4),
-> 化学 int(4),
-> 生物 int(4))charset utf8;
4.导入数据
mysql> load data infile “F:/MySQL/查询导入文本/graduateutf8.txt” into table score
-> charset utf8
-> fields
-> terminated by ‘,’ #以逗号隔开
-> ignore 1 lines; #忽略第一行