MySQL基础二

来源:互联网 发布:unity3d 超级玛丽 编辑:程序博客网 时间:2024/03/29 03:41
SHOW DATABASES;CREATE DATABASE day20;USE day20;SHOW TABLES;-- 默认值约束(当没有插入默认值字段的时候,默认值才会有作用)CREATE TABLE test(NAME VARCHAR(20),gender VARCHAR(2) DEFAULT '男');INSERT INTO test(NAME) VALUES('张三');INSERT INTO test(NAME,gender) VALUES('张三',NULL);DELETE FROM test;DROP TABLE test;-- 非空约束(not null)CREATE TABLE test(NAME VARCHAR(20) NOT NULL,gender VARCHAR(2));DESC test;-- 1、不能不插入值(虽然说设置了不能为空,但是由于使用外部软件插入数据,--     他会默认插入一个'',所以数据还是可以存入数据库,但是在使用MySQL自带的操作界面的时候就会报错)INSERT INTO test(gender) VALUES('男');-- Field 'NAME' doesn't have a default value-- 2、插入值不能为null INSERT INTO test(NAME,gender) VALUES(NULL,'男');-- Column 'name' cannot be null-- 唯一约束(添加唯一约束的字段不能出现重复值)CREATE TABLE test(id INT UNIQUE,NAME VARCHAR(20));-- 1、不能添加重复的值INSERT INTO test(id,NAME) VALUES(1,'李四');INSERT INTO test(id,NAME) VALUES(1,'王五');-- Duplicate entry '1' for key 'id'-- 2、不能约束null,可以添加多个nullINSERT INTO test(id,NAME) VALUES(NULL,'赵六');INSERT INTO test(id,NAME) VALUES(NULL,'赵');-- 主键约束(primary key)== 唯一+非空CREATE TABLE test(id INT PRIMARY KEY,NAME VARCHAR(20));-- 唯一性INSERT INTO test VALUES(1,'戴安娜');INSERT INTO test VALUES(1,'孔明');-- Duplicate entry '1' for key 'PRIMARY'-- 非空性INSERT INTO test(NAME) VALUES('张三');INSERT INTO test(id,NAME) VALUES(NULL,'李四');-- Column 'id' cannot be null-- 自增长约束(初始值为0,每一递增1)CREATE TABLE test(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20));INSERT INTO test(NAME) VALUES('刘德华'); INSERT INTO test(NAME) VALUES('张学友'); INSERT INTO test(NAME) VALUES('白小春'); INSERT INTO test(NAME) VALUES('复方木'); -- 删除数据使用delete不会重置主键,使用truncate会重置主键DELETE FROM test;TRUNCATE TABLE test;SELECT * FROM test;-- --------------------------------------------------------- 外键约束,当一张表中出现另一张表的数据的,且数据存在冗余-- 有了外键约束后,操作数据的顺序:-- 插入数据:先插入主表的数据,再插入副表的数据-- 删除数据:先删除副表的数据,再删除主表的数据-- 修改数据:先修改主表的数据,再修改副表的数据CREATE TABLE employee(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20),deptName VARCHAR(20));INSERT INTO employee(NAME,deptName) VALUES('张三','软件开发部');INSERT INTO employee(NAME,deptName) VALUES('李四','软件维护部');INSERT INTO employee(NAME,deptName) VALUES('王五','软件开发部');INSERT INTO employee(NAME,deptName) VALUES('赵六','软件维护部');INSERT INTO employee(NAME,deptName) VALUES('李齐','软件开发部');-- 部门名称存在冗余,解决方法,再创建一个部门表CREATE TABLE employee(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20),deptId VARCHAR(20));CREATE TABLE dept(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20));INSERT INTO dept(NAME) VALUES('软件开发部');INSERT INTO dept(NAME) VALUES('软件维护部');INSERT INTO dept(NAME) VALUES('后勤部');INSERT INTO employee(NAME,deptId) VALUES('陈六',1);INSERT INTO employee(NAME,deptId) VALUES('王五',1);INSERT INTO employee(NAME,deptId) VALUES('赵六',2);INSERT INTO employee(NAME,deptId) VALUES('李毅',1);INSERT INTO employee(NAME,deptId) VALUES('小春',2);INSERT INTO employee(NAME,deptId) VALUES('刘姐',3);-- 再没有添加外键约束的时候,非法数据也可以插入,比如说本身没有4号部门,但是却可以插入4号部门的成员INSERT INTO employee(NAME,deptId) VALUES('刘姐',4);-- 添加外键约束-- 创建的时候,先创建主表,再创建副表-- 主表:约束副表的表-- 副表:被约束的表CREATE TABLE employee(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20),deptId INT,CONSTRAINT employee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id)-- 申明         外键名字        外键       字段      依赖于   哪张表的字段         );-- 插入数据:先插入主表的数据,再插入副表的数据INSERT INTO dept(NAME) VALUES('秘书部');INSERT INTO employee(NAME,deptId) VALUES('刘姐',4);-- 删除数据:先删除副表的数据,再删除主表的数据DELETE FROM employee WHERE id=7;DELETE FROM dept WHERE id=4;-- 修改数据:先修改主表的数据,再修改副表的数据UPDATE dept SET id=4 WHERE id=3;UPDATE employee SET deptId=4 WHERE id=5;DROP TABLE employee;DROP TABLE dept;SELECT * FROM employee;SELECT * FROM dept;SHOW TABLES;-- -------------------------------------------------------- 级联删除-- 当删除或者修改主表的数据的时候。对副表造成影响副表的数据CREATE TABLE employee(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20),deptId INT,CONSTRAINT employee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id) ON UPDATE CASCADE ON DELETE CASCADE );DELETE FROM dept WHERE id=2;-- -------------------------------------------------------------- 多表查询-- 步骤:1、确定查询哪些表2、确定查询哪些字段,3、确定连接条件(规则,表数量减一)-- 效果:只有满足连接条件的的数据才能显示出来-- 1、交叉查询(笛卡尔乘积4*3=12,产生原因是没有足够的连接条件)-- 查询员工及其部门SELECT employee.name,dept.name FROM employee,dept;-- 2、内连接查询-- 方式一:使用from。。。whereSELECT e.name,d.nameFROM employee e,dept dWHERE e.deptId=d.id;-- 方式二:使用from...inner join..onSELECT e.name,d.nameFROM employee eINNER JOIN dept dON e.deptId=d.id;-- 3、左外连接查询-- 效果:左表的数据会完全显示出来,右表的符合连接条件的显示数据,不满足连接条件的显示为null-- 需求:查询部门及其部门的员工SELECT d.name,e.nameFROM dept dLEFT OUTER JOIN employee eON d.id=e.deptId;-- 4、右连接查询-- 效果:右表的数据会完全显示,左表的数据符合连接条件的显示数据,不满足连接条件的显示为nullSELECT e.name,d.nameFROM employee eLEFT OUTER JOIN dept dON e.deptId=d.id;-- 5、内连接查询-- 查询一张表里面的数据,表中数据存在内在关系ALTER TABLE employee ADD COLUMN sname INT;SELECT * FROM employee;-- 查询员工和上司SELECT e.name AS '员工',m.name AS '上司'FROM employee eLEFT OUTER JOIN employee mON e.id=m.sname;-- ------------------------------------------------------ 存储过程-- 创建存储过程-- delimiter 结束符号-- create procedure 存储过程名字(形式参数列表)-- begin--    多个sql语句-- end结束符号-- 参数类型:-- IN: 输入参数,可以携带数据到存储过程中-- OUT: 输出参数,可以携带数据到存储过程外面。-- INOUT: 输入输出参数。-- 带有输入参数-- 需求:传入员工id查询对应的员工DELIMITER $CREATE PROCEDURE pro_getId(IN eid INT)BEGINSELECT * FROM employee WHERE id=eid;END$-- 调用存储过程CALL pro_getId(2);-- 带有输出参数DELIMITER $CREATE PROCEDURE pro_getOut(OUT n VARCHAR(20))BEGINSET n='输出参数';END$-- 获取输数参数-- 设置输出-- set @n='ada';CALL pro_getOut(@n);SELECT @n;-- mysql数据库有三种变量-- 1、全局变量,MySQL内置的变量,只有当程序关闭的时候全局变量才会失效-- 查看全局变量SHOW VARIABLES;-- 查看某个全局变量-- select @@+全局变量名SHOW VARIABLES LIKE 'character%';SELECT @@character_set_client;-- 如何去改变数据库的全局变量-- set @@+全局变量=值SET @@character_set_client=gbk;-- 2、会话变量。变量只在某次登录的会话中有效!退出连接,会话变量数据失效!!   -- 查看某个会话变量: select @变量名SELECT @n;-- 修改/定义某个会话变量: set @变量名=值 SET @n='da';-- 3、局部变量:在存储过程中定义的变量。存储过程结束局部变量失效!!-- 查看某个局部变量: select 变量名-- 修改某个局部变量: set 变量名=值 -- 定义某个局部变量: declare 变量名 数据类型;        CREATE TABLE student(id INT,NAME VARCHAR(20),chinese FLOAT,english FLOAT,math FLOAT);INSERT INTO student(id,NAME,chinese,english,math) VALUES(1,'张小明',89,78,90);INSERT INTO student(id,NAME,chinese,english,math) VALUES(2,'李进',67,53,95);INSERT INTO student(id,NAME,chinese,english,math) VALUES(3,'王五',87,78,77);INSERT INTO student(id,NAME,chinese,english,math) VALUES(4,'李一',88,98,92);INSERT INTO student(id,NAME,chinese,english,math) VALUES(5,'李来财',82,84,67);INSERT INTO student(id,NAME,chinese,english,math) VALUES(6,'张进宝',55,85,45);INSERT INTO student(id,NAME,chinese,english,math) VALUES(7,'黄蓉',75,65,30);SELECT * FROM student;SELECT AVG(english) FROM student;DELIMITER $CREATE PROCEDURE stu_avgEnglish(OUT tavg FLOAT,OUT pifen VARCHAR(20))BEGIN -- 定义一个局部变量 DECLARE eavg FLOAT DEFAULT 0.0; -- 携带数据库的数据给输出参数(into)SELECT AVG(english) INTO eavg FROM student;IF eavg>80 AND eavg<=100 THENSET pifen='优秀';ELSEIF eavg>=60 AND eavg<=80 THENSET pifen='良好';ELSE SET pifen='不及格';END IF;SET tavg=eavg;END$-- 调用存储过程CALL stu_avgEnglish(@tavg,@pifen);-- 查看会话变量SELECT @pifen;-- 删除存储过程DROP PROCEDURE stu_avgEnglish;-- 触发器-- 当往表中插入、修改、删除一天数据的时候,同时使用日志记录下来,这是就要使用触发器实现CREATE TABLE emp_log(id INT PRIMARY KEY AUTO_INCREMENT,content VARCHAR(20));-- 插入数据的触发器CREATE TRIGGER tri_empAdd AFTER INSERT ON employee FOR EACH ROWINSERT INTO emp_log(content) VALUES('插入一条数据');INSERT INTO employee(NAME,deptId) VALUES('阿发啊',1);-- 修改数据的触发器CREATE TRIGGER tri_empUpdate AFTER UPDATE ON employee FOR EACH ROWINSERT INTO emp_log(content) VALUES('修改一条数据');UPDATE employee SET NAME='发' WHERE id=3;-- 删除数据的触发器CREATE TRIGGER tri_empDelete AFTER DELETE ON employee FOR EACH ROWINSERT INTO emp_log(content) VALUES('删除一条数据');DELETE FROM employee WHERE id=4;SELECT * FROM emp_log;-- --------------------------------------------------------- 权限问题-- root用户,超级管理员,权限:增删改查(数据库,表,数据)-- MySQL数据库:存放mysql配置信息,包括用户信息USE mysql;-- 用户表SELECT * FROM USER;-- 加密函数(MD5-加密--单向加密)SELECT PASSWORD('root');-- *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B-- 修改用户密码UPDATE USER SET PASSWORD=PASSWORD('123456') WHERE USER='root';-- 分配不同权限给不同的用户-- 需求: 分配查询day15数据库的employee表的权限给eric用户,密码为‘123456’GRANT SELECT ON day20.employee TO 'eric'@'localhost' IDENTIFIED BY '123456';GRANT DELETE ON day20.employee TO 'eric'@'localhost' IDENTIFIED BY '123456';

原创粉丝点击