MySQL基本语法

MySQL基本命令
MySQL 登录命令
mysql -uroot -proot或者root@owaspbwa:~# mysql -uroot -p
Enter password:

创建数据库
mysql> create database aaa；
显示所有数据库
mysql> show databases;
查看所在数据库
mysql> select database();
删除数据库
mysql> drop database aaa；
进入数据库
mysql> use yazd;
查看表记录
mysql> select * from users\G
创建表格并定义格式
mysql>create table 1927(学号 char(10),姓名 varchar(12),性别 char(1),手机号 char(12),
通信地址 varchar(40));
查看表
mysql> desc 1927;
查看表里面是数据
mysql>select * from 1927;
添加表内容
mysql>insert into 1927 values(‘10005’,‘王方块’,‘男’,‘13567890123’,‘西安
大雁塔 5 层’)；
删除数据库
MariaDB [tedu]> delete from ntd1912;
MariaDB [tedu]> select * from ntd1912; //查看表中的记录看是否删除
MariaDB [tedu]> show tables ntd1912; //查看表是否还在
MariaDB [tedu]> desc ntd1912; //查看表格式是否在

删除整个 ntd1912 表，确认结果
MariaDB [tedu]> drop table ntd1912;
MariaDB (tedu]> show tables ntd1912;

MySQL数据查询
/简单查询示例
当前库dvwa dvwa.users
mysql> select * from users;
mysql> select user_id,first_name,last_name from users;

其它库 mysql.user
mysql> desc mysql.user;
mysql> select * from mysql.user;
mysql> select user,password,host from mysql.user;

其它库 wordpress.user
mysql> desc wordpress.wp_users;
mysql> select * from wordpress.wp_users;
mysql> select user_login,user_pass from wordpress.wp_users;

//条件查询示例
mysql> select user,password,host from mysql.user where user=‘root’;
mysql> select user,password,host from mysql.user where user=‘root’ and host=‘localhost’;
mysql> select user,password,host from mysql.user where user=‘root’ or host=‘localhost’;
mysql> desc dvwa.users;
mysql> select user_id,first_name,last_name from dvwa.users where first_name=‘yangge’;
mysql> select user_id,first_name,last_name from dvwa.users where first_name=‘yangge’ or 1=1;
mysql> select user_id,first_name,last_name from dvwa.users where first_name=‘admin’ and 1=2;
mysql> select user_id,first_name,last_name from dvwa.users where user_id=2;
mysql> select user_id,first_name,last_name from dvwa.users where user_id=7;
mysql> select user_id,first_name,last_name from dvwa.users where user_id=7 or 1=1;

//联合查询UNION
mysql> select user,password from mysql.user;
mysql> select user_login,user_pass from wordpress.wp_users;
mysql> select user,password from mysql.user union select user_login,user_pass from
wordpress.wp_users;
mysql> select user,password,host from mysql.user union select user_login,user_pass from
wordpress.wp_users;
ERROR 1222 (21000): The used SELECT statements have a different number of columns
注：union查询前后字段数必须相同
mysql> select user,password,host from mysql.user union select user_login,user_pass,3 from
wordpress.wp_users;

方法：猜字段数
mysql> select * from dvwa.users union select 1;
mysql> select * from dvwa.users union select 1,2;
mysql> select * from dvwa.users union select 1,2,3;
mysql> select * from dvwa.users union select 1,2,3,4;
mysql> select * from dvwa.users union select 1,2,3,4,5;
mysql> select * from dvwa.users union select 1,2,3,4,5,6;
mysql> select * from dvwa.users union select user_login,user_pass,1,2,3,4 from
wordpress.wp_users;

information_schema
//联合查询UNION
mysql> select user,password from mysql.user;
mysql> select user_login,user_pass from wordpress.wp_users;
mysql> select user,password from mysql.user union select user_login,user_pass from
wordpress.wp_users;
mysql> select user,password,host from mysql.user union select user_login,user_pass from
wordpress.wp_users;
ERROR 1222 (21000): The used SELECT statements have a different number of columns
注：union查询前后字段数必须相同
mysql> select user,password,host from mysql.user union select user_login,user_pass,3 from
wordpress.wp_users;
思考：前面的查询已经写死了，如何使下面的语句成功？
mysql> select * from dvwa.users
-> union
-> select user_login,user_pass from wordpress.wp_users;
ERROR 1222 (21000): The used SELECT statements have a different number of columns
方法：猜字段数
mysql> select * from dvwa.users union select 1;
mysql> select * from dvwa.users union select 1,2;
mysql> select * from dvwa.users union select 1,2,3;
mysql> select * from dvwa.users union select 1,2,3,4;
mysql> select * from dvwa.users union select 1,2,3,4,5;
mysql> select * from dvwa.users union select 1,2,3,4,5,6;
mysql> select * from dvwa.users union select user_login,user_pass,1,2,3,4 from
wordpress.wp_users;

==查询数据库库名、表名 information_schema.tables=
mysql> select * from information_schema.TABLES\G
mysql> select DISTINCT TABLE_SCHEMA from information_schema.TABLES; //等价于show
databases
mysql> select TABLE_SCHEMA,TABLE_NAME from information_schema.TABLES\G
mysql> select TABLE_SCHEMA,GROUP_CONCAT(TABLE_NAME) from information_schema.TABLES GROUP BY
TABLE_SCHEMA\G
mysql> select TABLE_NAME from INFORMATION_SCHEMA.tables where TABLE_SCHEMA=‘dvwa’; //等价于show
tables