mysql java版_MySQL简单介绍（java版）

MySQL不区分大小写，但开发人员习惯上SQL关键字大写，列名表名小写，这样易于查看和维护

MySQL语句被处理时空格会被忽略，语句可以全部写在一行，也可以写成多行，建议写成多行，便于查看维护

关键字SHOW

//显示所有数据库

SHOW DATABASES;

//选择数据库

USE db_name;

//显示某个数据库中所有表

SHOW TABLES;

//显示所有的列

SHOW COLUMNS FROM tbl_name;

//显示状态

SHOW STATUS;

关键字 SELECT

//查询

SELECT 列名1，列名2... FROM 表名;

//返回不同的值

SELECT DISTINCT 列名1，列名2... FROM 表名;

//查询并返回结果数量小于5

SELECT col1 FROM tbl_products LIMIT 5;

//用的是完全限定名(db_products是数据库)，效果同上

SELECT tbl_products.col1

FROM db_products.tbl_products

LIMIT 5;

关键字 ORDER BY(order by位于sql语句最后，limit关键字之前)

//按列 col1，col2 的字母顺序升序排序(先col1后col2)

SELECT col1

FROM tbl_products

ORDER BY col1，col2;

//产品价格降序排列，产品名升序排列

SELECT prod_id, prod_price, prod_name

FROM products

ORDER BY prod_price DESC, prod_name;

//查询最大值等同于 SELECT MAX(分数) FROM db_scores;

SELECT 分数 FROM db_scores

ORDER BY 分数 DESC

LIMIT 1 ;

关键字 WHERE(ORDER BY位于WHERE之后)

WHERE子句操作符

<>与！=效果相同

比较字符串加' ',

SELECT price FROM tbl_products WHERE price<=3;

SELECT name FROM tbl_products WHERE name ='土豆';

关键字：WHERE ... BETWEEN...AND(确定检索范围)

语法：WHERE 列名 BETWEEN 值1 AND 值2

SELECT prod_name, prod_price

FROM products

WHERE prod_price BETWEEN 5 AND 10;

关键字：WHERE ... IS NULL

SELECT prod_name

FROM products

WHERE prod_price IS NULL;

关键字:WHERE AND(AND优先级高于OR)

给WHERE字句附加条件，表示同时匹配

SELECT prod_id,prod_price,prod_name

FROM products

WHERE vend_id = 1003 AND prod_price <= 10;

关键字 WHERE OR(AND优先级高于OR)

给WHERE字句附加条件，表示匹配任意一条即可

SELECT prod_name , prod_price

FROM products

WHERE vend_id=1002 OR vend_id=1003;

//返回vend_id 为1002或1003，他们的价格都大于等于10

SELECT prod_name,prod_price

FROM products

WHERE (vend_id =1002 OR vend_id=1003) AND prod_price>=10

//返回vend_id 为1002的信息，和价格大于等于10且vend_id=1003 的信息

SELECT prod_name,prod_price

FROM products

WHERE vend_id =1002 OR vend_id=1003 AND prod_price>=10

关键字WHERE IN

IN 指定条件范围

SELECT prod_name, prod_price

FROM products

WHERE vend_id IN (1002,1003)

ORDER BY prod_name;

//意义同上语句

SELECT prod_name, prod_price

FROM products

WHERE vend_id =1002 OR vend_id=1003

ORDER BY prod_name;

关键字 WHERE NOT

WHERE...NOT IN(1，5，7，8)不在1，5，7，8四个数字中

WHERE...NOT BETWEEN...AND不在...之间

WHERE...NOT EXISTS

否定NOT之后的任何条件

SELECT prod_name,prod_price

FROM products

WHERE vend_id NOT IN(1002,1003,1004)

ORDER BY prod_name;

关键字 LIKE

WHERE 列名 LIKE '%a%'

%表示0,1,多个任意字符

%不能匹配NULL

//返回以jet开头的name和对应id

SELECT prod_id, prod_name

FROM products

WHERE prod_name LIKE 'jet%';

关键字 % _

%匹配0,1或多个任意字符，但%不能匹配NULL

_ 匹配任意单个字符，且字符不为空

WHERE 列名 LIKE '%a_'

关键字 WHERE ... REGEXP ....

关键字 AS

//给列起别名vend_title

列 AS vend_title

FROM vendors

ORDER BY vend_name;