爱玩君 - MySQL
type: channel
link: https://www.aiwanjun.com/tag/MySQL/
items: MySQL命令实例

MySQL命令实例
type: item
date: 2022-07-07T15:00:00+08:00
description: --创建一个mytest数据库 create database mytest; --删除一个mytest1数据库 drop database mytest1; --使用这个数据库 use mytest; --在这个数据库中创建一个websites表 create table websites( site_id INT NOT NULL AUTO_INCREMENT, site_name varchar(40) NOT NULL, site_url varchar(40) NOT NULL, site_count int, PRIMARY KEY(site_id) )ENGINE=InnoDB DEFAULT CHARSET=utf8; --删除一个websites1数据表 drop table websites1; --在websites表中插入数据 insert into websites (site_name,site_url,site_count) value ("百度","https://www.baidu.com",4956); --查询websites表中内容 select * from websites; --更新表中某行一列或几列的数据 update websites set site_name="百度一下",site_count="1704952" where site_id=1; --批量替换表中某列特定字符串 update websites set site_name=replace(site_name,'搜索','检索'); --删除表中某行数据 delete from websites where site_id=5; --通过LIKE子句进行模糊查找 select * from websites where site_url like '%og%'; --创建一个apps表并插入数据 --使用union子句连接多个select查询 select site_name as name,site_url as url from websites union select app_name,app_url from apps; --通过order by对查询的数据进行降序排序 select * from websites order by site_count DESC; --创建一个used表并插入数据 --使用group by子句对查询结果进行分组 select used_name as name,sum(used_times) as times from used group by used_name; --通过join语句联合多表查询(inner join,left join,right join) select u.used_id,w.site_name,w.site_url,u.used_date,u.used_times from used u join websites w on u.used_name=w.site_name; --创建一个allow_null表并插入数据 --通过is null或is not null进行空值处理 select * from allow_null where null_date is null; -- 通过REGEXP 操作符来进行正则表达式匹配 select * from websites where site_name regexp '搜'; select * from allow_null where null_things regexp '^我'; --mysql事务处理(begin、commit、rollback、savepoint、rollback to) begin; insert into allow_null (null_things,null_date) value ('我老婆也生孩子了','2022-07-05'); savepoint lp; --创建保存点lp insert into allow_null (null_things,null_date) value ('你们的老婆是同一个？','2022-07-05'); rollback to lp; --回滚到保存点lp commit; --通过alter修改数据表名或数据表字段 alter table websites add site_country varchar(10); --给数据表websites添加site_country字段 alter table allow_null rename a_null; --将数据表allow_null重命名为a_null --通过…