表复制:key不会被复制: 主键、外键和索引
复制表
1.复制表结构+记录 (key不会复制: 主键、外键和索引)
语法:create table 新表 select * from 旧表;
mysql>use company;
mysql> create table new_t1 select * from employee5;
mysql> show tables;
就会出现和employee5一样的表(相当于复制功能)
mysql>select * from new_t1;
2.复制单个字段和记录:
mysql> create table new_t2(select id,name from employee5);
3.多条件查询: and ----和(多个条件同时满足)
select count(name) from employee5 where post=‘hr’ and sex=‘male’;
会查看的更细致一点
mysql> select * from employee5 where post=‘hr’ and sex=‘male’;
语法: select 字段,字段2 from 表名 where 条件 and where 条件;
mysql> SELECT name,salary from employee5 where post=‘hr’ AND salary>1000;
mysql> SELECT name,salary from employee5 where post=‘instructor’ AND salary>1000;
4.多条件查询: or ----或者(任意一个满足都ok)
语法: select 字段,字段2 from 表名 where 条件 or 条件;
select count(name) from employee5 where post=‘hr’ or sex=‘male’;
mysql> select name from employee5 where salary>5000 and salary<10000 or dep_id=102;
mysql> select name from employee5 where salary>2000 and salary<6000 or dep_id=100;
5.关键字 BETWEEN AND 什么和什么之间(类似于 >= <=)
mysql> SELECT name,salary FROM employee5 WHERE salary BETWEEN 5000 AND 15000;
mysql> SELECT name,salary FROM employee5 WHERE salary NOT BETWEEN 5000 AND 15000;
mysql> select name,dep_id,salary from employee5 where not salary>5000;
注:not 给条件取反
6.关键字IS NULL 空的
mysql> SELECT name,job_description FROM employee5 WHERE job_description IS NULL;
执行这条命令有空格 空格也是有值的
mysql> SELECT name,job_description FROM employee5 WHERE job_description IS NOT NULL; #-取反 不是null
mysql> SELECT name,job_description FROM employee5 WHERE job_description=‘’; #什么都没有==空
NULL说明:
1、等价于没有任何值、是未知数。
2、NULL与0、空字符串、空格都不同,NULL没有分配存储空间。
3、对空值做加、减、乘、除等运算操作,结果仍为空。
4、比较时使用关键字用“is null”和“is not null”。
5、排序时比其他数据都小(索引默认是降序排列,小→大),所以NULL值总是排在最前。
7.排序查询 order by :指令,在mysql是排序的意思。
根据数字进行排序
mysql> select name,salary from employee5 order by salary; #-默认从小到大排序。
mysql> select name,salary from employee5 order by salary desc; #降序,从大到小
根据name字母顺序进行排列 首字母一个个对比
mysql> select name,salary from employee5 order by name;
先执行post等于hr 然后在进行排序
mysql> select name,salary from employee5 where post=‘hr’ order by salary;
8.limit 限制
mysql> select * from employee5 limit 5; #只显示前5行
高的在前 低的在后
mysql> select name,salary from employee5 order by salary desc;
只显示前5行
mysql> select name,salary from employee5 order by salary desc limit 5;
不做筛选 只看前5行
mysql> select name,salary from employee5 limit 5;
mysql> select name,salary from employee5 order by salary desc limit 0,1; #从第几行开始,打印一行
查找什么内容从那张表里面降序排序只打印第二行。
注意:
0-------默认第一行
1------第二行 依次类推…
打印总行
mysql> select name,salary from employee5;
从第一行后面开始 打印2行
mysql> select name,salary from employee5 limit 1,2;
mysql> SELECT * FROM employee5 ORDER BY salary DESC LIMIT 0,5; #降序,打印5行
mysql> SELECT * FROM employee5 ORDER BY salary DESC LIMIT 4,5; #从第5条开始,共显示5条
mysql> SELECT * FROM employee5 ORDER BY salary LIMIT 4,3; #默认从第5条开始显示3条。
9.分组查询(分类汇总求和) :group by
展现所有数据
mysql> select * from employee5;
分类求和(先分类在求和)
mysql> select post,sum(salary) from employee5 group by post;
分类总计(先分类在计数)
mysql> select count(name),post from employee5 group by post;
±------------±-----------+
| count(name) | post |
±------------±-----------+
| 2 | hr |
| 4 | instructor |
| 4 | sale |
±------------±-----------+
统计一下大于5000的都有谁(竖的排序)
mysql> select name from employee5 where salary>5000;
统计一下大于5000的都有谁(横的排序)
count可以计算字段里面有多少条记录,如果分组会分组做计算
mysql> select count(name),group_concat(name) from employee5 where salary>5000;
查找 统计(条件:工资大于5000)的有几个人(count(name)),分别是谁(group_concat(name))
±------------±---------------------------+
| count(name) | group_concat(name) |
±------------±---------------------------+
| 5 | tom,robin,alice,harry,emma |
±------------±---------------------------+
分组明细列表
11.GROUP BY和GROUP_CONCAT()函数一起使用
GROUP_CONCAT()-------组连接
mysql> SELECT dep_id,GROUP_CONCAT(name) FROM employee5 GROUP BY dep_id; #以dep_id分的组,dep_id这个组里面都有谁
根据dep_id进行分组,GROUP_CONCAT(name)打印出明细列表
mysql> SELECT dep_id,GROUP_CONCAT(name) as emp_members FROM employee5 GROUP BY dep_id; #给组连接设置了一个别名
对数字进行计算
count是计数 数字字符都可以
12.函数
max() 最大值
想统计出工资最高的一个人
mysql> select * from employee5 order by salary desc limit 1;
mysql> select name from employee5 order by salary desc limit 1;
想统计出工资最高的
mysql> select max(salary) from employee5;
查询薪水最高的人的详细信息:
mysql> select name,sex,hire_date,post,salary,dep_id from employee5 where salary = (SELECT MAX(salary) from employee5);
min()最小值
mysql>select min(salary) from employee5;
avg()平均值
mysql>select avg(salary) from employee5;
sum() 计算和
mysql>select sum(salary) from employee5 where post=‘sale’;