5 SQL语句之DDL

千家信息网最后更新 2024年09月22日5 SQL语句之DDLDDL：Databse Definition Language一 数据库定义1 创建数据库CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification]
create_specification: [DEFAULT] CHARACTER SET [=] charset_name [DEFAULT] COLLATE [=] collation_nameexample1：
1.1 查看字符集gbk，和gbk支持的排序规则mysql> show character set like 'gbk';+---------+------------------------+-------------------+--------+| Charset | Description | Default collation | Maxlen |+---------+------------------------+-------------------+--------+| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |+---------+------------------------+-------------------+--------+mysql> show collation like 'gbk%';+----------------+---------+----+---------+----------+---------+| Collation | Charset | Id | Default | Compiled | Sortlen |+----------------+---------+----+---------+----------+---------+| gbk_chinese_ci | gbk | 28 | Yes | Yes | 1 || gbk_bin | gbk | 87 | | Yes | 1 |+----------------+---------+----+---------+----------+---------+mysql> create database if not exists students default character set = 'gbk' default collate = 'gbk_chinese_ci';
1.2 数据库默认字符集和排序规则
# cat db.optdefault-character-set=gbkdefault-collation=gbk_chinese_ci
2 修改数据库属性ALTER {DATABASE | SCHEMA} [db_name] alter_specificationALTER {DATABASE | SCHEMA} db_name UPGRADE DATA DIRECTORY NAME 升级数据库
alter_specification: [DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name
3 删除数据库DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

4 数据库重命名（少用）修改数据库文件下的目录名称，或者新建目录后复制数据，重启mysql

---

---

二 表定义1 创建表如何查看表的属性？mysql> show table status like 'lesson'\G
mysql> show table status \G;
如何查看表的字段？ mysql> desc table_name；
查看表内容mysql> select * from lesson;
1.1 直接定义一张空表；
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name(create_definition,...) [table_options] [partition_options]
table_option: ENGINE [=] engine_name | AUTO_INCREMENT [=] value | CHECKSUM [=] {0 | 1} |
DELAY_KEY_WRITE [=] {0 | 1} |
MAX_ROWS [=] value |
[DEFAULT] CHARACTER SET [=] charset_name |
[DEFAULT] COLLATE [=] collation_name
单字段： PRIMARY KEY UNIQUE KEY
单或多字段： PRAMARY KEY (col,...) UNIQUE KEY (col,...) INDEX (col,...)

mysql> create table course(course_id tinyint unsigned not null primary key,course_name char(20) not null) engine=MyISAM;
mysql> create table lesson(course_id tinyint unsigned not null auto_increment primary key,course_name char(20) not null) engine=MyISAM; auto_increment位置mysql> insert into lesson(course_name) value ('English'),('Maths'),('Music'),('Physics'),('Chemical'); 填入内容
mysql> select * from lesson; 查看表内容
mysql> show indexes from course\G 查看索引

1.2 从其它表中查询出数据，并以之创建新表；字段属性可能会改变
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name[(create_definition,...)] [table_options] [partition_options] select_statement
mysql> create table lesson_1 select * from lesson where course_id <= 3;

1.3 以其它表为模板创建一个空表；字段属性保持不变
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { LIKE old_tbl_name | (LIKE old_tbl_name) }example3：
mysql> create table test like course;

键也称作约束，可用作索引，属于特殊索引(有特殊限定)：B+Tree

---

2 修改表定义ALTER TABLE 添加、删除、修改字段 添加、删除、修改索引 改表名 修改表属性ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name [alter_specification [, alter_specification] ...] [partition_options]

alter_specification:table_options2.1 添加表字段 | ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name ] | ADD [COLUMN] (col_name column_definition,...)mysql> alter table lesson add starttime date default '2016-06-07';

2.2 添加索引的主见 | ADD {INDEX|KEY} [index_name] [index_type] (index_col_name,...) [index_option] ... | ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...) [index_option] ... | ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...) [index_option] ...mysql> alter table test1 add unique key (course_name);
| ADD FULLTEXT [INDEX|KEY] [index_name] (index_col_name,...) [index_option] ... | ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition
2.3 修改字段名称 | CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]
注意使用字段定义mysql> alter table test1 change course_name lesson_name char(20) not null;

2.4 修改字段属性 | MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name] | DROP [COLUMN] col_name | DROP PRIMARY KEY | DROP {INDEX|KEY} index_name如何删除unique ？ | RENAME [TO|AS] new_tbl_name
mysql> alter table test1 rename to test;
mysql> rename table test to test_1;

2.5 修改 | ORDER BY col_name [, col_name] ...
2.6 修改字符集和排序规则 | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name] | [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name

mysql> create table students(student_id tinyint unsigned not null auto_increment primary key,name char(20) not null,course_id tinyint not null);

2.7 插入数据mysql> insert into students (name,course_id) value ('Yang',2),('Zhang',3),('Wang',1);
mysql> select name,course_name from lesson,students where lesson.course_id=students.course_id; +-------+-------------+| name | course_name |+-------+-------------+| Yang | Maths || Zhang | Music || Wang | English |+-------+-------------+
mysql> show create table tutors \G*************************** 1. row *************************** Table: tutorsCreate Table: CREATE TABLE `tutors` ( `TID` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `Tname` varchar(50) NOT NULL, `Gender` enum('F','M') DEFAULT 'M', `Age` tinyint(3) unsigned DEFAULT NULL, UNIQUE KEY `TID` (`TID`)) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1

---

3 删除表
DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ... [RESTRICT | CASCADE]级联，危险
InnoDB支持外键mysql> alter table students add foreign key foreign_cid (course_id) references lesson (course_id);ERROR 1005 (HY000): Can't create table 'students.#sql-500b_8' (errno: 150)mysql> alter table lesson engine=InnoDB；

---

三 索引1 创建索引CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON tbl_name (index_col_name [(length)] [ASC | DESC],...) 【升序|降序】
2 删除索引：DROP [ONLINE|OFFLINE] INDEX index_name ON tbl_nameDROP INDEX `PRIMARY` ON t;
3 查看索引SHOW INDEXES FROM tb_name：显示指定表上的索引
mysql> create index index_on_name on students (name);
mysql> drop index index_on_name on students;
mysql> create index index_on_name on students (name (5) desc);



来自为知笔记(Wiz)