首页 > 代码库 > 浅谈“Mysql”的基础操作语句
浅谈“Mysql”的基础操作语句
/*-------------------------------------------读者可以补充内容到下面--------------------------------------------------*/
//修改表名
alter table qq_user rename user;
//修改字段数据类型
alter table user modify qq_id int;
//修改字段名
alter table user change qq_id Id int;
//修改字段名和字段数据类型
alter table user change Id id bigint(6);
//增加字段(可以指定字段增加得位置)
alter table user add Name varchar(50) after id;
//删除字段
alter table user drop Name;
//修改字段得排列位置
alter table user modify Level int after Password;
//删除外键约束
alter table user drop foreign key 外键别名
//删除有外键关联的表
//俩种解决方案 1先删除子表再删除父表（不建议）2先删除子表的约束 再删除父表
//**注意俩点：desc 表 和 show create 表 俩种方法的//表结构的查询 后者更加详细 可以看到外键
//*T-sql语句如何使用check约束
INSERT INTO depts(deptId,depName)
VALUES
(1, ‘人事部‘),
(2, ‘研发部‘),
(3, ‘市场部‘),
(4, ‘培训部‘);
create table depts(
Id int primary key not null AUTO_INCREMENT,
deptId int not null,
depName nvarchar(50));
/*------------------------------------以下是我创建的表，以及如何插入数据------------------------------------*/
************************************************************************************
创建表
CREATE TABLE qquser(
QQID BIGINT NOT NULL AUTO_INCREMENT,
PassWord VARCHAR(100) NOT NULL,
LastLogTime DATETIME,
Online INT NOT NULL CHECK(Online=0 or Online=1 or Online=2),
Level INT NOT NULL,
PRIMARY KEY ( QQID )
);
CREATE TABLE baseinfo(
QQID BIGINT NOT NULL AUTO_INCREMENT,
NickName VARCHAR(100) NOT NULL,
SEX INT check(RelationStatus=0 or RelationStatus=1),
Age INT NOT NULL,
Province varchar(50) NOT NULL,
City varchar(50),
Address varchar(100),
Phone char(100),
PRIMARY KEY ( QQID )
);
CREATE TABLE relation(
QQID BIGINT NOT NULL AUTO_INCREMENT,
RelationQQID BIGINT NOT NULL,
RelationStatus INT NOT NULL CHECK( RelationStatus=0 or RelationStatus=1),
PRIMARY KEY ( QQID )
);
************************************************************************************
QQUser表添加数据
INSERT INTO qquser
(QQID, PassWord, LastLogTime, Online, Level)
VALUES
("54789625
", "add512#&
", "2008-02-16 17:01:35
", "2", "1");
INSERT INTO qquser
(QQID, PassWord, LastLogTime, Online, Level)
VALUES
("88662753
", "admin0219
", "2008-02-19 21:08:50
", "0", "5");
INSERT INTO qquser
(QQID, PassWord, LastLogTime, Online, Level)
VALUES
("8855678
", "guest0221
", "2008-02-21 16:28:20
", "1", "6");
****************************************************************************************
Relation表添加数据
INSERT INTO relation
(QQID, RelationQQID, RelationStatus)
VALUES
("54789625
", "88662753
", "0
");
INSERT INTO relation
(QQID, RelationQQID, RelationStatus)
VALUES
("88662753
", "8855678
", "1
");
INSERT INTO relation
(QQID, RelationQQID, RelationStatus)
VALUES
("154789625
", "8855678
", "0
");
************************************************************************************
Baselnfo表添加数据
INSERT INTO baseinfo
(QQID, NickName, SEX, Age, Province, City, Address, Phone)
VALUES
("54789625
", "蝴蝶飞飞
", "1
", "16", "北京
", "朝阳
", "亚运村
", "37547388157668
");
INSERT INTO baseinfo
(QQID, NickName, SEX, Age, Province, City, Address, Phone)
VALUES
("88662753
", "秋芙蓉
1 your MySQL server version for the right syntax to use near ‘\)‘ at line 1 2 mysql> create table qq_user(id int primary key ); 3 Query OK, 0 rows affected (0.01 sec) 4 5 mysql> show tables; 6 +------------------+ 7 | Tables_in_systop | 8 +------------------+ 9 | qq_user | 10 +------------------+ 11 1 row in set (0.00 sec) 12 13 mysql> 14 mysql> 15 mysql> 16 mysql> 17 mysql> 18 mysql> 19 mysql> 20 mysql> 21 mysql> 22 mysql> 23 mysql> 24 mysql> create table qq_user(id int primary key ) 25 -> create table qq_user(id/; int primary key ) 26 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 ‘create table qq_user(id/‘ at line 2 27 -> 28 -> ; 29 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 ‘int primary key )‘ at line 1 30 31 mysql> create table qq_user( 32 -> qq_id bigint primary key, 33 -> Password nvrchar(50) not null, 34 -> ; 35 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 ‘nvrchar(50) not null,‘ at line 3 36 mysql> create table qq_user( 37 -> ; 38 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 39 mysql> show tables; 40 +------------------+ 41 | Tables_in_systop | 42 +------------------+ 43 | qq_user | 44 +------------------+ 45 1 row in set (0.00 sec) 46 47 mysql> drop table qq_user; 48 Query OK, 0 rows affected (0.00 sec) 49 50 mysql> show tables; 51 Empty set (0.00 sec) 52 53 mysql> 54 mysql> 55 mysql> create table qq_user( 56 -> qq_id bigint primary key, 57 -> Password nvarchar(50) not null, 58 -> LastLoginTime datetime, 59 -> Oline int, 60 -> Level int); 61 Query OK, 0 rows affected (0.00 sec) 62 63 mysql> show tables; 64 +------------------+ 65 | Tables_in_systop | 66 +------------------+ 67 | qq_user | 68 +------------------+ 69 1 row in set (0.00 sec) 70 71 mysql> descl 72 -> ; 73 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 ‘descl‘ at line 1 74 mysql> desc; 75 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 76 mysql> desc qq_user; 77 +---------------+-------------+------+-----+---------+-------+ 78 | Field | Type | Null | Key | Default | Extra | 79 +---------------+-------------+------+-----+---------+-------+ 80 | qq_id | bigint(20) | NO | PRI | NULL | | 81 | Password | varchar(50) | NO | | NULL | | 82 | LastLoginTime | datetime | YES | | NULL | | 83 | Oline | int(11) | YES | | NULL | | 84 | Level | int(11) | YES | | NULL | | 85 +---------------+-------------+------+-----+---------+-------+ 86 5 rows in set (0.00 sec) 87 88 mysql> insert into qq_user values(‘123344‘,‘xiaoguo‘,‘2014-1-1‘,‘1‘,‘2‘); 89 Query OK, 1 row affected (0.00 sec) 90 91 mysql> select *from qq_user; 92 +--------+----------+---------------------+-------+-------+ 93 | qq_id | Password | LastLoginTime | Oline | Level | 94 +--------+----------+---------------------+-------+-------+ 95 | 123344 | xiaoguo | 2014-01-01 00:00:00 | 1 | 2 | 96 +--------+----------+---------------------+-------+-------+ 97 1 row in set (0.00 sec) 98 99 mysql> 100 mysql> 101 mysql> truncate qq_user;102 Query OK, 0 rows affected (0.00 sec)103 104 mysql> selcet *from qq_user;105 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 ‘selcet *from qq_user‘ at line 1106 mysql> show tables;107 +------------------+108 | Tables_in_systop |109 +------------------+110 | qq_user |111 +------------------+112 1 row in set (0.00 sec)113 114 mysql> select * from qq_user;115 Empty set (0.00 sec)116 117 mysql> alter table qq_user rename user;118 Query OK, 0 rows affected (0.00 sec)119 120 mysql> show tables;121 +------------------+122 | Tables_in_systop |123 +------------------+124 | user |125 +------------------+126 1 row in set (0.00 sec)127 128 mysql> alter table user modify qq_id int;129 Query OK, 0 rows affected (0.00 sec)130 Records: 0 Duplicates: 0 Warnings: 0131 132 mysql> desc user;133 +---------------+-------------+------+-----+---------+-------+134 | Field | Type | Null | Key | Default | Extra |135 +---------------+-------------+------+-----+---------+-------+136 | qq_id | int(11) | NO | PRI | 0 | |137 | Password | varchar(50) | NO | | NULL | |138 | LastLoginTime | datetime | YES | | NULL | |139 | Oline | int(11) | YES | | NULL | |140 | Level | int(11) | YES | | NULL | |141 +---------------+-------------+------+-----+---------+-------+142 5 rows in set (0.00 sec)143 144 mysql> alter table user qq_id bigint(6);145 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 ‘qq_id bigint(6)‘ at line 1146 mysql> alter table user modify qq_id bigint(6);147 Query OK, 0 rows affected (0.00 sec)148 Records: 0 Duplicates: 0 Warnings: 0149 150 mysql> desc user;151 +---------------+-------------+------+-----+---------+-------+152 | Field | Type | Null | Key | Default | Extra |153 +---------------+-------------+------+-----+---------+-------+154 | qq_id | bigint(6) | NO | PRI | 0 | |155 | Password | varchar(50) | NO | | NULL | |156 | LastLoginTime | datetime | YES | | NULL | |157 | Oline | int(11) | YES | | NULL | |158 | Level | int(11) | YES | | NULL | |159 +---------------+-------------+------+-----+---------+-------+160 5 rows in set (0.00 sec)161 162 mysql> 163 mysql> 164 mysql> alter table user change qq_id id165 -> ;166 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 1167 mysql> alter table user change qq_id id int;168 Query OK, 0 rows affected (0.01 sec)169 Records: 0 Duplicates: 0 Warnings: 0170 171 mysql> desc user;172 +---------------+-------------+------+-----+---------+-------+173 | Field | Type | Null | Key | Default | Extra |174 +---------------+-------------+------+-----+---------+-------+175 | id | int(11) | NO | PRI | 0 | |176 | Password | varchar(50) | NO | | NULL | |177 | LastLoginTime | datetime | YES | | NULL | |178 | Oline | int(11) | YES | | NULL | |179 | Level | int(11) | YES | | NULL | |180 +---------------+-------------+------+-----+---------+-------+181 5 rows in set (0.00 sec)182 183 mysql> alter table user add Name varchar(20) after id;184 Query OK, 0 rows affected (0.01 sec)185 Records: 0 Duplicates: 0 Warnings: 0186 187 mysql> desc user;188 +---------------+-------------+------+-----+---------+-------+189 | Field | Type | Null | Key | Default | Extra |190 +---------------+-------------+------+-----+---------+-------+191 | id | int(11) | NO | PRI | 0 | |192 | Name | varchar(20) | YES | | NULL | |193 | Password | varchar(50) | NO | | NULL | |194 | LastLoginTime | datetime | YES | | NULL | |195 | Oline | int(11) | YES | | NULL | |196 | Level | int(11) | YES | | NULL | |197 +---------------+-------------+------+-----+---------+-------+198 6 rows in set (0.00 sec)199 200 mysql> alter table user drop Name;201 Query OK, 0 rows affected (0.00 sec)202 Records: 0 Duplicates: 0 Warnings: 0203 204 mysql> desc user;205 +---------------+-------------+------+-----+---------+-------+206 | Field | Type | Null | Key | Default | Extra |207 +---------------+-------------+------+-----+---------+-------+208 | id | int(11) | NO | PRI | 0 | |209 | Password | varchar(50) | NO | | NULL | |210 | LastLoginTime | datetime | YES | | NULL | |211 | Oline | int(11) | YES | | NULL | |212 | Level | int(11) | YES | | NULL | |213 +---------------+-------------+------+-----+---------+-------+214 5 rows in set (0.00 sec)215 216 mysql> alter table user modify Level int after Password;217 Query OK, 0 rows affected (0.00 sec)218 Records: 0 Duplicates: 0 Warnings: 0219 220 mysql> desc user;221 +---------------+-------------+------+-----+---------+-------+222 | Field | Type | Null | Key | Default | Extra |223 +---------------+-------------+------+-----+---------+-------+224 | id | int(11) | NO | PRI | 0 | |225 | Password | varchar(50) | NO | | NULL | |226 | Level | int(11) | YES | | NULL | |227 | LastLoginTime | datetime | YES | | NULL | |228 | Oline | int(11) | YES | | NULL | |229 +---------------+-------------+------+-----+---------+-------+230 5 rows in set (0.00 sec)231 232 mysql> alter table user modify id bigint(6);233 Query OK, 0 rows affected (0.00 sec)234 Records: 0 Duplicates: 0 Warnings: 0235 236 mysql> desc user;237 +---------------+-------------+------+-----+---------+-------+238 | Field | Type | Null | Key | Default | Extra |239 +---------------+-------------+------+-----+---------+-------+240 | id | bigint(6) | NO | PRI | 0 | |241 | Password | varchar(50) | NO | | NULL | |242 | Level | int(11) | YES | | NULL | |243 | LastLoginTime | datetime | YES | | NULL | |244 | Oline | int(11) | YES | | NULL | |245 +---------------+-------------+------+-----+---------+-------+246 5 rows in set (0.00 sec)247 248 mysql> 249 mysql> 250 mysql> show create user;251 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 ‘user‘ at line 1252 mysql> show create table user;253 +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+254 | Table | Create Table |255 +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+256 | user | CREATE TABLE `user` (257 `id` bigint(6) NOT NULL DEFAULT ‘0‘,258 `Password` varchar(50) CHARACTER SET utf8 NOT NULL,259 `Level` int(11) DEFAULT NULL,260 `LastLoginTime` datetime DEFAULT NULL,261 `Oline` int(11) DEFAULT NULL,262 PRIMARY KEY (`id`)263 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 |264 +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+265 1 row in set (0.00 sec)266 267 mysql> desc user;268 +---------------+-------------+------+-----+---------+-------+269 | Field | Type | Null | Key | Default | Extra |270 +---------------+-------------+------+-----+---------+-------+271 | id | bigint(6) | NO | PRI | 0 | |272 | Password | varchar(50) | NO | | NULL | |273 | Level | int(11) | YES | | NULL | |274 | LastLoginTime | datetime | YES | | NULL | |275 | Oline | int(11) | YES | | NULL | |276 +---------------+-------------+------+-----+---------+-------+277 5 rows in set (0.00 sec)278 279 mysql> alter table user modify Level int(3) default 0;280 Query OK, 0 rows affected (0.00 sec)281 Records: 0 Duplicates: 0 Warnings: 0282 283 mysql> desc user;284 +---------------+-------------+------+-----+---------+-------+285 | Field | Type | Null | Key | Default | Extra |286 +---------------+-------------+------+-----+---------+-------+287 | id | bigint(6) | NO | PRI | 0 | |288 | Password | varchar(50) | NO | | NULL | |289 | Level | int(3) | YES | | 0 | |290 | LastLoginTime | datetime | YES | | NULL | |291 | Oline | int(11) | YES | | NULL | |292 +---------------+-------------+------+-----+---------+-------+293 5 rows in set (0.00 sec)294 295 mysql> alter table user modify Olin default 1;296 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 ‘default 1‘ at line 1297 mysql> alter table user modify Olin int default 1;298 ERROR 1054 (42S22): Unknown column ‘Olin‘ in ‘user‘299 mysql> alter table user modify Oline int default 1;300 Query OK, 0 rows affected (0.00 sec)301 Records: 0 Duplicates: 0 Warnings: 0302 303 mysql> desc user;304 +---------------+-------------+------+-----+---------+-------+305 | Field | Type | Null | Key | Default | Extra |306 +---------------+-------------+------+-----+---------+-------+307 | id | bigint(6) | NO | PRI | 0 | |308 | Password | varchar(50) | NO | | NULL | |309 | Level | int(3) | YES | | 0 | |310 | LastLoginTime | datetime | YES | | NULL | |311 | Oline | int(11) | YES | | 1 | |312 +---------------+-------------+------+-----+---------+-------+313 5 rows in set (0.00 sec)314 315 mysql> alter table user LastLoginTime modify nvarchar(20) default 123456;316 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 ‘LastLoginTime modify nvarchar(20) default 123456‘ at line 1317 mysql> alter table user Password modify nvarchar(20) default 123456;318 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 ‘modify nvarchar(20) default 123456‘ at line 1319 mysql> desc user;320 +---------------+-------------+------+-----+---------+-------+321 | Field | Type | Null | Key | Default | Extra |322 +---------------+-------------+------+-----+---------+-------+323 | id | bigint(6) | NO | PRI | 0 | |324 | Password | varchar(50) | NO | | NULL | |325 | Level | int(3) | YES | | 0 | |326 | LastLoginTime | datetime | YES | | NULL | |327 | Oline | int(11) | YES | | 1 | |328 +---------------+-------------+------+-----+---------+-------+329 5 rows in set (0.00 sec)330 331 mysql> alter table user modify Password varchar(50) default ‘12345‘;332 Query OK, 0 rows affected (0.01 sec)333 Records: 0 Duplicates: 0 Warnings: 0334 335 mysql> desc;336 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 1337 mysql> desc user;338 +---------------+-------------+------+-----+---------+-------+339 | Field | Type | Null | Key | Default | Extra |340 +---------------+-------------+------+-----+---------+-------+341 | id | bigint(6) | NO | PRI | 0 | |342 | Password | varchar(50) | YES | | 12345 | |343 | Level | int(3) | YES | | 0 | |344 | LastLoginTime | datetime | YES | | NULL | |345 | Oline | int(11) | YES | | 1 | |346 +---------------+-------------+------+-----+---------+-------+347 5 rows in set (0.00 sec)348 349 mysql> alter table user add age int null check(1,100);350 Query OK, 0 rows affected (0.03 sec)351 Records: 0 Duplicates: 0 Warnings: 0352 353 mysql> desc user;354 +---------------+-------------+------+-----+---------+-------+355 | Field | Type | Null | Key | Default | Extra |356 +---------------+-------------+------+-----+---------+-------+357 | id | bigint(6) | NO | PRI | 0 | |358 | Password | varchar(50) | YES | | 12345 | |359 | Level | int(3) | YES | | 0 | |360 | LastLoginTime | datetime | YES | | NULL | |361 | Oline | int(11) | YES | | 1 | |362 | age | int(11) | YES | | NULL | |363 +---------------+-------------+------+-----+---------+-------+364 6 rows in set (0.00 sec)365 366 mysql> alter table user modify age int after password;367 Query OK, 0 rows affected (0.01 sec)368 Records: 0 Duplicates: 0 Warnings: 0369 370 mysql> desc user;371 +---------------+-------------+------+-----+---------+-------+372 | Field | Type | Null | Key | Default | Extra |373 +---------------+-------------+------+-----+---------+-------+374 | id | bigint(6) | NO | PRI | 0 | |375 | Password | varchar(50) | YES | | 12345 | |376 | age | int(11) | YES | | NULL | |377 | Level | int(3) | YES | | 0 | |378 | LastLoginTime | datetime | YES | | NULL | |379 | Oline | int(11) | YES | | 1 | |380 +---------------+-------------+------+-----+---------+-------+381 6 rows in set (0.00 sec)382 383 mysql> alter table user add Name nvarchar(20) not null default ‘guo‘ after id;384 Query OK, 0 rows affected (0.01 sec)385 Records: 0 Duplicates: 0 Warnings: 0386 387 mysql> desc user;388 +---------------+-------------+------+-----+---------+-------+389 | Field | Type | Null | Key | Default | Extra |390 +---------------+-------------+------+-----+---------+-------+391 | id | bigint(6) | NO | PRI | 0 | |392 | Name | varchar(20) | NO | | guo | |393 | Password | varchar(50) | YES | | 12345 | |394 | age | int(11) | YES | | NULL | |395 | Level | int(3) | YES | | 0 | |396 | LastLoginTime | datetime | YES | | NULL | |397 | Oline | int(11) | YES | | 1 | |398 +---------------+-------------+------+-----+---------+-------+399 7 rows in set (0.00 sec)400 401 mysql> CREATE TABLE baseinfo(402 -> QQID BIGINT NOT NULL AUTO_INCREMENT,403 -> NickName VARCHAR(100) NOT NULL,404 -> SEX INT check(RelationStatus=0 or RelationStatus=1),405 -> Age INT NOT NULL,406 -> Province varchar(50) NOT NULL,407 -> City varchar(50),408 -> Address varchar(100),409 -> Phone char(100),410 -> PRIMARY KEY ( QQID )411 -> );412 Query OK, 0 rows affected (0.00 sec)413 414 mysql> show tables;415 +------------------+416 | Tables_in_systop |417 +------------------+418 | baseinfo |419 | user |420 +------------------+421 2 rows in set (0.00 sec)422 423 mysql> desc baseinfo;424 +----------+--------------+------+-----+---------+----------------+425 | Field | Type | Null | Key | Default | Extra |426 +----------+--------------+------+-----+---------+----------------+427 | QQID | bigint(20) | NO | PRI | NULL | auto_increment |428 | NickName | varchar(100) | NO | | NULL | |429 | SEX | int(11) | YES | | NULL | |430 | Age | int(11) | NO | | NULL | |431 | Province | varchar(50) | NO | | NULL | |432 | City | varchar(50) | YES | | NULL | |433 | Address | varchar(100) | YES | | NULL | |434 | Phone | char(100) | YES | | NULL | |435 +----------+--------------+------+-----+---------+----------------+436 8 rows in set (0.00 sec)437 438 mysql> CREATE TABLE qquser(439 -> QQID BIGINT NOT NULL AUTO_INCREMENT,440 -> PassWord VARCHAR(100) NOT NULL,441 -> LastLogTime DATETIME,442 -> Online INT NOT NULL CHECK(Online=0 or Online=1 or Online=2),443 -> Level INT NOT NULL,444 -> PRIMARY KEY ( QQID )445 -> );446 Query OK, 0 rows affected (0.00 sec)447 448 mysql> show tables;449 +------------------+450 | Tables_in_systop |451 +------------------+452 | baseinfo |453 | qquser |454 | user |455 +------------------+456 3 rows in set (0.00 sec)457 458 mysql> desc user;459 +---------------+-------------+------+-----+---------+-------+460 | Field | Type | Null | Key | Default | Extra |461 +---------------+-------------+------+-----+---------+-------+462 | id | bigint(6) | NO | PRI | 0 | |463 | Name | varchar(20) | NO | | guo | |464 | Password | varchar(50) | YES | | 12345 | |465 | age | int(11) | YES | | NULL | |466 | Level | int(3) | YES | | 0 | |467 | LastLoginTime | datetime | YES | | NULL | |468 | Oline | int(11) | YES | | 1 | |469 +---------------+-------------+------+-----+---------+-------+470 7 rows in set (0.00 sec)471 472 mysql> desc qquser;473 +-------------+--------------+------+-----+---------+----------------+474 | Field | Type | Null | Key | Default | Extra |475 +-------------+--------------+------+-----+---------+----------------+476 | QQID | bigint(20) | NO | PRI | NULL | auto_increment |477 | PassWord | varchar(100) | NO | | NULL | |478 | LastLogTime | datetime | YES | | NULL | |479 | Online | int(11) | NO | | NULL | |480 | Level | int(11) | NO | | NULL | |481 +-------------+--------------+------+-----+---------+----------------+482 5 rows in set (0.00 sec)483 484 mysql> CREATE TABLE relation(485 -> QQID BIGINT NOT NULL AUTO_INCREMENT,486 -> RelationQQID BIGINT NOT NULL,487 -> RelationStatus INT NOT NULL CHECK( RelationStatus=0 or RelationStatus=1),488 -> PRIMARY KEY ( QQID )489 -> );490 Query OK, 0 rows affected (0.01 sec)491 492 mysql> show tables;493 +------------------+494 | Tables_in_systop |495 +------------------+496 | baseinfo |497 | qquser |498 | relation |499 | user |500 +------------------+501 4 rows in set (0.00 sec)502 503 mysql> desc relation;504 +----------------+------------+------+-----+---------+----------------+505 | Field | Type | Null | Key | Default | Extra |506 +----------------+------------+------+-----+---------+----------------+507 | QQID | bigint(20) | NO | PRI | NULL | auto_increment |508 | RelationQQID | bigint(20) | NO | | NULL | |509 | RelationStatus | int(11) | NO | | NULL | |510 +----------------+------------+------+-----+---------+----------------+511 3 rows in set (0.00 sec)512 513 mysql> 514 mysql> 515 mysql> 516 mysql> 517 mysql> 518 mysql> 519 mysql> 520 mysql> INSERT INTO qquser 521 -> (QQID, PassWord, LastLogTime, Online, Level) 522 -> VALUES 523 -> ("54789625524 "> ", "add512#&525 "> ", "2008-02-16 17:01:35526 "> ", "2", "1");527 Query OK, 1 row affected (0.00 sec)528 529 mysql> select *from qquser;530 +----------+-----------+---------------------+--------+-------+531 | QQID | PassWord | LastLogTime | Online | Level |532 +----------+-----------+---------------------+--------+-------+533 | 54789625 | add512#&534 | 2008-02-16 17:01:35 | 2 | 1 |535 +----------+-----------+---------------------+--------+-------+536 1 row in set (0.00 sec)537 538 mysql> show tables;539 +------------------+540 | Tables_in_systop |541 +------------------+542 | baseinfo |543 | qquser |544 | relation |545 | user |546 +------------------+547 4 rows in set (0.00 sec)548 549 mysql> ceate table depts;550 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 ‘ceate table depts‘ at line 1551 mysql> create table depts;552 ERROR 1113 (42000): A table must have at least 1 column553 mysql> create table depts(554 -> Id int primary key,555 -> deptId int not null,556 -> depName nvarchar(50),);557 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 4558 mysql> create table depts( Id int primary key, deptId int not null, depName nvarchar(50));559 Query OK, 0 rows affected (0.00 sec)560 561 mysql> 562 mysql> 563 mysql> 564 mysql> 565 mysql> INSERT INTO depts 566 -> VALUES 567 -> (‘1‘, ‘人事部‘, ‘ ‘), 568 -> (‘2‘, ‘研发部‘, ‘ ‘), 569 -> (‘3‘, ‘市场部‘, ‘ ‘), 570 -> (‘4‘, ‘培训部‘, ‘ ‘);571 Query OK, 4 rows affected, 4 warnings (0.00 sec)572 Records: 4 Duplicates: 0 Warnings: 0573 574 mysql> show tables;575 +------------------+576 | Tables_in_systop |577 +------------------+578 | baseinfo |579 | depts |580 | qquser |581 | relation |582 | user |583 +------------------+584 5 rows in set (0.00 sec)585 586 mysql> select * from depts;587 +----+--------+---------+588 | Id | deptId | depName |589 +----+--------+---------+590 | 1 | 0 | |591 | 2 | 0 | |592 | 3 | 0 | |593 | 4 | 0 | |594 +----+--------+---------+595 4 rows in set (0.00 sec)596 597 mysql> delect from depts;598 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 ‘delect from depts‘ at line 1599 mysql> delete *from depts;600 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 ‘*from depts‘ at line 1601 mysql> delete from depts;602 Query OK, 4 rows affected (0.00 sec)603 604 mysql> select *from 605 Display all 750 possibilities? (y or n)606 mysql> select *from depts;607 Empty set (0.00 sec)608 609 mysql> INSERT INTO depts 610 -> VALUES 611 -> (‘1‘, ‘人事部‘, ‘ ‘), 612 -> (‘2‘, ‘研发部‘, ‘ ‘), 613 -> (‘3‘, ‘市场部‘, ‘ ‘), 614 -> (‘2‘, ‘研发部‘, ‘ ‘), ;615 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 6616 mysql> INSERT INTO depts 617 -> VALUES 618 -> (‘1‘, ‘人事部‘), 619 -> (‘2‘, ‘研发部‘), 620 -> (‘3‘, ‘市场部‘), 621 -> (‘4‘, ‘培训部‘);622 ERROR 1136 (21S01): Column count doesn‘t match value count at row 1623 mysql> select *from depts;624 Empty set (0.00 sec)625 626 mysql> INSERT INTO depts(deptId,depName) 627 -> VALUES 628 -> (‘1‘, ‘人事部‘), 629 -> (‘2‘, ‘研发部‘), 630 -> (‘3‘, ‘市场部‘), 631 -> (‘4‘, ‘培训部‘);632 ERROR 1062 (23000): Duplicate entry ‘0‘ for key ‘PRIMARY‘633 mysql> desc depts;634 +---------+-------------+------+-----+---------+-------+635 | Field | Type | Null | Key | Default | Extra |636 +---------+-------------+------+-----+---------+-------+637 | Id | int(11) | NO | PRI | NULL | |638 | deptId | int(11) | NO | | NULL | |639 | depName | varchar(50) | YES | | NULL | |640 +---------+-------------+------+-----+---------+-------+641 3 rows in set (0.00 sec)642 643 mysql> INSERT INTO depts(deptId,depName) 644 -> VALUES 645 -> (1, ‘人事部‘), 646 -> (2, ‘研发部‘), 647 -> (3, ‘市场部‘), 648 -> (4, ‘培训部‘);649 ERROR 1062 (23000): Duplicate entry ‘0‘ for key ‘PRIMARY‘650 mysql> alter table depts modify Id int not null atuo_increment;651 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 ‘atuo_increment‘ at line 1652 mysql> alter table depts modify Id int atuo_increment;653 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 ‘atuo_increment‘ at line 1654 mysql> 655 mysql> 656 mysql> 657 mysql> drop table depts;658 Query OK, 0 rows affected (0.00 sec)659 660 mysql> show tables;661 +------------------+662 | Tables_in_systop |663 +------------------+664 | baseinfo |665 | qquser |666 | relation |667 | user |668 +------------------+669 4 rows in set (0.00 sec)670 671 mysql> create table depts(672 -> -> Id int primary key AUTO_INCREMENT,673 -> -> deptId int not null,674 -> -> depName nvarchar(50),);675 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 ‘-> Id int primary key AUTO_INCREMENT,676 -> deptId int not null,677 -> depName‘ at line 2678 mysql> show tables;679 +------------------+680 | Tables_in_systop |681 +------------------+682 | baseinfo |683 | qquser |684 | relation |685 | user |686 +------------------+687 4 rows in set (0.00 sec)688 689 mysql> create table depts(690 -> -> Id int primary key not null AUTO_INCREMENT,691 -> -> deptId int not null,692 -> -> depName nvarchar(50),);693 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 ‘-> Id int primary key not null AUTO_INCREMENT,694 -> deptId int not null,695 -‘ at line 2696 mysql> create table depts(697 -> Id int primary key not null AUTO_INCREMENT,698 -> deptId int not null,699 -> depName nvarchar(50),);700 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 4701 mysql> create table depts(702 -> Id int primary key not null AUTO_INCREMENT,703 -> deptId int not null,704 -> depName nvarchar(50));705 Query OK, 0 rows affected (0.00 sec)706 707 mysql> show tables;708 +------------------+709 | Tables_in_systop |710 +------------------+711 | baseinfo |712 | depts |713 | qquser |714 | relation |715 | user |716 +------------------+717 5 rows in set (0.00 sec)718 719 mysql> desc depts;720 +---------+-------------+------+-----+---------+----------------+721 | Field | Type | Null | Key | Default | Extra |722 +---------+-------------+------+-----+---------+----------------+723 | Id | int(11) | NO | PRI | NULL | auto_increment |724 | deptId | int(11) | NO | | NULL | |725 | depName | varchar(50) | YES | | NULL | |726 +---------+-------------+------+-----+---------+----------------+727 3 rows in set (0.00 sec)728 729 mysql> INSERT INTO depts(deptId,depName) 730 -> VALUES 731 -> (1, ‘人事部‘), 732 -> (2, ‘研发部‘), 733 -> (3, ‘市场部‘), 734 -> (4, ‘培训部‘);735 Query OK, 4 rows affected (0.00 sec)736 Records: 4 Duplicates: 0 Warnings: 0737 738 mysql> select *from depts;739 +----+--------+-----------+740 | Id | deptId | depName |741 +----+--------+-----------+742 | 1 | 1 | 人事部 |743 | 2 | 2 | 研发部 |744 | 3 | 3 | 市场部 |745 | 4 | 4 | 培训部 |746 +----+--------+-----------+747 4 rows in set (0.00 sec)748 749 mysql> 750 mysql> 751 mysql> 752 mysql> 753 mysql> 754 mysql> 755 mysql> create table employees(756 -> Id int primary key auto_increment,757 -> Name nvarchar(22) not null,758 -> flag varchar(20),759 -> JoinTime datetime,760 -> Money decimal,761 -> adress nvarchar(20),762 -> mark1 nvarchar(20),763 -> mark2 nvarchar(20)764 -> );765 Query OK, 0 rows affected (0.01 sec)766 767 mysql> 768 mysql> 769 mysql> show tables;770 +------------------+771 | Tables_in_systop |772 +------------------+773 | baseinfo |774 | depts |775 | employees |776 | qquser |777 | relation |778 | user |779 +------------------+780 6 rows in set (0.00 sec)781 782 mysql> INSERT INTO employees 783 -> VALUES 784 -> (‘01‘, ‘张三‘, ‘M‘, ‘2‘, ‘2008-02-02 12:12:12‘, ‘3500.00‘, ‘北京‘, ‘ ‘, ‘ ‘), 785 -> (‘02‘, ‘李四‘, ‘F‘, ‘4‘, ‘2007-02-20 13:13:13‘, ‘5000.00‘, ‘上海‘, ‘ ‘, ‘ ‘),786 -> (‘03‘, ‘王五‘, ‘M‘, ‘1‘, ‘2012-12-30 14:14:14‘, ‘7000.00‘, ‘福建‘, ‘ ‘, ‘ ‘),787 -> (‘04‘, ‘赵六‘, ‘F‘, ‘2‘, ‘2008-06-06 15:15:15‘, ‘2800.00‘, ‘广东‘, ‘ ‘, ‘ ‘),788 -> (‘05‘, ‘钱七‘, ‘M‘, ‘4‘, ‘2005-08-21 11:11:11‘, ‘8000.00‘, ‘山东‘, ‘ ‘, ‘ ‘),789 -> (‘06‘, ‘孙八‘, ‘F‘, ‘2‘, ‘2008-04-16 10:10:10‘, ‘2000.00‘, ‘河北‘, ‘ ‘, ‘ ‘);790 ERROR 1136 (21S01): Column count doesn‘t match value count at row 1791 mysql> select *from employees;792 Empty set (0.00 sec)793 794 mysql> desc employees;795 +----------+---------------+------+-----+---------+----------------+796 | Field | Type | Null | Key | Default | Extra |797 +----------+---------------+------+-----+---------+----------------+798 | Id | int(11) | NO | PRI | NULL | auto_increment |799 | Name | varchar(22) | NO | | NULL | |800 | flag | varchar(20) | YES | | NULL | |801 | JoinTime | datetime | YES | | NULL | |802 | Money | decimal(10,0) | YES | | NULL | |803 | adress | varchar(20) | YES | | NULL | |804 | mark1 | varchar(20) | YES | | NULL | |805 | mark2 | varchar(20) | YES | | NULL | |806 +----------+---------------+------+-----+---------+----------------+807 8 rows in set (0.00 sec)808 809 mysql> alter table employees add deptId int after flag;810 Query OK, 0 rows affected (0.01 sec)811 Records: 0 Duplicates: 0 Warnings: 0812 813 mysql> desc employees;814 +----------+---------------+------+-----+---------+----------------+815 | Field | Type | Null | Key | Default | Extra |816 +----------+---------------+------+-----+---------+----------------+817 | Id | int(11) | NO | PRI | NULL | auto_increment |818 | Name | varchar(22) | NO | | NULL | |819 | flag | varchar(20) | YES | | NULL | |820 | deptId | int(11) | YES | | NULL | |821 | JoinTime | datetime | YES | | NULL | |822 | Money | decimal(10,0) | YES | | NULL | |823 | adress | varchar(20) | YES | | NULL | |824 | mark1 | varchar(20) | YES | | NULL | |825 | mark2 | varchar(20) | YES | | NULL | |826 +----------+---------------+------+-----+---------+----------------+827 9 rows in set (0.00 sec)828 829 mysql> INSERT INTO employees 830 -> VALUES 831 -> (‘01‘, ‘张三‘, ‘M‘, ‘2‘, ‘2008-02-02 12:12:12‘, ‘3500.00‘, ‘北京‘, ‘ ‘, ‘ ‘), 832 -> (‘02‘, ‘李四‘, ‘F‘, ‘4‘, ‘2007-02-20 13:13:13‘, ‘5000.00‘, ‘上海‘, ‘ ‘, ‘ ‘),833 -> (‘03‘, ‘王五‘, ‘M‘, ‘1‘, ‘2012-12-30 14:14:14‘, ‘7000.00‘, ‘福建‘, ‘ ‘, ‘ ‘),834 -> (‘04‘, ‘赵六‘, ‘F‘, ‘2‘, ‘2008-06-06 15:15:15‘, ‘2800.00‘, ‘广东‘, ‘ ‘, ‘ ‘),835 -> (‘05‘, ‘钱七‘, ‘M‘, ‘4‘, ‘2005-08-21 11:11:11‘, ‘8000.00‘, ‘山东‘, ‘ ‘, ‘ ‘),836 -> (‘06‘, ‘孙八‘, ‘F‘, ‘2‘, ‘2008-04-16 10:10:10‘, ‘2000.00‘, ‘河北‘, ‘ ‘, ‘ ‘);837 Query OK, 6 rows affected (0.00 sec)838 Records: 6 Duplicates: 0 Warnings: 0839 840 mysql> select *from employees;841 +----+--------+------+--------+---------------------+-------+--------+-------+-------+842 | Id | Name | flag | deptId | JoinTime | Money | adress | mark1 | mark2 |843 +----+--------+------+--------+---------------------+-------+--------+-------+-------+844 | 1 | 张三 | M | 2 | 2008-02-02 12:12:12 | 3500 | 北京 | | |845 | 2 | 李四 | F | 4 | 2007-02-20 13:13:13 | 5000 | 上海 | | |846 | 3 | 王五 | M | 1 | 2012-12-30 14:14:14 | 7000 | 福建 | | |847 | 4 | 赵六 | F | 2 | 2008-06-06 15:15:15 | 2800 | 广东 | | |848 | 5 | 钱七 | M | 4 | 2005-08-21 11:11:11 | 8000 | 山东 | | |849 | 6 | 孙八 | F | 2 | 2008-04-16 10:10:10 | 2000 | 河北 | | |850 +----+--------+------+--------+---------------------+-------+--------+-------+-------+851 6 rows in set (0.00 sec)852 853 mysql> INSERT INTO baseinfo 854 -> (QQID, NickName, SEX, Age, Province, City, Address, Phone)855 -> VALUES856 -> ("54789625857 "> ", "蝴蝶飞飞858 "> 859 "> ", "1860 "> ", "16", "北京861 "> ", "朝阳862 "> ", "亚运村863 "> ", "37547388157668864 "> ");865 Query OK, 1 row affected (0.00 sec)866 867 mysql> 868 mysql> INSERT INTO baseinfo 869 -> (QQID, NickName, SEX, Age, Province, City, Address, Phone)870 -> VALUES871 -> ("88662753872 "> 873 "> ", "秋芙蓉874 "> 875 "> 876 "> ", "0877 "> ", "20", "河南878 "> ", "南阳879 "> ", "方城博望880 "> 881 "> ", "88715783657725882 "> 883 "> ");884 Query OK, 1 row affected (0.00 sec)885 886 mysql> 887 mysql> INSERT INTO baseinfo 888 -> (QQID, NickName, SEX, Age, Province, City, Address, Phone)889 -> VALUES890 -> ("8855678891 "> 892 "> ", "双眼皮の潴893 "> 894 "> 895 "> ", "1896 "> ", "38", "北京897 "> ", "海淀898 "> ", "双榆树东里899 "> 900 "> ", "65794968876143901 "> 902 "> ");903 Query OK, 1 row affected (0.00 sec)904 905 mysql> select *from baseinfo;906 +----------+--------------------+------+-----+----------+---------+-------------------+------------------+907 | QQID | NickName | SEX | Age | Province | City | Address | Phone |908 +----------+--------------------+------+-----+----------+---------+-------------------+------------------+909 | 54789625 | 蝴蝶飞飞910 911 | 1 | 16 | 北京912 | 朝阳913 | 亚运村914 | 37547388157668915 |916 | 88662753 | 秋芙蓉917 918 919 | 0 | 20 | 河南920 | 南阳921 | 方城博望922 923 | 88715783657725924 925 |926 | 8855678 | 双眼皮の潴927 928 929 | 1 | 38 | 北京930 | 海淀931 | 双榆树东里932 933 | 65794968876143934 935 |936 +----------+--------------------+------+-----+----------+---------+-------------------+------------------+937 3 rows in set (0.00 sec)938 939 mysql> 940 mysql> 941 mysql> 942 mysql> 943 mysql> 944 mysql> 945 mysql> Relation表添加数据946 -> INSERT INTO relation 947 -> (QQID, RelationQQID, RelationStatus)948 -> VALUES949 -> ("54789625950 "> ", "88662753951 "> 952 "> ", "0953 "> 954 "> 955 "> ");956 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 ‘Relation表添加数据957 INSERT INTO relation 958 (QQID, RelationQQID, RelationStat‘ at line 1959 mysql> 960 mysql> INSERT INTO relation 961 -> (QQID, RelationQQID, RelationStatus)962 -> VALUES963 -> ("88662753964 "> 965 "> ", "8855678966 "> 967 "> 968 "> ", "1969 "> 970 "> 971 "> ");972 Query OK, 1 row affected (0.00 sec)973 974 mysql> 975 mysql> INSERT INTO relation 976 -> (QQID, RelationQQID, RelationStatus)977 -> VALUES978 -> ("154789625979 "> 980 "> ", "8855678981 "> 982 ">
", "0
", "20", "河南
", "南阳
", "方城博望
", "88715783657725
");
INSERT INTO baseinfo
(QQID, NickName, SEX, Age, Province, City, Address, Phone)
VALUES
("8855678
", "双眼皮の潴
", "1
", "38", "北京
", "海淀
", "双榆树东里
", "65794968876143
");
INSERT INTO depts
VALUES
(‘1‘, ‘人事部‘, ‘ ‘),
(‘2‘, ‘研发部‘, ‘ ‘),
(‘3‘, ‘市场部‘, ‘ ‘),
(‘4‘, ‘培训部‘, ‘ ‘);
INSERT INTO employees
VALUES
(‘01‘, ‘张三‘, ‘M‘, ‘2‘, ‘2008-02-02 12:12:12‘, ‘3500.00‘, ‘北京‘, ‘ ‘, ‘ ‘),
(‘02‘, ‘李四‘, ‘F‘, ‘4‘, ‘2007-02-20 13:13:13‘, ‘5000.00‘, ‘上海‘, ‘ ‘, ‘ ‘),
(‘03‘, ‘王五‘, ‘M‘, ‘1‘, ‘2012-12-30 14:14:14‘, ‘7000.00‘, ‘福建‘, ‘ ‘, ‘ ‘),
(‘04‘, ‘赵六‘, ‘F‘, ‘2‘, ‘2008-06-06 15:15:15‘, ‘2800.00‘, ‘广东‘, ‘ ‘, ‘ ‘),
(‘05‘, ‘钱七‘, ‘M‘, ‘4‘, ‘2005-08-21 11:11:11‘, ‘8000.00‘, ‘山东‘, ‘ ‘, ‘ ‘),
(‘06‘, ‘孙八‘, ‘F‘, ‘2‘, ‘2008-04-16 10:10:10‘, ‘2000.00‘, ‘河北‘, ‘ ‘, ‘ ‘);
写博客刚开始起步，积累点经验！！！
浅谈“Mysql”的基础操作语句
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。