sql语句

sql语句

针对数据

• insert into t1 values(1,'jason');
• insert into t1 values(1,'jason'),(2,'agen'),(3,'tank');
• delete from ti where id>2;
• delete from t1; 清空数据表
  truncate t1： 清空数据表并重置主键
• update t1 set name='ds' where id=1;
• select * from t1; # 查看所有数据
  \G：分行展示内容
• select name from t1;

针对表

• create table(id int,name char(10)) 创建表
• drop table t1 删除表
• show tables 展示所有表
• show create table t1 查看表的创建语句
• show columns from tablename; 查看表的详细信息
• desc t1 查看表的详细信
• create table new_table select * from old_table 复制表，不复制主键索引
• alter table tbname rename to tbname2; 修改表的名字
• alter table tableName engine=myisam; 修改存储引擎
• alter table tableName drop foreign key keyName; 修改外键约束
• alter table tableName modify name1 type1 first|after name2; 修改字段的相对位置

创建临时表： create temporary table tbname(id int not null);

断开链接临时表自动清除

针对字段

• alter table 表名 add 列名 类型 -- 添加字段

• alter table 表名 drop 列名 -- 删除字段

• alter table 表名 modify 列名 类型及约束 -- 修改字段约束

• alter table 表名 change 原名 新名 类型及约束 -- 修改字段

• alter table 表名 add primary key(id); -- 添加主键

针对数据库

• create database db1 创建数据库
• create database db2 charset='gbk'
• drop database db1 删除数据库
• alter database db1 charset='utf8'
• show databases 查看所有数据库
• show create database db1 查看单个信息
• use db2 切换数据库

字段类型：

约束：

• primary key 约束主键
• auto_increment 自增主键
• not noll 不为空
• unique 字段唯一
• default 默认值
• create index 创建索引
• foreign key 外键
• on update cascade 级联更新
• on delete cascade 级联删除

高级查询

where条件查询

1、比较运算

= 、 > 、 < 、 >= 、 <= 、 !=

2、逻辑运算

and
or
not

3、like 模糊查询

like
select * from tb where name like 'text%';

% 代表任意字符

_ 代表一个字符

4、in 范围查询

in 在指定范围内
	select * from t1 where id in (1,2,3);
	
between and 在一个连续的范围内
	selsect * from t1 where id between 1 and 5

5、空判断

判断为空    select * from t1 where name is null;
判断为非空   select * from t1 where name is not null;

优先级：小括号 > not ? 比较运算符 > 逻辑运算符

and > or

6、ifnull(columnName2,0)

如果第一个数是null，返回第二个数

select * , columnName1+ifnull(columnName2,0) from tableName;

7、binary

where查询默认不区分大小写，添加binary可以区分大小写

select * from tb where binary name='Root';

order by排序

select * from t1 where order by name age desc

desc：降序

asc：升序，默认

distinct 去重

select distinct age from t1;

不能包含主键

limit分页

select * from t1 limit 2,2;

start：从这个位置开始

count：获取指定个数的数据

regexp正则

select * from t1 where naem regexp"^[a-d]{3}$";

• ^ ：匹配开头。如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 '\n' 或 '\r' 之后的位置。
• $ ：匹配结尾。如果设置了RegExp 对象的 Multiline 属性，$ 也匹配 '\n' 或 '\r' 之前的位置。
• . ：匹配任意单个字符，除了\n。 [.\n] 匹配任意内容
• [...] ：匹配包含的任意一个字符
• [^...] ：匹配未包含的任一字符
• | ：或
• * ：匹配0次或多次
• + ：匹配一次或多次
• {n} ：匹配n次
• {n,m} ：匹配n~m次。

聚合查询

1、最大值 max()

2、最小值 min()

3、计算总数 count()

4、求和 sum()

5、平均值 avg()

select max(id) from t1;

6、concat() 获取字段集合

select concat(name,":",age) from tt;

连接符需要自己加

concat_ws() 获取字段集合

select concat(":",name,age) from tt;

参数1是连接符，自动添加

group by分组查询

+------+--------+------+-------+
| id   | name   | age  | hobby |
+------+--------+------+-------+
|    1 | python |   11 | play  |
|    2 | java   |   22 | play  |
|    3 | c      |   33 | paly  |
|    4 | g0     |    4 | paly  |
+------+--------+------+-------+

1、简单分组

select name from user group by hobby;

每一组只显示一个内容，可以去重

2、组合 group_concat()

select hobby group_concat(name) from user group by hobby;

把每一组的内容组合起来，用逗号隔开

+-------+--------------------+
| hobby | group_concat(name) |
+-------+--------------------+
| paly  | c,g0               |
| play  | python,java        |
+-------+--------------------+

3、组合聚合函数

select hobby,avg(age) from user group by hobby;
+-------+----------+
| hobby | avg(age) |
+-------+----------+
| play  |  16.5000 |
| paly  |  18.5000 |
+-------+----------+

4、组合having

select hobby,count(*) from user group by hobby having count(*)>2;

可以在分组查询之后添加查询条件

5、添加汇总结果with rollup

select hobby,count(*) from user group by hobby with rollup;
+-------+----------+
| hobby | count(*) |
+-------+----------+
| paly  |        2 |
| play  |        3 |
| NULL  |        5 |
+-------+----------+

6、coalesce(a,b,c)

如果anull,则选择b；如果bnull,则选择c

mysql> SELECT coalesce(name, '总数'), SUM(singin) as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;
+--------------------------+--------------+
| coalesce(name, '总数') | singin_count |
+--------------------------+--------------+
| 小丽                   |            2 |
| 小明                   |            7 |
| 小王                   |            7 |
| 总数                   |           16 |
+--------------------------+--------------+
4 rows in set (0.01 sec)

如果名字为空我们使用总数代替

多表查询

1、内连接 inner join on

select s.name,c.name from table1 as s inner join table2 as c on s.id=c.id

显示两张表都有的数据

// where 字句也可以达到 union join 的效果
SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;

SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a, tcount_tbl b WHERE a.runoob_author = b.runoob_author;

2、左连接 left join on

select * from t1 as s left join t2 as c on s.id=c.id;

左表展示完整数据，右表没有对应数据展示NULL

3、右连接 left join on

select * from t1 as s right join t2 as c on s.id=c.id;

右表展示完整数据，左表没有对应数据展示NULL

4、全连接union

select count(*) from user union select count(*) from app01_user;
+----------+
| count(*) |
+----------+
|        5 |
|        3 |
+----------+

组合两种查询结果，条件是两个查询所获取的字段要相同

会删除重复的数据 union all 展示所有数据

5、直接连接

select * from t1,t2;    获取笛卡尔积

select * from t1,t2 where t1.id=t2.id;  从笛卡尔积中找结果

子查询

select * from students where age > (select avg(age) from students);

select name from classes where id in (select cls_id from students);

内部函数

1、聚合函数

​ 最大值 max()

​ 最小值 min()

​ 计算总数 count()

​ 求和 sum()

​ 平均值 avg()

2、coalesce(a,b,c)

​ 如果anull,则选择b；如果bnull,则选择c

3、ifnull(colum,0)

​ 如果第一个数是null，返回第二个数