MySQL基础（四）-DDL

#一、如何创建库
CREATE DATABASE studb;
#提高容错性，在java中用下面语句写
CREATE DATABASE IF NOT EXISTS studb;




#二、如何删除库
DROP DATABASE studb;
DROP DATABASE IF NOT EXISTS studb;

语法：
CREATE TABLE 表名（
  字段名  字段类型  【字段约束】,
  字段名  字段类型  【字段约束】,
  字段名  字段类型  【字段约束】,
  字段名  字段类型  【字段约束】,
  字段名  字段类型  【字段约束】
  );
  
#(无约束)案例:创建一个学生信息表
CREATE TABLE IF NOT EXISTS stuinfo(
  stuid INT ,
  stuname VARCHAR(20),
  gender CHAR,
  email VARCHAR(20),
  borndate DATETIME
  );


DESC stuinfo;

说明：用于限制表中字段的数据的，从而进一步保证数据表的数据是一致的准确的
NOT NULL 非空 ：限制字段为必填项
DEFAULT 默认 ：用于限制该字段没有显式插入值，则直接显式默认值
PRIMARY KEY 主键 ：用于限制该字段的值不能重复，并默认不能为空，一个表只能有一个主键
UNIQUE 唯一 ：用于限制该字段的值不能重复，一个表可以有n个
CHECK 检查 : 用于限制该字段的值必须满足指定条件,mysql不支持CHECK命令，但是也不报错
  CHECK(age BETWEEN 1 AND 100)
FOREIGN KEY 外键 ：用于限制两个表的关系，要求外键列的值必须来自于主表的关联列
  要求：1.主表关联列和从表的关联列的类型必须一致，意思一样
       2.主表的关联列要求必须是主键
         
#(有约束)案例：
CREATE TABLE IF NOT EXISTS stu_info(
  stuid INT PRIMARY KEY ,       # 添加主键约束
  stuname VARCHAR(20) UNIQUE NOT NULL,   # 添加唯一约束+非空约束，可以叠加
  gender CHAR(1) DEFAULT '男',  # 添加默认约束
  email VARCHAR(20) NOT NULL,
  borndate DATETIME,
  age INT CHECK(age BETWEEN 0 AND 100) , # 添加检查约束，mysql不支持
  majorid INT,
  CONSTRAINT fk_stu_info_major FOREIGN KEY (majorid) REFERENCES major(id) # 外键
  );
  
  
#案例：不同方式添加主键
CREATE TABLE IF NOT EXISTS stu_info(
  stuid INT ,
  stuname VARCHAR(20) UNIQUE NOT NULL,   # 添加唯一约束+非空约束，可以叠加
  gender CHAR(1) DEFAULT '男',  # 添加默认约束
  email VARCHAR(20) NOT NULL,
  borndate DATETIME,
  age INT CHECK(age BETWEEN 0 AND 100) , # 添加检查约束，mysql不支持
  majorid INT,
    primary key(stuid),  # 添加主键
    constraint uq unique (stuname) , #添加唯一键
  CONSTRAINT fk_stu_info_major FOREIGN KEY (majorid) REFERENCES major(id) # 外键
  );

语法：ALTER TABLE 表名 ADD | MODIFY | CHANGE | DROP COLUMN 字段名  字段类型  【字段约束】


#1.修改表名


ALTER TABLE stuinfo RENAME TO students;


#2.添加字段


ALTER TABLE students ADD COLUMN borntime TIMESTAMP NOT NULL;
ALTER TABLE students DROP COLUMN borntime ;


DESC students;


#3.修改字段名


ALTER TABLE students CHANGE COLUMN borndate birthday DATETIME NULL;


#4.修改字段类型


ALTER TABLE students MODIFY COLUMN birthday TIMESTAMP ;
DESC students;


#5.删除字段
ALTER TABLE students DROP COLUMN birthday ;

DROP TABLE IF EXISTS students;

#复制表结构
CREATE TABLE newTable LIKE stu_info;


#复制表的结构加数据
CREATE TABLE newTable2 SELECT * FROM girls.`beauty`;


#案例：复制employees表中的last_name,department_id,salary字段到新表emp
CREATE TABLE emp
SELECT last_name,department_id,salary
FROM myemployees.`employees`
WHERE 1=2;

# 测试题
#1.使用分页查询实现，查询员工信息表中的部门为50号的工资最低的5名员工的信息


SELECT * FROM employees 
WHERE department_id = 50 
ORDER BY salary 
LIMIT 5;


#2.使用子查询实现城市为'Seattle'的，且工资>10000的员工姓名
#步骤1：查询城市toroto的部门编号，需要使用内连接
SELECT department_id
FROM departments d
JOIN locations l ON d.`location_id`=l.`location_id`
WHERE city = 'Seattle';
#步骤2：查询部门号在1中的员工姓名
SELECT last_name
FROM employees
WHERE salary > 10000
AND
department_id IN (
  SELECT department_id
  FROM departments d
  JOIN locations l ON d.`location_id`=l.`location_id`
  WHERE city = 'Seattle'
);


#3.创建表qqinfo，里面包含qqid，添加主键约束（昵称nickname），添加唯一约束、邮箱email(非空)、性别gender


CREATE TABLE IF NOT EXISTS qqinfo (
  nickname VARCHAR(20) PRIMARY KEY,
  qqid INT,
  email VARCHAR(20) UNIQUE NOT NULL,
  gender CHAR(2)
  );


#4.删除表qqinfo


DROP TABLE IF EXISTS qqinfo;


#5.试写出sql查询语句的定义顺序和执行顺序
SELECT DISTINCT 查询列表
FROM 表名 别名
JOIN 连接表
ON 连接条件
WHERE 筛选条件
GROUP BY 分组列表
HAVING 分组后筛选
ORDER BY 排序
LIMIT 条目数;


执行顺序：
1.from
2.join 
3.on
4.where
5.group BY
6.having
7.select
8.order BY
9.limit