编辑代码

CREATE DATABASE test;
use test;
CREATE TABLE student (
	id int,
	name varchar(255),
	address varchar(255),
	city varchar(255)
);

INSERT INTO student VALUES (1, '刘一', '郑十家隔壁', '河南');
INSERT INTO student VALUES (2, '陈二',  '李四家隔壁', '安徽');
INSERT INTO student VALUES (3, '张三',  '白娘子家隔壁', '杭州');
INSERT INTO student VALUES (4, '李四',  '许仙家隔壁', '杭州');
INSERT INTO student VALUES (5, '王五',  '李四家隔壁', '杭州');
INSERT INTO student VALUES (6, '赵六',  '赵六家隔壁', '杭州');
INSERT INTO student VALUES (7, '孙七',  '张三家隔壁', '杭州');
INSERT INTO student VALUES (8, '周八',  '雷峰塔附近', '杭州');
INSERT INTO student VALUES (9, '吴九',  '孙七家隔壁', '杭州');
INSERT INTO student VALUES (10, '郑十',  '周八家隔壁', '杭州');
 
SELECT * FROM student;



CREATE  DATABASE    1htz;
USE  1htz;
CREATE  TABLE   stock_industry(
    ts_code  CHAR(6) NOT NULL    PRIMARY KEY,
    code_name  CHAR(8) NOT  NULL,
    stock_industry  VARCHAR (8) NOT NULL
    );
CREATE  TABLE   income(
  ts_code   CHAR(6) NOT NULL,
  end_date  DATE  NOT   NULL,
  oper_income   DECIMAL(10,2),
  oper_cost DECIMAL(10,2),
  tax_surchg    DECIMAL(10,2),
  sell_exp  DECIMAL(10,2),
  admin_exp DECIMAL(10,2),
  fin_exp   DECIMAL(10,2),
  operate_profit    DECIMAL(10,2),
  non_oper_income   DECIMAL(10,2),
  non_oper_cost DECIMAL(10,2),
  total_profit  DECIMAL(10,2),
  income_tax    DECIMAL(10,2),
  net_profit    DECIMAL(10,2),
  PRIMARY   KEY(ts_code,end_date),
  CONSTRAINT    fk_income   FOREIGN KEY(ts_code)    REFERENCES  stock_industry(ts_code) );

CREATE  TABLE   balancesheet(
  ts_code   CHAR(6) NOT NULL,
  end_date  DATE    NOT NULL,
  cash_all  DECIMAL(10,2),
  receivable    DECIMAL(10,2),
  advances_suppliers    DECIMAL(10,2),
  inventories   DECIMAL(10,2),
  tol_cur_assets    DECIMAL(10,2),
  long_equity   DECIMAL(10,2),
  fixde_assets  DECIMAL(10,2),
  intangible_assets DECIMAL(10,2),
  non_cur_assets    DECIMAL(10,2),
  assets    DECIMAL(10,2),
  short_debt    DECIMAL(10,2),
  accounts_pay  DECIMAL(10,2),
  payroll   DECIMAL(10,2),
  interest  DECIMAL(10,2),
  cur_liabilities   DECIMAL(10,2),
  non_cur_liabilities   DECIMAL(10,2),
  liabilities   DECIMAL(10,2),
  owners_equity DECIMAL(10,2),
  PRIMARY   KEY（ts_code,end_date),
  CONSTRAINT    fk_balance  FOREIGN KEY(ts_code)    REFERENCES  stock_industry(ts_code) );
  ALTER TABLE   balancesheet
  ADD  COLUMN   industry    VARCHAR(20) NOT NULL;
  ALTER TABLE   income
  ADD   COLUMN  industry    VARCHAR(20) NOT NULL;
  ALTER TABLE   balancesheet
  DROP  COLUMN  industry;
  ALTER TABLE   income
  DROP  COLUMN  industry;
DESC    income;
CREATE  TABLE   income_backup   LIKE    income;
DROP    TABLE   income_backup;



CREATE  DATABASE    xzgl;
USE xzgl;
CREATE  TABLE   departments(
  dnum  CHAR(3) NOT NULL,
  dname CHAR(20)    NOT NULL,
  dphone    CHAR(10),
  PRIMARY   KEY(dnum)
);
CREATE  TABLE   employees(
  enum  CHAR(6) NOT NULL    PRIMARY KEY,
  ename CHAR(10)    NOT NULL,
  education CHAR(4) NOT NULL,
  birthday  DATE    NOT NULL,
  sex   CHAR(2) DEFAULT'男',
  workyears TINYINT(1),
  address   VARCHAR(30),
  tel   CHAR(12),
  dnum  CHAR(3),
  CONSTRAINT    fk_dnum FOREIGN KEY(dnum)   REFERENCES  departments（dnum)
);
INSERT  INTO    departments VALUES('1','财务部','88657641');
INSERT  INTO    departments VALUES('2','行政部','88657642'),
                              ('3','经理办公室','88657643'),
                              ('4','生产部'，'88657644'),
                              ('5','市场部','88657645'),
                              ('6','安全监察部','88657646'),
INSERT  INTO    employees   VALUES('4003','李贞雅','本科','1990-07-09','女'，'6''遥临巷115号','113987657101','4'
),('5002')