电脑装配网

命令太多记不住?MySQL数据库常用命令大全

 人阅读 | 作者xiaolin | 时间:2024-03-01 19:47

1https://www.dnzp.com/uploads/allimg/240301/hadatuzzwlg>

库操作https://www.dnzp.com/uploads/allimg/240301/hadatuzzwlg> 创建数据库:create database shujuku创建带字符集的数据库:create database mydb2 CHARACTER SET=UTF8创建带校验的数据库:create database mydb3 CHARACTER SET=UTF8 COLLATE utf8_general_ci显示数据库:show databases删除数据库:drop database shujuku修改数据库编码:alter databaese shujuku character set gb2312

2https://www.dnzp.com/uploads/allimg/240301/hadatuzzwlg>

表操作https://www.dnzp.com/uploads/allimg/240301/hadatuzzwlg>

创建数据库表(创建一个表名为:employee,该表中含有id、name、sex、birthday、job字段);https://www.dnzp.com/uploads/allimg/240301/hadatuzzwlg>

create table employee(id int,name varchar(40),sex char(4),birthday date,job varchar(40),);https://www.dnzp.com/uploads/allimg/240301/hadatuzzwlg> 表中增加image字段:alter table employee add image blob修改job值,使其长度为60(原长度为1000):alter table employee modify job varchar(60)删除sex列:alter table employee drop sex;表名改为user(原名为employee):rename table employee to user修改表的字符集为utf_8:alter table user character set utf8列name修改为 username:alter table change columm name username varchar(100)删除表:drop table user

3https://www.dnzp.com/uploads/allimg/240301/hadatuzzwlg>

增删改查实例https://www.dnzp.com/uploads/allimg/240301/hadatuzzwlg>

准备表:https://www.dnzp.com/uploads/allimg/240301/hadatuzzwlg>

create table employee(id int,name varchar(40),sex varchar(4),birthday date,entry_date date,salary decimal(8,2),resume text);https://www.dnzp.com/uploads/allimg/240301/hadatuzzwlg>

插入表数据:https://www.dnzp.com/uploads/allimg/240301/hadatuzzwlg>

insert into employee(id ,name,sex,birthday,entry_date,salary,resume) values(1,'zhangsan','male','1999-08-22','2020-08-22,'1000','i am a developer');https://www.dnzp.com/uploads/allimg/240301/hadatuzzwlg> 指定某些列插入数据:insert into employee(id) values(6)插入汉字:insert into employee(id,name) values(6,‘张三’)

4https://www.dnzp.com/uploads/allimg/240301/hadatuzzwlg>

修改表数据https://www.dnzp.com/uploads/allimg/240301/hadatuzzwlg> 将所有员工薪水修改为5000元:update employee set salary=5000将姓名为’zs’的员工薪水修改为3000元:update employee set salary = 3000 where name=‘zhangsan’将姓名为’aaa’的员工薪水修改为4000元,job改为ccc:update employee set salary = 4000,job=‘ccc’ where name=‘张三’将wu的薪水在原有基础上增加1000元:update employee set salary = salary+1000 where name=‘张三’

5https://www.dnzp.com/uploads/allimg/240301/hadatuzzwlg>

删除表数据https://www.dnzp.com/uploads/allimg/240301/hadatuzzwlg> 删除表中名称为“zs”的记录:delete from employee where job=‘ccc’删除表中所有记录:delete from employee使用truncate删除表中记录:truncate table employee

6https://www.dnzp.com/uploads/allimg/240301/hadatuzzwlg>

查询表数据https://www.dnzp.com/uploads/allimg/240301/hadatuzzwlg> 查询表中所有学生的信息:select id,name,chinese,english,math from student查询表中所有学生的姓名和对应的英语成绩:select name,english from student查询姓名为wu的学生成绩:select * from student where name=‘张三’查询英语成绩大于90分的同学:select * from student where english>‘90’查询英语分数在 80-90之间的同学:select * from student where english>=80 and english=<90

7https://www.dnzp.com/uploads/allimg/240301/hadatuzzwlg>

常见MySQL语句命令https://www.dnzp.com/uploads/allimg/240301/hadatuzzwlg> 进入mysql 命令行:mysql -uroot -p查看所有数据库:show databases创建数据库:create database niu charset utf8删除数据库:drop database niu选择数据库:use databases查看所有表:show tables查看创建数据库的语句:show create database databasename查看创建表的语句:show create table tablename查看表结构:desc tablenmae

8https://www.dnzp.com/uploads/allimg/240301/hadatuzzwlg>

常见MySQL字段含义https://www.dnzp.com/uploads/allimg/240301/hadatuzzwlg> 自增长:auto_increment非空:not null默认值:default唯一:unique指定字符集:charset主键:primary key荐:【中国风动漫】除了《哪吒》,这些良心国产动画也应该被更多人知道!【中国风动漫】《雾山五行》大火,却很少人知道它的前身《岁城璃心》一个拿着十米大刀的男主夭折!声明来源:博客园,人工智能产业链联盟推荐阅读,不代表人工智能产业链联盟立场,转载请注明,如涉及作品版权问题,请联系我们删除或做相关处理!


文章标签:

本文链接:『转载请注明出处』