MySQL2:数据类型补完,数据操作(DML)及数据查询操作(DQL)

1152-张同学

发表文章数:21

首页 » MySQL » 正文

三、数据表相关操作

1.什么是表?

  • 数据表是数据库最重要的组成部分之一,是其其它对象的基础;
  • 数据表是存储数据的数据结构;
  • 数据表是包含了特定实体类型的数据;
  • 数据表由行(row)和列(column)构成的二维网络;
  • 数据表一定现有表结构,再有数据;
  • 数据表至少有一列,可以没有行或者多行;
  • 数据表名称要求唯一,而且不要包含特殊字符;

2.如何创建数据表

CREATE TABLE [IF NOT EXISTS] tbl_name(字段名称 字段类型 [完整性约束条件]) ENGINE = 引擎名称 CHARSET = ‘编码方式’。

  • CREATE TABLE [ IF NOT EXISTS ] tbl_name(

    字段名称 字段类型 [ USIGNED | ZEROFILL ] [ NOT NULL ] [DEFAULT] [[PRIMARY] key| UNIQUE [KEY]] [AUTO_INCREMENT ]

    ) ENGINE = INNODB CHARSET=UTF8 AUTO_INCREMENT=100;

-- 创建数据库
CREATE DATABASE IF NOT EXISTS `spike` DEFAULT CHARACTER SET 'UTF8';

USE `spike`;

-- 创建学员表(user)
-- 编号 id
-- 用户名 username
-- 年龄 age
-- 性别 sex
-- 邮箱 email
-- 地址 addr
-- 生日 birth
-- 薪水 salary
-- 电话 tel
-- 是否结婚 married
-- 注意:当需要输入中文的时候,需要临时转换客户端的编码方式
-- SET NAMES GBK 仅针对当年链接有效且从设置位置开始到之后有效
-- 字段注释 通过comment注释内容,给字段添加注释
CREATE TABLE IF NOT EXISTS `user`
(
    id       SMALLINT,
    username VARCHAR(20),
    age      TINYINT,
    sex      ENUM ('男','女','保密'),
    email    VARCHAR(50),
    addr     VARCHAR(200),
    birth    Year,
    salary   FLOAT(8, 2),
    tel      INT,
    married  TINYINT(1) COMMENT 'O代表结婚,非0代表未结婚'
) ENGINE = INNODB
  CHARACTER SET = UTF8;
-- 创建新闻分类表cms_cate
-- 编号、分类名称、分类描述
CREATE TABLE IF NOT EXISTS cms_cate
(
    id       TINYINT,
    cateName VARCHAR(50),
    cateDesc VARCHAR(200)
) ENGINE = MyISAM
  CHARSET = UTF8;

-- 创建新闻表cms_news
-- 编号、新闻标题、新闻内容、新闻发布时间、点击量、是否置顶、新闻所属分类、发布人
CREATE TABLE IF NOT EXISTS cms_news
(
    id       INT,
    title    VARCHAR(50),
    content  TEXT,
    pubTime  INT,
    clickNum INT,
    top      TINYINT(0) COMMENT 'O代表不置顶,非0代表置顶'
);

-- 查看cms_news表的表机构
DESC cms_news;
DESCRIBE cms_news;
SHOW COLUMNS FROM cms_news;
  • 完整性约束条件

    • PRIMARY KEY主键:被标记为主键的值要具有唯一性(相当于ID)且不能为空,一个表中只可以有一个主键,也可以是符合主键;

      -- 测试主键
      CREATE TABLE IF NOT EXISTS user1
      (
          id       INT PRIMARY KEY,
          username VARCHAR(20)
      );
      -- 查看创建表的定义
      SHOW CREATE TABLE user1;
      
      -- 符合主键
      CREATE TABLE IF NOT EXISTS user2
      (
          id       INT,
          username VARCHAR(20),
          card     CHAR(18),
          PRIMARY KEY (id, card)
      );
      
      INSERT user2
      VALUES (1, 'king', '111'); 
      
      INSERT user2
      VALUES (1, 'queen', '112'); -- 因为是符合主键,所有仅有序号重复不会报错
      
    • AUTO_INCREMENT自增长

      -- 测试AUTO_INCREMENT
      CREATE TABLE IF NOT EXISTS user5
      (
          id       SMALLINT KEY AUTO_INCREMENT,
          username VARCHAR(20)
      );
      
      INSERT user5 VALUES(1,'KING');
      -- 列明指定字段,仅给username赋值
      INSERT user5(username) VALUES('queen');
      INSERT user5() VALUES(111,'king1');
      -- 在已有最大编号的基础上自增长一
      INSERT user5(username) VALUES('queen1');
      INSERT user5 VALUES(NULL,'AAA');
      INSERT user5 VALUES(DEFAULT,'AAA');
      

      MySQL2:数据类型补完,数据操作(DML)及数据查询操作(DQL)

      -- 建立表时可以直接指定自增长初始值
      CREATE TABLE IF NOT EXISTS user6
      (
          id       SMALLINT KEY AUTO_INCREMENT,
          username VARCHAR(20)
      ) AUTO_INCREMENT = 100;
      
      INSERT user6(username)
      VALUES ('queen1');
      -- 修改自增长的值
      ALTER TABLE user6 AUTO_INCREMENT = 500;
      INSERT user6(username)
      VALUES ('queen2');
      

      MySQL2:数据类型补完,数据操作(DML)及数据查询操作(DQL)

    • FOREIGN KEY外键

    • NOT NULL非空

      • 定义为非空后,不能插入NULL,INSERT user7(username,password) VALUES(‘NULL’,‘NULL’);

        -- 测试NOT NULL,被定义的字段必须要有值
        CREATE TABLE IF NOT EXISTS user7(
            id INT UNSIGNED KEY  AUTO_INCREMENT,
            username VARCHAR(20) NOT NULL,
            password CHAR(32) NOT NULL,
            age TINYINT UNSIGNED
        );
        
        INSERT user7(username,password) VALUES('KING','KING');
        INSERT user7(username,password,age) VALUES('KING','KING',11);
        
    • UNIQUE KEY唯一

      • 一个表中只能有一个主键,但可以有多个唯一,被标注为唯一的值不能重复,除了NULL值;

        -- 测试唯一UNIQUE KEY
        CREATE TABLE IF NOT EXISTS user9
        (
            id       TINYINT UNSIGNED KEY AUTO_INCREMENT,
            username VARCHAR(20) NOT NULL UNIQUE,
            card     CHAR(18) UNIQUE
        );
        
        INSERT user9(username)
        VALUES ('A');
        INSERT user9(username)
        VALUES ('A1');
        INSERT user9(username, card)
        VALUES ('B1', 111);
        INSERT user9(username, card)
        VALUES ('B1', NULL);
        INSERT user9(username, card)
        VALUES ('B2', NULL);
        

        MySQL2:数据类型补完,数据操作(DML)及数据查询操作(DQL)

    • DEFAULT默认值

      -- 测试默认值DEFAULT
      CREATE TABLE IF NOT EXISTS user8
      (
          id       INT UNSIGNED KEY AUTO_INCREMENT,
          username VARCHAR(20)         NOT NULL,
          password CHAR(32)            NOT NULL,
          age      TINYINT UNSIGNED             DEFAULT 18,
          addr     VARCHAR(50)         NOT NULL DEFAULT '北京',
          sex      ENUM ('男','女','保密') NOT NULL DEFAULT '男'
      );
      
      INSERT user8(username, password)
      VALUES ('KING', 'KING');
      INSERT user8
      VALUES (2, 'QUEEN', 'QUEEN', 29, '山海', '保密');
      -- 也可以直接插入DEFALUT,代表使用默认值
      INSERT user8
      VALUES (3, 'QUEEN', 'QUEEN', DEFAULT, DEFAULT, '保密');
      

      MySQL2:数据类型补完,数据操作(DML)及数据查询操作(DQL)

3.如何查看数据库中的数据表以及表结构

  • 查看数据库下的数据表:

    • SHOW TABLES;
  • 查看指定表的表结构:

    • DESC tbl_name
    • DESCRIBE tbl_name
    • SHOW COLUMNS FROM tbl_name

4.如何修改表结构

修改表名

  • ALTER TABLE tbl_name RENAME [TO|AS ] new_name; 此处的to可以省略。

  • RENAME TABLE tbl_name TO new_name; 此处的to不可以省略。

    -- 重命名数据表
    CREATE TABLE IF NOT EXISTS user10
    (
        id       SMALLINT UNSIGNED KEY AUTO_INCREMENT,
        username VARCHAR(20)  NOT NULL UNIQUE,
        password CHAR(32)     NOT NULL,
        email    VARCHAR(50)  NOT NULL DEFAULT '936238703@QQ.COM',
        age      TINYINT UNSIGNED      DEFAULT 18,
        sex      ENUM ('男','女','保密')   DEFAULT '保密',
        addr     VARCHAR(200) NOT NULL DEFAULT '北京',
        salary   FLOAT(6, 2),
        regtime  INT UNSIGNED,
        face     CHAR(100)    NOT NULL DEFAULT 'default.jpg'
    );
    
    -- 将user10重命名成user11
    ALTER TABLE user10 RENAME TO user11;
    RENAME TABLE user11 TO user10;
    ALTER TABLE user10 RENAME user11;
    

添加字段

  • ALTER TABLE tbl_name ADD字段名称 字段类型 [完整性约束条件] [FIRST|AFTER 字段名称]

    -- 为user10添加card字段CHAR(18)
    ALTER TABLE user10
        ADD card CHAR(10);
    ALTER TABLE user10
        ADD test1 VARCHAR(100) NOT NULL UNIQUE;
    ALTER TABLE user10
        ADD test2 VARCHAR(20) NOT NULL FIRST;
    ALTER TABLE user10
        ADD test3 INT NOT NULL DEFAULT 100 AFTER username;
    
    -- 选中一次表,完成多个操作
    ALTER TABLE user10
        ADD test4 INT NOT NULL DEFAULT 123 AFTER password,
        ADD test5 FLOAT(6, 2) FIRST,
        ADD test6 SET ('A','B','C');
    

删除字段

  • ALTER TABLE tbl_name DROP 字段名称

    -- 删除字段
    ALTER TABLE user10
        DROP test6;
    
    -- 一次删除test2,test3,test4,test5
    ALTER TABLE user10
        DROP test2,
        DROP test3,
        DROP test4,
        DROP test5;
    
    -- 添加test字段删除addr字段
    ALTER TABLE user10
        ADD test VARCHAR(20) AFTER username,
        DROP addr;
    

修改字段

  • ALTER TABLE tbl_name MODIFY 字段名称 字段类型 [完整性约束条件] [FIRST|AFTER 字段名称]

    -- 将email VARCHAR(200)
    ALTER TABLE user10
        MODIFY email VARCHAR(200);
    ALTER TABLE user10
        MODIFY email VARCHAR(50) NOT NULL DEFAULT '936238703@QQ.com';
    
    -- 将card字段移动到test之后
    ALTER TABLE user10 MODIFY card CHAR(18) AFTER test;
    
    -- 将test字段修改为CHAR(20) NOT NULL DEFAULT '123',移动到第一个位置
    ALTER TABLE user10 MODIFY test CHAR(20) NOT NULL DEFAULT '123' FIRST;
    

修改字段名称

  • ALTER TABLE tbl_name CHANGE 旧字段名称 新字段名称 字段类型 [完整性约束条件] [FIRST|AFTER 字段名称]

    -- 将test字段改为test2
    ALTER TABLE user10 CHANGE test test2 CHAR(32) NOT NULL DEFAULT '123';
    ALTER TABLE user10 CHANGE test2 test VARCHAR(200) NOT NULL AFTER username;
    ALTER TABLE user10 CHANGE test test INT;
    

添加默认值

  • AFTER TABLE tbl_name ALTER 字段名称 SET DEFAULT 默认值

    -- 添加默认值
    CREATE TABLE IF NOT EXISTS user11
    (
        id       TINYINT UNSIGNED KEY AUTO_INCREMENT,
        username VARCHAR(20) NOT NULL UNIQUE,
        age      TINYINT UNSIGNED
    );
    
    ALTER TABLE user11
        ALTER age SET DEFAULT 18;
    ALTER TABLE user11
        ADD email VARCHAR(50);
    ALTER TABLE user11
        ALTER email SET DEFAULT '936238703@QQ.COM';
    

删除默认值

  • ALTER TABLE tbl_name ALTER 字段名称 DROP DEFAULT

    -- 删除默认值
    ALTER TABLE user11
        ALTER age DROP DEFAULT;
    ALTER TABLE user11
        ALTER email DROP DEFAULT;
    

添加主键

  • ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY[index_type] (字段名称,…)

    -- 添加主键
    CREATE TABLE IF NOT EXISTS test12
    (
        id INT
    );
    
    ALTER TABLE test12
        ADD PRIMARY KEY (id);
    
    CREATE TABLE IF NOT EXISTS test13
    (
        id       INT,
        card     CHAR(18),
        username VARCHAR(20) NOT NULL
    );
    
    ALTER TABLE test13
        ADD PRIMARY KEY (id, card);
    

删除主键

  • ALTER TABLE tbl_name DROP PRIMARY KEY

    -- 删除主键
    ALTER TABLE test12
        DROP PRIMARY KEY;
    ALTER TABLE test13
        DROP PRIMARY KEY;
    
    -- 当一个字段主键同时又是AUTO_INCREMENT的时候,要先修改AUTO_INCREMENT,再删除主键
    CREATE TABLE IF NOT EXISTS test14
    (
        id INT PRIMARY KEY AUTO_INCREMENT
    );
    
    ALTER TABLE test14
        MODIFY id INT UNSIGNED;
    ALTER TABLE test14
        DROP PRIMARY KEY;
    

添加唯一

  • ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [索引名称] (字段名称,…)

    -- 添加唯一索引
    CREATE TABLE IF NOT EXISTS user12
    (
        id       TINYINT UNSIGNED KEY AUTO_INCREMENT,
        username VARCHAR(20) NOT NULL,
        card     CHAR(18)    NOT NULL,
        test     VARCHAR(20) NOT NULL,
        test1    CHAR(32)    NOT NULL
    );
    
    ALTER TABLE user12
        ADD UNIQUE (username);
    
    ALTER TABLE user12
        ADD CONSTRAINT symbol UNIQUE KEY uni_card (card);
    
    ALTER TABLE user12
        ADD CONSTRAINT symbol UNIQUE INDEX mulUni_test_test1 (test, test1);
    

MySQL2:数据类型补完,数据操作(DML)及数据查询操作(DQL)

删除唯一

  • ALTER TABLE tbl_name DROP {INDEX|KEY} index_name

    -- 删除唯一索引
    ALTER TABLE user12 DROP INDEX username;
    ALTER TABLE user12
        DROP KEY uni_card;
    ALTER TABLE user12
        DROP KEY mulUni_test_test1;
    
  • MySQL2:数据类型补完,数据操作(DML)及数据查询操作(DQL)

修改表的存储引擎

  • ALTER TABLE tbl_name ENGINE=存储引擎名称

    -- 修改表的存储引擎为MyISAM
    ALTER TABLE user12 ENGINE = MyISAM;
    

设置自增长的值

  • ALTER TABLE tbl_name AUTO_INCREMENT=值

    -- 修改自增长值
    ALTER TABLE user12 AUTO_INCREMENT=100;
    

5.如何删除数据表

  • DROP TABLE [IF EXISTS] tbl_name [tbl_name]

    -- 删除数据表user12
    DROP TABLE user12;
    
    DROP TABLE
    IF EXISTS user12;
    
    -- 一次删除多个数据表
    DROP TABLE
    IF EXISTS user11,
     user10,
     user9;
    
    -- 如果删除的数据中有未创建的数据表,则仅删除已有数据表
    DROP TABLE IF EXISTS user123,user6,user7,user8;
    SHOW TABLES;
    

四、MySQL中的数据类型

1.整数类型

  • 数据类型 存储范围 字节
    TINYINT 有符号值:-128到127(-27到27-1) 1
    无符号值:0到255(0到28-1)
    SMALLINT 有符号值:-32768到32767(-215到215-1) 2
    无符号值:0到65535(0到216-1)
    DEDIUMINT 有符号值:-8388608到8388607(-223到223-1) 3
    无符号值:0到4294967295(0到224-1)
    INT 有符号值:-2147683648到2147683647(-251到251-1) 4
    无符号值:0到4294967295(0到252-1)
    BIGINT 有符号值:-9223372036854775808到9223372036854775807(-263到263-1) 8
    无符号值:0到18446744073709551615(0到264-1)
    BOOL,BOOLEAN 等价于TINYINT{1},0为false,其余为true 1
    -- 测试整型
    CREATE TABLE test1
    (
        num1 TINYINT,
        num2 SMALLINT,
        num3 MEDIUMINT,
        num4 INT,
        num5 BIGINT
    );
    

2.浮点类型

  • 数据类型 存储范围 字节
    FLOAT[(M,D)] 负数取值范围为-3.40E+38到-1.17E-38、0和1.175E-38到3.40E+38; 4
    M是数字总位数,D是小数点后面的位数。如果M和D被省略,根据硬件允许的限制来保存值。单精度浮点数精确到大约7位小数。
    DOUBLE[(M,D)] -1.79E+308到-2.22E-308、0和2.22E-308到1.79E+308 8
    DECIMAL[(M,D)] 和DOUBLE一样,内部以字符串形式存储数值 M+2

3.字符串类型

  • 列类型 存储需求
    CHAR(M) M个字节,0<= M <= 255
    VARCHAR(M) L+1个字节,其中L<=M且0<= M <= 65535
    TINYTEXT l+1个字节,其中L<28
    TEXT l+1个字节,其中L<216
    MEDIUMTEXT l+1个字节,其中L<224
    LONGTEXT l+1个字节,其中L<232
    ENUM(‘value1’,‘value2’,…) 1或2个字节,取决与枚举值的个数(最多65535个值)
    SET(‘value1’,‘value2’,…) 1、2、3、4或者8个字节,取决于set成员的数目(最多64个成员)
    • CHAR定长字符串,占用空间大,速度快;

    • VARCHAR变长字符串,占用空间小,速度慢;

    • TEXT类型是一种特殊的字符串类型,只能保存字符数据,而且不能有默认值

    • 上述三类的存储和检索数据的方式都不一样,数据检索的效率:CHAR > VARCHAR > TEXT

      CREATE TABLE IF NOT EXISTS test5
      (
          str1 CHAR(5),
          str2 VARCHAR(5)
      );
      
      INSERT test5
      VALUES ('1', '1');
      
      INSERT test5
      VALUES ('12345', '12345');
      
      -- CHAR不保留插入的空格,VARCHAR保留
      INSERT test5
      VALUES ('1    ', '1    ');
      
      -- 为了体现CHAR与VARCHAR对于空格保留的体现,通过CONCAT函数为str插入'-'
      SELECT CONCAT(str1, '-'), CONCAT(str2, '-')
      FROM test5;
      
      -- 字符串开始的空格CHAR不会去掉
      INSERT test5
      VALUES ('  a', '  a');
      
      SELECT CONCAT('-', str1), CONCAT('-', str2)
      FROM test5;
      
      -- 插入中文
      INSERT test5
      VALUES ('啊', '啊');
      
      -- 中文在UTF8下占用的长度是3个,但也只占有一个在字符
      SELECT LENGTH('啊');
      SELECT CHAR_LENGTH('啊');
      
      -- TEXT不能有默认值
      CREATE TABLE test6
      (
          str1 TEXT
      );
      
      INSERT test6
      VALUES ('在山的那边,海的那边,有一群蓝精灵');
      
      -- 测试枚举类型
      CREATE TABLE IF NOT EXISTS test7
      (
          sex ENUM ('男','女','保密')
      );
      
      INSERT test7
      VALUES ('男');
      INSERT test7
      VALUES ('女');
      INSERT test7
      VALUES ('保密');
      
      -- 如果插入在'男''女''保密'之外的值,则报错,'男''女''保密'可以用序号表示,即1,2,3(序号顺序从1开始)
      -- 可以插入NULL,但不能插入空值
      INSERT test7
      VALUES (NULL);
      
      -- 测试集合类型
      CREATE TABLE IF NOT EXISTS test8
      (
          fav SET ('A','B','C','D')
      );
      
      INSERT test8
      VALUES ('A,C,D');
      INSERT test8
      -- 显示的顺序还是按照检索中的顺寻,即如下显示的是ABD
      VALUES ('D,B,A');
      -- MySQL存储是通过二进制,可根据二进制之和显示,如3代表A与B之和
      INSERT test8
      VALUES (3);
      

4.日期时间类型

  • 列类型 存储范围 存储需求
    TIME -838:59:59~838:59:59 3
    DATE 1000-01-01~9999-12-31 3
    DATETIME 1000-01-01 00:00:00999912~31 23:59:59 8
    TIMESTAMP 19700101 00:00:01 UTC~2038-01-19 03:14:07 4
    YEAR 1901~2155 1
    • YEAR:

    • TIME:可以录入天数,但会换算成小时,所以范围数值为 -838:59:59~838:59:59

      -- 测试YEAR
      CREATE TABLE test9
      (
          birth YEAR
      );
      
      INSERT test9
      VALUES (1901);
      INSERT test9
      VALUES (2155);
      INSERT test9
      VALUES (12);
      INSERT test9
      VALUES ('79');
      INSERT test9
      VALUES ('0');
      
      -- 测试TIME
      CREATE TABLE IF NOT EXISTS test10
      (
          test TIME
      );
      INSERT test10
      VALUES ('1 12:12:12');
      INSERT test10
      VALUES ('11:11');
      INSERT test10
      VALUES ('1234');
      INSERT test10
      VALUES ('12');
      INSERT test10
      VALUES ('0');
      
      CREATE TABLE IF NOT EXISTS test11
      (
          test DATE
      );
      INSERT test11
      VALUES ('12-6-7');
      INSERT test11
      VALUES ('12/6/7');
      INSERT test11
      VALUES ('120607');
      

五、其他常用操作

  • 零填充ZEROFILL,当使用ZEROFILL时,自动默认为无符号,从0开始
  • 查询表中所有记录SELECT * FROM tbl_name;
CREATE TABLE test3
(
    num1 TINYINT ZEROFILL,
    num2 SMALLINT ZEROFILL,
    num3 MEDIUMINT ZEROFILL,
    num4 INT ZEROFILL,
    num5 BIGINT ZEROFILL
);

-- 未达到显示长度,则进行补充零
INSERT test3
VALUES (1, 1, 1, 1, 1);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jFf9zuy5-1594647420035)(C:/Users/z&y/AppData/Roaming/Typora/typora-user-images/image-20200709212729254.png)]

  • 向表中插入记录INSERT tbl_name VALUE|VALUES(值,…)
INSERT test1
VALUES (-128, -3278, -8388608, -2147483648, -9223372036854775808);
  • 无符号UNSIGNED,如学员编号不可能出现负数,就用UNSIGNED修饰;
CREATE TABLE test2
(
    num1 TINYINT UNSIGNED,
    num2 TINYINT
);

INSERT test2
VALUES (0, -12);
  • 查看创建表的定义

    -- 查看创建表的定义
    SHOW CREATE TABLE user1;
    
    INSERT user1
    VALUES (1, 'king');
    INSERT user1
    VALUES (2, 'Queen');
    
    CREATE TABLE IF NOT EXISTS user2
    (
        id       INT,
        username VARCHAR(20),
        card     CHAR(18),
        PRIMARY KEY (id, card)
    );
    
    INSERT user2
    VALUES (1, 'king', '111');
    
    INSERT user2
    VALUES (1, 'queen', '112');
    

七、数据的操作

1.插入数据

  • 不指定具体的字段名:INSERT [INTO] tbl_name VALUES|VALUE(值…)

    -- 测试插入记录INSERT
    -- 一次插入一条记录
    CREATE TABLE
    IF NOT EXISTS USER (
    	id TINYINT UNSIGNED AUTO_INCREMENT KEY,
    	username VARCHAR (20) NOT NULL UNIQUE,
    	PASSWORD CHAR (32) NOT NULL,
    	email VARCHAR (50) NOT NULL DEFAULT '936238703@QQ.COM',
    	age TINYINT UNSIGNED DEFAULT 18
    );
    
    DESC USER;
    
    INSERT INTO USER
    VALUES
    	(
    		1,
    		'king',
    		'king',
    		'king@qq.com',
    		20
    	);
    
    INSERT USER
    VALUES
    	(
    		2,
    		'QUEEN',
    		'QUEEN',
    		'QUEEN@qq.com',
    		30
    	);
    
    INSERT USER (username, PASSWORD)
    VALUES
    	('A', 'AAA');
    
    INSERT USER (username, PASSWORD)
    VALUES
    	('B', 'BBB');
    
    SELECT * FROM user;
    
  • 列出指定字段:INSERT [INTO] tbl_name(字段名称1,…) VALUES | VALUE(值1,…)

  • 同时插入多条记录:INSERT [INTO] tbl_name [(字段名称,…)] VALUES(值…), (值…)…

    -- 一次插入多条记录
    INSERT user VALUES(5,'c','ccc','cccc@qq.com',20),
    (6,'D','DDD','DDD@qq.com',20),
    (7,'E','EEE','EEE@qq.com',25);
    
  • 通过SET形式插入记录:INSERT [INTO] tbl_name SET 字段名称=值,…

    -- 通过INSERT SET形式插入记录
    INSERT INTO user SET id=98,username='test',password='this is a test',email='123@qq.com',age=35;
    
  • 将查询结果插入到表中:INSERT [INTO] tbl_name [(字段名称,…)] SELECT 字段名称 FROM tbl_name [WHERE 条件]

    -- 将查询结果插入到表中
    CREATE TABLE testUser(
    id TINYINT UNSIGNED AUTO_INCREMENT KEY,
    username VARCHAR(20) NOT NULL UNIQUE
    );
    
    INSERT testUser SELECT id,username FROM user;
    
    -- 字段数目不匹配则报错
    INSERT testUser SELECT * FROM user;
    
    TRUNCATE testUser;
    INSERT testUser(username) SELECT username FROM user;
    

2.更新数据

  • UPDATE tbl_name SET 字段名称=值, …[WHERE 条件] [ORDER BY 字段名称] [LIMIT 限制条数]

    -- 将user表中所有用户的年龄更新为15
    UPDATE user SET age=5;
    
    UPDATE user SET age=20,email='尼达叶@qq.com';
    
    -- 将第一条记录的passw,email,age改变
    UPDATE user SET password='king123',email='123@qq.com',age=99 where id=1;
    SELECT * FROM user;
    
    -- 将id大于等于3的字段的age改为age-5
    UPDATE user SET age =age-5 WHERE id>=3;
    
    -- 恢复默认值
    UPDATE user SET age=DEFAULT WHERE username='A';
    

3.删除数据

  • DELETE FROM tbl_name [WHERE 条件] [ORDER BY 字段名称] [LIMIT 限制条数]

  • 彻底清空数据表:TRUNCATE [TABLE] tbl_name,同时可以重置自增长值;

    -- 删除testUser表中的记录,如果删除时不加条件(WHERE),所有记录都会被删除
    DELETE FROM User WHERE id=1;
    
    -- 当删除数据后,AUTO_INCREMENT仍然从清空之前的id开始,即100+1
    DELETE FROM user;
    INSERT user(username,password) VALUES('king','123');
    

    MySQL2:数据类型补完,数据操作(DML)及数据查询操作(DQL)

    -- 可以重新设置自增长值
    DELETE FROM user;
    ALTER TABLE user AUTO_INCREMENT=1;
    INSERT user(username,password) VALUES('king','123');
    
    -- TRUNCATE在删除数据的同时也会清空自增长值
    TRUNCATE user;
    INSERT user(username,password) VALUES('king','123'),
    ('Queen','456');
    

    MySQL2:数据类型补完,数据操作(DML)及数据查询操作(DQL)

    八、查询数据操作(DQL)

1.查询记录

  • SELECT select_expr [select_expr…] [FROM table_references [WHERE 条件] [GROUP BY {col_name | position} [ASC |DESC], …分组] [HAVING 条件 对分组结果进行二次筛选] [ORDER BY {col_name | position} [ASC | DESC], …排序] [LIMIT 限制显示条数]];

2.查询表达式

  • 每一个表达式表示想要的一列,必须至少有一列,多个列之间以逗号分割;

    -- 查询管理员编码和名称
    SELECT id,username FROM cms_admin;
    SELECT id,username,role FROM cms_admin;
    
    -- 表来自于哪个数据库下
    SELECT id,username,role FROM spike.cms_admin;
    
    -- 字段来自于哪张表
    SELECT cms_admin.id,cms_admin.username,cms_admin.role FROM spike.cms_admin;
    
  • 表示所有列, tbl_name.*可以表示命名表的所有列;

    SELECT * FROM cms_admin;
    SELECT cms_admin.* FROM cms_admin;
    
  • 查询表达式可以使用[AS]alias_name为其赋予别名;

    -- 查询时给表名起别名,AS可以省略
    SELECT id,username FROM cms_admin AS a;
    SELECT id,username FROM cms_admin a;
    SELECT a.id,a.username,a.email,a.role FROM cms_admin a;
    
    -- 给字段起别名,建议保留AS
    SELECT id AS '编号',username AS '用户名',email AS '邮箱',role '角色' FROM cms_admin;
    SELECT a.id AS i,a.username AS u,a.email AS e,a.role AS r FROM cms_admin AS a;
    SELECT id as proID,proID AS id,username FROM cms_user;
    SELECT 1,2,3,4,5,id,username FROM cms_user;
    

    MySQL2:数据类型补完,数据操作(DML)及数据查询操作(DQL)

3.WHERE 条件

查询条件 符号
比较 =、<、<=、>、>=、!=、<>(不等于)、!>、!<、<=>(检测NULL值)
指定范围 BETWEEN AND、NOT BETWEEN AND
指定集合 IN、NOT IN
匹配字符 LIKE、NOT LIKE
是否为控制 IS NULL、IS NOT NULL
多个查询条件 AND、OR
-- WHERE条件
SELECT id,username,email FROM cms_user WHERE id=1;
SELECT id,username,email FROM cms_user WHERE username='king';

-- 查询编号不为1的用户
SELECT * FROM cms_user WHERE id!=1;
SELECT * FROM cms_user WHERE id<>1;

ALTER TABLE cms_user ADD age TINYINT UNSIGNED DEFAULT 18;
INSERT cms_user(username,password,regTime,proId,age)
VALUES('test1','test1',1419811708,1,NULL);

SELECT * FROM cms_user;

-- 查询表中记录age值为NULL,WHERE查询条件不能直接用=,需要使用<=>
SELECT * FROM cms_user WHERE age<=>NULL;
SELECT * FROM cms_user WHERE age<=>18;

-- IS NULL和IS NOT NULL-
SELECT * FROM cms_user WHERE age IS NULL;
SELECT * FROM cms_user WHERE age IS NOT NULL;
-- 查询编号在3-10之间的用户
SELECT * FROM cms_user WHERE id BETWEEN 3 AND 10;
SELECT * FROM cms_user WHERE 3<=id<=10;

-- 查询编号为1,3,5,7,9,11,13,100
SELECT * FROM cms_user WHERE id IN(1,3,5,7,9,11,13,100);

-- 查询proID为1和3的用户
SELECT * FROM cms_user WHERE proID IN(1,3);

-- 查询用户名为king,queen,张三、章子怡的记录
SELECT * FROM cms_user WHERE username IN('king','queen','张三','章子怡');
-- 查询时会忽略大小写
SELECT * FROM cms_user WHERE username IN('King','Queen','张三','章子怡');
-- 模糊查询
-- %:代表0个一个或多个任意字符;
-- _:代表1个任意字符;

-- 查询姓张的用户
SELECT * FROM cms_user WHERE username LIKE '张%';

-- 查询用户名中包含in的用户;
SELECT * FROM cms_user WHERE username LIKE '%in%';

-- 查询用户名为3位的用户,3个_
SELECT * FROM cms_user WHERE username LIKE '___';

-- 查询用户名_i%
SELECT * FROM cms_user WHERE username LIKE '_i%';

4.GROUP BY查询结果分组

  • 配合GROUP_CONCAT()得到分组情况

    -- 按照用户所属省份分组proId
    SELECT * FROM cms_user GROUP BY proId;
    
    -- 向用户表中添加性别字段
    ALTER TABLE cms_user ADD sex VARCHAR(20);
    
    UPDATE cms_user set sex='男' WHERE id IN(1,3,5,7,9);
    UPDATE cms_user set sex='女' WHERE id IN(2,4,6,8,10);
    UPDATE cms_user set sex='保密' WHERE id IN(11);
    UPDATE cms_user set sex='保密' WHERE id IN(12);
    UPDATE cms_user set age =18 WHERE id=12;
    
    -- 按照用户性别分组
    SELECT * FROM cms_user GROUP BY sex;
    
    -- 按照字段位置分组,7代表了proId的列数
    SELECT * FROM cms_user GROUP BY 7;
    
    -- 按照多个字段分组
    SELECT * FROM cms_user GROUP BY SEX,proId; 
    
    -- 查询编号大于等于5的用户,按照sex分组
    SELECT * FROM cms_user WHERE id >=5 GROUP BY sex;
    

    MySQL2:数据类型补完,数据操作(DML)及数据查询操作(DQL)

  • 配合聚合函数

    • COUNT()

      -- 查询编号,sex,用户名详情以及组中总人数,按照sex分组
      SELECT id,sex,GROUP_CONCAT(username) AS users,COUNT(*) AS totalUsers FROM cms_user GROUP BY sex;
      
      -- 统计表中所有记录(11条)
      SELECT COUNT(*) AS totalUser FROM cms_user;
      SELECT COUNT(id) AS totalUser From cms_user;
      
    • MAX()

    • MIN()

    • AVG()

    • SUM()

      -- 查询编号,性别,用户名详情,组中总人数,组中最大年龄,最小年龄,平均年龄,以及年龄总和按照性别来分组
      SELECT id,sex,GROUP_CONCAT(username),
      COUNT(*) AS totalUsers,
      MAX(age) AS maxAge,
      MIN(age) AS minAge,
      AVG(age) AS avgAge,
      SUM(age) AS totalAge
      FROM cms_user
      GROUP BY sex;
      
  • 配合WITH ROLLUP记录上面所有记录的总和

    -- WITH ROLLUP
    SELECT id,sex,GROUP_CONCAT(username),
    COUNT(*) AS totalUsers,
    MAX(age) AS maxAge,
    MIN(age) AS minAge,
    AVG(age) AS avgAge,
    SUM(age) AS totalAge
    FROM cms_user
    GROUP BY sex WITH ROLLUP;
    

    MySQL2:数据类型补完,数据操作(DML)及数据查询操作(DQL)

5.HAVING子句

  • 通过HAVING子句对分组结果进行二次筛选

    -- 查询性别sex,用户名详情,组中总人数,最大年龄,年龄总和,根据性别分组
    SELECT sex,GROUP_CONCAT(username) AS users,
    COUNT(*) AS totalUsers,
    MAX(age) AS maxAge,
    SUM(age) as totalAge 
    FROM cms_user
    GROUP BY sex;
    
    -- 查询组中人数大于2的
    SELECT sex,GROUP_CONCAT(username) AS users,
    COUNT(*) AS totalUsers,
    MAX(age) AS maxAge,
    SUM(age) as totalAge 
    FROM cms_user
    GROUP BY sex
    HAVING COUNT(*) >2;
    
    -- 查询组中人数大于2并且最大年龄大于60的
    SELECT sex,GROUP_CONCAT(username) AS users,
    COUNT(*) AS totalUsers,
    MAX(age) AS maxAge,
    SUM(age) as totalAge 
    FROM cms_user
    GROUP BY sex
    HAVING COUNT(*) >2 AND MAX(age) >60;
    
    -- 查询编号大于等于2的用户
    SELECT sex,GROUP_CONCAT(username) AS users,
    COUNT(*) AS totalUsers,
    MAX(age) AS maxAge,
    SUM(age) as totalAge 
    FROM cms_user
    WHERE id>=2;
    GROUP BY sex
    HAVING COUNT(*) >2 AND MAX(age) >60;
    

6.ORDER BY排序

  • 通过ORDER BY对查询结果排序

未经允许不得转载:作者:1152-张同学, 转载或复制请以 超链接形式 并注明出处 拜师资源博客
原文地址:《MySQL2:数据类型补完,数据操作(DML)及数据查询操作(DQL)》 发布于2020-07-13

分享到:
赞(0) 打赏

评论 抢沙发

评论前必须登录!

  注册



长按图片转发给朋友

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏

Vieu3.3主题
专业打造轻量级个人企业风格博客主题!专注于前端开发,全站响应式布局自适应模板。

登录

忘记密码 ?

您也可以使用第三方帐号快捷登录

Q Q 登 录
微 博 登 录