Day36

Day36

MySQL基础到这里已经学完了，接下来是JDBC了。

/*
常见约束：
说明：用于限制表中字段的数据的，从而进一步保证数据表的数据是一致的、准确的、可靠的
​
NOT NULL 非空：用于限制该字段为必填项
default 默认： 用于限制该字段没有显示插入值，则直接显示默认值
primary key 主键：用于限制该字段值不能重复（具备唯一性），设置为主键列的字段默认不能为空
unique 唯一 ：用于限制该字段值不能重复
对比：     字段是否可以为空    一个表可以有几个
    主键      ×           1个
    唯一      √           n个
​
check 检查 ：用于限制该字段值必须满足指定条件
    语法 check(age between 1 and 100)，mysql不支持这种语法
foreign key 外键：用于限制两个表的关系，要求外键列的值必须来自主表的关联列
        要求：1.主表的关联列和从表的关联列的类型必须一致，意思一样，名称无要求
              2.主表的关联列要求必须为主键。
​
*/
​
#案例：添加约束
DROP TABLE IF EXISTS stuinfo1
CREATE TABLE IF NOT EXISTS stuinfo1(
    stuid INT PRIMARY KEY,#添加主键
    stuname VARCHAR(20) UNIQUE NOT NULL,#添加了唯一+非空
    stugender CHAR(1) DEFAULT '男',#添加了默认值男
    email VARCHAR(20) NOT NULL,
    age INT CHECK(age BETWEEN 0 AND 100)#添加了检查约束，mysql不支持
​
    )
    
#二、修改表
语法： ALTER TABLE 表明 ADD|MODIFY|CHANGE|DROP COLUMN 字段名 字段类型[字段约束]
​
#1.修改表名
ALTER TABLE stuinfo1 RENAME TO students
​
#2.添加字段
ALTER TABLE students ADD COLUMN borndate TIMESTAMP NOT NULL
​
#3.修改字段名
ALTER TABLE students CHANGE COLUMN borndate brithday DATETIME NULL
​
#4.修改字段类型
ALTER TABLE students MODIFY COLUMN brithday TIMESTAMP 
​
#5.删除字段
ALTER TABLE students DROP COLUMN brithday
​
#三、删除表
DROP TABLE IF EXISTS students
​
#四、复制表
CREATE TABLE newtable LIKE stuinfo1
​
#复制表的结构加数据
CREATE TABLE newtable2 SELECT*FROM girls.`admin`
​
#案例：复制employees表中的last_name,department_id,salary字段复制到新表emp中，但不复制数据
CREATE TABLE emp1 
SELECT last_name,department_id,salary
FROM myemployees.`employees`
WHERE 1=2
​
#测试
#1.使用分页查询，查询员工信息表中部门为50号的工资最低的五名员工信息
SELECT *
FROM employees
WHERE department_id =50
ORDER BY salary ASC
LIMIT 0,5
​
#2.使用子查询实现城市为Toroto的，且工资>10000的员工姓名
#方式一：
SELECT last_name,salary
FROM employees
WHERE salary>10000 AND department_id =(
    SELECT department_id
    FROM departments
    WHERE location_id =(
        SELECT location_id
        FROM locations
        WHERE city = 'Toronto'))
#方式二：
SELECT last_name,salary
FROM employees 
WHERE salary>10000 AND department_id IN(
    SELECT department_id 
    FROM departments d 
    JOIN locations l
    ON d.location_id =l.location_id
    WHERE city ='Toronto'
    )
​
#3.创建qqinfo，里面包含qqid，添加主键约束（昵称nickname），添加唯一约束、邮箱email（添加非空约束），性别gender
CREATE TABLE IF NOT EXISTS qqinfo(
    qqid INT PRIMARY KEY,
    nickname VARCHAR(20) UNIQUE,
    email VARCHAR(20) NOT NULL,
    gender CHAR(1) DEFAULT '男'
    )
    
    
#4.删除表qqinfo
DROP TABLE IF EXISTS qqinfo
​
#5.试写出sql查询语句的定义顺序和执行顺序
​
#定义顺序
SELECT DISTINCT 查询列表
FROM 表明 别名
JOIN 表明 别名
ON 连接条件
WHERE 筛选条件
GROUP BY 分组列表
HAVING 分组后筛选
ORDER BY 排序列表
LIMIT 条目数
​
#执行顺序
FROM 子句
JOIN 子句
ON 子句
WHERE 子句
GROUP BY 子句
HAVING 子句
SELECT 子句
ORDER BY 子句
LIMIT 子句
​
#子查询测试题
​
#3.查询各部门中工资比本部门平均工资高的员工号，姓名和工资
SELECT last_name,salary,employee_id
FROM employees
WHERE department_id IN(
    SELECT department_id 
    FROM employees
    WHERE salary>(
        SELECT AVG(salary)
        
​
#1.各部门的平均工资
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
​
#将结果1，和employees表连接
SELECT employee_id,last_name,salary
FROM employees e
JOIN (
    SELECT AVG(salary) ag,department_id
    FROM employees
    GROUP BY department_id
) dep_ag ON e.department_id =dep_ag.department_id
WHERE e.salary>dep_ag.ag
​
#4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
​
SELECT last_name,employee_id,department_id
FROM employees
WHERE last_name LIKE '%u%'
​
SELECT last_name,employee_id
FROM employees e
WHERE department_id IN (
    SELECT DISTINCT department_id
    FROM employees
    WHERE last_name LIKE '%u%'
    )
​
#查询管理者是K_ing的员工姓名和工资
​
SELECT last_name,manager_id
FROM employees
WHERE last_name ='K_ing'
​
​
SELECT last_name,salary
FROM employees
WHERE manager_id IN (
SELECT manager_id
FROM employees
WHERE last_name ='K_ing'
    )


#3.查询平均工资最低的部门信息和该部门的平均工资
​
#方式1：
SELECT AVG(salary) 平均工资,department_id
FROM employees
GROUP BY department_id
ORDER BY 平均工资 ASC
LIMIT 1
​
SELECT d.*,dep_ag.平均工资
FROM departments d
JOIN(
SELECT AVG(salary) 平均工资,department_id
FROM employees
GROUP BY department_id
ORDER BY 平均工资 ASC
LIMIT 1
    ) dep_ag 
ON d.department_id =dep_ag.department_id
​
#4.各个部门中 最高工资中最低的那个部门的 最低工资是多少
​
SELECT MAX(salary) 最高工资中最低的,department_id
FROM employees
GROUP BY department_id
ORDER BY 最高工资中最低的 ASC
LIMIT 1
​
SELECT MIN(salary),department_id
FROM employees
WHERE department_id =(
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY MAX(salary) ASC
LIMIT 1
    )
​
#DML语言
/*
数据操作语言：
插入：insert
修改：update
删除：delete
​
*/
​
#一、插入语句
#方式一：经典的插入
/*
语法： 插入单行语句
insert into 表名(字段名1,字段名2,...) values(值1,值2,...);

插入多行：
insert into 表名(字段名1,字段名2,...) values(值1,值2,...),(值1,值2,...),(值1,值2,...)
特点
1.字段和值列表一一对应
包含类型、约束等必须匹配
2.数值型的值，不用单引号
  非数值型的值，必须使用单引号
3.字段顺序无要求
​
*/
​
#案例1:要求字段和值列表一一对应，且遵循类型和约束的限制。
INSERT INTO stuinfo1 (stuid,stuname,stugender,email,age) VALUES(1,'梁慧森','男',67036811,21)
​
#案例2：可以为空字段插入
INSERT INTO stuinfo1 (stuid,stuname,stugender,email) VALUES(3,'xixi','男',67036811)
​
#案例3：默认字段如何插入
INSERT INTO stuinfo1 (stuid,stuname,stugender,email) VALUES(4,'xixssssi',DEFAULT,67036811)
​
#案例4：可以省略字段名,默认所有字段。
INSERT INTO stuinfo1 VALUES(8,'冲冲冲','女','11@qq.com',12)
​
​
#如何设置自增长列。
/*
1.自增长列必须设置在一个键上，比如主键或唯一键
2.自增长列要求数据类型为数值型
3.一个表至多有一个自增长列。
*/
INSERT INTO stuinfo1 VALUES(NULL,'小黄','男','123@qq.com',12)
​
SELECT * FROM stuinfo1
​
CREATE TABLE gradeinfo(
    gradeID INT PRIMARY KEY AUTO_INCREMENT,
    gradeName VARCHAR(20)
    )

INSERT INTO gradeinfo VALUES(NULL,'一年级'),(NULL,'二年级'),(NULL,'三年级')
​
/*
方式一：delete
语法：
​
1、单表的删除【★】
delete from 表名 where 筛选条件
​
2、多表的删除【补充】
​
sql92语法：
delete 表1的别名,表2的别名
from 表1 别名,表2 别名
where 连接条件
and 筛选条件;
​
sql99语法：
​
delete 表1的别名,表2的别名
from 表1 别名
inner|left|right join 表2 别名 on 连接条件
where 筛选条件;
*/
​
#案例一：删除姓小的所有信息
DELETE FROM stuinfo1 WHERE stuname LIKE '小%'
​
#案例二：删除表中所有数据
TRUNCATE TABLE stuinfo1 
​
#面试题，delete和truncate区别
1.delete可以添加where条件
  truncate不能添加where条件，一次性清楚所有数据，属于ddl语句，drop掉旧表，重新生成一个表
2.truncate的效率高，直接删除所有，delete是逐行比较删除
3.如果删除带自增长列的表
  使用delete删除后，重新插入数据，记录从断点处开始
  使用truncate删除后，重新插入数据，记录从1开始。
4.delete 删除数据，会返回受影响的行数
  truncate删除数据，不返回受影响的行数
5.delete 删除数据，可以支持事务 回滚
  truncate删除数据，不支持事务回滚
  
  
#TCL
/*
Transaction Control Language 事务控制语言
​
事务：
一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行。
​
案例：转账
​
张三丰  1000
郭襄  1000
​
update 表 set 张三丰的余额=500 where name='张三丰'
意外
update 表 set 郭襄的余额=1500 where name='郭襄'
​
​
事务的特性：
ACID
原子性：一个事务不可再分割，要么都执行要么都不执行
一致性：一个事务执行会使数据从一个一致状态切换到另外一个一致状态
隔离性：一个事务的执行不受其他事务的干扰
持久性：一个事务一旦提交，则会永久的改变数据库的数据.
​
​
事务的创建
隐式事务：事务没有明显的开启和结束的标记
比如insert、update、delete语句
​
delete from 表 where id =1; 这个就是事务
​
显式事务：由多条事务组成，具有明显的开启和结束的标记
前提：必须先设置自动提交功能为禁用
​
*/
#演示事务的使用步骤
​
#1.取消事务自动开启
SET autocommit = 0;
​
#2.开启事务
START TRANSACTION;
​
#3.编写事务的sql语句
UPDATE stuinfo SET balance=balance -1000 WHERE stuid =1;
UPDATE stuinfo SET balance=balance +1000 WHERE stuid =2;
​
#4.结束事务
#提交
COMMIT;
​
#回滚
ROLLBACK;
​
​
CREATE TABLE stuinfo(
    stuid INT PRIMARY KEY,
    stuname VARCHAR(7),
    balance INT)
​
SHOW VARIABLES LIKE '%auto%'

 

明天开始学JDBC