mysql的SQL用法及Navicat的相关使用

以下语法均在mysql 8.0下

一.建立约束：

数据库中约束分为一下几种
●主键约束（Primary Key constraint）：要求主键列数据唯一，并且不允许为空。
●唯一约束（Unique constraint）：要求该列唯一，允许为空，但只能出现一个空值
●检查约束（Check constraint）：某列取值范围限制，格式限制等，如有关年龄、性别的约束。
●默认约束（Default constraint）：某列的默认值，如在数据库里有一项数据很多重复，可以设为默认值。
●外键约束（Foreign Key constraint）：用于在两个表之间建立关系，需要指定引用主表的哪一列。

1、navicat中沒有可视化添加约束的选项，所以只能用代码添加。
直接新建查询，然后用代码添加。

alter table `LoginTable` add CONSTRAINT   
check_sex(约束名字) CHECK(Lsex="男" or Lsex="女")    --CHECK后面是列的值

建立约束成功后，如果添加的值不是约束中的值，则会报错。

2. 删除约束：

use testss;  --使用的数据库名
go
--判断是否存在约束，如果存在则删除，如果不存在则不
if exists(select * from sysobjects where name='default1')
alter table test1 drop constraint default1;
go

3、设置默认约束
两个alter关键字

alter table table_name alter column Paddress set default '地址不详'

二.建立外键时报错

3780 Referencing column ‘%s’ and referenced column ‘%s’ in foreign key constraint ‘%s’ are incompatible

因为参照键和被参照键的类型不同！！！
或者是 字符集不同， 需要将两个字段的字符集设为相同

三.定义存储过程与函数须注意的问题

1. 需要自己添加varchar的长度
   定义过程中的参数，需要自己添加varchar的长度，，自动生成时是沒有数值长度的。我们自己添加成 varchar（255）
   
2. 根据需要，在写函数或者过程时就需要定义好，参数是输入的还是传出的
   有三种形式：IN、OUT、INOUT。OUT参数是SQL向外接程序输出的参数。在JDBC里如果想要获取到SQL返回的参数，需要先注册输出函数，用getString(index,)来获取值。
   JDBC中调用存储过程是建立 CallableStatement 对象。

jdbc用法

主要就是三大类：Statement 、PreparedStatement 、CallableStatemet

execQuery() 查询用结果集ResultSet接收

// 建立存储过程调用对象 CallableStatement对象
CallableStatement clbStmt = null; 
ResultSet res = null; // 结果集对象
try
{
// 获取数据库连接
conn = getConnection();
 
// 创建CallableStatement对象
clbStmt = conn.prepareCall("{CALL proc_search_user(?,?,?,?)}");
 
// 设置输入参数
clbStmt.setInt(1, 3); // 第一个参数设值
clbStmt.setInt(2, 10); // 第二个参数设值
 
/**
*从SQL获取的接收的参数需要自己设置
**/
clbStmt.registerOutParameter(3, Types.INTEGER);
clbStmt.registerOutParameter(4, Types.INTEGER);
 
// 执行调用存储过程，并获取结果集
//一般调用查询的化返回集用REsultSet对象去接收
res = clbStmt.executeQuery();
 
// 循环遍历结果集
while (res.next())
{
// 获取列值   如果参数是字符串的话表示，在存储过程的参数名。
int id = res.getInt("id");
String name = res.getString("name");
Timestamp createTime = res.getTimestamp("create_time");
 
// 输出列值
System.out.println("编号：" + id + "  姓名：" + name + "  创建时间：" + createTime);
}

4. SQL字符连接CONCAT函数使用&模糊搜索

1 很多情况我们需要使用从未界传入的字符串利用 LIKE %外界传入的字符串% 来搜索，但mysql中不能直接用"+"来 拼接字符串。
我们可以使用SQL中的CONCAT函数来拼接字符串：CONCAT(str1,str2,…)。

CREATE DEFINER=`root`@`%` PROCEDURE `a`(IN `inpur` varchar(255))
BEGIN
	#Routine body goes here...
-- 	SELECT CONCAT('你好','%');
END

运行结果：

2 利用这个方法我们可以进行模糊搜索： inpur是从外界获取的参数

CREATE DEFINER=`root`@`%` PROCEDURE `a`(IN `inpur` varchar(255))
BEGIN
-- 	#Routine body goes here...
	Select LoginTable.Lpname from LoginTable WHERE LoginTable.Lpname LIKE CONCAT(inpur,'%');
END

获得所有匹配结果：

3. 带分隔符的CONCAT
CONCAT_WS() 代表 CONCAT With Separator ，是CONCAT()的特殊形式。第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是其它参数。
注意：
如果分隔符为 NULL，则结果为 NULL。函数会忽略任何分隔符参数后的 NULL 值。
如连接后以逗号分隔

 select concat_ws(',','11','22','33');

运行结果：

五. 从SQL中获取时间

在实际中我们添加表的数据是时间或时间戳类型。
1、NOW() 返回当前的日期和时间

2、timestamp(now()) 同样获取日期和时间，它是用函数的方法得到值。
3、unix_timestamp(now()) 返回的是时间戳类型。

4、我们可以利用时间戳类型来生成唯一ID
如·用时间戳拼接其他的数据项形成唯一id: SET sid = CONCAT(Sname,unix_timestamp(now()));

其他时间函数：图片截取W3school。

六.Navicat中查看mysqL已经创建好的触发器、约束等对象

mysql 中保存的这些触发器、约束、索引等数据库对象都统一保存在 information_schema 这个数据库中。

可以看到这个information_schema 这个数据库下有很多表每张表都保存了所用对数据库的操作。比如：

比如想查看触发器我们点击Trigger ，可以看到现在mysql系统中存在的触发器有哪些，TRIGGER_SCHEMA列指明了触发器所属的数据库，其中sys是系统的数据库我们不需要有修改。MasterDataBase是某用户自己定义的数据库，我们保存自己写的触发器也就是
TRIGGER_SCHEMA列为MasterDataBase的记录了。
在 ACTION_STATEMENT列中就记录了触发器保存的语句，

MySQL中约束保存在information_schema数据库的table_constraints中。
查看 外键 ： show keys from tblname;

七. 其他用法和总结

1. 在数据库中尽量少用触发器，所有的触发器功能都可以用存储过程代替。
2. 有时候需要设某列为自增来作为唯一id，则此列必须为主键且非空，，但在实际中最好不用这种方法来生成唯一id，因为你不知道它的id到底是多少，这在后期删改数据是可能引起一些不必要的问题。
3. 很多时候，我们在定义约束时，我们可以使用外键参照来代替CHECK约束，就是可以让要被约束的属性作为外键参照其他的表的某一属性，而这某一属性的范围就是，被约束的属性的值空间。
   比如，商品的类别有：服饰类、食品类、日用品、数码产品、美妆类、运动类、电器类、古玩类、图书类、网络资源类。
   我们可以建一个表，就一个类别属性，这张表里的值就是这些类别的值如：
   
   然后让需要被约束的列作为外键参照此表。
   
   4.SQL语句的导出
   Navicat中提供能了数据库所有结构的导出和结构与数据的导出。
   
   Navicat导出的SQL语句也非常的工整可以导入到其他的数据库中，但数据库名字需要相同。
   导出的SQL语句
   

---

八、用SQL语言表示除运算

除法运算的概念在次就不赘述了。
首先需要清楚除法是关系代数中的一种运算，在衍生到数据中，除操作非常适合用于求“至少使用了…的全部”之类的查询。

sql语言并没有定义除操作，甚至连全称量词都没有，但是有存在量词EXISTS 和 NOT EXISTS,所以通常的思路就是将全程量词转变为存在量词来实现除操作。

具体的就是用外连接和存在量词配合来构造成出运算。

更多SQL详细用法详见W3school的文档 https://www.w3school.com.cn/sql/sql_like.asp