MySQL操作详解

一、DOS 界面登录数据库 mysql -u用户名 -p密码 -h主机（mysql -uroot -p）
（1）mysql的默认端口号是：3306；
（2）MySQL中超级用户叫‘root’；

二、数据库操作：
1.显示数据库 SHOW DATABASES;
2.进入/打开数据库 USE 数据库名；
3.查看已打开的数据库 select datase();
4.创建数据库：CREATE DATABASES DB_NAME；
5.修改数据库：ALTER DATABASES DB_NAME；
6.删除数据库：DROP DATABASES DB_NAME；

三、表操作：
1.显示所有表 SHOW TABLES;
2.显示表结构 SHOW COLUMNS FROM table;
3.创建表 CREATE TABLE 表名（字段及信息）；
4.查看数据表中的索引信息 show indexes from tb1(表名);（表格显示）
show indexes from tb1\G;（网格显式）

四、数据类型
整型 （tinyint smallint mediumint int bigint）
浮点型 （FLOAT{[M,D]},DOUBLE{[M,D]}）M:总位数,D:小数点后位数
日期 （YEAR DATE TIME TIMESTEMP）
字符型 （CHAR VARCHAR TEXT）

插入数据 INSERT INTO table []VALUES()；[]为空说明插入全部的值
查询数据 SELECT 字段 FROM table WHERE 条件
自增自动编号 auto_increment

非空约束 NOT NULL
主键约束 primary key（有auto_increment的一定是主键，主键不一定有auto_increment）
唯一约束 unique key
默认约束 default 当没有赋值时则自动添加默认值
外键约束 FOREIGN KEY

CREATE TABLE tb1(
    -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -> username VARCHAR(20) NOT NULL UNIQUE KEY,
    -> sex ENUM('1','2','3') DEFAULT '3');

添加单列ADD：

ALTER TABLE person ADD password VARCHAR(32) not null default 123456 ;//(在person表末尾插入password列)
ALTER TABLE person ADD sex VARCHAR(10) not null after name;//(在person表name列之后插入sex列)
ALTER TABLE person ADD isMaried VARCHAR(10) not null first;//(在person表的最前面插入isMaried列)

添加多列ADD：

ALTER TABLE person ADD (isMaried VARCHAR(10),sex VARCHAR(10));//（在最后面添加列）

删除单列DROP：

ALTER TABLE person drop password;//（删除password列）
删除多列DROP：ALTER TABLE person drop password，drop age;//（同时删除password和age列）

添加主键约束：

ALTER TABLE person ADD CONSTRAINT PK_person_id primary key (id)；//（将id设置为主键）

添加唯一约束：

ALTER TABLE person ADD unique (name);//(将person表中的name字段设置为唯一约束)

添加外键约束：

ALTER TABLE person_1 ADD foreign key (pid) references person(ID);//(添加person_1的外键约束为person的ID键)

添加默认约束：

ALTER TABLE users2 ALTER age SET DEFAULT 15;

删除主键约束：

ALTER TABLE users2 drop primary key;//因为任何一张表只有一个主键，所以删除时不用加主键名称。

删除唯一约束：

ALTER TABLE users2 drop INDEX username;

删除外键约束：

ALTER TABLE users2 drop foreign key table2_ibfk_1;//用系统自动生成的foreign key

删除默认约束：

ALTER TABLE users2 ALTER age DROP DEFAULT;

修改数据表（尽量少使用，影响索引等）
修改列定义：

ALTER TABLE users2 MODIFY id tinyint unsigned not null first;//修改了数据类型(SMALLINT-tinyint)和位置(第三列到第一列)

修改列名称：

ALTER TABLE users2 CHANGE pid p_id tinyint unsigned not null after;//既可以修改列定义也可以修改列名称

数据表更名：

ALTER TABLE users2 RENAME users3;//将表名users2改为users3
RENAME TABLE users3 TO users2,table2 TO table1;//多表更名用逗号隔开

读写操作：
插入记录

insert tb1 values (NULL,'aaa',2); //为自动增长的默认值id赋值，可以用NULL或default
insert tb1 values (default,'bbb',1),(default,'eee',3);//连续插入用逗号隔开
insert tb1 values (default,'ccc',default); //为默认值sex赋值，只能用default，不能用NULL
insert into tb1 values (default,'ddd',NULL);//sex值变为NULL。into可以省略
insert tb1 SET username='fff',sex=1;
insert users2(username) SELECT username FROM users3 WHERE age>=30;//将查找的结果插入到指定数据表中

修改记录（单表更新、多表更新）

UPDATE users2 SET age = age+5;//将age列所有值加5操作
UPDATE users2 SET age = age+5,sex = 0 WHERE;//修改age和sex两列的所有值
UPDATE users2 SET age = age+10 WHERE id % 2 = 0;//修改偶数行的age值

删除记录（单表删除、多表删除）

DELETE  FROM users2 where id=3;

查找记录（SELECT语句字段出现的顺序和别名会影响查找的结果集）

SELECT id,username from tb1;SELECT username,id from tb1;SELECT tb1.username,id from tb1;
SELECT id AS tb1_id,username AS tb1_username from tb1;//AS后面的名字是前面名字的别名，并显示到查找的结果中，AS在单表中查询可以省略
SELECT id username from tb1;//省略AS，将username作为id的别名使用，在结果中显示一个字段。使用别名最好不要省略AS关键字

查询结果分组（GROUP BY）

SELECT age FROM users2 GROUP BY age;//可以查看users2中的age有哪些不同的值
SELECT * FROM users2 GROUP BY sex;//按性别分组，结果集中只出现性别首次不同的数据

分组条件（HAVING）

SELECT id,username,sex FROM tb1 GROUP BY id HAVING count(id) >= 1;

对查询结果进行排序（ORDER BY … ASE|DESC）

SELECT * FROM tb1 ORDER BY id DESC;//将序排列
SELECT * FROM tb1 ORDER BY sex,id DESC;//sex后面没有DESC，按默认顺序排序。sex没有重复，则忽略id的排序规则；sex有重复，重复部分将按id降序排列

限制查询结果返回的数量（LIMIT）

SELECT * FROM tb1 LIMIT 2;//查询前两条结果
SELECT * FROM tb1 LIMIT 3,2;//查询第三条开始前两条结果。注意SELECT语句从0开始编号，而自增的id 默认从1开始自增
SELECT * FROM tb1 ORDER BY id DESC LIMIT 2;//查询按id降序排列后的前两条结果

mysql> CREATE TABLE tdb_goods(
    -> goods_id smallint unsigned auto_increment primary key not null,
    -> goods_name varchar(150) not null,
    -> goods_cate varchar(40) not null,
    -> brand_name varchar(40) not null,
    -> goods_price decimal(15,3) unsigned not null default 0.000,
    -> is_show tinyint(1) not null default 1,
    -> is_saleoff tinyint(1) not null
    -> );

子查询（嵌套在查询内部，且必须在圆括号内）
子查询可以包含多个关键字（GROUP BY、ORDER BY、LIMIT函数等）
子查询外层查询可以是：SELECT、insert、UPDATE、SET或DO
子查询可以返回标量、一行、一列或子查询。
例如：SELECT * FROM t1 WHERE col1 = (SELECT col2 FROM t2);//(SELECT col2 FROM t2)为子查询
使用比较运算符（=、>、<、>=、<=、<>、!=、<=>）的子查询
比较运算符可以用ANY、SOME、ALL修饰，用法: operand ANY(subquery)。

SELECT AVG(goods_price) FROM tdb_goods;
SELECT ROUND(AVG(goods_price),2) FROM tdb_goods;//保留两位有效数字，结果为5636.36
SELECT goods_id,goods_name,goods_price from tdb_goods WHERE goods_price>=5636.36;
SELECT goods_id,goods_name,goods_price from tdb_goods WHERE goods_price>=(SELECT ROUND(AVG(goods_price),2) FROM tdb_goods);

使用[NOT]IN的子查询，用法和ANY等一样

CREATE TABLE IF NOT EXISTS tdb_goods_cates(
    cate_id smallint unsigned auto_increment primary key,
    cate_name varchar(40) not null);

SELECT goods_cate from tdb_goods GROUP BY goods_cate;//查找tdb_goods中所有的goods_cate
INSERT  tdb_goods_cates(cate_name) SELECT goods_cate from tdb_goods GROUP BY goods_cate;//将查询结果插入到tdb_goods_cates的cate_name中

多表更新
步骤一，建表tdb_goods_cates；步骤二，通过INSERT…SELECT把tdb_goods中的记录写入到新创建的表中；步骤三，用步骤二的表更新步骤一中的表
连接类型：INNER JOIN内连接；LEFT [OUTER] JOIN左外连接；RIGHT [OUTER] JOIN右外连接。//ON为连接条件，WHERE进行结果集的过滤

UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate=cate_name SET goods_cate=cate_id;//ON为INNER JOIN的连接条件，也可使用WHERE

//tdb_goods表内连接tdb_goods_cates表，当goods_cate=cate_name时，使goods_cate=cate_id

CREATE…SELECT创建数据表的同时将查询结果写入到数据表中

CREATE TABLE [IF NOT EXISTS] SELECT tbl_name[(create_definition)] select_statement;

例如：

CREATE TABLE IF NOT EXISTS tdb_goods_brands(
    brand_id smallint unsigned auto_increment primary key,
    brand_name varchar(40) not null)
    SELECT brand_name from tdb_goods GROUP BY brand_name;
//将tdb_goods表中查询到的所有的brand_name插入到tdb_goods_brands表的brand_name中

两张表出现同名字段都有brand_name的情况下按照上面的UPDATE语句，会出错（语意不明），这时可以通过给表取别名解决：

UPDATE tdb_goods AS g INNER JOIN      tdb_goods_brands AS b ON g.brand_name=b.brand_name SET g.brand_name=b.brand_id;

//上面两个UPDATE语句将tdb_goods中的goods_cate和brand_name虽然修改成tdb_goods_cates和tdb_goods_brands表对应的id，
//但是这两个id在tdb_goods中存储的类型却还是varchar(40)类型，而不是smallint unsigned类型
//此时需要使用CHANGE修改表中的定义和列名

ALTER TABLE tdb_goods CHANGE goods_cate cate_id smallint unsigned not null,
              CHANGE brand_name brand_id smallint unsigned not null);

//分别在tdb_goods_cates和tdb_goods_brands表中插入和tdb_goods表中不同的cate_name和brand_name

多表连接（一般用INNER JOIN）
连接类型区别：
1.A INNER JOIN B，内连接,显示A和B交集的部分，例如：

SELECT goods_id,goods_name,cate_name from tdb_goods AS g INNER JOIN tdb_goods_cates AS b ON g.cate_id=b.cate_id;

2.A LEFT [OUTER] JOIN B，左外连接，显示A表的全部记录及B表符合连接条件的记录，如果B中没有符合条件的记录则显示为NULL.例如：

SELECT goods_id,goods_name,cate_name from tdb_goods AS g LEFT JOIN tdb_goods_cates AS b ON g.cate_id=b.cate_id;

3.A RIGHT [OUTER] JOIN B，右外连接，显示B表的全部记录及A表符合连接条件的记录.

//tdb_goods和tdb_goods_cates、tdb_goods_brands三张表的连接

SELECT goods_id,goods_name,cate_name,brand_name,goods_price from tdb_goods AS g INNER JOIN tdb_goods_cates AS c ON g.cate_id=c.cate_id
                                        INNER JOIN tdb_goods_brands AS b ON g.brand_id=b.brand_id;

//无线分类的数据表设计

CREATE TABLE tdb_goods_types(
    type_id smallint unsigned auto_increment primary key,//分类id
    type_name varchar(20) not null,              //分类name
    parent_id smallint unsigned not null default 0       //父类id，通过自身来连接实现
);
insert tdb_goods_types(type_id,parent_id) values('家用电器',default);
insert tdb_goods_types(type_id,parent_id) values('电脑、办公',default);
insert tdb_goods_types(type_id,parent_id) values('大家电',1);//parent_id=1表示'大家电'属于type_id=1的'家用电器'
insert tdb_goods_types(type_id,parent_id) values('生活电器',1);
insert tdb_goods_types(type_id,parent_id) values('平板电视',3);
//查找，通过自身连接（假设有两张一模一样的表s=son,p=parent）
SELECT s.type_id,s.type_name,p.type_name from tdb_goods_types AS s 
    LEFT JOIN tdb_goods_types AS p ON s.parent_id = p.type_id;