MySql的常见操作方法和代码

目录

一、基础

1、说明：创建数据库

2、说明：删除数据库

3、说明：创建新表

4、根据已有的表创建新表： 

5、说明：删除新表

6、说明：增加一个列

7、说明：添加主键： Alter table tabname add primary key(col) 

8、说明：创建索引：create [unique] index idxname on tabname(col….) 

9、说明：创建视图：create view viewname as select statement 

10、说明：几个简单的基本的sql语句

11、说明：几个高级查询运算词

12、内连接

13、说明：使用外连接 

14、分组:Group by:

15、对数据库进行操作：

16.如何修改数据库的名称:

17、去除重复的数据 ：

二、题目演练

         1、请你找出所有员工具体的薪水salary情况，对于相同的薪水只显示一次,并按照逆序显示（考察order by、排序倒序desc、distinct）

2、请你找出所有非部门领导的员工emp_no（考察联表、in和not in)

3、获取所有的员工和员工对应的经理，如果员工本身是经理的话则不显示(as , inner join on)

4、统计 access_log 各个 site_id 的访问量(as , groub by)

---

一、基础

mysql执行顺序

1. from
2. on
3. join
4. where
5. group by  分组
6. with
7. having
8. select  
9. distinct 去重
10. order by  排序
11. limit  分页 2,1的意思检索第二行后的一行，即第三行

1、说明：创建数据库

CREATE DATABASE database-name 

2、说明：删除数据库

 drop database dbname

3、说明：创建新表

 create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)

 

4、根据已有的表创建新表： 

A：create table tab_new like tab_old (使用旧表创建新表)
B：create table tab_new as select col1,col2… from tab_old definition only

5、说明：删除新表

drop table tabname 

6、说明：增加一个列

Alter table tabname add column col type
注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。

7、说明：添加主键： Alter table tabname add primary key(col) 

说明：删除主键： Alter table tabname drop primary key(col) 

8、说明：创建索引：create [unique] index idxname on tabname(col….) 

 删除索引：drop index idxname
     注：索引是不可更改的，想更改必须删除重新建。

9、说明：创建视图：create view viewname as select statement 

删除视图：drop view viewname

10、说明：几个简单的基本的sql语句

选择：select * from table1 where 范围 

插入：insert into table1(field1,field2) values(value1,value2)

 

去重：A: select distinct salary from salaries order by salary desc

                B:SELECT salary FROM salaries  group by salary  order by salary desc

               distinct是将所有查询的字段进行对比去重，所有字段都完全相同才会去重

               distinct 必须放在查询字段开头进行查询

               group by 根据字段进行去重，字段相同就会去重

删除：delete from table1 where 范围

更新：update table1 set field1=value1 where 范围

查找：select * from table1 where field1 like ’%value1%’ ---like的语法很精妙，查资料!

               
排序：select * from table1 order by field1,field2 [desc]

请你查找employees里入职员工时间排名倒数第三的员工所有信息

SELECT * FROM employees
  ORDER BY hire_date DESC
  LIMIT 2,1

   LIMIT常用来做分页，参数2,1的意思检索第二行后的一行，返回第三行数据。3,2的意思是检索第3行后的两行，则返回的是第4第5行的数据。如果只有一个参数 5，则是表示返回前5行数据。

 总数：select count as totalcount from table1

求和：select sum(field1) as sumvalue from table1

平均：select avg(field1) as avgvalue from table1

最大：select max(field1) as maxvalue from table1

例子：请你查找employees里最晚入职员工的所有信息

SELECT * from employees
WHERE hire_date = (
  SELECT MAX(hire_date)
    FROM employees
);

MAX()可以返回最大的值和最近的时间（时间戳最大）

最小：select min(field1) as minvalue from table1

    

取别名：select de.emp_no,dm.emp_no as manager_no  from dept_emp de,dept_manager dm  where de.dept_no=dm.dept_no

      先执行from语句，将dept_emp取别名为de,将dept_manager 取别名为dm;

     而AS关键字是将查询的两个结果做组成一个表

11、说明：几个高级查询运算词

A： UNION 运算符 
UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 

联合查询结果是将多个select语句的查询结果合并到一块因为在某种情况下需要将几个select语句查询的结果合并起来显示。

11.1、其中union选项有两个选项可选
all：表示无论重复都输出
distinct: 去重(整个重复)（默认的）

下面就是将两个查询结果并在一起（虽然这个两个查询相同）

11.2、联合查询只要求字段一样, 跟数据类型和顺序无关

11.3、order by不能直接出现在union的子句中，但是可以出现在子句的子句中。

B： EXCEPT 运算符 
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。 

C： INTERSECT 运算符
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL)，不消除重复行。 
注：使用运算词的几个查询结果行必须是一致的。 

 

12、内连接

              内连接结果是：

如上图，将不匹配的dirver那列去掉了。（将两个表中匹配的保留，其他的去掉）

 

13、说明：使用外连接 

A、left （outer） join： 左连接  包含所有左边表中的记录，甚至是右边表中没有和他匹配的记录。
左外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所有行。 
SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

     左连接的结果
B：right （outer） join: 右连接  包含所有右边表中的记录，甚至是右边表中没有和他匹配的记录。
右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。 

    右连接的结果是：
 

C：full/cross （outer） join：
全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。

14、分组:Group by:

一张表，一旦分组 完成后，查询后只能得到组相关的信息。
组相关的信息：（统计信息） count,sum,max,min,avg  分组的标准)
    在SQLServer中分组时：不能以text,ntext,image类型的字段作为分组依据
在selecte统计函数中的字段，不能和普通的字段放在一起；

SELECT site_id, SUM(access_log.count) AS nums
FROM access_log GROUP BY site_id;

15、对数据库进行操作：

分离数据库： sp_detach_db; 附加数据库：sp_attach_db 后接表明，附加需要完整的路径名

16.如何修改数据库的名称:

sp_renamedb 'old_name', 'new_name' 

 

17、去除重复的数据 ：

select distinct name from table

 

二、题目演练

1、请你找出所有员工具体的薪水salary情况，对于相同的薪水只显示一次,并按照逆序显示（考察order by、排序倒序desc、distinct）

薪水表，salaries简况如下:

方法一：通过group by

SELECT salary
FROM salaries
group by salary
order by salary desc

方法二：通过distinct

select distinct salary from salaries order by salary desc

 

2、请你找出所有非部门领导的员工emp_no（考察联表、in和not in)

有一个员工表employees简况如下

有一个部门领导表dept_manager简况如下

代码：

select emp_no
from employees
where emp_no not in (select emp_no from dept_manager)

3、获取所有的员工和员工对应的经理，如果员工本身是经理的话则不显示(as , inner join on)

有一个员工表dept_emp简况如下: 第一行表示为员工编号为10001的部门是d001部门。

有一个部门经理表dept_manager简况如下: 第一行表示为d001部门的经理是编号为10002的员工。

方法一：WHERE 多条件限制

select de.emp_no, dm.emp_no as manager_no
from dept_emp de,dept_manager dm
where de.dept_no = dm.dept_no
and de.emp_no!=dm.emp_no
and dm.to_date="9999-01-01"
and de.to_date='9999-01-01'

方法二：内连接+WHERE 多条件限制

select de.emp_no,dm.emp_no as manager_no
from dept_emp de inner join dept_manager dm on de.dept_no=dm.dept_no
and  de.emp_no!=dm.emp_no
and dm.to_date='9999-01-01'
and de.to_date='9999-01-01'

inner join  on的意思是通过on后面的条件进行内连接

4、统计 access_log 各个 site_id 的访问量(as , groub by)

下面是access_log表

SELECT site_id, SUM(access_log.count) AS nums
FROM access_log GROUP BY site_id;

结果：

 

5、查找总访问量大于 200 的网站。（having)

SELECT Websites.name, Websites.url, SUM(access_log.count) AS nums FROM (access_log
INNER JOIN Websites
ON access_log.site_id=Websites.id)
GROUP BY Websites.name
HAVING SUM(access_log.count) > 200;