2019-08-21sql创建数据库和表

修改密码之后 在调用数据库时

连接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='女'