数据库常用命令和语句

一：数据库管理命令

1.mysql命令连接数据库格式：
mysql -h 主机地址 -P 端口号 -u用户号 -p用户密码
实例：
（1）mysql连接本地服务器，用户名为root，密码123
mysql -h localhost -u root -p123
注：用户名前可以有空格也可以没有空格，但是密码前必须没有空格。
（2）连接到远程主机上的Mysql
假设远程主机的IP为：110.110.110.110，端口“3306”，用户名为root，密码为123。
mysql -h 110.110.110.110 -P 3306 -u root -p123
（3）MySQL连接本地数据库，用户名为“root”，隐藏密码
mysql -h localhost -u root -p
enter password
2.退出mysql命令
exit
3.修改用户密码
mysqladmin -u 用户名 -p 旧密码 password 新密码
开始时root没有密码，-p旧密码一项可以省略，增加一个密码ab12
mysqladmin -u root -password ab12
将root密码修改为abc123
mysqladmin -u root -p abc12 -password abc123;
4.增加新用户并控制其权限
grant select on 数据库.* to 用户名@登陆主机 identified by “密码”；
增加一个用户test1，密码为abc，让他可以在任何主机上登录，并对所有数据库有查询、插入、修改、删除的权限。首先用root用户连入MYSQL，然后键入以下命令
grant select,insert,update,delete on . to [email=test1@”%]test1@”%[/email]” Identified by “abc”;
增加一个用户test2密码为abc，让他只可以在localhost上登录，并可以对数据库mydb进行查询、插入、修改、删除的操作（localhost指本地主机，即MYSQL数据库所在的那台主机），这样用户即使用知道test2的密码，他也无法从internet上直接访问数据库，只能通过MYSQL主机上的web页来访问了
grant select,insert,update,delete on mydb.* to [email=test2@localhost]test2@localhost[/email] identified by “abc”;
如果不想test2有密码可以将密码消除
grant select,insert,update,delete on mydb.* to [email=test2@localhost]test2@localhost[/email] identified by “”;

二、SQL语句

数据库：
#查看所有的数据库
SHOW DATABASES；
#创建一个数据库
CREATE DATABASES k；
#删除一个数据库
DROP DATABASE k；
#使用这个数据库
USE k；
表：
#查看所有表
SHOW TABLES；
#创建一个表
CREATE TABLE n(id INT,name VARCHAR(10));
#references用于使用他表中的主键对此表中的外键进行约束，unique表示为列提供唯一性的保证。一张表中可以有多个unique。
CREATE TABLE m(id INT, name VARCHAR(10), PRIMARY KEY (id),FOREIGN KEY (id) REFERENCES n(id), UNIQUE (name));
CREATE TABLE m(id INT, name VARCHAR(10));

直接将查询结果导入或复制到新创建的表

CREATE TABLE n SELECT * FROM m;

新创建的表与一个存在的表的数据结构类似

CREATE TABLE m LIKE n;

创建一个临时表

临时表将在你连接MySQL期间存在。当断开连接时，MySQL将自动删除表并释放所用的空间。也可手动删除。

CREATE TEMPORARY TABLE l(id INT, name VARCHAR(10));

直接将查询结果导入或复制到新创建的临时表

CREATE TEMPORARY TABLE tt SELECT * FROM n;

删除一个存在表

DROP TABLE IF EXISTS m;

更改存在表的名称

ALTER TABLE n RENAME m;
RENAME TABLE n TO m;

查看表的结构(以下五条语句效果相同）

DESC n; # 因为简单，所以建议使用
DESCRIBE n;
SHOW COLUMNS IN n;
SHOW COLUMNS FROM n;
EXPLAIN n;

查看表的创建语句

SHOW CREATE TABLE n;
表的结构：
#添加字段
ALTER TABLE n ADD age VARCHAR（2）；
#删除字段
ALTER TABLE n DROP age；
#更改字段和属性 age是原字段名称。
ALTER TABLE n CHANGE age a INT；
#只更改字段属性
ALTER TABLE n MODIFY age VARCHAE（7）；
表的数据：
#增加数据
INSERT INTO n VALUE（1，‘tom’，‘23’），（2，‘john’，‘22’）；
#把数据复制一遍重新插入
INSERT INTO n SELECT * FROM n；
#删除数据
DELETE FROM n WHERE id = 2；
#更改数据
UPDATA n SET name =‘tom’WHERE id =2；
#数据查找
SELECT * FROM n WHERE name LIKE ‘%h%’；
#数据排序（反序） name升序（默认），id 降序
SELECT * FROM n ORDER BY name，id DESC；
键：
#添加主键
ALTER TABLE n ADD PRIMARY KEY（id）；
#给列增加主键约束
格式：alter table 表格名称 add constraint 约束名称 增加的约束类型 （列名）
ORACLE支持五种类型的完整性约束
NOT NULL (非空)–防止NULL值进入指定的列,在单列基础上定义,默认情况下,ORACLE允许在任何列中有NULL值.
CHECK (检查)–检查在约束中指定的条件是否得到了满足.
UNIQUE (唯一)–保证在指定的列中没有重复值.在该表中每一个值或者每一组值都将是唯一的.
PRIMARY KEY (主键)–用来唯一的标识出表的每一行,并且防止出现NULL值,一个表只能有一个主键约束.
POREIGN KEY (外部键)–通过使用公共列在表之间建立一种父子(parent-child)关系,在表上定义的外部键可以指向主键或者其他表的唯一键.
#给id列增加主键约束
ALTER TABLE n ADD CONSTRAINT pk_n PRIMAEY KEY （id）；
#删除主键
ALTER TABLE n DROP PRIMARY KEY；
#添加外键，自动生成键名m_idfk_1,references参考完整性，指向对应表中的主键。
ALTER TABLE m ADD FOREIGN KEY （id） REFERENCES n（id）；
#添加外键，使用定义的键名fk_id
ALTER TABLE m ADD CONSTRANINT fk_id FOREIGN KEY（id） REFERENCES n（id）；
#删除外键
ALTER TABLE m DROP FOREIGN KEY ‘fk_id’;
#修改外键，删除后重新建
ALTER TABLE m DROP FOREIGN KEY ‘fk_id’，ADD CONSTRAINT fk_id2 FOREIGN KEY（id） REFERENCES n（id）；
添加唯一键
ALTER TABLE n ADD UNIQUE （name）；
ALTER TABLE n ADD UNIQUE uname （name）；
ALTER TABLE n ADD UNIQUE INDEX u_name （name）；
ALTER TABLE n ADD CONSTRAINT u_name UNIQUE （name）；
CREATE UNIQUE INDEX u_name ON n(name);

添加索引

ALTER TABLE n ADD INDEX (age);
ALTER TABLE n ADD INDEX i_age (age);
CREATE INDEX i_age ON n(age);

删除索引或唯一键

DROP INDEX u_name ON n;
DROP INDEX i_age ON n;
视图：
#创建视图
CREATE VIEW v AS SELECT id，name FROM n；
CREATE VIEW v（id，name）AS SELECT id，name FEOM n；

查看视图(与表操作类似)

ELECT * FROM v;
DESC v;

查看创建视图语句

SHOW CREATE VIEW v;

更改视图

CREATE OR REPLACE VIEW v AS SELECT name, age FROM n;
ALTER VIEW v AS SELECT name FROM n ;

删除视图

DROP VIEW IF EXISTS v;
联接

内联接：使用比较运算符根据每个表共有的列的值匹配两个表中的行

SELECT * FROM m INNER JOIN n ON m.id = n.id;

左外联接：以左表为基础，根据ON后给出的两表的条件将两表连接起来。结果会将左表所有的查询信息列出，而右表只列出ON后条件与左表满足的部分

SELECT * FROM m LEFT JOIN n ON m.id = n.id;

右外联接

SELECT * FROM m RIGHT JOIN n ON m.id = n.id;

交叉联接

SELECT * FROM m CROSS JOIN n; # 标准写法
SELECT * FROM m, n;
#类似全连接full join的联接用法
SELECT id,name FROM m
UNION
SELECT id,name FROM n;
函数：
#聚合函数
SELECT count（id） AS total FROM n；#总数
SELECT sum（age） AS all_age FROM n；#总和
SELECT avg（age） AS all_age FROM n；#平均值
SELECT max（age） AS all_age FROM n；#最大值
SELECT min（age） AS all_age FROM n；#最小值
#窗口函数
窗口指表中记录的集合。窗口可以理解为把待分析数据分为n组的组或集合。每组内的每条数据都要在此窗口内执行同一个函数。窗口函数运行后，数据行数保持不变。
函数名 OVER 子句
over关键字用来指定函数执行的窗口范围，若后面括号中什么都不写，则意味着窗口包含满足WHERE条件的所有行，窗口函数基于所有行进行计算；如果不为空，则支持以下4中语法来设置窗口。
①window_name：WINDOW w as window_name给窗口指定一个别名。如果SQL中涉及的窗口较多，采用别名可以看起来更清晰易读；
②PARTITION BY 子句：窗口按照哪些字段进行分组，窗口函数在不同的分组上分别执行；
③ORDER BY子句：按照哪些字段进行排序，窗口函数将按照排序后的记录顺序进行编号；
④FRAME子句：FRAME是当前分区的一个子集，子句用来定义子集的规则，通常用来作为滑动窗口使用。
序号函数：显示分区中的当前行号
ROW_NUMBER（） 排序：1,2,3
RANK（） 排序：1,1,3
DENSE_RANK() 排序：1,1,2
分布函数：
PERCENT_RANK() (rank-1)/(rows-1)
CUME_DIST() 当前rank值的行数/总行数
前后函数：
LAG（expr，n） 返回当前行的前n行的expr的值
LEAD（expr，n） 返回当前行的后n行的expr的值
头尾函数：
FIRST_VALUE(expr) 返回第一个expr的值
LAST_VALUE(expr) 返回最后一个expr的值
其他函数：
NTH_VALUE(expr,n) 返回第n个expr的值
NTILE(n) 将有序数据分为n个桶，记录等级数
示例
查询每个学生的分数最高的前3门课程
SELECT *
FROM（
SELECT stu_id,
ROW_NUMBER() OVER (PARTITION BY stu_id ORDER BY score DESC) AS score_order,
lesson_id,
score
FROM t_score) t
WHERE score_order <= 3;
#数学函数
SELECT abs（-5）；#绝对值
SELECT bin（15），oct（15），hex（15）；#二进制，八进制，十六进制
SELECT pi（）；#圆周率3.141593
SELECT ceil（5.5）；#大于x的最小整数值6
SELECT floor（5.5）；#小于x的最大整数值5
SELECT greatest（3,1,4,1,5,9,2,6）；#返回集合中最大的值9
SELECT least（3,1,4,1,5,9,2,6）；#返回集合中最小的值1
SELECT mod(5,3); # 余数2
SELECT rand(); # 返回０到１内的随机值，每次不一样
SELECT rand(5); # 提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。
SELECT round(1415.1415); # 四舍五入1415
SELECT round(1415.1415, 3); # 四舍五入三位数1415.142
SELECT round(1415.1415, -1); # 四舍五入整数位数1420
SELECT truncate(1415.1415, 3); # 截短为3位小数1415.141
SELECT truncate(1415.1415, -1); # 截短为-1位小数1410
SELECT sign(-5); # 符号的值负数-1
SELECT sign(5); # 符号的值正数1
SELECT sqrt(9); # 平方根3
SELECT sqrt(9); # 平方根3

字符串函数

SELECT concat(‘a’, ‘p’, ‘p’, ‘le’); # 连接字符串-apple
SELECT concat_ws(’,’, ‘a’, ‘p’, ‘p’, ‘le’); # 连接用’,‘分割字符串-a,p,p,le
SELECT insert(‘chinese’, 3, 2, ‘IN’); # 将字符串’chinese’从3位置开始的2个字符替换为’IN’-chINese
SELECT left(‘chinese’, 4); # 返回字符串’chinese’左边的4个字符-chin
SELECT right(‘chinese’, 3); # 返回字符串’chinese’右边的3个字符-ese
SELECT substring(‘chinese’, 3); # 返回字符串’chinese’第三个字符之后的子字符串-inese
SELECT substring(‘chinese’, -3); # 返回字符串’chinese’倒数第三个字符之后的子字符串-ese
SELECT substring(‘chinese’, 3, 2); # 返回字符串’chinese’第三个字符之后的两个字符-in
SELECT trim(’ chinese ‘); # 切割字符串’ chinese ‘两边的空字符-‘chinese’
SELECT ltrim(’ chinese ‘); # 切割字符串’ chinese ‘两边的空字符-‘chinese ’
SELECT rtrim(’ chinese ‘); # 切割字符串’ chinese ‘两边的空字符-’ chinese’
SELECT repeat(‘boy’, 3); # 重复字符’boy’三次-‘boyboyboy’
SELECT reverse(‘chinese’); # 反向排序-‘esenihc’
SELECT length(‘chinese’); # 返回字符串的长度-7
SELECT upper(‘chINese’), lower(‘chINese’); # 大写小写 CHINESE chinese
SELECT ucase(‘chINese’), lcase(‘chINese’); # 大写小写 CHINESE chinese
SELECT position(‘i’ IN ‘chinese’); # 返回’i’在’chinese’的第一个位置-3
SELECT position(‘e’ IN ‘chinese’); # 返回’i’在’chinese’的第一个位置-5
SELECT strcmp(‘abc’, ‘abd’); # 比较字符串，第一个参数小于第二个返回负数- -1
SELECT strcmp(‘abc’, ‘abb’); # 比较字符串，第一个参数大于第二个返回正数- 1

时间函数

SELECT current_date, current_time, now(); # 2018-01-13 12:33:43 2018-01-13 12:33:43
SELECT hour(current_time), minute(current_time), second(current_time); # 12 31 34
SELECT year(current_date), month(current_date), week(current_date); # 2018 1 1
SELECT quarter(current_date); # 1
SELECT monthname(current_date), dayname(current_date); # January Saturday
SELECT dayofweek(current_date), dayofmonth(current_date), dayofyear(current_date); # 7 13 13

控制流函数

SELECT if(3>2, ‘t’, ‘f’), if(3<2, ‘t’, ‘f’); # t f
SELECT ifnull(NULL, ‘t’), ifnull(2, ‘t’); # t 2
SELECT isnull(1), isnull(1/0); # 0 1 是null返回1，不是null返回0
SELECT nullif(‘a’, ‘a’), nullif(‘a’, ‘b’); # null a 参数相同或成立返回null，不同或不成立则返回第一个参数
SELECT CASE 2
WHEN 1 THEN ‘first’
WHEN 2 THEN ‘second’
WHEN 3 THEN ‘third’
ELSE ‘other’
END ; # second

系统信息函数

SELECT database(); # 当前数据库名-test
SELECT connection_id(); # 当前用户id-306
SELECT user(); # 当前用户-root@localhost
SELECT version(); # 当前mysql版本
SELECT found_rows(); # 返回上次查询的检索行数
#用户：

增加用户

CREATE USER ‘test’@‘localhost’ IDENTIFIED BY ‘test’;
INSERT INTO mysql.user(Host, User, Password) VALUES (‘localhost’, ‘test’, Password(‘test’)); # 在用户表中插入用户信息，直接操作User表不推荐

删除用户

DROP USER ‘test’@‘localhost’;
DELETE FROM mysql.user WHERE User=‘test’ AND Host=‘localhost’;
FLUSH PRIVILEGES ;

更改用户密码

SET PASSWORD FOR ‘test’@‘localhost’ = PASSWORD(‘test’);
UPDATE mysql.user SET Password=Password(‘t’) WHERE User=‘test’ AND Host=‘localhost’;
FLUSH PRIVILEGES ;

用户授权

GRANT ALL PRIVILEGES ON . TO test@localhost IDENTIFIED BY ‘test’;

授予用’test’密码登陆成功的test@localhost用户操作所有数据库的所有表的所有的权限

FLUSH PRIVILEGES ; # 刷新系统权限表,使授予权限生效

撤销用户授权

REVOKE DELETE ON . FROM ‘test’@‘localhost’; # 取消该用户的删除权限
存储过程
CREATE PROCEDURE <过程名> ( [过程参数[,…] ] ) <过程体>
[过程参数[,…] ] 格式
[ IN | OUT | INOUT ] <参数名> <类型>
过程参数有in，out，inout三种类型，分别代表输入参数，输出参数，输入/输出参数。
#创建存储过程
DELIMITER // #使用//代替默认的；作为语句的终结，回车后符号前语句可以执行。
CREATE PROCEDURE getDates（）
BAGIN
SELECT * FEOM test；
END//
CREATE PROCEDURE getDates_2(IN id INT) # in参数
BEGIN
SELECT * FROM test WHERE a = id;
END //
CREATE PROCEDURE getDates_3(OUT sum INT) # out参数
BEGIN
SET sum = (SELECT count(*) FROM test);
END //
CREATE PROCEDURE getDates_4(INOUT i INT) # inout参数
BEGIN
SET i = i + 1;
END //
DELIMITER ;

删除存储过程

DROP PROCEDURE IF EXISTS getDates;

修改存储过程的特性

ALTER PROCEDURE getDates MODIFIES SQL DATA ;

修改存储过程语句（删除再重建）略

查看存储过程

SHOW PROCEDURE STATUS LIKE ‘getDates’; # 状态
SHOW CREATE PROCEDURE getDates_3; # 语句

调用存储过程

CALL getDates();
CALL getDates_2(1);
CALL getDates_3(@s);
SELECT @s;
SET @i = 1;
CALL getDates_4(@i);
SELECT @i; # @i = 2
其他语句
#查看所有的表信息（包括视图）
SHOW TABLE STATUS；
其他
#数据库备份
mysqldump -u root -p db_name>file.sql
mysqldump -u root -p db_name table_name>file.sql
#数据库还原
mysql -u root -p<C:\file.sql