数据库上机实验一：操作命令

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）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. 查询表结构

DESCRIBE 表名;或 DESC 表名;

SHOW  [FULL] COLUMNS FROM 数据表名

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

alter table 旧表名 rename to 新表名;

或者

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

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

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

1. 修改字段的数据类型

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

1. 添加字段

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

或者

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

1. 删除列

ALTER TABLE 表名 DROP 字段名;

1. 修改字段的排列位置

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

1. 修改表的存储引擎

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

1. 修改表的字符集

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

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

drop table 表名;

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 -- 添加了唯一约束