一篇文章学完Mysql

在数据库中所有的字符串类型，必须使用单引号，不能使用双引！

零：数据库引擎

*看你的mysql现在已提供什么存储引擎:

>show engines;

*看你的mysql当前默认的存储引擎:

>show variables like '%storage_engine%';

*查看表的引擎类型(MyISAM、InnoDB)

>show table status from 数据库名 where name='表名';

*你要看某个表用了什么引擎(在显示结果里参数engine后面的就表示该表当前用的存储引擎):

>show create table 表名;

*最常使用的2种存储引擎:

1.Myisam是Mysql的默认存储引擎，当create创建新表时，未指定新表的存储引擎时，默认使用Myisam。每个MyISAM在磁盘上存储成三个文件。文件名都和表名相同，扩展名分别是.frm（存储表定义）、.MYD(MYData，存储数据)、.MYI(MYIndex，存储索引)。数据文件和索引文件可以放置在不同的目录，平均分布io，获得更快的速度。

2.InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比Myisam的存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。

一：操作数据库

查看所有数据库：SHOW DATABASES

切换(选择要操作的)数据库：USE 数据库名

创建数据库：CREATE DATABASE [IF NOT EXISTS] 数据库名 [CHARSET=utf8]

删除数据库：DROP DATABASE [IF EXISTS] 数据库名

修改数据库编码：ALTER DATABASE 数据库名 CHARACTER SET utf8

二：数据类型

int：整型

double：浮点型，例如double(5,2)，表示最多5位，必须有2位小数。

decimal：浮点型，在表单钱方面使用该类型，因为不会出现精度缺失问题。

char：固定长度字符串类型；char(255)

varchar：可变长度字符串类型；varchar(65535)

text(clob)：字符串类型；

clob：包括：tinytext，text，mediumtext，longtext

blob：包括：tinyblod，blod，mediumblod，longblod

date：日期类型，格式为：yyyy-MM-dd。

time：时间类型，格式为：hh:mm:ss。

timestamp：时间戳类型。

三：表

*创建表：

CREATE TABLE [IF NOT EXISTS] 表名(

列名列类型，

....

列名列类型

)；

*查看当前数据库中所有的表名称：SHOW TABLES;

*查看指定表的创建语句：SHOW CREATE TABLE 表名;

*查看表结构：DESC 表名;

*删除表：DROP TABLE 表名;

*修改表：前缀：ALTER TABLE 表名

>修改之添加列：

ALTER TABLE 表名 ADD(

列名列类型，

...

列名列类型

)

>修改之修改列类型（如果被修改的列已存在数据，那么新的类型可能会影响到已存在的数据）：ALTER TABLE 表名 MODIFY 列名列类型；

>修改之修改列名：ALTER TABLE 表名 CHANGE 原列名新列名列类型；

>修改之删除列：ALTER TABLE 表名 DROP 列名；

>修改表名称：ALTER TABLE 原表名 RENAME TO 新表名;

四：数据操作

*查询表记录：

SELECT * FROM 表名;

*插入数据：

INSTER INTO 表名(列名1，列名2，...) VALUES (列值1，列值2，...)

或

INSTER INTO 表名 VALUES(列值1，列值2，...);

*修改数据：

UPDATE 表名 SET 列名1=列值1，列名2=列值2，...[WHERE 条件]

条件包括：

>条件必须是一个boolean类型的值或表达式

>运算符：=、!=、<>、>、<、>=、<=、BETWEEN...AND、IN(...)、IS NULL、NOT、OR、AND

between...and举例：update stu set age=20 where age between 10 and 40;

is null举例：select * from stu where age is null;

WHERE age>=18 AND age<=80

WHERE age BETWEEN 18 AND 80

WHERE name IN('zhangsan','lisi')

WHERE age IS NULL，不能用等号

WHERE age IS NOT NULL

*删除数据：

DELETE FROM 表名 [WHERE 条件];

TRUNCATE TABLE 表名：TUUNCATE是DDL语句，它是先删除drop该表，再create该表.而且无法回滚。

五：DCL

1.创建用户

*CREATE USER 用户名@IP地址 IDENTIFIED BY '密码'

>用户只能在指定的IP地址上登陆

*CREATE USER 用户名@'%' IDENTIFIED BY '密码'

>用户可以在任意IP地址上登陆

2.给用户授权

*GRAND 权限1，...，权限n ON 数据库.* TO 用户名@IP地址

>权限、用户、数据库

>给用户分派在指定的数据库上的指定的权限

>例如：GRANT CREATE,ALTER,DROP,INSERT,UPDATE,DELETE,SELECT ON stu.* TO user1@localhost;

*GRANT ALL ON 数据库.* TO 用户名@IP地址

>给用户分派指定的数据库上的所有权限

3.撤销授权

*REVOKE 权限1，...，权限n ON 数据库.* FROM 用户名@IP地址；

>撤消指定用户在指定数据库上的指定权限

>例如：REVOKE CREATE,ALTER,DROP ON stu.* FROM user1@localhost;

4.查看权限

*SHOW GRANTS FOR 用户名@IP地址

>查看指定用户的权限

5.删除用户

*DROP USER 用户名@IP地址

*************************DQL************数据查询语言*********************

一：基本查询

1:查询所有列

SELECT * FROM 表名;

SELECT * FROM emp;

2:查询指定列

SELECT 列1[，列2，...，列N] FROM 表名；

SELECT empno,enname,sal,comm FORM 表名；

3:完全重复的记录只一次

当查询结果中的多行记录一模一样时，只显示一行。一般查询所有列时很少会有这种情况，但只查询一列或多列时，这种可能就大了。

SELECT DISTINCT *|列1[，列2，...，列N] FROM 表名；

SELECT DISTINCT sal FROM emp;

->查询员工表的工资，如果存在相同的工资只显示一次

4:字段名称修改,列加字符串

SELECT 列1，CONCAT(列2+“%”) FROM 表名 WHERE 条件;

5：列运算

*数量类型的列可以做加、减、乘、除运算

SELECT sal*1.5 FROM emp;

SELECT sal+comm FROM emp;

*字符串类型可以做连续运算

SELECT CONCAT('$',sal) FROM emp;

*转换NULL值

有时需要把NULL转换成其它值，例如comm+1000时，如果comm列存在NULL值，那么NULL+1000还是NULL,而我们这时希望把NULL当0来运算。

SELECT IFNULL(comm,0)+1000 FROM emp;

->IFNULL(comm,0) 如果comm中存在NULL值，那么当成0来运算。

*给列起别名

当使用列运算后，查询出的结果集中的列名称很不好看，这时我们可以给列起别名，这样在结果集中列名就显示别名了

SELECT IFNULL(comm,0)+1000 AS 奖金 FORM emp;

->其中AS可以省略

条件查询：

*SELECT empno,ename,sal,comm FROM emp WHERE sal>1000 AND comm IS NOT NULL;

*SELECT empno,ename,sal FROM emp WHERE sal BETWEEN 2000 AND 3000;

*SELECT empno,ename,job FROM emp WHERE job IN('经理','董事长');

6：模糊查询

*SELECT * FROM emp WHERE ename LIKE '张_'；

->模糊查询需要使用运算符：LIKE，其中_匹配一个任意字符，注意，只匹配一个字符而不是多个。

->上面语句查询的是姓张，名字由两个字组成的员工。

*SELECT * FROM emp WHERE ename LIKE '___' ;

->上面查询姓名由3个字组成的员工

SELECT * FROM emp WHERE ename LIKE '张%'；

->如果我们想查询姓张，名字几个字都可以的员工时就要使用“%”了。

->其中%匹配0~N个任意字符，所以上面的语句查询的是姓张的所有员工。

SELECT * FROM emp WHERE ename LIKE '%阿%'

->千万不要认为上面的语句是查询姓名中间带有“阿”字的员工，因为%匹配0~N个字符，所以姓名以阿开头和结尾的员工也都会查询到。

SELECT * FROM emp WHERE ename LIKE '%';

->这个条件等同与不存在，但如果姓名为NULL的查询不出来。

7：排序

SELECT * FROM emp ORDER BY sal ASC;

->上面是升序，默认是升序，ASC可省略

SELECT * FROM emp ORDER BY comm DESC

->按comm降序，DESC不能省略

SELECT * FROM emp ORDER BY sak ASC,comm DESC.

->先按sal升序排，如sal一样，按 comm降序排序。

8：聚合函数

聚合函数用来做某列的纵向运算

1) COUNT

SELECT COUNT(*) FROM mep;

->计算emp表中所有列都不为NULL的记录的行数

SELECT COUNT(comm) FROM emp;

->云计算emp表中comm列不为NULL的记录的行数

2) MAX

SELECT MAX(sal) FROM emp;

->查义最高工资

3） MIN

SELECT MIN(sal) FROM emp;

->查询最低工资

4) SUM

SELECT SUM(sal) FROM emp;

->查询工资合

5） AVG

SELECT AVG(sal) FROM emp;

->查询平均工资

9:分组查询

分组查询是把记录使用某一列进行分组，然后查询组信息

例如：查看所有部门的记录数

SELECT deptno,COUNT(*) FROM emp GROUP BY deptno;

->使用deptno分组，查询部门编号和每个部门的记录数

SELECT job,MAX(sal) FROM emp GROUP BY job

->使用job分组，查询每种工作的最高工资

SELECT deptno,COUNT(*) FROM emp GROUP BY deptno HAVING COUNT(*)>3;

->以部门分组，查询每组记录数。条件为记录数大于3

10：limit子句

LIMIT用来限定查询结果的起始行，以及总行数。

SELECT * FROM emp LIMIT 4,3;

->其中4表示从第5行开始，其中的3表示一共查询3行。即第5，6，7行记录。

各关键字执行顺序：

SELECT -> FROM -> WHERE -> GROUP BY -> HAVING -> ORDER BY

*****************编码设置*****************

1.查看MySQL数据库编码

* SHOW VARIABLES LIKE 'char%'；

->获取char开头的所有变量，这些变量有编辑有关

| character_set_client | utf8mb4 |

| character_set_connection | utf8mb4 |

| character_set_database | utf8 |

| character_set_filesystem | binary |

| character_set_results | utf8mb4 |

| character_set_server | utf8 |

| character_set_system | utf8 |

| character_sets_dir | E:\phpstudy_pro\Extensions\MySQL5.7.26\share\charsets\

*character_set_client:MySQL使用该编码来解读客户端发过来的数据

*character_set_results:MySQL会把数据转换成该编码后，再发送给客户端。

注：在my.ini修改编码才是一劳永逸的办法

[client]

port=3306

default-character-set=utf8 /*修改它一劳永逸，它可以修改三个变量，client、results、connection*/

***********************备份与恢复******************************

1:数据库导出SQL脚本

>mysqldump -u用户名 -p密码数据库名>生成的脚本文件路径

>例如：mysqldump -uroot -proot test>C:/name.sql

>注意：不要打分号，要不登陆mysql，直接在cmd下运行

>注意：生成的脚本文件中不包含create database语句

2：执行SQL脚本

一:

>mysql -u用户名 -p密码数据库<脚本文件路径

>mysql -uroot -proot test<c:\name.sql

二：

登陆mysql，然后>source SQL脚本路径

********************************约束**********************************

约束是添加在列上的，用来约束列的。

一：主键约束（唯一标识)

>非空

>唯一

>被引用

当表中某一列被指定为主键后，该列就不能为空，不能有重复的值出现。

创建表时指定主键的两种方式：

>CREATE TABLE stu{

sid CHAR(6) PRIMARY KEY,

sname VARCHAR(50),

age INT,

gender VARCHAR(50)

}

>CREATE TABLE stu{

sid CHAR(6),

sname VARCHAR(50),

age INT,

gender VARCHAR(50),

PRIMARY KEY(sid)

}

指定sid为主键列，即为sid列添加主键约束

修改表时指定主键，ALTER TABLE stu ADD PRIMARY KEY(sid)

删除主键：ALTER TABLE stu DROP PRIMARY KEY;

二：主键自增长

*因为主键列的特性是：必须唯一，不能为空，所以我们常会指定主键类为整型，然后设置其自动增长，这样可以保证在插入数据时主键列的唯一和非空特性。

*创建表时指定主键自增长：

CREATE TABLE stu{

sid INT PRIMARY KEY AUTO_INCREMENT,

sname VARCHAR(20),

age INT,

gender VARCHAR(20)

}

*修改表时设置主键自增长：ALTER TABLE stu CHANGE sid sid INT AUTO_INCREMENT;

*修改表时删除主键自增长：ALTER TABLE stu CHANGE sid sid INT;

三：非空约束

*因为某些列不能设置为NULL值，所以可以对列添加非空约束

*例如:

CREATE TABLE stu{

sid INT PRIMARY KEY AUTO_INCREMENT,

sname VARCHAR(20) NOT NULL,

age INT,

gender VARCHAR(20)

}

*对sname列设置了非空约束

四：唯一约束

*数据库有些列不能设置为重复的值，所以可以对列添加唯的约束。

*CREATE TABLE s tu{

sid INT PRIMARY KEY AUTO_INCREMETN,

sname VARCHAR(20) NOT NULL UNIQUE,

age INT,

gender VARCHAR(10)

}

*对sname列设置了非空约束

五：概念模型

当我们要完成一个软件系统时，需要把系统中的实体抽出来，形成概念模型。

如部门，员工都是系统中的实体。概念模型中的实体最终会成为JAVA中的类，数据库中表。

实体之间还存在关系，关系统有三种：

>1对多：如每个员工都从属于一个部门，而一个部门可以有多个员工，其中员工是多方，部门是一方；

>1对1：如老公和老婆

>多对多：老师与学生就是多对多，一个老师可以有多个学生，一个学生可以有多个老师。

概念模型在JAVA中成为实体类

类就是使用成员变量来完成关系，一般都是双向关联。

多对一双各中关联系，即员工关系部门，部门也关联员工。

class Employee{ //多方关联一方

...

private Department department;

}

class Deprtment { //一方关联多方

...

private List<Employee> employees;

}

class Husband{

...

private Wife wife;

}

class Wife{

...

private Husband

}

class Student{

...

private List<Teacher> teachers;

}

class Teacher{

...

private List<Student> students;

}

六：外键约束

*外键必须是另一个表的主键

*外键可以重复

*外键可以为空

*一张表中可以有多个外键。

概念模型在数据库中成为表

数据库表中的多对一关系，只需要在多方使用一个独立的列来引用1方的主键即可

创建外键约束：

CREATE TABLE dept(

deptno INT PRIMARY KEY AUTO_INCREMENT,

dname VARCHAR(50)

);

INSERT INTO dept VALUES(10,'研发部')；

INSERT INTO dept VALUES(20,'人力部')；

INSERT INTO dept VALUES(30,'财务部')；

SELECT * FROM dept;

CREATE TABLE emp(

empno INT PRIMARY KEY AUTO_INCREMENT,

ename VARCHAR(50),

dno INT,

CONSTRAINT fk_emp_dno FOREIGN KEY(dno) REFERENCES dept(deptno)

)

INSERT INTO emp(empno,ename) VALUES(NULL,'zhangSan');

***********************多表查询********************

一：分类：

>合并结果集

>连接查询

>子查询

二：合并结果集：

>要求被合并的表中，列的类型和列数相同

>UNION，去除重复行

>UNION ALL，不去除重复行

SELECT * FROM 表名1 UNION ALL SELECT * FROM 表名2;

三：连接查询：

1：分类

*内连接

*外连接

>左外连接

>右外连接

>全外连接(Mysql不支持)

*自然连接(属于一种简化方式)

2：内连接

*方式：SELECT * FROM 表1 别名1，表2 别名2 WHERE 别名1.xx=别名2.xx

*标准：SELECT * FROM 表1 别名1 INNER JOIN 表2 别名2 ON 别名1.xx==别名2.xx

*自然：SELECT * FROM 表1 别名1 NATURAL JOIN 表2 别名2

*内连接查询出的所有记录都满足条件。

3.外连接

*左外：SELECT * FROM 表1 别名1 LEFT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx

>左表记录无论是否满足条件都会查询出来，而右表只有满足条件才能出来，左表中不满足条件的记录，右表部分都为NULL

*左外自然：SELECT * FROM 表1 别名1 NATURAL LEFTT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx

*右外：SELECT * FROM 表1 别名1 RIGHT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx

>右表记录无论是否满足条件都会查询出来，而左表只有满足条件才能出来。右表中不满足条件的记录，左表部分都为NULL

*右外自然：SELECT * FROM 表1 别名1 NATURAL RIGHT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx

*全链接：可以使用UNION来完成连接

4:查询中有查询（查看select关键字的个数）

1：出现的位置

*WHERE 后作为条件存在

*from后作为表存在（多行多列）

2：条件

*（***）单行单列：SELECT * FROM 表1 别名1 WHERE 列1[=、>、<、>=、<=、!=] (SELECT 列 FROM 表2 别名2 WHERE 条件)

*（**）多行单列：SELECT * FROM 表1 别名1 WHERE 列1[IN,ALL,ANY](SELECT 列 FROM 表2 别名2 WHERE 条件)

*（*）单行多列：SELECT * FROM 表1 别名1 WHERE （列1，列2）IN （SELECT 列1，列2 FROM 表2 别名2 WHERE 条件）

*（***）多行多列：SELECT * FROM 表1 别名1，（SELECT...）别名2 WHERE 条件

**********案例**********

表1	表2

1：查出至少有一个员工的部门，显示部门编号、部门名称、部门位置、部门人数。

分析：

列：dept.deptno、dept.deptname、dept.loc、部门人数

表：dept,emp

条件：empt.deptno=dept.deptno

SELECT * FROM dept;

SELECT deptno,count(*) FROM emp GROUP BY deptno

结束：

SELECT dept.*,z1.cnt FROM dept,(SELECT deptno,COUNT(*) cnt FROM emp GROUP BY deptno) z1

WHERE dept.deptno=z1.deptno;

3：列出所有员工的姓名及其直接上级的姓名

分析：

列：员工姓名，上级姓名

表：emp

条件：员工的mgr=上级的empno

结果：

SELECT e.ename,IFNULL(m.ename,'BOOS') 领导

FROM emp e LEFT OUTER JOIN emp m

ON e.mgr=m.empno

4：列出受雇日期早于直接上级的所有员工的编号，姓名，部门名称。

列：e.empno,e.ename,d.dname

表：emp e,emp m,dept d

条件：e.hiredate<m.hiredate

结果：

SELECT e.empno,e.ename,d.deptname

FROM emp e,emp m,dept d

WHERE e.mgr=m.empno AND e.hiredate<m.hiredate AND e.deptno=d.deptno

posted @ 2020-03-03 14:21 寅春树阅读(292) 评论(0) 编辑收藏举报

刷新页面返回顶部

一篇文章学完Mysql

公告