SQL 语句

连接数据库：mysql -h10.20.66.32 -uroot -p123456

查看数据库：show databases;

使用数据库：use 数据库名；

查勘表：show tables;

查看表结构：desc 表名；

建表：create table t1();

删除表：drop table 表名；

修改表：
添加字段：alter table t1 add(字段名 类型 描述)；
移除字段：alter table t1 drop 字段名；
变更字段：alter table t1 change 旧字段名 新字段名 新字段描述；

插入：
全字段插入：insert into 表名 values(字段1值，字段2值,…),(字段1值，字段2值，…);
个别字段插入：insert into 表名(字段名) values(值1)(值2)；

普通查询：
单表全字段查询：select * from t1;
单表个别字段查询：select id from t1;
多表查询：select t1.id,t1.scroe,winton.name from t1,winton;

条件查询：
单表条件查询：select * from t1 where score>90;
多表条件查询：select t1.id,t1.score,winton.name from t1,winton where t1.id=winton.id;

嵌套查询：select name from winton where id=(select id from t1 where score=90);

并查询：(select id from t1)union(select id from winton);

交查询：select id from t1 where id in (select id from winton);

删除：delect from winton where id=4;

更新：update t1 set score=69 where id=2;
select * from t1 where name like ‘%银行账户%’ for update;

常用函数：
求和：select sum(score) from t1;
求平均值：select avg(score) from t1;
计数：select count(*) from t1;
求最大值：select max(name) from winton;
求最小值：select min(name) from winton;

常用修饰符：
Distinct字段中值唯一：select distinct name from winton;
Limit查询结果数限制：select * from winton limit 2;
Order by 排序：select * from winton order by name; 默认升序，降序在最后加desc。
Group by 分组：select name from winton group by name;

索引：
创建普通索引：create index wintonindex on winton (name);
创建唯一索引：create unique index wintonIndex on winton (id);
移除索引：drop index wintonIndex on winton;

Sql and or:
Select * from t1 where id=1 and score>90;
Select * from t1 where id =1 or score>90;

为列名称和表名称指定别名：as

用where连表查询：select Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons, Orders where Persons.Id_P = Orders.Id_P ;

用inner join 连表查询：
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons INNER JOIN Orders ON Persons.Id_P = Orders.Id_P ORDER BY Persons.LastName;

左外连接：
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons LEFT JOIN Orders ON Persons.Id_P=Orders.Id_P ORDER BY Persons.LastName;

右外连接：
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons RIGHT JOIN Orders ON Persons.Id_P=Orders.Id_P ORDER BY Persons.LastName;

全连接：
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons FULL JOIN Orders ON Persons.Id_P=Orders.Id_P ORDER BY Persons.LastName;

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
列出两个不同的：Select * from t1 union select * from t2;
列出所有：select * from t1 union all select * from t2;