当前位置:   article > 正文

数据库MySQL一些命令整理(一)_表's_sale_rec_mor other'已存在是什么意思

表's_sale_rec_mor other'已存在是什么意思

一些命令的一览:

对数据库的增删改查:

增数据库
CREATE DATABASE my_database; -- 新建数据库
CREATE DATABASE IF NOT EXISIT my_database;    -- 如果数据库不存在才新建数据库
  • 1
  • 2
删数据库
DROP DATABASE IF EXISIT my_database;   -- 如果数据库存在才删除,因为重复删除会报错。
DROP DATABASE my_database;    -- 删除数据库
  • 1
  • 2
更改数据库
USE my_database;    -- 切换数据库
ALTER DATABASE my_database DEFAULT CHARACTER SET UTF8;    -- 更改数据库的字符集
  • 1
  • 2
查询数据库
SHOW DATABASES;     -- 显示所有数据库
SELECT DATABASE();      -- 显示当前选中的数据库
SHOW CREATE DATABASE my_database;     -- 显示数据库的创建语句以及characterset。
  • 1
  • 2
  • 3

对表的增删改查:

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

新建表
-- 增加一个表
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);
-- 注意,跟建普通表一样,括号不能省。
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
修改表的信息
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 可以看到表的当前引擎*/
  • 1
  • 2
  • 3
  • 4
查看表的信息
SHOW TABLES; -- 显示选中的数据库(use my_database)的所有表
SHOW CREATE TABLE t_student;   -- 显示表的创建语句

-- 显示表的所有列与列的属性,两者一样的。
DESC t_student;  
SHOW COLUMNS FROM t_student; 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
删除表
DROP TABLE t_student;          -- 直接删除表
DROP TABLE IF EXISIT t_student;   -- 如果表存在就删除表
  • 1
  • 2
对表的列进行操作
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也能做外键操作,而且性能更好。
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23

对表中数据增删改查

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

-- 可以只增加一部分
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...);
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
-- 按条件删除
DELETE FROM t_student WHERE condition1 && condition2;

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

-- 销毁表格,auto_increase值不会保留
TRUNCATE TABLE t_student;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
-- 按条件更改某一列的数值
UPDATE t_student SET valueName_1 = newVAl,valueName_2 = newVal 
WHERE condition1 && condition2

--全部对所有数值全部改完
UPDATE t_student SET valueName_1 = newVal ,valueName_2= newVal;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
--查询表所有数据
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;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37

一些DDL命令:

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

-- 删除用户
DROP USER 'username'@'主机名'
  • 1
  • 2
  • 3
  • 4
  • 5
  • 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'@'主机名'
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 可以用ALL来代替各种权限。
  • 数据库和表名定义了权限在哪个数据库的哪个表可用,用*.* 可以表示所有数据库的所有表。
-- 修改普通用户密码:
(登录有权限的用户状态下)SET PASSWORD FOR 'username'@'主机名'='new password';

-- 修改管理员密码:
(不必登录)mysqladmin -uroot -p password;  -- 然后按照提示操作即可
  • 1
  • 2
  • 3
  • 4
  • 5
  • 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);

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22

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

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

一些聚合函数

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

-- 把一些属性连接成字符串返回
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(23;
-- 这些函数都是返回一个数值,除了放在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后面的。
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 一个查询语句中,只要**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个字符。

  • DATEyyyy-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)
    );
    -- 成绩表格可以用学生表格和课程表格作为主键,只有两个主键都一样,才认为是同一条成绩数据。这样可以省下一个主键位。
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
  • 但是要注意:联合主键的定义方式只能像上面代码那样定义,如果逐个定义主键,会报错诶。

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

    -- 下面这句话
    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定义的别名
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
  • 清除重复查询时,当有多个字段名,要求指定的字段都一样才算是重复的

  • 应该把筛选最多的条件放到WHERE前面,可以提高点性能

MySQL支持的数值类型:

类型占用空间存储范围
TINYINT1字节-128~127
SMALLINT2字节-32768~32767
MEDIUMINT3字节-800万到800万
INT4字节-21亿~21亿
BIGINT8字节超大的
FLOAT4字节不精确
DOUBLE8字节不精确
DECIMAL8字节精确
CHAR(M)
VARCHAR(M)
DATE3字节yyyy-mm-dd格式
DATETIME8字节YYYY-MM-DD HH:MM:SS格式
  • 金额货币一般都是用DECIMAL的啦
  • 一个数字如果是FLOAT(5,2) 表示有5个数字,其中小数点后面有2个。
  • 如果CHAR(20)来存储,如果不足20个,还是按照20个字符位置来存,这样找起来速度就快很多
  • VARCHAR(20)如果不足20个字符就用实际的字符数储存,占位小一点但是速度会低一点
  • 但日常使用还是**VARCHAR(20)**这种多一点。

表的一些约束:

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

  • NOT NULL 非空约束,如果传入null值会报错,一般值最好还是加个非空约束,对运行速度有提升的;

  • UNIQUE 唯一约束,该列的内容必须唯一。

  • 增加**PRIMARY KEY这个约束会送你一个NOT NULLUNIQUE,无论是建表时期还是后来加**的。

  • 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 NULLIS NOT NULL这两种判断

  • NULL值也是不能参与四则运算的,可以用MySQL的**IFNULL(val_name,0)**来定义如果值为null时,用来计算的默认值

  • DEFAULT XX 默认值约束,当不传入数据,就会用这个默认值,不能给有AUTO_INCREASEPRIMARY KEY设置默认值。

  • NOT NULL 只能在建表的时候就定义好的,想追加或者删除只能用MODIFY了,不能用ALTER TABLE ADD 那个的。

  • UNIQUEDEFAULT 都可以。在建表以后追加和删除:

    -- 向已存在的表定义新增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
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
  • 所有约束都可以通过ADD,MODIFY,CHANGE等方式来重置和定义,上面所述的只是多一个快捷一点的方式而已。

  • PRIMARY KEYAUTO_INCREMENT一起用,则不能直接通过DROP删除PRIMARY KEY了,要先用MODIFYAUTO_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;  
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
  • 如果想关联的列跟随着主键更新或者删除,应该在定义的时候加上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
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
  • 但是要知道,级联操作好像很叼,但是他运行效率比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;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12

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

当有表格Persons

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

还有表格Orders

id_o订单号id_p
1订单_11
2订单_22
3订单_365

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

Persons.id_pid_o姓名订单号Orders.id_p
11张三订单_11
12张三订单_22
13张三订单_365
21李四订单_11
22李四订单_22
23李四订单_365
31王五订单_11
32王五订单_22
33王五订单_365

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

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

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

Persons.id_pid_o姓名订单号Orders.id_p
11张三订单_11
22李四订单_22
3null王五nullnull

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;
  • 1
  • 2
  • 3

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后面指定的属性值作为列
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

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;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18

利用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方法。
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

子查询语句的几种结果

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

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

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

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

子查询一共会有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;
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
  • 多行多列:可以放在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
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11

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
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9

所以有了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 ) 

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

案例三:利用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; 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12

案例四:嵌套CASE函数

假设有学生表如下:

c_idmain_flags_id
1y100
2n100
2n200
3y200
4n200
4n300
5n400
6n500
/*
只选修一门课程的人,返回那门课程的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;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13

一些值得记录的案例:

在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');
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16

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

-- 查询"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;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

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

-- 查询没有学全所有课程的同学的信息
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那种属性名,或者多个聚合函数,是会报错的
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11

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 ;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/我家小花儿/article/detail/440770
推荐阅读
相关标签
  

闽ICP备14008679号