mysql的基本操作语句

-- 查询所有数据库(对创建数据库和建表进行操作)
SHOW DATABASES;
CREATE DATABASE day15;
DROP DATABASE day15;
USE day15;
-- 显示所有的表
 SHOW TABLES;
 -- 创建一个表
 CREATE TABLE student(id INT,sname VARCHAR(20),sage INT);
 -- 查看表的具体内容
 DESC student;
 -- 删除表
 DROP TABLE student;
 -- 添加字段
 ALTER TABLE student ADD servlet VARCHAR(2);
 -- 删除字段
 ALTER TABLE student DROP grade;
 -- 修改字段类型
 ALTER TABLE student MODIFY COLUMN sname VARCHAR(30);
 -- 修改字段名称
 ALTER TABLE student CHANGE sgender gender VARCHAR(2);
 -- 修改表的名称
 ALTER TABLE student RENAME    TO teacher;
 
 -- 对插入数据进行操作（增删改数据）
 -- 1.1增加数据
 -- 查看所有的数据
 SELECT * FROM student;
 -- 插入所有字段，一定依次按顺序插入（不能多或少字段值）
 INSERT INTO student VALUES(2,'赵六',15,'男',44,65);
 -- 插入部分字段
 INSERT INTO student(id,sname)VALUES(2,'李四');
 
 -- 1.2修改数据
 -- 修改所有的数据（建议少用）
 UPDATE student SET gender='女';
 --带条件的修改（推荐使用）
  UPDATE student SET gender='女' WHERE id=1;
  --修改多个字段
  UPDATE student SET java=85,servlet=80 WHERE id=1;
 
  -- 1.3删除数据
  -- 删除所有数据（建议少用）
  DELETE FROM student;
  --带条件的删除
  DELETE FROM student WHERE id=2;
 
  -- 查询数据（重点）
  -- 查询所有列
  SELECT * FROM student;
 -- 查询指定列
 SELECT id,sname,sage FROM student;
 -- 查询时添加常量列
 SELECT id,sname,sage,gender,'java就业班'AS'grade' FROM student;
 -- 查询时合并列（只能合并数值类型的字段）
 SELECT id,sname,(java+servlet)AS '总成绩'FROM student;
 -- 查询时去除重复记录（保留需要看到的数据）
 SELECT DISTINCT id FROM student;
 
 -- 条件查询
 -- 交集（用and，并集用or）
 SELECT * FROM student WHERE id=2 AND sname='王五';
 
 -- 比较条件：>  <  >=   <=  =   <>（不等于） between and (等价于>= 且 <=)
 SELECT *FROM student WHERE servlet>85;
 SELECT *FROM student WHERE java BETWEEN 85 AND 95;-- 包前包后
 SELECT *FROM student WHERE gender<>'男';
 
 -- 判空条件(null 空字符串)：  is null/is not null/=''  /<>''
 -- null vs  空字符串
-- null：表示没有值
-- 空字符串：有值的！
-- 需求： 查询有地址的学生(不包括null和空字符串)
SELECT * FROM student WHERE address IS NOT NULL AND address<>'';

--  模糊条件： like
-- 通常使用以下替换标记：
-- % : 表示任意个字符
-- _ : 表示一个字符
-- 查询姓‘张’的学生
SELECT *FROM student WHERE sname LIKE '张%';
-- 查询姓‘李’且姓名只有两个字的学生
SELECT *FROM student  WHERE sname LIKE '李_';


-- 聚合查询
-- 查询servlet的平均分
SELECT AVG(servlet)AS 'servlet的平均分'FROM student;
-- 查询当前servlet的最高分
SELECT MAX(servlet)AS '最高分' FROM student;
-- 查询当前（servlet）的最低分
SELECT MIN(servlet)AS '最低分' FROM student;
-- 统计当前有多少个学生
SELECT COUNT(*) FROM student;
SELECT COUNT(id) FROM student;
-- 注意：count（）函数统计的数量不包含null的数据
-- 使用count统计表的记录数，要使用不包含null值的字段

-- 分页查询（limit 起始行,查询几行）
-- 起始行从0开始
-- 分页：当前页  每页显示多少条
-- 分页查询当前页的数据的sql: SELECT * FROM student LIMIT (当前页-1)*每页显示多少条,每页显示多少条;
-- 查询第1,2条记录（第一页的数据）
SELECT * FROM student LIMIT 0,2;
-- 查询第3,4条记录(第二页的数据)
SELECT * FROM student LIMIT 2,2;


-- 查询排序（order by ）
-- 语法 ：order by 字段 asc/desc
-- asc: 顺序，正序。数值：递增，字母：自然顺序（a-z）
-- desc: 倒序，反序。数值：递减，字母：自然反序(z-a)
-- 默认情况下，按照插入记录顺序排序
SELECT *FROM student;
-- 按照id进行排序
SELECT *FROM student ORDER BY id ASC;
-- 按照id反序排列
SELECT *FROM student ORDER BY id DESC;
-- 注意：多个排序条件
-- 需求： 按照servlet正序，按照jsp的倒序
SELECT * FROM student ORDER BY servlet ASC,jsp DESC;


-- 分组查询(group by)
-- 需求： 查询男女的人数
-- 预期结果：
 --  男   2
 -- 女   1
 -- 统计每组的人数
 SELECT gender,COUNT(*)AS '数量' FROM student GROUP BY gender;


-- 分组查询后筛选
-- 需求： 查询总人数大于2的性别
-- 1) 查询男女的人数
-- 2）筛选出人数大于2的记录(having)
SELECT gender,COUNT(*) FROM student WHERE GROUP BY gender HAVING COUNT(*)>1;

CREATE DATABASE day16;
USE day16;
-- 2.1 在表中设置默认值，当用户对使用默认值的字段不插入值的时候，就使用默认值。（可以插入null）
CREATE TABLE student(id INT,NAME VARCHAR(20),address VARCHAR(20) DEFAULT '广州天河');
DESC student;
INSERT INTO student(id,NAME,address) VALUES (2,'李四',NULL);
SELECT * FROM student;
DELETE FROM student WHERE id=2;

-- 2.2 非空，作用： 限制字段必须赋值（可以是空不能是NULL）
 DROP TABLE student;
 CREATE TABLE student(id INT,NAME VARCHAR(20),gender VARCHAR(2) NOT NULL);
INSERT INTO student(id,NAME,gender) VALUES (2,'李四','');

-- 2.3唯一，对字段的值不能重复（唯一字段可以插入多个null）
 DROP TABLE student;
 CREATE TABLE student(id INT UNIQUE,NAME VARCHAR(20));
INSERT INTO student(id,NAME) VALUES (2,'王五');

-- 2.4 主键。作用： 非空+唯一，每张表都会设置一个主键字段。用于标记表中的每条记录的唯一性。
-- 建议不要选择表的包含业务含义的字段作为主键，建议给每张表独立设计一个非业务含义的id字段。
 DROP TABLE student;
 CREATE TABLE student(id INT PRIMARY KEY,NAME VARCHAR(20));
SELECT * FROM student;
INSERT INTO student(id,NAME) VALUES(1,'张三');
INSERT INTO student(id,NAME) VALUES(2,'张三');
-- 修改语句
UPDATE student SET NAME='李四' WHERE id=2;
INSERT INTO student(id,NAME) VALUES(1,'李四'); -- 违反唯一约束
INSERT INTO student(NAME) VALUE('李四'); -- 违反非空约束

-- 2.5 自增长。作用：自动递增。
 DROP TABLE student;
 -- INT(4) ZEROFILL id填入四位数，当位数不够时插入0
 CREATE TABLE student(id INT(4) ZEROFILL PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20));
 INSERT INTO student(NAME) VALUES('张三');
 
 
 -- 2.7 外键。约束两种表的数据。解决数据冗余高问题： 独立出一张表。
 -- 部门表（主表）
 CREATE TABLE dept(id INT PRIMARY KEY,deptName VARCHAR(20));
 -- 员工表（副表/从表）
 -- 建表时把部门名称换为部门id，然后声明一个外键约束。
 
 -- 使用级联修改，ON UPDATE CASCADE ON DELETE CASCADE，可以先对主表进行操作修改和删除。
 CREATE TABLE employee(id INT PRIMARY KEY,empName VARCHAR(20),deptid INT,CONSTRAINT emplyee_dept_fk FOREIGN KEY(deptid) REFERENCES dept(id)ON UPDATE CASCADE ON DELETE CASCADE);
 DROP TABLE employee;
SELECT * FROM employee;
SELECT * FROM dept;
INSERT INTO dept(id,deptName) VALUES(4,'总经办') ;
INSERT INTO employee(id,empName,deptid) VALUES(3,'王五',2) ;
UPDATE employee SET deptid=2 WHERE id=1;
DELETE FROM dept WHERE id=1;
-- 1）被约束的表称为副表，约束别人的表称为主表，外键设置在副表上的！！！
-- 2）主表的参考字段通用为主键！
-- 3）添加数据： 先添加主表，再添加副表
-- 4）修改数据： 先修改副表，再修改主表
-- 5）删除数据： 先删除副表，再删除主表


-- 3 关联查询（多表查询）
-- 查询员工及其所在部门（显示员工姓名，部门名称）
-- 3.1 交叉连接查询，不推荐，会产生笛卡尔积
SELECT empName,deptName FROM employee,dept;
-- 查询员工及其所在部门(显示员工姓名，部门名称)
-- 多表查询规则：1）确定查询哪些表   2）确定哪些哪些字段 3）表与表之间连接条件 (规律：连接条件数量是表数量-1)
-- 3.2 内连接查询：只有满足条件的结果才会显示(使用最频繁)
SELECT empName,deptName FROM employee,dept WHERE employee.deptid=dept.id;
-- 内连接查询的另外一种写法
SELECT empName,deptName FROM employee INNER JOIN dept ON employee.deptid=dept.id;

-- 3.3 使用别名
SELECT empName,deptName FROM employee e INNER JOIN dept d ON e.deptid=d.id;

-- 3.4 查询每个部门的员工（左外连接查询，使用左边的表去匹配右边表的数据，符合连接条件的结果则显示，如果不符合连接条件则显示null）
-- 左边的数据一定会显示
SELECT deptName,empName FROM dept d LEFT OUTER JOIN employee e ON e.deptid=d.id;
SELECT * FROM employee;
UPDATE employee SET deptid=NULL WHERE id=1;

-- 3.5自连接查询
-- 需求：查询员工及其上司
SELECT e.empName,b.empName FROM employee e LEFT OUTER JOIN employee b ON e.deptid=b.id;



-- 4.1存储过程语法
DELIMITER $     -- 声明一个存储过程结束符号
CREATE PROCEDURE pro_test()
BEGIN
   -- 可以写多个sql语句
   SELECT * FROM employee;
END $

-- 执行存储过程
CALL pro_test();   -- CALL  存储过程的名称（参数）
-- 参数：
-- IN：   表示输入参数，可以携带数据到存储过程中
-- OUT： 表示输出参数，可以从存储过程中返回结果
-- INOUT： 表示输入输出参数，既可以输入功能，也可以输出功能

-- 4.2  带有输入参数的存储过程
DELIMITER $
CREATE PROCEDURE pro_findByid(IN eid INT)   -- 输入参数
BEGIN
SELECT * FROM employee WHERE id=eid;
END $
-- 调用
CALL pro_findByid(3);

-- 4.3 带有输出参数的存储过程
DELIMITER $
CREATE PROCEDURE pro_testOut(OUT str VARCHAR(20))   -- 输出参数
BEGIN
SET str='hellojava';
END $

-- 调用（变量分为全局变量，会话变量，局部变量，这里使用会话变量）
CALL pro_testOut(@str);
-- 查看变量的值
SELECT @str;

-- 4.4 带有输入输出参数的存储过程
DELIMITER $
CREATE PROCEDURE pro_testInOut(INOUT n INT) -- INOUT： 输入输出参数
BEGIN
-- 查看传入的变量
SELECT n;
SET n=500;
END $

-- 调用
SET @n=10;
CALL pro_testInOut(@n);
-- 查看返回的值
SELECT @n;


-- 带有条件判断的存储过程
-- 需求：输入一个整数，如果1，则返回“星期一”,如果2，返回“星期二”,如果3，返回“星期三”。其他数字，返回“错误输入”;
DELIMITER $
CREATE PROCEDURE pro_testIf(IN num INT,OUT str VARCHAR(20))
BEGIN
IF num=1 THEN SET str='星期一';
ELSEIF num=2 THEN SET str='星期二';
ELSEIF num=3 THEN SET str='星期三';
ELSEIF num=4 THEN SET str='星期四';
ELSEIF num=5 THEN SET str='星期五';
END IF;
END $

-- 调用
CALL pro_testIf(4,@str);
-- 查看结果
SELECT @str;


-- 4.5带有循环功能的存储过程
-- 需求： 输入一个整数，求和。例如，输入100，统计1-100的和
DELIMITER $
CREATE PROCEDURE pro_testWhile(IN num INT,OUT result INT)
BEGIN
-- 定义两个局部变量，一个变量用来保存i，一个变量用来保存结果0
DECLARE i INT DEFAULT 1;
DECLARE vsum INT DEFAULT 0;
WHILE i<=num DO
SET vsum=i+vsum;
SET i=i+1;
END WHILE;
SET result=vsum;
END $

-- 删除一个存储过程
DROP PROCEDURE pro_testWhile;

-- 调用
CALL pro_testWhile(100,@result);
SELECT @result;

-- 4.6使用查询的结果赋值给变量（使用INTO）
DELIMITER $
CREATE PROCEDURE pro_findByid2(IN eid INT,OUT vname VARCHAR(20))
BEGIN
SELECT empName INTO vname FROM employee WHERE id=eid;
END $

-- 调用
CALL pro_findByid2(1,@NAME);
-- 输出查询的结果
SELECT @NAME;




-- 5.0  触发器
-- 当操作了某张表时，希望同时触发一些动作/行为，可以使用触发器完成！！
-- 需求：当向员工表插入一条记录时，希望同时往日志表插入数据。
-- 建立日志表
CREATE TABLE test_log(id INT PRIMARY KEY AUTO_INCREMENT,content VARCHAR(100));
-- 创建触发器
CREATE TRIGGER tri_empAdd AFTER INSERT ON employee FOR EACH ROW
INSERT INTO test_log(content) VALUES('员工表插入了一条记录');

-- 向员工表插入数据
INSERT INTO employee(id,empName,deptid) VALUES(4,'赵六',2);
SELECT * FROM employee;
-- 查看日志表中的内容
SELECT * FROM test_log;

-- 修改触发器
CREATE TRIGGER tri_empUpdate AFTER UPDATE ON employee FOR EACH ROW
INSERT INTO test_log(content) VALUES('员工表修改了一条记录');
-- 删除触发器
DROP TRIGGER tri_empUpdate;
-- 修改员工表数据
UPDATE employee SET empName='joker' WHERE id=4;


-- 6.0权限问题

USE mysql;
SELECT * FROM USER;
SELECT PASSWORD('root');

-- 修改密码
UPDATE USER SET PASSWORD=PASSWORD('123456') WHERE USER='root';

-- 分配权限账户（固定写法）localhost为账户类型（本地或者··）操作哪个数据库的哪张表
-- 注意！！root账户拥有操作数据库的所有权限！！
-- 新建一个用户，并给这个用户分配权限
GRANT SELECT ON day16.employee TO 'eric'@'localhost' IDENTIFIED BY '123456';
GRANT DELETE ON day16.employee TO 'eric'@'localhost' IDENTIFIED BY '123456';
DELETE FROM employee WHERE id=1;
INSERT INTO employee(id,empName,deptid) VALUES(2,'李四',3);
SELECT * FROM employee;