数据库基本操作

1、操作数据库：

                 

1. C(Create)：创建
   • 创建数据库

create database 数据库名称;

• • 创建数据库，判断不存在，再创建

create database if not exists 数据库名称;    //常用

• • 创建数据库，并指定字符集

create database [if not exists] 数据库名称 character set 字符集名;

           创建db4数据库，判断是否存在，并制定字符集为gbk。

           create database if not exists db4 character set gbk;

1. R(Retrieve)：查询
   • 查询所有数据库的名称

show databases;

• • 查询某个数据库的字符集（查询某个数据库的创建语句）

show create database 数据库名称;

1. A(Alter)：修改
   • 修改数据库的字符集

alter database 数据库名称 character set 字符集名称;

1. D(Drop)：删除
   • 删除数据库

drop database 数据库名称;

• • 判断数据库存在，存在再删除

drop database if exists 数据库名称;        //常用

1. U(Use)：使用数据库
   • 查询当前正在使用的数据库名称

select database();   或者\s

• • 使用数据库

use 数据库名称;

2、操作数据表：

                 

1. C(Create)：创建
   1. 创建表

语法：create table if exist表名( 列名1 数据类型1 [约束],

列名2 数据类型2 [约束] , ...... );

1. 1. 复制表

语法：1）create table 表名 like 被复制的表名;  //仅仅复制表的结构(里面无数据)

1. create table 表名 select * from 被复制的表名; //复制原表结构+所有数据

3）create table 表名 select 字段名from 被复制的表名 [where 条件];      //复制原表结构+部分数据（单个或多个字段名）

4）create table 表名 select 字段名 from 被复制的表名where 0;  （或）

create table 表名 select 字段名 from 被复制的表名where 1 = 2;

//仅仅复制某些字段  where 0，where 1=2均为恒不成立, where 1=1为恒成立

注意：  

         复制表可以跨库，只需  库名.表名  即可。

3   创建临时表

语法：CREATE TEMPORARY TABLE 表名(字段名 字段类型,...);

特征：

1. 临时表的创建语法需要用到关键字 TEMPORARY           

2. 临时表创建完成之后，只有当前连接可见，其他连接是看不到的，具有连接隔离性；

3. 临时表在当前连接结束之后，会被自动删除。

1. R(Retrieve)：查询
   1. 查询某个数据库中所有的表名称

show tables;

1. 1. 查询表结构

DESCRIBE 表名;或 DESC 表名;

SHOW  [FULL] COLUMNS FROM 数据表名

1. A(Alter)：修改
   1. 修改表名

alter table 旧表名 rename to 新表名;

或者

RENAME TABLE 旧表名1 TO 新表名1[, 旧表名2 TO 新表名2] ...;

1. 1. 修改字段名，字段属性

ALTER TABLE 表名 CHANGE 旧字段名  新字段名 新数据类型;

1. 1. 修改字段的数据类型

ALTER TABLE 表名 MODIFY 字段名  数据类型;

1. 1. 添加字段

ALTER TABLE 表名 ADD  新字段名  数据类型       [约束条件][FIRST|AFTER 已存在字段名]

或者

ALTER TABLE 数据表名  ADD [COLUMN] (新字段名1 字段类型1, 新字段名2 字段类型2, ...);

1. 1. 删除列

ALTER TABLE 表名 DROP 字段名;

1. 1. 修改字段的排列位置

ALTER TABLE表名 MODIFY 字段名1 数据类型 FIRST|AFTER 字段名2

1. 1. 修改表的存储引擎

ALTER TABLE 数据表名  ENGINE=新的存储引擎类型

1. 1. 修改表的字符集

ALTER TABLE 数据表名  [DEFAULT] CHARSET=新的字符集

1. D(Drop)：删除
   1. 删除表

drop table 表名;

1. 1. 判断表存在，存在再删除

drop table if exists 表名;     //常用

3、数据操纵：

（1）I(Insert)：插入

1 为所有字段添加数据

INSERT INTO 表名(字段名1，字段名2，……)  VALUES(值1，值2，……);

或者

INSERT INTO 表名 VALUES(值1，值2，……);   

2 为部分字段添加数据

   INSERT   INTO 表名(字段1,字段2，…)    VALUES(值1,值2,…)

或者

INSERT INTO 表名   SET 字段名1=值1[，字段名2=值2，……]

3 一次添加多行数据

INSERT INTO 表名[(字段名1，字段名2，……) ]       VALUES(值1，值2，……)，(值1，值2，……)，     … …     (值1，值2，……);

或者

INSERT [INTO] 数据表名1 [(字段列表)]  SELECT [(字段列表)] FROM 数据表名2;

4 主键冲突解决

1. 主键冲突更新：当插入数据的过程中若发生主键冲突，则插入数据操作利用更新的方式实现。

INSERT [INTO] 数据表名 [(字段列表)] {VALUES | VALUE} (字段列表)  ON DUPLICATE KEY UPDATE 字段名1 = 新值1[,字段名2 = 新值2] …;

1. 主键冲突替换：当插入数据的过程中若发生主键冲突，则删除此条记录，并重新插入。

REPLACE [INTO] 数据表名[(字段列表)] VALUES(值列表)

或者

REPLACE [INTO] 目标数据表名[(字段列表1)] SELECT (字段列表2) FROM 源表 [WHERE 条件表达式]

或者

REPLACE [INTO] 数据表名 SET 字段1=值1,字段2=值2,字段3=值3……

（2）U(Update)：更新

   UPDATE 表名  SET 字段名1 = 值1[，字段名2 = 值2，……]  [WHERE 条件表达式]

（3）D(Delete)：删除

DELETE FROM 表名 [WHERE 条件表达式]  

或者

TRUNCATE [TABLE] 表名

                 

4、约束：

（1）分类：

1 主键约束：primary key

2 非空约束：not null

3 唯一约束：unique

4 外键约束：foreign key

 5 默认约束：default 

 6 检查约束：check

（2）主键约束：primary key

       1在创建表时，添加主键约束

              create table stu(

                     id int primary key,-- 给id添加主键约束

                     name varchar(20)

              );

       2 删除主键

              ALTER TABLE stu DROP PRIMARY KEY;

       3 创建完表后，添加主键

        1) 列级约束

               ALTER TABLE stu MODIFY id INT PRIMARY KEY;

        2）表级约束

         ALTER TABLE stu ADD [CONSTRAINT 外键名] PRIMARY KEY (id);

（2）非空约束：not null，值不能为null

1 创建表时添加约束

              CREATE TABLE stu(

                     id INT,

                     NAME VARCHAR(20) NOT NULL -- name为非空

              );

2 创建表完后，添加name的非空约束

              ALTER TABLE stu MODIFY name VARCHAR(20) NOT NULL;

3  删除name的非空约束

              ALTER TABLE stu MODIFY name VARCHAR(20);

（3）唯一约束：unique，值不能重复，可以为空。

       1创建表时，添加唯一约束

              CREATE TABLE stu(

                     id INT,

                     phone_number VARCHAR(20) UNIQUE -- 添加了唯一约束

              );

       2 删除唯一约束

              ALTER TABLE stu DROP INDEX phone_number;

       3 在创建表后，添加唯一约束

              ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;

（4）默认约束：DEFAULT，当在表中插入一条新记录时，如果没有给这个字段赋值，那么，数据库系统会自动为这个字段插入默认值。

       1创建表时，添加默认约束

              CREATE TABLE stu(

                     id INT default 1, -- 添加了默认约束，默认值为1

                     phone_number VARCHAR(20)

              );

       2 删除默认约束

  ALTER TABLE  stu  MODIFY id int;

       3 在创建表后，添加默认约束

              ALTER TABLE  stu  MODIFY id int  DEFAULT  10;

（5）自动增长：如果某一列是数值类型的，使用 auto_increment 可以来完成值得自动增长，默认从1开始。

       1 在创建表时，添加主键约束，并且设置主键自增长

              create table stu(

                     id int primary key auto_increment,-- 给id添加主键约束

                     name varchar(20)

              );

2 删除自动增长

              ALTER TABLE stu MODIFY id INT;

3 添加自动增长

              ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;

4 设置自动增长值

              ALTER TABLE stu AUTO_INCREMENT = 新值;

  

5、索引：

（1）分类：

1 普通索引：不应用任何限制条件的索引，可以在任何数据类型中创建。

2 唯一性索引：使用UNIQUE参数可以设置唯一索引。创建该索引时，索引的值必须唯一。

3 单列索引：只对应一个字段的索引。

4 多列索引：在表的多个字段上创建一个索引。

5 全文索引：使用FULLTEXT参数可以设置索引为全文索引。全文索引只能创建在CHAR、VARCHAR或者TEXT类型的字段上。

6 空间索引：使用SPATIAL参数可以设置索引为空间索引。空间索引只能建立在空间数据类型上。

（2）创建索引

1创建表的时候创建索引：

CREATE TABLE 表名（字段名 数据类型[完整性约束条件],

                     字段名 数据类型[完整性约束条件],

                     ．．．．．．

　　　　　　 字段名 数据类型

　　　　　［UNIQUE|FULLTEXT|SPATIAL］ INDEX|KEY

                    [索引名] (字段名 [(长度)]  [ASC|DESC]));

2 使用CREATE INDEX 语句在已经存在的表上创建索引

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名  ON 表名 (字段名 [(长度)] [ASC|DESC]);

3 使用ALTER TABLE语句在已经存在表上创建索引

ALTER TABLE 表名 ADD [UNIQUE|FULLTEXT|SPATIAL]  INDEX  索引名 (字段名 [(长度)] [ASC|DESC])；

（3）删除索引

      DROP INDEX 索引名 ON 表名;

ALTER TABLE 表名 DROP INDEX 字段名

（4）查看索引

SHOW INDEX FROM table_name\G

6、单表查询：

（1）语法格式：

        SELECT  [DISTINCT] *|{字段名1, 字段名2, 字段名3,……}

                       FROM 表名

                [WHERE 条件表达式1]

                [GROUP BY 字段名 [HAVING 条件表达式2]]

                [ORDER BY 字段名 [ASC|DESC]]

                [LIMIT [OFFSET] 记录数]

1查询所有字段

SELECT 字段名1,字段名2,…… FROM  表名;

或

SELECT  * FROM  表名;

2 查询指定字段

SELECT 字段名1,字段名2,…… FROM  表名;

3 查询指定数据

1. 带关系运算符的查询：关系运算符包括>,>=,<,<=,=,<>,!=

SELECT    *|字段名1,字段名2,……

    FROM 表名

    WHERE 条件表达式   

1. 带IN关键字的查询

SELECT *|字段名1,字段名2,……

FROM 表名

WHERE 字段名 [NOT] IN (元素1,元素2,……)

1. 带BETWEEN  AND的范围查询

SELECT *|{字段名1,字段名2,……}

FROM 表名

WHERE 字段名 [NOT] BETWEEN 值1 AND 值2

1. 带LIKE关键字的字符匹配查询:匹配符有%和_，%可以匹配任意长度的字符串，_只匹配一个字符。

SELECT *|{字段名1,字段名2,……}

FROM 表名

WHERE 字段名 [NOT] LIKE '匹配字符串';

1. 用IS NULL关键字查询空值

SELECT *|字段名1,字段名2,……

FROM 表名

WHERE 字段名IS [NOT] NULL

1. 带AND关键字的多条件查询

SELECT *|{字段名1,字段名2,……}

FROM 表名

WHERE 条件表达式1 AND 条件表达式2

[…… AND 条件表达式n];

1. 带OR关键字的多条件查询

SELECT *|{字段名1,字段名2,……}

FROM 表名

WHERE 条件表达式1 OR 条件表达式2

[…… AND 条件表达式n];

4 用DISTINCT关键字去掉结果中的重复行

SELECT DISTINCT 字段名1,字段名2,……

FROM 表名;

5 用ORDER BY关键字对查询结果排序: 升序ASC，降序DESC

SELECT *|{字段名1,字段名2,……}

FROM 表名

ORDER BY 字段名1 [ASC | DESC]，

字段名2 [ASC | DESC]……；

6 用GROUP BY关键字分组查询

 SELECT 字段名1,字段名2,……

     FROM 表名

     GROUP BY 字段名1，字段名2，……

[HAVING 条件表达式];

7 用LIMIT限制查询结果的数量

“OFFSET”：为可选值，表示偏移量，如果偏移量为0则从查询结果的第一条记录开始…以此类推，如果不指定其默认值为0。 “记录数”表示返回查询记录的条数。

SELECT 字段名1,字段名2,……

     FROM 表名

     LIMIT [OFFSET，] 记录数

8 聚合函数查询

聚合函数的结果值只根据选定行中非NULL的值进行计算，NULL值被忽略。

函数名	描述
COUNT()	返回所选择集合中非NULL值的行的数量
SUM()	返回参数字段之和
AVG()	返回参数字段的平均值
MAX()	返回参数字段的最大值
MIN()	返回参数字段的最小值

7、多表查询：

（1）连接查询：当两张或多张数据表中存在公共的字段时，如果需要同时显示多张数据表的数据，便可以通过这些公共的字段将不同的数据表进行连接，并对连接后的数据表进行查询。

（2）连接查询分类：

1. 交叉连接查询：将表1的每一行数据都与表2的每一行数据进行组合，返回结果中包含表1和表2的所有列。

SELECT 查询字段 FROM 表1 CROSS JOIN 表2

或者

SELECT 查询字段 FROM 表1 ，表2

1. 内连接查询：根据连接条件，可以对交叉连接查询的部分结果进行筛选，仅筛选出两张表中相互匹配的记录。

• 非等值内连接（Non-Equi Join）是指连接条件中使用除等号以外的运算符实现连接，如大于号“>”、“<”、“>=”、“<=”等，将两个表中满足条件的数据行匹配在一起。
• 等值内连接（Equi Join）是指连接条件中使用等号“=”进行连接，将两个表中相同的数据行匹配在一起。

1. 隐式内连接是指在FROM子句中直接写入需要连接的表，并使用WHERE子句来指定连接条件。

SELECT 表1.col_name1, 表2.col_name2

FROM 表1，表2    WHERE

表1 .col_nameX = 表2 .col_nameX

1. 显式内连接是指在FROM子句中使用JOIN关键字，并在ON子句中明确指定连接条件。

SELECT 表1.col_name1, 表2.col_name2

FROM 表1 [INNER] JOIN 表2 ON

表1 .col_nameX = 表2 .col_nameX

1. 自连接：表与其自身进行笛卡儿积连接、根据相同名称的字段进行记录匹配，查询结果仅包含符合连接条件与筛选条件的行。为了区别该表的每一次出现， 需要为表分别定义别名。

SELECT B.col_name FROM table1 [as] A

[INNER]  JOIN  table1 [as] B

ON A.col_nameX= B. col_nameX

WHERE  condition

1. 外连接查询：如果需要查询结果不仅包含符合连接条件的行，而且还包括左表、右表或两个连接表中的所有数据行，则应该使用外连接查询。支持的外连接有两种类型：

• 左外连接查询（左连接 LEFT [OUTER] JOIN）：也称为左连接，可以将左侧表中的所有行和右侧表中的匹配行合并在一起，并在右侧表中无匹配行的情况下，用 NULL 填充右侧表中的列。
  SELECT

table_name1 .col_name1, table_name2.col_name2,…FROM  table_name1

LEFT  [OUTER] JOIN

table_name2 ON

table_name1.col_nameX= table_name2. col_nameX;

• 右外连接查询（右连接 RIGHT[ OUTER] JOIN）：也称为右连接，可以将右侧表中的所有行和左侧表中的匹配行合并在一起，并在左侧表中无匹配行的情况下，用 NULL 填充左侧表中的列。

SELECT

table_name1. col_name1, table_name2. col_name2,…

FROM table_name1

RIGHT  [OUTER] JOIN table_name2

ON table_name1 .col_nameX= table_name2 . col_nameX;

1. 复合条件连接查询：在连接查询时，也可以增加其它的限制条件，通过多个条件的复合查询，可以使查询结果更加准确。