常用的内置函数,MySQL常用操作手册

  4.SQL语言:

  SQL(Structured Query Language)语言的全称是结构化查询语言。它包括:

    - 数据库定义语言(Data Definition Language, DDL)

    - 数据操作语言(Data Manipulation Language, DML)

    - 数据控制语言(Data Control Language, DCL)

 

对查询结果排序

mysql> SELECT 列名... FROM 表名 ORDER BY 首选排序列名 DESC/ASC, 第二排序列名 DESC/ASC, ... DESC/ASC;

ORDER BY 默认升序排列,使用ASC指定升序排序,使用DESC指定降序排序。

DESC/ASC只能直接作用于直接位于其前面的列名,如果想在多个列上进行降序排序,必须对每个列指定DESC关键字。

  Linux安装MySQL

  

1、“%”通配符:表示任意字符的匹配,且不计字符的多少。
(1)开头、结尾匹配
开头匹配(以字母“c”开头的所有数据):SELECT * FROM table_name WHERE
COLUMN LIKE ‘c%’
结尾匹配(以字母“c”结尾的所有数据):SELECT * FROM table_name WHERE
COLUMN LIKE ‘%c’

新建数据表

mysql> CREATE TABLE 数据表名
    -> (
    -> 列名1 数据类型(数据长度) PRIMARY KEY,        --主键
    -> 列名2 数据类型(数据长度) NOT NULL,        --非空约束
    -> 列名3 数据类型(数据长度) DEFAULT '默认值',        --默认值约束
    -> UNIQUE(列名a),        --唯一约束
    -> CONSTRAINT 主键名 PRIMARY KEY (列名a,列名b,...),        --复合主键
    -> CONSTRAINT 外键名 FOREIGN KEY (列名) REFERENCES 表名(主键名)        --外键
    -> );

注意最后一个列名后面不加逗号”,”。

  1、查询数据

IN运算符
若只需要满足多个条件中的一个查询条件,则可以使用IN运算符。IN运算符允许根据一行记录中是否有一列包括在一系列值之中而选择改行。
所有的条件罗列在IN运算符之后,并以园括号括起来,各个条件之间使用逗号分开。
示例:
查询ID为1、3或4的数据:SELECT * FROM table_name WHERE ID IN (1,3,4)
注意:
在大多数情况下,OR运算符与IN运算符可以实现相同的功能。
示例:SELECT * FROM talbe_name WHERE ID=1 OR ID=3 OR ID=4

子查询

MySQL 4.1引入了对子查询的支持。

查询

任何SQL语句都是查询,但此术语一般指 SELECT 语句。

子查询

即嵌套在其他查询中的查询。

  • 在 SELECT 语句中,子查询总是从内向外处理
  • 嵌套的子查询的数目没有限制
  • 实际使用时由于性能的限制,不能嵌套太多的子查询

在 WHERE 子句中使用子查询,应该保证 SELECT 语句具有与 WHERE
相同数据的列。通常,子查询将返回单个列并且与单个列匹配,但如果需要,也可以使用多个列。

子查询一般与 IN
操作符结合使用,但也可以用于测试等于(=)、不等于(<>)等。

相关子查询

涉及外部查询的子查询。

当列名可能有多义性的时候要使用完全限定列名来避免歧义。

  3.过滤关键字 where + 操作符

  where子句操作符表:

 操作符  说明
 =  等于
 <>  不等于
 !=  不等于
 <  小于
 <=  小于等于
 !<  不小于
 >  大于
 >=  大于等于
 !>  不大于
BETWEEN 在指定的两个值之间
IS NULL 为null值
AND、OR、NOT、IN 组合查询
LIKE 通配符过滤

  用例:

-- 匹配查询
SELECT prod_price, prod_name FROM products where prod_price = 3.49;
-- 不匹配查询
SELECT prod_price, prod_name FROM products where prod_price <> 10;
-- 范围查询
SELECT prod_price, prod_name FROM products where prod_price BETWEEN 5 AND 10; 
-- 空值查询
SELECT prod_name FROM products where prod_price IS NULL;
-- 组合查询AND
SELECT prod_id, prod_name, prod_price FROM products WHERE vend_id='DLL01' AND prod_price <= 4;
-- 组合查询OR
SELECT prod_name, prod_price FROM products WHERE vend_id='DLL01' OR vend_id = 'BRS01';
-- 组合查询AND和OR
SELECT prod_name, prod_price FROM products WHERE vend_id='DLL01' OR vend_id = 'BRS01' AND prod_price <= 4;
-- 组合查询IN
SELECT prod_name, prod_price FROM products WHERE vend_id IN ('DLL01', 'BRS01') ORDER BY prod_name;
-- 组合查询NOT
SELECT prod_name, prod_price FROM products WHERE vend_id NOT vend_id='DLL01';
-- 通配符过滤
SELECT prod_name FROM products WHERE prod_name LIKE '%TNT%';

 

显示表属性

mysql> SHOW COLUMNS FROM 表名;

该命令将会输出指定表的每个字段的字段名、数据类型、非空约束、是否是主键和默认值等信息。

  3、分组关键字 GROUP BY 和HAVING

条件查询注意:
1、在一个WHERE子句中,可以同时使用多个AND运算符链接多个查询条件。
2、在“(表达式1)OR(表达式2)”中,如果表达式1为TRUE,将不再执行表达式2。

子查询

mysql> SELECT 所查列名,函数()
    -> FROM 表名 
    -> WHERE 所查列名 IN 
    -> (SELECT 相关列名 FROM 相关表名 WHERE 限制条件);

处理多个表且处理结果来自一个表时使用子查询,子查询可以扩展多层。

  1.查询关键字 SELECT FROM

-- 查询单列
SELECT prod_name FROM products;
-- 查询多列
SELECT prod_id, prod_name, prod_price FROM products;
-- 查询所有列
SELECT * FROM products;

注意:分离数据库是将数据库文件从SQL
Server中分离出来,而删除数据库是将数据库文件删除。

删除列

mysql-> ALTER TABLE 表名
     -> DROP COLUMN 列名;

mysql-> ALTER TABLE 表名
     -> DROP 列名;

  2.日期和时间处理函数

  注:日期和时间函数根据

 函数  说明
 NOW()、SYSDATE()、CURRENT_TIMESTAMP、LOCALTIME、LOCALTIMESTAMP 获取当前日期和时间
 CURDATE(), CURRENT_DATE 获取当前日期
 CURTIME(), CURRENT_TIME 获取当前时间
 DATE、YEAR、QUARTER、MONTH、WEEK、DAY、HOUR、MINUTE、SECOND、MICROSECOND 获取指定日期和时间的日期、年、季度、月、周、日、小时、分钟、秒、毫秒数
 WEEKOFYEAR、DAYOFYEAR、DAYOFMONTH、DAYOFWEEK、LAST_DAY 获取指定日期和时间的年周索引、年天索引、月天索引、周天索引,最后一天的日期
MONTHNAME、 DAYNAME 获取指定日期和时间的英文月名、英文天名
DATE_ADD、DATE_SUB 指定日期按指定参数进行加减运算
PERIOD_ADD、PERIOD_DIFF 指定日期加、减多少个月
TIMEDIFF 指定日期和时间相差多少个时间
TIMESTAMPDIFF 指定日期/时间或日期时间的差值
TO_DAYS、FROM_DAYS 日期和月数的相互转换函数
TIME_TO_SEC、SEC_TO_TIME 时间和秒数的相互转换函数
STR_TO_DATE、DATE_FORMAT 字符串/日期时间格式转换成新的格式
TIME_FORMAT 时间格式转换你成新的格式
MAKEDATE、MAKETIME 拼凑日期/时间
UNIX_TIMESTAMP、FROM_UNIXTIME 日期时间和unix时间戳的相互转化

   用例:

-- 获取当前日期和时间,日期指的是年月日,时间指的是时分秒
SELECT NOW(), SYSDATE(), CURRENT_TIMESTAMP, LOCALTIME, LOCALTIMESTAMP;
-- 分别获取当前日期和时间
SELECT CURDATE(), CURRENT_DATE, CURTIME(), CURRENT_TIME;
-- 分别获取日期时间、年、季度、月、周、日、时、分、秒
SELECT DATE(SYSDATE()), YEAR(SYSDATE()), QUARTER(SYSDATE()), MONTH(SYSDATE()), WEEK(SYSDATE()), DAY(SYSDATE()), HOUR(SYSDATE()), MINUTE(SYSDATE()), SECOND(SYSDATE()), MICROSECOND(SYSDATE());
-- 获取指定索引
SELECT WEEKOFYEAR(SYSDATE()), DAYOFYEAR(SYSDATE()), DAYOFMONTH(SYSDATE()), DAYOFWEEK(SYSDATE()), LAST_DAY(SYSDATE());
-- 获取月和周的英文名称
SELECT MONTHNAME(SYSDATE()), DAYNAME(SYSDATE());

-- DATE加,第一个参数是指定的日期和时间,第二个参数是间隔和单位
SELECT DATE_ADD(now(), INTERVAL 1 YEAR), DATE_ADD(now(), INTERVAL 2 MONTH), DATE_ADD(now(), INTERVAL 1000 SECOND);
-- DATE减,与DATE加参数相同
SELECT DATE_SUB(now(), INTERVAL 1 YEAR), DATE_SUB(now(), INTERVAL 2 MONTH), DATE_SUB(now(), INTERVAL 1000 SECOND);
-- 日期的加减运算
SELECT PERIOD_ADD(201808, 2), PERIOD_ADD(1808, 2),PERIOD_ADD(DATE_FORMAT(SYSDATE(), '%Y%m'), 2), PERIOD_DIFF(201808, 201004), PERIOD_DIFF(1808, 1004); 
-- 时间差计算
SELECT TIMEDIFF('2018-08-06', '2018-08-5');-- 不支持日期
SELECT TIMEDIFF('19:00:00', '17:00:00'), TIMEDIFF('2018-08-6 9:30:30', '2018-08-5 17:00:00');
-- 更便捷的日期/时间差值计算,第一个参数是要计算的字段,其值为第三个日期时间减去第二个日期时间
SELECT TIMESTAMPDIFF(DAY, '2018-08-5 17:00:00', '2018-08-8 9:30:30'), TIMESTAMPDIFF(DAY, '2018-08-5', '2018-08-8');
SELECT TIMESTAMPDIFF(SECOND, '17:00:00', '19:30:30');-- 不支持单独时间计算
-- 日期和天数的相互转换
SELECT TO_DAYS(SYSDATE()), TO_DAYS('2018-8-8'), FROM_DAYS(737279);
-- 时间和秒数的相互转换
SELECT TIME_TO_SEC(SYSDATE()), TIME_TO_SEC('12:00:00'), SEC_TO_TIME(43200);
-- 字符串格式化;字符串格式化成日期只能要按照字符串的写法改写成标准日期时间字符串
SELECT STR_TO_DATE('2018.08.6 9:30:30', '%Y.%m.%d %H:%i:%s');
-- 日期时间字符串可以随便更改或获取字段
SELECT DATE_FORMAT('2018-08-06 09:30:30', '%Y%m');-- 获取年月的组合字符串
SELECT DATE_FORMAT('2018-08-06 09:30:30', '%H%i%s');-- 获取时分秒的组合字符串
SELECT DATE_FORMAT(SYSDATE(), '%Y年%m月%d日 %H时哈哈%i分嘿嘿%d秒呵呵');-- 重新格式化
-- 时间格式化只能格式化时间
SELECT TIME_FORMAT('2018-08-06 09:30:30', '%Y年%m月%d日 %H时%i分%d秒');
-- 只对'09:30:30'进行格式化,日期全部为00
SELECT TIME_FORMAT('09:30:30', '%H时%i分%d秒');
-- MAKEDATE根据数字组合成日期(以天数换算),MAKETIME根据数字组合成时间
SELECT MAKEDATE(2018, 9);-- 结果是'2018-01-09'而不是'2018-09-01'
SELECT MAKEDATE(2018, 220);-- 结果是'2018-08-08'
SELECT MAKETIME(19,30,30);-- 与日期相反,支持三个参数拼接而不支持两个参数换算
-- 日期时间和unix时间的相互转换
SELECT UNIX_TIMESTAMP(), FROM_UNIXTIME(UNIX_TIMESTAMP());

(3)两端匹配
查询出包含字母“c”的所有数据:SELECT * FROM table_name WHERE COLUMN
LIKE ‘%c%’

显示某表的索引

mysql-> SHOW INDEX FROM 表名;

  3.数值处理函数

函数  说明
ABS() 返回数字表达式的绝对值。
ACOS() 返回数字表达式的反余弦值。如果值是不在范围-1到1,则返回NULL。
ASIN() 返回数字表达式的反正弦。返回NULL,如果值不在范围-1到1
ATAN() 返回数字表达式的反正切。
ATAN2() 返回传递给它的两个变量的反正切。
BIT_AND() 返回按位AND运算表达中的所有位。
BIT_COUNT() 返回传递给它的二进制值的字符串表示。
BIT_OR() 返回传递表达的所有位的位或。
CEIL() 返回最小的整数值但不能比传递的数字表达式小
CEILING() 返回最小的整数值但不能比传递的数字表达式小
CONV() 表达式从一个基数到另一个基数转换的数字。
COS() 返回传递数字表达式的余弦值。数字表达式应该用弧度表示。
COT() 返回传递数字表达式的余切。
DEGREES() 返回数字表达式从弧度转换为度。
EXP() 返回数值表达式的自然对数(E)为基数的幂。
FLOOR() 返回最大整数值但不能大于通过表达式数值。
FORMAT() 返回数字表达式舍入到小数位数。
GREATEST() 返回输入表达式的最大值。
INTERVAL() 需要多个表达式exp1, exp2和exp3等..如果为exp1小于exp2返回0,如果为exp1小于exp3返回1等。
LEAST() 给两个或两个以上时,返回所有输入的最小值。
LOG() 返回通过数字表达式的自然对数。
LOG10() 返回传递表达的基数为10对数的数值。
MOD() 返回表达式由另一个表达式除以剩余部分。
OCT() 返回通过数字表达式的八进制值的字符串表示。如果传递值为NULL,返回NULL。
PI() 返回圆周率的值
POW() 返回一个表达式到另一个表达的次方值
POWER() 返回一个表达式到另一个表达的次方值
RADIANS() 返回传递表达从度转换为弧度值
ROUND() 返回数字表达式四舍五入到整数。可用于舍入表达式为小数点数值
SIN() 返回给定的数字表达的正弦值(弧度)
SQRT() 返回数字表达式的非负平方根
STD() 返回数字表达式的标准偏差
STDDEV() 返回数字表达式的标准偏差
TAN() 返回以弧度表示数值表达式的正切值。
TRUNCATE() 返回exp1小数位数字截断到exp2。如果exp2为0,则结果将没有小数点。

  

新建数据库

mysql> CREATE DATABASE 数据库名;

通常SQL语句不区分大小写,但建议将关键字大写,变量和数据小写。

  4.聚合函数

函数  说明
 AVG  返回某列的平均值
 COUNT  返回某类的行数
 MAX  返回某列的最大值
 MIN  返回某列的最小值
 SUM  返回某列值之和

  用例:

-- AVG
SELECT AVG(prod_price) AS avg_price FROM products;
SELECT AVG(prod_price) AS avg_price FROM products WHERE vend_id=1003;
SELECT AVG(DISTINCT prod_price) AS avg_price FROM products WHERE vend_id=1003;-- 计算唯一值列表平均值
-- COUNT
SELECT COUNT(*) AS num_cust FROM customers;-- 对所有行技数
SELECT COUNT(cust_email) as num_cust FROM customers;-- 只对具有电子邮寄地址的客户计数(除去null)
-- SUM
SELECT SUM(item_price * quantity) AS total_price FROM orderitems WHERE order_num = 20005;
-- 组合
SELECT COUNT(*) AS nun_items, MIN(prod_price) AS price_min, MAX(prod_price) AS pric_max, AVG(prod_price) AS price_ag FROM products;

3、”[]”通配符:”[]”通配符用于指定一系列的字符,只要满足这些字符其中之一且出现在”[]”通配符的位置的字符串就满足查询条件
(1)、各种通配符可以组合使用,必须自习分析它所表示的匹配条件
       
                                  匹配条件列表
   查询条件                                                     
匹配条件
   LIKE ‘5[%]’                                                    
5%
   LIKE ‘[_]n’                                                    
_n
   LIKE
‘[a-f]’                        a,b,c,d,e,f
   LIKE ‘[[]’                                                      
[
   LIKE ‘[]]’                                                      
]
   LIKE ‘abc[def]’                                            
abcd,abce,abcf
   LIKE ‘abc[_]d%’                                            
adbc_d…(省略号表示可以有任意字符)
   LIKE ‘a[^a]d%’                                             
不能为aad…([^]表示不能取[]内的字符)
   
示例:
查询名称(name)中包含”_”的所有数据:SELECT * FROM table_name WHERE name
LIKE ‘%[_]%’
查询名称(name)中最后一个字符为”a”,”b”,”f”,”g”的所有数据:SELECT * FROM
table_name WHERE name LIKE ‘%[a,b,f,g]’
查询名称(name)中最后一个字符不为”a”,”b”,”f”,”g”的所有数据:SELECT *
FROM table_name WHERE name LIKE ‘%[^a,b,f,g]’

重命名数据表

mysql-> RENAME TABLE 原数据表名 TO 新数据表名;

mysql-> ALTER TABLE 原数据表名 RENAME 新数据表名;

mysql-> ALTER TABLE 原数据表名 RENAME TO 新数据表名;

  2.约束条件

  约束是为了保证数据的完整性和一致性,约束类型包括:

 键名  类型
PRIMARY KEY 主键约束
UNIQUE KEY 唯一约束
NOT NULL 非空约束
UNSIGNED 无符号约束
DEFAULT 默认约束
FOREIGN KEY 外键约束

 

HAVING子句
GROUP
BY子句只是简单地依据所选列的数据进行分组,将该列具有相同值的行划为一组。而实际应用中,
往往还需要删除不满足条件的组,SQL提供HAVING子句来实现该功能。
示例:SELECT EmployeeID,MIN(price) FROM table_name GROUP BY EmployeeID
HAVING MIN(price)

退出数据库

mysql> EXIT/QUIT;

  4.修改表结构

 

MySQL查询语句关键字顺序

mysql> SELECT ...
       FROM ...
       WHERE ...
       ORDER BY ...
       LIMIT ...;

  1.整数类型的存储和范围

类型 大小 范围(有符号) 范围(无符号) 用途
TINYINT 1 字节 (-128,127) (0,255) 小整数值
SMALLINT 2 字节 (-32 768,32 767) (0,65 535) 大整数值
MEDIUMINT 3 字节 (-8 388 608,8 388 607) (0,16 777 215) 大整数值
INT或INTEGER 4 字节 (-2 147 483 648,2 147 483 647) (0,4 294 967 295) 大整数值
BIGINT 8 字节 (-9 233 372 036 854 775 808,9 223 372 036 854 775 807) (0,18 446 744 073 709 551 615) 极大整数值
FLOAT 4 字节 (-3.402 823 466 E+38,-1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38) 0,(1.175 494 351 E-38,3.402 823 466 E+38) 单精度
浮点数值
DOUBLE 8 字节 (-1.797 693 134 862 315 7 E+308,-2.225 073 858 507 201 4 E-308),0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) 0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) 双精度
浮点数值
DECIMAL 对DECIMAL(M,D) ,如果M>D,为M+2否则为D+2 依赖于M和D的值 依赖于M和D的值 小数值

二者的结果完全一致,都是返回多条数据记录。但是,与OR运算符相比,IN运算符具有以下两个优点:
(1)当选择条件很多时,采用IN运算符将十分简捷,只需要在括号中用逗号间隔后一次罗列即可,运行效率高于OR运算符。
(2)IN运算符后面所列的条件可以是另一条SELECT语句,即子查询。

显示服务器警告或错误信息

mysql> SHOW ERRORS;

mysql> SHOW WARNINGS;

一、基本概念

2、SELECT 后多个聚合函数情况
示例:从订单表Orders中查询各个员工最早的订单时间(OrderDate)和购买最便宜的价格(price)。员工ID为(EmployeeID)
SELECT EmployeeID,min(OrderDate),min(price) FROM table_name GROUP BY
EmployeeID
原理:当执行时首先在表中查询员工ID(EmployeeID),然后将相同的ID合并为一个。当所有的ID都唯一时,这时便开始通过聚合函数获得
员工的最早订单时间,接着再通过聚合函数获得最便宜的价格。

插入数据

INSERT INTO 数据表名(列名1,列名2,列名3) VALUES(值1,值2,值3);

VALUES中的值为对应列属性的值,其中CHAR,VARCHAR,TEXT,DATE,TIME,ENUM等类型的数据需要单引号修饰。

  2.排序关键字 ORDER BY

  查询的数据如果不排序,一般是以它在底层表中出现的顺序显示。如果不明确规定排序顺序,则不应该假定检索出的数据的顺序有意义。

-- 单排
SELECT prod_name FROM products ORDER BY prod_name;
-- 多排
SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price, prod_name;
-- 按列位置排
SELECT prod_id, prod_price, prod_name FROM products ORDER BY 2, 3; 注,它只能根据已选择字段的相对位置排序
-- 指定排序方向
SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESC, prod_name;

 

SQL约束

  1.GROUP BY

  - GROUP BY子句可以包含任意数目的列。

  - GROUP BY会在最后规定的分组上进行汇总。

  - GROUP
BY子句列出的每个列都必须是检索列或有效的表达式(但不能是聚合函数)。

  - 除聚合函数外,SELECT语句中的每个列都必须在GROUP BY子句中给出。

  -
如果分组列中具有NULL值,则NULL将作为一个分组返回;如果列中有多行NULL值,它们将分为一组。

  - GROUP BY子句必须出现在WHERE子句字后,ORDER BY子句之前。

  一般在使用GROUP BY子句时,应该也给出ORDER
BY子句,以保证数据正确排序。

表的连接类型
1、内链接:内连接也称等同连接,返回的结果集是两个表中所有相匹配的数据,并舍弃不匹配的数据(类似于上面的表的基本连接)。
语法:SELECT * FROM table_name1 [INNER] JOIN table_name2 ON
<表达式> [WHERE] [<表达式>]
示例:SELECT * FROM Categories INNER JOIN  Prouducts ON
Categories.CategorID=Prouducts.CategorID
注意:INNER关键字可以省略。

日期和时间处理函数

函数 说明
AddDate() 增加一个日期(天、周等)
AddTime() 增加一个时间(时、分等)
CurDate() 返回当前日期
CurTime() 返回当前时间
Date() 返回日期时间的日期部分 v4.1.1
DateDiff() 计算两个日期之差
Date_Add() 高度灵活的日期计算函数?
Date_Format() 返回一个格式化的日期或时间串
Day() 返回一个日期的天数部分
DayOfWeek() 返回一个日期对应的星期
Hour() 返回一个时间的小时部分
Minute() 返回一个时间的分钟部分
Now() 返回当前日期时间
Second() 返回一个时间的秒部分
Time() 返回一个日期的时间部分 v4.1.1
Year() 返回一个日期的年份部分

MySQL日期格式使用 yyyy-mm-dd 格式

检索日期时应该使用Date()函数,直接比较可能检测不到结果

MySQL会将00-69处理为2000-2069,将70-99处理为1970-1999,为避免歧义,使用标准格式

  2.修改列定义和列名

-- 修改列名,要重新声明列的数据类型和约束条件
ALTER TABLE users CHANGE name username varchar(10) NOT NULL;
-- 修改列定义
ALTER TABLE users MODIFY id SMALLINT NOT NULL FIRST;
-- 修改表名
ALTER TABLE users RENAME TO tb;

 

 

MySQL通配符

_   ### 只能匹配单个任意字符
%   ### 匹配0或多个任意字符

必须通过LIKE使用通配符。

通配符不能用于检索NULL。

使用通配符搜索相对于其他搜索方式通常要花费更长的时间。

将通配符放在搜索模式的开头处,搜索起来是最慢的,尽量避免这样做。

三、MySQL表操作

(3)”_”与”%”的综合应用
在模糊查询过程中,经常需要”_”和”%”一起使用才能完成查询功能。
示例:查询名称(name)第二个字母为”c”的所有数据,SQL语句如下:
SELECT * FROM table_name where name LIKE ‘_c%’

导出(将表中数据保存到文件中)

mysql-> SELECT 列名1,列名2,...
     -> INTO OUTFILE '文件路径'
     -> FROM 表名;

  1.创建表

-- 创建表语法:CREATE TABLE [IF NOT EXISTS] table_name(column_name data_type,...);
CREATE TABLE users(id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20), salary FLOAT(8,2) UNSIGNED);
-- 查看表结构
DESC users;
SHOW COLUMNS FROM users;
SHOW CREATE TABLE users;

 
模糊查询
在SQL
Server中,通过使用通配符来实现LIKE运算,通配符“%”,“_”,“[]”。
注意:只有char、varchar和text类型的数据才能使用LIKE运算符和通配符。

查看表中所有的内容

mysql> SELECT * FROM 数据表名;

  1.文本处理函数

 函数 说明
 LEFT 返回字符串左边的字符
 LENGTH 返回字符串的长度
 LOWER  返回字符串的小写
 LTRIM 去掉字符串左边的空格
 RIGHT 返回字符串右边的字符
 RTRIM 去掉字符串右边的空格
UPPER 返回字符串的大写

   - 用例: 

SELECT vend_name, UPPER(vend_name) AS vend_name_upcase FROM vendors ORDER BY vend_name;

修改表中的数据
语法:UPDATE table_name SET COLUMN1={},COLUMN2={},….
WHERE<表达式>

查看当前数据库中含有哪些表

mysql> SHOW TABLES;

mysql> DESCRIBE 表名;

  常见数据库

  商业数据库:甲骨文的Oracle、IBM的DB2、微软的Access和SQL
Server。开源数据库:PostgreSQL、MySQL。

单列排序:SELECT * FROM table_name ORDER BY
多列排序;SELECT * FROM table_name ORDER BY COLUMN1 DESC,COLUMN2
DESC….

注释

-- 单行注释
/* 多行注释 */

  版本分类

  根据操作系统:Windows版,UNIX版,Linux版,MacOS版;根据开发情况:Alpha、Beta、Gamma与Available(GA)。

  alpha
暗示这是一个以展示新特性为目的的版本,存在比较多的不稳定因素,还会向代码中添加新新特性

  beta
以后的beta版、发布版或产品发布中,所有API、外部可视结构和SQL命令列均不再更改,不再向代码中添加影响代码稳定性的新特性。Gamma比Beta版更高级。

  GA如果没有后缀,则暗示这是一个大多数情况下可用版本或者是产品版本。.
GA releases则是稳定版。

  优势:MySQL开放源码、跨平台性、价格优势、功能强大且使用方便。

2、”_“通配符:”_“统配符的功能与”%“基本相同,只是它仅表示任意一个字符(区别)的匹配。若需要表示两个字符的匹配,则使用两个”_“通配符,即写成”_
_“。
(1)匹配一个字符
示例:从表中查询名称(name)为”t_fu“(“_”代表任意一个字符)的所有数据,SQL语句如下:
SELECT * FROM table_name where name LIKE ‘t_fu’    
匹配字符串有(tafu,tbfu,tcfu…..)

SELECT子句顺序

子句 说明 是否必须使用
SELECT 要返回的列或表达式
FROM 从中检索数据的表 仅在从表选择数据时使用
WHERE 行级过滤
GROUP BY 分组说明 尽在按组计算聚集时使用
HAVING 组级过滤
ORDER BY 输出排序顺序
LIMIT 要检索的行数

  4、关键字顺序

关键字(子句) 说明
 是否必须使用
 SELECT  要返回的列或表达式  是
 FROM  从中检索数据的表  仅在从表选择数据时使用
 WHERE  行级过滤  否
 GROUP BY  分组说明  仅在按组计算聚集时使用
 HAVING  组级过滤  否
 ORDER BY  指定排序字段和熟顺序  否

   在实现SQL语句时,通用格式为:

SELECT *columns* FROM *tables* WHERE *condition* GROUP BY *columns* HAVING *condition* ORDER BY *columns* LIMIT *start*, *offset*;

  实际执行的顺序为:

FROM *tables*
WHERE *condition*
GROUP BY *columns*
HAVING *condition*
SELECT *columns*
ORDER BY *columns*
LIMIT *start*, *offset*

# 注:数据表来自

图片 1图片 2

CREATE TABLE customers
(
cust_id int NOT NULL AUTO_INCREMENT,
cust_name char(50) NOT NULL ,
cust_address char(50) NULL ,
cust_city char(50) NULL ,
cust_state char(5) NULL ,
cust_zip char(10) NULL ,
cust_country char(50) NULL ,
cust_contact char(50) NULL ,
cust_email char(255) NULL ,
PRIMARY KEY (cust_id)
) ENGINE=InnoDB;

CREATE TABLE orderitems
(
order_num int NOT NULL ,
order_item int NOT NULL ,
prod_id char(10) NOT NULL ,
quantity int NOT NULL ,
item_price decimal(8,2) NOT NULL ,
PRIMARY KEY (order_num, order_item)
) ENGINE=InnoDB;

CREATE TABLE orders
(
order_num int NOT NULL AUTO_INCREMENT,
order_date datetime NOT NULL ,
cust_id int NOT NULL ,
PRIMARY KEY (order_num)
) ENGINE=InnoDB;

CREATE TABLE products
(
prod_id char(10) NOT NULL,
vend_id int NOT NULL ,
prod_name char(255) NOT NULL ,
prod_price decimal(8,2) NOT NULL ,
prod_desc text NULL ,
PRIMARY KEY(prod_id)
) ENGINE=InnoDB;

CREATE TABLE vendors
(
vend_id int NOT NULL AUTO_INCREMENT,
vend_name char(50) NOT NULL ,
vend_address char(50) NULL ,
vend_city char(50) NULL ,
vend_state char(5) NULL ,
vend_zip char(10) NULL ,
vend_country char(50) NULL ,
PRIMARY KEY (vend_id)
) ENGINE=InnoDB;

CREATE TABLE productnotes
(
note_id int NOT NULL AUTO_INCREMENT,
prod_id char(10) NOT NULL,
note_date datetime NOT NULL,
note_text text NULL ,
PRIMARY KEY(note_id),
FULLTEXT(note_text)
) ENGINE=MyISAM;

ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num);
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id) REFERENCES products (prod_id);
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id) REFERENCES customers (cust_id);
ALTER TABLE products ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id) REFERENCES vendors (vend_id);


INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10001, 'Coyote Inc.', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'Y Lee', 'ylee@coyote.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES(10002, 'Mouse House', '333 Fromage Lane', 'Columbus', 'OH', '43333', 'USA', 'Jerry Mouse');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10003, 'Wascals', '1 Sunny Place', 'Muncie', 'IN', '42222', 'USA', 'Jim Jones', 'rabbit@wascally.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10004, 'Yosemite Place', '829 Riverside Drive', 'Phoenix', 'AZ', '88888', 'USA', 'Y Sam', 'sam@yosemite.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES(10005, 'E Fudd', '4545 53rd Street', 'Chicago', 'IL', '54545', 'USA', 'E Fudd');

INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1001,'Anvils R Us','123 Main Street','Southfield','MI','48075', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1002,'LT Supplies','500 Park Street','Anytown','OH','44333', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1003,'ACME','555 High Street','Los Angeles','CA','90046', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1004,'Furball Inc.','1000 5th Avenue','New York','NY','11111', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1005,'Jet Set','42 Galaxy Road','London', NULL,'N16 6PS', 'England');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1006,'Jouets Et Ours','1 Rue Amusement','Paris', NULL,'45678', 'France');


INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV01', 1001, '.5 ton anvil', 5.99, '.5 ton anvil, black, complete with handy hook');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV02', 1001, '1 ton anvil', 9.99, '1 ton anvil, black, complete with handy hook and carrying case');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV03', 1001, '2 ton anvil', 14.99, '2 ton anvil, black, complete with handy hook and carrying case');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('OL1', 1002, 'Oil can', 8.99, 'Oil can, red');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FU1', 1002, 'Fuses', 3.42, '1 dozen, extra long');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('SLING', 1003, 'Sling', 4.49, 'Sling, one size fits all');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('TNT1', 1003, 'TNT (1 stick)', 2.50, 'TNT, red, single stick');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('TNT2', 1003, 'TNT (5 sticks)', 10, 'TNT, red, pack of 10 sticks');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FB', 1003, 'Bird seed', 10, 'Large bag (suitable for road runners)');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FC', 1003, 'Carrots', 2.50, 'Carrots (rabbit hunting season only)');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('SAFE', 1003, 'Safe', 50, 'Safe with combination lock');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('DTNTR', 1003, 'Detonator', 13, 'Detonator (plunger powered), fuses not included');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('JP1000', 1005, 'JetPack 1000', 35, 'JetPack 1000, intended for single use');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('JP2000', 1005, 'JetPack 2000', 55, 'JetPack 2000, multi-use');

INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20005, '2005-09-01', 10001);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20006, '2005-09-12', 10003);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20007, '2005-09-30', 10004);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20008, '2005-10-03', 10005);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20009, '2005-10-08', 10001);

INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 1, 'ANV01', 10, 5.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 2, 'ANV02', 3, 9.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 3, 'TNT2', 5, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 4, 'FB', 1, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20006, 1, 'JP2000', 1, 55);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 1, 'TNT2', 100, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 1, 'FC', 50, 2.50);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 1, 'FB', 1, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 2, 'OL1', 1, 8.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 3, 'SLING', 1, 4.49);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 4, 'ANV03', 1, 14.99);

INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(101, 'TNT2', '2005-08-17',
'Customer complaint:
Sticks not individually wrapped, too easy to mistakenly detonate all at once.
Recommend individual wrapping.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(102, 'OL1', '2005-08-18',
'Can shipped full, refills not available.
Need to order new can if refill needed.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(103, 'SAFE', '2005-08-18',
'Safe is combination locked, combination not provided with safe.
This is rarely a problem as safes are typically blown up or dropped by customers.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(104, 'FC', '2005-08-19',
'Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(105, 'TNT2', '2005-08-20',
'Included fuses are short and have been known to detonate too quickly for some customers.
Longer fuses are available (item FU1) and should be recommended.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(106, 'TNT2', '2005-08-22',
'Matches not included, recommend purchase of matches or detonator (item DTNTR).'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(107, 'SAFE', '2005-08-23',
'Please note that no returns will be accepted if safe opened using explosives.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(108, 'ANV01', '2005-08-25',
'Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(109, 'ANV03', '2005-09-01',
'Item is extremely heavy. Designed for dropping, not recommended for use with slings, ropes, pulleys, or tightropes.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(110, 'FC', '2005-09-01',
'Customer complaint: rabbit has been able to detect trap, food apparently less effective now.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(111, 'SLING', '2005-09-02',
'Shipped unassembled, requires common tools (including oversized hammer).'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(112, 'SAFE', '2005-09-02',
'Customer complaint:
Circular hole in safe floor can apparently be easily cut with handsaw.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(113, 'ANV01', '2005-09-05',
'Customer complaint:
Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(114, 'SAFE', '2005-09-07',
'Call from individual trapped in safe plummeting to the ground, suggests an escape hatch be added.
Comment forwarded to vendor.'
);

数据表

 

一次添加多行数据
语法:
INSERT [INTO] tabale_name([COLUMN1,COLUMN2,…..])
SELECT VALUE UNION
SELECT VALUE

使用root用户登陆

$ mysql -u 用户名 -p

-p表示使用密码登录

  5.MySQL数据库版本和优势:

SQL Server内置函数(内置函数可作为where条件,聚合函数不能做为where条件)

拼接字段

  • 在 SELECT 之后、FROM 之前使用 Concat()函数拼接字段(列)

  • 将要查询并拼接的多个列名和其他要插入的字符(例如括号等)作为
    Concat()的参数

  • 各个参数之间使用逗号分隔

  • 多数DBMS使用 || 或者 + 来实现拼接,但MySQL使用 Concat()函数

  • 在进行SQL语句转换时需要留意这一区别

  • MySQL函数可以嵌套使用

  • 使用 Trim()函数来去除查询结果中两边的所有空格

  • 使用 LTrim()函数来去除查询结果中左边的所有空格

  • 使用 RTrim()函数来去除查询结果中右边的所有空格

  • 函数参数为要查找的列名

四、MySQL查询操作

                                    日期函数
        函数名                 
示例                                                  函数功能
        GetDate                
GetDate结果返回“当前日期”                             返回当前日期
        DateAdd                
DateAdd(mm,2,’2009-10-08′)结果返回‘2009-12-08’       
向日期指定部分添加数字,其中,yy表示年、mm表示月、dd表示日
  DateDiff               
DateDiff(dd,’2009-09-09′,’2010-09-09′)结果返回‘365’  
返回两个日期之间的间隔,其中,yy表示年、mm表示月、dd表示日
  DateName               
DateName(DW,’2009-09-09′)结果返回“星期三”            
以字符串的形式返回某个日期指定的部分
  DatePart               
DatePart(DW,’2009-09-09′)结果返回“4”                 
以整数形式返回某个日期指定的部分
  
实例:获取系统当前日期函数为GetDate(),表达式可以写为“COLUMN1<GetDate()”,SQL语句如下:
SELECT * FROM table_name WHERE COLUMN1<GetDate()

在表中增加列

mysql-> ALTER TABLE 数据表名
     -> ADD COLUMN 新增列名 数据类型(数据长度) 约束
     -> 插入的位置;

mysql-> ALTER TABLE 数据表名
     -> ADD 新增列名 数据类型(数据长度) 约束
     -> 插入的位置;

插入的位置:

新增列默认放在表的最右边,使用 FIRST 将新列插入到第一列,使用 AFTER 指定列名 将新列插入到指定列后面。

发表评论

电子邮件地址不会被公开。 必填项已用*标注