MySQL基础--SQL语句

1.连接MySQL数据库

//connect to MySQL Database
mysql -h lcoalhost -uroot -p123456 -P3306
//-h host
//-u account
//-p password
//-P port

//quit MySQL
\q

2.数据库

2.1 创建数据库

CREATE DATABASE [IF NOT EXISTS] db_name 
[CHARACTER SET charset_name] --数据库编码格式
[COLLATE collation_name];  --数据库校验格式

2.2 其他

--查看所有数据库
SHOW DATABASES;

--查看当前库
SELECT DATABASE();

--删除数据库
DROP DATABASE [IF EXISTS] db_name;

--查看数据库信息
SHOW CREATE DATABASE db_name;

--修改数据库选项
ALTER DATABASE db_name [CHARACTER SET character_name] [COLLATE collation_name]

3. 数据表

3.1 创建表

--新建表
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name(
`field1` TYPE [NULL|NOT NULL] [DEFAULT value] [UNIQUE|PRIMARY] [AUTO_INCREMENT] [COMMENT 'comment'],
`field1` TYPE [NULL|NOT NULL] [DEFAULT value] [UNIQUE|PRIMARY] [AUTO_INCREMENT] [COMMENT 'comment'],
PRIMARY KEY(`field1`),
FOREIGN KEY(`field2`) REFERENCE table_name2(`field`)
)[ENGINE = engine_name] [DEFAULT CHARSET = charset] [AUTO_INCREMENT = value] [DATA DIRECTORY = 'PATH'] [INDEX DIRECTORY = 'PATH'] [COMMENT 'COMMENT'] [PARTITION BY ...];

--复制表结构
CREATE TABLE table_name LIKE table_name2

--复制表结构+数据
CREATE TABLE table_name AS SELECT * FROM table_name2

• TEMPORARY：是否为临时表
• IF NOT EXISTS：判断该表是否存在
• TYPE：字段数据类型
• NULL|NOT NULL：是否允许为空
• DEFAULT：默认值
• UNIQUE：唯一索引
• PRIMARY：主键
• AUTO_INCREMENT：自增长
• COMMENT：注释
• FOREIGN KEY：外键
• ENGINE：数据库引擎
• DEFAULT CHARSET：默认表编码格式
• AUTO_INCREMENT：自增长值
• DATA DIRECTORY：数据文件存放路径
• INDEX DIRECTORY：索引文件存放路径
• PARTITION BY：分区方式

3.2 修改表

--修改表名
RENAME TABLE old_name TO new_name

--删除表
DROP TABLE table_name

--修改表选项
ALETR TABLE table_name option = value

--查看所有表
SHOW TABLES FROM db_nmae

--清空表数据
TRUNCATE TABLE table_name

--表检查(检查表异常)
CHECK TABLE table_name

--表分析（重建异常索引）
ANALYSE TABLE table_name

--表优化（整理表的磁盘空间，清理空间碎片）
OPTIMIZE TABLE table_name

--表修复
REPAIR TABLE table_name 

3.3 字段操作

--增加表字段
ALTER TABLE table_name ADD field_name TYPE [field_option]

--增加主键
ALTER TABLE table_name ADD PRIMARY KEY(field)

--增加普通索引
ALTER TABLE table_name ADD INDEX(field)

--增加唯一索引
ALTER TABLE table_name ADD UNIQUE(field)


--删除表字段
ALTER TABLE table_name DROP field

--删除主键
ALTER TABLE table_name DROP PRIMARY KEY

--删除索引
ALTER TABLE table_name DROP INDEX(field)

--删除唯一索引
ALTER TABLE table_name DROP UNIQUE(field)

--删除外键
ALTER TABLE table_name DROP FOREIGN KEY(field)

--修改字段名
ALTER TABLE table_name CHANGE old_field new_field [option]

--修改字段属性
ALTER TABLE table_name MODIFY field_name [OPTION]

4. 数据操作

--插入数据
INSERT INTO table_name(field1,field2,...)VALUES(val1,var2,..)

--更新数据
UPDATE TABLE table_name SET field = val 

--删除数据
DELETE FROM table_name 

--查询数据
SELECT * FROM table_name

5.字符集编码

--查看所有环境编码
SHOW VARIABLES LIKE 'character_set%'

/*
character_set_client	客户端向服务器发送数据时使用的编码
character_set_connection	连接层编码，主要用来设置连接数据库时的字符集，如果程序中没有指明连接数据库使用的字符集类型则按照这个字符集设置。
character_set_database	主要用来设置默认创建数据库的编码格式，如果在创建数据库时没有设置编码格式，就按照这个格式设置。
character_set_filesystem	文件系统的编码格式，把操作系统上的文件名转化成此字符集，即把 character_set_client转换character_set_filesystem， 默认binary是不做任何转换的。
character_set_results	服务器端将结果返回给客户端所使用的编码
character_set_server	服务器安装时指定的默认编码格式，这个变量建议由系统自己管理，不要人为定义。
character_set_system	数据库系统使用的编码格式，这个值一直是utf8，不需要设置，它是为存储系统元数据的编码格式。
character_sets_dir	这个变量是字符集安装的目录。
*/

--设置变量
SET var_name = val
/*
SET character_set_client = gbk;
SET character_set_results = gbk;
SET character_set_connection = gbk;
*/


--查看校对集
SHOW COLLATION

--查看所有字符集
SHOW CAHRACTER SET

借助网上的一个完整的用户请求的字符集转换流程来更好的理解上述几个变量：
mysql Server收到请求时将请求数据从 character_set_client 转换为 character_set_connection
进行内部操作前将请求数据从 character_set_connection 转换为内部操作字符集,步骤如下
　　A. 使用每个数据字段的 CHARACTER SET 设定值；
　　B. 若上述值不存在，则使用对应数据表的字符集设定值
　　C. 若上述值不存在，则使用对应数据库的字符集设定值；
　　D. 若上述值不存在，则使用 character_set_server 设定值。
最后将操作结果从内部操作字符集转换为 character_set_results

6.查找语句

SELECT [ALL|DISTINCT] select_expr FROM -> WHERE -> GROUP BY [合计函数] -> HAVING -> ORDER BY -> LIMIT

select dept_no,group_concat(first_name) from dept_emp left join employees on dept_emp.emp_no = employees.emp_no group by dept_emp.dept_no

group_caoncat

7. join

--左连接（允许右边为空）
t1 LEFT JOIN t2 ON t1.col1 = t2.col2

--右连接（允许左边为空）
t1 RIGHT JOIN t2 ON t1.col1 = t2.col2

--自然连接，自动判断条件
t1 NATURAL JOIN t2 

--自然左连接（允许右边为空）
t1 NATURAL LEFT JOIN t2 

--自然右连接（允许左边为空）
t1 NATURAL RIGHT JOIN t2 

--三表查询
SELECT * FROM (表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号

8.导入导出表

--导出表数据到某个文件中
SELECT * FROM table_name into OUTFILE 'path' [FIELDS TERMINATED BY '分隔符'，默认为制表符 ENCLOSED BY ‘包裹符号’, 默认无 ESCAPED BY '转义符'，默认\]

--导入数据到表中
LOAD DATA INFILE 'path' [REPLACE|IGNORE] INTO TABLE table_name

导入导出失败：检查secure_file_priv值（空：无限制；null：不允许导入导出；具体地址：只允许导入导出到指定目录）

 

9.视图

--创建视图
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement

--删除视图
DROP VIEW [IF EXISTS] view_name

--显示视图结构
SHOW CREATE VIEW view_name

--视图算法(ALGORITHM)
--MERGE       合并    将视图的查询语句，与外部查询需要先合并再执行！
--TEMPTABLE   临时表    将视图执行完毕后，形成临时表，再做外层查询！
--UNDEFINED   未定义(默认)    指的是MySQL自主去选择相应的算法。