Mysql记录事本
语法:
<字段名><类型><默认值>
实例:
MySQL [wordpress]> create table ly_content(
-> ID int auto_increment,
-> TITLE varchar(20),
-> CONTENT varchar(100),
-> WriteTime datetime default now(),
-> primary key(ID)
-> );
Query OK, 0 rows affected (0.02 sec)
向表中插入一个值看看效果:
[insert into ly_content (TITLE , CONTENT) values ("和平精英","出火箭少女101皮肤啦");]
1.添加时间戳字段
语法:
<表名><字段名><字段描述>
实例:
ALTER TABLE ly_contentadd ADD
COLUMN update_time timestamp NULLDEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
COMMENT '发布时间';
插入留言的发布时间,注意comment只是对该字段的描述,要与字段名区分
2.建表
语法:
create table <表名> ( 字段1 数据类型, 字段2 数据类型,字段3 数据类型 ...... <主键>);
实例1:
MySQL [newlyb]> create table users(
-> id int auto_increment,
-> username varchar(30),
-> password varchar(30),
-> sex varchar(10),
-> adress varchar(30),
-> email varchar(30),
-> register_time timestamp NULL DEFAULT CURRENT_TIMESTAMP,
-> primary key (id)
-> );
Query OK, 0 rows affected (0.02 sec)
插入值看看效果
insert into <TableName>(Column1,Column2....) values(" value1","value2",....);
实例2:
MySQL [newlyb]> create table message(
-> id int auto_increment,
-> username varchar(30),
-> sex varchar(10),
-> title varchar(50),
-> content varchar(300),
-> post_time timestamp NULL DEFAULT CURRENT_TIMESTAMP,
-> primary key(id)
-> );
Query OK, 0 rows affected (0.02 sec)
插入值看看效果
insert into message(username,sex,title,content) values ('1','1','1','1');
实例3:
MySQL [newlyb]> create table log(id int auto_increment,
-> username varchar(30),
-> ip varchar(30),
-> login_time timestamp NULL DEFAULT CURRENT_TIMESTAMP,
-> primary key(id)
-> );
Query OK, 0 rows affected (0.01 sec)
插入值看看效果
实例4:
MySQL [newlyb]> create table all_ip(
-> id int auto_increment,
-> username varchar(30),
-> ip varchar(30),
-> login_time timestamp NULL DEFAULT CURRENT_TIMESTAMP,
-> logout_time varchar(30),
-> primary key(id)
-> );
Query OK, 0 rows affected (0.01 sec)
插入值看看效果
3.插入int型字段默认为 0
语法:
ALTER TABLE <表名> ADD COLUMN <字段名> <值类型> DEFAULT <默认值>
实例:
MySQL [newlyb]> ALTER TABLE message ADD COLUMN love int(5) default 0;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
以插入信息表中love字段为例(代表留言的点赞量),初始默认值为0;效果如下。
4.向表中同时插入多个字段
语法:
ALTER TABLE <表名> ADD (<字段名> <值类型>,<字段名> <值类型>,<字段名> <值类型>...);
原表:
实例:
MySQL [newlyb]> alter table all_ip add(
-> status varchar(30)
-> default '登录中...');
Query OK, 0 rows affected (0.04 sec)
以向ip记录表all_ip中插入用户登录状态status字段为例,效果如下。
5.修改字段默认值
语法:
ALTER TABLE <表名> TABLE COLUMN <字段名> SET DEFAULT <默认值>
原表:
实例:
MySQL [newlyb]> alter table all_ip alter column status set default '在线...';
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
上图中status默认值为“登陆中...”我们修改为“在线...”,效果如下。
(djtest) j@ubuntu20:~/桌面/demo/mdshop/bookmanager$ pip install mysqlclient Collecting mysqlclient Using cached mysqlclient-2.1.0.tar.gz (87 kB) ERROR: Command errored out with exit status 1: command: /home/j/.virtualenvs/djtest/bin/python -c 'import sys, setuptools, tokenize; sys.argv[0] = '"'"'/tmp/pip-install-bacdtdo3/mysqlclient/setup.py'"'"'; __file__='"'"'/tmp/pip-install-bacdtdo3/mysqlclient/setup.py'"'"';f=getattr(tokenize, '"'"'open'"'"', open)(__file__);code=f.read().replace('"'"'\r\n'"'"', '"'"'\n'"'"');f.close();exec(compile(code, __file__, '"'"'exec'"'"'))' egg_info --egg-base /tmp/pip-install-bacdtdo3/mysqlclient/pip-egg-info cwd: /tmp/pip-install-bacdtdo3/mysqlclient/ Complete output (15 lines): /bin/sh: 1: mysql_config: not found /bin/sh: 1: mariadb_config: not found /bin/sh: 1: mysql_config: not found Traceback (most recent call last): File "<string>", line 1, in <module> File "/tmp/pip-install-bacdtdo3/mysqlclient/setup.py", line 15, in <module> metadata, options = get_config() File "/tmp/pip-install-bacdtdo3/mysqlclient/setup_posix.py", line 70, in get_config libs = mysql_config("libs") File "/tmp/pip-install-bacdtdo3/mysqlclient/setup_posix.py", line 31, in mysql_config raise OSError("{} not found".format(_mysql_config_path)) OSError: mysql_config not found mysql_config --version mariadb_config --version mysql_config --libs ---------------------------------------- ERROR: Command errored out with exit status 1: python setup.py egg_info Check the logs for full command output. (djtest) j@ubuntu20:~/桌面/demo/mdshop/bookmanager$ (djtest) j@ubuntu20:~/桌面/demo/mdshop/bookmanager$ (djtest) j@ubuntu20:~/桌面/demo/mdshop/bookmanager$ (djtest) j@ubuntu20:~/桌面/demo/mdshop/bookmanager$ (djtest) j@ubuntu20:~/桌面/demo/mdshop/bookmanager$ sudo apt-get install libmysqlclient-dev 正在读取软件包列表... 完成 正在分析软件包的依赖关系树 正在读取状态信息... 完成 将会同时安装下列软件: libssl-dev libssl1.1 建议安装: libssl-doc 下列【新】软件包将被安装: libmysqlclient-dev libssl-dev 下列软件包将被升级: libssl1.1 升级了 1 个软件包,新安装了 2 个软件包,要卸载 0 个软件包,有 83 个软件包未被升级。 需要下载 3,186 kB/4,507 kB 的归档。 解压缩后会消耗 18.1 MB 的额外空间。 您希望继续执行吗? [Y/n] y 获取:1 http://cn.archive.ubuntu.com/ubuntu focal-updates/main amd64 libssl-dev amd64 1.1.1f-1ubuntu2.9 [1,583 kB] 获取:2 http://cn.archive.ubuntu.com/ubuntu focal-updates/main amd64 libmysqlclient-dev amd64 8.0.27-0ubuntu0.20.04.1 [1,603 kB] 已下载 3,186 kB,耗时 3秒 (1,075 kB/s) debconf: 无法初始化前端界面:Dialog debconf: (对话框界面要求屏幕画面必须为至少 13 行高及 31 列宽.) debconf: 返回前端界面:Readline 正在预设定软件包 ... (正在读取数据库 ... 系统当前共安装有 180854 个文件和目录。) 准备解压 .../libssl1.1_1.1.1f-1ubuntu2.9_amd64.deb ... 正在解压 libssl1.1:amd64 (1.1.1f-1ubuntu2.9) 并覆盖 (1.1.1f-1ubuntu2.8) ... 正在选中未选择的软件包 libssl-dev:amd64。 准备解压 .../libssl-dev_1.1.1f-1ubuntu2.9_amd64.deb ... 正在解压 libssl-dev:amd64 (1.1.1f-1ubuntu2.9) ... 正在选中未选择的软件包 libmysqlclient-dev。 准备解压 .../libmysqlclient-dev_8.0.27-0ubuntu0.20.04.1_amd64.deb ... 正在解压 libmysqlclient-dev (8.0.27-0ubuntu0.20.04.1) ... 正在设置 libssl1.1:amd64 (1.1.1f-1ubuntu2.9) ... debconf: 无法初始化前端界面:Dialog debconf: (对话框界面要求屏幕画面必须为至少 13 行高及 31 列宽.) debconf: 返回前端界面:Readline 正在设置 libssl-dev:amd64 (1.1.1f-1ubuntu2.9) ... 正在设置 libmysqlclient-dev (8.0.27-0ubuntu0.20.04.1) ... 正在处理用于 man-db (2.9.1-1) 的触发器 ... 正在处理用于 libc-bin (2.31-0ubuntu9.2) 的触发器 ... (djtest) j@ubuntu20:~/桌面/demo/mdshop/bookmanager$ (djtest) j@ubuntu20:~/桌面/demo/mdshop/bookmanager$ (djtest) j@ubuntu20:~/桌面/demo/mdshop/bookmanager$ pip install mysqlclient Collecting mysqlclient Using cached mysqlclient-2.1.0.tar.gz (87 kB) Building wheels for collected packages: mysqlclient Building wheel for mysqlclient (setup.py) ... done Created wheel for mysqlclient: filename=mysqlclient-2.1.0-cp38-cp38-linux_x86_64.whl size=109124 sha256=f9d82bb00ab00f8143d9fbf579d46160079d3abb1a55211b5fe99fdbb62fedc1 Stored in directory: /home/j/.cache/pip/wheels/61/e7/42/9d56347e42d7ce19397c0ca050c6bef56640e18be7021ac189 Successfully built mysqlclient Installing collected packages: mysqlclient Successfully installed mysqlclient-2.1.0 (djtest) j@ubuntu20:~/桌面/demo/mdshop/bookmanager$
6. 查看表的建表语言
mysql> mysql> mysql> mysql> show create table course; +--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | course | CREATE TABLE `course` ( `cno` char(2) NOT NULL, `cname` char(30) DEFAULT NULL, `cpno` char(2) DEFAULT NULL, `ccredit` tinyint DEFAULT NULL, PRIMARY KEY (`cno`), CONSTRAINT `course_chk_1` CHECK ((`ccredit` between 0 and 6)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | +--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.23 sec) mysql> mysql>
________________________________________________________
Every good deed you do will someday come back to you.
Love you,love word !