MySQL基基基基础

MySQL基基基基础：

//1. #登陆mysql u用户名 p密码
mysql -uroot  -p****

//2.#列出所有数据库
show databases;
| information_schema |
| dagl               |
| mysql              |
| test               |

//3.#创建数据库dbtest
create database dbtest;

//4.#使用数据库dbtest
use dbtest;

//5.#创建表emp
create table dbtest.emp(
    -> ename varchar(10),
    -> hiredate date,
    -> sal int(2));

//6.#查看表的结构emp
desc dbtest.emp;
或者desc emp;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| ename    | varchar(10) | YES  |     | NULL    |       |
| hiredate | date        | YES  |     | NULL    |       |
| sal      | int(2)      | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

//7.#查看更详细的表结构：表的创建语句。
show create table emp;
//结果：
CREATE TABLE `emp` (
   `ename` varchar(10) DEFAULT NULL,
  `hiredate` date DEFAULT NULL,
  `sal` int(2) DEFAULT NULL)
 ENGINE=InnoDB DEFAULT CHARSET=utf8

//数据库引擎是:InnoDB

//8.#删除表emp
drop table emp;
show tables;//检查是否删除

========================================
//1.#修改表中的列类型（1）
desc emp;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| ename    | varchar(10) | YES  |     | NULL    |       |
| hiredate | date        | YES  |     | NULL    |       |
| sal      | int(2)      | YES  |     | NULL    |       |

alter table emp modify sal decimal(16,2);
desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(10)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(16,2) | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+

//2.#修改表中的列类型（2）
alter table emp change sal sal decimal(16,2);
desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(10)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(16,2) | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+

//3.#修改表中的列名及列类型
alter table emp change sal salary decimal(16,2);
desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(10)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| salary   | decimal(16,2) | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+

//4.#修改表名
alter table emp rename temp;
show tables;
+------------------+
| Tables_in_dbtest |
+------------------+
| temp             |
+------------------+

alter table temp rename to emp;
show tables;
+------------------+
| Tables_in_dbtest |
+------------------+
| emp              |
+------------------+
=================================

//1.#表字段的增加
alter table emp add column deptno int(3);
desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(10)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| salary   | decimal(16,2) | YES  |     | NULL    |       |
| deptno   | int(3)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+

//2.#表字段的删除
alter table emp drop column deptno;
desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(10)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| salary   | decimal(16,2) | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+