MySQL基本操作汇总

　　目录：

　　一， mysql 登录参数

　　二，修改MySQL提示符：2 种方式

　　三，MySQL常用命令

　　四，数据库操作

　　五，数据表操作

　　六，约束

　　七，约束扩展

　　八，外键约束的参照操作

　　九，表级约束与列级约束

　　十，小结

　　十一，修改数据表(一)

　　十二，修改数据表(二)

　　十三，insert插入记录

　　十四，update方法

　　十五，select方法

　　十六，group by方法

　　十七，order by方法

　　十八，limit方法

　　一，mysql 登录参数

　　1， -D,--database=name 打开指定数据库

　　2，- -delimiter = name 指定分隔符

　　3，-h，- -host = name 服务器名称

　　4，-p，- -password[ = name] 密码

　　5，-P,- -port=# 端口号

　　6，- -prompt =name 设置提示符

　　7，-V，- -version 输出版本信息并且退出

　　8，-u，- -user =name 用户名

　　9，-d --database 当前数据库

　　例：mysql -u root -p -P 127.0.0.1

　　127.0.0.1 : 网络术语当中成为本地回环地址

　　二，修改MySQL提示符：

　　有以下2 种方式

　　1，连接上客户端后，通过prompt命令修改

　　mysql>prompt 提示符

　　2，连接客户端时通过参数指定

　　shell> mysql -uroot -proot - -prompt 提示符

　　三，MySQL常用命令

　　1，显示当前服务器版本

　　SELECT VERSION();

　　2，显示当前用户

　　SELECT USER();

　　3，显示当前日期时间

　　SELECT NOW();

　　四，数据库操作

　　(花括号必选，中括号可选),语句后面要分号。

　　增：CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name

　　删：DROP DATABASE db_name;

　　改：ALTER DATABASE db_name CHARACTER SET charset_name;

　　查：显示所有的数据库 ：SHOW DATABASES;

　　五，数据表操作

　　1，数据库最重要的组成部分之一，是其他对象的基础。

　　(1)打开数据库

　　USE 数据库名称;

　　(2)创建数据表

　　CREATE TABLE [IF NOT EXISTS] table_name(

　　column_name data_type,

　　...

　　)

　　2，查看数据表：(查看选择的数据库的表)

　　SHOW TABLES [FROM db_name] [LIKE 'pattern' | WHERE expr];

　　例：

　　SHOW TABLES;//查看当前选择的数据库的所有表

　　SHOW TABLES FROM TEST;//查看TEST数据库中的所有表，当前选择数据库位置不变。

　　SELECT DATABASE();//查看当前选择的数据库

　　SHOW COLUMNS FROM tbl_name;//查看数据表结构

　　3，插入记录(数据表中的 行)

　　INSERT [INTO] tb1_name [(col_name,...)] VALUES(val,...);

　　INSERT 关键字

　　INTO 可省略

　　tb1_name 数据表的名称

　　col_name 数据表中字段(列)的名称 可以省略，当省略时，后面插入记录数要和所有字段数相符，不省略时，要输入字段名称，后面插入记录也要相符。

　　VALUES(val,..) 关键字(记录) 记录数要和前面字段相符

　　4，记录查找

　　SELECT expr,...FROM tb1_name

　　SELECT 查找关键字 expr 字段 *表示全部

　　FROM tb1_name哪个数据表

　　六，约束

　　1. 约束保证数据的完整性和一致性。

　　2. 约束分为表级约约束和列级约束。

　　3. 约束类型包括：

　　NOT NULL(非空约束)

　　UNIQUE KEY(唯一约束)

　　PRIMARY KEY(主键约束)

　　FOREIGN KEY(外键约束)

　　DEFAULT(默认约束)

　　FOREIGN KEY(外键约束)

　　作用：

　　保持数据一致性，完整性

　　实现一对一或者一对多关系

　　要求：

　　1. 父表和子表必须使用相同的存储引擎，而且禁止使用临时表。

　　子表： 具有外键列的表。

　　父表： 子表参照的表。

　　2. 数据表的存储引擎只能为InnoDB。

　　3. 外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同;而字符的长度则可以不同。

　　* 数字长度或是否有符号位必须相同，否则将提示错误并无法创建外键约束：

　　外键列： 加过外键列的列。

　　参照列： 外键列所参照的列。

　　4. 外键列和参照列必须创建索引。如果外键列不存在索引的话，MySQL将自动创建索引。

　　编辑数据表的默认存储引擎

　　MySQL配置文件： default-storage-engine=INNODB

　　查看创建表的存储引擎: SHOW CREATE TABLE table_name;

　　例子：

　　子表：

　　mysql> CREATE TABLE users(

　　id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

　　username VARCHAR(20) NOT NULL,

　　pid SMALLINT UNSIGNED,

　　FOREIGN KEY (pid) REFERENCES provinces (id) //外键列： pid 参照列：provinces.id

　　);

　　父表：

　　mysql> CREATE TABLE provinces(

　　id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, //参照列： id

　　pnane VARCHAR(20) NOT NULL

　　);

　　七，约束扩展

　　1.自动编号 - AUTO_INCREMENT

　　自动编号，且必须与主键配合使用

　　自动编号AUTO_INCREMENT

　　1、自动编号：保证记录的唯一性

　　2、类型必须为整型(可以是FLOAT(5,0)等，小数点后必须为0)，必须和主键PRIMARY KEY组合使用

　　3、在默认情况下，起始值为1，每次的增量为1

　　默认情况下，起始值为1， 每次增量为1.

　　CREATE TABLE tb3(

　　id SMALLINT UNSIGNED AUTO_INCREMENT,

　　username VARCHAR(30) NOT NULL); //有错，自动增量字段必须设置成主键

　　主键可以写为PRIMARY KEY，也可以写成KEY

　　每张数据表只能存在一个主键

　　主键保证记录的唯一性

　　主键自动为NOT NULL

　　auto_increment必须和主键primary key一起使用，但是主键primary key不一定要和auto_increment一块使用

　　八，外键约束的参照操作：

　　1. CASCADE: 从父表删除或更新且自动删除或更新子表中匹配的行。

　　2. RESTRICT: 拒绝对父表的删除或者更新操作。

　　3. SET NULL: 从父表删除或更新行，并设置子表中的外键列为NULL。如果使用该选项，必须保证子表列没有指定NOT NULL。

　　4. NO ACTION: 标准SQL的关键字，在MySQL中与RESTRICT相同。

　　例子：

　　添加父表：

　　mysql> CREATE TABLE provinces(

　　id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, //参照列： id

　　pnane VARCHAR(20) NOT NULL

　　);

　　添加子表带CASCADE操作：

　　mysql> CREATE TABLE user1(

　　id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

　　username VARCHAR(10) NOT NULL,

　　pid SMALLINT UNSIGNED,

　　FOREIGN KEY (pid) REFERENCES provinces (id) ON DELETE CASCADE

　　);

　　为父表添加数据：

　　INSERT INTO provinces(pname) VALUES('A')

　　INSERT INTO provinces(pname) VALUES('B')

　　INSERT INTO provinces(pname) VALUES('C')

　　删除父表中的数据，并影响子表数据：

　　DELETE FROM provinces where id = 3;

　　==> 父表id为3的数据被删， 其子表中pid为3的数据也被删!

　　*外键约束 很少使用物理外键约束(innodb支持)，在实际开发用的比较多是逻辑外键(不使用freign key)

　　九，表级约束与列级约束

　　1.表级约束与列级约束

　　1)对一个数据列建立的约束，称为列级约束

　　2)对多个数据列建立的约束，称为表级约束

　　3)表级约束只能在列定义后声明

　　4)列级约束既可以在列定义时声明，也可以在列定以后声明

　　2.主键、外键、非空、唯一、check，default都是约束条件。主键、外键、既可以作为表级约束，也可作为列级约束

　　而像非空、唯一等( not null， default )只有列级约束

　　十，小结

　　数据类型 整型 (tinyint smallint mediumint int bigint)

　　浮点型 (FLOAT{[M,D]},DOUBLE{[M,D]})M:总位数,D:小数点后位数

　　日期 (YEAR DATE TIME TIMESTEMP)

　　字符型 (CHAR VARCHAR TEXT)

　　DOS 界面连接数据库 mysql -u用户名 -p密码 -hHOST

　　----总结------

　　创建数据表：CREATE TABLE table_name;

　　查看数据表：SHOW COLUMNS FROM table_name;

　　向表中插入：INSERT table_name

　　查找记录 ：SELECT * FROM table_name

　　创建数据表

　　CREATE TABLE tb5(

　　->id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

　　->username VARCHAR(20) NOT NULL UNIQUE KEY,

　　->sex ENUM('男','女','保密') DEFAULT '保密'

　　->);

　　十一，修改数据表(一)

　　1，添加

　　(1)添加单列

　　ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name]

　　例如：

　　ALTER TABLE users1 ADD age TINYINT UNSIGNED NOT NULL DEFAULT 10;

　　在 id 后面添加 username属性：

　　ALTER TABLE users1 ADD password VARCHAR(32) NOT NULL AFTER username;

　　(2)添加多列

　　ALTER TABLE tbl_name ADD [COLUMN](col_name column_definition，...)该方法添加的列不可以指定位置

　　2，删除

　　(1)删除单列

　　ALTER TABLE tbl_name DROP [COLUMN] col_name

　　例如：ALTER TABLE users1 DROP truename;

　　(2)一次删除多列：

　　ALTER TABLE uses1 DROP password,DROP age;

　　也能删除一列的同时，新增一列。其间用逗号隔开。

　　3、添加主键约束：

　　ALTER TABLE tbl_name ADD [CONSTRAINT[symbol]] PRIMARY KEY[index_type] (col_name);

　　4、添加外键约束

　　ALTER TABLE tbl_name ADD [CONSTRAINT[symbol]] FOREIGN KEY [index_type](外键列) REFERENCES 父类(参考列);

　　6、添加唯一约束

　　ALTER TABLE tbl_name ADD UNIQUE(col_name);

　　6、添加/删除默认DEFAULT约束

　　ALTER TABLE tbl_name ALTER col_name SET DEFAULT literal / DROP DEFAULT;

　　7、删除主键约束：ALTER TABLE 表名 DROP PRIMARY KEY;

　　8、删除外键约束：ALTER TABLE 表名 DROP FOREIGN KEY 外键索引名字;

　　9、删除唯一约束：ALTER TABLE 表名 DROP {INDEX|KEY} 约束所在字段;

　　10、查看外键约束名称：SHOW CREATE TABLE 表名;

　　11、查看索引：SHOW INDEXES FROM 表名;

　　十二，修改数据表(二)

　　修改列名称：

　　ALTER TABLE 表名 CHANGE 原字段名 新字段名 数据类型 (FIRST 或者 AFTER 字段名);

　　修改列定义：

　　ALTER TABLE 表名 MODIFY 字段名 数据类型(FIRST或者AFTER 字段名);

　　修改数据表名：

　　1、ALTER TABLE 表名 RENAME (TO|AS) 新表名;

　　2、RENAME TABLE 表名 TO 新表名(表名2 TO 新表名2);

　　//不建议随意修改，可能造成数据丢失||数据列之间的关联等

　　十三，insert插入记录

　　1，第一种方法

　　INSERT [INTO] table_name [(column_name,...)] {VALUES|VALUE} ({expr|DEFAULT},...),(...),...;

　　/**

　　column_name 表示列名

　　expr 表示表达式

　　DEFAULT 可直接书写，表示默认值

　　当字段有自动增加的属性后，在插入表的所有字段的数据时，可以填写 NULL 或 DEFAULT 这两个关键字，表示按照原先默认自动增长的属性

　　插入多行数据时，可以用逗号隔开

　　*/

　　Eg：

　　INSERT INTO table_name VALUE(DEFAULT,'BeforeDayBreak',md5('123'),DEFAULT,0),(NULL,'Superman',md5('123'),DEFAULT,0);

　　自增的属性可以为其赋值为null或者default来实现自增

　　2，的第二种方法：

　　INSERT [INTO] tb1_name SET col_name={expr|DEFAULT},……

　　说明：与第一种方式的区别在于，此方法可以使用子查询(SubQuery);且只能一次性插入一条记录

　　例：INSERT users SET username='Ben',password='456';

　　十四，update方法

　　更新记录UPDATE，有两种方式，单表和多表

　　1.单表更新：

　　UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1={exp1|DEFAULT}[,col_name2=...][WHERE where_condition] (一般来说要用WHERE指定位置，不然所有数据都会被更新)

　　例：

　　不指定位置：UPDATE users set age = age + 5,sex = 0; 使表里所有记录的age加5

　　指定位置： UPDATE users set age = age+ + 10 WHERE id % 2=0; 取id为偶数的位置

　　update更新数据

　　update <表名>

　　set <列名>=<表达式>，[,<列名>=<表达式>]...

　　[where <条件>];

　　十五，group by方法

　　查询结果分组：

　　group by{col_name|position}

　　select * from users group by sex;

　　select * from users group by 1;(这里的一表示查询的第一个字段，这里查询所有，第一个字段就是id，也就是会按照字段进行分组)

　　ASC 升序默认 desc 降序

　　SELECT sex from users GROUP BY sex 按照sex进行分组。

　　十六，select方法

　　select version();

　　select database();

　　select now();

　　select 表达式;(只是计算表达式返回结果不会依赖某个表)

　　select columns from 表：(显示表的结构)

　　select 字段，字段，.. from 表;(selecth后面指定的字段名影响返回结果的先后顺序)

　　select 表1.字段，表2.字段.. from 表;

　　select id AS userid, 字段 as 字段别名.... from 表。 (as对字段别名返回结果，不加AS后面的会默认是别名)

　　十七，limit方法

　　select * from users limit 2 ;从第一条开始返回，返回前两个;

　　select * from users limit 3,2 ;忽略前三条，从第四条开始，取前两条;//mysql从0开始计数和id无关

　　将查询出的数据插入到指定表的指定字段中，支持多条数据：

　　insert table_name(column_name) select column_name from table_name1;

　　例：INSERT test(username) SELECT username FROM users WHERE age >=30;

　　mysql分页要用到：

　　[LIMIT{[offset,]row_count|row_count OFFSET offset}]

　　十八，order by方法

　　对查询结果进行排序，默认是升序

　　order by{col_name}

　　select * from users order by id desc; //对一个字段排序

　　select * from users order by age,id desc; //两个字段同时排序

　　desc是降序

　　1、对查询结果进行排序：[ORDER BY [col_name | expr | position } [ASC|DESC],...]elect * from user order by id desc;

　　2、可以同时按多条字段进行排序，规则是先按前面的字段排，在基础上再按后面字段排。

　　3、如：SELECT * FROM users ORDER BY age,id DESC; 先按照age排序，如果age有重复的，重复的字段里按id排序