MySQL数据库

最新推荐文章于 2024-08-26 16:32:13 发布

m0_74730416

最新推荐文章于 2024-08-26 16:32:13 发布

阅读量26

点赞数

文章标签：数据库 mysql

本文链接：https://blog.csdn.net/m0_74730416/article/details/134779069

版权

是关系型数据库管理系统，文件不适合管理系统

mysql注释用--

--登录mysql
mysql -u root -p

--查看所有数据库
show databases;

--使用数据库
--唯一可以不用分号
use mysql

--查看所有表
show tables;

--查看所有用户
select user from user;

--创建用户    指定哪台主机使用
create user "test"@"localhost" identified by "123456";

--修改密码
set password for "test"@"localhost" = "test";

--授权操作   所有数据库的所有表的select权限
grant select on *.* to "test"@"localhost";

--创建数据库
create database mydatabase;

--创建表
create table int_test(
tiny_int tinyint,
small_int smallint,
mudium_int mediumint,
common_int int,
big_int bigint
);

--显示表的内容
describe int_test;

--插入数据
insert into int_test values(1,1,1,1,1);

--创建学生表
//插入数据不为空
create table student(
name varchar(10) not null,        
age tinyint unsigned not null,
sex char(6)
);

--修改表
alter table student rename stu;
alter table student drop sex;
alter table student add sex char(6);
alter table student add id int unsigned not null primary key auto_increment first;     --主键不能重复  逐渐递增
alter table student add birth date after name;
alter table student add tel char(11) default '-';    //没写默认用横杠填充

--插入数据
insert into student (id,name,birth,age,sex,tel) values(1000,"jack","1998-8-9",21,"male","11111111111");
insert into student (name,birth,age,sex,tel) values("tom","1999-8-9",22,"male","11111222222");
insert into student values(1002,"rose","1997-8-9",22,"female","33333333333"),(1003,"lily","1997-9-9",23,"male","77777777777");
insert into student (id,name,birth,age,sex) values(1004,"david","1996-6-6",24,"male");
insert into student values (1005,"张三","1997-1-1",27,"male","12345678987");

--查询所有数据
select * from student;
select name from student;
select name from student where id > 1002;
select name from student where id between 1001 and 1004;

--修改字符集(可以输入中文)
alter table student convert to character set utf8;

--修改数据
update student set tel = "00000000000" where name = "david";

--删除数据
delete from student where id = 1000;

--删除表
drop table student;

--删除数据库
drop database mydatabase;

数据类型

mysql数据类型	含义（有符号）
tinyint(m)	1个字节范围（-128~127）
smallint(m)	2个字节范围（-32768~32767）
mediumint(m)	3个字节范围（-8388608~8388608）
int(m)	4个字节范围（-214783648~2147483647）
bigint(m)	8个字节范围（+-9.22*10的18次方）

初始化