mysql

最新推荐文章于 2024-10-08 17:39:57 发布

Linux_痞子

最新推荐文章于 2024-10-08 17:39:57 发布

阅读量345

点赞数

分类专栏： mysql 文章标签：数据库管理数据管理数据库 mysql windows

本文链接：https://blog.csdn.net/u010165367/article/details/51465142

版权

mysql 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

windows 下的mysql 启动，停止

net start 服务名
net stop 服务名

例如：

net start mysql
net stop mysql

登录认证
mysql -hIP -Pport -uuser -p

针对的操作对象，分成不同的语言
1、数据管理语言DML
2、数据定义语言（对保存数据的格式进行定义）DDL
3、数据库管理语言（针对数据库服务软件）DCL

a、按照指定模式查询表

show tables like ['pattern']
例如：
mysql> show tables like 'stu%';
+--------------------------+
| Tables_in_student (stu%) |
+--------------------------+
| stu                      |
| stu_exam                 |
+--------------------------+
2 rows in set (0.00 sec)
其中‘%’是正则表达式代表任意个数任意字符的组合。


b、显示表的创建信息
mysql> show create table stu;
+-------+----------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                               |
+-------+----------------------------------------------------------------------------------------------------------------------------+
| stu   | CREATE TABLE `stu` (
  `name` varchar(10) NOT NULL,
  `passwd` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
可以看出，显示的结果有点杂乱，此时我们可以使用\G作为结束符。

mysql> show create table stu\G
*************************** 1. row ***************************
       Table: stu
Create Table: CREATE TABLE `stu` (
  `name` varchar(10) NOT NULL,
  `passwd` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

c、查看表结构
mysql> desc stu;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name   | varchar(10) | NO   |     | NULL    |       |
| passwd | varchar(10) | NO   |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

d、删除表
drop table [if exists] table_name;

e、修改表
修改表名，列（字段定义），表属性
    重命名表
    rename table old_table_name to new_table_name;

    mysql> rename table stu_exam to stu_info;
    Query OK, 0 rows affected (0.04 sec)

    mysql> show tables;
    +-------------------+
    | Tables_in_student |
    +-------------------+
    | stu_info          |
    | ts                |
    +-------------------+
    2 rows in set (0.00 sec)
    其中rename还支持扩数据库重命名[相当与剪切]

    修改表的操作
    alter table table_name add|modify|change|drop
    增加新列
    add 新列
    alter table ts add age int; 

    mysql> alter table ts add age int;
Query OK, 0 rows affected (0.38 sec)
Records: 0  Duplicates: 0  Warnings: 0

    mysql> desc ts;
    +-------+-------------+------+-----+---------+-------+
    | Field | Type        | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | name  | varchar(10) | YES  |     | NULL    |       |
    | age   | int(11)     | YES  |     | NULL    |       |
    +-------+-------------+------+-----+---------+-------+
    2 rows in set (0.00 sec)

    删除列 
    drop 列 
    alter table ts drop age;

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

    mysql> desc ts;
    +-------+-------------+------+-----+---------+-------+
    | Field | Type        | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | name  | varchar(10) | YES  |     | NULL    |       |
    +-------+-------------+------+-----+---------+-------+
    1 row in set (0.00 sec)

    修改列属性 
    modify 
    alter table ts modify name varchar(20);

    mysql> alter table ts modify name varchar(20);
Query OK, 0 rows affected (0.47 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc ts;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)

    重命名一个列 
    change
    alter table ts change name myname varchar(10); 

mysql> alter table ts change name myname varchar(10);
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc ts;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| myname | varchar(10) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)