[mysql] 常用命令一

一时失言乱红尘 2022-03-28 07:00 103阅读 0赞

① 连接数据库：mysql -r用户名 -p密码

返回信息：

Welcome to the MySQL monitor. Commands end with ; or \\g.  
Your MySQL connection id is 2  
Server version: 5.5.27 MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its  
affiliates. Other names may be trademarks of their respective  
owners.

② 查看所有的数据库模式:show batabases;

③ 创建数据库模式：create database 模式名;  
 删除数据库模式：drop database 模式名;

mysql> create database jdbctest;  
Query OK, 1 row affected (0.01 sec)

mysql> drop database jdbctest;  
Query OK, 0 rows affected (0.01 sec)

④ 使用模式：use 模式名;

mysql> use jdbctest;  
Database changed

⑤ 查看模式下所有的表

mysql>show tables;

Empty set (0.00 sec)

⑥ 新建表

drop table if exists user;  
create table user(  
id int auto\_increment primary key,  
name varchar(30) not null,  
password varchar(32) not null,  
age int not null,  
sex varchar(2) default '男',  
birthday date  
);

mysql> drop table if exists user;  
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table user(  
\->  
\-> id int auto\_increment primary key,  
\-> name varchar(30) not null,  
\-> password varchar(32) not null,  
\-> age int not null,  
\-> sex varchar(2) default '男',  
\-> birthday date  
\-> );  
Query OK, 0 rows affected (0.14 sec)

⑦ 删除表：drop table user;

mysql> drop table user;  
Query OK, 0 rows affected (0.07 sec)

⑧ 查看表的结构：desc 表名

mysql> desc user;  
\+----------+-------------+------+-----+---------+----------------+  
| Field | Type | Null | Key | Default | Extra |  
\+----------+-------------+------+-----+---------+----------------+  
| id | int(11) | NO | PRI | NULL | auto\_increment |  
| name | varchar(30) | NO | | NULL | |  
| password | varchar(32) | NO | | NULL | |  
| age | int(11) | NO | | NULL | |  
| sex | varchar(2) | YES | | 男 | |  
| birthday | date | YES | | NULL | |  
\+----------+-------------+------+-----+---------+----------------+  
6 rows in set (0.01 sec)

⑨ 查看一个模式下所有的表

mysql> show tables;  
\+--------------------+  
| Tables\_in\_jdbctest |  
\+--------------------+  
| user |  
\+--------------------+  
1 row in set (0.00 sec)

⑩ 往表里增加数据

insert into user(name,password,age,sex,birthday) values('name1','yeqing',22,'女','2012-11-09');  
insert into user(name,password,age,sex,birthday) values('name2','lijiuyang',20,'男','2012-11-09');

mysql> insert into user(name,password,age,sex,birthday) values('yeqing','yeqing'  
,22,'女','2012-11-09');  
Query OK, 1 row affected (0.04 sec)

mysql> insert into user(name,password,age,sex,birthday) values('lijiuyang','liji  
uyang',20,'男','2012-11-09');  
Query OK, 1 row affected (0.03 sec)

11 查看数据

1）查看全部数据

mysql> select \* from user;  
\+----+-----------+-----------+-----+------+------------+  
| id | name | password | age | sex | birthday |  
\+----+-----------+-----------+-----+------+------------+  
| 1 | yeqing | yeqing | 22 | 女 | 2012-11-09 |  
| 2 | lijiuyang | lijiuyang | 20 | 男 | 2012-11-09 |  
\+----+-----------+-----------+-----+------+------------+  
2 rows in set (0.00 sec)

2） 查看部分字段信息

mysql> select name,age from user;  
\+-----------+-----+  
| name | age |  
\+-----------+-----+  
| yeqing | 22 |  
| lijiuyang | 20 |  
\+-----------+-----+  
2 rows in set (0.00 sec)

3）加入条件检索

mysql> select \* from user where id = 1;  
\+----+--------+----------+-----+------+------------+  
| id | name | password | age | sex | birthday |  
\+----+--------+----------+-----+------+------------+  
| 1 | yeqing | yeqing | 22 | 女 | 2012-11-09 |  
\+----+--------+----------+-----+------+------------+  
1 row in set (0.00 sec)

mysql> select \* from user where name='yeqing';  
\+----+--------+----------+-----+------+------------+  
| id | name | password | age | sex | birthday |  
\+----+--------+----------+-----+------+------------+  
| 1 | yeqing | yeqing | 22 | 女 | 2012-11-09 |  
\+----+--------+----------+-----+------+------------+  
1 row in set (0.00 sec)

模糊检索  
mysql> select \* from user where name like '%y%';  
\+----+-----------+-----------+-----+------+------------+  
| id | name | password | age | sex | birthday |  
\+----+-----------+-----------+-----+------+------------+  
| 1 | yeqing | yeqing | 22 | 女 | 2012-11-09 |  
| 2 | lijiuyang | lijiuyang | 20 | 男 | 2012-11-09 |  
\+----+-----------+-----------+-----+------+------------+  
2 rows in set (0.00 sec)

mysql> select \* from user where name like '%ye%';  
\+----+--------+----------+-----+------+------------+  
| id | name | password | age | sex | birthday |  
\+----+--------+----------+-----+------+------------+  
| 1 | yeqing | yeqing | 22 | 女 | 2012-11-09 |  
\+----+--------+----------+-----+------+------------+  
1 row in set (0.00 sec)

中文字符看有无乱码

mysql> select \* from user where sex = '男';  
\+----+-----------+-----------+-----+------+------------+  
| id | name | password | age | sex | birthday |  
\+----+-----------+-----------+-----+------+------------+  
| 2 | lijiuyang | lijiuyang | 20 | 男 | 2012-11-09 |  
\+----+-----------+-----------+-----+------+------------+  
1 row in set (0.00 sec)

日期类  
mysql> select \* from user where birthday='2012-11-09';  
\+----+-----------+-----------+-----+------+------------+  
| id | name | password | age | sex | birthday |  
\+----+-----------+-----------+-----+------+------------+  
| 1 | yeqing | yeqing | 22 | 女 | 2012-11-09 |  
| 2 | lijiuyang | lijiuyang | 20 | 男 | 2012-11-09 |  
\+----+-----------+-----------+-----+------+------------+  
2 rows in set (0.00 sec)

4）升降序【默认升序】

mysql> select \* from user order by age;  
\+----+-----------+-----------+-----+------+------------+  
| id | name | password | age | sex | birthday |  
\+----+-----------+-----------+-----+------+------------+  
| 2 | lijiuyang | lijiuyang | 20 | 男 | 2012-11-09 |  
| 1 | yeqing | yeqing | 22 | 女 | 2012-11-09 |  
\+----+-----------+-----------+-----+------+------------+  
2 rows in set (0.00 sec)

mysql> select \* from user order by age desc;  
\+----+-----------+-----------+-----+------+------------+  
| id | name | password | age | sex | birthday |  
\+----+-----------+-----------+-----+------+------------+  
| 1 | yeqing | yeqing | 22 | 女 | 2012-11-09 |  
| 2 | lijiuyang | lijiuyang | 20 | 男 | 2012-11-09 |  
\+----+-----------+-----------+-----+------+------------+  
2 rows in set (0.00 sec)

5）限制取出数据的条数【分页开发中非常有用】

现在我再表里插入了多条数据

mysql> select \* from user;  
\+----+-----------+-----------+-----+------+------------+  
| id | name | password | age | sex | birthday |  
\+----+-----------+-----------+-----+------+------------+  
| 1 | yeqing | yq | 22 | 女 | 1990-09-07 |  
| 2 | lijiuyang | ljy | 20 | 男 | 1992-06-19 |  
| 4 | lijiuyang | lijiuyang | 20 | 男 | 2012-11-09 |  
| 5 | lijiuyang | lijiuyang | 20 | 男 | 2012-11-09 |  
| 6 | lijiuyang | lijiuyang | 20 | 男 | 2012-11-09 |  
| 7 | lijiuyang | lijiuyang | 20 | 男 | 2012-11-09 |  
| 8 | lijiuyang | lijiuyang | 20 | 男 | 2012-11-09 |  
| 9 | lijiuyang | lijiuyang | 20 | 男 | 2012-11-09 |  
| 10 | lijiuyang | lijiuyang | 20 | 男 | 2012-11-09 |  
| 11 | lijiuyang | lijiuyang | 20 | 男 | 2012-11-09 |  
\+----+-----------+-----------+-----+------+------------+  
10 rows in set (0.00 sec)

取出前2条  
select \* from user limit 0,2;

mysql> select \* from user limit 0,2;  
\+----+-----------+----------+-----+------+------------+  
| id | name | password | age | sex | birthday |  
\+----+-----------+----------+-----+------+------------+  
| 1 | yeqing | yq | 22 | 女 | 1990-09-07 |  
| 2 | lijiuyang | ljy | 20 | 男 | 1992-06-19 |  
\+----+-----------+----------+-----+------+------------+  
2 rows in set (0.00 sec)

取出后2条

mysql> select \* from user limit 8,10;  
\+----+-----------+-----------+-----+------+------------+  
| id | name | password | age | sex | birthday |  
\+----+-----------+-----------+-----+------+------------+  
| 10 | lijiuyang | lijiuyang | 20 | 男 | 2012-11-09 |  
| 11 | lijiuyang | lijiuyang | 20 | 男 | 2012-11-09 |  
\+----+-----------+-----------+-----+------+------------+  
2 rows in set (0.00 sec)

12 修改数据

update user set password='yq',birthday='1990-09-07' where name='yeqing';  
update user set password='ljy',birthday='1992-06-19' where name='lijiuyang';

mysql> update user set password='yq',birthday='1990-09-07' where name='yeqing';  
Query OK, 0 rows affected (0.06 sec)  
Rows matched: 1 Changed: 0 Warnings: 0

mysql> update user set password='ljy',birthday='1992-06-19' where name='lijiuyang';  
Query OK, 0 rows affected (0.09 sec)  
Rows matched: 1 Changed: 0 Warnings: 0

mysql> select \* from user;  
\+----+-----------+----------+-----+------+------------+  
| id | name | password | age | sex | birthday |  
\+----+-----------+----------+-----+------+------------+  
| 1 | yeqing | yq | 22 | 女 | 1990-09-07 |  
| 2 | lijiuyang | ljy | 20 | 男 | 1992-06-19 |  
\+----+-----------+----------+-----+------+------------+  
2 rows in set (0.00 sec)

13 删除数据

新增一条数据测试：

mysql> insert into user(name,password,age,sex,birthday) values('lijiuyang','liji  
uyang',20,'男','2012-11-09');  
Query OK, 1 row affected (0.15 sec)

查看数据

删除  
mysql> delete from user where id = 3;  
Query OK, 1 row affected (0.06 sec)

14 查看mysql的命令：mysql>?

mysql> ?

For information about MySQL products and services, visit:  
[http://www.mysql.com/][http_www.mysql.com]  
For developer information, including the MySQL Reference Manual, visit:  
[http://dev.mysql.com/][http_dev.mysql.com]  
To buy MySQL Enterprise support, training, or other products, visit:  
[https://shop.mysql.com/][https_shop.mysql.com]

List of all MySQL commands:  
Note that all text commands must be first on line and end with ';'  
? (\\?) Synonym for \`help'.  
clear (\\c) Clear the current input statement.  
connect (\\r) Reconnect to the server. Optional arguments are db and host.  
delimiter (\\d) Set statement delimiter.  
ego (\\G) Send command to mysql server, display result vertically.  
exit (\\q) Exit mysql. Same as quit.  
go (\\g) Send command to mysql server.  
help (\\h) Display this help.  
notee (\\t) Don't write into outfile.  
print (\\p) Print current command.  
prompt (\\R) Change your mysql prompt.  
quit (\\q) Quit mysql.  
rehash (\\\#) Rebuild completion hash.  
source (\\.) Execute an SQL script file. Takes a file name as an argument.  
status (\\s) Get status information from the server.  
tee (\\T) Set outfile \[to\_outfile\]. Append everything into given outfile.  
use (\\u) Use another database. Takes database name as argument.  
charset (\\C) Switch to another charset. Might be needed for processing binlog  
with multi-byte charsets.  
warnings (\\W) Show warnings after every statement.  
nowarning (\\w) Don't show warnings after every statement.

For server side help, type 'help contents'

[http_www.mysql.com]: http://www.mysql.com/
[http_dev.mysql.com]: http://dev.mysql.com/
[https_shop.mysql.com]: https://shop.mysql.com/