1.MySQL数据库服务配置好后，系统会有4个默认的数据库。
information_schema：虚拟对象，其对象都保存在内存中
performance_schema：服务器性能指标库
mysql：记录用户权限，帮助，日志等信息
test：测试库
查看当前的所有数据库：
show databases //只显示当前用户拥有权限访问的所有数据库
删除数据库
drop database if exists db_name
创建数据库
create database if not exist db_name
在操作返回的结果中有ERROR,WARNING，可用以下语句来查看
show errors or show warnings
查看数据库的创建脚本
show create database db_name or
select * from scheme_information.schemata
选定数据库
use mysql
查看某库中的所有表
show tables
表的创建，语法如下：
Syntax:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_options]
[partition_options] Or: CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
[table_options]
[partition_options]
select_statement Or: CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
{ LIKE old_tbl_name | (LIKE old_tbl_name) }
下面以student为表名来创建表
use test;
create table if not exists student(
id int not null primary key auto_increment,
sno char(11) not null unique,
sname varchar(50) not null
);
查看表的结构
desc student(tbl_name)
查看表的创建脚本
show create table student(tbl_name)
查看表的状态信息
show table status like 'student'
修改表的结构，使用ALTER命令，语法：
Syntax:
ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name
[alter_specification [, alter_specification] ...]
[partition_options] alter_specification:
table_options
| ADD [COLUMN] col_name column_definition
[FIRST | AFTER col_name ]
| ADD [COLUMN] (col_name column_definition,...)
| 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] ...
| ADD FULLTEXT [INDEX|KEY] [index_name]
(index_col_name,...) [index_option] ...
| ADD SPATIAL [INDEX|KEY] [index_name]
(index_col_name,...) [index_option] ...
| ADD [CONSTRAINT [symbol]]
FOREIGN KEY [index_name] (index_col_name,...)
reference_definition
| ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
| CHANGE [COLUMN] old_col_name new_col_name column_definition
[FIRST|AFTER col_name]
| MODIFY [COLUMN] col_name column_definition
[FIRST | AFTER col_name]
| DROP [COLUMN] col_name
| DROP PRIMARY KEY
| DROP {INDEX|KEY} index_name
| DROP FOREIGN KEY fk_symbol
| MAX_ROWS = rows
| DISABLE KEYS
| ENABLE KEYS
| RENAME [TO|AS] new_tbl_name
| ORDER BY col_name [, col_name] ...
| CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
| [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]
| DISCARD TABLESPACE
| IMPORT TABLESPACE
| FORCE
| ADD PARTITION (partition_definition)
| DROP PARTITION partition_names
| TRUNCATE PARTITION {partition_names | ALL}
| COALESCE PARTITION number
| REORGANIZE PARTITION [partition_names INTO (partition_definitions)]
| ANALYZE PARTITION {partition_names | ALL}
| CHECK PARTITION {partition_names | ALL}
| OPTIMIZE PARTITION {partition_names | ALL}
| REBUILD PARTITION {partition_names | ALL}
| REPAIR PARTITION {partition_names | ALL}
| PARTITION BY partitioning_expression
例如
alter table student add (sgender int(1) not null) -----增加列------
alter table student modify (sname varchar(100) not null) -----修改列------
alter table student drop sno -----删除列------
锁定表为只读
lock table student read only ----锁定单表---
flush tables with read lock ----锁定所有表
解锁表
unlock tables
删除表
drop table if exists student
清空表
truncate table student
表的重命名
rename table student to new_student
---------------可以使用rename进行数据库的移动---------------
rename table test.student to test1.stduent