MySQL单表操作

一、新增表

  • 1、新建一个数据库

    mysql> create database mysqltest charset utf8;
    Query OK, 1 row affected (0.00 sec)
    
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | images360          |
    | mysql              |
    | mysqltest          |
    | nodejs             |
    | performance_schema |
    | sys                |
    +--------------------+
    7 rows in set (0.01 sec)
    
    mysql> use mysqltest;
    Database changed
    mysql>
    
  • 2、新建数据表

    mysql> create table student( 
        -> id int primary key auto_increment comment "学生id",
        -> name varchar(100) comment "学生名字",
        -> age int comment "学生年龄"
        -> );
    Query OK, 0 rows affected (0.03 sec)
    
    mysql> show tables;
    +---------------------+
    | Tables_in_mysqltest |
    +---------------------+
    | student             |
    +---------------------+
    1 row in set (0.00 sec)
    

二、基本表结构操作

  • 1、几个基本的词
    • comment字段的描素
    • primary key主键
    • auto_increment自动增长
  • 2、显示表结构show create table 表名

    mysql> show create table student;
    +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Table   | Create Table                                                                                                                                                                                                                                                  |
    +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | student | CREATE TABLE `student` (
      `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学生id',
      `name` varchar(100) DEFAULT NULL COMMENT '学生名字',
      `age` int(11) DEFAULT NULL COMMENT '学生年龄',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8           |
    +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.01 sec)
    
    mysql>
    
  • 3、修改表名alter table 原来表名 rename to 新表名;

    mysql> show tables;
    +---------------------+
    | Tables_in_mysqltest |
    +---------------------+
    | student             |
    +---------------------+
    1 row in set (0.01 sec)
    
    mysql> alter table student rename to student1;
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> show tables;
    +---------------------+
    | Tables_in_mysqltest |
    +---------------------+
    | student1            |
    +---------------------+
    1 row in set (0.00 sec)
    
    mysql>
    
  • 4、查看表结构desc student1;

    mysql> desc student1;
    +-------+--------------+------+-----+---------+----------------+
    | Field | Type         | Null | Key | Default | Extra          |
    +-------+--------------+------+-----+---------+----------------+
    | id    | int(11)      | NO   | PRI | NULL    | auto_increment |
    | name  | varchar(100) | YES  |     | NULL    |                |
    | age   | int(11)      | YES  |     | NULL    |                |
    +-------+--------------+------+-----+---------+----------------+
    3 rows in set (0.00 sec)
    
    mysql>
    
  • 5、修改表字段类型alter table 表名 modify age 新的数据类型;

    mysql> desc student1;
    +-------+-------------+------+-----+---------+----------------+
    | Field | Type        | Null | Key | Default | Extra          |
    +-------+-------------+------+-----+---------+----------------+
    | id    | int(11)     | NO   | PRI | NULL    | auto_increment |
    | name  | varchar(10) | YES  |     | NULL    |                |
    | age   | int(11)     | YES  |     | NULL    |                |
    +-------+-------------+------+-----+---------+----------------+
    3 rows in set (0.00 sec)
    
    mysql> alter table student1 modify age varchar(10);
    Query OK, 0 rows affected (0.02 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql> desc student1;
    +-------+-------------+------+-----+---------+----------------+
    | Field | Type        | Null | Key | Default | Extra          |
    +-------+-------------+------+-----+---------+----------------+
    | id    | int(11)     | NO   | PRI | NULL    | auto_increment |
    | name  | varchar(10) | YES  |     | NULL    |                |
    | age   | varchar(10) | YES  |     | NULL    |                |
    +-------+-------------+------+-----+---------+----------------+
    3 rows in set (0.01 sec)
    
    mysql>
    
  • 6、修改表字段名alter table 表明 change 旧的列名 新的列名 数据类型;

    mysql> desc student1;
    +-------+-------------+------+-----+---------+----------------+
    | Field | Type        | Null | Key | Default | Extra          |
    +-------+-------------+------+-----+---------+----------------+
    | id    | int(11)     | NO   | PRI | NULL    | auto_increment |
    | name  | varchar(10) | YES  |     | NULL    |                |
    | age   | varchar(10) | YES  |     | NULL    |                |
    +-------+-------------+------+-----+---------+----------------+
    3 rows in set (0.01 sec)
    
    mysql> alter table student1 change name username varchar(100);
    Query OK, 0 rows affected (0.02 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql> desc student1;
    +----------+--------------+------+-----+---------+----------------+
    | Field    | Type         | Null | Key | Default | Extra          |
    +----------+--------------+------+-----+---------+----------------+
    | id       | int(11)      | NO   | PRI | NULL    | auto_increment |
    | username | varchar(100) | YES  |     | NULL    |                |
    | age      | varchar(10)  | YES  |     | NULL    |                |
    +----------+--------------+------+-----+---------+----------------+
    3 rows in set (0.00 sec)
    
    mysql>
    
  • 7、新增列alter table 表名 add 新增的列名 新增列名数据类型;

    mysql> alter table student1 add birthday datetime;
    Query OK, 0 rows affected (0.02 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql> desc student1;
    +----------+--------------+------+-----+---------+----------------+
    | Field    | Type         | Null | Key | Default | Extra          |
    +----------+--------------+------+-----+---------+----------------+
    | id       | int(11)      | NO   | PRI | NULL    | auto_increment |
    | username | varchar(100) | YES  |     | NULL    |                |
    | age      | varchar(10)  | YES  |     | NULL    |                |
    | birthday | datetime     | YES  |     | NULL    |                |
    +----------+--------------+------+-----+---------+----------------+
    4 rows in set (0.00 sec)
    
    mysql>
    
  • 8、删除列alter table 表名 drop 要删除的列名;

    mysql> alter table student1 drop birthday;
    Query OK, 0 rows affected (0.02 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql> desc student1;
    +----------+--------------+------+-----+---------+----------------+
    | Field    | Type         | Null | Key | Default | Extra          |
    +----------+--------------+------+-----+---------+----------------+
    | id       | int(11)      | NO   | PRI | NULL    | auto_increment |
    | username | varchar(100) | YES  |     | NULL    |                |
    | age      | varchar(10)  | YES  |     | NULL    |                |
    +----------+--------------+------+-----+---------+----------------+
    3 rows in set (0.00 sec)
    
    mysql>
    

三、基本的增、删、改

  • 1、插入数据insert into 表名(字段名1, 字段名2...) values(值1, 值2....), (值1, 值2....)

    mysql> insert into student1(username, age) values("张三", 20);
    Query OK, 1 row affected (0.00 sec)
    
    mysql> insert into student1(username, age) values("李四", 10), ("王五", 30);
    Query OK, 2 rows affected (0.00 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    
    mysql>
    
  • 2、修改数据update 表名 set 列名1 = 新值1, 列名2 = 新值2 where 条件 = 条件值;

    update student set username = "哈哈" where id = 1;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> select * from student;
    +----+----------+------+
    | id | username | age  |
    +----+----------+------+
    |  1 | 哈哈     |   20 |
    |  2 | 李四     |   10 |
    |  3 | 王五     |   30 |
    +----+----------+------+
    3 rows in set (0.00 sec)
    
    mysql>
    
  • 3、删除数据delete from 表名 where 条件 = 条件值1;

    delete from student where id = 1;
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select * from student;
    +----+----------+------+
    | id | username | age  |
    +----+----------+------+
    |  2 | 李四     |   10 |
    |  3 | 王五     |   30 |
    +----+----------+------+
    2 rows in set (0.00 sec)
    
    mysql>
    

四、查询语句

  • 1、查询数据

    mysql> select * from student1;
    +----+----------+------+
    | id | username | age  |
    +----+----------+------+
    |  1 | 张三     |   20 |
    |  2 | 李四     |   10 |
    |  3 | 王五     |   30 |
    +----+----------+------+
    3 rows in set (0.00 sec)
    mysql> select username, age from student1;
    +----------+------+
    | username | age  |
    +----------+------+
    | 张三     |   20 |
    | 李四     |   10 |
    | 王五     |   30 |
    +----------+------+
    3 rows in set (0.00 sec)
    
    mysql> select username name, age from student1;
    +--------+------+
    | name   | age  |
    +--------+------+
    | 张三   |   20 |
    | 李四   |   10 |
    | 王五   |   30 |
    +--------+------+
    3 rows in set (0.00 sec)
    
    mysql>
    
  • 2、排序查询select * from 表名 order by 字段名 desc/asc

    mysql> select * from student1 order by age desc;
    +----+----------+------+
    | id | username | age  |
    +----+----------+------+
    |  3 | 王五     |   30 |
    |  1 | 张三     |   20 |
    |  2 | 李四     |   10 |
    +----+----------+------+
    3 rows in set (0.00 sec)
    
    mysql> select * from student1 order by age asc;
    +----+----------+------+
    | id | username | age  |
    +----+----------+------+
    |  2 | 李四     |   10 |
    |  1 | 张三     |   20 |
    |  3 | 王五     |   30 |
    +----+----------+------+
    3 rows in set (0.01 sec)
    
    mysql>
    
  • 3、分页查询select * from 表名 limit 开始位置, 取几条数据

    mysql> select * from student limit 0, 2;
    +----+----------+------+
    | id | username | age  |
    +----+----------+------+
    |  1 | 张三     |   20 |
    |  2 | 李四     |   10 |
    +----+----------+------+
    2 rows in set (0.00 sec)
    
    mysql>
    
  • 4、定义别名查询(可以定义字段及表)

    mysql> select username as "名字", age as "年龄" from student;
    +--------+--------+
    | 名字   | 年龄   |
    +--------+--------+
    | 李四   |     10 |
    | 王五   |     30 |
    +--------+--------+
    2 rows in set (0.01 sec)
    
    mysql> select username "年龄", age "年龄" from student;
    +--------+--------+
    | 年龄   | 年龄   |
    +--------+--------+
    | 李四   |     10 |
    | 王五   |     30 |
    +--------+--------+
    2 rows in set (0.00 sec)
    
    mysql>
    
  • 5、字段可以运算

    mysql> select username, age + 10 from student;
    +----------+----------+
    | username | age + 10 |
    +----------+----------+
    | 李四     |       20 |
    | 王五     |       40 |
    +----------+----------+
    2 rows in set (0.00 sec)
    
    mysql>
    
  • 6、where的条件查询

    mysql> select id,username,age from student where id = 2;
    +----+----------+------+
    | id | username | age  |
    +----+----------+------+
    |  2 | 李四     |   10 |
    +----+----------+------+
    1 row in set (0.01 sec)
    
    mysql> select id,username,age from student where age < 20;
    +----+----------+------+
    | id | username | age  |
    +----+----------+------+
    |  2 | 李四     |   10 |
    |  4 | 张三     |   18 |
    +----+----------+------+
    2 rows in set (0.00 sec)
    
    mysql>
    
  • 7、聚合函数的使用

    • 1.新建数据

      mysql> create table student(
          ->  id int primary key auto_increment comment "用户id",
          ->  name varchar(100) not null unique comment "名字",
          ->  city varchar(100) not null comment "城市",
          ->  age int not null comment "年龄",
          ->  create_at timestamp default current_timestamp comment "创建时间"
          ->  );
      Query OK, 0 rows affected (0.02 sec)
      
      mysql> insert into student(name, city, age) values ("张三", "广东", 10), ("李四", "广西", 22), ("王五", "广东", 25), ("马六", "湖南", 40);
      Query OK, 4 rows affected (0.00 sec)
      Records: 4  Duplicates: 0  Warnings: 0
      
      mysql> select * from student;
      +----+--------+--------+-----+---------------------+
      | id | name   | city   | age | create_at           |
      +----+--------+--------+-----+---------------------+
      |  1 | 张三   | 广东   |  10 | 2018-06-29 14:31:08 |
      |  2 | 李四   | 广西   |  22 | 2018-06-29 14:31:08 |
      |  3 | 王五   | 广东   |  25 | 2018-06-29 14:31:08 |
      |  4 | 马六   | 湖南   |  40 | 2018-06-29 14:31:08 |
      +----+--------+--------+-----+---------------------+
      4 rows in set (0.00 sec)
      
      mysql>
      
    • 2.count(列名)计数select count(*) from 表名;

      mysql> select count(name) from student;
      +-------------+
      | count(name) |
      +-------------+
      |           4 |
      +-------------+
      1 row in set (0.00 sec)
      
      mysql>
      
    • 3.sum(列名)总共select sum(age) as 年龄总共 from 表名;

      mysql> select sum(age) as 年龄总共 from student;
      +--------------+
      | 年龄总共     |
      +--------------+
      |           97 |
      +--------------+
      1 row in set (0.01 sec)
      
      mysql>
      
    • 4.avg(列名)求平均

      mysql> select avg(age) as 平均年龄 from student;
      +--------------+
      | 平均年龄     |
      +--------------+
      |      24.2500 |
      +--------------+
      1 row in set (0.00 sec)
      
      mysql>
      
    • 5.max(列名)求最大值

      mysql> select max(age) from student;
      +----------+
      | max(age) |
      +----------+
      |       40 |
      +----------+
      1 row in set (0.01 sec)
      
      mysql>
      
    • 6.min(列名)求最小值

      mysql> select min(age) from student;
      +----------+
      | min(age) |
      +----------+
      |       10 |
      +----------+
      1 row in set (0.00 sec)
      
      mysql>
      
  • 8、使用正则查询

    mysql> select * from student where city regexp "^广";
    +----+--------+--------+-----+---------------------+
    | id | name   | city   | age | create_at           |
    +----+--------+--------+-----+---------------------+
    |  1 | 张三   | 广东   |  10 | 2018-06-29 14:31:08 |
    |  2 | 李四   | 广西   |  22 | 2018-06-29 14:31:08 |
    |  3 | 王五   | 广东   |  25 | 2018-06-29 14:31:08 |
    +----+--------+--------+-----+---------------------+
    3 rows in set (0.00 sec)
    
    mysql>
    

五、having的使用

  • 1、使用where过滤数据会报错

    mysql> select id, name, age as 年龄 from student where 年龄 > 30;
    ERROR 1054 (42S22): Unknown column '年龄' in 'where clause'
    mysql>
    
  • 2、使用having替换where

    mysql> select id, name, age as 年龄 from student having 年龄 > 30;
    +----+--------+--------+
    | id | name   | 年龄   |
    +----+--------+--------+
    |  4 | 马六   |     40 |
    +----+--------+--------+
    1 row in set (0.00 sec)
    
    mysql>
    

六、distinct去重查询

  • 1、数据表数据

    mysql> select name from student;
    +--------+
    | name   |
    +--------+
    | 张三   |
    | 李四   |
    | 王五   |
    | 马六   |
    | 张三   |
    +--------+
    5 rows in set (0.00 sec)
    
    mysql>
    
  • 2、使用去重查询

    mysql> select distinct name from student;
    +--------+
    | name   |
    +--------+
    | 张三   |
    | 李四   |
    | 王五   |
    | 马六   |
    +--------+
    4 rows in set (0.00 sec)
    
    mysql>
    

results matching ""

    No results matching ""