mingling
mysql> USE mon Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> SHOW TABLES; +---------------+ | Tables_in_mon | +---------------+ | qqq | +---------------+ 1 row in set (0.00 sec) mysql> DESC qqq; +----------+--------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------------+------+-----+---------+-------+ | username | varchar(10) | YES | | NULL | | | munber | int(11) | YES | | NULL | | | money | decimal(8,2) | YES | | NULL | | | sex | enum('n','NV','M') | YES | | NULL | | | content | text | YES | | NULL | | +----------+--------------------+------+-----+---------+-------+ 5 rows in set (0.00 sec) mysql> SHOW COLUMNS FROM qqq; +----------+--------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------------+------+-----+---------+-------+ | username | varchar(10) | YES | | NULL | | | munber | int(11) | YES | | NULL | | | money | decimal(8,2) | YES | | NULL | | | sex | enum('n','NV','M') | YES | | NULL | | | content | text | YES | | NULL | | +----------+--------------------+------+-----+---------+-------+ 5 rows in set (0.00 sec) mysql> INSERT INTO qqq (username,munber,money,sex) VALUES('quanzhiqia',23,1351.41,'n'); Query OK, 1 row affected (0.07 sec) mysql> INSERT INTO qqq (username,munber,money,sex) VALUES('quanzhiqia',23,1351.41121,'n'); Query OK, 1 row affected, 1 warning (0.12 sec) mysql> SHOW WARNINGS; +-------+------+--------------------------------------------+ | Level | Code | Message | +-------+------+--------------------------------------------+ | Note | 1265 | Data truncated for column 'money' at row 1 | +-------+------+--------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT money FROM qqq; +---------+ | money | +---------+ | 1351.41 | | 1351.41 | +---------+ 2 rows in set (0.01 sec) mysql> SELECT money,sex FROM qqq; +---------+------+ | money | sex | +---------+------+ | 1351.41 | n | | 1351.41 | n | +---------+------+ 2 rows in set (0.09 sec) mysql> SELECT * FROM qqq; +------------+--------+---------+------+---------+ | username | munber | money | sex | content | +------------+--------+---------+------+---------+ | quanzhiqia | 23 | 1351.41 | n | NULL | | quanzhiqia | 23 | 1351.41 | n | NULL | +------------+--------+---------+------+---------+ 2 rows in set (0.00 sec) mysql> mysql> CREATE TABLE numm(tiny TINYINT UNSIGNED) ENGINE=MYISAM CHARACTER SET utf8MB4; Query OK, 0 rows affected (0.14 sec) mysql> DESC numm; +-------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------------+------+-----+---------+-------+ | tiny | tinyint(3) unsigned | YES | | NULL | | +-------+---------------------+------+-----+---------+-------+ 1 row in set (0.02 sec) mysql> INSERT INTO numm VALUES(255); Query OK, 1 row affected (0.10 sec) mysql> SELECT * FROM numm; +------+ | tiny | +------+ | 255 | +------+ 1 row in set (0.00 sec) mysql> CREATE TABLE nummm( -> mun SMALLINT ZEROFILL )ENGINE=MYISAM CHARSET utf8MB4; Query OK, 0 rows affected (0.00 sec) mysql> DESC nummm; +-------+-------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------------------------+------+-----+---------+-------+ | mun | smallint(5) unsigned zerofill | YES | | NULL | | +-------+-------------------------------+------+-----+---------+-------+ 1 row in set (0.00 sec) mysql> INSERT INTO nummm VALUES(99); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM nummm; +-------+ | mun | +-------+ | 00099 | +-------+ 1 row in set (0.00 sec) mysql> DESC qqq; +----------+--------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------------+------+-----+---------+-------+ | username | varchar(10) | YES | | NULL | | | munber | int(11) | YES | | NULL | | | money | decimal(8,2) | YES | | NULL | | | sex | enum('n','NV','M') | YES | | NULL | | | content | text | YES | | NULL | | +----------+--------------------+------+-----+---------+-------+ 5 rows in set (0.00 sec) mysql> CREATE TABLE nu ( -> user CHAR(10) NOT NULL, -> pass CHAR(32) NULL); Query OK, 0 rows affected (0.13 sec) mysql> DESC nu; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | user | char(10) | NO | | NULL | | | pass | char(32) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> INSERT INTO nu (pass) VALUES('FEF'); ERROR 1364 (HY000): Field 'user' doesn't have a default value mysql> SELECT * FROM nu; Empty set (0.00 sec) mysql> INSERT INTO nu (user) VALUES('FEF'); Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM nu; +------+------+ | user | pass | +------+------+ | FEF | NULL | +------+------+ 1 row in set (0.00 sec) mysql> CREATE TABLE tp3( -> username CHAR(10) NOT NULL DEFAULT 'QUAN', -> user CHAR(12) NULL); Query OK, 0 rows affected (0.12 sec) mysql> DESC tp3; +----------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------+------+-----+---------+-------+ | username | char(10) | NO | | QUAN | | | user | char(12) | YES | | NULL | | +----------+----------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> INSERT INTO tp3 (user) VALUES('FEFE'); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM tp3; +----------+------+ | username | user | +----------+------+ | QUAN | FEFE | +----------+------+ 1 row in set (0.00 sec)
主键约束：
mysql> CREATE TABLE tp4( -> id INT AUTO_INCREMENT PRIMARY KEY, -> user CHAR(10) NOT NULL); Query OK, 0 rows affected (0.13 sec) mysql> DESC tp4; +-------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | user | char(10) | NO | | NULL | | +-------+----------+------+-----+---------+----------------+ 2 rows in set (0.01 sec) mysql> INSERT INTO tp4 (user) VALUES('FEF'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO tp4 (user) VALUES('FEFE'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO tp4 (user) VALUES('WWWE'); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM tp4; +----+------+ | id | user | +----+------+ | 1 | FEF | | 2 | FEFE | | 3 | WWWE | +----+------+ 3 rows in set (0.00 sec) mysql> CREATE TABLE tp5 ( -> id INT PRIMARY KEY, -> user CHAR(10) NOT NULL); Query OK, 0 rows affected (0.11 sec) mysql> DESC tp5; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | user | char(10) | NO | | NULL | | +-------+----------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> INSERT INTO tp5(user) VALUES('fefe'); ERROR 1364 (HY000): Field 'id' doesn't have a default value mysql> INSERT INTO tp5(id,user) VALUES(11,'fefe'); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO tp5 VALUES(21,'fefe'); Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM tp5; +----+------+ | id | user | +----+------+ | 11 | fefe | | 21 | fefe | +----+------+ 2 rows in set (0.00 sec) mysql> INSERT INTO tp5 VALUES(21,'fefe'); ERROR 1062 (23000): Duplicate entry '21' for key 'PRIMARY'
mysql> CREATE TABLE tp6( id INT AUTO_INCREMENT PRIMARY KEY, username CHAR(10) NOT NULL UNIQUE KEY, tel C Query OK, 0 rows affected (0.19 sec) mysql> DESC tp5; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | user | char(10) | NO | | NULL | | +-------+----------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> DESC tp6; +----------+------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | username | char(10) | NO | UNI | NULL | | | tel | char(11) | YES | UNI | NULL | | | sex | tinyint(4) | NO | | 0 | | +----------+------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) mysql> INSERT INTO tp6 VALUES( 'null','quan','15219588659'); ERROR 1136 (21S01): Column count doesn't match value count at row 1 mysql> INSERT INTO tp6 VALUES( 'null','quan','15219588659',1); ERROR 1366 (HY000): Incorrect integer value: 'null' for column 'id' at row 1 mysql> INSERT INTO tp6 VALUES( null,'quan','15219588659',1); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM tp6; +----+----------+-------------+-----+ | id | username | tel | sex | +----+----------+-------------+-----+ | 1 | quan | 15219588659 | 1 | +----+----------+-------------+-----+ 1 row in set (0.00 sec) mysql> INSERT INTO tp6 VALUES( null,'quan','15219588659',0); ERROR 1062 (23000): Duplicate entry 'quan' for key 'username' mysql> INSERT INTO tp6 VALUES( null,'quan1','15219588659',0); ERROR 1062 (23000): Duplicate entry '15219588659' for key 'tel' mysql> INSERT INTO tp6 VALUES( null,'quan1','15219588658',0); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM tp6; +----+----------+-------------+-----+ | id | username | tel | sex | +----+----------+-------------+-----+ | 1 | quan | 15219588659 | 1 | | 4 | quan1 | 15219588658 | 0 | +----+----------+-------------+-----+ 2 rows in set (0.00 sec) mysql> INSERT INTO tp6 (username,tel) VALUES('quan2','13145710069'); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM tp6; +----+----------+-------------+-----+ | id | username | tel | sex | +----+----------+-------------+-----+ | 1 | quan | 15219588659 | 1 | | 4 | quan1 | 15219588658 | 0 | | 5 | quan2 | 13145710069 | 0 | +----+----------+-------------+-----+ 3 rows in set (0.00 sec)
mysql> CREATE TABLE fa( -> id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, -> name CHAR(10) NOT NULL); Query OK, 0 rows affected (0.12 sec) mysql> CREATE TABLE ss( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name CHAR(10) NOT NULL, cid INT UNSIGNED, FOREIGN KEY(cid) REFERENCES fa(id)); Query OK, 0 rows affected (0.20 sec) mysql> SHOW CREATE TABLE fa; +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------+ | fa | CREATE TABLE `fa` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` char(10) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SHOW CREATE TABLE ss; +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ss | CREATE TABLE `ss` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` char(10) NOT NULL, `cid` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`id`), KEY `cid` (`cid`), CONSTRAINT `ss_ibfk_1` FOREIGN KEY (`cid`) REFERENCES `fa` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.05 sec) mysql> SHOW INDEXES FROM fa\G; *************************** 1. row *************************** Table: fa Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: id Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: 1 row in set (0.00 sec) ERROR: No query specified mysql> SHOW INDEXES FROM ss \G *************************** 1. row *************************** Table: ss Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: id Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 2. row *************************** Table: ss Non_unique: 1 Key_name: cid Seq_in_index: 1 Column_name: cid Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: 2 rows in set (0.00 sec) mysql> SHOW TABLES; +---------------+ | Tables_in_mon | +---------------+ | fa | | father | | nu | | numm | | nummm | | qqq | | son | | ss | | tp3 | | tp4 | | tp5 | | tp6 | +---------------+ 12 rows in set (0.00 sec) mysql> DESC fa; +-------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | char(10) | NO | | NULL | | +-------+------------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec) mysql> INSERT INTO fa VALUES('A'); ERROR 1136 (21S01): Column count doesn't match value count at row 1 mysql> INSERT INTO fa(name) VALUES('A'); Query OK, 1 row affected (0.06 sec) mysql> INSERT INTO fa(name) VALUES('B'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO fa(name) VALUES('C'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO fa(name) VALUES('C'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO fa(name) VALUES('D'); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM fa; +----+------+ | id | name | +----+------+ | 1 | A | | 2 | B | | 3 | C | | 4 | C | | 5 | D | +----+------+ 5 rows in set (0.00 sec) mysql> DESC ss; +-------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | char(10) | NO | | NULL | | | cid | int(10) unsigned | YES | MUL | NULL | | +-------+------------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) mysql> INSERT INTO ss(name,cid) VALUES('LLL',1); Query OK, 1 row affected (0.05 sec) mysql> INSERT INTO ss(name,cid) VALUES('LLL',6); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`mon`.`ss`, CONSTRAINT `ss_ibfk_1` FOREIGN KEY (`cid`) REFERENCES `fa` (`id`)) mysql> INSERT INTO ss(name,cid) VALUES('LLL',2); Query OK, 1 row affected (0.04 sec) mysql> INSERT INTO ss(name,cid) VALUES('LLL',3); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM ss; +----+------+------+ | id | name | cid | +----+------+------+ | 1 | LLL | 1 | | 3 | LLL | 2 | | 4 | LLL | 3 | +----+------+------+ 3 rows in set (0.00 sec)
mysql> CREATE TABLE ALT( id INT , username CHAR(10)); Query OK, 0 rows affected (0.11 sec) mysql> DESC ALT; +----------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | username | char(10) | YES | | NULL | | +----------+----------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> ALTER TABLE ALT ADD sex CHAR(10) NOT NULL DEFAULT '0'; Query OK, 0 rows affected (0.28 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC ALT; +----------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | username | char(10) | YES | | NULL | | | sex | char(10) | NO | | 0 | | +----------+----------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> ALTER TABLE ALT ADD pid INT FIRST; Query OK, 0 rows affected (0.12 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC ALT; +----------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------+------+-----+---------+-------+ | pid | int(11) | YES | | NULL | | | id | int(11) | YES | | NULL | | | username | char(10) | YES | | NULL | | | sex | char(10) | NO | | 0 | | +----------+----------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> ALTER TABLE ALT price INT NOT NULL AFTER id; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MyQL server version for the right syntax to use near 'price INT NOT NULL AFTER id' at line 1 mysql> ALTER TABLE ALT ADD price INT NOT NULL AFTER id; Query OK, 0 rows affected (0.11 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC ALT; +----------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------+------+-----+---------+-------+ | pid | int(11) | YES | | NULL | | | id | int(11) | YES | | NULL | | | price | int(11) | NO | | NULL | | | username | char(10) | YES | | NULL | | | sex | char(10) | NO | | 0 | | +----------+----------+------+-----+---------+-------+ 5 rows in set (0.00 sec) mysql> ALTER TABLE ALT ADD(xing CHAR(10),age INT); Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC ALT; +----------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------+------+-----+---------+-------+ | pid | int(11) | YES | | NULL | | | id | int(11) | YES | | NULL | | | price | int(11) | NO | | NULL | | | username | char(10) | YES | | NULL | | | sex | char(10) | NO | | 0 | | | xing | char(10) | YES | | NULL | | | age | int(11) | YES | | NULL | | +----------+----------+------+-----+---------+-------+ 7 rows in set (0.00 sec) mysql> ALTER TABLE ALT DROP age; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC ALT; +----------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------+------+-----+---------+-------+ | pid | int(11) | YES | | NULL | | | id | int(11) | YES | | NULL | | | price | int(11) | NO | | NULL | | | username | char(10) | YES | | NULL | | | sex | char(10) | NO | | 0 | | | xing | char(10) | YES | | NULL | | +----------+----------+------+-----+---------+-------+ 6 rows in set (0.00 sec) mysql> ALTER TABLE ALT DROP xing,sex; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MyQL server version for the right syntax to use near 'sex' at line 1 mysql> ALTER TABLE ALT DROP xing,DROP sex; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC ALT; +----------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------+------+-----+---------+-------+ | pid | int(11) | YES | | NULL | | | id | int(11) | YES | | NULL | | | price | int(11) | NO | | NULL | | | username | char(10) | YES | | NULL | | +----------+----------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> ALTER TABLE ALT ADD PRIMARY id; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MyQL server version for the right syntax to use near 'id' at line 1 mysql> ALTER TABLE ALT ADD PRIMARY (id); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MyQL server version for the right syntax to use near '(id)' at line 1 mysql> ALTER TABLE ALT ADD PRIMARY KEY (id); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC ALT; +----------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------+------+-----+---------+-------+ | pid | int(11) | YES | | NULL | | | id | int(11) | NO | PRI | NULL | | | price | int(11) | NO | | NULL | | | username | char(10) | YES | | NULL | | +----------+----------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> ALTER TABLE ALT ADD UNIQUE(pid,price); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC ALT; +----------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------+------+-----+---------+-------+ | pid | int(11) | YES | MUL | NULL | | | id | int(11) | NO | PRI | NULL | | | price | int(11) | NO | | NULL | | | username | char(10) | YES | | NULL | | +----------+----------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> ALTER TABLE ALT ADD UNIQUE(pid); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE ALT ADD UNIQUE(pid); Query OK, 0 rows affected, 1 warning (0.01 sec) Records: 0 Duplicates: 0 Warnings: 1 mysql> DESC ALT; +----------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------+------+-----+---------+-------+ | pid | int(11) | YES | UNI | NULL | | | id | int(11) | NO | PRI | NULL | | | price | int(11) | NO | | NULL | | | username | char(10) | YES | | NULL | | +----------+----------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> ALTER TABLE ALT ADD UNIQUE(pid,price); Query OK, 0 rows affected, 1 warning (0.11 sec) Records: 0 Duplicates: 0 Warnings: 1 mysql> DESC ALT; +----------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------+------+-----+---------+-------+ | pid | int(11) | YES | UNI | NULL | | | id | int(11) | NO | PRI | NULL | | | price | int(11) | NO | | NULL | | | username | char(10) | YES | | NULL | | +----------+----------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> ALTER TABLE ALT ADD UNIQUE(usename,price); ERROR 1072 (42000): Key column 'usename' doesn't exist in table mysql> ALTER TABLE ALT ADD UNIQUE(username,price); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC ALT; +----------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------+------+-----+---------+-------+ | pid | int(11) | YES | UNI | NULL | | | id | int(11) | NO | PRI | NULL | | | price | int(11) | NO | | NULL | | | username | char(10) | YES | MUL | NULL | | +----------+----------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
mysql> ALTER TABLE ALT ADD UNIQUE(username,price); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC ALT; +----------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------+------+-----+---------+-------+ | pid | int(11) | YES | UNI | NULL | | | id | int(11) | NO | PRI | NULL | | | price | int(11) | NO | | NULL | | | username | char(10) | YES | MUL | NULL | | +----------+----------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
两项加在一起作为唯一索引
mysql> ALTER TABLE ALT ADD price INT NOT NULL AFTER id; Query OK, 0 rows affected (0.11 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC ALT; +----------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------+------+-----+---------+-------+ | pid | int(11) | YES | | NULL | | | id | int(11) | YES | | NULL | | | price | int(11) | NO | | NULL | | | username | char(10) | YES | | NULL | | | sex | char(10) | NO | | 0 | | +----------+----------+------+-----+---------+-------+ 5 rows in set (0.00 sec) mysql> ALTER TABLE ALT ADD(xing CHAR(10),age INT); Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC ALT; +----------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------+------+-----+---------+-------+ | pid | int(11) | YES | | NULL | | | id | int(11) | YES | | NULL | | | price | int(11) | NO | | NULL | | | username | char(10) | YES | | NULL | | | sex | char(10) | NO | | 0 | | | xing | char(10) | YES | | NULL | | | age | int(11) | YES | | NULL | | +----------+----------+------+-----+---------+-------+ 7 rows in set (0.00 sec) mysql> ALTER TABLE ALT DROP age; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC ALT; +----------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------+------+-----+---------+-------+ | pid | int(11) | YES | | NULL | | | id | int(11) | YES | | NULL | | | price | int(11) | NO | | NULL | | | username | char(10) | YES | | NULL | | | sex | char(10) | NO | | 0 | | | xing | char(10) | YES | | NULL | | +----------+----------+------+-----+---------+-------+ 6 rows in set (0.00 sec) mysql> ALTER TABLE ALT DROP xing,sex; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MyQL server version for the right syntax to use near 'sex' at line 1 mysql> ALTER TABLE ALT DROP xing,DROP sex; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC ALT; +----------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------+------+-----+---------+-------+ | pid | int(11) | YES | | NULL | | | id | int(11) | YES | | NULL | | | price | int(11) | NO | | NULL | | | username | char(10) | YES | | NULL | | +----------+----------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> ALTER TABLE ALT ADD PRIMARY id; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MyQL server version for the right syntax to use near 'id' at line 1 mysql> ALTER TABLE ALT ADD PRIMARY (id); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MyQL server version for the right syntax to use near '(id)' at line 1 mysql> ALTER TABLE ALT ADD PRIMARY KEY (id); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC ALT; +----------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------+------+-----+---------+-------+ | pid | int(11) | YES | | NULL | | | id | int(11) | NO | PRI | NULL | | | price | int(11) | NO | | NULL | | | username | char(10) | YES | | NULL | | +----------+----------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> ALTER TABLE ALT ADD UNIQUE(pid,price); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC ALT; +----------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------+------+-----+---------+-------+ | pid | int(11) | YES | MUL | NULL | | | id | int(11) | NO | PRI | NULL | | | price | int(11) | NO | | NULL | | | username | char(10) | YES | | NULL | | +----------+----------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> ALTER TABLE ALT ADD UNIQUE(pid); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE ALT ADD UNIQUE(pid); Query OK, 0 rows affected, 1 warning (0.01 sec) Records: 0 Duplicates: 0 Warnings: 1 mysql> DESC ALT; +----------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------+------+-----+---------+-------+ | pid | int(11) | YES | UNI | NULL | | | id | int(11) | NO | PRI | NULL | | | price | int(11) | NO | | NULL | | | username | char(10) | YES | | NULL | | +----------+----------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> ALTER TABLE ALT ADD UNIQUE(pid,price); Query OK, 0 rows affected, 1 warning (0.11 sec) Records: 0 Duplicates: 0 Warnings: 1 mysql> DESC ALT; +----------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------+------+-----+---------+-------+ | pid | int(11) | YES | UNI | NULL | | | id | int(11) | NO | PRI | NULL | | | price | int(11) | NO | | NULL | | | username | char(10) | YES | | NULL | | +----------+----------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> ALTER TABLE ALT ADD UNIQUE(usename,price); ERROR 1072 (42000): Key column 'usename' doesn't exist in table mysql> ALTER TABLE ALT ADD UNIQUE(username,price); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC ALT; +----------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------+------+-----+---------+-------+ | pid | int(11) | YES | UNI | NULL | | | id | int(11) | NO | PRI | NULL | | | price | int(11) | NO | | NULL | | | username | char(10) | YES | MUL | NULL | | +----------+----------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> ALTER TABLE ALT ADD age TINYINT UNSIGNED NOT NULL DEFAULT 0; Query OK, 0 rows affected (0.21 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC ALT; +----------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------------+------+-----+---------+-------+ | pid | int(11) | YES | UNI | NULL | | | id | int(11) | NO | PRI | NULL | | | price | int(11) | NO | | NULL | | | username | char(10) | YES | MUL | NULL | | | age | tinyint(3) unsigned | NO | | 0 | | +----------+---------------------+------+-----+---------+-------+ 5 rows in set (0.13 sec) mysql> ALTER TABLE ALT ALTER age SET DEFAULT 15; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC ALT; +----------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------------+------+-----+---------+-------+ | pid | int(11) | YES | UNI | NULL | | | id | int(11) | NO | PRI | NULL | | | price | int(11) | NO | | NULL | | | username | char(10) | YES | MUL | NULL | | | age | tinyint(3) unsigned | NO | | 15 | | +----------+---------------------+------+-----+---------+-------+ 5 rows in set (0.00 sec) mysql> ALTER TABLE ALT ALTER DROP DEFAULT; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DROP DEFAULT' at line 1 mysql> ALTER TABLE ALT ALTER age DROP DEFAULT; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC ALT; +----------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------------+------+-----+---------+-------+ | pid | int(11) | YES | UNI | NULL | | | id | int(11) | NO | PRI | NULL | | | price | int(11) | NO | | NULL | | | username | char(10) | YES | MUL | NULL | | | age | tinyint(3) unsigned | NO | | NULL | | +----------+---------------------+------+-----+---------+-------+ 5 rows in set (0.00 sec) mysql> SHOW CTEATE TABLE ALT; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CTEATE TABLE ALT' at line 1 mysql> SHOW CREATE TABLE ALT; +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ALT | CREATE TABLE `ALT` ( `pid` int(11) DEFAULT NULL, `id` int(11) NOT NULL, `price` int(11) NOT NULL, `username` char(10) DEFAULT NULL, `age` tinyint(3) unsigned NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `pid` (`pid`,`price`), UNIQUE KEY `pid_2` (`pid`), UNIQUE KEY `pid_3` (`pid`), UNIQUE KEY `pid_4` (`pid`,`price`), UNIQUE KEY `username` (`username`,`price`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> ALTER TABLE ALT DROP PRIMARY KEY; Query OK, 0 rows affected (0.19 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SHOW CREATE TABLE ALT; +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ALT | CREATE TABLE `ALT` ( `pid` int(11) DEFAULT NULL, `id` int(11) NOT NULL, `price` int(11) NOT NULL, `username` char(10) DEFAULT NULL, `age` tinyint(3) unsigned NOT NULL, UNIQUE KEY `pid` (`pid`,`price`), UNIQUE KEY `pid_2` (`pid`), UNIQUE KEY `pid_3` (`pid`), UNIQUE KEY `pid_4` (`pid`,`price`), UNIQUE KEY `username` (`username`,`price`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SHOW INDEXES FROM ALT\G *************************** 1. row *************************** Table: ALT Non_unique: 0 Key_name: pid Seq_in_index: 1 Column_name: pid Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: *************************** 2. row *************************** Table: ALT Non_unique: 0 Key_name: pid Seq_in_index: 2 Column_name: price Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 3. row *************************** Table: ALT Non_unique: 0 Key_name: pid_2 Seq_in_index: 1 Column_name: pid Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: *************************** 4. row *************************** Table: ALT Non_unique: 0 Key_name: pid_3 Seq_in_index: 1 Column_name: pid Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: *************************** 5. row *************************** Table: ALT Non_unique: 0 Key_name: pid_4 Seq_in_index: 1 Column_name: pid Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: *************************** 6. row *************************** Table: ALT Non_unique: 0 Key_name: pid_4 Seq_in_index: 2 Column_name: price Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 7. row *************************** Table: ALT Non_unique: 0 Key_name: username Seq_in_index: 1 Column_name: username Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: *************************** 8. row *************************** Table: ALT Non_unique: 0 Key_name: username Seq_in_index: 2 Column_name: price Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: 8 rows in set (0.09 sec) mysql> ALTER TABLE ALT DROP username; Query OK, 0 rows affected (0.18 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SHOW INDEXES FROM ALT\G *************************** 1. row *************************** Table: ALT Non_unique: 0 Key_name: username Seq_in_index: 1 Column_name: price Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 2. row *************************** Table: ALT Non_unique: 0 Key_name: pid Seq_in_index: 1 Column_name: pid Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: *************************** 3. row *************************** Table: ALT Non_unique: 0 Key_name: pid Seq_in_index: 2 Column_name: price Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 4. row *************************** Table: ALT Non_unique: 0 Key_name: pid_2 Seq_in_index: 1 Column_name: pid Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: *************************** 5. row *************************** Table: ALT Non_unique: 0 Key_name: pid_3 Seq_in_index: 1 Column_name: pid Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: *************************** 6. row *************************** Table: ALT Non_unique: 0 Key_name: pid_4 Seq_in_index: 1 Column_name: pid Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: *************************** 7. row *************************** Table: ALT Non_unique: 0 Key_name: pid_4 Seq_in_index: 2 Column_name: price Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: 7 rows in set (0.00 sec) mysql> SHOW CREATE TABLE ALT; +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ALT | CREATE TABLE `ALT` ( `pid` int(11) DEFAULT NULL, `id` int(11) NOT NULL, `price` int(11) NOT NULL, `age` tinyint(3) unsigned NOT NULL, UNIQUE KEY `username` (`price`), UNIQUE KEY `pid` (`pid`,`price`), UNIQUE KEY `pid_2` (`pid`), UNIQUE KEY `pid_3` (`pid`), UNIQUE KEY `pid_4` (`pid`,`price`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
mysql> DESC ALT; +-------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------------+------+-----+---------+-------+ | pid | int(11) | YES | UNI | NULL | | | id | int(11) | NO | | NULL | | | price | int(11) | NO | PRI | NULL | | | age | tinyint(3) unsigned | NO | | NULL | | +-------+---------------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> ALTER TABLE ALT MODIFY id int(11) NOT NULL DEFAULT 22; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC ALT; +-------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------------+------+-----+---------+-------+ | pid | int(11) | YES | UNI | NULL | | | id | int(11) | NO | | 22 | | | price | int(11) | NO | PRI | NULL | | | age | tinyint(3) unsigned | NO | | NULL | | +-------+---------------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> ALTER TABLE ALT MODIFY age FIRST; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FIRST' at line 1 mysql> ALTER TABLE ALT MODIFY age TINYINT(3) UNSIGNED NOT NULL FIRST; Query OK, 0 rows affected (1.24 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC ALT; +-------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------------+------+-----+---------+-------+ | age | tinyint(3) unsigned | NO | | NULL | | | pid | int(11) | YES | UNI | NULL | | | id | int(11) | NO | | 22 | | | price | int(11) | NO | PRI | NULL | | +-------+---------------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> ALTER TABLE ALT CHANGE price prices; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 mysql> ALTER TABLE ALT CHANGE price prices INT NOT NULL PRIMARY KEY; Query OK, 0 rows affected (0.28 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC ALT; +--------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+---------------------+------+-----+---------+-------+ | age | tinyint(3) unsigned | NO | | NULL | | | pid | int(11) | YES | UNI | NULL | | | id | int(11) | NO | | 22 | | | prices | int(11) | NO | PRI | NULL | | +--------+---------------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> ALTER TABLE ALT RENAME alt; Query OK, 0 rows affected (0.22 sec) mysql> show tables; +---------------+ | Tables_in_mon | +---------------+ | alt | | fa | | father | | nu | | numm | | nummm | | qqq | | son | | ss | | tp3 | | tp4 | | tp5 | | tp6 | +---------------+ 13 rows in set (0.00 sec) mysql> DESC alt; +--------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+---------------------+------+-----+---------+-------+ | age | tinyint(3) unsigned | NO | | NULL | | | pid | int(11) | YES | UNI | NULL | | | id | int(11) | NO | | 22 | | | prices | int(11) | NO | PRI | NULL | | +--------+---------------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> RANAME TABLE alt TO ALT; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RANAME TABLE alt TO ALT' at line 1 mysql> RENAME TABLE alt TO ALT; Query OK, 0 rows affected (0.00 sec) mysql> show tables; +---------------+ | Tables_in_mon | +---------------+ | ALT | | fa | | father | | nu | | numm | | nummm | | qqq | | son | | ss | | tp3 | | tp4 | | tp5 | | tp6 | +---------------+ 13 rows in set (0.00 sec)
用户表；
用户id INT
用户名 VARCHAR(20)
密码 CHAR(32)
性别 sex TINYINT DEFAULT 0
年龄 age TINYINT DEFAULT 0
学历 education TINYINT 1 2 3 4 5
所在班级 class VARCHAR(10) NOT NULL
省份 province VARCHAR(10) NOT NULL
mysql> CREATE TABLE user(
-> id INT AUTO_INCREMENT PRIMARY KEY,
-> username VARCHAR(20) NOT NULL ,
-> pwd CHAR(32) NOT NULL,
-> sex TINYINT DEFAULT 0,
-> age TINYINT DEFAULT 18,
-> edu TINYINT DEFAULT 2 NOT NULL,
-> classname VARCHAR(10) NOT NULL,
-> province VARCHAR(10) NOT NULL);
mysql> SHOW TABLES; +---------------+ | Tables_in_mon | +---------------+ | ALT | | fa | | father | | nu | | numm | | nummm | | qqq | | son | | ss | | tp3 | | tp4 | | tp5 | | tp6 | +---------------+ 13 rows in set (0.00 sec) mysql> CREATE TABLE user( -> id INT AUTO_INCREMENT PRIMARY KEY, -> username VARCHAR(20) NOT NULL , -> pwd CHAR(32) NOT NULL, -> sex TINYINT DEFAULT 0, -> age TINYINT DEFAULT 18, -> edu TINYINT DEFAULT 2 NOT NULL, -> classname VARCHAR(10) NOT NULL, -> province VARCHAR(10) NOT NULL); Query OK, 0 rows affected (0.13 sec) mysql> INSERT INTO user (username ,pwd,classname ,province) VALUES('小强'，md5(123),'jave','内蒙古') ； -> ' '> ‘ '> ^C mysql> INSERT INTO user (username ,pwd,classname ,province) VALUES('小强'，md5(123),'jave','内蒙古'); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '，md5(123),'jave','内蒙古')' at line 1 mysql> INSERT INTO user (username ,pwd,classname ,province) VALUES('小强'，'md5(123)','jave','内蒙古'); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '，'md5(123)','jave','内蒙古')' at line 1 mysql> INSERT INTO user (username ,pwd,classname ,province) VALUES('小强',md5(123),'jave','内蒙古'); Query OK, 1 row affected (0.11 sec) mysql> SELECT * FROM user; +----+----------+----------------------------------+------+------+-----+-----------+-----------+ | id | username | pwd | sex | age | edu | classname | province | +----+----------+----------------------------------+------+------+-----+-----------+-----------+ | 1 | 小强 | 202cb962ac59075b964b07152d234b70 | 0 | 18 | 2 | jave | 内蒙古 | +----+----------+----------------------------------+------+------+-----+-----------+-----------+ 1 row in set (0.00 sec) mysql> INSERT INTO user (username ,pwd,classname ,province) VALUES('小Q',md5(123),'jŠŠ罆罆 ^C mysql> INSERT INTO user (username ,pwd,classname ,province) VALUES('小强',md5(123),'jave','内蒙古');INSERT INTO user (username ,pwd,classname ,province) VALUES('小强'，md5(123),'jave','内蒙古') [1]+ Stopped mysql -u root -p [root@mysql.bktwo.quan.bbs ~]$mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 134 Server version: 5.7.29-log Source distribution Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use qqq ERROR 1049 (42000): Unknown database 'qqq' mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | m1 | | mon | | mysql | | performance_schema | | quan； | | quanbbs | | quantest | | quantesttwo | | sys | +--------------------+ 10 rows in set (0.01 sec) mysql> use mon Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> INSERT INTO user (username ,pwd,classname ,province) VALUES('小xx',md5(123),'jave','内蒙古'),('小jj',md5(1234),'javed','湛江'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM user; +----+----------+----------------------------------+------+------+-----+-----------+-----------+ | id | username | pwd | sex | age | edu | classname | province | +----+----------+----------------------------------+------+------+-----+-----------+-----------+ | 1 | 小强 | 202cb962ac59075b964b07152d234b70 | 0 | 18 | 2 | jave | 内蒙古 | | 2 | 小xx | 202cb962ac59075b964b07152d234b70 | 0 | 18 | 2 | jave | 内蒙古 | | 3 | 小jj | 81dc9bdb52d04dc20036dbd8313ed055 | 0 | 18 | 2 | javed | 湛江 | +----+----------+----------------------------------+------+------+-----+-----------+-----------+ 3 rows in set (0.00 sec) mysql> INSERT INTO user VARLUES(default,'小米',md5(1245),1,79,3,'py','背景'); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'VARLUES(default,'小米',md5(1245),1,79,3,'py','背景')' at line 1 mysql> INSERT INTO user VALUES(default,'小米',md5(1245),1,66,3,'py','背景'); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM user; +----+----------+----------------------------------+------+------+-----+-----------+-----------+ | id | username | pwd | sex | age | edu | classname | province | +----+----------+----------------------------------+------+------+-----+-----------+-----------+ | 1 | 小强 | 202cb962ac59075b964b07152d234b70 | 0 | 18 | 2 | jave | 内蒙古 | | 2 | 小xx | 202cb962ac59075b964b07152d234b70 | 0 | 18 | 2 | jave | 内蒙古 | | 3 | 小jj | 81dc9bdb52d04dc20036dbd8313ed055 | 0 | 18 | 2 | javed | 湛江 | | 4 | 小米 | 5eac43aceba42c8757b54003a58277b5 | 1 | 66 | 3 | py | 背景 | +----+----------+----------------------------------+------+------+-----+-----------+-----------+ 4 rows in set (0.00 sec) mysql> INSERT INTO user VALUES(default,'小米',md5(1245),1,66,3,'py','背景'),(default,'小xiao',md5(12fe45),1,99,3,'py','福门'); ERROR 1054 (42S22): Unknown column '12fe45' in 'field list' mysql> INSERT INTO user VALUES(default,'小米',md5(1245),1,66,3,'py','背景'),(default,'小xiao',md5(123245),1,99,3,'py','福门'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM user; +----+----------+----------------------------------+------+------+-----+-----------+-----------+ | id | username | pwd | sex | age | edu | classname | province | +----+----------+----------------------------------+------+------+-----+-----------+-----------+ | 1 | 小强 | 202cb962ac59075b964b07152d234b70 | 0 | 18 | 2 | jave | 内蒙古 | | 2 | 小xx | 202cb962ac59075b964b07152d234b70 | 0 | 18 | 2 | jave | 内蒙古 | | 3 | 小jj | 81dc9bdb52d04dc20036dbd8313ed055 | 0 | 18 | 2 | javed | 湛江 | | 4 | 小米 | 5eac43aceba42c8757b54003a58277b5 | 1 | 66 | 3 | py | 背景 | | 5 | 小米 | 5eac43aceba42c8757b54003a58277b5 | 1 | 66 | 3 | py | 背景 | | 6 | 小xiao | 09a6f4ead95fb05ee29ab9e7d1219e33 | 1 | 99 | 3 | py | 福门 | +----+----------+----------------------------------+------+------+-----+-----------+-----------+ 6 rows in set (0.00 sec) mysql> INSERT INTO user SET username='root',pwd=md5(3453),classname='dada',province='天津'； -> ^C mysql> INSERT INTO user SET username='root',pwd=md5(3453),classname='dada',province='天津'; Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM user; +----+----------+----------------------------------+------+------+-----+-----------+-----------+ | id | username | pwd | sex | age | edu | classname | province | +----+----------+----------------------------------+------+------+-----+-----------+-----------+ | 1 | 小强 | 202cb962ac59075b964b07152d234b70 | 0 | 18 | 2 | jave | 内蒙古 | | 2 | 小xx | 202cb962ac59075b964b07152d234b70 | 0 | 18 | 2 | jave | 内蒙古 | | 3 | 小jj | 81dc9bdb52d04dc20036dbd8313ed055 | 0 | 18 | 2 | javed | 湛江 | | 4 | 小米 | 5eac43aceba42c8757b54003a58277b5 | 1 | 66 | 3 | py | 背景 | | 5 | 小米 | 5eac43aceba42c8757b54003a58277b5 | 1 | 66 | 3 | py | 背景 | | 6 | 小xiao | 09a6f4ead95fb05ee29ab9e7d1219e33 | 1 | 99 | 3 | py | 福门 | | 7 | root | f356355c1634839cf42769e7f30905a3 | 0 | 18 | 2 | dada | 天津 | +----+----------+----------------------------------+------+------+-----+-----------+-----------+ 7 rows in set (0.00 sec)
mysql> SELECT * FROM user; +----+----------+----------------------------------+------+------+-----+-----------+-----------+ | id | username | pwd | sex | age | edu | classname | province | +----+----------+----------------------------------+------+------+-----+-----------+-----------+ | 1 | 小强 | 202cb962ac59075b964b07152d234b70 | 0 | 18 | 2 | jave | 内蒙古 | | 2 | 小xx | 202cb962ac59075b964b07152d234b70 | 0 | 18 | 2 | jave | 内蒙古 | | 3 | 小jj | 81dc9bdb52d04dc20036dbd8313ed055 | 0 | 18 | 2 | javed | 湛江 | | 4 | 小米 | 5eac43aceba42c8757b54003a58277b5 | 1 | 66 | 3 | py | 背景 | | 5 | 小米 | 5eac43aceba42c8757b54003a58277b5 | 1 | 66 | 3 | py | 背景 | | 6 | 小xiao | 09a6f4ead95fb05ee29ab9e7d1219e33 | 1 | 99 | 3 | py | 福门 | | 7 | root | f356355c1634839cf42769e7f30905a3 | 0 | 18 | 2 | dada | 天津 | +----+----------+----------------------------------+------+------+-----+-----------+-----------+ 7 rows in set (0.00 sec) mysql> UPDATE user SET age + 5; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '+ 5' at line 1 mysql> UPDATE user SET age = age + 5; Query OK, 7 rows affected (0.09 sec) Rows matched: 7 Changed: 7 Warnings: 0 mysql> SELECT * FROM user; +----+----------+----------------------------------+------+------+-----+-----------+-----------+ | id | username | pwd | sex | age | edu | classname | province | +----+----------+----------------------------------+------+------+-----+-----------+-----------+ | 1 | 小强 | 202cb962ac59075b964b07152d234b70 | 0 | 23 | 2 | jave | 内蒙古 | | 2 | 小xx | 202cb962ac59075b964b07152d234b70 | 0 | 23 | 2 | jave | 内蒙古 | | 3 | 小jj | 81dc9bdb52d04dc20036dbd8313ed055 | 0 | 23 | 2 | javed | 湛江 | | 4 | 小米 | 5eac43aceba42c8757b54003a58277b5 | 1 | 71 | 3 | py | 背景 | | 5 | 小米 | 5eac43aceba42c8757b54003a58277b5 | 1 | 71 | 3 | py | 背景 | | 6 | 小xiao | 09a6f4ead95fb05ee29ab9e7d1219e33 | 1 | 104 | 3 | py | 福门 | | 7 | root | f356355c1634839cf42769e7f30905a3 | 0 | 23 | 2 | dada | 天津 | +----+----------+----------------------------------+------+------+-----+-----------+-----------+ 7 rows in set (0.00 sec) mysql> UPDATE user SET age = age + 2 ,sex = 0; Query OK, 7 rows affected (0.01 sec) Rows matched: 7 Changed: 7 Warnings: 0 mysql> SELECT * FROM user; +----+----------+----------------------------------+------+------+-----+-----------+-----------+ | id | username | pwd | sex | age | edu | classname | province | +----+----------+----------------------------------+------+------+-----+-----------+-----------+ | 1 | 小强 | 202cb962ac59075b964b07152d234b70 | 0 | 25 | 2 | jave | 内蒙古 | | 2 | 小xx | 202cb962ac59075b964b07152d234b70 | 0 | 25 | 2 | jave | 内蒙古 | | 3 | 小jj | 81dc9bdb52d04dc20036dbd8313ed055 | 0 | 25 | 2 | javed | 湛江 | | 4 | 小米 | 5eac43aceba42c8757b54003a58277b5 | 0 | 73 | 3 | py | 背景 | | 5 | 小米 | 5eac43aceba42c8757b54003a58277b5 | 0 | 73 | 3 | py | 背景 | | 6 | 小xiao | 09a6f4ead95fb05ee29ab9e7d1219e33 | 0 | 106 | 3 | py | 福门 | | 7 | root | f356355c1634839cf42769e7f30905a3 | 0 | 25 | 2 | dada | 天津 | +----+----------+----------------------------------+------+------+-----+-----------+-----------+ 7 rows in set (0.00 sec) mysql> UPDATE user SET age = age + 10 WHRER id%2 =0; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHRER id%2 =0' at line 1 mysql> UPDATE user SET age = age + 10 WHRER id%2 ==0; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHRER id%2 ==0' at line 1 mysql> UPDATE user SET age = age + 10 WHERE id%2 ==0; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '==0' at line 1 mysql> UPDATE user SET age = age + 10 WHERE id%2 =0; Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0 mysql> SELECT * FROM user; +----+----------+----------------------------------+------+------+-----+-----------+-----------+ | id | username | pwd | sex | age | edu | classname | province | +----+----------+----------------------------------+------+------+-----+-----------+-----------+ | 1 | 小强 | 202cb962ac59075b964b07152d234b70 | 0 | 25 | 2 | jave | 内蒙古 | | 2 | 小xx | 202cb962ac59075b964b07152d234b70 | 0 | 35 | 2 | jave | 内蒙古 | | 3 | 小jj | 81dc9bdb52d04dc20036dbd8313ed055 | 0 | 25 | 2 | javed | 湛江 | | 4 | 小米 | 5eac43aceba42c8757b54003a58277b5 | 0 | 83 | 3 | py | 背景 | | 5 | 小米 | 5eac43aceba42c8757b54003a58277b5 | 0 | 73 | 3 | py | 背景 | | 6 | 小xiao | 09a6f4ead95fb05ee29ab9e7d1219e33 | 0 | 116 | 3 | py | 福门 | | 7 | root | f356355c1634839cf42769e7f30905a3 | 0 | 25 | 2 | dada | 天津 | +----+----------+----------------------------------+------+------+-----+-----------+-----------+ 7 rows in set (0.00 sec)
