数据库MySQL一些命令整理（一）

最新推荐文章于 2021-06-27 18:07:27 发布

小鹏头

最新推荐文章于 2021-06-27 18:07:27 发布

阅读量666

点赞数 2

分类专栏：学习笔记文章标签： MySQL 学习笔记数据库必知必会

本文链接：https://blog.csdn.net/q876651731/article/details/88859224

版权

学习笔记专栏收录该内容

7 篇文章 0 订阅

订阅专栏

文章目录

一些命令的一览：
一些值得记录的案例：

一些命令的一览：

对数据库的增删改查：

增数据库

CREATE DATABASE my_database; -- 新建数据库
CREATE DATABASE IF NOT EXISIT my_database;    -- 如果数据库不存在才新建数据库

删数据库

DROP DATABASE IF EXISIT my_database;   -- 如果数据库存在才删除，因为重复删除会报错。
DROP DATABASE my_database;    -- 删除数据库

更改数据库

USE my_database;    -- 切换数据库
ALTER DATABASE my_database DEFAULT CHARACTER SET UTF8;    -- 更改数据库的字符集

查询数据库

SHOW DATABASES;     -- 显示所有数据库
SELECT DATABASE();      -- 显示当前选中的数据库
SHOW CREATE DATABASE my_database;     -- 显示数据库的创建语句以及characterset。

对表的增删改查：

我们能执行选中操作的只有数据库，所以任何命令，都需要显式地指定表名。

新建表

-- 增加一个表
CREATE TABLE  t_student (
    first_name VARCHAR(30),
	second_name VARCHAR(30),
	age INT
);

-- 如果表不存在才增加一个表
CREATE TABLE IF NOT EXIST t_student(
    first_name VARCHAR(30),
	second_name VARCHAR(30),
	age INT
);

-- 可以在新建表的时候就定义后约束
CREATE TABLE IF NOT EXISTS t_students(
	first_name VARCHAR(30),
    second_name VARCHAR(30),
    age INT,
    NOT NULL(first_name,second_name,age)    -- 建议属性都加个NOT NULL约束比较好
);

-- 新增一个类似的表
CREATE TABLE t_newtable LIKE t_oldtable;

-- 使用蠕虫复制来复制表
CREATE TABLE t_newtable LIKE t_oldtable;	-- 先建好一个新的表
INSERT INTO t_newtable (属性值1，属性值2) -- 这里的属性值可以省略
SELECT 属性值1, 属性值2   -- 注意，这里的属性值可以是常量来的。就t_newtable对应的属性永远都插常量。
FROM t_oldtable;
-- t_newtable后面的属性值要跟SELECT后面的属性值一对一对应才可以的。
-- 就像是普通INSERT INTO那样，只是VALUES 变成了SELECT而已。

-- 这个命令适合已经有表存在的情况，如果想新建一个表，建议用
CREATE TABLE t_newtable (
SELECT * 
FROM t_oldtable
WHERE condition);
-- 注意，跟建普通表一样，括号不能省。

修改表的信息

ALTER TABLE t_students RENAME TO t_student;     -- 修改表的命名
ALTER TABLE t_student CHARACTER SET utf8;   -- 把一个表的编码改成指定编码
ALTER TABLE t_student ENGINE='InnoDB';   -- 修改表的引擎，用InnoDB就好了，支持外键和事物。
/*用 SHOW CREATE TABLE t_student 可以看到表的当前引擎*/

查看表的信息

SHOW TABLES; -- 显示选中的数据库(use my_database)的所有表
SHOW CREATE TABLE t_student;   -- 显示表的创建语句

-- 显示表的所有列与列的属性，两者一样的。
DESC t_student;  
SHOW COLUMNS FROM t_student;

删除表

DROP TABLE t_student;          -- 直接删除表
DROP TABLE IF EXISIT t_student;   -- 如果表存在就删除表

对表的列进行操作

ALTER TABLE t_student ADD score INT;  -- 增加表一列
ALTER TABLE t_student CHANGE score address VARCHAR(30) NOT NULL;  -- 修改一个列的名字和类型 
ALTER TABLE t_student DROP scroe; 	 -- 删除一列

--增加一个主键，可以用多个列作为联合主键
ALTER TABLE t_student ADD PRIMARY KEY [AUTO_INCREMENT] (id1 [,id2]);   

-- 删除主键
ALTER TABLE t_student DROP PRIMARY KEY;  -- 因为主键一般只有一个，所以要删除就只需这样。
-- 但是当主键连住AUTO_INCREMENT，就不能用这种方法删除主键了。

 -- 修改自增值的大小
ALTER TABLE t_student AUTO_INCREASE = 100;

-- AUTO_INCREMENT 只能由两种方法设置
1、在创建表的时候就在PRIMARY KEY后面设置
2、用MODIFY把一个列设为PRIMARY KEY的时候设置（注意要先把原来的主键删除了）

-- 增加一个外键列
ALTER TABLE t_student 
ADD CONSTRAINT fk_1 
FOREIGN KEY (classroom_id) 
REFERENCE t_classroom (id);  -- 比较少用，因为java也能做外键操作，而且性能更好。

对表中数据增删改查

我们能选中的只有数据库，不能选中表，所以任何对表的增删改查操作，都需要指定表名。

增

-- 可以只增加一部分
INSERT INTO t_student (valueName_1,valueName_2) 
VALUES (value_1,value2),(value_1,value2),(value_1,value2)...;

--需要全部属性都增加，不增加的要用null
INSERT INTO t_student VALUES (value_1,value_2,value_3...);

删

-- 按条件删除
DELETE FROM t_student WHERE condition1 && condition2;

-- 没有条件删除，一条一条删，auto_increase值会保留
DELETE FROM t_student;

-- 销毁表格，auto_increase值不会保留
TRUNCATE TABLE t_student;

改

-- 按条件更改某一列的数值
UPDATE t_student SET valueName_1 = newVAl,valueName_2 = newVal 
WHERE condition1 && condition2

--全部对所有数值全部改完
UPDATE t_student SET valueName_1 = newVal ,valueName_2= newVal;

查

--查询表所有数据
SELECT * FROM t_student;

-- 查询指定列
SELECT valName_1 , valName_2 FROM t_student;

-- 别名查询，加上别名方便观看和处理查询到的数据
SELECT valName_1 otherName, valName_2 anotherName FROM t_student;
 
-- 清除重复值查询。
SELECT DISTINCT valName_1,valName_2 FROM t_student;

-- 查询结果参与运算：要求参与运算的值都是整数才行
	-- 某列数据与固定值运算
SELECT valName_1 + 20 FROM t_student;
	
	-- 多列数据之间运算
SELECT price * count TotalPrice FROM t_student;
	
-- 条件查询
SELECT * FROM t_student WHERE id>3 && id<10;

-- 查询后排序
SELECT * FROM t_student ORDER BY score [DESC / ASC]  -- 默认是升序，ASC，降序需要设置。

-- 分页查询
SELECT * FROM t_stduent LIMIT beginIndex,pageSize;  
/*
分页查询分为逻辑分页和物理分页，逻辑分页就是一次过把东西都拿出来，在JAVA中模拟分页效果，内存占用比较大，但是速度就很快，物理分页就是真实地从MYSQL中拿出指定位置和长度的数据，内存占用比较低， 但是速度慢一点。
*/

-- 当只需要前面几个的时候，可以用LIMIT来限制生成数据的数量
SELECT b.s_id 学号, s_name 姓名, s_birth 生日, s_sex 性别, c_name 科目, s_score 成绩
FROM t_scores a,t_students b,t_courses c
WHERE a.s_id = b.s_id && c.c_id = a.c_id
ORDER BY a.s_score DESC
LIMIT 5;

一些DDL命令：

-- 创建用户名
CREATE USER 'username'@'主机名' IDENTIFIED BY '密码';

-- 删除用户
DROP USER 'username'@'主机名'

IDENTIFIED BY '密码’是可以不要的，就等于用户不需要输入密码就能登录。
主机名用于指定该用户从哪个主机上登录，如果只有本地能用这个用户，那就把主机名设置为’localhost’，如果想让任意远程主机登录，可以把主机名设为’%’。
数据库是以"用户名"+"主机名"来分别用户的，同一个用户名用不同的主机名，也算是不同的用户。
所有用户信息都存放在mysql数据库下的user 表中，密码是加密过的。
要登录有删除用户权限的用户才能删除用户。所以不能自己删除自己

-- 给用户赋予权限
GRANT 权限1,权限2,权限3,权限4 ON database_name.table_name TO 'username'@'主机名'

-- 收回用户的权限
REVOKE 权限1，权限2，权限3 ON database_name.table_name FROM 'username'@'主机名'

-- 查看权限
SHOW　GRANTS FOR 'username'@'主机名'

可以用ALL来代替各种权限。
数据库和表名定义了权限在哪个数据库的哪个表可用，用*.* 可以表示所有数据库的所有表。

-- 修改普通用户密码：
(登录有权限的用户状态下)SET PASSWORD FOR 'username'@'主机名'='new password';

-- 修改管理员密码：
(不必登录)mysqladmin -uroot -p password;  -- 然后按照提示操作即可

mysqladmin是一个程序。password是传给这个程序的命令。

运用命令的方法

一些条件语句

在WHERE后面可以放这些语句：

-- BETWEEN ... AND...  不同数据库处理方式不一样，MySQL是左右都是闭区间
SELECT * FROM t_student WHERE score BETWEEN 80 AND 100; 
-- 当然也可以配合NOT来使用

-- IN运算符：查询列的值是否在指定的集合中。精确查找指定的。
SELECT * FROM t_student WHERE id IN (1,4,5,10);

-- 空值判断： IS NULL
SELECT * FROM t_stdent WHERE score IS NULL;  -- 查找成绩为空的人。

-- 模糊查询：LIKE,只有LIKE条件才能用通配符！等号或者不等号不能用通配符
SELECT * FROM t_student WHERE name LIKE '王_%';
-- 其中'_'表示一个任意字符，
-- '%'表示0个或多个任意字符，

-- 住址不包含'lon'三个字母的人，这时不能用'!'来表示取反了
SELECT * FROM Persons
WHERE City NOT LIKE '%lon%'

-- 经典的判断
SELECT * FROM t_student WHERE score >= 80 && name LIKE '王_%' OR id IN (2,4,6);

还有一些常用的比较运算符：

符号	含义
=	等于
>	大于
>=	大于或等于
<	小于
<=	小于或等于
!=	不等于

一些聚合函数

数据库也是可以用函数的，叫聚合函数，常用的聚合函数如下：

-- 把一些属性连接成字符串返回
SELECT CONCAT(productName,'的售价为：',salePrice*cutoff) detail FROM table_name
COUNT(*); --统计计算最大值
COUNT(val_name); -- 统计某列的行数，不包括null值。
COUNT(DISTINCT val_name); --统计某列不重复的行数。
MIN(val_name);--统计计算最小值
MAX(val_name);-- 统计计算最大值
SUM(val_name);-- 统计计算求和
AVG(val_name);-- 统计计算求平均
GROUPCONCAT(val_name); -- 把表的值聚合成一条数据

FIRST(val_name); -- 返回第一个值，可以配合ORDER BY来获取值。
LAST(val_name); --返回最后一个值，也可以配合ORDER BY
-- 这两个方法跟MAX那些不同的地方就是，ORDER是可以考虑很多前提的，而MAX则只能处理1个列。

SELECT AVG(val_name) FROM table_name;
SELECT COUNT(*) FROM table_name;   -- 在JAVA中必须用long来接受结果。
SELECT SUM(val_name) FROM table_name WHERE dir_id IN(2，3）;
-- 这些函数都是返回一个数值，除了放在SELECT后面获得返回的表，还可以直接用来做HAVING后面做比较的，用来跟其他数据比较的：

-- 找到订单总价小于2000的客户。
SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
HAVING SUM(OrderPrice)<2000  -- 因为聚合函数不能放在WHERE后面，所以当聚合函数作为条件时，就要用到HAVING代替。HAVING是专门用来处理GROUP BY分裂出来的表的。

-- 找客户 "Bush" 或 "Adams" 拥有超过 1500 的订单总金额。
SELECT Customer,SUM(OrderPrice) FROM Orders
WHERE Customer='Bush' OR Customer='Adams'
GROUP BY Customer
HAVING SUM(OrderPrice)>1500
-- 要注意跟VIEW区分开，VIEW是放在FROM后面的。

一个查询语句中，只要**SELECT**后面跟了一个这种聚合函数，表中的所有记录就会马上塌陷成一行。如果有GROUP BY则会塌陷成好几个表。
因为GROUP BY就是把一个表先分裂成几个表，就特别适合跟这些聚合函数在一起使用，聚合函数把一个表塌陷了，还是有好几个数据**（因为GROUP BY会把表分开的）**
另外GROUP BY在把表分裂成几个以后，即使没有聚合函数，也是会把表塌陷成几个表的。
把表分裂以后，可以直接：HAVING COUNT(id) 获得某个分裂表的数量
AVG() 函数如果只有null值，则返回null，否则像其他所有聚合函数一样，忽略null值
所有聚合函数都一定要传参数！
WHERE 后面不能使用聚合函数的！聚合函数只能用在两个地方，SELECT后面和HAVING后面
要注意，所有聚合函数的作用域都优先是被group by分裂出来的表
- COUNT(id) 是只计算分裂表中的id数，
- SUM(score) 是只计算分列表中的每一条score的总和
- SUM(CASE WHEN) 是只对分裂表中的对应列每一条记录进行转换然后求最大值。

一些提示

像CREATE ，ALTER ，DROP ， RENAME ，SHOW 这种既能对database操作，又能对table进行操作的关键字，就统一需要加上DATABASE 或者 TABLE
像 SELECT , INSERT INTO , DELETE FROM , UPDATE 这种只能作用在table的命令行，就不需要加上TABLE或者DATABASE。
INT(10) 的意思是数字最多有10位，**VARCHAR(20)**表示最多有20个字符。
DATE是yyyy-mm-dd 的格式，DATE是要加单引号的，在SQL中除了整数、小数以外，其他类型数字都要加上单引号。
用飘号：` 包住的关键字，会失去关键字的效果，变成普通的单词。
想跨数据库访问表，应该用如下格式：otherDB.otherTable 其他库+.+其他表。
不要在数据库中放入时间，只放入日期，那么就可以用日期进行各种比较。一涉及时间数据库就蒙圈了。

当一个表包含了另外两个表的主键，那么可以用联合主键来代替自己维护一个主键。比如：

CREATE TABLE t_students (	-- 学生表格
  s_id varchar(20) PRIMARY KEY,
  s_name varchar(20) DEFAULT '',
  s_birth date DEFAULT '1990-09-20',
  s_sex varchar(10) DEFAULT '',
);

CREATE TABLE t_coureses(    --课程表格
	c_id VARCHAR(20) PRIMARY KEY,
	c_name VARCHAR(20) DEFAULT '' NOT NULL,
	t_id VARCHAR(20) NOT NULL
);

CREATE TABLE t_scores(      -- 成绩表格
	s_id VARCHAR(20)  , -- 学生编号
	c_id VARCHAR(20) , -- 课程编号
	s_score DECIMAL NOT NULL,  -- 分数
    PRIMARY KEY (s_id,c_id)
);
-- 成绩表格可以用学生表格和课程表格作为主键，只有两个主键都一样，才认为是同一条成绩数据。这样可以省下一个主键位。

但是要注意：联合主键的定义方式只能像上面代码那样定义，如果逐个定义主键，会报错诶。

另外，因为一个表只能有一个主键，所以不用为主键起名也可以用语句正常增加和删除主键，算式一个小语法糖啦

-- 下面这句话
SELECT * 
FROM t_student ts 
WHERE condition_1 
GROUP BY ts_valName
HAVING condition_2
ORDER BY valName_1 
DESC LIMIT beginIndex  , pageSize; 
-- 执行的真正顺序是：
1. FROM t_student ts
2. WHERE condition_1
3. GROUP BY ts_valueName
4. SELECT *
5. HAVING condition_2
6. ORDER BY val_name DESC
7. LIMIT beginIndex , pageSize;

-- 所以在Where的时候就能用到table的别名，但是不能用select中定义的别名。
-- where后面不能定义别名。HAVING 后面也不能定义别名，但是HAVING可以用SELECT定义的别名

清除重复查询时，当有多个字段名，要求指定的字段都一样才算是重复的。
应该把筛选最多的条件放到WHERE前面，可以提高点性能

MySQL支持的数值类型：

类型	占用空间	存储范围
TINYINT	1字节	-128~127
SMALLINT	2字节	-32768~32767
MEDIUMINT	3字节	-800万到800万
INT	4字节	-21亿~21亿
BIGINT	8字节	超大的
FLOAT	4字节	不精确
DOUBLE	8字节	不精确
DECIMAL	8字节	精确
CHAR(M)
VARCHAR(M)
DATE	3字节	yyyy-mm-dd格式
DATETIME	8字节	YYYY-MM-DD HH:MM:SS格式

金额货币一般都是用DECIMAL的啦
一个数字如果是FLOAT(5,2) 表示有5个数字，其中小数点后面有2个。
如果CHAR(20)来存储，如果不足20个，还是按照20个字符位置来存，这样找起来速度就快很多
VARCHAR(20)如果不足20个字符就用实际的字符数储存，占位小一点但是速度会低一点。
但日常使用还是**VARCHAR(20)**这种多一点。

表的一些约束：

约束可以直接接到类型后面。但建议PRIMARY KEY和FROEIGN KEY 还是另外放在后面。

NOT NULL 非空约束，如果传入null值会报错，一般值最好还是加个非空约束，对运行速度有提升的;
UNIQUE 唯一约束，该列的内容必须唯一。
增加**PRIMARY KEY这个约束会送你一个NOT NULL和UNIQUE，无论是建表时期还是后来加**的。
PRIMARY KEY 最好只用来约束数值类型，而且加上**AUTO_INCREASE。系统并不会自动变成AUTO_INCREASE** 的！还是要自己设置。只有PRIMARY KEY才有资格加上AUTO_INCREASE
AUTO_INCREASE 只要不赋值或者赋值为null，就会从1开始，步长为1，不能与DEFAULT共用。
**AUTO_INCREASE这个约束只能加在INT等数值后面，而且DEFAULT只能是NULL否则会报错。如果给AUTO_INCREASE**的主键赋值为null，系统会自动计算新的主键值。
如果没有特别定义约束的话，是啥约束都没有的，没有NOT NULL，没有定义DEFAULT，也没有UNIQUE的。每次MODIFY或者CHANGE都要重新定义约束，但MODIFY不会删除PRIMARY KEY约束。
NULL值是不能进行**>=、！=、<=** 那些逻辑判断的，他只能接受 IS NULL 和 IS NOT NULL这两种判断。
NULL值也是不能参与四则运算的，可以用MySQL的**IFNULL(val_name,0)**来定义如果值为null时，用来计算的默认值
DEFAULT XX 默认值约束，当不传入数据，就会用这个默认值，不能给有AUTO_INCREASE的PRIMARY KEY设置默认值。
NOT NULL 只能在建表的时候就定义好的，想追加或者删除只能用MODIFY了，不能用ALTER TABLE ADD 那个的。

UNIQUE 和 DEFAULT 都可以。在建表以后追加和删除：

-- 向已存在的表定义新增default值
ALTER TABLE Persons
ALTER City SET DEFAULT 'SANDNES';

-- 把已经定好的default值删掉
ALTER TABLE Persons
ALTER City DROP DEFAULT;

-- 向已存在的表追加UNIQUE
ALTER TABLE Persons ADD UNIQUE (name);

-- 给UNIQUE约束起个名可以方便删除UNQIE约束
ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName);

-- 删除UNIQUE约束
ALTER TABLE Persons
DROP INDEX uc_PersonID;   --注意是INDEX而不是UNQUE

所有约束都可以通过ADD，MODIFY，CHANGE等方式来重置和定义，上面所述的只是多一个快捷一点的方式而已。
当PRIMARY KEY跟AUTO_INCREMENT一起用，则不能直接通过DROP删除PRIMARY KEY了，要先用MODIFY把AUTO_INCREMENT 去掉（MODIFY不能去掉PRIMARY KEY），再DROP PRIMARY KEY

外键约束

外键格式有点复杂，只能记住吧：

-- 在建表时期增加一个外键
CREATE TABLE Employee(
	id INT,
    name VARCHAR(20),
    dep_id INT,
    PRIMARY KEY AUTO_INCREASE (id),
    CONSTRAINT E_d_D_id_fk    -- 注意，要在定义好外键的列之后，再声明外键约束！
    FOREIGN KEY (dep_id) REFERENCES Department (id)   --要注意从表的属性列也要用括号包住的！
);

-- 当建好表以后，对表添加外键指向另一个表
ALTER TABLE Employee ADD 
CONSTRAINT employee_dep_id_to_department_id_fk
FOREIGN KEY (dep_id) REFERENCES Department(id);

-- 删除外键约束
ALTER TABLE Employee DROP FOREIGN KEY E_d_D_id_fk;

如果想关联的列跟随着主键更新或者删除，应该在定义的时候加上ON DELETE CASCADE 以及ON UPDATE CASCADE

-- 创建带有级联更新外键的表完整写法
CREATE TABLE Employee(
	id INT,
    name VARCHAR(20),
    dep_id INT,
    PRIMARY KEY AUTO_INCREASE (id),
    CONSTRAINT E_d_D_id_fk 
    FOREIGN KEY (dep_id) REFERENCES Department (id) --要注意主从表的属性列也要用括号包住的！
    ON DELETE CASCADE ON UPDATE CASCADE
);

-- 添加级联更新的外键完整写法
ALTER TABLE Employee ADD 
CONSTRAINT E_d_D_id_fk 
FOREIGN KEY (dep_id) REFERENCES Department (id) 
ON DELETE CASCADE ON UPDATE CASCADE

但是要知道，级联操作好像很叼，但是他运行效率比java低很多，尽量不要在数据库中进行各种运算操作。
外键只能是主表的主键或者空。

JOIN导致的笛卡尔积：

当多表查询时，就会有笛卡尔积，因为两个表相交的话，会导致表之间的记录相乘。所以才要有不会重复的主键，方便多表查询时可以找到对应的记录。

-- 多表查询，有多少个表，就至少要有n-1个链接
SELECT p.id,p.productName,pd.dirName,ps.storeNum,p.salePrice,p.salePrice*ps.storeNum 利润
FROM product p,productdir pd,productstock ps  -- FROM中可以声明表的别名，可以让SELECT用。
WHERE p.dir_id=pd.id AND p.id=ps.product_id   -- 用链接消除笛卡尔积
ORDER BY 利润 DESC;  -- 这里可以用别名了

-- 但是这种叫隐式连接，已经逐渐被淘汰了，以后都用JOIN...ON ...这种形式的显示连接了
SELECT p.id,p.productName,pd.dirName,ps.storeNum,p.salePrice,p.salePrice*ps.storeNum 利润
FROM product p
JOIN productdir pd ON p.dir_id = pd.id		-- 显式连接
JOIN productstock ps ON ps.product_id = p.id	-- 显示连接
ORDER BY 利润 DESC;

上面是**JOIN…ON…的形式，他是INNER JOIN … ON…**的简化版，就用简化版好了。

当有表格Persons

id_p	姓名
1	张三
2	李四
3	王五

还有表格Orders

id_o	订单号	id_p
1	订单_1	1
2	订单_2	2
3	订单_3	65

如果只是用JOIN，而不用ON的时候，会返回如下结果

Persons.id_p	id_o	姓名	订单号	Orders.id_p
1	1	张三	订单_1	1
1	2	张三	订单_2	2
1	3	张三	订单_3	65
2	1	李四	订单_1	1
2	2	李四	订单_2	2
2	3	李四	订单_3	65
3	1	王五	订单_1	1
3	2	王五	订单_2	2
3	3	王五	订单_3	65

当用了ON以后，就会按照要求，把Persons.id_p和Orders.id_p相同的筛选出来，也就是标红的两条记录。

如果用普通JOIN，则只返回匹配到的，也就是这两条标红的。

如果用LEFT JOIN，就会把JOIN左边的表，无论是否有匹配到，全部都返回，**如果没有匹配到，那值对应的值就是null。**非常强势

Persons.id_p	id_o	姓名	订单号	Orders.id_p
1	1	张三	订单_1	1
2	2	李四	订单_2	2
3	null	王五	null	null

RIGHT JOIN同理的。注意，JOIN右边的列表，永远是RIGHT。

还有一个两个都强势的：FULL JOIN，两个都返回了再说，没有匹配就用null，有匹配才填信息。

用UNION关键字来组合两个表的信息

UNION / UNION ALL是用来把两个查询的结果合并的，他要求两次查询的结果，列数相等，类型相似。

SELECT val_name(s) FROM table_1
UNION ALL   -- UNION 和 UNION ALL 的区别就是是否会保留相同的记录，UNION ALL会保留相同的记录的。
SELECT val_name(s) FROM table_2;

UNION本来是被设计用来组合两个不同的表的，但是他的效率比OR高，所以即使我们是对同一个表操作，也可以使用UNION来代替OR！

获得的记录，属性名会由UNION前面的语句来定义

SQL判断是否相同的逻辑是：所有列的元素都相同，才认为两条记录相同。

用CREATE SELECT 跟INSERT INTO SELECT 互补

在INSERT INTO table_2 (a,b,c) SELECT a , b ,c FROM table_1;之前，我们总是要新建一个表：CREATE TABLE table_2 LIKE table_1 ，显得有点麻烦了。而SELECT INTO 就是用来处理没有表的情况。

但是MySQL不支持SELECT INTO命令！他有代替的命令：

-- 把table_1选中的列插入table_2中
CREATE TABLE Persons_orders_backup(
SELECT p.id_p 客户id,p.name 客户姓名,p.city 城市,o.order_num 订单号码
FROM Persons p
JOIN Orders o ON p.id_p = o.id_p); -- 是可以用JOIN的！
-- 因为新的表啥信息都没有，所以会以SELECT后面指定的属性值作为列

CREATE SELECT要求table_2不存在，所以在已经存在一个table_2，只是想用另外的表扩充下的情况他是处理不了的。

但是用来作为快速备份，或者把查询的结果保存成一个新的表格，是非常有用的！跟INSERT INTO SELECT各有优势。

用视图简化你的代码

数据库里是没有变量保存值这种概念的，视图是你保存数据的唯一方式了。

-- 新建一个视图
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition;
-- 当新建了一个视图之后，就可以把它看成一张表了，MySQL每次用到这个视图，都会重新查询一次，保证数据是最新的

-- 新建好以后就可以使用视图，就当是普通表来用就好。
SELECT * FROM view_name;   

-- 更新一个视图，表示用同一个视图名，但是视图里面的内容就不同了
CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition;

-- 删除视图
DROP VIEW view_name;

利用DATE()函数来定义时间

虽然JAVA也能做，但是知道一下SQL里是如何操作时间的也是不错的，但是SQL处理时间就很尴尬，一般都是用来处理日期：

一些关于时间的方法

函数	描述
NOW()	返回当前的日期和时间
CURDATE()	返回当前的日期
CURTIME()	返回当前的时间
DATE()	提取日期或日期/时间表达式的日期部分
EXTRACT()	返回日期/时间按的单独部分
DATE_ADD()	给日期添加指定的时间间隔
DATE_SUB()	从日期减去指定的时间间隔
DATEDIFF()	返回两个日期之间的天数
DATE_FORMAT()	用不同的格式显示日期/时间

利用GROUP BY 给表格分类

-- GROUP BY的常规用法
SELECT Customer,SUM(OrderPrice) 
FROM Orders
GROUP BY Customer

-- 可以看做GROUP BY按照列，把一个表分成好几个表，然后SELECT 对每一个表执行操作以后，再把所有表结合成一个表。有点像java 中stream流的toMap方法。

子查询语句的几种结果

所谓子查询，就是让一个查询结果成为另一个查询的一部分：

-- 找到成绩比平均分高的学生
SELECT * 
FROM t_student
WHERE s_score > (SELECT AVG(s_score) FROM t_student);

注意，子查询语句一定要放在小括号中的，否则程序会报错。

数据库查询的步骤是先查询出子查询的结果，再将子查询结果作为父查询的一部分执行父查询。

子查询一共会有3种情况：

单行单列：可以用作 ＞、＜、＝、！=、＞＝、＜＝的比较条件（案例同上）。

多行单列：可以放在IN或者NOT IN后面作为条件

-- 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select stu_b.s_id 学号, avg(sco_b.s_score) 平均分
from t_students stu_b
       join t_scores sco_b on sco_b.s_id = stu_b.s_id and stu_b.s_id in (
  select stu.s_id
  from t_students stu
         left join t_scores sco on sco.s_id = stu.s_id and sco.s_score < 60
  group by stu.s_name
  having COUNT(*) >= 2
)
group by stu_b.s_name;

多行多列：可以放在FROM 或者JOIN 后面。暂时找不到比较好的案例。

功能异常强大的CASE函数

这部分参考了璀璨小二的博客

CASE WHEN THEN ELSE END 这个语法对数据库操作太重要了，他有两种写法：

-- 简单CASE函数
CASE sex
WHEN 1 THEN '男'
WHEN 2 THEN '女'
ELSE '其他' END

-- CASE搜索函数
CASE
WHEN sex=1 THEN '男'
WHEN sex=2 THEN '女'
ELSE '其他' END

CASE函数其实可以看成一个普通列，只是他会逐条改变这个列的内容，所以这个函数可以放在SELECT、HAVING、WHERE、聚合函数参数这些地方。

他最后会把这个列的值变成THEN 后面的类型。

案例一：用CASE来按条件筛选

-- 按照人口统计人数
SELECT country, 
       SUM( CASE WHEN sex = '1' THEN 
                      population ELSE 0 END),  --男性人口 
       SUM( CASE WHEN sex = '2' THEN 
                      population ELSE 0 END)   --女性人口 
FROM  Table_A 
GROUP BY country; 
-- 这样就可以一个select统计两个数，不用JOIN

所以有了case以后，就再也不用为了一个数值就JOIN一个新的表了！

案例二：在Check中使用CASE函数

可以作为检查条件，又不会阻挡其他数据的输入

-- 要求如果是女员工，那个工资就要比1000高
CONSTRAINT check_salary CHECK 
           (CASE WHEN sex = '2' 
                 THEN CASE WHEN salary > 1000 
                       		THEN 1 ELSE 0 END 
                 ELSE 1 END = 1 )

案例三：利用CASE函数筛选更新条件

/*
工资5000以上的职员，工资减少10% 
工资在2000到4600之间的职员，工资增加15% 
很容易考虑的是选择执行两次UPDATE语句，如下所示 
*/

UPDATE Personnel 
SET salary = CASE WHEN salary >= 5000 
　            THEN salary * 0.9 
WHEN salary >= 2000 AND salary < 4600 
THEN salary * 1.15 
ELSE salary END;

案例四：嵌套CASE函数

假设有学生表如下：

c_id	main_flag	s_id
1	y	100
2	n	100
2	n	200
3	y	200
4	n	200
4	n	300
5	n	400
6	n	500

/*
只选修一门课程的人，返回那门课程的ID
选修多门课程的人，返回所选的主课程ID
 */
select *,case
  when count(stu.c_id) =1  
    then max(stu.c_id)
    else max(case stu.main_flag
      when 'y' then stu.c_id
      else null end )
      end 主修课程ID
from Students stu
group by stu.s_id;

一些值得记录的案例：

在IN()中加入多行单列的子查询

-- 查询没学过"张三"老师授课的同学的信息
SELECT *
FROM t_students
WHERE s_id NOT IN (
SELECT stu_a.`s_id`
FROM t_students stu_a
JOIN t_scores sco ON sco.`s_id`=stu_a.`s_id`
JOIN t_courses cou ON sco.`c_id`=cou.`c_id`
JOIN t_teachers tea ON cou.`t_id`=tea.`t_id` AND tea.`t_name`='张三'
);

-- 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
SELECT *
FROM t_students
WHERE s_id IN (SELECT s_id FROM t_scores WHERE c_id='01')
AND s_id NOT IN(SELECT s_id FROM t_scores WHERE c_id='02');

同一个表的多次插入，以获得特定的值

-- 查询"01"课程比"02"课程成绩高的学生的信息及课程分数
SELECT a.* ,b.s_score  01_score,c.s_score  02_score 
FROM t_students a 
JOIN t_scores b ON a.s_id=b.s_id AND b.c_id='01'
LEFT JOIN t_scores c ON a.s_id=c.s_id AND c.c_id='02'
WHERE b.s_score>c.s_score;

用聚合函数和单行单列的子查询做比较

-- 查询没有学全所有课程的同学的信息
SELECT * 
FROM t_students
WHERE s_id NOT IN(
SELECT stu.s_id 
FROM t_students stu
JOIN t_scores sco ON stu.`s_id`=sco.`s_id`
GROUP BY stu.`s_id`
HAVING COUNT(DISTINCT sco.`c_id`)=(SELECT COUNT(c_id) FROM t_courses)
);
-- 注意，只有SELECT 后面跟单个聚合函数，才能比较，跟c_id那种属性名，或者多个聚合函数，是会报错的

JOIN多行多列的子查询

-- 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select stu_b.s_name 学生姓名,sco_b.s_score,ss.平均成绩 平均成绩
from t_students stu_b
left join t_scores sco_b on sco_b.s_id=stu_b.s_id
left join (select avg(sco.s_score) 平均成绩,stu.s_id student_id
      from t_students stu
             join t_scores sco on stu.s_id = sco.s_id
      group by stu.s_name)  ss on sco_b.s_id=ss.student_id
order by sco_b.s_score desc ;

小鹏头

关注

2
点赞
踩
11

收藏

觉得还不错? 一键收藏
1
评论
数据库MySQL一些命令整理（一）

文章目录一些命令的一览：对数据库的增删改查：增数据库删数据库更改数据库查询数据库对**表**的增删改查：增加表修改表的信息查看表的信息删除表对表的列进行操作对表中数据增删改查增删改查一些条件语句一些函数一些提示MySQL支持的类型：表的一些约束：一些命令的一览：对数据库的增删改查：增数据库CREATE DATABASE my_database; -- 新建数据库CREATE DATABA...
复制链接

扫一扫