表的基本操作

表的基本操作

创建一张表

#创建test表默认引擎为innodb
create table test(
id int,
name char(18));

#创建test1表 设置引擎为myisam
create table test1(
id int,
name char(18)) engine=myisam;

查看表结构

desc 表名;

查看表的详细信息

show create table test;

修改表结构

语法：
1. 修改表名
      ALTER TABLE 表名 
                      RENAME 新表名;

2. 增加字段
      ALTER TABLE 表名
                      ADD 字段名  数据类型 [完整性约束条件…],
                      ADD 字段名  数据类型 [完整性约束条件…];
                            
3. 删除字段
      ALTER TABLE 表名 
                      DROP 字段名;

4. 修改字段
      ALTER TABLE 表名 
                      MODIFY  字段名 数据类型 [完整性约束条件…];
      ALTER TABLE 表名 
                      CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
      ALTER TABLE 表名 
                      CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];

5.修改字段排列顺序/在增加的时候指定字段位置
    ALTER TABLE 表名
                     ADD 字段名  数据类型 [完整性约束条件…]  FIRST;
    ALTER TABLE 表名
                     ADD 字段名  数据类型 [完整性约束条件…]  AFTER 字段名;
    ALTER TABLE 表名
                     CHANGE 字段名  旧字段名 新字段名 新数据类型 [完整性约束条件…]  FIRST;
    ALTER TABLE 表名
                     MODIFY 字段名  数据类型 [完整性约束条件…]  AFTER 字段名;

表的增、删、改、查

插入数据

#方式一，这张表有多少字段，就需要按照字段的顺序写入多少个值
insert into 表名 value ('minmin',18);
insert into 表名 values ('minmin',18);
#方式二，同时写入多个数据
insert into 表名 values ('minmin',18),('xiaohong',20);
#方式三，给指定的字段写入数据
insert 表 (字段1，字段2) values ('minmin',18);
#方式四，直接将t2中的id,name数据写入t1中
insert into t2(id,name) select id,username from t1;

查询数据

#查询student表中的所有数据
select * from student;
#查寻student表中id=1的数据
select * from student where id=1;
#查询student表中字段1和字段2的数据
select 字段1,字段2 from student;

#查询student中user_name字段的数据并把user_重命名为name
select user_name as name from student;

#去掉重复的内容
select distinct 字段 from 表名;
select distinct 字段1,字段2 from 表名;

#函数(concat,concat_ws)
select concat(name,':',age) from 表名; 
select concat_ws('|','1','2','3');

where约束

• where字句中可以使用：
  • 比较运算符：>、<、>=、<=、=！
  • 范围：
    • between 80 and 100 ： 表示80到100之间
    • in(80,90,100) ：值是否是80，90，100（值是否在集合里面）
    • not in（80,90,100） ：值不是80,90,100的数据匹配出来
  • 模糊匹配：
    • like 'e%'：模糊查询 （%表示任意长度的任意内容，_表示一个长度的任意内容）
      • like 'e%':匹配以e开头的字符串
      • like '%e%':匹配以e开头或e在中间的字符串
      • like '%e':匹配以e结尾的字符串
    • 正则：regexp
  • 逻辑运算符 ：and,not,or
  • 注：判断一个字段是否为NULL的时候要使用is

分组group by

• group by 根据谁分组，可以求这个组的总人数，最大值，最小值，平均值，求和，但是这个求出来的值只是和分组字段对应，并不和其他任何字段对应，这个时候查出来的所有其他字段都不生效。

聚合函数

• count：求总数

• max ：求最大值

• min ：求最小值

• sum ：求总数

• avg：求平均值

过滤语句 having

• 在having语句中可以使用聚合函数，在where中不行

• 适合筛选符合条件的某一组数据

• 先分组在过滤：求平均薪资大于xx的部门，求人数大于多少岁的性别，求年龄多少的人

排序 order by

• #从大到小排序,默认为asc所以asc可以不写
  select * from 表名 order by 要排序的字段;
  select * from 表名 order by 要排序的字段 asc;  
  #从小到大排序
  select * from 表名 order by 要排序的字段 desc;
  

limit m,n

• 从m+1项开始取n项
• 如果不写m默认为0
• limit n offset m (从m+1开始取n项)

多表查询

• 连表查询

  • 内连接：inner join ... on ...
    • select * from 表1,表2 where 条件; (了解)
    • select * from 表1 inner join 表2 on 条件;
  • 外连接
    • 左外连接
      • select * from 表1 left join 表2 on 条件;
    • 右外连接
      • select * from 表1 right join 表2 on 条件;

• 子查询

  • in,not in
  • <,>,=,!=,<=,>=

• select 的执行顺序
  

• where字段不能用select字段的重命名，应为where是在select之前执行而重命名是在执行完select之后才执行的。

• order by和 having可以使用select字段的重命名，order by 在select之后执行，having经过了MySQL的特殊处理，使得他可以感知到select语句中的重命名。

• 在执行selelct语句的时候，实际上是通过where、group by、having几个语句来锁定对应的行，然后循环每一行执行select语句

修改数据

#修改一个字段
update student set age=17 where name='minmin';
#修改两个字段
update student set 字段=值，字段=值 where 条件;

删除数据

#会清空表，但不会清空自增字段的offset(偏移量)值
delete from student where name='minmin';
#会清空表和自增字段的偏移量
truncate table student；