SQL基础语言整理1

025-SQL 语句基本介绍

–数据类型
// 字符串：char(?),varchar(20) ，varchar是可变长度的字符
// 数值：int, float(m,n) / double(m,n) ， m代表总共位数，n代表小数位数
// 日期：date(年月日), time(小时/分/秒), datetime / timestamp(年月日/小时/分/秒)

–创建员工信息表
// 员工信息表 employees
// 工号 姓名 编码 性别 年龄 部门 薪水
// empid empname gender age dept salary

create table employees(
    empid int,
    empname varchar(20), // ‘aaa’
    gender char(1), // ‘f______’
    age int,
    dept varchar(30),
    salary float(7,2)   //9999.99
);

–查看表结构

desc employees

–创建部门信息表departments（信息表名一般用复数）
// 部门id 部门名称 所在城市
// deptid deptname location

create table
departments(
    deptid int,
    deptname varchar(100) unique,
    location varchar(50),
    primary key(deptid)
);

–查看创建表的语句

show create table employees; //生成一个默认的utf-8数据表
show create table employees\G; //垂直方式显示，比上一个美观
show create table departments;

–练习：创建商品信息表products
// 商品id 名称 价格 生产日期 库存数量
// pdid pdname price birthday stock

create table
products(
    pdid int,
    pdname varchar(30),
    price float(12,2),
    birthday datetime,
    stock int
);

026-SQL 默认的约束条件

–主键约束：保证每行数据不重复

create table employees2(
    empid int primary key,
    empname varchar(20),
    gender char(1),
    age int,
    dept varchar(30),
    salary float(8,2)
);

create table departments2(
    deptid int,
    deptname varchar(100),
    location varchar(50),
    primary key(deptid)
);

–非空约束：字段的值不能为空
// 字段名 字段类型 not null

–唯一性约束：要求字段值必须唯一
// 字段名 字段类型 unique

–默认约束：指定某个字段的默认值
// 字段名 字段类型 default ‘默认值’

–删除数据表
// drop table 表名

–外键约束：在两个表之间建立连接，一个table可以有一个或者多个外键。外键的值，可以是空值；但只要非空，每一个外键值必须等于另一个table中主键的某个值。
主表：主键所在的表
从表：外键所在的表

// 字段名 字段类型
// foreign key(字段名) references 表名（字段名）

create table
employees(
    empid int primary key,
    empname varchar(20) not null,
    gender char(1) default ’F’, 
    age int,
    deptid int,
    salary float(7,2),
    foreign key(deptid) references
    departments(deptid)
);

–向表中填充数据
// insert into 表名 values(字段值列表)

insert into departments values(1010,’技术研发部’,’北京’);
insert into departments values(1011,财务部,上海)；
insert into departments values(1012,销售部,null)；
insert into departments(deptid,deptname) values(104,公共关系部)
insert into products values(111,方便面,5.00,’2017-05-12’,200);

–批量插入数据

insert into employees values
(1010,技术研发部,北京),
(1011,财务部,上海);

–查询表中记录
// select 字段列表 from 表名

select deptid,deptname,location from departments;
select * from departments; --查看全部

027-SQL 加入外部数据集使用

–将查询结果保存到另一张数据表中，有两种方式
// 法1：创建一张新数据表，再用insert into……select……
// 法2：直接使用create table……select……

create table dept2(
    deptid int,
    deptname varchar(200)
);
insert into dept2 select deptid,deptname from departments;

create table dept3
select deptid,deptname from departments;
create table dept4
select deptid as id,deptname as name from departments; //更改字段名

–加载外部数据文件到数据表中
// 如csv，以逗号分隔。如果有中文内容的话，需要utf-8编码另存。
// 如txt，以制表符分隔。如果不指定fields terminated by的话，默认以tab（’/t’）分隔。

load data infile ‘路径.csv’
into table 表名
fields terminated by ‘,’;

load data infile ‘路径.txt’
into table 表名;

–修改表中数据：update
// 将月薪低于五千的员工的薪资上调10%

update employees set salary=salary*1.1
where salary<5000;

// 将月薪低于五千的员工的薪资上调10%，并调到101部门

update employees set salary=salary*1.1,deptid=101
where salary<5000;

// 将products表中所有商品的价格打八折

update products set price=price*0.8

// 将products表中所有未标明价格的商品，定价为10元
// 处理null值：对于null的比较不能用=，要用is null或者is not null

update products set price=10 where price is null;

028-SQL 数据表、库结构的更新

–删除表中记录：delete
// 将products表中“商品名称未知”的记录删除
// 全部删除：delete from products;

delete from products
where pdname is null;

// 将公司中月薪超过8000的员工，或者女性员工，辞退

delete from employees
where salary>8000 or gender=’F’;

// 将公司中所有不属于102部门的男员工都辞退

delete from employees
where deptid!=102 and gender=’M’;

–修改数据表：alter table 表名
// 改表名：rename

alter table employees rename emp;

// 改字段数据类型：modify

alter table employees modify empname varchar(100);

// 改字段名+类型：change

alter table depts change location loc varchar(200);

// 增加新字段：add

alter table depts add aaa varchar(20);

注：实际操作中，一般在创建table的时候，就会创建几个无意义字段，以后如果需要补充字段，可直接更改这些无意义字段的名字和类型。

// 在第一列添加一个int类型字段

alter table depts add aaa int first;

// 在某列后添加一个int类型字段

alter table depts
add aaa int after XXXX;

// 删除某字段：drop

alter table depts
drop (column) aaa;

// 删除外键：drop foreign key

alter table depts
drop foreign key aaa;

注：实际操作中，大量数据的导入被外键阻碍，会先删除外键，数据导入后再加上