一.创建数据库
create database| schema[if not exists]databasename
[[default]character set charset_name]
[|[default]collate collation_name];
例:通过createdatabase语句创建一个名称为mysqltest的数据库。
命令和运行结果如下:
mysql > create database if not exists mysqltest;
Query OK, 1 row affected (0. 05 sec)
结果表明,创建mysqltest数据库成功。
二.创建数据库表
create [temporary]table[ if not exists]table_name
[([column_definition], …|[index_definition])]
[table_option][select_statement];
三.修改数据库
alter database | schema[db_name]
[[default] character set charset_name]
[|[default] collate collation_name];
四..删除数据库
drop database [if exists] db name
alter[ ignore] table tbl_ name
五.修改数据库表
alter_ specification [ , alter_ specif ication ]
alter_ specif ication: /
add [ column] column_ definition [ first| after col_ name]
//添加字段
lalter [ column]col_ name{ set default l iteral| drop default}
//修改字段默认值
| change [ column] old col_ name column_ def inition
//重命名字段
[ first after col_ name ]
| modify [ column]column_ definition[ first| aftercol name ]
//修改字段数据类型
drop [column] col_ name
//删除列
rename [TO] new_ tbl_ name
//对表重命名
order by col_ name
//按字段排序
convert TO character set charset_ name[ collate collation name ]
//将字符集转换为二进制
[ default] character set charset_ name [ collate collat ion name]
/修改表的默认字符集
六.删除数据库表
drop table table_name
七.表记录的插入
insert replace[ into]table_name[(col_name,...)]
values((expr |default,...),(...),...
set col_name = (expr|default,…
八.表记录的修改
update table_name
set col_namel = expr1[,col_name2 = expr2 ..]
[here 子句]
九.表记录的删除
delete[1ow_ priority] [quick] [ ignore] from tbl_ name
[where子句]
[order by子句]
[limit row count]
例:删除studentOl表中人学成绩最低的2行记录。命令和运行结果如下:
mysgl> delete from student01 order by entrance ] imit 2;
Query OK, 2 rows affected (0. 01 sec )