mysql相关命令

# 安装服务端 (需要中途设置root密码)
sudo apt-get install mysql-server
# 安装客户端
sudo apt-get install mysql-client
# 查看是否安装并启动成功
sudo netstat -tap | grep mysql
# mysql的配置文件在 /etc/mysql/my.conf

sudo service mysql start
mysql -u root -p

show databases;

use 数据库名称
use information_schema

show tables；

exit or quit

create 数据库名称
create database mysql_shiyan;

show tables;

create table 表名
create table employee (id int(10),name char(20),pyhone int(12))；

insert into 表名 值
insert into employee(id,name,phone) values(01,'Tom',110110110);
or
insert into employee values(02,'Jack',119119119);
or
insert into employee(id,name) values(03,'Rose');

select * from employee;

drop table 表名;
drop table employee;

drop database 库名;
drop database mysql_shiyan;

在mysql控制台输入source + 目录路径
source /home/olizhao2/SQL3/MySQL-03-01.sql

show create table 表名

SELECT 要查询的列名 FROM 表名字 WHERE 限制条件;
SELECT name,age FROM employee;

符号查询 > < =
SELECT name,age FROM employee WHERE age>25;
SELECT name,age,phone FROM employee WHERE name='Mary';
“AND”与“OR”
#筛选出 age 小于 25，或 age 大于 30
SELECT name,age FROM employee WHERE age<25 OR age>30;
#筛选出 age 大于 25，且 age 小于 30
SELECT name,age FROM employee WHERE age>25 AND age<30;
IN 和 NOT IN
SELECT name,age,phone,in_dpt FROM employee WHERE in_dpt IN ('dpt3','dpt4');
SELECT name,age,phone,in_dpt FROM employee WHERE in_dpt NOT IN ('dpt1','dpt3');
通配符
SELECT name,age,phone FROM employee WHERE phone LIKE '1101__';
SELECT name,age,phone FROM employee WHERE name LIKE 'J%';

SELECT name,age,salary,phone FROM employee ORDER BY salary DESC;
# salary降序排序 asc为升序排

select max(salary) as max_salary,min(salary) from employee;

SELECT of_dpt,COUNT(proj_name) AS count_project FROM project
WHERE of_dpt IN
(SELECT in_dpt FROM employee WHERE name='Tom');

将某数据库下的 表 table_1改名
rename table table_1 to table_2;

drop table 表名
drop table table_2;

alter table employee add height int(4) default 170;

# 新增体重默认120放在age的后边
alter table employee add weight int(4) default 120 after age;
# 放在第一行
alter table employee add test int(10) default 11 first;

alter table employee drop test;

alter table employee change height shengao int(5) default 170;

alter table 表名字 modify 列名字 新数据类型;

update employee set age=99,salary=3212 where name='Tom';

delete from employee where name = 'Tom';

1 alter table employee add index idx_id(id);
2 create index idx_name on employee(name);

CREATE VIEW 视图名(列a,列b,列c) AS SELECT 列1,列2,列3 FROM 表名字;

LOAD DATA INFILE '文件路径和文件名' INTO TABLE 表名字;

vim /etc/mysql/my.cnf
bind-address            = 172.0.0.1 改成
bind-address            = 0.0.0.0
1、新建用户远程连接mysql数据库
grant all on *.* to admin@'%' identified by '123456' with grant option; 
flush privileges;
允许任何ip地址(%表示允许任何ip地址)的电脑用admin帐户和密码(123456)来访问这个mysql server。
注意admin账户不一定要存在。
2、支持root用户允许远程连接mysql数据库
grant all privileges on *.* to 'root'@'%' identified by '123456' with grant option;
flush privileges;