MySql数据操作

MySql数据操作

插入记录 INSERT [INTO] tb_name（col_name,...) {VALUE|VALUES}(VALUES...);

VALUE和VALUES的执行效率是一样的

• 不指定字段名称 INSERT tb_name VALUE(value); 按照建表时的顺序给每一条记录赋值

• 列出指定字段 INSERT tb_name (字段名称) VALUES（值); 字段名称和值一一对应；

• 一次添加多条记录 Insert tb_name [(字段名称,...)] Values(值...),（值...),...;

• INSERT ... SET的形式 INSERT tb_name SET 字段名称 = 值 ,...;

• INSERT ... SELECT的形式 `INSERT tb_name (字段名称,...) SELECT 字段名称,...FROM TB_NAME[WHERE 条件];

    -- 测试数据添加记录
    CREATE DATABASE IF NOT EXISTS king DEFAULT CHARACTER SET 'UTF8';

    USE king;

    CREATE TABLE IF NOT EXISTS user(
      id INT UNSIGNED AUTO_INCREMENT KEY COMMENT '编号',
      username VARCHAR(20) NOT NULL UNIQUE COMMENT '用户名',
      age TINYINT UNSIGNED DEFAULT 18 COMMENT '年龄',
      email VARCHAR(50) NOT NULL DEFAULT 'iMist@qq.com' UNIQUE COMMENT '邮箱'

    )ENGINE = INNODB CHARACTER SET = UTF8;

    -- 不指定字段名称
    INSERT user VALUE (1,'king',23,'az460024285@qq.com');

    INSERT user VALUES (null ,'queue','20','az45152@qq.com');-- 这里主键id允许为null,填null就代表自增

    -- 列出指定字段的形式来插入数据
    INSERT user (username, email) VALUES ('rose','az5412@gmail.com'); -- 字段和值一一对应，id自增，age默认；

    -- 一次添加多条记录
    INSERT user(id,username,age,email) VALUES (4,'jony',16,'aa.@qq.com'),(10,'jerry',3,'asfaf@163.com');

    -- INSERT ... SET的形式
    INSERT user SET id = 11,username = 'Tom',age = 18,email='sjf@gmail.com';

    -- INSERT ... SELECT的形式
    CREATE TABLE test(
      a VARCHAR(20)
    );
    INSERT test VALUES ('AA'),('BB'),('CC');

    INSERT user(username) SELECT a FROM test;-- 将从test表中查询出的字段和 user表中的字段一一对应进行插入

    ALTER TABLE user DROP INDEX `email`;-- 这里建表的时候出现了错误,email唯一索引的时候邮箱默认值无效

    SELECT * FROM user;

修改记录

• UPDATE tb_name SET 字段名称 = 值,字段名称 = 值,...[WHERE 条件]

    -- 测试更新/修改数据

    -- 修改id为1的记录年龄
    UPDATE user SET age = 29 WHERE id = 1;
    -- 修改id为3的 username ,age,email字段记录
    UPDATE user SET username = 'xiaohua',age= 23,email = 'iMist@qq.com' WHERE id = 3;
    -- 将表中所有的字段年龄加上10
    UPDATE user SET age = age+10 ;
    -- 将表中id < 5 的用户年龄减20，邮箱改为默认值
    UPDATE user SET age = age - 20 ,email = DEFAULT  WHERE id <= 5;

    SELECT * FROM user;

删除记录

• DELETE FROM tb_name [WHERE 条件];, 如果不添加条件，那么表中所有的数据都会被删除

   -- 测试删除记录
    -- 删除用户名为king的记录
    DELETE FROM user WHERE username = 'king';

    -- 删除年龄为28 的用户
    DELETE FROM user WHERE age = 28;
    -- 删除表中的所有记录
    DELETE FROM user ;
    -- 重置编号
    ALTER TABLE user AUTO_INCREMENT = 1;

    SELECT * FROM user;

> 说明：1.  直接用DELETE语句无法重置编号，下次插入数据会从上次最后一条记录的编号开始（`SHOW CREATE TABLE tb_name` 查看 ）

2. 使用ALTER TABLE tb_name AUTO_INCREMENT = 1 来重置编号或者指定编号的起始位置

查询记录

• 基础查询语句

 -- 建表
 CREATE TABLE IF NOT EXISTS user1(
   id INT UNSIGNED AUTO_INCREMENT KEY COMMENT '编号',
   username VARCHAR(20) NOT NULL UNIQUE COMMENT '用户名',
   age TINYINT UNSIGNED DEFAULT 18 COMMENT '年龄',
   sex ENUM('男','女','保密') NOT NULL DEFAULT '保密' COMMENT '性别',
   addr VARCHAR(20) NOT NULL DEFAULT '北京',
   married TINYINT(1) NOT NULL DEFAULT 0 COMMENT '0代表未婚 1代表已婚',
   salarry FLOAT(8,2) NOT NULL DEFAULT 0 COMMENT '薪水'
   )ENGINE = INNODB CHARACTER SET = UTF8;

 INSERT user1 VALUES (1,'king',23,'男','北京',1,50000);
 INSERT user1 VALUES (NULL ,'王五',5,'男','广州',0,3000),
   (NULL ,'zhangsan',12,'男','上海',0,15000),
   (NULL ,'lisi',75,'男','上海',0,500000),
   (NULL ,'wangwu',65,'男','汉川',0,2000),
   (NULL ,'iMist',20,'男','上海',0,15000),
   (NULL ,'xiaoqiang',38,'男','上海',0,5000),
   (NULL ,'dagang',20,'男','北京',0,1500),
   (NULL ,'qiangqiang',38,'男','深圳',0,20000),
   (NULL ,'huahua',23,'女','武汉',0,20000);
 -- 这里只要有一条数据问题就会导致所有数据都无法插入，并且插入失败的数据会占用编号,导致编号跳跃

 SELECT * FROM user1;

        SELECT select_expr ,... FROM tb_name 
        [WHERE 条件]
        [GROUP BY {col_name|position} HAVING二次筛选]
        [ORDER BY {col_name|position|epre}[ASC|DESC]]
        [LIMIT 限制结果集的显示条数]

• 查询表中的所有记录 SELECT * FROM tb_name;

• 查询指定字段的信息 SELECT 字段名称,... FROM tb_name;

• 指定数据库下的指定表 SELECT 字段名称,... FROM db_name.tb_name;
  这种方式可以不用打开数据库

• 给字段起别名 SELECT 字段名称 [AS] 别名名称,... FROM db_name.tb_name; 当不指定数据库的时候默认就是当前数据库

• 给数据表起别名 SELECT 字段名称 ,... FROM tb_name [AS] 表别名;
  一般表别名不超过三个字母,主要用于多表联查;

• 表名.字段名的形式 SELECT TB_NAME.COL_NAME,... FROM tb_name;
  一般用于多表联查

  SELECT * FROM user1;
  -- 查询指定字段
  SELECT username,age,addr FROM user1;
  
  -- 查询king数据库下的user1表的所有记录，可以不同打开数据库就查询到数据表中的数据
  SELECT * FROM king.user1;
  
  -- 查询user1表中的id 编号 username 用户名 sex 性别;
  SELECT id AS '编号' ,username AS '用户名' ,sex AS '性别' FROM user1;
  
  -- 给user1表起别名
  SELECT id,username FROM user1 AS u ;
  
  -- 测试表名.字段名的形式
  SELECT user1.id,user1.username FROM user1;