SQL知识总结

1、数据类型

CHAR、 CHARACTER 通常使用单引号

INT、INTEGER 从不用引号

DEC、DECIMAL千万不能使用引号

DATATIME、TIME或TIMESTAMP必用引号

VARCHAR 必用单引号

DATE 必用单引号

BLOB 必用单引号

2、SQL语句

创建数据库  CREATE DATABASE gregs_list;

进入数据库  USE DATABASE

创建表  

CTEATE TABLE doughnut_list

(

doughtnut_name VARCHAR(10),

doughtnut _type  VARCHAR(10)

);

查看创建的my_contacts表结构  DESC my_contacts;

删除表   DROP TABLE my_contacts;

插入数据  INSERT INTO your_table(column_name1,column_name2,) VALUES ('value1','value2');-->数字可不加单引号

查看表  SELECT * FROM my_contacts;

NULL --> 不等于零，不等于另一个NULL，不等于空值；值可以是NULL，但不等于NULL，NULL代表未定义的值

用DEFAULT填补空白  CREATE TABLE doughnut_list(doughnut_cost DEC(3,2) NOT NULL DEFAULT 1.00);

2.1、SELECT语句

SELECT * FROM my_contacts;

SELECT * FROM my_contacts WHERE first_name = ''Anne;

语句中有单引号时转义

SELECT * FROM my_contacts WHERE location = 'Grover\'s Mill, NJ' ;

SELECT * FROM my_contacts WHERE location = 'Grover''s Mill, NJ';

SELECT drink_name FROM easy_drinks WHERE main = 'soda' AND amount1 > 1;

SELECT drink_name FROM easy_drinks WHERE main = 'orange juice' OR main = 'apple juice';

SELECT drink_name FROM drink_info WHERE calories IS NULL;

SELECT * FROM my_contacts WHERE location LIKE '%CA';-->百分号是任意数量的未知字符的替身

SELECT * FROM my_contacts WHERE location LIKE '_CA';-->下划线是一个未知字符的替身

SELECT drink_name FROM drink_info WHERE calories BETWEEN 30 AND 50;

SELECT date_name FROM black_book WHERE rating IN ('innovative','fabulous','delightful');

SELECT date_name FROM black_book WHERE rating NOT IN ('innovative','fabulous','delightful');

NOT 可以和BETWEEN或LIKE 一起使用，NOT一定要紧接在WHERE后面,NOT IN 例外

SELECT drink_name FROM drink_info WHERE NOT carbs BETWEEN 3 AND 6;

SELECT date_name FROM black_book WHERE NOT date_name LIKE 'A%' AND NOT date_name LIKE 'B%';

2.2、DELETE语句

DELETE FROM clown_info WHERE activities = 'dancing';

DELETE FROM your_table;可以删除表中的每一行

2.3、UPDATE语句

UPDATE doughnut_ratings SET type = 'glazed' WHERE type = 'plain glazed';

UPDATE drink_info SET cost = cost +1 WHERE drink_name = ''Blue Moon;

2.4、查看创建表的语句 SHOW CREATE TABLE my_contacts;

3、表设计

原子性数据：一小块无法或不应该分割的信息。

原子性数据的规则：

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

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

第一范式1NF：每个数据行必须包含具有原子性的值；每个数据行必须有独一无二的识别项，即主键。

主键用于独一无二的识别出每条记录。

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

最佳主键可能是新的主键，即认为添加一列作为主键。

-------------------------------------------------------------------------------------

加上主键的CREATE TABLE：

--> CREATE TABLE my_contacts

(

contact_id  INT  NOT NULL,

PRIMARY KEY (contact_id)

)

或

--> CREATE TABLE my_contacts

(

contact_id  INT  NOT NULL PRIMARY KEY,

)

--------------------------------------------------------------------------------------

为contact_id列加上关键字AUTO INCREMENT,可以让SQL软件自动为该列填入数字，第一行填入1，后面的依序递增

--> CREATE TABLE my_contacts

(

contact_id  INT  NOT NULL AUTO_INCREMENT,

PRIMARY KEY (contact_id)

)

4、ALTER

用ALTER来添加某列、设置主键 ALTER TABLE my_contacts ADD COLUMN contact_id INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (contact_id);

FIRST 即为第一列，其他如SECOND、THIRD等皆可

AFTER your_column、BEFORE your_column 某列之后、之前

LAST最后一列

一些关键字：

CHANGE：可同时改变现有列的名称和数据类型

--> ALTER TABLE project_list CHANGE COLUMN number proj_id INT NOT NULL AUTO_INCREMENT;

MODIFY：修改现有列的数据类型或位置

-->ALTER TABLE project_list MODIFY COLUMN proj_desc VARCHAR(120);

ADD：在当前表中添加一列-----可自选类型

DROP：从表中删除某列

-->ALTER TABLE project_list DROP COLUMN start_date;

更改表名：ALTER TABLE projects RENAME TO project_list;

删除已建的主键：ALTER TABLE your_table DROP PRIMARY KEY;

去除自增功能：ALTER TABLE your_table CHANGE your_id your_id INT(11) NOT NULL;将AUTO_INCREMENT去掉即可

字符串函数

RIGHT( )-->SELECT RIGHT(location,2) FROM my_contacts;-->location是列名，2是要从列的右侧开始选取的字符数量

LEFT( )同RIGHT，区别在于从列的左侧开始选取字符

SUBSTRING_INDEX( )-->SELECT SUBSTRING_INDEX(location,',',1) FROM my_contacts;

-->location为列名，‘，’即为要寻找的符号，1表示命令要寻找第一个逗号，然后截取它前面的所有内容

这个函数截取部分列值，寻找引号里面的字符串，然后取出它前面的所有内容

UPPER(your_string)和LOWER(your_string) 分别可把整租字符串改为大写或小写

REVERSE(your_string)反转字符串里的字符排序

LTRIM(your_string)与RTRIM(your_string)会返回清除多余空格后的字符串，他们分别清除字符左侧和右侧的多余空格

LENGTH（your_string）返回字符串中的字符数量

超大型UPDATE，利用CASE检查现有的值和条件，结合所有UPDATE语句

UPDATE my_table SET new_column = 

CASE 

   WHEN  column1=somevalue1

       THEN newvalue1

   WHEN column2=somevalue2

        THEN newvalue2

   ELSE  newvalue3

END;

ORDER BY-->SELECT title,category FROM movie_table WHERE category = 'family' ORDER BY title;排序

SELECT title,category,purchased FROM movie_table ORDER BY category,purchased;按两列排序，先按第一列排序，然后第二列根据第一列拍好之后进行排序

默认升序ASC，DESC是降序  SELECT title,purchased FROM movie_table ORDER BY title ASC, purchased DESC;

SUM-->SELECT SUM(sales) FROM cookie_sales WHERE first_name = 'Nicole';求和

GROUP BY-->SELECT first_name,SUM(sales) FROM cookie_sales GROUP BY first_name ORDER BY SUM(sales)DESC;分组

AVG-->SELECT first_name,AVG(sales) FROM cookie_sales GROUP BY first_name;平均值

MIN-->SELECT first_name,MIN(sales) FROM cookie_sales GROUP BY first_name;最小值

MAX-->SELECT first_name,MAX(sales) FROM cookie_sales GROUP BY first_name;最大值

 COUNT-->SELECT COUNT(sale_date) FROM cookie_sales;COUNT返回sales_date列中的行数。如果数据值是NULL，则不纳入计算

DISTINCT-->SELECT DISTINCT sale_date FROM cookie_sales ORDER BY sale_date;

SELECT first_name,SUM(sales) FROM cookie_sales GROUP BY first_name ORDER BY SUM(sales) DESC LIMIT 2;指定呈现的结果为两行

SELECT first_name,SUM(sales) FROM cookie_sales GROUP BY first_name ORDER BY SUM(sales) DESC LIMIT 1,1;SQL从0开始计数，所以1代表第二条记录，第二个1代表记录数量