美文网首页SQL小小白
MySQL入门(三):表操作

MySQL入门(三):表操作

作者: 凡有言说 | 来源:发表于2019-06-14 10:42 被阅读0次

    一、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');
    
    在这里插入图片描述
    参考资料:
    1.MySQL 教程
    2.MYSQLTASK3---表的删除,更新,更改
    3.Datawhale-MySQL-任务三

    相关文章

      网友评论

        本文标题:MySQL入门(三):表操作

        本文链接:https://www.haomeiwen.com/subject/aclufctx.html