MySQL经常使用命令--create命令使用
2023-09-11 14:20:45 时间
- 切换数据库的字符集
在mysql中的配置文件里:
使用vim /etc/mysql/my.cnf
[client]
default-character-set = utf8
[mysqld]
character-set-server = utf8
这当中的mysql的版本号是mysql 5.6系列的
- create 数据库
create database db_test;
create database if not exists db_test;
drop databases if exists db_test;
- create 表
create table if not exists t_user(
uid varchar(50) not null,
account_kind enum("stock","future"),
stock_cash varchar(30),
stock_sh varchar(30),
stock_sz varchar(30),
stock_pwd varchar(50),
future_BrokerID varchar(30),
future_InvestorID varchar(30),
future_pwd varchar(50),
unique key(stock_cash,stock_sh,stock_sz,stock_pwd),
unique key(future_BrokerID,future_InvestorID,future_pwd),
primary key(uid),
index STOCKINFO(stock_cash,stock_sh,stock_sz,stock_pwd)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;
you can also use this to create index
create table t_TEST(
id int not null,
name varchar(10)
);
create index IDX_TEST_Name on t_TEST(name);
将从一个已经存储好的表中创建一个新的表
mysql> select * from t_subscribe;
+------+-------------+---------------------+
| uid | strategy_id | date |
+------+-------------+---------------------+
| 1233 | 123414 | 2015-06-11 10:19:52 |
| 0 | 0 | 2015-06-11 10:19:52 |
| 1 | 1 | 2015-06-11 10:19:52 |
| 2 | 2 | 2015-06-11 10:19:52 |
| 3 | 3 | 2015-06-11 10:19:52 |
| 4 | 4 | 2015-06-11 10:19:52 |
| 5 | 5 | 2015-06-11 10:19:52 |
| 6 | 6 | 2015-06-11 10:19:52 |
| 7 | 7 | 2015-06-11 10:19:52 |
| 8 | 8 | 2015-06-11 10:19:52 |
| 9 | 9 | 2015-06-11 10:19:52 |
+------+-------------+---------------------+
11 rows in set (0.00 sec)
mysql> create table test select * from t_subscribe;
Query OK, 11 rows affected (0.03 sec)
Records: 11 Duplicates: 0 Warnings: 0
mysql> select * from test;
+------+-------------+---------------------+
| uid | strategy_id | date |
+------+-------------+---------------------+
| 1233 | 123414 | 2015-06-11 10:19:52 |
| 0 | 0 | 2015-06-11 10:19:52 |
| 1 | 1 | 2015-06-11 10:19:52 |
| 2 | 2 | 2015-06-11 10:19:52 |
| 3 | 3 | 2015-06-11 10:19:52 |
| 4 | 4 | 2015-06-11 10:19:52 |
| 5 | 5 | 2015-06-11 10:19:52 |
| 6 | 6 | 2015-06-11 10:19:52 |
| 7 | 7 | 2015-06-11 10:19:52 |
| 8 | 8 | 2015-06-11 10:19:52 |
| 9 | 9 | 2015-06-11 10:19:52 |
+------+-------------+---------------------+
mysql> insert into test select * from t_subscribe;
Query OK, 11 rows affected (0.00 sec)
Records: 11 Duplicates: 0 Warnings: 0
mysql> select * from test;
+------+-------------+---------------------+
| uid | strategy_id | date |
+------+-------------+---------------------+
| 1233 | 123414 | 2015-06-11 10:19:52 |
| 0 | 0 | 2015-06-11 10:19:52 |
| 1 | 1 | 2015-06-11 10:19:52 |
| 2 | 2 | 2015-06-11 10:19:52 |
| 3 | 3 | 2015-06-11 10:19:52 |
| 4 | 4 | 2015-06-11 10:19:52 |
| 5 | 5 | 2015-06-11 10:19:52 |
| 6 | 6 | 2015-06-11 10:19:52 |
| 7 | 7 | 2015-06-11 10:19:52 |
| 8 | 8 | 2015-06-11 10:19:52 |
| 9 | 9 | 2015-06-11 10:19:52 |
| 1233 | 123414 | 2015-06-11 10:19:52 |
| 0 | 0 | 2015-06-11 10:19:52 |
| 1 | 1 | 2015-06-11 10:19:52 |
| 2 | 2 | 2015-06-11 10:19:52 |
| 3 | 3 | 2015-06-11 10:19:52 |
| 4 | 4 | 2015-06-11 10:19:52 |
| 5 | 5 | 2015-06-11 10:19:52 |
| 6 | 6 | 2015-06-11 10:19:52 |
| 7 | 7 | 2015-06-11 10:19:52 |
| 8 | 8 | 2015-06-11 10:19:52 |
| 9 | 9 | 2015-06-11 10:19:52 |
+------+-------------+---------------------+
22 rows in set (0.00 sec)
相关文章
- Python——MySQL操作,使用mysql.connector
- PHP连接MySQL数据库的三种方式(mysql、mysqli、pdo)
- Mysql日期和时间总结
- 使用mysql-connector-python操作MYSQL数据库
- 数据库 MySQL SQLite 简介 API [MD]
- Linux启动/停止/重启Mysql数据库的方法
- PHP连接MySQL数据库的三种方式(mysql、mysqli、pdo)--续
- MySQL保留字不能作为字段名使用_Table字段不能设为关键字range,range在mysql中是分区,是mysql的关键字
- Mysql 间隙锁原理,以及Repeatable Read隔离级别下可以防止幻读原理(百度)
- MySQL用户权限(Host,User,Password)管理(mysql.user)
- Starting MySQL ERROR! Couldn‘t find MySQL server (/usr/local/mysql/bin/mysqld_safe)
- Python:mysql-replication监控MySQL的binlog变动
- ERROR 3009 (HY000): Column count of mysql.user is wrong. Expected 45, found 43. Created with MySQL 5
- Mysql报错:Can't connect to local MySQL server through socket '/tmp/mysql.sock'
- MySQL还能这样玩---第一篇之你所不知道的命令
- Mysql mysqldump命令使用之 --single-transaction --hex-blob
- [MySQL] 解决办法: Error: Transaction test error: file /etc/my.cnf from install of mysql-community-server