MYSQL 数据库创建，修改等知识整理-白红宇

MYSQL 数据库创建，修改等知识整理

阅读量：6819 次

发布时间：2019-06-26

本文共 13550 字，大约阅读时间需要 45 分钟。

[root@www ~]# mysql -uroot -p -h127.0.0.1（或者指定-hlocalhost）这里指定了用户密码和主机，

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 7

Server version: 5.1.73 Source distribution

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases；

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| test |

| testdb |

+--------------------+

4 rows in set (0.04 sec)

mysql> use testdb 切换到testdb数据库

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> create table bird (name char(10) not null, age tinyint,xingbie char(1) not null );

Query OK, 0 rows affected (0.02 sec) 创建bird表定义相关字段和属

mysql> desc bird; 查看相关bird表的信息

+---------+------------+------+-----+---------+-------+

+---------+------------+------+-----+---------+-------+

| age | tinyint(4) | YES | | NULL | |

+---------+------------+------+-----+---------+-------+

3 rows in set (0.03 sec)

mysql> alter table bird change xingbie XB char(1);修改表的相关属性，这里注意要带定义的属性char（1）

Query OK, 0 rows affected (0.03 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc bird;

+-------+------------+------+-----+---------+-------+

+-------+------------+------+-----+---------+-------+

| age | tinyint(4) | YES | | NULL | |

| XB | char(1) | YES | | NULL | |

+-------+------------+------+-----+---------+-------+

3 rows in set (0.02 sec)

mysql> alter table bird add zhuangye varchar(100); 这里增加了一个字段

Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc bird

-> ;

+----------+--------------+------+-----+---------+-------+

+----------+--------------+------+-----+---------+-------+

| age | tinyint(4) | YES | | NULL | |

| XB | char(1) | YES | | NULL | |

+----------+--------------+------+-----+---------+-------+

4 rows in set (0.00 sec)

mysql> alter table bird change zhuangye ZY varchar(100); 又一次修改表的字段注意带上字段定义varchar（100）

Query OK, 0 rows affected (0.03 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc bird

-> ;

+-------+--------------+------+-----+---------+-------+

+-------+--------------+------+-----+---------+-------+

| age | tinyint(4) | YES | | NULL | |

| XB | char(1) | YES | | NULL | |

| ZY | varchar(100) | YES | | NULL | |

+-------+--------------+------+-----+---------+-------+

4 rows in set (0.00 sec)

mysql> help alter table 查看修改表的帮助信息也可以用help create table查看创建表

Name: 'ALTER TABLE'

Description:

Syntax:

ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name

[alter_specification [, alter_specification] ...]

[partition_options]

alter_specification:

table_options

| ADD [COLUMN] col_name column_definition

[FIRST | AFTER col_name ]

| ADD [COLUMN] (col_name column_definition,...)

| ADD {INDEX|KEY} [index_name]

[index_type] (index_col_name,...) [index_option] ...

| ADD [CONSTRAINT [symbol]] PRIMARY KEY

[index_type] (index_col_name,...) [index_option] ...

| ADD [CONSTRAINT [symbol]]

UNIQUE [INDEX|KEY] [index_name]

[index_type] (index_col_name,...) [index_option] ...

| ADD FULLTEXT [INDEX|KEY] [index_name]

(index_col_name,...) [index_option] ...

| ADD SPATIAL [INDEX|KEY] [index_name]

(index_col_name,...) [index_option] ...

| ADD [CONSTRAINT [symbol]]

FOREIGN KEY [index_name] (index_col_name,...)

reference_definition

| ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}

| CHANGE [COLUMN] old_col_name new_col_name column_definition

[FIRST|AFTER col_name]

| MODIFY [COLUMN] col_name column_definition

[FIRST | AFTER col_name]

| DROP [COLUMN] col_name

| DROP PRIMARY KEY

| DROP {INDEX|KEY} index_name

| DROP FOREIGN KEY fk_symbol

| DISABLE KEYS

| ENABLE KEYS

| RENAME [TO|AS] new_tbl_name

| ORDER BY col_name [, col_name] ...

| CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]

| [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]

| DISCARD TABLESPACE

| IMPORT TABLESPACE

| ADD PARTITION (partition_definition)

| DROP PARTITION partition_names

| COALESCE PARTITION number

| REORGANIZE PARTITION [partition_names INTO (partition_definitions)]

| ANALYZE PARTITION {partition_names | ALL}

| CHECK PARTITION {partition_names | ALL}

| OPTIMIZE PARTITION {partition_names | ALL}

| REBUILD PARTITION {partition_names | ALL}

| REPAIR PARTITION {partition_names | ALL}

| PARTITION BY partitioning_expression

| REMOVE PARTITIONING

ALTER TABLE changes the structure of a table. For example, you can add

or delete columns, create or destroy indexes, change the type of

existing columns, or rename columns or the table itself. You can also

change characteristics such as the storage engine used for the table or

the table comment.

A number of partitioning-related extensions to ALTER TABLE were added

in MySQL 5.1.5. These can be used with partitioned tables for

repartitioning, for adding, dropping, merging, and splitting

partitions, and for performing partitioning maintenance. It is possible

for an ALTER TABLE statement to contain a PARTITION BY or REMOVE

PARTITIONING clause in an addition to other alter specifications, but

the PARTITION BY or REMOVE PARTITIONING clause must be specified last

after any other specifications. The ADD PARTITION, DROP PARTITION,

COALESCE PARTITION, REORGANIZE PARTITION, ANALYZE PARTITION, CHECK

PARTITION, and REPAIR PARTITION options cannot be combined with other

alter specifications in a single ALTER TABLE, since the options just

listed act on individual partitions. For more information, see

Following the table name, specify the alterations to be made. If none

are given, ALTER TABLE does nothing.

URL: http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

mysql> alter table bird modify ZY char(11); 修改了ZY定义的字段长度

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc bird;

+--------+------------+------+-----+---------+-------+

+--------+------------+------+-----+---------+-------+

| age | tinyint(4) | YES | | NULL | |

| XB | char(1) | YES | | NULL | |

| ZY | char(11) | YES | | NULL | |

| weight | int(11) | YES | | NULL | |

+--------+------------+------+-----+---------+-------+

5 rows in set (0.00 sec)

mysql> alter table bird add xuli char(10) after weight;

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc bird;

+--------+------------+------+-----+---------+-------+

+--------+------------+------+-----+---------+-------+

| age | tinyint(4) | YES | | NULL | |

| XB | char(1) | YES | | NULL | |

| ZY | char(11) | YES | | NULL | |

| weight | int(11) | YES | | NULL | |

+--------+------------+------+-----+---------+-------+

6 rows in set (0.00 sec)

mysql> alter table bird change xuli Xueli char(10) after name; change修改字段

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc bird;

+--------+------------+------+-----+---------+-------+

+--------+------------+------+-----+---------+-------+

| age | tinyint(4) | YES | | NULL | |

| XB | char(1) | YES | | NULL | |

| ZY | char(11) | YES | | NULL | |

| weight | int(11) | YES | | NULL | |

+--------+------------+------+-----+---------+-------+

6 rows in set (0.00 sec)

mysql> alter table bird drop weight; 删除weight字段

Query OK, 0 rows affected (0.04 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc bird;

+-------+------------+------+-----+---------+-------+

+-------+------------+------+-----+---------+-------+

| age | tinyint(4) | YES | | NULL | |

| XB | char(1) | YES | | NULL | |

| ZY | char(11) | YES | | NULL | |

+-------+------------+------+-----+---------+-------+

5 rows in set (0.00 sec)

mysql> insert into bird (name,xueli) value ('qiaofeng','xiaoxue'),('yangguo','chuzhong');输入字段定义的具体信息

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from bird;

+----------+----------+------+------+------+

| name | Xueli | age | XB | ZY |

+----------+----------+------+------+------+

+----------+----------+------+------+------+

2 rows in set (0.00 sec)

mysql> select name from bird;

+----------+

| name |

+----------+

| qiaofeng |

| yangguo |

+----------+

2 rows in set (0.04 sec)

mysql> select * from bird;

+----------+----------+------+------+------+

| name | Xueli | age | XB | ZY |

+----------+----------+------+------+------+

+----------+----------+------+------+------+

2 rows in set (0.02 sec)

mysql> insert into bird value ('hongqigong','xiaox',50,'man','jianghuadaxia'); 没有指定哪个字段则依次修改

Query OK, 1 row affected, 2 warnings (0.00 sec)

mysql> select * from bird;

+------------+----------+------+------+-------------+

| name | Xueli | age | XB | ZY |

+------------+----------+------+------+-------------+

+------------+----------+------+------+-------------+

3 rows in set (0.00 sec)

mysql> update bird set xueli='daxue' where name='yangguo' 修改指定字段对应的信息

-> ;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from bird 查看

-> ;

+------------+---------+------+------+-------------+

| name | Xueli | age | XB | ZY |

+------------+---------+------+------+-------------+

+------------+---------+------+------+-------------+

3 rows in set (0.00 sec)

mysql>

alter是改房间，update   是改家具 mysql> create user 'tom'@'%' identified by 'tom'；创建tom用户指定在任何主机上 密码也是tom,%表示任意长度任意字符

Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'tom'@'%'

-> ;

+----------------------------------------------------------------------------------------------------+

| Grants for tom@% |

+----------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'tom'@'%' IDENTIFIED BY PASSWORD '*71FF744436C7EA1B954F6276121DB5D2BF68FC07' |

+----------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

mysql> grant all privileges on testdb.* to 'tom'@'%'; 给tom用户所有权限

Query OK, 0 rows affected (0.00 sec)

这样远程连接Mysql服务器的话就可以直接 mysql -utom -p -h192.168.137.222

如果提示连接不上则可能是iptables没有关闭，service iptables stop 即可

、················

###### show databases; 查看数据库

show tables; 查看表

show table bird 查看一张叫做bird的表

select name,age...from bird 进bird表中查看name age等相关字段，

################################

步骤 creat testdb；创建testdb数据库

create table testdb (name char(10) not null, age tinyint,xingbie char(1) not null ); 创建testdb表中的相关字段name age xingbie等

alter table testdb change|modify|add|drop 修改

alter table bird change xuli Xueli char(10) after name; change 修改字段

alter table bird change xingbie XB char(1); 修改字段大小写，这里注意要带定义的属性char（1）即使没有变化

alter table bird add zhuangye varchar(100); 这里增加了一个字段

alter table bird drop weight; 删除weight字段

alter table bird modify ZY char(11); 修改了ZY定义的字段长度

insert into bird (name,xueli) value ('qiaofeng','xiaoxue'),('yangguo','chuzhong');为字段name xueli定义的具体信息

insert into bird value ('hongqigong','xiaox',50,'man','jianghuadaxia'); 没有指定哪个字段则依次修改

update bird set xueli='daxue' where name='yangguo' 修改指定字段对应的信息

select * from bird 查看

#############################################3

mysql> select User,Host,Password from user;

+-------+-----------------+-------------------------------------------+

| User | Host | Password |

+-------+-----------------+-------------------------------------------+

| root | localhost | *00A51F3F48415C7D4E8908980D443C29C69B60C9 |

| root | www.bird.com | *00A51F3F48415C7D4E8908980D443C29C69B60C9 |

| root | 127.0.0.1 | *00A51F3F48415C7D4E8908980D443C29C69B60C9 |

| | localhost | |

| | www.bird.com | |

| tom | % | *71FF744436C7EA1B954F6276121DB5D2BF68FC07 |

| bird | 192.168.137.223 | *2157821D5B01640A8367829F19E09D525615B0A6 |

| bird | 192.168.137.128 | |

| hello | 192.168.137.128 | *6B4F89A54E2D27ECD7E8DA05B4AB8FD9D1D8B119

设定密码： 1、mysql> set password for 'root'@'host'=password('123456'); #host可以是具体的ip

2、# mysqladmin -uroot -p -hhost password ‘123456’

3、mysql> update user set password=password（‘12345’）where user=‘root' and host='127.0.0.1'; 这是修改user表并制定root用户且host为127.0.0.1的

create user 'tom'@'%' identified by 'tom'；创建tom用户指定在任何主机上 密码也是tom,%表示任意长度任意字符 grant all privileges on testdb.* to 'tom'@'%'; 给tom用户所有权限

转载于:https://blog.51cto.com/09112012/1930286

你可能感兴趣的文章