MySQL 表结构(添加、修改、查询) 详解

文章目录

  • 表结构
    • 1,添加字段
      • 添加新字段
      • 添加到指定字段下
      • 添加的字段放第一个
    • 2,修改字段和类型
      • 修改名称、数据类型、类型
      • 修改字段类型、约束、顺序
      • 删除字段
    • 3,插入数据(添加记录)
      • 添加一条记录
      • 添加多条记录
      • 使用 set 添加记录
      • 更新修改记录
      • 删除记录
    • 4,单表查询
      • 语法
      • 简单查询
      • 多字段查询
      • 有条件查询:where
      • 设置别名:as
      • 统计记录数量:count()
      • 统计字段得到的数量
      • 避免重复 DISTINCT:
    • 表复制
      • 复制表结构+记录
      • 复制单个字段和内容
      • 多条件查询:and(和)
      • 多条件查询:or(或者)
      • 筛选关键字:BETWEEN AND(什么和什么之间)
      • 筛选关键字:IS NULL(空的)
      • NULL说明
      • 筛选关键字:IN(集合查询)
      • 排序查询:ORDER BY
      • 限制查询:LIMIT
      • 分组查询:GROUP BY
      • GROUP BY 和 GROUP_CONCAT()(组连接)函数一起使用
      • 函数
    • 5,多表查询
      • 概念
      • 内连接
        • 准备实验环境
      • 内连接查询:只显示表中有匹配的数据
      • 外连接查询
        • 左外连接(左键)
        • 右外连接(右键)

表结构

1,添加字段

添加新字段

alter table 表名 add 字段 类型;

mysql> alter table t3  add math int(10);
    \\添加字段
    
mysql> alter table t3  add (chinese int(10),english int(10));
    \\添加多个字段,中间用逗号隔开

添加到指定字段下

alter table 表名 add 添加的字段(和类型) after name;
    \\把添加的字段放到name后面

mysql> alter table t9 add id char(10) after home;
  Query OK, 0 rows affected (0.04 sec)
  Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t9;
  +----------+---------------+------+-----+---------+-------+
  | Field    | Type          | Null | Key | Default | Extra |
  +----------+---------------+------+-----+---------+-------+
  | name     | char(10)      | YES  |     | NULL    |       |
  | hostname | char(10)      | NO   |     | NULL    |       |
  | ip       | char(20)      | YES  |     | NULL    |       |
  | math     | int(3)        | YES  |     | NULL    |       |
  | chinese  | int(10)       | YES  |     | NULL    |       |
  | english  | int(10)       | YES  |     | NULL    |       |
  | home     | char(10)      | YES  |     | NULL    |       |
  | id       | char(10)      | YES  |     | NULL    |       |
  | sex      | enum('w','m') | YES  |     | NULL    |       |
  +----------+---------------+------+-----+---------+-------+
  9 rows in set (0.00 sec)

添加的字段放第一个

alter table 表名 add 添加的字段(和类型) first;
    \\把添加的字段放在第一个

mysql> alter table t9 add name char(10) first;
  Query OK, 0 rows affected (0.04 sec)
  Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t9;
  +----------+---------------+------+-----+---------+-------+
  | Field    | Type          | Null | Key | Default | Extra |
  +----------+---------------+------+-----+---------+-------+
  | name     | char(10)      | YES  |     | NULL    |       |
  | hostname | char(10)      | NO   |     | NULL    |       |
  | ip       | char(20)      | YES  |     | NULL    |       |
  | math     | int(3)        | YES  |     | NULL    |       |

2,修改字段和类型

修改名称、数据类型、类型

alter table 表名 change 旧字段 新字段 类型;
  \\change 修改字段名称,类型,约束,顺序 

mysql> alter table t3 change max maxs int(15) after id;
  \\修改字段名称与修饰并更换了位置

修改字段类型、约束、顺序

alter table 表名 modify 字段 类型;
  \\modify 不能修改字段名称

mysql> alter table t3 modify maxs int(20) after math;
  \\修改类型并更换位置

删除字段

alter table 表名 drop 字段名;
  \\drop 丢弃的字段。
mysql> alter table t3 drop maxs;
  \\删除maxs 字段

3,插入数据(添加记录)

mysql> create table t3(id int, name varchar(20), sex enum('m','f'), age int);
  \\字符串必须引号引起来
  \\记录与表头相对应,表头与字段用逗号隔开。

添加一条记录

insert into 表名(字段1,字段2,字段3,字段4) values(1,"tom","m",90);

mysql> insert into t3(id,name,sex,age) values(1,"tom","m",18);
  Query OK, 1 row affected (0.00 sec)

注:添加的记录与表头要对应,

添加多条记录

mysql> insert into t3(id,name,sex,age) values(2,"jack","m",19),(3,"xiaoli","f",20);
  Query OK, 2 rows affected (0.34 sec)

使用 set 添加记录

mysql> insert into t3 set id=4,name="zhangsan",sex="m",age=21;
Query OK, 1 row affected (0.00 sec)

更新修改记录

update 表名 set  修改的字段  where  给谁修改;

mysql> update t3 set id=6 where name="xiaoli";

删除记录

删除单条记录

delete from 表名 where 字段=记录;

mysql> delete from t3 where id=6;
  \\删除那个记录,等于删除那个整条记录

删除所有记录

delete from 表名;

mysql> delete from t3;

4,单表查询

语法

select   字段名称,字段名称2    from  表名   条件

简单查询

mysql> select * from employee5;
  *  代表所有的意思

多字段查询

mysql> select id,name,sex from employee5;

有条件查询:where

mysql> select id,name from employee5 where id<=3;

mysql> select id,name,salary from employee5 where salary>2000;

设置别名:as

mysql> select id,name,salary as "salry_num" from employee5 where salary>5000;
  \\给 salary 的值起个别名,显示值的表头会是设置的别名

统计记录数量:count()

mysql> select count(*) from employee5;

统计字段得到的数量

mysql> select count(id) from employee5;

避免重复 DISTINCT:

表里的数据有相同的

select distinct 字段 from 表名;
  
mysql> select distinct post from employee5;


表复制

复制表结构+记录

key不会复制: 主键、外键和索引

create table 新表 select * from 旧表;

mysql> create table new_t1 select * from employee5;

复制单个字段和内容

mysql> create table new_t2(select id,name from employee5);

多条件查询:and(和)

select   字段,字段2 from   表名   where   条件 and where 条件;

mysql> SELECT name,salary from employee5 where post='hr' AND salary>1000;

mysql> SELECT name,salary from employee5 where post='instructor' AND salary>1000;

多条件查询:or(或者)

select   字段,字段2 from   表名   where   条件   or   条件;

mysql> select name from employee5 where salary>5000 and salary<10000 or dep_id=102;

mysql> select name from employee5 where salary>2000 and salary<6000 or dep_id=100;

筛选关键字:BETWEEN AND(什么和什么之间)

mysql> SELECT name,salary FROM employee5 WHERE salary BETWEEN 5000 AND 15000;

mysql> SELECT name,salary FROM employee5 WHERE salary NOT BETWEEN 5000 AND 15000;

mysql> select name,dep_id,salary from employee5 where  not salary>5000;
  \\注:not  给条件取反

筛选关键字:IS NULL(空的)

mysql> SELECT name,job_description FROM employee5 WHERE job_description IS NULL;

mysql> SELECT name,job_description FROM employee5  WHERE job_description IS NOT NULL;
  \\注:not  给条件取反  不是null的

mysql> SELECT name,job_description FROM employee5 WHERE job_description=''; 
  \\什么都没有==空

NULL说明

  • 1、等价于没有任何值、是未知数。
  • 2、NULL与0、空字符串、空格都不同,NULL没有分配存储空间。
  • 3、对空值做加、减、乘、除等运算操作,结果仍为空。
  • 4、比较时使用关键字用“is null”和“is not null”。
  • 5、排序时比其他数据都小(索引默认是降序排列,大→小),所以NULL值总是排在最后面。

筛选关键字:IN(集合查询)

一般查询

mysql> SELECT name,salary FROM employee5 WHERE salary=4000 OR salary=5000 OR salary=6000 OR salary=9000;

IN集合查询

mysql> SELECT name, salary FROM employee5 WHERE salary IN (4000,5000,6000,9000);

mysql> SELECT name, salary FROM employee5 WHERE salary NOT IN (4000,5000,6000,9000);
  \\注:not  给条件取反 

排序查询:ORDER BY

order by :指令,在mysql是排序的意思。

mysql> select name,salary from employee5 order by salary;
  \\默认从小到大排序。

mysql> select name,salary from employee5 order by salary desc;
  \\降序,从大到小

限制查询:LIMIT

mysql> select * from employee5 limit 5;
  \\只显示前5行

mysql> select name,salary from employee5 order by salary desc limit 0,1;
  \\从第几行开始,打印一行

mysql> SELECT * FROM employee5 ORDER BY salary DESC LIMIT 0,5;
  \\降序,打印5行

mysql> SELECT * FROM employee5 ORDER BY salary DESC LIMIT 4,5;
  \\从第5条开始,共显示5条

mysql> SELECT * FROM employee5 ORDER BY salary  LIMIT 4,3;
  \\默认从第5条开始显示3条。

查找什么内容从那张表里面降序排序只打印第二行。

注意:

  • 0-------默认第一行
  • 1------第二行 依次类推…

分组查询:GROUP BY

mysql> select count(name),post from employee5 group by post;
  +-------------+------------+
  | count(name) | post       |
  +-------------+------------+
  |           2 | hr         |
  |           4 | instructor |
  |           4 | sale       |
  +-------------+------------+
 count可以计算字段里面有多少条记录,如果分组会分组做计算

mysql> select count(name),group_concat(name) from employee5 where salary>5000;
查找 统计(条件:工资大于5000)的有几个人(count(name)),分别是谁(group_concat(name))
  +-------------+----------------------------+
  | count(name) | group_concat(name)         |
  +-------------+----------------------------+
  |           5 | tom,robin,alice,harry,emma |
  +-------------+----------------------------+

GROUP BY 和 GROUP_CONCAT()(组连接)函数一起使用

mysql> SELECT dep_id,GROUP_CONCAT(name) FROM employee5 GROUP BY dep_id;
  \\以dep_id分的组,dep_id这个组里面都有谁

mysql> SELECT dep_id,GROUP_CONCAT(name) as emp_members FROM employee5 GROUP BY dep_id;
  \\给组连接设置了一个别名

函数

max() 最大值

mysql> select max(salary) from employee5;
查询薪水最高的人的详细信息:

mysql> select name,sex,hire_date,post,salary,dep_id from employee5 where salary = (SELECT MAX(salary) from employee5);

min()最小值

select min(salary) from employee5;

avg()平均值

select avg(salary) from employee5;

now() 现在的时间

select now();

sum() 计算和

select sum(salary) from employee5 where post='sale';

5,多表查询

概念

当在查询时,所需要的数据不在一张表中,可能在两张表或多张表中。此时需要同时操作这些表。即关联查询。

内连接

在做多张表查询时,这些表中应该存在着有关联的两个字段,组合成一条记录。只连接匹配到的行

准备实验环境

准备两张表(表一)

mysql> create database company;

mysql> use  company;

mysql> create table employee6( emp_id int auto_increment primary key not null, emp_name varchar(50), age int, dept_id int);
  Query OK, 0 rows affected (0.00 sec)

mysql> desc employee6;
  +----------+-------------+------+-----+---------+----------------+
  | Field    | Type        | Null | Key | Default | Extra          |
  +----------+-------------+------+-----+---------+----------------+
  | emp_id   | int(11)     | NO   | PRI | NULL    | auto_increment |
  | emp_name | varchar(50) | YES  |     | NULL    |                |
  | age      | int(11)     | YES  |     | NULL    |                |
  | dept_id  | int(11)     | YES  |     | NULL    |                |
  +----------+-------------+------+-----+---------+----------------+
  4 rows in set (0.08 sec)

插入数据

mysql> insert into employee6(emp_name,age,dept_id) values('xiaoli',19,200),('tom',26,201),('jack',30,201),('alice',24,202),('robin',40,200),('zhangsan',28,204);
  Query OK, 6 rows affected (0.00 sec)
  Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from employee6;
  +--------+----------+------+---------+
  | emp_id | emp_name | age  | dept_id |
  +--------+----------+------+---------+
  |      1 | xiaoli   |   19 |     200 |
  |      2 | tom      |   26 |     201 |
  |      3 | jack     |   30 |     201 |
  |      4 | alice    |   24 |     202 |
  |      5 | robin    |   40 |     200 |
  |      6 | zhangsan |   28 |     204 |
  +--------+----------+------+---------+
  6 rows in set (0.00 sec)

创建表二

mysql> create table department6(dept_id int, dept_name varchar(100));
  Query OK, 0 rows affected (0.01 sec)

mysql> desc department6;
  +-----------+--------------+------+-----+---------+-------+
  | Field     | Type         | Null | Key | Default | Extra |
  +-----------+--------------+------+-----+---------+-------+
  | dept_id   | int(11)      | YES  |     | NULL    |       |
  | dept_name | varchar(100) | YES  |     | NULL    |       |
  +-----------+--------------+------+-----+---------+-------+
  2 rows in set (0.00 sec)

mysql> insert into department6 values  (200,'hr'),(201,'it'),(202,'sale'),(203,'op');
  Query OK, 4 rows affected (0.00 sec)
  Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from department6;
  +---------+-----------+
  | dept_id | dept_name |
  +---------+-----------+
  |     200 | hr        |
  |     201 | it        |
  |     202 | sale      |
  |     203 | op        |
  +---------+-----------+
  4 rows in set (0.00 sec)

内连接查询:只显示表中有匹配的数据

mysql> select employee6.emp_id,employee6.emp_name,employee6.age,department6.dept_name from employee6,department6 where employee6.dept_id = department6.dept_id;
  +--------+----------+------+-----------+
  | emp_id | emp_name | age  | dept_name |
  +--------+----------+------+-----------+
  |      1 | xiaoli   |   19 | hr        |
  |      2 | tom      |   26 | it        |
  |      3 | jack     |   30 | it        |
  |      4 | alice    |   24 | sale      |
  |      5 | robin    |   40 | hr        |
  +--------+----------+------+-----------+
  5 rows in set (0.00 sec)

外连接查询

  • 在做多张表查询时,所需要的数据,除了满足关联条件的数据外,还有不满足关联条件的数据。此时需要使用外连接.外连接分为三种
  • 左外连接:表A left [outer] join 表B on 关联条件,表A是主表,表B是从表
  • 右外连接:表A right [outer] join 表B on 关联条件,表B是主表,表A是从表
  • 全外连接:表A full [outer] join 表B on 关联条件,两张表的数据不管满不满足条件,都做显示。

左外连接(左键)

案例:查找出所有员工及所属部门

mysql> select emp_id,emp_name,dept_name from  employee6 left join department6 on employee6.dept_id = department6.dept_id;
  +--------+----------+-----------+
  | emp_id | emp_name | dept_name |
  +--------+----------+-----------+
  |      1 | xiaoli   | hr        |
  |      5 | robin    | hr        |
  |      2 | tom      | it        |
  |      3 | jack     | it        |
  |      4 | alice    | sale      |
  |      6 | zhangsan | NULL      |
  +--------+----------+-----------+
  6 rows in set (0.01 sec)

右外连接(右键)

案例:找出所有部门包含的员工

mysql> select emp_id,emp_name,dept_name from  employee6 right join department6 on employee6.dept_id = department6.dept_id;
  +--------+----------+-----------+
  | emp_id | emp_name | dept_name |
  +--------+----------+-----------+
  |      1 | xiaoli   | hr        |
  |      2 | tom      | it        |
  |      3 | jack     | it        |
  |      4 | alice    | sale      |
  |      5 | robin    | hr        |
  |   NULL | NULL     | op        |
  +--------+----------+-----------+
  6 rows in set (0.00 sec)

你可能感兴趣的