首页手记 MySql零距离接触总结

MySql零距离接触总结

标签：

Java

一，mysql的初涉
1，下载并安装mysql了，这在网上有很多的教程很详细的，大家可以自行百度。
在登录到mysql之后，可以使用如下的命令来显示当前mysql的信息：
SELECT VERSION(); #显示当前服务器的版本
SELECT NOW(); #显示当前日期时间
SELECT USER(); #显示当前用户
SHOW DATABASES; #显示系统当前的数据库
2，对数据库进行操作
创建数据库：CREATE {DATABASE} [IF NOT EXISTS] 数据库名字 CHARACTER SET [=] chaeacter_name;
如：CREATE DATABASE base_t1;
修改数据库：ALTER {DATABASE} 数据库名字 CHARACTER SET [=] chaeacter_name;
如：ALTER DATABASE base_t1 CHARACTER SET = uft8; #将数据的字符格式修改成uft8
删除数据库：DROP {DATABASE | SCHEMA} [IF EXISTS] 数据库名字;
如：DROP DATABASE base_t1 ;
显示当前所有的数据库：SHOW DATABASES;

二：数据表的基本操作
1，先使用USE打开数据
USE 数据库名字; 如：USE base_t1; 打开数据库名为base_t1的数据库
2，创建数据表
CREATE TABLE [IF NOT EXISTS] table_name(
column_name data_type,...)
如：CREATE TABLE tb1(id TINYINT ,username VARCHAR(20),sarary float(8,2));
3，删除数据表
DROP TABLE table_name;
如： DROP TABLE tb1;

4，修改数据表
     修改表中的列定义：ALTER TABLE tbl_name MODIFY [COLUMN] col_name            column_definition [FIRST | AFTER col_name].
     例如：ALTER TABLE users2 MODIFY id SMALLINT UNSIGNED NOT NULL FIRST;//修改id字段的数据类型和在表users2中所处的位置
      修改列名称：ALTER TABLE tbl_name CHANGE [column] old_col_name new_col_name column_defintion [FIRST | AFTER col_name].
     例如：ALTER TABLE user CHANGE id p_id TINYINT NUSIGNED NOU NULL;  #将user表中的字段id改成p_id
      给数据表更名:ALTER TABLE tbl_name RENAME new_tbl_name;
      例如：ALTER TABLE users2 RENAME users3;//把表名users2改为新的users3;

5,查看数据表以及数据的表结构：
    SHOW TABLES[FROM db_name] [LIKE 'pattern' | WHERE expr]
    如：SHOW TABLES;  #显示当前数据库下的数据表
    显示某个数据表的表结构
    SHOW COLUMNS FROM 数据表名;
    如： SHOW COLUMNS FROM tb1;

6，数据表的约束：
    NULL | NOT NULL :空与非空约束
    AUTO_INCREMENT: 自动编号，默认情况，起始为1，每次增1
    PRIMARY KEY:主键约束，一张表一个主键，具有唯一性，且自动为  NOT NULL
    UNIQUE KEY:唯一约束，可以多个，可以为空，
    DEFAULT :默认约束，在插入记录时，若没有赋值，会自动赋予默认值
 如：CREATE TABLE tb2(id SAMLLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, username VARCHAR(20) NOT NULL UNIQUE KEY, sex ENUM('1','2','3') DEFAULT '1');

三，数据表的修改及其约束
对一个数据列的建立的约束为列级约束；对多个数据列的建立的约束为表级约束。
1，外键约束（实际开发时基本不用）
FOREIGN KEY (子表字段) REFERENCES 父表（父表字段）
2，修改数据表
添加单列： ALTER TABLE tbl_name [COLUMN] col_name column_definition[FIRST | AFTER col_name];
例如： ALTER TABLE tb2 ADD password VARCHAR(20) NOT NULL AFTER id; #在tb2中添加password 字段，并且放在id字段后面
删除单列： ALTER TABLE tbl_name DROP [COLUMN] col_name;
例如： ALTER TABLE tb2 DROP password ;

    3，添加和删除约束
    a，添加和删除主键约束
         添加：ALTER TABLE tbl_name ADD PRIMARY KEY (index_col_name,...);
     例如：ALTER TABLE user2 ADD PRIMARY KEY(id);
         删除： ALTER TABLE tbl_name DROP PRIMARY KEY 
       例如：ALTER TABLE user2 DROP PRIMARY KEY(id);
   b， 添加/删除唯一约束：
      添加：ALTER TABLE tbl_name ADD UNIQUE (...)
      例如：ALTER TABLE user2 ADD UNIQUE(username);
      删除：ALTER TABLE tbl_name DROP {INDEX|KEY} index_name;
      例如：ALTER TABLE user2 DROP INDEX username;(需要知道username的   索引)
    c, 添加/删除外键约束
     添加：ALTER TABLE tbl_name ADD FOREIGN KEY (index_col_name) REFERENCES reference_definition.(index_col_name);
    例如：ALTER TABLE user2 ADD FOREIGN KEY(id) REFERENCES province (pid);
     删除：ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol
     例如：ALTER TABLE user2 DROP FOREIGN KEY user2_ibfk_1;
     d,添加/删除默认约束
     添加/删除：ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DAFAULT};
     例如：ALTER TABLE user2 ALTER age SET DEFAULT 15; #为user2表的age字段添加默认约束15.
     例如：ALTER TABLE user2 ALTER age DROP DEFAULT； #删除了age字段的默认约束。

四，操作数据表的记录
1,插入记录：INSERT [INTO] tbl_name [(col_name,...)]{VALUES}();
例如：INSERT users VALUES(NULL,'tons',23); 注意列数目的匹配。
也可以一次插入多个值：
INSERT users VALUES(NULL,'tons',23),(DEFAULT,'Rose',23,md5('2334'));
INSERT [INTO] tbl_name [(col_name,...)] VALUES(val,;;;;);
若是省略插入的字段名，则不能省略要插入所有字段。
INSERT tb1 VALUES('TOM',25,6782.12)
当我们仅仅是为某一些字段赋值时，就必须将字段写上。INSERT tb1(username,salary) VALUES('jOIN',3398.98);
也可以通过查询条件将查询的结果数据插入到数据表中：
例如：INSERT test (username) SELECT username FROM users WHERE age>30; 即将users表中年纪大于30的用户的名字插入到test表中。
2,更新记录(即修改某条记录)
UPDATE table_reference SET col_name = {expr}[,col_name={expr2}][where ..]
例如：UPDATE user SET age+5 where id=3;将id为3的用户的年纪加上5

3，删除记录
DELETE FROM tbl_from [WHERE ...] 若省略where语句，则删除所有记录
例如：DELETE FROM users where id =4;删除id为4的那个字段。

4，查询数据表（重头戏）
   SELECT select_expr[,select_expr]
   [  FROM table_reference [WHERE where_condition] 
     [GROUP BY {col_name | position}] [ASC|DESC] [HAVING where_condition]
     [ORDER BY {col_name | expr | position} [ASC|DESC] ]
     [LIMIT {[offset,] row_count | row_count OFFSET offset}]
   ]
   例如：SELECT id,username,age FROM users WHER age =21 GROUP BY id ASCI;   查询users表中年纪为21的用户的 id,username,age并以id的大小升序排列。

GROUP BY 后面也可以跟位置，来表示要排序的字段。
要使用GROUP BY 后面的HAVING语句，就要保证HAVING 后的字段要么出现在了SELECT后面的语句中，要么HAVING 后面是一个聚合函数，如sum,avg等！
例如：SELECT age,username FROM users GROUP BY age HAVING count(id)>3;
还可以使用LIMIT来限制返回的结果：
例如：SELECT FROM users LIMIT 2; 查询users表中前两个记录。
也可以返回指定下标出的几个数据，如
SELECT FROM users LIMIT 3,2;//这里取得是第4,5条记录，因为记录的下标是从0开始的。

五，子查询与连接
首先改变客户端的数据数据显示格式：SET NAMES gbk;
子查询：指出现在其他SQL语句内的SELECT字句。
如：SELECT FROM t1 WHERE col1=(SELECT col2 FROM t2);
其中SELECT FROM t1 为外查询,SELECT col2 FROM t2 为子查询
子查询可以返回标量、一行、一列或子查询
1，使用比较运算符的子查询
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price >=(SELECT　ROUND(AVG(goods_price),2) FROM tdb_goods ); #显示商品价格大于商品平均价格的商品的名字，id和价格。
比较运算符的关键字：ANY,SOME,ALL。（ANY,SOME具有相同的意义）
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price > ANY(SELECT goods_price FROM tdb_goods WHERE goods_cate='超级本'); # 查询商品中大于类型为“超级本”的所有商品的id，商品名和商品价格。
2,使用IN或NOT IN引发的子查询
IN相当于ANY关键字，NOT IN相当于 NOT ALL运算：

3，使用INSERT...SELECT...语句
INSERT tab_goods_cates(cate_name ) SELECT goods_cate FROM tdb_goods GROUP BY goods_cate ; #将tdb_goods 数据表中的goods_cate 类型全部写入到tab_goods_cates表中的cate_name 中。

4，多表更新
UPDATE table_reference SET col_name1={expr1|DEFAULT} [,col_name={expr2|DEFAULT}]...[WHERE where_condition]
表的参照关系：
table_reference {[INNER | CROSS] JOIN | {LEFT|RIGHT}} [OUTER| JOIN]
table_reference ON conditional_expr
UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate=cate_name SET goods_cate = cate_id; #将表tdb_goods与表 tdb_goods_cates 内连接，并将tdb_goods中的goods_cate更换成tdb_goods_cates 中的cate_id。
若是两张表中想要连接的字段名字相同，连接时就需要给两张表起别名了，如：UPDATE tdb_goods AS t INNER JOIN tdb_goods_brands AS c ON t.brand_name=c.brand_name SET t.brand_name = c.brand_id; #将两张表内连接，并在连接时给表起别名，然后将t的brand_name改成c的brand_id。
这种连接被称作适时的外键。

5，连接
关系：table_reference {[INNER | CROSS] JOIN | {LEFT|RIGHT}} [OUTER| JOIN] table_reference ON conditional_expr;
连接包括内连接和外连接两种，外连接又包括左连接和右连接两种。
内连接表示左表和右表符合连接条件的记录：
SELECT goods_id,goods_name,cate_name FROM tdb_goods AS g INNER JOIN tdb_goods_cate AS c ON g.cate_id =c.cate_id; #连接两张表，将c表中的 cate_name在g表和c表cate_id相同时显示出来。
左外连接：显示左表中全部记录以及右表中符合连接条件的记录。
如下图所示，分别是，左外连接，右外连接，内连接