数据库
mysql5.5 mysql5.6 mysql5.7(稳定) mysql8 更高版本
-
使用命令行窗口连接MYSQL数据库
-
- mysql -h 主机名 -P端口 -u用户名 -p密码
- 登陆前，保证服务启动
- 请务必安装VisualC++2013运行库
- 请务必 在my.ini使用双斜杠描述路径
- 请务必确认命令是否有差错
-
连接到Mysql服务（Mysql数据库）的指令
-
-
mysql -h 主机IP -P 端口 -u 用户名 -p密码
-
-p密码不用有空格
-
-p后面没有写密码，回车会要求输入密码
-
如果没有写-h 主机，默认就是本机
-
如果没有写-P 端口，默认就是3306
-
注意点：
- 破解Navicat时首先不要打开Navicat，获取到注册码之后再打开，如果注册码输入进去出现红色叉叉，就在用注册机破解时的product选择为MySQL
- 进入Navicat，建立连接前要打开mysql不然会出现错误Can’'t connect to MySQL server on localhost (10061)
- 如果my.ini中没有skip-grant-tables或者被注释掉，则在建立连接时一定要输入密码，不然会出现错误Access denied for user [‘root’@‘localhost’](mailto:’ rel=)
数据库三层结构
- 所谓安装Mysql数据库，就是在主机安装一个数据库管理系统（DBMS），这个管理程序可以管理多个数据库。database manage system
- 一个数据库中可以创建多个表，以保存数据（信息）
- 数据库管理系统（DBMS）、数据库和表的关系如图所示：
SQL语句分类
- DDL：数据定义语句 [create 表，库…]
- DML：数据操作语句 [增加 insert，修改 update，删除 delete]
- DQL: 数据查询语句 [select]
- DCL: 数据控制语句 [管理数据库：比如用户权限 grant revoke]
创建数据库
CREATE DATABASE [IF NOT EXISTS] db_name
[create_specification [, create_specification] ...]
create_specification:
[DEFAULT] CHARACTER SET charset_name
| [DEFAULT] COLLATE collation_name
# CHARACTER SET:指定数据库采用的字符集，如果不指定字符集，默认为utf8
# COLLATE:指定数据库字符集的校对规则（常用的 utf8_bin【区分大小写】、utf8_general_ci【不区分大小写】 注意默认是utf8_general_ci）
#使用指令创建数据库
CREATE DATABASE hsp_db01;
#删除数据库
DROP DATABASE hsp_db01;
#创建一个使用utf8字符集的hsp_db02数据库
CREATE DATABASE hsp_db02 CHARACTER SET utf8
#创建一个使用utf8字符集，并带校对规则的hsp_db03数据库
CREATE DATABASE hsp_db03 CHARACTER SET utf8 COLLATE utf8_bin
查看、删除数据库
#显示数据库语句：
SHOW DATABASES
#显示数据库创建语句
SHOW CREATE DATABASE db_name
#数据库删除语句[一定要慎用]
DROP DATABASE [IF EXISTS] db_name
#在创建数据库，表的时候，为了规避关键字，可以使用反引号解决
SHOW DATABASES
SHOW CREATE DATABASE `hsp_db02`#使用反引号更安全，不过不用也没关系，只要数据库的名字不是关键字
备份、恢复数据库
- 备份数据库（注意：在DOS执行）命令行，mysqldump指令其实在mysql安装目录\bin
-
- mysqldump -u 用户名 -p -B 数据库1 数据库2 数据库n > 文件名.sql
- 恢复数据库（注意：进入Mysql命令行再执行）
-
- Source 文件名.sql
- 备份库的表
-
- mysqldump -u 用户名 -p密码 数据库 表1 表2 表n > d:\\文件名.sql
- 这里在恢复表时现在Mysql命令行用use 数据库选中数据库后再进行恢复
#CMD
mysqldump -u root -phsp -B hsp_db02 hsp_db03 > d:\\bak.sql
#Sqlyog
DROP DATABASE hsp_db02;
DROP DATABASE hsp_db03;
#CMD
mysql -u root -phsp
source d:\\bak.sql
#第二种方式是将生成的bak.sql内的内容复制到sql.yog中执行一遍
创建表
CREATE TABLE table_name
(
field1 datatype,
field2 datatype,
field3 datatype
)character set 字符集 collate 校对规则 engine 存储引擎
field:指定列名 datatype:指定列类型（字段类型）
character set:如不指定则为所在数据库字符集
collate:如不指定则为所在数据库校对规则
engine:引擎
- 注意：创建表时，要根据需保存的数据创建相应的列，并根据数据的类型定义相应的列类型。
CREATE TABLE t3
(
id INT,
`name` VARCHAR(255),
`password` VARCHAR(255),
birthday DATE
)CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB;
Mysql常用数据类型（列类型）
数值型（整数）的基本使用
- 使用规范：在能够满足需求的情况下，尽量选择占用空间小的类型。
- 如果没有指定unsigned，则该类型就是有符号
-
- create table t10 (id tinyint);//默认是有符号的
- create table t10 (id tinyint unsigned);//无符号的
数值型（bit）的使用
- 基本使用
-
- mysql> create table t02 (num bit(8));
- mysql> insert into t02 (1,3);//有问题
- mysql> insert into t02 values(2,65);//有问题
- 细节说明
-
- bit字段显示时，按照 位的方式显示
- 查询的时候仍然可以用使用添加的数值
- 如果一个值只有0，1可以考虑使用bit(1)，可以节约空间
- 位类型。M指定位数，默认值1，范围1-64
- 使用不多
数值型（小数）的基本使用
- FLOAT/DOUBLE [UNSIGNED]
-
- Float 单精度，Double 双精度
- DECIMAL[M,D] [UNSIGNED]
-
- 可以支持更加精确的小数位。M是小数位数（精度）的总数，D是小数点（标度）后面的位数。
- 如果D是0，则值没有小数点或分数部分。M最大65。D最大是30。如果D被省略，默认是0。如果M被省略，默认是10。
- 建议：如果希望小数的精度高，推荐使用decimal。
CREATE TABLE t5(
num1 FLOAT,
num2 DOUBLE,
num3 DECIMAL(30,20));
INSERT INTO t5 VALUES(88.12345678945678,88.12345678945678,88.12345678945678);
SELECT * FROM t5;
字符串的基本使用
- CHAR(size)-------固定长度字符串 最大255字符
- VARCHAR(size)-------可变长度字符串 最大65532字节【utf8编码最大21844字符 1-3个字节用于记录大小】size =(65535-3)/3=21844
- 如果表的编码是GBK，size =(65535-3)/2=32766
#注释的快捷键 shift + ctrl + c
#取消注释 shift + ctrl + r
CREATE TABLE t6 (
`name` CHAR(255));
CREATE TABLE t7 (
`name` CHAR(256));#错误
CREATE TABLE t8(
`name` VARCHAR(65535));#错误
CREATE TABLE t9(
`name` VARCHAR(21845));#错误
CREATE TABLE t10(
`name` VARCHAR(21844));
CREATE TABLE t11(
`name` VARCHAR(32766)) CHARSET gbk;
使用细节
- char(4) //这个4表示字符数（最大255），不是字节数，不管是中文还是字母都是放四个，按字符计算。
- varchar(4) //这个4表示字符数，不管是字母还是中文都以定义好的表的编码来存放数据。
- 不管是中文还是英文字母，都是最多存放4个，是按照字符来存放的。
CREATE TABLE t12(
`name` CHAR(4));
INSERT INTO t12 VALUES('abcde');#错误
INSERT INTO t12 VALUES('abcd');
INSERT INTO t12 VALUES('自知之明');
SELECT * FROM t12;
CREATE TABLE t13(
`name` VARCHAR(4));
INSERT INTO t13 VALUES('abcde');#错误
INSERT INTO t13 VALUES('abcd');
INSERT INTO t13 VALUES('自知之明');
INSERT INTO t13 VALUES('自知之明a');#错误
INSERT INTO t13 VALUES('自知ab');
SELECT * FROM t13;
- char(4)是定长（固定的大小），就是说，即使你插入 ‘aa’ ，也会占用分配的4个字符的空间。
- varchar(4)是变长，就是说，如果你插入了 ‘aa’ ，实际占用空间大小并不是4个字符，而是按照实际占用空间来分配。（varchar 本身还需要占1-3个字节来记录存放内容长度）
- 什么时候使用char，什么时候使用varchar
- 如果数据是定长，推荐使用char，比如md5的密码，邮编，手机号，身份证号码等。char(32)
- 如果一个字段的长度是不确定，我们使用varchar，比如留言，文章等
- 查询速度：char > varchar
- 在存放文本时，也可以使用Text数据类型，可以将TEXT列视为VARCHAR列，注意Text不能有默认值，大小0-2^16字节
- 如果希望存放更多字符，可以选择MEDIUMTEXT 0-2^24 或者 LONGTEXT 0-2^32
日期类型的基本使用
CREATE TABLE birthday6(
t1 DATE,t2 DATETIME,t3 TIMESTAMP
NOT NULL DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP);#timestamp时间戳
INSERT INTO birthday6 (t1,t2) VALUES('2022-11-11','2022-11-11 10:10:10');
SELECT * FROM birthday6;
#时间戳在没有插入的时候用当前时间插入，或者更新的时候用当前的时间更新
- 日期类型的细节说明
-
- TimeStamp 在Insert 和update时自动更新。
CREATE TABLE employees(
Id INT,
`name` VARCHAR(32),
sex CHAR(1),
birthday DATE,
entry_date DATETIME,
job VARCHAR(32),
Salary DOUBLE,
`resume` TEXT) CHARSET utf8 COLLATE utf8_bin ENGINE INNODB;
DROP TABLE employees;
SELECT * FROM employees;
INSERT INTO employees VALUES(
1,'Jack','男','1998-07-12','2019-01-18 14:32:33',
'工程师',3000.55,'这个人不错哦');
修改表
- 使用ALTER TABLE 语句追加，修改，或删除列的语法。
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 字符集；
DESC employees;
#员工表上增加一个image列，varchar类型，要求在resume后面
ALTER TABLE employees
ADD image VARCHAR(64) NOT NULL DEFAULT ''
AFTER RESUME;
#修改job列,使其长度为60
ALTER TABLE employees
MODIFY job VARCHAR(60) NOT NULL DEFAULT '';
#删除sex列
ALTER TABLE employees
DROP sex;
#表名改为emps
RENAME TABLE employees TO emps;
DESC emps;
#修改表的字符集为utf8
ALTER TABLE emps CHARACTER SET utf8;
#列名name修改为user_name
ALTER TABLE emps
CHANGE `name` user_name VARCHAR(32) NOT NULL DEFAULT '';
CRUD 增删查改
Insert
- 使用INSERT语句向表中插入数据
- INSERT INTO table_name [(column [, column…])]
- VALUES (value [, value…]);
CREATE TABLE goods (
id INT,
goods_name VARCHAR(10),
price DOUBLE);
INSERT INTO goods VALUES(1001,'手机',3888);
INSERT INTO goods VALUES(1002,"耳机",99.99);
SELECT * FROM goods;
细节说明
- 插入的数据应与字段的数据类型相同。比如 把‘abc’ 添加到 int 类型会错误。
- 数据的长度应在列的规定范围内，例如：不能将一个长度为80的字符串加入到长度为40的列中。
- 在values中列出的数据位置必须与被加入的列的排列位置相对应。
- 字符和日期型数据应包含在单引号中。
- 列可以插入空值【前提是该字段允许为空】，insert into table values(null)
- insert into tab_name (列名…) values (),(),() 形式添加多条记录。
- 如果是给表中的所有字段添加数据，可以不写前面的字段名称。
- 默认值的使用，当不给某个字段值时，如果有默认值就会添加，否则报错。
CREATE TABLE goods (
id INT,
goods_name VARCHAR(10),
price DOUBLE);
INSERT INTO goods VALUES(1001,'手机',3888);
INSERT INTO goods VALUES(1002,"耳机",99.99);
INSERT INTO goods (goods_name, id, price)
VALUES ('电池', 1003, 99.88);
SELECT * FROM goods;
INSERT INTO goods (goods_name, id, price)
VALUES ('电池', '8888', 99.88);#可以，系统可以把字符串1003转成整数型1003
INSERT INTO goods (goods_name, id, price)
VALUES ('电池', '66.66', 99.88);#可以，系统四舍五入了，66.66-》67
INSERT INTO goods (goods_name, id, price)
VALUES ('电池', 'abc', 99.88);#错误，系统转型时出错
INSERT INTO goods (goods_name, id, price)
VALUES ('电池吃吃吃次吃初次迟迟此处此', '9999', 99.88);#错误，good_names超长
INSERT INTO goods (goods_name, id, price)
VALUES (电池, '9999', 99.88);#错误，字符和日期型数据应包含在单引号中
INSERT INTO goods (goods_name, id, price)
VALUES (NULL,NULL,NULL);#可以，创建表时允许为空,null和列相对应
INSERT INTO goods (goods_name, id, price)
VALUES ('电池1', '9999', 99.88),('电池2', '2222', 199.88);#可以，一次加入多条记录
INSERT INTO goods
VALUES ('1003','无列名', 99.88);#可以，数据完全填充，不必写列名，但要和建表时顺序相同
INSERT INTO goods (id, goods_name)
VALUE(3333, '充电宝');#成功，price被添加为空值，因为它没有被指定为 not null
#如果我们希望指定某个列的默认值，就在创建表的时候指定
CREATE TABLE goods2 (
id INT,
goods_name VARCHAR(10),
price DOUBLE NOT NULL DEFAULT 100);
INSERT INTO goods2 (id, goods_name)
VALUES(4444,'美瞳');
SELECT * FROM goods2;
update
- 使用update语句修改表中数据
- UPDATE tb1_name
- SET col_name1 = expr1 [, col_name2 = expr2 …]
- [WHERE where_definition]
CREATE TABLE employees(
Id INT,
`name` VARCHAR(32),
sex CHAR(1),
birthday DATE,
entry_date DATETIME,
job VARCHAR(32),
Salary DOUBLE,
`resume` TEXT) CHARSET utf8 COLLATE utf8_bin ENGINE INNODB;
INSERT INTO employees
VALUES(1001,'小妖怪','男','1657-11-11',
'1859-12-4 13:13:11','寻山',3000,'大王叫我来巡山');
INSERT INTO employees
VALUES(1002,'孙悟空','男','1257-11-11',
'1522-12-4 13:13:11','保镖',8000,'保护师傅');
SELECT * FROM employees;
#将所有员工薪水修改为5000元，没有带where，会修改所有的记录，慎用
UPDATE employees
SET Salary = 5000;
#将姓名为小妖怪的员工的薪水修改为300元
UPDATE employees
SET Salary = 3000
WHERE `name`='小妖怪';
#将孙悟空的薪水在原有基础上增加1000元
UPDATE employees
SET Salary = Salary + 1000
WHERE `name` = '孙悟空';
使用细节
- UPDATE语法可以用新值更新原有表行中的各列；
- SET子句指示要修改哪些列和要给予哪些值；
- WHERE子句指定应更新哪些行。如没有WHERE子句，则更新所有的行；
- 如果需要修改多个字段，可以通过set 字段1 = 值1,字段2 = 值2…
delete
- 使用detele语句删除表中的数据。
- delete from tb1_name
- [WHERE where_definition]
#删除名称为孙悟空的记录
DELETE FROM employees
WHERE `name` = '孙悟空';
#删除所有数据
DELETE FROM employees;
使用细节
- 如果不使用where子句，将删除表中的所有数据。
- Delete语句不能删除某一列的值（可使用update 设为null 或者’'）
- 使用delete语句仅删除记录，不删除表本身。如要删除表，使用drop table语句。drop table 表名；
select
- SELECT [DISTINCT] * | {column1, column2, column3…}
- FROM tablename;
注意事项
- select 指定查询哪些列的数据；
- column指定列名；
- *号代表查询所有列；
- From指定查询哪张表；
- DISTINCT可选，指显示结果时，是否去掉重复数据。
CREATE TABLE student(
id INT NOT NULL DEFAULT 1,
NAME VARCHAR(20) NOT NULL DEFAULT '',
chinese FLOAT NOT NULL DEFAULT 0.0,
english FLOAT NOT NULL DEFAULT 0.0,
math FLOAT NOT NULL DEFAULT 0.0
);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(1,'韩顺平',89,78,90);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(2,'张飞',67,98,56);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(3,'宋江',87,78,77);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(4,'关羽',88,98,90);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(5,'赵云',82,84,67);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(6,'欧阳锋',55,85,45);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(7,'黄蓉',75,65,30);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(5,'赵云',82,84,67);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(1,'韩顺平',89,78,90);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(7,'黄蓉',75,65,30);
#查询表中所有学生的信息
SELECT * FROM student;
#查询表中所有学生的姓名和对应的英语成绩
SELECT NAME,english FROM student;
#过滤表中重复数据distinct
SELECT DISTINCT * FROM student;
#要查询的记录，每个字段都相同，才会去重
SELECT DISTINCT english FROM student;
使用表达式对查询的列进行运算
- SELECT * | {column1 | expression, column2 | expression, …}
- FROM tablename;
- 在select语句中可使用as语句
- SELECT columnname as 别名 from 表名；
#统计每个学生的总分
SELECT chinese + english + math
FROM student;
#在所有学生总分加10分的情况
SELECT chinese + english + math + 10
FROM student;
#使用别名表示学生分数
SELECT `name` AS '名字', (chinese + english + math) AS '总分' FROM student;
在where子句中经常使用的运算符(过滤查询)
#查询姓名为赵云的学生成绩
SELECT `name`,chinese,english,math FROM student
WHERE `name` = '赵云';
#查询英语成绩大于90分的同学
SELECT `name`,english FROM student
WHERE english > 90;
#查询总分大于200分的所有同学
SELECT `name`,(chinese + english + math) AS total_score FROM student
WHERE chinese + english + math > 200;
#查询math大于60，且id 大于=4的学生成绩
SELECT id,`name`,math FROM student
WHERE id >= 4 AND math > 60;
#查询英语成绩大于语文成绩的同学
SELECT `name`,english,chinese FROM student
WHERE english > chinese;
#查询总分大于200分 并且 数学成绩小于语文成绩的姓韩的学生
SELECT `name`,math,chinese,(chinese + english + math) AS total_score FROM student
WHERE chinese + english + math > 200 AND math < chinese AND `name` LIKE '韩%';
#查询英语分数在80-90之间的同学
SELECT * FROM student
WHERE english BETWEEN 80 AND 90;#相当于english >= 80 and english <= 90;
#查询数学分数为89，90，91的同学
SELECT * FROM student
WHERE math = 89 OR math = 90 OR math = 91;#也可以 math in (89,90,91);
#查询所有姓李的学生成绩
SELECT * FROM student
WHERE `name` LIKE '李%';
#查询数学分>80，语文分>80的同学
SELECT * FROM student
WHERE chinese > 80 AND math > 80;
使用order by子句排序查询结果
-
SELECT column1, column2, column3 …
-
FROM table;
-
order by column asc|desc, …
-
order by 指定排序的列，排序的列既可以是表中的列名，也可以是select 语句后指定的列名。
-
Asc 升序【默认】、Desc 降序
-
ORDER BY子句应位于SELECT语句的结尾。
#对数学成绩排序后输出
SELECT * FROM student
ORDER BY math ASC;#默认是ASC，可以省略不写
#对总分按从高到低的顺序输出（降序）
SELECT `name`, (chinese + english + math) AS total_score FROM student
#where total_score > 200 这样写是不对的
ORDER BY total_score DESC;
#对姓李的学生成绩排序输出（升序）
SELECT * , (chinese + english + math) AS total_score FROM student
WHERE `name` LIKE '李%'
ORDER BY total_score ASC;
函数
合计/统计函数
- count 返回行的总数
- select count(*) | count(列名) from tablename
- [WHERE where_definition]
#统计一个班级共有多少学生
SELECT COUNT(*) FROM student;
#统计数学成绩大于90的学生有多少个
SELECT COUNT(*) FROM student
WHERE math > 90;
#统计总分大于250的人数有多少
SELECT COUNT(*) FROM student
WHERE (chinese + english + math) > 250;
#count(*) 和 count(列名) 的区别
-- 解释：count(*) 返回满足条件的记录的行数
-- count(列名)：统计满足条件的某列有多少个，但是会排除为 null的情况
- sum 函数返回满足where条件的行的和
- select sum(列名) {，sum(列名)…} from tablename
- [WHERE where_definition]
#统计一个班数学总成绩
SELECT SUM(math) FROM student;
#统计一个班数学、语文、英语各科的总成绩
SELECT SUM(math),SUM(english),SUM(chinese) AS chinese_totalscore FROM student;
#统计一个班数学、语文、英语的成绩总和
SELECT SUM(math + english + chinese) FROM student;
#统计一个班级语文成绩平均分
SELECT SUM(chinese)/COUNT(chinese) FROM student;
#注意：sum仅对数值起作用，否则会报错
#注意：对多列求和，","号不能少
- AVG函数返回满足where条件的一列的平均值
- select avg(列名) {，avg(列名)…} from tablename
- [WHERE where_definition]
#求一个班级数学平均分
SELECT AVG(math) FROM student;
#求一个班级总分平均分
SELECT AVG(math + english + chinese) AS avg_score FROM student;
- Max/Min函数返回满足where条件的一列的最大/最小值
- select max(列名) from tablename
- [WHERE where_definition]
#求班级的最高分和最低分
SELECT MAX(math + english + chinese) AS max_score FROM student;
SELECT MIN(math + english + chinese) AS min_score FROM student;
分组统计
-
使用group by 子句对列进行分组-----用于对查询的结果分组统计
-
SELECT column1, column2, column3 …FROM table
-
group by column
-
使用having 子句对分组后的结果进行过滤-----用于限制分组显示结果
-
SELECT column1, column2, column3 …FROM table
-
group by column having …
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');
SELECT * FROM dept;
#创建表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);
SELECT * FROM emp;
#工资级别表
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);
SELECT * FROM salgrade;
#如何显示每个部门的平均工资和最高工资
SELECT AVG(sal),MAX(sal),deptno FROM emp
GROUP BY deptno;
#显示每个部门的每种岗位的平均工资和最低工资
SELECT AVG(sal),MIN(sal),deptno,job FROM emp
GROUP BY deptno,job;
#显示平均工资低于2000的部门号和它的平均工资
SELECT AVG(sal),deptno FROM emp
GROUP BY deptno
HAVING AVG(sal) < 2000;
SELECT AVG(sal) AS avg_sal,deptno FROM emp
GROUP BY deptno
HAVING avg_sal < 2000;#效率更高，要重新计算
字符串相关函数
SELECT CHARSET(ename) FROM emp;
SELECT CONCAT(ename, " 工作是", job) FROM emp;
#dual 亚元表，系统表 可以作为测试表使用
SELECT INSTR('hanshunping','ping') FROM DUAL;
SELECT UCASE(ename) FROM emp;
SELECT LCASE(ename) FROM emp;
SELECT LEFT(ename,2) FROM emp;
SELECT RIGHT(ename,2) FROM emp;
SELECT LENGTH(ename) FROM emp;
SELECT ename,REPLACE(job,'MANAGER','经理') FROM emp;
SELECT STRCMP('hsp','jsp') FROM DUAL;
SELECT SUBSTRING(ename,1,2) FROM emp;
SELECT LTRIM(' 韩顺平教育') FROM DUAL;
SELECT RTRIM('韩顺平教育 ') FROM DUAL;
SELECT TRIM(' 韩顺平教育 ') FROM DUAL;
#以首字母小写的方式显示所有员工emp表的姓名
SELECT CONCAT(LCASE(LEFT(ename,1)),SUBSTRING(ename,2)) FROM emp;
数学相关函数
SELECT ABS(-10) FROM DUAL;#10
SELECT BIN(55) FROM DUAL;#110111
SELECT CEILING(5.3) FROM DUAL;#6
SELECT CEILING(-2.2) FROM DUAL;#-2
SELECT CONV(39,10,2) FROM DUAL;#100111
SELECT CONV('A',16,10) FROM DUAL;#10
SELECT FLOOR(5.3) FROM DUAL;#5
SELECT FLOOR(-2.2) FROM DUAL;#-3
SELECT FORMAT(32.4567788,4) FROM DUAL;#32.4568 四舍五入
SELECT HEX(55) FROM DUAL;#37
SELECT LEAST(34,54,22,69) FROM DUAL;#22
SELECT MOD(45,4) FROM DUAL;#1
SELECT RAND() FROM DUAL;# 0.0<=v<=1.0
SELECT RAND(3) FROM DUAL;#产生固定的随机数
时间日期相关函数
SELECT CURRENT_DATE() FROM DUAL;
SELECT CURRENT_TIME() FROM DUAL;
SELECT CURRENT_TIMESTAMP() FROM DUAL;
CREATE TABLE mes(
id INT,
content VARCHAR(30),
send_time DATETIME);
SELECT * FROM mes;
INSERT INTO mes
VALUES(1,'北京新闻',CURRENT_TIMESTAMP());
INSERT INTO mes
VALUES(2,'上海新闻',NOW());
INSERT INTO mes
VALUES(3,'天津新闻',NOW());
SELECT id,content,DATE(send_time) FROM mes;
#查询10分钟内的mes
SELECT * FROM mes
WHERE DATE_ADD(send_time,INTERVAL 10 MINUTE) >= NOW();
SELECT * FROM mes
WHERE DATE_SUB(NOW(),INTERVAL 10 MINUTE) <= send_time;
SELECT * FROM mes
WHERE send_time >= DATE_SUB(NOW(),INTERVAL 10 MINUTE);
#计算两个日期相差多少天
SELECT DATEDIFF('2011-11-11','1990-01-11') FROM DUAL;
SELECT DATE('1998-5-20 10:10:10') FROM DUAL;
SELECT DATEDIFF('1998-5-18', NOW()) FROM DUAL;
SELECT DATEDIFF(DATE_ADD('1998-5-18',INTERVAL 80 YEAR),NOW()) FROM DUAL;
SELECT DATEDIFF('2078-5-18', NOW()) FROM DUAL;
SELECT TIMEDIFF('2011-11-11 19:01:01','1990-01-11 19:01:01') FROM DUAL;
#UNIX_TIMESTAMP()返回的是1970-1-1 00:00:00到现在的秒数
SELECT UNIX_TIMESTAMP() FROM DUAL;
#FROM_UNIXTIME():可以把一个unix_timestamp 秒数，转成指定格式的日期
#%Y-%m-%d %H:%i:%s是规定好的，表示年月日时分秒
#意义：在开发中，可以存放一个整数，然后表示时间，通过FROM_UNIXTIME转换
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP()) FROM DUAL;
SELECT FROM_UNIXTIME(1618483484, '%Y-%m-%d %H:%i:%s') FROM DUAL;
- DATE_ADD(）中的interval 后面可以是 year minute second hour day 等
- DATE_SUB(）中的interval 后面可以是 year minute second hour day 等
- DATEDIFF(date1,date2)得到的是天数，而且是date1 - date2 的天数，因此可以取负数
- 这四个函数的日期类型可以是date datetime 或者 datestamp
加密函数
#USER() 查询用户
-- 可以查看登陆到mysql的有哪些用户，以及登陆的IP
SELECT USER() FROM DUAL; -- 用户@IP地址
#DATABASE() 查询当前使用数据库名称
SELECT DATABASE();
#MD5(str) 为字符串算出一个MD5 32的字符串,常用（用户密码）加密
-- root 密码是hsp -> 加密MD5 -> 在数据库中存放的是加密后的密码
SELECT MD5('hsp');-- 不管明文密码有多长，经过MD5加密后都是32位
-- 如果存放密码时用MD5加密，在查询时要写成password = MD5('hsp')
#PASSWORD(str) 从原文密码str计算并返回密码字符串，
#通常用于对mysql数据库的用户密码加密
#select * from mysql.user \G
-- MySQL数据库的用户密码就是PASSWORD函数加密
SELECT PASSWORD('hsp') FROM DUAL;
# 从原文密码str计算并返回密码字符串
# 通常用于对mysql数据库的用户密码加密
# mysql.user 表示 数据库.表
SELECT * FROM mysql.user;
流程控制函数
SELECT IF(TRUE,'北京','上海') FROM DUAL;-- 北京
SELECT IFNULL(NULL,'beijing') FROM DUAL;-- beijing
SELECT IFNULL('shanghai','beijing') FROM DUAL; -- shanghai
SELECT CASE WHEN TRUE THEN 'marry'
WHEN TRUE THEN 'tom' ELSE 'dog'
END; -- marry
SELECT CASE WHEN FALSE
THEN 'marry' END; -- NUll
SELECT ename, IF(comm IS NULL, 0.0, comm)
FROM emp;
SELECT ename, IFNULL(comm, 0.0)
FROM emp;
SELECT ename, (SELECT CASE
WHEN job = 'CLERK' THEN '职员'
WHEN job = 'MANAGER' THEN '经理'
WHEN job = 'SALESMAN' THEN '销售人员'
ELSE job END) AS 'job',job
FROM emp;
查询
查询增强
-- 使用where子句
-- 如何查找1992.1.1后入职的员工
-- 在mysql中，日期类型可以直接比较，但是要注意格式
SELECT * FROM emp
WHERE DATEDIFF(hiredate, '1992-01-01') > 0;
SELECT * FROM emp
WHERE hiredate > '1992-01-01';
-- 如何使用like操作符
-- %:表示0到多个字符 _:表示单个字符
-- 如何显示首字符为S的员工姓名和工资
SELECT ename, sal FROM emp
WHERE ename LIKE 'S%';
-- 如何显示第三个字符为大写O的所有员工的姓名和工资
SELECT ename, sal FROM emp
WHERE ename LIKE '__O%';
-- 如何显示没有上级的雇员的情况
SELECT * FROM emp
WHERE mgr IS NULL;
-- 查询表结构
DESC emp;
-- 使用order by子句
-- 如何按照工资的从低到高的顺序显示雇员信息
SELECT * FROM emp
ORDER BY sal;
-- 按照部门号升序而雇员的工资降序排列，显示雇员信息
SELECT * FROM emp
ORDER BY deptno ASC,sal DESC;
分页查询
- 按雇员的id号升序取出，每页显示3条记录，请分别显示第一页，第二页，第三页
- 基本语法：select … limit start, rows
- 表示从start + 1行开始取，取出rows 行，start 从0开始计算
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;
#每页显示的记录数 * （页数 - 1），每页显示的记录数
分组增强
- 使用分组函数和分组子句group by
#显示每种岗位的雇员总数、平均工资
SELECT job,COUNT(job),AVG(sal) FROM emp
GROUP BY job;
#显示雇员总数，以及获得补助的雇员数
SELECT COUNT(ename),COUNT(comm) FROM emp;
#统计没有获得补助的人员
SELECT COUNT(ename),COUNT(IF(comm IS NULL,1,NULL)) FROM emp;
#显示管理者的总人数
SELECT COUNT(job) FROM emp
WHERE job = 'MANAGER' OR job = 'PRESIDENT';
SELECT COUNT(DISTINCT mgr) FROM emp;
#显示雇员工资的最大差额
SELECT MAX(sal) - MIN(sal) FROM emp;
多子句查询
- 数据分组的总结
-
- 如果select语句同时包含有group by,having,limit,order by 那么他们的顺序是group by,having,order by,limit
#请统计各个部门的平均工资，并且是大于1000的，并且按照平均工资从高到低排序，取出前两行记录
SELECT deptno,AVG(sal) AS avg_sal FROM emp
GROUP BY deptno
HAVING avg_sal > 1000
ORDER BY avg_sal DESC
LIMIT 0,2;
多表笛卡尔集和多表查询
- 多表查询是指基于两个和两个以上的表查询，在实际应用中，查询单个表可能不满足你的需求，需要使用多表。
- 笛卡尔集：第一张表的每一行和第二张表的每一行进行拼接，最终得到表的行数是两表行数的乘积。
-- 显示雇员名，雇员工资及所在部门的名字
-- 多表查询的条件不能少于表的个数-1，否则会出现笛卡儿集
SELECT ename,sal,dname FROM emp,dept
WHERE emp.deptno = dept.deptno;
-- 如何显示部门号为10的部门名、员工名和工资
SELECT dept.deptno,dname,ename,sal FROM emp,dept
WHERE emp.deptno = dept.deptno AND dept.deptno = 10;
-- 显示各个员工的姓名，工资及其工资级别
SELECT ename,sal,grade FROM emp,salgrade
WHERE sal >= losal AND sal <= hisal;
-- 显示雇员名，雇员工资及所在部门的名字，并按部门排序{降序}
SELECT ename,sal,dname FROM emp,dept
WHERE emp.deptno = dept.deptno
ORDER BY dname;
自连接
- 指在同一张表的连接查询【将同一张表看做两张表】
-- 显示公司员工名字和他的上级的名字
SELECT worker.ename AS 'worker', boss.ename AS 'boss'
FROM emp worker, emp boss # 给表取别名
WHERE worker.mgr = boss.empno;
多行子查询
- 子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询
- 单行子查询是指只返回一行数据的子查询语句
- 多行子查询指返回多行数据的子查询 使用关键字in
-- 如何显示与SMITH同一部门的所有员工
SELECT * FROM emp
WHERE deptno = (
SELECT deptno FROM emp
WHERE ename = 'SMITH');
-- 如何查询和部门10的工作相同的雇员的名字、岗位、工资、部门号，但不含10自己的
SELECT ename, job, sal, deptno FROM emp
WHERE job IN (
SELECT DISTINCT job
FROM emp
WHERE deptno = 10
)
AND deptno != 10;
子查询临时表
- 子查询当做临时表使用
SELECT goods_id, cat_id, goods_name, shop_price
FROM ecs_goods;
-- 查询ecshop中各个类别中，价格最高的商品
-- 将下表当作临时表
SELECT cat_id,MAX(shop_price) FROM ecs_goods
GROUP BY cat_id;
SELECT goods_id,ecs_goods.cat_id,goods_name,shop_price
FROM (SELECT cat_id,MAX(shop_price) AS max_price
FROM ecs_goods
GROUP BY cat_id
) temp, ecs_goods
WHERE temp.cat_id = ecs_goods.cat_id
AND temp.max_price = ecs_goods.shop_price;
all和any
-- 如何显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
SELECT ename, sal, deptno FROM emp
WHERE sal > ALL(
SELECT sal FROM emp
WHERE deptno = 30
)
-- 如何显示工资比部门30的其中一个员工的工资高的员工的姓名、工资和部门号
SELECT ename, sal, deptno FROM emp
WHERE sal > ANY(
SELECT sal FROM emp
WHERE deptno = 30
)
多列子查询
- 多列子查序列是指查询返回多个列数据的子查询语句
-- 如何查询与smith的部门和岗位完全相同的所有雇员（并且不含smith本人）
SELECT deptno, job FROM emp
WHERE ename = 'SMITH';
SELECT ename,emp.deptno,emp.job
FROM emp,(SELECT deptno, job FROM emp
WHERE ename = 'SMITH') smith
WHERE emp.deptno = smith.deptno
AND emp.job = smith.job
AND ename <> 'SMITH';
-- -----------------------------
SELECT * FROM emp
WHERE (deptno, job) = (
SELECT deptno, job FROM emp
WHERE ename = 'WARD'
) AND ename <> 'WARD';
-- 查找每个部门工资高于本部门平均工资的人的资料
SELECT deptno, AVG(sal) AS avg_sal FROM emp
GROUP BY deptno
SELECT emp.deptno, ename, sal FROM (
SELECT deptno, AVG(sal) AS avg_sal FROM emp
GROUP BY deptno
)temp, emp
WHERE temp.deptno = emp.deptno
AND sal > avg_sal;
-- 查找每个部门工资最高的人的详细资料
SELECT deptno,MAX(sal) FROM emp
GROUP BY deptno
SELECT emp.deptno, ename, sal FROM (
SELECT deptno,MAX(sal) AS max_sal FROM emp
GROUP BY deptno
)temp, emp
WHERE temp.deptno = emp.deptno
AND sal = max_sal;
-- 显示每个部门的信息（包括：部门名，编号，地址）和人员数量
-- 表.* 表示将该表所有列都显示出来
SELECT deptno,COUNT(ename) FROM emp
GROUP BY deptno
SELECT dname, dept.deptno, loc, total
FROM (
SELECT deptno,COUNT(ename) AS total
FROM emp
GROUP BY deptno) temp, dept
WHERE temp.deptno = dept.deptno;
表复制和去重
CREATE TABLE my_tab01(
id INT,
`name` VARCHAR(32),
sal DOUBLE,
job VARCHAR(32),
deptno INT);
DESC my_tab01
SELECT * FROM my_tab01;
-- 自我复制
-- 先把emp表的记录复制到 my_tab01
INSERT INTO my_tab01
(id,`name`,sal,job,deptno)
SELECT empno,ename,sal,job,deptno FROM emp;
-- 自我复制
INSERT INTO my_tab01
SELECT * FROM my_tab01;
-- 如何去掉一张表的重复记录
-- 下面这句话意思就是将emp表的结构(列)复制到my_tab02
CREATE TABLE my_tab02 LIKE emp;
SELECT * FROM my_tab02;
SELECT * FROM my_temp;
-- 1.构建临时表
CREATE TABLE my_temp LIKE my_tab01;
-- 2.将不重复的记录复制进临时表
INSERT INTO my_temp
SELECT DISTINCT * FROM my_tab01;
-- 3.清除原来表中的记录
DELETE FROM my_tab01;
-- 4.将临时表中的记录插入到表中
INSERT INTO my_tab01
SELECT * FROM my_temp;
-- 5.删除临时表
DROP TABLE my_temp;
合并查询
- 为了合并多个select 语句的结果，可以使用集合操作符号union,union all
- union all
-
- 该操作符用于取得两个结果集的并集。当使用该操作符时，不会取消重复行
- select ename,sal,job from emp where sal > 2500 union all
- select ename,sal,job from emp where job = ‘MANAGER’
- union
-
- 该操作符与union all相似，但是会自动去掉结果集中重复行
- select ename,sal,job from emp where sal > 2500 union
- select ename,sal,job from emp where job = ‘MANAGER’
扫一扫
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
