MySQL-基本操作

本文整理自《深入浅出MySQL数据库开发、优化与管理维护(第2版)》

第一部分 基础篇

数据库DCL

1、连接与断开MySQL服务器:

#启动mysql
mysql.server start
#回车后输入密码
mysql –uroot –p
#断开连接
quit或者exit

2、执行SQL语句:

语句以“;”结束。不区分大小写。 在一行中可以输入多条SQL语句,各语句以“;”号隔开。 各条语句得到各自的结果集。 在任何时候输入“\c”并回车,则取消当前输入,重新回到mysql>提示符。 某些情况下,按回车后,SQL语句并不执行,出现的也不是“->”提示符,如:[’>]、[”>]、[`>]、[/*],则表示引号或注释没有结束。即使通过\c取消,也要先结束引号。

3、创建数据库:

#CHARACTER SET:字符集。
#COLLATE:整理或校对。如不指定则采用默认。
CREATE DATABASE db_test default character set gb2312 collate gb2312_chinese_ci;

4、关于字符校对collate

指比较字符串时采用的一套规则。还可以设置服务器校对和表校对。 一般情况下不需要设置字符校对,因为对应于每一种字符集MySQL有默认的校对规则,例如gb2312字符集默认校对规则为gb2312_chinese_ci。当不设置校对时采用默认。

5、删除数据库:

DROP DATABASE database_name;

6、选择数据库:

#在创建数据表之前,首先要选择数据库。
#其它查询、修改、删除等操作也一样。
use db_name;

#查看当前数据库下所有表:
show tables;

7、查看运行端口:

show global variables like 'port';

8、执行sql脚本:

source 文件位置

9、查看mysql版本

#注意大写
mysql -V

表DDL

1、查看表:

show columns from user_info from db_test;
show columns from db_test.user_info;

#如果当前使用数据库为表所在的数据库,则可以省略db_test。
describe user_info;

#用法同上,还可以只得到某一个列的信息:
describe user_info name;

2、删除表:

一次删除多个表:

DROP TABLE user_info1,user_info2;

3、重命名表:

可以同时对多个表重命名,之间以逗号“,”隔开。

RENAME TABLE user_info TO users_infomation, sale_info TO sales_infomation;

4、修改表:

ALTER TABLE table_name MODIFY [COLUMN] col_name column_definition [FIRST/AFTER]col_name
ADD [COLUMN] col_name column_definition [FIRST/AFTER]col_name
DROP [COLUMN] col_name
CHANGE [COLUMN] col_name NEW col_name column_definition [FIRST/AFTER]col_name
RENAME [TO] NEW table_name

modify和change区别: change需要写两次col_name,但是可以用来修改列名。

在列reg_date中添加索引:

ALTER TABLE user_information ADD INDEX (reg_date);

一次添加多个列(字段):

ALTER TABLE table_name ADD func varchar(50), ADD gene varchar(50), ADD genedetail varchar(50);

记录DML

1、插入记录:

INSERT INTO table_name(…...) values (…..);

INSERT INTO user_info (name, gender, age, email) values(‘XiaoHei’, ‘男’,28,‘xhei@sohu.com’);

#可以一次同时插入多行记录,用“,”隔开。
INSERT INTO user_info(name,gender,age,email) values(‘ChenYi’, ‘女’,25,‘chenyi@shou.com’), (‘XiaoHei’, ‘男’,28,‘xhei@sohu.com’);

CONCAT为MySQL系统函数,将多个字符串连接起来。

2、修改记录:

UPDATE table_name SET field1=values1,field2=values2,…. [ WHERE condition]

UPDATE table1_name t1, table2_name t2, table3_name t3  SET  t1.field1=values1,t2.field2=values2,…. [ WHERE condition]

3、删除记录:

DELETE FROM table_name [ WHERE condition];

DELETE   t1,t2  FROM  table1_name t1, table2_name t2 [ WHERE condition];

各关键字含义用UPDATE,不指定where子句时删除所有行。

4、查询记录:

#全选 SQL实际运行的时候会把*转换成列名,一般建议不使用*
SELECT * FROM  table_name  [ WHERE condition]; 

#选择部分字段
SELECT  field1_name,field2_name,…. FROM table_name  [ WHERE condition]; 

#去重 选择某字段的不重复的记录
SELECT  DISTINCT  field_name  FROM table_name  ; 

#排序 desc表示降序,默认为升序。
SELECT [ ORDER BY field1_name [DESC/ASC],  field2_name [DESC/ASC],  field3_name [DESC/ASC],….  ]    


#offset_start表示记录起始偏移量,
#row_count表示显示的行数。如果只写一个表示 row_count(其他数据库不通用)
#limit 和order by常配合使用来进行记录的分页显示。limit用在rollup后面。
SELECT [ LIMIT offset_start, row_count ]  

#GROUP BY 要进行分类聚合的字段
#WITH ROOLUP 是否对分类聚合后的结果进行再汇总,和ORDER BY是互斥的。
#HAVING 表示对分类后的结果再进行条件的过滤
#Function_name  :sum,count(*),max,min
SELECT  field1_name,field2_name,…. Function_name FROM  table_name
[ WHERE condition][GROUP BY  field1_name,field2_name,…. [WITH ROOLUP] ][HAVING where_condition]

5、表连接p43

左连接:包含所有左边表中的记录甚至是右边表中没有和它匹配的记录 左连接:包含所有右边表中的记录甚至是左边表中没有和它匹配的记录

SELECT col1_name,col2_name… FROM table1_name RIGHT/LEFT JOIN table2_name ON condition

6、子查询:

WHERE col_name in (select …..)

如果子查询记录数唯一,可以用=代替in

7、记录联合:

UNION ALL 把结果直接合并在一起, 而UNION 是在此基础上进行一次DISTINCT去除重复记录后的结果。


踩坑的地方

1、sql语句中千万不要有sql的保留词,不然会无法识别 报错:The error occurred while setting parameters

2、MySQL和excel互相导入

excel到MySQL:excel文件另存为CSV utf-8(逗号分隔),MySQL直接import就行

MySQL到excel:MySQL 导出csv格式,excel选择导入。