MySQL学习笔记

一、mysql环境安装

服务+管理工具（省略）

二、Mysql

DB2 Sybase Oracle SQL Server Mysql

Access MangoDB(noSQL)

Apache/nginx服务器：

LAMP Linux+Apache+Mysql+PHP

LNMP Linux+Nginx+Mysql+PHP

WAMP window+Apache+Mysql+PHP

SQL:

DDL(数据定义语言)： create dorp alter

DML（数据操作语言）:insert update delete

DQL(数据查询语言): select

DCL（数据控制语言）： comment revoke

三、进入mysql服务器

1.直接通过管理工具（navicat）

2.通过mysql的命令进入

3.通过cmd命令进入

Cmd:

Mysql的安装路径/bin/mysql –h locahost –u root –p 回车

Enter password:XXXX

四、使用navicat操作关联sql脚本

进入navicat之后，建立连接=》然后打开数据库=》选择查询=》新建查询=》书写sql语句

1.-- 查询系统中现有的所有数据库

show databases;

2.-- 创建数据库语法:

CREATE DATABASE 【if not EXISTS】数据库名称

DEFAULT CHARSET utf8; --设置数据库的编码

推荐写上if not EXISTS，可以避免当数据库存在的时候不会报错（不再创建数据库）

3.-- 删除数据库

DROP database 【if EXISTS】数据库名称;

4.新建的数据库保存的物理文件路径，查看数据库的配置文件my.ini中：

#Path to the database root

datadir=“C:/ProgramData/MySQL/MySQL Server 5.5/Data/”；

找到创建的数据库文件夹，里边db.opt文件。就是该数据库的物理文件。

5.创建表的步骤：

1.确定表中的列（字段）

2.确定列的数据类型（字段的数据类型）

3.确定每一列的属性（是否是主键，是否为空。。。。。）

字段名称	字段类型	字段属性
学号（studentID）	int	主键 not null
姓名（ studentName）	Varchar(20)	not null

Default charset=utf8;

use 1219sql; – 定位数据库

CREATE TABLE IF not EXISTS studentinfo

(

studentID int not null,

studentName VARCHAR(20) not null

)

DEFAULT CHARSET=utf8; – 设置表的编码

SELECT * from studentinfo; – 查看表的信息

drop table studentinfo – 删除数据表

五、mysql中的数据类型

1. 数值类型
在这里插入图片描述

浮点类型:float/double
在这里插入图片描述

2.字符串类型
在这里插入图片描述
注：如果要存文本，可以用text。

枚举类型：

Enum(‘值1’，‘值2’、、)	枚举类型，将可取值列举出来，只能选择一个值
Set(‘值1’，‘值2’、、)	枚举类型，将可取值列举出来，可以选择一个或者多个值

3.时间/日期数据类型
在这里插入图片描述

timestamp（时间戳）比较特殊，如果定义一个字段的类型为timestamp，这个字段的时间会在其他字段修改的时候自动刷新。所以这个数据类型的字段可以存放这条记录最后被修改的时间，而不是真正来的存放时间。

datatime中的时间可以用字符串表示。注意，在数据库中，用单引号表示字符串。

六、字段属性

1. null/not null: 设置是否为空

2.Default: 设置默认值（在mysql中default是字段的属性，不是约束）

3. auto_increment: 设置自增量（在mysql中自增量可以接受自己输入）

4.zerofill: 用“0”填充值达到显示列的宽度（只对数值有作用）

5.unsigned :非负限定只能对数值有作用（只能放在数据类型后边，其他属性前面）

七、数据库中的约束

在SQL server 中有五种约束：主键约束、外键约束、默认值约束、唯一约束、检查约束，但是在mysql中，对于检查约束不是完全支持（列值如果是数字类型的范围限定，可以使用坚持约束）,对于外键约束，需要数据表的类型来限定，在mysql中default不算默认约束，

在mysql中有三种约束：

主键约束： primary key
外键约束： foreign key
唯一约束: unique

– 查询创建表的信息

show create table 表名;

创建学生信息表：studentinfo

学号	姓名	性别	专业	年龄	爱好
10000	Xxxx	xxxx	xxx	xxx	xxx

创建课程表：course

课程编号	课程名称
1	Php
2	.NET

创建学生成绩表：score

. create table if not exists score

(

sid int auto_increment not null,

stuid int not null,

courseid int not null,

score float not null,

PRIMARY key(sid),

FOREIGN KEY(stuid) REFERENCES studentinfo(stuid),

foreign key(courseid) REFERENCES course(cid)

– 添加外键约束

) auto_increment=1

default charset=utf8;

show create table studentinfo;

CREATE TABLE `studentinfo` (

`stuid` int(11) NOT NULL AUTO_INCREMENT,

`stuName` varchar(20) NOT NULL,

`stuSex` enum(‘男’,‘女’) DEFAULT ‘男’,

`stuMajor` varchar(20) NOT NULL,

`stuAge` tinyint(4) DEFAULT NULL,

`stuHobbies` set(‘吃饭’,‘睡觉’,‘敲代码’) DEFAULT ‘敲代码’,

PRIMARY KEY (`stuid`)

) ENGINE=InnoDB AUTO_INCREMENT=10003 DEFAULT CHARSET=utf8

查看表的结构：

desc 表名; – 查看表的结构

– <===>

show COLUMNS from 表名;

– 查看备注的内容 comment

show full columns from course

八、约束写法:

1.直接在列的后面写：eg

Uid int not null auto_increment primary key,

2.在所有列的后面写

Constraint pk_uid primary key(uid) – 主键约束

constraint fk_tid FOREIGN KEY (tid) REFERENCES tb1(tid)

- –外键约束

缩写: primary key(uid)

FOREIGN KEY (tid) REFERENCES tb1(tid)

九、数据库移植

Engine – 引擎

InnooDB和MyISAM表类型的

数据库移植问题：

MyISAM类型表的直接将数据库文件夹拷贝到另外一台机器上的对应的目录下；

InnoDB类型表：需要通过使用mysqldump在一台机器转储你的表，也就相当于备份以下，然后还原!

1、对于InnoDB类型表的数据库备份还原步骤如下：

DOS命令

Cmd中输入:

Mysql安装路径\bin\mysqldump –uroot –proot 要备份的数据库的名称>备份文件的保存路径\xxx.txt

例如：
在这里插入图片描述

然后将备份文件考到到别的机器中的磁盘里（E:\books.txt），然后再进行还原，在cmd中输入命令：

Mysql安装路径\bin\mysql –uroot –proot 要还原到的数据库的名字<要还原的文件路径（E:\books.txt）

在这里插入图片描述

注意：还原的时候，是把当前备份文件中的所有表还原到其他机子上的一个已有的数据库中去了。

2、第二种方法：工具备份（navicat）

十、增删改

1.-------------------------------增加--------------------------------：

A、单行增加

语法: insert [into] 表名[（列名1，列名2，列名3，……）] values(值1，值2，值3，…….);

注意:

a、当表中的自增列存在的时候，在省略所有列名的时候，自增列必须插入值，但是还要保持自增，用0占位！！！在没有省略所有列名的时候，所有的值和列名必须一一对应。

b、注意列名和列值的对应（个数对应、类型对应、顺序对应）

c、非空列必须保证插入值，为空列可以选择插入值，如果步插入值用null占位，如果字段有默认值，那么直接用default 或者插入的值

B、多行插入值

语法: insert [into] 表名 [（列名1，列名2，列名3，……）]

Select 值1，值2，值3……. Union

Select 值1，值2，值3…….

…….

注意: 多行插入值的时候不能使用default 默认值，值不能重复

C、批量复制数据

语法: Insert into 表名（新列名1，新列名2，、、、）

Select 旧列名1、旧列名2、…… from 旧表名

2.-------------------------------删除---------------------------------

语法: Delete from 表名【 where 条件】

同时删除多条记录：

delete from 表名where 列名 =列值 or 列名=列值 or列名=列值《===》

delete from 表名where 列名 in(值,值,值)

delete from 表名：表示情况表的数据

delete from 表名《====》truncate table 表名

3.---------------------------------修改---------------------------------

语法: update 表名 set 列名=列值【，列名=列值，列名=列值】【where 条件】

– 修改表结构的相关语法：

– 1. 通过修改表的语法添加约束

create table if not exists MainTable

(

cid int not null,

cname varchar(20) not NULL)

alter table表名

add [CONSTRAINT pk_cid] PRIMARY key(cid) 添加主键约束

alter tbale 表名

drop primary key – 删除主键约束

– 添加外键约束

alter table tb

add CONSTRAINT 外键名称 FOREIGN key(tid) REFERENCES

MainTable(cid)

alter table tb

drop foreign key fk_tid – 删除对应的外键

– 添加列(添加字段名的时候也要添加字段属性)

– 默认添加到所有字段的末尾

alter table tb add col3 varchar(30) not null

– 添加到所有字段的首位

alter table tb add col1 int first

– 添加到指定的字段名后面

alter table tb add col2 int after tid

– 删除列名

alter table 表名 drop 字段名

– 修改字段类型

alter table tb modify col1 varchar(20) not null

注意:修改字段属性或者字段类型时，必须写字段类型

– 修改字段名称

alter table tb change 旧字段名新字段名称原字段类型

– 修改表的名称

Alter table 表名 rename 新表名

十一、查询

select * from 表名;

1. 查询是取别名

Select userId 【as】‘学号’ from 表名

2. 条件查询

补充讲解:

Enum、set枚举类型: 在插入值的时候，可以用提供的选项去选择插入，也可以用索引选择插入，索引默认从1开始

3. 排序：

– 正序：asc 一般省略不写

– 倒序: desc

select * from studentinfo ORDER BY stuAge desc , stuid DESC

– 多列排序的时候，整体先按照紧挨 order by后面的先排序，

– 当值出现重复的时候，再按照另外的条件排序

4. 分组查询 group by

select stu_id as “编号”,count(*) as “总人数” from studentinfo group by stu_sex

5. 查询值为null的数据（不等于==》<> ）

select * from studentinfo where stu_age is null

查询值不为null的数据

select * from studentinfo where stu_age is not null

6. 分页查询 limit:

– limit 每页的起始索引,每页的显示条数

– 每页的起始索引=（当前页-1）*每页显示的条数

select * from studentinfo limit 0,5

7. having的用法：（分组？）

– having where

– 写法一: 没有分组的情况下，两者可以相互替换

select * from studentinfo having stu_id=10000

– 写法二:在分组的情况下，两者不能替换，而且只能用having

select *,COUNT(*) from studentinfo group by stu_sex having count(*)>6

8. 统计函数（聚合函数）

– 统计函数（聚合函数）

– 1.count()：总记录数

– 2.avg(): 平均值

– 3.sum():求总和

– 4.max():求最大值

– 5.min():求最小值

select * from score

select count(soc_stuid) as “总人数”,sum(sco_score) as “总分”,max(sco_score)

as “最高分”,min(sco_score) as “最低分”, avg(sco_score) as “平均值”

from score;

9. 模糊查询

– 模糊查询

– 通配符

– %表示任意一个字符

– _表示匹配一个字符

select * from studentinfo where stu_name like ‘%巴%’

select * from ff_infomation where stuname not like ‘张_’

10. 高级查询

10.1：子查询（in）

select \* from studentinfo where stu_id=10000 or stu_id=10001 or stu_id=10002

\<==\>

select \* from studentinfo where stu_id in(10000,10001,10002)

-- 查询考了php试题的及格的学生的详细信息

--先在 成绩表中查询考了php试题并且成绩大于等于60分的学生的学号

select \* from studentinfo where stu_id in(

select soc_stuid from score where soc_couid=1

and sco_score\>=60)

-- 子查询如果返回的结果大于1行，in和=不能替换

-- 当in子查询中不允许返回的结果是多列，但是可以返回多行数据，单只能有一列

-- 查询参加考试的人的学号

select \* from studentinfo where stu_id in(

select soc_stuid from score

)

-- 查询每页参加过考试人的信息

select \* from studentinfo where stu_id not in(

select soc_stuid from score

)

-- 范围查询

select \* from studentinfo where stu_age\>=20 and

stu_age\<=30

-- between A and B: 在A和B之间，包含AB

select \* from studentinfo where stu_age BETWEEN 20 and 30；

10.2. 多表连接查询（内连接、外链接）

-- 多表连接查询：外连接、内连接

– 内连接

两张表连接

-- 在成绩表显示所有学号对于的姓名并且是男生

-- 相当于连接score和studentinfo

-- 写法一:

select soc_socid,studentinfo.stu_name,studentinfo.stu_sex,soc_stuid,soc_couid,sco_score

from score,studentinfo

where score.soc_stuid=studentinfo.stu_id

and stu_sex=1

-- 写法二:

-- INNER JOIN... On（条件）

select soc_socid,studentinfo.stu_name,studentinfo.stu_sex,soc_stuid,soc_couid,sco_score

from score inner join studentinfo

on score.soc_stuid=studentinfo.stu_id where stu_sex=1

注:使用内连接（inner join ... on）的时候，on后面跟条件。

可以用where去替换on，如果有多个条件，on 后面可以跟 where或者and ,如果inner join ... Where 出现多个条件，后边添加条件时，只能用and 不能用on

内连接取交集，不区分表的顺序

三张表连接

-- 在成绩表中显示对应课程号的名称以及学号对应的学生姓名

-- 连接 score+course + studentinfo

-- 写法一:

select soc_socid,studentinfo.stu_name,soc_stuid,soc_couid,sco_score,course.cou_name

from score,studentinfo,course

where score.soc_stuid=studentinfo.stu_id and

score.soc_couid=course.cou_id

-- 写法二:

select soc_socid,studentinfo.stu_name,soc_stuid,soc_couid,sco_score,course.cou_name

from score inner join studentinfo

on score.soc_stuid=studentinfo.stu_id

inner join course

on score.soc_couid=course.cou_id

-- 外连接：左外连接、右外连接

-- 在左外连接查询的结果中:在 left join 左边的表（称为“左表”）中所有的列,

-- 在查询的结果中都会出现。

-- 在 left join 右边的表（称为"右表"),如果右表中的列在左表中没有值。

-- 那么查询的结果中会用null来填充

select stu_id,stu_age,stu_hobby,stu_major,stu_name,stu_sex,soc_socid,soc_stuid,

soc_couid,sco_score

from studentinfo

LEFT JOIN score

on studentinfo.stu_id=score.soc_stuid

-- 在右外连接查询的结果中:在 right join 右边的表（称为"右表"),

-- 在查询的结果中所有的列都会出现。

-- 在 right join 左边的表（称为“左表”），在查询的结果中如果左表中的列在右表中没有值

-- 那么查询的结果中会用null来填充

select stu_id,stu_age,stu_hobby,stu_major,stu_name,stu_sex,soc_socid,soc_stuid,

soc_couid,sco_score

from studentinfo

right JOIN score

on studentinfo.stu_id=score.soc_stuid