- A+
所属分类:数据库
交互式登陆命令:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
mysql: 常见参数: -u:指定用户名 -p:指定密码 -h:指定登陆主机 -P:指定端口 -S:指定socket文件 范例: [root@VM_87_96_centos ~]# mysql -uroot -p123456 -P3306 -S /tmp/mysql.sock -hlocalhost Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 119927 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> |
登陆了mysql,当然要执行些有意思的查询啦
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
#查询数据库: mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | test | | mysql | | performance_schema | +--------------------+ 4 rows in set (0.05 sec) #创建一个库 mysql> create database tommy; Query OK, 1 row affected (0.01 sec) #进入一个数据库 mysql> use tommy Database changed #查询当前所在数据库 mysql> select database(); +------------+ | database() | +------------+ | tommy | +------------+ 1 row in set (0.00 sec) #新建一个表试试 mysql> create table t1(id int,name varchar(20)); Query OK, 0 rows affected (0.18 sec) #查询当前数据库所有表 mysql> show tables; +-----------------+ | Tables_in_tommy | +-----------------+ | t1 | +-----------------+ 1 row in set (0.39 sec) #查询表结构 mysql> desc t1; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) |
表数据的增删改查:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 |
增加一张表:auto_increment自增 mysql> create table iyunw(id int not null auto_increment,name varchar(20),primary key(id)); Query OK, 0 rows affected (0.08 sec) #查询刚创建的表 mysql> desc iyunw; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec) 表里增加一行数据 mysql> insert into iyunw(name) values('Tommy'); Query OK, 1 row affected (0.02 sec) #查询当前表所有数据 mysql> select * from iyunw; +----+-------+ | id | name | +----+-------+ | 1 | Tommy | +----+-------+ 1 row in set (0.00 sec) 增加多行数据: mysql> insert into iyunw(name) values('Tommy1'),('tommy2'),('tommy3'); Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0 delete删除某一行数据,where指定删除条件,不然会全删 mysql> delete from iyunw where name='Tommy'; Query OK, 1 row affected (0.02 sec) mysql> select * from iyunw; +----+--------+ | id | name | +----+--------+ | 2 | Tommy1 | | 3 | tommy2 | | 4 | tommy3 | +----+--------+ 3 rows in set (0.00 sec) update更改数据: mysql> update iyunw set name='jarry' where id=3; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from iyunw; +----+--------+ | id | name | +----+--------+ | 2 | Tommy1 | | 3 | jarry | | 4 | tommy3 | +----+--------+ 3 rows in set (0.00 sec) alter修改表:ALTER TABLE alter table 表名 rename 新表名; alter table 表名 modify id int(10); 修改表中id字段字段类型 alter table 表名 change 原字段名 新字段名 新字段类型; alter table hd1 add id enum("B","d"); 添加一个字段枚举字段id类型只能选B或者d alter table hd1 add id_id int(10) first ;天假一个字段并在第一个字段 alter table hd1 drop id;删除id字段 |
权限相关:
1 2 3 4 5 6 7 |
用户权限管理: 创建:CREATE USER 'username'@'host' [IDENTIFIED BY 'password']; 删除:DORP USER 'username'@'host'; 授权:权限级别:管理权限、数据库、表、字段、存储例程; GRANT priv_type,.... ON dbname.tbname TO 'user'@'host' [IDENTIFIED BY 'password'] 回收权限: REVOKE priv_type, ... ON db_name.tb_name FROM 'user'@'host'; |
其他:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
查询查询操作是否用到索引:EXPLAIN : 查询当前服务器统计数据:SHOW GLOBAL STATUS; 更新权限:FLUSH PRIVILEGES; 查询引擎:SHOW ENGINES; 查看支持所有字符集:SHOW CHARACTER SET; 查看支持所有排序规则:SHOW COLLATIONS; 查询表创建过程:SHOW CREATE TABLE student; 查看表当前状态:SHOW TABLE STATUS LINK 'student'\G 查询表的索引:SHOW INDEX FROM tb_name; 查看用户权限:SHOW GRANTS FOR user@host; 从库查看当前状态:show processlist; 查询详细参数:show variables; 设置参数:set global "" 锁表:5.1:flush tables with read lock; 5.5:flush table with read lock; |
- 我的微信
- 这是我的微信扫一扫
-
- 我的微信公众号
- 我的微信公众号扫一扫
-