初识MySQL

2018-06-17 23:56:23来源:未知 阅读 ()

新老客户大回馈,云服务器低至5折

系统信息:macOS Sierra Version 10.12.1     Hombrew 1.1.7

1、MySQL安装

#Terminal中输入命令,等待下载安装完成,时间较长
$ brew install mysql
#若已安装则出现以下警告,安装操作随即停止
Warning: mysql 5.7.18_1 is already installed

2、修改mysql 的root密码

登录进去,输入命令:SET  PASSWORD  =  PASSWORD(“新密码”);

3、MySQL卸载

    Terminal 中输入命令  brew uninstall mysql 或 brew remove mysql 

    记得删除 /usr/local/etc/ 中my.cnf 文件(防止重新安装时出现问题)。

    官网下载安装的卸载方法:

$ sudo rm /usr/local/mysql
$ sudo rm -rf /usr/local/var/mysql
$ sudo rm -rf /usr/local/mysql*
$ sudo rm -rf /Library/Startupltens/MySQLCOM
$ sudo rm -rf /Library/PreferencePanes/My*
$ vim /etc/hostconfig and removed the line MYSQLCOM=-YES-rm -rf ~/Library/PreferencePanes/My*
$ sudo rm -rf /Library/Receipts/mysql*
$ sudo rm -rf /Library/Receipts/MySQL*
$ sudo rm -rf /var/db/receipts/com.mysql.*

4、启动MySQL创建数据库及列表简单示例

# 启动MySQL
$ mysql.server start Starting MySQL . SUCCESS
!
# 建立本地连接并登录 $ mysql
-u root -p Enter password: #未改密码,默认为空,以下为登录后显示的信息 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.7.18 Homebrew Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. # 查看MySQL基本信息
mysql
> \s -------------- mysql Ver 14.14 Distrib 5.7.18, for osx10.12 (x86_64) using EditLine wrapper Connection id: 4 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.7.18 Homebrew Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8 Db charactersiet: utf8 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /tmp/mysql.sock Uptime: 32 sec Threads: 1 Questions: 6 Slow queries: 0 Opens: 105 Flush tables: 1 Open tables: 98 Queries per second avg: 0.187 --------------
# 命令大小写均可用,但不知格式有无要求
# 显示数据库 mysql
> SHOW DATABASES ; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec)

# 创建数据库mytest mysql> CREATE DATABASE mytest; Query OK, 1 row affected (0.01 sec)
# 检查数据库是否创建成功 mysql
> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | mytest | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec)
# 未选择数据库情况,创建列表情况 mysql> CREATE TABLE employees ( -> empid int not null, -> lastname varchar(30), -> firstname varchar(30), -> salary float, -> primary key (empid)); ERROR 1046 (3D000): No database selected # 选择使用的数据库 mysql> USE mytest; Database changed mysql> SHOW tables; Empty set (0.00 sec) #在mytest数据库中创建employees表 mysql> create table employees ( -> empid int not null, -> lastname varchar(30), -> firstname varchar(30), -> salary float, -> primary key (empid)); Query OK, 0 rows affected (0.03 sec) # empid数据字段还指定了一个数据约束 (data constraint)。数据约束会限制输入什么类型数据可以创建一个有效的记录。
# not null数据指明每条记录都必须有一个指定的empid值。 # 最后,primary key定义了可以唯一标识每条记录的数据字段。这意味着每条记录中在表中都必须有一个唯一的empid值。 #查看是否创建成功 mysql
> show tables; +------------------+ | Tables_in_mytest | +------------------+ | employees | +------------------+ 1 row in set (0.00 sec) #插入记录,格式:INSERT INTO table VALUES (....) mysql> INSERT INTO employees VALUES (1, 'Blum', 'Rich', 2500.00); Query OK, 1 row affected (0.01 sec) #插入相同记录会出现错误提示 mysql> INSERT INTO employees VALUES (1, 'Blum', 'Rich', 2500.00); ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY' #删除指定记录,格式:DELETE FROM employees WHERE enpid = 2; mysql> DELETE FROM employees WHERE empid = 1; Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO employees VALUES (2, 'Blum', 'Barbara', 45000.00); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO employees VALUES (1, 'Blum', 'Rich', 25000.00); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO employees VALUES (3, 'Blum', 'Katie Jane', 34500.00); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO employees VALUES (4, 'Blum', 'Jessica', 52340.00); Query OK, 1 row affected (0.00 sec) #查询数据,格式:SELECT datafields FROM table;datafields参数是一个用逗号分开的数据字段名称列表, #指明了希望查询返回的字段。如果你要提取所有的数据字段值,可以用星号作通配符。 #查询所有记录 mysql> SELECT * FROM employees; +-------+----------+------------+--------+ | empid | lastname | firstname | salary | +-------+----------+------------+--------+ | 1 | Blum | Rich | 25000 | | 2 | Blum | Barbara | 45000 | | 3 | Blum | Katie Jane | 34500 | | 4 | Blum | Jessica | 52340 | +-------+----------+------------+--------+ 4 rows in set (0.00 sec) #常用的修修饰符。 # WHERE:显示符合特定条件的数据行子集。 # ORDER BY:以指定顺序显示数据行。 # LIMIT:只显示数据行的一个子集。 mysql> SELECT * FROM employees WHERE salary > 40000; +-------+----------+-----------+--------+ | empid | lastname | firstname | salary | +-------+----------+-----------+--------+ | 2 | Blum | Barbara | 45000 | | 4 | Blum | Jessica | 52340 | +-------+----------+-----------+--------+ 2 rows in set (0.00 sec) #退出 mysql> exit Bye #关闭数据库连接 $ mysql.server stop Shutting down MySQL .. SUCCESS!

 

    

 

标签:

版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有

上一篇:mysql优化

下一篇:ci框架中model简单的mysql操作