java -通用查询sql语句

• 简单查询语句（DQL）
  语法格式：
  select 字段1,字段2,字段3，.....from 表名;

 //*代表所有字段
 select *from 表名;

 select name, id  from t_user;
 select name, id*12  from t_user; 可以进行计算

//给查询列起别名 不会修改数据库
 select name as title, id  from t_user; 
 select name as` title`, id  from t_user;
//as可以省略加空格
select name  title, id  from t_user; 
 提示:
      1.任何一条sql语句以  ;  结尾
       2.sql语句不区分大小写

• 条件查询
  语法格式
  select 字段1 字段2 from 表名 where 条件;
  提示：
  1.and:优先级高 or:优先级低
2.or 等同于 in
3.模糊查询like %:代表任意多个字符 _:代表任意一个字符

select name from t_user where name = 'LiLei';
+-------+
| name  |
+-------+
| LiLei |
+-------+

select * from t_user where name = 'LiLei'
+----+-------+-----+--------+
| id | name  | age | gender |
+----+-------+-----+--------+
|  1 | LiLei |  18 |      1 |
+----+-------+-----+--------+

select * from t_user where age>17;
+----+----------+-----+--------+
| id | name     | age | gender |
+----+----------+-----+--------+
|  1 | LiLei    |  18 |      1 |
|  5 | WeiHua   |  32 |      0 |
|  6 | ZhangWei |  25 |      1 |
|  7 | Ann      |  36 |      0 |
|  8 | Lisa     |  19 |      0 |
|  9 | ZhangWei |  18 |      1 |
+----+----------+-----+--------+

select *from t_user where id  between 1 and 5;  相当于 [1~5]
+----+-----------+-----+--------+
| id | name      | age | gender |
+----+-----------+-----+--------+
|  1 | LiLei     |  18 |      1 |
|  2 | HanMeimei |  17 |      0 |
|  3 | Lucy      |  17 |      0 |
|  4 | Lili      |  16 |      0 |
|  5 | WeiHua    |  32 |      0 |
+----+-----------+-----+--------+

select *from t_user where content is not  null;
+----+------+-----+--------+---------+
| id | name | age | gender | content |
+----+------+-----+--------+---------+
|  8 | Lisa |  19 |      0 | ceshi   |
+----+------+-----+--------+---------+

//俩个语句效果一样
select *from t_user where name='Lisa' or name ='ZhangWei';
select *from t_user where name in('Lisa','ZhangWei');
+----+----------+-----+--------+---------+
| id | name     | age | gender | content |
+----+----------+-----+--------+---------+
|  8 | Lisa     |  19 |      0 | ceshi   |
|  9 | ZhangWei |  18 |      1 | NULL    |
|  6 | ZhangWei |  25 |      1 | NULL    |
+----+----------+-----+--------+---------+

//名字中包含n的
select *from t_user where name like '%n%';
+----+-----------+-----+--------+---------+
| id | name      | age | gender | content |
+----+-----------+-----+--------+---------+
|  2 | HanMeimei |  17 |      0 | NULL    |
|  6 | ZhangWei  |  25 |      1 | NULL    |
|  7 | Ann       |  36 |      0 | NULL    |
|  9 | ZhangWei  |  18 |      1 | NULL    |
+----+-----------+-----+--------+---------+

//第二个字母为u的名字
select *from t_user where name like '_u%';
+----+------+-----+--------+---------+
| id | name | age | gender | content |
+----+------+-----+--------+---------+
|  3 | Lucy |  17 |      0 | NULL    |
+----+------+-----+--------+---------+

//特殊 名字中有下划线的
select *from t_user where name like '%_%' ;//不行
只能让 _ 转义 
select *from t_user where name like '%\_%'; ok
+----+-------+-----+--------+---------+
| id | name  | age | gender | content |
+----+-------+-----+--------+---------+
| 10 | Ka_te |  17 |      1 | NULL    |
+----+-------+-----+--------+---------+