SQL简单语句作用

一、简单的sql语句

　　1、对数据库操作

MariaDB [(none)]> create database testdb;                      创建数据库  
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> show databases;                              显示mysql中的所有数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| testdb             |
+--------------------+
5 rows in set (0.00 sec)

MariaDB [(none)]> use testdb;                                   使用testdb这个数据库
Database changed
MariaDB [testdb]> 

MariaDB [mysql]> show tables;                                   显示这个数据库下的所有表
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
24 rows in set (0.00 sec)

MariaDB [mysql]> drop database testdb;                           删除testdb这个数据库
Query OK, 0 rows affected (0.01 sec)

　　2、对表操作

MariaDB [testdb]> CREATE TABLE user(                             创建一个表
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> username VARCHAR(20) UNIQUE NOT NULL,
    -> sex ENUM('1','2','3') NOT NULL DEFAULT 3
    -> );
Query OK, 0 rows affected (0.01 sec)

MariaDB [testdb]> desc user;                                     查看表结构
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)          | NO   | UNI | NULL    |                |
| sex      | enum('1','2','3')    | NO   |     | 3       |                |
+----------+----------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

MariaDB [testdb]> show create table user;                         查看一个表的创表语句
+-------+---------------------------------------------------+
| Table | Create Table                                           
+-------+---------------------------------------------------+
| user  | CREATE TABLE `user` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL,
  `sex` enum('1','2','3') NOT NULL DEFAULT '3',
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [testdb]> insert into user(id,username,sex) values(1,'TOM',1);            插入一条数据
Query OK, 1 row affected (0.00 sec)

MariaDB [testdb]> insert into user(username) values('Alan');                      插入一条数据 
Query OK, 1 row affected (0.00 sec)

MariaDB [testdb]> select * from user;                                             查看表里面的数据
+----+----------+-----+
| id | username | sex |
+----+----------+-----+
|  1 | TOM      | 1   |
|  2 | Alan     | 3   |
+----+----------+-----+
2 rows in set (0.00 sec)

MariaDB [testdb]> truncate table user;                                             删除表中的数据(保留表结构)
Query OK, 0 rows affected (0.00 sec)

MariaDB [testdb]> update user  set sex=0 where id=1;                               修改id为1的条数据，把sex修改为0
Query OK, 1 row affected, 1 warning (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 1

MariaDB [testdb]> delete from user where id =2;                                    删除id为2的那条数据
Query OK, 1 row affected (0.00 sec)

MariaDB [testdb]> drop table user;                                                 删除user表
Query OK, 0 rows affected (0.00 sec)

  MariaDB [testdb]> alter table user add age varchar(10) default '1';                添加字段
  Query OK, 0 rows affected (0.00 sec)
  Records: 0 Duplicates: 0 Warnings: 0 

　MariaDB [testdb]> alter table user drop age1;                                       删除字段
  Query OK, 0 rows affected (0.08 sec)  
  Records: 0 Duplicates: 0 Warnings: 0

 二、mysql的配置文件

[root@master ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d