MySQL basics
2024-08-31 04:48:16
@1: MySQL有三大类数据类型, 分别为数字、日期\时间、字符串, 这三大类中又更细致的划分了许多子类型:
数字类型
整数: tinyint、smallint、mediumint、int、bigint
浮点数: float、double、real、decimal
日期和时间
date、time、datetime、timestamp、year
字符串类型
字符串: char、varchar
文本: tinytext、text、mediumtext、longtext
二进制(可用来存储图片、音乐等): tinyblob、blob、mediumblob、longblob
@2: 在MySQL中创建数据库中的表:
mysql> use db1;
Database changed
mysql> show tables;
Empty set (0.00 sec) mysql> create table users(
-> id int(2) not null primary key auto_increment,
-> username varchar(40),
-> passwd text,
-> email text)default charset=utf8;
Query OK, 0 rows affected (0.05 sec) mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| users |
+---------------+
1 row in set (0.00 sec) mysql> desc users;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(2) | NO | PRI | NULL | auto_increment |
| username | varchar(40) | YES | | NULL | |
| passwd | text | YES | | NULL | |
| email | text | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec) mysql> insert into users(username, passwd, email)
-> values("lxw", "123", "lxw.ucas@gmail.com");
Query OK, 1 row affected (0.01 sec) mysql> select * from users;
+----+----------+--------+--------------------+
| id | username | passwd | email |
+----+----------+--------+--------------------+
| 1 | lxw | 123 | lxw.ucas@gmail.com |
+----+----------+--------+--------------------+
1 row in set (0.01 sec)
desc table_name命令用于显示表的结构.
@3: alter table 语句用于创建后对表的修改, 基础用法如下:
添加列 alter table 表名 add 列名 列数据类型 [after 插入位置];
在表的最后追加列 address: alter table students add address char(60);
在名为 age 的列后插入列 birthday: alter table students add birthday date after age;
修改列 alter table 表名 change 列名称 列新名称 新数据类型;
将表 tel 列改名为 telphone: alter table students change tel telphone char(13) default "-";
将 name 列的数据类型改为 char(16): alter table students change name name char(16) not null;
删除列 alter table 表名 drop 列名称;
删除 birthday 列: alter table students drop birthday;
重命名表 alter table 表名 rename 新表名;
重命名 students 表为 workmates: alter table students rename workmates;
删除整张表 drop table 表名;
删除 workmates 表: drop table workmates;
删除整个数据库 drop database 数据库名;
删除 samp_db 数据库: drop database samp_db;
@4:
mysql> select * from users;
+----+----------+--------+-------------------+
| id | username | passwd | email |
+----+----------+--------+-------------------+
| 1 | lxw | 123 | lxwin@foxmail.com |
| 2 | lxw | 123 | lxwin@foxmail.com |
| 3 | lxw | 123 | lxwin@foxmail.com |
+----+----------+--------+-------------------+
3 rows in set (0.04 sec) mysql> select * from users where id>2&username="lxw";
Empty set, 3 warnings (0.00 secG mysql> select * from users where id>2 and username="lxw";
+----+----------+--------+-------------------+
| id | username | passwd | email |
+----+----------+--------+-------------------+
| 3 | lxw | 123 | lxwin@foxmail.com |
+----+----------+--------+-------------------+
1 row in set (0.02 sec) mysql> select * from users where username like "%xw%";
+----+----------+--------+-------------------+
| id | username | passwd | email |
+----+----------+--------+-------------------+
| 1 | lxw | 123 | lxwin@foxmail.com |
| 2 | lxw | 123 | lxwin@foxmail.com |
| 3 | lxw | 123 | lxwin@foxmail.com |
+----+----------+--------+-------------------+
3 rows in set (0.00 sec) mysql> update users set username="wxl" where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from users;
+----+----------+--------+-------------------+
| id | username | passwd | email |
+----+----------+--------+-------------------+
| 1 | lxw | 123 | lxwin@foxmail.com |
| 2 | wxl | 123 | lxwin@foxmail.com |
| 3 | lxw | 123 | lxwin@foxmail.com |
+----+----------+--------+-------------------+
3 rows in set (0.00 sec) mysql> select * from users;
+----+----------+--------+---------------------+
| id | username | passwd | email |
+----+----------+--------+---------------------+
| 1 | lxw | 123 | lxwin@foxmail.com |
| 2 | wxl | 456 | wxl24life@gmail.com |
| 3 | lxw | 123 | lxwin@foxmail.com |
+----+----------+--------+---------------------+
3 rows in set (0.00 sec) mysql> update users set username="lfc", passwd="789" where id=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from users;
+----+----------+--------+---------------------+
| id | username | passwd | email |
+----+----------+--------+---------------------+
| 1 | lxw | 123 | lxwin@foxmail.com |
| 2 | wxl | 456 | wxl24life@gmail.com |
| 3 | lfc | 789 | lxwin@foxmail.com |
+----+----------+--------+---------------------+
3 rows in set (0.00 sec) mysql> update users set username="lfc", passwd=passwd+1, email=email+1 where id=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0 mysql> desc users;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(10) | NO | PRI | NULL | auto_increment |
| username | varchar(40) | YES | | NULL | |
| passwd | text | YES | | NULL | |
| email | text | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec) mysql> select * from users;
+----+----------+--------+---------------------+
| id | username | passwd | email |
+----+----------+--------+---------------------+
| 1 | lxw | 123 | lxwin@foxmail.com |
| 2 | wxl | 456 | wxl24life@gmail.com |
| 3 | lfc | 791 | 1 |
+----+----------+--------+---------------------+
3 rows in set (0.00 sec) mysql> delete from users where id = 3;
Query OK, 1 row affected (0.01 sec) mysql> select * from users;
+----+----------+--------+---------------------+
| id | username | passwd | email |
+----+----------+--------+---------------------+
| 1 | lxw | 123 | lxwin@foxmail.com |
| 2 | wxl | 456 | wxl24life@gmail.com |
+----+----------+--------+---------------------+
2 rows in set (0.00 sec)
where 子句不仅仅支持 "where 列名 = 值" 这种名等于值的查询形式, 对一般的比较运算的运算符都是支持的, 例如
=、>、<、>=、<、!= 以及一些扩展运算符 is [not] null、in、like 等等。 还可以对查询条件使用 or 和 and 进行组
合查询。
Reference:
21分钟MySQL入门教程:http://www.cnblogs.com/mr-wid/archive/2013/05/09/3068229.html#c8
最新文章
- pgRouting 2.0 for windows 来了
- Strange Problem O(∩_∩)O~
- System.Data.SqlTypes.SqlNullValueException: 数据为空。不能对空值调用此方法或
- oc-22-sel
- 一款兼容pc 移动端的tab切换
- 【原创】 Shuffling
- ProFTPD 初探
- php随笔11-Thinkphp常用系统配置大全
- HDU 5009 DP
- springboot学习（二）——springmvc配置使用
- Spring-Security自定义登录页&inMemoryAuthentication验证
- Java 线程锁机制 -Synchronized Lock 互斥锁 读写锁
- unigui 1.90.0 Example
- java 开发注意事项
- 多个Activity之间共享数据的方式
- wireshark抓本地回环包
- Mysql复习大全（转）
- 免费SSL证书Let's Encrypt(certbot)安装使用教程
- redis如何随系统启动
- Codeforces Round #272 (Div. 2) E. Dreamoon and Strings 动态规划