mysql中常用SQL语句
DDL相关：#创建数据库
MariaDB [(none)]> CREATE DATABASE testdb;
Query OK, 1 row affected (0.00 sec)
#查看数据库
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| testdb |
+--------------------+
5 rows in set (0.02 sec)
#使用testdb数据库，
MariaDB [(none)]> use testdb;
Database changed
SHOW CHARACTER SET;查看字符集
SHOW COLLATIONS;查看排序规则
创建表的三种方式：
(1) 直接创建；
CREATE TABLE tb_name (col1 datatype 修饰符, col2 datatype 修饰符) ENGINE=''
数据类型：
字符：
变长字符：VARCHAR(#), VARBINARY(#)
定长字符: CHAR(#), BINARY(#)
内建类型：ENUM, SET
对象：TEXT, BLOB
数值：
精确数值：
整型：INT
TINYINT, SMALLINT, INT, MEDIUMINT, BIGINT
十进制：DECIMAL
近似数值：
单精度: FLOAT
双精度: DOUBLE
日期时间型：
日期：DATE
时间：TIME
日期时间：DATETIME
时间戳：TIMESTAMP
年份：YEAR(2), YEAR(4)
修饰符：
所有类型适用：
NOT NULL
DEFAULT
PRIMARY KEY
UNIQUE KEY
数值型适用：
UNSIGNED
AUTO_INCREMENT
CREATE TABLE 'tbl_name' (col1 type1, col2 type2, ...)
可选：col type1
PRIMARY KEY(col1,...)#可以定义关联主键
INDEX(col1, ...)#以此字段为基础创建索引
UNIQUE KEY(col1, ...)
表选项：
ENGINE [=] engine_name
ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
(2) 通过查询现存的表创建；新表会被直接插入查询而来的数据；
CREATE TABLE tbl_name
[(create_definition,...)]
select_statement
(3) 通过复制现存的表的表结构创建；不复制数据；
CREATE TABLE tbl_name
{ LIKE old_tbl_name | (LIKE old_tbl_name) }
#创建表students，定义id为无符号非空int型，并且id是主键，name是VARCHAR非空型，最多容纳20字符，age是无符号tinyint型
MariaDB > CREATE TABLE students (id int UNSIGNED NOT NULL PRIMARY KEY, name VARCHAR(20) NOT NULL, age tinyint UNSIGNED);
Query OK, 0 rows affected (0.04 sec)
#查看表详细信息
MariaDB > DESC students;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id | int(10) unsigned | NO | PRI | NULL | |
| name| varchar(20) | NO | | NULL | |
| age | tinyint(3) unsigned | YES| | NULL | |
+-------+---------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
#定义联合主键---此次创建id和name组合起来为主键，这样只要求id+name和其他行不同即可
MariaDB > CREATE TABLE tbl2 (id int UNSIGNED NOT NULL, name VARCHAR(20) NOT NULL, age tinyint UNSIGNED, PRIMARY KEY(id,name));
Query OK, 0 rows affected (0.00 sec)
MariaDB > DESC tbl2;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id | int(10) unsigned | NO | PRI | NULL | |
| name| varchar(20) | NO | PRI | NULL | |
| age | tinyint(3) unsigned | YES| | NULL | |
+-------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
#查看正规创建命令：\G使用竖向格式
MariaDB > SHOW CREATE TABLE students\G;
*************************** 1. row ***************************
Table: students
Create Table: CREATE TABLE `students` (
`id` int(10) unsigned NOT NULL,
`name` varchar(20) NOT NULL,
`age` tinyint(3) unsigned DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
#查看表状态：
MariaDB > SHOW TABLE STATUS LIKE 'students'\G;
*************************** 1. row ***************************
Name: students
Engine: InnoDB
Version: 10
Row_format: Compact #compact表示使用紧致型存储
Rows: 0
Avg_row_length: 0
Data_length: 16384 #表中数据大小
Max_data_length: 0 #表的最大容量，此处0表示无上限
Index_length: 0 #索引大小（字节为单位）
Data_free: 10485760 #目前已分配但未被使用的空间
Auto_increment: NULL #下一次该增长到数字几
Create_time: 2015-10-27 21:57:09
Update_time: NULL
Check_time: NULL #最近一次执行checktable命令或myisamchk根据检查表的时间
Collation: latin1_swedish_ci #排序规则
Checksum: NULL #校验和
Create_options: #创建表时额外指明的选项
Comment:
1 row in set (0.00 sec)
#删除表：
DROP TABLE 'tbl_name';
修改表：
ALTER TABLE 'tbl_name'
字段：
添加字段：add
ADD col1 data_type
删除字段：drop
修改字段：alter, change, modify
索引:
添加索引：add
删除索引: drop
表选项
修改:
查看表上的索引：SHOW INDEXES FROM tbl_name;
#使用ADD在name段之后添加gender段，类型为枚举类型
MariaDB > ALTER TABLE students ADD gender ENUM('m','f') AFTER name;
Query OK, 0 rows affected (0.02 sec)
Records: 0Duplicates: 0Warnings: 0
MariaDB > DESC students;
+--------+---------------------+------+-----+---------+-------+
| Field| Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| id | int(10) unsigned | NO | PRI | NULL | |
| name | varchar(20) | NO | | NULL | |
| gender | enum('m','f') | YES| | NULL | |
| age | tinyint(3) unsigned | YES| | NULL | |
+--------+---------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
使用CHANGE修改表，注意之前是主键修改时不需要指明主键，UNSIGNED用于修饰int类型，所以必须放在int后
MariaDB > ALTER TABLE students CHANGE id sid int UNSIGNED NOT NULL PRIMARY KEY;
ERROR 1068 (42000): Multiple primary key defined
MariaDB > ALTER TABLE students CHANGE id sid int UNSIGNED NOT NULL;
Query OK, 0 rows affected (0.02 sec)
Records: 0Duplicates: 0Warnings: 0
MariaDB > DESC students;
+--------+---------------------+------+-----+---------+-------+
| Field| Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| sid | int(10) unsigned | NO | PRI | NULL | |
| name | varchar(20) | NO | | NULL | |
| gender | enum('m','f') | YES| | NULL | |
| age | tinyint(3) unsigned | YES| | NULL | |
+--------+---------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
#增加唯一键：唯一约束保证在一个字段或者一组字段里的数据与表中其它行的数据相比是唯一的。
MariaDB > ALTER TABLE students ADD UNIQUE KEY(name);
Query OK, 0 rows affected (0.22 sec)
Records: 0Duplicates: 0Warnings: 0
MariaDB > DESC students;
+--------+---------------------+------+-----+---------+-------+
| Field| Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| sid | int(10) unsigned | NO | PRI | NULL | |
| name | varchar(20) | NO | UNI | NULL | |
| gender | enum('m','f') | YES| | NULL | |
| age | tinyint(3) unsigned | YES| | NULL | |
+--------+---------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
#添加索引：如果创建索引时没给名字，则索引名为字段名。
索引是特殊数据结构；定义在查找时作为查找条件的字段；
创建索引：
CREATE INDEX index_name ON tbl_name (index_col_name,...);
删除索引：
DROP INDEX index_name ON tbl_name;
在age字段添加一个索引
MariaDB > ALTER TABLE students ADD INDEX(age);
Query OK, 0 rows affected (0.01 sec)
Records: 0Duplicates: 0Warnings: 0
#查看索引
MariaDB > SHOW INDEXES FROM students\G;
*************************** 1. row ***************************
Table: students
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: sid
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: students
Non_unique: 0
Key_name: name
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 3. row ***************************
Table: students
Non_unique: 1
Key_name: age
Seq_in_index: 1
Column_name: age
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
3 rows in set (0.00 sec)
#删除索引：(索引一旦不使用应该即刻删除，否则会影响数据库性能)
DROP INDEX name ON students;
查看是否删除成功：
MariaDB > SHOW INDEX FROM students\G;
*************************** 1. row ***************************
Table: students
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: sid
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.00 sec)
#删除字段：
MariaDB > ALTER TABLE students DROP age;
页:
[1]