笔记-mysql常用sql语句

mysql常用sql语句

create database test01; # 创建数据库

show databases; # 查询所有数据库

use test01; # 切换到test01数据库；

create table dadong2023(
id int(100) primary key auto_increment,
name VARCHAR(15),
age varchar(15)
)charset=utf8;; # 创建表格dadong2023 列名为id,name,charset=utf8代表支持中文；

drop table dadong2023; #删除表

UPDATE dadong2023 set name=‘rd’ where name=‘tt’ LIMIT 2; # 修改前两行数据name=tt,改为name=rd;

insert into dadong2023(name,age) value(‘dadong’,‘20’); # dadong2023表中插入数据name=dadong,age=20;

insert into dadong2023(name) SELECT name from dadong2023 WHERE name=‘dadong’;
##将查询到的结果插入表中,相当于复制了一编查询结果，查询到10个即插入10个；

delete from dadong2023 where name=‘dadong’; #删除dadong2023表中所有name='dadong’的数据;

delete from dadong2023 where name=‘22’;

#查询重复值列名；
select distinct name,age from dadong2023;

SELECT name as names from dadong2023;

select concat(name,“34444”,age) from dadong2023;

use test01;

create table dadong2024(
id int(100) primary key auto_increment,
name VARCHAR(15),
age int(15),
height int(15)
)charset=utf8;

drop table dadong2024;

use test01;

INSERT into dadong2024(name,age) value(‘大哥3’,‘90’);

SELECT concat(name,‘对应的年龄是：’,age) from dadong2024; # 查询插上备注；

SELECT * from dadong2024 WHERE name LIKE ‘%大哥’; #查找name='大哥’的数据

SELECT * from dadong2024 WHERE name LIKE ‘%测试%’; #查找包含name='大哥’的数据

delimiter//
create procedure test_10()
BEGIN
declare i int default 1;
while (i<10) DO
insert into dadong2024(name,age,height) value(CONCAT(‘测试’,i),CONCAT(1,i),CONCAT(15,i));
set i = i+1;
end while;
end//
delimiter;

call test_10() # 插入十行数据、

drop procedure test_10; # 删除test_10

SELECT GROUP_CONCAT(height) from dadong2024 GROUP BY age HAVING age=‘11’; 3# 查询除了分组条件之外的值，
##查询height的值，条件为age=11，返回某个值

SELECT * from dadong2024 GROUP BY age HAVING age=‘11’; # 查询age=11的行

#sum,count,max,MIN,avg

SELECT avg(age) from dadong2024;