投稿
  1. 极客之音首页
  2. 操作系统

MySQL（一）库和表的基本操作 DDL

小半 • • 操作系统 • 阅读 166

导读：本篇文章讲解 MySQL（一）库和表的基本操作 DDL，希望对大家有帮助，欢迎收藏，转发！站点地址：www.bmabk.com

主要介绍数据定义语言DDL

  • CREATE
  • DROP
  • ALTER

一、登入数据库

1、本地登录

[root@localhost ~]# mysql -uroot -p 
Enter password:

2、远程客户端登录（-h：mysql服务器IP地址）

[root@client ~]# mysql -h192.168.14.212 -uroot -p
Enter password:

3、修改登录密码

1、mysql>SET PASSWORD FOR 'USERNAME'@'HOST'=PASSWORD('new_password');
	SET PASSWORD FOR 'root'@'localhost'=PASSWORD('123456');
	FLUSH PRIVILEGES;

2、#mysqladmin -u USERNAME -h HOSTNAME password 'new_password' -p

3、mysql>UPDATE user SET Passwrod=PASSWORD('new_password') WHERE USER='root' AND Host='127.0.0.1';
	MariaDB [mysql]> UPDATE  user SET Password=PASSWORD('123456') WHERE USER='root' AND Host='127.0.0.1';

二、库的操作

1、创建数据库

database后面跟新建数据库名称
MariaDB [(none)]> CREATE DATABASE company;

2、查看数据库

MariaDB [(none)]> SHOW DATABASES;

3、选择数据库

MariaDB [(none)]> USE company;

4、删除数据库

MariaDB [(none)]> DROP DATABASE company;

三、表的操作

1、创建表的语法形式

MariaDB [company]> CREATE TABLE t_dept(depton INT,dname VARCHAR(20),loc VARCHAR(40));

表名：t_dept
属性名：depton、dname、loc
数据类型：INT、VARCHAR

2、查看表

MariaDB [company]> DESC t_dept;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| depton | int(11)     | YES  |     | NULL    |       |
| dname  | varchar(20) | YES  |     | NULL    |       |
| loc    | varchar(40) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

3、删除表

MariaDB [company]> DROP TABLE t_dept;
Query OK, 0 rows affected (0.01 sec)

再次查看提示表不存在
MariaDB [company]> DESC t_dept;
ERROR 1146 (42S02): Table 'company.t_dept' doesn't exist

4、修改表

4.1修改表名

#ALTER TABLE 旧表名 RENAME 新表名;
MariaDB [company]> ALTER TABLE t_dept RENAME tab_dept;

查看修改之后
MariaDB [company]> SHOW TABLES;
+-------------------+
| Tables_in_company |
+-------------------+
| tab_dept          |
+-------------------+

4.2增加字段

------------1、在表的最后一个位置添加字段------------
MariaDB [company]> ALTER TABLE tab_dept ADD descri VARCHAR(20);
Query OK, 0 rows affected (0.04 sec)               
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [company]> DESC tab_dept;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| depton | int(11)     | YES  |     | NULL    |       |
| dname  | varchar(20) | YES  |     | NULL    |       |
| loc    | varchar(40) | YES  |     | NULL    |       |
| descri | varchar(20) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

------------2、在表的第一个位置添加字段------------
MariaDB [company]> ALTER TABLE tab_dept ADD descri2 VARCHAR(20) FIRST;
Query OK, 0 rows affected (0.04 sec)               
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [company]> DESC tab_dept;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| descri2 | varchar(20) | YES  |     | NULL    |       |
| depton  | int(11)     | YES  |     | NULL    |       |
| dname   | varchar(20) | YES  |     | NULL    |       |
| loc     | varchar(40) | YES  |     | NULL    |       |
| descri  | varchar(20) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+

------------3、在表的指定字段之后添加字段------------
MariaDB [company]> ALTER TABLE tab_dept ADD descri3 VARCHAR(20) AFTER depton;
Query OK, 0 rows affected (0.04 sec)               
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [company]> DESC tab_dept;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| descri2 | varchar(20) | YES  |     | NULL    |       |
| depton  | int(11)     | YES  |     | NULL    |       |
| descri3 | varchar(20) | YES  |     | NULL    |       |
| dname   | varchar(20) | YES  |     | NULL    |       |
| loc     | varchar(40) | YES  |     | NULL    |       |
| descri  | varchar(20) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+

4.3删除字段

MariaDB [company]> ALTER TABLE tab_dept DROP descri3;
Query OK, 0 rows affected (0.05 sec)               
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [company]> DESC tab_dept;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| descri2 | varchar(20) | YES  |     | NULL    |       |
| depton  | int(11)     | YES  |     | NULL    |       |
| dname   | varchar(20) | YES  |     | NULL    |       |
| loc     | varchar(40) | YES  |     | NULL    |       |
| descri  | varchar(20) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+

4.4修改字段

------------1、修改字段的数据类型（把descri的字段类型修改为int）------------
MariaDB [company]> ALTER TABLE tab_dept MODIFY descri INT;
Query OK, 0 rows affected (0.04 sec)               
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [company]> DESC tab_dept;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| descri2 | varchar(20) | YES  |     | NULL    |       |
| depton  | int(11)     | YES  |     | NULL    |       |
| dname   | varchar(20) | YES  |     | NULL    |       |
| loc     | varchar(40) | YES  |     | NULL    |       |
| descri  | int(11)     | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+

------------2、修改字段的名字（change 旧属性名 新属性名 旧数据类型）------------
MariaDB [company]> ALTER TABLE tab_dept CHANGE loc location VARCHAR(40);
Query OK, 0 rows affected (0.03 sec)               
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [company]> DESC tab_dept;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| descri2  | varchar(20) | YES  |     | NULL    |       |
| depton   | int(11)     | YES  |     | NULL    |       |
| dname    | varchar(20) | YES  |     | NULL    |       |
| location | varchar(40) | YES  |     | NULL    |       |
| descri   | int(11)     | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

------------3、同时修改字段的名字和属性------------
MariaDB [company]> ALTER TABLE tab_dept CHANGE descri2 Age INT;
Query OK, 0 rows affected (0.05 sec)               
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [company]> DESC tab_dept;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| Age      | int(11)     | YES  |     | NULL    |       |
| depton   | int(11)     | YES  |     | NULL    |       |
| dname    | varchar(20) | YES  |     | NULL    |       |
| location | varchar(40) | YES  |     | NULL    |       |
| descri   | int(11)     | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

------------4、修改字段的顺序------------
MariaDB [company]> ALTER TABLE tab_dept MODIFY location VARCHAR(40) FIRST;
Query OK, 0 rows affected (0.04 sec)               
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [company]> DESC tab_dept;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| location | varchar(40) | YES  |     | NULL    |       |
| Age      | int(11)     | YES  |     | NULL    |       |
| depton   | int(11)     | YES  |     | NULL    |       |
| dname    | varchar(20) | YES  |     | NULL    |       |
| descri   | int(11)     | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

 

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。

文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/95203.html

赞 (0)
小半的头像小半
0 0

相关推荐

极客之音——专业性很强的中文编程技术网站，欢迎收藏到浏览器，订阅我们！