库操作和表操作
一、库操作
1.新建数据库
语法： create database 数据库名 charset utf8; mysql> create database gao1 charset utf8; Query OK, 1 row affected (0.00 sec)
2.查看数据库
查看所有的库：show databases；
show databases； mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | gao | | gao1 | | mysql | | performance_schema | | sys | +--------------------+ 6 rows in set (0.01 sec)
具体查看某个库：show create database 数据库名
mysql> show create database gao1; +----------+---------------------------------------------------------------+ | Database | Create Database | +----------+---------------------------------------------------------------+ | gao1 | CREATE DATABASE `gao1` /*!40100 DEFAULT CHARACTER SET utf8 */ | +----------+---------------------------------------------------------------+ 1 row in set (0.00 sec)
查看当前所在的库：select database()；
mysql> use gao1 Database changed mysql> select database(); +------------+ | database() | +------------+ | gao1 | +------------+ 1 row in set (0.00 sec)
3.使用库
use 数据库名
mysql> use gao Database changed mysql> select database(); +------------+ | database() | +------------+ | gao | +------------+ 1 row in set (0.00 sec)
4.删除数据库
drop database 数据库名;
mysql> drop database gao1; Query OK, 0 rows affected (0.01 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | gao | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec)
5.修改数据库
alter database 数据库名 charset 编码名
mysql> alter database gao charset gbk; Query OK, 1 row affected (0.00 sec) mysql> show create database gao; +----------+-------------------------------------------------------------+ | Database | Create Database | +----------+-------------------------------------------------------------+ | gao | CREATE DATABASE `gao` /*!40100 DEFAULT CHARACTER SET gbk */ | +----------+-------------------------------------------------------------+ 1 row in set (0.00 sec)
二、表操作
1、创建表
格式：
create table(
字段名1 类型[宽度，约束类型]，
字段名2 类型[宽度，约束类型]，
字段名3 类型[宽度，约束类型]，
)；
注意点：
1、同一个表中，字段名不能相同
2、宽度和约束类型是自己选择的
3、字段名和类型是必须的
mysql> use gao Database changed mysql> create table intforce( -> id int, -> name varchar(50), -> age int(3) -> ); Query OK, 0 rows affected (0.04 sec) mysql> show tables; +---------------+ | Tables_in_gao | +---------------+ | intforce | | t1 | | t2 | | t3 | | t4 | | t5 | | t6 | +---------------+ 7 rows in set (0.00 sec) mysql> desc intforce -> ; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(50) | YES | | NULL | | | age | int(3) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.02 sec)
2.查看表
show create table 表名； \G 可查看表详细结构
1、在当前数据库下
mysql> show create table intforce; +----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+ | intforce | CREATE TABLE `intforce` ( `id` int(11) DEFAULT NULL, `name` varchar(50) DEFAULT NULL, `age` int(3) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=gbk | +----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
2、在其他数据库下
[mysql> show create table mysql.user; +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | user | CREATE TABLE `user` ( `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '', `User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '', `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '', `ssl_cipher` blob NOT NULL, `x509_issuer` blob NOT NULL, `x509_subject` blob NOT NULL, `max_questions` int(11) unsigned NOT NULL DEFAULT '0', `max_updates` int(11) unsigned NOT NULL DEFAULT '0', `max_connections` int(11) unsigned NOT NULL DEFAULT '0', `max_user_connections` int(11) unsigned NOT NULL DEFAULT '0', `plugin` char(64) COLLATE utf8_bin NOT NULL DEFAULT 'mysql_native_password', `authentication_string` text COLLATE utf8_bin, `password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `password_last_changed` timestamp NULL DEFAULT NULL, `password_lifetime` smallint(5) unsigned DEFAULT NULL, `account_locked` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', PRIMARY KEY (`Host`,`User`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges' | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec)
3、修改表结构
1、修改表名
alter table 表名 rename 新表名
如下图把表test1 改成 test2
mysql> show tables; +---------------+ | Tables_in_gao | +---------------+ | t1 | | t2 | | t3 | | t4 | | t5 | | t6 | | test1 | +---------------+ 7 rows in set (0.00 sec) mysql> alter table test1 rename test2; Query OK, 0 rows affected (0.00 sec) mysql> show tables; +---------------+ | Tables_in_gao | +---------------+ | t1 | | t2 | | t3 | | t4 | | t5 | | t6 | | test2 | +---------------+ 7 rows in set (0.00 sec)
2、增加字段
alter table 表名 add 字段名 数据类型 【约束条件】
add 字段名 数据类型 【约束条件】
add 字段名 数据类型 【约束条件】
mysql> alter table test2 -> add money int(4) not null default 10, -> add place varchar(10) not null -> ; mysql> desc test2; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(50) | YES | | NULL | | | age | int(3) | YES | | NULL | | | money | int(4) | NO | | 10 | | | place | varchar(10) | NO | | NULL | | +-------+-------------+------+-----+---------+-------+ 5 rows in set (0.01 sec)
添加在某个字段后面
mysql> alter table test2 -> add stu_name varchar(10) after name; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc test2; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(50) | YES | | NULL | | | stu_name | varchar(10) | YES | | NULL | | | age | int(3) | YES | | NULL | | | money | int(4) | NO | | 10 | | | place | varchar(10) | NO | | NULL | | +----------+-------------+------+-----+---------+-------+ 6 rows in set (0.01 sec)
把字段加在第一个
mysql> alter table test2 -> add stu_age int(2) first; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc test2; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | stu_age | int(2) | YES | | NULL | | | id | int(11) | YES | | NULL | | | name | varchar(50) | YES | | NULL | | | stu_name | varchar(10) | YES | | NULL | | | age | int(3) | YES | | NULL | | | money | int(4) | NO | | 10 | | | place | varchar(10) | NO | | NULL | | +----------+-------------+------+-----+---------+-------+ 7 rows in set (0.00 sec)
3.删除字段
alter table 表名 drop 字段名
删除stu_age 字段 mysql> alter table test2 -> drop stu_age; Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc test2; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(50) | YES | | NULL | | | stu_name | varchar(10) | YES | | NULL | | | age | int(3) | YES | | NULL | | | money | int(4) | NO | | 10 | | | place | varchar(10) | NO | | NULL | | +----------+-------------+------+-----+---------+-------+ 6 rows in set (0.00 sec)
4.修改字段
把id的类型改为int(8) mysql> alter table test2 -> modify id int(8); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc test2; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | id | int(8) | YES | | NULL | | | name | varchar(50) | YES | | NULL | | | stu_name | varchar(10) | YES | | NULL | | | age | int(3) | YES | | NULL | | | money | int(4) | NO | | 10 | | | place | varchar(10) | NO | | NULL | | +----------+-------------+------+-----+---------+-------+ 6 rows in set (0.00 sec)
5.复制一个表
结构和数据都有
新建一个和test2一样的test1表 mysql> create table test1 select * from test2; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show tables; +---------------+ | Tables_in_gao | +---------------+ | t1 | | t2 | | t3 | | t4 | | t5 | | t6 | | test1 | | test2 | +---------------+ 8 rows in set (0.00 sec)
只有表结构不需要数据
方法1:
create table 新表名 like 老表名 mysql> select * from test2; +------+------+----------+------+-------+-------+ | id | name | stu_name | age | money | place | +------+------+----------+------+-------+-------+ | NULL | gao | NULL | 20 | 10 | aa | +------+------+----------+------+-------+-------+ 1 row in set (0.00 sec) mysql> create table test3 like test1; Query OK, 0 rows affected (0.03 sec) mysql> select * from test3; Empty set (0.00 sec)
方法二：
create table 新表名 select * from 旧表名 where 1=2; ##条件为假 查不到任何记录 mysql> show tables; +---------------+ | Tables_in_gao | +---------------+ | t1 | | t2 | | t3 | | t4 | | t5 | | t6 | | test1 | | test2 | | test3 | +---------------+ 9 rows in set (0.00 sec) mysql> create table test4 select * from test1 where 1=2; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select * from test4; Empty set (0.00 sec)
6.删除表
删除表test5 mysql> show tables; +---------------+ | Tables_in_gao | +---------------+ | t1 | | t2 | | t3 | | t4 | | t5 | | t6 | | test1 | | test2 | | test3 | | test5 | +---------------+ 10 rows in set (0.00 sec) mysql> drop table test5 -> ; Query OK, 0 rows affected (0.01 sec) mysql> show tables; +---------------+ | Tables_in_gao | +---------------+ | t1 | | t2 | | t3 | | t4 | | t5 | | t6 | | test1 | | test2 | | test3 | +---------------+ 9 rows in set (0.00 sec)
