问题:

所以我试图将一个主键添加到我的数据库中的一个表。 现在它有一个这样的主键:

PRIMARY KEY (user_id, round_number)

其中user_id是外键。

我想把它改成这个:

PRIMARY KEY (user_id, round_number, created_at)

我在phpmyadmin中通过单击表结构视图中的主键图标来执行此操作。

这是我得到的错误:

#1025 - Error on rename of './database/#sql-2e0f_1254ba7' to './database/table' (errno: 150)

它是一个带有InnoDB表引擎的MySQL数据库。

So I am trying to add a primary key to one of the tables in my database.Right now it has a primary key like this:Where user_id is a foreign key.I am trying to change it to this:I am doing this in phpmyadmin by clicking on the primary key icon in the table structure view.This is the error I get:It is a MySQL database with InnoDB table engine.

问题:

CREATE TABLE `users` (
  `UID` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `username` VARCHAR(45) NOT NULL ,
  `password` VARCHAR(100) NULL ,
  `name` VARCHAR(100) NULL ,
  `gender` BIT NULL ,
  `email` VARCHAR(255) NULL ,
  `phone` VARCHAR(30) NOT NULL ,
  `verified` BIT NOT NULL DEFAULT 0 ,
  `time_zone` INT NULL ,
  `time_register` DATETIME NULL ,
  `time_active` DATETIME NULL ,
  PRIMARY KEY (`UID`) ,
  UNIQUE INDEX `username_UNIQUE` (`username` ASC) ,
  INDEX `verified_INDEX` (`verified` ASC) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `companies`
-- -----------------------------------------------------
CREATE TABLE `companies` (
  `CID` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NOT NULL ,
  `address` VARCHAR(100) NULL ,
  `email` VARCHAR(255) NULL ,
  `phone` VARCHAR(30) NULL ,
  `link` TEXT NULL ,
  `image_small` TEXT NULL ,
  `image_large` TEXT NULL ,
  `yahoo` VARCHAR(100) NULL ,
  `linkin` VARCHAR(100) NULL ,
  `twitter` VARCHAR(20) NULL ,
  `description` TEXT NULL ,
  `shoutout` VARCHAR(140) NULL ,
  `verified` BIT NOT NULL DEFAULT 0 ,
  PRIMARY KEY (`CID`) ,
  INDEX `name_INDEX` (`name` ASC) ,
  INDEX `verified_INDEX` (`verified` ASC) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

-- -----------------------------------------------------
-- Table `products`
-- -----------------------------------------------------
CREATE TABLE `products` (
  `PID` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `CID` INT UNSIGNED NOT NULL ,
  `name` VARCHAR(100) NULL ,
  `description` TEXT NULL ,
  `image_small` TEXT NULL ,
  `image_large` TEXT NULL ,
  `tag` VARCHAR(45) NULL ,
  `price` DECIMAL(11,2) NULL ,
  PRIMARY KEY (`PID`) ,
  INDEX `tag_INDEX` (`tag` ASC) ,
  INDEX `company.cid_FK` (`CID` ASC) ,
  CONSTRAINT `company.cid_FK`
    FOREIGN KEY (`CID` )
    REFERENCES `companies` (`CID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

-- -----------------------------------------------------
-- Table `users_companies`
-- -----------------------------------------------------
CREATE TABLE `users_companies` (
  `UID` INT UNSIGNED NOT NULL ,
  `CID` INT UNSIGNED NOT NULL ,
  `role` INT UNSIGNED NULL ,
  PRIMARY KEY (`CID`, `UID`) ,
  INDEX `users.uid_FK` (`UID` ASC) ,
  INDEX `company.cid_FK` (`CID` ASC) ,
  CONSTRAINT `users.uid_FK`
    FOREIGN KEY (`UID` )
    REFERENCES `users` (`UID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `company.cid_FK`
    FOREIGN KEY (`CID` )
    REFERENCES `companies` (`CID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

我收到了错误

#1005 - Can't create table 'wew.users_companies' (errno: 121) (<a href="server_engines.php?engine=InnoDB&amp;page=Status&amp;token=3f35afdea97dd11f6b4ec1b669816738">Details...</a>) 

谁能告诉我哪一个是问题?

i got errorcan anyone tell me which one is the problem ?

问题:

我很困惑如何导入SQL转储文件。 如果不首先在MySQL中创建数据库,我似乎无法导入数据库。

这是尚未创建database_name时显示的错误:

username =有权访问原始服务器上数据库的用户的username名。
database_name =原始服务器中的数据库名称

$ mysql -u username -p -h localhost database_name < dumpfile.sql   
Enter password:  
ERROR 1049 (42000): Unknown database 'database_name' 

如果我以root身份登录MySQL并创建数据库database_name

mysql -u root  
create database database_name;  
create user username;# same username as the user from the database I got the dump from.  
grant all privileges on database_name.* to [email protected]"localhost" identified by 'password';  
exit mysql

然后再次尝试导入sql转储:

$ mysql -u username -p database_name < dumpfile.sql  
Enter password:  
ERROR 1007 (HY000) at line 21: Can't create database 'database_name'; database exists

我该如何导入SQL转储文件?

I’m confused how to import a SQL dump file.I can’t seem to import the database without creating the database first in MySQL.This is the error displayed when database_name has not yet been created:username = username of someone with access to the database on the original server.database_name = name of database from the original serverIf I log into MySQL as root and create the database, database_namethen attempt to import the sql dump again:How am I supposed to import the SQL dumpfile?

问题:

每当我运行此查询时,我都会不断收到此错误消息:

Msg 8115, Level 16, State 8, Line 33
Arithmetic overflow error converting numeric to data type numeric.
The statement has been terminated.

但是,如果我将创建表更改为(7,0),则不会收到错误消息,但是我需要将数据显示为小数。 我试过8,3不起作用。

有谁能帮助我完成这项工作?任何帮助将不胜感激。

DECLARE @StartDate AS DATETIME
DECLARE @StartDate_y AS DATETIME
DECLARE @EndDate AS DATETIME
DECLARE @temp_y AS DATETIME

SET @temp_y = Dateadd(yy, Datediff(yy, 0, Getdate()), 0)
SET @StartDate_y = Dateadd(dd, 1 - Datepart(dw, Dateadd("ww", -2, @temp_y)),
                                      Dateadd("ww", -2, @temp_y))
SET @StartDate = Dateadd(dd, 1 - Datepart(dw, Dateadd("ww", -2, Getdate())),
                                  Dateadd("ww", -2, Getdate()))
SET @EndDate = Dateadd(dd, 6, @StartDate)

--temp table to hold all cities in list
CREATE TABLE ##temp
  (
     city VARCHAR(50)
  )

INSERT INTO ##temp
VALUES     ('ABERDEEN'),
            ('CHESAPEAKE'),
            ('Preffered-Seafood/CHICAGO'),
            ('Preffered-Redist/CHICAGO'),
            ('CLACKAMAS'),
            ('COLUMBUS'),
            ('CONKLIN'),
            ('DENVER'),
            ('FORT WORTH'),
            ('HANOVER PARK'),
            ('JACKSONVILLE'),
            ('LAKELAND'),
            ('MONTGOMERY'),
            ('PFW-NORTHEAST'),
            ('PFW-SOUTHEAST'),
            ('RIVERSIDE'),
            ('TRENTON,CANADA'),
            ('VERNON')

--temp to hold data for the cities
CREATE TABLE #temp
  (
     city            VARCHAR(50),
     ytdshipments    INT,
     ytdtotalweight  DECIMAL(7, 2) NOT NULL,
     ytdtotalcharges DECIMAL (7, 2) NOT NULL
  --YTDRevperPound decimal (7,2) not null
  )

INSERT INTO #temp
SELECT ##temp.city,
       0,
       0,
       0
FROM   ##temp

INSERT #temp
-- YTD shipments/Charges/Weight by city
SELECT city = CASE
                WHEN nameaddrmstr_1.city IN( 'ABERDEEN', 'CHESAPEAKE', 'CHICAGO'
                                             ,
                                             'CLACKAMAS',
                                             'COLUMBUS', 'CONKLIN', 'DENVER',
                                             'FORT WORTH',
                                             'HANOVER PARK', 'JACKSONVILLE',
                                             'LAKELAND'
                                             ,
                                             'MONTGOMERY'
                                                    ,
                                             'RIVERSIDE', 'TRENTON', 'VERNON' )
              THEN
                CASE
                  WHEN
              nameaddrmstr_1.city = 'CHICAGO'
              AND h.shipr = 'PREFRESVS' THEN 'Preffered-Redist/CHICAGO'
                WHEN
              nameaddrmstr_1.city = 'TRENTON'
              AND nameaddrmstr_1.city = 'CA' THEN 'TRENTON,CANADA'
                ELSE
              nameaddrmstr_1.city
                END
                ELSE 'Other'
              END,
       ytdshipments = COUNT(CONVERT(VARCHAR(10), h.dateshipped, 101)),
       ytdtotalweight =SUM(CASE
                             WHEN h.totaldimwgt > h.totalwgt THEN h.totaldimwgt
                             ELSE h.totalwgt
                           END),
       ytdtotalcharges = SUM (cs.totalestrevcharges)
--YTDRevperPound = convert(decimal(7,2),sum (cs.TotalEstRevCharges )/sum( CASE WHEN h.TotalDimWGT > > h.TotalWGT THEN h.TotalDimWGT ELSE h.TotalWGT END ))
FROM   as400.dbo.hawb AS h WITH(nolock)
       INNER JOIN as400.dbo.chargesummary AS cs
         ON h.hawbnum = cs.hawbnum
       LEFT OUTER JOIN as400.dbo.nameaddrmstr AS nameaddrmstr_1
         ON h.shipr = nameaddrmstr_1.nameaddrcode
WHERE  h.dateshipped >= '01/01/2010'
       AND h.dateshipped <= '12/19/2010'
       --WHERE H.DateShipped >= >= @StartDate_y AND H.dateshipped <= @EndDate 
       AND h.cust IN( 'DARDENREED', 'MAINEDARDE', 'MBMRIVRSDE', 'MBMCOLUMBS',
                      'MBMLAKELND', 'MBMFTWORTH', 'SYGMACOLUM', 'SYGMANETW6',
                      'MAI215', 'MBMMNTGMRY' )
GROUP  BY CASE
  WHEN nameaddrmstr_1.city IN( 'ABERDEEN', 'CHESAPEAKE', 'CHICAGO', 'CLACKAMAS',
                               'COLUMBUS', 'CONKLIN', 'DENVER', 'FORT WORTH',
                               'HANOVER PARK', 'JACKSONVILLE', 'LAKELAND',
                               'MONTGOMERY'
                                      ,
                               'RIVERSIDE', 'TRENTON', 'VERNON' ) THEN CASE
                                                                         WHEN
nameaddrmstr_1.city = 'CHICAGO'
AND h.shipr = 'PREFRESVS' THEN 'Preffered-Redist/CHICAGO'
                                                                         WHEN
nameaddrmstr_1.city = 'TRENTON'
AND nameaddrmstr_1.city = 'CA' THEN 'TRENTON,CANADA'
                                                                         ELSE
nameaddrmstr_1.city
                                                                       END
  ELSE 'Other'
END

SELECT #temp.city                 AS city,
       MAX(#temp.ytdshipments)    AS ytdshipments,
       MAX(#temp.ytdtotalweight)  AS ytdtotalweight,
       MAX(#temp.ytdtotalcharges) AS ytdtotalcharges
FROM   #temp WITH(nolock)
       LEFT OUTER JOIN ##temp
         ON ##temp.city = #temp.city
GROUP  BY #temp.city

DROP TABLE #temp

DROP TABLE ##temp  

I keep getting this error message everytime I run this query:But if I change the create table to (7,0), I don’t get the error message.But I need my data to be displayed as a decimal.I have tried 8,3 does not work.Is there any one who can help me work this?Any help will be greatly appreciated.

问题:

对某人来说非常容易,以下插页给了我

ORA-01722:无效的号码

为什么?

INSERT INTO CUSTOMER VALUES (1,'MALADY','Claire','27 Smith St Caulfield','0419 853 694');
INSERT INTO CUSTOMER VALUES (2,'GIBSON','Jake','27 Smith St Caulfield','0415 713 598');
INSERT INTO CUSTOMER VALUES (3,'LUU','Barry','5  Jones St Malvern','0413 591 341');
INSERT INTO CUSTOMER VALUES (4,'JONES','Michael','7  Smith St Caulfield','0419 853 694');
INSERT INTO CUSTOMER VALUES (5,'MALADY','Betty','27 Smith St Knox','0418 418 347');

A very easy one for someone, The following insert is giving me theORA-01722: invalid numberwhy?