sql语句大全及实例

连续复制

一键复制

一键打包

一、 insert into语句

insert into 表名称 values ('val1','val2','val3',...)
insert into Person values ('Bill','22','male',...)

insert into 表名称(列1,列2,列3,...) values ('val1','val2','val3',...)
insert into Persons (LastName, Address) values ('Wilson', 'Champs-Elysees')

将数据库表拷贝到另一个表中

insert into table2 select * from table1

二、update 语句

update 表名称 set 字段名=新值 where 列表名=某值
update Person set LastName='andy',age=33 where Address='Champs-Elysees'

三、delete 语句

delete from 表名称 where 列表名=某值

四、select 语句

select 列名称 from 表名称

1. AND & OR

把两个或多个条件语句结合起来

select * from Person where sex='male' and (age=22 or name='andy')

2. ORDER BY

对指定结果集进行排序

select * from Person where sex='male' order by age ASC, name DESC

* 另一种用法

select * from Product where cid='0' order by case when price='0' then 1 when tag='free' then 2 when tag='recommand' then 3 else 4 end, times DESC, cid ASC

3. like

指定模式查询

select * from Person where city like '%changsha%' and male not like '%male%'
select * from Person where city like '%c' // 以c结尾的

4. in

select * from Person where LastName in ('adam','carter')

5. between

between... and 会选取介于两者之间的范围
select * from Person where LastName between 'adam' and 'carter'

6. 表和列的别名

表的别名：应用于多表查询

select a.city, a.age, b.salary from Person as a, User as b where a.cid=b.id

列的别名

select thetitle as title, id as appid from app

7. UNION ALL

合并两个或多个select语句的结果集,允许重复的值，必须拥有相同数量的列

SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2

8. alter

添加列

alter table table_name add column_name datatype

删除列

alter table table_name drop column column_name

改变列表数据类型

alter table table_name
alter column column_name datatype

改变自增id

alter table users AUTO_INCREMENT=起始值;

9. COUNT, AVG, MAX, MIN

select COUNT(*) as total from table
select AVG(points) as avarage from table

10. GROUP BY

select customer, SUM(OrderPrice) as price from Orders group by customer

11. CREATE INDEX

在不扫描数据库表的情况下，索引能更快地查询到数据

CREATE INDEX PersonIndex on Person (LastName DECS);

在数据量非常大的情况下，经常要用到某字段作为条件来查询的话，就可以给该字段添加索引，提高查询速度

五.链接查询

1.交叉连接（cross join）

语句1：隐式的交叉连接，没有cross join。

select o.id, o.order_number, c.id, c.name from orders o , customers c where o.id=1;

语句2：显式的交叉连接，使用cross join。

select o.id,o.order_number,c.id,c.name from orders o cross join customers c where o.id=1;

2.内连接（inner join）

语句3：隐式的内连接，没有inner join，形成的中间表为两个表的笛卡尔积。

select o.id,o.order_number,c.id,c.name from customers c, orders o where c.id=o.customer_id;

语句4：显示的内连接，一般称为内连接，有inner join，形成的中间表为两个表经过on条件过滤后的笛卡尔积。

select o.id,o.order_number,c.id,c.name from customers c inner join orders o onc.id=o.customer_id;

3.外连接（outer join）

语句5：左外连接（left outer join）

select o.id,o.order_number,o.customer_id,c.id,c.name from orders o left outer join customers c on c.id=o.customer_id;

语句6：右外连接（right outer join）

select o.id,o.order_number,o.customer_id,c.id,c.name from orders o right outer join customers c on c.id=o.customer_id;

left join 查询多条数据排序取一条

left join (select * from (select aunt_id,user_id,created_time,log_action from sys_aunt_log where log_action=${auntLog.delete.code} order by created_time desc) bb group by aunt_id ) b

语句7：where条件独立。

select o.id,o.order_number,o.customer_id,c.id,c.name from orders o left outer join customers c on c.id=o.customer_id where o.order_number<>'mike_order001';

语句8：将语句7中的where条件放到on后面。

select o.id,o.order_number,o.customer_id,c.id,c.name from orders o left outer join customers c on c.id=o.customer_id and o.order_number<>'mike_order001';

语句9：全外连接（full outer join）。

select o.id,o.order_number,o.customer_id,c.id,c.name from orders o full outer join customers c on c.id=o.customer_id;

语句10：左外和右外的合集，实际上查询结果和语句9是相同的。

select o.id,o.order_number,o.customer_id,c.id,c.name from orders o left outer join customers c on c.id=o.customer_id union selecto.id,o.order_number,o.customer_id,c.id,c.name from orders o right outer join customers c on c.id=o.customer_id;

4.联合连接（union join）

语句11：联合查询（union join）例句，还没有找到能执行的sql环境。

select o.id,o.order_number,o.customer_id,c.id,c.name from orders o union join customers c on c.id=o.customer_id

语句12：语句11在db2下的等价实现。还不知道db2是否支持语句11呢！

select o.id,o.order_number,o.customer_id,c.id,c.name from orders o full outer join customers c on c.id=o.customer_id except selecto.id,o.order_number,o.customer_id,c.id,c.name from orders o inner join customers c onc.id=o.customer_id;

语句13：语句11在oracle下的等价实现。

select o.id,o.order_number,o.customer_id,c.id,c.name from orders o full outer join customers c on c.id=o.customer_id minus selecto.id,o.order_number,o.customer_id,c.id,c.name from orders o inner join customers c onc.id=o.customer_id;

5.自然连接（natural inner join）

语句14：

select * from orders o natural inner join customers c;

语句15：

select * from orders o natural left outer join customers c;

语句16：

select * from orders o natural right outer join customers c;

语句17：

select * from orders o natural full outer join customers c;

6.连接查询总结

连接查询是sql查询的核心，连接查询的连接类型选择依据实际需求。如果选择不当，非但不能提高查询效率，反而会带来一些逻辑错误或者性能低下。下面总结一下两表连接查询选择方式的依据：

1、查两表关联列相等的数据用内连接。
2、 col_l是col_r的子集时用右外连接。
3、 col_r是col_l的子集时用左外连接。
4、 col_r和col_l彼此有交集但彼此互不为子集时候用全外。
5、求差操作的时候用联合查询。
多个表查询的时候，这些不同的连接类型可以写到一块。例如：

六.练习实例

数据库表 user

id	name	subject	score
1	张三	语文	80
2	张三	数学	60
3	李四	语文	70
4	王五	外语	90
5	张三	外语	70
6	李四	数学	59

mysql_cli创建表和数据

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `subject` varchar(255) DEFAULT NULL,
  `score` float DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `user`
-- ----------------------------
BEGIN;
INSERT INTO `user` VALUES ('1', '张三', '语文', '80'), ('2', '张三', '数学', '60'), ('3', '李四', '语文', '70'), ('4', '王五', '外语', '90'), ('5', '张三', '外语', '70'), ('6', '李四', '数学', '59');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

1.请找出每门课程都大于等于60分的那个人名字的SQL语句

select name  from `user` group by name having min(score)>=60;

解释：
先通过group by语句将表以name进行分组，结果类似如下：

①张三
    语文  80
    数学  60
    外语  70
②李四
    语文 70
    数学  59
③王五
    外语  90

然后having+聚组函数进行过滤处理，查询score最小>=60分的。

其他实现方式:

SQL1:
select name from user
group by name
having count(score) =sum(case  when score>=60 then 1 else 0 end )

SQL2:
select name from user
group by name
having name not in (
select name from user
where score <60)

2.查询这个班总共有多少个人？

select count(DISTINCT name) from user;

解释：
可以一同使用 DISTINCT 和 COUNT 关键词，来计算非重复结果的数目。

3.计算每个人的平均分，总分（分类汇总）?

select name,avg(score) avg,sum(score) sum from user  group by  name;

注意：在select指定的字段要么就要包含在Group By语句的后面，作为分组的依据；要么就要被包含在聚合函数中。

4.LIMIT 1会提高查询效率

在某些情况下，如果明知道查询结果只有一个，SQL语句中使用LIMIT 1会提高查询效率。

例如下面的用户表(主键id,邮箱,密码)：

create table t_user(
    id int primary key auto_increment,
    email varchar(255),
    password varchar(255)
);

每个用户的email是唯一的，如果用户使用email作为用户名登陆的话，就需要查询出email对应的一条记录。

SELECT * FROM t_user WHERE email=?;

上面的语句实现了查询email对应的一条用户信息，但是由于email这一列没有加索引，会导致全表扫描，效率会很低。

SELECT * FROM t_user WHERE email=? LIMIT 1;

加上LIMIT 1，只要找到了对应的一条记录，就不会继续向下扫描了，效率会大大提高。

LIMIT 1适用于查询结果为1条（也可能为0）会导致全表扫描的的SQL语句。

如果email是索引的话，就不需要加上LIMIT 1，如果是根据主键查询一条记录也不需要LIMIT 1，主键也是索引。

例如：

SELECT * FROM t_user WHERE id=?;

就不需要写成：

SELECT * FROM t_user WHERE id=? LIMIT 1;

二者效率没有区别。
附上我做的实验：

存储过程生成100万条数据：

delimiter $$
DROP PROCEDURE IF EXISTS batch_insert_user;
CREATE PROCEDURE batch_insert_user()
BEGIN
DECLARE i INT;
--START TRANSACTION;
SET i=0;
WHILE i<1000000 DO
 INSERT INTO t_user VALUES(NULL,CONCAT(i+1,'@xxg.com'),i+1);
 SET i=i+1;
END WHILE;
--COMMIT;
END $$
delimiter ;
call batch_insert_user();

查询语句

SELECT * FROM t_user WHERE email='222@xxg.com'; 耗时0.56 s
SELECT * FROM t_user WHERE email='222@xxg.com' LIMIT 1; 耗时0.00 s

小结：

1.LIMIT 1适用于查询结果为1条（也可能为0）会导致全表扫描的的SQL语句。
2.如果查询字段建立了索引，不需要使用limit 1。
mysql的delete删除不能有别名
比如：

delete from   users   u    where   u.id=1;

这样就执行不成功。
改成

delete   from users   where   id=1;

即可。

修改表更新时间

alter table xxxx_table
    modify column update_time DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'UpdateTime';

批量插入

try {
    $pdo = new pdo('mysql:host=localhost;dbname=w8scan_p_com','root','root');;
    for ($i=0; $i <300000 ; $i++) { 
        $query = "insert into user values($i,'xiaoyin{$i}',$i,'username{$i}','password{$i}')";
        $pdo->query($query);
    }
    
} catch (Exception $e) {
    print "Error!: " . $e->getMessage() . "<br/>";
    die();
}

设置root远程访问

update user set host='%' where user='root';
flush privileges;

mysql 0-5.5 修改密码

use mysql;
update user set password=PASSWORD("12345678") where User='root';
FLUSH PRIVILEGES;

mysql 5.5-5.7

use mysql; 
update user set authentication_string='' where user='root'
ALTER user 'root'@'localhost' IDENTIFIED BY '密码'
FLUSH PRIVILEGES;

mysql 8.0 修改密码

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '12345678';
FLUSH PRIVILEGES;

一、创建mysql数据库

1.创建数据库语法

--创建名称为“testdb”数据库，并设定编码集为utf8
CREATE DATABASE IF NOT EXISTS testdb DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

二、创建用户

1.新建用户

 --创建了一个名为：test 密码为：1234 的用户
 create user 'test'@'localhost' identified by '1234';

注意：
此处的"localhost"，是指该用户只能在本地登录，不能在另外一台机器上远程登录。如果想远程登录的话，将"localhost"改为"%"，表示在任何一台电脑上都可以登录。也可以指定某台机器可以远程登录。

2.查询用户

--查询用户
select user,host from mysql.user;

3.删除用户

--删除用户“test”
drop user test@localhost ;
--若创建的用户允许任何电脑登陆，删除用户如下
drop user test@'%';

4.更改密码

--方法1，密码实时更新；修改用户“test”的密码为“1122”
set password for test =password('1122');
--方法2，需要刷新；修改用户“test”的密码为“1234”
update  mysql.user set  password=password('1234')  where user='test'
--刷新
flush privileges;

5.用户分配权限

--授予用户test通过外网IP对数据库“testdb”的全部权限
grant all privileges on 'testdb'.* to 'test'@'%' identified by '1234';  

--刷新权限
flush privileges; 

--授予用户“test”通过外网IP对于该数据库“testdb”中表的创建、修改、删除权限,以及表数据的增删查改权限
grant create,alter,drop,select,insert,update,delete on testdb.* to test@'%';

6.查看用户权限

--查看用户“test”
show grants for test;

注意：修改完权限以后一定要刷新服务，或者重启服务，刷新服务用：flush privileges;

三、创建用户关联数据库

5.5 以上版本

CREATE DATABASE IF NOT EXISTS dev_catchaadmin_com DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;
create user 'dev_catchaadmin_com'@'localhost' identified by '123456';
grant all privileges on dev_catchaadmin_com.* to 'dev_catchaadmin_com'@'localhost' identified by '123456';
flush privileges;

8.0 以上版本

CREATE DATABASE IF NOT EXISTS dev_catchaadmin_com DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;
create user 'dev_catchaadmin_com'@'localhost' identified by '123456';
grant all privileges on dev_catchaadmin_com.* to 'dev_catchaadmin_com'@'localhost';
flush privileges;

删除用户和数据库

drop user dev_catchaadmin_com@localhost;
drop DATABASE dev_catchaadmin_com;

四、上亿大表建索引

select * from sgk into outfile 'sgk.txt'
load data infile 'D:/BtSoft/WebSoft/mysql/MySQL5.5/data/sgk/sgk.txt' into table sgk2;

五、xls转sql语句

=CONCATENATE("insert into sgk.sgk2(name1,pass,email,salt,other,site)  values ('",A1,"','",B1,"','",C1,"','",D1,"','",E1,"','",F1,"');")

=CONCATENATE("insert into sgk.sgk2(name1,pass,email,other,site)  values ('",A1,"','",B1,"','",C1,"','",D1,"','",E1,"');")

=CONCATENATE("insert into sgk.sgk2(name1,pass,email,site)  values ('",A1,"','",B1,"','",C1,"','",D1,"');")

客户、信合删数据

DELETE FROM dede_addonarticle WHERE aid >19487 ;
DELETE FROM dede_arctiny WHERE id > 19487;
DELETE FROM dede_archives WHERE id > 19487;
DELETE FROM dede_addon19 WHERE id > 19487;

mysql 高并发 Cannot assign requested address

vi /etc/sysctl.conf
net.ipv4.tcp_fin_timeout = 30
net.ipv4.ip_local_port_range = 10000 65000
sysctl -p

mysql io过高

# my.ini 配置
sync_binlog = 500
innodb_flush_log_at_trx_commit = 2
# sql执行
set global sync_binlog= 500;
set global innodb_flush_log_at_trx_commit = 2;

mysqldump 不锁表操作

/www/server/mysql/bin/mysqldump -R -E --triggers=false --single-transaction --quick --default-character-set=utf8 --force --opt "api_wzrdwl2_com"  -u root -p12345678 | gzip > /home2/backup/database/api_wzrdwl2_com_20230319_142603.sql.gz

windows 注册mysql服务

mysqld install mysql5.5 --defaults-file=G:\mysql-5.6.32-winx64\my-default.ini