C:Usersaaa>mysql -u User -p 123456
mysql> show databases;
mysql> use mysql_test;
mysql> create table customers
-> (
-> cust_id int not null auto_increment,
-> cust_name char(10) not null,
-> cust_sex char(2) not null default 0,
-> cust_address char(50) null,
-> cust_contact char(50) null ,
-> primary key(cust_id), //联合主键primary key(cust_id,cust_name),
-> index index_cust(cust_id), //创建索引 第一种方式 三种索引:普通索引 index 或 key 唯一性索引 unique 主键 primary key
-> );
mysql> alter table mysql_test.customers
-> add column cust_city char(10) not null default 'wuhan' after cust_sex;
mysql> alter table mysql_test.customers
-> change column cust_sex sex char(1) null default 'm';
mysql> alter table mysql_test.customers
-> alter column cust_city set default 'beijing'; //更改默认值
mysql> alter table mysql_test.customers
-> modify column cust_name char(20) first; //modify只改数据类型
mysql> alter table mysql_test.customers
-> drop column cust_contact;
mysql> alter table mysql_test.customers
-> rename to mysql_test.cust; //修改表名 rename table 改前的老名字 to 改后的新名字;
mysql> drop table mysql_test.cust;
mysql> show columns from mysql_test.cust; //查看表结构 desc(describe) mysql_test.cust;
mysql> create index index_customers
-> on mysql_test.cust(cust_name(3) asc) ; //根据客户姓名列的前三个字符创建一个升序索引 第二种方式
mysql> create index index_cust
-> on mysql_test.cust(cust_name,cust_id) ; //根据客户姓名列和客户id号创建一个组合索引
mysql> alter table mysql_test.cust
-> add index index_name(cust_name) ; //非唯一性索引 第三种方式
mysql> show index from mysql_test.cust; //显示当前表下的全部索引show indexes from mysql_test.cust;
mysql> drop index index_name on mysql_test.cust;
mysql> alter table mysql_test.cust
-> drop primary key, //现在大多数是不允许删除主键
-> drop index index_name, ;