SQL必知必会（3）

将之前学习的数据库知识在整理下，主要是看的《SQL必知必会》。这本书不愧是经典，入门数据库真的完全足够啦！

image

创建表create

创建表

比如创建一个user表，包含用户id、用户名、用户年纪等各种字段信息

创建表create

创建表

比如创建一个user表，包含用户id、用户名、用户年纪等各种字段信息

create table user(
  id int(10) unsigned not null auto_increment comment 'user_id',    -- auto_increment自增，指定为主键
  name varchar(20) not null comment "用户姓名",
  age tinyint unsigned null comment "用户年纪",
  email varchar(50) not null comment "用户邮箱",
  fee decimal(10,2) not null default 0.00 comment "用户余额",   -- 总长度是10位，保留2位有效小数位
  create_time timestamp not null comment "注册时间",
  primary key(id)  -- 指定主键
);

image

关于NULL

1. NULL表示没有值，空字符串是''
2. 空字符串是一个有效的值，它不是无值
3. 每个字段在创建的时候必须指定null或者not null
4. 允许为NULL的值不能作为主键
5. 主键primary key 和auto_increment必须连在一起使用

表中插入数据insert

1. 省略id号进行插入。字段和值一一对应即可

insert into user(name, email, age, fee, password)
                        values("xiaoming", "123456@qq.com", 20, 25.18, Password("xiaoming"))；  -- id号可以省略

笔记：

1. 相应的字段填上相应的信息
2. 字符串需要使用引号
3. 密码使用函数Password

2. 直接插入values值，此时id不能省略

insert into user values(3, "xiaoming", "123456@qq.com", 20, 25.18, Password("xiaoming"))；  -- id为3也不能省略

3. 插入部分数据

insert into user(name,   --省略了age字段
                 email, 
                 fee, 
                 password)
values("xiaoming", 
       "123456@qq.com", 
       25.18, 
       Password("xiaoming"))；

4. 插入从某个表中检索出来的数据

insert into user(name, email, age, fee, password)
select name, email, age, fee, password
from old_user;   -- 从 old_user 中检索出数据插入 user 中

5. 从一个表复制到另一个表select into

select *   -- 可以指定某些字段，而不是全部
into new_user
from old_user;   -- 将old_user中将数据全部复制到new_user中

更新和删除

更新表alter

alter table user
add phone char(20);  --增加一个字段

alter table user
drop column phone;   -- 删除表中的字段

删除表drop

永久性地删除某个表

drop table user;   

修改表名

alter table user rename to new_user;

更新数据update

通过关键字update和set来实现数据的更新

mysql> update user set name="nangying" where id=6;   // 通过id指定
mysql> update user set fee=88.76 where fee=56.90;   // 通过字段名直接指定
mysql> update user set email="81847919@qq.com", age=54 where id=7;  // 同时修改多个值
mysql> update user set fee=88.88 where id in(2,4,6);   // in的用法
mysql> update user set fee=66.66 where id between 2 and 6;  // between ... and ...

删除数据delete和truncate

删除表有两种情况：

• delete：删除表中的行，而不是表本身，插入数据从上一次结束的id号开始继续插入；占用内存
• truncate：清空表，重新插入数据id从1开始；不占内存空间

delete table user;     
truncate table user;

image
image

组合查询union

SQL中允许执行多个查询，即执行多条select语句，并将结果作为一个查询结果进行返回。两种情况需要使用组合查询：

1. 在一个查询中从不同的表中返回结构数据
2. 对一个表执行多个查询，按照一个查询返回数据

创建组合查询

在每条select语句之间放上关键字union

select name, contact, email
from customers
where state in ('IL', 'IN', 'MI')
union   -- 通过关键字来连接两个子句
select name, contact, email
from customers
where name = 'Fun4All'

-- 通过多个where子句来实现上述功能
select name, contact, email
from customers
where state in ('IL', 'IN', 'MI')
or name = 'Fun4All'   -- or是关键字

笔记：

1. union至少由两条或者两条以上的select语句构成
2. 每个查询中必须包含相同的列、表达式或者聚集函数
3. 列数据类型必须兼容：类型不必完全相同
4. union的查询结果是自动去掉重复的行；如果想改变，可以使用union all

对组合查询结果排序

使用一条order by子句来进行排序，而且一定是最后的一行

select name, contact, email
from customers
where state in ('IL', 'IN', 'MI')
union   -- 通过关键字来连接两个子句
select name, contact, email
from customers
where name = 'Fun4All'
order by name, contact   -- 排序功能