MySQL语句

SQL语句：

DDL、DML、DCL

               DDL: CREATE、DROP、ALTER

               DML: INSERT(REPLACE如果有就替换)、DELETE、UPDATE、SELECT

               DCL：GRANT, REVOKE

 

DDL: Data Defination Language  //数据定义语言，即创建或删除数据库对象，用于定义和管理 SQL 数据库中的所有对象的语言

1.CREATE - to create objects in the database 创建

2.ALTER - alters the structure of the database 修改

3.DROP - delete objects from the database 删除

4.TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed

TRUNCATE TABLE [Table Name]。

下面是对Truncate语句在MSSQLServer2000中用法和原理的说明：

Truncate table 表名 速度快,而且效率高,因为:

TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。

DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。

TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。如果要删除表定义及其数据，请使用 DROP TABLE 语句。

对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。

TRUNCATE TABLE 不能用于参与了索引视图的表。

5.COMMENT - add comments to the data dictionary 注释

6.GRANT - gives user's access privileges to database 授权

7.REVOKE - withdraw access privileges given with the GRANT command 收回已经授予的权限

 

DML: Data Manipulation Language  //数据操作语言，SQL中处理数据等操作统称为数据操纵语言

INSERT, DELETE, UPDATE, SELECT向表中插入、删除、修改、查询数据

1.SELECT - retrieve data from the a database 查询

2.INSERT - insert data into a table 添加

3.UPDATE - updates existing data within a table 更新

4.DELETE - deletes all records from a table, the space for the records remain 删除

5.CALL - call a PL/SQL or Java subprogram

6.EXPLAIN PLAN - explain access path to data

Oracle RDBMS执行每一条SQL语句，都必须经过Oracle优化器的评估。所以，了解优化器是如何选择(搜索)路径以及索引是如何被使用的，对优化SQL语句有很大的帮助。Explain可以用来迅速方便地查出对于给定SQL语句中的查询数据是如何得到的即搜索路径(我们通常称为Access Path)。从而使我们选择最优的查询方式达到最大的优化效果。

7.LOCK TABLE - control concurrency 锁，用于控制并发

 

 DCL: Data Control Language   //数据库控制语言，用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等

GRANT授权, REVOKE撤销

Grant语句：允许对象的创建者给某用户或某组或所有用户(PUBLIC)某些特定的权限。

Revoke语句：可以废除某用户或某组或所有用户访问权限

 

 在MySQL或者mariadb客户端命令行中可执行的命令有两类：

1、客户端命令，不需要分号，只能在客户端本地执行;

2、服务器端命令，需要加语句结束符：分号(;)

 

数据库：

创建数据库：

CREATE DATABASE|SCHEMA [IF NOT EXISTS] 'DB_NAME'; 

CHARACTER SET [=] charset   //定义字符串基于什么格式存储

                        COLLATE [=] collate  //指明排序规则

删除数据库：

DROP DATABASE|SCHEMA [IF EXISTS] 'DB_NAME'; 

字符集：

CHARACTER SET 'charecter set name'  

排序规则：

COLLATE 'collate name'  

查看支持所有字符集：

SHOW CHARACTER SET;

        查看支持所有排序规则：

SHOW COLLATIONS;

 

获取命令使用帮助：

mysql> HELP KEYWORD;

 

获取帮助：

                mysql> HELP CREATE TABLE;   查看支持哪些数据类型

查看创建命令：

                         SHOW CREATE TABLE tbl_name;

> show create table students;

查看表状态：

SHOW TABLE STATUS LIKE 'tbl_name'\G   //  \G 表示可以让表中的每一行数据竖状显示

> show table status like 'students'\G

查看数据库：

mysql> SHOW DATABASES;

 

修饰符：

      所有类型适用：

               NOT NULL     非空

               DEFAULT

               PRIMARY KEY   主键

               UNIQUE KEY    唯一键

 

      数值型适用：

               UNSIGNED   无符号，不能表示负数

               AUTO_INCREMENT   自动增长，修饰正数

 

表：

创建表：

CREATE TABLE [IF NOT EXISTS] 'tbl_name' (col1 type1, col2 type2, ...) 

col type1 字段名称、字段类型

                PRIMARY KEY(col1,...)   把一个字段或多个字段定义主键

                INDEX(col1, ...)  给字段添加索引

                UNIQUE KEY(col1, ...)  为字段定义唯一键

表选项：

ENGINE [=] engine_name  定义表所使用的存储引擎的

ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}  指明行格式

        SHOW ENGINES;  列出mariadb所支持的存储引擎

 

CREATE TABLE [IF NOT EXISTS] tb_name (col1 datatype 修饰符, col2 datatype 修饰符) ENGINE=''

> create database if not exists testdb;  

> use testdb;

> create table students (id int unsigned not null primary key, name varchar(20) not null, age tinyint unsigned);

或者
> create table students2 (id int unsigned not null, name varchar(20) not null, age tinyint unsigned, primary key (id,name));  把id和name同时定义为主键

> desc students;  

> desc students2;  

 

> create table tbl1 (id int not null, name varchar(100) not null, age tinyint);

> show databases;

 

> show tables from testdb;   //查看testdb库中的表

 

> desc tbl1;  // 查看表结构

 > drop table if exists tbl1;  //删除是不可逆的

查看所有的引擎：

SHOW ENGINES;

查看表：

SHOW TABLES [FROM db_name]

> show tables from testdb; 

查看表结构：

DESC [db_name.]tb_name

> desc tbl1;

修改表：

ALTER TABLE 'tbl_name' 

删除表：

DROP TABLE [IF EXISTS] tb_name；

 > drop table if exists tbl1;

 

修改表：三种：字段、索引、表选项

ALTER TABLE 'tbl_name'     

 

1、字段

 添加字段：add

ADD col1 data_type [FIRST|AFTER col_name]

> alter table students add gender enum('m','f') after name;

> alter table students add unique key(name);  添加name为唯一键

> alter table students add index(age);  //添加索引

> show indexes from students;  //查看索引

 删除字段：drop

修改字段：alter, change, modify

Alter 改字段定义的两个属性，设置默认值或移除默认值等属性

        Change 把一个字段从一个老名称改为新名字，改字名称和字段定义

        Modify 把一个column_definition从字段的老定义改为新定义，或改字段排序

> alter table students change id sid int unsigned not null;   //primary key定义过了，不用再次定义

 

2、索引

索引是特殊数据结构；定义在查找时作为查找条件的字段；要有索引名称；

查看索引帮助： >help create index; 

创建索引：CREATE INDEX index_name ON tbl_name (index_col_name,...);

查看索引：SHOW INDEXES FROM [db_name.]tbl_name;

删除索引：DROP INDEX index_name ON tbl_name;

> drop index name on students;

 

3、表选项

修改：

 

DML:INSERT, DELETE, SELECT, UPDATE

INSERT INTO：

INSERT [INTO] tbl_name [(col_name,...)] {VALUES | VALUE} (val1,...),(...),...

SELECT:

SELECT col1,col2,... FROM tbl_name [WHERE clause] [ORDER BY 'col_name' [DESC]] [LIMIT [m,]n];  //[DESC]表示降序，默认为升序

DELETE: 

DELETE FROM tbl_name [WHERE clause] [ORDER BY 'col_name' [DESC]] [LIMIT [m,]n];   //[m,]n]表示偏移m个位置

DELETE FROM tbl_name；  //特别注意：如果使用这段命令，会清空整张表的

UPDATE:

UPDATE tbl_name SET col1=new_val1, col2=new_val2, ... [WHERE clause] [ORDER BY 'col_name' [DESC]] [LIMIT [m,]n];

UPDATE tbl_name SET col1=new_val1, col2=new_val2, ...  如果后面没有跟条件，表中所有字段将会被改掉

 

SELECT:

> select * from students where sid<3;

> select * from students where gender='m';

> select * from students where gender is null;  //一般不用

> select * from students order by name;

> select * from students order by name desc limit 2;  //限制降序后显示前两个

> select * from students order by name desc limit 1,2;   //偏过去一个，即

 

INSERT INTO：

> insert into students values (1,'liming','m',18), (2,'xiaohong','f',12);

> select * from students;

 

>desc students;

> insert into students (sid,name) values (3,'lixiang'),(4,'lingling');  //上述可以留空

> select * from students;

 

DELETE: 

> delete from students where sid=3;  //删除sid=3的

 

UPDATE:

> update students set gender='f' where sid=4;   //修改gender=4的用户性别为f

 

字段表示法：

 *:  所有字段；

 as：字段别名，col1 AS alias1;  给col1去别名为alias1

> select sid as stuid, name as stuname from students;

 

WHERE clase:

操作符：

>, <, >=, <=, ==, !=

> select *from students where sid>=2 and sid<=4;

简单写法

> select *from students where sid between 2 and 4;

BETWEEN ... AND ...

LIKE:  //模糊匹配

%：任意长度的任意字符；

> select * from students where name like 'x%';

_：任意单个字符；

RLIKE：regular expression

> select * from students where name rlike '.*a.*';

正则表达式模式匹配；（字符串匹配）

        IS NULL    

IS NOT NULL

 

条件逻辑操作：and  or  not