修改密码之后 在调用数据库时
连接MySQL数据库时出错信息如下：
Client does not support authentication protocol requested
by server; consider upgrading MySQL client
-- 1 创建数据库
CREATE DATABASE ems;
-- 2 drop 命令删除数据库
DROP DATABASE ems;
-- 使用哪个数据库
USE mysql;
-- ================================================
CREATE TABLE table_name (column_name column_type);
员工（属性：工号，姓名，密码，性别，出生日期，头像，部门）
Employee(EmpId,EmpName,EmpPassword,Gender,BirthDate,EmpPic,DeptId)
部门（属性：部门编号 ，部门名称）
Dept(DeptId,DeptName)
-- ================================================
-- 3创建表
-- ********************************1*******************************
CREATE TABLE dept(
deptId INT PRIMARY KEY AUTO_INCREMENT,
deptName VARCHAR(50)
)
-- **************************************************************
-- 4删除表
DROP TABLE dept;
-- 添加数据
INSERT INTO dept(deptName) VALUES('销售部')
INSERT INTO dept VALUES(NULL,'开发部')
-- 查询数据
SELECT * FROM dept
CREATE TABLE Employee(
EmpId VARCHAR(20) ,
EmpName VARCHAR(50),
EmpPassword VARCHAR(32),
Gender CHAR(2),
BirthDate DATE,
EmpPic VARCHAR(50),
DeptId INT
)
INSERT INTO employee(EmpId,EmpName,EmpPassword,Gender,BirthDate,EmpPic,DeptId)
VALUES( '101','Lily', '123456','女','1996/1/8','101.jpg',1);
INSERT INTO employee VALUES( '102','Tomas', '123456','女','1996/1/8','102.jpg',2);
INSERT INTO employee(EmpId,EmpName,EmpPassword,Gender,DeptId)
VALUES( '103','Lucy', '123456','女',2)
INSERT INTO employee(EmpId,EmpName,EmpPassword,Gender,DeptId)
VALUES( NULL ,'Bill Gates', '123456','男',3) ===》解决
SELECT * FROM employee
********************************2*******************************
===================PK,FK OK===================
CREATE TABLE Employee(
EmpId VARCHAR(20) PRIMARY KEY ,
EmpName VARCHAR(50),
EmpPassword VARCHAR(32),
Gender CHAR(2),
BirthDate DATE,
EmpPic VARCHAR(50),
DeptId INT,
FOREIGN KEY (DeptId) REFERENCES Dept(DeptId)
)
****************************************************************
DROP TABLE Employee;
=======================================================================
================EMS2（添加主键，添加 外键）===========================
CREATE DATABASE ems2;
USE ems2;
CREATE TABLE dept(
deptId INT PRIMARY KEY AUTO_INCREMENT,
deptName VARCHAR(50)
);
INSERT INTO dept(deptName) VALUES('销售部');
INSERT INTO dept VALUES(NULL,'开发部');
CREATE TABLE Employee(
EmpId VARCHAR(20) ,
EmpName VARCHAR(50),
EmpPassword VARCHAR(32),
Gender CHAR(2),
BirthDate DATE,
EmpPic VARCHAR(50),
DeptId INT
)
****************************************************************
INSERT INTO employee VALUES( '102','Tomas', '123456','女','1996/1/8','102.jpg',2);
****************************************************************
SELECT * FROM employee;
****************************************************************
-- ALTER TABLE 时的 SQL PRIMARY KEY 约束
-- 添加主键
ALTER TABLE Employee ADD PRIMARY KEY (EmpId);
ALTER TABLE Employee DROP PRIMARY KEY ;
****************************************************************
===================
-- 添加外键
FOREIGN KEY (DeptId) REFERENCES Dept(DeptId)创建时
INSERT INTO employee VALUES( '103','Tomas', '123456','女','1996/1/8','102.jpg',2);
ALTER TABLE employee ADD FOREIGN KEY (deptId) REFERENCES dept(deptId);
****************************************************************
增删改查
INSERT
SELECT
UPDATE
DELETE
INSERT INTO dept(deptName) VALUES('销售部');
SELECT * FROM employee;
UPDATE employee SET EmpName='Lucy' WHERE empid='102'
UPDATE employee SET EmpName='张三' WHERE empid='103'
UPDATE employee SET Gender='男' WHERE empid='103'
UPDATE employee SET EmpName='张三',Gender='男' WHERE empid='103'
DELETE FROM employee WHERE Gender='女'
网友评论