连接指定数据库
- -u 指定用户名
- -h 指定主机
- -p 指定需要输入密码
- -d 指定连接的数据库
C:\Users\java-boy>mysql -u root -h 127.0.0.1 -p -d cs_zcsgo
Enter password: ******
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 504
Server version: 10.5.5-MariaDB mariadb.org binary distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
导出数据库
C:\Users\java-boy>mysqldump -u root -p -d wms >wms.sql
Enter password: ******
导出指定表数据
C:\Users\java-boy>mysqldump -u root -p wms role >wms_role.sql
Enter password: ******
导出指定表结构
同上，仅需指定*-d*参数
C:\Users\java-boy>mysqldump -u root -p -d wms role >wms_role.sql
Enter password: ******
导出查询结果集数据
- 先查询mysql允许导出的安全路径
secure_file_priv
mysql> show variables like '%secure%';
+--------------------------+-----------------------+
| Variable_name | Value |
+--------------------------+-----------------------+
| require_secure_transport | OFF |
| secure_auth | ON |
| secure_file_priv | /var/lib/mysql-files/ |
+--------------------------+-----------------------+
3 rows in set (0.01 sec)
- 将查询结果集保存至安全路径
select * from r_outbound_record into outfile '/var/lib/mysql-files/data.csv';
导出的data.csv只包含了表数据如下
ZS11 \N \N \N \N TP1904131002 0005 0123 978500080186 1 \N \N \N shipped \N bjdcd_temp 2019-4-13 12:12:12 \N \N \N CK0110 CK01100004 \N \N \N \N \N 0120 广州铁路局 \N \N no 厕所门锁体 \N \N \N \N 74 \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N
ZS11 \N \N \N \N XQ2007060121 0001 0101 19010002100 1.0 \N JSW0 973600050008 shipped \N \N 2020-07-06 15:55:43 \N \N \N CK200706050620008 CK2007060506200080000 \N \N \N \N \N 0105 \N \N XQ20200706454138 no 荧光灯管110W \N \N \N \N EA \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N
ZS11 \N 1 \N \N XQ1910240034 0009 0101 35000064279 1.0 \N 25WW \N shipped \N \N 2019-10-24 10:54:43 \N \N \N CK191024050600008 CK1910240506000080009 \N \N \N \N \N 0105 \N \N XQ20191024588466 no VIP控制接线盒 \N \N \N \N 74 \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N
数据格式默认以 Tab 分隔，但我们也可指,为定分隔符
select * from r_outbound_record into outfile '/var/lib/mysql-files/data.csv' fields terminated by ',';
导入csv文件到指定表
mysql> load data infile '/var/lib/mysql-files/data.csv' into table r_outbound_record;
Query OK, 1 row affected (0.01 sec)
Records: 1 Deleted: 0 Skipped: 0 Warnings: 0
-
如果导入过程中有唯一性约束，可以选择直接覆盖
replace intoload data infile '/var/lib/mysql-files/data.csv' replace into table r_outbound_record; -
或者使用
ignore into则跳过load data infile '/var/lib/mysql-files/data.csv' ignore into table r_outbound_record; -
如果数据值被某种符号包围，则需要指定
fields enclosed bymysql> load data infile "/data/mysql/e.sql" into table e fields enclosed by '"'; Query OK, 1 row affected (0.01 sec) Records: 1 Deleted: 0 Skipped: 0 Warnings: 0 -
同样我们也可以指定导入表的字段
load data infile ... into table db.tbname (col1,col2,...) -
或者set指定字段
load data infile ... into table db.tbname (a,b,c,d,e,f) set g=11,h='xxx'; -
如果指定了分割符的话，则需要指定
fields terminated by ','mysql> load data infile '/var/lib/mysql-files/data.csv' into table r_outbound_record fields terminated by ','; Query OK, 1 row affected (0.01 sec) Records: 1 Deleted: 0 Skipped: 0 Warnings: 0 -
如果指定了换行符的话，则需指定
lines terminated by '\n'mysql> load data infile '/var/lib/mysql-files/data.csv' into table r_outbound_record lines terminated by '\n'; Query OK, 1 row affected (0.01 sec) Records: 1 Deleted: 0 Skipped: 0 Warnings: 0 -
如果指定了字符集的话，则需要指定
character set gbkload data infile '\物资目录\目录1.csv' replace into table rmstat_catalogdt character set gbk;如果字符集不匹配的话可能会报如下错误：
ERROR 1366 (22007): Incorrect string value: '\xC1\xB6\xB8\xD6\xC9\xFA...' for column `zc_sgo`.`rmstat_catalogdt`.`SMATLNAME` at row 1此时指定
character set gbk即可解决该问题。
完整的导入示例如下：
MariaDB [zc_sgo]> load data infile '\物资目录\目录1.csv' replace into table rmstat_catalogdt character set gbk fields enclosed by '"' terminated by ',' lines terminated by '\n';
Query OK, 388458 rows affected, 65535 warnings (10.643 sec)
Records: 388458 Deleted: 0 Skipped: 0 Warnings: 388697
导入过程中可能会遇到以下报错：
ERROR 1262 (01000): Row 1 was truncated; it contained more data than there were input columns
解决方法如下：
MariaDB [zc_sgo]> show variables like '%sql_mode%';
+---------------+-------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-------------------------------------------------------------------------------------------+
| sql_mode | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------+
1 row in set (0.107 sec)
MariaDB [zc_sgo]> set sql_mode = '';
Query OK, 0 rows affected (0.000 sec)
复制表结构及数据到新表
create table a as select * from b ;
查看所有数据库
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| chd-mes |
| cs_zcsgo |
| dl_sgo |
| information_schema |
| jpress |
| mro_yansi |
| mysql |
| performance_schema |
| sfs_mro_test |
| test |
| wms |
| zc_sgo |
+--------------------+
12 rows in set (0.192 sec)
查看当前数据库中的所有表
MariaDB [test]> show tables;
Empty set (0.001 sec)
查看当前库的变量信息
mysql> show variables;
+----------------------------------------------------------+------------------------------------------------------------------------------------------+
| Variable_name | Value |
+----------------------------------------------------------+------------------------------------------------------------------------------------------+
| automatic_sp_privileges | ON |
| avoid_temporal_upgrade | OFF |
| binlog_gtid_simple_recovery | ON |
| binlog_rows_query_log_events | OFF |
| character_set_server | utf8 |
| collation_server | utf8_general_ci |
| div_precision_increment | 4 |
| eq_range_index_dive_limit | 200 |
| event_scheduler | OFF |
| have_compress | YES |
| have_crypt | YES |
| have_dynamic_loading | YES |
| have_geometry | YES |
| have_openssl | YES |
| have_profiling | YES |
| have_query_cache | YES |
| have_rtree_keys | YES |
| have_ssl | YES |
| have_statement_timeout | YES |
| have_symlink | DISABLED |
| init_slave | |
| innodb_adaptive_flushing | ON |
| innodb_adaptive_flushing_lwm | 10 |
| innodb_adaptive_hash_index | ON |
| innodb_adaptive_hash_index_parts | 8 |
| innodb_adaptive_max_sleep_delay | 150000 |
| innodb_api_bk_commit_interval | 5 |
| innodb_api_trx_level | 0 |
| innodb_compression_level | 6 |
| innodb_flushing_avg_loops | 30 |
| innodb_force_recovery | 0 |
| innodb_ft_server_stopword_table | |
| innodb_numa_interleave | OFF |
| innodb_use_native_aio | ON |
| innodb_version | 5.7.17 |
| interactive_timeout | 28800 |
| key_cache_division_limit | 100 |
| log_bin_use_v1_row_events | OFF |
| log_error_verbosity | 3 |
| log_slave_updates | OFF |
| log_slow_slave_statements | OFF |
| master_verify_checksum | OFF |
| myisam_recover_options | OFF |
| mysql_native_password_proxy_users | OFF |
| ndb_eventbuffer_free_percent | 20 |
| ndb_eventbuffer_max_alloc | 0 |
| ndb_log_exclusive_reads | OFF |
| ndb_recv_thread_activation_threshold | 8 |
| ndb_recv_thread_cpu_mask | |
| ndb_slave_conflict_role | NONE |
| ndb_version | 460037 |
| ndb_version_string | ndb-7.5.5 |
| ndbinfo_version | 460037 |
| optimizer_prune_level | 1 |
| performance_schema_events_stages_history_long_size | 10000 |
| performance_schema_events_stages_history_size | 10 |
| performance_schema_events_statements_history_long_size | 10000 |
| performance_schema_events_statements_history_size | 10 |
| performance_schema_events_transactions_history_long_size | 10000 |
| performance_schema_events_transactions_history_size | 10 |
| performance_schema_events_waits_history_long_size | 10000 |
| performance_schema_events_waits_history_size | 10 |
| protocol_version | 10 |
| pseudo_slave_mode | OFF |
| query_cache_wlock_invalidate | OFF |
| relay_log_recovery | OFF |
| rpl_stop_slave_timeout | 31536000 |
| secure_file_priv | /var/lib/mysql-files/ |
| server_id | 4 |
| server_id_bits | 32 |
| server_uuid | fc45f184-eb8e-11e8-944f-fa163e4a0fb7 |
| session_track_system_variables | time_zone,autocommit,character_set_client,character_set_results,character_set_connection |
| skip_name_resolve | ON |
| slave_allow_batching | OFF |
| slave_checkpoint_group | 512 |
| slave_checkpoint_period | 300 |
| slave_compressed_protocol | OFF |
| slave_exec_mode | STRICT |
| slave_load_tmpdir | /tmp |
| slave_max_allowed_packet | 1073741824 |
| slave_net_timeout | 60 |
| slave_parallel_type | DATABASE |
| slave_parallel_workers | 0 |
| slave_pending_jobs_size_max | 16777216 |
| slave_preserve_commit_order | OFF |
| slave_rows_search_algorithms | TABLE_SCAN,INDEX_SCAN |
| slave_skip_errors | OFF |
| slave_sql_verify_checksum | ON |
| slave_transaction_retries | 10 |
| slave_type_conversions | |
| sql_slave_skip_counter | 0 |
| tls_version | TLSv1,TLSv1.1 |
| updatable_views_with_limit | YES |
| validate_password_check_user_name | OFF |
| validate_password_dictionary_file | |
| validate_password_length | 8 |
| validate_password_mixed_case_count | 1 |
| validate_password_number_count | 1 |
| validate_password_policy | MEDIUM |
| validate_password_special_char_count | 1 |
| version | 5.7.17-ndb-7.5.5-cluster-gpl-log |
| version_comment | MySQL Cluster Community Server (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+----------------------------------------------------------+------------------------------------------------------------------------------------------+
104 rows in set (0.00 sec)
查看指定表结构
MariaDB [wms]> describe unit;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| unit_id | int(11) | NO | PRI | NULL | auto_increment |
| unit_code | text | NO | | NULL | |
| unit_name | varchar(20) | NO | | NULL | |
| memo | text | YES | | NULL | |
| company_id | int(11) | YES | | NULL | |
+------------+-------------+------+-----+---------+----------------+
5 rows in set (0.537 sec)
切换数据库
MariaDB [cs_zcsgo]> use wms;
Database changed
MariaDB [wms]>
查看当前选择的数据库
MariaDB [wms]> select database();
+------------+
| database() |
+------------+
| wms |
+------------+
1 row in set (0.000 sec)
查看当前登陆用户
MariaDB [(none)]> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.040 sec)
查看数据库版本
MariaDB [wms]> select version();
+----------------+
| version() |
+----------------+
| 10.5.5-MariaDB |
+----------------+
1 row in set (0.135 sec)
查看时间
MariaDB [wms]> select now();
+---------------------+
| now() |
+---------------------+
| 2021-07-16 09:51:12 |
+---------------------+
1 row in set (0.238 sec)
查看 年、月、日
MariaDB [wms]> select day(current_date);
+-------------------+
| day(current_date) |
+-------------------+
| 16 |
+-------------------+
1 row in set (0.033 sec)
MariaDB [wms]> select month(current_date);
+---------------------+
| month(current_date) |
+---------------------+
| 7 |
+---------------------+
1 row in set (0.018 sec)
MariaDB [wms]> select year(current_date);
+--------------------+
| year(current_date) |
+--------------------+
| 2021 |
+--------------------+
1 row in set (0.000 sec)
查个字符串
MariaDB [wms]> select "查个字符串";
+------------+
| 查个字符串 |
+------------+
| 查个字符串 |
+------------+
1 row in set (0.074 sec)
算数
MariaDB [wms]> select 1*2+3-4/5;
+-----------+
| 1*2+3-4/5 |
+-----------+
| 4.2000 |
+-----------+
1 row in set (0.037 sec)
拼接字符串
MariaDB [wms]> select concat("a","b","c","d");
+-------------------------+
| concat("a","b","c","d") |
+-------------------------+
| abcd |
+-------------------------+
1 row in set (0.000 sec)
建库
MariaDB [wms]> create database wms2;
Query OK, 1 row affected (0.051 sec)
建表
MariaDB [wms2]> create table test(id varchar(255),name varchar(255),age int(20));
Query OK, 0 rows affected (0.220 sec)
单条插入
MariaDB [wms2]> insert into test(id,name,age) values(4,"zhaoliu",22);
Query OK, 1 row affected (0.129 sec)
批量插入
MariaDB [wms2]> insert into test values("1","zhangsan",10),("2","lisi",15),("3","wangwu",20);
Query OK, 3 rows affected (0.254 sec)
Records: 3 Duplicates: 0 Warnings: 0
批量更新
MariaDB [wms2]> update test set age=25 where id in(1,2,3);
Query OK, 3 rows affected (0.219 sec)
Rows matched: 3 Changed: 3 Warnings: 0
连接更新
在MySQL中，可以在 UPDATE语句 中使用JOIN子句执行跨表更新
UPDATE T1
[INNER JOIN | LEFT JOIN] T2 ON T1.C1 = T2. C1
SET T1.C2 = T2.C2,
T2.C3 = expr
WHERE condition
更详细地看看MySQL UPDATE JOIN语法：
- 首先，在
UPDATE子句之后，指定主表(T1)和希望主表连接表(T2)。 - 第二，指定一种要使用的连接，即
INNER JOIN或LEFT JOIN和连接条件。JOIN子句必须出现在UPDATE子句之后。 - 第三，要为要更新的
T1和/或T2表中的列分配新值。 - 第四，WHERE子句中的条件用于指定要更新的行。
示例：
update shanghai a INNER JOIN zc_sgo.m_materiel_luju_info b on(a.wuliao=b.materiel_code)
set a.wuzi = b.lujuCode;
批量删除
MariaDB [wms2]> delete from test where id in(2,3);
Query OK, 2 rows affected (0.054 sec)
删表
MariaDB [wms2]> drop table test;
Query OK, 0 rows affected (0.367 sec)
删库跑路
MariaDB [wms2]> drop database wms2;
Query OK, 0 rows affected (0.127 sec)
登陆后执行指定.sql文件（高级删库跑路）
drop database wms2;
C:\Users\java-boy>mysql < C:\Users\java-boy\demo.sql -u root -p
Enter password: ******
添加PRIMARY KEY（主键索引）
ALTER TABLE table_name ADD PRIMARY KEY ( column )
添加UNIQUE (唯一索引)
ALTER TABLE table_name ADD UNIQUE ( column )
添加INDEX (普通索引)
ALTER TABLE 表名 ADD INDEX 索引名( 字段名 )
添加FULLTEXT (全文索引)
ALTER TABLE table_name ADD FULLTEXT ( column)
添加多列索引
ALTER TABLE table_name ADD INDEX index_name ( column1, column2, column3 )
常见问题
Mysql同一局域网内访问设置
1.允许所有内网主机访问
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
root 123456 需要更改为被允许访问的账户密码
2.允许指定IP访问
GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.3' IDENTIFIED BY '123456' WITH GRANT OPTION;
root 192.168.1.3 123456 需要更改为被允许访问的账户、IP及密码
3.检查防火墙
检查防火墙设置是否关闭防火墙，或者是否包含3306端口的入站规则(关于出入站规则不了解的可以访问【操作系统】【Win10常用使用技巧】)
source 命令导入数据库 乱码
mysql -u root -p --default-character-set=utf8
use dbname
source F:/my.sql
