文章目录
1、创建和管理数据库
1.1 创建数据库
CREATE DATABASE dbname;
# 创建数据库
CREATE DATABASE testdb;
# 创建数据库并指定字符集
CREATE DATABASE testdb CHARACTER SET UTF8;
# 判断数据库是否已经存在,不存在则创建数据库
CREATE DATABASE IF NOT EXISTS testdb;
1.2 查看数据库
- 注意:要操作表格和数据之前必须先说明是对哪个数据库进行操作,否则就要对所有对象加上“数据库名.”。
查看当前所有的数据库
SHOW DATABASES;
查看当前正在使用的数据库
SELECT DATABASE();
查看指定库下所有的表
SHOW TABLES FROM atguigudb;
查看数据库的创建信息
SHOW CREATE DATABASE atguigudb;
使用/切换数据库
USE atguigudb;
SELECT DATABASE();
USE testdb;
SELECT DATABASE();
1.3 修改数据库
- 注意:DATABASE 不能改名。一些可视化工具可以改名,它是建新库,把所有表复制到新库,再删旧库完成的。
- 更改数据库字符集
更改数据库字符集
ALTER DATABASE testdb CHARACTER SET gbk;
ALTER DATABASE testdb CHARACTER SET utf8;
1.4 删除数据库
方式1:删除指定的数据库
DROP DATABASE test2;
方式2:删除指定的数据库
DROP DATABASE IF EXISTS test2;
# 示例
CREATE DATABASE test2;
SHOW DATABASES;
DROP DATABASE test2;
SHOW DATABASES;
DROP DATABASE IF EXISTS test2;
SHOW DATABASES;
2、 创建表
2.1 创建表CREATE
- 必须具备:CREATE TABLE权限、存储空间
CREATE TABLE emp (
emp_id INT,
emp_name VARCHAR(20),
salary DOUBLE,
birthday DATE
);
DESC emp;
CREATE TABLE dept(
deptno INT,
dname VARCHAR(14),
loc VARCHAR(13),
-- 主键
PRIMARY KEY(deptno));
DESC dept;
SHOW TABLES FROM testdb;
2.2 创建表AS
- 将创建表和插入数据结合起来
- 指定的列和子查询中的列要一一对应
- 通过列名和默认值定义列
CREATE TABLE emp1 AS SELECT * FROM atguigudb.employees;
CREATE TABLE emp2 AS SELECT * FROM atguigudb.employees WHERE 1=2;
-- 创建的emp2是空表
CREATE TABLE dept80
AS
SELECT employee_id, last_name, salary*12 ANNSAL, hire_date
FROM atguigudb.employees
WHERE department_id = 80;
2.3 查看表结构
- DESCRIBE/DESC 语句查看数据表结构
- SHOW CREATE TABLE 语句查看数据表结构:不仅可以查看表创建时的详细语句,还可以查看存储引擎和字符编码。
DESC dept80;
SHOW CREATE TABLE dept80;
3、修改表
- 使用 ALTER TABLE 语句可以实现修改数据库中已经存在的数据表的结构。
- 向已有的表中添加列、修改现有表中的列、删除现有表中的列、重命名现有表中的列。
3.1 添加列 ALTERT TABLE ADD
# 语法格式如下:
ALTER TABLE 表名 ADD 【COLUMN】 字段名 字段类型 【FIRST|AFTER 字段名】;
# 举例:
ALTER TABLE dept80
ADD job_id VARCHAR(15);
3.2 修改列 ALTER TABLE MODIFY
- 可以修改列的数据类型,长度、默认值和位置
# 修改字段数据类型、长度、默认值、位置的语法格式如下:
ALTER TABLE 表名 MODIFY 【COLUMN】 字段名1 字段类型 【DEFAULT 默认值】【FIRST|AFTER 字段名2】;
ALTER TABLE dept80
MODIFY last_name VARCHAR(30);
3.3 重命名列 ALTER TABLE CHANGE
- 使用 CHANGE old_column new_column dataType子句重命名列。
# 语法格式如下:
ALTER TABLE 表名 CHANGE 【column】 列名 新列名 新数据类型;
ALTER TABLE dept80
CHANGE ANNSAL ann_sal VARCHAR(15);
3.4 删除列 ALTER TABLE DROP
- 删除表中某个字段的语法格式如下:
ALTER TABLE 表名 DROP 【COLUMN】字段名
ALTER TABLE dept80
DROP COLUMN job_id;
4、重命名表 ALTER TABLE RENAME
RENAME TABLE emp
TO myemp;
ALTER TABLE dept
RENAME TO detail_dept;
ALTER TABLE dept
RENAME detail_dept;
5、删除表DROP TABLE
- 当一张数据表没有与其他任何数据表形成关联关系时,可以将当前数据表直接删除。
- 数据和结构都被删除
- 所有正在运行的相关事务被提交
- 所有相关索引被删除
- DROP TABLE 语句不能回滚
# 语法格式:
DROP TABLE [IF EXISTS] 数据表1 [, 数据表2, …, 数据表n];
DROP TABLE emp2;
SHOW TABLES;
6、清空表 DELETE FROM
- TRUNCATE TABLE语句:
删除表中所有的数据
释放表的存储空间 - TRUNCATE语句不能回滚
- DELETE 语句删除数据,可以回滚
TRUNCATE TABLE myemp;
SELECT * FROM dept80;
DELETE FROM dept80;
SELECT * FROM dept80;
ROLLBACK;
SELECT * FROM dept80;
7、练习题目
-- ---------------------------------------------------------------1
#1. 创建数据库test01_office,指明字符集为utf8。并在此数据库下执行下述操作
CREATE DATABASE test01_office CHARACTER SET utf8;
CREATE DATABASE IF NOT EXISTS test01_office;
CREATE DATABASE IF NOT EXISTS test01_office CHARACTER SET utf8;
SHOW DATABASES;
#2. 创建表dept01
/*
字段 类型
id INT(7)
NAME VARCHAR(25)
*/
USE test01_office;
SELECT DATABASE();
CREATE TABLE dept01 (
id INT(7),
name VARCHAR(10)
);
SHOW TABLES FROM test01_office;
DESC dept01;
#3. 将表departments中的数据插入新表dept02中
SELECT DATABASE();
CREATE TABLE dept02
AS
SELECT * FROM atguigudb.departments;
SHOW TABLES FROM test01_office;
DESC dept02;
#4. 创建表emp01
/*
字段 类型
id INT(7)
first_name VARCHAR (25)
last_name VARCHAR(25)
dept_id INT(7)
*/
CREATE TABLE emp01 (
id INT(7),
first_name VARCHAR(25),
last_name VARCHAR(25),
dept_id INT(7)
);
SHOW TABLES FROM test01_office;
DESC emp01;
#5. 将列last_name的长度增加到50
ALTER TABLE emp01
MODIFY last_name VARCHAR(50);
DESC emp01;
#6. 根据表employees创建emp02
CREATE TABLE emp02 AS
SELECT * FROM atguigudb.employees;
DESC emp02;
SHOW TABLES FROM test01_office;
#7. 删除表emp01
SHOW TABLES FROM test01_office;
DROP TABLE IF EXISTS emp01;
SHOW TABLES FROM test01_office;
#8.将表emp02重命名为emp01
ALTER TABLE emp02
RENAME emp01;
SHOW TABLES FROM test01_office;
#9.在表dept02和emp01中添加新列test_column VARCHAR(10),并检查所作的操作
ALTER TABLE dept02 ADD test_column VARCHAR(10);
ALTER TABLE emp01 ADD test_column VARCHAR(10);
DESC dept02;
DESC emp01;
#10.直接删除表emp01中的列 department_id
ALTER TABLE emp01
DROP department_id;
-- ---------------------------------------------------------------2
# 1、创建数据库 test02_market
CREATE DATABASE IF NOT EXISTS test02_market CHARACTER SET utf8;
SHOW DATABASES;
# 2、创建数据表 customers
-- c_num int
-- c_name varchar(50)
-- c_contact varchar(50)
-- c_city varchar(50)
-- c_birth date
SELECT DATABASE();
USE test02_market;
SELECT DATABASE();
CREATE TABLE customers (
c_cum INT,
c_name VARCHAR(50),
c_contact VARCHAR(50),
c_city VARCHAR(50),
c_birth DATE
);
# 3、将 c_contact 字段移动到 c_birth 字段后面
ALTER TABLE customers
MODIFY c_phone VARCHAR(50) AFTER c_birth;
DESC customers;
# 4、将 c_name 字段数据类型改为 varchar(70)
DESC customers;
ALTER TABLE customers
MODIFY c_name VARCHAR(70);
DESC customers;
# 5、将c_contact字段改名为c_phone
ALTER TABLE customers
CHANGE c_contact c_phone VARCHAR(50);
-- 使用change改名也必须声明字段类型
DESC customers;
# 6、增加c_gender字段到c_name后面,数据类型为char(1)
ALTER TABLE customers
ADD c_gender CHAR(1) AFTER c_name;
DESC customers;
ALTER TABLE customers
MODIFY c_gender CHAR(1) AFTER c_name;
# 7、将表名改为customers_info
ALTER TABLE customers
RENAME customers_info;
SHOW TABLES FROM test02_market;
# 8、删除字段c_city
ALTER TABLE customers_info
DROP c_city;
DESC customers_info;
-- ---------------------------------------------------------------3
# 1、创建数据库test03_company
CREATE DATABASE IF NOT EXISTS test03_company CHARACTER SET utf8;
SHOW DATABASES;
# 2、创建表offices
-- 字段名数据类型
-- officeCode int
-- city varchar(30)
-- address varchar(50)
-- country varchar(50)
-- postalCode varchar(25)
SELECT DATABASE();
USE test03_company;
CREATE TABLE offices (
officeCode INT,
city VARCHAR(30),
address VARCHAR(50),
country VARCHAR(50),
postalCode VARCHAR(25)
);
# 3、创建表employees
-- 字段名数据类型
-- empNum int
-- lastName varchar(50)
-- firstName varchar(50)
-- mobile varchar(25)
-- code int
-- jobTitle varchar(50)
-- birth date
-- note varchar(255)
-- sex varchar(5)
CREATE TABLE IF NOT EXISTS employees (
empNum INT,
lastName VARCHAR(50),
firstName VARCHAR(50),
mobile VARCHAR(25),
code INT,
jobTitle VARCHAR(50),
birth DATE,
note VARCHAR(255),
sex VARCHAR(5)
);
# 4、将表employees的mobile字段修改到code字段后面
ALTER TABLE employees
MODIFY mobile VARCHAR(25) AFTER code;
DESC employees;
# 5、将表employees的birth字段改名为birthday
ALTER TABLE employees
CHANGE birth birthday DATE;
# 6、修改sex字段,数据类型为char(1)
ALTER TABLE employees
MODIFY sex CHAR(1);
# 7、删除字段note
ALTER TABLE employees
DROP note;
# 8、增加字段名favoriate_activity,数据类型为varchar(100)
ALTER TABLE employees
ADD favoriate_activity VARCHAR(100);
DESC employees;
# 9、将表employees的名称修改为 employees_info
ALTER TABLE employees
RENAME employees_info;
RENAME TABLE employees_info TO employees;
SHOW TABLES FROM test03_company;