mysql基本命令

最新推荐文章于 2024-06-05 22:54:12 发布
殇殇亿往岁月流
最新推荐文章于 2024-06-05 22:54:12 发布
阅读量743
点赞数
分类专栏： MySql
本文链接：https://blog.csdn.net/ah_quwei/article/details/79405051
版权
MySql 专栏收录该内容
5 篇文章 0 订阅
订阅专栏
1、创建数据表
mysql> use qq;
Database changed
mysql> create table if not exists user(
    -> id smallint,
    -> username varchar(20),
    -> age tinyint,
    -> sex enum('男','女','保密'),
    -> email varchar(50),
    -> addr varchar(200),
    -> birth year,
    -> salary float(8,2),
    -> tel int,
    -> married tinyint(1) comment '0没结婚，非零已结婚')
    -> engine=innodb charset=utf8;
Query OK, 0 rows affected (0.22 sec)


2、显示数据表
mysql> show tables;
+--------------+
| Tables_in_qq |
+--------------+
| user         |
+--------------+
1 row in set (0.00 sec)


3、查看表结构
mysql> desc user;


mysql> describe user;


mysql> show columns from user;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | smallint(6)  | YES  |     | NULL    |       |
| username | varchar(20)  | YES  |     | NULL    |       |
| age      | tinyint(4)   | YES  |     | NULL    |       |
| sex      | enum('       | YES  |     | NULL    |       |
| email    | varchar(50)  | YES  |     | NULL    |       |
| addr     | varchar(200) | YES  |     | NULL    |       |
| birth    | year(4)      | YES  |     | NULL    |       |
| salary   | float(8,2)   | YES  |     | NULL    |       |
| tel      | int(11)      | YES  |     | NULL    |       |
| married  | tinyint(1)   | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
10 rows in set, 3 warnings (0.10 sec)


4、查看创建表的详细信息
mysql> show create table user;


| Table | Create Table
| user  | CREATE TABLE `user` (
  `id` smallint(6) DEFAULT NULL,
  `username` varchar(20) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL,
  `sex` enum('男','女','保密') DEFAULT NULL,
  `email` varchar(50) DEFAULT NULL,
  `addr` varchar(200) DEFAULT NULL,
  `birth` year(4) DEFAULT NULL,
  `salary` float(8,2) DEFAULT NULL,
  `tel` int(11) DEFAULT NULL,
  `married` tinyint(1) DEFAULT NULL COMMENT '0没结婚，非零已结婚'
) ENGINE=InnoDB DEFAULT CHARSET=utf8                      |


1 row in set (0.01 sec)


5、创建主键


mysql> create table if not exists user1(
    -> id int primary key,
    -> username varchar(20)
    -> );
Query OK, 0 rows affected (0.08 sec)


6、插入一条记录
mysql> insert user1 values(1,'king');
Query OK, 1 row affected (0.05 sec)


mysql> insert user1 values(12,'Queen');
Query OK, 1 row affected (0.05 sec)


7、查询表中的全部记录
mysql> select * from user1;
+----+----------+
| id | username |
+----+----------+
|  1 | king     |
| 12 | Queen    |
+----+----------+
2 rows in set (0.00 sec)


8、查询（含主键）指定的记录
mysql> select * from user1 where id=12;
+----+----------+
| id | username |
+----+----------+
| 12 | Queen    |
+----+----------+
1 row in set (0.01 sec)


9、创建自增长的表（id）


mysql> create table if not exists user(
    -> id smallint key auto_increment,
    -> username varchar(20)
    -> );
Query OK, 0 rows affected, 1 warning (0.00 sec)


10、表中字段不能为空


mysql> create table if not exists user3(
    -> id int unsigned key auto_increment,
    -> username varchar(20) not null,
    -> pwd char(32) not null,
    -> age tinyint unsigned
    -> );
Query OK, 0 rows affected (0.09 sec)


11、创建唯一键unique
mysql> create table if not exists user5(
    -> id tinyint unsigned key auto_increment,
    -> username varchar(20) not null unique,
    -> card char(18) unique
    -> );
Query OK, 0 rows affected (0.10 sec)


mysql> desc user5;
+----------+---------------------+------+-----+---------+----------------+
| Field    | Type                | Null | Key | Default | Extra          |
+----------+---------------------+------+-----+---------+----------------+
| id       | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)         | NO   | UNI | NULL    |                |
| card     | char(18)            | YES  | UNI | NULL    |                |
+----------+---------------------+------+-----+---------+----------------+
3 rows in set (0.02 sec)


12、创建标的规则
create table [ if not exists] tbl_name(
字段名称 字段类型 [unsigned|zerofill] [not null] [default 默认值] [[primary] key|unique [key]] [auto_increment]
）engint=innodb charset=utf8 auto_increment=100;


13、修改表名
mysql> alter table user6 rename [to|as] user66;
Query OK, 0 rows affected (0.09 sec)


mysql> rename table user6 to user66;
Query OK, 0 rows affected (0.06 sec)


14、添加字段
mysql> alter table user6 add sex enum('男','女','保密 ');
Query OK, 0 rows affected (0.25 sec)
Records: 0  Duplicates: 0  Warnings: 0


15、添加带有约束条件的字段
mysql> alter table user6 add test1 varchar(100) not null unique;
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0


16、将添加的字段放到指定位置
mysql> alter table user6 add test2 varchar(10) not null first;
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> alter table user6 add test3 varchar(10) not null after age;
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0


17、添加多个字段
mysql> alter table user6
    -> add tse4 int not null default 123 after pwd,
    -> add test5 float(6,2) first,
    -> add test6 set('a','b','c');
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0


18、删除指定字段（也可添加其他字段）
mysql> alter table user6 drop test6;
Query OK, 0 rows affected (0.18 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> alter table user6 add test2 varchar(10) not null after age ,drop test3;
Query OK, 0 rows affected (0.31 sec)
Records: 0  Duplicates: 0  Warnings: 0


19、修改字段的名称、类型或者约束、位置
mysql> alter table user6 modify sex enum('男','女','保密','不清楚') after username;
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> alter table user6 modify email varchar(100) not null default '987654321.qq.com';
Query OK, 0 rows affected (0.24 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> alter table user6 change test2 test1 varchar(10) not null;
Query OK, 0 rows affected (0.23 sec)
Records: 0  Duplicates: 0  Warnings: 0


20、添加默认值
mysql> desc user7;
+----------+---------------------+------+-----+---------+----------------+
| Field    | Type                | Null | Key | Default | Extra          |
+----------+---------------------+------+-----+---------+----------------+
| id       | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)         | NO   | UNI | NULL    |                |
| age      | tinyint(3) unsigned | YES  |     | NULL    |                |
+----------+---------------------+------+-----+---------+----------------+
3 rows in set (0.02 sec)
（1）、添加默认值
mysql> alter table user7 alter age set default 18;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0
（2）、添加字段
mysql> alter table user7 add email varchar(50);
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> alter table user7 alter email set default '123456.qq.com';
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0


21、删除默认值
mysql> alter table user7 alter age drop default;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0


22、添加主键
mysql> desc user8;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.02 sec)


mysql> alter table user8 add primary key(id);
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0


23、删除主键
mysql> alter table user8 drop primary key;
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0


24、添加唯一索引
mysql> alter table user8 add constraint symbol unique key uni_card(card);
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0


25、删除唯一索引
mysql> alter table user8 drop key uni_card;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0


26、修改表的存储引擎
mysql> alter table user8 engine=myisam;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0


27、重新设置自动增长数值
mysql> alter table user8 auto_increment=100;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0


 user8 | CREATE TABLE `user8` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `card` varchar(20) DEFAULT NULL,
 PRIMARY KEY (`id`)
 ENGINE=MyISAM AUTO_INCREMENT=100 DEFAULT CHARSET=utf8 |


28、删除数据表
mysql> drop table user7;
Query OK, 0 rows affected (0.05 sec)
殇殇亿往岁月流
关注
0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
mysql基本命令

1、创建数据表mysql&gt; use qq;Database changedmysql&gt; create table if not exists user( -&gt; id smallint, -&gt; username varchar(20), -&gt; age tinyint, -&gt; sex enum('男','女','保密'), ...
复制链接

扫一扫
专栏目录