你的位置:首页 > 数据库

[数据库]MySQL学习之路(1):SQL脚本语言


 

使用MySQL数据库,首先安装MySQL数据库,本文所有SQL脚本在MySQL上测试和执行。
安装Mysql服务器;
安装Mysql workbench客户端,可以以图形化界面管理mysql;
安装phpMyadmin客户端,可以通过bs方式图形化管理Mysql;

1. Mysql服务器操作


 

windows下为例,win+r 进入cmd界面
启动服务器:net start mysql
停止服务器:net stop mysql
连接服务器:mysql -h localhost -u root -p password

2. Database基本操作


 

create database db_bookstore; //创建数据库:
use database db_bookstore; //使用数据库:
drop database db_bookstore; //删除数据库:


3. DDL 表操作



①创建表
creat table table_name(id int not null auto_increment primary key,
name char(50)
) if not exists;

②查看表结构
show columns from tb_name from db_name;
desc table_name;

③删除表:
drop table tb_name if exists;

④修改表: alter tb_name alter/modify/change [column] ...
ALTER COLUMN:设置或删除列的默认值(操作速度非常快)
例子:
alter table film alter column rental_duration set default 5;
alter table film alter column rental_duration drop default;

CHANGE COLUMN:列的重命名、列类型的变更以及列位置的移动
例子:
ALTER TABLE MyTable CHANGE COLUMN foo bar VARCHAR(32) NOT NULL FIRST;
ALTER TABLE MyTable CHANGE COLUMN foo bar VARCHAR(32) NOT NULL AFTER baz;

MODIFY COLUMN:除了不能给列重命名之外,他干的活和CHANGE COLUMN是一样的
例子:
ALTER TABLE MyTable MODIFY COLUMN foo VARCHAR(32) NOT NULL AFTER baz;

⑤重命名表
ALTER tb_name RENAME [AS] new_tb_name;
RENAME TABLE tb_name TO new_tb_name;

注意:[]内的是可以选择的,包括change,modify,alter后满的COLUMN

4. DML语言:数据操纵语言


 

①插入
INSERT INTO tb_name(field1,...) VALUES(value1,...);
②修改
UPDATE tb_name SET f1=v1,f2=v2...;
③删除
DELETE FROM tb_name WHERE condition

 


5.DQL:数据查询,SELECT专题


 

--------------------------------
SELECT f1,f2,... FROM tb_name
WHERE primary_constraint
GROUP BY grouping_columns
ORDER BY column_name DESC/ASC
HAVING secdonary_constraint
LIMIT count;
---------------------------------

关键语法:
SELECT *
SELECT DISTINCT *
SELECT TOP count *


(1)单表查询
SELECT * FROM tb_name; // 所有字段
SELECT id,name FROM tb_name; // 指定字段
SELECT * FROM tb_name WHERE id<5 // 查询符合条件的字段

(2)IN, NOT IN, BETWEEN AND (这是指明字段的范围,后面还有一个IN用于子查询)
SELECT * FROM tb_name WHERE id IN(1,2,3);
SELECT * FROM tb_name WHERE id NOT IN(1,2,3);
SELECT * FROM tb_name WHERE id BETWEEN 1 AND 5;

(3)使用LIKE + 通配符
%:匹配一个或者多个字符
_: 匹配任意单个字符
SELECT * FROM tb_name WHERE user LIKE 'm%r_';// 以m开头,中间若干个字符,r后面一个字符结尾

(4)空值查询IS NULL,IS NOT NULL
SELECT * FROM tb_name WHERE row IS NULL;
SELECT * FROM tb_name WHERE row IS NOT NULL;

(5)多条件查询AND, OR
SELECT * FROM tb_name WHERE id=1 AND name='Jack';
SELECT * FROM tb_name WHERE id=1 OR name='Jack';

(6)去掉重复行
SELECT DISTINCT * FROM tb_name;

(7)使用ORDER BY [ASC/DESC]
SELECT * FROM tb_name ORDER BY id DESC
SELECT * FROM tb_name ORDER BY id ASC

(8)GROUP BY 子句
注意:没有出现在GROUP BY子句中的字段,不能出现在SELECT 子句中,聚合函数除外
如果SELECT 子句出现GROUP BY中没有出现的字段,则结果只显示每一组的一条记录;
一般形式为:
SELECT field,func_name(field) FROM tb_name GROUP BY field;

(9)多字段分组: 2个字段的笛卡尔积
SELECT field1,field2 FROM tb_name GROUP BY field1,field2;

(10)LIMIT限制结果集
SELECT * FROM db_name WHERE id<5 LIMIT 3; // 显示结果的3行
SELECT * FROM db_name WHERE id<5 LIMIT 5,10; // 从第5行开始,的10行

(11)聚合函数:MIN(),AGV,MAX,COUNT
聚合函数的特点是根据一组数据计算出一个值,聚合函数只能对非NULL值进行计算,NULL值被忽略;

(12)连接查询:
理解:就是有至少2表,查询结果是两张表满足某种条件的拼接;
不带查询条件,则是2张表的笛卡尔积;

①内连接查询,最常见的是等值连接
SELECT * FROM tb_left,tb_right WHERE tb_left.id=tb_right.id;
SELECT * FROM tb_left INNER JOIN tb_right WHERE tb_left.id=tb_right.id;

②外连接查询:
左外连接: 内连接结果,还包含不符合条件的左表数据,右表相应列加NULL
SELECT * FROM tb_left LEFT [OUTER] JOIN tb_right WHERE tb_left.id=tb_right.id;


右外连接:
SELECT * FROM tb_left RIGHT [OUTER] JOIN tb_right WHERE tb_left.id=tb_right.id;

全外连接:
SELECT * FROM tb_left FULL OUTER JOIN tb_right WHERE tb_left.id=tb_right.id;

(13)字查询:
①IN:
SELECT * FROM tb_name WHERE id IN(SELECT id FROM tb_2);
②ANY:
SELECT * FROM tb_name WHERE id<ANY(SELECT id FROM tb_2);
③ALL:
SELECT * FROM tb_name WHERE id<ALL(SELECT id FROM tb_2);
④EXISTS: 子查询返回一个bool值
SELECT * FROM tb_1 WHERE row>=90 AND EXISTS(SELECT * FROM tb_2 WHERE score='优秀');
// 如果有score为优秀的记录,则查询 row>=90的记录

⑤关系运算符:子查询返回唯一值
SELECT * FROM tb_name WHERE id >(SELECT id FROM tb_2 WHERE id=1);

注:子查询一般都是一个单列列表,SELECT list FROM tb_name; list为单列列表
使用EXISTS时候除外;
使用关系运算符是子查询返回唯一值;

(14)合并查询结果

UNION: 合并查询结果,除去相同的行
UNION ALL : 合并查询结果,简单的把两个集合的元素混合

SELECT id,name UNION SELECT id,author; // 正确
SELECT id,name UNION SELECT id; // 错误,字段数目不同,无法UNION


注意:前提条件是,左边和右边的字段列表的字段数必须相同


6. Mysql函数


 

6.1 数学函数
①ABS(X) 取绝对值
SELECT ABS(-1); // 结果为1
②取整函数
CEIL(); // 向上取整
FLOOR(); // 向下取整
③随机数生成器
RAND(); // 0-1 随机数
RAND(X); // 产生0-1随机数,x相同时,随机数相同
④符号检测器
SIGN(X); // 返回-1,0,1作为参数的符号
⑤圆周率
PI(); // 返回π值,默认7位

⑥小数位数截断函数:
TRUNCATE(X,Y) // 保留x后y位小数

⑦圆整(最近的整数)
ROUND(X) // 返回离X最近的整数
ROUND(X,Y) // 返回x,后面保留y位小数,截断时四舍五入
// 例如ROUND(3.1415,3)=3.142
⑧平方开方
POW(X,Y) // x的y次方
SQRT(X); // 对x开平方
MOD(X,Y) // 求余数
⑨指数对数
EXP(X); // e的x次方
LOG(X);
LOG10(X);
⑩弧度和角度转换:
DEGREES(radians) // 弧度---角度
RADIANS(degree) // 度数---弧度

三角函数:
SIN(X); // x是弧度
ASIN(X);
COS(X);
ACOS(X);
TAN(X);
ATAN(X);
COT(X);

6.2 字符串函数


 

①长度:
CHAR_LENGTH(str);
②大小写转换:
UPPER(str);UCASE(str);
LOWER(str);LCASE(str);
③修剪函数(前导、结尾空格)
LTRIM(s);RTRIM(s);TRIM(s);//删除引导空格;结尾空格;引导和结尾空格
TRIM(s1 from s_src); //删除s2开始和结尾处的s1
④替换、子串、反转
REPLACE(s,s1,s2); //用s2替换s中的s1
SUBSTRING(s,n,len); //s处n开始长度为len的字符串
LEFT(s,n),RIGHT(s,n) //返回左边或者右边长度为n的字符串
REVERSE(s); //字符串倒序

更多请参考:PHP 学习路线图《MySQL快速入门》

6.3 日期和时间函数---------DATE
        |----------TIME


 

①日期、时间
CURRENT_DATE(); // 2014-08-07
CURRENT_TIME(); // 10:30:30

②日期和时间(时间戳)
NOW(); // 2014-08-07 10:30:30
CURRENT_TIMESTAMP();// 2014-08-07 10:30:30

LOCALTIME(); // 2014-08-07 10:30:30
LOCALTIMESTAMP(); // 2014-08-07 10:30:30
SYSDATE(); // 2014-08-07 10:30:30

③以秒形式的时间戳和转换
UNIX_TIMESTAMP(); // 当前UNIX时间戳
UNIX_TIMESTAMP(d);
FROM_UNIXTIME(d);

④年月日、时分秒
YEAR(d);
MONTH(d);
DAY(d);
HOUR(t);
MINUTE(t);
SECOND(t);

⑤工具函数:
DATEDIFF(d1,d2) // d1-d2 的天数,d1是最近的日期,d2是早些的日期
ADDDATE(d,n);
SUBDATE(d,n);
更多请参考:PHP 学习路线图《MySQL快速入门》

6.4 条件判断函数


 

6.5 系统信息函数


 

①版本号、连接数
VERSION(); // 获取版本
CONNECTION_ID(); // 获取连接数

②当前数据库名
DATABASE();SCHEMA(); // 当前数据库

③获取当前用户
USER();SYSTEM_USER();SESSON_USER(); // 当前用户
CURRENT_USER();

④字符集
CHARSET();

⑤最后插入的ID
LAST_INSERT_ID();

6.6 加密函数


 

MD5(str); // 普通数据加密
PASSWORD(s); // 不可逆加密,对用户名加密

ENCODE(str_dst,pass_str); // 用pass_str加密str_dst,返回二进制数,用blob类型存储
DECODE(crypt_str,psss_str);// 用pass_str解密crpyt_str,加密和解密用的pass_str相同