MySQL系列：SQL语句详解
一、数据库管理1.创建数据库：createDatabase database_name;
CREATE{DATABASE | SCHEMA}db_name;
CHARACTER SET [=] charset_name
COLLATE [=] collation_name
查看支持的所有字符集：SHOWCHARACTER SET
查看支持的所有排序规则：SHOWCOLLATION
注意：database_name命名规范
1)首字母不能是数字、$、空格、特殊字符、保留字
2)最大128字节
3)由字母、数字、下划线、@和$组成
2.查看数据库
SHOW databases
3.选择数据库
USE database_name
4.删除数据库：DROPDATABASE database_name
DROP{DATABASE | SCHEMA} db_name
5.修改数据库
ALTER{DATABASE | SCHEMA}
CHARACTER SET [=] charset_name
COLLATE [=] collation_name
二、表管理
1.创建表
createtable table_name (属性名 数据类型…)
2.查看表结构
(1)表定义信息
DESCRIBE table_name
DESC table_name
(2)表详细定义
SHOW CREATETABLE table_name
3.删除表
DROP TABLE table_name
4.修改表(ALTER TABLE)
(1)修改表名
ALTER TABLE老表名rename 新表名
(2)增加字段
最后：ALTHETABLE表名ADD属性名 属性类型
第一位置：ALTHE TABLE表名 ADD属性名 属性类型FIRST
指定字段后：ALTHETABLE表名ADD属性名 属性类型 AFTER 属性名
(3)删除字段
> (4)修改字段
修改数据类型：ALTER TABLE表名MODIFY属性名 属性类型
修改字段名：ALTERTABLE表名CHANGE旧属性名 新属性名 旧类型
同时修改：ALTERTABLE表名CHANGE 旧属性名 新属性名 新类型
修改字段顺序：ALTERTABLE 表名MODIFY 属性名1 数据类型 FIRSE|AFTER 属性名2
三、数据管理
1.插入数据
(1)INSERT INTOtable_name (field1….field n)VALUES(value1…value n)
(2) INSERT INTOtable_name (field1….field n)VALUES(value1…value n)…(value11…value mn)
(3)插入查询结果
INSERT INTOtable_name1(field11….field 1n)SELECT(field21….field 2n)FROM table_name2WHERE…
2.更新数据
UPDATE table_name SETfield1=value1…fieldn=value n WHERE CONDITION
3.删除记录
DELETE FROMtable_nameWHERE CONDITION
四、表的约束
1.设置非空约束(NOT NULL,NK)
CREATE TABLEtable_name (属性名 类型 NOT NULL )；
2.设置字段默认值(DEFAULT)
CREATE TABLEtable_name (属性名 类型 DEFAULT 默认值)；
3.设置唯一约束(UNIQUE,UK)
CREATE TABLEtable_name (属性名 类型 UNIQUE L )；
4.设置主键约束(PRIMARY KEY,PK)
单字段主键：CREATETABLEtable_name (属性名 类型 PRIMARY KEY )；
多字段主键：
CREATETABLEtable_name (属性名 类型 ,…【CONSTRAINT 约束名】 PRIMARY KEY（属性名，属性名…） )；
5.设置字段值自动增加(AUTO_INCREMENT)
CREATE TABLE table_name (属性名 类型 AUTO_INCREMENT,… )；
6.设置外键约束(FOREIGNKEY,FK)
CREATETABLE table_name (属性名 类型; 属性名 类型;… CONSTRAINT 外键约束名 FOREIGN （属性名1）REFERENCES 表名（属性名2）)；
五、索引管理
1.创建普通索引
(1) CREATE TABLE table_name (
属性名 数据类型，属性名 数据类型，……属性名 数据类型，
INDEX | KEY 【索引名】（属性名1【（长度）】【ASC|DESC】）);
注意：
1) INDEX 或KEY参数用来指定字段为索引
2) 不同存储引擎定义了表的最大索引数和最大索引长度
3) Mysql所支持的存储引擎对每个表至少支持16个索引，总索引长度至少256
(2) CREATE INDEX索引名 ON 表名（属性名1【（长度）】【ASC|DESC】）);
(3)ALTER TABLEtable_name ADD INDEX | KEY 索引名（属性名1【（长度）】【ASC|DESC】）
2.创建唯一索引
(1) CREATE TABLE table_name (
属性名 数据类型，属性名 数据类型，……属性名 数据类型，
UNIQUE INDEX | KEY【索引名】（属性名1【（长度）】【ASC|DESC】）);
(2) CREATE UNIQUEINDEX索引名 ON表名（属性名1【（长度）】【ASC|DESC】）);
(3)> 3.创建全文索引
(1) CREATE TABLE table_name (
属性名 数据类型，属性名 数据类型，……属性名 数据类型，
FULLTEXT INDEX |KEY 【索引名】（属性名1【（长度）】【ASC|DESC】）);
(2) CREATE FULLTEXTINDEX索引名 ON表名（属性名1【（长度）】【ASC|DESC】）);
(3)> 4.创建多列查询
(1) CREATE TABLE table_name (
属性名 数据类型，属性名 数据类型，……属性名 数据类型，
INDEX |KEY 【索引名】（属性名1【（长度）】【ASC|DESC】）
…….
属性名n【（长度）】【ASC|DESC】）);
(2) CREATE INDEX索引名
ON 表名（属性名1【（长度）】【ASC|DESC】）
…….
属性名n【（长度）】【ASC|DESC】）);
(3)ALTER TABLEtable_name
ADD INDEX | KEY 索引名（属性名1【（长度）】【ASC|DESC】）
…….
属性名n【（长度）】【ASC|DESC】）);
5.查询索引
EXPLAIN
6.删除索引
DROP INDEXindex_name ON table_name
六、视图管理
1.创建视图
CREATE VIEWview_name AS 查询语句
2.查看视图
(1)名称：USE 库； SHOW TABLES;不仅会显示表名,还会显示视图名
(2)详细信息：SHOW TABLESTATUS 【FROM 库】【LIKE ‘关键字’】
(3)设计信息：DESCRIBE|DESC view_name
(4)系统数据库：information_schema中视图信息的表格views
3.删除视图
DROP VIEWview_name1, view_name1……
4.修改视图
(1)替换的创建：create ORREPLACE view view_name AS 查询语句
(2)修改语句：ALTER VIEW view_name AS 查询语句
七、触发器
1.创建有一条语句的触发器
Create TRIGGERtrigger_name BEFORE|AFTER trigger_EVENTONtable_name FOR EACH ROWtrigger_STMT
trigger_EVENT：触发事件，即触发执行的条件（DELETE、INSERT、UPDATE）
FOR EACH ROW：任何一条记录上的操作满足触发事件都会触发该触发器
trigger_STMT：激活触发器后执行语句
2.创建包含多条执行语句的触发器
Create triggertrigger_name
BEFORE|AFTERtrigger_EVENT
ONtable_name FOR EACH ROW
BEGIN
trigger_STMT
END
3.查看触发器
SHOW TRIGGERS;
4.删除
DROP TRIGGER trigger_name;
八、存储过程
CREATE PROCEDURE procedure_name(])routine_body
注释：
1) procedure_parameter：存储过程的参数
procedure_nametype
2) characteristic：存储过程的特性
3) routine_body：SQL语句主体，可用BEGIN…END开始和结束
九、用户相关
1.命令行连接mysql
mysql –hHOST–uUSERNAME –pPASSWD –e “SQL”
-h：指明所需要连接的主机名称或者地址
-u：指明需要登录的用户名
-p：指明登录该数据库用户的密码
-e：不进入mysql交互界面指明SQL语句
注意：
1) mysql后接参数可以直接加上数值，无需空格
` 2) 直接加在-p 参数之后是明文密码，可以不填，在回车后填写不显示格式密码
3) -e参数非登录必须参数
4) 若匿名用户在本机登录该数据库，可以直接输入mysql后回车即可
2.创建普通用户账户，赋予权限
(1) 命令创建用户
mysql> CREATE USER username ‘password’]…
mysql>FLUSH PRIVILEGES;
关键字PASSWORD实现对密码加密
(2) 在mysql指定的表中插入用户信息数据
mysql>INSERT INTO user(host,user,password)VALUES(‘hostname’,’user’, PASSWORD (‘password’))；
mysql>FLUSHPRIVILEGES;
注意：
1) 由于该语句是向mysql_user插入用户账号数据，ssl_cipher、x509_issuer、x509_subject无默认值要设置这些字段
2) 对于Password字段的值一定要用PASSWORD()函数加密
3) 刷新授权信息后所创建用户才能生效
(3)GRANT创建账户可以对账户赋权限
mysql> GRANT priv_type ONdatabasename.tablename TO username@"host" ‘password’]…
mysql> FLUSHPRIVILEGES;
参数说明：
priv_type：表示权限信息，如select、all privileges… ...
权限级别：管理权限、数据库、表、字段、存储例程；
databasename.tablename:表示该用户只能对该表的权限
*.*：所有库的所有表；
db_name.*：指定库的所有表；
db_name.tbl_name：指定库的特定表；
db_name.routine_name：指定库上的存储过程或存储函数；
username：所需创建的用户，或者在对已存在的用户进行授权时存在的用户的用户名
host：允许其通过哪些主机远程创建连接；表示方式：IP，网络地址、主机名、通配符(%和_)；例如"172.16.%.%"
3.用户权限相关
(1)查看指定用户所获得的授权：
mysql>SHOW GRANTS FOR'user'@'host'
(2)回收权限
mysql>REVOKEpriv_type, ...ON db_name.tbl_nameFROM'user'@'host';
注意：
MariaDB服务进程启动时，会读取mysql库的所有授权表至内存中；
1)GRANT或REVOKE等执行的权限操作会保存于表中，MariaDB此时一般会自动重读授权表，权限修改会立即生效；
2)其它方式实现的权限修改，要想生效，必须手动运行FLUSH PRIVILEGES命令方可；
4.修改root密码
(1) mysqladmin命令修改
mysqladmin –u username –p password“new_password”
(2)Root用户对密码修改
mysql> SETPASSWORD=PASSWORD(“new_password”)
(3)更新mysql.user表数据
mysql>UPDATE user SET password=PASSWORD(“new_password”) WHERE user=”root” AND host=”hostname”;
5.利用超级用户更改普通用户密码
(1) GRANT priv_type ONdatabasename.tablenameTO username ‘new_password’]
(2) SET PASSWORD FOR‘username’@’hostname’=PASSWORD(“new_password”)
(3)UPDATE user SET password=PASSWORD(“new_password”) WHERE user=”” AND host=”hostname”;
(4)进入普通用户，再SET PASSWORD=PASSWORD(“new_password”)
6.数据库备份
mysql> Mysqldump -u username –pdbname> backupname.sql
mysql> Mysqldump -u username –pdbname1…dbname n>backupname.sql
mysql> Mysqldump -u username –p--all –databases>backupname.sql
7.安全加固
在安装完成后，运行mysql_secure_installation命令；会设置root用户密码，删除测试数据库，删除匿名用户等，以提高数据库的安全性
8.图形管理组件
phpMyAdmin(运行于lamp架构)、Navicat、Mysql-Front、ToadForMySQL、SQLyog
页:
[1]