wamp环境下,我可以手敲一遍,但是lamp环境下我绝对不会手敲一遍
好吧~写脚本的确也是一遍~~~~(>_<)~~~~
函数和后面的触发器中文档上局部是有错误的,所以大家不要一味的相信文档,最好自己亲自执行一边~
\G参数在navicat Preminum下会出错,但是在wamp下mysql的控制台上不会报错,原因未知,google没有找到,
或许工具不支持吧
去除id自增
mysql> alter table t1 modify id int;
linux
偷懒了,有些命令没写~
脚本内容如下,非常简单
#!/bin/bash mysql -uroot -p000000 <<EOF show databases;
create database if not exists test;
use test;
#create table t1
create table t1 (
id int primary key auto_increment,
name char()
);
insert into t1 values(,'jack'),(,'rose'),(,'mary');
desc t1; #copy
create table t2 like t1;
insert into t2 select * from t1;
create table t3 select * from t1; #math
select abs(-);
select bin();
select CEILING();
select FLOOR();
select GREATEST(,,,);
select LEAST(,,,,);
select LN();
select log();
select mod(,);
select pi();
select rand();
select round(,);
select sign();
select sqrt();
select truncate(123.23,);
#string
select ascii();
select bit_length();
select concat(,,,);
select concat_ws(,,,,);
SELECT CONCAT_WS("-", "SQL", "Tutorial", "is", "fun!");
select lcase("AVNA");
select lower("AVNA");
select ucase("avna");
select upper("avna");
select left("hello world",);
select length("hello world");
select ltrim(" hello world");
SELECT POSITION("3" IN "W3Schools.com");
SELECT REPEAT("SQL Tutorial", );
select REVERSE("str");
select RIGHT("str",);
select RTRIM("str ");
select trim(" 123 34 ");
#time
select CURDATE();
select CURRENT_DATE();
select CURTIME();
select CURRENT_TIME();
SELECT DATE_FORMAT("2017-06-15", "%Y");
SELECT HOUR("2017-06-20 09:34:00");
SELECT MINUTE("2017-06-20 09:34:00");
SELECT MONTH("2017-06-20 09:34:00");
select now();
SELECT year("2017-06-20 09:34:00");
SELECT WEEK("2017-06-20 09:34:00");
#pass
select MD5();
select password();
select sha();
#format
select format("1231231",);
select inet_aton("192.168.13.14");
select inet_ntoa("3232238862");
#system
select database();
select benchmark(,"12");
select connection_id();
select FOUND_ROWS();
select USER();
select SYSTEM_USER();
select VERSION();
#show
SHOW CHARACTER SET ;
SHOW COLLATION ;
SHOW COLUMNS FROM t3 ;
SHOW CREATE DATABASE test;
SHOW DATABASES;
SHOW ENGINES ;
SHOW INDEX from t3;
SHOW TABLES ;
SHOW VARIABLES;
#prepare
prepare s1 from 'select * from t1 where id>?';
set @i=;
execute s1 using @i;
drop prepare s1;
#Transaction
set autocommit=;
begin;
delete from t1 where id = ;
savepoint p1;
delete from t1 where id = ;
savepoint p2;
delete from t1 where id = ;
rollback to p2;
rollback to p1;
rollback;
commit;
#view
create view v_t1 as select * from t1 where id> and id<;
show tables;
select * from v_t1;
drop view v_t1;
#tmp
create temporary table tmp1 (id int) ;
#vir
select now() from dual;
#truncate
truncate table t1;
select * from t2 into outfile '/tmp/t2.txt';
truncate t2;
load data infile '/tmp/t2.txt' into table t2;
#index
alter table t2 add index index_name(name);
alter table t2 add unique uniqe_name(name);
show index from t2;
#store
\d //
create procedure p1()
begin
set @i=;
while @i< do
select * from t1 where id=@i;
set @i=@i+;
end while;
end//
\d ;
call p1;
drop procedure p1;
\d //
create trigger tg1 before insert on t2 for each row
begin
insert into t3(name) values("he");
end//
\d ;
insert into t2(name) values("hello");
EOF
执行结果
[root@centos_6_8 ~]# sh mysql.sh
Database
information_schema
mysql
performance_schema
test
Field Type Null Key Default Extra
id int(11) NO PRI NULL auto_increment
name char(50) YES NULL
abs(-1)
1
bin(2)
10
CEILING(1)
1
FLOOR(2)
2
GREATEST(1,2,5,3)
5
LEAST(1,2,3,5,6)
1
LN(10)
2.302585092994046
log(12)
2.4849066497880004
mod(10,5)
0
pi()
3.141593
rand()
0.5353721264147872
round(1,10)
1
sign(2)
1
sqrt(4)
2
truncate(123.23,1)
123.2
ascii(12)
49
bit_length(123)
24
concat(1,2,3,4)
1234
concat_ws(0,1,2,3,4)
1020304
CONCAT_WS("-", "SQL", "Tutorial", "is", "fun!")
SQL-Tutorial-is-fun!
lcase("AVNA")
avna
lower("AVNA")
avna
ucase("avna")
AVNA
upper("avna")
AVNA
left("hello world",4)
hell
length("hello world")
11
ltrim(" hello world")
hello world
POSITION("3" IN "W3Schools.com")
2
REPEAT("SQL Tutorial", 3)
SQL TutorialSQL TutorialSQL Tutorial
REVERSE("str")
rts
RIGHT("str",2)
tr
RTRIM("str ")
str
trim(" 123 34 ")
123 34
CURDATE()
2018-09-26
CURRENT_DATE()
2018-09-26
CURTIME()
03:49:09
CURRENT_TIME()
03:49:09
DATE_FORMAT("2017-06-15", "%Y")
2017
HOUR("2017-06-20 09:34:00")
9
MINUTE("2017-06-20 09:34:00")
34
MONTH("2017-06-20 09:34:00")
6
now()
2018-09-26 03:49:09
year("2017-06-20 09:34:00")
2017
WEEK("2017-06-20 09:34:00")
25
MD5(123)
202cb962ac59075b964b07152d234b70
password(123)
*23AE809DDACAF96AF0FD78ED04B6A265E05AA257
sha(123)
40bd001563085fc35165329ea1ff5c5ecbdbbeef
format("1231231",3)
1,231,231.000
inet_aton("192.168.13.14")
3232238862
inet_ntoa("3232238862")
192.168.13.14
database()
test
benchmark(3,"12")
0
connection_id()
37
FOUND_ROWS()
1
USER()
root@localhost
SYSTEM_USER()
root@localhost
VERSION()
5.5.48-log
Charset Description Default collation Maxlen
big5 Big5 Traditional Chinese big5_chinese_ci 2
dec8 DEC West European dec8_swedish_ci 1
cp850 DOS West European cp850_general_ci 1
hp8 HP West European hp8_english_ci 1
koi8r KOI8-R Relcom Russian koi8r_general_ci 1
latin1 cp1252 West European latin1_swedish_ci 1
latin2 ISO 8859-2 Central European latin2_general_ci 1
swe7 7bit Swedish swe7_swedish_ci 1
ascii US ASCII ascii_general_ci 1
ujis EUC-JP Japanese ujis_japanese_ci 3
sjis Shift-JIS Japanese sjis_japanese_ci 2
hebrew ISO 8859-8 Hebrew hebrew_general_ci 1
tis620 TIS620 Thai tis620_thai_ci 1
euckr EUC-KR Korean euckr_korean_ci 2
koi8u KOI8-U Ukrainian koi8u_general_ci 1
gb2312 GB2312 Simplified Chinese gb2312_chinese_ci 2
greek ISO 8859-7 Greek greek_general_ci 1
cp1250 Windows Central European cp1250_general_ci 1
gbk GBK Simplified Chinese gbk_chinese_ci 2
latin5 ISO 8859-9 Turkish latin5_turkish_ci 1
armscii8 ARMSCII-8 Armenian armscii8_general_ci 1
utf8 UTF-8 Unicode utf8_general_ci 3
ucs2 UCS-2 Unicode ucs2_general_ci 2
cp866 DOS Russian cp866_general_ci 1
keybcs2 DOS Kamenicky Czech-Slovak keybcs2_general_ci 1
macce Mac Central European macce_general_ci 1
macroman Mac West European macroman_general_ci 1
cp852 DOS Central European cp852_general_ci 1
latin7 ISO 8859-13 Baltic latin7_general_ci 1
utf8mb4 UTF-8 Unicode utf8mb4_general_ci 4
cp1251 Windows Cyrillic cp1251_general_ci 1
utf16 UTF-16 Unicode utf16_general_ci 4
cp1256 Windows Arabic cp1256_general_ci 1
cp1257 Windows Baltic cp1257_general_ci 1
utf32 UTF-32 Unicode utf32_general_ci 4
binary Binary pseudo charset binary 1
geostd8 GEOSTD8 Georgian geostd8_general_ci 1
cp932 SJIS for Windows Japanese cp932_japanese_ci 2
eucjpms UJIS for Windows Japanese eucjpms_japanese_ci 3
Collation Charset Id Default Compiled Sortlen
big5_chinese_ci big5 1 Yes Yes 1
big5_bin big5 84 Yes 1
dec8_swedish_ci dec8 3 Yes Yes 1
dec8_bin dec8 69 Yes 1
cp850_general_ci cp850 4 Yes Yes 1
cp850_bin cp850 80 Yes 1
hp8_english_ci hp8 6 Yes Yes 1
hp8_bin hp8 72 Yes 1
koi8r_general_ci koi8r 7 Yes Yes 1
koi8r_bin koi8r 74 Yes 1
latin1_german1_ci latin1 5 Yes 1
latin1_swedish_ci latin1 8 Yes Yes 1
latin1_danish_ci latin1 15 Yes 1
latin1_german2_ci latin1 31 Yes 2
latin1_bin latin1 47 Yes 1
latin1_general_ci latin1 48 Yes 1
latin1_general_cs latin1 49 Yes 1
latin1_spanish_ci latin1 94 Yes 1
latin2_czech_cs latin2 2 Yes 4
latin2_general_ci latin2 9 Yes Yes 1
latin2_hungarian_ci latin2 21 Yes 1
latin2_croatian_ci latin2 27 Yes 1
latin2_bin latin2 77 Yes 1
swe7_swedish_ci swe7 10 Yes Yes 1
swe7_bin swe7 82 Yes 1
ascii_general_ci ascii 11 Yes Yes 1
ascii_bin ascii 65 Yes 1
ujis_japanese_ci ujis 12 Yes Yes 1
ujis_bin ujis 91 Yes 1
sjis_japanese_ci sjis 13 Yes Yes 1
sjis_bin sjis 88 Yes 1
hebrew_general_ci hebrew 16 Yes Yes 1
hebrew_bin hebrew 71 Yes 1
tis620_thai_ci tis620 18 Yes Yes 4
tis620_bin tis620 89 Yes 1
euckr_korean_ci euckr 19 Yes Yes 1
euckr_bin euckr 85 Yes 1
koi8u_general_ci koi8u 22 Yes Yes 1
koi8u_bin koi8u 75 Yes 1
gb2312_chinese_ci gb2312 24 Yes Yes 1
gb2312_bin gb2312 86 Yes 1
greek_general_ci greek 25 Yes Yes 1
greek_bin greek 70 Yes 1
cp1250_general_ci cp1250 26 Yes Yes 1
cp1250_czech_cs cp1250 34 Yes 2
cp1250_croatian_ci cp1250 44 Yes 1
cp1250_bin cp1250 66 Yes 1
cp1250_polish_ci cp1250 99 Yes 1
gbk_chinese_ci gbk 28 Yes Yes 1
gbk_bin gbk 87 Yes 1
latin5_turkish_ci latin5 30 Yes Yes 1
latin5_bin latin5 78 Yes 1
armscii8_general_ci armscii8 32 Yes Yes 1
armscii8_bin armscii8 64 Yes 1
utf8_general_ci utf8 33 Yes Yes 1
utf8_bin utf8 83 Yes 1
utf8_unicode_ci utf8 192 Yes 8
utf8_icelandic_ci utf8 193 Yes 8
utf8_latvian_ci utf8 194 Yes 8
utf8_romanian_ci utf8 195 Yes 8
utf8_slovenian_ci utf8 196 Yes 8
utf8_polish_ci utf8 197 Yes 8
utf8_estonian_ci utf8 198 Yes 8
utf8_spanish_ci utf8 199 Yes 8
utf8_swedish_ci utf8 200 Yes 8
utf8_turkish_ci utf8 201 Yes 8
utf8_czech_ci utf8 202 Yes 8
utf8_danish_ci utf8 203 Yes 8
utf8_lithuanian_ci utf8 204 Yes 8
utf8_slovak_ci utf8 205 Yes 8
utf8_spanish2_ci utf8 206 Yes 8
utf8_roman_ci utf8 207 Yes 8
utf8_persian_ci utf8 208 Yes 8
utf8_esperanto_ci utf8 209 Yes 8
utf8_hungarian_ci utf8 210 Yes 8
utf8_sinhala_ci utf8 211 Yes 8
utf8_general_mysql500_ci utf8 223 Yes 1
ucs2_general_ci ucs2 35 Yes Yes 1
ucs2_bin ucs2 90 Yes 1
ucs2_unicode_ci ucs2 128 Yes 8
ucs2_icelandic_ci ucs2 129 Yes 8
ucs2_latvian_ci ucs2 130 Yes 8
ucs2_romanian_ci ucs2 131 Yes 8
ucs2_slovenian_ci ucs2 132 Yes 8
ucs2_polish_ci ucs2 133 Yes 8
ucs2_estonian_ci ucs2 134 Yes 8
ucs2_spanish_ci ucs2 135 Yes 8
ucs2_swedish_ci ucs2 136 Yes 8
ucs2_turkish_ci ucs2 137 Yes 8
ucs2_czech_ci ucs2 138 Yes 8
ucs2_danish_ci ucs2 139 Yes 8
ucs2_lithuanian_ci ucs2 140 Yes 8
ucs2_slovak_ci ucs2 141 Yes 8
ucs2_spanish2_ci ucs2 142 Yes 8
ucs2_roman_ci ucs2 143 Yes 8
ucs2_persian_ci ucs2 144 Yes 8
ucs2_esperanto_ci ucs2 145 Yes 8
ucs2_hungarian_ci ucs2 146 Yes 8
ucs2_sinhala_ci ucs2 147 Yes 8
ucs2_general_mysql500_ci ucs2 159 Yes 1
cp866_general_ci cp866 36 Yes Yes 1
cp866_bin cp866 68 Yes 1
keybcs2_general_ci keybcs2 37 Yes Yes 1
keybcs2_bin keybcs2 73 Yes 1
macce_general_ci macce 38 Yes Yes 1
macce_bin macce 43 Yes 1
macroman_general_ci macroman 39 Yes Yes 1
macroman_bin macroman 53 Yes 1
cp852_general_ci cp852 40 Yes Yes 1
cp852_bin cp852 81 Yes 1
latin7_estonian_cs latin7 20 Yes 1
latin7_general_ci latin7 41 Yes Yes 1
latin7_general_cs latin7 42 Yes 1
latin7_bin latin7 79 Yes 1
utf8mb4_general_ci utf8mb4 45 Yes Yes 1
utf8mb4_bin utf8mb4 46 Yes 1
utf8mb4_unicode_ci utf8mb4 224 Yes 8
utf8mb4_icelandic_ci utf8mb4 225 Yes 8
utf8mb4_latvian_ci utf8mb4 226 Yes 8
utf8mb4_romanian_ci utf8mb4 227 Yes 8
utf8mb4_slovenian_ci utf8mb4 228 Yes 8
utf8mb4_polish_ci utf8mb4 229 Yes 8
utf8mb4_estonian_ci utf8mb4 230 Yes 8
utf8mb4_spanish_ci utf8mb4 231 Yes 8
utf8mb4_swedish_ci utf8mb4 232 Yes 8
utf8mb4_turkish_ci utf8mb4 233 Yes 8
utf8mb4_czech_ci utf8mb4 234 Yes 8
utf8mb4_danish_ci utf8mb4 235 Yes 8
utf8mb4_lithuanian_ci utf8mb4 236 Yes 8
utf8mb4_slovak_ci utf8mb4 237 Yes 8
utf8mb4_spanish2_ci utf8mb4 238 Yes 8
utf8mb4_roman_ci utf8mb4 239 Yes 8
utf8mb4_persian_ci utf8mb4 240 Yes 8
utf8mb4_esperanto_ci utf8mb4 241 Yes 8
utf8mb4_hungarian_ci utf8mb4 242 Yes 8
utf8mb4_sinhala_ci utf8mb4 243 Yes 8
cp1251_bulgarian_ci cp1251 14 Yes 1
cp1251_ukrainian_ci cp1251 23 Yes 1
cp1251_bin cp1251 50 Yes 1
cp1251_general_ci cp1251 51 Yes Yes 1
cp1251_general_cs cp1251 52 Yes 1
utf16_general_ci utf16 54 Yes Yes 1
utf16_bin utf16 55 Yes 1
utf16_unicode_ci utf16 101 Yes 8
utf16_icelandic_ci utf16 102 Yes 8
utf16_latvian_ci utf16 103 Yes 8
utf16_romanian_ci utf16 104 Yes 8
utf16_slovenian_ci utf16 105 Yes 8
utf16_polish_ci utf16 106 Yes 8
utf16_estonian_ci utf16 107 Yes 8
utf16_spanish_ci utf16 108 Yes 8
utf16_swedish_ci utf16 109 Yes 8
utf16_turkish_ci utf16 110 Yes 8
utf16_czech_ci utf16 111 Yes 8
utf16_danish_ci utf16 112 Yes 8
utf16_lithuanian_ci utf16 113 Yes 8
utf16_slovak_ci utf16 114 Yes 8
utf16_spanish2_ci utf16 115 Yes 8
utf16_roman_ci utf16 116 Yes 8
utf16_persian_ci utf16 117 Yes 8
utf16_esperanto_ci utf16 118 Yes 8
utf16_hungarian_ci utf16 119 Yes 8
utf16_sinhala_ci utf16 120 Yes 8
cp1256_general_ci cp1256 57 Yes Yes 1
cp1256_bin cp1256 67 Yes 1
cp1257_lithuanian_ci cp1257 29 Yes 1
cp1257_bin cp1257 58 Yes 1
cp1257_general_ci cp1257 59 Yes Yes 1
utf32_general_ci utf32 60 Yes Yes 1
utf32_bin utf32 61 Yes 1
utf32_unicode_ci utf32 160 Yes 8
utf32_icelandic_ci utf32 161 Yes 8
utf32_latvian_ci utf32 162 Yes 8
utf32_romanian_ci utf32 163 Yes 8
utf32_slovenian_ci utf32 164 Yes 8
utf32_polish_ci utf32 165 Yes 8
utf32_estonian_ci utf32 166 Yes 8
utf32_spanish_ci utf32 167 Yes 8
utf32_swedish_ci utf32 168 Yes 8
utf32_turkish_ci utf32 169 Yes 8
utf32_czech_ci utf32 170 Yes 8
utf32_danish_ci utf32 171 Yes 8
utf32_lithuanian_ci utf32 172 Yes 8
utf32_slovak_ci utf32 173 Yes 8
utf32_spanish2_ci utf32 174 Yes 8
utf32_roman_ci utf32 175 Yes 8
utf32_persian_ci utf32 176 Yes 8
utf32_esperanto_ci utf32 177 Yes 8
utf32_hungarian_ci utf32 178 Yes 8
utf32_sinhala_ci utf32 179 Yes 8
binary binary 63 Yes Yes 1
geostd8_general_ci geostd8 92 Yes Yes 1
geostd8_bin geostd8 93 Yes 1
cp932_japanese_ci cp932 95 Yes Yes 1
cp932_bin cp932 96 Yes 1
eucjpms_japanese_ci eucjpms 97 Yes Yes 1
eucjpms_bin eucjpms 98 Yes 1
Field Type Null Key Default Extra
id int(11) NO 0
name char(50) YES NULL
Database Create Database
test CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */
Database
information_schema
mysql
performance_schema
test
Engine Support Comment Transactions XA Savepoints
MRG_MYISAM YES Collection of identical MyISAM tables NO NO NO
PERFORMANCE_SCHEMA YES Performance Schema NO NO NO
CSV YES CSV storage engine NO NO NO
InnoDB DEFAULT Supports transactions, row-level locking, and foreign keys YES YES YES
MyISAM YES MyISAM storage engine NO NO NO
MEMORY YES Hash based, stored in memory, useful for temporary tables NO NO NO
Tables_in_test
t1
t2
t3
Variable_name Value
auto_increment_increment 1
auto_increment_offset 1
autocommit ON
automatic_sp_privileges ON
back_log 50
basedir /usr/local/mysql
big_tables OFF
binlog_cache_size 32768
binlog_direct_non_transactional_updates OFF
binlog_format MIXED
binlog_stmt_cache_size 32768
bulk_insert_buffer_size 8388608
character_set_client utf8
character_set_connection utf8
character_set_database latin1
character_set_filesystem binary
character_set_results utf8
character_set_server utf8
character_set_system utf8
character_sets_dir /usr/local/mysql/share/charsets/
collation_connection utf8_general_ci
collation_database latin1_swedish_ci
collation_server utf8_general_ci
completion_type NO_CHAIN
concurrent_insert AUTO
connect_timeout 10
datadir /usr/local/mysql/data/
date_format %Y-%m-%d
datetime_format %Y-%m-%d %H:%i:%s
default_storage_engine InnoDB
default_week_format 0
delay_key_write ON
delayed_insert_limit 100
delayed_insert_timeout 300
delayed_queue_size 1000
div_precision_increment 4
engine_condition_pushdown ON
error_count 0
event_scheduler OFF
expire_logs_days 0
external_user
flush OFF
flush_time 0
foreign_key_checks ON
ft_boolean_syntax + -><()~*:""&|
ft_max_word_len 84
ft_min_word_len 4
ft_query_expansion_limit 20
ft_stopword_file (built-in)
general_log OFF
general_log_file /usr/local/mysql/data/centos_6_8.log
group_concat_max_len 1024
have_compress YES
have_crypt YES
have_csv YES
have_dynamic_loading YES
have_geometry YES
have_innodb YES
have_ndbcluster NO
have_openssl NO
have_partitioning YES
have_profiling YES
have_query_cache YES
have_rtree_keys YES
have_ssl NO
have_symlink YES
hostname centos_6_8
identity 0
ignore_builtin_innodb OFF
init_connect
init_file
init_slave
innodb_adaptive_flushing ON
innodb_adaptive_hash_index ON
innodb_additional_mem_pool_size 8388608
innodb_autoextend_increment 8
innodb_autoinc_lock_mode 1
innodb_buffer_pool_instances 1
innodb_buffer_pool_size 134217728
innodb_change_buffering all
innodb_checksums ON
innodb_commit_concurrency 0
innodb_concurrency_tickets 500
innodb_data_file_path ibdata1:10M:autoextend
innodb_data_home_dir
innodb_doublewrite ON
innodb_fast_shutdown 1
innodb_file_format Antelope
innodb_file_format_check ON
innodb_file_format_max Antelope
innodb_file_per_table OFF
innodb_flush_log_at_trx_commit 1
innodb_flush_method
innodb_force_load_corrupted OFF
innodb_force_recovery 0
innodb_io_capacity 200
innodb_large_prefix OFF
innodb_lock_wait_timeout 50
innodb_locks_unsafe_for_binlog OFF
innodb_log_buffer_size 8388608
innodb_log_file_size 5242880
innodb_log_files_in_group 2
innodb_log_group_home_dir ./
innodb_max_dirty_pages_pct 75
innodb_max_purge_lag 0
innodb_mirrored_log_groups 1
innodb_old_blocks_pct 37
innodb_old_blocks_time 0
innodb_open_files 300
innodb_print_all_deadlocks OFF
innodb_purge_batch_size 20
innodb_purge_threads 0
innodb_random_read_ahead OFF
innodb_read_ahead_threshold 56
innodb_read_io_threads 4
innodb_replication_delay 0
innodb_rollback_on_timeout OFF
innodb_rollback_segments 128
innodb_spin_wait_delay 6
innodb_stats_method nulls_equal
innodb_stats_on_metadata ON
innodb_stats_sample_pages 8
innodb_strict_mode OFF
innodb_support_xa ON
innodb_sync_spin_loops 30
innodb_table_locks ON
innodb_thread_concurrency 0
innodb_thread_sleep_delay 10000
innodb_use_native_aio OFF
innodb_use_sys_malloc ON
innodb_version 5.5.48
innodb_write_io_threads 4
insert_id 0
interactive_timeout 28800
join_buffer_size 131072
keep_files_on_create OFF
key_buffer_size 16777216
key_cache_age_threshold 300
key_cache_block_size 1024
key_cache_division_limit 100
large_files_support ON
large_page_size 0
large_pages OFF
last_insert_id 0
lc_messages en_US
lc_messages_dir /usr/local/mysql/share/
lc_time_names en_US
license GPL
local_infile ON
lock_wait_timeout 31536000
locked_in_memory OFF
log OFF
log_bin ON
log_bin_trust_function_creators OFF
log_error /usr/local/mysql/data/centos_6_8.err
log_output FILE
log_queries_not_using_indexes OFF
log_slave_updates OFF
log_slow_queries OFF
log_warnings 1
long_query_time 10.000000
low_priority_updates OFF
lower_case_file_system OFF
lower_case_table_names 0
max_allowed_packet 1048576
max_binlog_cache_size 18446744073709547520
max_binlog_size 1073741824
max_binlog_stmt_cache_size 18446744073709547520
max_connect_errors 10
max_connections 151
max_delayed_threads 20
max_error_count 64
max_heap_table_size 16777216
max_insert_delayed_threads 20
max_join_size 18446744073709551615
max_length_for_sort_data 1024
max_long_data_size 1048576
max_prepared_stmt_count 16382
max_relay_log_size 0
max_seeks_for_key 4294967295
max_sort_length 1024
max_sp_recursion_depth 0
max_tmp_tables 32
max_user_connections 0
max_write_lock_count 4294967295
metadata_locks_cache_size 1024
min_examined_row_limit 0
multi_range_count 256
myisam_data_pointer_size 6
myisam_max_sort_file_size 2146435072
myisam_mmap_size 4294967295
myisam_recover_options OFF
myisam_repair_threads 1
myisam_sort_buffer_size 8388608
myisam_stats_method nulls_unequal
myisam_use_mmap OFF
net_buffer_length 8192
net_read_timeout 30
net_retry_count 10
net_write_timeout 60
new OFF
old OFF
old_alter_table OFF
old_passwords OFF
open_files_limit 1024
optimizer_prune_level 1
optimizer_search_depth 62
optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on
performance_schema OFF
performance_schema_events_waits_history_long_size 10000
performance_schema_events_waits_history_size 10
performance_schema_max_cond_classes 80
performance_schema_max_cond_instances 1000
performance_schema_max_file_classes 50
performance_schema_max_file_handles 32768
performance_schema_max_file_instances 10000
performance_schema_max_mutex_classes 200
performance_schema_max_mutex_instances 1000000
performance_schema_max_rwlock_classes 30
performance_schema_max_rwlock_instances 1000000
performance_schema_max_table_handles 100000
performance_schema_max_table_instances 50000
performance_schema_max_thread_classes 50
performance_schema_max_thread_instances 1000
pid_file /usr/local/mysql/data/centos_6_8.pid
plugin_dir /usr/local/mysql/lib/plugin/
port 3306
preload_buffer_size 32768
profiling OFF
profiling_history_size 15
protocol_version 10
proxy_user
pseudo_slave_mode OFF
pseudo_thread_id 37
query_alloc_block_size 8192
query_cache_limit 1048576
query_cache_min_res_unit 4096
query_cache_size 0
query_cache_type ON
query_cache_wlock_invalidate OFF
query_prealloc_size 8192
rand_seed1 0
rand_seed2 0
range_alloc_block_size 4096
read_buffer_size 262144
read_only OFF
read_rnd_buffer_size 524288
relay_log
relay_log_index
relay_log_info_file relay-log.info
relay_log_purge ON
relay_log_recovery OFF
relay_log_space_limit 0
report_host
report_password
report_port 3306
report_user
rpl_recovery_rank 0
secure_auth OFF
secure_file_priv
server_id 1
skip_external_locking ON
skip_name_resolve OFF
skip_networking OFF
skip_show_database OFF
slave_compressed_protocol OFF
slave_exec_mode STRICT
slave_load_tmpdir /tmp
slave_max_allowed_packet 1073741824
slave_net_timeout 3600
slave_skip_errors OFF
slave_transaction_retries 10
slave_type_conversions
slow_launch_time 2
slow_query_log OFF
slow_query_log_file /usr/local/mysql/data/centos_6_8-slow.log
socket /tmp/mysql.sock
sort_buffer_size 524288
sql_auto_is_null OFF
sql_big_selects ON
sql_big_tables OFF
sql_buffer_result OFF
sql_log_bin ON
sql_log_off OFF
sql_low_priority_updates OFF
sql_max_join_size 18446744073709551615
sql_mode
sql_notes ON
sql_quote_show_create ON
sql_safe_updates OFF
sql_select_limit 18446744073709551615
sql_slave_skip_counter 0
sql_warnings OFF
ssl_ca
ssl_capath
ssl_cert
ssl_cipher
ssl_key
storage_engine InnoDB
stored_program_cache 256
sync_binlog 0
sync_frm ON
sync_master_info 0
sync_relay_log 0
sync_relay_log_info 0
system_time_zone CST
table_definition_cache 400
table_open_cache 64
thread_cache_size 0
thread_concurrency 10
thread_handling one-thread-per-connection
thread_stack 196608
time_format %H:%i:%s
time_zone SYSTEM
timed_mutexes OFF
timestamp 1537904949
tmp_table_size 16777216
tmpdir /tmp
transaction_alloc_block_size 8192
transaction_prealloc_size 4096
tx_isolation REPEATABLE-READ
unique_checks ON
updatable_views_with_limit YES
version 5.5.48-log
version_comment Source distribution
version_compile_machine i686
version_compile_os Linux
wait_timeout 28800
warning_count 0
id name
2 rose
3 mary
Tables_in_test
t1
t2
t3
v_t1
id name
2 rose
3 mary
now()
2018-09-26 03:49:09
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
t2 0 PRIMARY 1 id A 3 NULL NULL BTREE
t2 0 uniqe_name 1 name A 3 NULL NULL YES BTREE
t2 1 index_name 1 name A 3 NULL NULL YES BTREE
windows
mysql> create table t1 (
id int primary key auto_increment,
name char(50)
);
Query OK, 0 rows affected mysql> insert into t1 values(1,'jack'),(2,'rose'),(3,'mary');
Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0 mysql> desc t1;
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(50) | YES | | NULL | |
+-------+----------+------+-----+---------+----------------+
2 rows in set mysql> create table t2 like t1;
Query OK, 0 rows affected mysql> insert into t2 select * from t1;
Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0 mysql> create table t3 select * from t1;
Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0 -- 数学函数 mysql> select abs(-1);
+---------+
| abs(-1) |
+---------+
| 1 |
+---------+
1 row in set mysql> select bin(2
);
+--------+
| bin(2) |
+--------+
| 10 |
+--------+
1 row in set mysql> select CEILING(1
);
+------------+
| CEILING(1) |
+------------+
| 1 |
+------------+
1 row in set mysql> select FLOOR(2
);
+----------+
| FLOOR(2) |
+----------+
| 2 |
+----------+
1 row in set mysql> select GREATEST(1,2,5,3,77
);
+----------------------+
| GREATEST(1,2,5,3,77) |
+----------------------+
| 77 |
+----------------------+
1 row in set mysql> select LEAST(1,2,3,455,6);
+--------------------+
| LEAST(1,2,3,455,6) |
+--------------------+
| 1 |
+--------------------+
1 row in set mysql> select LN(10);
+-------------------+
| LN(10) |
+-------------------+
| 2.302585092994046 |
+-------------------+
1 row in set mysql> select log(12
);
+--------------------+
| log(12) |
+--------------------+
| 2.4849066497880004 |
+--------------------+
1 row in set mysql> select mod(10,5);
+-----------+
| mod(10,5) |
+-----------+
| 0 |
+-----------+
1 row in set mysql> select pi();
+----------+
| pi() |
+----------+
| 3.141593 |
+----------+
1 row in set mysql> select rand();
+--------------------+
| rand() |
+--------------------+
| 0.4066098634215164 |
+--------------------+
1 row in set mysql> select round(1,10);
+-------------+
| round(1,10) |
+-------------+
| 1 |
+-------------+
1 row in set mysql> select sign(2
);
+---------+
| sign(2) |
+---------+
| 1 |
+---------+
1 row in set mysql> select sqrt(4
);
+---------+
| sqrt(4) |
+---------+
| 2 |
+---------+
1 row in set mysql> select truncate(123.123,3
);
+---------------------+
| truncate(123.123,3) |
+---------------------+
| 123.123 |
+---------------------+
1 row in set -- 聚合函数
-- AVG(col) 返回指定列的平均值
-- COUNT(col) 返回指定列中非NULL值的个数
-- MIN(col) 返回指定列的最小值
-- MAX(col) 返回指定列的最大值
-- SUM(col) 返回指定列的所有值之和
-- GROUP_CONCAT(col) 返回由属于一组的列值连接组合而成的结果 -- 字符串
mysql> select ascii(12);
+-----------+
| ascii(12) |
+-----------+
| 49 |
+-----------+
1 row in set mysql> select bit_length(123);
+-----------------+
| bit_length(123) |
+-----------------+
| 24 |
+-----------------+
1 row in set mysql> select concat(1,2,3,4);
+-----------------+
| concat(1,2,3,4) |
+-----------------+
| 1234 |
+-----------------+
1 row in set mysql> select concat_ws(0,1,2,3,4);
+----------------------+
| concat_ws(0,1,2,3,4) |
+----------------------+
| 1020304 |
+----------------------+
1 row in set mysql> SELECT CONCAT_WS("-", "SQL", "Tutorial", "is", "fun!");
+-------------------------------------------------+
| CONCAT_WS("-", "SQL", "Tutorial", "is", "fun!") |
+-------------------------------------------------+
| SQL-Tutorial-is-fun! |
+-------------------------------------------------+
1 row in set mysql> select lcase("AVNA");
+---------------+
| lcase("AVNA") |
+---------------+
| avna |
+---------------+
1 row in set mysql> select lower("AVNA");
+---------------+
| lower("AVNA") |
+---------------+
| avna |
+---------------+
1 row in set mysql> select ucase("avna");
+---------------+
| ucase("avna") |
+---------------+
| AVNA |
+---------------+
1 row in set mysql> select upper("avna");
+---------------+
| upper("avna") |
+---------------+
| AVNA |
+---------------+
1 row in set mysql> select left("hello world",4);
+-----------------------+
| left("hello world",4) |
+-----------------------+
| hell |
+-----------------------+
1 row in set mysql> select length
("hello world");
+-----------------------+
| length("hello world") |
+-----------------------+
| 11 |
+-----------------------+
1 row in set mysql> select ltrim(" hello world");
+------------------------+
| ltrim(" hello world") |
+------------------------+
| hello world |
+------------------------+
1 row in set mysql> SELECT POSITION("3" IN "W3Schools.com");
+----------------------------------+
| POSITION("3" IN "W3Schools.com") |
+----------------------------------+
| 2 |
+----------------------------------+
1 row in set mysql> SELECT REPEAT("SQL Tutorial", 3);
+--------------------------------------+
| REPEAT("SQL Tutorial", 3) |
+--------------------------------------+
| SQL TutorialSQL TutorialSQL Tutorial |
+--------------------------------------+
1 row in set mysql> select REVERSE("str");
+----------------+
| REVERSE("str") |
+----------------+
| rts |
+----------------+
1 row in set mysql> select RIGHT("str",2);
+----------------+
| RIGHT("str",2) |
+----------------+
| tr |
+----------------+
1 row in set mysql> select RTRIM("str ");
+----------------+
| RTRIM("str ") |
+----------------+
| str |
+----------------+
1 row in set mysql> select trim(" 123 34 ");
+------------------+
| trim(" 123 34 ") |
+------------------+
| 123 34 |
+------------------+
1 row in set -- 日期时间函数
mysql> select CURDATE();
+------------+
| CURDATE() |
+------------+
| 2018-09-25 |
+------------+
1 row in set mysql> select CURRENT_DATE();
+----------------+
| CURRENT_DATE() |
+----------------+
| 2018-09-25 |
+----------------+
1 row in set mysql> select CURTIME();
+-----------+
| CURTIME() |
+-----------+
| 17:15:02 |
+-----------+
1 row in set mysql> select CURRENT_TIME();
+----------------+
| CURRENT_TIME() |
+----------------+
| 17:15:11 |
+----------------+
1 row in set mysql> SELECT DATE_FORMAT("2017-06-15", "%Y");
+---------------------------------+
| DATE_FORMAT("2017-06-15", "%Y") |
+---------------------------------+
| 2017 |
+---------------------------------+
1 row in set Format Description
%a Abbreviated weekday name (Sun to Sat)
%b Abbreviated month name (Jan to Dec)
%c Numeric month name (0 to 12)
%D Day of the month as a numeric value, followed by suffix (1st, 2nd, 3rd, ...)
%d Day of the month as a numeric value (01 to 31)
%e Day of the month as a numeric value (0 to 31)
%f Microseconds (000000 to 999999)
%H Hour (00 to 23)
%h Hour (00 to 12)
%I Hour (00 to 12)
%i Minutes (00 to 59)
%j Day of the year (001 to 366)
%k Hour (0 to 23)
%l Hour (1 to 12)
%M Month name in full (January to December)
%m Month name as a numeric value (00 to 12)
%p AM or PM
%r Time in 12 hour AM or PM format (hh:mm:ss AM/PM)
%S Seconds (00 to 59)
%s Seconds (00 to 59)
%T Time in 24 hour format (hh:mm:ss)
%U Week where Sunday is the first day of the week (00 to 53)
%u Week where Monday is the first day of the week (00 to 53)
%V Week where Sunday is the first day of the week (01 to 53). Used with %X
%v Week where Monday is the first day of the week (01 to 53). Used with %X
%W Weekday name in full (Sunday to Saturday)
%w Day of the week where Sunday=0 and Saturday=6
%X Year for the week where Sunday is the first day of the week. Used with %V
%x Year for the week where Monday is the first day of the week. Used with %V
%Y Year as a numeric, 4-digit value
%y Year as a numeric, 2-digit value mysql> SELECT HOUR("2017-06-20 09:34:00");
+-----------------------------+
| HOUR("2017-06-20 09:34:00") |
+-----------------------------+
| 9 |
+-----------------------------+
1 row in set mysql> SELECT MINUTE("2017-06-20 09:34:00");
+-------------------------------+
| MINUTE("2017-06-20 09:34:00") |
+-------------------------------+
| 34 |
+-------------------------------+
1 row in set mysql> SELECT MONTH("2017-06-20 09:34:00");
+------------------------------+
| MONTH("2017-06-20 09:34:00") |
+------------------------------+
| 6 |
+------------------------------+
1 row in set mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2018-09-25 17:20:05 |
+---------------------+
1 row in set mysql> SELECT year("2017-06-20 09:34:00");
+-----------------------------+
| year("2017-06-20 09:34:00") |
+-----------------------------+
| 2017 |
+-----------------------------+
1 row in set mysql> SELECT WEEK("2017-06-20 09:34:00");
+-----------------------------+
| WEEK("2017-06-20 09:34:00") |
+-----------------------------+
| 25 |
+-----------------------------+
1 row in set -- 加密函数
mysql> select MD5(123);
+----------------------------------+
| MD5(123) |
+----------------------------------+
| 202cb962ac59075b964b07152d234b70 |
+----------------------------------+
1 row in set mysql> select password(123);
+-------------------------------------------+
| password(123) |
+-------------------------------------------+
| *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+-------------------------------------------+
1 row in set mysql> select sha(123);
+------------------------------------------+
| sha(123) |
+------------------------------------------+
| 40bd001563085fc35165329ea1ff5c5ecbdbbeef |
+------------------------------------------+
1 row in set -- 格式化函数 mysql> select format("1231231",3);
+---------------------+
| format("1231231",3) |
+---------------------+
| 1,231,231.000 |
+---------------------+
1 row in set mysql> select inet_aton("192.168.13.14");
+----------------------------+
| inet_aton("192.168.13.14") |
+----------------------------+
| 3232238862 |
+----------------------------+
1 row in set mysql> select inet_ntoa("3232238862");
+-------------------------+
| inet_ntoa("3232238862") |
+-------------------------+
| 192.168.13.14 |
+-------------------------+
1 row in set -- 系统信息函数
mysql> select database();
+------------+
| database() |
+------------+
| s79 |
+------------+
1 row in set mysql> select benchmark(3,"12");
+-------------------+
| benchmark(3,"12") |
+-------------------+
| 0 |
+-------------------+
1 row in set mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
| 33 |
+-----------------+
1 row in set mysql> select FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
| 1 |
+--------------+
1 row in set mysql> select USER();
+----------------+
| USER() |
+----------------+
| root@localhost |
+----------------+
1 row in set mysql> select SYSTEM_USER();
+----------------+
| SYSTEM_USER() |
+----------------+
| root@localhost |
+----------------+
1 row in set mysql> select VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.7.11 |
+-----------+
1 row in set mysql> SHOW CHARACTER SET ;
+----------+---------------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| tis620 | TIS620 Thai | tis620_thai_ci | 1 |
| euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| cp1250 | Windows Central European | cp1250_general_ci | 1 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| cp866 | DOS Russian | cp866_general_ci | 1 |
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
| macce | Mac Central European | macce_general_ci | 1 |
| macroman | Mac West European | macroman_general_ci | 1 |
| cp852 | DOS Central European | cp852_general_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |
| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| utf16 | UTF-16 Unicode | utf16_general_ci | 4 |
| utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 |
| cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| cp1257 | Windows Baltic | cp1257_general_ci | 1 |
| utf32 | UTF-32 Unicode | utf32_general_ci | 4 |
| binary | Binary pseudo charset | binary | 1 |
| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
| gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set mysql> SHOW COLLATION ;
+--------------------------+----------+-----+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+--------------------------+----------+-----+---------+----------+---------+
| big5_chinese_ci | big5 | 1 | Yes | Yes | 1 |
| big5_bin | big5 | 84 | | Yes | 1 |
| dec8_swedish_ci | dec8 | 3 | Yes | Yes | 1 |
| dec8_bin | dec8 | 69 | | Yes | 1 |
| cp850_general_ci | cp850 | 4 | Yes | Yes | 1 |
| cp850_bin | cp850 | 80 | | Yes | 1 |
| hp8_english_ci | hp8 | 6 | Yes | Yes | 1 |
| hp8_bin | hp8 | 72 | | Yes | 1 |
| koi8r_general_ci | koi8r | 7 | Yes | Yes | 1 |
| koi8r_bin | koi8r | 74 | | Yes | 1 |
| latin1_german1_ci | latin1 | 5 | | Yes | 1 |
| latin1_swedish_ci | latin1 | 8 | Yes | Yes | 1 |
| latin1_danish_ci | latin1 | 15 | | Yes | 1 |
| latin1_german2_ci | latin1 | 31 | | Yes | 2 |
| latin1_bin | latin1 | 47 | | Yes | 1 |
| latin1_general_ci | latin1 | 48 | | Yes | 1 |
| latin1_general_cs | latin1 | 49 | | Yes | 1 |
| latin1_spanish_ci | latin1 | 94 | | Yes | 1 |
| latin2_czech_cs | latin2 | 2 | | Yes | 4 |
| latin2_general_ci | latin2 | 9 | Yes | Yes | 1 |
| latin2_hungarian_ci | latin2 | 21 | | Yes | 1 |
| latin2_croatian_ci | latin2 | 27 | | Yes | 1 |
| latin2_bin | latin2 | 77 | | Yes | 1 |
| swe7_swedish_ci | swe7 | 10 | Yes | Yes | 1 |
| swe7_bin | swe7 | 82 | | Yes | 1 |
| ascii_general_ci | ascii | 11 | Yes | Yes | 1 |
| ascii_bin | ascii | 65 | | Yes | 1 |
| ujis_japanese_ci | ujis | 12 | Yes | Yes | 1 |
| ujis_bin | ujis | 91 | | Yes | 1 |
| sjis_japanese_ci | sjis | 13 | Yes | Yes | 1 |
| sjis_bin | sjis | 88 | | Yes | 1 |
| hebrew_general_ci | hebrew | 16 | Yes | Yes | 1 |
| hebrew_bin | hebrew | 71 | | Yes | 1 |
| tis620_thai_ci | tis620 | 18 | Yes | Yes | 4 |
| tis620_bin | tis620 | 89 | | Yes | 1 |
| euckr_korean_ci | euckr | 19 | Yes | Yes | 1 |
| euckr_bin | euckr | 85 | | Yes | 1 |
| koi8u_general_ci | koi8u | 22 | Yes | Yes | 1 |
| koi8u_bin | koi8u | 75 | | Yes | 1 |
| gb2312_chinese_ci | gb2312 | 24 | Yes | Yes | 1 |
| gb2312_bin | gb2312 | 86 | | Yes | 1 |
| greek_general_ci | greek | 25 | Yes | Yes | 1 |
| greek_bin | greek | 70 | | Yes | 1 |
| cp1250_general_ci | cp1250 | 26 | Yes | Yes | 1 |
| cp1250_czech_cs | cp1250 | 34 | | Yes | 2 |
| cp1250_croatian_ci | cp1250 | 44 | | Yes | 1 |
| cp1250_bin | cp1250 | 66 | | Yes | 1 |
| cp1250_polish_ci | cp1250 | 99 | | Yes | 1 |
| gbk_chinese_ci | gbk | 28 | Yes | Yes | 1 |
| gbk_bin | gbk | 87 | | Yes | 1 |
| latin5_turkish_ci | latin5 | 30 | Yes | Yes | 1 |
| latin5_bin | latin5 | 78 | | Yes | 1 |
| armscii8_general_ci | armscii8 | 32 | Yes | Yes | 1 |
| armscii8_bin | armscii8 | 64 | | Yes | 1 |
| utf8_general_ci | utf8 | 33 | Yes | Yes | 1 |
| utf8_bin | utf8 | 83 | | Yes | 1 |
| utf8_unicode_ci | utf8 | 192 | | Yes | 8 |
| utf8_icelandic_ci | utf8 | 193 | | Yes | 8 |
| utf8_latvian_ci | utf8 | 194 | | Yes | 8 |
| utf8_romanian_ci | utf8 | 195 | | Yes | 8 |
| utf8_slovenian_ci | utf8 | 196 | | Yes | 8 |
| utf8_polish_ci | utf8 | 197 | | Yes | 8 |
| utf8_estonian_ci | utf8 | 198 | | Yes | 8 |
| utf8_spanish_ci | utf8 | 199 | | Yes | 8 |
| utf8_swedish_ci | utf8 | 200 | | Yes | 8 |
| utf8_turkish_ci | utf8 | 201 | | Yes | 8 |
| utf8_czech_ci | utf8 | 202 | | Yes | 8 |
| utf8_danish_ci | utf8 | 203 | | Yes | 8 |
| utf8_lithuanian_ci | utf8 | 204 | | Yes | 8 |
| utf8_slovak_ci | utf8 | 205 | | Yes | 8 |
| utf8_spanish2_ci | utf8 | 206 | | Yes | 8 |
| utf8_roman_ci | utf8 | 207 | | Yes | 8 |
| utf8_persian_ci | utf8 | 208 | | Yes | 8 |
| utf8_esperanto_ci | utf8 | 209 | | Yes | 8 |
| utf8_hungarian_ci | utf8 | 210 | | Yes | 8 |
| utf8_sinhala_ci | utf8 | 211 | | Yes | 8 |
| utf8_german2_ci | utf8 | 212 | | Yes | 8 |
| utf8_croatian_ci | utf8 | 213 | | Yes | 8 |
| utf8_unicode_520_ci | utf8 | 214 | | Yes | 8 |
| utf8_vietnamese_ci | utf8 | 215 | | Yes | 8 |
| utf8_general_mysql500_ci | utf8 | 223 | | Yes | 1 |
| ucs2_general_ci | ucs2 | 35 | Yes | Yes | 1 |
| ucs2_bin | ucs2 | 90 | | Yes | 1 |
| ucs2_unicode_ci | ucs2 | 128 | | Yes | 8 |
| ucs2_icelandic_ci | ucs2 | 129 | | Yes | 8 |
| ucs2_latvian_ci | ucs2 | 130 | | Yes | 8 |
| ucs2_romanian_ci | ucs2 | 131 | | Yes | 8 |
| ucs2_slovenian_ci | ucs2 | 132 | | Yes | 8 |
| ucs2_polish_ci | ucs2 | 133 | | Yes | 8 |
| ucs2_estonian_ci | ucs2 | 134 | | Yes | 8 |
| ucs2_spanish_ci | ucs2 | 135 | | Yes | 8 |
| ucs2_swedish_ci | ucs2 | 136 | | Yes | 8 |
| ucs2_turkish_ci | ucs2 | 137 | | Yes | 8 |
| ucs2_czech_ci | ucs2 | 138 | | Yes | 8 |
| ucs2_danish_ci | ucs2 | 139 | | Yes | 8 |
| ucs2_lithuanian_ci | ucs2 | 140 | | Yes | 8 |
| ucs2_slovak_ci | ucs2 | 141 | | Yes | 8 |
| ucs2_spanish2_ci | ucs2 | 142 | | Yes | 8 |
| ucs2_roman_ci | ucs2 | 143 | | Yes | 8 |
| ucs2_persian_ci | ucs2 | 144 | | Yes | 8 |
| ucs2_esperanto_ci | ucs2 | 145 | | Yes | 8 |
| ucs2_hungarian_ci | ucs2 | 146 | | Yes | 8 |
| ucs2_sinhala_ci | ucs2 | 147 | | Yes | 8 |
| ucs2_german2_ci | ucs2 | 148 | | Yes | 8 |
| ucs2_croatian_ci | ucs2 | 149 | | Yes | 8 |
| ucs2_unicode_520_ci | ucs2 | 150 | | Yes | 8 |
| ucs2_vietnamese_ci | ucs2 | 151 | | Yes | 8 |
| ucs2_general_mysql500_ci | ucs2 | 159 | | Yes | 1 |
| cp866_general_ci | cp866 | 36 | Yes | Yes | 1 |
| cp866_bin | cp866 | 68 | | Yes | 1 |
| keybcs2_general_ci | keybcs2 | 37 | Yes | Yes | 1 |
| keybcs2_bin | keybcs2 | 73 | | Yes | 1 |
| macce_general_ci | macce | 38 | Yes | Yes | 1 |
| macce_bin | macce | 43 | | Yes | 1 |
| macroman_general_ci | macroman | 39 | Yes | Yes | 1 |
| macroman_bin | macroman | 53 | | Yes | 1 |
| cp852_general_ci | cp852 | 40 | Yes | Yes | 1 |
| cp852_bin | cp852 | 81 | | Yes | 1 |
| latin7_estonian_cs | latin7 | 20 | | Yes | 1 |
| latin7_general_ci | latin7 | 41 | Yes | Yes | 1 |
| latin7_general_cs | latin7 | 42 | | Yes | 1 |
| latin7_bin | latin7 | 79 | | Yes | 1 |
| utf8mb4_general_ci | utf8mb4 | 45 | Yes | Yes | 1 |
| utf8mb4_bin | utf8mb4 | 46 | | Yes | 1 |
| utf8mb4_unicode_ci | utf8mb4 | 224 | | Yes | 8 |
| utf8mb4_icelandic_ci | utf8mb4 | 225 | | Yes | 8 |
| utf8mb4_latvian_ci | utf8mb4 | 226 | | Yes | 8 |
| utf8mb4_romanian_ci | utf8mb4 | 227 | | Yes | 8 |
| utf8mb4_slovenian_ci | utf8mb4 | 228 | | Yes | 8 |
| utf8mb4_polish_ci | utf8mb4 | 229 | | Yes | 8 |
| utf8mb4_estonian_ci | utf8mb4 | 230 | | Yes | 8 |
| utf8mb4_spanish_ci | utf8mb4 | 231 | | Yes | 8 |
| utf8mb4_swedish_ci | utf8mb4 | 232 | | Yes | 8 |
| utf8mb4_turkish_ci | utf8mb4 | 233 | | Yes | 8 |
| utf8mb4_czech_ci | utf8mb4 | 234 | | Yes | 8 |
| utf8mb4_danish_ci | utf8mb4 | 235 | | Yes | 8 |
| utf8mb4_lithuanian_ci | utf8mb4 | 236 | | Yes | 8 |
| utf8mb4_slovak_ci | utf8mb4 | 237 | | Yes | 8 |
| utf8mb4_spanish2_ci | utf8mb4 | 238 | | Yes | 8 |
| utf8mb4_roman_ci | utf8mb4 | 239 | | Yes | 8 |
| utf8mb4_persian_ci | utf8mb4 | 240 | | Yes | 8 |
| utf8mb4_esperanto_ci | utf8mb4 | 241 | | Yes | 8 |
| utf8mb4_hungarian_ci | utf8mb4 | 242 | | Yes | 8 |
| utf8mb4_sinhala_ci | utf8mb4 | 243 | | Yes | 8 |
| utf8mb4_german2_ci | utf8mb4 | 244 | | Yes | 8 |
| utf8mb4_croatian_ci | utf8mb4 | 245 | | Yes | 8 |
| utf8mb4_unicode_520_ci | utf8mb4 | 246 | | Yes | 8 |
| utf8mb4_vietnamese_ci | utf8mb4 | 247 | | Yes | 8 |
| cp1251_bulgarian_ci | cp1251 | 14 | | Yes | 1 |
| cp1251_ukrainian_ci | cp1251 | 23 | | Yes | 1 |
| cp1251_bin | cp1251 | 50 | | Yes | 1 |
| cp1251_general_ci | cp1251 | 51 | Yes | Yes | 1 |
| cp1251_general_cs | cp1251 | 52 | | Yes | 1 |
| utf16_general_ci | utf16 | 54 | Yes | Yes | 1 |
| utf16_bin | utf16 | 55 | | Yes | 1 |
| utf16_unicode_ci | utf16 | 101 | | Yes | 8 |
| utf16_icelandic_ci | utf16 | 102 | | Yes | 8 |
| utf16_latvian_ci | utf16 | 103 | | Yes | 8 |
| utf16_romanian_ci | utf16 | 104 | | Yes | 8 |
| utf16_slovenian_ci | utf16 | 105 | | Yes | 8 |
| utf16_polish_ci | utf16 | 106 | | Yes | 8 |
| utf16_estonian_ci | utf16 | 107 | | Yes | 8 |
| utf16_spanish_ci | utf16 | 108 | | Yes | 8 |
| utf16_swedish_ci | utf16 | 109 | | Yes | 8 |
| utf16_turkish_ci | utf16 | 110 | | Yes | 8 |
| utf16_czech_ci | utf16 | 111 | | Yes | 8 |
| utf16_danish_ci | utf16 | 112 | | Yes | 8 |
| utf16_lithuanian_ci | utf16 | 113 | | Yes | 8 |
| utf16_slovak_ci | utf16 | 114 | | Yes | 8 |
| utf16_spanish2_ci | utf16 | 115 | | Yes | 8 |
| utf16_roman_ci | utf16 | 116 | | Yes | 8 |
| utf16_persian_ci | utf16 | 117 | | Yes | 8 |
| utf16_esperanto_ci | utf16 | 118 | | Yes | 8 |
| utf16_hungarian_ci | utf16 | 119 | | Yes | 8 |
| utf16_sinhala_ci | utf16 | 120 | | Yes | 8 |
| utf16_german2_ci | utf16 | 121 | | Yes | 8 |
| utf16_croatian_ci | utf16 | 122 | | Yes | 8 |
| utf16_unicode_520_ci | utf16 | 123 | | Yes | 8 |
| utf16_vietnamese_ci | utf16 | 124 | | Yes | 8 |
| utf16le_general_ci | utf16le | 56 | Yes | Yes | 1 |
| utf16le_bin | utf16le | 62 | | Yes | 1 |
| cp1256_general_ci | cp1256 | 57 | Yes | Yes | 1 |
| cp1256_bin | cp1256 | 67 | | Yes | 1 |
| cp1257_lithuanian_ci | cp1257 | 29 | | Yes | 1 |
| cp1257_bin | cp1257 | 58 | | Yes | 1 |
| cp1257_general_ci | cp1257 | 59 | Yes | Yes | 1 |
| utf32_general_ci | utf32 | 60 | Yes | Yes | 1 |
| utf32_bin | utf32 | 61 | | Yes | 1 |
| utf32_unicode_ci | utf32 | 160 | | Yes | 8 |
| utf32_icelandic_ci | utf32 | 161 | | Yes | 8 |
| utf32_latvian_ci | utf32 | 162 | | Yes | 8 |
| utf32_romanian_ci | utf32 | 163 | | Yes | 8 |
| utf32_slovenian_ci | utf32 | 164 | | Yes | 8 |
| utf32_polish_ci | utf32 | 165 | | Yes | 8 |
| utf32_estonian_ci | utf32 | 166 | | Yes | 8 |
| utf32_spanish_ci | utf32 | 167 | | Yes | 8 |
| utf32_swedish_ci | utf32 | 168 | | Yes | 8 |
| utf32_turkish_ci | utf32 | 169 | | Yes | 8 |
| utf32_czech_ci | utf32 | 170 | | Yes | 8 |
| utf32_danish_ci | utf32 | 171 | | Yes | 8 |
| utf32_lithuanian_ci | utf32 | 172 | | Yes | 8 |
| utf32_slovak_ci | utf32 | 173 | | Yes | 8 |
| utf32_spanish2_ci | utf32 | 174 | | Yes | 8 |
| utf32_roman_ci | utf32 | 175 | | Yes | 8 |
| utf32_persian_ci | utf32 | 176 | | Yes | 8 |
| utf32_esperanto_ci | utf32 | 177 | | Yes | 8 |
| utf32_hungarian_ci | utf32 | 178 | | Yes | 8 |
| utf32_sinhala_ci | utf32 | 179 | | Yes | 8 |
| utf32_german2_ci | utf32 | 180 | | Yes | 8 |
| utf32_croatian_ci | utf32 | 181 | | Yes | 8 |
| utf32_unicode_520_ci | utf32 | 182 | | Yes | 8 |
| utf32_vietnamese_ci | utf32 | 183 | | Yes | 8 |
| binary | binary | 63 | Yes | Yes | 1 |
| geostd8_general_ci | geostd8 | 92 | Yes | Yes | 1 |
| geostd8_bin | geostd8 | 93 | | Yes | 1 |
| cp932_japanese_ci | cp932 | 95 | Yes | Yes | 1 |
| cp932_bin | cp932 | 96 | | Yes | 1 |
| eucjpms_japanese_ci | eucjpms | 97 | Yes | Yes | 1 |
| eucjpms_bin | eucjpms | 98 | | Yes | 1 |
| gb18030_chinese_ci | gb18030 | 248 | Yes | Yes | 2 |
| gb18030_bin | gb18030 | 249 | | Yes | 1 |
| gb18030_unicode_520_ci | gb18030 | 250 | | Yes | 8 |
+--------------------------+----------+-----+---------+----------+---------+
222 rows in set mysql> SHOW COLUMNS FROM user ;
+----------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(255) | NO | UNI | NULL | |
| sex | tinyint(3) unsigned | NO | | 0 | |
| age | tinyint(3) unsigned | NO | | 0 | |
| province | varchar(255) | NO | | | |
+----------+---------------------+------+-----+---------+----------------+
5 rows in set mysql> SHOW CREATE DATABASE s79;
+----------+----------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------+
| s79 | CREATE DATABASE `s79` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+----------------------------------------------------------------+
1 row in set mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| project |
| s79 |
| sys |
+--------------------+
6 rows in set mysql> SHOW ENGINES ;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | DEFAULT | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set mysql> SHOW INDEX from user
;
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user | 0 | PRIMARY | 1 | id | A | 30 | NULL | NULL | | BTREE | | |
| user | 0 | name | 1 | name | A | 30 | NULL | NULL | | BTREE | | |
| user | 0 | idxlq_name | 1 | name | A | 29 | NULL | NULL | | BTREE | | |
| user | 1 | idx_name | 1 | name | A | 29 | NULL | NULL | | BTREE | | |
| user | 1 | idxl_name | 1 | name | A | 29 | NULL | NULL | | BTREE | | |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set mysql> SHOW TABLES ;
+---------------+
| Tables_in_s79 |
+---------------+
| hc_lover |
| hc_user |
| lamp_address |
| lover |
| money |
| score |
| t1 |
| t2 |
| t3 |
| user |
| user2 |
| user_1_copy |
+---------------+
12 rows in set mysql> SHOW VARIABLES;
-- 太长...503行 -- 预处理
mysql> prepare s1 from 'select * from t1 where id>?';
Query OK, 0 rows affected
Statement prepared mysql> set @i=1;
Query OK, 0 rows affected mysql> execute s1 using @i;
+----+------+
| id | name |
+----+------+
| 2 | rose |
| 3 | mary |
+----+------+
2 rows in set mysql> drop prepare s1;
Query OK, 0 rows affected -- 事务
mysql> set autocommit=0;
Query OK, 0 rows affected mysql> begin;
Query OK, 0 rows affected mysql> delete from t1 where id = 2;
Query OK, 1 row affected mysql> savepoint p1;
Query OK, 0 rows affected mysql> delete from t1 where id = 3;
Query OK, 1 row affected mysql> savepoint p2;
Query OK, 0 rows affected mysql> delete from t1 where id = 4;
Query OK, 0 rows affected mysql> rollback to p1;
Query OK, 0 rows affected mysql> rollback to p2;
1305 - SAVEPOINT p2 does not exist
mysql> rollback;
Query OK, 0 rows affected mysql> commit;
Query OK, 0 rows affected -- 存储
mysql> delimiter //
mysql> create procedure p1()
-> begin
-> set @i=1;
-> while @i<6 do
-> select * from t1 where id=@i;
-> set @i=@i+1;
-> end while;
-> end//
1304 - PROCEDURE p1 already exists
mysql> delimiter ;
mysql> call p1;
+----+------+
| id | name |
+----+------+
| 1 | jack |
+----+------+
1 row in set Empty set Empty set Empty set Empty set Query OK, 0 rows affected mysql> drop procedure p1;
Query OK, 0 rows affected -- 触发器
mysql> delimiter //
mysql> create trigger tg1 before insert on t2 for each row
-> begin
-> insert into t3(name) values ('hello');
-> end//
Query OK, 0 rows affected
mysql> delimiter ;
mysql> insert into t2(name) values ('tom');
Query OK, 1 row affected
mysql> select * from t2;
+----+------+
| id | name |
+----+------+
| 1 | tom |
+----+------+
1 row in set
mysql> select * from t3
;
+----+-------+
| id | name |
+----+-------+
| 0 | hello |
+----+-------+
1 row in set
-- 视图
mysql> create view v_t1 as select * from t1 where id>1 and id<5;
Query OK, 0 rows affected mysql> show tables;
+---------------+
| Tables_in_s79 |
+---------------+
| a |
| hc_lover |
| hc_user |
| lamp_address |
| lover |
| money |
| score |
| t1 |
| t2 |
| t3 |
| user |
| user2 |
| user_1_copy |
| v_t1 |
+---------------+
14 rows in set mysql> select * from v_t1;
Empty set mysql> drop view v_t1;
Query OK, 0 rows affected -- 临时/虚拟/重置自增
mysql> create temporary table tmp1 (id int) ;
Query OK, 0 rows affected mysql> select now() from dual;
+---------------------+
| now() |
+---------------------+
| 2018-09-25 18:13:21 |
+---------------------+
1 row in set mysql> truncate table t1;
Query OK, 0 rows affected -- 数据导入导出
[root@centos_6_8 ~]# /usr/local/mysql/bin/mysqldump -uroot -p000000 -l -F test>'/tmp/test.sql'
[root@centos_6_8 ~]# ll /tmp/
总用量 44
srwxrwxrwx 1 mysql mysql 0 9月 26 00:50 mysql.sock
-rw-------. 1 daemon daemon 38182 8月 21 13:15 sess_vfqb9s6aoe4o5e15t2mq5nb6ogkpt6lv
-rw-r--r-- 1 root root 1247 9月 26 02:14 test.sql
-rw-------. 1 root root 0 8月 21 03:56 yum.log [root@centos_6_8 ~]# /usr/local/mysql/bin//mysql -uroot -p000000 test</tmp/test.sql -- 单表数据备份
[root@centos_6_8 ~]# /usr/local/mysql/bin/mysql -uroot -p000000
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.5.48-log Source distribution Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. 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> use mysql
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
24 rows in set (0.00 sec) mysql> select * from user into outfile '/tmp/t1.txt';
Query OK, 6 rows affected (0.00 sec) mysql> truncate user;
Query OK, 0 rows affected (0.00 sec) mysql> load data infile '/tmp/t1.txt' into table user;
Query OK, 6 rows affected (0.04 sec)
Records: 6 Deleted: 0 Skipped: 0 Warnings: 0 -- 索引
mysql> alter table t2 add index index_name(name);
Query OK, 4 rows affected
Records: 4 Duplicates: 0 Warnings: 0 mysql> alter table t2 add unique uniqe_name(name);
Query OK, 4 rows affected
Records: 4 Duplicates: 0 Warnings: 0 mysql> alter table t2 drop primary key;
Query OK, 4 rows affected
Records: 4 Duplicates: 0 Warnings: 0 mysql> alter table t2 add primary key(id);
Query OK, 4 rows affected
Records: 4 Duplicates: 0 Warnings: 0 mysql> show index from t2;
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t2 | 0 | PRIMARY | 1 | id | A | 4 | NULL | NULL | | BTREE | | |
| t2 | 0 | uniqe_name | 1 | name | A | NULL | NULL | NULL | YES | BTREE | | |
| t2 | 1 | index_name | 1 | name | A | NULL | NULL | NULL | YES | BTREE | | |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set