MySQL管理数据库、数据表、数据的基本运作是什么
发布时间:2021-12-21 13:39:45 所属栏目:MySql教程 来源:互联网
导读:本篇内容主要讲解MySQL管理数据库、数据表、数据的基本操作是什么,感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷,实用性强。下面就让小编来带大家学习MySQL管理数据库、数据表、数据的基本操作是什么吧! 注意:MySQL数据库命令不区分大小写,但在Mac
本篇内容主要讲解“MySQL管理数据库、数据表、数据的基本操作是什么”,感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷,实用性强。下面就让小编来带大家学习“MySQL管理数据库、数据表、数据的基本操作是什么”吧! 注意:MySQL数据库命令不区分大小写,但在Mac端如果想使用tab键自动补全命令,就必须使用大写。 1、数据库管理 1.1 create 创建数据库 mysql> create database test; Query OK, 1 row affected (0.50 sec) 1.2 show 查看所有数据库 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ 5 rows in set (0.32 sec) 1.3 alter 修改数据库 alter命令修改数据库编码: 一般我们设置的字符集使用的都是UTF8,若发现数据库编码不是UTF8,可使用该语句更改数据库编码: mysql> alter database test character set utf8; Query OK, 1 row affected (0.01 sec) 1.4 use 使用数据库 mysql> use test; Database changed 1.5 查看当前使用的数据库 mysql> select database(); +------------+ | database() | +------------+ | test | +------------+ 1 row in set (0.00 sec) 1.6 drop 删除数据库 mysql> drop database test; Query OK, 0 rows affected (0.00 sec) 2、数据库表管理 我们先创建一个数据库,以供我们后面使用: mysql> create database test; Query OK, 1 row affected (0.06 sec) 创建后,使用use命令进入数据库: mysql> use test; Database changed 2.1 create 创建表 mysql> create table test(id int auto_increment primary key, -> name varchar(20) not null, -> age int not null, -> birthday datetime); Query OK, 0 rows affected (0.14 sec) 2.2 show 显示表 mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | test | +----------------+ 1 row in set (0.00 sec) 2.3 desc 查看表结构 mysql> desc test; +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | | age | int(11) | NO | | NULL | | | birthday | datetime | YES | | NULL | | +----------+-------------+------+-----+---------+----------------+ 4 rows in set (0.01 sec) 2.4 alter 修改表结构(增、删、改) 2.4.1 add 在表中添加字段(列) mysql> alter table test add star bool; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 注意:在MySQL中布尔类型会自动转变为tinyint(1)类型 用desc查看可得到: mysql> desc test; +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | | age | int(11) | NO | | NULL | | | birthday | datetime | YES | | NULL | | | star | tinyint(1) | YES | | NULL | | +----------+-------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec) 2.4.2 modify 修改表字段(列) mysql> alter table test modify star int; Query OK, 0 rows affected (0.43 sec) Records: 0 Duplicates: 0 Warnings: 0 注意:int可以指定长度,也可以不指定,不指定时默认长度为int(11),修改后再次用desc查看: mysql> desc test; +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | | age | int(11) | NO | | NULL | | | birthday | datetime | YES | | NULL | | | star | int(11) | YES | | NULL | | +----------+-------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec) 2.4.3 drop 删除表字段(列) mysql> alter table test drop column star; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 查看表结构可以看到没有star字段了: mysql> desc test; +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | | age | int(11) | NO | | NULL | | | birthday | datetime | YES | | NULL | | +----------+-------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) 2.4.4 rename 重命名表名 mysql> rename table test to people; Query OK, 0 rows affected (0.01 sec) 再次查看表格,发现表名已改变: mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | people | +----------------+ 1 row in set (0.00 sec) 2.5 create 利用已有数据创建新表 mysql> create table newpeople select * from people; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 查看数据库存在的表发现已经有了: mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | newpeople | | people | +----------------+ 2 rows in set (0.00 sec) 3、数据的操作及管理 3.1 增加数据(增) mysql> insert into people values(null,'Anny',22,'1990-09-09'); Query OK, 1 row affected (0.00 sec) 用select查询: mysql> select * from people; +----+------+-----+---------------------+ | id | name | age | birthday | +----+------+-----+---------------------+ | 1 | Anny | 22 | 1990-09-09 00:00:00 | +----+------+-----+---------------------+ 1 row in set (0.00 sec) 3.2 删除数据(删) mysql> delete from people where name='Anny'; Query OK, 1 row affected (0.00 sec) 再用select查询,发现没有删除的数据了: mysql> select * from people; +----+------+-----+---------------------+ | id | name | age | birthday | +----+------+-----+---------------------+ | 2 | Lisa | 20 | 1992-09-09 00:00:00 | +----+------+-----+---------------------+ 1 row in set (0.00 sec) 3.3 修改数据(改) mysql> update people set name='Anny' where name='Lisa'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 再查数据可以看到Lisa名字变为anny了: mysql> select * from people; +----+------+-----+---------------------+ | id | name | age | birthday | +----+------+-----+---------------------+ | 2 | Anny | 20 | 1992-09-09 00:00:00 | +----+------+-----+---------------------+ 1 row in set (0.00 sec) 3.4 查询数据(查) 最简单的就是下面的语句: mysql> select * from people; +----+------+-----+---------------------+ | id | name | age | birthday | +----+------+-----+---------------------+ | 2 | Anny | 20 | 1992-09-09 00:00:00 | +----+------+-----+---------------------+ 1 row in set (0.00 sec) * 表示所有字段,也可以指定字段查询,如下: mysql> select name from people; +------+ | name | +------+ | Anny | +------+ 1 row in set (0.00 sec) 4、管理视图 4.1 创建视图 mysql> create view people_view(name,age) as select name,age from people; Query OK, 0 rows affected (0.00 sec) 创建成功后可以查看视图: mysql> select * from people_view; +------+-----+ | name | age | +------+-----+ | Anny | 20 | +------+-----+ 1 row in set (0.00 sec) 可以用desc 查看视图字段: mysql> desc people_view; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | name | varchar(20) | NO | | NULL | | | age | int(11) | NO | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) 4.2 替换视图 创建或替换原有视图: mysql> create or replace view people_view (id,name,age) as select id,name,age from people; Query OK, 0 rows affected (0.00 sec) 查看现有视图: mysql> select * from people_view; +----+------+-----+ | id | name | age | +----+------+-----+ | 2 | Anny | 20 | | 3 | Lisa | 22 | +----+------+-----+ 2 rows in set (0.00 sec) 4.3 操作视图 当视图有变化时,对应的表格也会跟着改变,即操作视图就是操作表格,我们在视图中插入一条数据: mysql> insert into people_view values('Lisa',22); Query OK, 1 row affected (0.08 sec) 插入成功后,我们查看视图数据: mysql> select * from people_view; +------+-----+ | name | age | +------+-----+ | Anny | 20 | | Lisa | 22 | +------+-----+ 2 rows in set (0.00 sec) 可以看到视图中已经有了,再查看我们表格数据: mysql> select * from people; +----+------+-----+---------------------+ | id | name | age | birthday | +----+------+-----+---------------------+ | 2 | Anny | 20 | 1992-09-09 00:00:00 | | 3 | Lisa | 22 | NULL | +----+------+-----+---------------------+ 2 rows in set (0.00 sec) 发现也有了对应的数据 4.4 删除视图: mysql> drop view people_view; Query OK, 0 rows affected (0.00 sec) 到此,相信大家对“MySQL管理数据库、数据表、数据的基本操作是什么”有了更深的了解,不妨来实际操作一番吧! (编辑:好传媒网) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |