MySQL 连接远程数据库
mysql -h 192.168.12.12 db_name -uusername -p
MySQL 创建数据库
CREATE DATABASE 数据库名;
create DATABASE RUNOOB;
删除数据库
drop database <数据库名>;
drop database RUNOOB;
选择数据库
use RUNOOB;
创建数据表
CREATE TABLE table_name (column_name column_type);
CREATE TABLE IF NOT EXISTS `runoob_tbl`(
`runoob_id` INT UNSIGNED AUTO_INCREMENT,
`runoob_title` VARCHAR(100) NOT NULL,
`runoob_author` VARCHAR(40) NOT NULL,
`submission_date` DATE,
PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
删除数据表
DROP TABLE table_name;
DROP TABLE runoob_tbl;
插入数据
INSERT INTO table_name ( field1, field2,...fieldN )
VALUES
( value1, value2,...valueN );
INSERT INTO runoob_tbl (runoob_title, runoob_author, submission_date) VALUES ("学习 PHP", "菜鸟教程", NOW());
查询数据
SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]
select * from runoob_tbl;
WHERE 子句
SELECT field1, field2, ...fieldN FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....
SELECT * from runoob_tbl WHERE runoob_author='菜鸟教程';
MySQL 的 WHERE 子句的字符串比较是不区分大小写的。 你可以使用 BINARY 关键字来设定 WHERE 子句的字符串比较是区分大小写的：
SELECT * from runoob_tbl WHERE BINARY runoob_author='runoob.com';
SELECT * from runoob_tbl WHERE BINARY runoob_author='RUNOOB.COM';
UPDATE 更新
UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]
UPDATE runoob_tbl SET runoob_title='学习 C++' WHERE runoob_id=3;
DELETE 语句
DELETE FROM table_name [WHERE Clause]
DELETE FROM runoob_tbl WHERE runoob_id=3;
LIKE 子句
SELECT field1, field2,...fieldN
FROM table_name
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'
SELECT * from runoob_tbl WHERE runoob_author LIKE '%COM';
UNION 操作符
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];
参数
- expression1, expression2, ... expression_n: 要检索的列。
- tables: 要检索的数据表。
- WHERE conditions: 可选， 检索条件。
- DISTINCT: 可选，删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据，所以 DISTINCT 修饰符对结果没啥影响。
- ALL: 可选，返回所有结果集，包含重复数据。
SELECT country FROM Websites
UNION
SELECT country FROM apps
ORDER BY country;
SELECT country FROM Websites
UNION ALL
SELECT country FROM apps
ORDER BY country;
SELECT country, name FROM Websites
WHERE country='CN'
UNION ALL
SELECT country, app_name FROM apps
WHERE country='CN'
ORDER BY country;
排序
SELECT field1, field2,...fieldN FROM table_name1, table_name2...
ORDER BY field1 [ASC [DESC][默认 ASC]], [field2...] [ASC [DESC][默认 ASC]]
SELECT * from runoob_tbl ORDER BY submission_date ASC;
SELECT * from runoob_tbl ORDER BY submission_date DESC;
