数据库 打印

DDL :
CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

CREATE TABLE IF NOT EXISTS Employees (
  EmployeeID INTEGER PRIMARY KEY,
  FirstName TEXT NOT NULL,
  LastName TEXT NOT NULL,
  Email TEXT UNIQUE,
  DepartmentID INTEGER,
  FOREIGN KEY(DepartmentID) REFERENCES Departments(DepartmentID)
);


DROP TABLE database_name.table_name;

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Allen', 25, 'Texas', 15000.00 );

UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];
UPDATE COMPANY SET ADDRESS = 'Texas' WHERE ID = 6;
UPDATE COMPANY SET ADDRESS = 'Texas', SALARY = 20000.00;

DELETE FROM COMPANY WHERE ID = 7; delete table's tuble

权力:
GRANT SELECT, INSERT ON table1 TO user1 WITH GRANT OPTION;
REVOKE SELECT, INSERT ON table1 FROM user1 cascade;

select :

CREATE VIEW v10BestSellingArtists  AS
SELECT tmp.Artist as Artist,  count(tmp. AlbumID) as TotalAlbum, IFNULL(sum(tmp.TotalTrackSales),0) as TotalTrackSales

FROM (
SELECT artists.ArtistID as ID,artists.Name as Artist,IFNULL(sum(invoice_items.Quantity),0) as TotalTrackSales,
albums.AlbumID
FROM  tracks left join albums ON tracks.AlbumID=albums.AlbumID
             LEFT JOIN artists  ON albums.ArtistID=artists.ArtistID
             LEFT JOIN invoice_items ON tracks.TrackID=invoice_items.TrackID
GROUP BY albums.ArtistID,albums.AlbumID
) as tmp
GROUP BY tmp.ID
// having
ORDER BY TotalTrackSales DESC LIMIT 10; //ASC


DROP TRIGGER IF EXIStS test_trigger;

CREATE TRIGGER test_trigger
AFTER INSERT ON tb_test FOR EACH ROW
BEGIN
    UPDATE tb_test SET age = age - 1 WHERE name = new.name;
END;



2.锁的粒度 Granularity of lock
1）锁的粒度越高，并发问题越少，但效率越低
2）全局锁，锁整个数据库，常用于备份
3）表级锁：表锁，意向锁
4）行级锁，锁住一行
3. 意向锁（Intention）https://zhuanlan.zhihu.com/p/185003485
1）为什么要有意向锁？
行锁与表锁大部分时候互斥。行共享锁兼容表共享锁，排斥表排他锁；行排他锁排斥表共享锁和表排他锁
无意向锁时，DBMS每次加表锁时都要遍历每一行，去判断表中有没有行持有排斥该表锁的行锁。而有了意向锁后，DBMS只需检查有无和该表锁排斥的意向锁
2）意向锁分为意向共享锁和意向排他锁
3）两种意向锁相互兼容。意向共享锁兼容意向共享锁和意向排他锁；意向排他锁兼容意向共享锁和意向排他锁
4）加行锁时自动加意向锁。加行共享锁时会自动加意向共享锁，加行排他锁时会自动加意向排他锁

P1 DB,是什么, 好处,  DB mangement 是什么 ,好处 , 最后 提到了视图啥的

P2 3级架构, 免疫, modeling ,  DMBS function and compoenents C/S 模型
P5 relation = table 关于表的大部分知识 , +  view的定义+ 更新+ 一点点功能
p6 SQL 语言 ddl ,dml
P7 复杂的查询语言 
P8 关系代数
p11 RE模型, 强实体,弱实体.. multipli....  fan chasm 
p13 范式化
p14 数据库设计吗, 总的概括
p15 数据库设计, ER关系, 到实际上面的建表 ,1:*.... golbal啥的 
p16 依然, 里面有数据约束 ... 
P17 数据库威胁因素, 保护措施 , 在web上面的安全 , 数据管理员, 数据库管理员, 相关的法律啥的
p18 事务, 会产生的问题, 锁
p19 死锁的解决方法, 和其他东西解决并发,  数据库的恢复..
 
 


 

道德:

data那个啥

Data subject: the subject who the data pertains to, and who has certain legal and moral rights over the way that data is collected or used.

Personal data: a particularly morally important type of data. This is data which relates to a living individual who can be identified:

1.From that data or

2.From that data and other information which is in the possession of, or is likely to come into the possession of, the data controller.

Sensitive personal data is even more morally important. This involves matters which could be used to discriminate against people, such as their ethnicity, sexuality, and religion.

 

We can think of the “lifecycle” of data as having four main parts. Each part has its own accompanying ethical issues:

  1. Collection: issues of consent.
  2. Processing: issues of privacy.
  3. Storage: issues of security.
  4. Use: issues of objectivity and responsibility.
 Informed consent
 

Why do we think privacy is valuable?

  • Privacy is connected with individuality, the capacity to think, speak, and act as we like.
  • Privacy allows people to have freedom or autonomy over their own lives, without being subject to control.
  • Intimacy with others means that we select who we allow into our private lives. Without privacy, there is no intimacy.
  • Psychological studies show that people who lack (or perceive themselves to lack) this basic ability to control their own lives suffer physically and mentally. Privacy is necessary for well-being.
 
解决方法在收集数据时:

Transparency: Make sure the algorithms used, data collected, uses of the tools and so on is transparent to other peers and to the public.

Communication: Make clear to users and others for what purposes their data is being collected for, so that data subjects can give informed consent, and hold organisations accountable if they use this data in another way.

Data Stewardship: Make sure that data is securely kept, and also that it is not forwarded or sold to any untrustworthy third parties.

pole 那个个B

第二个ppt 是关于数据分析的

带来的问题:

  • Invasion or eroding of privacy – individually or societally.
  • Categorization of people into groups for ease of analysis. Could be discriminating or just disrespectful. Algorithms could perpetuate bias. For example an algorithm used for hiring candidates could filter out women if they have not, in general, been successful in the past.
  • Allowing the control, rather than the prediction, of people’s actions.
  • Inappropriate or illegal access to data or analysis tools.
  • Legal but immoral access to data or analysis tools.

Surveillance 会带来好处和坏处等等

剑桥那个分析

posted @ 2023-05-31 12:11  VxiaohuanV  阅读(19)  评论(0)  编辑  收藏  举报