Common MySQL Statements

II. Common SQL statements

Library-->--> Line--> field

Library operations

___________________________________________________________

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 namesyou can turn off this feature to get a quicker startup With-adatabase changed

Mysql> 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)

Table Operations

___________________________________________________________

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 ') ;       #插入语句1Query  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 (max))  engine=myisam default  CHARSet=gbk;     #创建一个表, the table name is TB1, and there are two statements, encoded as 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)

Permission actions

___________________________________________________________

[[email protected] ~]# mysqladmin -uroot  password  ' 111qqq ... '      #给root用户设置密码语法:create user  ' username ' @ ' host '   identified by  ' password '; mysql> create user [email protected] identified  by  ' szk5043 ';       #创建一个mysql登录用户, set the password by identified, and restrict users from the specified host to login mysql> drop user szk5043;      #删除用户语法:grant  Permissions 1, Permissions 2,... Permissions n on  the database name. Table name  to  user name @ user address  identified by  ' connection password '; Select,insert,update, Delete,create,drop,index,alter,grant,references,reload,shutdown,process,file, etc. 14 permissions Mysql>grant all  privileges on *.* to [email protected] identified by  ' szk5043 ';    assigns szk5043 permissions to all operations on all tables in all databases, and sets the password to szk5043mysql>flush privileges;   # Refresh the System Permissions table 

View Operations

___________________________________________________________

mysql> show variables;          #查看进程mysql >  show status;              # View 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                             |+-----------------------+--------+--------- -+---------------------------------------------+

This article is from the "Fuqin Wine" blog, please make sure to keep this source http://szk5043.blog.51cto.com/8456440/1762961

Common MySQL Statements