MySql数据库笔记

于 2022-12-20 19:46:59 发布
阅读量767 收藏 3
点赞数 1
CC 4.0 BY-SA版权
文章标签： 数据库 mysql sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/baihuaeryue/article/details/128389529

MySql

特别说明(慎重): 安装失败可以利用dos窗口 命令: sc delete mysql, 删除数据库重新安全

常用指令

  1. 启动数据库 net start mysql
  2. 关闭数据库 net stop mysql

登录前必须启动服务

连接到MySQL服务器的指令:

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

  1. -p密码没有空格
  2. -p后面没写密码, 会让你输入密码
  3. 如果没有写 -h 默认主机
  4. 如果没有写 -p端口没有写, 默认是3306

Mysq数据库-普通表的本质仍然是文件

以后主要还是使用sql命令行, 进行操作数据库, 因为图形化界面如果要添加100次效率非常低, 学习sql语句非常重要

MySql语句

数据库相关

如果不指明字符集和校对规则则使用默认

默认字符集 : utf8

默认校对规则: utf8_general_ci 这个不区分大小写

CREATE DATABASE hsp_dbo1

指定字符集

# 创建一个指定utf8字符集的hsp_db02数据库
CREATE DATABASE hsp_dbo2 CHARACTER SET utf8

创建一个指定utf8的字符集的hsp_db03的数据库 带有校对规则

两个规则的比较:

utf8_bin区分大小写

utf8_general_ci 不区分大小写

# 创建一个指定utf8的字符集的hsp_db03的数据库 带有校对规则
CREATE DATABASE hsp_dbo3 CHARACTER SET utf8 COLLATE utf8_bin

注意: 创建表时如果需要使用到关键字, 解决办法 : 使用反引号解决

CREATE DATABASE `CREATE`

查询语句

#下面是一条查询sql, 
# SELECT 查询
# * 表示所有字段
# FROM 从哪个表
# WHERE 从哪个字段 NAME = 'tom' 查询名字为tom
SELECT * FROM T1
SELECT * FROM T1 WHERE NAME = 'tom'

删除数据库

# 删除数据库
DROP DATABASE hsp_dbo1;

查看数据库信息

SHOW DATABASES 
#查看前面创建的hsp db01数据库的定义信息
SHOW CREATE DATABASE hsp_dbo2
# 在创建表时, 为了规避关键字, 可以使用反引号解决
CREATE DATABASE `CREATE`

备份和恢复

# 备份  必须在dos下执行 执行mysqldump 指令其实就在mysql的安装目录\bin下面
#  这个备份文件, 就对应着sql语句
mysqldump -u root -p -B hsp_dbo2 hsp_dbo3 > e:\\bak.sql

# 恢复数据库 语句需要在sql中运行  
source e:\\bak.sql
# 方法二: 可以直接复制粘贴运行

备份某个数据库的某一个表

mysqldump -u 用户名 -p密码 数据库 表一 表二 > 位置 (文件名.sql)

数据库中的表相关操作

创建一个表

如果不写, 就按照数据库的相关数据为默认

一个字节8位

# 创建数据库
CREATE DATABASE hsp_dbo4 CHARACTER SET utf8 COLLATE utf8_bin 

# 创建一个表
CREATE TABLE `user` (id INT, `name` VARCHAR(255), `adddres` VARCHAR(25), `password` VARCHAR(23) )
CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB
表中数据的添加(后续补充)
# 添加操作
INSERT INTO t2 VALUES (110)

举例:

CREATE TABLE t2 (age INT)
CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB
# 添加操作
INSERT INTO t2 VALUES (110)
SELECT * FROM t2
表中的数据类型
数据类型深入
INT

TINYINT为例

# 如果没有指定 UNSIGNED , 则tinyint就是有符号的(-128 ~ 127)
CREATE TABLE t2 (age TINYINT)
CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB

INSERT INTO t2 VALUES (110)
SELECT * FROM t2

# 如果unsigned 被指定为带符号范围（0 ~ 255）
CREATE TABLE t3(age TINYINT UNSIGNED)
CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB

INSERT INTO t3 VALUES (-12)
INSERT INTO t3 VALUES(255)

BIT为例

说明

  1. bit(m) m 在1~64位
  2. 添加数据显示是按照 位 来显示
  3. 查找时任然按照数值来查询
  4. 添加数值时如果只有0和1可以考虑使用这个类型

CREATE TABLE t4(num bit(8))
INSERT INTO t4 values(1)
INSERT INTO t4 VALUES (255)
SELECT * FROM t4
SELECT * FROM t4 WHERE num = 255
#查询结果:
00000001
11111111
小数
  1. FLOAT/DOUBLE[UNSIGNED]
    float单精度, Double双精度
  2. DECIMAL[M, D] [UNSIGNED]

可以支持更加精确的小数位。M是小数位数（精度）的总数（包含D）, D是小数点（标度）后面的位数。
如果D是0，则值没有小数点或分数部分。M最大65。D最大是30。

如果M被省略，默认是10。

如果D被省略，默认是0。

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

CREATE TABLE t5 (
id FLOAT,
id2 DOUBLE,
id3 DECIMAL(30, 20))
CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB


INSERT INTO t5 VALUES(88.12345678912345,88.12345678912345,88.12345678912345)
SELECT * FROM t5

#DECIMAL 存放最大的数字

CREATE TABLE t6 (
num DECIMAL ( 65, 0 ))
insert INTO t6 VALUES(899999993333838838838388838383009338388383838383838383)

SELECT * FROM t6

CREATE TABLE t7(
num DOUBLE ) CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB
字符串
  1. CHAR(sIze)固定长度字符串最大255字符
  2. VARCHAR(size)0~65535可变长度的字节 [utf_8最大21844个字符 1-3个字节用于记录]

解释 :
VARCHAR(size)0~65535 这个不能直接写65535因为这个单位是字节, 需要字节用于记录。
而且每个编码格式(比如gbk两个字节, utf83个字节), 所以创建一个varchar(65535)是不会成功的

使用细节
  1. 细节一
    char(4) ,这里的4表示4个字符数(最大255), 不是字节数, 不管是英文或中文都只能写存入4个
    varchar(4), 这里也是表示4个字符, 不管是英文或中文都只能写存入4个
    所以占用的内存大小是不确定的, 需靠考虑编码格式(比如gbk utf8)
  2. 细节二
    对于char固定长度的理解 :
    如果你创建一个char(4), 如果你插入’aa’ ,也会分配的4个字符的空间, 没有填数据的按照默认初始化
    对于varchar是变长的理解:
    如果创建一个varchar(4), 如果你插入’aa’, 内存只会分配两个字符空间( 但是注意一点: 占用内存的大小 = 实际的数据大小 + (1-3)个字节 )
  3. 细节三
    什么时候使用char或者varchar ?

① 如果长度固定, 可以使用char 比如md5加密, 邮件编码等 优点: 查询速度快
② 如果长度不确定可以用使用varcahr

  1. 细节四
    如果不够用， 可以使用text[0 ~ 2^16字节]， 注意tert没有默认值
    放入更多字符可以使用mediumtext [0 ~ 2^24字节] longtext [0 ~ 2^32字节]

代码演示

# 里面填写的是字符, 不是字节
CREATE TABLE t11 (`name` VARCHAR(32766)) CHARACTER SET gbk
CREATE TABLE t12 (`name` CHAR(4));
INSERT INTO t12 VALUES ('韩顺平6')

CREATE TABLE t14 (article TEXT, num MEDIUMTEXT , `name` LONGTEXT)
INSERT INTO t14 values('韩顺平nb', '韩顺平6', '韩顺平1212')
SELECT * FROM t14
日期类型
CREATE TABLE t15 (birthday DATE, job_time DATETIME, longin_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP) -- 登录时间如果想要自动更新的话需要写这句话

INSERT INTO t15 (birthday, job_time) VALUES ('2022-11-12', '2022-11-11 10:10:10') 

SELECT * FROM 	t15
表的操作

举例 :

ALTER TABLE emp 
			ADD image VARCHAR(23) NOT NULL DEFAULT '' 
			AFTER resume 
ALTER TABLE emp 
			ADD sex CHAR(1) NOT NULL DEFAULT '' AFTER `NAME`

-- 修改job列，使其长度为60。
ALTER TABLE emp
			MODIFY job VARCHAR(60) not NULL DEFAULT ''
-- 删除sex列。
ALTER TABLE emp
				DROP sex
-- 表名改为employee。
RENAME TABLE emp TO employee
-- 修改表的字符集为utf8
ALTER TABLE emp 
				CHARACTER SET utf8
-- 列名name修改为user name
ALTER TABLE emp 1
		CHANGE `NAME` `user_name`VARCHAR(32) NOT NULL DEFAULT ''
-- 查看表结构, 可以查看的列
DESC emp
CRUD

解释 :

C[CREAT] R[READ] U[UPDATE] D[DELETE]

雇员系统表(使用频率高)
CREATE TABLE dept( /*部门表*/
deptno MEDIUMINT   UNSIGNED  NOT NULL  DEFAULT 0, 
dname VARCHAR(20)  NOT NULL  DEFAULT "",
loc VARCHAR(13) NOT NULL DEFAULT ""
);

INSERT INTO dept VALUES(10, 'ACCOUNTING', 'NEW YORK'), (20, 'RESEARCH', 'DALLAS'), (30, 'SALES', 'CHICAGO'), (40, 'OPERATIONS', 'BOSTON');




#创建表EMP雇员
CREATE TABLE emp
(empno  MEDIUMINT UNSIGNED  NOT NULL  DEFAULT 0, /*编号*/
ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/
job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/
mgr MEDIUMINT UNSIGNED ,/*上级编号*/
hiredate DATE NOT NULL,/*入职时间*/
sal DECIMAL(7,2)  NOT NULL,/*薪水*/
comm DECIMAL(7,2) ,/*红利*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/
);

 
 INSERT INTO emp VALUES(7369, 'SMITH', 'CLERK', 7902, '1990-12-17', 800.00,NULL , 20), 
(7499, 'ALLEN', 'SALESMAN', 7698, '1991-2-20', 1600.00, 300.00, 30),  
(7521, 'WARD', 'SALESMAN', 7698, '1991-2-22', 1250.00, 500.00, 30),  
(7566, 'JONES', 'MANAGER', 7839, '1991-4-2', 2975.00,NULL,20),  
(7654, 'MARTIN', 'SALESMAN', 7698, '1991-9-28',1250.00,1400.00,30),  
(7698, 'BLAKE','MANAGER', 7839,'1991-5-1', 2850.00,NULL,30),  
(7782, 'CLARK','MANAGER', 7839, '1991-6-9',2450.00,NULL,10),  
(7788, 'SCOTT','ANALYST',7566, '1997-4-19',3000.00,NULL,20),  
(7839, 'KING','PRESIDENT',NULL,'1991-11-17',5000.00,NULL,10),  
(7844, 'TURNER', 'SALESMAN',7698, '1991-9-8', 1500.00, NULL,30),  
(7900, 'JAMES','CLERK',7698, '1991-12-3',950.00,NULL,30),  
(7902, 'FORD', 'ANALYST',7566,'1991-12-3',3000.00, NULL,20),  
(7934,'MILLER','CLERK',7782,'1992-1-23', 1300.00, NULL,10);



#工资级别表
CREATE TABLE salgrade
(
grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
losal DECIMAL(17,2)  NOT NULL,
hisal DECIMAL(17,2)  NOT NULL
);

INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);
INSERT语句

举例:

INSERT INTO employee ( id, `name`, birthday, entry_date, job, Salary, resume, img ) VALUES ( 12, '李华', '2001-01-01', '2022-02-03 12:12:12', '英语作文杀手', 3000, '英语作文的常客, 喜欢搞别人心态', '不告诉你长什么样子')


**细节**

 1.  插入的数据应与字段的数据类型相同。比如把'abc'添加到INT类型会错误
 2. 数据的长度立在列的规定范围内，例如：不能将一个长度为80的字符串加入到长度为40的列中。
 3. 在va1ues中列出的数据位置必须与被加入的列的排列位置相对立。
 4. 字符和日期型数据立包含在单引号中。
 5. 列可以插入空值[前提是该字段允许为空]，insert INTO TABLE VALUE(NULL)
 6. INSERT INTO tab name(列名..)VALUES(),(),()形式添加多条记录
 7. 如果是给表中的所有字段添加数据，可以不写前面的字段名称
 8. 默认值的使用，当不给某个字段值时，如果有默认值就会添加，否则报错
     如果某个列没有指定not nu11,那么当添加数据时，没有给定值，则会默认给nu11


演示:
```mysql
-- 插入的数据应与字段的数据类型相同。
CREATE TABLE goods (
			id INT,
			`name` VARCHAR(10),
			price DOUBLE)
-- 比如把'abc'添加到INT类型会错误
INSERT INTO goods (id, `name`, price) VALUES ( 1, '华为手机', 2000)
INSERT INTO goods (id, `name`, price) VALUES ('abc', '锤子手机', 4000) -- 这个就会报错
INSERT INTO goods (id , `name`, price) VALUES ('2', '小米手机', 1000)

-- 2.数据的长度立在列的规定范围内，例如：不能将一个长度为80的字符串加入到长度为40的列中。
INSERT INTO goods VALUES (3, 'ibmib,ib,mibmindasd', 100) -- Data too long for column 'name'

-- 3.在va1ues中列出的数据位置必须与被加入的列的排列位置相对立。
INSERT INTO goods (`name`, id, price) VALUES ('IBM手机', 3, 8000)
-- 4.字符和日期型数据立包含在单引号中。
CREATE TABLE goods2 (
				id INT,
				address VARCHAR(10) NOT NULL DEFAULT '北京市',
				login_time TIMESTAMP NOT null DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)
				
INSERT INTO goods2 ( id ) VALUES (1)
SELECT * FROM goods2
-- 5.列可以插入空值[前提是该字段允许为空]，insert INTO TABLE VALUE(NULL)
INSERT INTO goods (id, `name`, price) values (5 , NULL, NULL)
-- 6.INSERT INTO tab name(列名..)VALUES(),(),()形式添加多条记录
INSERT INTO goods VALUES (6, '一加手机', 6000), (7, '惠普', 12000);
-- 7.如果是给表中的所有字段添加数据，可以不写前面的字段名称
INSERT INTO goods VALUES (8, '苹果手机', 20000)
-- 8.默认值的使用，当不给某个字段值时，如果有默认值就会添加，否则报错

DESC  goods2
SELECT * FROM goods
UPDATE

细节:

  1. UPDATE语法可以用新值更新原有表行中的各列。
  2. SET子句指示要修改哪些列和要给予哪些值。
  3. NHERE子句指定应更新哪些行。如没有WHERE:子句，则更新所有的
    行（记录），一定小心。
  4. 如果需要修改多个字段，可以通过set字段1=值1，字段2=值2.…

代码演示

INSERT INTO employee VALUES ( 2, '老妖', '男', '2000-12-12', '2022-12-12 12:12:12', '大学老师', 5000, '大学老师 + 建筑地领头羊', 'd:\beautiful.jpg');
-- 1.将所有员工薪水修改为5000元。
# 不加WHERE关键字, 那么会修改所有的这个一帮不会使用
UPDATE employee 
			SET Salary = 6000
 
SELECT * FROM employee
-- 2.将姓名为小妖怪的员工薪水修改为3000元。
UPDATE employee
				SET salary = 2000
				WHERE id = 12 -- 这里也可以改为 user_name = '小妖'
				
-- 3.将老妖怪的薪水在原有基础上增加1000元。

UPDATE employee 
				SET salary = salary + 1000, salary = 100000
				WHERE user_name = '老妖'
DESC employee
# 重名表
RENAME TABLE employee to emp
DELETE

细节:

  1. **如果不使用where子句，将删除表中所有数据。
  2. Delete 语句不能删除某一列的值（可使用update设为nul或者"）
  3. 使用deletei语句仅删除记录，不删除表本身。
    如要删除表，使用drop table 语句。drop table表名

举例 :

 DELETE FROM emp 
				WHERE id = 2
				
drop emp
SELECT【重要】

去重标准 ： 都相投才算相同包含各个字段

# 查询表中所有字段的信息
SELECT * FROM student
# 查询表中包字段的信息， 并去重    去重标准 ：都相同才去包括name和english
SELECT DISTINCT `name`, english FROM student

注意:

  1. AS关键字可以省略
  2. 列的别名可以用双引号引起来也, 可以不同但是某些特殊情况必须使用 比如 hsp nb 这种
  3. 单引号不报错的原因是MySQL数据库对这个不严谨

举例：

# 别名和运算  
#名字太长， 可以起别名
SELECT `name`, (english + math + chinese) FROM student
# 总分加10
SELECT `name`, (english + math + chinese + 10) FROM student
# 别名
SELECT `name` AS "姓名", (english + math + chinese ) AS "total_score" FROM student
WHERE

注意:

  1. between and 是包含两头数据的!
  2. like ‘韩%’ 表示以韩开头的就可以
# 相关的运算符
-- 查询姓名为赵云的学生成绩
SELECT * FROM student
				WHERE `name` = '赵云'
-- 查询英语成绩大于90分的同学
SELECT * FROM student 
				WHERE english > 90
-- 查询总分大于200分的所有同学
SELECT * FROM student
					WHERE (chinese + english + math) > 200
					

#■查询math大于60并且(and)id大于4的学生成绩
SELECT `name`, math AS '数学成绩' FROM student
				WHERE math > 60 AND id > 4
#■查询英语成绩大于语文成绩的同学
SELECT * FROM student
				WHERE english > chinese
#■查询总分大于200分并且数学成绩小于语文成绩，的
#姓韩的学生

# 这里写 like '韩%' 表示以韩开头的就可以
SELECT * FROM student
				WHERE ( chinese + math  + english ) > 200 AND chinese > math AND `name` LIKE '赵%'
				
SELECT * FROM student
课堂练习
#1.查询英语分数在80-90之间的同学。
SELECT * FROM student 
				WHERE english >= 80 AND english <= 90
#2.查询数学分数为89,90,91的同学。
SELECT * FROM student 
				WHERE math IN (89, 90, 91)
#3.查询所有姓李的学生成绩。
SELECT * FROM student
				WHERE `name` LIKE '李%'
#4.查询数学分>80，语文分>80的同学。
SELECT * FROM student
					WHERE math > 80 AND chinese > 80


课堂练习[学员自己练习]
#1.查询语文分数在70-80之间的同学。between and 
SELECT * FROM student
				WHERE chinese BETWEEN 70 AND 80
				
#2.查询总分为189,190,191的同学。

SELECT * FROM student
WHERE ( chinese + english + math ) IN (189, 190, 191)
#3.查询所有姓李或者姓宋的学生成绩。
SELECT * FROM student
			WHERE `name` LIKE '李%' OR `name` LIKE '宋%'
#4.查询数学比语文多30分的同学。			
select * FROM student
				WHERE ( math - chinese ) > 30

创键的表

Order by
-- 对数学成绩排序后输出【升序】

SELECT * FROM student
				ORDER BY math;
-- 对总分按从高到低的顺序输出
select `name`, ( english + math + chinese ) AS 'total_score' FROM student
			 ORDER BY total_score DESC;
-- 对姓李的学生成绩排序输出（升序）
SELECT `name`, (english + math + chinese) AS 'total_score' FROM student 
				WHERE `name` LIKE '韩%'
				ORDER BY total_score DESC -- 降序
统计函数

COUNT

count( * ) 和 count(列)的区别

count( * )会返回满足条件的行数
count(列) 会返回满足条件的列有多少个, 但是会排除为NULL的情况

举例说明:

-- count(*)和count(列)的区别

CREATE TABLE t16 (
			`name` VARCHAR(30));
			
INSERT INTO t16 VALUES ('jack');
INSERT INTO t16 VALUES ('mick');
INSERT INTO t16 VALUES ('tom');
INSERT INTO t16 VALUES (NULL);

SELECT * FROM t16

SELECT count(*) FROM t16 -- 结果为4
SELECT COUNT(`name`) FROM T16 -- 结果为3

SUM
SUM会返回满足where条件行数的总和， 一般用于数值型的列

注意: 去多个列的总和中间需要加上 ,

-- 统计一个班级数学总成绩？
SELECT SUM(math) FROM student;
-- 统计一个班级语文、英语、数学各科的总成绩
SELECT SUM(math) AS 'math_total', SUM(chinese) AS 'Chinese_total', SUM(english) AS 'english_total' from student	
-- 统计一个班级语文、英语、数学的成绩总和
SELECT SUM(chinese + english + math) FROM student;
-- 统计一个班级语文成绩平均分
SELECT SUM(chinese) / COUNT(*) AS 'chinese_avg' FROM student;

-- 求一个班级数学平均分？
SELECT AVG(math) FROM student
-- 求一个班级总分平均分
SELECT AVG(math + chinese + english) AS 'total_avg_socer' FROM student


-- 求一个班级数学平均分？
SELECT AVG(math) FROM student
-- 求一个班级总分平均分
SELECT AVG(math + chinese + english) AS 'total_avg_socer' FROM student
GROUP BY + HAVING

group by 把相同的东西合并之后组成新的表之后可以通过having条件记件过滤

-- 如何显示每个部门的平均工资和最高工资
SELECT AVG(sal), max(sal) , deptno FROM emp GROUP BY deptno

-- 显示每个部门的每种岗位的平均工资和最低工资
-- 老师分析 
			-- 1.先把每个部门的的平均工资和最低工资
			-- 2. 再到显示每个部门的每种岗位的平均工资和最低工资
SELECT AVG(sal), MAX(sal), deptno, job FROM emp
					GROUP BY deptno, job
-- 显示平均工资低于2000的部门号和它的平均工资/别名
SELECT AVG(sal) AS 'avg_sal', deptno FROM emp
			 GROUP BY deptno HAVING avg_sal < 2000 -- having 过滤条件 经过group by 分组之后过滤
函数
字符串函数

DUAL 为亚元表, 可以作为测试表

SUBSTRING ( str, position, length ) 空格也计算在内, 不写length取到最后

-- CHARSET(str)返回字串字符集
SELECT CHARSET(ename) FROM emp
-- CONCAT (string) [,..]连接字串
SELECT CONCAT(ename,'  job is  ', job) FROM emp;
-- INSTR(string ,substring)返回substring在string中出现的位置，没有返回O
SELECT instr('hsp','h') FROM  DUAL
-- UCASE (string)转换成大写
SELECT UCASE(ename) FROM emp
-- LCASE (string)转换成小写
SELECT LCASE(ename) FROM emp
-- LEFT(string),length从string 中的左边第一个起取length个字符, 不写length就取到最后
SELECT LEFT('java_study',3) FROM DUAL
select RIGHT(ename,3) FROM emp; -- 从右边取
-- LENGTH (string)string长度[按照字节]
SELECT LENGTH(ename) FROM emp
SELECT LENGTH('韩顺平') FROM DUAL --  结果是9
SELECT 	LENGTH('HSP') FROM DUAL -- 结果是3
-- REPLACE (str ,search str)在str中用replace_str替换search_strreplace_str
SELECT `ename`,REPLACE(job ,'MANAGER','经理') FROM emp;
-- STRCMP(string1,string)逐字符比较两字串大小，
SELECT STRCMP('HSP','asp') 
-- SUBSTRING (str,position [,length从str的position开始【从1开始计算】，取length])个字符
SELECT SUBSTRING('On java book ', 2) FROM DUAL -- 空格也计算在内, 不谢长度取到最后
-- LTRIM (string) RTRIM(string)去除前端空格或后端空格
SELECT LTRIM('  onjava  ') FROM DUAL;
SELECT RTRIM('  onjava  ') FROM DUAL;
-- trim 去除两端的空格 
SELECT TRIM('  onjava  ') FROM DUAL;

考题 输出以首字母小写的方式输出员工的ename


-- 使用连接的方式进行搞  sMITH 
SELECT CONCAT(SUBSTRING(LCASE(ename),1 , 1), SUBSTRING(ename, 2)) FROM emp

-- 使用replace() 替代搞起来
SELECT REPLACE(ename,SUBSTRING(ename, 1, 1),LCASE(SUBSTRING(ename, 1, 1))) FROM emp
SELECT REPLACE(ename,LEFT(ename,1),LCASE(SUBSTRING(ename, 1, 1))) FROM emp
数学函数

RAND([seed]) 会返回一个浮点数范围是0~1, 如果里面没有写seed那么每次刷新就会一直变化 如果写了seed那么就不会变化 !

-- ABS(num)绝对值
SELECT ABS(-10) FROM DUAL

-- BIN (decimal_number)十进制转二进制
SELECT BIN(10) FROM DUAL

-- CEILING (number)向上取整，得到比num2大的最小整数
SELECT CEILING(2.8)FROM DUAL
SELECT CEILING(3.3) FROM DUAL

-- CONV(number,from_base,to_bas) 进制转换
-- 十进制的 8, 转化为 2 进制
SELECT CONV(8,10,2) FROM DUAL
SELECT CONV(16, 10, 2) FROM DUAL
-- FLOOR (number) 向下取整，得到比num2小的最大整数
SELECT FLOOR(2.8) FROM DUAL

-- FORMAT (number,decimal_places) 保留小数位数
SELECT FORMAT(2.989789898,2) FROM DUAL

-- HEX (DecimalNumber) 转十六进制
SELECT HEX(12) FROM DUAL

-- LEAST (number,number2 [,..])求最小值
SELECT LEAST(12.2,12.4,-18) FROM DUAL

-- MOD (numerator ,denominator) 求余
SELECT MOD(10,2) FROM DUAL

-- RAND([seed]) 其范围为 0 ≤ v≤ 1.0
SELECT RAND(12) FROM DUAL -- 里面写一个种子(数), 会生成随机数范围是0~1,但是只会变化一次
SELECT RAND() FROM DUA
时间日期相关函数

注意事项:

  1. 上面函数的类型可以是date, datetime, timestamp

  2. DATE_ADD( ) 中的interval 后面可以是 年月时分秒

  3. DATE_SUB 同理

  4. DATEDIFF(date1, date2) 获得天数, 这里是 date1 - date2 所以可能为负值

    重要实例

请查询在10分钟内发布的帖子

-- 
-- 有两种解决方案
-- 第一种
SELECT * FROM mes
			WHERE DATE_ADD(sendtime,INTERVAL 10 MINUTE ) >= NOW()
-- 第二种
SELECT * FROM mes 
		WHERE DATE_SUB(NOW(), interval 10 MINUTE) <= sendtime

普通实例

-- 日期时间相关的函数

-- CURRENT DATE()当前日期
SELECT CURRENT_DATE FROM DUAL
-- CURRENT_TIME ()当前时间
SELECT CURRENT_TIME FROM DUAL
-- CURRENT TIMESTAMP()当前时间戳
SELECT CURRENT_TIMESTAMP FROM DUAL

CREATE TABLE mes (id INT, content VARCHAR(30), sendtime datetime)

INSERT INTO mes VALUES (1, '北京新闻', CURRENT_TIMESTAMP)
INSERT INTO mes VALUES (2, '上海新闻', CURRENT_TIMESTAMP);
INSERT INTO mes VALUES (3, '广州新闻', CURRENT_TIMESTAMP);
SELECT * FROM mes

SELECT NOW() FROM DUAL -- 现在的时间
-- 显示所有新闻信息，发布日期只显示日期，不用显示时间
SELECT id, content, DATE(sendtime) FROM mes;
-- 请查询在10分钟内发布的帖子
-- 有两种解决方案
-- 第一种
SELECT * FROM mes
			WHERE DATE_ADD(sendtime,INTERVAL 10 MINUTE ) >= NOW()
-- 第二种
SELECT * FROM mes 
		WHERE DATE_SUB(NOW(), interval 10 MINUTE) <= sendtime
-- 请在mysql的sql语句中求出2011-11-11和1990-1-1相差多少天
SELECT FORMAT(DATEDIFF('2011-11-11','1990-01-01') / 365,0) FROM DUAL
-- 请用mysql的sql语句求出你活了多少天？[练习]
#SELECT CONCAT(('我活了', DATEDIFF(NOW(),'2003-03-23'), '天') FROM DUAL --不对不能运行

SELECT DATEDIFF(NOW(),'2003-03-23') FROM DUAL

-- 如果你能活80岁，求出你还能活多少天[练习]
-- 我的方案
SELECT (DATEDIFF('2083-03-23','2003-03-23') - DATEDIFF(NOW(),'2003-03-23')) AS '我还能活的天数' FROM DUAL
-- 老韩的方案(吊打我)
SELECT DATEDIFF(DATE_ADD('2003-03-23',interval 80 YEAR),NOW()) FROM DUAL

获取某个日期的年月日

SELECT YEAR(NOW()) FROM DUAL;
SELECT MONTH(NOW()) FROM DUAL;
SELECT DAY(NOW()) FROM DUAL;

时间撮相关

unix_timestamp 返回的是1970-01-01到现在的秒数
FROM_UNIXTIME 可以放回指定格式的 %Y %m %d %H:%i:%s 格式是不变的

在实际开发中可以通过int类型来存放秒数,通过from_unixtime来显示指定格式的时间

SELECT UNIX_TIMESTAMP(NOW()) FROM DUAL;
SELECT FROM_UNIXTIME(132199102, '%Y %m %d %H:%i:%s') FROM DUAL
加密和系统函数

存放用户密码一定要加密存放, mysql存放密码利用的是password()函数

# 演示加密和系统函数

-- USER()查询用户
SELECT USER() FROM DUAL  -- root@localhost 这里的格式是 用户@用户的ip地址
-- DATABASE()当前使用的数据库名称
SELECT DATABASE() FROM DUAL
-- MD5(str)  为字符串算出一个MD532的字符串，（用户密码）加密
-- MD5(str) 的密码是 hsp -> 加密md5 -> 在数据库存放加密后的密码
SELECT LENGTH(MD5('hsp')) FROM DUAL

CREATE TABLE `users` (id INT, `names` VARCHAR(32) NOT NULL DEFAULT '', `password` CHAR(32) NOT NULL)
INSERT INTO users VALUES (100, '韩顺平', MD5('hsp'))

SELECT * FROM users 
			WHERE `names` = '韩顺平' AND `password` = MD5('hsp')
SELECT * FROM users

ALTER TABLE users 
			MODIFY `password` CHAR(32) NOT null DEFAULT ''
DESC users
-- PASSWORD(str) 从原文密码str计算并返回密码字符串，通常用于对mysql用户进行加密
-- select from mysql.user \G 数据库的用户密码加密
SELECT * FROM mysql.`user` -- *81220D972A52D4C51BB1C37518A2613706220DAC
SELECT PASSWORD('hsp') FROM DUAL
流程控制函数

注意: 判断是否为NULL需要 IS NULL 判断不为空IS NOT NULL

-- IF(expr1,expr2,expr3)如果expr1为True,则返回expr2否则返回expr3
SELECT IF(TRUE,'tom','jack') AS 'if语句测试' FROM DUAL

-- IFNULL(expr1,expr2)如果expr1不为空NULL,则返回expr1,否则返回expr2
select IFNULL('java','mysql') FROM DUAL
SELECT IFNULL(NULL,'java') FROM DUAL
-- SELECT CASE WHEN expr1 THEN expr2如果expr1为TRUE,则返回expr2,如果expr2
SELECT CASE 
	WHEN TRUE THEN 'jack'
	WHEN FALSE THEN 'tom'
  ELSE 'mary' END;


-- HEN expr3 THEN expr4 ELSE expr5为t,返回expr4,否则返回expr5 END;[类似多重分支]
-- 查询emp表，如果comm是nul1,则显示0.0
-- 方式一
SELECT ename, IF(comm IS NULL,0.0,comm) FROM emp

-- 方式二
SELECT ename, IFNULL(comm,0.0) FROM emp -- 返回0.0


-- 如果emp表的job是CLERK!则显示职员，如果是MANAGER则显示经理如果是SALESMAN则显示销售人员，其它正常显示

SELECT ename, (SELECT CASE 
	                   WHEN job = 'MANAGER' THEN '经理'
										 WHEN job = 'SALESMAN' THEN '销售人员'
										 WHEN job = 'CLERK' THEN '职员'
										 ELSE job END) FROM emp
MySql表查询强化
分页查询

规律: LIMIT 每页的数据量 * (第n页 - 1) , 每页的数据量


SELECT * FROM emp
-- 第一页
SELECT * FROM emp
			ORDER BY empno
			LIMIT 0, 3
-- 第二页
SELECT * FROM emp
		  ORDER BY empno
			LIMIT 3, 3
-- 第三页		
SELECT * FROM emp
			ORDER BY empno
			LIMIT 6, 3
-- 第四页
SELECT * FROM emp
			ORDER BY empno
			LIMIT 9, 3
-- 第五页		
SELECT * FROM emp
			ORDER BY empno
			LIMIT 12, 3
分组函数和分组语句 GROUP BY

SELECT * FROM emp
-- 显示每种岗位的雇员总数、平均工资
SELECT job,  COUNT(*) , AVG(sal) FROM emp
			GROUP BY job;
-- 显示雇员总数，以及获得补助的雇员数。
SELECT COUNT(*) FROM emp
-- 获得补助的雇员数
-- count() 如果为null那么不会计算在内
SELECT COUNT(comm) FROM emp	;
-- 下面的方式结果是对的, 但是感觉可扩张性比较差
SELECT count(*) FROM emp
			WHERE comm IS NULL
-- 获没得补助的雇员数
SELECT COUNT(IF(comm IS NULL,1,NULL)) FROM emp; 
-- 下面的方式结果是对的, 但是感觉可扩张性比较差
SELECT count(*) FROM emp
			WHERE comm IS NOT NULL
-- 显示管理者的总人数。
SELECT COUNT(DISTINCT mgr) AS '经理总数' FROM emp -- DISTINCT 去重

-- 显示雇员工资的最大差额。
SELECT FORMAT(MAX(sal) - MIN(sal),0) AS '最大工资差值' FROM emp
数据分组总结

顺序不能颠倒

-- 应用案例：请统计各个部门的平均工资avg,
-- 并且是大于1000的，并且按照平均工资从高到低排厅
-- 取出前两行记录 -- 好像我落了这节

SELECT * FROM emp

SELECT deptno, AVG(sal) AS 'avg_sal' FROM emp
				GROUP BY deptno
				HAVING avg_sal > 1000
				ORDER BY avg_sal DESC
				LIMIT 0, 2
多表查询

定义: 多表查询是指个或2个以上的表进行查询

笛卡尔集 :

  1. 多表查询, 默认情况下, 把第一张表的一行和第二表的每一行进行组合,并返回结果包含两张表的所有的列
  2. 这样的处理方法称为笛卡尔集
  3. 多表查询的关键是如何过滤WHERE

注意: 过滤条件最少是表数 - 1 否则会出现笛卡尔集

-- 显示雇员名，雇员工资及所在部门的名字【笛卡尔集】
/*
		1. 雇员和工资来自emp表
		2. 部门名称所在得表时dept
		3. 需求对emp 和 dept查询 ename, sal, dname, deptno
		4. 当我们需要指定某一个列时, 需要 表.列表
*/
SELECT ename, sal, dname, emp.deptno FROM emp, dept
				WHERE emp.deptno = dept.deptno

练习:

				
SELECT * FROM EMP, dept -- 结果为笛卡尔集
-- 老韩小技巧：多表查询的条件不能少于表的个数-1，否则会出现笛卡尔集
-- 如何显示部门号为10的部门名、员工名和工资

SELECT ename, sal, dname, emp.deptno FROM emp, dept
			WHERE emp.deptno = dept.deptno AND dept.deptno = 10

-- 显示各个员工的姓名，工资，及其工资的级别

SELECT ename, sal, salgrade.grade FROM emp, salgrade
				WHERE emp.sal >= salgrade.losal AND emp.sal <= salgrade.hisal
				ORDER BY grade DESC;
SELECT ename, sal, salgrade.grade FROM emp, salgrade
				WHERE sal BETWEEN salgrade.losal AND salgrade.hisal
				ORDER BY grade DESC;				
				
				
SELECT * FROM emp				
				
				
				
# 练习题 显示雇员名，雇员工资及所在部阶门的名字，并按部门排序[降序排]

-- 两个表, emp 和 dept
SELECT ename, sal, dept.dname, dept.deptno FROM emp, dept
			WHERE emp.deptno = dept.deptno
			ORDER BY dept.deptno DESC;

SELECT * FROM dept
自连接查询

如何显示公司上级和他上级的姓名?

注意:

  1. 把同一张表当成两张表使用, 而且必须得起别名
  2. 表起别名的格式 : 表名 表别名
  3. 类名不确定可以起别名 格式: 列名 AS 列别名

-- 显示公司上级和他自己的名字

-- 老韩分析 : 员工姓名在emp 上级姓名也在emp表
-- 员工上级是通过emp表的mgr关联

SELECT  worker.ename AS '员工',  boss.ename AS '管理者'
			FROM emp worker, emp boss
			WHERE worker.mgr = boss.empno
子查询

子查询分为如下几类：

  1. 标量子查询：返回单一值的标量，最简单的形式。

  2. 列子查询：返回的结果集是 N 行一列。

  3. 行子查询：返回的结果集是一行 N 列。

  4. 表子查询：返回的结果集是 N 行 N 列。

-- 如何显示雨smith 同一部门的所有员工
-- 查询smith所在地1部门
SELECT deptno FROM emp
			WHERE ename = 'SMITH'
			
SELECT ename, deptno, sal, job FROM emp
			WHERE deptno = (SELECT deptno FROM emp
			WHERE ename = 'SMITH')
			
-- 表示看看么表结构			
SELECT * FROM emp

-- 课堂练习：如何查询和部门10的工作相同的雇员的
-- 名字、岗位、工资、部门号，但是不含10自己的.

SELECT DISTINCT job FROM emp -- 可以检测一下如果没有distinct排重, 那么某一个岗位就会出现两次
			WHERE deptno = 10
-- 注意多条数据需要用IN		
SELECT ename, job, sal, deptno freo FROM emp
			WHERE job IN (SELECT DISTINCT job FROM emp
			WHERE deptno = 10) AND ename NOT IN (
			SELECT DISTINCT ename FROM emp
			WHERE deptno = 10)
-- 方式二
SELECT ename, job, sal, deptno FROM emp
			WHERE job IN (SELECT job FROM emp WHERE deptno = 10)
			AND deptno <> 10
子查询当做临时表（重要）

把子查询当作临时表可以解决很多复杂的查询

例题: 查询ecshop中各个类别中，价格最高的商品


-- 找到ecshop里面需要的属性
SELECT goods_id, temp.cat_id, goods_name, max_price
FROM (SELECT cat_id, MAX(shop_price) max_price
FROM ecs_goods
GROUP BY cat_id
) temp , ecs_goods
WHERE temp.max_price = ecs_goods.shop_price 
AND temp.cat_id = ecs_goods.cat_id

结果:

多行子查询中ALL 和ANY
-- ：显示工资比培部门30的所有员工的工资高的员工的姓名、工资和部门号
SELECT * FROM emp

SELECT ename, sal, deptno
FROM emp 
WHERE sal > ALL (SELECT sal FROM emp WHERE deptno = 30)

-- 就是大于最大工资
SELECT ename, sal, deptno 
FROM emp
WHERE sal > (SELECT MAX(sal) FROM emp WHERE deptno = 30)

-- -- 子查询any和all


-- ：显示工资比培部门30的所有员工的工资高的员工的姓名、工资和部门号
SELECT * FROM emp

SELECT ename, sal, deptno
FROM emp 
WHERE sal > ALL (SELECT sal FROM emp WHERE deptno = 30)

-- 就是大于最大工资
SELECT ename, sal, deptno 
FROM emp
WHERE sal > (SELECT MAX(sal) FROM emp WHERE deptno = 30)

-- 请思考：显示工资比培部门30的所有员工的工资低的员工的姓名、工资和部门号

SELECT ename, sal, deptno 
FROM emp
WHERE sal > ANY(SELECT sal FROM emp WHERE deptno = 30)

SELECT ename, sal, deptno 
FROM emp
WHERE sal > (SELECT MIN(sal) FROM emp WHERE deptno = 30)
多列子查询

-- 请思考如何查询与smith的部门和岗位完全相同的所有雇员（并且不含smith本人）

-- smith所在的部门和岗位

SELECT job, deptno 
FROM emp
WHERE ename = 'SMITH'

--找满足需求的这个人
SELECT * 
FROM emp
WHERE (job, deptno) = (SELECT job, deptno 
FROM emp
WHERE ename = 'SMITH') 
AND ename <> 'SMITH'

-- 练习 和宋江各科成绩都一样的学生 student表
SELECT * 
FROM student
WHERE (chinese, math, english) = (SELECT chinese, math, english
FROM student
WHERE `name` = '宋江')
AND `name` <> '宋江'


SELECT chinese, math, english
FROM student
WHERE `name` = '宋江'

INSERT INTO student (`name` , chinese, math, english) 
VALUES ('张三', 87, 77, 78)

SELECT * FROM student
练习
-- 查找每个部门工资高于本部门平均工资的人的资料

SELECT * FROM emp -- 就是这个表

SELECT deptno, avg(sal) AS "平均工资"
FROM emp
GROUP BY deptno

SELECT ename, sal, avg_sal.sall, emp.deptno
FROM emp , (SELECT deptno, avg(sal) sall
FROM emp
GROUP BY deptno) avg_sal
WHERE avg_sal.deptno = emp.deptno
AND emp.sal > avg_sal.sall

-- 查找每个部门工资最高的人的详细资料

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

SELECT ename, sal
FROM emp, (SELECT deptno, MAX(sal) maxsal
FROM emp
GROUP BY deptno) temp
WHERE emp.deptno = temp.deptno
AND emp.sal = temp.maxsal




-- 显示每个部门的信息（包括：部门名，编号，地址）和人员数量

SELECT dept.deptno, dname, loc, number
FROM dept , (SELECT deptno, FORMAT(SUM(deptno) / deptno ,0) AS number
FROM emp
GROUP BY deptno) temp
WHERE dept.deptno = temp.deptno

-- 如何找出每个部门的人数量
SELECT deptno, FORMAT(SUM(deptno) / deptno ,0) AS number
FROM emp
GROUP BY deptno

-- 每一个行代表一个人
SELECT deptno, COUNT(*)
FROM emp
GROUP BY deptno
leikooo
关注
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值