SQL语言,是结构化查询语言(Structured Query Language)的简称。SQL语言是一种数据库查询和程序设计语言,用于存取数据以及查询、更新和管理关系数据库系统;同时也是数据库脚本文件的扩展名。详见 360百科
查询数据库
SHOW DATABASES;
创建数据库,设置字符集为 utf8mb4
CREATE DATABASE IF NOT EXISTS db_name CHARACTER SET = utf8mb4;
查询创建
SHOW CREATE DATABASE db_name;
修改数据库字符集
ALTER DATABASE db_name CHARACTER SET = utf8;
ALTER DATABASE db_name CHARACTER SET = utf8mb4;
删除数据库
DROP DATABASE IF EXISTS db_name;
指定使用数据库
USE db_name;
查看数据表
SHOW TABLES FROM db_name;
SHOW FULL TABLES IN db_name;
查看数据表结构
DESC db_name.tbl_name;
查看数据表
SELECT * FROM db_name.tbl_name;
直接查看表
mysql -u root -p table_name -e “command;”
创建数据表
CREATE [ TEMPORARY ] TABLE [ IF NOT EXISTS ] tbl_name(
col_name column_definition,
col_name column_definition,
col_name column_definition,
...
)
[table_options];
创建用户
CREATE USER IF NOT EXISTS user_name IDENTIFIED BY 'password';
删除用户
DROP USER user_name;
查看用户信息
SELECT Host,User,Password from mysql.user;
修改用户密码
5.7.6 之前:SET PASSWORD FOR user_name = PASSWORD('new_password');
5.7.6 之后:ALTER USER user_name IDENTIFIED BY 'new_password';
````
----------
**数据库授权( ALL 所有权限;SELECT 查询;INSERT 插入;UPDATE 更新;DELETE 删除)**
GRANT 权限 ON 数据库对象 TO 用户
(1) GRANT 权限 ON 数据库.表单名称 TO [email protected]/* <![CDATA[ */!function(t,e,r,n,c,a,p,m,o){try{t=document.currentScript||function(){for(t=document.getElementsByTagName('script'),e=t.length;e--;)if(t[e].getAttribute('data-yjshash'))return t[e]}();if(t&&(c=t.previousSibling)){p=t.parentNode;if(a=c.getAttribute('data-yjsemail')){for(e='',o=0,r='0x'+a.substr(0,2)|0,n=2;a.length-n;n+=2){m=('0'+('0x'+a.substr(n,2)^r).toString(16)).slice(-2);if((a.length-n)<=6&&a.length>=128)o=(parseInt(m)<=191)?1:o*2;if(o>1)break;e+='%'+m;}p.replaceChild(document.createTextNode(decodeURIComponent(e)),c)}p.removeChild(t)}}catch(u){}}()/* ]]> */ //对某个特定数据库中的特定表单给予授权
(2) GRANT 权限 ON 数据库.* TO [email protected]/* <![CDATA[ */!function(t,e,r,n,c,a,p,m,o){try{t=document.currentScript||function(){for(t=document.getElementsByTagName('script'),e=t.length;e--;)if(t[e].getAttribute('data-yjshash'))return t[e]}();if(t&&(c=t.previousSibling)){p=t.parentNode;if(a=c.getAttribute('data-yjsemail')){for(e='',o=0,r='0x'+a.substr(0,2)|0,n=2;a.length-n;n+=2){m=('0'+('0x'+a.substr(n,2)^r).toString(16)).slice(-2);if((a.length-n)<=6&&a.length>=128)o=(parseInt(m)<=191)?1:o*2;if(o>1)break;e+='%'+m;}p.replaceChild(document.createTextNode(decodeURIComponent(e)),c)}p.removeChild(t)}}catch(u){}}()/* ]]> */ //对某个特定数据库中的所有表单给予授权
(3) GRANT 权限1,权限2 ON 数据库.* TO [email protected]/* <![CDATA[ */!function(t,e,r,n,c,a,p,m,o){try{t=document.currentScript||function(){for(t=document.getElementsByTagName('script'),e=t.length;e--;)if(t[e].getAttribute('data-yjshash'))return t[e]}();if(t&&(c=t.previousSibling)){p=t.parentNode;if(a=c.getAttribute('data-yjsemail')){for(e='',o=0,r='0x'+a.substr(0,2)|0,n=2;a.length-n;n+=2){m=('0'+('0x'+a.substr(n,2)^r).toString(16)).slice(-2);if((a.length-n)<=6&&a.length>=128)o=(parseInt(m)<=191)?1:o*2;if(o>1)break;e+='%'+m;}p.replaceChild(document.createTextNode(decodeURIComponent(e)),c)}p.removeChild(t)}}catch(u){}}()/* ]]> */ //对某个数据库中的所有表单给予多个授权
(4) GRANT ALL PRIVILEGES ON *.* TO [email protected]/* <![CDATA[ */!function(t,e,r,n,c,a,p,m,o){try{t=document.currentScript||function(){for(t=document.getElementsByTagName('script'),e=t.length;e--;)if(t[e].getAttribute('data-yjshash'))return t[e]}();if(t&&(c=t.previousSibling)){p=t.parentNode;if(a=c.getAttribute('data-yjsemail')){for(e='',o=0,r='0x'+a.substr(0,2)|0,n=2;a.length-n;n+=2){m=('0'+('0x'+a.substr(n,2)^r).toString(16)).slice(-2);if((a.length-n)<=6&&a.length>=128)o=(parseInt(m)<=191)?1:o*2;if(o>1)break;e+='%'+m;}p.replaceChild(document.createTextNode(decodeURIComponent(e)),c)}p.removeChild(t)}}catch(u){}}()/* ]]> */ //对所有数据库及所有表单给予全部授权(需谨慎操作)
**刷新权限**
FLUSH PRIVILEGES;
**撤销授权**
REVOKE 权限 ON 数据库 FROM [email protected]/* <![CDATA[ */!function(t,e,r,n,c,a,p,m,o){try{t=document.currentScript||function(){for(t=document.getElementsByTagName('script'),e=t.length;e--;)if(t[e].getAttribute('data-yjshash'))return t[e]}();if(t&&(c=t.previousSibling)){p=t.parentNode;if(a=c.getAttribute('data-yjsemail')){for(e='',o=0,r='0x'+a.substr(0,2)|0,n=2;a.length-n;n+=2){m=('0'+('0x'+a.substr(n,2)^r).toString(16)).slice(-2);if((a.length-n)<=6&&a.length>=128)o=(parseInt(m)<=191)?1:o*2;if(o>1)break;e+='%'+m;}p.replaceChild(document.createTextNode(decodeURIComponent(e)),c)}p.removeChild(t)}}catch(u){}}()/* ]]> */;
**查看权限**
自己:SHOW GRANTS;
某个用户: SHOW GRANTS FOR [email protected]/* <![CDATA[ */!function(t,e,r,n,c,a,p,m,o){try{t=document.currentScript||function(){for(t=document.getElementsByTagName('script'),e=t.length;e--;)if(t[e].getAttribute('data-yjshash'))return t[e]}();if(t&&(c=t.previousSibling)){p=t.parentNode;if(a=c.getAttribute('data-yjsemail')){for(e='',o=0,r='0x'+a.substr(0,2)|0,n=2;a.length-n;n+=2){m=('0'+('0x'+a.substr(n,2)^r).toString(16)).slice(-2);if((a.length-n)<=6&&a.length>=128)o=(parseInt(m)<=191)?1:o*2;if(o>1)break;e+='%'+m;}p.replaceChild(document.createTextNode(decodeURIComponent(e)),c)}p.removeChild(t)}}catch(u){}}()/* ]]> */;
----------
**修改数据表**
修改表名:`ALTER TABLE tbl_name RENAME [ TO|AS ] new_tbl_name`
添加列:`ALTER TABLE tbl_name ADD [ COLUMN ] (col_name column_definition,...)`
删除列:`ALTER TABLE tbl_name DROP [ COLUMN ] col_name`
修改数据类型:`ALTER TABLE tbl_name ALTER [ COLUMN ] col_name {SET DEFAULT literal | DROP DEFAULT}`
修改列定义:`ALTER TABLE tbl_name CHANGE [ COLUMN ] old_col_name new_col_name column_definition`
更新字段:`UPDATE `tbl_name` SET `col_name`=’String’ WHERE `col_name`=’String’;`
删表:`DELETE from tabl_name;`
清空表: `TRUNCATE table tbl_name;`
----------
**数据备份**
(1) 直接备份数据目录(完整备份)
*备份完成既定修改目录所属组,所有者,否则mysql无法启动*
(2) mysqldump 客户端备份工具
备份数据或数据库中的一个或多个表 `mysqldump [ options ] db_name [tbl_name ...]`
备份一个或多个数据库 `mysqldump [ options ] --databases db_name ...`
备份所有数据库 `mysqldump [ options ] --all-databases`
**实列:**
mysqldump -u root --databases database_name > file_name.sql
**还原数据库**
mysql -u root -p database_name < file_name.sql