一、MySQL表数据类型

MySQL支持多种类型,大致可以分为三类:数值、日期/时间和字符串(字符)类型。 定义数据字段的类型对数据库的优化是非常重要的。

(一)数值类型

MySQL支持所有标准SQL数值数据类型。

这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC),以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。

mysql创建时间字段 mysql创建表时间类型_Math

(二)日期和时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

mysql创建时间字段 mysql创建表时间类型_Math_02

(三)字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。

mysql创建时间字段 mysql创建表时间类型_Math_03

二、用SQL语句创建表

(一)通用语法

CREATE TABLE table_name (column_name column_type);

(二)语句解释

创建MySQL数据表需要以下信息:

  • 表名
  • 表字段名
  • 定义每个表字段

(三)操作示例

CREATE TABLE runoob_tbl 
(
   runoob_id        INT UNSIGNED   AUTO_INCREMENT,
   runoob_title     VARCHAR(100)   NOT NULL,
   runoob_author    VARCHAR(40)    NOT NULL,
   submission_date  DATE,
   PRIMARY KEY ( runoob_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

注意:

  • RIMARY KEY关键字用于定义列为主键。 可以使用多列来定义主键,列间以逗号分隔。比如:primary key(order,item)表示这两个的组合值是唯一的 ,主键必须NOT NULL
  • 如果不想字段为 NULL 可以设置字段的属性为 NOT NULL, 在操作数据库时如果输入该字段的数据为NULL ,就会报错。
  • ENGINE 设置存储引擎,CHARSET 设置编码。
  • auto_increment自动增量 必须和主键组合使用,默认情形起始值为1,每次增量为1

关于主键

一张表只能有一个字段可以使用对应的键,用来唯一的约束该字段里面的数据,不能重复。主键本身一定不为空,且不允许重复。

设定主键一共有三种方式:如何设定和删除主键

三、用SQL语句向表中添加数据

(一)通用语法

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );

注:如果数据是字符型,必须使用单引号或者双引号,如:“value”。

(二)语句解释

MySQL 表中使用 INSERT INTO SQL语句来插入数据。

插入数据有三种方式:

  • 插入完整的行;
  • 插入行的一部分(指定列名);
  • 插入某些查询的结果(INSERT SELECT);

注:不指定列名是一种危险的方式,因为其高度依赖于表中列的定义次序,还依赖于其容易获得的次序信息。因此为保险起见,应指定列名插入。

(三)操作示例

1.插入单行

insert into city (Name,CountryCode,District,Population)
values( 'Kabul' , 'AFG' , 'Kabol' , '1780000' );

2.插入多行

insert into city (Name,CountryCode,District,Population)
values( 'Zarul' , 'BFG' , 'cabol' , '180000' );
values( 'Kabul' , 'AFG' , 'Kabol' , '1780000' );

3.插入某些查询数据

insert into city (Name,CountryCode,District,Population)
select Name,CountryCode,District,Population
from cityx

其实MySQL不关心名字对不对应,只要位置对应即可

四、用SQL语句删除表

(一)通用语法

DROP TABLE table_name ;

(二)不同类型

1.DROP

单独使用的时候是删除整个表

DROP TABLE <表名>;

drop和alter一起使用的时候可以指定column删除某个列

alter table test
    drop column  name;

2.DELETE

单独使用delete语句会留下表,而删除表中的数据 ,通常和where一起使用

delete from Product
	where sale_price>=3000;

注:delete语句不能使用groupby,having,order by子句

另外,delete语句删除对象不是表或者列,而是行 。如果删除单个列的值可以采用update将其更新为Null。

update Product 
set regist_date=null
where product_id='0008';

3.TRUNCATE

TRUNCATE TABLE <表名>

如果想从表中删除所有行,就是用TRUNCATE,速度很快。

小结:当不需要表时,用drop,当要保留该表,但要删除所有记录时,用truncate,当要删除部分记录时,用delete.

五、用SQL语句修改表

(一)修改表中列名

ALTER TABLE 表名 CHANGE 列名 新列名 列类型

(二)修改表中数据

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]
  • 可以同时更新一个或多个字段。
  • 可以在 WHERE 子句中指定任何条件。
  • 可以在一个单独表中同时更新数据。

(三)删除行和列

1.删除行
从一个表中删除(去掉)数据,使用DELETE语句。

2.删除列

ALTER TABLE 表名 DROP COLUMN 列名 ;

(四)新建行和列

1.新建行

INSERT...INTO 表名(字段名)VALUES();

2.新建列

ALTER TABLE 表名 ADD 列名 数据类型;

对比:alter和insert都有插入的作用,但是alter插入的是列(字段),insert插入的是行(记录)

此外,注意:

  • 添加字段和删除字段不同,一个需要指明column一个不需要
  • alter和update都有更改表中某列数据的作用,但是alter更注重的是该列的数据结构,update则改变列的内容。
  • 当alter和drop一起使用删除某个列的时候,该列没有了,但update是该列变成null还是存在该列的

练习

项目三:超过5名学生的课(难度:简单)
创建如下所示的courses 表 ,有: student (学生) 和 class (课程)。
例如,表:

±--------±-----------+
 | student | class |
 ±--------±-----------+
 | A | Math |
 | B | English |
 | C | Math |
 | D | Biology |
 | E | Math |
 | F | Computer |
 | G | Math |
 | H | Math |
 | I | Math |
 | A | Math |
 ±--------±-----------+


编写一个 SQL 查询,列出所有超过或等于5名学生的课。
应该输出:
±--------+
| class |
±--------+
| Math |
±--------+
Note:
学生在每个课中不应被重复计算。

CREATE TABLE course 
(
  student  VARCHAR(50)  NOT NULL,
  class    VARCHAR(50)  NOT NULL
);

INSERT INTO course(student,class) VALUES ('A','Math');
INSERT INTO course(student,class) VALUES ('B','English');
INSERT INTO course(student,class) VALUES ('C','Math');
INSERT INTO course(student,class) VALUES ('D','Biology');
INSERT INTO course(student,class) VALUES ('E','Math');
INSERT INTO course(student,class) VALUES ('F','Computer');
INSERT INTO course(student,class) VALUES ('G','Math');
INSERT INTO course(student,class) VALUES ('H','Math');
INSERT INTO course(student,class) VALUES ('I','Math');
INSERT INTO course(student,class) VALUES ('A','Math');

select class from course group by class having count(distinct student) >=5;

mysql创建时间字段 mysql创建表时间类型_Math_04

项目四:交换工资(难度:简单)
创建一个 salary表,如下所示,有m=男性 和 f=女性的值 。
例如:

| id | name | sex | salary |
 |----|------|-----|--------|
 | 1 | A | m | 2500 |
 | 2 | B | f | 1500 |
 | 3 | C | m | 5500 |
 | 4 | D | f | 500 |


交换所有的 f 和 m 值(例如,将所有 f 值更改为 m,反之亦然)。要求使用一个更新查询,并且没有中间临时表。
运行你所编写的查询语句之后,将会得到以下表:

| id | name | sex | salary |
 |----|------|-----|--------|
 | 1 | A | f | 2500 |
 | 2 | B | m | 1500 |
 | 3 | C | f | 5500 |
 | 4 | D | m | 500 |
CREATE TABLE salary
(
  id         INT          NOT NULL ,
  n_ame      VARCHAR(50)  NOT NULL,
  sex        VARCHAR(50)  NOT NULL,
  salary     INT          NOT NULL
);

INSERT INTO salary(id,n_ame,sex,salary) VALUES (1,'A','m',2500);
INSERT INTO salary(id,n_ame,sex,salary) VALUES (2,'B','f',1500);
INSERT INTO salary(id,n_ame,sex,salary) VALUES (3,'C','m',5500);
INSERT INTO salary(id,n_ame,sex,salary) VALUES (4,'D','f',500);

UPDATE salary SET sex = IF(sex = 'm','f','m');

mysql创建时间字段 mysql创建表时间类型_字段_05