创建数据库

create database db_sanguo charset utf8;

切进db_sanguo

use db_sanguo

 

创建英雄

create table t_hero(    id int unsigned auto_increment primary key,    name varchar(10) unique not null,    age tinyint unsigned default 0,    gender set("男", "女"),    state varchar(10));insert into t_hero(name, age, gender, state) values("曹操","45","男","魏");insert into t_hero(name, age, gender, state) values("刘备","43","男","蜀");insert into t_hero(name, age, gender, state) values("孙权","42","男","吴");insert into t_hero(name, age, gender, state) values("诸葛亮","36","男","蜀");insert into t_hero(name, age, gender, state) values("司马懿","35","男","魏");insert into t_hero(name, age, gender) values("貂蝉","22","女");insert into t_hero(name, age, gender) values("吕布","30","男");insert into t_hero(name, age, gender) values("小乔","20","女");insert into t_hero(name, age, gender, state) values("关羽","35","男","蜀");insert into t_hero(name, gender, state) values("孙尚香","女","吴");insert into t_hero(name, age, gender, state) values("张飞","33","男","蜀");insert into t_hero(name, gender, state) values("小张飞","男","吴");insert into t_hero(name, age, gender, state) values("小张飞儿","33","男","蜀");insert into t_hero(name, age, state) values("张小飞儿","33","蜀");

 

数据库的操作

查看所有存在的数据库show databases;使用数据库use 数据库名;查看当前选择的数据库select database();创建数据库create database 数据库名 charset utf8;例：create database python charset utf8;MySQL数据库默认编码是Latin1，如果在创建数据库忘记指定字符集为utf8，可以通过下面方法修改show variables like "%char%";+--------------------------+----------------------------+| Variable_name            | Value                      |+--------------------------+----------------------------+| character_set_client     | utf8                       || character_set_connection | utf8                       || character_set_database   | latin1                     || character_set_filesystem | binary                     || character_set_results    | utf8                       || character_set_server     | latin1                     || character_set_system     | utf8                       || character_sets_dir       | /usr/share/mysql/charsets/ |+--------------------------+----------------------------+修改数据库 python_0314 的字符集    alter database python charset utf8;修改表的字符集，以及所有字符列(char, varchar, text等)的字符集为utf8    alter table students convert to charset utf8;删除数据库drop database 数据库名;例：drop database python;查看所有存在的数据库show databases;使用数据库use 数据库名查看当前选择的数据库select database();删除数据库drop database 数据库名;例：drop database python;

数据表的操作

查看当前数据库中所有表show tables;查看表结构desc 表名;创建表auto_increment表示自动增长create table 表名(列 类型 约束,...);例：创建班级表create table classes(    id int unsigned auto_increment primary key not null,    name varchar(10),    isdelete bit default 0);例：创建学生表create table students(    id int unsigned auto_increment primary key not null,    name varchar(10) not null,    gender bit default 1,    hometown varchar(20),    clsid int unsigned,    isdelete bit default 0,    foreign key(clsid) references classes(id));修改表-添加字段alter table 表名 add 列名 类型;alter table students add birthday datetime;修改表-修改字段：重命名版alter table 表名 change 原名 新名 类型及约束;alter table students change name name1 varchar(20) not null;修改表-修改字段：不重命名版alter table 表名 modify 列名 类型及约束;alter table students modify name1 varchar(10) not null;修改表-删除字段alter table 表名 drop 列名;alter table students drop birthday;删除表drop table 表名;drop table students;查看表的创建语句show create table 表名;show create table students;让数据表ID从零开始方法1：truncate table 你的表名//这样不但将数据全部删除，而且重新定位自增的字段方法2：delete from 你的表名dbcc checkident(你的表名,reseed,0) //重新定位自增的字段，让它从1开始般mysql命令行中加;号代表一行命令的结束\c 可以退出当前行命令，\q或者exit退出mysql命令行