快速掌握mysql，可备用查找相关用法（吐血整理）

浅浅的花香味﹌ 2022-06-10 02:58 70阅读 0赞

**MySQL使用指南**

**1.mysql创建数据库以及相关操作** 1

**2. mysql常用数据类型** 1

**3. mysql数据表操作** 2

**3.1创建表与删除表** 2

**3.2添加主键** 2

**3.3添加外键** 3

**3.4其他表操作** 3

**4. mysql基本操作CRUD** 4

**4.1插入记录** 4

**4.2删除记录** 4

**4.3修改记录** 4

**4.4查询记录** 5

**5. mysql查询语句** 5

**5.1基础查询** 5

**5.2条件查询** 5

**(1)  运算符** 5

**(2)关键字** 5

**5.3模糊查询** 6

关键字 6

通配符 6

**5.4排序** 6

升序 6

降序 6

**5.5聚合函数** 6

**5.6分组查询** 7

**5.7分页查询** 8

**6. mysql多表查询** 8

**6.1 union合并结果集** 8

**6.2普通多表查询** 9

**(1) 直接合并两个表** 9

**(2)给限定条件合并两个表** 9

**6.3连接查询** 10

**(1)内连接inner** 10

**(2) 外连接outer** 10

**(3) 自然连接natural** 10

**7.mysql子查询** 11

**(1)子查询出现的位置** 11

**(2)当子查询出现在where后作为条件时，可以使用any,all两个关键字** 11

**1.mysql创建数据库以及相关操作**

**创建数据库：**

create database 数据库名;

eg. CREATE DATABASE mydb;

**删除数据库：**

drop database 数据库名;

eg. drop database mydb;

**启用数据库：**

use 数据库名;

eg. USE mydb;

**查询当前用户下的所有数据库：**

SHOW DATABASES;

**查询当前连接数据库的信息：**

SHOW CREATE DATABASE  数据库名;

eg. SHOW CREATE DATABASE mydb;

**显示当前所连接的数据库下所有表：**

SHOW TABLES;

2.**mysql常用数据类型**

**int 整型**

**double 浮点型**

double(6,2) 表示 总位数6位 小数点后2位

**varchar 可变长度字符**

varchar(10) 'aa' 占两个字节

数据库中 utf-8编码(出中文外一个字符一个字节,中文占3个字节 )

**char 不可变长度字符串类型**

char(10) 10表示的是字节数 'aa '占十个字节

**日期类**

date 日期形 yyyy-MM-dd 格式

time 事件形 hh:mm:ss

timestamp 时间戳  yyyy-MM-dd hh:mm:ss

**注意时间戳在**[**MySQL**][MySQL]**中会自动的赋值 在insert 语句中给null 就可以**

datetime 日期时间  yyyy-MM-dd hh:mm:ss

3.**mysql数据表操作**

**3.1创建表与删除表**

**创建表**

CREATE TABLE users(

uid INT PRIMARY KEY AUTO\_INCREMENT,

uname VARCHAR(20) NOT NULL,

upass VARCHAR(20) NOT NULL,

age INT);

**删除表**

drop table users;

**3.2添加主键**

**(1) 开头声明主键**

create table users (  
username varchar(50) primary key   
);

**(2)结尾声明主键**

create table users(  
username varchar(50) ,  
userpwd varchar(50),  
primary key (username)   
);

**(3)声明联合主键**  
create table users(  
username varchar(50),  
userpwd varchar(50),  
birth datetime,  
primary key (username, userpwd)   
);

**(4)创建表后在添加主键**

create table users (  
username varchar(50),  
userpwd varchar(50)  
);

alter table users add primary key (username, userpwd);

**3.3添加外键**

**(1) 添加一个外键**

create table users(  
username varchar(50),  
role\_id int ,  
constraint fk\_users\_roles foreign key (role\_id) references roles(roleid)   
);

添加外键时：constraint fk\_users\_roles 可以省略

简写为：foreign key (role\_id) references roles(roleid)   
**(2)添加两个两个外键**  
CREATE TABLE roles\_menus (  
roles\_id INT ,  
menus\_id INT,  
PRIMARY KEY (roles\_id, menus\_id),  
CONSTRAINT fk\_users\_roles FOREIGN KEY (roles\_id) REFERENCES roles(roleid),   
CONSTRAINT fk\_users\_menus FOREIGN KEY (menus\_id) REFERENCES menus(menuid)  
);

**(3)创建表后再添加外键**

create table users(  
username varchar (50) primary key,  
role\_id int  
);

alter table users add constraint fk\_users\_roles foreign key (role\_id) references roles(roleid);

备注：添加外键前，必须先创建被参照表

**3.4其他表操作**

(1)**创建表后添加或删除列**

alter table student add score int;

alter table student drop score;

(2)**修改表某字段类型**

alter table student modify age  text;

(3)**修改字段名称**

alter table stu change age ag  int;

(4)**修改表名**

rename  table student to stu;

(5)**查看表字段信息**

desc student;

4.**mysql基本操作CRUD**

CREATE TABLE student(

sid INT PRIMARY KEY AUTO\_INCREMENT,

sname VARCHAR(30),

sage INT,

gender VARCHAR(10));

**4.1插入记录**

(1)**插入单条记录**

INSERT INTO student(sid,sname,sage,gender) VALUES(8,"金凤",20,"女");

INSERT INTO student VALUES(10,"无名",21,"男");

(2)**插入多条记录**

INSERT INTO student(sid,sname,sage,gender) VALUES

(1,"张三",20,"男"),

(2,"李四",18,"男"),

(3,"王五",22,"男"),

(4,"赵六",21,"男");

或

INSERT INTO student VALUES

(1,"张三",20,"男"),

(2,"李四",18,"男");

(3)**插入指定字段**

INSERT INTO student(sid,sname) VALUES(5,"沈佳1");

INSERT INTO student(sid,sname) VALUES(6,"沈佳2"),(7,"沈佳2");

**4.2删除记录**

DELETE FROM student WHERE sid=1 OR sid=2;

DELETE FROM student;//删除所有数据，表结构还在

TRUNCATE TABLE student2; //删除整个表(包括表结构)，再创建表结构

**4.3修改记录**

update student set age=25;

update student set age=15，name=”哈哈” where id=1 ;

**4.4查询记录**

select  列名1，列名2，…….，列名n from 表名 where  条件 group by 列名 having 条件 order by 列名 limit 偏移量,记录条数；

**说明：**

1.where condition //条件,分组前的过滤条件，其实就是对整个表进行第一次筛选；有一张虚拟表1

2.group by group\_columns //对结果进行分组，对第一次结果虚拟表1进行分组；得到一个虚拟表2

3.having condition //分组后的行条件，对虚拟表2进行条件筛选，得到一个虚拟表3

4.order by sorting\_colunms //对结果排序 对虚拟表3再次进行排序；

5.limit offset\_start,row\_count //结果限定 又一次筛选查询 ，常用来分页

5.**mysql查询语句**

**5.1基础查询**

(1)**查询所有字段**

SELECT \* FROM student;

SELECT sid,sname,sage,gender FROM student;

(2)**查询指定字段**

SELECT sid,sname FROM student;

**使用别名：as可以省略**

SELECT sid  学号,sname 学生姓名 FROM student;

SELECT sid AS  学号,sname AS 学生姓名 FROM student;

**5.2条件查询**

(1)**运算符**

=、!=、<>(不等于)、<、<=、>、>=；

**(2)关键字**

BETWEEN…AND；NOT BETWEEN…AND

SELECT \* FROM student WHERE sage BETWEEN 20 AND 22; \[20,22\]中所有记录

SELECT \* FROM student WHERE sage NOT BETWEEN 20 AND 22; \[20,22\]以外所有记录

IN(set)；NOT IN(set)

SELECT \* FROM student WHERE sage IN(20,23,222); 取交集

等价于SELECT \* FROM student WHERE sage=20 OR sage=23 OR sage=222;

IS NULL； IS NOT NULL

SELECT \* FROM student WHERE gender IS NULL;

取出gender为null(空)的字段\--这里null为：未初始化，未赋值

区别：SELECT \* FROM student WHERE gender=NULL;

取出gender值为null的记录，已初始化只是赋值为null

SELECT \* FROM student WHERE gender IS NOT NULL;

AND；OR；NOT

SELECT \* FROM student WHERE NOT sname='张三';

**5.3模糊查询**

关键字

like 模式匹配

not like 去模式匹配相反的结果

通配符

\_ 任意一个字符 相当于占位符，一个 \_ 占一位，该位的内容可以任意

% 任意0~n个字符 相当于Java中\*，表示任意字符且长短也任意

SELECT \* FROM student WHERE sage LIKE '\_3'; 取出年龄为2位数，且结尾是3的记录

SELECT \* FROM student WHERE sage LIKE '%3'; 取出年龄的位数任意，结尾是3的记录

SELECT \* FROM student WHERE sname LIKE '%张%'; 取出名字中含有”张”的记录

SELECT \* FROM student WHERE sname NOT LIKE '%张%';

备注：无论字段是何类型，模式匹配时，匹配内容必须放在引号中。

错误语句：SELECT \* FROM student WHERE sage LIKE \_3;

原因：以为sage是整数，没将匹配内容放在引号中

**5.4排序**

升序

order by 列名 asc(默认) 按主键升序排列

降序

order by 列名 desc

SELECT \* FROM student ORDER BY sage DESC; 按sage降序排列

SELECT \* FROM student ORDER BY sage ASC; 按sage升序排列

SELECT \* FROM student WHERE sage>20 ORDER BY sage DESC; 先筛选再排序

(3)字段控制

distinct：去除重复

SELECT DISTINCT \* FROM student; 去除每个字段都一样的记录 \[取出所有不重复的记录\]

SELECT DISTINCT sname FROM student; 去除名字相同的记录 \[取出所有不重复的名字\]

SELECT DISTINCT sname,sage FROM student; 去除名字和年龄均相同的记录

备注：distinct只能放在select和from之间，修饰字段

distinct前不能有其他字段  eg. SELECT sid,DISTINCT sname,sage FROM student;

**5.5聚合函数**

特点：聚合函数是用来做纵向运算的函数：

COUNT()：统计指定列不为NULL的记录行数；

MAX()：计算指定列的最大值；

MIN()：计算指定列的最小值；

SUM()：计算指定列的数值和(非数值计算结果为0)；

AVG()：计算指定列的平均值(非数值计算结果为0)；

SELECT COUNT(sid) 学生人数,MAX(sage) 学生最大年龄,MIN(sage) 学生最小年龄,AVG(sage) 学生平均年龄 FROM student;

备注：

a.数值类型和非数值类型无法做加法运算

b.数值类型和空数值做加法运算：把NULL转换成数值0的函数IFNULL，例如IFNULL(列名,0)

c.直接拿某个字段值与null进行运算，结果均为null

d.聚合函数sum,max,min,avg等等，内部对null值做了处理，结果不会直接是null

数值和非数值运算

进行加减乘除运算时，非数值默认为0(非数值必须是初始化的，即已赋值的)

SELECT sname,sage,sname+sage FROM student;

SELECT sname,sage,sname\*sage FROM student;

NULL处理，IFNULL(列名,0)

条件：sid=10学生年龄为初始化，为null

SELECT sid,sage,sid+sage FROM student WHERE sid=10; 结果为null

SELECT sid,sage,sid+IFNULL(sage,0) FROM student WHERE sid=10; 结果为sid的值10

**5.6分组查询**

group by 字段 \[having 条件\]

根据性别进行分组，并计算每组中的人数

SELECT gender,COUNT(\*) FROM student GROUP BY gender;

根据性别进行分组，再取出性别为女的组，并计算该组人数

SELECT gender,COUNT(\*) FROM student GROUP BY gender HAVING gender='女';

同上(备注：某些记录的gender字段没有初始化，所以为null，即按性别分有3组)

SELECT gender,COUNT(\*) FROM student GROUP BY gender HAVING gender IN ('女','男');

having与where的区别:

1.having是在分组后对数据进行过滤，后面可以直接使用聚合函数

2.having紧跟group by，它们是一个整体

3.where是在分组前对数据进行过滤，不可以直接使用聚合函数

having和where + 聚合函数分析

a.having+聚合函数

SELECT gender,COUNT(\*),SUM(sage) FROM student GROUP BY gender HAVING SUM(sage)>43;

SELECT gender,COUNT(\*),AVG(sage) FROM student GROUP BY gender HAVING AVG(sage)>22;

默认根据分组后的结果，计算每组的SUM，再与43比较

默认根据分组后的结果，计算每组的AVG，再与22比较

b.where直接加聚合函数

SELECT \* FROM student WHERE sage>AVG(sage);

报错：因为mysql引擎不知道计算什么条件下的AVG，改为如下语句则正常执行

SELECT \*,AVG(sage) FROM student WHERE sage>(SELECT AVG(sage) FROM student);

**5.7分页查询**

select \* from 表名 limit 参数1,参数2

参数1：offset，偏移量 可省略，默认offset=0

参数2：查询返回的最大记录数 不可省略

偏移量理解：offset=n 表示前面n条数据不用管，从n+1条开始查询

若总记录数小于5，则有几条就返回几条；否则返回5条

SELECT \* FROM student LIMIT 5; 默认偏移量为0，则从第一条数据开始，获得5条记录

SELECT \* FROM student LIMIT 0,5;  效果同上

若去除前7条记录后，剩下总记录数小于5，则有几条就返回几条；否则返回5条

SELECT \* FROM student LIMIT 7,5;  偏移量为7，则从第8条数据开始，获取5条记录

6.**mysql多表查询**

**为什么要拆表**：去除冗余数据（注意区别于分表）

**表与表之间的关系**：一对一，一对多(多对一)，多对多

**6.1 union合并结果集**

UNION:去除重复数据。例如  select \* from t1 UNION select \* from t2

UNION ALL:不去除重复纪录，全部保留。例如 select \* from t1 UNION ALL select \* from t2

重复：指记录的每个字段完全一样，若主键一样，其他字段不一样，是不会去除的

注意：union合并的两个表或报表的列数，列类型必须相同，列名可以不同

CREATE TABLE t1(

tid INT PRIMARY KEY,

tname VARCHAR(20));

CREATE TABLE t2(

tid INT PRIMARY KEY,

tn VARCHAR(20));

报表与基本表合并

SELECT \* FROM t1 UNION SELECT sid,sname FROM student;

两个完全相同表合并

select \* from t1 UNION select \* from t2

union合并：相当于是将分表合并在一起，这两个表的结构完全一样，只是列名可能有差异。

union是将表t2一条条记录insert进t1表，不会再左侧或右侧产生新的字段。

**6.2普通多表查询**

普通多表查询相当于下面的内连接，

区别：内连接限定条件的关键词是on，普通多表查询使用where

创员工表emp和部门表dept

CREATE TABLE emp(

empno INT PRIMARY KEY,

ename VARCHAR(20),

job VARCHAR(20),

sal DOUBLE(6,2),

deptno INT);

CREATE TABLE dept(

deptno INT PRIMARY KEY,

dname VARCHAR(20),

loc VARCHAR(20));

(1)**直接合并两个表**

因为没有给限定条件，所以会产生笛卡尔积

公式：合并后的总记录数=emp表记录数\*dept表记录数

SELECT \* FROM emp,dept; 等价于内连接：SELECT \* FROM emp JOIN dept;

SELECT e.empno,e.ename,e.job,e.sal,d.dname,d.loc FROM emp e,dept d;

**(2)给限定条件合并两个表**

SELECT \* FROM emp e,dept d WHERE e.deptno=d.deptno;

等价于内连接：SELECT \* FROM emp e JOIN dept d ON e.deptno=d.deptno;

**6.3连接查询**

**(1)内连接inner**

语法：select 列名 from  表1 inner join 表2 on 条件

inner可以省略，只有join关键字，mysql默认是内连接

可以没有on条件

SELECT \* FROM emp INNER JOIN dept;

SELECT \* FROM emp JOIN dept;

SELECT \* FROM emp e JOIN dept d ON e.deptno=d.deptno;

(2)**外连接outer**

语法：select 列名 from  表1 left/right outer join 表2 on 条件

outer可省略

必须有on条件

分为左连接和右连接，两者恰好相反

a.左连接left

左连接是先查询出左表，然后查询右表，右表中满足条件的显示出来，不满足的显示NULL

(先查询t1表，再根据后面条件查询t2表中满足条件的数据)

SELECT \* FROM emp e LEFT JOIN dept d ON e.deptno=d.deptno;(outer可以省略)

b.右连接right

右连接就是先把右表中所有记录都查询出来，然后左表满足条件的显示，不满足显示NULL

SELECT \* FROM emp e RIGHT JOIN dept d ON e.deptno=d.deptno;

(3)**自然连接natural**

自动去除无用重复信息

语法：select 列 from  表1 natural join 表2;

内连接和自然连接比较

a.内连接 SELECT \* FROM emp INNER JOIN dept;

b.自然连接 SELECT \* FROM emp NATURAL JOIN dept;

自然连接：1.默认根据两个表中的相同字段去除重复，emp和dept中都有deptno，

则根据e.deptno=d.deptno来去除重复

2.若两个表没有相同字段，则会直接合并产生笛卡尔积

natural可以修饰外连接，此时可以不需要on条件，因为若有相同字段，natural默认会根据该字段进行限定。若是没有相同字段，不会像使用外连接时，不加on时(SELECT \* FROM emp LEFT JOIN dept;)，直接报错，而是产生笛卡尔积。【或者说left和不存才是一样的，因为只有natural时，也是同样的效果，并且有left时，也不能加on来限制】

SELECT \* FROM emp NATURAL LEFT JOIN dept;

**7.mysql子查询**

定义：子查询就是嵌套查询，即select中包含select,如果一条语句中存在两个或者两个以上select,那么就是子查询了

**(1)子查询出现的位置**

where后，作为条件查询的中条件的一部分

from后，作为表使用

**(2)当子查询出现在where后作为条件时，可以使用any,all两个关键字**

SELECT \* FROM emp WHERE deptno IN(SELECT deptno FROM dept WHERE dname='销售部');

等价SELECT \* FROM emp WHERE deptno=(SELECT deptno FROM dept WHERE dname='销售部');

[MySQL]: http://lib.csdn.net/base/mysql