0%

'sql查询语法'

SELECT schema_name FROM information_schema.schemata;
查询所有数据库名

SELECT table_name FROM information_schema.tables WHERE table_schema=’information_schema’;
查询数据库information_schema的所有表名

SELECT column_name FROM information_schema.columns WHERE table_name=’TABLES’;
查询information_schema数据库下TABLES表的所有字段名

select title,author from text.content;
查询text数据库下content表中的所有title和author值

sql设置数据库语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
mysql获取某个数据库的所有表名
select TABLE_NAME from information_schema.tables
where TABLE_SCHEMA="your database name"

mysql获取某个表的所有字段名
select COLUMN_NAME from information_schema.COLUMNS
where table_name = 'your_table_name';

用户表
create table `users`(
`id` int(0) not null auto_increment,
`username` varchar(50) not null UNIQUE, //名字//UNIQUE设置不重复
`password` varchar(50) not null, //密码
`email` varchar(50) null, //邮箱
`nickname` varchar(50) not null, //昵称
`safepoint` varchar(50) null, //安全问题
`avatarimg` varchar(100) default '/images/default_avatar.jpg', //头像地址
`reg_time` timestamp default current_timestamp(), //创建时间
`lastlogin_time` timestamp default current_timestamp(), //最后登录时间
`lastlogin_ip` varchar(255) null, //最后登录ip
primary key(`id`)
);

INSERT INTO `mynews`.`tb_users`(
`username`, `password`,`nick_name`
) VALUES (
'zs','080687','mynews_12346'
);


新闻表
create table `tb_news`(
`id` int(0) not null auto_increment, //id
`title` varchar(255) not null, //标题
`contents` text not null, //内容
`author` varchar(50) not null, //作者
`new_img` varchar(100) null, //图片浏览图
`add_time` timestamp default current_timestamp(), //创建时间
`hot` int(0) default 0, //浏览量
primary key(`id`)
);

评论表
create table `tb_discuss`(
`id` int(0) not null auto_increment,
`new_id` int(0) not null, //新闻id
`discuss_content` text not null, //评论内容
`discuss_user` varchar(50) not null, //评论用户
`discuss_time` timestamp default current_timestamp(), //评论时间
primary key(`id`)
);


DELETE FROM `mynews` . `tb_users`;