MySQL基本使用

层级关系

服务器 - 数据库 -表

创建数据库

CREATE database test;

使用数据库

USE test

显示所有表

SHOW tables;

创建表格

CREATE TABLE CS350EMP(
  `Employee Number` varchar(255) NOT NULL,
  `Employee Name` varchar(255) NOT NULL,
  `Region` enum('NW','S','NE','SE','HQ') NOT NULL,
  `Start Date` date NOT NULL,
  `Salary` integer(10) NOT NULL,
    PRIMARY KEY(`Employee Number`)                #设置主键
);

CREATE TABLE CS350REG(
  `Region Code` enum('NW','S','NE','SE','HQ') NOT NULL,
  `Region Name` varchar(255) NOT NULL,
  `HQ` VARCHAR(255) NOT NULL,
  `Manager` varchar(255) NOT NULL,
    PRIMARY KEY(`Region Code`)              #设置主键
);

修改表格

ALTER TABLE test.cs350emp
ADD COLUMN `age` int(1) NOT NULL AFTER `Salary`,
ADD FOREIGN KEY (`Region`) REFERENCES `test`.`cs350reg` (`Region Code`) ON DELETE CASCADE ON UPDATE CASCADE;

插入行

INSERT INTO cs350emp(`Employee Number`,`Employee Name`,`Region`,`Start Date`,`Salary`) VALUES
    ('958 94 573','Jeff Smythe','NW','2018/05/16',23000),
    ('900 58 472','Mary Faris','NE','2010/01/22',33000),
    ('280 24 328','Scott Williams','S','2014/03/09',46800),
    ('429 27 943','Keith Weber','SE','2010/01/22',52500),
    ('944 58 432','Jeff Leffer','NW','2017/05/23',71200),
    ('750 38 528','Gerry Cooke','NE','2012/10/04',69300),
    ('132 45 678','Alfred Alberts','S','2005/04/04',98500),
    ('987 65 432','Bo Brown','S','2012/04/19',19300),
    ('623 38 548','Keith Baker','SE','2012/10/04',69300),
    ('333 33 333','Fran Weber','HQ','2012/12/11',200800),
    ('684 39 542','Sally Weber','SE','2011/11/10',38900),
    ('785 02 675','Bob Smithers','SE','2014/03/30',23600),
    ('423 28 267','Jay Monson','HQ','2015/12/17',31000)

INSERT INTO cs350reg(`Region Code`,`Region Name`,`HQ`,`Manager`) VALUES
    ('NW','Northwest','Spokane, WA','2018/05/16','944 58 432'),
    ('S','Southern','NE','Dallas, TX','11'),
    ('NE','New England','S','Albany, NY','900 58 472'),
    ('HQ','Headquarters Staff','Waltham, MA','11'),
    ('SE','Southeast','NW','Miami, FL','785 02 675')

数据更新

UPDATE cs350emp SET `Employee Number` = '785 02 675' WHERE `Employee Name` = 'Mary Fairs';

数据删除

DELETE FROM cs350emp WHERE `Employee Name` = `Jeff Smythe`;

数据查询

SELECT `Salary` FROM cs350emp WHERE `Salary` > 30000;

设置外键

ALTER TABLE test.cs350emp
ADD CONSTRAINT `fkey` FOREIGN KEY (`Region`) REFERENCES `test`.`cs350reg` (`Region Code`) ON DELETE CASCADE ON UPDATE CASCADE;

# 删除
ALTER TABLE `test`.`cs350emp` DROP FOREIGN KEY `fkey`;

最后编辑于：2020.06.20 23:18:40