SQL学习笔记（二） MySQL数据库的创建和使用（下）

本文为《SQL学习指南》一书的学习笔记，供自己记录查找使用。如有侵权可私信删除。

今日内容：chap 2 创建与修改表 

包含四个基础的SQL语句： insert ，update ,delete , select .

目录

一、插入数据

生成数字型主键数据

 insert语句

二、更新数据

 三、删除数据

四、日期转换

今日学习命令小结 Day 2,3

---

---

一、插入数据

使用insert语句为空表添加数据/为已经存在数据的表加入数据.

insert语句包含三个主要组成部分：

· 所要插入数据的表的名称

· 表中需要使用的列的名称

· 需要插入到列的值

一些关于null的解释《暂缺》

生成数字型主键数据

首先为主键列打开自增（auto-increment）特性。也可以使用SQL方案语句修改已存在的表定义：alter table.

alter table person modify person_id smallint unsigned auto_increment;

该语句实质上重新定义了person表中的person_id列.现在如果再次使用describe命令，则会看到person_id的 extra 列出现了自增特性。不过在这里，如果直接使用ALTER TABLE语句会出现如下报错，原因是person表中的person_id字段为food表的外键，不能直接进行修改。解决方法是对表进行锁定后解除外键约束，再改表，之后重新添加外键约束。

 报错：Cannot change column 'person_id': used in a foreign key constraint ..

解决：

LOCK TABLES person WRITE,food WRITE;

SHOW OPEN TABLES where in_use > 0;

ALTER TABLE food DROP FOREIGN KEY fk_fav_food_person_id;

ALTER TABLE person MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT;

ALTER TABLE food ADD CONSTRAINT fk_fav_food_person_id FOREIGN KEY (person_id) REFERENCES person(person_id);

 于是便修改好了主键的自增特性，使用desc语句可查看：

这里又学到一个锁表语句lock table.不过这个我们可以以后再说，先回到本节的学习中来……

 当向person表添加数据时，可以简单地将person_id赋值为null，MySQL会自动向该列提供下一个可用的主键数字（默认情况下从1开始自增）。

有话要说：发现列名lname被错误写成了iname，使用alter table 语句进行修正：

ALTER TABLE <table-name> CHANGE <old-column> <new-column> <type-of-data>;

 insert语句

基本形式（注意：values的s不要忘记）

INSERT INTO  <table-name> (column_1,column_2,...)  VALUES  (value_1,value_2,...)

 INSERT INTO person
    -> (person_id,fname,lname,gender)
    ->  VALUES(null,'William','Turner','M');

通过select语句，我们即可查看这条数据。

直接查看：

SELECT * FROM person;

SELECT person_id,fname,lname FROM person;

 注意到：person_id我们输入为null，MySQL服务器自动为主键产生值为1.上述查询省略了查询条件，只是简单获取表中所有数据。如果表中数据多于一行，那么可以用where子句指定想要提取的数据，即person_id 列值为1的行：

 SELECT person_id,fname,lname,birth_date
    -> FROM person
    -> WHERE person_id=1;

 几点注意：

• 并未为birth_date一列赋值。没有问题，因为允许为null。
• 若为birth_date一列提供字符串‘1972-05-27’为该列的值，MySQL会自动将其转换为日期类型（前提是符合一定的格式）。稍后使用update语句将其补全。
• 列与值的数目及类型需要匹配。

为了后续文章的可读性，简要说明一下我们又（悄悄地）对表又进行了什么操作。

1.完善William喜爱的其他食物信息，并排序：

INSERT INTO food (person_id,food) VALUES (1,'pizza');
INSERT INTO food (person_id,food) VALUES (1,'cookies');
INSERT INTO food (person_id,food) VALUES (1,'nachos');
SELECT food 
-> FROM food 
-> WHERE person_id=1 
-> ORDER BY food;

2.增加Susan的信息：

 INSERT INTO person
    -> (person_id,fname,lname,gender,birth_date)
    -> VALUES (null,'Susan','Smith','F','1975-11-02');

（ select一下，发现person_id被自动补全了~ ）

二、更新数据

对于已有的数据，更改其中某一列上的值，我们使用update语句。在上文中，我们忽略了William的生日，此处对其进行更新：

 UPDATE person
    -> SET birth_date= '1972-05-27'
    -> WHERE person_id = 1;

得到以下结果，说明修改成功：

 总结一下UPDATE语句的结构：

        UPDATE <table-name>

        SET <column_1>=<value_1>,

                <column_2>=<value_2>,

                ...

        WHERE <column_primary> = <xxx> ;

 三、删除数据

使用DELETE语句：

DELETE FROM person WHERE person_id = 2;

若省略WHERE子句，则会删除表中所有的行。

删除数据使用delete语句，而删除表使用drop语句:

DROP TABLE food,person;

 

四、日期转换

默认的日期格式仅为‘YYYY-MM-DD’，对于更多的日期格式，应对其进行转换。可以使用str_to_date函数指定所用字符串的格式，从而使字符串能够被丝滑地转化为日期类型：

UPDATE person

-> SET birth_date = str_to_date(‘Dec-21-1980’,’%b-$d-%Y’)

-> WHERE person_id = 1;

可能会用到的格式有：   

%a  Mon,Tue,…

%b  Jan,Feb,…

%c  月份的数字形式：0-12

%d  日在月中的次序  00-31

%f  毫秒数

%H  24小时制  小时   %h  12小时制  小时

%i  分钟   %j  秒

%M  完整的月名称  January

%m  月份的数字表示  （？）

%p  AM,PM

%s  秒数

%W  完整的星期名

%w  天在星期中的次序  0=周日..6=周六

%Y  四位数字的年份

今日学习命令小结 Day 2,3

修改表的属性  ALTER TABLE 表名

UPDATE语句：

        UPDATE <table-name>

        SET <column_1>=<value_1>,

                <column_2>=<value_2>,

                ...

        WHERE <column_primary> = <xxx> ;