测试表company.employee5
mysql> create database company;
#创建一个库；
创建一个测试表:
mysql> CREATE TABLE company.employee5(id int primary key auto_increment not null,name varchar(30) not null,sex enum('male','female') default 'male' not null,hire_date date not null,post varchar(50) not null,job_description varchar(100),salary double(15,2) not null,office int,dep_id int);
插入数据:
mysql> insert into company.employee5(name,sex,hire_date,post,job_description,salary,office,dep_id) values ('jack','male','20180202','instructor','teach',5000,501,100),('tom','male','20180203','instructor','teach',5500,501,100),('robin','male','20180202','instructor','teach',8000,501,100),('alice','female','20180202','instructor','teach',7200,501,100),('tianyun','male','20180202','hr','hrcc',600,502,101),('harry','male','20180202','hr',NULL,6000,502,101),('emma','female','20180206','sale','salecc',20000,503,102),('christine','female','20180205','sale','salecc',2200,503,102),('zhuzhu','male','20180205','sale',NULL,2200,503,102),('gougou','male','20180205','sale','',2200,503,102);mysql> use company
简单查询语法：
select 字段名称,字段名称2 from 表名 条件
简单查询
mysql> select * from employee5;多字段查询：
mysql> select id,name,sex from employee5;
有条件查询：where
mysql> select id,name from employee5 where id<=3;#查询表中id大于等于三的mysql> select id,name,salary from employee5 where salary>2000;
设置别名：as
mysql> select id,name,salary as "salry_num" from employee5 where salary>5000;给 salary 的值起个别名，显示值的表头会是设置的别名
统计记录数量：count()
mysql> select count(*) from employee5;#统计表中的记录数量统计字段得到数量:mysql> select count(id) from employee5;
#统计表中id的记录数量避免重复DISTINCT:表里面的数据有相同的mysql> select distinct post from employee5;#字段 表名
#查找post字段中重复的字符
复制表
1.复制表结构＋记录 语法:create table 新表 select * from 旧表;mysql> create table new_t1 select * from employee5;2.复制单个字段和记录:mysql> create table new_t2(select id,name from employee5);
3.多条件查询: and ----和，并且
语法: select 字段，字段2 from 表名 where 条件 and 条件；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 ----或者
语法： select 字段，字段2 from 表名 where 条件 or 条件；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;查找salary中5000到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; #-取反 不是nullmysql> 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; #降序，从大到小
8.limit 限制
mysql> select * from employee5 limit 5; #只显示前5行mysql> select name,salary from employee5 order by salary desc limit 0,1; #从第几行开始，打印一行查找什么内容从那张表里面降序排序只打印第二行。注意：0-------默认第一行1------第二行 依次类推...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条
9.分组查询 ：group by
mysql> select count(name),post from employee5 group by post;+-------------+------------+| count(name) | post |+-------------+------------+| 2 | hr || 4 | instructor || 4 | sale |+-------------+------------+count可以计算字段里面有多少条记录，如果分组会分组做计算
10.函数
max() 最大值mysql> select max(salary) from employee5;min()最小值select min(salary) from employee5;
avg()平均值select avg(salary) from employee5;now() 现在的时间
select now();sum() 计算和
mysql>select sum(salary) from employee5 where post='sale';
，源码可白嫖!)
