2.1 SQL分类
- DDL（DataDefinitionLanguages）语句：数据定义语言，这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象。常用的语句关键字主要包括create、drop、alter等。
- DML（DataManipulationLanguage）语句：数据操纵语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性。常用的语句关键字主要包括insert、delete、update和select等。
- DCL（DataControlLanguage）语句：数据控制语句，用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要的语句关键字包括grant、revoke等。
2.2 DDL
简单来说，就是对数据库内部的对象进行创建、删除、修改等操作的语言。
2.2.1 连接数据库
(base) xubinbin:~ xubinbin$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.19 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
- mysql:客户端命令
- -u: 数据库用户名选项
- root: 数据库用户名
- -p:表求需要输入密码
2.2.2 创建数据库
- 语法: create database dbname;
示例:
mysql> create database test1;
Query OK, 1 row affected (0.00 sec)
2.2.3 查看存在哪些数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| max_compute |
| mysql |
| renren_fast |
| test1 |
| tutorial |
| yunlang |
+--------------------+
7 rows in set (0.01 sec)
- information_schema：主要存储了系统中的一些数据库对象信息，比如用户表信息、列信息、权限信息、字符集信息、分区信息等。
- mysql：存储了系统的用户权限信息。
- 其它：自建数据库，任何用户都可以使用。
2.2.4 选择数据库
mysql> use test1;
Database changed
2.2.5 查看数据库中有哪些表
mysql> show tables;
Empty set (0.00 sec)
由于没有在数据库test1中创建表，所以没有数据。切换到系统数据mysql，再查看表信息。
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| engine_cost |
| event |
| func |
| general_log |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
31 rows in set (0.00 sec)
2.2.6 删除数据库
- 语法: drop database dbname;
示例:
mysql> drop database test1;
Query OK, 0 rows affected (0.01 sec)
2.2.7 创建表
- 语法:
create table tablename(
column_name_1column_type_1constraints,
column_name_2column_type_2constraints,
...
column_name_ncolumn_type_nconstraints
）;
示例:
create table emp (
ename varchar(10),
hiredate date,
sal decimal(10,2),
deptno int(2)
);
查看表结构:
mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename | varchar(10) | YES | | NULL | |
| hiredate | date | YES | | NULL | |
| sal | decimal(10,2) | YES | | NULL | |
| deptno | int(2) | YES | | NULL | |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
查看建表语句:
mysql> show create table emp;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| emp | CREATE TABLE `emp` (
`ename` varchar(10) DEFAULT NULL,
`hiredate` date DEFAULT NULL,
`sal` decimal(10,2) DEFAULT NULL,
`deptno` int(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
2.2.8 删除表
- 语法: drop table tablename;
示例:
mysql> drop table emp;
Query OK, 0 rows affected (0.01 sec)
2.2.9 修改表
- 修改表类型
语法:
alter table tablename modify [column ] column_definition [first|after col_name]
示例:
mysql> alter table emp modify ename varchar(20);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
- 增加表字段
语法如下：
alter table tablename add [column] column_definition [first|after col_name]
示例:
mysql> alter table emp add column age int(3);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
- 删除表字段
语法：
alter table tablename drop [column] col_name
示例:
mysql> alter table emp drop column age;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
- 字段改名
语法：
alter table tablename change [column] old_col_name column_definition [first|after col_name]
示例:
mysql> alter table emp change age age1 int(4);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
- 更改表名
语法：
alter table tablename rename [to] new_tablename
示例:
mysql> alter table emp rename emp1;
Query OK, 0 rows affected (0.00 sec)
2.3 DML
DML操作是指对数据库中表记录的操作，主要包括表记录的插入（insert）、更新（update）、删除（delete）和查询（select），是开发人员日常使用最频繁的操作
2.3.1 插入记录
语法:
insert into tablename (field1,field2,…,fieldn) values (value1,value2,…,valuen);
示例:
mysql> insert into emp(ename,hiredate,sal,deptno) values('zzx1','20000101','2000',1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into emp(ename,hiredate,sal,deptno) values('zzx1','20000101','2000',1), ('zzx1','20000101','2000',1);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
2.3.2 更新记录
语法：
update tablename set field1=value1,field2=value2,…,fieldn=valuen [where condition]
示例:
mysql> update emp set sal=4000 where ename='lisa';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
2.3.3 删除记录
语法:
delete from tablename [where condition]
示例:
mysql> delete from emp where ename='dony';
Query OK, 0 rows affected (0.00 sec)
2.3.4 查询记录
语法:
select * from tablename [where condition]
示例:
mysql> select * from emp;
+-------+------------+---------+--------+------+
| ename | hiredate | sal | deptno | age1 |
+-------+------------+---------+--------+------+
| zzx1 | 2000-01-01 | 2000.00 | 1 | NULL |
| zzx1 | 2000-01-01 | 2000.00 | 1 | NULL |
| zzx1 | 2000-01-01 | 2000.00 | 1 | NULL |
+-------+------------+---------+--------+------+
3 rows in set (0.00 sec)
mysql> select ename,hiredate from emp;
+-------+------------+
| ename | hiredate |
+-------+------------+
| zzx1 | 2000-01-01 |
| zzx1 | 2000-01-01 |
| zzx1 | 2000-01-01 |
+-------+------------+
3 rows in set (0.00 sec)
2.3.5 排序和限制。
语法:
select * from tablename [where condition] [order by field1 [desc|asc]，field2[desc|asc],…,fieldn[desc|asc]]
示例:
mysql> select * from emp order by sal;
+-------+------------+---------+--------+------+
| ename | hiredate | sal | deptno | age1 |
+-------+------------+---------+--------+------+
| zzx1 | 2000-01-01 | 2000.00 | 1 | NULL |
| zzx1 | 2000-01-01 | 2000.00 | 1 | NULL |
| zzx1 | 2000-01-01 | 2000.00 | 1 | NULL |
+-------+------------+---------+--------+------+
3 rows in set (0.00 sec)
mysql> select * from emp order by sal asc;
+-------+------------+---------+--------+------+
| ename | hiredate | sal | deptno | age1 |
+-------+------------+---------+--------+------+
| zzx1 | 2000-01-01 | 2000.00 | 1 | NULL |
| zzx1 | 2000-01-01 | 2000.00 | 1 | NULL |
| zzx1 | 2000-01-01 | 2000.00 | 1 | NULL |
+-------+------------+---------+--------+------+
3 rows in set (0.00 sec)
mysql> select * from emp order by sal asc,deptno desc;
+-------+------------+---------+--------+------+
| ename | hiredate | sal | deptno | age1 |
+-------+------------+---------+--------+------+
| zzx1 | 2000-01-01 | 2000.00 | 1 | NULL |
| zzx1 | 2000-01-01 | 2000.00 | 1 | NULL |
| zzx1 | 2000-01-01 | 2000.00 | 1 | NULL |
+-------+------------+---------+--------+------+
3 rows in set (0.00 sec)
2.3.6限制返回记录
语法:
select … [limit offset_start,row_count]
其中offset_start表示记录的起始偏移量，row_count表示显示的行数。
示例:
mysql> select * from emp order by sal limit 2;
+-------+------------+---------+--------+------+
| ename | hiredate | sal | deptno | age1 |
+-------+------------+---------+--------+------+
| zzx1 | 2000-01-01 | 2000.00 | 1 | NULL |
| zzx1 | 2000-01-01 | 2000.00 | 1 | NULL |
+-------+------------+---------+--------+------+
2 rows in set (0.00 sec)
mysql> select * from emp order by sal limit 1,2;
+-------+------------+---------+--------+------+
| ename | hiredate | sal | deptno | age1 |
+-------+------------+---------+--------+------+
| zzx1 | 2000-01-01 | 2000.00 | 1 | NULL |
| zzx1 | 2000-01-01 | 2000.00 | 1 | NULL |
+-------+------------+---------+--------+------+
2 rows in set (0.00 sec)
2.3.7 聚合
语法:
select [field1,field2,…,fieldn] fun_name
from tablename
[where where_contition]
[group by field1,field2,…,fieldn[with rollup]]
[having where_contition]
示例:
mysql> select deptno,count(1) from emp group by deptno;
+--------+----------+
| deptno | count(1) |
+--------+----------+
| 1 | 3 |
+--------+----------+
1 row in set (0.00 sec)
mysql> select deptno,count(1) from emp group by deptno having count(1)>1;
+--------+----------+
| deptno | count(1) |
+--------+----------+
| 1 | 3 |
+--------+----------+
1 row in set (0.01 sec)
2.4 DCL语句
DCL语句主要是DBA用来管理系统中的对象权限时使用，一般的开发人员很少使用。下面通过一个例子简单说明一下。
创建一个数据库用户z1，具有对test1数据库中所有表的SELECT/INSERT权限：
mysql> grant select,insert on test1.* to 'z1' @'localhost' identified by '123';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> exit
Bye
(base) xubinbin:~ xubinbin$ mysql -u z1 -p
Enter password:
mysql> use test1;
mysql> insert into emp(ename,hiredate,sal,deptno) values('zzx1','20000101','2000',1);
Query OK, 1 row affected (0.00 sec)
mysql> update emp set ename='test1' where deptno=1;
ERROR 1142 (42000): UPDATE command denied to user 'z1'@'localhost' for table 'emp'
2.5 帮助文档
- 查看帮助能提供什么:
mysql> ? contents
You asked for help about help category: "Contents"
For more information, type 'help <item>', where <item> is one of the following
categories:
Account Management
Administration
Compound Statements
Data Definition
Data Manipulation
Data Types
Functions
Functions and Modifiers for Use with GROUP BY
Geographic Features
Help Metadata
Language Structure
Plugins
Procedures
Storage Engines
Table Maintenance
Transactions
User-Defined Functions
Utility
- 查看mysql支持哪些数据类型
mysql> ? Data Types
You asked for help about help category: "Data Types"
For more information, type 'help <item>', where <item> is one of the following
topics:
AUTO_INCREMENT
BIGINT
BINARY
BIT
BLOB
BLOB DATA TYPE
BOOLEAN
CHAR
CHAR BYTE
DATE
DATETIME
DEC
DECIMAL
DOUBLE
DOUBLE PRECISION
ENUM
FLOAT
INT
INTEGER
LONGBLOB
LONGTEXT
MEDIUMBLOB
MEDIUMINT
MEDIUMTEXT
SET DATA TYPE
SMALLINT
TEXT
TIME
TIMESTAMP
TINYBLOB
TINYINT
TINYTEXT
VARBINARY
VARCHAR
YEAR DATA TYPE
- 查看int类型具体介绍
mysql> ? int
Name: 'INT'
Description:
INT[(M)] [UNSIGNED] [ZEROFILL]
A normal-size integer. The signed range is -2147483648 to 2147483647.
The unsigned range is 0 to 4294967295.
URL: http://dev.mysql.com/doc/refman/5.7/en/numeric-type-overview.html
- 查看show命令能看些什么:
mysql> ? show
Name: 'SHOW'
Description:
SHOW has many forms that provide information about databases, tables,
columns, or status information about the server. This section describes
those following:
SHOW {BINARY | MASTER} LOGS
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
SHOW CHARACTER SET [like_or_where]
SHOW COLLATION [like_or_where]
SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where]
SHOW CREATE DATABASE db_name
SHOW CREATE EVENT event_name
SHOW CREATE FUNCTION func_name
SHOW CREATE PROCEDURE proc_name
SHOW CREATE TABLE tbl_name
SHOW CREATE TRIGGER trigger_name
SHOW CREATE VIEW view_name
SHOW DATABASES [like_or_where]
SHOW ENGINE engine_name {STATUS | MUTEX}
SHOW [STORAGE] ENGINES
SHOW ERRORS [LIMIT [offset,] row_count]
SHOW EVENTS
SHOW FUNCTION CODE func_name
SHOW FUNCTION STATUS [like_or_where]
SHOW GRANTS FOR user
SHOW INDEX FROM tbl_name [FROM db_name]
SHOW MASTER STATUS
SHOW OPEN TABLES [FROM db_name] [like_or_where]
SHOW PLUGINS
SHOW PROCEDURE CODE proc_name
SHOW PROCEDURE STATUS [like_or_where]
SHOW PRIVILEGES
SHOW [FULL] PROCESSLIST
SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n]
SHOW PROFILES
SHOW RELAYLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
SHOW SLAVE HOSTS
SHOW SLAVE STATUS [FOR CHANNEL channel]
SHOW [GLOBAL | SESSION] STATUS [like_or_where]
SHOW TABLE STATUS [FROM db_name] [like_or_where]
SHOW [FULL] TABLES [FROM db_name] [like_or_where]
SHOW TRIGGERS [FROM db_name] [like_or_where]
SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
SHOW WARNINGS [LIMIT [offset,] row_count]
like_or_where:
LIKE 'pattern'
| WHERE expr
If the syntax for a given SHOW statement includes a LIKE 'pattern'
part, 'pattern' is a string that can contain the SQL % and _ wildcard
characters. The pattern is useful for restricting statement output to
matching values.
Several SHOW statements also accept a WHERE clause that provides more
flexibility in specifying which rows to display. See
http://dev.mysql.com/doc/refman/5.7/en/extended-show.html.
URL: http://dev.mysql.com/doc/refman/5.7/en/show.html
- 查看create table语法
mysql> ? create table
Name: 'CREATE TABLE'
Description:
Syntax:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_options]
[partition_options]
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
[table_options]
[partition_options]
[IGNORE | REPLACE]
[AS] query_expression
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
{ LIKE old_tbl_name | (LIKE old_tbl_name) }
create_definition:
col_name column_definition
| [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
[index_option] ...
| {INDEX|KEY} [index_name] [index_type] (index_col_name,...)
[index_option] ...
| [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]
[index_name] [index_type] (index_col_name,...)
[index_option] ...
| {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...)
[index_option] ...
| [CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name,...) reference_definition
| CHECK (expr)
column_definition:
data_type [NOT NULL | NULL] [DEFAULT default_value]
[AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
[COMMENT 'string']
[COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
[STORAGE {DISK|MEMORY|DEFAULT}]
[reference_definition]
| data_type [GENERATED ALWAYS] AS (expression)
[VIRTUAL | STORED] [UNIQUE [KEY]] [COMMENT comment]
[NOT NULL | NULL] [[PRIMARY] KEY]
data_type:
BIT[(length)]
| TINYINT[(length)] [UNSIGNED] [ZEROFILL]
| SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
| MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
| INT[(length)] [UNSIGNED] [ZEROFILL]
| INTEGER[(length)] [UNSIGNED] [ZEROFILL]
| BIGINT[(length)] [UNSIGNED] [ZEROFILL]
| REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
| DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
| FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
| DECIMAL[(length[,decimals])] [UNSIGNED] [ZEROFILL]
| NUMERIC[(length[,decimals])] [UNSIGNED] [ZEROFILL]
| DATE
| TIME[(fsp)]
| TIMESTAMP[(fsp)]
| DATETIME[(fsp)]
| YEAR
| CHAR[(length)] [BINARY]
[CHARACTER SET charset_name] [COLLATE collation_name]
| VARCHAR(length) [BINARY]
[CHARACTER SET charset_name] [COLLATE collation_name]
| BINARY[(length)]
| VARBINARY(length)
| TINYBLOB
| BLOB
| MEDIUMBLOB
| LONGBLOB
| TINYTEXT [BINARY]
[CHARACTER SET charset_name] [COLLATE collation_name]
| TEXT [BINARY]
[CHARACTER SET charset_name] [COLLATE collation_name]
| MEDIUMTEXT [BINARY]
[CHARACTER SET charset_name] [COLLATE collation_name]
| LONGTEXT [BINARY]
[CHARACTER SET charset_name] [COLLATE collation_name]
| ENUM(value1,value2,value3,...)
[CHARACTER SET charset_name] [COLLATE collation_name]
| SET(value1,value2,value3,...)
[CHARACTER SET charset_name] [COLLATE collation_name]
| JSON
| spatial_type
index_col_name:
col_name [(length)] [ASC | DESC]
index_type:
USING {BTREE | HASH}
index_option:
KEY_BLOCK_SIZE [=] value
| index_type
| WITH PARSER parser_name
| COMMENT 'string'
reference_definition:
REFERENCES tbl_name (index_col_name,...)
[MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
table_options:
table_option [[,] table_option] ...
table_option:
ENGINE [=] engine_name
| AUTO_INCREMENT [=] value
| AVG_ROW_LENGTH [=] value
| [DEFAULT] CHARACTER SET [=] charset_name
| CHECKSUM [=] {0 | 1}
| [DEFAULT] COLLATE [=] collation_name
| COMMENT [=] 'string'
| COMPRESSION [=] {'ZLIB'|'LZ4'|'NONE'}
| CONNECTION [=] 'connect_string'
| DATA DIRECTORY [=] 'absolute path to directory'
| DELAY_KEY_WRITE [=] {0 | 1}
| ENCRYPTION [=] {'Y' | 'N'}
| INDEX DIRECTORY [=] 'absolute path to directory'
| INSERT_METHOD [=] { NO | FIRST | LAST }
| KEY_BLOCK_SIZE [=] value
| MAX_ROWS [=] value
| MIN_ROWS [=] value
| PACK_KEYS [=] {0 | 1 | DEFAULT}
| PASSWORD [=] 'string'
| ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
| STATS_AUTO_RECALC [=] {DEFAULT|0|1}
| STATS_PERSISTENT [=] {DEFAULT|0|1}
| STATS_SAMPLE_PAGES [=] value
| TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}]
| UNION [=] (tbl_name[,tbl_name]...)
partition_options:
PARTITION BY
{ [LINEAR] HASH(expr)
| [LINEAR] KEY [ALGORITHM={1|2}] (column_list)
| RANGE{(expr) | COLUMNS(column_list)}
| LIST{(expr) | COLUMNS(column_list)} }
[PARTITIONS num]
[SUBPARTITION BY
{ [LINEAR] HASH(expr)
| [LINEAR] KEY [ALGORITHM={1|2}] (column_list) }
[SUBPARTITIONS num]
]
[(partition_definition [, partition_definition] ...)]
partition_definition:
PARTITION partition_name
[VALUES
{LESS THAN {(expr | value_list) | MAXVALUE}
|
IN (value_list)}]
[[STORAGE] ENGINE [=] engine_name]
[COMMENT [=] 'comment_text' ]
[DATA DIRECTORY [=] 'data_dir']
[INDEX DIRECTORY [=] 'index_dir']
[MAX_ROWS [=] max_number_of_rows]
[MIN_ROWS [=] min_number_of_rows]
[TABLESPACE [=] tablespace_name]
[(subpartition_definition [, subpartition_definition] ...)]
subpartition_definition:
SUBPARTITION logical_name
[[STORAGE] ENGINE [=] engine_name]
[COMMENT [=] 'comment_text' ]
[DATA DIRECTORY [=] 'data_dir']
[INDEX DIRECTORY [=] 'index_dir']
[MAX_ROWS [=] max_number_of_rows]
[MIN_ROWS [=] min_number_of_rows]
[TABLESPACE [=] tablespace_name]
query_expression:
SELECT ... (Some valid select or union statement)
CREATE TABLE creates a table with the given name. You must have the
CREATE privilege for the table.
By default, tables are created in the default database, using the
InnoDB storage engine. An error occurs if the table exists, if there is
no default database, or if the database does not exist.
For information about the physical representation of a table, see
http://dev.mysql.com/doc/refman/5.7/en/create-table-files.html.
URL: http://dev.mysql.com/doc/refman/5.7/en/create-table.html
