-
在一个select语句中，嵌入了另外一个select语句，那么被嵌入的select语句称之为子查询语句。
例如：
查询最高的男生的信息
select * from students where gender=1 and high=(select max(high)from students where gender=1)
查询出高于平均身高的信息
select * from students where high>(select avg(high) from students); -
列级子查询
查询学生的班级号能够对应的学生信息
select * from students where id in(select id from classes);
select s.* from students as s left join classes as c on s.cls_id=c.id having c.id is not null;
-
自关联其实就是连结查询，需要两张表，只不过它的左表（主表）和右表（子表）都是自己。在做自连接查询的时候是自己链接自己，分别给主表和子表取别名，再付加条件执行。
-
在你填写收货信息的时候，会提示你选择省-市-区，当你选则江西省的后，市选项里不会出现广州市，只会显示江西省下的市，这样一个省级联动如何实现？
如以下两种方法。
查找广州市下的区也是同样的原理，但是如果想要查找县，乡，甚至是具体到某一个街道，那就需要更多的表，这个查询就变得很长，自关联就是把方法1中的3个表融合为一个表。
方法二的优点：存储的都是地区信息，而且每种信息的数据量有限，没必要增加一个新表，或者将来还要存储区、乡镇信息，都增加新表的开销太大。 -
No.1.多个表的连接查询
-
设计省信息表(province)
id
provinceid
province设计市信息表(city)
id
cityid
ctitle -
provinceid-与省表中对应
设计区信息表(areas)
id
atitle
cityid-与市表中对应 -
例如，
查询广东省下的市
1.现在省表中拿到广东省的provinceid——select * from province where province=‘广东省’;
2.再去city表中找到provinceid对应的市——select * from city where provinceid=‘440000’;
或者
使用子查询select * from city where provinceid =(select provinceid from province where province=‘广东省’);
或者
使用内连接selecet * from city as c inner join province as p on p.provinceid=c.provinceid having p.province=‘广东省’;
-
-
No.2.一个表的自关联
- 设计表结构(site)
id 从中国(1)开始
pid 中国(0)/省(1-国id)/市(省id)/区(市id)
name 省/市/区的名字
–即，省的pid=国的id，市的pid=省的id，区的pid=市的id，区的id自增 - 查询广东省下的市
select * from site as s1 inner join site as s2 on s1.id=s2.pid having s1.name=‘广东省’;
查询广州市下的区
selcect * from site as s1 inner join site as s2 on s1.id=s2.pid having s1.name=‘广州市’;
- 设计表结构(site)
- MySql外键必须使用存储引擎为innodb
- 因程序很难100%保证数据的完整性，而外键在数据库服务器当机或者出现其他的问题时，能够最大限度的保证数据的一致性和完整性。
- 设置外键约束的两个表之间会具有父子关系，即子表中外键的字段的取值范围由父表所决定
- 设置外键一定程度上降低数据库的速度
- 子表的外键字段的数据类型和父表要一致
- 语法：
alter table 表名 add constraint 外键名字 foreign key(外键字段名) references 外表表名(主键字段名); - 例如
将students表中的cls_id与class表中的id相关联alter table students add constraint fk_cls_id foreign key(cls_id) references class(id); - 引入外键之后，外键列只能插入参照列存在的值，参照列被参照的值不能被删除，这就保证了数据的参照完整性。
- –当class表中的id只有1,2,3 则students表中的cls_id插入其他数据（比如4）【 insert into students values(0,‘orange’,18,156,‘男’,4,0)；】会失败，因为字段cls_id不允许存在4，若想成功插入，需要先给class表中加入id为4的数据。【 insert into class values(4,‘四班’); 】
- –当class表中的id有1,2,3 students表中的cls_id有1,2 那么class表中的id1,2不允许修改，而3可以修改。
- 接上↑
若想将class表中的信息删除，若id已使用则会因为students的外键关系而不被允许。 - 语法：
alter table 表名 drop foreign key 外键名;- 取消students表中的cls_id与class表中的id的关联关系
alter table students drop foreign key fk_cls_id;
- 取消students表中的cls_id与class表中的id的关联关系
- 一个表可以有多个外键
- 对子表students（外键所在的表）的作用：子表在进行写操作的时候，如果外键字段在父表中找不到对应的匹配，操作就会失败
- 对父表class的作用：对父表的主键字段进行删或改时，如果对应的主键在字表中被应用，操作会失败。主表主表（父表）：对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表。
从表（子表）：对于两个具有关联关系的表而言，相关联字段中外键所在的表就是从表。
-
语法：
alter table 表名 add constraint 外键名字 foreign key(外键字段名) references 外表表名(主键字段名) on delete 约束模式; -
district：严格模式(默认), 父表不能删除或更新一个被子表引用的记录。
cascade：级联模式, 父表操作后，子表关联的数据也跟着一起操作。
set null：置空模式,前提外键字段允许为NULL, 父表操作后，子表对应的字段被置空。
- 导出整个数据库
mysqldump -u 用户名 -p –default-character-set=latin1 数据库名 > 导出的文件名(数据库默认编码是latin1)
mysqldump -u wcnc -p smgp_apps_wcnc > wcnc.sql
- 导出一个表
mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名
mysqldump -u wcnc -p smgp_apps_wcnc users> wcnc_users.sql
- 导出一个数据库结构
mysqldump -u wcnc -p -d –add-drop-table smgp_apps_wcnc >d:wcnc_db.sql
-d 没有数据 –add-drop-table 在每个create语句之前增加一个drop table
- 导入数据库
A:常用source 命令
进入mysql数据库控制台，
如mysql -u root -p
mysql>use 数据库
然后使用source命令，后面参数为脚本文件(如这里用到的.sql)
mysql>source wcnc_db.sql
B:使用mysqldump命令
mysqldump -u username -p dbname < filename.sql
C:使用mysql命令
mysql -u username -p -D dbname < filename.sql
7.2 数据表的拆分 8. Python操作MySQLimport pymysql
from pymysql import cursors
from pymysql.connections import SSL_ENABLED
# 链接数据库
db = pymysql.connect(host = "172.16.74.145", port = 3306 , user = "root", passwd = "123456",db = "mysql", charset = "utf8") # 链接数据库
#定义一个游标
cursor = db.cursor()
#查询版本
cursor.execute("select version()")
data_version = cursor.fetchone()
print("Database version:%s" % data_version)
#创建数据库test01
cursor.execute("drop database if exists test01") #如果数据库已经存在，那么删除数据库后重新创建
sql_create_db = 'create database test01 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin'
cursor.execute(sql_create_db)
#选择数据库
cursor.execute('use test01')
#创建数据库表
cursor.execute("drop table if exists EMPLOYEE") #如果表已经存在，删除重建
sql_new_table = "CREATE TABLE EMPLOYEE (FIRST_NAME CHAr(20) NOT NULL,LAST_NAME CHAr(20),AGE INT,SEX CHAr(1),INCOME FLOAT)"
cursor.execute(sql_new_table)
#查询操作
sql_search = 'select * from EMPLOYEE'
cursor.execute(sql_search)
data_search = cursor.fetchone()
print(data_search)
#插入操作
sql_insert = 'insert into EMPLOYEE values ( "李","梅",20,"W",5000)'
cursor.execute(sql_search)
db.commit() #应用修改
cursor.execute(sql_search)
data_search = cursor.fetchone()
print(data_search)
#指定条件查询数据
sql_search_Conditional = "select * from EMPLOYEE where income > '%d' " %(1000)
cursor.execute(sql_search_Conditional)
data = cursor.fetchone()
print(data)
#更新操作
sql_update = "update EMPLOYEE set age = age+1 where sex = '%c' " % ("W")
cursor.execute(sql_update)
db.commit()
cursor.execute(sql_search)
data = cursor.fetchone()
print(data)
#删除操作
sql_del = " delete from EMPLOYEE where age > '%d' " % (30)
cursor.execute(sql_del)
db.commit()
#查看更新后的结果
cursor.execute(sql_search)
data = cursor.fetchone()
print(data)
#关闭数据库
db.close()
#异常处理
try:
# 执行 SQL 语句
cursor.execute(sql_new_table)
# 提交修改
db.commit()
except:
# 发生错误时回滚
db.rollback()
9 Yearning SQL审核平台
9.1 安装
注意事项
Yearning 不依赖于任何第三方 SQL 审核工具作为审核引擎,内部已自己实现审核/回滚相关逻辑。
仅依赖 Mysql 数据库。
mysql 版本必须为5.7及以上版本(8.0及以上请将sql_mode 设置为空)并已事先自行安装完毕且创建 Yearning 库,字符集应为 UTF8mb4 (仅 Yearning 所需 mysql 版本)
Yearning 基于 1080p 分辨率开发仅支持 1080p 及以上显示器访问
对于设置页面配置重叠的问题请确认自己的分辨率以及是否进行了放大操作
请使用Chrome最新版本(不包括 360 等其他魔改版本)
9.2 Yearning 下载Yearning-go 提供二进制下载包
下载地址 https://github.com/cookieY/Yearning/releases
请选择最新版本 在 Assets 中选择 Yearning-x.x.x.linux-amd64.zip 包进行下载
如需进行代码修改或自己编译请移步至二次开发页面
Yearning 目录结构
9.3.填写配置文件cat conf.toml [Mysql] Db = "Yearning" Host = "127.0.0.1" Port = "3306" Password = "xxxx" User = "root" [General] #数据库加解密key，只可更改一次。 SecretKey = "dbcjqheupqjsuwsm"
关于 SecretKey
SecretKey 是 token/数据库密码加密/解密的 salt。
建议所有用户在初次安装 Yearning 之前将 SecretKey 更改(不更改将存在安全风险)
格式: 大小写字母均可, 长度必须为 16 位 如长度不是16位将会导致无法新建数据源
特别注意:
此 key 仅可在初次安装时更改!之后不可再次更改!如再次更改会导致之前已存放的数据源密码无法解密,最终导致无法获取相关数据源信息
使用帮助
./Yearning --help9.4.初始化及安装
./Yearning install
如要再次安装，请先把 yearning 库下所有表删除，否则重复执行无效
9.5启动服务默认启动
./Yearning run
参数启动
./Yearning run --push "172.27.80.35" -port "8000"
打开浏览器 http://127.0.0.1:8000
默认账号/密码：admin/Yearning_admin
