MySQL Basic Learning

MySQL核心技术基础篇

一、数据库相关概念

1、DB：数据库，保存一组有组织的数据的容器
2、DBMS：数据库管理系统，又称为数据库软件（产品），用于管理DB中的数据
3、SQL:结构化查询语言，用于和DBMS通信的语言

二、数据库存储数据的特点

1、将数据放到表中，表再放到库中
2、一个数据库中可以有多个表，每个表都有一个的名字，用来标识自己。表名具有唯一性。
3、表具有一些特性，这些特性定义了数据在表中如何存储，类似java中 “类”的设计。
4、表由列组成，我们也称为字段。所有表都是由一个或多个列组成的，每一列类似java 中的”属性”
5、表中的数据是按行存储的，每一行类似于java中的“对象”。

三、初始化MySQL

MySQL产品的介绍和安装

熟稔于心，不再赘述

MySQL服务的启动和停止

1.方式一：计算机——右击管理——服务
2.方式二：通过管理员身份运行

	net start 服务名（启动服务）
	net stop 服务名（停止服务）

MySQL服务的登录和退出

1.方式一：通过mysql自带的客户端，只限于root用户。
2.方式二：通过windows自带的客户端

	登录：mysql 【-h主机名 -P端口号 】-u用户名 -p密码 。简写：mysql -uroot -proot
	退出：exit或ctrl+C

MySQL的常见命令（ps：命令结尾用分号）

1.查看当前所有的数据库：show databases;
2.打开指定的库：use 库名
3.查看当前库的所有表：show tables;
4.查看其它库的所有表：show tables from 库名;
5.创建表

create table 表名(
		列名 列类型,
		列名 列类型，
		。。。
	);

6.查看表结构：desc 表名
7.查看服务器的版本

方式一：登录到mysql服务端：select version();
方式二：没有登录到mysql服务端：mysql --version 或 mysql --V

MySQL的语法规范

1.不区分大小写,但建议关键字大写，表名、列名小写
2.每条命令最好用分号结尾
3.每条命令根据需要，可以进行缩进或换行
4.注释

	单行注释：#注释文字
	单行注释：-- 注释文字
	多行注释：/* 注释文字  */

SQL的语言分类

1.DQL（Data Query Language）：数据查询语言 select
2.DML(Data Manipulate Language):数据操作语言 insert 、update、delete
3.DDL（Data Define Languge）：数据定义语言 create、drop、alter
4.TCL（Transaction Control Language）：事务控制语言 commit、rollback

SQL的常见命令

1.show databases；查看所有的数据库
2.use 库名；打开指定的库
3.show tables ; 显示库中的所有表
4.show tables from 库名;显示指定库中的所有表
5.create table 表名( 字段名字段类型, 字段名字段类型 ); 创建表
6.desc 表名; 查看指定表的结构
7.select * from 表名;显示表中的所有数据

五、DQL语言的学习

进阶1：基础查询

1.语法：SELECT 要查询的东西【FROM 表名】; 类似于Java中 :System.out.println(要打印的东西);
2.特点：
①通过select查询完的结果，是一个虚拟的表格，不是真实存在
② 要查询的东西可以是常量值、可以是表达式、可以是字段、可以是函数

进阶2：条件查询

1.条件查询：根据条件过滤原始表的数据，查询到想要的数据
2.语法：select 要查询的字段|表达式|常量值|函数 from 表 where 条件 ;
3.分类：

一、条件表达式示例：salary>10000 条件运算符：> < >= <= = != <>
二、逻辑表达式示例：salary>10000 && salary<20000 逻辑运算符：
and（&&）:两个条件如果同时成立，结果为true，否则为false
or(||)：两个条件只要有一个成立，结果为true，否则为false
not(!)：如果条件成立，则not后为false，否则为true
三、模糊查询示例：last_name like 'a%'

进阶3：排序查询（ DESC降序；ASC升序；默认升序）

1.语法：select 要查询的东西 from 表 where 条件 order by 排序的字段|表达式|函数|别名【asc|desc】

如：SELECT * FROM employees ORDER BY salary DESC;

进阶4：常见函数

一、单行函数
1、字符函数
	concat拼接：SELECT CONCAT(first_name,last_name) AS 姓名 FROM employees;
	substr截取子串：SELECT SUBSTR('哈哈，我是平凡之路',4);
	upper转换成大写：SELECT UPPER('guet');
	lower转换成小写：SELECT LOWER('GUET');
	trim去前后指定的空格和字符：SELECT TRIM('  哈哈  123 ');
	ltrim去左边空格：SELECT LTRIM('  guet   ');
	rtrim去右边空格：SELECT LTRIM('  guet   ');
	replace替换：SELECT REPLACE('abc','a','x');
	lpad左填充：SELECT LPAD('abc',5,'xx');
	rpad右填充：SELECT RPAD('abc',5,'xx');
	instr返回子串第一次出现的索引
	length 获取字节个数：SELECT  LENGTH('guet');
2、数学函数
	round 四舍五入：SELECT	LENGTH('guet');
	rand 随机数：SELECT RAND();
	floor向下取整：SELECT FLOOR(1.5);
	ceil向上取整：SELECT CEIL(1.5);
	mod取余：SELECT MOD(5,2);
	truncate截断：SELECT TRUNCATE(1.23456,3);
3、日期函数
	now当前系统日期+时间：SELECT NOW();
	curdate当前系统日期：SELECT CURDATE();
	curtime当前系统时间：SELECT CURTIME();
	str_to_date 将字符转换成日期：SELECT STR_TO_DATE("August 10 2017", "%M %d %Y");
	date_format将日期转换成字符：SELECT TIME_FORMAT('11:11:11','%r');
4、流程控制函数
	if 处理双分支
	case语句 处理多分支
		情况1：处理等值判断
		情况2：处理条件判断
5、其他函数
	version版本
	database当前库
	user当前连接用户

二、分组函数

        sum 求和
        max 最大值
        min 最小值
        avg 平均值
        count 计数
        特点：
            1、以上五个分组函数都忽略null值，除了count(*)
            2、sum和avg一般用于处理数值型；max、min、count可以处理任何数据类型
            3、都可以搭配distinct使用，用于统计去重后的结果
            4、count的参数可以支持：字段、*、常量值，一般放1 ； 建议使用 count(*)

进阶5：分组查询

1.语法：select 查询的字段，分组函数 from 表 group by 分组的字段

如：查询每个工种的最高工资：SELECT MAX(salary),job_id FROM employees GROUP BY job_id;
如：查询每个位置上的部门个数：SELECT COUNT(*) ,location_id FROM departments GROUP BY location_id;

2.特点：

可以按单个字段分组
和分组函数一同查询的字段最好是分组后的字段
分组筛选
针对的表位置关键字
分组前筛选：原始表 group by的前面 where
分组后筛选：分组后的结果集 group by的后面 having
可以按多个字段分组，字段之间用逗号隔开
可以支持排序
having后可以支持别名

#添加筛选条件 （根据原始表筛选，用 WHERE ）
#案例1：查询邮箱中包含a字符的，每个部门的平均工资
SELECT AVG(salary) ,department_id FROM employees WHERE email LIKE '%a%' GROUP BY department_id;
#案例2：查询有奖金的每个领导手下员工的最高工资
SELECT MAX(salary) ,manager_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY manager_id;

#添加复杂的筛选条件 （根据新的结果集筛选，用 HAVING）
#案例1：查询那个部门的员工个数>2  
SELECT COUNT(*),department_id FROM employees GROUP BY department_id HAVING COUNT(*)>2;

#案例2：查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
#①查询每个工种有奖金的员工的最高工资
SELECT MAX(salary),job_id 
FROM employees 
WHERE commission_pct IS NOT NULL 
GROUP BY job_id;
#②根据①的结果继续筛选，最高工资大于12000
SELECT MAX(salary),job_id 
FROM employees 
WHERE commission_pct IS NOT NULL 
GROUP BY job_id 
HAVING MAX(salary)>12000

#案例3：查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个，以及最低工资
#①查询领导编号>102的每个领导手下的最低工资
SELECT MIN(salary),manager_id FROM  employees WHERE manager_id>102   GROUP BY manager_id;
#②最低工资>5000
SELECT MIN(salary),manager_id FROM  employees WHERE manager_id>102   GROUP BY manager_id HAVING MIN(salary)>5000;

#按表达式分组
#案例1：按员工姓名的长度分组，查询每一组的员工个数>5的有哪些
#①查询每个长度的员工个数
SELECT COUNT(*),LENGTH(last_name) len_name FROM employees GROUP BY LENGTH(last_name); 
#②添加筛选条件
SELECT COUNT(*) ,LENGTH(last_name) len_name FROM employees GROUP BY LENGTH(last_name) HAVING COUNT(*)>5;

进阶6：多表连接查询

1.笛卡尔乘积：如果连接条件省略或无效则会出现
2.解决办法：添加上连接条件

一、sql92 传统模式下的连接：等值连接——非等值连接（推荐使用sql99）

1.等值连接的结果 = 多个表的交集
2.n表连接，至少需要n-1个连接条件
3.多个表不分主次，没有顺序要求
4.一般为表起别名，提高阅读性和性能

#案例1：查询员工名和对应的部门名
SELECT last_name,department_name FROM employees,departments WHERE employees.`department_id` = departments.`department_id`

#2.为表起别名(如果为表起了别名，则不能用表名起限定)
#案例2：查询员工名、工种号、工种名
SELECT last_name,job_title,e.`job_id` FROM employees AS e,jobs WHERE e.`job_id` = jobs.`job_id`

#3.可以加筛选吗？
#案例3：查询有奖金的员工名、部门名
SELECT last_name,department_name,commission_pct FROM employees e,departments d WHERE e.`department_id` = d.`department_id` 
AND e.`commission_pct` IS NOT NULL

#案例4：查询城市名中第二个字符为O的部门名和城市名
SELECT department_name,city FROM departments d,locations l WHERE d.location_id = l.location_id AND l.city LIKE '%_o%'

#5.可以加分组吗？
#案例5：查询每个城市的部门个数
SELECT COUNT(*) 个数,city FROM departments d,locations l WHERE d.location_id = l.location_id GROUP BY city

#案例6：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
#①查询每个部门的部门名和部门的领导编号
SELECT department_name,d.manager_id,d.department_id FROM departments d,employees e
WHERE d.`department_id` = e.`department_id` GROUP BY d.department_id
#②有奖金
SELECT department_name,d.manager_id,MIN(e.`salary`)FROM departments d,employees e
WHERE d.`department_id` = e.`department_id` AND e.`commission_pct` IS NOT NULL GROUP BY department_name

#7.可以实现三表连续？
#案例：查询员工名、部门名和所在城市
SELECT last_name,department_name,city FROM employees e,departments d,locations l WHERE e.`department_id` = d.`department_id` 
AND d.`location_id` = l.`location_id`;

非等值连接

#案例1：查询员工的工资和工资级别   
SELECT salary,grade_level FROM employees e,job_grades g WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`;

自连接

#案例：查询 员工名和上级的名称
 SELECT e.employee_id,e.last_name ,m.employee_id ,m.last_name FROM employees e,employees m WHERE e.`manager_id` = m.`employee_id`;

二、sql99语法：通过join关键字实现连接

含义：1999年推出的sql语法
支持：等值连接、非等值连接（内连接）外连接交叉连接
语法：select 字段，...from 表1

	【inner 内连接|left outer  左外连接|right outer  右外连接|cross  交叉连接】join 表2 on  连接条件
	【inner|left outer|right outer|cross】join 表3 on  连接条件
	【where 筛选条件】
	【group by 分组字段】
	【having 分组后的筛选条件】
	【order by 排序的字段或表达式】

好处：语句上，连接条件和筛选条件实现了分离，简洁明了！

（一）内连接

1.等值连接

#1.等值连接
#案例1：查询员工名、部门名
SELECT last_name,department_name 
FROM employees e 
INNER JOIN departments d 
ON e.`department_id` = d.`department_id`

#案例2：查询名字中包含e的员工名和工种名（筛选）
SELECT last_name,job_title FROM employees e INNER JOIN jobs j ON e.`job_id` = j.`job_id` WHERE last_name LIKE '%a%';

#案例3：查询部门个数>3的城市名和部门个数。（分组+筛选）
#①查询每个城市的部门个数
SELECT COUNT(*) 部门个数 FROM departments d INNER JOIN locations l ON d.`location_id` = l.`location_id` GROUP BY l.`city`
#②再①的基础上部门个数>3
SELECT COUNT(*) 部门个数 FROM departments d INNER JOIN locations l ON d.`location_id` = l.`location_id` GROUP BY l.`city` HAVING COUNT(*)>3

#案例4：查询那个部门的部门员工个数>3的部门名和员工个数，并按个数降序
#①查询部门名和员工个数
SELECT department_name ,COUNT(*) 员工个数 FROM departments d INNER JOIN employees e ON d.`department_id` = e.`department_id` GROUP BY department_name;
#②在①的结果集上  部门的部门员工个数>3 降序
SELECT department_name ,COUNT(*) 员工个数 FROM departments d INNER JOIN employees e ON d.`department_id` = e.`department_id` GROUP BY department_name 
HAVING COUNT(*)>3 ORDER BY COUNT(*) DESC;

#案例5：查询员工名、部门名、工种名，并按部门名降序(三表连接)
SELECT last_name,department_name,job_title FROM employees e INNER JOIN departments d  ON e.`department_id` = d.`department_id`
INNER JOIN jobs j ON j.`job_id` = e.`job_id` ORDER BY d.department_id DESC;

2.非等值连接

#案例1：查询员工的工资级别
SELECT last_name,salary,grade_level FROM employees e INNER JOIN job_grades  j ON e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`

#案例2：查询每个工资级别的个数，并且按工资级别降序
SELECT COUNT(*),j.`grade_level` FROM employees e INNER JOIN job_grades j ON e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal` GROUP BY j.`grade_level`
ORDER BY j.`grade_level` DESC;

3.自连接

#案例1：查询员工的名字，上级的名字
SELECT e.last_name ,m.last_name FROM employees e INNER JOIN employees m ON e.`manager_id` = m.`employee_id`;

（二）外连接

应用场景：用于查询一个表中有，另一个表没有的记录
特点：
- 外连接查询结果为主表中的所有记录。如果从表中有和他匹配的，显示匹配的值。没有匹配的，则显示null 。外连接的查询结果=内连接结果+主表中有而从表中没有的记录。
- 左外连接，left左边的是主表。右外连接，right join 右边的是主表
- 左外和右外交换两个表的顺序，可以实现同样的效果

#引入：查询没有男朋友的女神名 (左外连接)
SELECT  b.name FROM beauty b  LEFT  OUTER JOIN boys bo  ON  b.`boyfriend_id` = bo.`id` WHERE bo.id IS  NULL
#案例：查询那个部门没有员工
#左外 主表部门
SELECT d.* ,e.employee_id FROM departments d LEFT OUTER JOIN employees e ON d.department_id = e.employee_id WHERE e.employee_id IS NULL;
#右外
SELECT d.*,e.employee_id FROM employees e RIGHT OUTER JOIN departments d ON d.`department_id` = e.`department_id` 
WHERE e.`employee_id` IS NULL;

进阶7：子查询

1.含义：一条查询语句中又嵌套了另一条完整的select语句，其中被嵌套的select语句，称为子查询或内查询在外面的查询语句，称为主查询或外查询
2.特点：

子查询都放在小括号内
子查询可以放在from后面、select后面、where后面、having后面，但一般放在条件的右侧
子查询优先于主查询执行，主查询使用了子查询的执行结果
子查询根据查询结果的行数不同分为以下两类：
- 单行子查询，结果集只有一行，一般搭配单行操作符使用：> < = <> >= <=
  非法使用子查询的情况：
  a、子查询的结果为一组值
  b、子查询的结果为空
- 多行子查询
  结果集有多行，一般搭配多行操作符使用：any、all、in、not in。in：属于子查询结果中的任意一个就行。any和all往往可以用其他查询代替

（一） where 或 having 后面 ※

标量子查询（单行子查询）

#标量子查询（单行子查询）
#案例一：查询工资大于Abel的员工
#①查询Abel工资
SELECT salary FROM employees WHERE last_name = 'Abel'
#②查询员工的信息，满足salary>①结果
SELECT * 
FROM employees 
WHERE salary > (
	SELECT salary 
	FROM employees 
	WHERE last_name = 'Abel'
);

#案例2：查询job_id 与141号员工相同，salary 比 143号员工多的员工姓名，job_id和工资
#①查询141号员工的job_id
SELECT job_id FROM employees WHERE employee_id = 141
#②查询143号员工的salary
SELECT salary FROM employees WHERE employee_id = 143
#③查询员工的姓名，job_id 和工资，要求job_id=①并且salary>②
SELECT last_name,job_id,salary 
FROM employees 
WHERE job_id = (
	SELECT job_id 
	FROM employees 
	WHERE employee_id = 141
)
AND salary > (
	SELECT salary 
	FROM employees 
	WHERE employee_id = 143
)

#案例3：返回公司工资最少的员工的last_name,job_id和salary
#①查询最小工资
SELECT MIN(salary) FROM employees
#②查询last_name,job_id和salary
SELECT last_name,job_id,salary 
FROM  employees
WHERE salary = (
	SELECT MIN(salary) 
	FROM employees
)

#案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资
#① 查询10号部门的最低工资
SELECT MIN(salary) FROM employees WHERE department_id = 50
#②查询每个部门的最低工资
SELECT MIN(salary),department_id FROM employees GROUP BY department_id
#③筛选②满足min（salary）> ①
SELECT MIN(salary),department_id 
FROM employees 
GROUP BY department_id 
HAVING MIN(salary) > (
	SELECT MIN(salary) 
	FROM employees 
	WHERE department_id = 50
)

列子查询（多行子查询）

#2.列子查询（多行子查询）
#案例1：返回location_id 是 1400 或 1700的部门中的所有员工姓名
#①查询location_id是1400或1700的部门编号
SELECT DISTINCT department_id
FROM departments
WHERE location_id 
IN (1400,1700)
#②查询员工姓名，部门id是①
SELECT  last_name 
FROM employees
WHERE department_id 
IN (
	SELECT  DISTINCT department_id
	FROM departments
	WHERE location_id 
	IN (1400,1700)
)

#案例2：返回其他工种中job_id为'IT_PROG'部门任一工资低的员工的员工号、姓名、job_id 以及 salary
#①查询job_id为'IT_PROG'部门任一工资
SELECT DISTINCT salary 
FROM employees e 
WHERE  e.`job_id` = 'IT_PROG'
#②查询员工号、姓名、job_id 以及 salary，salary < ①中任意一个
SELECT last_name,employee_id,salary
FROM employees 
WHERE salary < ANY(
	SELECT DISTINCT salary 
	FROM employees e 
	WHERE  e.`job_id` = 'IT_PROG'
) AND job_id <> 'IT_PROG'

#案例2：返回其他部门中比job_id为'IT_PROG'部门所有工资都低的员工 的员工号、姓名、job_id 以及salary
SELECT last_name,employee_id,salary
FROM employees 
WHERE salary < ALL(
    SELECT DISTINCT salary 
    FROM employees e 
    WHERE  e.`job_id` = 'IT_PROG'
) AND job_id <> 'IT_PROG'

行子查询（多行多列用的较少操作符一样）

#案例1：查询员工编号最小并且工资最多的员工信息
SELECT * 
FROM employees
WHERE (employee_id,salary) = (
	SELECT	MIN(employee_id),MAX(salary)
	FROM employees
)

（二） select 后面的子查询 [可以用其他方式代替]

仅仅支持标量子查询

#二、select 后面
#案例1：查询每个部门的员工个数
SELECT d.*,(
	SELECT COUNT(*)
	FROM employees e
	WHERE d.`department_id` = e.`department_id`
) 个数
FROM departments d

#案例2：查询员工号=102的部门名
SELECT(
	SELECT department_name 
	FROM departments d
	INNER JOIN employees e
	ON d.`department_id` = e.`department_id`
	WHERE e.employee_id = 102
) 部门名

（三） from 后面的子查询

将子查询结果充当一张表，要求必须起别名

#案例1：查询每个部门的平均工资的工资等级
#①查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
#②链接1的结果集和job_gredes表，筛选条件平均工资正在。。中间
SELECT ag_dep.*,g.grade_level
FROM (
	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id
) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal

（四） exists后面的子查询使用

语法：EXISTS（完整的查询语句）；结果：0或1

#四、 exists后面的子查询使用（相关子查询）
SELECT EXISTS(
	SELECT employee_id FROM employees WHERE salary = 300000
)

进阶8：分页查询

应用场景：实际的web项目中需要根据用户的需求提交对应的分页查询的sql语句
语法：

	select 字段|表达式,...
	from 表
	【where 条件】
	【group by 分组字段】
	【having 条件】
	【order by 排序的字段】
	limit 【起始的条目索引，】条目数;

特点：
- 1.起始条目索引从0开始
- 2.limit子句放在查询语句的最后
- 3.公式：select * from 表 limit （page-1）*sizePerPage,sizePerPage
  假如:
  每页显示条目数sizePerPage
  要显示的页数 page

#案例1：查询前5条员工信息
SELECT * FROM employees LIMIT 0,5;
#案例2：查询11-25条
SELECT * FROM employees LIMIT 10,15
#案例3：有奖金的员工信息，并且工资较高的前10名
SELECT
  *
FROM
  employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 10

进阶9：联合查询

引入：union 联合、合并
语法：

	select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】
	select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】
	select 字段|常量|表达式|函数 【from 表】 【where 条件】 union  【all】
	.....
	select 字段|常量|表达式|函数 【from 表】 【where 条件】

特点：
- 1、多条查询语句的查询的列数必须是一致的
- 2、多条查询语句的查询的列的类型几乎相同
- 3、union代表去重，union all代表不去重

#语法：
SELECT 查询列表      7
FROM 表1 别名        1
连接类型 JOIN 表2    2
ON 连接条件          3
WHERE 筛选           4
GROUP BY 分组列表    5
HAVING 筛选          6
ORDER BY 排序列表    8
LIMIT 起始条目索引，条目数  9

六、DML语言

一、插入语句

1.语法：

	insert into 表名(字段名，...)
	values(值1，...);

2.特点：

	1、字段类型和值类型一致或兼容，而且一一对应
	2、可以为空的字段，可以不用插入值，或用null填充
	3、不可以为空的字段，必须插入值
	4、字段个数和值的个数必须一致
	5、字段可以省略，但默认所有字段，并且顺序和表中的存储顺序一致

二、修改语句

1.修改单表语法：

	update 表名 set 字段=新值,字段=新值
	【where 条件】

2.修改多表语法：

	update 表1 别名1,表2 别名2
	set 字段=新值，字段=新值
	where 连接条件
	and 筛选条件

#案例1：修改张无忌的女朋友的手机号为114
UPDATE
  boys bo
  INNER JOIN beauty b
    ON bo.`id` = b.`boyfriend_id` SET b.`phone` = '114'
WHERE bo.`boyName` = '张无忌'

三、删除语句

1.方式1：delete语句

单表的删除： ★

	delete from 表名 【where 筛选条件】

多表的删除：

	delete 别名1，别名2
	from 表1 别名1，表2 别名2
	where 连接条件
	and 筛选条件;

2.方式2：truncate语句

	truncate table 表名

3.两种方式的区别【面试题】

	#1.truncate不能加where条件，而delete可以加where条件
	#2.truncate的效率高一丢丢
	#3.truncate 删除带自增长的列的表后，如果再插入数据，数据从1开始
	#delete 删除带自增长列的表后，如果再插入数据，数据从上一次的断点处开始
	#4.truncate删除不能回滚，delete删除可以回滚

七、DDL语句

一、库和表的管理

库的管理：

一、创建库
create database 库名
二、删除库
drop database 库名

表的管理：
#1.创建表

CREATE TABLE IF NOT EXISTS stuinfo(
	stuId INT,
	stuName VARCHAR(20),
	gender CHAR,
	bornDate DATETIME	
);

DESC studentinfo;
#2.修改表 alter
语法：ALTER TABLE 表名 ADD|MODIFY|DROP|CHANGE COLUMN 字段名 【字段类型】;

#①修改字段名

ALTER TABLE studentinfo CHANGE  COLUMN sex gender CHAR;

#②修改表名
ALTER TABLE stuinfo RENAME [TO]  studentinfo;
#③修改字段类型和列级约束
ALTER TABLE studentinfo MODIFY COLUMN borndate DATE ;

#④添加字段
ALTER TABLE studentinfo ADD COLUMN email VARCHAR(20) first;

#⑤删除字段
ALTER TABLE studentinfo DROP COLUMN email;

#3.删除表
DROP TABLE [IF EXISTS] studentinfo;

#4.标的复制
    仅仅复制表的结构：CREATE TABLE copy LIKE book
    复制表的结构+数据：CREATE TABLE  copy2  SELECT * FROM book;;

二、常见的数据型

1.数值型

整型：TINYINT（常用）、SMALLINT、MEDIUMINT、INT、INTEGER、BIGINT（常用）

整型：如果不设置无符号（只支持正数）还是有符号，默认是有符号，如果想设置有符号需要添加 UNSIGNED。如果插入的范围超出了整形的范围，会报错。
如果不设置长度，会有默认的长度。长度代表了显示的最大宽度，如果不够可以用0填充，需要搭配ZEROFILL

小数：浮点型 FLOAT、DOUBLE （不用）；定点型 DEC、DECIMAL （常用）

小数：FLOAT和DOUBLE 容易丢失精度。定点型的精度高，如果要求插入的精度较高，用定点型，如：货币运算

2.字符型

较短的文本：char(M) 最多字符数、varchar(M)；一个A是一个字符，一个汉字也是一个字符
较长的文本：text、blob（较长的二进制数据）

char 代表固定长度的字符；varchar代表可变长度的字符。如存中国，char(10) 开10个字符长度；varchar(10)只开2个

3.日期型

DATE、 DATETIME 、TIMESTAMP、 TIME 、 YE AR

三、常见约束

1.约束定义以及种类

约束：一种限制，用于限制表中的数据，为了保证表中的数据的准确性和可靠性
分类：六大约束：

NOT NULL 非空（用于保证该字段的值不能为空）。比如：姓名、学号
DEFAULT 默认，用于保证该字段有默认值。比如性别
PRIMARY KEY 主键：用于保证该字段的值具有唯一性，并且非空。比如：学号
UNIQUE 唯一，用于保证该字段的值具有唯一性，可以为空。比如：座位号
CHECK 检查约束【MySQL不支持】
FOREIGN KEY 外键，用于限制两个表的关系，用于保证该字段的值必须来只主表的关联列的值。在从表中添加外键约束，用于引用主表中某列的值

添加约束的时机：1.创建表时；2.修改表时

约束的添加分类：

列级约束：六大约束都支持；但是外键约束没有效果
表级约束：除了非空、默认、其他的都支持

CREATE TABLE 表名 (
	字段名 字段类型 列级约束.
	字段名 字段类型,
	表级约束
)

2.创建表时添加约束

添加列级约束：
语法：直接在字段名和类型后面追加约束类型即可；只支持默认、非空、主键、唯一

CREATE TABLE stuinfo(
	id INT PRIMARY KEY,#主键
	stuName VARCHAR(20) NOT NULL, #非空
	gender CHAR(1) CHECK(gender='男' OR gender='女'),#检查约束
	seat INT UNIQUE ,#唯一
	age INT DEFAULT 18 ,#默认约束
	majorId INT  REFERENCES major(id) #外键
);

CREATE TABLE major(
	id INT PRIMARY KEY,
	majorName VARCHAR(20)
)

添加表级约束：

#2.添加表级约束
CREATE TABLE stuinfo(
	id INT,
	stuname VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT ,
	majarid INT,
	
	CONSTRAINT pk  PRIMARY KEY(id),#主键
	CONSTRAINT uq UNIQUE(seat),#唯一键
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majarid) REFERENCES major(id) #外键
)

#通用的写法
CREATE TABLE IF EXISTS stuinfo(
	id INT PRIMARY KEY,
	stuname VARCHAR(20) NOT NULL,
	sex CHAR(1),
	age INT DEFAULT 18,
	seat INT UNIQUE ,
	majorid INT,
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(marjorid) REFERENCES majarid(id)
)

3.主键和唯一的大对比

名称	保证唯一性	是否允许为空	一个表中可以有多少个	是否允许组合
主键	√	×	至多有一个	√，但不推荐
唯一	√	√	可以有多个	√，但不推荐

4.外键

1.要求在从表设置外键关系
2.从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
3.主要的关联列必须是一个key（主键、唯一）
4.插入数据时，先插入主表，再插入从表。如先插入专业，再插入学生。删除时：先删从表，再删主表。

5.修改表时添加约束

1.添加列级约束：ALTER TABLE 表名 MODIFY COLUMN 字段名字段类型新约束;
2.添加表级约束：ALTER TABLE 表名 ADD 【CONSTRAINT 约束名】约束类型(字段名) 【外键的引用】

#修改表时添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL

八、TCL语言的学习

事务：一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行。
案例：转账；张三丰给郭襄转500

UPDATE 表 SET 张三丰的余额=500 WHERE NAME ='张三丰'
UPDATE 表 SET 郭襄的余额=1500 WHERE NAME ='郭襄'

特点

（ACID）
原子性：要么都执行，要么都回滚
一致性：保证数据的状态操作前和操作后保持一致
隔离性：多个事务同时操作相同数据库的同一个数据时，一个事务的执行不受另外一个事务的干扰
持久性：一个事务一旦提交，则数据将持久化到本地，除非其他事务对其进行修改

相关步骤：
1、开启事务
2、编写事务的一组逻辑操作单元（多条sql语句）
3、提交事务或回滚事务

事务的分类：

隐式事务，没有明显的开启和结束事务的标志

比如
insert、update、delete语句本身就是一个事务

显式事务，具有明显的开启和结束事务的标志

	1、开启事务
	取消自动提交事务的功能
	
	2、编写事务的一组逻辑操作单元（多条sql语句）
	insert
	update
	delete
	
	3、提交事务或回滚事务

九、视图

1.应用场景：

多个地方用到同样的查询结果
该查询结果使用的sql语句较复杂

/*
语法；
create view 视图名
as
查询语句
*/

#案例1：查询邮箱中包含a字符的员工名、部门名和工种信息
#①创建视图
CREATE VIEW myv1
AS 
SELECT last_name,department_name,job_title 
FROM employees e 
JOIN departments d ON e.department_id = d.department_id
JOIN jobs j ON j.job_id = e.job_id;
#②使用视图
SELECT * FROM myv1

十、变量

(一)、系统变量

说明：变量由系统提供，不是用户定义，属于服务器层面

全局变量作用域：服务器每次启动将为所有的全局变量赋初始值，若修改值，针对所有连接有效。但是不能跨重启

会话变量作用域：仅仅针对于当前的会话（连接）有效。

使用语法：
1、查看所有的系统变量
查询全局、会话的变量：

SHOW GLOBAL|SESSION VARIABLES

2.查看满足条件的部分系统变量

SHOW GLOBAL  VARIABLES LIKE '%char%'

3、查看指定的某个系统变量的值

SELECT @@global|SESSION . 系统变量名

// 如：SELECT @@global.autocommit;

4.为某个系统变量赋值
方式一：

SET GLOBAL|[SESSION] 系统变量名 = 值;

SET @@global.autocommit = 0

方式二：

SET @@global | [sesssion].系统变量名 = 值;

注意：
如果是全局级别，则需要加global，如果是会话级别，则需要加session，如果不写，则默认session

(二)、自定义变量

1.说明：变量是用户自定义i的，不是由系统提供的。
2.使用步骤：声明、赋值、使用
3.用户变量作用域：针对于当前会话（连接）有效。同于会话变量作用域。

十一、存储过程

1.存储过程和函数：类似Java中的方法。
2.存储过程定义：一组预先编译好的SQL语句的集合，理解成批处理语句。
3.存储过程的好处：提高代码的重用性，简化操作，减少了编译次数并减少了和数据库服务器的连接次数，提高了效率。
4.存储过程的创建

#创建语法
CREATE PROCEDURE 存储过程名字(参数列表)
BEGIN
	存储过程体（一组合法的SQL语句）
END

注意：参数列表包含三部分：1.参数模式、2.参数名、3.参数类型。如：IN stuname VARCHAR(20)

5.参数模式：

IN ：该参数可以作为输入，也就是改参数需要调用方传入值
OUT : 该参数作为输出，也就是该参数可以作为返回值。
INOUT ：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值。

6.如果存储过程体仅仅只有一句话， BEGIN END 可以省略。存储过程体中的每条SQL语句的结尾要求必须加分号。存储过程的结尾可以是用 DELIMITER 重新设置。语法：DELIMITER 结束标记。如：DELIMITER $

7.调用

CALL 存储过程名(实参列表)

十二、函数

十三、流程控制

流程控制结构

顺序结构：程序从上往下依次执行。
分支结构：程序从两条或多条路径中选择一条去执行。
循环结构：程序在满足一定条件的基础上，重复执行一段代码。

1.分支结构
if函数：
功能：实现简单的双分支。

语法：
SELECT IF(表达式1，表达式2，表达式3，)
执行顺序：如果表达式1成立，则if函数返回表达式2的值，否则返回表达式3的值

2.case结构

posted @ 2019-05-28 09:11 HappyFish101 阅读(454) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

平凡之路@2019

Talk is cheap. Show me the code.