sql使用

概念

外键

外键是另一表主键, 可重复, 可为空, 用于数据约束

一个表中 FOREIGN KEY(外键) 指向另一个表中 PRIMARY KEY(主键)
FOREIGN KEY 必须是其指向表中值之一
FOREIGN KEY 用于约束预防破坏表之间连接的动作, 防止非法数据插入外键列

使用

CREATE TABLE `Stu` (
  `s_id`   varchar(255)  NOT NULL, 
  `s_name` varchar(255), 
  PRIMARY KEY (`s_id`) USING BTREE
);

CREATE TABLE `Course` (
  `c_id`   varchar(255)  NOT NULL, 
  `c_name` varchar(255), 
  PRIMARY KEY (`c_id`) USING BTREE
);

CREATE TABLE `Score` (
  `r_id`  int(0) NOT NULL AUTO_INCREMENT, 
  `s_id`  varchar(255), 
  `c_id`  varchar(255), 
  `score` int(0), 
  PRIMARY KEY (`r_id`) USING BTREE, 
  INDEX `s_id`(`s_id`) USING BTREE, 
  INDEX `c_id`(`c_id`) USING BTREE, 
  
  # Score.c_id(外键)指向 Course.c_id(主键)
  CONSTRAINT `c_id` FOREIGN KEY (`c_id`) REFERENCES `Course` (`c_id`) ON DELETE RESTRICT ON UPDATE RESTRICT, 

  # Score.s_id(外键)指向 Stu.s_id(主键)
  CONSTRAINT `s_id` FOREIGN KEY (`s_id`) REFERENCES `Stu` (`s_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
);

若向 Score 表中插入一行数据, 其中 Score.s_id与Score.c_id 值必须存在于 Stu 表与Course 表中, 否则会报错

Stu 表

s_id	s_name
0000-XAB-1	Xi
0000-XAB-2	Sui
0000-XAB-3	Hu

Course 表

c_id	c_name
AV-170001	结构力学
AV-275455	量子力学
AV-999900	流体力学

向 Score 表中插入Xi量子力学成绩 97 分

mysql> insert info Score (s_id,c_id,score) values ('0000-XAB-1','AV-275455',97);
Query OK, 1 row affected (0.01 sec)

mysql> select * from Score;
+------+---------------+------------+------+
| r_id | s_id         | c_id        | score|
| 1    | '0000-XAB-1' | 'AV-275455' | 97   |
+------+---------------+------------+------+
1 row in set (0.00 sec)

插入正常, 因为s_id, c_id 值都在 Stu 与 Course 表中存在

现在Xi有门课编号为 BV-000001, 成绩为 80 分, 插入 Score

mysql> insert info Score(s_id,c_id,score) values ('0000-XAB-1','BV-000001',80)
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails ('data','Score',CONSTRAINT 'c_id' FOREIGNKEY ('c_id') REFERENCES 'Course' ('c_id'))

显示插入错误, 因为 c_id = BV-000001 在其指向 Course 表中不存在, 产生了约束错误

现在有位同学编号为 X-47-W, 其量子力学成绩为 80 分, 插入 Score 表

同样显示因为外键约束错误, 插入失败, 因为 s_id 值在 Stu 表中不存在

mysql

指令

TODO

事务

事务处理可以用来维护数据库完整性, 保证 $SQL$ 语句要么全执行, 要么全不执行

事务用来管理 INSERT, UPDATE, DELETE 语句, 只有使用 $Innodb$ 数据库引擎的数据库或表才支持事务

特性

原子性 Atomic

一个事务中全部操作, 要么全部都完成, 要么全部不完成.事务在执行过程中发生错误, 会被回滚(rollback)到事务开始前状态

一致性 consistency

在事务开始之前和事务结束以后, 数据库完整性没有被破坏, 表示写入资料必须完全符合所有预设规则, 包含资料精确度、串联性以及后续数据库可以自发性地完成预定工作

隔离性 isolation

隔离性可以防止多个事务并发执行时, 由于交叉执行而导致对数据进行读写和修改产生不一致情况

graph LR;
    S(事务隔离级别)
    S-->A(读未提交<br>Read uncommitted)
    S-->B(读提交<br>Read committed)
    S-->C(可重复读<br>Repeatable read)
    S-->D(串行化<br>Serializable)
    S-->E(持久性<br>Durability)

事务处理结束后, 对数据修改即是永久

控制

graph LR;
    S(操作指令)
    S-->A(事务开始)-->A1(BEGIN)
    S-->B(事务回滚)-->B1(ROLLBACK)
    S-->C(事务确认)-->C1(COMMIT)

开始

CREATE TABLE info (
    "id"   text NOT NULL, 
    "name" text, 
    PRIMARY KEY ("id")
);

BEGIN;

INSERT INTO info (id, name) VALUES ('000A-X', 'WANG-QI');

# 事务确认
COMMIT;

mysql> select * from info;
+----------+-----------+
| id       | name      |
| '000A-X' | 'WANG-QI' |
+------+---------------+
1 row in set (0.00 sec)

回滚

BEGIN;

INSERT INTO info (id, name) VALUES ('000B-Y', 'QING-XU');

COMMIT;

BEGIN;

INSERT INTO info (id, name) VALUES ('000C-Z', 'SONG-LING');

# 回滚, 撤回插入
ROLLBACK;

可以看到第二条数据并未写入

mysql> select * from info;
+----------+-----------+
| id       | name      |
| '000A-X' | 'WANG-QI' |
| '000B-Y' | 'QING-XU' |
+------+---------------+
2 row in set (0.00 sec)

BEGIN;

DELETE FROM info WHERE id = '000B-Y';

# 回滚, 撤销删除
ROLLBACK;

数据没有被删除

mysql> select * from info;
+----------+-----------+
| id       | name      |
| '000A-X' | 'WANG-QI' |
| '000B-Y' | 'QING-XU' |
+------+---------------+
2 row in set (0.00 sec)

授权

创建

create user '用户名'@'host' identified by '密码';

host取值	含义
localhost	本地登录用户
ip	允许某个 ip 登录
%	允许所有 ip 登录

创建用户dmjcb, 密码123456

create user 'dmjcb'@'%' identified by '123456';

查看信息

select host, user, authentication_string from mysql.user;

查看

查看所有用户权限

show grants;

查看指定用户权限

show grants for'用户名'@'%';

授权

授权仅root用户有权操作

grant all privileges on *.* to '用户名'@'%' identified by '密码';

参数	含义
all privileges	所有权限, 包括`SELECT`, `UPDATE`, `CREATE`, `DELETE`, `DROP`
ON	指定权限针对哪些库和表
前一个 `*`	指定数据库, `*`表示全部
后一个 `*`	指定表, `*`表示全部
identified by	指定用户登录密码, 可省略
TO	表示将权限赋予某个用户
@	限制地址, 可以是 IP、域名与%(表示任何IP)

授予dmjcb用户对所有数据库所有表所有操作权限

grant all privileges on *.* to 'dmjcb'@'%' identified by '123';

新建用户 user_test, 仅授予 SELECT 权限

create user 'user_test'@'%' identified by '999';

grant select on *.* to 'user_test'@'%';

使用 user_test 登录, 尝试创建数据库 test_db, 可以发现被拒绝了

create database test_db;

使用 user_test 用户, 尝试查询

docker部署

docker run -itd --name=mysql_test -p 3306:3306 -v $PWD/mysql:/var/lib/mysql -e MYSQL_PASSWORD=456 -e MYSQL_ROOT_PASSWORD=123 mysql:5.7

远程连接

mysql -u 用户名 -p

允许远程登录

grant all privileges on *.* to '用户名'@'%' identified by '密码';

flush privileges;

常见问题

中文乱码

echo "character-set-server=utf8" >> /etc/mysql/mysql.conf.d/mysqld.cnf

service mysql restart

降低占用内存

cat >> /etc/mysql/mysql.conf.d/mysqld.cnf <<EOF
performance_schema_max_table_instances=400

table_definition_cache=400

table_open_cache=256
EOF

service mysql restart

oracle

部署

docker run -itd --name=orace -p 8080:8080 -p 1521:1521 truevoly/oracle-12c

交互

进入

sys 用户默认密码 oracle

sqlplus sys as sysdba

查看

查看表空间

select tablespace_name from dba_tablespaces;

查看表空间路径

select * from dba_data_files;

SQL> select * from dba_data_files;
FILE_NAME                              FILE_ID TABLESPACE_NAME BYTES   BLOCKS STATUS    RELATIVE_FNO AUT MAXBYTES   MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/xe/users01.dbf 6       USERS           5242880 640    AVAILABLE 6            YES 3.4360E+10 4194302   160          4194304    512         ONLINE

创建名为 space_demo, 路径为’/u01/app/oracle/oradata/xe/space_demo.DBF’的表空间

CREATE TABLESPACE space_demo DATAFILE '/u01/app/oracle/oradata/xe/space_demo.DBF' SIZE 32M AUTOEXTEND ON NEXT 32M MAXSIZE 20480M EXTENT MANAGEMENT LOCAL;

创建名为 user_test 的用户, 其密码是 123, 默认表空间 space_demo

CREATE USER user_test IDENTIFIED BY 123 ACCOUNT UNLOCK DEFAULT TABLESPACE space_demo;

将connect, resource, dba 权限赋予 user_test 用户

GRANT CONNECT, RESOURCE, DBA TO user_test;

sqlserver

docker部署

docker run -itd -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=dmjcb@13546!" -p 1433:1433 --name sqlserver2019 mcr.microsoft.com/mssql/server:2019-latest

交互

进入

docker exec -it sqlserver2019 bash

/opt/mssql-tools18/bin/sqlcmd -S localhost -U SA -P "dmjcb@13546!" -C

查看

显示所有数据库

SELECT Name FROM Master..SysDatabases ORDER BY Name
go

显示表

SELECT * FROM [表名]
go

python

主机地址需写为: ip, 端口, 注意是逗号

import pymssql

conn = pymssql.connect(host = IP:端口, user = "sa", password = 密码, database = 数据库, charset = 'utf8')
# 使用cursor()方法获取操作游标
cursor = conn.cursor()

sql = "SELECT * FROM 表"

try:
    # 执行SQL语句
    cursor.execute(sql)
    results = cursor.fetchall()
except:
    ...

# 关闭数据库连接
conn.close()

上篇欧拉函数

下篇费马小定理