mysql 表数据操作

╰半橙微兮° 2024-02-19 12:20 106阅读 0赞

INSERT INTO USER
    VALUES(10,'root','root','dsff@fgfdf.com');
    
    
    INSERT INTO USER(username,password,email)
    VALUES('admin','123','agdfg@agf.com');
    
    #插入查询出来的数据
    INSERT INTO USER(username)
    SELECT NAME 
    FROM account;
    
    UPDATE USER
    SET username='robot',PASSWORD='robot'
    WHERE username = 'root'
    
    #删除表中的指定的数据
    DELETE FROM USER
    WHERE  username ='robot';
    
    #--清空表中的数据
    TRUNCATE TABLE USER
    
    #查询单列
    SELECT username
    FROM USER;
    
    #查询多列
    SELECT username,PASSWORD
    FROM user;
    
    
    #查询所有列
    SELECT * 
    from USER;
    
    #按id降序排序
    SELECT * 
    from USER ORDER BY id DESC;
    
    #查询不同的值
    #DISTINCT 用于返回唯一不同的值。它作用于所有列，也就是说所有列的值都相同才算相同。
    
    SELECT DISTINCT  username
    FROM USER
    
    -- 返回前3行
    SELECT * FROM USER LIMIT 3;
    SELECT * FROM USER LIMIT 0,3;
    
    -- 返回第3-5行
    SELECT * FROM USER LIMIT 2,3;
    
    -- 返回3-6行
    SELECT * FROM USER LIMIT 2,4;
    
    
    
    -- WHERE 条件查询
    SELECT * FROM USER
    WHERE username = 'admin';
    
    UPDATE USER
    SET PASSWORD = '123'
    WHERE username = 'root';
    
    DELETE FROM USER
    WHERE username = 'sadfa2';
    
    
    
    --  IN 和 BETWEEN 限定范围
    /*
    IN 操作符在 WHERE 子句中使用，作用是在指定的几个特定值中任选一个值。
    BETWEEN 操作符在 WHERE 子句中使用，作用是选取介于某个范围内的值
    */
    
    -- 查询id=11 和13的数据
    SELECT * from USER
    WHERE id  in ('11','13')
    
    -- 查询10到13的数据
    SELECT * FROM USER
    WHERE id BETWEEN 10 AND 13;
    
    
    -- -----------------------------------
    -- -----------------------------------
    CREATE TABLE `products` (
      `prod_id` int(10) NOT NULL,
      `prod_name` varchar(255) NOT NULL,
      `prod_price` decimal(10,0) NOT NULL,
    	`vend_id`  varchar(255) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    -- AND、OR、NOT是用于对过滤条件的逻辑处理
    
    SELECT prod_id, prod_name, prod_price
    FROM products
    WHERE vend_id  = 'DLL01' AND prod_price <= 200;
    
    
    
    SELECT prod_id, prod_name, prod_price
    FROM products
    WHERE vend_id = 'DLL01' OR vend_id = 'BRS01';
    
    
    SELECT *
    FROM products
    WHERE prod_price NOT BETWEEN 100 AND 200;
    
    -- -------------------------------
    -- ---------------------------------
    
    -- LIKE 模糊查询
    
    -- 通配符 %  表示任何字符出现任意次数。
    -- 通配符 _   表示任何字符出现一次
    SELECT prod_id, prod_name, prod_price
    FROM products
    WHERE prod_name LIKE '%dfg%';
    
    
    
    SELECT prod_id, prod_name, prod_price
    FROM products
    WHERE prod_name LIKE '_fgash';