概述
二、常用SQL语句
库 -- >表 -- >行 -- >字段
■库操作
___________________________________________________________
mysql> show databases; #查看本地库
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.08 sec)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 changedmysql> select database(); #查看当前库
+------------+
| database() |
+------------+
| mysql |
+------------+
1 row in set (0.00 sec)mysql> select version(); #查看当前数据库版本
+-----------+
| version() |
+-----------+
| 5.6.28 |
+-----------+
1 row in set (0.00 sec)mysql> create database szk; #创建一个szk库
Query OK, 1 row affected (0.02 sec)mysql> drop database szk; #删除库
Query OK, 0 rows affected (0.00 sec)
■表操作
___________________________________________________________mysql> show tables; #查看当前库中的表
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |mysql> desc user; #查看当前表的字段
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+mysql> insert into tb1 values(1, 'szk'); #插入语句1
Query OK, 1 row affected (0.01 sec)
mysql> select * from tb1; #查看表中语句
+------+------+
| id | name |
+------+------+
| 1 | szk |
+------+------+
1 row in set (0.00 sec)mysql> truncate table tb1; #清空这个表
Query OK, 0 rows affected (0.00 sec)
mysql> drop table tb1; #删除这个表
Query OK, 0 rows affected (0.00 sec)
mysql> create table tb1 (`id` int(4), `name` char(40)) ENGINE=MyISAM DEFAULT CHARSet=gbk; #创建一个表，表名为tb1，并且有俩语句，编码为gbk
Query OK, 0 rows affected (0.02 sec)mysql> desc tb1; #查看当前表中字段
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(4) | YES | | NULL | |
| name | char(40) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
■权限操作
___________________________________________________________[root@lab-1-C6 ~]# mysqladmin -uroot password '111qqq...' #给root用户设置密码
语法：
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
mysql> create user szk5043@192.168.1.201 identified by 'szk5043'; #创建一个mysql登录用户，通过identified设定密码，并且限制用户只能从指定的主机登录
mysql> Drop user szk5043; #删除用户
语法：
grant 权限1,权限2,...权限n on 数据库名称.表名称 to 用户名@用户地址 identified by '连接口令';
select,insert,update,delete,create,drop,index,alter,grant,references,reload,shutdown,process,file等14个权限
mysql>grant all privileges on *.* to szk5043@192.168.1.201 identified by 'szk5043';
给szk5043分配可对所有数据库的所有表进行所有操作的权限，并设定口令为szk5043
mysql>flush privileges; #刷新系统权限表
■查看操作
___________________________________________________________mysql> show variables; #查看进程
mysql> show status; #查看状态
mysql> show status like '%running'; #查看单个对象状态
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| Slave_running | OFF |
| Threads_running | 1 |
+-----------------+-------+
2 rows in set (0.00 sec)
mysql> repair table discuz.pre_forum_port; #修复表
+-----------------------+--------+----------+---------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-----------------------+--------+----------+---------------------------------------------+
| discuz.pre_forum_port | repair | Error | Table 'discuz.pre_forum_port' doesn't exist |
| discuz.pre_forum_port | repair | status | Operation failed |
+-----------------------+--------+----------+---------------------------------------------+
最后
以上就是聪慧项链为你收集整理的mysql语句sec_常用mysql语句的全部内容，希望文章能够帮你解决mysql语句sec_常用mysql语句所遇到的程序开发问题。
如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。
发表评论 取消回复