MySQL基本语法
一.MySQL 基本概念:
database db: 数据库
table: 表
db1:t1,t2,t3
db2:t2,t3,t4
二.字段类型:
http://www.runoob.com/mysql/mysql-data-types.html
三.常规命令:
use db1; 使用db1
show tables; 查看db1下的所有的表
show create table t1; 查看t1的表结构
创建一个新的db:
create database xxx;
eg:
mysql> create database xk;
Query OK, 1 row affected (0.42 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| xk |
+--------------------+
5 rows in set (0.00 sec)
(1)创建一个新的table:
create table xk.demo(
id int,
name varchar(128),
sex char(1)
) CHARSET=utf8;
(2)往表格里面插入一条数据:
insert into demo(id,name,sex) values(1,'小明','男');
mysql> insert into demo(id,name,sex) values(1,'小明','男');
Query OK, 1 row affected (0.02 sec)
(3)查询一个表格数据:
mysql> select * from demo;
+------+--------+------+
| id | name | sex |
+------+--------+------+
| 1 | 小明 | 男 |
| 2 | 小刚 | 男 |
+------+--------+------+
2 rows in set (0.00 sec)
(4)更新一条数据:
mysql> update demo set sex='女' where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from demo;
+------+--------+------+
| id | name | sex |
+------+--------+------+
| 1 | 小明 | 女 |
| 2 | 小刚 | 男 |
+------+--------+------+
2 rows in set (0.00 sec)
(5)删除一条数据:
mysql> delete from demo where id=2;
Query OK, 1 row affected (0.10 sec)
mysql> select * from demo;
+------+--------+------+
| id | name | sex |
+------+--------+------+
| 1 | 小明 | 女 |
+------+--------+------+
1 row in set (0.00 sec)
排序: order by xxx desc|asc
只取多少行数据: limit n(取前n行数据)
select * from userinfo limit 2;
聚合语法: select 列1,列2...,sum(memory) from userinfo group by 列1,列2... having sum(memory) >30000
聚合函数:
count() 求数量
sum() 求和
avg() 求平均
删表:
drop table 表名;
eg:
create table salary(
id int,
username varchar(124),
dept varchar(124),
sal double,
cretime timestamp
) CHARSET=utf8;
insert into salary(id,username,dept,sal) values(1,'小明1','技术1',3000.99);
insert into salary(id,username,dept,sal) values(2,'小明2','技术2',4000.99);
insert into salary(id,username,dept,sal) values(3,'小明3','技术3',2000.99);
insert into salary(id,username,dept,sal) values(4,'小明4','技术1',1000.99);
insert into salary(id,username,dept,sal) values(5,'小明5','技术2',5000.99);
--求每个部门多少人?
mysql> select dept,count(*) from salary group by dept;
+---------+----------+
| dept | count(*) |
+---------+----------+
| 技术1 | 2 |
| 技术2 | 2 |
| 技术3 | 1 |
+---------+----------+
3 rows in set (0.00 sec)
--求各个部
mysql> select dept,sum(sal) from salary group by dept;
+---------+--------------------+
| dept | sum(sal) |
+---------+--------------------+
| 技术1 | 4001.9799999999996 |
| 技术2 | 9001.98 |
| 技术3 | 2000.99 |
+---------+--------------------+
3 rows in set (0.00 sec)门的总薪水?
--求总薪水在5000千以上部门?
mysql> select dept,sum(sal) from salary group by dept having sum(sal)>5000;
+---------+----------+
| dept | sum(sal) |
+---------+----------+
| 技术2 | 9001.98 |
+---------+----------+
1 row in set (0.00 sec)
字段别名: as xxx
子查询:
mysql> select * from (select dept,sum(sal) as sum_sal from salary group by dept) t where t.sum_sal>5000;
+---------+---------+
| dept | sum_sal |
+---------+---------+
| 技术2 | 9001.98 |
+---------+---------+
1 row in set (0.00 sec)
两张表怎样关联:
A left join B on A.字段=B.字段 A表数据最全<--B表补全
A right join B on A.字段=B.字段 A表补全--> B表数据最全
A inner join B on A.字段=B.字段 慎用
select a.*,b.deptno,b.dname from emp a left join dept b on a.deptno=b.deptno ;
A表数据最全--》
只有10、20、30、50 这四个部门编码
B表的数据:10、20、30、40 这四个部门编码
select a.*,b.deptno,b.dname from emp a right join dept b on a.deptno=b.deptno ;
B表数据最全--》
B表的数据:10、20、30、40 这四个部门编码
A只有10、20、30、50 这四个部门编码
注意点:
只要满足on条件,有几行算几行
怎么查看MySQL的登陆账户和密码:
mysql> use mysql
mysql> select user,password,host from user;
+------+----------+-----------+
| user | password | host |
+------+----------+-----------+
| root | | localhost |
| root | | xkhadoop |
| root | | 127.0.0.1 |
| root | | ::1 |
| | | localhost |
| | | xkhadoop |
+------+----------+-----------+
6 rows in set (0.00 sec)
Linux进入到MySQL:
mysql -u+用户名 -p+密码(可以不输入)
eg:
[mysqladmin@xkhadoop ~]$ mysql -uroot -p (-h(IP))
Enter password:
四.创建DB、USER
create database jepsondb;
grant all privileges on (db名).* to (用户名)@'(IP)%' identified by '(密码)';(把某个db付给某个账号)
eg:
grant all privileges on jepsondb.* to jepsonuser@'%' identified by '123456';(把某个db付给某个账号)
flush privileges;(生效命令)
注意点: 只要涉及权限修改,必须执行flush privileges;
补充:
1.登录 mysql -uroot -p123456 -h127.0.0.1
mysql -u root -p123456 -h 127.0.0.1 --> ok
mysql -u root -p 123456 -h 127.0.0.1 -->错误的 -p要不后面什么不跟,直接回车输入密码;要不紧跟密码
2.dbeaver(企业)
配置hostname -i可以查看本机ip: vi /etc/hosts
[root@xkhadoop001 ~]# hostname -i
hostname: Unknown host
[root@xkhadoop001 ~]# vi /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.40.130 xkhadoop001
~
"/etc/hosts" 4L, 186C written
[root@xkhadoop001 ~]# hostname -i
192.168.40.130
五.Mysql中的一些基本语法: