Sql Snippets
Page content
show tables
SHOW TABLES
command provides you with an option that allows you to filter the returned tables using the LIKE
operator or an expression in the WHERE
clause as follows:
SHOW TABLES LIKE pattern;
SHOW TABLES WHERE expression;
lock a row
START TRANSACTION
SELECT * FROM x_tab WHERE id = 2 FOR UPDATE;
UPDATE x_tab SET amount = amount + 5 WHERE id = 2;
COMMIT
rename table
RENAME TABLE old_db.table TO new_db.table;
generate rename SQL
mysql -u username -ppassword old_db -sNe 'show tables' | while read table; \
do mysql -u username -ppassword -sNe "rename table old_db.$table to new_db.$table"; done
shows all tables in the testdb that start with the string pr
> SHOW TABLES LIKE 'p%';
+------------------------------+
| Tables_in_testdb (p%) |
+------------------------------+
| productlines |
| products |
+------------------------------+
2 rows in set (0.00 sec)
show the tables that end with the string ‘es’
> SHOW TABLES LIKE '%es';
+-------------------------------+
| Tables_in_testdb (%es) |
+-------------------------------+
| employees |
| offices |
| productlines |
+-------------------------------+
3 rows in set (0.00 sec)
query table_schema
select table_schema, count(1) from information_schema.tables where table_schema like "keyword%" group by table_schema;
create view
CREATE VIEW your_view_tab AS SELECT * FROM your_tab;
MAC mysql install info
==> Caveats
We've installed your MySQL database without a root password. To secure it run:
mysql_secure_installation
MySQL is configured to only allow connections from localhost by default
To connect run:
mysql -uroot
To have launchd start mysql now and restart at login:
brew services start mysql
Or, if you don't want/need a background service you can just run:
mysql.server start
==> Summary
🍺 /usr/local/Cellar/mysql/8.0.19: 286 files, 288.7MB
######################################################################## 100.0%
==> Pouring mysql@5.7-5.7.29.mojave.bottle.tar.gz
==> Caveats
We've installed your MySQL database without a root password. To secure it run:
mysql_secure_installation
MySQL is configured to only allow connections from localhost by default
To connect run:
mysql -uroot
mysql@5.7 is keg-only, which means it was not symlinked into /usr/local,
because this is an alternate version of another formula.
If you need to have mysql@5.7 first in your PATH run:
echo 'export PATH="/usr/local/opt/mysql@5.7/bin:$PATH"' >> ~/.zshrc
For compilers to find mysql@5.7 you may need to set:
export LDFLAGS="-L/usr/local/opt/mysql@5.7/lib"
export CPPFLAGS="-I/usr/local/opt/mysql@5.7/include"
本文由 络壳 原创或整理,转载请注明出处