mysql基本操作语法

//创建数据库
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  |
+------+--------+------+------+--------+---------+------------+-------+