列举出所有的数据库
MariaDB [(none)]> show databases;
查看当前状态和登录用户是谁,查看数据库的版本和一些其他的信息
MariaDB [mysql]> status
返回结果:
--------------
mysql Ver 15.1 Distrib 10.4.15-MariaDB, for Linux (x86_64) using readline 5.1
Connection id: 9
Current database: mysql
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server: MariaDB
Server version: 10.4.15-MariaDB MariaDB Server
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 1 hour 9 min 45 sec
Threads: 7 Questions: 83 Slow queries: 0 Opens: 36 Flush tables: 1 Open tables: 30 Queries per second avg: 0.019
进入某一个数据库里,比如进入mysql这个数据库里
MariaDB [(none)]> use mysql
列举出mysql里所有的表,需要先进入mysql表,use mysql
MariaDB [(none)]> show tables;
查看MySQL里的user这张表的内容,这里显示的比较乱
MariaDB [mysql]> select * from user;
可以竖排显示,这样看起来就没有那么乱了
MariaDB [mysql]> select * from user\G
专门挑出两个列来查看,查看user列和host列查看,这样看起来就整齐的很多的
MariaDB [mysql]> select user,host from user;
返回结果
+-------------+-------------+
| User | Host |
+-------------+-------------+
| | centos7.6-1 |
| | localhost |
| mariadb.sys | localhost |
| mysql | localhost |
| root | localhost |
+-------------+-------------+
5 rows in set (0.001 sec)
查看user和host和password列查看
MariaDB [mysql]> select user,host,password from user;
返回结果:
+-------------+-------------+----------+
| User | Host | Password |
+-------------+-------------+----------+
| mariadb.sys | localhost | |
| root | localhost | invalid |
| mysql | localhost | invalid |
| | localhost | |
| | centos7.6-1 | |
+-------------+-------------+----------+
5 rows in set (0.001 sec)
列举出所有查存放密码口令的地方
MariaDB [mysql]> select password,authentication_string from user;
返回结果:
+----------+-----------------------+
| Password | authentication_string |
+----------+-----------------------+
| | |
| invalid | invalid |
| invalid | invalid |
| | |
| | |
+----------+-----------------------+
5 rows in set (0.001 sec)
查看user列的名称,desc描述的意思,描述user这张表.描述一个数据表,(查看表结果)
MariaDB [mysql]> desc user;
返回结果:
+------------------------+---------------------+------+-----+----------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+---------------------+------+-----+----------+-------+
| Host | char(60) | NO | | | |
| User | char(80) | NO | | | |
| Password | longtext | YES | | NULL | |
| Select_priv | varchar(1) | YES | | NULL | |
| Insert_priv | varchar(1) | YES | | NULL | |
| Update_priv | varchar(1) | YES | | NULL | |
| Delete_priv | varchar(1) | YES | | NULL | |
| Create_priv | varchar(1) | YES | | NULL | |
| Drop_priv | varchar(1) | YES | | NULL | |
| Reload_priv | varchar(1) | YES | | NULL | |
| Shutdown_priv | varchar(1) | YES | | NULL | |
| Process_priv | varchar(1) | YES | | NULL | |
| File_priv | varchar(1) | YES | | NULL | |
| Grant_priv | varchar(1) | YES | | NULL | |
| References_priv | varchar(1) | YES | | NULL | |
| Index_priv | varchar(1) | YES | | NULL | |
| Alter_priv | varchar(1) | YES | | NULL | |
| Show_db_priv | varchar(1) | YES | | NULL | |
| Super_priv | varchar(1) | YES | | NULL | |
| Create_tmp_table_priv | varchar(1) | YES | | NULL | |
| Lock_tables_priv | varchar(1) | YES | | NULL | |
| Execute_priv | varchar(1) | YES | | NULL | |
| Repl_slave_priv | varchar(1) | YES | | NULL | |
| Repl_client_priv | varchar(1) | YES | | NULL | |
| Create_view_priv | varchar(1) | YES | | NULL | |
| Show_view_priv | varchar(1) | YES | | NULL | |
| Create_routine_priv | varchar(1) | YES | | NULL | |
| Alter_routine_priv | varchar(1) | YES | | NULL | |
| Create_user_priv | varchar(1) | YES | | NULL | |
| Event_priv | varchar(1) | YES | | NULL | |
| Trigger_priv | varchar(1) | YES | | NULL | |
| Create_tablespace_priv | varchar(1) | YES | | NULL | |
| Delete_history_priv | varchar(1) | YES | | NULL | |
| ssl_type | varchar(9) | YES | | NULL | |
| ssl_cipher | longtext | NO | | | |
| x509_issuer | longtext | NO | | | |
| x509_subject | longtext | NO | | | |
| max_questions | bigint(20) unsigned | NO | | 0 | |
| max_updates | bigint(20) unsigned | NO | | 0 | |
| max_connections | bigint(20) unsigned | NO | | 0 | |
| max_user_connections | bigint(21) | NO | | 0 | |
| plugin | longtext | NO | | | |
| authentication_string | longtext | NO | | | |
| password_expired | varchar(1) | NO | | | |
| is_role | varchar(1) | YES | | NULL | |
| default_role | longtext | NO | | | |
| max_statement_time | decimal(12,6) | NO | | 0.000000 | |
+------------------------+---------------------+------+-----+----------+-------+
删除表
drop table IF EXISTS表名;
解释
IF EXISTS
判断一个表面存不存在,如果不存在不会报错,建议把这条写上
mysql使用模式
交互模式
可运行命令分两类
客户端命令,不需要加分号;
简写命令
\h
help 帮助命令
\u
use
\s
status
!
system调用系统命令
服务端命令
SQL语句,需要语句结束符";"(分号结尾)
脚本模式
mysql -uUSERNAME -pPASSWORD </dir/somefile.sql
mysql<source /dir/somefile.sql
mysql客户端常用选项
-A
--no-auto-rehash禁止补全
-D --databses=name
指定默认登入数据库如
mysql -uroot -p -Dmysql 这里默认登入mysql这个数据库
-e --execute="name" SQL COMMAND
连接至服务器并让其执行此命令后直接返回如
mysql -uroot -p -e "show databases;"
-u --user=username
mysql用户名,默认为root
-h --host=hostanme
远程登录主机的IP或者登录主机的主机名,默认为localhost,客户端链接服务端,服务器会反解客户端的IP为主机名,一定要关闭此功能
(sklp_name_resolve=ON)
-p --password
mysql登录密码,默认为空
-P --port
mysql服务器监听的端口,默认为3306TCP
-S --socket/PATH/TO/mysql.sock
mysql套接字文件存放路径
source,导入sql脚本
首先在linux,编写一个sql的脚本,建议用sql结尾
vim /root/test.sql
写入以下内容
use mysql
select user,host from user;
进入数据库
mysql -uroot -pPASSWORD
没有密码直接输入mysql回车即可
导入sql脚本
MariaDB [(none)]> source /root/test.sql
返回结果:
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
+------+-------------+
| user | host |
+------+-------------+
| root | 127.0.0.1 |
| root | ::1 |
| root | centos8.2-1 |
| root | localhost |
+------+-------------+
4 rows in set (0.000 sec)
不进入数据库,并执行内部命令
[root@CEntos8 ~]# mysql -uroot -p123456 -e '\s'
返回结果
--------------
mysql Ver 15.1 Distrib 10.3.17-MariaDB, for Linux (x86_64) using readline 5.1
Connection id: 20
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server: MariaDB
Server version: 10.3.17-MariaDB MariaDB Server
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 45 min 27 sec
Threads: 7 Questions: 67 Slow queries: 0 Opens: 37 Flush tables: 1 Open tables: 31 Queries per second avg: 0.024
--------------
创建一个test的数据库
create database test;
进入test的数据里
use test
在test里创建一个xg的表要求如下
d字段,11位整型,不为空,,自增,主键
usernme字段,varchar类型,20长度
assword字段,char类型32长度
create table xg( id int(11) not null auto_increment,
username varchar(20),
password char(32),
primary key(id)
);
一整行写法
create table xg( id int(11) not null auto_increment, username varchar(20), password char(32), primary key(id) );
解释:
int(整型)一般为数字,一般是正数,
not null 不许为空
auto_increment 自动递增地段名称,这个参数一般给第一个字段添加,一般第一个字段都为序号
char (定长字符)比如char(10)这个列表里最多放10个再多了就丢弃了
varchar(不定长),自动扩展
primary key (主键字段名)一般就是序号所在的那一列(主键不能重复)
create table xg 船舰的表名xg
( id int(11) not null auto_increment, id字段,11位整型,不为空,,自增,主键
username varchar(20), usernme字段,varchar类型,20长度
password char(32), password字段,char类型32长度
primary key(id) (主键字段名)一般就是序号所在的那一列(主键不能重复)
);结束
列举出test所有的表
MariaDB [test]> show tables;
返回结果
+----------------+
| Tables_in_test |
+----------------+
| xg |
+----------------+
1 row in set (0.001 sec)
描述以下xg这张表
返回结果
MariaDB [test]> desc xg;
返回结果
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(20) | YES | | NULL | |
| password | char(32) | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.002 sec)
增加记录
1.语法
INSERT INTO 表名称 VALUES (值1,值2,....);
2.语法
INSERT INTO 表名称(列1,列2....) VALUES (值1,值2,....);
往数据包xg表中新增一个记录username为zhangsan,lisi,wangwu,password为123456(MD5加密)(E10ADC3949BA59ABBE56E057F20F883E)
insert into xg(username,password) values('zhangsan','E10ADC3949BA59ABBE56E057F20F883E');
insert into xg(username,password) values ('lisi','E10ADC3949BA59ABBE56E057F20F883E');
insert into xg(username,password) values ('wangwu','E10ADC3949BA59ABBE56E057F20F883E');
要求前面的列名与值要一一对应,如果错误就不堪设想
查寻表记录
select 列名称1,列名称2 from 表名称 where 条件;
查询用户和密码;
MariaDB [test]> select username,password from xg;
返回结果
+----------+----------------------------------+
| username | password |
+----------+----------------------------------+
| zhangsan | E10ADC3949BA59ABBE56E057F20F883E |
| lisi | E10ADC3949BA59ABBE56E057F20F883E |
| wangwu | E10ADC3949BA59ABBE56E057F20F883E |
+----------+----------------------------------+
3 rows in set (0.001 sec)
第二种查看所有的列
MariaDB [test]> select * from xg;
返回结果
+----+----------+----------------------------------+
| id | username | password |
+----+----------+----------------------------------+
| 1 | zhangsan | E10ADC3949BA59ABBE56E057F20F883E |
| 2 | lisi | E10ADC3949BA59ABBE56E057F20F883E |
| 3 | wangwu | E10ADC3949BA59ABBE56E057F20F883E |
+----+----------+----------------------------------+
第三种查看,只查询用户名和密码,并且是id=2的
MariaDB [test]> select username,password from xg where id=2;
返回结果
+----------+----------------------------------+
| username | password |
+----------+----------------------------------+
| lisi | E10ADC3949BA59ABBE56E057F20F883E |
+----------+----------------------------------+
1 row in set (0.001 sec)
第四种,字段查看,查询usernam和password,只看username段里的zhangsan,zhangsan为字段一定要加上'zhangsan'
MariaDB [test]> select username,password from xg where username='zhangsan';
+----------+----------------------------------+
| username | password |
+----------+----------------------------------+
| zhangsan | E10ADC3949BA59ABBE56E057F20F883E |
+----------+----------------------------------+
1 row in set (0.001 sec)
更新记录
语法:update 表名称 set 列名称1 = 新值1,列名称2 = 新值2,... where 列名称 =某值;
使用更新语句更新id大于等于2的记录,将其密码改为123456789 MD5加密(25F9E794323B453885F5181F1B624D0B)
sql语句
查看Mysql支持的字符集,默认字符集是拉丁
MariaDB [(none)]> show character set;
或者
MariaDB [(none)]> show collation;
+----------+-----------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| tis620 | TIS620 Thai | tis620_thai_ci | 1 |
| euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| cp1250 | Windows Central European | cp1250_general_ci | 1 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| cp866 | DOS Russian | cp866_general_ci | 1 |
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
| macce | Mac Central European | macce_general_ci | 1 |
| macroman | Mac West European | macroman_general_ci | 1 |
| cp852 | DOS Central European | cp852_general_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |
| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| utf16 | UTF-16 Unicode | utf16_general_ci | 4 |
| utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 |
| cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| cp1257 | Windows Baltic | cp1257_general_ci | 1 |
| utf32 | UTF-32 Unicode | utf32_general_ci | 4 |
| binary | Binary pseudo charset | binary | 1 |
| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
+----------+-----------------------------+---------------------+--------+
40 rows in set (0.001 sec)
