DQL数据查询语言,用于对数据库表中的记录进行查询操作,select
1.查询
1、查看表中所有数据: select * from 表名;星号*是个通配符,表示获取所有列的值。
例句:查看学生的所有信息
select * from tb_student;
2.按列查询:select 列名1,列名2.... from 表名;
例句:查看学生的学号和姓名
select id,name from tb_student;
mysql> create table if not exists tb_student(
-> id bigint primary key auto_increment,
-> name varchar(20) not null,
-> birth date,
-> sex boolean default 1
-> )engine=innodb default charset utf8;
Query OK, 0 rows affected, 1 warning (2.38 sec)
mysql> select * from tb_student;
+----+------+------------+------+
| id | name | birth | sex |
+----+------+------------+------+
| 1 | 张三 | 1996-12-27 | 1 |
| 2 | 李四 | 1997-07-05 | 0 |
| 3 | 王五 | 1999-04-01 | 0 |
| 4 | 赵六 | 1998-05-19 | 1 |
+----+------+------------+------+
4 rows in set (0.03 sec)
mysql> select id,name from tb_student;
+----+------+
| id | name |
+----+------+
| 1 | 张三 |
| 2 | 李四 |
| 3 | 王五 |
| 4 | 赵六 |
+----+------+
4 rows in set (0.00 sec)
2.带条件查询where
使用where子句可以对表中的数据筛选,返回结果为true的结果集
语法:select * from 表名 where 条件;
例句:查询id小于四的学生信息:select * from tb_student where id<4;
mysql> select * from tb_student where id<4;
+----+------+------------+------+
| id | name | birth | sex |
+----+------+------------+------+
| 1 | 张三 | 1996-12-27 | 1 |
| 2 | 李四 | 1997-07-05 | 0 |
| 3 | 王五 | 1999-04-01 | 0 |
+----+------+------------+------+
3 rows in set (0.03 sec)
mysql比较运算符 :
等于判断=、不等于判断!=、大于>、小于<,大于等于>=、小于等于<=,
in:用于判断某个列的取值是否在指定的集合内
例句:查询id为1或2或4的学生信息 select * from tb_student where id in(1,2,4);
mysql> select * from tb_student where id in(1,2,4);
+----+------+------------+------+
| id | name | birth | sex |
+----+------+------------+------+
| 1 | 张三 | 1996-12-27 | 1 |
| 2 | 李四 | 1997-07-05 | 0 |
| 4 | 赵六 | 1998-05-19 | 1 |
+----+------+------------+------+
3 rows in set (0.03 sec)
between and:用于判断数据是否在指定的范围内,连续值
例句:查询id在1和3之间的学生信息 select * from tb_student where id between 1 and 3;
mysql> select * from tb_student where id between 1 and 3;
+----+------+------------+------+
| id | name | birth | sex |
+----+------+------------+------+
| 1 | 张三 | 1996-12-27 | 1 |
| 2 | 李四 | 1997-07-05 | 0 |
| 3 | 王五 | 1999-04-01 | 0 |
+----+------+------------+------+
3 rows in set (0.00 sec)
3、非空判断
在MySQL中null 的意思是不确定,不知道,对于任何值和null 比较返回的都是null(包括null),所以=null 或 !=null 是不会查到任何数据的,所以MySQL在进行非空判断是使用is null或者is not null
mysql> select null=null,null!=null;
+-----------+------------+
| null=null | null!=null |
+-----------+------------+
| NULL | NULL |
+-----------+------------+
1 row in set (0.00 sec)
4、字符串比较和模糊查询
例句:查询 ' 张三 ' 的信息: select * from tb_student where name = '张三';
模糊查询 like/not like 统配符_和%
统配符_:表示一个任意字符
统配符%:表示任意个任意字符
统配符_和%可以是在字符串的任意位置
例句:查询所有姓张的同学的信息:select* from tb_student where name like '张%';
mysql> select * from tb_student;
+----+--------+------------+------+
| id | name | birth | sex |
+----+--------+------------+------+
| 1 | 张三 | 1996-12-27 | 1 |
| 2 | 李四 | 1997-07-05 | 0 |
| 3 | 王五 | 1999-04-01 | 0 |
| 4 | 赵六 | 1998-05-19 | 1 |
| 5 | null | NULL | 1 |
| 6 | NULL | 1999-11-20 | 0 |
| 7 | NULL | 2000-10-19 | 1 |
| 8 | 张三三 | NULL | NULL |
+----+--------+------------+------+
8 rows in set (0.00 sec)
mysql> select* from tb_student where name like '张_';
+----+------+------------+------+
| id | name | birth | sex |
+----+------+------------+------+
| 1 | 张三 | 1996-12-27 | 1 |
+----+------+------------+------+
1 row in set (0.03 sec)
mysql> select* from tb_student where name like '张%';
+----+--------+------------+------+
| id | name | birth | sex |
+----+--------+------------+------+
| 1 | 张三 | 1996-12-27 | 1 |
| 8 | 张三三 | NULL | NULL |
+----+--------+------------+------+
2 rows in set (0.00 sec)
5、选择表中的若干元组
写法: select * from tb_student where (id,name) > (1,'aaa');
select * from tb_student where id>1 and name>'aaa';