MySQL

1.返回其参数中的第一个非空表达式

 select COALESCE(NULL, 3, 4)

执行后结果为3

// 使用 COALESCE该关键词时, 首先可以查询出结果集, 并且当前字段的结果为空(即 null)时才有效, 如果结果
//  如果查出来的storelevel的结果为空, 则结果为10
 SELECT COALESCE(storelevel, 10) FROM organ WHERE id = 47
// 查询出的结果为' ', 执行结果为 ' '  
SELECT COALESCE(email, 's') from organ WHERE id = 47
// 执行结果为 '2009-09-01'
SELECT COALESCE( null,'2009-09-01')

可设默认值.png
不可设置默认值.png

2. 插入语句

// 单条插入
insert into table_name (key1, key2) values (value1, value2);
// 也可以使用SET 关键字设置值
INSERT INTO work_address_table SET id=4, address='ddd'
// 多条插入
 INSERT INTO work_address_table (id, address) VALUES (2, 'bbb'),(3, 'ccc');

3. 更新语句

update work_address_table SET address='fff' WHERE id=4

4. 删除语句

// 根据条件删除数据
DELETE FROM work_address_table WHERE id=4
// 删除表中的所有的数据
DELETE FROM tablename;

5. 四舍五入

// 结果为2.56
SELECT ROUND(2.555,2)

// 结果为 2.54
SELECT ROUND(2.544,2)

createdb mydb  // 创建数据库
dropdb  mydb  // 删除数据库

//创建表
create table user(
name  varchar(30),  --姓名
age  int, --年龄
);

drop table tableName;  // 删除表

// 排序
SELECT * from work_address_table ORDER BY address;
// 去重
SELECT DISTINCT address from work_address_table;
// 内连接
select * from weather inner join cities on (weather.city = cities.name);
// 左外连接
select * from weather left join cities on (weather.city = cities.name)
// 标记表名
select * from weather w, cities c where w.city = c.name;
// 查询每个城市的最高温度
select city, max(temp_lo) from weather group by city;

// 查询每个城市的最高温度, 并且最高温度小于 40
select city, max(temp_lo) from weather group by city having max(temp_lo) < 40;

创建视图

CREATE VIEW myview AS
    SELECT city, temp_lo, temp_hi, prcp, date, location
        FROM weather, cities
        WHERE city = name;

SELECT * FROM myview;

主键

primary key

CREATE TABLE cities (
        id   int,
        city     varchar(80) primary key
);
CREATE TABLE weather (
        city      varchar(80) references cities(city),
        temp_lo   int,
        temp_hi   int,
);
这时往weather 表中插入数据时一定要保证 city已经在cities表中存在, 否则会报错

外键

foreign key

create table weather(
  id  int,
  city_id  int ,
  temp_lo  int,
  foreign key (city_id) references cities(id) on delete cascade on update cascade
);

// 插入,更新, 删除数据的时候一定要保证city_id在cities 表中存在.

事务

begin;
// 执行操作语句
commit;  (提交)/ rollback;(回滚)

// savepoint  // 设置保存点
// 可以使用 rollback to 回滚到该保存点
BEGIN;
UPDATE accounts SET balance = balance - 100.00
    WHERE name = 'Alice';
SAVEPOINT my_savepoint;
UPDATE accounts SET balance = balance + 100.00
    WHERE name = 'Bob';
-- 呀！加错钱了，应该用 Wally 的账号
ROLLBACK TO my_savepoint;
UPDATE accounts SET balance = balance + 100.00
    WHERE name = 'Wally';
COMMIT;

数组构造器

// 查询数组
SELECT ARRAY [1, 2, 3]    // 结果为{1,2,3}
// 查询数组, 将值强转为整形
SELECT ARRAY [1.2, 2.3, 3.6]::INT[]    // 结果为 {1, 2, 4}

自定义函数

// 创建一个拼接两个字符串的函数, 输出大小写类型 
CREATE FUNCTION concat_lower_or_upper(a TEXT, b TEXT, uppercase BOOLEAN DEFAULT FALSE)
RETURNS text
AS
$$
    SELECT CASE
        WHEN $3 THEN upper($1 || ' ' || $2)
        ELSE LOWER($1 || ' ' || $2)
        END;
$$
LANGUAGE SQL IMMUTABLE STRICT;

// 使用自定义函数
SELECT concat_lower_or_upper('hello','laowei', TRUE);
// 输出:HELLO LAOWEI

SELECT concat_lower_or_upper('hello','laowei');
// 输出:   hello laowei

约束

// 添加约束
check (price>10) 约束条件
not null 非空约束
unique  唯一约束
// 添加约束, 没有设置约束名
alter table products add check (name <> '');
// 添加约束, 设置约束名称
alter table products add constraint checkName unique (product_no);
// 删除约束
alter table products drop constraint checkName;

// 设置默认值
alter table products alter column price set default 10;
// 删除设置的默认值
alter table products alter column price drop default;

extract 函数 (提取函数)

// 计算当前时间是一年的第几天
SELECT extract(doy from CURRENT_DATE)
// 查询当前日期的年份
SELECT extract(YEAR from CURRENT_DATE)
// 直接写时间字符串
SELECT extract(DAY FROM TIMESTAMP '2017-12-19 12:13:20')

修改表结构

// 新增字段
alter table products add column description text;
// 删除字段(同时删除该字段的约束)
alter table products drop column description;
// 删除字段, 同时删除任何依赖该字段的东西  cascade(级联)
alter table products drop column descripton cascade;
// 修改字段的数据类型
alter table products alter column price type int;
// 重命名字段
alter table products rename column product_no to product_number;
// 重命名表
alter table products rename to items;

<> 与 != 都代表不等于

// nullif(value1, value2)  两者不相同时返回value1, 两者相同时 则返回 null
SELECT NULLIF('a','b');  // 结果为 a
SELECT NYLLIF('a', 'a');  // 结果为 null

// 查询所有数据, 在重复数据中 只取 某个字段值为最大的数据
SELECT a.* FROM test a INNER JOIN (SELECT name,MAX(version) as max_version FROM test   
GROUP BY name DESC) b ON a.name=b.name AND a.version=b.max_version;