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(50) ); insert into t1 values(1,'jack'),(2,'rose'),(3,'mary'); desc t1; #copy create table t2 like t1; insert into t2 select * from t1; create table t3 select * from t1; #math select abs(-1); select bin(2); select CEILING(1); select FLOOR(2); select GREATEST(1,2,5,3); select LEAST(1,2,3,5,6); select LN(10); select log(12); select mod(10,5); select pi(); select rand(); select round(1,10); select sign(2); select sqrt(4); select truncate(123.23,1); #string select ascii(12); select bit_length(123); select concat(1,2,3,4); select concat_ws(0,1,2,3,4); SELECT CONCAT_WS("-", "SQL", "Tutorial", "is", "fun!"); select lcase("AVNA"); select lower("AVNA"); select ucase("avna"); select upper("avna"); select left("hello world",4); select length("hello world"); select ltrim(" hello world"); SELECT POSITION("3" IN "W3Schools.com"); SELECT REPEAT("SQL Tutorial", 3); select REVERSE("str"); select RIGHT("str",2); 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(123); select password(123); select sha(123); #format select format("1231231",3); select inet_aton("192.168.13.14"); select inet_ntoa("3232238862"); #system select database(); select benchmark(3,"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=1; execute s1 using @i; drop prepare s1; #Transaction set autocommit=0; begin; delete from t1 where id = 2; savepoint p1; delete from t1 where id = 3; savepoint p2; delete from t1 where id = 4; rollback to p2; rollback to p1; rollback; commit; #view create view v_t1 as select * from t1 where id>1 and id<5; 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=1; while @i<6 do select * from t1 where id=@i; set @i=@i+1; 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