mysql8导入导出数据库 1.连接数据库mysql -u root -p远程数据库mysql -u root -p -h 192.168.1.100 -P 33072.导出mysqldump -u 用户名 -p 数据库名>/mnt/usb/testdb.sqlmysqldump -u root -p testdb>/mnt/usb/testdb.sql远程数据库mysqldump -h 主机 -P 端口 -u 用户名 -p 数据库名>/mnt/usb/testdb.sqlmysqldump -h 192.168.1.100 -P 3307 -u root -p testdb>/mnt/usb/testdb.sql3.导入mysql -u 用户名 -p 数据库名</mnt/usb/testdb.sqlmysql -u root -p testdb</mnt/usb/testdb.sql4.定制导出脚本USER_NAME=$1 if [ ! -n "$USER_NAME" ]; then echo "User name can't be empty!" exit 0 fi PWD=$2 if [ ! -n "$PWD" ]; then echo "Password name can't be empty!" exit 0 fi echo "Password is $PWD" DB_NAME=$3 if [ ! -n "$DB_NAME" ]; then echo "Database name can't be empty!" exit 0 fi PORT=$4 if [ ! -n "$PORT" ]; then PORT=3306 fi BACK_PATH=$5 if [ ! -n "$BACK_PATH" ]; then BACK_PATH='./db-back' fi HOST=$6 if [ ! -n "$HOST" ]; then HOST='127.0.0.1' fi BACK_FILE_NAME=_"$PORT"_$(date '+%Y%m%d%H%M%S') if [ -a $BACK_PATH ]; then echo "Back path is '$BACK_PATH'." else mkdir $BACK_PATH fi cd $BACK_PATH mysqldump -h $HOST -P $PORT -u$USER_NAME -p$2 $DB_NAME>$DB_NAME$BACK_FILE_NAME.sqldemosh backup.sh root 111111 dbname 3306 /mnt/usb/db-backup

在特定数据库中查找字段 在整个MySQL数据库中查找select * from INFORMATION_SCHEMA.columns where COLUMN_NAME Like '%placement%';在特定数据库中查找SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME IN ('ColumnA','ColumnB') AND TABLE_SCHEMA='DatabaseName'; SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%ColumnA%' AND TABLE_SCHEMA='DatabaseName';在特定数据库中查找包含A和B字段的表SELECT DISTINCT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME IN ('A') and TABLE_NAME in (SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME IN ('B') AND TABLE_SCHEMA='DatabaseName')

mybatis常用SQL 1.SET<update id="updateStatus"> UPDATE t_user <set> <if test="status != null and status != ''"> status =#{status}, </if> <if test="runStatus != null and runStatus != ''"> run_status =#{runStatus}, </if> </set> WHERE id=#{id} </update>2.DELETE/删除指定条数<delete id="deleteByRobotTeamId"> DELETE from t_user WHERE id = #{id} <if test="num != null"> LIMIT #{num} </if> </delete>3.choose-when结构/IFNULL/MAX/<update id="save" parameterType="Object"> INSERT INTO t_robot_chat_expand (chat_word_id,sort, content ) VALUES( #{chatWordId}, <choose> <when test="sort == null"> (SELECT IFNULL(MAX( RCE.sort ) + 1,0) FROM t_robot_chat_expand RCE WHERE RCE.chat_word_id = #{chatWordId} ), </when> <otherwise> IF((SELECT MAX( RCE.sort ) FROM t_robot_chat_expand RCE WHERE RCE.chat_word_id = 1 ) &lt; #{sort}, (SELECT MAX( RCE.sort ) + 1 FROM t_robot_chat_expand RCE WHERE RCE.chat_word_id = #{chatWordId} ), #{sort}), </otherwise> </choose> #{content}) </update>

mysql更新移除逗号分割字符串中部分字符 UPDATE t_robot_team SET chat_words = TRIM(BOTH ',' FROM REPLACE( CONCAT( ',', chat_words, ',' ), CONCAT( ',', 21, ',' ), ',' )) WHERE locate(CONCAT(',',21,','),CONCAT(',',chat_words,','));

mysql常用命令 1.从某张表随机取一条记录简单，低效SELECT * FROM table_name ORDER BY RAND() LIMIT 1；速度快，效率高SELECT t1.id, t1.word, t1.STATUS FROM hy_idiom AS t1 JOIN ( SELECT ROUND( RAND() * (( SELECT MAX( id ) FROM hy_idiom WHERE STATUS = 1 )-( SELECT MIN( id ) FROM hy_idiom WHERE STATUS = 1 ))+ ( SELECT MIN( id ) FROM hy_idiom WHERE STATUS = 1 ) ) AS id ) AS t2 WHERE t1.id >= t2.id AND t1.STATUS = 1 ORDER BY t1.id LIMIT 5;SELECT T1.* FROM ( SELECT (@RN := @RN + 1) AS sort, U.* FROM t_user U, (SELECT @RN := -1) AS T11 ) T1 JOIN( SELECT FLOOR( rand()*( SELECT COUNT(1) FROM t_user ) ) AS sort ) T2 ON T1.sort = T2.sort2.从表总记录数取随机序号SELECT FLOOR( rand()*( SELECT COUNT(*) FROM t_user ) );3.显示某张表序号/行号SELECT (@RN := @RN + 1) AS sort, id FROM t_user, (SELECT @RN := -1) AS T0