SQL学习笔记

1.数据库是保存表和其它相关SQL结构的容器

常用数据类型： CHAR VARCHAR INT DEC DATE DATETIME BLOG,INT DEC的值不加引号''

2.常用命令：

CREATE DATABASE dbname;

DROP DATABASE dbname;

USE dbaname;

CREATE TABLE listname(filedname DATATYPE(len) [NOT NULL[ DEFAULT va[AUTO_INCREASE]]]l,...);

DESC listname;//查看表的结构

DROP TABLE listname;

INSERT INTO listname (colname) VALUES (val),(val2);

SELECT * FROM tablename;

SELECT *(colname) FORM tablename WHERE 　fieldname='val';

SELECT *(colname) FORM tablename WHERE 　fieldname=(<,>,<>,>=,<=)'val' AND(OR) anotherfield='val';

SELECT *(colname) FORM tablename WHERE   fieldname IS NULL;

WHERE fieldname LIKE '%val'; //以val结尾,%任意字符串，_任意一个字符

WHERE fieldname BETWEEN  val1 IN val2;(val1 val2也包含在内)

WHERE fieldname IN (val1,...); //NOT IN

WHERE NOT fieldname AND (OR) NOT fieldname LIKE..;

DELETE FROM tablename WHERE .. ;(先用SELECT确认情况)；

UPDATE tablename SET fieldnam='newval',fieldnam2='NV'.. WHERE ..;

UPDATE tablename SET fieldnam=fieldnam+1 WHERE ..;(update能用在表的多条记录上，可以和基础数学符一起使用，可以操作数值数据)

SHOW CREATE TABLE tablename;

SHOW COLUMNS FROM tablename;

SHOW INDEX FROM tablename;

SHOW WARNING;

PRIMARY KEY(colname);

ALTER TABLE tablename ADD COLUMN colname INT NOT NULL AUTO_INCREMENT FIRST[AFTER colname]（列的位置）,ADD PRIMARY KEY(colname);//在原来的基础上增加表，其它修改命令 ：

　　CHANGE:可同时改变现有列的名称和数据类型,MODIFY:修改现有列的数据类型或位置；DROP从表中删除一列

ALTER TABLE oldtablename RENAME TO newtablename;

ATER TABLE tablename CHANGE COLUMN oldcolname newcolname INT NOT NULL AUTO INCREMENT,ADD PRIMARY KEY(newcolname );

如果把数据改变成另一种类型，可能会丢失数据

ATER TABLE tablename DROP COLUMN  colname;

ATER TABLE tablename DROP PRIMARY KEY;移除主键不改变该列的数据

每个表中只有一列可以加上AUTO_INCREMENT 该列必须为整型数据而且不能包含NULL

字符串功能函数选出文本列中的部分内容

SELECT RIGHT(colname,2) FROM tablename;

SELECT SUBSTRING_INDEX(colname,'str',num(寻找第几个字符)) FROM tablename;

等，字符串函数不会修改存储在表中的内容，只是把修改的摸样当成查询结果返回

UPDATE tablename SET colname=RIGHT(colname,2);//不加where条件

UPDATE tablename SET colname=

　　CASE 

　　　　WHEN colname='val' THEN 'val'

　　　　ELSE 'VAL'

　　END;

ORDER BY colname,colname..[DESC[ASC]];

SELECT firstname,SUM(salse) FROM tablename ORDER BY SUM(salse) GROUP BY firstname;

常用函数SUM MIN MAX AVG

DISTINCT去重复 SELECT COUNT(DISTINCT(sales_date)) FROM cookies_sales;

LIMIT返回查询结果的数量 SELECT first_name,SUM(sales) FROM cookies_sales GROUP BY first_name ORDER BY SUM(sales) DESC LIMIT 2;

 

 

使用数据的方式影响数据表的设置方式

设计表：

列之间如何产生关系，来一起描述某项事物

1.挑出事物，挑出你希望表描述的某样事物

2.列出一份关于那样事物的信息列表，这些信息都是使用表的必要信息

3.使用信息列表吧关于那样事物的综合信息拆分成小块信息，以便于组织表

当数据具有原子性，表示数据已经被分割至最小块了，不能或不应该再被分割了

原子性数据的规则：

一、具有原子性数据的列中不会有多个类型相同的值

二、具有原子性数据的表中不会有存储多个类型相同的列

让数据具有原子性时规范表的第一步

 

主键用于独一无二识别出每条记录，让每一列成为唯一的

主键不可以为NULL 插入新纪录时必须指定主键值 主键必须简洁 主键值不可以被修改

 

 

 对数据库内的数据描述（列和表），以及任何相关对象和各种连接方式的描述成为SCHEMA,模式。

外键是表中的某一列，它引用到另一个表的主键

外键使用的主键也被称为父键，主键所在的表被称为父表

外键能用于确认一张表中的行与另一张表中的 行相对应

外键值可以是NULL，即使主键值不可以为NULL

外键值不需唯一

确保外键有意义，用约束

插入外键列的值必须已经存在于父表的来源列中，这是引用完整性

外键不一定必须是父表的主键，但必须有唯一性

创建带有外键的表

CREATE TABLE interests (

int_id INT NOT NULL AUTO_INCREAMENT PRIMARY KEY,

interest VARCHAR(50) NOT NULL,

contact_id INT NOT NULL,

CONSTRAINT my_contacts_contact_id //约束 my_contacts 外键来源表

FOREIGN KEY (contact_id)//contact_id//外键名字 可以随意命名

REFERENCES my_contacts (contact_id)//外键列在另一表中的列名

)

数据表模式：

一对一：父表只有一行与子表的某行相对应

一对多：

多对多：创建中间表 转换为一对多的关系

 组合键就是有多个数据列构成的主键，组合各列后形成具有唯一性的键

 当某一列的数据必须随着另一列的数据改变而改变时，表示第一列函数依赖于第二列

第二范式 符合1NF，没有部分函数依赖性

第三范式 符合2NF，没有传递函数依赖性

AS 能把SELECT查询结果放到新表中/创建别名

联接：

1.交叉联接（笛卡尔积） SELECT t.toy,b.boy FROM toys AS t CROSS JOIN boys AS b;

2.内联接 就是通过查询中的条件移除了某些结果数据行后的交叉联接 inner join,相等联接，不等联接，自然联接（NATURE JOIN存在同名列时）

SELECT nt.title,nt.content,nc.class_name

FROM notesapp_notetable AS nt INNER JOIN
notesapp_noteclasstable AS nc ON
nt.noteclass_id=nc.id;

子查询 是被另一个查询包围的查询，也叫内查询（子查询设计起来比联接简单）

非关联子查询 ：如果子查询可以独立运行且不会引用外层查询的任何结果

关联子查询：内层查询的解析需要依赖外层查询的结果，常见用法是找出外层查询结果不存在于关联表里的记录 EXISTS NO EXSTS

 

外联接：

左外联接：LEFT OUTER IN会匹配左表的每一行及右表的符合条件的每行，当左表与右表存在一对多的关系时特别有用

左外连接的结果集中的NULL表示右表中没有找到与左表中相符的记录

右外联接根据左表评估右表

 

自联接：能把同一张表当成两张具有完全相同信息的表进行查询

自引用外键：出于其他目的而用于同一张表的主键 SELECT cl.name,c2.name AS boss FROM  clown_info c1 INNER JOIN clown_info c2 WHERE c1.boss_id=c2.id;

 

联合UNION （并集）把多张表的查询结果合并至一个表，UNION只能接受一个ORDER BY必须位于语句的末端

UNION规则：每个SELECT语句的列数必须一致；包含的表达式和统计函数必须相同；列的数据类型必须相同或可以相互转换；默认会清楚联合结果中的重复值，可以使用UNION ALL看到重复值

从联合创建表 CREATE TABLE tablename AS SELECT....

INTERSECT 交集 与 EXCEPT差集 （与union用法相同）

 

检查约束 CHECK 约束限定允许插入某个列的值，与WHERE子句使用相同的条件表达式，如果插入的值无法通过CHECK条件，则出现错误信息

 

创建视图：CREATE VIEW viewname AS 查询语句，虚拟表

查看视图：SELECT * FROM viewname;行为与子查询一样

利用视图插入、更新、删除

带有CHECK OPTION的视图，检查每个进行INSER INTO或DELETE的查询，根据视图中的WHERE子句来判断这些查询可否执行

 

事务：

是一群可以完成一组工作的SQL语句

在事务过程中，如果所有步骤无法不受干扰的完成，则不该完成任何单一步骤

ACID 原子性 一致性 隔离性 持久性

三种SQL事务工具可以保障账户的安全：

START TRANSACTION;持续追踪后续所有SQL语句

COMMIT;在commit之前数据库都不会发生改变

ROLLBACK；回到事务开始之前的状态

让事务在MySQL下运作：需要选用正确的存储引擎（有些不行）

存储引擎必须是BDB或InnoDB,两种支持事务的引擎之一

ALTER TABLE tablename TYPE=InnoDB

必须用START TRANSACTION告诉RDBMS事务开始

 

安全：

设置用户密码mysql:SET PASSWORD FOR 'root'@'localhost'=PASSWORD('password')

添加新用户：

CREATE USER username IDENTIFIED BY 'password';

赋予权限：GRANT

GRANT SELECT ON tablename(对表的操作) TO username;

GRANT ALL把对表所有的操作赋予赋予用户

databasename.*把权限范围运用到数据库的每张表上

A撤销权限：REVOKE

REVOKE SELECT ON tablename FROM username

 

角色：CREATE ROLE rolename;(MySQL没有角色功能)

授予角色权限：GRANT SELECT,INSERT ON tablename TO rolename;

使用角色：GRANT rolename TO username;

卸除角色：DROP ROLE rolename;

 

结合CREATE USER 与 GRANT:

GRANT SELECT ON clown_info TO username IDENTIFIED BY 'password';

558