补0818：数据库ddl，dml实操 及 新建用户表

//mysql  ddl实操  create drop databse；

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaz                |
| admin-tp6          |
| cmf                |
| easyadmin          |
| heima              |
| hm                 |
| lycms              |
| mysql              |
| newtp              |
| nonecms            |
| performance_schema |
| pyg                |
| snake              |
| sys                |
| thinkcmf           |
| xhhds              |
| yzncms             |
+--------------------+
18 rows in set (0.01 sec)

mysql> create database wtes;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaz                |
| admin-tp6          |
| cmf                |
| easyadmin          |
| heima              |
| hm                 |
| lycms              |
| mysql              |
| newtp              |
| nonecms            |
| performance_schema |
| pyg                |
| snake              |
| sys                |
| thinkcmf           |
| wtes               |
| xhhds              |
| yzncms             |
+--------------------+
19 rows in set (0.00 sec)

mysql> drop database wtes;
Query OK, 0 rows affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaz                |
| admin-tp6          |
| cmf                |
| easyadmin          |
| heima              |
| hm                 |
| lycms              |
| mysql              |
| newtp              |
| nonecms            |
| performance_schema |
| pyg                |
| snake              |
| sys                |
| thinkcmf           |
| xhhds              |
| yzncms             |
+--------------------+
18 rows in set (0.00 sec)
mysql> use lycms
Database changed
mysql> show tables;
+-----------------+
| Tables_in_lycms |
+-----------------+
| v1_ad           |
| v1_banner       |
| v1_collect      |
| v1_column       |
| v1_email_log    |
| v1_kami         |
| v1_say          |
| v1_see_record   |
| v1_tort         |
| v1_user         |
| v1_user_info    |
| v1_vod          |
| v1_vod_actor    |
| v1_vod_director |
| v1_vod_play     |
| v1_vod_type     |
+-----------------+
16 rows in set (0.00 sec)
mysql> create table v1_test(id int(10),name varchar(8),sex tinyint(1));
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+-----------------+
| Tables_in_lycms |
+-----------------+
| v1_ad           |
| v1_banner       |
| v1_collect      |
| v1_column       |
| v1_email_log    |
| v1_kami         |
| v1_say          |
| v1_see_record   |
| v1_test         |
| v1_tort         |
| v1_user         |
| v1_user_info    |
| v1_vod          |
| v1_vod_actor    |
| v1_vod_director |
| v1_vod_play     |
| v1_vod_type     |
+-----------------+
17 rows in set (0.00 sec)
mysql> desc v1_test
    -> ;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | int(10)    | YES  |     | NULL    |       |
| name  | varchar(8) | YES  |     | NULL    |       |
| sex   | tinyint(1) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> show create table v1_test;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                 |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| v1_test | CREATE TABLE `v1_test` (
  `id` int(10) DEFAULT NULL,
  `name` varchar(8) DEFAULT NULL,
  `sex` tinyint(1) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table v1_test modify name varchar(12);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table v1_test
    -> ;
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                  |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
| v1_test | CREATE TABLE `v1_test` (
  `id` int(10) DEFAULT NULL,
  `name` varchar(12) DEFAULT NULL,
  `sex` tinyint(1) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table v1_test add  age tinyint;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table v1_test;
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                   |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| v1_test | CREATE TABLE `v1_test` (
  `id` int(10) DEFAULT NULL,
  `name` varchar(12) DEFAULT NULL,
  `sex` tinyint(1) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table v1_test name uname varchar(12);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'name uname varchar(12)' at line 1
mysql> alter table v1_test modify name uname varchar(12);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'uname varchar(12)' at line 1
mysql> alter table v1_test change name uname varchar(12);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table v1_test drop age;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table v1_test;
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                   |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| v1_test | CREATE TABLE `v1_test` (
  `id` int(10) DEFAULT NULL,
  `uname` varchar(12) DEFAULT NULL,
  `sex` tinyint(1) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table v1_test add age tinyint before sex;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'before sex' at line 1
mysql> alter table v1_test add age tinyint after uname;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table v1_test  uid int(11) first;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'uid int(11) first' at line 1
mysql> alter table v1_test add  uid int(11) first;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table v1_test;
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                  |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| v1_test | CREATE TABLE `v1_test` (
  `uid` int(11) DEFAULT NULL,
  `id` int(10) DEFAULT NULL,
  `uname` varchar(12) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL,
  `sex` tinyint(1) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table v1_test rename test;
Query OK, 0 rows affected (0.00 sec)

mysql> show create table est;
ERROR 1146 (42S02): Table 'lycms.est' doesn't exist
mysql> show create table test;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                               |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `uid` int(11) DEFAULT NULL,
  `id` int(10) DEFAULT NULL,
  `uname` varchar(12) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL,
  `sex` tinyint(1) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> create index id_index on test(id);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create unique index uid_o on test(uid);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table test;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                      |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `uid` int(11) DEFAULT NULL,
  `id` int(10) DEFAULT NULL,
  `uname` varchar(12) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL,
  `sex` tinyint(1) DEFAULT NULL,
  UNIQUE KEY `uid_o` (`uid`),
  KEY `id_index` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> drop index id_index on test;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table test;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                             |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `uid` int(11) DEFAULT NULL,
  `id` int(10) DEFAULT NULL,
  `uname` varchar(12) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL,
  `sex` tinyint(1) DEFAULT NULL,
  UNIQUE KEY `uid_o` (`uid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show index from test;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test  |          0 | uid_o    |            1 | uid         | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

mysql> show index from test \G;
*************************** 1. row ***************************
        Table: test
   Non_unique: 0
     Key_name: uid_o
 Seq_in_index: 1
  Column_name: uid
    Collation: A
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
1 row in set (0.00 sec)

ERROR:
No query specified

mysql>
//mysqlDML实操
mysql> insert into test (uid,id,uname,age,sex) values (1,1,'yyds',11,1);
Query OK, 1 row affected (0.01 sec)

mysql> select * from test;
+------+------+-------+------+------+
| uid  | id   | uname | age  | sex  |
+------+------+-------+------+------+
|    1 |    1 | yyds  |   11 |    1 |
+------+------+-------+------+------+
1 row in set (0.00 sec)

mysql> insert into test (uid,id,uname,age,sex) values (2,2,'yyds',11,1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+------+------+-------+------+------+
| uid  | id   | uname | age  | sex  |
+------+------+-------+------+------+
|    1 |    1 | yyds  |   11 |    1 |
|    2 |    2 | yyds  |   11 |    1 |
+------+------+-------+------+------+
2 rows in set (0.00 sec)

mysql>  delete from test where uid = 2;
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+------+------+-------+------+------+
| uid  | id   | uname | age  | sex  |
+------+------+-------+------+------+
|    1 |    1 | yyds  |   11 |    1 |
+------+------+-------+------+------+
1 row in set (0.00 sec)

mysql> update test set uname = 'xswl' where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test;
+------+------+-------+------+------+
| uid  | id   | uname | age  | sex  |
+------+------+-------+------+------+
|    1 |    1 | xswl  |   11 |    1 |
+------+------+-------+------+------+
1 row in set (0.00 sec)

创建一张用户表

mysql> create table user(
    ->      uid int(10) not null auto_increment comment '用户ID',
    ->      uname varchar(10) not null,
    ->      pwd char(32) not null,
    ->      sex tinyint(1) not null,
    ->      age tinyint not null,
    ->      tel  char(11) not null,
    ->      create_time int(10) not null,
    ->      update_time int(10) not null,
    ->      PRIMARY KEY (`uid`),
    ->      UNIQUE KEY (`uname`)
    -> )engine=innodb auto_increment=20001 default charset =utf8;
Query OK, 0 rows affected (0.02 sec)

mysql> show create table user;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                          |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user  | CREATE TABLE `user` (
  `uid` int(10) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `uname` varchar(10) NOT NULL,
  `pwd` char(32) NOT NULL,
  `sex` tinyint(1) NOT NULL,
  `age` tinyint(4) NOT NULL,
  `tel` char(11) NOT NULL,
  `create_time` int(10) NOT NULL,
  `update_time` int(10) NOT NULL,
  PRIMARY KEY (`uid`),
  UNIQUE KEY `uname` (`uname`)
) ENGINE=InnoDB AUTO_INCREMENT=20001 DEFAULT CHARSET=utf8   |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>