coding mysql_mysql 基础使用

基础使用

创建新的数据库、用户并授权

登录mysql

mysql -u root -p

按提示输入root密码

~# mysql -u root -p

Enter password:

数据库操作

创建数据库

#create database dbname;

#CREATE DATABASE `todo` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

mysql> create database eva_db character set utf8mb4;

查看当前的数据库

mysql> show databases;

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

| Database           |

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

| eva_db             |

| information_schema |

| mysql              |

| performance_schema |

| sys                |

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

5 rows in set (0.00 sec)

显示当前数据库的字符集

mysql> show character set;

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

| Charset  | Description                     | Default collation   | Maxlen |

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

| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |

| ascii    | US ASCII                        | ascii_general_ci    |      1 |

| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |

| binary   | Binary pseudo charset           | binary              |      1 |

| cp1250   | Windows Central European        | cp1250_general_ci   |      1 |

| cp1251   | Windows Cyrillic                | cp1251_general_ci   |      1 |

| cp1256   | Windows Arabic                  | cp1256_general_ci   |      1 |

| cp1257   | Windows Baltic                  | cp1257_general_ci   |      1 |

| cp850    | DOS West European               | cp850_general_ci    |      1 |

| cp852    | DOS Central European            | cp852_general_ci    |      1 |

| cp866    | DOS Russian                     | cp866_general_ci    |      1 |

| cp932    | SJIS for Windows Japanese       | cp932_japanese_ci   |      2 |

| dec8     | DEC West European               | dec8_swedish_ci     |      1 |

| eucjpms  | UJIS for Windows Japanese       | eucjpms_japanese_ci |      3 |

| euckr    | EUC-KR Korean                   | euckr_korean_ci     |      2 |

| gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |

| gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |

| gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |

| geostd8  | GEOSTD8 Georgian                | geostd8_general_ci  |      1 |

| greek    | ISO 8859-7 Greek                | greek_general_ci    |      1 |

| hebrew   | ISO 8859-8 Hebrew               | hebrew_general_ci   |      1 |

| hp8      | HP West European                | hp8_english_ci      |      1 |

| keybcs2  | DOS Kamenicky Czech-Slovak      | keybcs2_general_ci  |      1 |

| koi8r    | KOI8-R Relcom Russian           | koi8r_general_ci    |      1 |

| koi8u    | KOI8-U Ukrainian                | koi8u_general_ci    |      1 |

| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |

| latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |

| latin5   | ISO 8859-9 Turkish              | latin5_turkish_ci   |      1 |

| latin7   | ISO 8859-13 Baltic              | latin7_general_ci   |      1 |

| macce    | Mac Central European            | macce_general_ci    |      1 |

| macroman | Mac West European               | macroman_general_ci |      1 |

| sjis     | Shift-JIS Japanese              | sjis_japanese_ci    |      2 |

| swe7     | 7bit Swedish                    | swe7_swedish_ci     |      1 |

| tis620   | TIS620 Thai                     | tis620_thai_ci      |      1 |

| ucs2     | UCS-2 Unicode                   | ucs2_general_ci     |      2 |

| ujis     | EUC-JP Japanese                 | ujis_japanese_ci    |      3 |

| utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |

| utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |

| utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |

| utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |

| utf8mb4  | UTF-8 Unicode                   | utf8mb4_0900_ai_ci  |      4 |

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

41 rows in set (0.00 sec)

修改数据库的字符集

mysql> alter database eva_db character set gbk;

Query OK, 1 row affected (0.01 sec)

创建数据库以及效验规则

mysql> create database eva_db character set utf8 collate utf8_general_ci;

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

查询当前数据库

mysql> select database();

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

| database() |

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

| eva_db     |

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

1 row in set (0.00 sec)

切换数据库

mysql> use sys;

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

查看当前选择(use)的数据库

mysql> status;

--------------

mysql  Ver 8.0.20 for macos10.15 on x86_64 (MySQL Community Server - GPL)

Connection id:  8

Current database: eva_db

Current user:  root@localhost

SSL:   Not in use

Current pager:  stdout

Using outfile:  ''

Using delimiter: ;

Server version:  8.0.21 Homebrew

Protocol version: 10

Connection:  Localhost via UNIX socket

Server characterset: utf8mb4

Db     characterset: utf8

Client characterset: utf8mb4

Conn.  characterset: utf8mb4

UNIX socket:  /tmp/mysql.sock

Binary data as:  Hexadecimal

Uptime:   28 min 43 sec

Threads: 2  Questions: 131  Slow queries: 0  Opens: 220  Flush tables: 3  Open tables: 142  Queries per second avg: 0.076

--------------

显示当前数据库的创建信息

show create database eva_db;

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

| Database | Create Database                                                                                                                  |

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

| eva_db   | CREATE DATABASE `eva_db` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |

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

1 row in set (0.00 sec)

删除数据库

mysql> drop database eva_db;

Query OK, 1 row affected (0.02 sec)

数据表操作

选择数据库

mysql> use eva_db;

Database changed

创建表

mysql> CREATE TABLE t_user(

key_id VARCHAR(255) NOT NULL PRIMARY KEY,  -- id 统一命名为key_id

user_name VARCHAR(255) NOT NULL ,

password VARCHAR(255) NOT NULL ,

phone VARCHAR(255) NOT NULL,

deleted INT NOT NULL DEFAULT 0,  -- 逻辑删除标志默认值

create_time   timestamp NULL default CURRENT_TIMESTAMP, -- 创建时间默认值

update_time   timestamp NULL default CURRENT_TIMESTAMP -- 修改时间默认值

) ENGINE=INNODB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8mb4;

Query OK, 0 rows affected (0.01 sec)

显示所有表

mysql> show tables;

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

| Tables_in_eva_db |

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

| eva_user         |

| t_user           |

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

2 rows in set (0.00 sec)

表结构详细描述

#  describe tablename; or desc tablename;

mysql> describe t_user;

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

| Field       | Type         | Null | Key | Default           | Extra             |

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

| key_id      | varchar(255) | NO   | PRI | NULL              |                   |

| user_name   | varchar(255) | NO   |     | NULL              |                   |

| password    | varchar(255) | NO   |     | NULL              |                   |

| phone       | varchar(255) | NO   |     | NULL              |                   |

| deleted     | int          | NO   |     | 0                 |                   |

| create_time | timestamp    | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |

| update_time | timestamp    | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |

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

7 rows in set (0.01 sec)

复制表结构

# create table newtable like oldtable;

mysql>  create table e_user like t_user;

Query OK, 0 rows affected (0.01 sec)

复制表数据

# insert into newtable select * from oldtable;

mysql> insert into e_user select * from t_user;

Query OK, 0 rows affected (0.01 sec)

Records: 0  Duplicates: 0  Warnings: 0

重命名表，同时命名多个表用逗号“,”分割

#rename table tablelname to new_tablelname

mysql> rename table e_user to eva_user;

Query OK, 0 rows affected (0.01 sec)

删除表

# drop table tablename;

mysql> drop table eva_user;

Query OK, 0 rows affected (0.00 sec)