深入浅出sql笔记

一、基本语句:

创建数据库:CREAT DATABASE gregs_list;
注:(1)数据库标准大小,区分命令和文件名
(2)文件名中不可以出现空格,用下划线替代
(3);表示命令结束
使用数据库:USE gregs_list;
新增列:列名称+数据格式
注:数据格式包括:(1)VARCHAR文本数据,最长255个字符
(2)CHAR或CHARACTER预先设定好长度,后加(长度)
(3)DATE记录日期;
DATETIME适合存储将来时间;
TIMETAMP用于记录当下这个时刻
(4)INT整数
(5)DEC提供数值空间
(6)BLOB大量文本数据
表明不能未定义:列名+NOT NULL
设置默认值:列名+DEFAULT(默认值)
查看表:
1、DESC my_contacts;
2、DESCRIBE my_contacts;查看表的构成,包括是否为主键,每列存储的数据类型
3、SHOW CREATE TABLE my_contacts;(查看表的创建方式)
删除表:DTAP TABLE my_contacts;(删除表和表里所有数据)
插入值:INSERT INTO my_contacts
(列1,列2,…,列n)
VALUES
(值1,值2,…,值n)
注:(1)列和值可以换位置,也可以只输入部分列,但要保证一一对应
(2)CHAR、VARCHAR、DATE 、BLOB的值需要加单引号;DEC、INT不需要
(3)列名可以全部省略,值的顺序与原来列的顺序对应
(4)如果文本中包含单引号则在单引号前加\或‘,表示转义
查找表:情况1:
SELECT表名(查看表内所有列)
WHERE first_name=‘Anne’;
情况2:
SELECT 列1,列2,列3 FROM 表名(查看部分列)
WHERE type=‘plain glazed’
AND
rating=10;
AND

情况3:
SELECT
FROM my_contacts
WHERE location LIKE ‘%CA’;(在locaton列中查找所有以"CA"结尾的值)
注:(1)判断句中可以是关系运算符:>,<,>=,<=,<>;
(2) 可以是IS NULL
(3)模糊查找的时候:LIKE与通配符搭配使用
1)%:‘%im’(以im结尾,前面字符长度无所谓)
2)_:‘_im’(im前只有一个字符)
(4)条件之间可以用:
1)AND并且
2)OR或者
3)BETWEEN数值在某个范围之间
例:WHERE calories BETWEEN 30 AND 60;
4)IN在某个值的集合中选择
例:WHERE ratin IN(’innovative‘,’fabulous‘,’delightful‘);
NOT IN取值不在这个集合中
5)NOT和AND或OR连用(NOT 直接跟在WHERE和AND或OR之后)
例:WHERE NOT date_name LIKE ‘A%’
AND NOT date_name LIKE ‘B%’;
6)NOT和NULL一起使用:
例:WHERE main IS NOT NULL 同 WHERE NOT main IS NULL
删除行:DELETE(用法同SELECT)
DELETE FROM clown_info
WHERE
activities=‘dancing’;(删除某行)
DELETE FROM your _table(删除每一行)
更新数据:UPDATE SET
情况1: 根据WHERE子句筛选出行,对该列内容赋新值
UPDATE daoughnut_ratings
SET type=‘glazed’
WHERE type=‘plain glazed’;
注:(1)SET后可跟多个赋值,逗号分开;
也可以在原来的基础上运算,如cost=cost+1
(2)UPDATE可用于更新单一行或多行,一切都交给WHERE
情况2:在新的一列中填充其他列的部分值
UPDATE my_contacts
SET state=RIGHT(location,2);
更改数据:ALTER
(1)更换表的名称:(把projekts改名为project_list)
ALTER TABLE projekts
RENAME TO project_list;
(2)同时改变列的名称和类型
ALTER TABLE project_list
CHANGE COLUMN number proj_id INT NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY(proj_id); (把number列改名为proj_id,不能未定义,自动递 增,设为主键)
CHANGE 其他列
(3)只更改列的数据类型,不改变列的名称
ALTER TABLE project_list
MODIFY COLUMN proj_desc VARCHAR(120)
(4)增加新的列:
ALTER TABLE my_contacts
1)ADD COLUMN phone VARCHAR(10) FIRST;(放在第一列)
2)ADD COLUMN phone VARCHAR(10) LAST;(放在最后一列)
3)ADD COLUMN phone VARCHAR(10) THIRD;(放在指定个数列)
4)ADD COLUMN phone VARCHAR(10);(默认放在最后一列)
5)ADD COLUMN phone VARCHAR(10) AFTER model;(phone列放在model列后面)
(5)删除某列:
ALTER TABLE projuect_list
DROP COLUMN start_date;(删除start_date列)
DROP PRIMARY KEY;(删除主键列)

二、规范化表:

(一)第一范式:1NF
1、表规范化方法:
(1)让数据具有原子性:
规则1:具有原子性数据的列中不会有多个类型相同的值
规则2:具有原子性数据的表中不会有多个存储同类数据的列
(2)每个数据行必须有独一无二的识别项,人称主键。可以使用ID
规则1:主键不可以为NULL
规则2:插入新纪录时必须指定主键值
规则3:主键值不可以被修改
对已有的表进行修改:
方法一:查看原表sql代码,重新创建表
SHOW CREATE TABLE my_contacts;(查看表的创建方式)
(
contact_id INT NOT NULL AUT_INCREMENT
(创建主键列,注意必须NOT NULL,自动递增)
…(创建其他列)
PRIMARY KEY(contact_id)(指定主键的列)
);
方法二:在原表的基础上修改
ALTER TABLE my_contacts
ADD COLUMN contact_id INT NOT NULL AUTO_INCREMENT FIRST,
(FIRST意为把新增加的列放在最前面)
ADD PRIMARY KEY(contact_id);
2、表规范化优点:
(1)没有重复的数据,可以减小数据库的大小
(2)因为查找的数据较少,查询会更快速
(二)第二范式(2NF)
只要所有列都是主键的一部分或者表中有唯一主键列符合1NF的表,也会符合2NF
规则1:先符合1NF
规则二:没有部分函数依赖性
(三)第三范式(3NF)
规则一:符合2NF
规则二:没有传递函数依赖性

三、字符串函数

1、RIGHT(列名,个数)或LEFT(列名,个数)从列中选出指定数量的字符
例:SELECT RIGHT(location,2) FROM my_contacts;
2、SUBSTRING_INDEX(列名,寻找目标,第几个目标)可撷取部分列值
例:SELECT SUBSTRING_INDEX(location,’,’,1) FROM my_contacts;
3、SUBSTR(interests列,LENGTH(interest1)+2)返回本列内原始字符串的一部分,它接受字符串并切除我们用括号指定的第一部分,然后返回剩下的部分。
3、UPPER(列名)或LOWER(列名)可把整组字符串改为大写或小写
例:SELECT UPPER(’uSa‘);
4、REVERSE(字符串)反转字符串里的字符排序
例:SELECT REVERSE(’spaGHEtti’);
5、LTRIM(字符串)和RTRIM(字符串)返回清楚多余空格后的字符串,分别清楚字符左侧和右侧的多余空格
例:SELECT LTRIM(‘ dogfood’);
6、LENGTH(字符串)返回字符串中的字符数量
例:SELECT LENGTH(‘San Antonio,TX’);
7、CASE语句(按照相应的条件赋相应值)
UPDATE movie_table
SET category=
CASE
WHEN drama=‘T’ THEN ‘drama’
WHEN comedy=‘T’ THEN ‘comedy’
WHEN cartoon=‘T’ AND rating=‘G’ THEN ‘family’
ELSE ‘misc’
END;
8、ORDER BY(按照要求排列数据)
情况1:按照一种方式排序
SELECT title,category
FROM movie_table
WHERE
category=‘family’
ORDER BY titile;
注:顺序为:(NULL在数字和字母前面)
深入浅出sql笔记_第1张图片
情况2:按照多种方式排序(在第一种排序方式的基础上,按第二种方式排列)
SELECT title,category,purchased
FROM movie_table
ORDER BY category,purchased;
情况3:按照某种方式降序排列(ASC默认升序,DESC降序)
SELECT title,purchased
FROM movie_table
ORDER BY title ASC,purchased DESC;
9、SUM加总
情况1:在where语句规定的范围内加和
SELECT SUM(sales)
FROM cookie_sales
WHERE first_name=‘Nicole’;
情况2:分组加总
SELECT first_name,SUM(sales)
FROM cookie_sales
GROUP BY first_name
ORDER BY SUM(sales)DESC;
10、AVG平均
SELECT first_name,AVG(sals)
FROM cookie_sales
GROUP BY first_name;
11、MAX与MIN
SELECT first_name,MAX(sals)
FROM cookie_sales
GROUP BY first_name;
12、COUNT(返回指定列的行数)
SELECT COUNT (sale _date)
FROM cookie_sales;
13、查询
(1)DISTINCT(去掉重复后的值)
SELECT DISTINCT sale_date
FROM cookie_sales
ORDER BY sale_date;
注:DISTINCT是关键字不是函数
(2)LIMIT(起始偏移量,返回查询结果的数量)
情况1:返回前两条结果
SELECT first_name,SUM(sales)
FROM cookie_sales
GROUP BY first_name
ORDER BY SUM(sales)DESC
LIMIT 2;
情况2:返回第二条结果
LIMIT 0,4(代表从0开始,取第4个结果)

五、多张表

1、外键
外键是表中的某一列,引用到另一个表的主键
(1)外键可能与它引用的主键名称不同
(2)外键使用的主键也称为父键,主键所在的表又被称为父表。
(3)外键能用于确认一张表中的行与另一张表中的行相对应。
(4)外键的值可以是NULL,即使主键值不可为NULL。外键为NULL,表示在父表中没有相符的主键。
(5)外键值不需唯一——事实上,外键通常都没有唯一性
(6)外键约束(如果违反了规则,约束会阻止我们破坏表)
CREATE TABLE interests 创建子表interests表

int_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,指定int_id为主键
interest VARCHAR(50) NOT NULL,
contact_id INT NOT NULL,
CONSTRAINT my_contacts_contact_id_ 约束父表my_contacts中contact_id列
FOREIGN KEY (contact_id)指定外键contact_id列
REFERENCES my_contacts (contact_id)指定外键的来源和外键列在另一张表中的名称
);
2、多对多
junction table存储原始表的主键,然后把原始表的主键列与junction table的相应列产生链接。
3、组合键
组合键就是由多个数据列构成的主键,组合多列后形成具有唯一性的键。
4、依赖列
(1)当某列的数据必须随着另一列的数据的改变而改变时,表示第一列函数依赖于第二列。
速记符号:T.x->T.y,表示在关系表T中,y列函数依赖于x列。
(2)传递依赖:如果改变任何非键列可能造成其他列的改变,即为传递依赖。
传递函数依赖:任何非键列与另一个非键列由关联。

六、联接与多张表的操作

(一)AS:
1、把SELECT的查询结果填入新表
深入浅出sql笔记_第2张图片
2、设置列的别名
深入浅出sql笔记_第3张图片
3、设置表的别名(表别名又称相关名称)
深入浅出sql笔记_第4张图片

你可能感兴趣的