13.5 常用sql语句
常用sql语句目录概要
- select count(*) from mysql.user;
- select * from mysql.db;
- select db from mysql.db;
- select db,user from mysql.db;
- select * from mysql.db where host like '192.168.%';
- insert into db1.t1 values (1, 'abc');
- update db1.t1 set name='aaa' where id=1;
- truncate table db1.t1;
- drop table db1.t1;
- drop database db1;
常用sql语句
- 增删改查，就是mysql和其他关系型数据库常用的select语句操作命令
查询语句
- 首先登录root下的mysql
[root@hf-01 ~]# mysql -uroot -p'hanfeng'
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 5.6.35 MySQL Community Server (GPL)
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> - 使用db1库
mysql> use db1;
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> - 查看当前库的所有表show tables;
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| t1 |
+---------------+
1 row in set (0.00 sec)
mysql> - 查看表的行数 select count(*) from mysql.user;
- 库和表中间有个分割符，就是用点 . 分割
mysql> select count(*) from mysql.user;
+----------+
| count(*) |
+----------+
| 12 |
+----------+
1 row in set (0.01 sec)
mysql> - 就是说user表有12行内容
- 查看所有的内容 select * from mysql.db;（这样看起来会很乱） ——>可以在后面加上\G，如select * from mysql.db\G;
- 这里的 * 表示查看所有内容
mysql> select * from mysql.db/G;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/G' at line 1
mysql> select * from mysql.db\G;
*************************** 1. row ***************************
Host: %
Db: test
User:
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: N
Execute_priv: N
Event_priv: Y
Trigger_priv: Y
*************************** 2. row ***************************
等等等，只截取了一部分- 查看db库的所有内容 select db from mysql.db; 第一个db是字段
mysql> select db from mysql.db;
+---------+
| db |
+---------+
| test |
| test\_% |
| db1 |
| db1 |
+---------+
4 rows in set (0.01 sec)
mysql> - 查db字段和user字段 select db,user from mysql.db;
mysql> select db,user from mysql.db;
+---------+-------+
| db | user |
+---------+-------+
| test | |
| test\_% | |
| db1 | user2 |
| db1 | user2 |
+---------+-------+
4 rows in set (0.00 sec)
mysql> - 模糊查询 select * from mysql.db where host like '192.168.%';
- like 就是模糊匹配
mysql> select * from mysql.db where host like '192.168.%'\G;
*************************** 1. row ***************************
Host: 192.168.133.1
Db: db1
User: user2
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: N
Create_priv: N
Drop_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Execute_priv: N
Event_priv: N
Trigger_priv: N
*************************** 2. row ***************************
Host: 192.168.133.2
Db: db1
User: user2
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: N
Create_priv: N
Drop_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Execute_priv: N
Event_priv: N
Trigger_priv: N
2 rows in set (0.00 sec)
ERROR:
No query specified
mysql> 插入语句
- 查看创建的表
mysql> desc db1.t1;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(4) | YES | | NULL | |
| name | char(40) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql>- 查看db1.t1表的内容，会发现为空
mysql> select * from db1.t1;
Empty set (0.00 sec)
mysql> - 插入数据到 insert into db1.t1 values (1, 'abc');
- 插入1, 'abc'到db1.t1表
mysql> insert into db1.t1 values (1, 'abc');
Query OK, 1 row affected (0.02 sec)
mysql> - 再来查询db1.t1
mysql> select * from db1.t1;
+------+------+
| id | name |
+------+------+
| 1 | abc |
+------+------+
1 row in set (0.00 sec)
mysql> - 这样就成功了插入了一条数据，在插入的时候 name 这个字段应该是是一个字符串，字符串需要加上一个单引号 ' ' ，数字可以不加单引号
mysql> insert into db1.t1 values (1, 234);
Query OK, 1 row affected (0.01 sec)
mysql> select * from db1.t1;
+------+------+
| id | name |
+------+------+
| 1 | abc |
| 1 | 234 |
+------+------+
2 rows in set (0.00 sec)
mysql> 5.这里没有做限制，这里id和name都可以是相同的，同一个字段里有相同的数字，相同的值 6. 也可以做一些限制，在插入相同的id的时候，就会冲突
update操作
- 更改db1.t1表 的字符串为name 的数据 和 字符串为id 的数据
- update db1.t1 set name='aaa' where id=1;
mysql> update db1.t1 set name='aaa' where id=1;
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from db1.t1;
+------+------+
| id | name |
+------+------+
| 1 | aaa |
| 1 | aaa |
+------+------+
2 rows in set (0.00 sec)
mysql> delete操作
- 删除db1.t1表 的数据 和 字符串为id 的数据
- delete from db1.t1 where id=1;
mysql> delete from db1.t1 where id=1;
Query OK, 2 rows affected (0.01 sec)
mysql> select * from db1.t1;
Empty set (0.00 sec)
mysql> truncate清空一个表
- 清空表数据 truncate table db1.t1;
- 即使表的数据清空了，但表的字段依旧存在的
mysql> insert into db1.t1 values (1, 234);
Query OK, 1 row affected (0.00 sec)
mysql> select * from db1.t1;
+------+------+
| id | name |
+------+------+
| 1 | 234 |
+------+------+
1 row in set (0.00 sec)
mysql> truncate table db1.t1;
Query OK, 0 rows affected (0.02 sec)
mysql> select * from db1.t1;
Empty set (0.00 sec)
mysql> desc db1.t1;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(4) | YES | | NULL | |
| name | char(40) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> - truncate 只是清空的内容，而drop 会清空表的数据并清除表的框架
- drop 会把表的框架也丢掉 drop table db1.t1;
mysql> drop table db1.t1;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from db1.t1; //因为表的架构已经不存在了
ERROR 1146 (42S02): Table 'db1.t1' doesn't exist
mysql> - 丢掉表 drop database db1;
总结
- 在使用mysql的时候，少用 * 这样的操作，因为若是一个表里面的内容很多，select count(*)这样操作就会很耗时，浪费资源
- 数据库中常用引擎是myisam和innodb，默认mysql库里面都是使用的myisam引擎
- 特点：myisam引擎，能自动去统计有多少行
- 在select count(*)查看表的时候会很快
- use mysql;
- show create table user\G;
- 特点：innodb引擎，不会自动统计行数，每次去查询，每次去统计行数，就会很耗时
- use db1
- show create table t1;
- 所以select count(*)这种操作尽量减少，会耗费太多资源
- 特点：myisam引擎，能自动去统计有多少行
本文参与 腾讯云自媒体分享计划，分享自作者个人站点/博客。
如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读