Mysql数据库-基本表操作

1.表操作

1. 创建表：CREATE TABLE table_name ( field1 datatype, field2 datatype, field3 datatype ) character set 字符集 collate 校验规则 engine 存储引擎;
   field 表示列名
   datatype 表示列的类型
   character set 字符集，如果没有指定字符集，则以所在数据库的字符集为准
   collate 校验规则，如果没有指定校验规则，则以所在数据库的校验规则为准
   示例：create table users ( id int, name varchar(20) comment '用户名', password char(32) comment '密码是32位的md5值', birthday date comment '生日' ) character set utf8 engine MyISAM;

2. 查看表结构：desc 表名;

3. 修改某个表的结构，比如字段名字，字段大小，字段类型，表的字符集类型，
   表的存储引擎等等
   示例1：在users表添加一个字段，用于保存图片路径
   alter table users add assets varchar(100) comment '图片路径' after birthday;
   示例2：修改name，将其长度改成60
   alter table users modify name varchar(60)；
   示例3：删除password列，删除字段及其对应的列数据都没了
   alter table users drop password;

4. 删除表：drop table t1;

2.增

1.插入数据
表结构如图

单行数据 + 全列插入：INSERT INTO students VALUES (100, 10000, '唐三藏', NULL);
多行数据 + 指定列插入：INSERT INTO students (id, sn, name) VALUES (102, 20001, '曹孟德'), (103, 20002, '孙仲谋');
2.插入冲突：
由于 主键 或者 唯一键 对应的值已经存在而导致插入失败：INSERT INTO students (id, sn, name) VALUES (100, 10010, '唐大师') ON DUPLICATE KEY UPDATE sn = 10010, name = '唐大师';
由于数据冲突，使用ON DUPLICATE KEY UPDATE sn = 10010, name = '唐大师' 更新数据
3.替换
主键 或者 唯一键 没有冲突，则直接插入；
主键 或者 唯一键 如果冲突，则删除后再插入
REPLACE INTO students (sn, name) VALUES (20001, '曹阿瞒');
–将一张表的查询结果插入到另一张表：insert into no_duplicate_table (select distinct*from duplicate_table);

3.删

–删除孙悟空同学的考试成绩：delete from exam_result where name='孙悟空';

–删除整张表数据（慎用）：delete from for_delete;
只是删除表中数据，表还在

–截断表(慎用)：

1. 只能对整表操作，不能像 DELETE 一样针对部分数据操作；
2. 实际上 MySQL 不对数据操作，所以比 DELETE 更快，但是TRUNCATE在删除数据的时候，并不经过真正的事
   物，所以无法回滚
3. 会重置 AUTO_INCREMENT 项

TRUNCATE for_truncate;

截断整表数据，注意影响行数是 0，所以实际上没有对数据真正操作
再插入一条数据，自增 id 在重新增长

4.改

–将孙悟空同学的数学成绩变更为 80 分：UPDATE exam_result SET math = 80 WHERE name = '孙悟空';

–将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分：UPDATE exam_result SET math = 60, chinese = 70 WHERE name = '曹孟德';

–将总成绩倒数前三的 3 位同学的数学成绩加上 30 分：UPDATE exam_result SET math = math + 30 ORDER BY chinese + math + english LIMIT 3;

–将所有同学的语文成绩更新为原来的 2 倍：update exam_result set chinese=chinese*2;

5.查

表结构如图

–全列查询：select *from exam_result;

–指定列查询：SELECT id, name, english FROM exam_result;

指定列名不按照表中的顺序来也可以，可自行调整：SELECT name,id, english FROM exam_result;

–查询字段为表达式：SELECT id, name, english + 10 FROM exam_result;
SELECT id, name, chinese + math + english FROM exam_result;

–为查询结果指定别名：select id,name,math+chinese+english as 总分 from exam_result;
as可以省略

– 结果去重：SELECT distinct math FROM exam_result;


带条件查找（WHERE 条件）
比较运算符

=不支持NULL的比较，要比较NULL需要用到<=>、IS NULL、IS NOT NULL
图中两个比较的结果不同

逻辑运算符

–语文成绩在 [80, 90] 分的同学及语文成绩：select name,chinese from exam_result where chinese>=80 and chinese <=90;
或select name,chinese from exam_result where chinese between 80 and 90;


–数学成绩是 78 或者 79 或者 98 或者 99 分的同学及数学成绩：数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩
或：select math from exam_result where math in(78,79,98,99);


–查询姓孙的同学 及 孙某同学
(模糊匹配)查询姓孙的同学：select name from exam_result where name like '孙%';
% 匹配任意多个（包括 0 个）任意字符

(严格匹配)查询孙某同学：select name from exam_result where name like '孙_';
_ 匹配严格的一个任意字符

– 语文成绩好于英语成绩的同学：select name ,chinese ,english from exam_result where chinese>english;

–语文成绩 > 80 并且不姓孙的同学：select name ,chinese from exam_result where chinese>80 and name not like '孙%';

– 孙某同学，否则要求总成绩 > 200 并且 语文成绩 < 数学成绩 并且 英语成绩 > 80：select name ,chinese+math+english as 总分 from exam_result where (chinese+math+english>200) and (chinese<math) and (english>80) or name like '孙_';

–查询 qq 号已知的同学姓名：SELECT name, qq FROM students WHERE qq IS NOT NULL;
–查询 qq 号已知的同学姓名：SELECT name, qq FROM students WHERE qq IS NULL;
查询结果排序：
– ASC 为升序（从小到大，默认）
– DESC 为降序（从大到小）
若查询结果不进行排序，则返回的顺序是未定义的
NULL 视为比任何值都小，降序出现在最下面
–查询同学及数学成绩，按数学成绩升序显示：select name,math from exam_result math order by math asc;
–查询同学及数学成绩，按数学成绩降序显示：select name,math from exam_result math order by math desc;

–查询同学各门成绩，依次按 数学降序，英语升序，语文升序的方式显示：select name,math,english,chinese from exam_result order by math desc , english asc , chinese asc;
–查询同学及总分，由高到低：select name,math+english+chinese as 总分 from exam_result order by 总分 desc;
ORDER BY 子句中可以使用列别名

– 查询姓孙的同学或者姓曹的同学数学成绩，结果按数学成绩由高到低显示：select name,math from exam_result where name like '孙%' or name like '曹%' order by math desc;

筛选分页结果
从 0 开始，筛选 n 条结果：limit n
从 s 开始，筛选 n 条结果：limit s,n
从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用：limit n offset s;
–按 id 进行分页，每页 3 条记录，分别显示 第 1、2、3 页：select id,name from exam_result order by id limit 3 offset 0;
select id,name from exam_result order by id limit 3 offset 3;
select id,name from exam_result order by id limit 3 offset 6;

–查找总分前三的同学，并降序显示：select id,name,chinese+math+english as 总分 from exam_result order by 总分 desc limit 6 offset 0;
注意：要先排序再分页