55、MySOL数据库

Love The Way You Lie 2024-03-30 10:07 194阅读 0赞

目录

一、MySQL安装和配置

二、数据库

三、表

四、数据库的C [create] R [read] U [update] D [delete] 语句

1、insert语句

2、update语句

3、delete语句

4、select语句

五、Mysql常用数据类型（列类型）：编辑

六、函数

*合计 / 统计函数

*数学相关函数

*时间日期相关的函数

*加密和系统函数

*流程控制函数

七、mysql表查询—加强版

八、mysql表外连接

九、mysql约束

1、primary key

2、not null

3、unique

4、foreign key

5、check

7、自增长

十、mysql索引

十一、mysql事务

十二、视图（view）

十三、MySQL用户管理

0d8df30760924e68a0382a7035ef5a77.jpeg

一、MySQL安装和配置

1、引入

e908e38ff95e4d54ba97068ac5596956.png

b54207cbd8c740b59fd23933d4e607c6.png

2、B站《韩顺平 零基础30天学会java》第731集，安装MySQL的详细步骤

3、使用命令行窗口连接MySQL数据库

9616e2874c0c4dacbdac33685bc8ba87.png

01261b45b32943969f86831bae74f828.png

（1）

mysql -h 主机名IP -P 端口 -u 用户名 -p密码

注意：-p密码，中间不要有空格

mysql -h 127.0.0.1 -P 3306 -u root -pliufu668

（2）登录前，保证服务启动

c5a4ca572791442cb673e8a937799b97.png

（3）如果如果没有写-h 主机名IP,默认就是本机

（4）如果没有写-P 端口,默认就是3306 (在实际工作中,3306一般修改)

4、下载安装破解版navicat和SQLyog

安装包：

http://t.csdn.cn/C9TYN

安装视频：

Navicat 15 安装与破解_哔哩哔哩_bilibili

2e3604f8b34646bba5e462bd8d3558a0.png

使用示范：

（1）使用命令行cmd：

f3130d8efb2049339a45d15090316126.png

5c2bdc69c8fd4f839ac465bab4035ffa.png

（2）使用navicat:

cc23e05bdff849ea8cfd44cd3170ce7b.png

5、SQLyog的下载与安装

http://t.csdn.cn/1XSOi

二、数据库

核心：用一个系统管理多个仓库，仓库里有一叠填写了货物信息的表

1 、数据库三层结构：

（1）所谓安装MySQL数据库，就是在主机安装一个数据库管理系统（DBMS），这个管理程序可以管理多个数据库，DBMS（database manage system）

（2）一个数据库中可以创建多个表，以保存数据（信息）

（3）数据库管理系统（DBMS）、数据库和表的关系如下： e232886a9b4f409abb9f5a6f6cfd3522.png8998981cf71142d2955acfd1996a8d43.png

2、数据在数据库中的存储方式：

0f21c1ba7ee54a699567d582d2b20fdb.png 6387234bcdda46b3b56a4d7fe7352e2d.png

3、SQL语句分类：

（1）DDL：数据定义语句[ create 表，库。。。]

（2）DML：数据操作语句[ 增加 insert，修改 updata，删除delete]

（3）DQL：数据查询语句[ select ]

（4）DCL：数据控制语句[ 管理数据库 ，比如用户权限 grant revoke]

4、cccf81f06f904ac4b92aaa492ea4c741.png

五、用SQLyog操作数据库

1、创建数据库

语法：

CREATE DATABASE [IF NOT EXISTS] db_name

[create_specification ，create_specification…]

说明：

create_specification:

[DEFAULT] CHARACTER SET charset_name ：

指数据库采用的字符集，如果不指定字符集，默认utf8

[DEFAULT] COLLATE collation_name：

指定数据库字符集的校对规则（常用的utf8_bin，utf8_general_ci（默认））

（1）hsp_db02

8656d405ada74508b4035b719b485c9c.png

（2）hsp_db03

f312c6f425d844b7ae51f01f0d2cc27f.png

2、查看、删除数据库

语法：

#显示数据库：

SHOW DATABASES

#显示数据库创建语句：

SHOW CREATE DATABASE db_name

#删除数据库语句：

DROP DATABASE [IF EXISTS] db_name

7cc8ba05ce3542679ca8472286376742.png

3、备份恢复数据库

（1）语法：

38382b36f7804a90ac617b752a97377e.png

b135ceb8e19d4acdbe68fe614d7819f4.png

（2）练习：

34326bd7a1a048b69bbf84667cd5ded2.png

1d4d33c3a9f147c18f4ae3ebabc39f79.png

4、综合练习：

e78c9c7a759f4ad080dcb7b61105531e.png

* 安装ecshop数据库：

方法一：

(1) 把ecshop数据库的数据备份到“新查询编辑器”中

(2)全选，执行

f1c5bf44157b4c48af5863877dbfffb0.png

方法二：

(1) 把ecshop数据库的数据备份到目录d:\\ecshop.sql

(2)在命令行中输入5b044f7d678348ac83ee09aab4e9e288.png，回车

* 备份ecshop数据库

在dos下输入：mysqldump -u root -p -B ecshop > d:\\ecshop.sql

ad5f9a5072f34d75993c16850cce7db7.png

三、表

1、创建表

（1）基本介绍：

语法：

CREATE TABLE table_name(

field1 datatype,

field2 datatype,

field3 datatype)CHARACTER SET COLLATE ENGINE

说明：

character set 字符集

collate 如不指定则为所在数据库校对规则

engine引擎

field 指定列名

datatype 指定列类型（字段类型）

character set 如不指定则为所在数据库字符集

注意：创建表时，要根据需保存的数据创建相应的列，并根据数据的类型定义相应的列类型

（2）练习：

建表users:

5722d485073a49d9add3d6e1fac85401.png

（1）方式一：新建表21b39b885cbe4a24ac4d827580cddadb.png 50fb7056c46d4a8e958d0b4482c78ceb.png

（2）方式二：sql语句

96c84ae4d9304dc9ad2f0ead4bb7b7b6.png

2、修改表、删除表

（1）基本介绍：

法一：指令语法：

#增加列

ALTER TABLE tablename

ADD (column datatype [DEFAULT expr]

[,column datatype]…);

#修改列

ALTER TABLE tablename

MODIFY (column datatype [DEFAULT expr]

[,column datatype]…);

删除列

ALTER TABLE tablename

DROP (column);

查看表的结构：desc 表名;

修改表名：Rename table 表名 to 新表名;

修改表字符集：alter table 表名 character set 字符集;

法二：界面修改

8783b3474bef45f88137a5da447f7391.png

（2）应用实例：

f98a6226fb3a4aa1bb7e5a7dc935254e.png

我的答案：

ec0c97362fc3449990c6cfe84621a806.png17f0e53a180043598c0cb9037fb36d10.png

老师的答案：

41f58374c2764b2fbdd195c156fddaa0.png

a2492202ed854f5fb5d2c65e2c3cd410.png

四、数据库的C [create] R [read] U [update] D [delete] 语句

1、insert语句

（1）使用INSERT语句向表中插入数据

INSERT INTO table_name [(column [, colum…])]

VALUES (value [, value…]);

（2）案例

1be2f6473bec4a78a10bb07fdc9be5f8.png

ff1a88ffcbca4e99909c9f3d4d5dd3eb.png

-—————————————————————————————————————

d2194cfbfb5341a28b2554569634dda8.png

（3）细节说明：

1）插入的数据应与字段的数据类型相同，比如把’abc’添加到int类型会报错

2）数据的长度应在列的规定范围内，例如：不能将一个长度为80的字符串加入到长度为40的列中

3）在values中列出的数据位置必须与被加入的列的排列位置相对应

4）字符和日期数据应包含在单引号中

5）列可以插入空值[前提是该字段允许为空]，insert into table value(null)

（后来接有NOT NULL说明不能为空）

babf26116370419392d764a8f03d4a25.png

6）insert into tab_name（列名……）values(),(),()形式添加多条记录

0376a4afa92e4fef8af4b4d37db63605.png

7）如果是给表中的所有字段添加数据，可以不写前面的名称

f9648183df964041a36133ea1c2d4791.png

8）默认值的使用，当不给某个字段值时，如果有默认值就会添加，否则报错

31801b9b698b4e02a039599dae291cc9.png

2、update语句

（1）基本介绍：

UPDATE tb1_name

SET col_name1=expr1[, col_name2=expr2…]

[WHERE where_definition]

（2）案例：

285078c547124b75b4aca193ad04b469.png

bc20b2aec0534027819d23d2b4027444.png

（3）使用细节：

1）UPDATE语法可以用新值更新原有表行中的各列

2）SET子句指示要修改哪些列和要给予哪些值

3）WHERE子句指定应更新哪些行，若没有WHERE子句，则更新所有的行（慎用）

4）如果需要修改多个字段，可以通过set字段1=值1，字段2=值2……

3、delete语句

（1）基本介绍

DELETE FROM tb1_name

[WHERE where_definition]

（2）案例

234eb5eb79c744358cf90c8d2e3a5acc.png

199231fae66549d9833bdae9edaa7041.png

（3）使用细节：

1）如果不使用where子句，将删除表中所有数据

2）DELETE语句不能删除某一列的值（可使用UPDATE设为null或者‘ ’）

3）使用DELETE语句仅删除记录，不删除表本身，如要删除表，使用DROP TABLE语句，

DROP TABLE 表名；

4、select语句

（1）基本语法：

SELECT [DISTINCT] *| （column1,column2,column3…)

FROM tablename;

（2）注意事项：

1）Select指定查询哪些列的数据

2）column指定列名

3）* 号代表查询所有列

4）From指定查询哪张表

5）DISTINCT可选，指显示结果时，是否去掉重复数据

7167dfe78db04d35806c1c32de3cb7d2.png

6fe51483453149cb9c14ea8f3533fb52.png

（3）细节：

#使用表达式对查询的列进行运算

SELECT *| (column1 | expression, column2 | expression…)

FROM tablename;

#使用as语句

SELECT column_name as 别名 from 表名；

b826f07598974a42bb031edd0ba32fdb.png

3f9ebe3878104d20b11dfabf8d81ab1f.png

（4）在where子句中经常使用的运算符






































比较运算符 >        <        <=        >=        =       !=或<>都是不等于
BETWEEN…AND… 显示在某一区间的值 
IN(set) 显示在in列表中的值，如in(100,200)

LIKE ‘张pattern’

NOT LIKE ‘’

模糊查询

模糊查询
IS NULL 判断是否为空
逻辑运算符 and 多个条件同时成立（逻辑与）
or 多个条件任一成立（逻辑或）
not 不成立，如where not (salary>100);（取反）

57d459d453e8480082878f657b77d977.png

8d8345c119734b5f8a5d1e19b80fef31.png

81ad0edc57da47b1bac4f7d6c0ff63ec.png

-——————————————————————-

b07194db38074b3b8d34a5a624a84606.png

4788dd294d024ee784a27e7fb97f4670.png

（5）使用order by 子句排序查询结果

SELECT column1, column2, column3…

FROM table

order by column asc…;

或

SELECT column1, column2, column3…

FROM table

order by column desc…;

*注意：

1）Order by指定排序的列，排序的列既可以是表中的列名，也可以是select语句后指定的列名

2）Asc升序（默认），Desc降序

3）ORDER BY子句应位于SELECT语句的结尾

0bdef5db2c15437ca6a5255a91336a24.png

97b2ef7cf3934d18a990abad250baf5c.png

（6）group by和having子句

#使用group by子句对列进行分组

SELECT column1, column2,column3…FROM table

GROUP BY column

#使用having子句对分组后的结果进行过滤

SELECT column1, column2, column3…

FROM table

GROUP BY column having…

*注意

1）group by 用于对查询的结果分组统计，having子句用于限制分组显示结果

2）4ec6aeeee79943bf8a46a25d316d8f65.png

  1. #部门表
  2. CREATE TABLE dept(
  3.     deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
  4.     dname VARCHAR(20) NOT NULL DEFAULT "",
  5.     loc VARCHAR(13) NOT NULL DEFAULT "");
  6. #添加部门信息    
  7. INSERT INTO dept VALUES(10,'ACCOUNTING','NEW YORK'),#财务部accounting，纽约
  8.     (20,'RESEARCH','DALLAS'),#研发部research，达拉斯
  9.     (30,'SALES','CHICACO'),#销售部sales，芝加哥
  10.     (40,'OPERATIONS','BOSTON');#业务部operations，波士顿
  12. SELECT * FROM dept;
  14. #员工表
  15. CREATE TABLE emp(
  16.     empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,#编号
  17.     ename VARCHAR(20) NOT NULL DEFAULT "",#名字
  18.     job VARCHAR(9) NOT NULL DEFAULT "",#工作
  19.     mgr MEDIUMINT UNSIGNED,#上级编号
  20.     hiredate DATE NOT NULL,#入职时间
  21.     sal DECIMAL(7,2) NOT NULL,#薪水
  22.     comm DECIMAL(7,2),#红利
  23.     deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0);#部门编号
  25. #添加员工信息    
  26. INSERT INTO emp VALUES 
  27.     (7369,'SMITH','CLERK',7902,'1990-12-17',800.00,NULL,20),
  28.     (7499,'ALLEN','SALESMAN',7698,'1991-2-20',1600.00,300.00,30),
  29.     (7521,'WARD','SALESMAN',7698,'1991-2-22',1250.00,500.00,30),
  30.     (7566,'JONES','MANAGER',7839,'1991-4-2',2975.00,NULL,20),
  31.     (7654,'MARTIN','SALESMAN',7698,'1991-9-28',1250.00,1400.00,30),
  32.     (7698,'BLAKE','MANAGER',7839,'1991-5-11',2850.00,NULL,30),
  33.     (7782,'CLARK','MANAGER',7839,'1991-6-9',2450.00,NULL,10),
  34.     (7788,'SCOTT','ANALYST',7566,'1997-4-19',3000.00,NULL,20),
  35.     (7839,'KING','PRESIDENT',NULL,'1991-11-17',5000.00,NULL,10),
  36.     (7844,'TURNER','SALESMAN',7698,'1991-9-8',1500.00,NULL,30),
  37.     (7900,'JAMES','CLERK',7698,'1991-12-3',950.00,NULL,30),
  38.     (7902,'FORD',' ANALYST',7566,'1991-12-3',3000.00,NULL,20),
  39.     (7934,'MILLER','CLERK',7782,'1992-1-23',1300.00,NULL,10);
  41. SELECT * FROM emp;
  43. #工资级别表
  44. CREATE TABLE salgrade(
  45.     grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,#工资级别
  46.     losal DECIMAL(17,2) NOT NULL,#该级别的最低工资
  47.     hisal DECIMAL(17,2) NOT NULL);#该级别的最高工资
  49. #添加工资信息
  50. INSERT INTO salgrade VALUES(1,700,1200);
  51. INSERT INTO salgrade VALUES(2,1201,1400);
  52. INSERT INTO salgrade VALUES(3,1401,2000);
  53. INSERT INTO salgrade VALUES(4,2001,3000);
  54. INSERT INTO salgrade VALUES(5,3001,9999);
  56. SELECT * FROM salgrade;
  58. #显示每个部门的平均工资和最高工资
  59. SELECT AVG(sal),MAX(sal),deptno
  60.     FROM emp GROUP BY deptno;#按照部门来分组查询
  62. #显示每个部门的每种岗位的平均工资和最低工资
  63. SELECT AVG(sal),MAX(sal),deptno,job
  64.     FROM emp GROUP BY deptno,job;#先按部门分，再按岗位分组查询
  66. #显示平均工资低于2000的部门号和它的平均工资
  67. #1、显示各个部门的平均工资和部门号
  68. #2、在1的结果基础上，进行过滤，保留AVG(sal) < 2000
  69. SELECT AVG(sal),deptno
  70.     FROM emp GROUP BY deptno
  71.         HAVING AVG(sal) < 2000;
  72. #3、使用别名过滤
  73. SELECT AVG(sal) AS avg_sal,deptno
  74.     FROM emp GROUP BY deptno
  75.         HAVING avg_sal < 2000;#此处可使用别名，避免了重复计算工作

" class="reference-link">五、Mysql常用数据类型（列类型）：74ba6f6797c24ae2bec8e66b87332a84.png

47c1dd40e63849fa847a2e298e85855e.png

*mysql5.1参考手册下载：http://t.csdn.cn/rgSPl

1、数值型（整数）的基本使用：

（1）说明：

使用规范：在能够满足需求的情况下，尽量选择占用空间小的类型（保小不保大）030fedc3499b4b2d9d60237955a6de26.png

（2）应用实例：

*如何定义一个无符号的整数：

create table t10 (id tinyint); //默认是有符号的

create table t11 (id tinyint unsigned); //无符号的

d61312e6e70d43978560344131729483.png41895446ed8149af9e2baa3521eeee95.png

2、数值型（bit）的使用

（1）基本使用：

mysql > create table t02(num bit(8));

mysql > insert into t02(1, 3);

mysql > insert into t02 values(2, 65);

（2）细节说明：

*bit字段显示时，按照位的方式显示

*查询时仍然可以用添加的数值

*如果一个值只有0，1可以考虑使用bit(1)，可以节约空间

*位类型，M指定位数，默认值1，范围1~64

*使用不多

（3）案例演示：

0977d4b3cd32497babbcd770aaf6dffe.png

3、数值型（小数）的基本使用：

（1）FLOAT/DOUBLE [UNSIGNED]

（2）DECIMAL[M，D] [UNSIGNED]

*可以支持更加精确的小数位，M是小数位数（精度）的总数，D是小数点（标度）后面的位数

*如果D是0，则值没有小数点或分数部分，M最大65，D最大是30，如果D被省略，默认是0，如果M被省略，默认是10

*建议：如果希望小数的精度高，推荐使用decimal

（3）案例演示：

aa3d82d1e7e247668ed59939ac33957b.png

a8624112919044d09e836fd434fec15b.png

4、字符串的基本使用：

（1）CHAR(size)：

固定长度字符串，最大 255 字符

（2）VARCHAR(size) ：

可变长度字符串，0~65535 字节 [utf8编码最大21844字符，1~3个字节用于记录大小]

（3）案例演示：

7a43d7b9fd114e3180033864b0d4e36c.png

（4）使用细节：

1）char(4)

*这个4表示字符数（最大255），不是字节数，不管是中文还是字母都是放4个，按字符计算

2）varchar(4)

*这个4表示字符数，不管是字母还是中文都以定义好的表的编码来存放数据

*不管是中文还是英文字母，都是最多存放4个，是按照字符来存放的

09889b96037242529040cce6b7710e4b.png

3）char(4) 是定长，即，即使你插入‘aa’，也会占用分配的4个字符的空间

varchar(4)是变长，即，如果你插入了‘aa’，实际占用的空间大小并不是4个字符，而是按照实际占用空间来分配

注：varchar本身还需占用1~3个字节来记录存放内容长度（备注）

4）什么时候使用char，什么时候使用varchar

* 如果数据是定长，推荐使用char，比如md5的密码、邮编、手机号、身份证号码等，char(32)

* 如果一个字段的长度是不确定，我们使用varchar，比如留言、文章

查询速度：char > varchar

（5）在存放文本时，也可使用Text数据类型，可以将Text列视为VARCHAR列，注意Text不能有默认值，大小0~2^16字节。如果希望存放更多字符，可以选择（中等大小）MEDIUMTEXT0~2^24 或者LONGTEXT 0~2^32。如果想简单点，可直接使用TEXT

26af9bac52b7434fb9d42d71c904b1bb.png

5、日期类型的基本使用：

（1）基本使用：

CREATE TABLE birthday(t1 DATE, t2 DATETIME, t3 TIMESTAMP

NOT NULL DEFAULT CURRENT_TIMESTAMP

ON UPDATE CURRENT_TIMESTAMP);

mysql > INSERT INTO birthday(t1, t2)

VALUES(‘2022-11-11’, ‘2022-11-11 10:10:10’);

（2）细节说明：

TimeStamp在Insert和update时，自动更新

2e60565ea106451581f2e908e8a3a878.png

6、练习题

09e198abf5c14aaca5c940955b5c486f.png

//我的答案：（运行失败- _ -）

1ff3eaad176b41f98149e9078eca46b4.png

//老师的答案：

8a4cb9345a60404e9d5bb58a0ea169e4.png

//简化版：

6bf424a39de148d09c5872278ffb5c3c.png

1dae3aa4b8044b2491b8d00193a4861a.png

六、函数

*合计 / 统计函数

1、count 返回行的总数

SELECT COUNT(*) FROM tablename

[WHERE where_difinition]

或

SELECT COUNT (列名) FROM tablename

[WHERE where_difinition]

-————————————————————————

fd6b2aad94924f5090b21307d070ac98.png

696faeeb631f44c8bf7e8d001cc985d3.png

2、Sum函数返回满足where条件的行的和

SELECT SUM(列名) (, SUM(列名)…) FROM tablename

[WHERE where_definition]

93f8103a56fe4b7197c4bc1892e93bef.png

74b65f5038ff4f45b0da389d2d6d2a95.png

3、AVG函数返回满足where条件的一列的平均值

SELECT AVG（列名）（，AVG(列名)…) FROM tablename

[WHERE where_definition]

09760d58ba0d4e3888c16c1d5eeb0977.png

71ae2ebdd2574814bbd6779f164c6cb0.png

4、MAX / MIN函数返回满足where条件的一列的最大 / 最小值

SELECT MAX（列名）FROM tablename

[WHERE where_definition]

04471c7a04a74217ac7ba1599fe88e56.png

5510f6120c1e4a579be5053c01f306b1.png

5、字符串相关函数

686022a94cf740c2940df8a373e7d2e6.png

  1. #CHARSET(str)返回字串字符集
  2. SELECT CHARSET(ename) FROM emp;
  4. #concat(string2 [,...])连接字串，将多个列拼接成一列
  5. SELECT CONCAT(ename,' 工作是 ',job) FROM emp;
  7. #INSTR(string,substring)返回substring在string中出现的位置，没有返回0
  8. SELECT INSTR('hanshunping','ping') FROM DUAL;
  10. #UCASE(string2)转换成大写
  11. SELECT UCASE(ename) FROM emp;
  13. #LCASE(string2)转换成小写
  14. SELECT LCASE(ename) FROM emp;
  16. #LEFT(string2,length)从string2中的左边起取length个字符
  17. SELECT LEFT(ename,2) FROM emp;
  19. #RIGHT(string2,length)从string2中的右边起取length个字符
  20. SELECT RIGHT(ename,2) FROM emp;
  22. #LENGTH(string) string长度[按照字节]
  23. SELECT LENGTH(ename) FROM emp;
  25. #REPLACE(str,search_str,replace_str)
  26. #在str中用replace_str替换search_str
  27. SELECT ename,REPLACE(job,'MANAGER','经理') FROM emp;
  29. #STRCMP(string1,string2)逐字符比较两字串大小
  30. SELECT STRCMP('hsp','hsp') FROM DUAL;
  32. #SUBSTRING(str,position,length)
  33. #从str的position开始[从1开始计算]，取length个字符
  34. #从ename列的第一位置开始取出2个字符
  36. #SUBSTRING(str,position)
  37. #从str的position开始[从1开始计算]，取后面所有的字符
  38. SELECT SUBSTRING(ename,1,2) FROM emp;
  40. #LTRIM(string2) RTRIM(string2) trim去除前端空格或后端空格
  41. SELECT LTRIM(' 老韩') FROM DUAL;
  42. SELECT LTRIM('老韩 ') FROM DUAL;
  43. SELECT LTRIM(' 老韩 ') FROM DUAL;
  45. SELECT * FROM emp;

828fc8f9ea60439283320219b43d8571.png

*数学相关函数

ea7dae94454344bb8f56ce44092dff40.png

40957d6a01c34b919943d9704db4a3ca.png

453b656171614d9fb1a966035c7f2c07.png

*时间日期相关的函数

42ff6b07f97044a382069c1f464eccb7.png

f8f476d97fa14d778c0b2d57fad00995.png

1、细节说明：

（1）DATE_ADD()中的interval后面可以是year minute second day（年、分、秒、日）等

（2）DATE_SUB()中的interval后面可以是year minute second day等

（3）DATEDIFF(date1, date2)得到的是天数，而且是date1-date2的天数，因此可以取负数

（4）这四个函数的日期类型可以是date, datetime或者timestamp

（5）在实际开发中，我们也经常使用int来保存一个unix时间戳，然后使用from_unixtime()进行转换，还是非常有实用价值的

2、案例演示：

  1. #CURRENT_DATE() 当前日期
  2. SELECT CURRENT_DATE() FROM daul;
  3. #CURRENT_TIME() 当前时间
  4. SELECT CURRENT_TIME() FROM DUAL;
  5. #CURRENT_TIMESTAMP() 当前时间戳
  6. SELECT CURRENT_TIMESTAMP() FROM DUAL;
  8. #创建信息表
  9. CREATE TABLE mes(
  10.     id INT,
  11.     content VARCHAR(30),
  12.     send_time DATETIME);
  14. #添加记录
  15. INSERT INTO mes 
  16.     VALUES(1,'北京新闻',CURRENT_TIMESTAMP());
  17. INSERT INTO mes 
  18.     VALUES(2,'上海新闻',NOW());
  19. INSERT INTO mes 
  20.     VALUES(3,'广州新闻',NOW());
  22. #查询
  23. SELECT * FROM mes;
  24. SELECT NOW() FROM DUAL;#查询当前时间
  26. #显示新闻信息的发布日期，不用显示时间
  27. SELECT id,content,DATE(send_time)
  28.     FROM mes;
  30. #查询在10分钟内发布的新闻
  31. SELECT * FROM mes
  32.     WHERE DATE_ADD(send_time,INTERVAL 10 MINUTE) >= NOW()#当前时间大于（发送时间+10分钟）10分钟 <= 发送时间-当前时间
  33. SELECT * FROM mes
  34.     WHERE send_time >= DATE_SUB(NOW(),INTERVAL 10 MINUTE)#发送时间大于（当前时间-10分钟）10分钟 >= 当前时间-发送时间
  36. #请在mysql的sql语句中求出2011-11-11和1990-1-1相差多少天
  37. SELECT DATEDIFF('2011-11-11','1990-1-1') FROM DUAL;
  39. #请用mysql的sql语句求出你活了多少天？2002-11-12出生
  40. SELECT DATEDIFF(NOW(),'2002-11-12') FROM DUAL;
  42. #请用mysql的sql语句求出你活了多少年？2002-11-12出生
  43. SELECT DATEDIFF(NOW(),'2002-11-12') /365 FROM DUAL;
  45. #如果你能活到80岁，求出你还能活多少天？2002-11-5出生
  46. #先求出活到80岁时的日期x,再使用datediff(x,now());
  47. SELECT DATEDIFF(DATE_ADD('2002-11-5',INTERVAL 80 YEAR),NOW())
  48.     FROM DUAL;
  49. #上面INTERVAL 80 YEAR：YEAR可以是年月日、时分秒
  50. #上面'2002-11-5'：形式可以是date, datetime,timestamp
  52. #计算两个时间的差
  53. SELECT TIMEDIFF('10:11:11','06:10:10') FROM DUAL;
  55. #YEAR | MONTH | DAY | DATE(datetime)
  56. SELECT YEAR(NOW()) FROM DUAL;
  57. SELECT MONTH(NOW()) FROM DUAL;
  58. SELECT DAY(NOW()) FROM DUAL;
  59. SELECT MONTH('2013-11-10') FROM DUAL;#查询'2013-11-10'这个日期的月份
  61. #UNIX_TIMESTAMP():　返回的是1970-1-1 00:00:00到现在的秒数
  62. SELECT UNIX_TIMESTAMP()FROM DUAL;
  64. #FROM_UNIXTIME(): 可以把一个unix_timestamp秒数，转成指定格式的日期
  65. SELECT FROM_UNIXTIME(1671681728,'%Y-%m-%d') FROM DUAL;#2022-12-22
  66. SELECT FROM_UNIXTIME(1671681728,'%Y-%m-%d %H:%i:%s') FROM DUAL;#2022-12-22 12:02:08

*加密和系统函数

c088b537f31a452d85cb5eea1faac663.png

424edca3645344ddb3660b8d904ef7eb.png

存密码时要加密，不要直接存明文，容易被人攻击，泄露出去

  1. #USER()查询用户
  2. #可以查看登录到mysql的有哪些用户，以及登录的IP
  3. SELECT USER() FROM DUAL;#用户IP地址: root@localhost
  5. #DATABASE()查询当前使用数据库名称
  6. SELECT DATABASE() FROM DUAL;#hsp_db05
  8. #MS5(str)为字符串算出一个MD5 32的字符串，常用（用户密码）加密
  9. #root密码是hsp->加密md5->在数据库中存放的是加密后密码
  10. SELECT MD5('hsp') FROM DUAL;
  11. SELECT LENGTH(MD5('hsp')) FROM DUAL;
  13. #演示用户表，存放密码时，是md5
  14. CREATE TABLE users(
  15.     id INT,
  16.     `name` VARCHAR(32) NOT NULL DEFAULT ' ',
  17.     pwd CHAR(32) NOT NULL DEFAULT ' ');
  19. #加密存密码，存密码时不要直接存明文，容易被人攻击，泄露出去
  20. INSERT INTO users
  21.     VALUES(100,'猪肉炖粉条',MD5('hsp'));
  23. SELECT * FROM users;
  25. SELECT * FROM users
  26.     WHERE `name`='猪肉炖粉条' AND pwd=MD5('hsp');
  28. #PASSWORD(str)加密函数，MySQL数据库的用户密码就是PASSWORD函数加密
  29. SELECT PASSWORD('hsp') FROM DUAL;
  30. #*81220D972A52D4C51BB1C37518A2613706220DAC
  32. #SELECT * FROM mysql.user\G从原文密码str计算并返回密码字符串
  33. #通常用于对mysql数据库的用户密码加密
  34. #mysql.user表示数据库.表
  35. SELECT * FROM mysql.user#主要是看一些权限

*流程控制函数

0f2ca09da86946eebb344d67e60c2c5e.png

530bf0e3f9324c60b0fe8eba56319665.png

  1. #IF(expr1,expr2,expr3)如果expr1为True,则返回expr2，否则返回expr3
  2. SELECT IF(TRUE,'北京','上海') FROM DUAL;
  4. #IFNULL(expr1,expr2)如果expr1不为空，则返回expr2，否则返回expr3
  5. SELECT IFNULL(NULL,'北京','上海') FROM DUAL;
  7. #SELECT CASE WHEN expr1 THEN expr2 WHEN expr3 THEN expr4 ELSE expr5 END;
  8. #如果expr1为TRUE，则返回expr2，如果expr2为TRUE，返回expr4，否则返回expr5
  9. SELECT CASE
  10.     WHEN TRUE THEN'jack'
  11.     WHEN FALSE THEN'tom'
  12.     ELSE 'mary' END
  14. #查询emp表，如果comm是NULL，则显示0.0
  15. #判断是否为NULL要使用IS NULL，判断不为空，使用IS NOT
  16. SELECT ename,IF(comm IS NULL,0.0,comm)
  17.     FROM emp;
  18. SELECT ename,IFNULL(comm,0.0)
  19.     FROM emp;
  20. #如果emp表的job是CLERK，则显示职员，如果是MANAGER则显示经理
  21. #如果是SALESMAN则显示销售人员，其它正常显示
  22. SELECT ename,(SELECT CASE
  23.         WHEN job='CLERK'THEN'职员'
  24.         WHEN job='MANAGER'THEN'经理'
  25.         WHEN job='SALESMAN'THEN'销售人员'
  26.         ELSE job END) AS 'job'
  27.     FROM emp;

七、mysql表查询—加强版

1、WHERE子句和ORDER BY子句

74328f74322741c5ac871268681e485b.png

2、分页查询

SELECT …

LIMIT START,ROWS

#表示从start+1行开始取，取出rows行，当成该页的内容。start从0开始计算

2c9f39773dcb40de9bb94c1cc2aa5ae5.png

1fce3814da3446eb894ee7feb87387a9.png

3、使用分组函数和分组子句group by

9953bd005b1e4d5997b481c9577b1e60.png

*如果select语句同时包含有group by, having, limit, order by，那么他们的顺序是group by, having, order by

SELECT column1, column2, column3…FROM tablename

GROUP BY column

HAVING condition

ORDER BY column

LIMIT start, rows;

应用案例：

b6c40f90bf1f46b7aa05db24acce6869.png

//我的答案：

7a72beb3399e46e9874437b98d8430c2.png

//老师的答案：

b693571f6200456e8ba5c54c9a33fbbd.png

4、mysql多表查询

（1）说明：多表查询是指基于两个或两个以上的表查询

（2）148724d5d48d48c1a8c0ee616ceeb987.png

170dea6fc0c64ff3baf9398a1bd5bb20.png

  1. #显示雇员名，雇员工资及所在部门的名字[笛卡尔集]
  2. #1、雇员名、雇员工资来自emp表
  3. #2、部门的名字来自dept表
  4. #3、需求对emp和dept查询
  5. SELECT *
  6.     FROM emp,dept#过滤前，52条
  7.     WHERE emp.deptno=dept.deptno#过滤后，13条
  8.     #由这个表和下一个表的相同的某个条件去下一个表里查其他信息
  10. SELECT ename,sal,dname#过滤条件
  11.     FROM emp,dept
  12.     WHERE emp.deptno=dept.deptno    
  14. #当多个表同时拥有相同的信息，需要指定显示某个的列，即表.列表
  15. SELECT ename,sal,dname,emp.deptno
  16.     FROM emp,dept
  17.     WHERE emp.deptno=dept.deptno    
  19. SELECT * FROM emp;
  20. SELECT * FROM dept;
  21. #提示：多表查询的条件不能少于表的个数 - 1，否则会出现笛卡尔集
  23. #显示部门号为10的部门名、员工名和工资
  24. #思路：先写下简单的结构，再添加过滤条件
  25. SELECT ename,sal,dname,emp.deptno
  26.     FROM emp,dept
  27.     WHERE emp.deptno=dept.deptno AND dept.deptno=10 #用emp.deptno=10也行
  29. #显示各个员工的姓名、工资，及其工资的级别
  30. SELECT ename,sal,grade
  31.     FROM emp,salgrade
  32.     WHERE sal BETWEEN losal AND hisal;    
  34. #练习：显示雇员名、雇员工资及所在部门的名字，并按部门排序[降序排]
  35. SELECT ename,sal,dname,emp.deptno
  36.     FROM emp,dept
  37.     ORDER BY emp.deptno DESC;

5、自连接

（1）自连接是指在同一张表的连接查询 [将同一张表看成两张表]

beb78eae30834901b77ad088f41ea6de.png

6、mysql表子查询

（1）子查询是指嵌入在其他sql语句中select语句，也叫嵌套查询

（2）单行子查询是指只返回一行数据的子查询语句

（3）多行子查询指返回多行数据的子查询，使用关键字in

（4）0b145e1f452041c6a647eabb4d18a179.png

  1. #如何显示与SMITH同一部门的所有员工？
  2. #1、先查到SMITH的部门号
  3. SELECT deptno
  4.     FROM emp
  5.     WHERE ename='SMITH'
  7. #2、把上面的select语句当作一个子查询来用
  8. SELECT *
  9.     FROM emp
  10.     WHERE deptno=(
  11.         SELECT deptno
  12.         FROM emp
  13.         WHERE ename='SMITH'
  14.         )
  16. #课堂练习：如何查询和部门10的工作相同的雇员的
  17. #名字、岗位、工资、部门号，但是不含10号部门自己的成员
  19. #1、查询到10号部门有哪些工作
  20. #2、把上面查询的结果当作子查询用
  21. SELECT DISTINCT job
  22.     FROM emp
  23.     WHERE deptno=10;
  24. #组合后
  25. SELECT ename,job,sal,deptno
  26.     FROM emp
  27.     WHERE job IN(
  28.         SELECT DISTINCT job
  29.         FROM emp
  30.         WHERE deptno=10;
  31.     )AND deptno <> 10 #不等于
  33. #查询ecshop中各个类别中，价格最高的商品
  34. #把子查询当作一张临时表可以解决很多很多复杂的查询问题
  35. SELECT cat_id,MAX(shop_price)
  36.     FROM eos_goods
  37.     GROUP BY cat_id
  39. SELECT goods_id,eos_goods,cat_id,goods_name,shop_price
  40.     FROM(
  41.         SELECT cat_id,MAX(shop_price) AS max_price
  42.             FROM eos_goods
  43.             GROUP BY cat_id
  44.     ) temp,eos_goods
  45.     WHERE temp.cat_id=eos_goods.cat_id
  46.     AND temp.max_price=eos_goods.shop_price

（5）

#在多行子查询中使用all操作符

-- 思考：显示工资比部门号为30的所有员工的工资高的员工的姓名、工资和部门号
SELECT ename,sal,deptno
FROM emp
WHERE sal>ALL(
SELECT sal
FROM emp
WHERE deptno=30)
#在多行查询中使用any操作符
-- 思考：如何显示工资比部门号为30的其中一个员工的工资高的员工姓名、工资和部门号
SELECT ename,sal,deptno
FROM emp
WHERE sal>ANY(
SELECT sal
FROM emp
WHERE deptno=30)

  1. #显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
  2. SELECT ename,sal,deptno
  3.     FROM emp
  4.     WHERE sal>ALL(
  5.         SELECT sal
  6.             FROM emp
  7.             WHERE deptno=30
  8.         )
  10. SELECT ename,sal,deptno
  11.     FROM emp
  12.     WHERE sal>(
  13.         SELECT MAX(sal)
  14.             FROM emp
  15.             WHERE deptno=30
  16.         )
  18. #显示工资比部门30的其中一个员工的工资高的员工的姓名、工资和部门号
  19. SELECT ename,sal,deptno
  20.     FROM emp
  21.     WHERE sal>ANY(
  22.         SELECT sal
  23.             FROM emp
  24.             WHERE deptno=30
  25.         )
  27. SELECT ename,sal,deptno
  28.     FROM emp
  29.     WHERE sal>(
  30.         SELECT MIN(sal)
  31.             FROM emp
  32.             WHERE deptno=30
  33.         )

（6）多列子查询是指查询返回多个列数据的子查询语句

  1. #查询与smith的部门和岗位完全相同的所有雇员（并且不含smith本人）
  2. #1、得到smith的部门和岗位
  3. SELECT deptno,job
  4.     FROM emp
  5.     WHERE ename='SMITH'
  7. #2、
  8. SELECT *
  9.     FROM emp
  10.     WHERE (deptno,job)=(
  11.         SELECT deptno,job
  12.         FROM emp
  13.         WHERE ename='SMITH'
  14.     ) AND ename !='SMITH'
  16. SELECT * FROM emp;
  19. #查询和宋江数学、语文、英语成绩完全相同的学生
  20. SELECT chinese,english,math
  21.     FROM student
  22.     WHERE `name`='宋江'
  24. SELECT * 
  25.     FROM student
  26.     WHERE (chinese,english,math)=( #注意顺序
  27.         SELECT chinese,english,math
  28.         FROM student
  29.         WHERE `name`='宋江'
  30.     )
  31. SELECT * FROM student;

（6）

#在FROM子句中使用子查询

查找每个部门工资高于本部门平均工资的人的资料，

使用数据查询的小技巧：把一个子查询当作一个临时表使用

  1. #查找每个部门工资高于本部门平均工资的人的资料
  2. #1、先得到每个部门的部门号和对应的平均工资
  3. SELECT deptno,AVG(sal) AS avg_sal
  4.     FROM emp
  5.     GROUP BY deptno
  8. #2、把上面的结果当作子查询，和emp进行多表查询
  9. SELECT ename,sal,temp.avg_sal,emp.deptno
  10.     FROM emp,(
  11.         SELECT deptno,AVG(sal) AS avg_sal
  12.         FROM emp
  13.         GROUP BY deptno
  14.     ) temp #一共查两个表
  15.     WHERE emp.deptno=temp.deptno AND emp.sal > temp.avg_sal
  18. #查找每个部门工资最高的人的详细资料
  19. SELECT ename,sal,temp.max_sal,emp.deptno
  20.     FROM emp,(
  21.         SELECT deptno,MAX(sal) AS max_sal
  22.         FROM emp
  23.         GROUP BY deptno
  24.     ) temp #一共查两个表
  25.     WHERE emp.deptno=temp.deptno AND emp.sal = temp.max_sal
  28. #显示每个部门的信息（包括：部门名，编号，地址）和人员数量
  29. SELECT * FROM emp
  30. SELECT * FROM temp
  32. #统计各部门人员数量
  33. SELECT *
  34.     FROM emp
  35.     GROUP BY deptno
  37. SELECT empno,deptno,
  40. #1、统计各部门的人员数量
  41. SELECT COUNT(*),deptno
  42.     FROM emp
  43.     GROUP BY deptno;
  45. SELECT dname,dept.deptno,loc,tmp.per_num AS '人数'
  46.     FROM dept,(
  47.         SELECT COUNT(*) AS per_num,deptno
  48.         FROM emp
  49.         GROUP BY deptno
  50.     ) tmp #tmp是临时表的表名
  51.     WHERE tmp.deptno=dept.deptno
  54. #另一种写法，表.* 表示将该表所有列都显示出来，可以简化sql语句
  55. #在多表查询中，当多个表的列不重复时，才可以直接写列名
  56. SELECT dname,loc,tmp.*
  57.     FROM dept,(
  58.         SELECT COUNT(*) AS per_num,deptno
  59.         FROM emp
  60.         GROUP BY deptno
  61.     ) tmp #tmp是临时表的表名
  62.     WHERE tmp.deptno=dept.deptno

（7）合并查询

在实际应用中，为了合并多个select语句的结果，可以使用集合操作符号union, union all

1）union all 该操作符用于取得两个结果集的并集，当使用该操作符时，不会取消重复行

1ae640a9d9414ed5beff8509e6295422.png

2）union 与union all 相似，但是会自动去掉结果中的重复行

452431d5e6dd49bdaf15136811331491.png

7、表复制

（1）自我复制数据（蠕虫复制）

有时，为了对某个sql语句进行效率测试，我们需要海量数据时，可以使用此法为表创建海量数据

  1. #表的复制
  2. CREATE TABLE my_tab01(
  3.     id INT,
  4.     `name` VARCHAR(32),
  5.     sal DOUBLE,
  6.     job VARCHAR(32),
  7.     deptno INT);
  8. DESC my_tab01
  10. #演示如何自我复制
  11. #1、先把emp表的记录复制到my_tab01
  12. INSERT INTO my_tab01(
  13.     id,`name`,sal,job,deptno)
  14.     SELECT empno,ename,sal,job,deptno
  15.     FROM emp;
  17. #2、自我复制
  18. INSERT INTO my_tab01
  19.     SELECT * FROM my_tab01;
  20. SELECT COUNT(*) FROM my_tab01;
  22. #如何删掉一张表的重复记录
  23. #1、先创建一张表my_tab02
  24. #2、让my_tab02有重复的记录
  25. CREATE TABLE my_tab02 LIKE emp;#这个语句把emp表的结构（列），复制到my_tab02
  27. DESC my_tab02;
  29. INSERT INTO my_tab02
  30.     SELECT * FROM emp;
  32. SELECT * FROM my_tab02;
  34. #3、考虑去重my_tab02的记录
  35. #（1）先创建一张临时表my_tmp，该表的结构和my_tab02一样
  36. CREATE TABLE my_tmp LIKE my_tab02
  38. #（2）把my_tmp的记录通过distinct关键字处理后，把记录复制到my_tmp
  39. INSERT INTO my_tmp
  40.     SELECT DISTINCT * FROM my_tab02;
  42. #（3）清除掉my_tab02记录
  43. DELETE FROM my_tab02;
  45. #（4）把my_tmp表的记录复制到my_tab02
  46. INSERT INTO my_tab02
  47.     SELECT * FROM my_tmp;
  49. #（5）drop 掉 临时表my_tmp
  50. DROP TABLE my_tmp;
  52. SELECT * FROM my_tab02;
  54. #为什么不直接去重呢？因为直接去重只是显示出来的数据去重，实际上并没有去重

八、mysql表外连接

111a3a0bf6d349cba99b040874f151d2.png

1、外连接

（1）左外连接：左侧的表完全显示

SELECT …

FROM 表1 LEFT JOIN 表2

ON 条件 [ 表1：左表，表2：右表

82785c7ebe5d40789e920eefffc89cac.png

3ffae4aba21b4c538cd5c1832c4b70c6.png

（2）右外连接：右侧的表完全显示

SELECT …

FROM 表1 RIGHT JOIN 表2

ON 条件 [ 表1：左表，表2：右表

66785722af1e467d9aa83a643e70fb7d.png

（3）练习：

dfae4af8f28b41cc81165bea9c976e92.png

2e284f6547714729b88dfc8e3f46caa7.png

*提示：在实际的开发中，我们绝大多数情况下使用的是前面学过的连接

九、mysql约束

约束用于确保数据库的数据满足特定的商业规则。在mysql中，约束包括：not null, unique, primary key, foreign key和check五种

1、primary key

888ea7c90353461baf8d8ef246f31af5.png

  1. CREATE TABLE t31
  2.     (id INT PRIMARY KEY, #表示id列是主键
  3.     `name` VARCHAR(32),
  4.     email VARCHAR(32));
  6. #主键不能重复且不能为null
  7. INSERT INTO t31
  8.     VALUES(NULL,'hsp','hsp@sohu.com');
  10. #一张表最多只能有一个主键，但可以是复合主键
  11. CREATE TABLE t32
  12.     (id INT,
  13.     `name` VARCHAR(32),
  14.     email VARCHAR(32),
  15.     PRIMARY KEY(id,`name`)
  16.     );
  18. #主键的指定方式有两种
  19. #1、直接在字段名后指定：字段名 primary key
  20. CREATE TABLE t33
  21.     (id INT,
  22.     `name` VARCHAR(32) PRIMARY KEY,
  23.     email VARCHAR(32)
  24.     );
  25. #2、在表定义最后写primary key(列名);
  26. CREATE TABLE t34
  27.     (id INT,
  28.     `name` VARCHAR(32),
  29.     email VARCHAR(32),
  30.     PRIMARY KEY(`name`)
  31.     );
  33. #使用desc 表名，可以看到primary key的情况
  34. DESC t31 #查看t31表的结果，显示约束的情况

2、not null

5b986ca1a4cf48cfb6d8111288c3f4ef.png

3、unique

508501595a3f46efac84be45e99a6821.png

4、foreign key

（1）用于定义主表和从表之间的关系：外键约束要定义在从表上，主表则必须具有主键约束或是unique约束，当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null

（2）69531cb4907e414bb18bcb23f931409c.png

（3）细节说明：

1）外键指向的表的字段，要求是primary key或者是unique

2）表的类型是innodb,这样的表才支持外键

3）外键字段的类型要和主键字段的类型一致（长度可以不同）

4）外键字段的值，必须在主键字段中出现过，或者为null [前提是外键字段允许为null ]

5）一旦建立主外键的关系，数据不能随意删除了

4157ef6aa14e4ab4a7f56f892fd1fc67.png

5、check

（1）用于强制行数据必须满足的条件，假定在sal列定义了check约束，并要求sal列值在1000~2000之间如果不再1000~2000之间就会提示出错

（2）oracle和sql server均支持check，但是mysql5.7目前还不支持check，只做语法校验，但不会生效

06620e8c7f57420a9e8b4723029fa84f.png

*在mysql中实现check的功能，一般是在程序中控制，或者通过触发器完成

c93fc659231245979e1123262e7ca58d.png

6、练习

9959fd18edfe44cb86e8c736ab2b9764.png

  1. CREATE DATABASE shop_db;
  3. #goods
  4. CREATE TABLE goods
  5.     (goods_id INT PRIMARY KEY,
  6.     goods_name VARCHAR(64) NOT NULL DEFAULT ' ',
  7.     unitprice DECIMAL(10,2) NOT NULL DEFAULT 0
  8.         CHECK(unitprice >= 1.0 AND unitprice <= 9999.99),
  9.     category INT NOT NULL DEFAULT 0,
  10.     provider VARCHAR(64) NOT NULL DEFAULT ' ');
  12. #customer
  13. CREATE TABLE customer
  14.     (customer_id CHAR(8) PRIMARY KEY,
  15.     `name` VARCHAR(64) NOT NULL DEFAULT ' ',
  16.     address VARCHAR(64) NOT NULL DEFAULT ' ',
  17.     email VARCHAR(64) UNIQUE NOT NULL,
  18.     sex ENUM('男','女') NOT NULL, #用枚举
  19.     card_id CHAR(18));
  21. #purchase
  22. CREATE TABLE purchase
  23.     (order_id INT UNSIGNED PRIMARY KEY,
  24.     customer_id CHAR(8) NOT NULL DEFAULT '',
  25.     goods_id INT NOT NULL DEFAULT 0,
  26.     nums INT NOT NULL DEFAULT 0,
  27.     FOREIGN KEY (customer_id) REFERENCES customer(customer_id),
  28.     FOREIGN KEY (goods_id) REFERENCES customer(goods_id);
  30. DESC goods;
  31. DESC customer;
  32. DESC puchase;

7、自增长

5163be1791d44f709d04d239f90fad81.png

*细节：

（1）一般来说自增长是和primary key配合使用的

（2）自增长也可以单独使用 [ 但需要配合一个unique ]

（3）自增长修饰的字段为整数型的（虽然小数也可以，但是非常少这样使用）

（4）自增长默认从1开始，你也可以通过如下命令修改alter table 表名 auto_increment = xxx;

（5）如果你添加数据时，给自增长字段（列）指定的有值，则以指定的值为准，如果指定了自增长，一般来说，就按照自增长的规则来添加数据

06fe9c2d5ad940869dd89671bc8753a3.png

十、mysql索引

1、索引的原理：

（1）没有索引为什么会慢？

——因为全表扫描

（2）有索引为什么会快？

——形成一个索引的数据结构，比如二叉树

（3）索引的代价：

1）磁盘战胜

2）对dml(update delete insert)语句的效率影响，但项目中select的操作占到90%，比update,delete, insert多得多

78271308659142008ea6aec0383adea6.png

2、索引的类型：

（1）主键索引，主键默认也为主键索引（类型primary key)

f93f18997d47455ab0ac1f84197cee78.png

（2）唯一索引（UNIQUE）

f66496e475c74a7a9e72aef22e968e6a.png

（3）普通索引（INDEX）

（4）全文索引（FULLTEXT）[适用于MyISAM]

开发中考虑使用：全文搜索Solr和ElasticSearch(ES)。mysql自带的全文索引不好用。

3、索引使用

添加索引
CREATE [UNIQUE] INDEX index_name ON tal_name (col_name[(LENGTH)][ASC | DESC],…);
ALTER TABLE table_name ADD INDEX[index_name](index_col_name,…)

添加主键（索引）
ALTER TABLE 表名 ADD PRIMARY KEY(列名,…);

删除索引
DROP INDEX index_name ON tbl_name;
ALTER TABLE table_name DROP INDEX index_name;

删除主键索引
ALTER TABLE t_ DROP PRIMARY KEY;

  1. #创建索引
  2. CREATE TABLE t38
  3.     (id INT,
  4.     `name` VARCHAR(32));
  6. #查询表是否有索引
  7. SHOW INDEXES FROM t38;
  9. #添加索引:
  10. #1、添加唯一索引
  11. CREATE UNIQUE INDEX id_index ON t38(id);
  13. #2、添加普通索引方式1：
  14. CREATE INDEX id_index ON t38(id);
  15. #添加普通索引方式2：
  16. ALTER TABLE t38 ADD INDEX id_index(id)
  18. #如果某列的值，是不会重复的，则优先考虑使用unique索引，否则使用普通索引
  20. #3、添加主键索引
  21. CREATE TABLE t39
  22.     (id INT,
  23.     `name` VARCHAR(32));
  24. ALTER TABLE t39 ADD PRIMARY KEY(id)
  26. SHOW INDEX FROM t39
  28. #删除索引
  29. DROP INDEX id_index ON t38
  30. #删除主键索引
  31. ALTER TABLE t39 DROP PRIMARY KEY
  33. #修改索引：先删除，再添加新的索引
  35. #查询索引
  36. #方式1：
  37. SHOW INDEX FROM t38
  38. #方式2：
  39. SHOW INDEXES FROM t38
  40. #方式3：
  41. SHOW KEYS FROM t38
  42. #方式4：（不推荐）
  43. DESC t38

4、练习

（1）d0cb423611b7426faa832110492fe24f.png

（2）c0228559a3754a8382a750cf48cf37f0.png

（3）6d22c54b4ee54797a423bb058f653879.png

5、小结：

哪些列适合使用索引

（1）较频繁地作为查询条件字段应该创建索引

SELECT * FROM emp WHERE empno=1;

（2）唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件

SELECT * FROM emp WHERE sex=’男’

（3）更新非常频繁的字段不适合创建索引

SELECT * FROM emp WHERE logincount=1

（4）不会出现在WHERE子句中字段不该创建索引

十一、mysql事务

1、基本介绍：

事务用于保证数据的一致性，它由一组相关的dml语句组成，该组的dml语句要么全部成功，要么全部失败。如：转账就要用事务来处理，用以保证数据的一致性

2、事务和锁：

当执行事务操作时（dml语句），mysql会在表上加锁，防止其它用户改表的数据。

c7a58f868c1d47a095e0e1c542d30177.png

3、事务操作

（1）回退事务
保存点（savepoint）：事务中的点用于取消部分事务，当结束事务时（commit） 会自动的删除该事务所定义的所有保存点。当执行回退事务时，通过指定保存点可以回退到指定的点。

（2）提交事务
使用commit语句可以提交事务，当执行了commit语句子后，会确认事务的变化、结束事务、删除保存点、释放锁，数据生效。

当使用commit语句结束事务子后，其它会话[其他连接]将可以查看到事务变化后的新数据[所有数据就正式生效.]

2347944b0a584653af6dfb41c2f2fb27.png

2a81a0af517543d6a20e42667e425ce3.png

4、事务细节
（1）如果不开始事务，默认情况下，dml操作是自动提交的，不能回滚

（2）如果开始一个事务，你没有创建保存点。你可以执行rollback，默认就是回退到你事务开始的状态

（3）你也可以在这个事务中（还没有提交时），创建多个保存点。比如：

savepoint aaa;

savepoint bbb; #执行dml
（4）你可以在事务没有提交前，选择回退到哪个保存点
（5）mysql的事务机制需要innodb的存储引擎还可以使用，myisam不好使
（6）开始一个事务start transaction 或 set autocommit=off;

16f2994f497d453da2baaf838e75a492.png

5、事务隔离级别
（1）多个连接开启各自事务操作数据库中数据时，数据库系统要负责隔离操作，以保证各个连接在获取数据时的准确性。（通俗解释）
（2）如果不考虑隔离性，可能会引发如下问题：
1）脏读 （dirty read）：一个事务读取另一个事务尚未提交的修改
2）不可重复读（nonrepeatable read）：同一查询在同一事务中多次进行，由于其他提交事务所做的修改或删除，每次返不同的结果集
3）幻读（phantom read）：同一查询在海务中多次进行，由于其他提交事务所做的插入操作，每次返回不同的结果集

（3）查看当前会话隔离级别：select @@tx_isolation;

ef8b44737ab34007927491b5fff86dc8.png

·全局修改， 修改mysql.ini配置文件，在最后加上
#可选参数有： READ-UNCOMMITTED，READ-COMMITTED，REPEATABLE-READ，SERIALIZABLE.
[mysqld]
transaction-isolation = REPEATABLE-READ

caa769c1c62745b5bc1eeffdb54c4504.png

063c8b02a7424dbcb0335184a5674683.png

6、事务的acid特性
（1）原子性（Atomicity）
原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生

（2）一致性（Consistency）
事务必须使数据库从一个一致性状态变换到另外一个一致性状态

（3）隔离性（Isolation）
事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离

（4）持久性（Durability）
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的。接下来即便数据库发生故障也不应该对其有任何影响

0c8de89895e04f40b3c3aef305a87ba0.png

7、mysql类型和存储引擎

（1）基本介绍：
1）MySQL的表类型由存储引擎（Storage Engines）决定，主要包括MyISAM、innoDB、Memory等。
2）MySQL数据表主要支持六种类型，分别是： CSV、Memory、 ARCHIVE、MRG_MYISAM、MYISAM、InnoBDB.

3）这六种又分为两类，一类是”事务安全型”（（transaction-safe），比如：
InnoDB：其余都属于第二类，称为”非事务安全型（non-transaction-safe）[mysiam和memory]

（2）主要的存储引擎 / 表类型特点

4d902b59b5bb4ca7b1def5daebf59e61.png

（3）细节：

1）MyISAM不支持事务、也不支持外键， 但其访问速度快，对事务完整性没有要求

2）InnoDB存储引擎提供了具有提交、回滚和前恢复能力的事务安全。但是比起MyISAM存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。
3）MEMORY存储引擎使用存在内存中的内容来创建表。每个MEMORY表只实际对应一个磁盘文件。MEMORY类型的表访问非常快，因为它的数据是放在内存中的，并且默认使用HASH索引。但是一旦服务关闭，表中的数据就会丢失掉，表的结构还在

（4）如何选择表的存储引擎

1）如果你的应用不需要事务， 处理的只是基本的CRUD操作，选MyISAM，速度快

2）如果需要支持事务，选择InnoDB

3）Memory存储引擎就是将数据存储在内存中，由于没有磁盘I./0的等待，速度极快。但由于是内存存储引擎，所做的任何修改在服务器重启后都将消失。（经典用法：用户的在线状态）

d65ff88422144ce0aa7f7a78e2d34bbf.png

f50d02b066a644968e1b9da4467eeac9.png

十二、视图（view）

1、引入

d834b1d735c948c3901f15678594ae01.png

2、基本介绍：

（1）视图：是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含列，其数据来自对应的真实表（基表）

（2）视图和对应的真实表（基本）的关系

d2e5f19a175946f59507fbd5a6cc8bbf.png

3、视图的基本使用

*create view 视图名 as select 语句

*alter view 视图名 as select 语句

*SHOW CREATE VIEW 视图名
*drop view 视图名1，视图名2

4、细节：

（1）创建视图后，到数据库去看，对应视图只有一个视图结构文件（形式：视图名.frm）
（2）视图的数据变化会影响到基表，基表的数据变化也会影响到视图[insert update delete]
——针对前面的员管理系统——
mysql> create view myview as select empno，ename，job，comm from emp；
mysql> select \ from myview；
mysql> update myview set comm=200 where empno=7369；//修改视图，对基表都有变化*

mysql> update emp set comm=100 where empno=7369；//修改基表，对视频也有变化
（3）视图中可以再使用视图，数据仍然来自基表【案例演示】

  1. #创建一个视图，只能查询emp表的(empno, ename, job和deptno)信息
  2. #创建视图
  3. CREATE VIEW emp_view01
  4.     AS
  5.     SELECT empno,ename,job,deptno FROM emp;
  7. #查看视图
  8. DESC emp_view01
  10. SELECT * FROM emp_view01;
  11. SELECT empno,job FROM emp_view01;
  13. #查看创建视图
  14. SHOW CREATE VIEW emp_view01
  16. #删除视图
  17. DROP VIEW emp_view01;
  19. #细节：
  20. #1、创建视图后，到数据库去看，对应视图只有一个视图结构文件（形式：视图名.frm）
  21. #2、视图的数据变化会影响到基表，基表的数据变化也会影响到视图
  23. #修改视图
  24. UPDATE emp_view01
  25.     SET job='MANAGER'
  26.     WHERE empno=7369
  28. SELECT * FROM emp; #查询基表
  29. SELECT * FROM emp_view01;
  31. #修改基表，会影响到视图
  32. UPDATE emp
  33.     SET job='SALESMAN'
  34.     WHERE empno=7369
  36. #3、视图中可以再使用视图，比如从emp_view01视图中，
  37. #选出empno和ename做出新视图
  38. CREATE VIEW emp_view02
  39.     AS
  40.     SELECT empno,ename FROM emp_view01

5、实践

（1）安全。一些数据表有着重要的信息。有些字段是保密的，不能让用户直接看到。这时就可以创建一个视图，在这张视图中只保留一部分字段。这样，用户就可以查询自己需要的字段，不能查看保密的字段
（2）性能。关系数据库的数据常常会分表存储，使用外键建立这些表的之间关系。这时，数据库查询通常会用到连接（JOIN）。这样做不但麻烦，效率相对也比较低。如果建立一个视图，将相关的表和字段组合在一起，就可以避免使用JOIN查询数据

（3）灵活。如果系统中有一张旧的表， 这张表由于设计的问题，即将被废弃。然而，很多应用都是基于这张表，不易修改。这时就可以建立一张视图，视图中的数据直接映射到新建的表。这样，就可以少做很多改动，也达到了升级数据表的自的

6、练习

9e5e0537b68043ecb90164bbbdbd8b80.png

5cfacfee355c4444afa10dbb41a6efab.png

十三、MySQL用户管理

1、基本介绍

（1）mysql中的用户，都存储在系统数据库mysql中的user表中

e637a0fe17fc40878bd408955fdd12f1.png

（2）其中user表的重要字段说明：
1）host:允许登录的“位置”，localhost表示该用户只允许本机登录，也可以指定ip地址，比如：192.168.1.100
2）user： 用户名；
3）authentication_string:密码，是通过mysql的password()函数加密之后的密码。

2、使用

（1）创建用户

create user ‘用户名’ @ ‘允许登录位置’ identified by ‘密码’

#创建用户，同时指定密码

（2）删除用户

drop user ‘用户名’ @ ‘允许登录位置’ ;

（3）用户修改密码

#修改自己的密码：

set password = password(‘密码’);

#修改他人的密码（需要有修改用户密码权限）

set password for ‘用户名’ @ ‘登录位置’ = password(‘密码’);

c45e5cb5026c439299ef09fe8f3afbf5.png

5afd297d170f432e9e41a0232bf055e7.png

3、细节

（1）在创建用户的时候，如果不指定Host，则为%，%表示表示所有IP都有连接权限

create user xxx;
（2）你也可以这样指定
create user ‘xxx’ @ ‘192.168.1.%’ 表示xxx用户在192.168.1.*的ip可以登录mysql
（3）在删除用户的时候，如果host不是%，需要明确指定 ‘用户’ @ ‘host值’

1414d284dddc4cbf8082bcacef523dda.png

发表评论

表情：
评论列表 （有 0 条评论，194人围观）

还没有评论，来说两句吧...

相关阅读