/*-------------------------------------------读者可以补充内容到下面--------------------------------------------------*/
//修改表名
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
", "秋芙蓉
your MySQL server version
mysql> create table qq_user(id int primary key );
Query OK, rows affected (0.01 sec)
mysql> show tables;
+------------------+
| Tables_in_systop |
+------------------+
| qq_user |
+------------------+
row in set (0.00 sec)
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql> create table qq_user(id int primary key )
-> create table qq_user(id/; int primary key )
ERROR (): You have an error
->
-> ;
ERROR (): You have an error
mysql> create table qq_user(
-> qq_id bigint primary key,
) not null,
-> ;
ERROR (): You have an error
mysql> create table qq_user(
-> ;
ERROR (): You have an error
mysql> show tables;
+------------------+
| Tables_in_systop |
+------------------+
| qq_user |
+------------------+
row in set (0.00 sec)
mysql> drop table qq_user;
Query OK, rows affected (0.00 sec)
mysql> show tables;
Empty set (0.00 sec)
mysql>
mysql>
mysql> create table qq_user(
-> qq_id bigint primary key,
) not null,
-> LastLoginTime datetime,
-> Oline int,
-> Level int);
Query OK, rows affected (0.00 sec)
mysql> show tables;
+------------------+
| Tables_in_systop |
+------------------+
| qq_user |
+------------------+
row in set (0.00 sec)
mysql> descl
-> ;
ERROR (): You have an error
mysql> desc;
ERROR (): You have an error
mysql> desc qq_user;
+---------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
) | NO | PRI | NULL | |
) | NO | | NULL | |
| LastLoginTime | datetime | YES | | NULL | |
) | YES | | NULL | |
) | YES | | NULL | |
+---------------+-------------+------+-----+---------+-------+
rows in set (0.00 sec)
mysql');
Query OK, row affected (0.00 sec)
mysql> select *from qq_user;
+--------+----------+---------------------+-------+-------+
| qq_id | Password | LastLoginTime | Oline | Level |
+--------+----------+---------------------+-------+-------+
:: |
+--------+----------+---------------------+-------+-------+
row in set (0.00 sec)
mysql>
mysql>
mysql> truncate qq_user;
Query OK, rows affected (0.00 sec)
mysql> selcet *from qq_user;
ERROR (): You have an error
mysql> show tables;
+------------------+
| Tables_in_systop |
+------------------+
| qq_user |
+------------------+
row in set (0.00 sec)
mysql> select * from qq_user;
Empty set (0.00 sec)
mysql> alter table qq_user rename user;
Query OK, rows affected (0.00 sec)
mysql> show tables;
+------------------+
| Tables_in_systop |
+------------------+
| user |
+------------------+
row in set (0.00 sec)
mysql> alter table user modify qq_id int;
Query OK, rows affected (0.00 sec)
Records: Duplicates: Warnings:
mysql> desc user;
+---------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
) | |
) | NO | | NULL | |
| LastLoginTime | datetime | YES | | NULL | |
) | YES | | NULL | |
) | YES | | NULL | |
+---------------+-------------+------+-----+---------+-------+
rows in set (0.00 sec)
mysql);
ERROR (): You have an error
mysql);
Query OK, rows affected (0.00 sec)
Records: Duplicates: Warnings:
mysql> desc user;
+---------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
) | |
) | NO | | NULL | |
| LastLoginTime | datetime | YES | | NULL | |
) | YES | | NULL | |
) | YES | | NULL | |
+---------------+-------------+------+-----+---------+-------+
rows in set (0.00 sec)
mysql>
mysql>
mysql> alter table user change qq_id id
-> ;
ERROR (): You have an error
mysql> alter table user change qq_id id int;
Query OK, rows affected (0.01 sec)
Records: Duplicates: Warnings:
mysql> desc user;
+---------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
) | |
) | NO | | NULL | |
| LastLoginTime | datetime | YES | | NULL | |
) | YES | | NULL | |
) | YES | | NULL | |
+---------------+-------------+------+-----+---------+-------+
rows in set (0.00 sec)
mysql) after id;
Query OK, rows affected (0.01 sec)
Records: Duplicates: Warnings:
mysql> desc user;
+---------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
) | |
) | YES | | NULL | |
) | NO | | NULL | |
| LastLoginTime | datetime | YES | | NULL | |
) | YES | | NULL | |
) | YES | | NULL | |
+---------------+-------------+------+-----+---------+-------+
rows in set (0.00 sec)
mysql> alter table user drop Name;
Query OK, rows affected (0.00 sec)
Records: Duplicates: Warnings:
mysql> desc user;
+---------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
) | |
) | NO | | NULL | |
| LastLoginTime | datetime | YES | | NULL | |
) | YES | | NULL | |
) | YES | | NULL | |
+---------------+-------------+------+-----+---------+-------+
rows in set (0.00 sec)
mysql> alter table user modify Level int after Password;
Query OK, rows affected (0.00 sec)
Records: Duplicates: Warnings:
mysql> desc user;
+---------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
) | |
) | NO | | NULL | |
) | YES | | NULL | |
| LastLoginTime | datetime | YES | | NULL | |
) | YES | | NULL | |
+---------------+-------------+------+-----+---------+-------+
rows in set (0.00 sec)
mysql);
Query OK, rows affected (0.00 sec)
Records: Duplicates: Warnings:
mysql> desc user;
+---------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
) | |
) | NO | | NULL | |
) | YES | | NULL | |
| LastLoginTime | datetime | YES | | NULL | |
) | YES | | NULL | |
+---------------+-------------+------+-----+---------+-------+
rows in set (0.00 sec)
mysql>
mysql>
mysql> show create user;
ERROR (): You have an error
mysql> show create table user;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user | CREATE TABLE `user` (
`id` ) ',
`Password` ) CHARACTER SET utf8 NOT NULL,
`) DEFAULT NULL,
`LastLoginTime` datetime DEFAULT NULL,
`Oline` ) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
row in set (0.00 sec)
mysql> desc user;
+---------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
) | |
) | NO | | NULL | |
) | YES | | NULL | |
| LastLoginTime | datetime | YES | | NULL | |
) | YES | | NULL | |
+---------------+-------------+------+-----+---------+-------+
rows in set (0.00 sec)
mysql) ;
Query OK, rows affected (0.00 sec)
Records: Duplicates: Warnings:
mysql> desc user;
+---------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
) | |
) | NO | | NULL | |
) | |
| LastLoginTime | datetime | YES | | NULL | |
) | YES | | NULL | |
+---------------+-------------+------+-----+---------+-------+
rows in set (0.00 sec)
mysql;
ERROR (): You have an error
mysql;
ERROR (42S22): Unknown column 'Olin' in 'user'
mysql;
Query OK, rows affected (0.00 sec)
Records: Duplicates: Warnings:
mysql> desc user;
+---------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
) | |
) | NO | | NULL | |
) | |
| LastLoginTime | datetime | YES | | NULL | |
) | |
+---------------+-------------+------+-----+---------+-------+
rows in set (0.00 sec)
mysql) ;
ERROR (): You have an error
mysql) ;
ERROR (): You have an error
mysql> desc user;
+---------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
) | |
) | NO | | NULL | |
) | |
| LastLoginTime | datetime | YES | | NULL | |
) | |
+---------------+-------------+------+-----+---------+-------+
rows in set (0.00 sec)
mysql) ';
Query OK, rows affected (0.01 sec)
Records: Duplicates: Warnings:
mysql> desc;
ERROR (): You have an error
mysql> desc user;
+---------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
) | |
) | |
) | |
| LastLoginTime | datetime | YES | | NULL | |
) | |
+---------------+-------------+------+-----+---------+-------+
rows in set (0.00 sec)
mysql,);
Query OK, rows affected (0.03 sec)
Records: Duplicates: Warnings:
mysql> desc user;
+---------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
) | |
) | |
) | |
| LastLoginTime | datetime | YES | | NULL | |
) | |
) | YES | | NULL | |
+---------------+-------------+------+-----+---------+-------+
rows in set (0.00 sec)
mysql> alter table user modify age int after password;
Query OK, rows affected (0.01 sec)
Records: Duplicates: Warnings:
mysql> desc user;
+---------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
) | |
) | |
) | YES | | NULL | |
) | |
| LastLoginTime | datetime | YES | | NULL | |
) | |
+---------------+-------------+------+-----+---------+-------+
rows in set (0.00 sec)
mysql) not null default 'guo' after id;
Query OK, rows affected (0.01 sec)
Records: Duplicates: Warnings:
mysql> desc user;
+---------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
) | |
) | NO | | guo | |
) | |
) | YES | | NULL | |
) | |
| LastLoginTime | datetime | YES | | NULL | |
) | |
+---------------+-------------+------+-----+---------+-------+
rows in set (0.00 sec)
mysql> CREATE TABLE baseinfo(
-> QQID BIGINT NOT NULL AUTO_INCREMENT,
) NOT NULL,
),
-> Age INT NOT NULL,
) NOT NULL,
),
),
),
-> PRIMARY KEY ( QQID )
-> );
Query OK, rows affected (0.00 sec)
mysql> show tables;
+------------------+
| Tables_in_systop |
+------------------+
| baseinfo |
| user |
+------------------+
rows in set (0.00 sec)
mysql> desc baseinfo;
+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
) | NO | PRI | NULL | auto_increment |
) | NO | | NULL | |
) | YES | | NULL | |
) | NO | | NULL | |
) | NO | | NULL | |
) | YES | | NULL | |
) | YES | | NULL | |
) | YES | | NULL | |
+----------+--------------+------+-----+---------+----------------+
rows in set (0.00 sec)
mysql> CREATE TABLE qquser(
-> QQID BIGINT NOT NULL AUTO_INCREMENT,
) NOT NULL,
-> LastLogTime DATETIME,
),
-> Level INT NOT NULL,
-> PRIMARY KEY ( QQID )
-> );
Query OK, rows affected (0.00 sec)
mysql> show tables;
+------------------+
| Tables_in_systop |
+------------------+
| baseinfo |
| qquser |
| user |
+------------------+
rows in set (0.00 sec)
mysql> desc user;
+---------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
) | |
) | NO | | guo | |
) | |
) | YES | | NULL | |
) | |
| LastLoginTime | datetime | YES | | NULL | |
) | |
+---------------+-------------+------+-----+---------+-------+
rows in set (0.00 sec)
mysql> desc qquser;
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
) | NO | PRI | NULL | auto_increment |
) | NO | | NULL | |
| LastLogTime | datetime | YES | | NULL | |
) | NO | | NULL | |
) | NO | | NULL | |
+-------------+--------------+------+-----+---------+----------------+
rows in set (0.00 sec)
mysql> CREATE TABLE relation(
-> QQID BIGINT NOT NULL AUTO_INCREMENT,
-> RelationQQID BIGINT NOT NULL,
),
-> PRIMARY KEY ( QQID )
-> );
Query OK, rows affected (0.01 sec)
mysql> show tables;
+------------------+
| Tables_in_systop |
+------------------+
| baseinfo |
| qquser |
| relation |
| user |
+------------------+
rows in set (0.00 sec)
mysql> desc relation;
+----------------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+------------+------+-----+---------+----------------+
) | NO | PRI | NULL | auto_increment |
) | NO | | NULL | |
) | NO | | NULL | |
+----------------+------------+------+-----+---------+----------------+
rows in set (0.00 sec)
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql> INSERT INTO qquser
-> (QQID, PassWord, LastLogTime, Online, Level)
-> VALUES
"> ", "add512#&
" ::
"", "");
Query OK, row affected (0.00 sec)
mysql> select *from qquser;
+----------+-----------+---------------------+--------+-------+
| QQID | PassWord | LastLogTime | Online | Level |
+----------+-----------+---------------------+--------+-------+
| add512#&
:: |
+----------+-----------+---------------------+--------+-------+
row in set (0.00 sec)
mysql> show tables;
+------------------+
| Tables_in_systop |
+------------------+
| baseinfo |
| qquser |
| relation |
| user |
+------------------+
rows in set (0.00 sec)
mysql> ceate table depts;
ERROR (): You have an error
mysql> create table depts;
ERROR (): A column
mysql> create table depts(
-> Id int primary key,
-> deptId int not null,
),);
ERROR (): You have an error
mysql));
Query OK, rows affected (0.00 sec)
mysql>
mysql>
mysql>
mysql>
mysql> INSERT INTO depts
-> VALUES
', '人事部', ' '),
', '研发部', ' '),
', '市场部', ' '),
', '培训部', ' ');
Query OK, rows affected, warnings (0.00 sec)
Records: Duplicates: Warnings:
mysql> show tables;
+------------------+
| Tables_in_systop |
+------------------+
| baseinfo |
| depts |
| qquser |
| relation |
| user |
+------------------+
rows in set (0.00 sec)
mysql> select * from depts;
+----+--------+---------+
| Id | deptId | depName |
+----+--------+---------+
| |
| |
| |
| |
+----+--------+---------+
rows in set (0.00 sec)
mysql> delect from depts;
ERROR (): You have an error
mysql> delete *from depts;
ERROR (): You have an error
mysql> delete from depts;
Query OK, rows affected (0.00 sec)
mysql> select *from
Display possibilities? (y or n)
mysql> select *from depts;
Empty set (0.00 sec)
mysql> INSERT INTO depts
-> VALUES
', '人事部', ' '),
', '研发部', ' '),
', '市场部', ' '),
', '研发部', ' '), ;
ERROR (): You have an error
mysql> INSERT INTO depts
-> VALUES
', '人事部'),
', '研发部'),
', '市场部'),
', '培训部');
ERROR (21S01): Column count doesn't match value count at row 1
mysql> select *from depts;
Empty set (0.00 sec)
mysql> INSERT INTO depts(deptId,depName)
-> VALUES
', '人事部'),
', '研发部'),
', '市场部'),
', '培训部');
' for key 'PRIMARY'
mysql> desc depts;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| Id | int(11) | NO | PRI | NULL | |
| deptId | int(11) | NO | | NULL | |
| depName | varchar(50) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> INSERT INTO depts(deptId,depName)
-> VALUES
-> (1, '人事部'),
-> (2, '研发部'),
-> (3, '市场部'),
-> (4, '培训部');
' for key 'PRIMARY'
mysql> alter table depts modify Id int not null atuo_increment;
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 1
mysql> alter table depts modify Id int atuo_increment;
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 1
mysql>
mysql>
mysql>
mysql> drop table depts;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+------------------+
| Tables_in_systop |
+------------------+
| baseinfo |
| qquser |
| relation |
| user |
+------------------+
4 rows in set (0.00 sec)
mysql> create table depts(
-> -> Id int primary key AUTO_INCREMENT,
-> -> deptId int not null,
-> -> depName nvarchar(50),);
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,
-> deptId int not null,
-> depName' at line 2
mysql> show tables;
+------------------+
| Tables_in_systop |
+------------------+
| baseinfo |
| qquser |
| relation |
| user |
+------------------+
4 rows in set (0.00 sec)
mysql> create table depts(
-> -> Id int primary key not null AUTO_INCREMENT,
-> -> deptId int not null,
-> -> depName nvarchar(50),);
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,
-> deptId int not null,
-' at line 2
mysql> create table depts(
-> Id int primary key not null AUTO_INCREMENT,
-> deptId int not null,
-> depName nvarchar(50),);
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 4
mysql> create table depts(
-> Id int primary key not null AUTO_INCREMENT,
-> deptId int not null,
-> depName nvarchar(50));
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+------------------+
| Tables_in_systop |
+------------------+
| baseinfo |
| depts |
| qquser |
| relation |
| user |
+------------------+
5 rows in set (0.00 sec)
mysql> desc depts;
+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| Id | int(11) | NO | PRI | NULL | auto_increment |
| deptId | int(11) | NO | | NULL | |
| depName | varchar(50) | YES | | NULL | |
+---------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> INSERT INTO depts(deptId,depName)
-> VALUES
-> (1, '人事部'),
-> (2, '研发部'),
-> (3, '市场部'),
-> (4, '培训部');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select *from depts;
+----+--------+-----------+
| Id | deptId | depName |
+----+--------+-----------+
| 1 | 1 | 人事部 |
| 2 | 2 | 研发部 |
| 3 | 3 | 市场部 |
| 4 | 4 | 培训部 |
+----+--------+-----------+
4 rows in set (0.00 sec)
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql> create table employees(
-> Id int primary key auto_increment,
-> Name nvarchar(22) not null,
-> flag varchar(20),
-> JoinTime datetime,
-> Money decimal,
-> adress nvarchar(20),
-> mark1 nvarchar(20),
-> mark2 nvarchar(20)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql>
mysql> show tables;
+------------------+
| Tables_in_systop |
+------------------+
| baseinfo |
| depts |
| employees |
| qquser |
| relation |
| user |
+------------------+
6 rows in set (0.00 sec)
mysql> INSERT INTO employees
-> VALUES
::', '3500.00', '北京', ' ', ' '),
::', '5000.00', '上海', ' ', ' '),
::', '7000.00', '福建', ' ', ' '),
::', '2800.00', '广东', ' ', ' '),
::', '8000.00', '山东', ' ', ' '),
::', '2000.00', '河北', ' ', ' ');
mysql> select *from employees;
Empty set (0.00 sec)
mysql> desc employees;
+----------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+----------------+
) | NO | PRI | NULL | auto_increment |
) | NO | | NULL | |
) | YES | | NULL | |
| JoinTime | datetime | YES | | NULL | |
,) | YES | | NULL | |
) | YES | | NULL | |
) | YES | | NULL | |
) | YES | | NULL | |
+----------+---------------+------+-----+---------+----------------+
rows in set (0.00 sec)
mysql> alter table employees add deptId int after flag;
Query OK, rows affected (0.01 sec)
Records: Duplicates: Warnings:
mysql> desc employees;
+----------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+----------------+
) | NO | PRI | NULL | auto_increment |
) | NO | | NULL | |
) | YES | | NULL | |
) | YES | | NULL | |
| JoinTime | datetime | YES | | NULL | |
,) | YES | | NULL | |
) | YES | | NULL | |
) | YES | | NULL | |
) | YES | | NULL | |
+----------+---------------+------+-----+---------+----------------+
rows in set (0.00 sec)
mysql> INSERT INTO employees
-> VALUES
', '2008-02-02 12:12:12', '3500.00', '北京', ' ', ' '),
', '2007-02-20 13:13:13', '5000.00', '上海', ' ', ' '),
', '2012-12-30 14:14:14', '7000.00', '福建', ' ', ' '),
', '2008-06-06 15:15:15', '2800.00', '广东', ' ', ' '),
', '2005-08-21 11:11:11', '8000.00', '山东', ' ', ' '),
', '2008-04-16 10:10:10', '2000.00', '河北', ' ', ' ');
Query OK, rows affected (0.00 sec)
Records: Duplicates: Warnings:
mysql> select *from employees;
+----+--------+------+--------+---------------------+-------+--------+-------+-------+
| Id | Name | flag | deptId | JoinTime | Money | adress | mark1 | mark2 |
+----+--------+------+--------+---------------------+-------+--------+-------+-------+
:: | 北京 | | |
:: | 上海 | | |
:: | 福建 | | |
:: | 广东 | | |
:: | 山东 | | |
:: | 河北 | | |
+----+--------+------+--------+---------------------+-------+--------+-------+-------+
rows in set (0.00 sec)
mysql> INSERT INTO baseinfo
-> (QQID, NickName, SEX, Age, Province, City, Address, Phone)
-> VALUES
"> ", "蝴蝶飞飞
">
"
"", "北京
"> ", "朝阳
"> ", "亚运村
"
"> ");
Query OK, row affected (0.00 sec)
mysql>
mysql> INSERT INTO baseinfo
-> (QQID, NickName, SEX, Age, Province, City, Address, Phone)
-> VALUES
">
"> ", "秋芙蓉
">
">
"
"", "河南
"> ", "南阳
"> ", "方城博望
">
"
">
"> ");
Query OK, row affected (0.00 sec)
mysql>
mysql> INSERT INTO baseinfo
-> (QQID, NickName, SEX, Age, Province, City, Address, Phone)
-> VALUES
">
"> ", "双眼皮の潴
">
">
"
"", "北京
"> ", "海淀
"> ", "双榆树东里
">
"
">
"> ");
Query OK, row affected (0.00 sec)
mysql> select *from baseinfo;
+----------+--------------------+------+-----+----------+---------+-------------------+------------------+
| QQID | NickName | SEX | Age | Province | City | Address | Phone |
+----------+--------------------+------+-----+----------+---------+-------------------+------------------+
| 蝴蝶飞飞
| 北京
| 朝阳
| 亚运村
|
| 秋芙蓉
| 河南
| 南阳
| 方城博望
|
| 双眼皮の潴
| 北京
| 海淀
| 双榆树东里
|
+----------+--------------------+------+-----+----------+---------+-------------------+------------------+
rows in set (0.00 sec)
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql> Relation表添加数据
-> INSERT INTO relation
-> (QQID, RelationQQID, RelationStatus)
-> VALUES
"
">
"
">
">
"> ");
ERROR (): 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表添加数据
INSERT INTO relation
mysql>
mysql> INSERT INTO relation
-> (QQID, RelationQQID, RelationStatus)
-> VALUES
">
"
">
">
"
">
">
"> ");
Query OK, row affected (0.00 sec)
mysql>
mysql> INSERT INTO relation
-> (QQID, RelationQQID, RelationStatus)
-> VALUES
">
"
">
"> ", "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', '河北', ' ', ' ');
写博客刚开始起步，积累点经验！！！