MySQL 语句汇总

码龄4年审核中

已于 2022-01-30 16:54:57 修改

阅读量423

点赞数

分类专栏：笔记心得文章标签： mysql 数据库 database

于 2022-01-20 18:25:43 首次发布

本文链接：https://blog.csdn.net/m0_47505062/article/details/122594579

版权

心得同时被 2 个专栏收录

79 篇文章 8 订阅

订阅专栏

笔记

71 篇文章 8 订阅

订阅专栏

mysql -u 用户名 -p 密码 -h 主机名
主机名（形如：192.168.231.129）
show databases;
use 数据库名
show tables;
show function status;
show procedure status;
show table status;
show table status where comment=‘view’;
select * from information_schema.views;
show create table 表名\视图名;
show create function 函数名;
show create procedure 过程名;

rand()*10;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 0.1~9.9
ceil(rand()*10);
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 1~10
floor(rand()*10;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 0~9

convert(数字,char(数字转化成字符的个数));
lpad(‘字符串’,字符串要达到的长度值,‘不够长度时使用这里的内容进行填充’);
rpad(char,n,char);
concat(char,char,char,char…);
if(条件判断,成立时取这里值,不成立时取这里的值);
describe 表名;
describe 视图名; 但字段不显示

mysql> select now();

		+---------------------+
		| now()               |
		+---------------------+
		| 2022-01-20 16:48:11 |
		+---------------------+

mysql> select replace(now(),’ ‘,’’);

		+-----------------------+
		| replace(now(),' ','') |
		+-----------------------+
		| 2022-01-2016:49:38    |
		+-----------------------+

mysql> select replace(replace(now(),’ ‘,’’),’:’,’’);

		+---------------------------------------+
		| replace(replace(now(),' ',''),':','') |
		+---------------------------------------+
		| 2022-01-20165154                      |
		+---------------------------------------+

mysql> select replace(replace(replace(now(),’ ‘,’’),’:’,’’),’-’,’’);

		+-------------------------------------------------------+
		| replace(replace(replace(now(),' ',''),':',''),'-','') |
		+-------------------------------------------------------+
		| 20220120165350                                        |
		+-------------------------------------------------------+

select table_name from information_schema.tables where table_name like ‘ST2%’ into @tn;
set @bl=concat('select * from ‘,@tn, ’ limit 1118,5’);
prepare ex from @bl;
execute ex;
上述语句解决，查询语句的表名是变量遇到的问题。

alter table students add primary key(studentid);

mysql> explain select * From students where studentid=9;

mysql> explain select * From students where studentid=9\G

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: students
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
Extra:
1 row in set (0.00 sec)

alter table students engine=innoDB;

查看索引使用下面两个语句共同查看索引的详细信息，
show index from TStudent;
describe TStudent;

drop index sname_index on TStudent;
alter table TStudent drop primary key;
alter table TStudent drop index cardid_index;

当执行了插入大量数据（比如百万级别的记录）的语句后，想查看当前已经产生的记录数，使用count，不要使用 select * 会死的，
select count(*)from TStudent;

按分组查询，使用语句
select class,count() from TStudent where class=‘java’ group by class;
而不能使用下面错误的分组查询语句，
select class,count() from TStudent group by class where class=‘java’;
这是因为，where 条件要写在 group by 之前，

另一种查看表相关信息的语句，表信息包含，数据量、行数、创建时间等信息，

select * from information_schema.TABLES where table_name like 'TStudent' and
 table_schema='schoolDB';

另一种创建表的命令，

create table t1 as select * from TStudent limit 20000;

mysql> show open tables where in_use>0;
Empty set (0.00 sec)

mysql> lock tables tt read;
Query OK, 0 rows affected (0.00 sec)

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

mysql> show open tables where in_use>0;
Empty set (0.00 sec)

mysql> select connection_id();
±----------------+
| connection_id() |
±----------------+
| 2 |
±----------------+
1 row in set (0.00 sec)

select user();
select database();

mysql> select @@version;
±----------+
| @@version |
±----------+
| 5.1.66 |
±----------+
1 row in set (0.00 sec)

mysql> select version();
±----------+
| version() |
±----------+
| 5.1.66 |
±----------+
1 row in set (0.00 sec)

mysql> exit
Bye
[root@localhost ~]# service mysqld restart;
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]

[root@localhost ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.66 Source distribution
Copyright © 2000, 2012, 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;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select host,user from user;
±----------------------±------+
| host | user |
±----------------------±------+
| 127.0.0.1 | root |
| 192.168.231.% | root |
| 192.168.231.% | wang |
| 192.168.231.% | zhang |
| localhost | root |
| localhost.localdomain | root |
±----------------------±------+
6 rows in set (0.00 sec)

mysql> set password for li@'192.168.231.%'=password('root');
Query OK, 0 rows affected (0.00 sec)

mysql> grant select on schoolDB.* to chen@'192.168.231.%' identified by 'root';
Query OK, 0 rows affected (0.00 sec)
mysql> select host,user from user;
±----------------------±------+
| host | user |
±----------------------±------+
| % | zhao |
| 127.0.0.1 | root |
| 192.168.231.% | chen |
| 192.168.231.% | li |
| 192.168.231.% | root |
| 192.168.231.% | wang |
| 192.168.231.% | zhang |
| 192.168.231.% | zhou |
| localhost | root |
| localhost.localdomain | root |
±----------------------±------+

mysql> set password for zhao@'%'=password('root');
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
mysql> update user set password=password('root') where user='zhao';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

？ contents
？ create user
？ Show

[root@localhost ~]# ll /var/lib/mysql/
total 28684
-rw-rw----. 1 mysql mysql 18874368 Jan 30 16:00 ibdata1
-rw-rw----. 1 mysql mysql 5242880 Jan 30 16:00 ib_logfile0
-rw-rw----. 1 mysql mysql 5242880 Jan 29 14:16 ib_logfile1
drwx------. 2 mysql mysql 4096 Jan 30 08:40 mysql
srwxrwxrwx. 1 mysql mysql 0 Jan 30 15:44 mysql.sock
drwx------. 2 mysql mysql 4096 Jan 30 15:56 schoolDB
drwx------. 2 mysql mysql 4096 Jan 30 08:21 test

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select user,host,password from user;
±-----±----------------------±------------------------------------------+
| user | host | password |
±-----±----------------------±------------------------------------------+
| root | localhost | |
| root | localhost.localdomain | |
| root | 127.0.0.1 | |
| | localhost | |
| | localhost.localdomain | |
| root | 192.168.231.% | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
±-----±----------------------±------------------------------------------+
6 rows in set (0.00 sec)
mysql> update user set password=password('root') where user='root';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 4 Changed: 3 Warnings: 0
mysql> select user,host,password from user;
±-----±----------------------±------------------------------------------+
| user | host | password |
±-----±----------------------±------------------------------------------+
| root | localhost | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| root | localhost.localdomain | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| root | 127.0.0.1 | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| | localhost | |
| | localhost.localdomain | |
| root | 192.168.231.% | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
±-----±----------------------±------------------------------------------+
6 rows in set (0.00 sec)
mysql> exit
Bye
[root@localhost ~]# service mysqld restart;
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
[root@localhost ~]# mkdir /mysqlbackup
[root@localhost mysqlbackup]# mysqldump -uroot -p'root' schoolDB TSubject > /mysqlbackup/schoolDB.TSubject.sql
[root@localhost mysqlbackup]# dir
schoolDB.TSubject.sql
[root@localhost mysqlbackup]# more /mysqlbackup/schoolDB.TSubject.sql
MySQL dump 10.13 Distrib 5.1.66, for redhat-linux-gnu (i686)
– Host: localhost Database: schoolDB

– Server version 5.1.66

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT /;
/!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS /;
/!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION /;
/!40101 SET NAMES utf8 /;
/!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE /;
/!40103 SET TIME_ZONE=’+00:00’ /;
/!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 /;
/!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHEC
KS=0 /;
/!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=‘NO_AUTO_VALUE_ON_ZERO’ /;
/!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
Table structure for table TSubject
DROP TABLE IF EXISTS TSubject;

[root@localhost mysqlbackup]# mysql -uroot -proot schoolDB < /mysqlbackup/schoolDB.TSubject.sql
[root@localhost mysqlbackup]# mysqldump -uroot -proot schoolDB TSubject TStudent > /mysqlbackup/schoolDB.TSubject_TStudent.sql
[root@localhost mysqlbackup]# dir
schoolDB.TSubject.sql schoolDB.TSubject_TStudent.sql

January the 30th 2022 Sunday

码龄4年审核中

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL 语句汇总

mysql -u 用户名 -p 密码 -h 主机名主机名（形如：192.168.231.129）show databases;use 数据库名show tables;show function status;show procedure status;show table status;show table status where comment=‘view’;select * from information_schema.views;show create table 表名\视图名
复制链接

扫一扫

专栏目录