数据库MySQL基础（2）

数据库MySQL基础（2）

SQL语法

1.DDL

数据（结构）定义语言DDL，是用于创建和修改数据库表结构的语言。

1. 创建，删除数据库

   创建数据库并设置编码格式

   CREATE DATABASE [ IF NOT EXISTS] 数据库名 [CHARSET utf8]

   IF NOT EXISTS – 判断数据库是否存在，即数据库名是否重复。

   CHARSET – 设置编码格式

   CREATE DATABASE IF NOT EXISTS test CHARSET utf8
   

   删除数据库

   DROP DATABASE [IF EXISTS]数据库名

   IF EXISTS – 判断是否存在数据库，不存在则不执行。

   DROP DATABASE IF EXISTS test
   

   修改字符集

   ALTER DATABASE 数据库名 CHARSET gbk

   ALTER DATABASE test CHARSET gbk
   

   查询有哪些数据库

   SHOW DATABASES

2. 设计表

   对于具体的某一个表，在创建之前，需要确定表的下列特征：

   1. 表中的字段

   2. 字段的数据类型和长度

      char（n） 长度为n的定长字符串
      varchar(n） 最大长度为n的可变长字符串
      date 日期， 包含年月日
      datetime 年月日 时分秒

      整数

      类型	字节	最小值	最大值
      TINYINT	1	-128	127
      SMALLINT	2	-32768	32767
      MEDIUMINT	3	-8388608	8388607
      INT	4	-2147483648	2147483647
      BIGINT	8	-9223372036854775808	9223372036854775807

      浮点

      类型	字节
      float	4
      double	8

      数据类型(M,D)
      M：精度，数据的总长度；
      D：标度，小数点后的长度
      Float(6,2)、Double(6,2)

      BLOB是一个二进制大对象，可以容纳可变数量，用于存储图片视频信息。

      有4种BLOB类型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。

      TEXT列字符字符串

      有4种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。
      它们（TEXT和BLOB同）的长度：

      ​ Tiny：最大长度255个字符(2^8-1)

      ​ BLOB或TEXT：最大长度65535(2^16-1)

      ​ Medium：最大长度16777215(2^24-1)

      ​ LongText 最大长度4294967295(2^32-1)

   3. 哪些约束

      1. PRIMARY KEY --设置主键约束
      2. NOT NULL --不能为空约束
      3. UNIQUE --唯一性约束
      4. CHECK 设置条件 --检查约束
      5. AUTO_INCREMENT –主键自动增长
      6. DEFAULT 列名 --默认值
      7. COMMENT ‘注释’ --字段注释

3. 创建表

   CREATE TABLE 表名（列名 数据类型 [约束] [默认值] [注释]，…）

   CREATE TABLE t_student (
     stu_num INT PRIMARY KEY  AUTO_INCREMENT COMMENT '主键', -- 自动增长，主键
     stu_name VARCHAR (10) NOT NULL COMMENT '姓名', -- 不为空
     stu_sex CHAR (1) DEFAULT '男',-- 默认值‘男’
     stu_birthday DATE,
     stu_score FLOAT (4, 1) CHECK (stu_score <= 100),-- 检查约束
     stu_phone VARCHAR (11) NOT NULL UNIQUE,-- 不为空 ，唯一性
     stu_reg_time DATETIME
   )
   

4. 删除表

   DROP TABLE [IF EXISTS] 表名

   DROP TABLE IF EXISTS test
   

5. 修改表名

   修改表名

   RENAME TABLE 旧表名 TO 新表名

   RENAME TABLE test TO new_test
   

   复制表结构

   CREATE TABLE 新表名 LIKE 被复制表名

   CREATE TABLE new_test LIKE test
   

6. 修改表结构

   添加主键约束

   ALTER TABLE 表名 ADD PRIMARY KEY(列名)
   ALTER TABLE 表名 DROP PRIMARY KEY

   ALTER TABLE t_student ADD PRIMARY KEY stu_num
   ALTER TABLE t_student DROP PRIMARY KEY
   

   设置自动增长

   ALTER TABLE 表名 MODIFY 列名 类型 AUTO_INCREMENT
   ALTER TABLE users MODIFY 列名 类型 AUTO_INCREMENT

   ALTER TABLE  t_student  MODIFY  stu_num  INT  AUTO_INCREMENT;
   
   

   设置列不能为空

   ALTER TABLE 表名 MODIFY 列名 类型 NOT NULL
   ALTER TABLE 表名 CHANGE COLUMN 列名 列名 类型 NULL

   ALTER TABLE   t_student  MODIFY  stu_name  VARCHAR(10)  NOT NULL; 
   ALTER TABLE t_student CHANGE COLUMN stu_name   stu_name   VARCHAR(10) NULL;-- 修改列可以为空
   
   

   添加唯一约束
   ALTER TABLE 表名 ADD UNIQUE(列名)
   ALTER TABLE 表名 DROP INDEX 索引名

   ALTER TABLE t_student  ADD UNIQUE(stu_phone)
   ALTER TABLE t_student DROP INDEX stu_phone;
   
   

   添加检查约束
   ALTER TABLE 表名 ADD CONSTRAINT 约束名 CHECK(条件)
   ALTER TABLE 表名 DROP CHECK 约束名

   ALTER TABLE  t_student ADD CONSTRAINT score_check CHECK(stu_score<=100)
   ALTER TABLE t_student  DROP CHECK  score_check;
   
   

   添加列
   ALTER TABLE 表名 ADD 列名 数据类型
   ALTER TABLE 表名 ADD 列名 数据类型 FIRST
   ALTER TABLE 表名 ADD 列名 数据类型 AFTER 列名

   ALTER  TABLE  t_student  ADD COLUMN   address VARCHAR(50) -- 默认添加列在最后
   ALTER  TABLE  t_student  ADD COLUMN   address VARCHAR(50) FIRST -- 添加列于第一列 
   ALTER  TABLE  t_student  ADD COLUMN   address VARCHAR(50) AFTER stu_sex -- 添加列在stu_sex后
   
   

   删除列
   ALTER TABLE 表名 DROP 列名

   ALTER TABLE t_student DROP COLUMN address
   
   

   修改列名
   ALTER TABLE 表名 CHANGE 旧列名 新列名 数据类型

   ALTER TABLE    t_student    CHANGE    address   stu_address    VARCHAR(50)
   
   

   修改列的数据类型
   ALTER TABLE 表名 MODIFY 列名 新数据类型

   ALTER TABLE    t_student    MODIFY    address     VARCHAR(25)
   
   

注意：示例代码基于SQLyog软件基础上所书写的