sql必知必会3

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

创建表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)  -- 指定主键
);

关于NULL

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

表中插入数据insert

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

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

笔记：

相应的字段填上相应的信息

字符串需要使用引号

密码使用函数Password

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

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

插入部分数据

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

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

1
2
3

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

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

1
2
3

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

永久性地删除某个表

1	drop table user;

修改表名

1	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开始；不占内存空间

1 2	delete table user; truncate table user;

组合查询union

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

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

创建组合查询

在每条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是关键字

笔记：

union至少由两条或者两条以上的select语句构成

每个查询中必须包含相同的列、表达式或者聚集函数

列数据类型必须兼容：类型不必完全相同

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   -- 排序功能