数据库基础

1. 数据库(DDL)

---

• 数据库创建

create database if not exists 1baibai charset utf8;

• 删除数据库

drop database if exists 1baibai;

• 修改数据库

alter database 1baibai character set utf8;

2. 表(DDL)

---

• 表创建

CREATE TABLE `job` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `job_name` varchar(255) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `create_people` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

• 查看所有表

show tables;

• 查看表结构

 desc job;

• 查看建表语句

show create table job;

• 修改表
  1. 添加字段

  ALTER TABLE job ADD (`des` varchar(255), `job_people` varchar(255));
  

  2. 修改字段类型

  ALTER TABLE job MODIFY des int;
  

  3. 修改字段名

  ALTER table job CHANGE des description VARCHAR(255);
  

  4. 删除字段

  ALTER TABLE job DROP create_time;
  

  5. 修改表名称

  ALTER TABLE job RENAME TO jobs;
  

  6. 删除主键

  ALTER TABLE jobs DROP PRIMARY KEY;
  

  7.修改表时添加主键

  ALTER TABLE jobs ADD PRIMARY KEY(des);
  

  8.设置主键自增长和非空约束

  ALTER TABLE place ADD PRIMARY KEY(id);
  ALTER TABLE place CHANGE id id INT NOT NULL auto_increment;
  

  9.设置字段唯一约束

  ALTER TABLE place CHANGE id id INT NOT NULL UNIQUE;
  

3. 数据操作（DML）

• 插入数据

INSERT INTO jobs (job_name, create_people, description) VALUES ('iOS', 'Bill', 'iOS开发招聘');

INSERT INTO jobs VALUES (2, 'Java', 'linda', 'Java开发招聘');

• 修改数据

UPDATE jobs SET job_name = 'android' WHERE id = 2;

• 删除数据

DELETE FROM jobs WHERE id = 3;

4. 查询

• 基本查询

SELECT * FROM jobs;

• 查询指定列

SELECT job_name, create_people FROM jobs;

• 去重

SELECT DISTINCT job_name, create_people FROM jobs;

• 列运算
  1. 数量类型：+、-、、/

  SELECT age * 5 FROM jobs;
  

  2. 字符串克拼接

  SELECT CONCAT('&&&',create_people) FROM jobs;
  

  3. NULL值转换

  SELECT job_name, IFNULL('空',des) FROM jobs;
  

  4.给列起别名

  SELECT job_name AS '职位', create_people AS '创建人', description AS '描述', IFNULL(des,0) AS '备注' FROM jobs;
  

• 条件控制
  1.条件查询

  SELECT * FROM jobs WHERE id BETWEEN 13 AND 16 AND des IS NOT  NULL;
  

  2. 模糊查询

  SELECT * FROM jobs WHERE create_people LIKE '%B%';
  SELECT * FROM jobs WHERE create_people LIKE 'B___';
  

  3. 排序

  SELECT * FROM jobs ORDER BY id ASC;
  SELECT * FROM jobs ORDER BY id DESC;
  SELECT * FROM jobs ORDER BY age ASC, id DESC;
  

5. 聚合函数

1. 列函数统计（不为NULL）

SELECT COUNT(des) FROM jobs;

2.最大值查询

SELECT MAX(age) FROM jobs;

3.最小值查询

SELECT MIN(age) FROM jobs;

4.和查询

SELECT SUM(age) FROM jobs;

5. 平均值查询

SELECT AVG(age) FROM jobs;

6.分组查询

SELECT job_name, COUNT(*) FROM jobs GROUP BY id;

7.限定查询

SELECT * from jobs LIMIT 1,5;