SQL语句-2

2.8 DDL（数据定义）语句

2.8.1 创建和删除数据库  

1.创建数据库

~~~mysql
# 格式： create database 数据库名
# 比如创建名为wzry的数据库
create database wzry;   # 注意：因为创建数据库时一次只创建一个，所以用的是单数 database

# 创建数据库时同时设置数据库的编码
# 格式：create database 数据库名 charset=编码
# 比如创建名为wzry的数据库，并将编码设置为utf8
create database wzry charset=utf8;

# 创建完数据库后可以查看数据库：
# 格式： show databases; 注意：查看数据库时，可能查看到的是多个数据库名称，所以此处用的是复数databases
~~~

2.删除数据库

~~~mysql
# 格式：drop database 数据库名
# 比如：删除名为wzry的数据库
drop database wzry;

# 删除之后，可以用show databases ； 查看一下是否删除成功
~~~

2.8.2 创建数据库表格

~~~mysql
# 格式：
CREATE TABLE [IF NOT EXISTS]  表名 (
    字段1 数据类型 [字段属性|约束][索引][注释],
      ……
     字段n 数据类型 [字段属性|约束][索引][注释]
)[表类型][表字符集][注释];

# 比如：创建student表
CREATE TABLE student（
    studentNo INT(4)  PRIMARY KEY,
    name CHAR(10),
     …
）;

# 注意：
# 1.mysql中关键字不区分大小写，create 和CREATE 效果一样
# 2.多字段使用逗号分隔
# 3.最后一个字段类型后面不加逗号
# 4.IF NOT EXISTS 表示如果不存在，create table if not exists 表名 表示如果数据库中不存在表名对应的表就创建，如果存在就不创建。
# 5.int 表示整数类型 int(4) 表示该字段的长度最大为4
# 6.char 表示定长字符串 char(10)表示字符串的长度为10 

# 案例：在wzry数据库中创建一个hero表
create table hero(
    id int,
    name varchar(20),
    level int,
    loc varchar(20)
);

~~~

2.8.3 mysql数据类型

| 类型      | 说明               | 取值范围                                      | 存储需求  |
| --------- | ------------------ | --------------------------------------------- | --------- |
| TINYINT   | 非常小的数据       | 有符值： -27 ~ 27-1  无符号值：0 ~ 28-1       | 1字节     |
| SMALLINT  | 较小的数据         | 有符值：  -215 ~ 215-1  无符号值：  0 ~ 216-1 | 2字节     |
| MEDIUMINT | 中等大小的数据     | 有符值：  -223 ~ 223-1  无符号值：  0 ~ 224-1 | 3字节     |
| INT       | 标准整数           | 有符值： -231 ~ 231-1  无符号值：0 ~ 232-1    | 4字节     |
| BIGINT    | 较大的整数         | 有符值： -263 ~263-1无符号值：0 ~264-1        | 8字节     |
| FLOAT     | 单精度浮点数       | ±1.1754351e -38                               | 4字节     |
| DOUBLE    | 双精度浮点数       | ±2.2250738585072014e -308                     | 8字节     |
| DECIMAL   | 字符串形式的浮点数 | Decimal（M，D）                               | M+2个字节 |

| 字符串类型   | 说明       | 长度        |
| ------------ | ---------- | ----------- |
| CHAR[(M)]    | 定长字符串 | M字节       |
| VARCHAR[(M)] | 可变字符串 | 可变长度    |
| TINYTEXT     | 微型文本串 | 0~28–1字节  |
| TEXT         | 文本串     | 0~216–1字节 |

| 日期类型  | 格式                 | 取值范围                                     |
| --------- | -------------------- | -------------------------------------------- |
| DATE      | YYYY-MM-DD，日期格式 | 1000-01-01~ 9999-12-31                       |
| DATETIME  | YY-MM-DD  hh:mm:ss:  | 1000-01-01 00：00：00 ~9999-12-31 23：59：59 |
| TIME      | hh:mm:ss:            | -835:59:59 ~ 838:59:59                       |
| TIMESTAMP | YYYYMMDDHHMMSS       | 1970年某时刻~2038年某时刻，精度为1秒         |
| YEAR      | YYYY格式的年份       | 1901~2155                                    |

若某日期字段默认值为当前日期，一般设置为TIMESTAMP类型

2.8.4字段约束

| 名称     | 关键字          | 说明                                                |
| -------- | --------------- | --------------------------------------------------- |
| 非空约束 | NOT NULL        | 字段不允许为空                                      |
| 默认约束 | DEFAULT         | 赋予某字段默认值                                    |
| 唯一约束 | UNIQUE KEY(UK)  | 设置字段的值是唯一的 允许为空，但只能有一个空值     |
| 主键约束 | PRIMARY KEY(PK) | 设置该字段为表的主键可唯一标识该表记录              |
| 外键约束 | FOREIGN KEY(FK) | 用于在两表之间建立关系， 需要指定引用主表的哪一字段 |
| 自动增长 | AUTO_INCREMENT  | 设置该列为自增字段 默认每条自增1 通常用于设置主键   |

1.主键约束

CREATE TABLE student（      studentNo INT(4)   **PRIMARY KEY**,      ……）;

2.注释

​    CREATE TABLE test (    

​           id int(11)  **COMMENT ‘编号’**

​    )**COMMENT='测试表’** ;

3.设置字符编码集

   CREATE TABLE [IF NOT EXISTS] 表名（ 

​      省略代码

   ）**CHARSET = 字符集名**;

2.8.5 综合案例创建Student表

~~~mysql
CREATE TABLE student(
    studentNo INT(4) NOT NULL COMMENT '学号' PRIMARY KEY,
    loginPwd VARCHAR(20) NOT NULL COMMENT '密码',  
    studentName VARCHAR(50) NOT NULL COMMENT '学生姓名',
    sex CHAR(2) DEFAULT '男' NOT NULL  COMMENT '性别', 
    gradeId INT(4)  UNSIGNED COMMENT '年级编号', 
    phone VARCHAR(50)  COMMENT '联系电话',
    address VARCHAR(255)  DEFAULT '地址不详'COMMENT '地址',     
     bornDate DATETIME  COMMENT '出生时间',
    email VARCHAR(50) COMMENT'邮件账号',
    identityCard VARCHAR(18)  UNIQUE KEY COMMENT '身份证号'
) COMMENT='学生表';
~~~

2.8.6 查看表结构

~~~mysql
# 格式： desc 表名；
# 查看student表结构
desc student;
~~~

2.8.7 删除表

~~~mysql
# 格式：DROP TABLE [IF  EXISTS] 表名;  
#注意：IF EXISTS 表示判断表是否存在，可以省略,
#     不过一般建议在删除表之前，先使用IF EXISTS语句验证表是否存在
# 比如删除student表
drop table if exists student;
~~~

2.8.8 修改表

~~~mysql
#修改表名 
ALTER TABLE 旧表名 RENAME [TO]  新表名;

#添加字段  
ALTER TABLE 表名 ADD 字段名  数据类型  [属性];

#修改字段
 ALTER TABLE 表名 CHANGE 原字段名 新字段名 数据类型 [属性];

#删除字段
ALTER TABLE 表名 DROP 字段名;

#添加主键
ALTER TABLE 表名 ADD CONSTRAINT 主键名 PRIMARY KEY 表名（主键字段）;

#添加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名  FOREIGN KEY (外键字段) REFERENCES 关联表名(关联字段);

~~~

案例：

~~~mysql
DROP TABLE IF EXISTS demo01;
#创建表
CREATE TABLE  demo01(
   id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
   name VARCHAR(8) NOT NULL
);
#修改表名
ALTER TABLE demo01 RENAME demo02;
#添加字段
ALTER TABLE demo02 ADD `password` VARCHAR(32) NOT NULL;
#修改字段
ALTER TABLE  demo02  CHANGE `name` `username`CHAR(10) NOT NULL;
#删除字段
ALTER TABLE demo02 DROP `password`;

# 如何设置grade表中gradeId字段为主键？
# 参考代码
ALTER TABLE grade ADD CONSTRAINT pk_grade   PRIMARY KEY grade(gradeId);

# 如何将student表的gradeId字段和grade表的gradeId字段建立外键关联？
# 参考代码
ALTER TABLE `student`  ADD  CONSTRAINT fk_student_grade FOREIGN KEY(`gradeId`) 
REFERENCES `grade` (`gradeId`);

~~~

2.9 MySQL的存储引擎

存储引擎的类型：**MyISAM**、**InnoDB** 、Memory、CSV等

MyISAM与InnoDB类型主要区别

| 名称       | InnoDB     | MyISAM |
| ---------- | ---------- | ------ |
| 事务处理   | 支持       | 不支持 |
| 数据行锁定 | 支持       | 不支持 |
| 外键约束   | 支持       | 不支持 |
| 全文索引   | 不支持     | 支持   |
| 表空间大小 | 较大,约2倍 | 较小   |

**适用场合**

 使用MyISAM:   不需事务，空间小，以查询访问为主 

 使用InnoDB:     多删除、更新操作，安全性高，事务处理及并发控制

**查看当前默认存储引擎**

SHOW VARIABLES LIKE ‘storage_engine%’;


**修改默认的存储引擎**

打开my.ini配置文件修改：

default-storage-engine= InnoDB  （**可以将InnoDB修改为其他引擎**）

**设置表的存储引擎**

~~~mysql
CREATE TABLE 表名(
    #省略代码
)ENGINE=存储引擎;

# 案例
CREATE TABLE mydemo (
   id  INT(4)
)ENGINE=MyISAM;

~~~

2.10  DML（数据管理）语句

2.101 插入数据

1.插入单条数据

~~~mysql
# 格式：
# INSERT INTO 表名 [(字段名列表)] VALUES (值列表);
#注意：
#1.字段名是可选的，如省略则依次插入所有字段
#2.多个列表和多个值之间使用逗号分隔
#3.值列表和字段名列表一一对应
#4.如插入的是表中部分数据，字段名列表必填

# 案例：
INSERT INTO student(loginPwd,studentName,gradeId,phone,bornDate)
VALUES('123','黄小平',1,'13956799999','1996-5-8');

~~~

2.插入多条数据

~~~mysql
#格式：INSERT INTO 新表（字段名列表）VALUES(值列表1),(值列表2),……,(值列表n);
#案例：
INSERT INTO subject(subjectName,classHour,gradeID)
VALUES('Logic Java',220,1),('HTML',160,1),('Java OOP',230,2);

#注意：为避免表结构发生变化引发的错误，建议插入数据时写明具体字段名！
~~~

3.将查询的结果加入到插入新表

~~~mysql
# 格式：CREATE TABLE 新表(SELECT 字段1，字段2……  FROM 原表);
# 问题：编写SQL语句实现从学生表提取姓名、手机号两列数据存储到通讯录表中
# 参考代码：
CREATE TABLE phoneList(
    SELECT studentName,phone
    FROM student);
# 注意：如新表已存在，将会报错
~~~

2.102 修改表数据

~~~mysql
# 格式：UPDATE 表名 SET 字段1=值1,字段2=值2,…,字段n=值n [WHERE 条件];
# 案例：
UPDATE student SET sex = ‘女’;
UPDATE student SET address = '北京女子职业技术学校家政班' WHERE address = '北京女子职业技术学校刺绣班';

# 注意：如果不加where 就会修改表中所有的数据
~~~

2.103 删除表数据

~~~mysql
#格式：DELETE FROM 表名 [WHERE条件];
~~~

~~~mysql
#格式：TRUNCATE TABLE 表名;
~~~

**TRUNCATE语句删除后将重置自增列，表结构及其字段、约束、索引保持不变，执行速度比DELETE语句快**

案例：

~~~mysql
DELETE FROM student WHERE studentName = ‘王宝宝’;
TRUNCATE TABLE student;
~~~