MySQL表操作
分类:MYSQL 热度:

 
MySQL表操作
========================================================
    表是数据库存储数据的基本单位,由若干个字段组成,主要用来存储数据记录。表的操作包括创建表、查看表、修改表和删除表。这些操作都是数据库管理中最基本,也是最重要的操作。本节内容包括:
    创建表
    查看表结构
    表完整性--约束
    修改表
    复制表
    删除表
 
一、创建表(表的基本操作)
表school.student1
    字段  字段    字段
    name sex    age
    tom male    23 记录
    jack male        21 记录
    alice female    19 记录
 
语法:
    create table 表名(
   字段名1  类型[(宽度) 约束条件],
   字段名2  类型[(宽度) 约束条件],
   字段名3  类型[(宽度) 约束条件]
    );
    说明:在同一张表中,字段名是不能相同;宽度和约束条件可选,字段名和类型是必须的
 
mysql> CREATE  DATABASE school;      //创建数据库school
mysql> use school;
mysql> create table student1( name varchar(50),sex enum('male','female'),age int);
Query OK, 0 rows affected (0.33 sec)
mysql> show tables;                              //查看表(当前所在库)
mysql> desc student1;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| name  | varchar(50)           | YES  |     | NULL    |       |
| sex   | enum('male','female') | YES  |     | NULL    |       |
| age   | int(11)               | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)              
 
mysql> select name,sex,age from student1; //查询表中所有字段的值
Empty set (0.00 sec)
 
mysql> select * from student1;                    //查询表中所有字段的值
Empty set (0.00 sec)
 
mysql> select name,age from student1;    //查询表中指定字段的值
Empty set (0.00 sec)
 
向表中插入内容   语法:
insert into 表名(字段1,字段2...)  values(字段值列表...)
mysql> desc student1;               //查看表结构
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| name  | varchar(50)           | YES  |     | NULL    |       |
| sex   | enum('male','female') | YES  |     | NULL    |       |
| age   | int(11)               | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> insert into student1(name,sex,age) values ('tom','male',23);
Query OK, 1 row affected (0.00 sec)
mysql> insert into student1 values //顺序插入,不用指定字段名
    -> ('jack','male',21),   //字符类型的值使用单引号或双引号
    -> ('alice','female',19),                    //插入多个记录
    -> ('rose','female',34);
 
mysql> insert into student1(name) values('robin'); //只向指定的字段插入值
Query OK, 1 row affected (0.00 sec)
mysql> select * from student1;
+-------+--------+------+
| name  | sex    | age  |
+-------+--------+------+
| tom   | male   |   23 |
| jack  | male   |   21 |
| alice | female |   19 |
| rose  | female |   34 |
| robin | NULL   | NULL |
+-------+--------+------+
5 rows in set (0.00 sec)
 
表school.student2
    姓名              name varchar(50)
    出生年份        born_year    year
    生日              birthday    date
    上课时间        class_time    time
    注册时间        reg_time datetime
 
mysql> create table student2(name varchar(50), born_year year,birthday date,class_time time,reg_time datetime );
mysql> desc student2;
mysql> insert into student2 values('tom',now(),now(),now(),now());
mysql> insert into student2 values('jack',1982,19821120,123000,20140415162545);
mysql> insert into student2 values('alice',1982,19821120,123000,now());
 
 
表school.student3
姓名 name varchar(50)
性别 sex  enum('male','female')
爱好 hobby set('music','book','game','disc')
 
mysql> create table student3(name varchar(50),sex enum('male','female'),hobby set('music','book','game','disc'));
mysql> insert into student3 values('tom','male','book,game');
mysql> select * from student3;
mysql> insert into student3 values ('jack','male','film');
mysql> select * from student3;
mysql> desc student3;
mysql> show create table student3\G
 
 
二、查看表结构
1. DESCRIBE查看表结构
DESCRIBE 表名;
DESC 表名;
 
2. SHOW CREATE TABLE查看表详细结构
SHOW CREATE TABLE 表名;
 
 
三、表完整性约束
作用:用于保证数据的完整性和一致性
========================================================
约束条件 说明
PRIMARY KEY (PK) 标识该字段为该表的主键,可以唯一的标识记录
FOREIGN KEY (FK) 标识该字段为该表的外键
NOT NULL    标识该字段不能为空
UNIQUE KEY  (UK) 标识该字段的值是唯一的
AUTO_INCREMENT 标识该字段的值自动增长(整数类型,而且为主键)
DEFAULT        为该字段设置默认值
on delete cascade         级联删除
on update cascade        级联更新
 
UNSIGNED                 无符号
ZEROFILL                  使用0填充
========================================================
说明:
1. 是否允许为空,默认NULL,可设置NOT NULL,字段不允许为空,必须赋值
2. 字段是否有默认值,缺省的默认值是NULL,如果插入记录时不给字段赋值,此字段使用默认值
  sex enum('male','female') not null default 'male'
age int unsigned NOT NULL default 20 必须为正值(无符号) 不允许为空  默认是20
3. 是否是key
主键 primary key
外键 forengn key
索引 (index,unique...)
 
==DEFAULT、NOT NULL==
表school.student5
mysql> create table school.student5(
mysql> name varchar(50) not null,
mysql> sex enum('male','female') default 'male' not null, 
mysql> age int unsigned  default 18 not null, 
mysql> hobby set('music','disc','dance','book') default 'book,dance'
mysql> );
mysql> desc student5;
 
mysql> insert into student5 values('jack','male',20,'book');
mysql> select * from student5;
+------+-----+-----+-------+
| name | sex | age | hobby |
+------+-----+-----+-------+
| jack | male |  20 | book  | 
+------+-----+-----+-------+
1 row in set (0.00 sec)
 
mysql> insert into student5(name) values('robin');
mysql> insert into student5 values(NULL,'male',40,'book');
ERROR 1048 (23000): Column 'name' cannot be null
 
==设置唯一约束 UNIQUE==
方法一:CREATE TABLE company.department (dept_id INT, dept_name VARCHAR(30) UNIQUE,comment VARCHAR(50));
方法二:CREATE TABLE company.department1 ( dept_id INT,dept_name VARCHAR(30), comment VARCHAR(50),CONSTRAINT uk_name UNIQUE(dept_name));
 
==设置主键约束 PRIMARY KEY==
primary key字段的值是不允许重复
单列做主键
多列做主键(复合主键)
 
    1、单列做主键
表school.student6 
方法一:create table student6(id int auto_increment not null primary key, name varchar(50) not null, sex enum('male','female') not null default 'male', age int not null default 18);
 
表school.student7
方法二:create table student7(id int auto_increment not null,name varchar(50) not null, sex enum('male','female') not null default 'male', age int not null default 18, CONSTRAINT pk_id primary key(id));
 
mysql> desc student6;
 
mysql> insert into student6 values (1,'alice','female',22);
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from student6;
+----+-------+------+-----+
| id | name  | sex  | age |
+----+-------+------+-----+
|  1 | alice | female |  22 | 
+----+-------+------+-----+
1 row in set (0.00 sec)
 
mysql> insert into student6(name,sex,age) values('jack','male',19),('tom','male',23);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
mysql> select * from student6;
+----+-------+------+-----+
| id | name  | sex  | age |
+----+-------+------+-----+
|  1 | alice | female |  22 | 
|  2 | jack  | male  |  19 | 
|  3 | tom   | male  |  23 | 
+----+-------+------+-----+
3 rows in set (0.00 sec)
 
mysql> insert into student6 values(3,'robin','male',21);
ERROR 1062 (23000): Duplicate entry '3' for key 1
 
mysql> insert into student6 values(6,'robin','male',21);
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from student6;
+----+-------+------+-----+
| id | name  | sex  | age |
+----+-------+------+-----+
|  1 | alice | female |  22 | 
|  2 | jack  | male  |  19 | 
|  3 | tom   | male  |  23 | 
|  6 | robin | male  |  21 | 
+----+-------+------+-----+
4 rows in set (0.00 sec)
 
mysql> insert into student6(name,sex,age) values ('aaa','male','19');
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from student6;
+----+-------+------+-----+
| id | name  | sex  | age |
+----+-------+------+-----+
|  1 | alice | female |  22 | 
|  2 | jack  | male  |  19 | 
|  3 | tom   | male  |  23 | 
|  6 | robin | male  |  21 | 
|  7 | aaa   | male  |  19 | 
+----+-------+------+-----+
5 rows in set (0.00 sec)
 
    2、复合主键
表school.service
host_ip  存储主机IP
service_name      服务名
port  服务对应的端口
allow(allow,deny)  服务是否允许访问
 
主键: host_ip + port = primary key
 
mysql> create table service(host_ip varchar(15) not null, service_name varchar(10) not null,port varchar(5) not null, allow enum('allow','deny') default 'deny',primary key(host_ip,port));
Query OK, 0 rows affected (0.00 sec)
 
mysql> desc service;
+--------------+----------------------+------+-----+---------+-------+
| Field        | Type                 | Null | Key | Default | Extra |
+--------------+----------------------+------+-----+---------+-------+
| host_ip      | varchar(15)          | NO   | PRI | NULL    |       | 
| service_name | varchar(10)          | NO   |     | NULL    |       | 
| port         | varchar(5)           | NO   | PRI | NULL    |       | 
| allow        | enum('allow','deny') | YES  |     | deny    |       | 
+--------------+----------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
 
mysql> insert into service values('192.168.2.168','ftp','21','allow'), ('192.168.2.168','httpd','80','allow');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
mysql> select * from service;
+---------------+--------------+------+-------+
| host_ip       | service_name | port | allow |
+---------------+--------------+------+-------+
| 192.168.2.168 | ftp          | 21   | allow | 
| 192.168.2.168 | httpd        | 80   | allow | 
+---------------+--------------+------+-------+
2 rows in set (0.00 sec)
 
 
==设置字段值增 AUTO_INCREMENT==
表company.department2
CREATE TABLE department3 (dept_id INT PRIMARY AUTO_INCREMENT,dept_name VARCHAR(30),comment VARCHAR(50) );
 
 
==设置外键约束 FOREIGN KEY==
父表company.employees
 create table employees(name varchar(50) not null, mail varchar(20),primary key(name) )engine=innodb;
    
子表company.payroll
   mysql> create table payroll(id int(5) not null,name varchar(50) not null, payroll float(10,2) not null,primary key(id),
    -> CONSTRAINT fk_name foreign key(name)  references employees(name) )engine=innodb; 
 //name外键,关联父表(employees主键name)
 
==级联删除==
drop table if exists emp;
drop table if exists dept;
create table dept(id tinyint unsigned auto_increment,dname varchar(50) not null, primary key(id));
create table emp(id int unsigned auto_increment,ename varchar(20) not null,did tinyint unsigned,primary key(id),
foreign key(did) references  dept(id) on delete cascade);
insert into dept values(null,"Training"),(null,"Sales");
insert into dept(dname) values("Accounting"),("Planning"),("Production"),("HR");
insert into emp(ename,did) values("tom",1),("robin",1),("eric",2),("hellen",2);
insert into emp(ename,did) values("marry",3),("bob",3),("jack",4),("josn",4);
 
select  *  from  emp;
delete  from  dept  where  id=2;
select  *  from  emp;
 
==级联更新==
drop table if exists emp;
drop table if exists dept;
create table dept(id tinyint unsigned auto_increment,dname varchar(50) not null, primary key(id));
create table emp2(id int unsigned auto_increment,ename varchar(20) not null,did tinyint unsigned,primary key(id),
foreign key(did) references  dept(id) on update cascade);
insert into dept values(null,"Training"),(null,"Sales");
insert into dept(dname) values("Accounting"),("Planning"),("Production"),("HR");
insert into emp(ename,did) values("tom",1),("robin",1),("eric",2),("hellen",2);
insert into emp(ename,did) values("marry",3),("bob",3),("jack",4),("josn",4);
 
select  *  from emp;
update  dept  set  id=20  where  id=2;
select  *  from  emp;
 
==级联删除并级联更新==
drop table if exists emp;
drop table if exists dept;
create table dept(id tinyint unsigned auto_increment,dname varchar(50) not null, primary key(id));
create table emp(id int unsigned auto_increment,ename varchar(20) not null,did tinyint unsigned,primary key(id),
foreign key(did) references  dept(id) on delete cascade on update cascade);
insert into dept values(null,"Training"),(null,"Sales");
insert into dept(dname) values("Accounting"),("Planning"),("Production"),("HR");
insert into emp(ename,did) values("tom",1),("robin",1),("eric",2),("hellen",2);
insert into emp(ename,did) values("marry",3),("bob",3),("jack",4),("josn",4);
 
select  *  from emp;
update  dept  set  id=20  where  id=2;
select  *  from  emp;
delete from dept  where  id=20;
    
结论:
当父表中某个员工的记录修改时,子表也会同步修改  
当父表中删除某个员工的记录,子表也会同步删除
 
四、修改表ALTER TABLE
语法:
1. 修改表名
 ALTER TABLE 表名  RENAME 新表名;
 
2. 增加字段
 ALTER TABLE 表名
                    ADD 字段名  数据类型 [完整性约束条件…],
                        ADD 字段名  数据类型 [完整性约束条件…];
    ALTER TABLE 表名
                      ADD 字段名  数据类型 [完整性约束条件…]  FIRST;
    ALTER TABLE 表名
                   ADD 字段名  数据类型 [完整性约束条件…]  AFTER 字段名;
                 
3. 删除字段
 ALTER TABLE 表名  DROP 字段名;
 
4. 修改字段
 ALTER TABLE 表名  MODIFY  字段名 数据类型 [完整性约束条件…];
 ALTER TABLE 表名  CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
 ALTER TABLE 表名  CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];
 
示例:
1. 修改存储引擎
    mysql> alter table service  engine=innodb;
 
2. 添加字段
    mysql> alter table student10 add name varchar(20) not null, add age int(3) not null default 22;    
    mysql> alter table student10 add stu_num varchar(10) not null after name;        //添加name字段之后
    mysql> alter table student10 add sex enum('male','female') default 'male' first;  //添加到最前面
 
3. 删除字段
    mysql> alter table student10 drop sex;
    mysql> alter table service drop mac;
 
4. 修改字段类型modify
    mysql> alter table student10 modify age int(3);
    mysql> alter table student10 modify id int(11) not null primary key auto_increment; //修改为主键
 
5. 增加约束(针对已有的主键增加auto_increment)
    mysql> alter table student10 modify id int(11) not null primary key auto_increment;
    ERROR 1068 (42000): Multiple primary key defined
 
    mysql> alter table student10 modify id int(11) not null auto_increment;
    Query OK, 0 rows affected (0.01 sec)
    Records: 0  Duplicates: 0  Warnings: 0
 
6. 对已经存在的表增加复合主键
    mysql> alter table service2  add primary key(host_ip,port);
 
7. 增加主键
    mysql> alter table student1 modify name varchar(10) not null primary key;
 
8. 增加主键和自动增长
    mysql> alter table student1 modify id int not null primary key auto_increment;
 
9. 删除主键
    a. 删除自增约束
    mysql> alter table student10 modify id int(11) not null; 
    b. 删除主键
    mysql> alter table student10 drop primary key;
 
五、复制表
复制表结构+记录 (key不会复制: 主键、外键和索引)
    mysql> create table new_service select * from service;
 
只复制表结构
    mysql> select * from service where 1=2; //条件为假,查不到任何记录
    Empty set (0.00 sec)
    mysql> create table new1_service select * from service where 1=2;  
    Query OK, 0 rows affected (0.00 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    mysql> create table t4 like employees;--------------->key也会被复制!
    
六、删除表
DROP TABLE 表名;
========================================================
 
上一篇:MySQL数据类型 下一篇:MySQL数据操作: DML
猜你喜欢
各种观点
热门排行
精彩图文