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

最新推荐文章于 2024-08-06 21:36:57 发布
最新推荐文章于 2024-08-06 21:36:57 发布
阅读量198 收藏
点赞数
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/spike1988zc/article/details/107326004
版权

文章目录

三、数据表相关操作

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');

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

      -- 建立表时可以直接指定自增长初始值
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');

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

    • 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);

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

    • 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, '保密');

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

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);

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

删除唯一

  • 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;

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

修改表的存储引擎

  • 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,其余为true1
    -- 测试整型
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+3088
    DECIMAL[(M,D)]和DOUBLE一样，内部以字符串形式存储数值M+2

3.字符串类型

  • 列类型存储需求
    CHAR(M)M个字节，0<= M <= 255
    VARCHAR(M)L+1个字节，其中L<=M且0<= M <= 65535
    TINYTEXTl+1个字节，其中L<28
    TEXTl+1个字节，其中L<216
    MEDIUMTEXTl+1个字节，其中L<224
    LONGTEXTl+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:593
    DATE1000-01-01~9999-12-313
    DATETIME1000-01-01 00:00:00999912~31 23:59:598
    TIMESTAMP19700101 00:00:01 UTC~2038-01-19 03:14:074
    YEAR1901~21551

    • 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');

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

    -- 可以重新设置自增长值
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');

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

    八、查询数据操作(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;

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

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;

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

  • 配合聚合函数

    • 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;

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

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：从入门到脱发
关注
MySQL-1 基础知识 数据库常用操作 表操作 数据类型 字段约束(主键约束,非空约束,唯一约束) 条件查询 分组 基初函数
至尊宝的月光宝盒
07-15 782
文章目录MySQL基础数据库常用操作表的常用操作表记录的常用操作数据类型命名规则字符数字日期字段约束主键约束非空约束唯一约束准备数据基础函数条件查询统计案例聚合 aggregation分组 group小结char和varchar有什么区别？datetime和timestamp有什么区别？中文乱码注释主键、外键、唯一索引的区别？drop、delete和truncate之间的区别？ MySQL基础 结构化查询语言(Structured Query Language)简称SQL，是一种特殊目的的编程语言，是一种
mysql入门基础笔记——DQL查询语句
worther的博客
02-27 1320
数据库中的基本单元：表 table 查看数据库下表：mysql> show tables; ·数据库中是以表来储存数据的，表较为直观且每一行都有行和列 行（row）：数据/字段 列（colum）:字段 ，每一个字段都有字段名，数据类型和约束条件等属性 SQL语句分类 DQL:数据查询语言（凡是带有select关键字的都是查询语句） DML:数据操作语言（凡是对标志那个数据进行增删改的都是DML） insert 增 delete 删 updata 改 DDL：数据定义语言（凡是
DMDTS MySQL2DM
Flora
06-05 344
DM DTS数据迁移 MySQL8.0迁移到DM数据库
达梦数据库(一)mysql2dm
Amarone'blog
07-20 951
mysql -> 达梦数据库
数据库表结构相关查询
wseghhh的博客
01-07 741
数据库表结构相关查询
手写MySQL(二) DM模块篇-通用缓存框架
zhang24360的博客
06-12 530
无
MySQL2 DML数据操纵语言和SQL约束
最新发布
Stringzhua的博客
08-06 1248
DML数据操纵语言和SQL约束
mysql 的数据的操作增删改查和用户管理
lv74134的博客
07-08 363
一，数据类型介绍 1、常用的数据类型: int:整型，用于定义整数类型的数据 float:单精度浮点4字节32位，准确表示到小数点后六位 double:双精度浮点8字节64位 char:固定长度的字符类型，用于定义字符类型数据。13814542131 则为char (11) varchar:可变长度的字符类型，varchar设置上限varchar ( 10)123456 text:文本 image:图片 decimal (5,2):5个有效长度数字，小数点后面有2位，指定整数和小数位数 #Char如果存入数
2024年大数据最全数据仓库｜数据库面试题总结_面试题 数据仓库
2301_82243558的博客
05-02 258
顾名思义，就是最左优先，在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
MySQL：基础篇
ywy124578的博客
07-05 1817
MySQL 基础部分，包括 SQL的基本语法以及事务相关的内容
MySQL-2（DML操作）
getBinary的博客
02-24 97
MySQL-2添加数据删除修改外键约束 添加数据 insert into 表名[(列1,列2,...,列n)] values (值1,值2,...,值n), (值1,值2,...,值n),...; value中的值为要添加的值，对应前面定义的列名； 如果前面不限制列名，就要和表格里面所有列匹配，且顺序一致； values后面每个括号代表一行的数据，可同时添加多行； 删除 delete from 表名 [where 条件判断]; where为条件控制，如果没有，则默认对全表操作； 判断运算符： AND和
Mysql到DM迁移
disclipline的博客
06-25 1173
MySQL 的体系架构是单实例多库，DM7 以上的版本是单库多实例的架构， MySQL 可能是一个 root 用户访问多个库，访问前切换一下当前库即可。例如 MySQL 中有一个库 test，达梦里面先创建一个表空间 tab_test，然后创建一个用户 test，指定默认表空间为 tab_test。迁移 MySQL 中 test 库的数据的时候，用 root 用户连接 MySQL，指定当前库为 test；迁移完整的库建议按照库模式更新统计信息，凡是涉及到迁移数据的及时进行更新统计信息的操作。
MySQL迁移到DM
m0_67269899的博客
11-02 1521
MySQL迁移达梦
将mysql数据迁移到dm
啦卡的博客
03-31 2357
1、新建一个dm数据库实例 注意：端口号不要用5236，推荐用5237，数据库名字自取 其他默认 2、使用dm迁移工具将数据从mysql迁移到dm 3、检查是否成功导入 ...
数据缺失值补全方法 sklearn.impute.SimpleImputer 使用实例
sdnuwjw的博客
09-09 2万+
一、 二、方法 官方说明：https://scikit-learn.org/stable/modules/generated/sklearn.impute.SimpleImputer.html 三、实例 1、数据 - 缺失值 - 数据 # 中间部分数据存在缺失值 >>> import numpy as np >>> from sklearn.impute im...
查询MySQL数据库中表结构的几种方法
热门推荐
菜鸟Li
12-28 12万+
什么是表结构？ 表结构就是定义数据表文件名，确定数据表包含哪些字段，各字段的字段名、字段类型、及宽度，并将这些数据输入到计算机当中。查询方法：
MySql常见数据类型
芒种、的博客
07-08 112
一、常见的数据类型 数值型：整型、小数（定点数、浮点数） 字符型：较短的文本（char、varchar）、较长的文本（text、blob(较长的二进制数据)）; 日期型： 1. 整型 tinyint 、 smallint 、 mediumint 、 int/integer 、 bigint 1(字节) 2(字节) 3(字节) 4(字节) 8(字节) 特点： ① 如果不设置无符号还是有符号,默认是有符号,如果设置无符号,需要添加unsigned ② 如果插入的数值超出了整型的返回，会直接报错（
解决问题：mysql存boolean数据
Ezrealmore
03-18 1万+
mysql数据库不提供boolean类型的数据存储，但是可以用tinyint代替，改该字段对应的javabean的那个变量定义为boolean类型即可，当存入true时，自动转换为1，false为0，取的时候也一样。
MySQL基础命令详解：数据类型与DDL/DML/DQL操作
本篇笔记主要涵盖了MySQL的基础命令，包括数据类型、数据定义语言（DDL）、数据操作语言（DML）以及数据查询语言（DQL）。 ### MySQL数据类型 MySQL的数据类型大致可以分为三类：数值型、字符型和日期型。以下是...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值