【mysql基础】08、常用的SQL语句
一、数据库及表的管理1、数据库
创建：
CREATE {DATABASE | SCHEMA} db_name;
CREATE {DATABASE | SCHEMA} db_name
[ CHARACTER SET [=] charset_name
| COLLATE [=] collation_name];
删除:
DROP {DATABASE | SCHEMA} db_name；
修改：
修改数据库的字符集和排序字符以及数据字典
>
[ CHARACTER SET [=] charset_name
| COLLATE [=] collation_name]
升级某数据库的数据字典：
ALTER {DATABASE | SCHEMA} db_name UPGRADE DATA DIRECTORY NAME；
不能修改数据库名：
查：
SHOW DATABASES;
### 增
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
MariaDB [(none)]> CREATE DATABASE mydb;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> CREATE schema mydb1;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mydb1 |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.00 sec)
MariaDB [(none)]> CREATE DATABASE mydb;
ERROR 1007 (HY000): Can't create database 'mydb'; database exists
MariaDB [(none)]> CREATE DATABASE IF NOT EXISTS mydb;
Query OK, 0 rows affected, 1 warning (0.00 sec)
MariaDB [(none)]> CREATE DATABASE IF NOT EXISTS mydb2;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mydb1 |
| mydb2 |
| mysql |
| performance_schema |
| test |
+--------------------+
7 rows in set (0.00 sec)
MariaDB [(none)]>
### 删
MariaDB [(none)]> DROP DATABASE mydb2;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> DROP SCHEMA mydb1;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> DROP SCHEMA mydb1;
ERROR 1008 (HY000): Can't drop database 'mydb1'; database doesn't exist
MariaDB [(none)]> DROP SCHEMA IF EXISTS mydb1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
MariaDB [(none)]> DROP SCHEMA IF EXISTS mydb;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
MariaDB > STATUS
--------------
mysqlVer 15.1 Distrib 10.1.21-MariaDB, for Linux (x86_64) using readline 5.1
Connection id:50
Current database:mydb
Current user:root@localhost
SSL:Not in use
Current pager:stdout
Using outfile:''
Using delimiter:;
Server:MariaDB
Server version:10.1.21-MariaDB Source distribution
Protocol version:10
Connection:Localhost via UNIX socket
Server characterset:utf8
Db characterset:utf8
Client characterset:utf8
Conn.characterset:utf8
UNIX socket:/tmp/mysql.sock
Uptime:23 hours 53 min 45 sec
Threads: 1Questions: 174Slow queries: 0Opens: 22Flush tables: 1Open tables: 16Queries per second avg: 0.002
--------------
MariaDB > ALTER DATABASE mydb CHARACTER SET "gbk" COLLATE "gbk_chinese_ci";
Query OK, 1 row affected (0.00 sec)
MariaDB > STATUS
--------------
mysqlVer 15.1 Distrib 10.1.21-MariaDB, for Linux (x86_64) using readline 5.1
Connection id:50
Current database:mydb
Current user:root@localhost
SSL:Not in use
Current pager:stdout
Using outfile:''
Using delimiter:;
Server:MariaDB
Server version:10.1.21-MariaDB Source distribution
Protocol version:10
Connection:Localhost via UNIX socket
Server characterset:utf8
Db characterset:gbk
Client characterset:utf8
Conn.characterset:utf8
UNIX socket:/tmp/mysql.sock
Uptime:23 hours 58 min 1 sec
Threads: 1Questions: 180Slow queries: 0Opens: 22Flush tables: 1Open tables: 16Queries per second avg: 0.002
2、表
创建：
CREATE （临时表，保存在内存中）] TABLE tbl_name
(create_definition,...) # 创建定义
#表选项
# 分区选项 # 忽略，暂时用不到
(create_definition,...)：创建定义包含：
字段的定义：字段名、类型和类型修饰符
键、索引和约束：
primary key，unique key，foreign key，check
{index|key}
注意：键本身就产生约束，键也可以拿来当索引用，但索引、约束未必是键，
ENGINE [=] engine_name
AUTO_INCREMENT [=] value 指定AUTO_INCREMENT的起始值
CHARACTER SET [=] charset_name 指定默认字符集
COLLATE [=] collation_name 排序规则
CHECKSUM [=] {0 | 1} 是否校验表
COMMENT [=] 'string' 表的注释信息
DELAY_KEY_WRITE [=] {0 | 1} 是否启用键延迟写入，索引信息过会再创建
ROW_FORMAT [=] {DEFAULT（默认）|DYNAMIC（动态）|FIXED（静态）|COMPRESSED（压缩）|REDUNDANT（冗余）|COMPACT（紧致）} 表格式
TABLESPACE tablespace_name 表空间
存储引擎是表级别的概念也称为表类型，常用的有两种：
MyISAM表，每个表有三个文件，都位于数据库目录中
tb_name.frm：表结构定义文件
tb_name.MYD：数据文件
tb_name.MYI：索引文件
InnoDB表，有两种存储方式
1.默认：每表有一个独立文件和一个多表共享的文件
tb_name.frm：表结构的定义，位于数据库目录中
ibdataN：共享的表空间文件，默认位于数据目录（datadir指向的目录）中
2.独立的表空间文件：
每表有一个表结构文件tb_name.frm
一个独立的表空间文件 tb_name.ibd
要设置该方式，应该在配置文件中添加：innodb_file_per_table = ON
# ls mydb/
db.optt1.frmt1.ibdt2.frmt2.MYDt2.MYI
表创建：第二种方式（复制表数据，不会复制表结构）
CREATE TABLE tbl_name
[(create_definition,...)]
select_statement
表创建：第三种方式（复制表结构）
CREATE TABLE tbl_name
{ LIKE old_tbl_name | (LIKE old_tbl_name) }
MariaDB > CREATE TABLE t1 (Name VARCHAR(50) NOT NULL, Age TINYINT UNSIGNED NOT NULL,PRIMARY KEY(Name,Age));
Query OK, 0 rows affected (0.62 sec)
MariaDB > SHOW TABLES;
+----------------+
| Tables_in_mydb |
+----------------+
| t1 |
+----------------+
1 row in set (0.00 sec)
MariaDB > DESC t1;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| Name| varchar(50) | NO | PRI | NULL | |
| Age | tinyint(3) unsigned | NO | PRI | NULL | |
+-------+---------------------+------+-----+---------+-------+
2 rows in set (0.04 sec)
MariaDB > SHOW TABLE STATUS\G
*************************** 1. row ***************************
Name: t1
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2017-02-18 16:24:08
Update_time: NULL
Check_time: NULL
Collation: gbk_chinese_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
MariaDB > CREATE TABLE t2 (Name VARCHAR(50) NOT NULL, Age TINYINT UNSIGNED NOT NULL,PRIMARY KEY(Name,Age)) ENGINE "myisam";
Query OK, 0 rows affected (0.02 sec)
MariaDB > SHOW TABLE STATUS\G
*************************** 1. row ***************************
Name: t1
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2017-02-18 16:24:08
Update_time: NULL
Check_time: NULL
Collation: gbk_chinese_ci
Checksum: NULL
Create_options:
Comment:
*************************** 2. row ***************************
Name: t2
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 281474976710655
Index_length: 1024
Data_free: 0
Auto_increment: NULL
Create_time: 2017-02-18 16:30:50
Update_time: 2017-02-18 16:30:50
Check_time: NULL
Collation: gbk_chinese_ci
Checksum: NULL
Create_options:
Comment:
2 rows in set (0.00 sec)
MariaDB >
删除：
DROP TABLE tbl_name [, tbl_name] ...
修改：
ALTER TABLE tbl_name [alter_specification [,>
修改字段定义：
插入新字段：
ADD col_name column_definition
删除字段：
DROP col_name
修改字段：
修改字段名称：
CHANGE old_col_name new_col_name column_definition
修改字段类型及属性等：
MODIFY col_name column_definition
修改表名：
RENAME |AS] new_tb_name
修改表名也可以直接使用RENAME修改：REANME TABLE old_tb_name TO new_tb_name;
修改表选项：和创建表时的选项一样
例如：修改存储引擎
ENGINE = "engine_name"
指定排序标准的字段：
ORDER BY col_name [, col_name] ...
转换字符集及排序规则：
CONVERT TO CHARACTER SET charset_name
修改约束、键或索引：# 查看帮助信息
ADD {INDEX|KEY}
(index_col_name,...) ...
DROP {INDEX|KEY} index_name
查看索引信息：SHOW INDEXS FROM table_name;
MariaDB > DESC t1;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| Name| varchar(50) | NO | PRI | NULL | |
| Age | tinyint(3) unsigned | NO | PRI | NULL | |
+-------+---------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
MariaDB > ALTER TABLE t1 ADD ID INT UNSIGNED AUTO_INCREMENT;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
MariaDB > ALTER TABLE t1 ADD ID INT UNSIGNED;
Query OK, 0 rows affected (0.15 sec)
Records: 0Duplicates: 0Warnings: 0
MariaDB > DESC t1;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| Name| varchar(50) | NO | PRI | NULL | |
| Age | tinyint(3) unsigned | NO | PRI | NULL | |
| ID | int(10) unsigned | YES| | NULL | |
+-------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
MariaDB > ALTER TABLE t1 DROP ID;
Query OK, 0 rows affected (0.35 sec)
Records: 0Duplicates: 0Warnings: 0
查：
SHOW TABLES;
DESC tb_name;
MariaDB > CREATE TABLE ZY (ID TINYINT PRIMARY KEY AUTO_INCREMENT, Name VARCHAR(25) NOT NULL, Age TINYINT, Gender ENUM("F","M") DEFAULT "M", Course VARCHAR(30));
Query OK, 0 rows affected (0.09 sec)
MariaDB > DESC ZY;
+--------+---------------+------+-----+---------+----------------+
| Field| Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+----------------+
| ID | tinyint(4) | NO | PRI | NULL | auto_increment |
| Name | varchar(25) | NO | | NULL | |
| Age | tinyint(4) | YES| | NULL | |
| Gender | enum('F','M') | YES| | M | |
| Course | varchar(30) | YES| | NULL | |
+--------+---------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
MariaDB > INSERT INTO ZY VALUE (1,"Ling huchong", 24, "M", "Hamogong"),(2,"Huang RONG",19,"F","Chilian shenzhang");
Query OK, 2 rows affected (0.03 sec)
Records: 2Duplicates: 0Warnings: 0
MariaDB > DESC ZY;
+--------+---------------+------+-----+---------+----------------+
| Field| Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+----------------+
| ID | tinyint(4) | NO | PRI | NULL | auto_increment |
| Name | varchar(25) | NO | | NULL | |
| Age | tinyint(4) | YES| | NULL | |
| Gender | enum('F','M') | YES| | M | |
| Course | varchar(30) | YES| | NULL | |
+--------+---------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
MariaDB > SELECT * FROM ZY;
+----+--------------+------+--------+-------------------+
| ID | Name | Age| Gender | Course |
+----+--------------+------+--------+-------------------+
|1 | Ling huchong | 24 | M | Hamogong |
|2 | Huang RONG | 19 | F | Chilian shenzhang |
+----+--------------+------+--------+-------------------+
2 rows in set (0.00 sec)
MariaDB > ALTER TABLE ZY ADD Class VARCHAR(20) AFTER Name;
Query OK, 0 rows affected (0.92 sec)
Records: 0Duplicates: 0Warnings: 0
MariaDB > ALTER TABLE ZY CHANGE ID TID INT UNSIGNED;
Query OK, 2 rows affected (0.20 sec)
Records: 2Duplicates: 0Warnings: 0
MariaDB > ALTER TABLE ZY MODIFY Age TINYINT AFTER Course;
Query OK, 0 rows affected (1.51 sec)
Records: 0Duplicates: 0Warnings: 0
MariaDB > DESC ZY;
+--------+---------------+------+-----+---------+-------+
| Field| Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| TID | int(11) | NO | PRI | NULL | |
| Name | varchar(25) | NO | | NULL | |
| Class| varchar(20) | YES| | NULL | |
| Gender | enum('F','M') | YES| | M | |
| Course | varchar(30) | YES| | NULL | |
| Age | tinyint(4) | YES| | NULL | |
+--------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
二、查询语句
查询的执行路径：
单表查询：简单查询
多表查询：连接查询
联合查询：
1、单表查询
选择和投影:
投影：挑选要符合的字段
选择：挑选符合条件的行
投影：select 字段1，字段2，... from tb_name;
selcet * from tb_name;
选择：select 字段1，字段2,.... from tb_name
# 将结果以什么方式排序
# 将结果以什么字段分组
# 对分组结果进行过滤
]; # 只返回有限的行，偏移行数，取的行数
布尔条件表达式操作符:
= 等值比较
等值比较，跟空值比较，不会产生额外信息
不等值
<
>=
IS NULL:是否为空
IS NOT NULL：是否不空
LIKE：支持的通配符%(任意长度的任意字符) _（任意单个字符）
RLIKE，REGEXP：支持使用正则表达式作为条件，都只能用于字符串匹配，不能用于数值匹配
IN：判断某行的某一字段的值是否在给定的列表中
BETWEEN...AND....:判断指定的值是否位于指定的范围之间
组合条件测试：
NOT ！
AND &&
OR ||
聚合函数：
SUM(),AVG(),MAX(),MIN(),COUNT(),
所有选择的字段的和，平均值，最大值，最小值，个数
MariaDB > SELECT * FROM ZY;
+-----+--------------+-------+--------+-------------------+------+
| TID | Name | Class | Gender | Course | Age|
+-----+--------------+-------+--------+-------------------+------+
| 1 | Ling huchong | NULL| M | Hamogong | 24 |
| 2 | Huang RONG | NULL| F | Chilian shenzhang | 19 |
+-----+--------------+-------+--------+-------------------+------+
2 rows in set (0.00 sec)
MariaDB > SELECT * FROM ZY WHERE Age > 20;
+-----+--------------+-------+--------+----------+------+
| TID | Name | Class | Gender | Course | Age|
+-----+--------------+-------+--------+----------+------+
| 1 | Ling huchong | NULL| M | Hamogong | 24 |
+-----+--------------+-------+--------+----------+------+
1 row in set (0.39 sec)
MariaDB > SELECT Name,Gender FROM ZY WHERE Course like "H%";
+--------------+--------+
| Name | Gender |
+--------------+--------+
| Ling huchong | M |
+--------------+--------+
1 row in set (0.00 sec)
MariaDB > SELECT * FROM ZY WHERE Age IN (18,20,22,24);
+-----+--------------+-------+--------+----------+------+
| TID | Name | Class | Gender | Course | Age|
+-----+--------------+-------+--------+----------+------+
| 1 | Ling huchong | NULL| M | Hamogong | 24 |
+-----+--------------+-------+--------+----------+------+
1 row in set (0.00 sec)
MariaDB > SELECT MAX(Age) FROM ZY;
+----------+
| MAX(Age) |
+----------+
| 24 |
+----------+
1 row in set (0.00 sec)
MariaDB > SELECT MAX(Name) FROM ZY;
+--------------+
| MAX(Name) |
+--------------+
| Ling huchong |
+--------------+
1 row in set (0.00 sec)
MariaDB >
MariaDB > SELECT Gender,SUM(Age) FROM ZY GROUP BY Gender;
+--------+----------+
| Gender | SUM(Age) |
+--------+----------+
| F | 19 |
| M | 24 |
+--------+----------+
2 rows in set (0.00 sec)
MariaDB >
SELECT语句的执行流程；
FROM clause --> WHERE clause --> GROUP BY --> HAVING clause --> 0RDER BY --> SELECT --> LIMIT
select常用修饰符：
DISTINCT 重复的只显示一次
SQL_CACHE 缓存于查询缓存中
SQL_NO_CACHE不缓存查询结果
2、多表查询
联结查询：事先将两张或者多张表join，根据join的结果进行查询
交叉联结(cross join)：交叉相乘（a+b）(c+d+e)
例：SELECT * FROM students,> 内联结(自然联结):
等值联结：把相同的字段进行等值连接
例：SELECT * FROM students,> 条件联结：# 用的很少
外联结：
左外联接：只保留出现在左外连接运算之前（左边）的关系中的元组（记录）（以左表为准）
left_tb LEFT JOIN right_tb ON 条件
例：SELECT s.Name,c.Class FROM students AS s LEFT JOIN> 右外联接：只保留出现在右外连接元算之后（右边）的关系中的元组（以右表为准）
left_tb RIGHT JOIN right_tb ON 条件
例：SELECT s.Name,c.Class FROM students AS s RIGHT JOIN> 全外联接：
自联结：
别名：AS
表别名：
例：SELECT s.Name, c.Class FROM students AS s,> 字段别名:
例：SELECT Name AS StuName FROM students;
3、子查询
子查询：在查询中嵌套的查询
用于WHERE中的子查询
1.用于比较表达式中的子查询
子查询的返回值只能有一个
2.用于EXISTS中的子查询
判断存在与否
3.用于IN中的子查询
判断存在于指定列表中
用于FROM中子查询
select alias.col,....FROM(SELECT CLUSE) alias WHERE condition
MYSQL不擅长于子查询，应该避免使用子查询
4、MYSQL的联合查询
把两个或多个查询语句的结果合并成一个结果进行输出
select clauase union select clause union.....；
5、索引
show indexs from tb_name； 查看索引
alter table tb_name add index 字段；创建索引
explain 解释命令，查看命令的执行过程
6、视图（虚表）
存储下来的select语句
创建：
create view 视图名 as select语句；
删除：
drop view 视图名；
三、INSERT/REPLACE、UPDATE、DELETE
1、INSERT
第一种：
INSERT tb_name [(col1,col2,....)]{VALUES|VALUE} (val1,val2,...)...;
第二种：
INSERT tb_name SET col_name=val1,col2=val2,....;
第三种(将一个表中的数据插入到另外一张表中)：
INSERT tb_name select clause;
2、REPLACE
replace替换表中数据，用法同insert，除了在新插入的数据与表中的主键或唯一索引定义的数据相同会替换老的行
3、UPDATE
UPDATE table_reference# 表引用，UPDATE也可以更新视图
SET col_name1=val1 [, col_name2={val2] ...
# 一般先排序再限定行数
update通常情况下，必须要使用where字句，或者使用limit限制要修改的行数
--safe-updates：启动时应该带选项，忘记使用WHERE或LIMIT子句则拒绝使用
5、DELETE
DELETE FROM tbl_name
TRUNCATE tb_name; 重置表的所有内容
页:
[1]