SQL数据库学习总结

意义：

1.创建数据库的好处，控制多位用户同时访问的行为 2.数据库名称不能有空格 3.命令结束用分号，好的代码习惯

一、创建函数

1.创建数据库 create database #关系型数据库 / RDBMS /Relational Database Management System

CREAT DATABASE gregs_list

创建表 creat table 和R的mutate函数很像

第一列	第二列
doughnut_name	doughnut_type
blooberry#覆盆子	filled
rockstar#摇滚明星	cruller#油条

CREAT TABLE doughnut_list              
(
   doughnut_nameVARCHAR(10),           #VARCHAR == variable character 
   doughnut_typeVARCHAR(6)             
);

数据类型：

数据类型	解释	用法
CHARACTER/CHAR	必须加上数据的长度	CHAR–邮编/单个字符F或 M CHAR(1)
DECJMAL/DEC	十进制	表示小数、百分比DEC(6,2)#6表示6位数，2表示两位小数，如5678.39
INTEGER/INT	整数	库存
BLOB	文本数据	一段文字
DATE/TIME/DATETIME	日期和时间

1.查看创建的表 DESC

DESC my_contacts;     # DESC == describe

2.使用数据库 use database

USE gregs_list

3.删除表及表中数据drop table

DROP TABLE my_contacts

eg:

CREAT DATABASE gregs_list;
USE gregs_list;
CREAT TABLE my_contacts
(
last_nameVACHAR(20),
first_nameVARCHR(30),       #not null == don't accept  null    
email VARCHR(50) NOTNULL,    
birthday DATE,
gender CHAR(1),
profession VARCHAR(50),
location VACHAR(50),
status_VARCHAR(20),
interests VARCHAR(20),
seeking VARCHAR(100),
cost DEC(4,2) NOTNULL DEFAULT 1.00    # 没有的值用1.00填充
);

二、按行输入数据( INSERT INTO)

INSERT INTIO your_table
(column_name1, column_name2, ...)
VALUES
('values1', 'value2', ...);


eg:
INSERT INTIO doughnut_purchases
(donut_type, dozens, 
topping, location, price)
VALUES
('jelly', 3, 
'sprinkles', 
'Grover\' Mill' 3.50);  # \表示后面的是前面的一部分 \ = ''

注释：INSERT语句加单引号—CHAR/VARCHAR/DATE/BLOB INSERT语句不加单引号—DEC/INT

改变列表顺序：列名和数据值一起调整即可
省略列名：列名可以省略不写，数据值必须全部填入
省略部分列：对应的列名和数据值即可
没有值的列：只填写知道的列名和数据值

三、查询 SELECT

单引号( 字符和日期)	不用引号（数字）
CHAR	DEC
VARCHAR	INT
DATE
DATETIME/TME/ TIMESTAMP
BLOB

通配符 NOT必须接在WHERE之后，但是和IN搭配例外

符号	含义	eg
<>	表示不等于
BETWEEN/ NOT BETWEEN	相当于<=&>=	SELECT drink_name FROM drink_info WHERE drink_name BETWEEN ‘G’ AND ‘P’
LIKE/NOT LIKE		SELECT drink_name FROM drink_info WHERE NOT carbs BETWEEN 3 AND 5
%abc abc%	以abc结尾/以abc开头
AND / OR,用NOT搭配	且/或	SELECT * FROM 　easy_drinks WHERE NOT main = ‘soda’ AND NOT main = ‘iced tea’ ;
IN/ NOT IN	IN前面的字符在后面的集合中	SELECT date_time FROM black_book WHERE rating IN ( ‘innovative’, ‘fabulous’, ‘delightful’);

SELECT *        
FROM my_contacts

条件查询

SELECT *                     # * means all column 
FROM my_contacts
WHERE first_name = 'Anne';   # = means is

结合查询

SELECT location                   
FROM doughnut_ratings
WHERE 
type = 'plain glazed'   
AND                         
rating = 10;

SELECT location                   
FROM doughnut_ratings
WHERE 
type LIKE '%plain'   # "通配符%在前表示以plain结尾"
AND                         # "AND结合两个where子句""
rating = 10;

用IS NULL选择NULL

SELECT drink_name
FROM drink_info
WHERE
calories IS NULL

四、DELETE AND UPDATE

DELETE FROM clown_info
WHERE
activities = 'dancing';

注释：1.不能删除列以及列中的值 2.可以删除一行或多行或所有行

更改变量中的值 #INSERT INTO 和DELETE同时执行，与R差异大,这里的思路是先增加新行，然后删除旧的行

# "把表中所有yellow值改为gold"
INSERT INTO drink_info  VALUES ('Blackthorn', 3, 8.4, 'gold', 'Y', 33),
('Greyhound', 4, 14, 'gold', 'Y', 50);

DELETE FROM drink_info WHERE color = 'yellow';

防止删错，用select确定真正想改变的记录（这样的方式并不高效）

#1. "选择要删除颜色为为黄色的行"
SELECT * FROM drink_info
WHERE
color = 'yellow';

#2. "用insert插入新纪录"
INSERT INTO drink_info  VALUES ('Blackthorn', 3, 8.4, 'gold', 'Y', 33),
('Greyhound', 4, 14, 'gold', 'Y', 50);

#3."用delete删除旧记录""
DELETE FROM drink_info WHERE color = 'yellow';

更高效的方式UPDATE SET #update不删除任何内容，只是替换旧记录

UPDATE your_table
SET first_column = 'glazed'   
second_column = 'plain glazed';


UPDATE my_contacts
SET state = RIGHT (location, 2)   # "提取州字符串，并存储为state列""

注释：1.UPDATE改变的是一列或者所有列， 2.可以更新一行或者多行，用where控制

UPDATE doughnut_ratings
SET
type = 'glazed'       # "指定的新值""
WHERE type = 'plain glazed';

优化的做法（运算符）

UPDATE drink_info
SET cost = cost+1
WHERE 
drink_name = 'Blue Moon'
OR
drink_name = 'Oh My Gosh'
;

是不是原子型数据要具体地看使用场景，对于外卖来说分割日期和时间/分割街道和门牌号码将会是灾难的。因为不需要单独查询门牌号码 1.你的表在表述什么事物？ 2.以何种方式表述事物？=》便于查询 3.列是否包含原子型数据=》可以让查询简单又直逼要害特点：一列中不会有多个类型/不会有多个列表示一个类型的数据

主键（primary key）：每行中独一无二的识别项（虚构的如ID，或者真实的如车牌号，身份证号等） 1.主键不可以为NULL 2.插入新记录必须指定主键值，否则主键值有可能为NULL 3.主键必须简洁且不可修改

CREAT TABLE doughnut_list   # "表名  doughnut甜甜圈""
（
 contact_id INT NOT NULL AUTO_INCREMENT,  #AUTO_INCREMENT表示自动为该列填入整数
 doughnut_nameVARCHAR(10) default NULL, 
 doughnut_typeVARCHAR(6) default NULL，
 PRIMARY KEY (contact_id)  #设定主键 
);

高效添加主键ALTER TABLE

USE gregs_list
ALTER TABLE my_contacts
ADD COLUMN contact_id INT NOT NULL AUTO_INCREMENT
ADD PRIMARY KEY(contact_id) FIRST/LAST/SECOND;
#remove primary key
ALTER TABLE my_contacts
DROP PRIMARY KEY;

修改表ALTER TABLE，以列的方式，加上where就可以改行

修改列名

ALTER TABLE  projekts     #old name
RENAME TO project_list;    #new name

修改列名和类型， ALTER CHANGE—-可改变列的类型

ALTER TABLE  project_list
CHANGE COLUMN number proj_id INT NOT NULL AUTO_INCREMENT
CHANGE COLUMN contractoronjob con_name VARCHAR(30)  #"contractoronjob 是旧名称， con_name是新名称""
ADD PRIMARY KEY (proj_id )

修改列类型，列名不变 ALTER MODIFY

ALTER TABLE  project_list
MODIFY COLUMN proj_desc VARCHAR(120)

删除列表

ALTER TABLE  project_list
DROP COLUMN  start_date   -"start_date为要从中移除的列""

案例练习：

hooptie

color	year	make	mo	howmuch
siliver	1998	Porsche	Boxter	17992.540
NULL	2000	Jaguar	XJ	15995
red	2002	Cadilac	Escalade	40215.9

car_table

car_id	VIN	make	model	color	year	price
1	RANKLK66N33G213481	Porsche	Boxter	siliver	1998	17992.54
2	SAEDA44B175B04113	Jaguar	XJ	NULL	2000	15995.00
3	3GYEK63NT2G280668	Cadilac	Escalade	red	2002	40215.90

代码：

ALTER TABLE hooptie                                     # "修改hooptie列表"
RENAME TO car_table,                                    # "表名改为car_table "
ADD COLUMN car_id INT NOT NULL AUTO_INCREMENT FIRST,    # "添加ID列，该列必须为整数且无NULL "  
ADD PRIMARY KEY(car_id),                                # "将ID列设为主键"
ADD COLUMN VIN VARCHAR(16) AFTER car_id,                # "添加vin列"
CHANGE COLUMN mo model VARCHAR(20),
MODIFY COLUMN color AFTER model,
MODIFY COLUMN year SIXTH,
CHANGE COLUMN how much price DECIMAL(7.2);

字符串函数：

UPDATE my_table
SET new_column =
CASE
  WHEN column1 = value1    # "当column1列的值等于value1时，将其值设定为newvalue1"
   THEN newvalue1
WHEN column2 = value2
   THEN newvalue2
 ELSE newvalue3            # "ELSE：任何不符合上述条件的记录都会被改为new value3"
END;

排序ORDER BY —–DESC/ASC (one column ,more column)

SUM/AVG/MIN/MAX SELECT SUM(sales) FROM cookie_sales WHERE first_name = ‘Nicole’ ;

LIMIT/OFFSET LIMIT19, 10 意思是查询第20-29名

COUNT,计算天数 DISTINCT & COUNT

分组求和 /求平均AVG/MIN/MAX

SELECT SUM(sales) 
FROM cookie_sales 
GROUP BY first_name
ORDER BY SUM(sales) DESC;

SELECT COUNT(DISTICNT sale_date) 
FROM cookie_sales ;

进阶查询：兴趣标签字符串

SELECT * FROM my_contacts
WHERE gender = 'F'
AND status = 'single'
AND state = 'MA'
AND seeking LIKE '%single M%' 
AND birthday > '1950-08-28'
AND birthday < '1960-08-28'
AND SUBSTRING_INDEX(interests,',',1)

跳出一张表：遇事不决，多表联结 ———— 沃茨基·硕德

UPDATE my_counts
SET interests = SUBSTR(interests, LENGTH(interest1)+2);

一个表变成两个表第一范式1NF

移除不具有原子性的列，并储存至新表
添加识别新表中每个观测的列（外键foreign key），外键的值可以为空值，外键约束（父表，子表）

第二范式2NF：已经符合1NF的表只要其所有列都是主键的一部分，也就符合2NF

先符合1NF
没有部分函数依赖性

第三范式：如果你的表有人工主键且没有组合主键，则符合2NF

符合2NF
没有传递函数依赖性

CREAT TABLE interests(
int_id INT NOT NULL_INCREMENT PRIMARY KEY,  # "加入主键"
interest VARCHAR(50) NOT NULL,
contact_id INT NOT NULL,                    # "创建外键索引"
CONSTRAINT my_contacts_contact_id_          # "称为CONSTRAINT，告知键的来源（my_contacts），键的名称（contact_id）"
FOREIGN KEY(contact_id)                     # "设定外键""
REFERENCES my_contacts(contact_id)          
);

表间的关系：一对一（用得少）

一对多（A表的某条记录对应B多条，B的每一条记录对应A每一条记录）

多对多（女士们和鞋的例子）把多对多转化为一对多

多对多：用JUNCTION TABLE储存原始表的主键组合键是有多个数据列构成的主键，组合各列后形成的具有唯一性的主键

意义：#

一、创建函数#

二、按行输入数据( INSERT INTO)#

三、查询 SELECT#

四、DELETE AND UPDATE#

意义：

一、创建函数

二、按行输入数据( INSERT INTO)

三、查询 SELECT

四、DELETE AND UPDATE