//创建数据库
create database t_sys;
//实例化数据库
use t_sys;
//创建部门表
create table t_dept
(
p_id int primary key auto_increment not null, --主键自增
p_name varchar(20) not null
);
//查询部门表结构
show columns from t_dept;
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| p_id | int(11) | NO | PRI | NULL | auto_increment |
| p_name | varchar(20) | YES | | NULL | |
+--------+-------------+------+-----+---------+----------------+
//插入数据
insert into t_dept(p_name) values('技术部');
insert into t_dept(p_name) values('总裁办');
insert into t_dept(p_name) values('行政部');
//查询
select * from t_dept;
+------+--------+
| p_id | p_name |
+------+--------+
| 1 | 技术部 |
| 2 | 总裁办 |
| 3 | 行政部 |
+------+--------+
//创建员工表
create table t_emp
(
e_id int primary key auto_increment not null, --主键自增
p_id int references t_dept(p_id), --外建
e_name varchar(20) not null,
e_uname varchar(20) not null
e_password varchar(20) not null
);
//查询表机构
show columns from t_emp;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| e_id | int(11) | NO | PRI | NULL | auto_increment |
| p_id | int(11) | YES | | NULL | |
| e_name | varchar(20) | NO | | NULL | |
| e_uname | varchar(20) | NO | | NULL | |
| e_password | varchar(20) | NO | | NULL | |
+------------+-------------+------+-----+---------+----------------+
//插入数据
insert into t_emp(p_id,e_name,e_uname,e_password) values(1,'程博文','etccbw','123456');
insert into t_emp(p_id,e_name,e_uname,e_password) values(1,'jok','etccbw','123456');
insert into t_emp(p_id,e_name,e_uname,e_password) values(3,'tom','etccbw','123456');
insert into t_emp(p_id,e_name,e_uname,e_password) values(2,'胡晓丽','etccbw','123456');
//查询
select * from t_emp;
+------+------+--------+---------+------------+
| e_id | p_id | e_name | e_uname | e_password |
+------+------+--------+---------+------------+
| 1 | 1 | 程博文 | etccbw | 123456 |
| 2 | 1 | jok | etccbw | 123456 |
| 3 | 3 | tom | etccbw | 123456 |
| 4 | 2 | 胡晓丽 | etccbw | 123456 |
+------+------+--------+---------+------------+
//添加字段
alter table t_emp add column e_sex char(2);
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| e_id | int(11) | NO | PRI | NULL | auto_increment |
| p_id | int(11) | YES | | NULL | |
| e_name | varchar(20) | NO | | NULL | |
| e_uname | varchar(20) | NO | | NULL | |
| e_password | varchar(20) | NO | | NULL | |
| e_sex | char(2) | YES | | NULL | |
+------------+-------------+------+-----+---------+----------------+
//删除字段
alter table t_emp drop column e_sex;
mysql> desc t_emp;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| e_id | int(11) | NO | PRI | NULL | auto_increment |
| p_id | int(11) | YES | | NULL | |
| e_name | varchar(20) | NO | | NULL | |
| e_uname | varchar(20) | NO | | NULL | |
| e_password | varchar(20) | NO | | NULL | |
+------------+-------------+------+-----+---------+----------------+
//修改字段数据类型
alter table t_emp modify column e_sex varchar(20);
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| e_id | int(11) | NO | PRI | NULL | auto_increment |
| p_id | int(11) | YES | | NULL | |
| e_name | varchar(20) | NO | | NULL | |
| e_uname | varchar(20) | NO | | NULL | |
| e_password | varchar(20) | NO | | NULL | |
| e_sex | varchar(20) | YES | | NULL | |
+------------+-------------+------+-----+---------+----------------+
//mysql链接查询
//内连接查询两种方式
select * from t_dept inner join t_emp on t_dept.p_id=t_emp.p_id;
+------+--------+------+------+--------+---------+------------+-------+
| p_id | p_name | e_id | p_id | e_name | e_uname | e_password | e_sex |
+------+--------+------+------+--------+---------+------------+-------+
| 1 | 技术部 | 1 | 1 | 程博文 | etccbw | 123456 | NULL |
| 1 | 技术部 | 2 | 1 | jok | etccbw | 123456 | NULL |
| 3 | 行政部 | 3 | 3 | tom | etccbw | 123456 | NULL |
| 2 | 总裁办 | 4 | 2 | 胡晓丽 | etccbw | 123456 | NULL |
+------+--------+------+------+--------+---------+------------+-------+
select * from t_dept as t1,t_emp as t2 where t1.p_id=t2.p_id;
+------+--------+------+------+--------+---------+------------+-------+
| p_id | p_name | e_id | p_id | e_name | e_uname | e_password | e_sex |
+------+--------+------+------+--------+---------+------------+-------+
| 1 | 技术部 | 1 | 1 | 程博文 | etccbw | 123456 | NULL |
| 1 | 技术部 | 2 | 1 | jok | etccbw | 123456 | NULL |
| 3 | 行政部 | 3 | 3 | tom | etccbw | 123456 | NULL |
| 2 | 总裁办 | 4 | 2 | 胡晓丽 | etccbw | 123456 | NULL |
+------+--------+------+------+--------+---------+------------+-------+
//外(左、右)连接
select * from t_dept left join t_emp on t_dept.p_id=t_emp.p_id; --左链接
select * from t_dept right join t_emp on t_dept.p_id=t_emp.p_id; --右链接
+------+--------+------+------+--------+---------+------------+-------+
| p_id | p_name | e_id | p_id | e_name | e_uname | e_password | e_sex |
+------+--------+------+------+--------+---------+------------+-------+
| 1 | 技术部 | 1 | 1 | 程博文 | etccbw | 123456 | NULL |
| 1 | 技术部 | 2 | 1 | jok | etccbw | 123456 | NULL |
| 3 | 行政部 | 3 | 3 | tom | etccbw | 123456 | NULL |
| 2 | 总裁办 | 4 | 2 | 胡晓丽 | etccbw | 123456 | NULL |
| NULL | NULL | 5 | NULL | etcycy | etcycy | 123456 | NULL |
+------+--------+------+------+--------+---------+------------+-------+
mysql基本操作语法
最新推荐文章于 2024-08-14 22:03:11 发布