SQL语句详解

数据库 jikk345 1129℃ 0评论

mysqllogo

一、数据库管理

1.创建数据库:create  Database  database_name;

CREATE  {DATABASE | SCHEMA}  [IF NOT EXISTS]  db_name;

[DEFAULT]  CHARACTER SET [=] charset_name

[DEFAULT]  COLLATE [=] collation_name

查看支持的所有字符集:SHOW CHARACTER SET

查看支持的所有排序规则:SHOW  COLLATION

注意:database_name命名规范

1)首字母不能是数字、$、空格、特殊字符、保留字

2)最大128字节

3)由字母、数字、下划线、@和$组成

2.查看数据库

SHOW  databases

3.选择数据库

USE  database_name

4.删除数据库:DROP  DATABASE  database_name

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

5.修改数据库

ALTER {DATABASE | SCHEMA}  [db_name]

[DEFAULT]  CHARACTER SET [=] charset_name

[DEFAULT]  COLLATE [=] collation_name

二、表管理

1.创建表

create  table 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)增加字段

最后:ALTHE TABLE  表名  ADD  属性名 属性类型

第一位置:ALTHE TABLE表名  ADD  属性名 属性类型  FIRST

指定字段后:ALTHE TABLE  表名ADD  属性名 属性类型   AFTER 属性名

(3)删除字段

ALTHE  TABLE  表名 drop 属性名

(4)修改字段

修改数据类型:ALTER  TABLE  表名  MODIFY  属性名 属性类型

修改字段名:ALTER  TABLE  表名  CHANGE  旧属性名 新属性名 旧类型

同时修改:ALTER  TABLE  表名  CHANGE 旧属性名 新属性名 新类型

修改字段顺序:ALTER  TABLE   表名  MODIFY 属性名1 数据类型 FIRSE|AFTER 属性名2

三、数据管理

1.插入数据

(1)INSERT INTO  table_name (field1….field n) VALUES(value1…value n)

(2) INSERT INTO  table_name (field1….field n)  VALUES(value1…value n)…(value11…value mn)

(3)插入查询结果

INSERT INTO  table_name1 (field11….field 1n)  SELECT  (field21….field 2n)  FROM table_name2  WHERE…

2.更新数据

UPDATE table_name SET field1=value1…fieldn=value n WHERE CONDITION

3.删除记录

DELETE  FROM  table_name  WHERE CONDITION

四、表的约束

1.设置非空约束(NOT NULL,NK)

CREATE  TABLE  table_name (属性名 类型 NOT NULL );

2.设置字段默认值(DEFAULT)

CREATE  TABLE  table_name (属性名 类型 DEFAULT 默认值);

3.设置唯一约束(UNIQUE,UK)

CREATE  TABLE  table_name (属性名 类型 UNIQUE L );

4.设置主键约束(PRIMARY KEY,PK)

单字段主键:CREATE  TABLE  table_name (属性名 类型 PRIMARY KEY );

多字段主键:

CREATE  TABLE  table_name (属性名 类型 ,…【CONSTRAINT 约束名】 PRIMARY KEY(属性名,属性名…) );

5.设置字段值自动增加(AUTO_INCREMENT)

CREATE  TABLE table_name (属性名 类型 AUTO_INCREMENT,… );

6.设置外键约束(FOREIGNKEY,FK)

CREATE  TABLE  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 TABLE  table_name  ADD INDEX | KEY 索引名(属性名1【(长度)】【ASC|DESC】)

2.创建唯一索引

(1) CREATE TABLE table_name (

属性名 数据类型,属性名 数据类型,……属性名 数据类型,

UNIQUE INDEX | KEY【索引名】(属性名1【(长度)】【ASC|DESC】));

(2) CREATE UNIQUE INDEX  索引名 ON表名(属性名1【(长度)】【ASC|DESC】));

(3) ALTER TABLE  table_name  ADD UNIQUE INDEX | KEY 索引名(属性名1【(长度)】【ASC|DESC】)

3.创建全文索引

(1) CREATE TABLE table_name (

属性名 数据类型,属性名 数据类型,……属性名 数据类型,

FULLTEXT INDEX | KEY 【索引名】(属性名1【(长度)】【ASC|DESC】));

(2) CREATE FULLTEXT INDEX  索引名 ON表名(属性名1【(长度)】【ASC|DESC】));

(3) ALTER TABLE  table_name   ADD FULLTEXT INDEX | KEY 索引名(属性名1【(长度)】【ASC|DESC】)

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 TABLE  table_name

ADD INDEX | KEY 索引名(属性名1【(长度)】【ASC|DESC】)

…….

属性名n【(长度)】【ASC|DESC】));

5.查询索引

EXPLAIN

6.删除索引

DROP  INDEX  index_name ON table_name

六、视图管理

1.创建视图

CREATE VIEW view_name AS 查询语句

2.查看视图

(1)名称:USE 库; SHOW TABLES;不仅会显示表名,还会显示视图名

(2)详细信息:SHOW TABLE  STATUS 【FROM 库】【LIKE ‘关键字’】

(3)设计信息:DESCRIBE|DESC view_name

(4)系统数据库:information_schema中视图信息的表格views

3.删除视图

DROP VIEW view_name1, view_name1……

4.修改视图

(1)替换的创建:create OR REPLACE view view_name AS 查询语句

(2)修改语句:ALTER VIEW view_name AS 查询语句

七、触发器

1.创建有一条语句的触发器

Create  TRIGGER  trigger_name   BEFORE|AFTER  trigger_EVENT  ON  table_name  FOR EACH ROW  trigger_STMT

trigger_EVENT:触发事件,即触发执行的条件(DELETE、INSERT、UPDATE)

FOR EACH ROW:任何一条记录上的操作满足触发事件都会触发该触发器

trigger_STMT:激活触发器后执行语句

2.创建包含多条执行语句的触发器

Create  trigger  trigger_name

BEFORE|AFTER  trigger_EVENT

ON  table_name  FOR EACH ROW

BEGIN

trigger_STMT

END

3.查看触发器

SHOW TRIGGERS;

4.删除

DROP TRIGGER   trigger_name;

八、存储过程

CREATE  PROCEDURE procedure_name([procedure_parameter[,…]]) [characteristic…]  routine_body

注释:

1) procedure_parameter:存储过程的参数

[IN|OUT|INOUT] procedure_name type

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 [IDENTIFIED BY [PASSWORD] ‘password’]…

mysql>  FLUSH  PRIVILEGES;

关键字PASSWORD实现对密码加密

(2) 在mysql指定的表中插入用户信息数据

mysql>  INSERT INTO user(host,user,password) VALUES(‘hostname’,’user’, PASSWORD (‘password’));

mysql>  FLUSH  PRIVILEGES;

注意:

1) 由于该语句是向mysql_user插入用户账号数据,ssl_cipher、x509_issuer、x509_subject无默认值要设置这些字段

2) 对于Password字段的值一定要用PASSWORD()函数加密

3) 刷新授权信息后所创建用户才能生效

(3)GRANT创建账户可以对账户赋权限

mysql> GRANT priv_type ON databasename.tablename TO username@”host” [IDENTIFIED BY [PASSWORD] ‘password’]…

mysql> FLUSH  PRIVILEGES;

参数说明:

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>  REVOKE  priv_type, …  ON  db_name.tbl_name  FROM  ‘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>SET PASSWORD=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 ON databasename.tablename  TO username [IDENTIFIED BY [PASSWORD] ‘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 –p  dbname  [table1…table n]  >  backupname.sql

mysql>    Mysqldump  -u username –p  dbname1…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

转载请注明:清麟博客 » SQL语句详解

喜欢 (1)
发表我的评论
取消评论

表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址