MySQl命令简述

最新推荐文章于 2024-09-08 16:18:33 发布

Star_ID

最新推荐文章于 2024-09-08 16:18:33 发布

阅读量543

点赞数 9

分类专栏： MySQL 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/Star_ID/article/details/134011883

版权

MySQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

DDL语句

创建数据库

create database test1;

查看系统中的数据库

show databases;

选择要操作的数据库

use test1

查看test1数据库中的表格

show tables;

删除数据库

drop database test1;

创建表

create table emp(ename varchar(10), hiredate date, sal decimal(10,2), deptno int(2));

查看emp表

desc emp;

输出

mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(10)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(2)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.18 sec)

查看创建表的SQL语句

show create table emp \G;

输出

mysql> show create table emp \G;
*************************** 1. row ***************************
       Table: emp
Create Table: CREATE TABLE `emp` (
  `ename` varchar(10) DEFAULT NULL,
  `hiredate` date DEFAULT NULL,
  `sal` decimal(10,2) DEFAULT NULL,
  `deptno` int(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

ERROR: 
No query specified

删除表

drop table emp;

修改表类型

alter table emp modify ename varchar(20);

输出

mysql> alter table emp modify ename varchar(20);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(20)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(2)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

增加表字段

alter table emp add column age int(3);

输出

ysql> alter table emp add column age int(3);
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(20)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(2)        | YES  |     | NULL    |       |
| age      | int(3)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

删除表字段

alter table emp drop column age

输出

mysql> alter table emp drop column age;
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(20)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(2)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

字段改名

alter table emp change age age1 int(4);

输出

mysql> alter table emp change age age1 int(10);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(20)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(2)        | YES  |     | NULL    |       |
| age1     | int(10)       | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

更改表名

alter table emp rename emp1;

输出

mysql> alter table emp rename emp1;
Query OK, 0 rows affected (0.04 sec)

mysql> desc emp;
ERROR 1146 (42S02): Table 'test1.emp' doesn't exist
mysql> desc emp1;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(20)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(2)        | YES  |     | NULL    |       |
| age1     | int(10)       | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

DML语句

插入记录

insert into emp1 values('lisa', '2003-02-01', '3000', 2);

输出

mysql> insert into emp1 values('lisa', '2003-02-01', '3000', 2);
Query OK, 1 row affected (0.01 sec)

mysql> select * from emp1;
+-------+------------+---------+--------+
| ename | hiredate   | sal     | deptno |
+-------+------------+---------+--------+
| lisa  | 2003-02-01 | 3000.00 |      2 |
+-------+------------+---------+--------+
1 row in set (0.00 sec)

更新记录

update emp1 set sal=4000 where ename='lisa'

输出

mysql> update emp1 set sal=4000 where ename='lisa';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from emp1;
+-------+------------+---------+--------+
| ename | hiredate   | sal     | deptno |
+-------+------------+---------+--------+
| lisa  | 2003-02-01 | 4000.00 |      2 |
+-------+------------+---------+--------+
1 row in set (0.00 sec)

删除记录

delete from emp1 where name='lisa';

输出

mysql> delete from emp1 where ename='lisa';
Query OK, 1 row affected (0.01 sec)

mysql> select * from emp1;
Empty set (0.00 sec)

查询记录

select * from emp1;

输出

mysql> select * from emp1;
+--------+------------+---------+--------+
| ename  | hiredate   | sal     | deptno |
+--------+------------+---------+--------+
| lisa   | 2003-02-01 | 4000.00 |      2 |
| zzx    | 2000-01-01 | 2000.00 |      1 |
| bjguan | 2004-04-02 | 5000.00 |      3 |
+--------+------------+---------+--------+
3 rows in set (0.00 sec)

条件查询

select * from emp1 where deptno=1;

输出

mysql> select * from emp1 where deptno=1;
+-------+------------+---------+--------+
| ename | hiredate   | sal     | deptno |
+-------+------------+---------+--------+
| zzx   | 2000-01-01 | 2000.00 |      1 |
+-------+------------+---------+--------+
1 row in set (0.00 sec)