一、MySQL表数据类型
MySQL支持多种类型,大致可以分为三类:数值、日期/时间和字符串(字符)类型。 定义数据字段的类型对数据库的优化是非常重要的。
(一)数值类型
MySQL支持所有标准SQL数值数据类型。
这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC),以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。
(二)日期和时间类型
表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
(三)字符串类型
字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。
二、用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;
项目四:交换工资(难度:简单)
创建一个 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');