MySQL单表查询

通过例子所学习命令的用法

 1.创建表，以及表字段类型

mysql> create table cc (
    -> id int primary key auto_increment not null comment "雇员编号",
    -> name varchar(30) not null comment "雇员姓名",
    -> sex enum('male','female') not null default 'female',
    -> hire_date date not null,
    -> post varchar(50),
    -> job_description varchar(100),
    -> salary double (15,2),
    -> office int,
    -> dep_id int
    -> );

 primary key ：主键：作为数据的唯一标识

1）一般加在无意义的字段上，如 id

2）标主键字段的要求：值不重复且值具有唯一性。主键不能为空

3）可以设置“单字段主键”和“多字段主键（复合主键）”，用多个字段确定唯一性

4）primary书写时可省略

auto_increment：

1）通过设置主键进行自增长，默认从1开始，每次+1

2）一个表中只能有1个自增长字段，而且自增长的字段一定配合主键使用，也就是说“被标识为自增长的字段，一定是主键，但是主键不一定是自增长的”自增长只对整数类、整数列有效，对字符串无意义

2.向表中写入数据

mysql> insert into 
    cc(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);

3.简单查询

mysql> SELECT * from t3;
mysql> SELECT name, salary, dep_id from cc;

 避免重复查询：

SELECT DISTINCT post  FROM cc;
查询岗位，distinct表示避免重复

 运算查询：

SELECT name, salary, salary*14 FROM cc;
SELECT name, salary, salary*14 AS Annual_salary FROM cc;
SELECT name, salary, salary*14 Annual_salary FROM cc;
代表运算查询，salary为雇员薪水，意思为查看14个月薪水，annual代表一年

 定义显示格式查询：

定义显示格式
   CONCAT() 函数用于连接字符串
   SELECT CONCAT(name, ' annual salary: ', salary*14)  AS Annual_salary FROM cc;
显示结果为：FROM cc;
+-----------------------------------+
| Annual_salary                     |
+-----------------------------------+
| jack annual salary: 70000.00      |
| tom annual salary: 77000.00       |
| robin annual salary: 112000.00    |
| alice annual salary: 100800.00    |
| tianyun annual salary: 8400.00    |
| harry annual salary: 84000.00     |
| emma annual salary: 280000.00     |
| christine annual salary: 30800.00 |
| zhuzhu annual salary: 30800.00    |
| gougou annual salary: 30800.00    |
+-----------------------------------+

 单条件查询，多条件查询：

单条件查询
    mysql> SELECT name from employee5 WHERE salary=5000;

多条件查询
    mysql> SELECT name from employee5 WHERE salary>5000 and salary<6000;

SELECT name,job_description FROM cc WHERE job_description IS NULL;
查询字段中为空的数据

NULL说明：
        1、等价于没有任何值、是未知数。
        2、NULL与0、空字符串、空格都不同,NULL没有分配存储空间。
        3、对空值做加、减、乘、除等运算操作，结果仍为空。
        4、比较时使用关键字用“is null”和“is not null”。
        5、排序时比其他数据都小，所以NULL值总是排在最前。

关键字in查询：

SELECT name, salary FROM cc WHERE salary=4000 OR salary=5000 OR salary=6000 OR salary=9000;
	
SELECT name, salary FROM cc WHERE salary IN (4000,5000,6000,9000) ;

SELECT name, salary FROM cc WHERE salary NOT IN (4000,5000,6000,9000) ;

in代表查询这几个数中所符合的人

模糊查询(通配符)   
    %  所有字符
    mysql> SELECT * from cc WHERE salary like '%20%';
  
正则查询
    SELECT * FROM cc WHERE salary regexp '72+';
    SELECT * FROM cc WHERE name REGEXP '^ali';
    SELECT * FROM cc WHERE name REGEXP 'yun$';
    SELECT * FROM cc WHERE name REGEXP 'm{2}';

排序查询
mysql> SELECT name from cc order by name;
mysql> SELECT name from cc order by name desc;
mysql> SELECT name from cc order by name desc limit 3;		//限制次数
mysql> SELECT name from cc order by name desc limit 1,3;

函数：

   count()  
    max()
    min()
    avg()
    database()
    user()
    now()
    sum()
    password()