一.概述
MySQL最由MySQL AB 公司开发的一套关系型数据库管理系统。08年SUN收购了MySQL AB,09年Oracle收购了SUN,MySQL并入了Oracle的数据库产品线。
MySQL产品:
MySQL企业服务器
MySQL社区服务器
MySQL嵌入式数据库
MySQL集群
工作方式还是常见的客户端服务器模型。
二. 基本命令
1.查看版本信息
SELECT VERSION();
2.查看当前使用的数据库
select database();
3.结束符;和g，作用相同可以互换
select version(),database()g
4.改变结束符，DELIMITER
DELEMETER GO
SELECT VERSION(),DATABASE()GO
5.G结束，以垂直方式显示结果
mysql> SELECT VERSION(),DATABASE()G
*************************** 1. row ***************************
VERSION(): 5.1.47-community
DATABASE(): NULL
1 row in set (0.00 sec)
6.c取消当前输入
mysql> select c
mysql>
7.退出q、QUIT、EXIT
Q
QUIT
EXIT
8.MySQL编辑键
/*
四个方向键:
左右移动光标向前/后一步
上下键查看MySQL客户端中的历史命令
UNIX下:
Ctrl+A 回到第一行开始
Ctrl+E 移动到该行末尾
Ctrl+K 从光标所在位置到行末尾全部删除
Ctrl+R 在历史命令中反向搜索
*/
9.使用MySQL脚本文件
SOURCE D:/world.sql
10.STATUS命令，显示有关当前连接到的服务器的信息
/*
mysql> STATUS
--------------
mysql Ver 14.14 Distrib 5.1.47, for Win32 (ia32)
Connection id: 3
Current database:
Current user: root@localhost
SSL: Not in use
Using delimiter: ;
Server version: 5.1.47-community MySQL Community Server (GPL)
Protocol version: 10
Connection: localhost via TCP/IP
Server characterset: gbk
Db characterset: gbk
Client characterset: gbk
Conn. characterset: gbk
TCP port: 3306
Uptime: 1 hour 50 min 59 sec
Threads: 1 Questions: 53 Slow queries: 0 Opens: 18 Flush tables: 1 Open tables: 0 Queries per second avg: 0.7
--------------
*/
三.查询命令
1.查看现有数据库
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | dang | | db_shopping | | db_wuliu | | mysql | | test | | world | +--------------------+ 7 rows in set (0.04 sec)
2.指定当前数据库
mysql> USE world Database changed
3.查看数据库中的表
mysql> SHOW TABLES; +-----------------+ | Tables_in_world | +-----------------+ | city | | country | | countrylanguage | +-----------------+ 3 rows in set (0.52 sec)
4.查看非当前使用数据库中的表
mysql> SHOW TABLES FROM dang; +--------------------+ | Tables_in_dang | +--------------------+ | d_book | | d_category | | d_category_product | | d_item | | d_order | | d_product | | d_receive_address | | d_user | +--------------------+ 8 rows in set (0.08 sec)
5.查看表结构 DESC 表名 ，以表格形式给出表各列的信息
mysql> DESC City; +-------------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+----------+------+-----+---------+----------------+ | ID | int(11) | NO | PRI | NULL | auto_increment | | Name | char(35) | NO | | | | | CountryCode | char(3) | NO | | | | | District | char(20) | NO | | | | | Population | int(11) | NO | | 0 | | +-------------+----------+------+-----+---------+----------------+ 5 rows in set (0.05 sec)
6.查看现有表的创建语句 SHOW CREATE TABLE table_name;
mysql> SHOW CREATE TABLE CityG; *************************** 1. row *************************** Table: City Create Table: CREATE TABLE `city` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `Name` char(35) NOT NULL DEFAULT '', `CountryCode` char(3) NOT NULL DEFAULT '', `District` char(20) NOT NULL DEFAULT '', `Population` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`ID`) ) ENGINE=MyISAM AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1 1 row in set (0.00 sec)
7.查看表中的数据
mysql> SELECT * FROM City LIMIT 6; +----+----------------+-------------+---------------+------------+ | ID | Name | CountryCode | District | Population | +----+----------------+-------------+---------------+------------+ | 1 | Kabul | AFG | Kabol | 1780000 | | 2 | Qandahar | AFG | Qandahar | 237500 | | 3 | Herat | AFG | Herat | 186800 | | 4 | Mazar-e-Sharif | AFG | Balkh | 127800 | | 5 | Amsterdam | NLD | Noord-Holland | 731200 | | 6 | Rotterdam | NLD | Zuid-Holland | 593321 | +----+----------------+-------------+---------------+------------+ 6 rows in set (0.00 sec) /* 若查看全部数据，不用LIMIT6*/
8.SELECT查询
SELECT NAME FROM CITY;
9.消除重复行查询
SELECT DISTINCT CONTINENT FROM Country
10.使用WHERE
SELECT ID,Name,District FROM city WHERE Name='New York';
11. 使用运算符多条件查询
mysql> SELECT Name,Population -> FROM Country -> WHERE Population>50000000 AND -> (Continent ='Europe' OR Code='USA'); +--------------------+------------+ | Name | Population | +--------------------+------------+ | United Kingdom | 59623400 | | Italy | 57680000 | | France | 59225700 | | Germany | 82164700 | | Ukraine | 50456000 | | Russian Federation | 146934000 | | United States | 278357000 | +--------------------+------------+ 7 rows in set (0.02 sec)
12.WHERE中IN的使用
mysql> SELECT Name,Population -> FROM Country -> WHERE Name in('China','JAPAN','Australia'); +-----------+------------+ | Name | Population | +-----------+------------+ | Australia | 18886000 | | Japan | 126714000 | | China | 1277558000 | +-----------+------------+ 3 rows in set (0.00 sec)
13.LIKE模式匹配
字符'%'可匹配0个或多个字符序列
a% 匹配任何以字符'a'开头的字符串
%b匹配任何以字符'b'结束的字符串
%c%匹配任何包含字符'c'的字符串
字符'_'匹配任何单字符
'd_g'匹配'dig','dog' 等。
'd_g'匹配d_g本身
mysql> SELECT NAME FROM Country -> WHERE Name LIKE 'United%'; +--------------------------------------+ | NAME | +--------------------------------------+ | United Arab Emirates | | United Kingdom | | United States | | United States Minor Outlying Islands | +--------------------------------------+ 4 rows in set (0.00 sec)
14.使用正则表达式进行模式匹配 RLIKE
查询城市中包含'nat'的字符串
mysql> select Name FROM City -> where Name RLIKE 'nat' -> ; +-----------------+ | Name | +-----------------+ | Natal | | Cabanatuan | | Maunath Bhanjan | | Minatitlán | | Cincinnati | +-----------------+ 5 rows in set (0.02 sec)
查询以'New'开始，以‘rk’结尾的城市名称
mysql> SELECT Name FROM City -> WHERE Name RLIKE '^New.*rk$'; +----------+ | Name | +----------+ | New York | | Newark | +----------+ 2 rows in set (0.01 sec)
查找所有包含'Los'或'Las'的城市名称
mysql> SELECT Name from City -> where Name RLIKE 'LOS|las'; +--------------------------------+ | Name | +--------------------------------+ | Las Heras | | San Nicolás de los Arroyos | | São Carlos | ………… | Las Vegas | | East Los Angeles | | North Las Vegas | +--------------------------------+ 36 rows in set (0.01 sec)
查找所有包含'Los'或'Las'的城市名称--也可以写成
mysql> SELECT Name from City -> WHERE Name RLIKE 'L[ao]S'; +--------------------------------+ | Name | +--------------------------------+ | Las Heras | | San Nicolás de los Arroyos | | São Carlos | | Ferraz de Vasconcelos | | Glasgow |