翻开终端输入以下敕令:
/usr/local/MySQL/bin/mysql -u root -p
个中root为用户名。
这时候会涌现以下敕令:
Enter password:
此时假如你没有改暗码,直接敲回车。不然,输入你的暗码。
如许就能够接见你的数据库服务器了。
1、的操纵及治理
数据表的基
数据库(database)治理
1.1 create 建立数据库
create database firstDB;
1.2 show 检察一切数据库
mysql> show databases;+--------------------+| Database | +--------------------+| information_schema | | firstDB | | mysql | | performance_schema |+--------------------+rows in set (0.00 sec)
1.3 alter 修正数据库
alter 敕令修正数据库编码:
默许建立的数据库默许不支撑中文字符,假如我们须要它支撑中文字符,则将它的编码设置为utf8花样:
mysql> ALTER DATABASE testDB CHARACTER SET UTF8;Query OK, 1 row affected (0.00 sec)
1.4 use 运用数据库
mysql> use firstDB; Database changed
1.5 检察当前运用的数据库
mysql> select database(); +------------+| database() | +------------+| firstdb | +------------+1 row in set (0.00 sec)
1.6 drop 删除数据库
mysql> drop database firstDB; Query OK, 0 rows affected (0.00 sec)
2、数据表(table)治理
我们起首建立一个数据库,供应我们今后的运用:
mysql> create database testDB;Query OK, 1 row affected (0.00 sec)
建立跋文得用use敕令进入(运用)数据库,不然背面的操纵都邑不胜利的。
2.1 create 建立表
mysql> create table PEOPLE ( -> ID int AUTO_INCREMENT PRIMARY KEY, -> NAME varchar(20) not null, -> AGE int not null, -> BIRTHDAY datetime); Query OK, 0 rows affected (0.01 sec)
2.2 show 显现表
显现当前数据库一切的数据表
mysql> show tables; +------------------+| Tables_in_testdb | +------------------+| PEOPLE | +------------------+1 row in set (0.00 sec)
2.3 desc 检察表构造
mysql> desc PEOPLE -> ; +----------+-------------+------+-----+---------+----------------+| 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 修正表构造(增、删、改)
默许建立的表不支撑中文字符,所以需将表编码设置为utf8:
mysql> ALTER TABLE KEYCHAIN CONVERT TO CHARACTER SET UTF8; Query OK, 1 row affected (0.02 sec) Records: 1 Duplicates: 0 Warnings: 0
2.4.1 insert 在表中增添列(字段)
mysql> alter table PEOPLE add star BOOL;Query OK, 0 rows affected (0.02 sec)Records: 0 Duplicates: 0 Warnings: 0
提醒:在MySQL里,布尔范例会自动转换为tinyint(1)范例。
我们无妨运用desc去检察一下PEOPLE表构造:
mysql> desc PEOPLE; +----------+-------------+------+-----+---------+----------------+| 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 alter 修正表(列)字段
mysql> alter table PEOPLE MODIFY star int;Query OK, 0 rows affected (0.01 sec)Records: 0 Duplicates: 0 Warnings: 0
也能够指定 int(n) 的长度,比方 int(2)。
我们再次运用desc检察PEOPLE表构造:
mysql> desc PEOPLE; +----------+-------------+------+-----+---------+----------------+| 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 delete 删除表(列)字段
mysql> alter table PEOPLE DROP column star;Query OK, 0 rows affected (0.02 sec)Records: 0 Duplicates: 0 Warnings: 0
删除后,再次检察PEOPLE表构造:
mysql> desc PEOPLE; +----------+-------------+------+-----+---------+----------------+| 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)
删除字段胜利,如今我们已不能看到star的字段了。
2.4.4 rename 重命名表名
mysql> RENAME TABLE PEOPLE TO NEW_PEOPLE; Query OK, 0 rows affected (0.00 sec)
2.4.5 null or not null
修正表字段许可为空或不许可为空:
mysql> ALTER TABLE PEOPLE MODIFY AGE INT(3) NULL; Query OK, 0 rows affected (0.01 sec)Records: 0 Duplicates: 0 Warnings: 0
把 PEOPLE 表的 AGE 字段设置成“许可为空”,即插进去纪录时这个字段能够不录入。不然相反。
它的花样为:ALTER TABLE MODIFY
mysql> create table newTable select * from PEOPLE; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0
我们检察一下现在数据库存在的表:
mysql> show tables; +------------------+| Tables_in_testdb | +------------------+| PEOPLE || newTable | +------------------+2 rows in set (0.00 sec)
3、数据的操纵及治理
本操纵,包括增、删、改、查数据。
以下敕令均在PEOPLE表上操纵。
3.1 增添数据(增)
PEOPLE表现在是没有数据的,它是空的数据表,我们如今先增添一些数据。
insert into 敕令增添数据:
mysql> insert into PEOPLE VALUES (null, 'Anny', 22, '1992-05-22'); Query OK, 1 row affected (0.00 sec)
运用select敕令检察表(会在背面引见),如今我们检察PEOPLE数据表的数据:
mysql> select * from PEOPLE; +----+------+-----+---------------------+| ID | NAME | AGE | BIRTHDAY | +----+------+-----+---------------------+| 1 | Anny | 22 | 1992-05-22 00:00:00 | +----+------+-----+---------------------+1 row in set (0.00 sec)
数据表如今有一条数据。
我们多增添几条数据,如:
mysql> select * from PEOPLE; +----+--------+-----+---------------------+| ID | NAME | AGE | BIRTHDAY | +----+--------+-----+---------------------+| 1 | Anny | 22 | 1992-05-22 00:00:00 | | 2 | Garvey | 23 | 1991-05-22 00:00:00 | | 3 | Lisa | 25 | 1989-05-22 00:00:00 | | 4 | Nick | 24 | 1990-05-22 00:00:00 || 5 | Rick | 24 | 1991-05-22 00:00:00 | +----+--------+-----+---------------------+5 rows in set (0.00 sec)
3.2 删除数据(删)
delete 敕令删除数据:
mysql> delete from PEOPLE where name = 'Lisa'; Query OK, 1 row affected (0.01 sec)
再次查询PEOPLE表:
mysql> select * from PEOPLE; +----+--------+-----+---------------------+| ID | NAME | AGE | BIRTHDAY | +----+--------+-----+---------------------+| 1 | Anny | 22 | 1992-05-22 00:00:00 | | 2 | Garvey | 23 | 1991-05-22 00:00:00 | | 4 | Nick | 24 | 1990-05-22 00:00:00 || 5 | Rick | 24 | 1991-05-22 00:00:00 | +----+--------+-----+---------------------+4 rows in set (0.00 sec)
已看不到名为“Lisa”的数据了。
3.3 修正数据(改)
update 敕令修正数据:
mysql> update PEOPLE set name='Calvin' where name = 'Garvey'; Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0
查询PEOPLE表内容:
mysql> select * from PEOPLE; +----+--------+-----+---------------------+| ID | NAME | AGE | BIRTHDAY | +----+--------+-----+---------------------+| 1 | Anny | 22 | 1992-05-22 00:00:00 | | 2 | Calvin | 23 | 1991-05-22 00:00:00 | | 4 | Nick | 24 | 1990-05-22 00:00:00 || 5 | Rick | 24 | 1991-05-22 00:00:00 | +----+--------+-----+---------------------+4 rows in set (0.00 sec)
名为“Garvey”的纪录已修正为“Calvin”。
3.4 查询数据(查)
select 敕令查询数据,最简朴的就是查询表的一切数据,也就是我们最初运用到的那条敕令:
mysql> select * from PEOPLE; +----+--------+-----+---------------------+| ID | NAME | AGE | BIRTHDAY | +----+--------+-----+---------------------+| 1 | Anny | 22 | 1992-05-22 00:00:00 | | 2 | Calvin | 23 | 1991-05-22 00:00:00 | | 4 | Nick | 24 | 1990-05-22 00:00:00 || 5 | Rick | 24 | 1991-05-22 00:00:00 | +----+--------+-----+---------------------+4 rows in set (0.00 sec)
花样:select * from <表名>,*代表一切字段。
查询数据时也可指定显现的(列)字段:
mysql> select NAME, AGE, BIRTHDAY from PEOPLE; +--------+-----+---------------------+| NAME | AGE | BIRTHDAY | +--------+-----+---------------------+| Anny | 22 | 1992-05-22 00:00:00 | | Calvin | 23 | 1991-05-22 00:00:00 | | Nick | 24 | 1990-05-22 00:00:00 || Rick | 24 | 1991-05-22 00:00:00 | +--------+-----+---------------------+4 rows in set (0.00 sec)
花样:select <字段名,字段名,…> from <表名>。
select查询敕令另有许多的高等用法,比方用来查找不反复(distinct)的数据,使数据按前提排序(order by),按查询前提显现数据(where)等等。这些都邑鄙人一篇文章作重点引见,请人人继承注意我的博客,感谢。
4、治理视图
4.1 建立视图
视图是从数据库里导出一个或多个表的假造表,是用来轻易用户对数据的操纵。
mysql> CREATE VIEW PEOPLE_VIEW ( -> NAME, AGE) -> AS SELECT NAME, AGE FROM PEOPLE;
建立胜利后检察视图。
PEOPLE PEOPLE.AGE PEOPLE.BIRTHDAY PEOPLE.ID PEOPLE.NAME mysql> SELECT * FROM PEOPLE_VIEW -> ;+--------+-----+ | NAME | AGE | +--------+-----+ | Anny | 22 | | Calvin | 23 | | Nick | 24 | | Rick | 24 | +--------+-----+4 rows in set (0.00 sec)
我们也能够运用 DESC 敕令检察视图的构造。
mysql> DESC PEOPLE_VIEW; +-------+---------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+| ID | int(11) | NO | | 0 | | +-------+---------+------+-----+---------+-------+1 row in set (0.01 sec)
4.2 替代视图
建立或替代原有视图。
mysql> CREATE OR REPLACE VIEW PEOPLE_VIEW(PEOPLE_ID,PEOPLE_NAME,PEOPLE_AGE) AS SELECT ID,NAME,AGE FROM PEOPLE; Query OK, 0 rows affected (0.00 sec)
建立或替代后检察视图。
mysql> SELECT * FROM PEOPLE_VIEW; +-----------+-------------+------------+| PEOPLE_ID | PEOPLE_NAME | PEOPLE_AGE | +-----------+-------------+------------+| 1 | Anny | 22 | | 2 | Calvin | 23 | | 4 | Nick | 24 || 5 | Rick | 24 | +-----------+-------------+------------+4 rows in set (0.00 sec)
4.3 操纵视图
当视图数据有变化时(增、删、改),实在的表数据也会跟着转变。也就是说,对视图的操纵就是对表的数据,所以我们能够把视图看成表。
例:往视图插进去一条数据。
mysql> INSERT INTO PEOPLE_VIEW VALUES(NULL, 'Kerry', '33'); Query OK, 1 row affected (0.00 sec)
插进去数据胜利后检察视图。
mysql> SELECT * FROM PEOPLE_VIEW ; +-----------+-------------+------------+| PEOPLE_ID | PEOPLE_NAME | PEOPLE_AGE | +-----------+-------------+------------+| 1 | Anny | 22 | | 2 | Calvin | 23 | | 4 | Nick | 24 | | 5 | Rick | 24 || 6 | Kerry | 33 | +-----------+-------------+------------+5 rows in set (0.00 sec)
能够在视图上看到我们方才插进去的数据,如今我们就来考证一下实在的表是不是也会作出变化。
mysql> SELECT * FROM PEOPLE; +----+--------+-----+---------------------+| ID | NAME | AGE | BIRTHDAY | +----+--------+-----+---------------------+| 1 | Anny | 22 | 1992-05-22 00:00:00 | | 2 | Calvin | 23 | 1991-05-22 00:00:00 | | 4 | Nick | 24 | 1990-05-22 00:00:00 | | 5 | Rick | 24 | 1991-05-22 00:00:00 || 6 | Kerry | 33 | NULL | +----+--------+-----+---------------------+5 rows in set (0.00 sec)
可见,实在的表数据也已有所转变,方才往视图里插进去的那一条数据存在于实在表中,真谛就是:对视图的操纵就是对表的数据。
4.4 删除视图
mysql> DROP VIEW PEOPLE_VIEW;Query OK, 0 rows affected (0.00 sec)
相干引荐:
mac运用终端运转mysql,mysql终端,mysql mac,mysql目次,mysql途径,macmysql
mac运用终端运转mysql,mysql终端,mysql mac,mysql目次,mysql途径
以上就是Mac上完成终端治理MySQL数据库的细致内容,更多请关注ki4网别的相干文章!