SQL命令和SQL语句

Mysql客户端命令

q:exit   退出

所有的SQL语句都是以分号结束的.

c:clear   中断不想执行的SQL语句

G:格式化显示查询的结果(key:value)

T:tee  记录日志

u:use 切换数据库

h:help 查看帮助信息

s:status  查看当前库详细信息

 .:source 导入SQL文件中的数据

Mysql常用命令行

#登录mysql:(#注意不要把密码写到命令行)

mysql -uroot -p

-S  指定socket文件位置	
mysql -uroot -p -S /application/mysql/tmp/mysql.sock 

注:
如果是编译安装的mysql,可以省略-S 
如果其他方式呢,加上-S 

-h  指定链接的地址
mysql -uroot -p -h 10.0.0.51 

-P  指定链接的端口号

mysql -uroot -p -h 10.0.0.51  -P 3307

-e  免交互式执行mysql内部命令

mysql -uroot -p -e "select user,host,password from mysql.user;"

<  导入SQL脚本到mysql中

mysql -uroot -p </root/world.sql

mysqladmin命令

1.修改密码,设置密码:password
mysqladmin -uroot -p旧密码 password '新密码'

2.关闭MySQL服务:shutdown
mysqladmin -uroot -p密码 -S socket文件 shutdown

3.库外建库:create
mysqladmin -uroot -p密码 create zls

mysql -uroot -p123 -e 'create database zls2'

4.库外删除数据库:drop
[root@db01 ~]# mysqladmin -uroot -p123 drop zls1
Do you really want to drop the 'zls1' database [y/N] y
Database "zls1" dropped

5.查看配置文件所有的默认参数:variables
mysqladmin -uroot -p123 variables

6.检测MySQL进程是否存活:ping
mysqladmin -uroot -p123 ping

7.查看数据库 慢查询,负载信息:status
mysqladmin -uroot -p123 status

8.重载授权表,刷新缓存主机:reload
mysqladmin -uroot -p123 reload

9.刷新binlog日志
mysqladmin -uroot -p123 flush-log

SQL语句:

​ DDL:数据定义语言

​ 库对象:库名字,库属性

​ 开发规范:库名小写

1.针对库的操作:

建库:

create database bgx;
create schema long;
create database if not exists long;
create database if not exists huan collate utf8_general_ci charset utf8;

删库:

drop database long;

修改库:

alter database huan charset utf8;

2.针对表的操作:

###### 建表:

	create table huan(id int);
	create table huan2( id int, name varchar(10), gender enum('m','f','oldtian'));
		

	create table huan( 
	id int, 
	name varchar(10), 
	age tinyint, 
	gender enum('f','m'), 
	cometime datetime);

表名:student

		sid
		sname
		sage
		sgender
		scometime

​

	数据类型
	整型:
	tinyint(m)	 最小整数 -128 ~ 127
	smallint(m)  2个字节  范围(-32768~32767)
	mediumint(m) 3个字节  范围(-8388608~8388607)
	int(m) 		 4个字节  整数 -2^31 ~ 2^31 -1
	bigint(m)    8个字节  范围(+-9.22*10的18次方)
	
	字符串:
	varchar：	字符类型 （变长）
	char： 	 	字符类型 （定长）
	enum：   	枚举类型
	浮点型:
	float(m,d):  单精度浮点数  8位精度(4字节)  m总个数，d小数位
	double(m,d): 双精度浮点型  16位精度(8字节) m总个数，d小数位
	日期时间类型:
	date:        日期'2019-12-12'
	time:        时间'12:30:30'
	datetime：   时间类型 年月日时分秒
	timestamp:   自动存储记录修改时间

数据属性:

	    null:                空(数据列可包含null值)
	    not null： 			非空
		primary key： 		主键（唯一且非空的）
		auto_increment： 	自增（此列必须是：primary key或者unique key）
		unique key： 		单独的唯一的
		default： 			默认值
		unsigned： 			无符号,非负数
		character set name   指定一个字符集
		comment： 			注释

修改表:

	添加字段:
#添加字段(默认添加到最后)
alter table student add 字段 varchar(10);
#在表头添加字段
alter table student add 字段 varchar(10) first;
#在指定字段之后添加字段
alter table student add 字段 varchar(10) after 字段;
#添加多个字段
alter table student add xiejun varchar(10),add lixin varchar(20);
#删除字段
alter table student drop xiejun;		
#修改字段的数据类型:(只能支持单个修改,不支持多个)
alter table student modify 字段 int;			
#修改字段名:
alter table student change lixin xiewang int;			
#修改字段名和数据类型
alter table student change xiewang lixin char(10);			
#修改表名
alter table student rename stu;

DCL:数据控制语言 control

grant

grant:
			grant all on *.* to root@'%' identified by '123';
			max_queries_per_hour：一个用户每小时可发出的查询数量
			max_updates_per_hour：一个用户每小时可发出的更新数量
			max_connections_per_hour：一个用户每小时可连接到服务器的次数
			max_user_connections：允许同时连接数量
revoke:
		revoke select on *.* from root@'%';

DML:数据操作语言

grant insert,update,delete,select

insert

insert:插入数据
	#不规范写法
mysql> insert into huan values(1,'wu',18,'f',now());
	
	#规范写法,插入一条数据
mysql> insert into huan(name,age,gender) values('ding',28,'m');
	
	#规范写法,插入多条数据
mysql> insert into huan(name,age,gender) values('long',18,'f'),('liu',84,'f');

update

update:修改数据
	#危险,整列全都修改成f
	update test.huan set gender='f';
	
	注意:使用update时,必须要接条件(where)
	update test.huan set gender='m' where id=7;
	
	#就是修改整列内容
	update test.huan set name='boy' where 1=1;

delete

delete:删除数据
#危险,删除整张表的数据
delete from test.huan;
注意:使用delete时,必须接条件(where)
delete from test.huan where id=13;

#就是修改整表内容
delete from test.huan where 1=1;

删除列(字段):
alter table test.huan drop name;
使用update代替delete做伪删除:
	1.添加一个状态列(一段),表示该行数据的状态
	alter table test.huan add status enum('0','1') default '1';
	
	2.如何删除数据?
	update test.huan set status='0' where id=9;
	
	3.如何查询数据?
	select * from test.huan where status='1';

DQL:数据查询语言

select:查询数据(awk)

    #查看表中所有内容(危险)
	select * from test.huan;
	
	#如果要查询所有数据的总量,使用count
	select count(*) from test.huan;
	
	#查看某几列中的内容
	select gender,age from test.huan;

	#查询,接多条件
	select gender,age from test.huan where age=18 and gender='f';
	
	mysql> use world

	mysql> show tables;
	+-----------------+
	| Tables_in_world |
	+-----------------+
	| city            |		城市
	| country         |		国家
	| countrylanguage |		国家语言
	+-----------------+
	#查看city表中所有的内容
	select * from city;
	#查询city表中的字段(表结构)
	desc city;

​
​ order by :排序
​ #按照人口数量排序(升序)
​ select * from city order by population;
​

	#按照人口数量排序(降序)
	select * from city order by population desc;

​
​ #按照人口数量排序,前十的(limit)
​ select * from city order by population limit 10;
​

	#按照步长60查找数据(翻页)
	select * from world.city limit 60,60
	
	where接条件: > < = , >= <= (!= <>  不等于),like ,and,or
	
	=:精确查询
	> < = , >= <= != :范围查询
	like:模糊查询
	
	#模糊查询
	select * from world.city where countrycode like '%H%';

	#or
	select * from world.city where countrycode='CHN' or countrycode='USA';
	
	#in
	select * from world.city where countrycode in ('CHN','USA');
											   not in
	#union all (联合查询)
	select * from world.city where countrycode='USA' union all select * from world.city where countrycode='CHN';

#select高级用法，多表联查
查看人口超过100万人口的城市，国家名国家代码，国土面积

mysql> select city.name,city.countrycode,country.name,country.SurfaceArea
    -> from city join country
    -> on city.countrycode=country.code
    -> where city.population > 1000000
    order by city.population
    desc limit 10,10;
 #传统方式多表连查
    select city.name,city.population,country.name 
    from city,country 
    where city.countrycode=country.code 
    and city.population 1000000;
    ORDER BY population;
 
#select 内连接（用的最多，最好用）
#查询龙在曾的课程里获得的分数
select student.sno,student.sname,student.sage,student.class,
teacher.depart,teacher.prof,
score.mark,teacher.tname,
course.cname
from score  
join student on score.sno=student.sno
join course on score.cno=course.cno
join teacher on course.tno=teacher.tno
where student.sname='龙'
and teacher.tname='曾';
 
#union all (联合查询),速度更快 union all 
        select * from world.city where countrycode='USA' union all select * from world.city where countrycode='CHN';

聚合函数：GROUP BY + 聚合函数 (COUNT(),MAX(),MIN(),AVG(),SUM())

(1) COUNT(),统计数量
  统计city表中城市的个数.
  SELECT COUNT(*) FROM city;
  使用* 是直接查找主键，因为count是直接查主键的
 (2)统计一下各个国家的城市个数     （原理是以国家的名字来分，一个相同的国家名就+1）
 （还有一种想法是，数个数，所以使用count（name），然后什么什么的，加上group by，就是形容词就是放在group by 后面）
 mysql> select countrycode,count(name) as a from city group by countrycode order by a desc limit 10;
+-------------+-----+
| countrycode | a   |
+-------------+-----+
| CHN         | 363 |
| IND         | 341 |
| USA         | 274 |
| BRA         | 250 |
| JPN         | 248 |
| RUS         | 189 |
| MEX         | 173 |
| PHL         | 136 |
| DEU         |  93 |
| IDN         |  85 |
+-------------+-----+
 
 (3)统计一下中国 , 各省的 人口 总和(SUM()).
 SELECT district, SUM(population) 
 FROM city  
 WHERE countrycode='chn' 
 GROUP BY district;
 
 mysql> select District,sum(population) from city  where countrycode='chn' group by district; ####group by 一定要放在最后面
 
 (4)统计 每个国家的人口总数
 SELECT countrycode,SUM(population) 
 FROM city
 GROUP BY countrycode;

外链接

#左连接
mysql> select teacher.tname,course.cname 
    -> from teacher 
    -> left join course 
    -> on teacher.tno=course.tno
    -> and teacher.tname='曾';
+-------+-----------+
| tname | cname     |
+-------+-----------+
| 曾    | 数据库    |
| 田    | NULL      |
| 徐    | NULL      |
+-------+-----------+
3 rows in set (0.00 sec)
 
#右连接
mysql> select teacher.tname,course.cname 
    -> from teacher 
    -> right join course 
    -> on teacher.tno=course.tno
    -> and teacher.tname='曾';
+-------+-----------+
| tname | cname     |
+-------+-----------+
| 曾    | 数据库    |
| NULL  | 架构      |
| NULL  | 计算机    |
+-------+-----------+
 

子查询（不推荐，效率太低）

mysql> select name from country where code=(select countrycode from city where population<100);
+----------+
| name     |
+----------+
| Pitcairn |
+----------+
1 row in set (0.00 sec
{1}
我们可以用多表联查来搞定
mysql> select c.name
from country as c 
join city as t 
on c.code=t.countrycode
where t.population < 100 ;
+----------+
| name     |
+----------+
| Pitcairn |
+----------+
1 row in set (0.01 sec)
 

视图

一个视图就是一个查询方法
create view test as
sql语句

然后形成一个视图
下次可以直接查看 select * from test (test就是一个视图了，可以看成一张正常的表)