作者:小TMM_ | 来源:互联网 | 2023-09-15 19:39
--测试主键,类似于身份证,一个表只能有一个主键(包括联合主键)CREATETABLEIFNOTEXISTSuser1(idINTPRIMARYKEY,#primaryuse
CREATE TABLE IF NOT EXISTS user1(
id INT PRIMARY KEY,
username VARCHAR(20)
);
desc user1;
SHOW CREATE TABLE user1;INSERT user1 VALUES(1,'king');
INSERT user1 VALUES(13,'QUEEN');
SELECT *from user1 where id=1;CREATE TABLE IF NOT EXISTS user2(
id INT,
username VARCHAR(20),
card CHAR(18),
PRIMARY KEY(id,card)
);INSERT user2 VALUES(1,'king','111');INSERT user2 VALUES(1,'queen','112');CREATE TABLE IF NOT EXISTS user3(
id INT KEY,
username VARCHAR(20)
);CREATE TABLE IF NOT EXISTS user4(
id INT,
username VARCHAR(20) KEY
);
INSERT user1(id) VALUE('lhy');
CREATE TABLE IF NOT EXISTS user5(
id SMALLINT KEY AUTO_INCREMENT,
username VARCHAR(20)
);INSERT user5 VALUES(1,'KING');
INSERT user5(username) VALUES('queen1');INSERT user5 VALUES(111,'KING1');INSERT user5 VALUES(NULL,'AAAA');INSERT user5 VALUES(DEFAULT,'AAAA');CREATE TABLE IF NOT EXISTS user6(
id SMALLINT KEY AUTO_INCREMENT,
username VARCHAR(20)
)AUTO_INCREMENT=100;INSERT user6(username) VALUES('queen1');
ALTER TABLE user6 AUTO_INCREMENT =500;
CREATE TABLE IF NOT EXISTS user7(
id INT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
password CHAR(32) NOT NULL,
age TINYINT UNSIGNED
);INSERT user7(username,password) VALUES('KING','KING');INSERT user7(username,password,age) VALUES('KING1','KING1',12);INSERT user7(username,password) VALUES(NULL,NULL);CREATE TABLE IF NOT EXISTS user8(
id INT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
password CHAR(32) NOT NULL,
age TINYINT UNSIGNED DEFAULT 18,
addr VARCHAR(50) NOT NULL DEFAULT '北京',
sex ENUM('男','女','保密') NOT NULL DEFAULT '男'
);INSERT user8(username,password) VALUES('KING','KING');INSERT user8 VALUES(2,'QUEEN','QUEEN',29,'上海','保密');INSERT user8 VALUES(3,'QUEEN','QUEEN',DEFAULT,DEFAULT,'保密');CREATE TABLE IF NOT EXISTS user9(
id TINYINT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL UNIQUE,
card CHAR(18) UNIQUE
);INSERT user9(username) VALUES('A');
INSERT user9(username,card) VALUES('B','111');INSERT user9(username,card) VALUES('B1',NULL);INSERT user9(username,card) VALUES('B2',NULL);
CREATE TABLE IF NOT EXISTS user10(
id SMALLINT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL UNIQUE,
password CHAR(32) NOT NULL,
email VARCHAR(50) NOT NULL DEFAULT '382771946@qq.com',
age TINYINT UNSIGNED DEFAULT 18,
sex ENUM('男','女','保密') DEFAULT '保密',
addr VARCHAR(200) NOT NULL DEFAULT '北京',
salary FLOAT(6,2),
regTime INT UNSIGNED,
face CHAR(100) NOT NULL DEFAULT 'default.jpg'
);
ALTER TABLE user10 RENAME TO user11;ALTER TABLE user11 RENAME AS user10;ALTER TABLE user10 RENAME user11;RENAME TABLE user11 TO user10;
ALTER TABLE user10 ADD card CHAR(18);ALTER TABLE user10 ADD test1 VARCHAR(100) NOT NULL UNIQUE;ALTER TABLE user10 ADD test2 VARCHAR(20) NOT NULL FIRST;ALTER TABLE user10 ADD test3 INT NOT NULL DEFAULT 100 AFTER username;
ALTER TABLE user10ADD test4 INT NOT NULL DEFAULT 123 AFTER password,ADD test5 FLOAT(6,2) FIRST,ADD test6 SET('A','B','C');ALTER TABLE user10 DROP test6;
ALTER TABLE user10DROP test2,DROP test3,DROP test4,DROP test5;
ALTER TABLE user10ADD test INT UNSIGNED NOT NULL DEFAULT 10 AFTER sex,DROP addr;ALTER TABLE user10 MODIFY email VARCHAR(200);ALTER TABLE user10 MODIFY email VARCHAR(50) NOT NULL DEFAULT '382771946@qq.com';
ALTER TABLE user10 MODIFY card CHAR(18) AFTER test;
ALTER TABLE user10 MODIFY test CHAR(32) NOT NULL DEFAULT '123' FIRST;
ALTER TABLE user10 CHANGE test test1 CHAR(32) NOT NULL DEFAULT '123';ALTER TABLE user10 CHANGE test1 test VARCHAR(200) NOT NULL AFTER username;ALTER TABLE user10 CHANGE test test INT;
CREATE TABLE IF NOT EXISTS user11(
id TINYINT UNSIGNED KEY AUTO_INCREMENT ,
username VARCHAR(20) NOT NULL UNIQUE,
age TINYINT UNSIGNED
);
ALTER TABLE user11 ALTER age SET DEFAULT 18;ALTER TABLE user11 ADD email VARCHAR(50) ;
ALTER TABLE user11 ALTER age DROP DEFAULT;ALTER TABLE user11 ALTER email DROP DEFAULT;
CREATE TABLE IF NOT EXISTS test12(
id INT
);ALTER TABLE test12 ADD PRIMARY KEY(id);CREATE TABLE IF NOT EXISTS test13(
id INT,
card CHAR(18),
username VARCHAR(20) NOT NULL
);ALTER TABLE test13 ADD PRIMARY KEY(id,card);
ALTER TABLE test12 DROP PRIMARY KEY;ALTER TABLE test13 DROP PRIMARY KEY;ALTER TABLE test12 ADD CONSTRAINT symbol PRIMARY KEY index_type(id);CREATE TABLE IF NOT EXISTS test14(
id INT UNSIGNED KEY AUTO_INCREMENT
);ALTER TABLE test14 MODIFY id INT UNSIGNED;
ALTER TABLE test14 DROP PRIMARY KEY ;
CREATE TABLE IF NOT EXISTS user12(
id TINYINT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
card CHAR(18) NOT NULL,
test VARCHAR(20) NOT NULL,
test1 CHAR(32) NOT NULL
);
ALTER TABLE user12 ADD UNIQUE(username);ALTER TABLE user12 ADD CONSTRAINT symbol UNIQUE KEY uni_card(card);ALTER TABLE user12 ADD CONSTRAINT symbol UNIQUE INDEX mulUni_test_test1(test,test1);
ALTER TABLE user12 DROP INDEX username;ALTER TABLE user12 DROP KEY uni_card;ALTER TABLE user12 DROP KEY mulUni_test_test1;
ALTER TABLE user12 ENGINE=MyISAM;ALTER TABLE user12 AUTO_INCREMENT=100;
DROP TABLE user12;DROP TABLE IF EXISTS user12;DROP TABLE IF EXISTS user11,user10,user9;DROP TABLE IF EXISTS user123,user8,user7,user6;
另外,在登陆的时候使用MySQL -uroot -p -D lhy
可以打开指定数据库.