MySQL高阶SQL语句

1、按关键字排序（order by）

1、使用order by语句来实现排序
2、排序可针对一个或多个字段
3、ASC：升序，默认排序方式
4、DESC：降序
5、order by的语法结构

mysql> create school;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'school' at line 1
mysql> create database school;
Query OK, 1 row affected (0.00 sec)

mysql> use school;
Database changed
mysql> create table info (id int,name varchar(10) primary key not null ,score decimal(5,2),address varchar(20),hobbid int(5));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into info values(1,'liuyi',80,'beijing',2);
Query OK, 1 row affected (0.00 sec)
mysql> insert into info values(2,'wangwu',90,'shengzheng',2);
Query OK, 1 row affected (0.01 sec)
mysql> insert into info values(3,'lisi',60,'shanghai',4);
Query OK, 1 row affected (0.00 sec)
mysql> insert into info values(4,'tianqi',99,'hangzhou',5);
Query OK, 1 row affected (0.00 sec)
mysql> insert into info values(5,'jiaoshou',98,'laowo',3);
Query OK, 1 row affected (0.00 sec)
mysql> insert into info values(6,'hanmeimei',10,'nanjing',3);
Query OK, 1 row affected (0.00 sec)
mysql> insert into info values(7,'lilei',11,'nanjing',5);
Query OK, 1 row affected (0.00 sec)
mysql> select * from info;

按分数排序，默认不指定是升序排列

mysql> select id,name,score from info order by score;

分数降序排列

mysql> select id,name,score from info order by score desc;

order by还可以结合where进行条件过滤，筛选地址是杭州的学生按分数降序排列

mysql> select name,score from info where address='shanghai' order by score desc;

id降序排列，相同分数的，id也按降序排列

mysql> select id,name,hobbid from info order by hobbid desc,id desc;

区间判断及查询不重复记录

select * from info where score >70 and score <=90;

select * from info where score >70 or score <=90;

select * from info where score >70 or (score >75 and score <90);

添加：

distinct 查询不重复记录

mysql> select distinct hobbid from info;

2、对结果进行分组

1、使用group by语句来实现分组
2、通常结合聚合函数一起使用
3、可以按一个或多个字段对结果进行分组
4、group by的语法结构

按hobbid相同的分组，计算相同分数的学生个数

mysql> select count(name),hobbid from info group by hobbid;

结合where语句，筛选分数大于等于80的分组

mysql> select count(name),hobbid from info where score>=80 group by hobbid;

mysql> select count(name),score,hobbid from info where score>=80 group by hobbid order by count(name) asc;

3、限制结果条目

1、只返回select查询结果的第一行或前几行
2、使用limit语句限制条目
3、limit语法结构
offset：位置偏移量，从0开始
number：返回记录行的最大数目

查询所有信息显示前4行记录

mysql> select * from info limit 3;

从第4行开始，往后显示3行内容

mysql> select * from info limit 3,3;

结合order by语句，按id的大小升序排列显示前三行

mysql> select id,name from info order by id limit 3;

基础select 小的升阶 怎么输出最后三行

mysql> select id,name from info order by id desc limit 3;

查询info表的字段数量，以number显示

mysql> select count(*) as number from info;

mysql> create table t1 as select * from info;
Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0
mysql> select * from t1;

通配符

通配符主要用于替换字符串中的部分字符，通过部分字符的匹配将相关结果查询出来。

查询名字是l开头的记录

mysql> select id,name from info where name like 'l%';

mysql> select id,name from info where name like 'l____';

查询名字中间有i的记录

mysql> select id,name from info where name like '%i%';

子查询

子查询也被称作内查询或者嵌套查询，是指在一个查询语句里面还嵌套着另一个查询语 句。子查询语句是先于主查询语句被执行的，其结果作为外层的条件返回给主查询进行下一 步的查询过滤。
PS: 子语句可以与主语句所查询的表相同，也可以是不同表

mysql> select name,score from info where id in (select id from info where score >80);

不同表/多表实例

mysql>  create table ky12 (id int);
Query OK, 0 rows affected (0.00 sec)
mysql> insert into ky12 values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> show tables;

mysql> select id,name,score from info where id in (select * from ky12);

查询分数打于90的记录

mysql>  select name,score from info where id in (select id from info where score>90);

mysql> insert into t1 select * from info where id in (select id from info);
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

将lisi的分数改为50

mysql> update info set score=50 where id in (select * from ky12 where id=3);
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from info;

删除分数不是大于等于80的记录

mysql> delete from t1 where id not in (select id where score>=80);
Query OK, 6 rows affected (0.00 sec)
mysql> select id,name,score from t1;

子查询

查询info表id，name字段

mysql> select id,name from info;

视图

数据库中的虚拟表，这张虚拟表中不包含真实数据，只是做了映射
镜花水月/倒影，动态保存结果集（数据）

满足80分的学生展示在视图中

mysql> create view v_score as select * from info where score>=80;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from v_score;

修改原表数据

mysql> update info set score='60' where name='wangwu';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from v_score;

NULL值

在 SQL 语句使用过程中，经常会碰到 NULL 这几个字符。通常使用 NULL 来表示缺失 的值，也就是在表中该字段是没有值的。如果在创建表时，限制某些字段不为空，则可以使 用 NOT NULL 关键字，不使用则默认可以为空。在向表内插入记录或者更新记录时，如果该字段没有 NOT NULL 并且没有值，这时候新记录的该字段将被保存为 NULL。需要注意 的是，NULL 值与数字 0 或者空白（spaces）的字段是不同的，值为 NULL 的字段是没有 值的。在 SQL 语句中，使用 IS NULL 可以判断表内的某个字段是不是 NULL 值，相反的用 IS NOT NULL 可以判断不是 NULL 值。
null值与空值的区别(空气与真空)
空值长度为0，不占空间，NULL值的长度为null，占用空间
is null无法判断空值
空值使用"=“或者”<>"来处理（！=）

mysql> desc info;

插入一条记录，分数字段输入null，显示出来就是null

mysql> alter table info add column addr varchar(50);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> select count(addr) from info;

将info表中其中一条数据修改为空值’’

mysql> update info set addr='' where name='wangwu';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0
mysql> select count(addr) from info;