MYSQL基本语句

DDL 的数据库操作
1.show databases; //查看数据库

2.use 数据库名称； //查看数据库

3.create database 数据库名称； //创见数据库

4.ALTER DATABASE 数据库名称
CHARACTER SET utf8; //修改数据库的编码为utf8

5.drop database 数据库名称； //删除数据库
DDL
表的操作

1.create table表名（列名 列类型,
列名 列类型,… 列名 列类型）；//创建表

2.show tables； //查看当前数据库中所有的表名称

3.show create table 表名； //查看指定表的创建

4.desc 表名； //查看表结构

5.drop table 表名；//删除表

6.alter table 表名 add（列名 列类型,
列名 列类型,… 列名 列类型）；//添加列

7.alter table 表名 modif 原列名 新列类型； //修改表中列的类型

alter table 表名 change 原列名 新列名 列类型； //修改表中的列名

alter table 表名 drop列名； //删除表中的列

alter table 原表名rename to 新表名； //修改表名

    			插入数据

INSERT INTO 表名(列名1,列名2, …)
VALUES(列值1, 列值2, …);
修改 // 修改数据
（一次可以同时修改表中的多条记录）
UPDATE 表名 SET 列名1=列值1, 列名2=列值2, … [WHERE 条件]
UPDATE stu SET sname=‘赵云’,age=200 WHERE sid BETWEEN 1 AND 3
UPDATE stu SET sname=‘赵云33’,age=200 WHERE sid IN(1,4) //修改sid为1 和 sid为4的记录
DELETE FROM 表名 [WHERE 条件]; // 删除数据

												表中数据类型汇总

CHAR（M）和VARCHAR（M）表示在创建表时指定的存储字符串的最大长度为M。
CHAR类型是固定长度的。取值范围为：0-255之间的任意值，设置成功后长度不变
VARCHAR类型是变长的。取值范围为：0-65535之间的任意值，设置最大值之后，其存储空间根据字符串长度进行变化。

创建字段类型为TINYINT(1)，则该字段可以取到的有符号整型最小值：-128，最大值：127

浮点数分为两种：单精度浮点类型（FLOAT）和双精度浮点类型（DOUBLE）。
定点数类型为：DECIMAL

			基本查询

SELECT * FROM 表名; // 查询所有列
SELECT 列1 [, 列2, … 列N] FROM 表名; //查询指定列
SELECT * FROM 表名 WHERE 列名>=列值 AND 列名<=列值
SELECT * FROM 表名 WHERE 列名 BETWEEN …AND…
SELECT * FROM 表名 WHERE 列名 IS NOT NULL AND
SELECT * FROM 表名 WHERE’z__________’;
如果想查询姓名中以“Z”开头的所有员工（姓名中字符个数任意），就要使用“%”了。
SELECT * FROM 表名 WHERE 列名 LIKE ‘z%’;
SELECT * FROM 表名 WHERE 列名 LIKE ‘%z%’;

SELECT * FROM 表名 ORDER BY 列名 ASC 升序 //升序排序
SELECT * FROM 表名 ORDER BY 列名 DESC 降序 //降序排列

                             统计：     （count里面最好不用*）

SELECT COUNT(列名) FROM 表名 //统计指定列不为NULL的记录行数
SELECT COUNT(列名1) FROM 表名 WHERE 列名1 LIKE ‘%i%’ //统计姓名当中包含I的的人数
SELECT COUNT(*) AS cnt FROM //一般统计时都要as后加一个虚拟字段名称

求和	AVG（计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0）

SELECT AVG(列名) FROM 表名; //列名中的平均值
SELECT MAX(列名) FROM 表名; //列名中的最大值
SELECT MIN(列名) FROM 表名; //列表中的最小值

			分组查询   （COUNT聚合函数）

SELECT 表名, COUNT(＊) FROM 表名 GROUP BY 列名; //分组查询
SELECT 表名, COUNT(＊) FROM 表名 GROUP BY 列名 where加条件
SELECT 表名, COUNT(＊) AS 虚拟字段名称 FROM 表名 WHERE 条件 GROUP BY 列名 HAVING 聚合函数条件
SELECT 表名, COUNT(＊) AS 虚拟字段名称 FROM 表名 WHERE 条件 GROUP BY 列名 HAVING 聚合函数条件 RDER BY 列名 ASC 升序（desc降序）

	增删改查的顺序

select
from
where
group by
having
order by

			limit分页

适用场合：分页查询
问题描述：如果一页记录为10条，希望查看第3页记录应该怎么查呢？
实现： select * from emp limit 20, 10;
分析：
（1）第一页记录起始行为0，一共查询10行，即0～9；
（2）第二页记录起始行为10，一共查询10行，即10～19 ；
（3）第三页记录起始行为20，一共查询10行，即20～29 ；
公式： limit 数字1，数字2
数字1公式：(当前页-1) * 每页记录数
数字2公式：每页记录数

								内连接	

select * from 表1 inner join 表2 on 条件(表1.字段=表2.字段)

                                外连接

左外连接(常用)
优先显示左表全部记录 left join
select * from 表1 left join 表2 on 条件(表1.字段=表2.字段)

右外链接
优先显示右表全部记录 right join
select * from 表1 right join 表2 on 条件(表1.字段=表2.字段)

					外连接 显示左右两个表全部记录

(mysql没有full join,可以有左外连接+右外连接来实现全外连接)
union

select * from 表1 left join 表2 on 条件((表1.字段=表2.字段))
union
select * from 表1 right join 表2 on 条件((表1.字段=表2.字段));

                                      子查询

SELECT * FROM staff
SELECT * FROM department
SELECT * FROM staff INNER JOIN department ON staff.dep_id=department.id
SELECT NAME FROM staff WHERE dep_id = (SELECT id FROM department WHERE NAME = ‘人力资源’)

                                   ***mysql查询去除重复记录***

1：使用distinct
select distinct name from a where statesign=0
查询表a中的name字段，去除重复记录
2:使用group by
select name from a where statesign=0 group by name
查询表a中的name字段，分组，去除重复记录

2、用子查询 找到部门为 销售 和 人力资源 的所有员工的名字
SELECT name FROM staff WHERE dep_id IN(SELECT id FROM department WHERE nameIN (‘人力资源’,‘销售’))
SELECT NAME FROM sataff WHERE dep_id IN (SELECT id FROM department WHERE NAME = ‘人力资源’ OR NAME =‘销售’)
查询平均年龄在28岁以上的部门名
SELECT dep_id,AVG(age)AS aa FROM staff GROUP BY dep_id HAVING aa>28
SELECT dep_id FROM staff GROUP BY dep_id HAVING AVG(age)>28
SELECT NAME FROM department WHERE id IN(SELECT dep_id FROM staff GROUP BY dep_id HAVING AVG(age)>28 )
②查看部门是挖矿小分队员工姓名
SELECT id FROM department WHERE NAME=‘挖矿小分队’
SELECT NAME FROM staff WHERE dep_id IN (SELECT id FROM department WHERE NAME=‘挖矿小分队’)
查看不足1人的部门名(子查询得到的是有人的部门id)
SELECT DISTINCT dep_id FROM staff
SELECT NAME FROM department WHERE id IN(SELECT DISTINCT dep_id FROM staff)
①查询大于所有人平均年龄的员工名与年龄
SELECT AVG(age) FROM staff
SELECT NAME,age FROM staff WHERE age>(SELECT AVG(age) FROM staff)
②查询大于部门内平均年龄的员工名、年龄
SELECT AVG(age) AS age FROM staff GROUP BY age
SELECT * FROM staff WHERE age>id
SELECT NAME,age FROM staff INNER JOIN
(SELECT id, AVG(age)AS ages FROM staff GROUP BY age) AS aa
ON
staff.id=aa.id AND staff.age=aa.ages

                                               **视图**

创建视图
CREATE [OR REPLACE] [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}]
VIEW 视图名[(属性清单)]
AS SELECT语句
[WITH [CASCADED|LOCAL] CHECK OPTION];
对视图进行筛选
SELECT *FROM myv1 WHERE Last_name LIKE ‘%a%’;
修改视图

SELECT id,name FROM tb_user where id in (select id from tb_user);
删除视图
DROP VIEW IF EXISTS view_user;
查看视图
SHOW CREATE VIEW v1;
DESC v1;

 MySQL视图中使用IF和CASE语句

示例：创建MySQL视图中使用IF和CASE语句。
（1）创建员工信息表。
– 判断数据表是否存在，存在则删除
DROP TABLE IF EXISTS tb_staff;

– 创建数据表
CREATE TABLE IF NOT EXISTS tb_staff
(
id INT AUTO_INCREMENT PRIMARY KEY COMMENT ‘编号’,
NAME VARCHAR(50) NOT NULL COMMENT ‘姓名’,
sex INT COMMENT ‘性别（1：男；2：女；）’,
dept_code VARCHAR(10) COMMENT ‘部门编号’,
is_post BIT COMMENT ‘是否在职（0：否；1：是）’
) COMMENT = ‘员工信息表’;
（2）新增员工数据。
– 新增数据
INSERT INTO tb_staff(NAME,sex,dept_code,is_post) VALUES(‘pan_junbiao的博客_01’,1,‘1001’,1);
INSERT INTO tb_staff(NAME,sex,dept_code,is_post) VALUES(‘pan_junbiao的博客_02’,2,‘1002’,1);
INSERT INTO tb_staff(NAME,sex,dept_code,is_post) VALUES(‘pan_junbiao的博客_03’,1,‘1003’,0);
INSERT INTO tb_staff(NAME,sex,dept_code,is_post) VALUES(‘pan_junbiao的博客_04’,1,‘1001’,1);
INSERT INTO tb_staff(NAME,sex,dept_code,is_post) VALUES(‘pan_junbiao的博客_05’,2,‘1008’,1);
INSERT INTO tb_staff(NAME,sex,dept_code,is_post) VALUES(‘pan_junbiao的博客_06’,1,‘1001’,0);
INSERT INTO tb_staff(NAME,sex,dept_code,is_post) VALUES(‘pan_junbiao的博客_07’,2,‘1002’,1);
INSERT INTO tb_staff(NAME,sex,dept_code,is_post) VALUES(‘pan_junbiao的博客_08’,1,‘1003’,0);
INSERT INTO tb_staff(NAME,sex,dept_code,is_post) VALUES(‘pan_junbiao的博客_09’,1,‘1001’,1);
INSERT INTO tb_staff(NAME,sex,dept_code,is_post) VALUES(‘pan_junbiao的博客_10’,2,‘1008’,0);
查询员工信息表结果：
Select * from tb_staff

（3）创建员工视图，在视图中使用IF和CASE语句
– 创建视图
CREATE OR REPLACE VIEW view_staff
AS
SELECT id
,NAME
,IF(sex=1,‘男’,‘女’) AS sex_name
,CASE dept_code
WHEN ‘1001’ THEN ‘研发部’
WHEN ‘1002’ THEN ‘人事部’
WHEN ‘1003’ THEN ‘财务部’
ELSE ‘其他’
END AS dept_name
,IF(is_post,‘在职’,‘离职’) AS is_post_name
FROM tb_staff
;
查询员工视图结果

• 开启事务管理：开启之后，下面的sql语句并不会马上执行并把结果写到表中，而是会写到事务日志中。
• start transaction;
• 回退操作：回退会清掉开始事务管理之后写到事务日志中的内容，即恢复到开启事务管理之前。“回滚”
• 语法：rollback;
• 注意：回退操作只是回退"写"的内容，对于普通的读表select语句不能回退。
• 事务提交：将sql语句的结果写到数据表中。
• 语法：commit;