MySQL数据库语法(二)

一、数据库的创建

创建数据库
	CRATE DATABASE
	语法：
        CREATE DATABASE [IF NOT EXISTS]数据库名;
        功能：用给定的名字创建一个数据库
        如果数据库已经存在，发生一个错误。
        查看创建数据库：SHOW CREATE DATABASE <数据库名>；
        示例：
        create database school DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
删除
    DROP DATABASE
	语法：
        DROP DATABASE [IF EXISTS]数据库名;
        功能：删除数据库中得所有表和数据库
        要小心使用这个命令（慎用）
切换
    使用USE选用数据库
    语法：
        USE 数据库名;
        功能：把指定数据库作为默认(当前)数据库使用，用于后续语句。
        其他：
        查看当前连接的数据库
        SELECT DATABASE();
        查看数据库版本
        SELECT VERSION();
        查看当前用户
        SELECT USER();
        查看所有用户
        SELECT User,Host,Password FROM mysql.user;

1、登陆数据库

2、创建数据库zoo

create database zoo; --创建数据库

3、修改数据库zoo字符集为gbk

create database if not exists zoo character set gbk;

4、选择当前数据库为zoo

5、查看创建数据库zoo信息

6、删除数据库zoo

二、数据类型

在 MySQL 中，有三种主要的类型：文本、数字和日期/时间类型。

Text类型：

Number 类型：

Date 类型：

标准的建表（table）语法(列定义之间以英文逗号,隔开)：
数据表的每行称为一条记录（record），每一列称为一个字段（field）。
主键列：唯一能够标识每条记录的列。
    CREATE TABLE [schema.]table
    (column datatype[DEFAULT expr] ,
    	…
    ) ENGINE = 存储机制
    

CREATE TABLE
简单语法：
    CREATE TABLE 表名（
    列名 列类型，
    列名 列类型
    ）;
功能：在当前数据库中创建一张表

创建表 1、创建一个名称为db_system的数据库

2、在该数据库下创建两张表，具体要求如下员工表 user 字段类型约束备注 id 整形主键，自增长 id NAME 字符型非空姓名 gender 字符非空性别 birthday 日期型生日 entry_date 日期型非空入职时间 job 字符型非空职位

员工绩效表 salary
字段       类型   约束                               备注
id           整形   主键，自增长                       id
userId       字符型   非空,外键，关联的是user表的id字段    用户id
baseSalary   小数   非空                               基本工资
month       整数   非空                               月份
allowances   小数   非空，默认为0                       补贴

三、数据库表的修改(表的约束)

修改表的结构
修改列类型
	ALTER TABLE 表名 MODIFY 列名 列类型;
	
增加列
	ALTER TABLE 表名 ADD 列名 列类型 [约束条件];
	
删除列
	ALTER TABLE 表名 DROP 列名;
	
列改名
	ALTER TABLE 表名 CHANGE 旧列名 新列名 列类型;
更改表名
	ALTER TABLE 表名 RENAME 新表名;
	RENAME TABLE 表名 TO 新表名;

复制表的结构
复制一个表结构的实现方法有两种

方法一：在create table语句的末尾添加like子句，可以将源表的表结构复制到新表中，语法格式如下。
create table 新表名 like 源表

方法二：在create table语句的末尾添加一个select语句，可以实现表结构的复制，甚至可以将源表的表
记录拷贝到新表中。下面的语法格式将源表的表结构以及源表的所有记录拷贝到新表中。
create table 新表名 select * from 源表


方法三：如果已经存在一张机构一致的表，复制数据
insert into 表 select * from 原表;

数据库字典
由information_schema数据库负责维护

tables－存放数据库里所有的数据表、以及每个表所在数据库。
schemata－存放数据库里所有的数据库信息
views－存放数据库里所有的视图信息。
columns－存放数据库里所有的列信息。
triggers－存放数据库里所有的触发器。
routines－存放数据库里所有存储过程和函数。
key_column_usage－存放数据库所有的主外键
table_constraints－存放数据库全部约束。
statistics－存放了数据表的索引。

表的约束
NOT NULL非空
UNIQUE Key唯一键
PRIMARY KEY主键
FOREIGN KEY外键
CHECK检查
默认值约束
语法：
    列定义
    [CONSTRAINT 约束名] 约束类型(列名)
alter table 表名 add constraint 约束名 约束类型(要约束的列名)

表的约束示例：
1、非空约束(NOT NULL)
列级约束，只能使用列级约束语法定义。
确保字段值不允许为空
只能在字段级定义

CREATE TABLE tb_student(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(18) NOT NULL
)

NULL
所有数据类型的值都可以是NULL。
空字符串不等于NULL。
0也不等于NULL。


2、唯一约束
唯一性约束条件确保所在的字段或者字段组合不出现重复值
唯一性约束条件的字段允许出现多个NULL
同一张表内可建多个唯一约束
唯一约束可由多列组合而成
建唯一约束时MySQL会为之建立对应的索引。
如果不给唯一约束起名，该唯一约束默认与列名相同。

CREATE TABLE tb_student(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(18) UNIQUE NOT NULL
)


3、主键约束
主键从功能上看相当于非空且唯一
一个表中只允许一个主键
主键是表中唯一确定一行数据的字段
删除表的约束
自动增长和默认值
存储引擎
主键字段可以是单字段或者是多字段的组合
当建立主键约束时，MySQL为主键创建对应的索引
主键约束名总为PRIMARY。

CREATE TABLE tb_student(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(18)
)

4、外键约束
外键是构建于一个表的两个字段或者两个表的两个字段之间的关系
外键确保了相关的两个字段的两个关系：
子(从)表外键列的值必须在主表参照列值的范围内，或者为空（也可以加非空约束，
强制不允许为空）。
当主表的记录被子表参照时，主表记录不允许被删除。
外键参照的只能是主表主键或者唯一键，保证子表记录可以准确定位到被参照的记录。
	
格式FOREIGN KEY (外键列名)REFERENCES 主表(参照列)

CREATE TABLE tb_dept(
    dept_id INT PRIMARY KEY,
    NAME VARCHAR(18),
    description VARCHAR(100)
);

CREATE TABLE tb_employee(
    employee_id INT PRIMARY KEY,
    NAME VARCHAR(18),
    gender VARCHAR(10),
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES tb_dept(dept_id),
    address VARCHAR(100)
);


# 检查约束

# 注意检查约束在8.0之前，MySQL默认但不会强制的遵循check约束（写不报错，
但是不生效，需要通触发器完成）
# 8之后就开始正式支持这个约束了。
create table t3(
    id int, 
    age int check(age > 18),
    gender char(1) check(gender in ('M','F'))
);


# 默认值
可以使用default关键字设置每一个字段的默认值。
-- 创建一张user表
CREATE TABLE `test`.`user`(  
  `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` VARCHAR(225) COMMENT '姓名',
  `sex` TINYINT(1) DEFAULT 1 COMMENT '性别 1男 0女',
  PRIMARY KEY (`id`)
) ENGINE=INNODB CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

删除表的约束：
删除NOT NULL约束
	alter table 表名 modify 列名 类型;

删除UNIQUE约束
	alter table 表名 drop index 惟一约束名;

删除PRIMARY KEY约束
	alter table 表名 drop primary key;

删除FOREIGN KEY约束
	alter table 表名 drop foreign key 外键名;

1、在上面员工表的基本上增加一个image列，类型是blod，长度255。